Videoyu İzle - İkinci, Üçüncü veya N. Eşleşen Değeri Arayın
Excel'de veri aramak söz konusu olduğunda, sıklıkla kullandığım iki harika işlev vardır: DÜŞEYARA ve İNDEKS (çoğunlukla KAÇINCI işleviyle bağlantılı olarak).
Ancak bu formüller, arama değerinin yalnızca ilk örneğini bulmak için tasarlanmıştır.
Ama ya ikinci, üçüncü, dördüncü veya N. değere bakmak isterseniz.
Eh, biraz fazladan çalışma ile yapılabilir.
Bu derste, Excel'de ikinci veya Nth değerini nasıl arayacağınızı (örneklerle) size göstereceğim.
Excel'de İkinci, Üçüncü veya N. Değeri Arayın
Bu eğitimde, Excel'de ikinci veya N. değeri aramanın iki yolunu ele alacağım:
- Bir yardımcı sütun kullanma.
- Dizi formüllerini kullanma.
Hadi başlayalım ve hemen dalalım.
Yardımcı Sütunu Kullanma
Bir kuruluşta eğitim koordinatörü olduğunuzu ve aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım. Tüm eğitimi bir çalışanın adının önünde listelemek istiyorsunuz.
Yukarıdaki veri setinde çalışanlara farklı Microsoft Office araçları (Excel, PowerPoint ve Word) konusunda eğitim verilmiştir.
Artık bir çalışanın tamamladığı eğitimi bulmak için DÜŞEYARA işlevini veya INDEX/MATCH combo'yu kullanabilirsiniz. Ancak, yalnızca ilk eşleşen örneği döndürür.
Örneğin, John söz konusu olduğunda, üç eğitimi de almıştır, ancak adını DÜŞEYARA veya INDEX/MATCH ile aradığımda, her zaman, adı için listedeki ilk eğitim olan 'Excel'i döndürecektir. .
Bunu yapmak için bir yardımcı sütun kullanabilir ve içinde benzersiz arama değerleri oluşturabiliriz.
İşte adımlar:
- Eğitimi listeleyen sütundan önce bir sütun ekleyin.
- B2 hücresine aşağıdaki formülü girin:
=A2&EĞERSAY($A$2:$A2,A2)
- F2 hücresine aşağıdaki formülü girin ve diğer tüm hücreler için kopyala-yapıştır:
=IFNA(DÜŞEYARA($E2&SÜTÜNLER($F$1:F1),$B$2:$C$14,2,0),"")
Yukarıdaki formül, her çalışan için eğitimi listede göründüğü sırayla döndürür. Bir çalışan için listelenmiş eğitim yoksa boş döner.
Bu formül nasıl çalışır?
Yardımcı sütundaki EĞERSAY formülü, her çalışanın adını bir sayı ekleyerek benzersiz kılar. Örneğin, John'un ilk örneği John1 olur, ikinci örnek John2 olur ve bu böyle devam eder.
DÜŞEYARA formülü artık eşleşen eğitimi bulmak için bu benzersiz çalışan adlarını kullanıyor.
$E2&COLUMNS($F$1:F1) formüldeki arama değeri olduğuna dikkat edin. Bu, sütun numarasına göre çalışan adına bir sayı ekler. Örneğin, bu formül F2 hücresinde kullanıldığında, arama değeri "John1" olur. G2 hücresinde “John2” olur ve bu böyle devam eder.
Dizi Formülünü Kullanma
Yardımcı sütunlar ekleyerek orijinal veri kümesini değiştirmek istemiyorsanız, ikinci, üçüncü veya n. değeri aramak için bir dizi formülü de kullanabilirsiniz.
Aşağıda gösterilenle aynı veri setine sahip olduğunuzu varsayalım:
Doğru arama değerini döndürecek formül şudur:
=EĞERHATA(INDEX($B$2:$B$14,KÜÇÜK(EĞER($A$2:$A$14=$D2,SATIR($A$2:$A$14)-1,"")),SÜTUN($E$1 :E1)))),"")
Bu formülü kopyalayın ve E2 hücresine yapıştırın.
Bunun bir dizi formülü olduğunu ve yalnızca Enter tuşuna basmak yerine Control + Shift + Enter (Kontrol ve Shift tuşlarını basılı tutun ve Enter tuşuna basın) kullanmanız gerektiğini unutmayın.
Örnek dosyayı indirmek için buraya tıklayın.
Bu formül nasıl çalışır?
Bu formülü parçalara ayıralım ve nasıl çalıştığını görelim.
$A$2:$A$14=$D2
Formülün yukarıdaki kısmı, A2:A14'teki her hücreyi D2'deki değerle karşılaştırır. Bu veri setinde, bir hücrenin “John” adını içerip içermediğini kontrol eder.
DOĞRU YANLIŞ dizisini döndürür. Hücrenin adı 'John' ise Doğru olur, aksi takdirde Yanlış olur.
Bu örnekte alacağınız dizi aşağıdadır:
{DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ}
Veri kümesinde John adının göründüğü yerde olduğu gibi, 1., 7. ve 111. konumda DOĞRU olduğuna dikkat edin.
EĞER($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””)
Yukarıdaki EĞER formülü DOĞRU ve YANLIŞ dizisini kullanır ve DOĞRU yerine listede geçtiği konumu (SATIR($A$2:$A$14)-1 ile verilir) ve YANLIŞ'ı “” (boşluklar) ile değiştirir. Bu EĞER formülüyle elde ettiğiniz sonuç dizisi aşağıdadır:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
1, 7 ve 11'den sonraki notlar, John'un listedeki oluşum konumudur.
KÜÇÜK(EĞER($A$2:$A$14=$D2,SATIR($A$2:$A$14)-1,””),SÜTUN($E$1:E1))
SMALL işlevi şimdi bu diziden birinci en küçük, ikinci en küçük, üçüncü en küçük sayıyı seçer. Sütun numarasını oluşturmak için COLUMNS işlevini kullandığını unutmayın. E2 hücresinde, COLUMNS işlevi 1 değerini ve KÜÇÜK işlevi 1 değerini döndürür. F2 hücresinde, COLUMNS işlevi 2 değerini ve KÜÇÜK işlevi 7 değerini döndürür.
INDEX($B$2:$B$14,KÜÇÜK(EĞER($A$2:$A$14=$D2,SATIR($A$2:$A$14)-1,””)),SÜTUN($E$1:E1) ))
INDEX işlevi artık KÜÇÜK işlevi tarafından döndürülen konuma bağlı olarak Sütun B'deki listeden değeri döndürür. Bu nedenle, E2 hücresinde, B2:B14'teki ilk öğe olan 'Excel'i döndürür. F2 hücresinde, listedeki 7. öğe olan PowerPoint'i döndürür.
Bazı çalışanlar için yalnızca bir veya iki eğitimin olduğu durumlar olduğu için INDEX işlevi bir hata döndürür. EĞERHATA işlevi, hatanın yerine bir boşluk döndürmek için kullanılır.
Bu örneklerde aralık referanslarını kullandığımı unutmayın. Bununla birlikte, pratik örneklerde, verileri bir Excel Tablosuna dönüştürmek faydalıdır. Excel Tablosuna dönüştürerek, formül oluşturmayı kolaylaştıran yapılandırılmış başvuruları kullanabilirsiniz. Ayrıca, bir Excel Tablosu, listeye eklenen yeni eğitim öğelerini otomatik olarak hesaba katabilir (böylece formülleri her seferinde ayarlamanız gerekmez).
İkinci, üçüncü veya N. değere bakmanız gerektiğinde ne yaparsınız? Bunu yapmanın daha fazla yolu olduğundan eminim. Burada listelenenden daha kolay bir şey kullanırsanız, yorumlar bölümünde bizimle paylaşın.
Örnek dosyayı indirmek için buraya tıklayın.
