Excel INDEX İşlevi (Örnekler + Video)
Excel INDEX İşlevi ne zaman kullanılır?
Excel INDEX işlevi, tablo halindeki bir veriden değer almak istediğinizde ve veri noktasının satır numarasına ve sütun numarasına sahip olduğunuzda kullanılabilir. Örneğin, aşağıdaki örnekte, veri kümesindeki satır numarasını ve sütun numarasını bildiğinizde Fizikte 'Tom' işaretlerini almak için INDEX işlevini kullanabilirsiniz.
Ne Döndürür
Belirtilen satır numarası ve sütun numarası için bir tablodan değeri döndürür.
Sözdizimi
=INDEX (dizi, satır_sayısı, [sütun_sayısı])
=INDEX (dizi, satır_sayısı, [sütun_sayısı], [alan_sayısı])
INDEX işlevi 2 sözdizimine sahiptir. İlki çoğu durumda kullanılır, ancak üç yönlü aramalarda ikincisi kullanılır (Örnek 5'te ele alınmıştır).
Giriş Bağımsız Değişkenleri
- 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.
Ek Notlar (Sıkıcı Şeyler… Ama Bilinmesi Önemli)
- Satır numarası veya sütun numarası 0 ise, sırasıyla tüm satırın veya sütunun değerlerini döndürür.
- INDEX işlevi bir hücre referansının (A1: gibi) önünde kullanılırsa, değer yerine bir hücre referansı döndürür (aşağıdaki örneklere bakın).
- En yaygın olarak MATCH işleviyle birlikte kullanılır.
- DÜŞEYARA'dan farklı olarak, INDEX işlevi, arama değerinin solundan bir değer döndürebilir.
- INDEX işlevinin iki formu vardır - Dizi formu ve Referans formu
- 'Dizi formu', belirli bir tablodan satır ve sütun numarasına dayalı bir değer aldığınız yerdir.
- 'Referans formu', birden çok tablonun olduğu yerdir ve tabloyu seçmek için alan_sayısı bağımsız değişkenini kullanır ve ardından satır ve sütun numarasını kullanarak içinde bir değer getirirsiniz (aşağıdaki canlı örneğe bakın).
Excel INDEX İşlevi - Örnekler
İşte Excel INDEX İşlevini kullanmanın altı örneği.
Örnek 1 - Fizikte Tom'un İşaretlerini Bulma (iki yönlü bir arama)
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:
Fizikte Tom'un işaretlerini bulmak için aşağıdaki formülü kullanın:
=INDEX($B$3:$E$10,3,2)
Bu INDEX formülü, diziyi tüm konular için işaretleri olan $B$3:$E$10 olarak belirtir. Ardından, Fizikte Tom'un işaretlerini almak için satır numarasını (3) ve sütun numarasını (2) kullanır.
Örnek 2 - MATCH İşlevini Kullanarak ARAMA Değerini Dinamik Yapma
Satır numarasını ve sütun numarasını manuel olarak belirlemek her zaman mümkün olmayabilir. Çok büyük bir veri kümeniz olabilir veya hücrelerde belirtilen adı ve/veya konuyu otomatik olarak tanımlaması ve doğru sonucu vermesi için dinamik hale getirmek isteyebilirsiniz.
Aşağıda gösterildiği gibi bir şey:
Bu, INDEX ve MATCH işlevinin bir kombinasyonu kullanılarak yapılabilir.
Arama değerlerini dinamik hale getirecek formül:
=INDEX($B$3:$E$10,KAÇINCI($G$5,$A$3:$A$10,0),KAÇINCI($Y$4,$B$2:$E$2,0))
Yukarıdaki formülde satır numarasını ve sütun numarasını sabit kodlamak yerine, dinamik hale getirmek için KAÇINCI işlevi kullanılır.
- Dinamik Satır Numarası, formülün şu kısmı tarafından verilir - KAÇINCI($G$5,$A$3:$A$10,0). Öğrencilerin adını tarar ve arama değerini tanımlar (bu durumda $G$5). Ardından, veri kümesindeki arama değerinin satır numarasını döndürür. Örneğin, arama değeri Matt ise 1 döndürür, Bob ise 2 döndürür vb.
- Dinamik Sütun Numarası, formülün aşağıdaki kısmı ile verilir - KAÇINCI($H$4,$B$2:$E$2,0). Konu adlarını tarar ve arama değerini tanımlar (bu durumda $H$4). Ardından, veri kümesindeki arama değerinin sütun numarasını döndürür. Örneğin, arama değeri Math ise 1 döndürür, Fizik ise 2 döndürür vb.
Örnek 3 - Açılır Listeleri Arama Değerleri Olarak Kullanma
Yukarıdaki örnekte, verileri manuel olarak girmemiz gerekiyor. Bu, özellikle çok sayıda arama değeri listeniz varsa, zaman alıcı ve hataya açık olabilir.
Bu gibi durumlarda iyi bir fikir, arama değerlerinin bir açılır listesini oluşturmak (bu durumda, öğrenci adları ve dersleri olabilir) ve ardından listeden seçim yapmaktır. Seçime bağlı olarak, formül sonucu otomatik olarak günceller.
Aşağıda gösterildiği gibi bir şey:
Bu, arka uçta yüzlerce öğrenciyle büyük bir veri setine sahip olabileceğiniz için iyi bir gösterge panosu bileşeni yapar, ancak son kullanıcı (diyelim ki bir öğretmen), sadece seçimler yaparak bir konuda bir öğrencinin notlarını hızla alabilir. damla aşağı.
Bu nasıl yapılır:
Bu durumda kullanılan formül, Örnek 2'de kullanılanla aynıdır.
=INDEX($B$3:$E$10,KAÇINCI($G$5,$A$3:$A$10,0),KAÇINCI($Y$4,$B$2:$E$2,0))
Arama değerleri açılır listelere dönüştürülmüştür.
Excel açılır listesini oluşturma adımları şunlardır:
- Açılır listeyi istediğiniz hücreyi seçin. Bu örnekte, G4'te öğrenci isimlerini istiyoruz.
- Veri -> Veri Araçları -> Veri Doğrulama'ya gidin.
- Veri Doğrulama İletişim kutusunda, ayarlar sekmesinde, İzin Ver açılır menüsünden Liste'yi seçin.
- Kaynakta $A$3:$A$10 öğesini seçin
- Tamam'ı tıklayın.
Şimdi G5 hücresinde açılır listeye sahip olacaksınız. Benzer şekilde, konular için H4'te bir tane oluşturabilirsiniz.
Örnek 4 - Bir Satırın/Sütunun Tümünden Dönen Değerler
Yukarıdaki örneklerde, 2 yönlü arama yapmak ve tek bir değer elde etmek için Excel INDEX işlevini kullandık.
Şimdi, ya bir öğrencinin tüm notlarını almak istiyorsanız. Bu, o öğrencinin maksimum/minimum puanını veya tüm derslerde alınan toplam puanları bulmanızı sağlayabilir.
Basit İngilizcede, önce bir öğrenci için tüm puan satırını (diyelim Bob) almak ve sonra bu değerler içinde en yüksek puanı veya tüm puanların toplamını belirlemek istersiniz.
İşte hile.
Excel INDEX İşlevinde, 0 olarak sütun numarası, tüm satırın değerlerini döndürür.
Yani bunun formülü şöyle olacaktır:
=INDEX($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),0)
Şimdi bu formül. olduğu gibi kullanılırsa, #DEĞER! hata. Hatayı görüntülerken, arka uçta Tom - {57,77,91,91} için tüm puanları içeren bir dizi döndürür.
Düzenleme modunda formülü seçip F9 tuşuna basarsanız, döndürdüğü diziyi görebileceksiniz (aşağıda gösterildiği gibi): 
Benzer şekilde, arama değerinin ne olduğuna bağlı olarak, sütun numarası 0 olarak belirtildiğinde (veya boş bırakıldığında), arama değeri için satırdaki tüm değerleri döndürür.
Şimdi Tom tarafından elde edilen toplam puanı hesaplamak için, SUM işlevi içinde yukarıdaki formülü kullanabiliriz.
=TOPLA(INDEX($B$3:$E$10,KAÇINCI($G$5,$A$3:$A$10,0),0))
Benzer satırlarda, en yüksek puanı hesaplamak için MAKS/BÜYÜK ve minimum hesaplamak için MIN/KÜÇÜK kullanabiliriz.
Örnek 5 - INDEX/MATCH Kullanarak Üç Yönlü Arama
Excel INDEX işlevi, üç yönlü aramaları işlemek için oluşturulmuştur.
Üç yönlü arama nedir?
Yukarıdaki örneklerde, farklı konulardaki öğrenciler için puanları olan bir tablo kullandık. Puanı almak için iki değişken kullandığımızdan (öğrencinin adı ve konu) bu, iki yönlü bir arama örneğidir.
Şimdi, bir yılda, bir öğrencinin üç farklı sınav düzeyi olduğunu varsayalım: Birim Testi, Ara Sınav ve Final Sınavı (öğrenciyken ben de böyleydim).
Üç yönlü bir arama, bir öğrencinin belirtilen sınav seviyesinden belirli bir konu için not alma yeteneği olacaktır. Bu, üç değişken olduğu için (öğrencinin adı, dersin adı ve sınav düzeyi) üç yönlü bir arama yapar.
İşte üç yönlü bir arama örneği:
Yukarıdaki örnekte öğrencinin adını ve konu adını seçmenin yanı sıra sınav düzeyini de seçebilirsiniz. Muayene düzeyine bağlı olarak, üç tablodan birinden eşleşen değeri döndürür.
İşte H4 hücresinde kullanılan formül:
=INDEX(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),MATCH($G$4,$A$3:$A$7.0),MATCH($H $3,$B$2:$E$2,0),IF($H$2="Birim Testi",1,IF($H$2="Ara sınav",2,3)))
Nasıl çalıştığını anlamak için bu formülü parçalayalım.
Bu formül dört argüman alır. INDEX, Excel'de birden fazla sözdizimine sahip olan işlevlerden biridir.
=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 yukarıdaki tüm örneklerde ilk sözdizimini kullandık, ancak üç yönlü bir arama yapmak için ikinci sözdizimini kullanmamız gerekiyor.
Şimdi formülün her bir bölümünü ikinci sözdizimine göre görelim.
- dizi - ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23): Bu durumda tek bir dizi kullanmak yerine parantez içinde üç dizi kullandık.
- row_num - KAÇINCI($G$4,$A$3:$A$7,0): KAÇINCI işlevi, öğrenci adı listesinde $G$4 hücresindeki öğrencinin adının konumunu bulmak için kullanılır.
- col_num - KAÇINCI($H$3,$B$2:$E$2,0): KAÇINCI işlevi, özne adı listesinde $H$3 hücresindeki özne adının konumunu bulmak için kullanılır.
- [area_num] - IF($H$2=”Birim Testi”,1,IF($H$2=”Ara Dönem”,2,3)): Alan numarası değeri, INDEX işlevine hangi diziyi seçeceğini söyler. Bu örnekte, ilk argümanda üç dizimiz var. Açılır menüden Birim Testi'ni seçerseniz, EĞER işlevi 1 döndürür ve INDEX işlevleri üç diziden ($B$3:$E$7 olan) 1. diziyi seçer.
Örnek 6 - INDEX İşlevini Kullanarak Referans Oluşturma (Dinamik Adlandırılmış Aralıklar)
Bu, Excel INDEX işlevinin vahşi bir kullanımıdır.
Basit bir örnek verelim.
Aşağıda gösterildiği gibi bir isim listem var:
Artık listedeki soyadını almak için basit bir INDEX işlevi kullanabilirim.
İşte formül:
=INDEX($A$2:$A$9,COUNTA($A$2:$A$9))
Bu işlev sadece boş olmayan hücre sayısını sayar ve bu listedeki son öğeyi döndürür (yalnızca listede boşluk olmadığında çalışır).
Şimdi, burada sihir geliyor.
Formülü bir hücre başvurusunun önüne koyarsanız, formül eşleşen değerin (değerin kendisi yerine) hücre başvurusunu döndürür.
=A2:INDEX($A$2:$A$9,COUNTA($A$2:$A$9))
Yukarıdaki formülün =A2:”Josh” (burada Josh listedeki son değerdir) döndürmesini beklersiniz. Ancak, =A2:A9 değerini döndürür ve bu nedenle aşağıda gösterildiği gibi bir dizi ad alırsınız:
Bu tekniğin yardımcı olabileceği pratik bir örnek, dinamik adlandırılmış aralıklar oluşturmaktır.
Bu eğitimde bu kadar. Excel INDEX işlevini kullanmanın başlıca örneklerini ele almaya çalıştım. Bu listeye eklenmiş daha fazla örnek görmek isterseniz, yorumlar bölümünde bana bildirin.
Not: Bu öğreticiyi okumak için elimden gelenin en iyisini yapmaya çalıştım, ancak herhangi bir hata veya yazım hatası bulursanız lütfen bana bildirin 🙂
Excel INDEX İşlevi - Eğitim Videosu
- Excel DÜŞEYARA İşlevi.
- Excel HLOOKUP İşlevi.
- Excel DOLAYLI İşlevi.
- Excel EŞLEŞTİRME İşlevi.
- Excel OFSET İşlevi.
Aşağıdaki Excel Eğitimlerini de Beğenebilirsiniz:
- DÜŞEYARA Vs. İNDEKS/MAÇ
- Excel Dizin Eşleştirme
- Tüm Satır/Sütundaki Arama ve Dönüş Değerleri.
