Kurs
Google Sheets ile İstatistiğe Giriş
4 sa
48.5K
Genellikle Excel ilk kez öğretilirken, veri düzenleme için birincil yöntem olarak VLOOKUP tanıtılır. VLOOKUP, Elektronik Tablolarda VLOOKUP ile Veri Düzenleme yazısında gösterildiği gibi veri işleme ile ilgili pek çok görevi yerine getirebilen güçlü bir işlevdir. Aynı eğitimde, daha karmaşık bir formül olmasının bedeliyle VLOOKUP'a göre çeşitli avantajlara sahip olan, dikey tablo aramaları gerçekleştirmeye yönelik bir başka yöntem olan INDEX-MATCH kombinasyonunun temellerini de ele aldık.
Bu avantajlar arasında şunlar yer alır:
- Dinamik Sütun Başvuruları: Arama işlevini etkilemeden veri kümenize sütun ekleyip silebilmenizi sağlar.
- Büyük Veri Kümelerinde Daha Yüksek İşleme Hızı: Tablolarınız genişse, INDEX-MATCH'in yalnızca almak istediğiniz sütun ve arama sütunuyla ilgilenmesi sayesinde VLOOKUP'tan çok daha hızlı olduğunu muhtemelen fark edersiniz.
- Arama Değerlerinin Konumu: VLOOKUP, tablo aralığında seçtiğiniz ilk sütunun solunda yer alan herhangi bir değere bakamaz; ancak INDEX-MATCH yatay olarak da bakabilir, dolayısıyla bu sınırlamaya sahip değildir.
- Sınırlı Arama Değeri Uzunluğu: VLOOKUP, 255 karakterden uzun değerleri arayamaz; INDEX-MATCH ise arayabilir.
Ayrıca, INDEX-MATCH birden fazla sütunun değerine göre eşleştirmeye ve büyük/küçük harf duyarlı eşleştirmeye de olanak tanır; bu da özellikle büyük/küçük harf farkına göre değişebilen belirli kimliklerle (ID) çalışırken çok yararlıdır. Bu nedenle bu eğitim, INDEX-MATCH kombinasyonunun gelişmiş özelliklerine daha derinlemesine inmeyi ve bu özelliklerin nasıl işe yaradığını örneklerle göstermeyi amaçlar.
INDEX-MATCH'in Temelleri
En basit haliyle INDEX-MATCH, ortak bir anahtara dayalı olarak basit dikey tablo aramaları yapmak için VLOOKUP ile aynı şekilde kullanılabilir. Formülün temel yapısı şu şekildedir:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: Değerini almak istediğiniz sütunun aralığı.
- lookup_value: Aramak istediğiniz değer (anahtar).
- lookup_column_range: Eşleştirmek istediğiniz anahtarları içeren sütunun aralığı.
- match_type: 0 veya 1 olabilir. 0 tam eşleşmeyi, 1 ise yaklaşık eşleşmeyi belirtir. VLOOKUP'taki FALSE/TRUE bayrağına benzer.
Şimdi, INDEX-MATCH'in nasıl çalıştığını birkaç basit tabloyla gösterelim. İlk tabloda bazı isimler ve benzersiz anahtarlar bulunuyor. İkinci tabloda ise aynı anahtarlar (her zaman aynı sırada olmak zorunda değil) ve bazı açıklamalar var. Dolayısıyla görev, ikinci tablodaki açıklamaları INDEX-MATCH kullanarak ilk tablodaki isimlerle birleştirmek. Tablolar aşağıda görülebilir:
 |
 |
Gördüğünüz gibi, ilk tabloda açıklama eksik. Şimdi INDEX-MATCH kullanarak ismi açıklamasıyla birleştirelim:
Kolay değil mi? Videoda gördüğünüz gibi, ilk adım INDEX işlevi içinde almak istediğimiz verileri içeren bir column_range seçmektir. Bu durumda bu, Description (M2:M8). İkinci adım MATCH işlevi içinde lookup_value seçmektir; yani B2 hücresi. Son olarak, MATCH işlevinin içine lookup_column_range (L2:L8) ekler ve lookup_value için tam eşleşme istediğimizden match_type olarak 0'ı seçeriz. Geri kalanı ENTER'a basıp formülü aşağı doğru sürükleyerek ad ve açıklamaları birleştirmektir.
INDEX-MATCH, VLOOKUP'ta olduğu gibi ayrı sayfalardaki tabloları birleştirmek için de kullanılabilir. Aşağıda bir örnek video görebilirsiniz:
Not: Videolarda seçtiğim sütun aralıklarının etrafının $ işaretleriyle çevrili olduğunu fark etmiş olabilirsiniz. Bu, formülleri dikey veya yatay sürüklediğinizde değişmeyen sabit aralıklar seçmek için yapılır. Bu gösterimi eğitim boyunca kullanmaya devam edeceğim.
Şimdiye kadar her şey biraz daha zahmetli olsa da basit bir VLOOKUP gibi davranıyor. Yine de, bu eğitimin başında belirtildiği gibi, INDEX-MATCH yolunu seçerseniz çeşitli avantajlar elde edersiniz. En kullanışlı olanlardan biri dinamik sütun başvurularıdır. Yani, değer aradığınız tablo aralığına sütunlar ekleyip çıkarmanıza rağmen aramanın etkilenmemesini sağlar.
Oyuncak örneğimize yeni bir sütun ekleseydik ve VLOOKUP kullansaydık, sol tablodaki formülü açıklamayı referans alacak şekilde baştan yazmamız gerekirdi; bu durumda açıklama 3. sütun olurdu. Oysa dinamik sütun başvurularına sahip INDEX-MATCH kullanırsanız buna gerek kalmaz. Bu karşılaştırmayı aşağıdaki videoda görebilirsiniz:
Yukarıdaki örnek, uzay gemisi türlerini içeren yeni sütun eklendiğinde INDEX-MATCH kullanan arama sütununun nasıl uyum sağladığını, VLOOKUP kullanan sütunun ise uyum sağlayamadığını gösteriyor. Bu avantaj, özellikle iş satış raporu gibi büyük Excel tablolarında arama yapılan görevlerde INDEX-MATCH'i öne çıkarır. Başka bir deyişle, Excel'de oluşturduğunuz ve tamamı tek bir veri tablosuna referans veren birden fazla iş panonuz olduğunu hayal edin. VLOOKUP kullanıyorsanız ve bir gün araya yeni bir özellik eklemek için ortaya bir sütun eklemek gerekirse, o tabloya bağlı tüm panolardaki VLOOKUP çağrılarını değiştirmeniz gerekir. Ancak panoları arama yöntemi olarak INDEX-MATCH ile oluşturduysanız böyle bir sorunla karşılaşmazsınız.
Gelişmiş INDEX-MATCH: Büyük/Küçük Harf Duyarlı Eşleştirme
Dinamik sütun başvuruları çok yararlıdır, ancak INDEX-MATCH'in sunduğu avantajlar bunlarla sınırlı değildir. Örneğin, büyük/küçük harf duyarlı aramalar yapmak için INDEX-MATCH'in temel sözdizimini geliştirmek mümkündür. Bunu başarmak için özellikle MATCH işlevinin içine bir EXACT işlevi ekleyeceğiz. Ayrıntılı sözdizimi aşağıda görülebilir:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: Arama sütununu ve almak istediğiniz hedef sütunu içeren ilgili tablo aralığı.
- lookup_value: Aramak istediğiniz değer (anahtar)
- lookup_column_range: Eşleştirmek istediğiniz anahtarları içeren sütunun aralığı
- match_type: 0 veya 1 olabilir. 0 tam eşleşmeyi, 1 yaklaşık eşleşmeyi belirtir. VLOOKUP'taki FALSE/TRUE bayrağına benzer
- desired_column_number: VLOOKUP'taki sütun indeks numarasıyla aynıdır. Almak istediğiniz veriyi içeren sütunun indeksidir.
Bu değiştirilmiş INDEX-MATCH kombinasyonunu kullanarak büyük/küçük harf duyarlı eşleştirme, harf içeren ve bu harflerin büyük/küçük olmasına göre ayrışan anahtarları eşleştirirken çok faydalı olabilir (ör. iki farklı anahtarınız var: "00567UUp" ve "00567UUP"). Bu tür durumlarla, örneğin yaygın olarak kullanılan Salesforce CRM'de Hesap ID'leri veya Satış Fırsatı ID'lerini eşleştirirken karşılaşılabilir. Şimdilik Salesforce veri kümeleri kullanmayacağız; yukarıdaki formülün pratikte nasıl çalıştığını göstermek için aşağıdaki videoda oyuncak tablolarımızın değiştirilmiş bir sürümünü kullanacağız.
Not: Yukarıdaki işlev kombinasyonunun süslü parantez içinde olduğuna dikkat edin. Bu, bunun bir dizi formülü olduğu ve Ctrl+Shift+Enter ile girilmesi gerektiği anlamına gelir.
Gördüğünüz gibi bu formülün sözdizimi yazması uzun olabilir, ancak sonuçlar beklediğiniz gibidir. Normal INDEX-MATCH gibi değerleri arar, ancak karakterlerin büyük/küçük harf durumunu da dikkate alır. Buradaki sihir, TRUE ve FALSE değerlerinden oluşan bir dizi döndüren EXACT işlevi sayesinde gerçekleşir. Bu dizide TRUE olan her yerde, aynı tam büyük/küçük harf durumuna sahip bir değer olduğunu söyleyebilirsiniz. Sonrasında MATCH işlevi, ilk TRUE oluşumunun konumunu alır.
Formülü yazmayı bitirdiğinizde Ctrl+Shift+Enter tuşlarına basmanızın kritik olduğunu lütfen unutmayın; aksi halde teşhisi zor olabilecek bir #YOK hatası alırsınız.
Gelişmiş INDEX-MATCH: Birden Fazla Özelliğe Dayalı Eşleştirme
Excel'de büyük/küçük harf duyarlı aramalar oldukça hoş, ancak daha da iyi bir özellik var; birden fazla kritere göre arama. Birden çok kriteri hesaba katmak, COUNTIFS veya SUMIFS gibi koşullu sayma ya da toplama işlevlerinin bir parçası olarak yaygın şekilde yapılır, ancak Excel'de arama işlevlerinin bir parçası olarak daha nadir görülür. Yine de belirli durumlarda avantaj sağlayabilir. Örneğin, bir İK Excel dosyanız var ve bir çalışanın maaşını ad VE soyadına göre aramak istiyorsunuz diyelim. Yalnızca iki sütundan birini dikkate alırsanız, aynı ada veya soyada sahip birden fazla kişi olabileceğinden yanlış çalışanın maaşını getirme olasılığınız vardır. Ancak hem ad hem de soyada göre değeri getirirseniz doğru kişiye ulaşma ihtimaliniz çok daha yüksektir.
Öyleyse şimdi, birden fazla özelliği dikkate alan INDEX-MATCH yapısına bakalım:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
Formülün büyük ölçüde normal INDEX-MATCH ile aynı bileşenlere sahip olduğunu, ancak artık ilgili sayıda arama aralığıyla birlikte $n$ adet arama değerini dikkate alabildiğini görebilirsiniz. Bu bileşenlere dair bir hatırlatmaya ihtiyacınız varsa, temel INDEX-MATCH sözdizimine yukarıdan bakabilirsiniz. MATCH işlevinin içinde "1" olmasının nedeni, bu formülün her bir istenen ölçüte karşılık gelen eşleşmeleri gösteren 1 ve 0'lardan oluşan diziler oluşturmasıdır. Ardından, bulunan ilk 1'in konumunu almak için MATCH işlevini kullanır.
Büyük/küçük harf duyarlı formülde olduğu gibi bunda da süslü parantezler vardır; bu da yazmayı bitirdiğinizde bunun bir dizi formülü olduğu için Ctrl+Shift+Enter tuşlarına basmanız gerektiği anlamına gelir.
Aşağıdaki videoda, belirli bir ırka ait ve belirli bir azami warp hızına sahip uzay gemilerini seçmeye çalışarak bu formülün nasıl çalıştığına dair bir örneğe bakalım:
Voilá! Artık birden fazla anahtara dayalı olarak argümanları eşleştirmek için gerekli araçlara sahipsiniz. Bu küçük oyuncak örnekte yalnızca iki ölçüt, yani anahtar ve azami warp hızını kullandığımı unutmayın; teoride isterseniz ölçüt eklemeye devam edebilirsiniz. İK örneğimize dönecek olursak, yanlış çalışanın maaşını getirme ihtimalini en aza indirmek için ad ve soyadın yanı sıra iş unvanına göre de eşleştirme yapabilirsiniz.
Sonuç
Tebrikler! Artık INDEX-MATCH ve bununla basit bir dikey aramanın ötesinde neler başarabileceğiniz konusunda bilgi sahibisiniz. Bir noktada karmaşık Excel raporlarıyla ilgilenmekle görevlendirilirseniz, bu işlev kombinasyonu muhtemelen en iyi yardımcınız olacaktır. Birçoğu gibi ben de okulda VLOOKUP öğrendim; ancak Salesforce hesap ID'lerinin büyük/küçük harf duyarlı aramaları gibi görevler için Excel'i ciddi şekilde kullanmaya başladığımda, formülü yazmanın zahmetli ve hata yapmaya açık olmasına rağmen uzun vadede INDEX-MATCH'in çok daha iyi bir seçenek olduğunu fark ettim. Excel çalışmalarınızda bir sonraki dikey arama işinizde denemenizi öneririm. Kim bilir, belki benim gibi siz de seversiniz.
Daha da fazla öğrenmek isterseniz, DataCamp'in Spreadsheets ile Veri Analizi Kursuna göz atabilirsiniz.
