İçerik
Yeni başlayanlara DÜŞEYARA'nın temel işlevlerinin nasıl kullanılacağını daha önce açıkladık (İngilizce DÜŞEYARA, kısaltması “dikey arama işlevi” anlamına gelir). Ve deneyimli kullanıcılara birkaç daha karmaşık formül gösterildi.
Ve bu yazımızda dikey arama ile çalışmanın başka bir yöntemi hakkında bilgi vermeye çalışacağız.
Merak ediyor olabilirsiniz: “Bu neden gerekli?”. Ve bu, olası tüm arama yöntemlerini göstermek için gereklidir. Ayrıca, çok sayıda DÜŞEYARA kısıtlaması genellikle istenen sonucun elde edilmesini engeller. Bu bağlamda, INDEX( ) MATCH( ) çok daha işlevsel ve çeşitlidir ve ayrıca daha az kısıtlamaya sahiptir.
Temeller İNDEKS MAÇI
Bu kılavuzun amacı, bu özelliğin ne kadar iyi olduğunu göstermek olduğundan, Çalışma prensipleri ile ilgili temel bilgilere bakalım. Ve örnekler göstereceğiz ve bunun neden DÜŞEYARA'dan () daha iyi olduğunu düşüneceğiz.
INDEX İşlev Sözdizimi ve Kullanımı
Bu işlev, sütun veya satır numarasına göre belirtilen arama alanları arasından istenen değeri bulmaya yardımcı olur. Sözdizimi:
=INDEX(dizi, satır numarası, sütun numarası):
- dizi – aramanın yapılacağı alan;
- satır numarası - belirtilen dizide aranacak satırın numarası. Satır numarası bilinmiyorsa, sütun numarası belirtilmelidir;
- sütun numarası - belirtilen dizide bulunacak sütunun numarası. Değer bilinmiyorsa, bir satır numarası gereklidir.
Basit bir formül örneği:
=DİZİN(A1:S10,2,3)
İşlev, A1 ila C10 aralığında arama yapacaktır. Rakamlar, istenen değerin hangi satır (2) ve sütundan (3) gösterileceğini gösterir. Sonuç, C2 hücresi olacaktır.
Oldukça basit, değil mi? Ancak gerçek belgelerle çalıştığınızda, sütun numaraları veya hücreler hakkında bilgi sahibi olmanız pek olası değildir. MATCH() işlevi bunun içindir.
MATCH İşlev Sözdizimi ve Kullanımı
MATCH() işlevi, istenen değeri arar ve belirtilen arama alanında yaklaşık sayısını gösterir.
searchpos() sözdizimi şöyle görünür:
=MATCH(arayacak değer, aranacak dizi, eşleme türü)
- arama değeri – bulunacak sayı veya metin;
- aranan dizi – aramanın yapılacağı alan;
- eşleme türü - tam değerin mi yoksa ona en yakın değerlerin mi aranacağını belirtir:
- 1 (veya değer belirtilmemiş) – belirtilen değere eşit veya bundan küçük olan en büyük değeri döndürür;
- 0 – aranan değerle tam eşleşmeyi gösterir. INDEX() MATCH() kombinasyonunda hemen hemen her zaman tam bir eşleşmeye ihtiyacınız olacak, bu yüzden 0 yazıyoruz;
- -1 – Formülde belirtilen değerden büyük veya eşit olan en küçük değeri gösterir. Sıralama azalan düzende gerçekleştirilir.
Örneğin, B1:B3 aralığında New York, Paris, Londra kayıtlıdır. Londra listede üçüncü olduğu için aşağıdaki formül 3 sayısını gösterecektir:
=POZLAMA(Londra,B1:B3,0)
INDEX MATCH işleviyle nasıl çalışılır
Muhtemelen bu işlevlerin ortak çalışmasının inşa edildiği prensibi anlamaya başlamışsınızdır. Kısacası, o zaman INDEX(), belirtilen satırlar ve sütunlar arasında istenen değeri arar. Ve MATCH() şu değerlerin sayılarını gösterir:
=INDEX(değerin döndürüldüğü sütun, KAÇINCI(arayacak değer, aranacak sütun, 0))
Hala nasıl çalıştığını anlamakta zorlanıyor musunuz? Belki bir örnek daha iyi açıklar. Diyelim ki dünya başkentleri ve nüfuslarının bir listesi var:
Belirli bir başkentin, örneğin Japonya'nın başkentinin nüfusunun büyüklüğünü bulmak için aşağıdaki formülü kullanıyoruz:
=INDEX(C2:C10, MAÇ(Japonya, A2:A10,0))
Açıklama:
- KAÇINCI() işlevi, A2:A10 dizisinde “Japonya” değerini arar ve 3 sayısını döndürür, çünkü Japonya listedeki üçüncü değerdir.
- Bu rakam gidersatır numarasıINDEX() formülünde ” ve işleve bu satırdan bir değer yazdırmasını söyler.
Böylece yukarıdaki formül standart formül olur İNDEKS(C2:C10,3). Formül, C2 ila C10 arasındaki hücrelerden arama yapar ve geri sayım ikinci satırdan başladığı için bu aralıktaki üçüncü hücreden, yani C4'ten veri döndürür.
Formülde şehrin adını yazmak istemiyor musunuz? Ardından herhangi bir hücreye yazın, F1 deyin ve MATCH() formülünde referans olarak kullanın. Ve sonunda dinamik bir arama formülü elde edersiniz:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Önemli! satır sayısı dizi INDEX(), içindeki satır sayısıyla aynı olmalıdır. dikkate alınan dizi MATCH() içinde, aksi takdirde yanlış sonuç alırsınız.
Bir dakika, neden sadece DÜŞEYARA() formülünü kullanmıyorsunuz?
=DÜŞEYARA(F1, A2:C10, 3, Yanlış)
INDEX MATCH'ın tüm bu karmaşıklıklarını anlamaya çalışarak zaman kaybetmenin amacı nedir?
Bu durumda, hangi işlevin kullanılacağı önemli değildir. Bu, INDEX() ve MATCH() işlevlerinin birlikte nasıl çalıştığını anlamak için yalnızca bir örnektir. Diğer örnekler, DÜŞEYARA'nın güçsüz olduğu durumlarda bu işlevlerin neler yapabileceğini gösterecektir.
İNDEKS EŞLEŞTİRME veya DÜŞEYARA
Hangi arama formülünün kullanılacağına karar verirken, çoğu kişi INDEX() ve MATCH()'ın DÜŞEYARA'dan çok daha üstün olduğu konusunda hemfikirdir. Ancak, birçok kişi hala DÜŞEYARA() kullanıyor. İlk olarak, DÜŞEYARA() daha basittir ve ikinci olarak, kullanıcılar INDEX() ve MATCH() ile çalışmanın tüm avantajlarını tam olarak anlamıyor. Bu bilgi olmadan, hiç kimse zamanını karmaşık bir sistemi incelemekle geçirmeyi kabul etmeyecektir.
INDEX() ve MATCH()'ın VLOOKUP()'a göre temel avantajları şunlardır:
- Sağdan sola arayın. DÜŞEYARA() sağdan sola arama yapamaz, bu nedenle aradığınız değerler her zaman tablonun en soldaki sütunlarında olmalıdır. Ancak INDEX() ve MATCH() bunu sorunsuz bir şekilde halledebilir. Bu makale size pratikte nasıl göründüğünü anlatacak: sol tarafta istenen değeri nasıl bulacağınız.
- Sütunların güvenli eklenmesi veya çıkarılması. DÜŞEYARA() formülü, sütunları kaldırırken veya eklerken yanlış sonuçlar gösteriyor çünkü DÜŞEYARA()'nın başarılı olması için tam sütun numarası gerekiyor. Doğal olarak sütunlar eklendiğinde veya kaldırıldığında sayıları da değişir.
Ve INDEX() ve MATCH() formüllerinde, tek tek sütunlar değil, bir dizi sütun belirtilir. Sonuç olarak, her seferinde formülü güncellemek zorunda kalmadan sütunları güvenle ekleyebilir ve kaldırabilirsiniz.
- Arama hacimlerinde sınır yok. DÜŞEYARA() kullanırken, toplam arama kriteri sayısı 255 karakteri geçmemelidir, aksi takdirde bir #DEĞER elde edersiniz! Dolayısıyla, verileriniz çok sayıda karakter içeriyorsa, INDEX() ve MATCH() en iyi seçenektir.
- Yüksek işlem hızı. Tablolarınız nispeten küçükse, herhangi bir fark görme ihtimaliniz yoktur. Ancak, tablo yüzlerce veya binlerce satır içeriyorsa ve buna göre yüzlerce ve binlerce formül varsa, INDEX () ve KAÇINCI () DÜŞEYARA () işlevinden çok daha hızlı başa çıkacaktır. Gerçek şu ki, Excel tüm tabloyu işlemek yerine yalnızca formülde belirtilen sütunları işleyecektir.
DÜŞEYARA()'nın performans etkisi, çalışma sayfanız DÜŞEYARA() ve TOPLA() gibi çok sayıda formül içeriyorsa özellikle fark edilir. Bir dizideki her bir değeri ayrıştırmak için DÜŞEYARA() işlevlerinin ayrı kontrolleri gerekir. Dolayısıyla Excel'in çok büyük miktarda bilgiyi işlemesi gerekiyor ve bu da işi önemli ölçüde yavaşlatıyor.
Formül Örnekleri
Bu işlevlerin faydasını zaten anladık, bu yüzden en ilginç kısma geçebiliriz: bilginin pratikte uygulanması.
Sağdan sola aranacak formül
Daha önce de belirtildiği gibi, DÜŞEYARA bu arama biçimini gerçekleştiremez. Yani istenilen değerler en soldaki sütunda değilse DÜŞEYARA() bir sonuç üretmeyecektir. INDEX() ve MATCH() işlevleri daha çok yönlüdür ve değerlerin konumu, çalışması için büyük bir rol oynamaz.
Örneğin, tablomuzun sol tarafına bir rütbe sütunu ekleyeceğiz ve Ülkemizin başkentinin nüfus açısından hangi sırada olduğunu bulmaya çalışacağız.
G1 hücresine, bulunacak değeri yazıyoruz ve ardından C1:C10 aralığında arama yapmak ve A2:A10'dan karşılık gelen değeri döndürmek için aşağıdaki formülü kullanıyoruz:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Çabuk. Bu formülü birden çok hücre için kullanmayı planlıyorsanız, mutlak adresleme kullanarak aralıkları düzelttiğinizden emin olun (örneğin, $A$2: $A$10 ve $C$2: 4C$10).
ENDEKS DAHA MARUZ KALMIŞ DAHA MARUZ KALMIŞ sütunlarda ve satırlarda arama yapmak için
Yukarıdaki örneklerde, önceden tanımlanmış bir satır aralığından değerler döndürmek için DÜŞEYARA() yerine bu işlevleri kullandık. Peki ya bir matris veya iki taraflı arama yapmanız gerekiyorsa?
Kulağa karmaşık geliyor, ancak bu tür hesaplamaların formülü, standart INDEX() MATCH() formülüne benzer, yalnızca bir farkla: MATCH() formülü iki kez kullanılmalıdır. Satır numarasını almak için ilk kez ve sütun numarasını almak için ikinci kez:
=INDEX(dizi, KAÇINCI(dikey arama değeri, arama sütunu, 0), KAÇINCI(yatay arama değeri, arama satırı, 0))
Aşağıdaki tabloya bakalım ve bir formül yapmaya çalışalım İNDEKS() EKSPRES() EKSPRES() Seçilen bir yıl için belirli bir ülkedeki demografiyi görüntülemek için.
Hedef ülke G1 hücresindedir (dikey arama) ve hedef yıl G2 hücresindedir (yatay arama). Formül şöyle görünecek:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Bu formül nasıl çalışır?
Diğer karmaşık formüllerde olduğu gibi, bunları tek tek denklemlere ayırarak anlamak daha kolaydır. Ve sonra her bir fonksiyonun ne yaptığını anlayabilirsiniz:
- MAÇ(G1,A2:A11,0) – A1:A2 aralığında bir değer (G11) arar ve bu değerin sayısını gösterir, bizim durumumuzda 2'dir;
- ARAMA(G2;B1:D1,0) – B2:D1 aralığında bir değer (G1) arar. Bu durumda sonuç 3 oldu.
Bulunan satır ve sütun numaraları, INDEX() formülündeki karşılık gelen değere gönderilir:
=DİZİN(B2:D11,2,3)
Sonuç olarak, B2:D3 aralığında 2 satır ve 11 sütunun kesiştiği bir hücrede bulunan bir değere sahibiz. Ve formül, D3 hücresindeki istenen değeri gösterir.
INDEX ve MATCH ile birden çok koşula göre arama yapın
DÜŞEYARA() kılavuzumuzu okuduysanız, muhtemelen birden çok arama formülü denemişsinizdir. Ancak bu arama yönteminin önemli bir sınırlaması vardır – yardımcı bir sütun ekleme ihtiyacı.
Ama iyi haber şu ki INDEX() ve MATCH() ile çalışma sayfanızı düzenlemek veya değiştirmek zorunda kalmadan birden çok koşulu arayabilirsiniz.
INDEX() MATCH() için genel çok koşullu arama formülü:
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Not: bu formül klavye kısayoluyla birlikte kullanılmalıdır CTRL+SHIFT+ENTER.
Aradığınız değeri 2 koşula göre bulmanız gerektiğini varsayalım: Alıcı и Ürün.
Bu, aşağıdaki formülü gerektirir:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
Bu formülde C2:C10 aramanın yapılacağı aralıktır, F1 - bu durum, A2:A10 — durumu karşılaştırmak için aralıktır, F2 – durum 2, V2: V10 – durum 2'nin karşılaştırılması için aralık.
Formül ile işin sonundaki kombinasyona basmayı unutmayınız. CTRL + ÜST KRKT + ENTER – Excel, örnekte gösterildiği gibi formülü kaşlı ayraçlarla otomatik olarak kapatır:
Çalışmanız için bir dizi formülü kullanmak istemiyorsanız, formüle başka bir INDEX() ekleyin ve ENTER'a basın, örnekteki gibi görünecektir:
Bu formüller nasıl çalışır?
Bu formül, standart INDEX() MATCH() formülüyle aynı şekilde çalışır. Birden çok koşulu aramak için, tek tek doğru ve yanlış koşulları temsil eden birden çok Yanlış ve Doğru koşul oluşturmanız yeterlidir. Ve sonra bu koşullar, dizinin karşılık gelen tüm öğelerine uygulanır. Formül, Yanlış ve Doğru bağımsız değişkenlerini sırasıyla 0 ve 1'e dönüştürür ve dizede bulunan eşleşen değerlerin 1 olduğu bir dizi çıktısı verir. MATCH(), 1 ile eşleşen ilk değeri bulur ve bunu INDEX() formülüne iletir. Ve sırayla, istenen sütundan belirtilen satırda zaten istenen değeri döndürür.
Dizi içermeyen bir formül, INDEX()'in bunları kendi başına işleme yeteneğine bağlıdır. Formüldeki ikinci INDEX() falsy (0) ile eşleşir, bu nedenle tüm diziyi bu değerlerle MATCH() formülüne geçirir.
Bu, bu formülün arkasındaki mantığın oldukça uzun bir açıklamasıdır. Daha fazla bilgi için makaleyi okuyun “Birden çok koşulla INDEX MATCH'.
İNDEKS ve MAÇTA ORTALAMA, MAKS ve MİN
Excel'in ortalamaları, maksimumları ve minimumları bulmak için kendi özel işlevleri vardır. Peki ya bu değerlerle ilişkili hücreden veri almak istiyorsanız? Bu durumda ORTALAMA, MAKS ve MİN, INDEX ve MAÇ ile birlikte kullanılmalıdır.
İNDEKS MAÇ ve MAKS
D sütunundaki en büyük değeri bulmak ve C sütununda görüntülemek için şu formülü kullanın:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
İNDEKS MAÇ ve MIN
D sütunundaki en küçük değeri bulmak ve C sütununda görüntülemek için aşağıdaki formülü kullanın:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
ARAMA İNDEKSİ ve SERPENT
D sütunundaki ortalama değeri bulmak ve bu değeri C'de görüntülemek için:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Verilerinizin nasıl yazıldığına bağlı olarak, MATCH() işlevinin üçüncü argümanı 1, 0 veya -1'dir:
- sütunlar artan düzende sıralanmışsa, 1'i ayarlayın (bu durumda formül, ortalama değerden küçük veya ona eşit olan maksimum değeri hesaplayacaktır);
- sıralama azalıyorsa, o zaman -1 (formül, ortalamadan büyük veya ona eşit olan minimum değeri verir);
- arama dizisi, tam olarak ortalamaya eşit bir değer içeriyorsa, onu 0'a ayarlayın.
Örneğimizde, popülasyon azalan düzende sıralanmıştır, bu yüzden -1 koyduk. Ve sonuç Tokyo'dur, çünkü nüfus değeri (13,189) ortalama değere (000) en yakın değerdir.
DÜŞEYARA() da bu tür hesaplamaları gerçekleştirebilir, ancak yalnızca bir dizi formülü olarak: ORTALAMA, MIN ve MAX ile DÜŞEYARA.
INDEX MATCH ve ESND/IFERROR
Formül istenen değeri bulamazsa hata verdiğini muhtemelen fark etmişsinizdir. # N / A. Standart hata mesajını daha bilgilendirici bir şeyle değiştirebilirsiniz. Örneğin, argümanı formülde ayarlayın XNUMX.'de:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Bu formül ile tabloda olmayan verileri girerseniz form size belirtilen mesajı verecektir.
Tüm hataları yakalamak istiyorsanız, o zaman hariç XNUMX.'de kullanılabilir EĞERHATA:
=EĞERHATA(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Bir şeyler ters gitti!”)
Ancak, hataları bu şekilde maskelemenin iyi bir fikir olmadığını unutmayın, çünkü standart hatalar formüldeki ihlalleri bildirir.
INDEX MATCH() işlevini kullanma kılavuzumuzu yararlı bulduğunuzu umuyoruz.