Excel'de INDEX & MATCH İşlevleri Birleşimi (10 Kolay Örnek)

Excel'in birçok işlevi vardır - yaklaşık 450'den fazla işlevi.

Ve bunların çoğu tek kelimeyle harika. Birkaç formülle yapabileceğiniz iş miktarı beni hala şaşırtıyor (Excel'i 10+ yıl kullandıktan sonra bile).

Ve tüm bu harika işlevler arasında, INDEX MATCH işlevleri kombinasyonu öne çıkıyor.

INDEX MATCH combo'nun büyük bir hayranıyım ve bunu birçok kez açıkça belirttim.

Hatta biraz tartışmaya yol açan Index Match Vs DÜŞEYARA hakkında bir makale bile yazdım (havai fişek için yorumlar bölümüne bakabilirsiniz).

Ve bugün, bu güçlü formül kombinasyonunu kullanabileceğiniz ve işi bitirebileceğiniz bazı basit ve gelişmiş senaryoları göstermek için yalnızca Dizin Eşleştirmeye odaklanan bu makaleyi yazıyorum.

Not: Excel'de DÜŞEYARA ve YATAYARA gibi başka arama formülleri de vardır ve bunlar harikadır. Birçok kişi DÜŞEYARA kullanımını daha kolay buluyor (ve bu da doğru). INDEX MATCH'ın birçok durumda daha iyi bir seçenek olduğuna inanıyorum. Ama insanlar zor bulduğu için daha az kullanılıyor. Bu yüzden bu öğreticiyi kullanarak basitleştirmeye çalışıyorum.

Şimdi size INDEX MATCH kombinasyonunun analistlerin ve veri bilimcilerin dünyasını nasıl değiştirdiğini göstermeden önce, önce sizi bağımsız parçalarla - INDEX ve MATCH işlevleriyle tanıştırayım.

INDEX Fonksiyonu: Koordinatlara Göre Değeri Bulur

Index işlevinin nasıl çalıştığını anlamanın en kolay yolu, onu bir GPS uydusu olarak düşünmektir.

Uyduya enlem ve boylam koordinatlarını söyler söylemez, nereye gideceğini tam olarak bilecek ve o konumu bulacaktır.

Bu nedenle, akıllara durgunluk veren sayıda son-uzun kombinasyona sahip olmasına rağmen, uydu tam olarak nereye bakacağını bilirdi.

Hızlı bir şekilde iş yerimi aradım ve bulduğum şey bu.

Neyse coğrafya yeter.

Tıpkı bir uydunun enlem ve boylam koordinatlarına ihtiyaç duyması gibi, Excel'deki INDEX işlevi, hangi hücreden bahsettiğinizi bilmek için satır ve sütun numarasına ihtiyaç duyar.

Ve bu, kısaca Excel INDEX işlevidir.

O halde sizin için basit kelimelerle tanımlayayım.

INDEX işlevi, verilen aralıktaki bir hücreyi bulmak ve içindeki değeri döndürmek için satır numarasını ve sütun numarasını kullanır.

Tek başına, INDEX, hiçbir yardımcı programı olmayan çok basit bir işlevdir. Sonuçta, çoğu durumda satır ve sütun numaralarını bilmiyorsunuzdur.

Fakat…

Satır numarasını ve sütun numarasını bulabilen diğer işlevlerle (ipucu: KAÇINCI) kullanabilmeniz, INDEX'i son derece güçlü bir Excel işlevi yapar.

INDEX işlevinin sözdizimi aşağıdadır:

=INDEX (dizi, satır_sayısı, [sütun_sayısı]) =INDEX (dizi, satır_sayısı, [sütun_sayısı], [alan_sayısı])

• dizi - a hücre aralığı veya bir dizi sabiti.
• satır_sayısı - değerin getirileceği satır numarası.
• [sütun_sayısı] - değerin getirileceği sütun numarası. Bu isteğe bağlı bir argüman olmasına rağmen, satır_sayısı sağlanmazsa verilmesi gerekir.
• [alan_sayısı] - (İsteğe bağlı) Dizi bağımsız değişkeni birden çok aralıktan oluşuyorsa, tüm aralıklardan referansı seçmek için bu sayı kullanılır.

INDEX işlevinin 2 sözdizimi vardır (sadece FYI).

İlki çoğu durumda kullanılır. İkincisi, bu eğitimde daha sonra örneklerden birinde ele alacağımız, yalnızca gelişmiş durumlarda (üç yönlü arama yapmak gibi) kullanılır.

Ancak bu işlevde yeniyseniz, ilk sözdizimini hatırlamanız yeterlidir.

Aşağıda INDEX işlevinin nasıl kullanılacağını açıklayan bir video bulunmaktadır.

MATCH Fonksiyonu: Bir Arama Değerine göre Pozisyonu Bulur

Bir önceki boylam ve enlem örneğime geri dönersek, KAÇINCI bu konumları bulabilen işlevdir (Excel elektronik tablo dünyasında).

Basit bir dilde, Excel MATCH işlevi, bir aralıktaki bir hücrenin konumunu bulabilir.

Ve hangi temelde bir hücrenin konumunu bulur?

Arama değerine göre.

Örneğin, aşağıda gösterildiği gibi bir listeniz varsa ve içindeki 'İşaretle' isminin konumunu bulmak istiyorsanız, KAÇINCI işlevini kullanabilirsiniz.

İşlev, içinde Mark adı bulunan hücrenin konumu olduğu için 3 değerini döndürür.

KAÇINCI işlevi, belirtilen aralıktaki (bu örnekte A1:A9 olan) arama değerini ('İşaret' olan) yukarıdan aşağıya aramaya başlar. Adı bulur bulmaz, o belirli aralıktaki konumu döndürür.

Excel'deki KAÇINCI işlevinin sözdizimi aşağıdadır.

=KAÇINCI(arama_değeri, arama_dizisi, [eşleşme_türü])

• aranan_değer - Aranan_dizide bir eşleşme aradığınız değer.
• arama_dizisi - Aranan_değeri aradığınız hücre aralığı.
• [eşleşme türü] - (İsteğe bağlı) Bu, excel'in eşleşen bir değeri nasıl araması gerektiğini belirtir. -1, 0 veya 1 olmak üzere üç değer alabilir.

MATCH İşlevinde Eşleme Türü Argümanını Anlama

KAÇINCI işlevi hakkında bilmeniz gereken bir şey daha var ve bu, verilerin nasıl geçtiği ve hücre konumunu nasıl bulduğu ile ilgili.

KAÇINCI işlevinin üçüncü argümanı 0, 1 veya -1 olabilir.

Aşağıda, bu argümanların nasıl çalıştığına dair bir açıklama bulunmaktadır:

• 0 - bu, değerin tam eşleşmesini arayacaktır. Tam bir eşleşme bulunursa, KAÇINCI işlevi hücre konumunu döndürür. Aksi takdirde, bir hata döndürür.
• 1 - bu, arama değerinden küçük veya ona eşit olan en büyük değeri bulur. Bunun çalışması için veri aralığınızın artan düzende sıralanması gerekir.
• -1 - bu, arama değerinden büyük veya ona eşit olan en küçük değeri bulur. Bunun çalışması için veri aralığınızın azalan düzende sıralanması gerekir.

Aşağıda KAÇINCI işlevinin nasıl kullanılacağını açıklayan bir video bulunmaktadır (eşleme türü bağımsız değişkeniyle birlikte)

Özetlemek ve basit kelimelerle ifade etmek için:

• INDEX, hücre konumuna (satır ve sütun numarası) ihtiyaç duyar ve hücre değerini verir.
• KAÇINCI, bir arama değeri kullanarak konumu bulur.

Bir Güç Merkezi Oluşturmak İçin Bunları Birleştirelim (INDEX + MAÇ)

Artık INDEX ve MATCH işlevlerinin ayrı ayrı nasıl çalıştığına dair temel bir anlayışa sahip olduğunuza göre, bu ikisini birleştirelim ve yapabileceği tüm harika şeyleri öğrenelim.

Bunu daha iyi anlamak için INDEX MATCH kombinasyonunu kullanan birkaç örneğim var.

Basit bir örnekle başlayacağım ve ardından size bazı gelişmiş kullanım durumları da göstereceğim.

Örnek dosyayı indirmek için buraya tıklayın

Örnek 1: INDEX MATCH Combo Kullanan Basit Bir Arama

INDEX/MATCH ile basit bir arama yapalım.

Aşağıda on öğrenci için not aldığım bir tablo var.

Bu tablodan Jim için işaretleri bulmak istiyorum.

Bunu kolayca yapabilen formül aşağıdadır:

=INDEX($A$2:$B$11,MATCH("Jim",$A$2:$A$11,0),2)

Şimdi, bunun bir DÜŞEYARA işlevi kullanılarak kolayca yapılabileceğini düşünüyorsanız, haklısınız! Bu, INDEX MATCH görkeminin en iyi kullanımı değildir. INDEX MATCH hayranı olmama rağmen, DÜŞEYARA'dan biraz daha zor. Tüm yapmak istediğiniz sağdaki bir sütundan veri almaksa DÜŞEYARA kullanmanızı öneririm.

DÜŞEYARA ile de kolayca yapılabilen bu örneği göstermemin nedeni, size INDEX MATCH'ın basit bir ortamda nasıl çalıştığını göstermektir.

Şimdi INDEX MATCH'ın bir faydasını göstereyim.

Aynı verilere sahip olduğunuzu, ancak bunları sütunlarda tutmak yerine satırlarda (aşağıda gösterildiği gibi) aldığınızı varsayalım.

Biliyor musun, Jim'in notlarını almak için hala INDEX MATCH combo'yu kullanabilirsin.

Size sonucu verecek formül aşağıdadır:

=INDEX($B$1:$K$2,2,MATCH(“Jim”,$B$1:$K$1,0))

Bu formülün yatay veriler için de çalışması için aralığı değiştirmeniz ve satır/sütun kısımlarını değiştirmeniz gerektiğini unutmayın.

Bu DÜŞEYARA ile yapılamaz, ancak yine de YATAYARA ile bunu kolayca yapabilirsiniz.

INDEX MATCH kombinasyonu, yatay ve dikey verileri kolayca işleyebilir.

Örnek dosyayı indirmek için buraya tıklayın

Örnek 2: Sola Arama

Düşündüğünden daha yaygın.

Çoğu zaman, arama değerine sahip sütunun solundaki bir sütundan veri getirmeniz gerekebilir.

Aşağıda gösterildiği gibi bir şey:

Michael'ın satışlarını öğrenmek için solda bir arama yapmanız gerekecek.

DÜŞEYARA'yı düşünüyorsanız, tam orada durmama izin verin.

DÜŞEYARA, soldaki değerleri aramak ve getirmek için yapılmamıştır.

Hala DÜŞEYARA kullanarak yapabilir misin?

Evet yapabilirsin!

Ancak bu uzun ve çirkin bir formüle dönüşebilir.

Bu nedenle, bir arama yapmak ve soldaki sütunlardan veri almak istiyorsanız, INDEX MATCH combo'yu kullanmanız daha iyi olur.

Michael'ın satış numarasını alacak formül aşağıdadır:

=INDEX($A$2:$C$11,MATCH("Michael",C2:C11,0),2)

INDEX MATCH için burada başka bir nokta. DÜŞEYARA, verileri yalnızca arama değerine sahip sütunun sağındaki sütunlardan getirebilir.

Örnek 3: İki Yönlü Arama

Şimdiye kadar, arama değeri olan sütunun bitişiğindeki sütundan veri almak istediğimiz örnekleri gördük.

Ancak gerçek hayatta, veriler genellikle birden çok sütuna yayılır.

INDEX MATCH, iki yönlü bir aramayı kolayca halledebilir.

Aşağıda, öğrencinin üç farklı konudaki notlarının bir veri seti bulunmaktadır.

Bir öğrencinin üç konudaki notlarını hızlı bir şekilde almak istiyorsanız, bunu INDEX MATCH ile yapabilirsiniz.

Aşağıdaki formül size üç konunun tümü için Jim için işaretleri verecektir (bir hücreye kopyalayıp yapıştırın ve diğer hücreleri doldurmak için sürükleyin veya diğer hücrelere kopyalayıp yapıştırın).

=INDEX($B$2:$D$11,KAÇINCI($F$3,$A$2:$A$11,0),KAÇINCI(G$2,$B$1:$D$1,0))

Bu formülü de hızlıca açıklayayım.

INDEX formülü, aralık olarak B2:D11'i kullanır.

İlk MAÇ, adı kullanır (F3 hücresindeki Jim) ve adlar sütunundaki konumunu alır (A2:A11). Bu, verilerin alınması gereken satır numarası olur.

İkinci KAÇINCI formülü, belirli konu adının B1:D1'deki konumunu almak için konu adını (G2 hücresindeki) kullanır. Örneğin Matematik 1, Fizik 2 ve Kimya 3'tür.

Bu KAÇINCI pozisyonları INDEX işlevine beslendiğinden, öğrenci adı ve konu adına göre puanı döndürür.

Bu formül dinamiktir, yani öğrenci adını veya konu adlarını değiştirirseniz çalışmaya devam eder ve doğru verileri getirir.

INDEX/MATCH kullanmanın harika bir yanı, konuların adlarını değiştirseniz bile size doğru sonucu vermeye devam etmesidir.

Örnek 4: Birden Çok Sütundan/Kriterden Aranan Değer

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve 'Mark Long' için işaretleri almak istediğinizi varsayalım.

Veriler iki sütunda olduğu için Mark'ı arayıp verileri alamıyorum.

Bu şekilde yaparsam, Mark Long için değil Mark Frost için işaret verilerini alacağım (çünkü MATCH işlevi bana karşılaştığı MARK için sonucu verecektir).

Bunu yapmanın bir yolu, bir yardımcı sütun oluşturmak ve adları birleştirmektir. Yardımcı sütuna sahip olduğunuzda, DÜŞEYARA'yı kullanabilir ve işaret verilerini alabilirsiniz.

Bunu nasıl yapacağınızı öğrenmek istiyorsanız, DÜŞEYARA'yı birden çok kriterle kullanma hakkındaki bu öğreticiyi okuyun.

Ancak INDEX/MATCH combo ile bir yardımcı sütuna ihtiyacınız yoktur. Formülün kendisinde birden çok ölçütü işleyen bir formül oluşturabilirsiniz.

Aşağıdaki formül sonucu verecektir.

=INDEX($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))

Bu formülün ne işe yaradığını hızlıca açıklayayım.

Formülün KAÇINCI kısmı, tüm arama dizisinin yanı sıra arama değerini (İşaret ve Uzun) birleştirir. Arama dizisi olarak $A$2:A11&”|”&$B$2:$B$11 kullanıldığında, aslında arama değerini ad ve soyadın birleştirilmiş dizesine (dikey çizgi simgesiyle ayrılmış) karşı kontrol eder.

Bu, herhangi bir yardımcı sütun kullanmadan doğru sonucu almanızı sağlar.

Bu tür bir aramayı (birden çok sütun/kriterin olduğu yerde) DÜŞEYARA ile de yapabilirsiniz, ancak bir yardımcı sütun kullanmanız gerekir. INDEX MATCH combo, herhangi bir yardımcı sütun olmadan bunu yapmayı biraz kolaylaştırır.

Örnek 5: Tüm Satırdan/Sütunundan Değer Alma

Yukarıdaki örneklerde, belirli bir hücreden değer almak için INDEX işlevini kullandık. Satır ve sütun numarasını sağlarsınız ve o hücredeki değeri döndürür.

Ama daha fazlasını yapabilirsiniz.

Tüm bir satır veya sütundan değerleri almak için INDEX işlevini de kullanabilirsiniz.

Ve bu nasıl faydalı olabilir diye soruyorsunuz!

Her üç konuda da Jim'in toplam puanını bilmek istediğinizi varsayalım.

Önce Jim'in tüm işaretlerini almak için INDEX işlevini kullanabilir ve ardından toplamı elde etmek için SUM işlevini kullanabilirsiniz.

Bunun nasıl yapılacağını görelim.

Aşağıda üç konuda tüm öğrencilerin puanları var.

Aşağıdaki formül bana üç konuda da Jim'in toplam puanını verecektir.

=TOPLA(INDEX($B$2:$D$11,KAÇINCI($F$4,$A$2:$A$11,0),0))

Bu formülün nasıl çalıştığını açıklayayım.

Buradaki hile, sütun numarası olarak 0 kullanmaktır.

INDEX işlevinde sütun numarası olarak 0 kullandığınızda, tüm satır değerlerini döndürür. Benzer şekilde, satır numarası olarak 0 kullanırsanız, sütundaki tüm değerleri döndürür.

Yani formülün aşağıdaki kısmı bir dizi değer döndürür - {97, 70, 73}

İNDEKS($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0)

Yukarıdaki formülü Excel'deki bir hücreye girip enter'a basarsanız, bir #DEĞER! hata. Bunun nedeni, tek bir değer değil, bir değer dizisi döndürmesidir.

Ama merak etmeyin, değerler dizisi hala orada. Formülü seçip F9 tuşuna basarak bunu kontrol edebilirsiniz. Size bu durumda üç değerli bir dizi olan formülün sonucunu gösterecektir - {97, 70, 73}

Şimdi, bu INDEX formülünü SUM işlevine sararsanız, size Jim tarafından atılan tüm puanların toplamını verecektir.

Aynı şeyi Jim'in en yüksek, en düşük ve ortalama notlarını almak için de kullanabilirsiniz.

Bunu bir öğrenci için yaptığımız gibi bir ders için de yapabilirsiniz. Örneğin bir konuda ortalama puan almak istiyorsanız INDEX formülünde satır numarasını 0 olarak tutabilirsiniz ve o konunun tüm sütun değerlerini size verecektir.

Örnek dosyayı indirmek için buraya tıklayın

Örnek 6: Öğrencinin Notunu Bulun (Yaklaşık Eşleştirme Tekniği)

Şimdiye kadar, arama değerinin tam eşleşmesini elde etmek için KAÇINCI formülünü kullandık.

Ancak bunu yaklaşık bir eşleşme yapmak için de kullanabilirsiniz.

Şimdi, yaklaşık eşleşme nedir?

Açıklamama izin ver.

Adlar veya kimlikler gibi şeyler ararken, tam bir eşleşme arıyorsunuz. Ancak bazen, arama değerlerinizin bulunduğu aralığı bilmeniz gerekir. Bu genellikle sayılarla olur.

Örneğin, bir sınıf öğretmeni olarak, bir dersteki her öğrencinin notunun ne olduğunu bilmek isteyebilirsiniz ve nota puana göre karar verilir.

Aşağıda, tüm öğrenciler için notu istediğim ve notun sağdaki tabloya göre kararlaştırıldığı bir örnek var.

Yani bir öğrenci 33'ten az alırsa, notu F'dir ve 50'den az ama 33'ten fazla alırsa E'dir, vb.

Bunu yapacak formül aşağıdadır.

=INDEX($F$3:$F$8,MATCH(B2,$E$3:$E$8,1),1)

Bu formülün nasıl çalıştığını açıklayayım.

KAÇINCI işlevinde, [eşleşme_türü] argümanı olarak 1 kullandık. Bu bağımsız değişken, arama değerinden küçük veya ona eşit olan en büyük değeri döndürür.

Bu, KAÇINCI formülünün işaretler aralığından geçtiği ve arama işaretleri değerine eşit veya bundan daha düşük bir işaret aralığı bulduğu anda burada duracağı ve konumuna geri döneceği anlamına gelir.

Bu nedenle, arama işareti değeri 20 ise, KAÇINCI işlevi 1 döndürür ve 85 ise 5 döndürür.

Ve INDEX işlevi, notu almak için bu konum değerini kullanır.

ÖNEMLİ: Bunun çalışması için verilerinizin artan düzende sıralanması gerekir. Değilse, yanlış sonuçlar alabilirsiniz.

Yukarıdakilerin aşağıdaki DÜŞEYARA formülü kullanılarak da yapılabileceğini unutmayın:

=DÜŞEYARA(B2,$E$3:$F$8,2,DOĞRU)

Ancak MATCH işlevi, yaklaşık eşleşme söz konusu olduğunda bir adım daha ileri gidebilir.

Ayrıca azalan bir veriye sahip olabilir ve sonucu bulmak için INDEX MATCH combo'yu kullanabilirsiniz. Örneğin, not tablosunun sırasını değiştirsem (aşağıda gösterildiği gibi) yine de öğrencilerin notlarını bulabilirim.

Bunu yapmak için tek yapmam gereken [match_type] argümanını -1 olarak değiştirmek.

Kullandığım formül aşağıdadır:

=INDEX($F$3:$F$8,MATCH(B2,$E$3:$E$8,-1),1)

DÜŞEYARA ayrıca yaklaşık bir eşleşme yapabilir, ancak yalnızca veriler artan düzende sıralandığında (ancak veriler azalan düzende sıralandığında çalışmaz).

Örnek 7: Büyük/Küçük Harfe Duyarlı Aramalar

Şimdiye kadar yaptığımız tüm aramalar büyük/küçük harfe duyarlı değildi.

Bu, arama değerinin Jim mi yoksa JIM mi yoksa Jim mi olduğunun önemli olmadığı anlamına gelir. Aynı sonucu alacaksınız.

Ama ya aramanın büyük/küçük harfe duyarlı olmasını istiyorsanız.

Bu genellikle, büyük veri kümeleriniz ve tekrarlama veya farklı adlar/kimlikler olasılığınız olduğunda geçerlidir (tek fark bu olmakla birlikte)

Örneğin, Jim adında iki öğrencinin bulunduğu aşağıdaki öğrenci veri setim olduğunu varsayalım (tek fark, birinin Jim, diğerinin de jim olarak girilmiş olmasıdır).

Aynı ada sahip iki öğrenci olduğunu unutmayın - Jim (hücre A2 ve A5).

Normal bir arama işe yaramayacağından, büyük/küçük harfe duyarlı bir arama yapmanız gerekir.

Size doğru sonucu verecek formül aşağıdadır. Bu bir dizi formülü olduğu için Control + Shift + Enter kullanmanız gerekir.

=INDEKS($B$2:$B$11,KAÇINCI(DOĞRU,TAM(D3,A2:A11),0),1)

Bu formülün nasıl çalıştığını açıklayayım.

EXACT işlevi, arama değerinin (bu durumda 'jim' olan) tam eşleşmesini kontrol eder. Tüm adları gözden geçirir ve eşleşme değilse YANLIŞ, eşleşme ise DOĞRU döndürür.

Yani bu örnekte TAM işlevinin çıktısı - {YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ}

Yalnızca bir DOĞRU olduğuna dikkat edin, o da EXACT işlevinin mükemmel bir eşleşme bulduğu zamandır.

MATCH işlevi daha sonra bu örnekte 4 olan EXACT işlevi tarafından döndürülen dizideki TRUE konumunu bulur.

Pozisyona sahip olduğumuzda, INDEX işlevi onu işaretleri bulmak için kullanır.

Örnek 8: En Yakın Eşleşmeyi Bulun

Şimdi biraz ilerleyelim.

Gerekli deneyime en yakın iş deneyimine sahip kişiyi bulmak istediğiniz bir veri kümeniz olduğunu varsayalım (D2 hücresinde belirtilmiştir).

Bunu yapmak için arama formülleri yapılmasa da, bunu yapmak için diğer işlevlerle (MIN ve ABS gibi) birleştirebilirsiniz.

Aşağıda, gerekli olana en yakın deneyime sahip kişiyi bulacak ve kişinin adını döndürecek formül bulunmaktadır. Deneyimin en yakın olması gerektiğini unutmayın (daha az veya daha fazla olabilir).

=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Bu bir dizi formülü olduğu için Control + Shift + Enter kullanmanız gerekir.

Bu formüldeki hile, gerekli ve gerçek değerlerdeki minimum deneyim farkını bulmak için arama değerini ve arama dizisini değiştirmektir.

Formülü açıklamadan önce manuel olarak nasıl yapacağınızı anlayalım.

B sütunundaki her hücreyi gözden geçirecek ve gerekli olan ile bir kişinin sahip olduğu deneyim arasındaki farkı bulacaksınız. Tüm farklılıklara sahip olduğunuzda, minimum olanı bulacaksınız ve o kişinin adını alacaksınız.

Bu formülle tam olarak bunu yapıyoruz.

Açıklamama izin ver.

KAÇINCI formülündeki arama değeri MIN(ABS(D2-B2:B15)).

Bu bölüm size verilen deneyim (2,5 yıl) ile diğer tüm deneyimler arasındaki minimum farkı verir. Bu örnekte, 0,3 döndürür

En yakını aradığımdan emin olmak için ABS kullandığımı unutmayın (bu, verilen deneyimden daha fazla veya daha az olabilir).

Şimdi, bu minimum değer bizim arama değerimiz olur.

KAÇINCI işlevindeki arama dizisi ABS'dir(D2-$B$2:$B$15).

Bu bize, 2.5'in (gerekli deneyim) çıkarıldığı bir sayı dizisini verir.

Şimdi bir arama değerimiz (0.3) ve bir arama dizimiz var ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

KAÇINCI işlevi, aynı zamanda en yakın deneyime sahip kişinin adının konumu olan bu dizide 0.3'ün konumunu bulur.

Bu pozisyon numarası daha sonra kişinin adını döndürmek için INDEX işlevi tarafından kullanılır.

İlgili Okuma: Excel'de En Yakın Eşleşmeyi Bulun (arama formüllerini kullanan örnekler)

Örnek dosyayı indirmek için buraya tıklayın

Örnek 9: Joker Karakterlerle INDEX MATCH kullanın

Kısmi eşleşme olduğunda bir değer aramak istiyorsanız, joker karakterler kullanmanız gerekir.

Örneğin, aşağıda şirket adının ve piyasa değerlerinin bir veri seti verilmiştir ve piyasa değerini almak istiyorsunuz. sağdaki üç şirket için veriler.

Bunlar tam eşleşme olmadığından, bu durumda normal bir arama yapamazsınız.

Ancak yine de bir joker karakter olan yıldız işareti (*) kullanarak doğru verileri alabilirsiniz.

Aşağıda ana sütundan şirket isimleri eşleştirilerek ve bunun için piyasa değeri rakamı getirilerek size veri verecek formül bulunmaktadır.

=INDEX($B$2:$B$10,MATCH(D2&”*”,$A$2:$A$10,0),1)

Bu formülün nasıl çalıştığını açıklayayım.

Arama değerlerinin tam eşleşmesi olmadığı için kullandım D2&”*” KAÇINCI işlevinde arama değeri olarak.

Yıldız işareti, herhangi bir sayıda karakteri temsil eden bir joker karakterdir. Bu, formüldeki Apple*'ın Apple kelimesiyle başlayan ve ondan sonra herhangi bir sayıda karakter alabilen herhangi bir metin dizesi anlamına geleceği anlamına gelir.

Öyleyse ne zaman Elma* arama değeri olarak kullanılır ve KAÇINCI formülü onu A sütununda arar, Apple kelimesiyle başladığı için 'Apple Inc.' konumunu döndürür.

Arama değerinin arada olduğu metin dizelerini bulmak için joker karakterler de kullanabilirsiniz. Örneğin, arama değeri olarak *Apple* kullanırsanız, herhangi bir yerinde elma kelimesi geçen herhangi bir dizeyi bulur.

Not: Bu teknik, yalnızca bir eşleştirme örneğiniz olduğunda iyi çalışır. Ancak birden fazla eşleştirme örneğiniz varsa (örneğin Apple Inc ve Apple Corporation, MATCH işlevi yalnızca ilk eşleşen örneğin konumunu döndürür.

Örnek 10: Üç Yönlü Arama

Bu, INDEX MATCH'ın gelişmiş bir kullanımıdır, ancak size bu kombinasyonun gücünü göstermek için yine de ele alacağım.

INDEX işlevinin iki sözdizimi olduğunu söylediğimi unutmayın:

=INDEX (dizi, satır_sayısı, [sütun_sayısı]) =INDEX (dizi, satır_sayısı, [sütun_sayısı], [alan_sayısı])

Şimdiye kadar tüm örneklerimizde sadece ilkini kullandık.

Ancak üç yönlü bir arama için ikinci sözdizimini kullanmanız gerekir.

Önce üç yönlü bakışın ne anlama geldiğini açıklayayım.

İki yönlü bir aramada, öğrencinin adı ve konu adı elimizde olduğunda işaretleri almak için INDEX MATCH formülünü kullanırız. Örneğin, Jim'in Matematikteki notlarını almak iki yönlü bir aramadır.

Üç yönlü bir bakış, ona başka bir boyut katacaktır. Örneğin, aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve Jim'in Ara sınavdaki Matematik dersindeki puanını öğrenmek istediğinizi varsayalım, o zaman bu üç yönlü bir arama olacaktır.

Sonucu verecek formül aşağıdadır.

=INDEX(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),MATCH($F$5,$A$3:$A$7.0),MATCH(G$4 ,$B$2:$D$2,0),(IF(G$3="Birim Testi",1,IF(G$3="Orta Dönem",2,3))))

Yukarıdaki formül üç şeyi kontrol etti - öğrencinin adı, konu ve sınav. Doğru değeri bulduktan sonra hücreye geri döndürür.

Formülü parçalara ayırarak bu formülün nasıl çalıştığını anlatayım.

• dizi - ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23): Bu durumda tek bir dizi kullanmak yerine parantez içinde üç dizi kullandım.
• satır_sayısı - KAÇINCI($F$5,$A$3:$A$7,0): KAÇINCI işlevi, öğrenci adı listesinde $F$5 hücresindeki öğrencinin adının konumunu bulmak için kullanılır.
• sütun_sayısı - MAÇ(G$4,$B$2:$D$2,0): KAÇINCI işlevi, özne adı listesinde $B$2 hücresindeki özne adının konumunu bulmak için kullanılır.
• [alan_sayısı] - IF(G$3=”Birim Testi”,1,IF(G$3=”Orta Dönem”,2,3)): Alan numarası değeri, değeri almak için üç diziden hangisinin kullanılacağını INDEX işlevine söyler. Muayene Birim Terim ise, EĞER işlevi 1 döndürür ve INDEX işlevi değeri almak için ilk diziyi kullanır. Sınav Ara Sınav ise, EĞER formülü 2, aksi takdirde 3 döndürür.

Bu, INDEX MATCH kullanımına ilişkin gelişmiş bir örnektir ve bunu kullanmanız gerektiğinde bir durum bulmanız pek olası değildir. Ancak Excel formüllerinin neler yapabileceğini bilmek yine de iyidir.

Örnek dosyayı indirmek için buraya tıklayın

INDEX/MATCH Neden DÜŞEYARA'dan Daha İyi?

Yoksa öyle mi?

Evet, öyle - çoğu durumda.

Bir süre sonra davamı sunacağım.

Ama bunu yapmadan önce şunu söylememe izin verin - DÜŞEYARA son derece kullanışlı bir işlev ve onu seviyorum. Excel'de pek çok şey yapabilir ve arada sırada kendim kullanırım. Bunu söyledikten sonra, daha iyi bir şey olamayacağı anlamına gelmez ve INDEX/MATCH (daha fazla esneklik ve işlevsellik ile) daha iyidir.

Bu nedenle, bazı temel aramalar yapmak istiyorsanız DÜŞEYARA kullanmanız daha iyi olur.

INDEX/MATCH, steroidlerde DÜŞEYARA'dır. Ve bir kez INDEX/MATCH öğrendikten sonra, onu kullanmayı her zaman tercih edebilirsiniz (özellikle sahip olduğu esneklik nedeniyle).

Çok fazla uzatmadan, INDEX/MATCH'ın DÜŞEYARA'dan daha iyi olmasının nedenlerini size hızlıca vereyim.

INDEX/MATCH, arama değerinin soluna (hem de sağına) bakabilir

Yukarıdaki örneklerden birinde anlattım.

Arama değerinin solunda bir değeriniz varsa DÜŞEYARA ile bunu yapamazsınız.

En azından sadece DÜŞEYARA ile değil.

Evet, DÜŞEYARA'yı diğer formüllerle birleştirip halledebilirsiniz, ancak işler karmaşık ve dağınık hale geliyor.

INDEX/MATCH ise her yerde (sol, sağ, yukarı veya aşağı) arama yapmak için yapılır.

INDEX/MATCH dikey ve yatay aralıklarla çalışabilir

Yine, DÜŞEYARA'ya tam saygıyla, bunu yapmak için yapılmamıştır.

Sonuçta, DÜŞEYARA'daki V dikey anlamına gelir.

DÜŞEYARA yalnızca dikey verilerden geçebilirken, INDEX/MATCH verilerden dikey ve yatay olarak geçebilir.

Tabii ki, yatay aramayı halletmek için YATAYARA işlevi var, ama o zaman DÜŞEYARA değil… değil mi?

INDEX MATCH combo'nun hem dikey hem de yatay verilerle çalışacak kadar esnek olması hoşuma gidiyor.

DÜŞEYARA azalan verilerle çalışamaz

Yaklaşık eşleşmeye gelince, DÜŞEYARA ve INDEX/MATCH aynı seviyede.

Ancak INDEX MATCH, azalan sırada olan verileri de işleyebildiği için konuyu ele alır.

Bunu, bu eğitimdeki öğrencilerin notlarını not tablosuna göre bulmamız gereken örneklerden birinde gösteriyorum. Tablo azalan düzende sıralanırsa DÜŞEYARA çalışmaz (ancak INDEX MATCH çalışır).

INDEX/MATCH biraz daha hızlı olabilir

dürüst olacağım. Bu testi kendim yapmadım.

Bir Excel ustasının bilgeliğine güveniyorum - Charley Kyd.

DÜŞEYARA ve INDEX/MATCH'deki hız farkı, küçük veri kümeleriniz olduğunda pek fark edilmez. Ancak binlerce satırınız ve birçok sütununuz varsa, bu belirleyici bir faktör olabilir.

Charley Kyd makalesinde şunları söylüyor:

“En kötüsü, INDEX-MATCH yöntemi DÜŞEYARA kadar hızlıdır; en iyi ihtimalle, çok daha hızlıdır."

İNDEKS/KAÇINCI Gerçek Sütun Konumundan Bağımsızdır

Fizikte Jim'in puanını alırken aşağıda gösterildiği gibi bir veri kümeniz varsa, bunu DÜŞEYARA'yı kullanarak yapabilirsiniz.

Bunun için DÜŞEYARA'da sütun numarasını 3 olarak belirleyebilirsiniz.

Her şey yolunda.

Ama ya Matematik sütununu silersem.

Bu durumda DÜŞEYARA formülü bozulur.

Niye ya? - Üçüncü sütunu kullanmak sabit kodlandığı için ve aradaki bir sütunu sildiğimde üçüncü sütun ikinci sütun oluyor.

Bu durumda INDEX/MATCH kullanmak, KAÇINCI kullanarak sütun numarasını dinamik hale getirebileceğiniz için daha iyidir. Bu nedenle, bir sütun numarası yerine konu adını kontrol eder ve bunu sütun numarasını döndürmek için kullanır.

Elbette bunu DÜŞEYARA ile MATCH'ı birleştirerek yapabilirsiniz, ancak yine de birleştirirseniz, neden çok daha esnek olan INDEX ile yapmıyorsunuz.

INDEX/MATCH kullanırken, veri kümenize güvenle sütun ekleyebilir/silebilirsiniz.

Tüm bu etkenlere rağmen DÜŞEYARA'nın bu kadar popüler olmasının bir nedeni var.

Ve bu büyük bir sebep.

DÜŞEYARA kullanımı daha kolaydır

DÜŞEYARA yalnızca en fazla dört bağımsız değişken alır. Kafanı bu dördünün etrafına sarabilirsen, gitmeye hazırsın.

Ve temel arama durumlarının çoğu DÜŞEYARA tarafından da işlendiğinden, hızla en popüler Excel işlevi haline geldi.

Ben buna Excel'in Kralı fonksiyonları diyorum.

INDEX/MATCH ise kullanımı biraz daha zordur. Kullanmaya başladığınızda takılabilir, ancak yeni başlayanlar için DÜŞEYARA'yı açıklamak ve öğrenmek çok daha kolaydır.

Ve bu sıfır toplamlı bir oyun değil.

Bu nedenle, arama dünyasında yeniyseniz ve DÜŞEYARA'yı nasıl kullanacağınızı bilmiyorsanız, bunu öğrenseniz iyi olur.

Excel'de DÜŞEYARA kullanımına ilişkin ayrıntılı bir kılavuzum var (birçok örnekle birlikte)

Bu makaledeki amacım, iki harika işlevi birbirine düşürmek değil. Size INDEX MATCH kombinasyonunun gücünü ve yapabileceği tüm harika şeyleri göstermek istedim.

Umarım bu makaleyi faydalı bulmuşsunuzdur.

Yorumlar bölümünde düşüncelerinizi bana bildirin ve bu eğitimde herhangi bir hata bulursanız lütfen bana bildirin.