DÜŞEYARA' yı Unutturan Profesyonel İkili İNDİS ve KAÇINCI

Selam!

 Büyük bir müşteri listesi, binlerce satırlık bir stok tablosu veya karmaşık bir finansal rapor. Eğer bu gibi büyük veri setleriyle çalışıyorsan, bekleme ekranını mutlaka gördün. Sadece bir veriyi aratmak için kullandığın DÜŞEYARA (VLOOKUP) formülü satırları tararken Excel'in donmasına, yavaşlamasına sebep olur.

Peki, ileri düzey kullanıcılar bu sorunu nasıl aşıyor?

Cevap, DÜŞEYARA’nın tahtını sallayan, ondan çok daha hızlı, çok daha esnek ve çok daha güçlü bir ikilide, İNDİS (INDEX) ve KAÇINCI (MATCH).

Excel’i ilk kullanmaya başladığım yıllarda, DÜŞEYARA (VLOOKUP) diye bir fonksiyondan haberim bile yoktu. Ofiste bir iş arkadaşım bana doğrudan İNDİS (INDEX) ve KAÇINCI (MATCH) formüllerini öğretmişti. (Herkes bildiği kadarını öğretir) Ben de yıllarca, aradığım verileri bulmak için bu ikiliyi kullandım.

Bir gün başka bir arkadaşımdan DÜŞEYARA’ yı öğrendiğimde ise kendi kendime, "Ya yıllarca boşuna ne uğraşmışım, bu formül süper kolaymış!" dedim ve adeta çağ atladığımı hissettim. 😂

Ama bu büyü çabuk bozuldu, evet DÜŞEYARA'yı yazması çok kolay, ancak zaten yavaş olan bilgisayarımda binlerce satırlık veri setleriyle çalışmaya başlayınca, formülün hiç de "süper" olmadığını fark ettim. Benim ikilimden daha yavaş çalışıyordu bu fonksiyon. Bende tekrar İNDİS ve KAÇINCI ikilisine geri döndüm.

Eğer benzer bir durumu yaşıyorsan, DÜŞEYARA' nın o "kolay" görünen ama performansı tüketen yapısından sıkıldıysan, doğru yerdesin. Bu yazıda, DÜŞEYARAnın neden yavaş kaldığını ve neden tüm Excel profesyonellerinin İNDİS/KAÇINCI ikilisini tercih ettiğini adım adım keşfedeceksin.

Haydi başlayalım.

Sorun Nerede? DÜŞEYARA (VLOOKUP) Neden Yavaş?

Öncelikle sorunu belirleyelim. DÜŞEYARA, aslında harika bir fonksiyon ancak bir "performans" sorunu var.

DÜŞEYARA formülü (=DÜŞEYARA(Aranan_Değer; Tablo_Dizisi; Sütun_İndis_Sayısı; [Aralık_Bak])) çalıştığında, Excel'e şunu dersin: "Bu değeri al, verdiğim tüm tablonun en sol sütununda ara. Bulunca, sağa doğru 3. (veya 4., 5.) sütundaki veriyi getir."

Sorunun başlangıcı burası. Veri boyutu büyüdüğünde;

• DÜŞEYARA, aradığı değeri bulmak için sadece ilgili sütuna değil, senin seçtiğin tüm tablo dizisine (örneğin, A:F arası) odaklanır. (Sanılanın aksine sadece ilk sütunda değil, her yerde ilgili değeri arar.)
• Excel'in hesaplama motoru her şeyden önce doğruluğa ve tutarlılığa önem verir. Bu yüzden, "Acaba bu değişince bu hücre etkilenmiş midir?" diyerek bağlantılı gördüğü tüm formülleri tekrar tekrar kontrol eder. (Tam delilik 😂) DÜŞEYARA gibi fonksiyonlar statik (sabit) bir değer değil, dinamik (değişken) veri çektiği için, Excel bu formülün her zaman en güncel veriyi gösterdiğinden emin olmak ister.

Tablonda yüzlerce DÜŞEYARA formülü varsa ve sen tek bir hücreyi bile değiştirsen, Excel "Acaba bu değişiklik tüm formülleri etkiledi mi?" diye düşünür ve binlerce formülü yeniden hesaplamaya başlar. İşte o "donma" anı tam da bu anda gerçekleşir.

Süper İkili İNDİS ve KAÇINCI Nasıl Çalışır?

Böyle anlarla olabildiğince az karşılaşmak için, sahneye daha hızlı çalışan ikilimiz çıkıyor. Öncelikle onları ayrı ayrı inceleyelim.

KAÇINCI (MATCH) Nedir?

Bu arkadaş bir arama fonksiyonu ve görevi, aradığın bir değerin (örneğin "Ürün-123") belirli bir sütun veya satır içinde tam olarak kaçıncı sırada olduğunu bulmak.

• Sözdizimi: =KAÇINCI(Aranan_Değer; Arama_Dizisi; [Eşleştirme_Türü])
• Örnek: KAÇINCI("Ahmet"; A1:A50; 0) formülü, "Ahmet" isminin A sütununda (A1'den A50'ye kadar) kaçıncı sırada olduğunu sana sayı olarak verir (örn: 12).
• Önemli İpucu: Eşleştirme_Türü için her zaman 0 (Sıfır) kullan. Bu, "Tam Eşleşme" demektir ve veri doğruluğu için çok önemlidir.

İNDİS (INDEX) Nedir?

Bu arkadaş ise, bir arama değil, getirme fonksiyonu, İNDİS’ e bir "adres" (koordinat) verirsin, o da sana o adresteki veriyi getirir.

• Sözdizimi: =İNDİS(Dizi; Satır_Sayısı; [Sütun_Sayısı])
• Örnek: =İNDİS(B1:B50; 12) formülü, "B1'den B50'ye kadar olan alanın 12. satırındaki değeri bana getir" demektir.

Birlikte Nasıl Çalışıyorlar?

KAÇINCI ile aradığımız verinin sıra numarasını buluyoruz. Sonra bu sıra numarasını İNDİS'in içine yerleştirerek, başka bir sütundaki aynı sıraya denk gelen veriyi çekiyoruz.

Yani :

=İNDİS(Sonucu_Getireceğin_Sütun; KAÇINCI(Aradığın_Değer; Değeri_Aradığın_Sütun; 0))

Bu yöntem, DÜŞEYARA’ nın aksine tüm tabloyu değil, sadece ilgili iki sütunu çalıştırır. Bu da Excel üzerindeki yükü %90'a varan oranda azaltır ve ciddi performans sağlar. 

'Sağdan Sola' Arama Özgürlüğü

DÜŞEYARA’ nın en sinir bozucu kuralı aradığın değer her zaman tablonun en sol sütununda olmasıdır. Eğer Personel ID'si C sütununda, ama sen A sütunundaki Adı getirmek istiyorsan, DÜŞEYARA çalışmaz, çalışamaz.

İNDİS ve KAÇINCI'’ da ise böyle bir kural yok. İster sağdan sola ister soldan sağa her yönden sonuç getirebilirsin.

Örnek:

Bir fatura listen var. Fatura Numarası (FT-1001) olan kayıt hangi firmaya ait bulmaya ihtiyacın var. Sana lazım olan Firma Adı A sütununda.

DÜŞEYARA’ nın bittiği yer burası işte. Devreye İNDİS/KAÇINCI giriyor:

Aramak istediğin Fatura No (örn: "FT-1002") E2 hücresinde olsun.

Formül: =İNDİS(A2:A4; KAÇINCI(E2; C2:C4; 0))

Burada Excel'e tam olarak şunu diyoruz: "Önce KAÇINCI fonksiyonunu çalıştır. E2 hücresindeki değeri (yani 'FT-1002') al, C2:C4 sütununda ara ve bana kaçıncı sırada olduğunu bul (Sonuç: 2). Şimdi, bu bulduğun '2' sayısını İNDİS fonksiyonuna ver. İNDİS de A2:A4 aralığındaki 2. sıradaki veriyi (yani 'XYZ A.Ş.') bulup getirsin."

Not: Kafan karışmasın! "Formüle İNDİS ile başladık ama neden önce KAÇINCI çalıştı?" diye düşünebilirsin. Tüm Excel formüllerinde hesaplama her zaman en içteki parantezden başlar. Bu yüzden önce KAÇINCI çalışır, o bir sayı üretir (2), sonra İNDİS bu sayıyı kullanarak sonucu getirir.

 Bu sayede tablolarını bozmadan, sütunların yerini değiştirmeden istediğin yerden sonuç getirebilirsin.

İki Yönlü (Matris) Arama Yapılabilir mi?

İNDİS/KAÇINCI ikilisiyle yapabileceklerinin sınırı neredeyse yok. En güçlü kullanımlarından biri de iki yönlü (hem satır hem sütun) aramadır.

Örnek :

Aşağıdaki gibi bir bölge-ay matrisin var ve "Marmara" bölgesinin "Şubat" ayı satışını tek formülle bulmak istiyorsun.

Burada iç içe iki adet KAÇINCI kullanacağız, biri satırı (Marmara), diğeri sütunu (Şubat) bulmak için.

Aranan Bölge (Marmara) G1 hücresinde, Aranan Ay (Şubat) H1 hücresinde olsun.

Formül: =İNDİS(B2:D4; KAÇINCI(G1; A2:A4; 0); KAÇINCI(H1; B1:D1; 0))

Bu formülle Excel'e bir adres tarifi veriyoruz. Bir önceki örnekte olduğu gibi, Excel yine en içteki parantezlerden, yani KAÇINCI fonksiyonlarından başlayacak:

Adım 1 (Satırı Bul): Önce ilk KAÇINCI çalışır. G1 hücresindeki "Marmara" değerini alır, A2:A4 dikey aralığında arar ve "Marmara"nın 2. sırada olduğunu bulur.

Adım 2 (Sütunu Bul): Sonra ikinci KAÇINCI çalışır. H1 hücresindeki "Şubat" değerini alır, B1:D1 yatay aralığında arar ve "Şubat"ın 2. sırada olduğunu bulur.

Adım 3 (Adresi Getir): Artık Excel'in elinde iki adet sayı var: Satır için 2 ve Sütun için 2. Bu iki sayıyı, en dıştaki İNDİS fonksiyonuna koordinat olarak verir.

Sonuç: İNDİS fonksiyonu, ana veri alanı olan B2:D4'e bakar ve "Bana 2. satır ile 2. sütunun kesiştiği yerdeki veriyi getir" komutunu uygular. Bu kesişim noktası tam olarak "210.000" değeridir.

DÜŞEYARA ile bunu yapmak için karmaşık formüllere ya da yardımcı sütunlara ihtiyacın varken, İNDİS/KAÇINCI ile tek seferde, ışık hızında formülde sonuç alırsın.

“E ÇAPRAZARA (XLOOKUP) fonksiyonu da yapar bu işi” dediğini duyar gibiyim. Haklısın, ÇAPRAZARA, temel olarak İNDİS ve KAÇINCI'nın yaptıklarını tek bir fonksiyonda birleştirir ve DÜŞEYARA’ nın yerini almak için tasarlanmıştır.

ÇAPRAZARA Formülü: =ÇAPRAZARA(Aranan_Değer; Arama_Dizisi; Döndürülen_Dizi)

“Madem ÇAPRAZARA var, İNDİS/KAÇINCI öğrenmek hala gerekli mi?”

Cevap: Kesinlikle EVET! ÇAPRAZARA harika bir fonksiyon olsa da, sadece en yeni Excel sürümlerinde çalışır. (2019 ve sonrası) Eğer hazırladığın bir Excel dosyasını Excel 2016, 2013 veya daha eski bir sürümü kullanan bir müşterinle, tedarikçinle veya başka bir departmanla paylaşırsan, ÇAPRAZARA formüllerin #AD? (#NAME?) hatası verir ve çalışmaz.

İNDİS ve KAÇINCI ise Excel'in en eski sürümlerinden beri var olan, evrensel ve %100 uyumlu fonksiyonlardır. Bu ikiliyi bilmek, senin her platformda çalışan, sağlam ve profesyonel dosyalar hazırlamanı garanti eder. Bu durumda şunu söyleyebiliriz, “ÇAPRAZARA lüks, İNDİS/KAÇINCI ise temel direktir”

Sonuç olarak, Excelde verimlilik, sadece bir işi bitirmek değil, o işi en hızlı, en pratik ve en az kaynakla bitirebilmektir. DÜŞEYARA süper bir fonksiyondur ama bazen yolda bırakabilir, ancak İNDİS ve KAÇINCI ikilisi, veri ne kadar büyük olursa olsun, sana her zaman en yüksek performansı sunar.

Aşağıdaki bağlantılardan fonksiyonların kullanımına iilşkin detaylı bilgi edinebilirsin.

İNDİS-KAÇINCI

Arama ve Başvuru Fonksiyonları