Excel'de INDEX ve MATCH birlikte nasıl kullanılır?
Excel tablolarıyla çalışırken, sürekli olarak bir değer aramanız gereken durumlarla karşılaşabilirsiniz. Bu öğreticide, yatay ve dikey aramalar, iki yönlü aramalar, büyük/küçük harfe duyarlı aramalar ve birden çok kriteri karşılayan aramalar yapmak için İNDEKS ve KAÇINCI işlevlerinin kombinasyonunu nasıl uygulayacağınızı göstereceğiz.
İNDEKS ve KAÇINCI işlevleri Excel'de ne yapar?
İNDEKS ve KAÇINCI işlevleri birlikte nasıl kullanılır?
- INDEX ve MATCH'ı birleştirmek için örnek
- Soldan arama uygulamak için INDEX ve MATCH
- İki yönlü arama uygulamak için INDEX ve MATCH
- Büyük/küçük harfe duyarlı bir arama uygulamak için INDEX ve MATCH
- Birden çok kritere sahip bir arama uygulamak için INDEX ve MATCH
- Birden çok sütuna bir arama uygulamak için INDEX ve MATCH
İNDEKS ve KAÇINCI işlevleri Excel'de ne yapar?
INDEX ve MATCH fonksiyonlarını kullanmadan önce, INDEX ve MATCH'ın ilk olarak değerleri aramamıza nasıl yardımcı olabileceğini bildiğimizden emin olalım.
Excel'de INDEX işlevinin kullanımı
The INDEX Excel'deki işlev, belirli bir aralıkta belirli bir konumdaki değeri döndürür. INDEX işlevinin sözdizimi aşağıdaki gibidir:
- dizi (gerekli) değeri döndürmek istediğiniz aralığı ifade eder.
- satır_sayısı (sütun_sayısı yoksa gereklidir) dizinin satır numarasını ifade eder.
- sütun_sayısı (isteğe bağlı, ancak satır_sayısı atlanırsa gereklidir) dizinin sütun numarasını belirtir.
Örneğin, bilmek Jeff'in final sınav puanı, listedeki 6. öğrenci, INDEX işlevini şu şekilde kullanabilirsiniz:
=DİZİN(E2:E11, 6) >>> döner 60
√ Not: Aralık E2: E11 final sınavının listelendiği yerdir, sayı ise 6 sınav notunu bulur 6öğrenci.
Burada küçük bir test yapalım. formül için =DİZİN(B2:E2,3), hangi değeri döndürecek? --- Evet, geri dönecek Çin, 3rd Verilen aralıktaki değer.
Artık INDEX işlevinin yatay veya dikey aralıklarla mükemmel şekilde çalışabileceğini bilmeliyiz. Peki ya birkaç satır ve sütun içeren daha geniş bir aralıkta bir değer döndürmemiz gerekirse? Peki, bu durumda hem satır numarası hem de sütun numarası uygulamalıyız. Örneğin, öğrenmek için Emily'nin geldiği ülke INDEX ile, B8'den E3'e kadar olan hücrelerde satır numarası 2 ve sütun numarası 11 olan değeri şu şekilde bulabiliriz:
=DİZİN(B2:E11,8,3) >>> döner Çin
Yukarıdaki örneklere göre, Excel'deki INDEX işlevi hakkında şunları bilmelisiniz:
- INDEX işlevi, dikey ve yatay aralıklarla çalışabilir.
- INDEX işlevi büyük/küçük harfe duyarlı değildir.
- INDEX formülünde satır numarası (her iki sayıya da ihtiyacınız varsa) sütun numarasının önüne geçer.
Ancak, birden çok satır ve sütun içeren gerçekten büyük bir veritabanı için, formülü tam bir satır numarası ve sütun numarası ile uygulamak bizim için kesinlikle uygun değildir. Ve bu, MATCH işlevinin kullanımını birleştirmemiz gerektiğinde.
Şimdi önce KAÇINCI işlevinin temellerini öğrenelim.
Excel'de KAÇINCI işlevinin kullanımı
Excel'deki KAÇINCI işlevi, verilen aralıktaki belirli bir öğenin konumu olan sayısal bir değer döndürür. KAÇINCI işlevinin sözdizimi aşağıdaki gibidir:
- arama_dizisi (gerekli) MATCH'ın aramasını istediğiniz hücre aralığını ifade eder.
- eşleşme türü (isteğe bağlı), 1, 0 or -1:
- 1(varsayılan), KAÇINCI değerden küçük veya ona eşit olan en büyük değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi artan düzende yerleştirilmelidir.
- 0, MAÇ tam olarak eşit olan ilk değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi herhangi bir sırada olabilir. (Eşleme türünün 0 olarak ayarlandığı durumlarda joker karakterler kullanabilirsiniz.)
- -1, KAÇINCI değerden büyük veya ona eşit olan en küçük değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi azalan düzende yerleştirilmelidir.
Örneğin, bilmek Vera'nın isim listesindeki konumu, MAÇ formülünü şu şekilde kullanabilirsiniz:
=KAÇINCI("vera",C2:C11,0) >>> döner 4
√ Not: KAÇINCI işlevi büyük/küçük harfe duyarlı değildir. “4” sonucu, “Vera” adının listenin 4. sırasında olduğunu gösterir. Formüldeki "0", "Vera" arama değerine tam olarak eşit olan arama dizisindeki ilk değeri bulan eşleme türüdür.
Bilmek B96'den E2'ye kadar olan satırdaki “2” puanının konumu, MATCH'ı şu şekilde kullanabilirsiniz:
=KAÇINCI(96;B2:E2,0) >>> döner 4
☞ Excel'deki KAÇINCI işlevi hakkında bilmemiz gerekenler:
- KAÇINCI işlevi, değerin kendisini değil, arama dizisindeki arama değerinin konumunu döndürür.
- KAÇINCI işlevi, yinelemeler olması durumunda ilk eşleşmeyi döndürür.
- INDEX işlevi gibi, KAÇINCI işlevi de dikey ve yatay aralıklarla çalışabilir.
- MATCH da büyük/küçük harfe duyarlı değildir.
- KAÇINCI formülünün arama değeri metin biçimindeyse, onu tırnak içine alın.
Artık INDEX ve MATCH işlevlerinin Excel'deki temel kullanımlarını öğrendiğimize göre, kolları sıvayalım ve iki işlevi birleştirmeye hazırlanalım.
İNDEKS ve KAÇINCI işlevleri birlikte nasıl kullanılır?
Bu bölümde, farklı ihtiyaçları karşılamak için INDEX ve MATCH işlevlerini kullanmak için farklı durumlardan bahsedeceğiz.
INDEX ve MATCH'ı birleştirmek için örnek
INDEX ve MATCH işlevlerini nasıl birleştirebileceğimizi anlamak için lütfen aşağıdaki örneğe bakın:
Örneğin, bilmek Evelyn'in final sınav puanı, şu formülü kullanmalıyız:
=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> döner 90
Pekala, formül karmaşık görünebileceğinden, hadi her bir kısmı üzerinden geçelim.
Yukarıda gördüğünüz gibi, büyük INDEX formül üç argüman içerir:
- dizi: A2: D11 INDEX'e hücrelerden eşleşen değeri döndürmesini söyler A2'den D11'e.
- satır_sayısı: KAÇINCI("evelyn",B2:B11,0) INDEX'e değerin tam satırını söyler.
- KAÇINCI formülü hakkında şöyle açıklayabiliriz: B2'den B11'e kadar olan hücrelerde “evelyn”e tam olarak eşit olan ilk değerin konumunu sayısal bir değerde döndürmek, yani 5.
- sütun_sayısı: MAÇ("final sınavı",A1:D1,0) INDEX'e değerin tam sütununu söyler.
- KAÇINCI formülü ile ilgili olarak şöyle açıklayabiliriz: A1'den D1'e kadar olan hücrelerde “final sınavına” tam olarak eşit olan ilk değerin konumunu sayısal bir değerde döndürmek, yani 4.
Böylece, büyük formülü aşağıda gösterdiğimiz kadar basit görebilirsiniz:
= INDEX (A2: D11,5,4)
Örnekte, sabit kodlanmış değerler olan "evelyn" ve "final sınavı" kullandık. Ancak, böyle büyük bir formülde, her yeni bir şey ararken onları değiştirmek zorunda kalacağımız için sabit kodlanmış değerler istemiyoruz. Bu gibi durumlarda, formülü şu şekilde dinamik hale getirmek için hücre referanslarını kullanabiliriz:
= INDEX (A2: D11,KİBRİT(G2,B2:B11,0),KİBRİT(F3,A1:D1,0))
Soldan arama uygulamak için INDEX ve MATCH
Şimdi, diyelim ki Evelyn'in sınıfını bilmeniz gerekiyor, cevabı bilmek için INDEX ve MATCH'ı nasıl kullanabiliriz? Dikkat ettiyseniz, sınıf sütununun ad sütununun sol tarafında olduğunu ve başka bir Excel'in güçlü arama işlevi olan DÜŞEYARA'nın yeteneğinin ötesinde olduğunu fark etmelisiniz.
Aslında, sola arama yeteneği, INDEX ve MATCH kombinasyonunun DÜŞEYARA'dan daha üstün olduğu yönlerden biri olur.
Bilmek Evelyn'in sınıfı, yapmanız gereken tek şey F3 hücresindeki değeri "Sınıf" olarak değiştirmek ve yukarıda gösterilen formülün aynısını kullanmaktır, İNDİS ve KAÇINCI işlevleri size hemen cevabı söyleyecektir:
=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> döner A
kurulumunuz varsaLed Kutools for Excel, Ekibimiz tarafından geliştirilen profesyonel bir Excel eklentisi, ayrıca kendi özellikleri ile belirtilen değerler için soldan arama yapabilirsiniz. Sağdan Sola ARA birkaç tıklama ile özellik. Özelliği uygulamak için lütfen şuraya gidin: Kutools excel'inizdeki sekmeyi bulun formül grubu ve tıklayın Sağdan Sola ARA açılır listesinde Süper ARAMA. Bunun gibi bir açılır iletişim kutusu göreceksiniz:
ile sola arama özelliğini uygulamak için somut adımlar için burayı tıklayın Kutools for Excel.
İki yönlü arama uygulamak için INDEX ve MATCH
Şimdi, iki yönlü aramaları uygulamak için dinamik arama değerleriyle İNDEKS ve KAÇINCI kombinasyon formülünü oluşturabiliyor musunuz? Aşağıda gösterildiği gibi G3, G4 ve G5 hücrelerinde formüller oluşturmaya çalışalım:
İşte cevaplar:
Hücre G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Hücre G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Hücre G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))
√ Not: Formülleri uyguladıktan sonra G2 hücresindeki ismi değiştirerek herhangi bir öğrencinin bilgilerine kolayca ulaşabilirsiniz.
Büyük/küçük harfe duyarlı bir arama uygulamak için INDEX ve MATCH
Yukarıdaki örneklerden, INDEX ve MATCH işlevlerinin büyük/küçük harfe duyarlı olmadığını biliyoruz. Ancak, büyük ve küçük harfleri ayırt etmek için formülünüze ihtiyaç duyduğunuz durumlarda, ekleyebilirsiniz. TAM formüllerinize şu şekilde çalışın:
√ Not: Bu, aşağıdakilerle girmenizi gerektiren bir dizi formülüdür: Ctrl + Üst Karakter + Enter. Formül çubuğunda bir çift süslü parantez görünecektir.
Örneğin, bilmek JIMMY'nin sınav puanı, aşağıdaki gibi işlevleri kullanın:
=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> döner 86
Veya hücre referanslarını kullanabilirsiniz:
=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> döner 86
√ Not: ile girmeyi unutmayın Ctrl + Üst Karakter + Enter.
Birden çok kritere sahip bir arama uygulamak için INDEX ve MATCH
Birkaç sütun ve satır başlığı içeren büyük bir veritabanıyla uğraşırken, birden çok koşulu karşılayan bir şey bulmak her zaman zordur. Bu durumda, birden çok kriter aramak için lütfen aşağıdaki formüle bakın:
√ Not: Bu, ile girmenizi gerektiren bir dizi formülüdür. Ctrl + Üst Karakter + Enter. Formül çubuğunda bir çift süslü parantez görünecektir.
Örneğin, bulmak için Hindistan'dan A sınıfı Coco'nun final sınav puanı, formül aşağıdaki gibidir:
=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> döner 88
√ Not: ile girmeyi unutmayın Ctrl + Üst Karakter + Enter.
Peki ya sürekli kullanmayı unutursanız Ctrl + Üst Karakter + Enter formülün yanlış sonuçlar vermesi için formülü tamamlamak için? Burada, tek bir basit formülle tamamlayabileceğiniz daha karmaşık bir formülümüz var. Keşfet anahtar:
Bulmak için yukarıdaki aynı örnek için Hindistan'dan A sınıfı Coco'nun final sınav puanı, sadece normal bir formüle ihtiyaç duyan formül Keşfet isabet aşağıdaki gibidir:
=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> döner 88
Burada, birden çok kriterli durumda evrensel bir formül isteyeceğimizden, sabit kodlanmış değerleri kullanmayacağız. Ancak bu şekilde yukarıdaki örnekte G2, G3, G4 hücrelerindeki değerleri değiştirerek istediğimiz sonucu kolayca elde edebiliriz.
İle Kutools for Excel's Çok Koşullu Arama özelliği, birkaç tıklamayla birden çok kriterle belirli değerleri arayabilirsiniz. Özelliği uygulamak için lütfen şuraya gidin: Kutools excel'inizdeki sekmeyi bulun formül grubu ve tıklayın Çok Durumlu Arama açılır listesinde Süper ARAMA. Daha sonra aşağıda gösterildiği gibi bir açılır iletişim kutusu göreceksiniz:
Birden çok sütuna bir arama uygulamak için INDEX ve MATCH
Aşağıda gösterildiği gibi bir başlığı paylaşan farklı sütunlara sahip bir Excel elektronik tablomuz varsa, her öğrencinin adını INDEX ve MATCH ile sınıfıyla nasıl eşleştirebiliriz?
Burada, profesyonel aracımızla görevi tamamlamanın yolunu göstereyim. Kutools for Excel. Onunla formül Yardımcısı, öğrencileri, aşağıda gösterilen adımlarla hızlı bir şekilde sınıflarıyla eşleştirebilirsiniz:
1. Fonksiyonu uygulamak istediğiniz hedef hücreyi seçin.
2. Altında Kutools sekmesinde, gidin Formül Yardımcısıtık Formül Yardımcısı açılır listede.
3. Seçmek Formül Türünden Aramave ardından tıklayın Birden çok sütunda dizin ve eşleştirme.
4. a. 1. tıklayın 
düğmesinin sağ tarafında Ara_kol değer döndürmek istediğiniz hücreleri, yani sınıf adlarını seçmek için. (Burada yalnızca tek bir sütun veya satır seçebilirsiniz.)
B. 2. tıklayın düğmesinin sağ tarafında tablo_rng seçilen değerlerle eşleşecek hücreleri seçmek için Ara_kol, yani öğrencilerin isimleri.
C. 3. tıklayın düğmesinin sağ tarafında aranan_değer aranacak hücreyi, yani sınıfıyla eşleştirmek istediğiniz öğrencinin adını seçmek için.
5. Tamam'a tıklayın, hedef hücrede Jimmy'nin sınıf adını göreceksiniz.
6. Artık diğer öğrencilerin sınıflarını doldurmak için doldurma tutamacını aşağı sürükleyebilirsiniz.
En İyi Ofis Üretkenliği Araçları
Kutools for Excel Sorunlarınızın Çoğunu Çözer, Verimliliğinizi %80 Artırır
- Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
- Hücreleri / Satırları / Sütunları Birleştirme ve Verilerin Saklanması; Bölünmüş Hücre İçeriği; Yinelenen Satırları ve Toplam / Ortalamayı Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
- Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
- Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
- Sık Kullanılan ve Hızlı Eklenen Formüller, Aralıklar, Grafikler ve Resimler; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
- Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
- Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
- Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
- Pivot Tablo Gruplaması hafta numarası, haftanın günü ve daha fazlası ... Kilidi Açılmış, Kilitli Hücreleri Göster farklı renklerle; Formülü / Adı Olan Hücreleri Vurgulayın...
- Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
- Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
