DÜŞEYARA güçlü bir işlevdir. Ancak, DÜŞEYARA'nın eşleşen tüm değerleri döndüreceğini bilmek isteyen birinden Power Excel seminerlerimden birinde sık sık bir soru alıyorum. Bildiğiniz gibi, dördüncü argüman olarak False olan DÜŞEYARA her zaman bulduğu ilk eşleşmeyi döndürür. Aşağıdaki ekran görüntüsünde, F2 hücresi 3623'ü döndürür çünkü bu, J1199 işi için bulunan ilk eşleşmedir.
O halde soru DÜŞEYARA tüm eşleşmeleri döndürebilir mi?
DÜŞEYARA olmayacak. Ancak diğer işlevler yapabilir.
J1199 işindeki tüm maliyetleri toplamak istiyorsanız =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Metin değerleriniz varsa ve tüm sonuçları tek bir değerde birleştirmek istiyorsanız, kullanabilirsiniz =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Bu formül yalnızca Office 365 ve Excel 2019'da çalışır.
Veya, tek bir iş için tüm sonuçları yeni bir çalışma sayfası aralığına döndürmeniz gerekebilir. =FILTER(B2:C53,A2:A53=K1,"None Found")2019'da Office 365'e gelen yepyeni bir işlev sorunu çözecek:
Bazen insanlar tüm DÜŞEYARA işlevlerini gerçekleştirmek ve toplamak ister. Arama tablonuz sıralanırsa, kullanabilirsiniz =SUM(LOOKUP(B2:B53,M3:N5)).
DÜŞEYARA'nın Tam Eşleşme sürümü ile tüm DÜŞEYARA'ları toplamanız gerekiyorsa, kullanmak için Dinamik Dizilere erişiminizin olması gerekir =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Dinamik Diziler hakkında daha fazla bilgi edinmek için Doğrudan Noktaya Doğru Excel Dinamik Dizileri konusuna bakın.
Video izle
Video Metni
Excel'i Podcast'ten Öğrenin Bölüm 2247: Tüm VLookUp Değerlerini Geri Verebilir misiniz?
Hey. İnternet yayınına tekrar hoş geldiniz. Ben Bill Jelen. Geçen hafta Wisconsin, Appleton'daki seminerimde iki soru geldi - ikisi de birbiriyle ilgili. Hey, tüm DÜŞEYARA'ları nasıl döndürürüz dediler, tamam mı? Bu durumda, J1199'un bir sürü kibriti olduğu gibi ve onlar, bilirsiniz, hepsini geri vermek istiyorlar ve birisi bana bunu sorduğunda ilk sorum şu, peki, kibritlerle ne yapmak istiyorsunuz? Eklemek istediğiniz sayılar mı yoksa birleştirmek istediğiniz metin mi? Ve bu komik. Aynı seminerdeki iki soruyu bir kişi eklemek istedi, diğeri sonuçları birleştirmek istedi.
Öyleyse ikisine de bir göz atalım. Metnin sonucunu görmek istiyorsanız diğerine atlayabileceğiniz bir içindekiler tablosu için YouTube açıklamasına bakın.
Tamam, ilk olarak, hepsini toplamak istiyorsak, bir DÜŞEYARA kullanmayacağız. Bu öğeyle eşleşen her şeyi toplayacak SUMIF veya SUMIFS adlı bir işlev kullanacağız. Yani, SUMIFS. İşte toplamak istediğimiz sayısal değerler ve bunu kilitlemek için F4'e basacağım. Bu şekilde, bunu aşağıya kopyaladığımda, aynı aralığı göstermeye devam edecek ve sonra kontrol etmek ve A sütunundaki JOB numarasının, yine orada F4'ün = solumuzdaki değer olup olmadığını kontrol etmek istiyoruz. - bu durumda E2 - ve bunu kopyaladığımızda, her öğe için TOTAL'i göreceğiz. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Burada biraz kontrol yapalım. J1199. Toplam 25365. Pekala. Yani, bu çalışıyor. Sayılarsa ve tüm sayıları alıp toplamak istiyorsanız, SUMIF veya SUMIFS'e geçin, ancak metinse tamam, şimdi bu işlev Şubat 2017'de Office 365'te yenidir. Yani, Excel 2016'nız varsa veya Excel 2013 veya Excel 2010 veya daha eski olanlardan herhangi biri, bu işleve sahip olmayacaksınız. Bu, METİNBİRLEŞTİR adlı bir işlevdir. METİNBİRLEŞTİR. Bu, 2018'de Ignite'a tüm bu harika dinamik dizi formüllerini getiren bir başka işlev (Joe McDade - 01:50) ve Joe, TEXTJOIN'in dizilerle çalışacağından emin oldu ki bu gerçekten harika.
Yani, buradaki sınırlayıcı, SPACE, EMPTY'yi kesinlikle yok sayacak. Burada BOŞALT'ı görmezden gelmek istiyoruz çünkü bu sonraki kısımda, EĞER ifadesinde çok fazla boşluk üreteceğiz. EĞER A2, F4 üzerindeki öğe, buradaki JOB numarasına = ise, o zaman C, F4 sütunundaki ilgili öğeyi istiyorum, aksi takdirde, böyle "" istiyorum. Bu IF ifadesini kapatın. METİN BİRLEŞTİRME'yi kapatın. CONTROL + SHIFT + ENTER tuşlarına basmam gerekir mi? Hayır. Bana bununla eşleşen tüm ürünleri getiriyor, tamam mı? Yani, tüm DÜŞEYARA döndürmek, onları toplamak istiyorsak, evet, onları birleştirmek istiyorsak, evet. (= METİNBİRLEŞTİR (",", Doğru, EĞER ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
Pekala, şimdi, burada insanlar bana tüm DÜŞEYARA'ları geri getirip getiremeyeceklerini sorduğunda bir olasılık daha var. Bu maliyetlerin her birine buradan bakıp ELLEÇLEME MALİYETİNİ hesaplamak ve ardından hepsini özetlemek istediğimiz bir sorun olabilir. Mesela buraya bir DÜŞEYARA ve buraya bir DÜŞEYARA ve buraya bir DÜŞEYARA ve buraya bir DÜŞEYARA eklemek istemiyorum. Sadece hepsini bir arada yapmak istiyorum ve bu durumda TOPLA işlevini ve ardından eski, eski ARA işlevini kullanacağız. LOOKUP, tüm bu değerleri B sütununda arayacağımızı söylüyor. Burada F4'e ihtiyacım yok çünkü onu hiçbir yere kopyalamıyorum. ,. İşte arama tablomuz. ), TOPLA'yı kapatın ve dışarı çıkar ve her bir DÜŞEYARA gerçekleştirir ve ardından hepsini bu şekilde toplar. (= TOPLA (ARA (B2: B53, K3: L5)))
Peki, merhaba. Tüm bu konular kitabım LIV: The 54 Greatest Tips Of All Time. Daha fazla bilgi edinmek için sağ üst köşedeki i tıklayın.
Öyleyse soru, tüm DÜŞEYARA'ları döndürebilir misiniz? Şey, bir çeşit, ama aslında DÜŞEYARA kullanmıyor. Çözmek için SUMIF, TEXTJOIN veya SUM veya LOOKUP kullanacağız.
Peki, merhaba. Uğradığın için teşekkür ederim. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Pekala, bir haftadır bu dinamik dizilerden bahsediyorum. Dinamik dizilere değinmediğim bir video yapmak istedim çünkü pek çok insanın henüz onlara sahip olmadığını biliyorum, ama işte buradayız. Çıkışı. Biliyorsun bunlar alfabetik değil. Bunları sıralayabilirsek bu çok daha iyi olurdu ve eğer yeni dinamik dizilere sahipseniz, bunu SORT işlevine gönderebilirsiniz, SIRALA bu şekilde ve ENTER tuşuna basın ve şimdi sonuçlar bu şekilde sıralanacaktır.
Biliyorsunuz, bu formül bile dinamik dizilerle daha iyi hale gelebilir. Arama, DOĞRU kullanmanızı gerektirir. Ya bir, YANLIŞ'ı kullanmak isteseydiniz? Bunu bir DÜŞEYARA olarak değiştirebiliriz, tüm bu metni bu tabloya bakabiliriz, 2,. Bu durumda TRUE kullanacağım ama başka bir durumda FALSE kullanabilirsiniz. CONTROL + SHIFT + ENTER. Hayır. Sadece işe yarayacak, tamam mı? (= TOPLA (DÜŞEYARA (B2: B53, K3: L5,2, Doğru)))
2019'un başlarında ortaya çıkan Dinamik Diziler pek çok sorunu çözecek.
Burada buluştuğunuz için teşekkürler. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Excel Dosyasını İndirin
Excel dosyasını indirmek için: can-you-return-all-vlookup-values.xlsx
Birisi "DÜŞEYARA tüm eşleşmeleri döndürebilir mi diye sorduğunda cevap Hayırdır. Ancak, esasen aynı şeyi yapabilen başka birçok işlev vardır.
Excel Günün Düşüncesi
Excel Master arkadaşlarıma Excel hakkında tavsiyelerini sordum. Bugünün düşüncesi:
"Başkalarının verilerini sizin için normalleştirmesini sağlayacağınız gibi verilerinizi normalleştirin"
Kevin Lehrbass
