Excel’de çoğunlukla bazı bilgiler bir dosya veya sayfada, bu bilgilere bağlı başka bilgiler ise başka bir dosya veya sayfada olabilir. Ve biz ana listemizdeki girdilere göre alt sayfa veya dosyalarda eşleşen çekmek isteyebiliriz.
Örnek olarak personel listesi bir sayfada yer alabilir, bu personelin sattığı siparişler başka bir sayfada uzun bir liste olarak yer alabilir, ve tabii müşterilerimizin listesi de apayrı bir listede toplanabilir. Biz personelimizin yaptığı siparişlerin bir listesini görmek istersek ilgili iki listeyi birleştirmemiz gereklidir.
Bu yazımda Power Query kullanarak bu işlemi hızlıca herhangi bir makro kullanmadan oldukça etkili bir şekilde nasıl yapabileceğimizi göstermek istiyorum.
1. Örnek Veriler ve Uygulama Dosyaları
Bu uygulama için ben oldukça basit 3 tane tablo kullanacağım. Bunlar sırası ile sipariş koduna göre sipariş bilgilerinin yer aldığı Siparişler tablosu, personel numarasına göre personel bilgilerinin yer aldığı Personel tablosu ve Hangi müşterinin hangi siparişi verdiğini gösteren bir Müşteri tablosu. Aşağıda listeleri görebilirsiniz, ben bunları Excel Tablosu olarak ayarladım. Örnek dosyalara buraya tıklayarak erişebilirsiniz.
Bu tablolarda dikkat edeceğiniz üzere Sipariş tablosundaki Personel Numarası ile Personel Tablosundaki Sicil Numarası bağlantılı, aynı şekilde Sipariş ve Müşteri tablolarındaki sipariş kodu da birbiri ile aynı. Bu tabloları bağlarken bize bir referans olacaklar.
Ben bu uygulamada hem tek bir dosya (PowerQueryMerge.xlsx) hem de her bir tablo için ayrı çalışma kitapları olarak dosyalar hazırladım. Her iki durumda da tablo bağlama uygulaması .
2. Uygulama
a. Farklı Dosyalardan Tabloları Çekme
İlk yapmamız gereken işlem elimizdeki tüm tabloları Power Query içine aktarmak. Eğer bu tablolarınız farklı dosyalarda ise aşağıdaki resimlerde farklı Excel sürümlerinde bu tabloları nasıl çekebileceğinizi gösterdim.
Eğer bu tablolar aynı dosyada ise yine aynı kısımdan bu sefer sol tarafta yer alan “Aralık/Tablo” seçeneğine tıklamanız gerek.
Bu seçeneğe tıkladığınızda karşınıza ilgili dosyayı seçmenizi isteyen bir pencere görüntülenecek. Önce “Siparişler.xlsx” dosyasını seçiyorum. Karşıma dosya içinde yer alan tablolar ve çalışma sayfalarını gösteren bir pencere çıkacak.
Burada ben tabloyu seçeceğim. Tablonun üzerine tıkladığınızda alt tarafta “Yükle” simgesi etkileşecek, siz bu simgenin yanındaki ufak oka basarak “Şuraya Yükle…” seçeneğini seçin. Bu seçeneği seçtiğinizde yeni bir pencere görüntülenecektir. Bu pencere Excel’in farklı sürümlerinde farklı seçenekler barındırıyor. Siz en son seçenek olan “Yalnızca bağlantı oluştur” seçeneğini seçin. Tüm pencereler kapanacak ve çalışma kitabının sol tarafında Sorgular ve Bağlantılar penceresi görüntülenir.
Bu işlemi diğer dosyalar için de uygulayacağız. Toplamda 3 dosya için de bir bağlantı oluşturacağız.
b. Aynı Dosyadan Tabloları Çekme
Aynı dosyadan veri çekmek için ilgili listede yer alan herhangi bir hücreye tıklayarak yukarıdaki resimlerde gösterdiğim “Tablo/Aralıktan” seçeneğine tıklamam gerek. Bundan sonra Excel Power Query düzenleyicisi açılacak ve liste içindeki verileri görüntüleyecektir.
Pencerenin sol üst kısmında yer alan “Kapat ve Yükle” seçeneğininin altındaki ok işaretine tıklayarak “Kapat ve Hedefe Yükle” seçeneğini seçin. Ardından bir önceki adımda olduğu gibi sadece bağlantı oluşturun.
Eğer benim gibi listeleri Excel Tablosu haline getirdi iseniz sol taraftaki ad kısmında tablo adı otomatik olarak gelecektir. Eğer tablo oluşturmadı iseniz bu pencereyi kapatmadan önce buraya belirleyici bir isim girmek çok iyi olacaktır.
c. İki Tabloyu Birleştirme
Tabloları birleştirme tamamen ne elde etmek istediğiniz ile alakalı. Örnek olarak ben burada Personel’e göre siparişleri bulmak istiyorum. Yani Personel tablosundaki her bir girdi için Sipariş tablosunda denk gelen tüm girdileri listelemek istiyorum. Bunun için bana bu iki tablodaki girdileri ilişkilendirmek için bir değer lazım. Bu değer de”Sicil No” sütununda.
Tabloları birleştirmek için kullanacağımız komut, birleştir komutu. Bu da Office 2013 ve öncesinde “Tablo/Aralıktan” komutunun hemen yanında. Diğer sürümlerde Veri Al/Yeni Sorgu’ya tıkladığınızda “Sorguları Birleştir” seçeneğinin altında “Birleştir” komutu. Bu komuta tıkladığınızda yeni bir pencere açılacak.
Bu pencere ilk açıldığında seçili tablo olmayacaktır. İlk tablo olarak Personel tablosunu seçeceğiz, ikinci tablo olarak Sipariş tablosunu seçeceğim. Bu seçimleri yaptığınızda ekranda gördüğünüz gibi seçim pencerelerinin altında ilgili tablonun içeriği görüntüleniyor.
Birleşim türü de “Sol Dış Birleşim” olarak kalacak. Buraya tıkladığınızda seçenekleri ve yanlarında kısa bir açıklamasını görebilirsiniz. Bu seçenek ile ilk tablodaki tüm girdiler alınacak ve ikinci tablodaki eşleşen tüm girdiler getirilecek.
İkinci olarak yapmanız gereken şey ise bağlantı için referans değerlerin yer aldığı sütunları seçmek. Bunun için ilgili tabloların ilgili sütun başlıklarına tıklamam yeterli. Excel her iki sütun içinde eşleşen değerleri birbirine bağlayacaktır.
Gerekli seçimleri yaptıktan sonra “Tamam” düğmesine bastığınızda Excel size Power Query düzenleyicisini açacaktır ve aşağıdaki ilk görüntü ile karşılaşacaksınız. Sol üst köşedeki yanlara açılan iki ok simgesine dikkat. Bu, ilgili kayıtlar için ikinci tabloda da kayıt olduğunu ifade eder. Buna tıkladığınızda Excel ikinci tablodan almak istediğiniz sütunları gösteren bir seçenek penceresi açacaktır (2. resim).
Ben seçim penceresinde “Personel” seçeneğini iptal edeceğim çünkü zaten sicil numarası ilk tablomda var. Diğer başlıklar bana lazım, onlara ellemiyorum. Tamam düğmesine bastığımda Power Query düzenleyicisinde hem ilk tablodaki tüm sütunlar, hem de ikinci tablodan seçtiğim sütunları içeren veriler gösterilir. Bu yapılırken de ikinci tablo sütun adları “Tablo Adı-Sütun Adı” biçiminde ayarlanır. Aşağıdaki resimde ekranı görebilirsiniz.
Ben burada “Görev” sütununu görmek istemiyorum. Ayrıca Tarih sütunundaki değerler tarih/saat olarak biçimlendirilmiş. Ben bu tabloyu istediğim gibi biçimlendireceğim. Sağ taraftaki pencerede yer alan Ad kısmına da sorguyu tanımlayan düzgün bir isim gireceğim. Kapat ve yükle dediğimde tabloyu Excel içinde yeni bir sayfada yeni bir tablo olarak oluşturacak ve Excel’in sağ tarafında gösterilen Sorgular ve Bağlantılar penceresinde yeni sorgunun ismi görüntülenecektir. . Aşağıdaki videoda işlem adımlarını gösteriyorum:
c. Yeni Tablo Ekleme
Personele göre siparişlerin müşterilere göre dağılımını da görmek istersem bir kez daha sorgu birleştirme işlemi yapacağım. Bu sefer en son oluşturduğu sorgu ile Müşteri tablosunu birleştireceğim. Burada benim referans değerlerim Sipariş Kodu. Bir kısa ipucu vereyim, eğer Sorguları birleştir komutuna girmeden önce “Sorgular ve Bağlantılar” penceresinde bir sorgu seçerseniz, bu sorgu birleştirme penceresinde ilk tablo olarak atanır.
Birleştir penceresine girdikten sonra ilk eleman olarak hazırladığım sorguyu, ikinci eleman olarak Müşteri tablosunu seçeceğim. Birleşim türü değerini değiştirmeyeceğim. Birleştir dediğimde gelen düzenleme ekranında tabloyu genişletirken sadece müşteri sütunu seçili olacak. Sonrasında bu sorguya da bir isim vererek Kapat ve yükle dediğimde Personel-Sipariş-Müşteri ile ilişkilendirilmiş bir tablo elde edeceğim.
İlgili adımları aşağıdaki videoda gösterdim:
Artık elimde tüm personelin hangi siparişleri hangi müşterilere yaptığını gösteren bir ana tablo var. Bu tablo üzerinden istediğim hesaplamaları yapabildiğim gibi istersem özet tablo veya grafik hazırlamak için bu verileri kullanabilirim. İşin güzel tarafı ana tablolarıma veri eklediğimde tek yapmam gereken tüm sorguları yenilemek. Bu sayede verilerimin güncelliğini çok daha hızlı ve kolayca yapabilirim.