Videoyu İzle - Birden Çok Kriterle DÜŞEYARA İşlevini Kullanma
Excel DÜŞEYARA işlevi, temel biçiminde bir arama değeri arayabilir ve belirtilen satırdan karşılık gelen değeri döndürebilir.
Ancak çoğu zaman Excel DÜŞEYARA'yı birden çok ölçütle kullanmaya ihtiyaç vardır.
DÜŞEYARA Birden Çok Kriterle Nasıl Kullanılır
Öğrencilerin adı, sınav türü ve Matematik puanı (aşağıda gösterildiği gibi) ile ilgili bir veriniz olduğunu varsayalım:
Her öğrencinin ilgili sınav seviyeleri için Matematik puanını almak için DÜŞEYARA işlevini kullanmak zor olabilir.
Veri setini yeniden yapılandırmak veya bir Pivot Tablo kullanmak daha iyi bir seçenek olabilir. Bu senin için işe yararsa, böyle bir şey yok. Ancak çoğu durumda, sahip olduğunuz verilere takılıp kalırsınız ve pivot tablo bir seçenek olmayabilir.
Bu gibi durumlarda, bu eğitim tam size göre.
Artık birden çok kriterle DÜŞEYARA kullanarak arama değerini almanın iki yolu vardır.
- Yardımcı Sütun Kullanma.
- SEÇ işlevini kullanma.
Birden Çok Kriterli DÜŞEYARA - Yardımcı Sütun Kullanma
Excel'deki yardımcı sütunların hayranıyım.
Dizi formüllerine göre yardımcı sütunları kullanmanın iki önemli avantajını buluyorum:
- Çalışma sayfasında neler olup bittiğini anlamayı kolaylaştırır.
- Dizi işlevlerine kıyasla daha hızlı hale getirir (büyük veri kümelerinde fark edilir).
Şimdi, beni yanlış anlama. Dizi formüllerine karşı değilim. Dizi formülleriyle yapılabilecek harika şeyleri seviyorum. Sadece diğer seçeneklerin hiçbir faydası olmadığı özel durumlar için saklıyorum.
Söz konusu soruya geri dönersek, benzersiz bir niteleyici oluşturmak için yardımcı sütuna ihtiyaç vardır. Bu benzersiz niteleyici daha sonra doğru değeri aramak için kullanılabilir. Örneğin, verilerde üç Matt vardır, ancak Matt ve Unit Test veya Matt ve Mid-Term'in yalnızca bir kombinasyonu vardır.
İşte adımlar:
- B ve C sütunları arasına bir Yardımcı Sütun ekleyin.
- Yardımcı sütunda şu formülü kullanın:=A2&”|”&B2
- Bu, aşağıda gösterildiği gibi her bir örnek için benzersiz niteleyiciler yaratacaktır.
- Bu, aşağıda gösterildiği gibi her bir örnek için benzersiz niteleyiciler yaratacaktır.
- G3 =DÜŞEYARA($F3&”|”&G$2,$C$2:$D$19,2,0) içinde aşağıdaki formülü kullanın
- Tüm hücreler için kopyalayın.
Bu nasıl çalışıyor?
Bir adın ve sınavın her örneği için benzersiz niteleyiciler oluştururuz. Burada kullanılan DÜŞEYARA işlevinde, arama değeri $F3&”|”&G$2 olarak değiştirildi, böylece her iki arama kriteri birleştirildi ve tek bir arama değeri olarak kullanıldı. Örneğin, G2'deki DÜŞEYARA işlevinin arama değeri Matt|Unit Test'tir. Şimdi bu arama değeri, C2:D19'dan puan almak için kullanılıyor.
Açıklamalar:
Aklınıza gelmesi muhtemel bir kaç soru var, bu yüzden burada cevaplamaya çalışayım dedim:
- Neden kullandım | iki kritere katılırken sembolü? - Bazı istisnai olarak nadir (ancak olası) durumlarda, farklı olan ancak birleştirildiğinde aynı sonucu veren iki kriteriniz olabilir. İşte çok basit bir örnek (burada yaratıcılık eksikliğimi bağışlayın):
A2 ve A3 farklı ve B2 ve B3 farklı olsa da kombinasyonların aynı olduğunu unutmayın. Ancak bir ayırıcı kullanırsanız, kombinasyon bile farklı olacaktır (D2 ve D3).
- Yardımcı sütunu neden B ve C sütunları arasına ekledim de en soldaki değil? - Yardımcı sütunu aşırı sola yerleştirmenin bir zararı yoktur. Aslında, orijinal verilerle uğraşmak istemiyorsanız, gitmeniz gereken yol bu olmalı. DÜŞEYARA işlevinde daha az sayıda hücre kullanmamı sağladığı için yaptım. Tablo dizisinde 4 sütun yerine sadece 2 sütunla yönetebiliyordum. Ama bu sadece benim.
Şimdi herkese uyan tek bir boyut yok. Bazı kişiler DÜŞEYARA'yı birden çok kriterle kullanırken herhangi bir yardımcı sütun kullanmamayı tercih edebilir.
İşte size yardımcı olmayan sütun yöntemi.
Örnek Dosyayı İndirin
Birden Çok Kriterli DÜŞEYARA - SEÇME İşlevini Kullanma
Yardımcı sütunlar yerine dizi formüllerini kullanmak, çalışma sayfası alanından tasarruf etmenizi sağlar ve bir çalışma kitabında daha az sayıda kullanıldığında performans eşit derecede iyi olabilir.
Yukarıda kullanılan aynı veri setini göz önünde bulundurarak, size sonucu verecek formül aşağıdadır:
=DÜŞEYARA($E3&”|”&F$2,SEÇ({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2, 0)
Bu bir dizi formülü olduğundan, sadece Enter yerine Control + Shift + Enter ile kullanın.
Bu nasıl çalışıyor?
Formül ayrıca yardımcı sütun kavramını da kullanır. Aradaki fark, yardımcı sütunu çalışma sayfasına koymak yerine, onu formülün bir parçası olan sanal yardımcı veriler olarak düşünmenizdir.
Sanal yardımcı verilerle ne demek istediğimi size göstereyim.
Yukarıdaki resimde formülün SEÇ kısmını seçip F9 tuşuna bastığımda, SEÇ formülünün vereceği sonucu gösteriyor.
Sonuç {“Matt|Birim Testi”,91;”Bob|Birim Testi”, 52;… }
Virgülün aynı satırdaki sonraki hücreyi ve noktalı virgülün sonraki sütunda aşağıdaki verileri temsil ettiği bir dizidir. Bu nedenle, bu formül 2 sütun veri oluşturur - Bir sütun benzersiz tanımlayıcıya ve bir puana sahiptir.
Şimdi, DÜŞEYARA işlevini kullandığınızda, yalnızca ilk sütundaki (bu sanal 2 sütun verisinin) değerini arar ve karşılık gelen puanı döndürür.
Örnek Dosyayı İndirin
Birden çok ölçütle (INDEX/MATCH veya SUMPRODUCT gibi) bir arama yapmak için başka formüller de kullanabilirsiniz.
Bunu yapmanın başka bir yolu var mı? Evet ise, yorumlar bölümünde benimle paylaşın.
Aşağıdaki ARAMA Öğreticilerini de Beğenebilirsiniz:
- DÜŞEYARA Vs. İNDEKS/MAÇ
- Tek Bir Hücrede Tekrarlamadan Birden Çok Arama Değeri Alın.
- DÜŞEYARA Büyük/Küçük Harfe Duyarlı nasıl yapılır.
- #YOK Hatalarından Kurtulmak için DÜŞEYARA ile EĞERHATA kullanın.
