Note: The other languages of the website are Google-translated. Back to English
Oturum aç  \/ 
x
or
x
Kaydol  \/ 
x

or

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?


İ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:

=INDEX(array, row_num, [column_num])
  • 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:

=INDEX(E2:E11, 6) >>> döner 60

excel dizin eşleşmesi 01

√ 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 =INDEX(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:

=INDEX(B2:E11,8,3) >>> döner Çin

excel dizin eşleşmesi 02

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:

=MATCH(lookup_value, lookup_array, [match_type])
  • 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

excel dizin eşleşmesi 03

√ 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 dizin eşleşmesi 04

☞ 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

excel dizin eşleşmesi 05

Pekala, formül karmaşık görünebileceğinden, hadi her bir kısmı üzerinden geçelim.

excel dizin eşleşmesi 06

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))

excel dizin eşleşmesi 07


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

excel dizin eşleşmesi 08

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:

excel dizin eşleşmesi 09

Kutools for Excel ile sol arama özelliğini uygulamak için somut adımlar için burayı tıklayın.


İ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:

excel dizin eşleşmesi 10

İş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:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ 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:

excel dizin eşleşmesi 11

=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:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
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:

excel dizin eşleşmesi 12

=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:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

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:

excel dizin eşleşmesi 13

=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:

excel dizin eşleşmesi 14

Kutools for Excel ile çok koşullu arama özelliğini uygulamak için somut adımlar için burayı tıklayın.


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?

excel dizin eşleşmesi 15

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.

excel dizin eşleşmesi 16

3. Seçmek Formül Türünden Aramave ardından tıklayın Birden çok sütunda dizin ve eşleştirme.

excel dizin eşleşmesi 17

4. a. 1. tıklayın excel dizin eşleştirme simgesidüğmesinin sağ tarafında Lookup_col 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 excel dizin eşleştirme simgesidüğmesinin sağ tarafında tablo_rng seçilen değerlerle eşleşecek hücreleri seçmek için Lookup_col, yani öğrencilerin isimleri.
    C. 3. tıklayın excel dizin eşleştirme simgesidüğ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.

excel dizin eşleşmesi 18

5. Tamam'a tıklayın, hedef hücrede Jimmy'nin sınıf adını göreceksiniz.

excel dizin eşleşmesi 19

6. Artık diğer öğrencilerin sınıflarını doldurmak için doldurma tutamacını aşağı sürükleyebilirsiniz.

excel dizin eşleşmesi 20

30 günlük ücretsiz deneme için Kutools for Excel'i indirmek için tıklayın.



  • 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...
kte sekmesi 201905
  • 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!
ofis tabanı
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.