Ron bir sürü DÜŞEYARA yapmak ve sonuçları toplamak istiyor. Bu soruna tek formüllü bir çözüm var.
Ron soruyor:
Her bir arama yapmadan tüm DÜŞEYARA'ları nasıl toplayabilirsiniz?
Birçok insan aşinadır:
=VLOOKUP(B4,Table,2,True)
DÜŞEYARA'nın yaklaşık eşleşme sürümünü yapıyorsanız (dördüncü bağımsız değişken olarak Doğru'u belirtirsiniz), ARA da yapabilirsiniz.
Arama tuhaftır çünkü tablodaki son sütunu döndürür. Bir sütun numarası belirtmezsiniz. Tablonuz E4'ten J8'e kadar çalışıyorsa ve G sütunundaki sonucu istiyorsanız, arama tablosu olarak E4: G4'ü belirtirsiniz.
Diğer bir fark: DÜŞEYARA'da yaptığınız gibi Doğru / Yanlış'ı dördüncü bağımsız değişken olarak belirtmezsiniz: ARA işlevi her zaman DÜŞEYARA'nın Yaklaşık Eşleşme sürümünü yapar.
Neden bu eski işlevle uğraşıyorsunuz? Çünkü Lookup'ın özel bir numarası vardır: Tüm değerleri aynı anda arayabilir ve onları toplar. İlk bağımsız değişken olarak B4 gibi tek bir değer iletmek yerine, tüm değerlerinizi belirtebilirsiniz =LOOKUP(B4:B17,E4:F8). Bu 14 farklı değer döndüreceğinden, işlevi =SUM( LOOKUP(B4:B17,E4:F8))veya =COUNT(LOOKUP(B4:B17,E4:F8))veya gibi bir sarmalayıcı işlevine sarmanız gerekir =AVERAGE( LOOKUP(B4:B17,E4:F8)). Unutmayın, bir Sarmalayıcı işlevine ihtiyacınız var, ancak bir rapçi işlevine ihtiyacınız yok. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))çalışmayacak.
Kaçınmak isteyeceğiniz gerçekten yaygın bir hata var. Formülü yazdıktan veya düzenledikten sonra Enter tuşuna basmayın! Bunun yerine, bu üç parmak klavye numarasını yapın. Ctrl ve Shift tuşlarını basılı tutun. Ctrl ve Shift tuşlarını basılı tutarken Enter tuşuna basın. Artık Ctrl ve Shift tuşlarını bırakabilirsiniz. Buna Ctrl + Shift + Enter diyoruz, ancak gerçekten doğru zamanlanması gerekiyor: Ctrl + Shift, Enter tuşuna basın, Ctrl + Shift tuşlarını basılı tutun. Doğru bir şekilde yaptıysanız formül, küme ayraçları ile çevrili formül çubuğunda görünecektir:(=SUM(LOOKUP(B4:B17,E4:F8)))
Kenar notu
ARA, YATAYARA eşdeğerini de yapabilir. Arama tablonuz uzunluğundan daha genişse, ARA YATAYARA'ya geçecektir. Beraberlik durumunda… 8x8 veya 10x10 olan bir tablo, LOOKUP tabloyu dikey olarak değerlendirecektir.
Aşağıdaki videoyu izleyin veya bu ekran görüntüsünü inceleyin.
Video izle
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2184: Tüm Aramaları Topla.
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Ron'un eski, eski ARA programını kullanmakla ilgili sorusu bugün. Ve bu, Excel 2016 In Depth kitabımdan.
Diyelim ki burada bir sürü ürünümüz vardı ve bir Arama tablosu kullanmak zorunda kaldık. Ve her ürün için, biliyorsunuz, Arama tablosundaki değeri alıp toplamamız gerekiyordu. Tipik yol, bu tablodaki bu ürün için bir DÜŞEYARA-- = DÜŞEYARA kullanırız. F4'e basacağım, onu kilitleyeceğim ve ikinci değerle. Ve bu özel durumda, aradığımız her bir değer tabloda olduğundan ve tablo sıralandığından, DOĞRU kullanmak güvenlidir. Normalde, DOĞRU kullanmam ama bu bölümde DOĞRU kullanacağız. Yani tüm bu değerleri alıyorum ve sonra burada, Alt + =, bunların toplamını alıyoruz, değil mi? Peki ya tüm amacımız 1130'u almaksa? Tüm bu değerlere ihtiyacımız yok. Sadece bu sonuca ihtiyacımız var.
Pekala, şimdi, Visical'in günlerinden beri var olan, LOOKUP adlı eski, eski bir işlev var. DÜŞEYARA değil. YATAYARA değil, sadece ARA. Görünüşe göre, ilk başta DÜŞEYARA'ya benzer - aradığınız değeri ve arama tablosunu belirlersiniz, F4'e basın, ama sonra bitirdik. Hangi sütunu belirtmemize gerek yok çünkü ARA tablodaki son sütuna gider. Yedi sütunlu bir tablonuz varsa ve dördüncü değeri aramak istiyorsanız, yalnızca birden dörde kadar olan sütunları belirtirsiniz. Peki? Ve son sütun her neyse, bakacağı şey de bu. Ve FALSE veya TRUE belirtmemiz gerekmez çünkü her zaman TRUE kullanır; YANLIŞ sürüm yok. Peki?
Bu yüzden anlamalısınız, eğer bir DÜŞEYARA yapıyorsanız, sonunda her zaman YANLIŞ kullanırım, ancak bu durumda bu kısa bir liste - listedeki her şeyin tabloda olduğunu biliyoruz. Eksik hiçbir şey yok ve masa sıralandı. Peki? Bu bize DÜŞEYARA için elde ettiğimiz sonucun aynısını verecektir.
Harika, bunu kopyalamak istiyorum: Alt + =. Peki. Ancak bu bize hiçbir şey kazandırmaz çünkü yine de tüm formülleri ve sonra SUM işlevini yerleştirmemiz gerekir. Güzel olan şey, ARA işlevinin DÜŞEYARA'nın yapamayacağı bir numara yapması, tamam mı? Ve bu, tüm aramaları aynı anda yapmaktır. Öyleyse, bunu TOPLA işlevine gönderdiğim yer, ARA dediğimde, arama öğesi nedir? Tüm bunları aramak istiyoruz, virgül ve sonra işte tablo - ve F4'e basmamıza gerek yok, çünkü bunu hiçbir yere kopyalamayacağız, sadece bir formül var - ARAMA'yı kapat, kapat toplam.
Pekala, şimdi, işlerin alt üst olabileceği yer burası: Sadece burada Enter tuşuna basarsanız, 60 alırsınız, tamam mı? Çünkü sadece ilkini yapacak. Yapmanız gereken şey sihirli üç tuşa basmaktır ve bu Ctrl + Shift - Sol elimle Ctrl + shift tuşunu basılı tutuyorum, bunları basılı tutmaya devam ediyorum ve sağ elimle ENTER tuşuna basıyorum ve DÜŞEYARA'nın tüm matematiğini yapacak. Bu harika değil mi? Buradaki formül çubuğunda veya formül metninde, etrafına kaşlı ayraçlar koyduğuna dikkat edin. Bu süslü parantezleri yazmazsınız, Excel, "Hey, bunun için Ctrl + Shift + Enter tuşlarına bastınız" demek için bu süslü parantezleri içine koyar.
Şimdi, hey, bu konu ve diğer birçok konu bu kitapta: Power Excel with, 2017 baskısı. Kitap hakkında daha fazla bilgi edinmek için sağ üst köşedeki "I" simgesine tıklayın.
Bugün Ron'un sorusu: Tüm DÜŞEYARA'ları nasıl toplayabilirsiniz? Artık çoğu insan, arama değerini, tabloyu, hangi sütunu ve ardından DOĞRU veya YANLIŞ'ı belirttiğiniz DÜŞEYARA'yı biliyor. Ve eğer - uygunsanız - DÜŞEYARA'nın DOĞRU sürümünü yapıyorsanız. o zaman bu eski ARAMA'yı da yapabilirsiniz. Tuhaftır, çünkü tablonun son sütununu döndürür, sütun numarasını belirtmezsiniz ve DOĞRU veya YANLIŞ demezsiniz. Her zaman DOĞRUDUR. Bunu neden kullanalım? Çünkü özel bir numarası var: Tüm arama değerlerini bir kerede yapabilirsiniz ve bunları toplar. Bu formülü yazdıktan sonra Ctrl + Shift + Enter tuşlarına basmanız gerekir, aksi takdirde çalışmaz. Ve sonra çıkışta, size LOOKUP için başka bir numara göstereceğim.
Pekala, hey, bu soruyu gönderdiği için Ron'a teşekkür etmek ve uğradığın için teşekkür etmek istiyorum. Gelecek sefer başka bir internet yayını için görüşürüz.
Pekala, biz burada LOOKUP'tan bahsederken, ARA işlevinin yapabileceği diğer şey: Biliyorsunuz, bunun gibi dikey bir tablo için DÜŞEYARA veya bunun gibi yatay bir tablo için YATAYARA var; ARAMA her iki şekilde de gidebilir. bu yüzden bu tabloda D değerini = LOOKUP yapmak istediğimizi söyleyebiliriz ve tablo uzun olduğundan daha geniş olduğu için LOOKUP otomatik olarak HLOOKUP sürümüne geçer, değil mi? Bu durumda, 3 satıra 5 sütun belirttiğimiz için, YATAYARA yapacak. Ve buradaki son satır sayılar olduğu için bize o sayıyı getirecek. Yani D var, Tarih bize 60 veriyor. Pekala. Yalnızca 12. satıra giden bir tablo belirtirsem, bunun yerine ürünün adını alırım. Peki? Yani bu biraz ilginç küçük bir işlev. Sanırım Excel Yardımı, "Hey, bu işlevi kullanma" derdi, ama orada "Bu işlevi kullanabileceğiniz belirli zamanlar.
Başlık Fotoğrafı: grandcanyonstate / Pixabay
