1. Amaç
Bazı durumlarda aradığımız veri aynı altyapıyı kullanan ancak farklı sayfalara dağılmış tablolarda olabilir. Böyle bir durumda farklı sayfalarda yer alan verilerden istediğimiz veri ile ilgili bilgileri nasıl çekebiliriz.
Bunun birden fazla yöntemi mevcut. Bana sorarsanız en kolay yöntemi, PowerQuery kullanarak dosyadaki tüm tabloları tek bir tablo olarak birleştirmek ve nihai tabloda DüşeyAra kullanmak.
Ancak PowerQuery kullanmadan bunu formüller ile çözmenin bir yolu var mı derseniz, evet var. Bu yazımda bu yöntemi anlatmak istiyorum.
Örnek dosyayı buradan indirebilirsiniz.
2. Uygulama
a. Çalışma Kitabı Sayfalarını Çekme
İlk yapmamız gereken, Excel’de yer alan tüm sayfaların listesini çıkarmak. Bunun için güncel bir yöntem olmadığından Excel 4 makro komutu kullanacağız. Bu komut aşağıdaki gibi yazılıyor.
=AL.ÇALIŞMAKİTABI(1)
=GET.WORKBOOK(1)
Komut temelde çalışma kitabı hakkında bazı bilgileri bize veriyor, buradaki bir parametresi çalışma sayfalarının adı anlamında. Ancak bize verdiği sayfa isimler [kitap_ismi]sayfa_ismi şeklinde. Bu isimden sadece sayfa ismini çekmek için PARÇAAL ve BUL metin formüllerini kullanacağız.
=PARÇAAL(AL.ÇALIŞMAKİTABI(1);BUL(“]”;AL.ÇALIŞMAKİTABI(1))+1;255)
Yalnız Excel çalışma kitapları Excel 4 Makro komutlarını çalıştırmanıza izin vermez. Bu komutları çalıştırmanın tek yolu, bunları tanımlı bir isim içinde kullanmaktır. Bu yüzden yukarıda yazdığım formülü, Sayfalar adında yeni bir isim tanımlayarak oraya giriyorum. (Videoda Sayfalar yazınca çıkan listenin sizde de gözükmesi için Office 365 son güncellemeye sahip olmalısınız.)
Videoda dikkat ederseniz Sayfa 1 adlı yeni bir sayfa açtım ve formülleri G3 ve H3 hücrelerine gireceğim. Sayfa1 ilk sayfa ise ve ürün kodu B3 hücresinde yer alır ise döngüsel başvuru oluşur ve formüller hata verir. Bunu çözmenin bir başka yolu da bu sayfayı en sona taşımak.
Not 1: Excel 4 makroları bu tarz tek satır komutlarla bir Makro sayfasına yazılıyor idi. Daha fazla bilgi için bu siteye girebilirsiniz.
Not 2: Excel 4 makroları, Microsoft tarafından desteklenen bir özellik değildir ve ne zamana kadar Excel içinde çalışmaya devam edeceği belli değildir.
Not 3: Excel 4 makro komutları eklenmiş bir dosyayı normal xlsx dosyası olarak kaydedemezsiniz. Makro içeren çalışma kitabı olarak kayıt etmeniz gerek. Bu da dosya paylaşırken güvenlik duvarlarına takılabilir.
Eğer Excel 4 makrolarını kullanmak istemiyorsanız aynı işlevi görecek bir makro yazmamız gerek. Merak etmeyin oldukça kısa ve öz bir makro, geliştirici sekmesine girerek yeni bir modül ekleyin. Aşağıda yazdığım makroyu bu yeni modüle kopyalayın. Kullanımı da bir önceki Excel 4 makrosu gibi isim tanımlamada =SayfaIsimleriDizisi() formülünü yazacaksınız.
Option Explicit
Public Function SayfaIsimleriDizisi() As Variant
Application.Volatile
Dim SayfaIsimleri(1 To ThisWorkbook.Worksheets.Count) As String
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
SayfaIsimleri(i) = ThisWorkbook.Worksheets.Item(i).Name
Next
SayfaIsimleriDizisi = SayfaIsimleri
End Function
b. Aranan Hangi Sayfada Olduğunu Bulma
İlk yapmamız gereken iş, aradığımız değerin tam olarak hangi sayfada olduğunu bulmak. İlk yapmamız gereken, değerlerin bulunduğu sayfaları ve aralıkları çıkarmak. Sayfa listemiz metin haline tanımladığımız isim içinde, sonuna hücre adreslerini ekleyeceğiz ve DOLAYLI formülü ile adrese çevireceğiz.
=DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5”)
Şimdi aradığım değer (F3 hücresinde) bu sayfalardan hangisinde yer alıyor bunu bulalım. Bunun için de EĞERSAY formülünü kullanacağım.
=EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5”);$F$3)
Bu formüller bir dizi değer döndüreceğinden ya CTRL+SHIFT+ENTER ile dizi formülü haline getirmeliyiz. Eğer formülün döndürdüğü değeri görmek istiyorsanız aşağıdaki formülü yazarak formül değerlendir diyebilirsiniz.
=İNDİS(EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5”);$F$3);;)
Artık elimizde 0;0;1;0 gibi aradığımız değerin hangi sayfada olduğunu bulan bir dizi var. Bu dizi bize değerin hangi sayfada olduğunu gösteriyor.
b. Aranan Değerin Değeri Çekme
Elimizde sayfalar listesi ve aradığımız değerin hangi sayfada olduğunu gösteren ve 0 ila 1’lerden oluşan bir dizi var. Ve bir diziden, istenilen elemanı çekmeye yarayan bir KAÇINCI formülümüz var.
=KAÇINCI(1;EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5”);$F$3);0)
Formül 3 değerini döndürecek. Bu Sayfalar ismindeki 3. elemanı ifade ediyor. Sayfayı da İNDİS formülü ile çekebiliriz:
=İNDİS(Sayfalar;;KAÇINCI(1;EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5”);$F$3);0))
Formül “Somun” değerini döndürecektir. Aradığımız değer somun sayfasında ama bize sadece sayfa ismi yaramaz, bir aralık lazım. Aralığımızda B3:D5 aralığı, bu ikisini birleştirip DOLAYLI ile kullanırsak elimizde bir Excel aralığı olacaktır:
=DOLAYLI(“‘”&İNDİS(Sayfalar;;KAÇINCI(1;EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$B$2:$D$5″);$F$3);0)) &”‘!$B$2:$D$5”)
Artık yapmamız gereken tek şey bunu DÜŞEYARA formülü ile birleştirerek istediğimiz sütundaki değeri bulmak. 2. ve 3. sütunlar için sadece DÜŞEYARA formülündeki sğtun değerini değiştirmeniz yeterli:
=DÜŞEYARA($F$3;DOLAYLI(“‘”&İNDİS(Sayfalar;;KAÇINCI(1;–(EĞERSAY(DOLAYLI(“‘”&Sayfalar&”‘!$b$2:$d$5″);$F$3)>0);0))&”‘!$B$2:$D$5”);3;0)
Anlattığım tüm formüllerde CTRL+SHIFT+ENTER ile formülü dizi formülü haline getirmeyi unutmayın lütfen.
d. Formülün Zaafları
Her ne kadar istediğimiz sayfadan veri çekmemize yarayan bir formül olsa da bazı eksiklikler mevcut. İlk olarak yeni bir sayfa eklediğinizde “AL.ÇALIŞMAKİTABI” ve verdiğim makro bunu algılamıyor. Sayfa konumunu değiştirmeniz veya CTRL+SHIFT+ALT+F9 tuşlarına basarak Excel’in tüm formülleri tekrar değerlendirmesini sağlamanız gerek.
İkinci olarak her bir sayfa için girilen aralık sabit, B2:D5 aralığı, eğer sayfalarda daha büyük veya daha küçük bir aralık var ise bu aralığı en büyük olana göre ayarlamanız gerek.
Düşeyara her excel kullanan kişi için en önemli formüllerdendir. Detaylı anlatımınız için teşekkür ediyorum.