Ron chce zrobić kilka WYSZUKAJ.PIONOWO i zsumować wyniki. Istnieje jednoformatowe rozwiązanie tego problemu.
Ron pyta:
Jak zsumować wszystkie WYSZUKAJ.PIONOWO bez przeprowadzania każdego pojedynczego wyszukiwania?
Wiele osób zna:
=VLOOKUP(B4,Table,2,True)
Jeśli wykonujesz przybliżoną wersję dopasowania WYSZUKAJ.PIONOWO (gdzie jako czwarty argument określasz True), możesz również wykonać WYSZUKAJ.
Wyszukiwanie jest dziwne, ponieważ zwraca ostatnią kolumnę w tabeli. Nie określasz numeru kolumny. Jeśli twoja tabela biegnie od E4 do J8 i chcesz uzyskać wynik z kolumny G, określ E4: G4 jako tabelę przeglądową.
Kolejna różnica: nie określasz Prawda / Fałsz jako czwarty argument, tak jak w przypadku WYSZUKAJ.PIONOWO: funkcja WYSZUKAJ zawsze wykonuje przybliżoną zgodność wersji WYSZUKAJ.PIONOWO.
Po co zawracać sobie głowę tą starożytną funkcją? Ponieważ Lookup ma specjalną sztuczkę: możesz wyszukać wszystkie wartości naraz, a on je zsumuje. Zamiast przekazywać pojedynczą wartość, taką jak B4, jako pierwszy argument, możesz określić wszystkie wartości =LOOKUP(B4:B17,E4:F8). Ponieważ zwróciłoby to 14 różnych wartości, musisz opakować funkcję w funkcję opakowania, taką jak =SUM( LOOKUP(B4:B17,E4:F8))lub =COUNT(LOOKUP(B4:B17,E4:F8))lub =AVERAGE( LOOKUP(B4:B17,E4:F8)). Pamiętaj, że potrzebujesz funkcji Wrapper, ale nie funkcji rapera. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))nie zadziała.
Jest naprawdę powszechny błąd, którego będziesz chciał uniknąć. Po wpisaniu lub edycji formuły nie naciskaj klawisza Enter! Zamiast tego zrób tę sztuczkę z klawiaturą trzema palcami. Przytrzymaj Ctrl i Shift. Trzymając wciśnięte klawisze Ctrl i Shift, naciśnij klawisz Enter. Możesz teraz zwolnić Ctrl i Shift. Nazywamy to Ctrl + Shift + Enter, ale tak naprawdę musi to być poprawnie ustawione w czasie: naciśnij i przytrzymaj Ctrl + Shift, naciśnij Enter, zwolnij Ctrl + Shift. Jeśli zrobiłeś to poprawnie, formuła pojawi się na pasku formuły w nawiasach klamrowych:(=SUM(LOOKUP(B4:B17,E4:F8)))
Dygresja
WYSZUKAJ. Może również wykonać odpowiednik WYSZUKAJ. Jeśli tabela przeglądowa jest szersza niż wysoka, funkcja WYSZUKAJ przełączy się na WYSZUKAJ. W przypadku remisu… stołu o wymiarach 8x8 lub 10x10, LOOKUP potraktuje stół jako pionowy.
Obejrzyj poniższy film lub przestudiuj ten zrzut ekranu.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2184: Sumuj wszystkie wyszukiwania.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie od Rona dotyczące używania starego, starego programu LOOKUP. A to z mojej książki „Excel 2016 In Depth”.
Powiedzmy, że mamy tutaj kilka produktów i musieliśmy użyć tabeli odnośników. I dla każdego produktu musieliśmy, wiesz, pobrać wartość z tabeli odnośników i zsumować ją. Cóż, typowym sposobem jest użycie funkcji WYSZUKAJ.PIONOWO-- = WYSZUKAJ.PIONOWO dla tego produktu w tej tabeli. Nacisnę F4, zablokuję to i drugą wartość. W tym konkretnym przypadku, ponieważ każda szukana przez nas wartość znajduje się w tabeli, a tabela jest posortowana, można bezpiecznie użyć wartości TRUE. Normalnie nigdy nie użyłbym TRUE, ale w tym odcinku użyjemy TRUE. Więc otrzymuję wszystkie te wartości, a następnie tutaj, Alt + =, otrzymujemy je w sumie, prawda? Ale co, jeśli naszym celem jest tylko zdobycie 1130? Nie potrzebujemy wszystkich tych wartości. Potrzebujemy tylko tego wyniku.
Cóż, okej, istnieje stara, stara funkcja, która istnieje od czasów Visical, o nazwie LOOKUP. Nie WYSZUKAJ.PIONOWO. Nie HLOOKUP, tylko WYSZUKAJ. Na początku wygląda to podobnie do funkcji WYSZUKAJ.PIONOWO - określasz, jaką wartość szukasz i tabelę przeglądową, naciśnij F4, ale potem skończymy. Nie musimy określać, która kolumna, ponieważ WYSZUKAJ przechodzi po prostu do ostatniej kolumny w tabeli. Jeśli masz tabelę z siedmioma kolumnami i chcesz wyszukać czwartą wartość, po prostu określ kolumny od pierwszej do czwartej. W porządku? I tak, niezależnie od tego, jaka jest ostatnia kolumna, właśnie to będzie wyglądać. I nie musimy określać FALSE lub TRUE, ponieważ zawsze używa TRUE; nie ma wersji FAŁSZ. W porządku?
Musisz więc zrozumieć, że jeśli robisz WYSZUKAJ.PIONOWO, zawsze używam na końcu FAŁSZ, ale w tym przypadku jest to krótka lista - wiemy, że wszystko na liście znajduje się w tabeli. Nic nie brakuje, a tabela jest posortowana. W porządku? Dzięki temu uzyskamy dokładnie taki sam wynik, jaki mamy dla WYSZUKAJ.PIONOWO.
Super, chcę to skopiować: Alt + =. W porządku. Ale to nam nic nie daje, ponieważ nadal musimy wstawić wszystkie formuły, a następnie funkcję SUMA. Piękną rzeczą jest to, że LOOKUP może zrobić sztuczkę, której WYSZUKAJ.PIONOWO nie może zrobić, dobrze? A to oznacza wykonanie wszystkich wyszukiwań naraz. Tak więc, gdy wysyłam to do funkcji SUMA, kiedy mówię WYSZUKAJ, jaki jest element wyszukiwania? Chcemy wyszukać wszystkie te rzeczy, przecinek, a następnie mamy tabelę - i nie musimy naciskać F4, ponieważ nie zamierzamy tego nigdzie kopiować, jest tylko jedna formuła - zamknij LOOKUP, zamknij Suma.
W porządku, teraz, oto miejsce, w którym wszystko może się schrzanić: Jeśli po prostu naciśniesz tutaj Enter, otrzymasz 60, dobrze? Ponieważ będzie to pierwsze. To, co musisz zrobić, to przytrzymać magiczne trzy naciśnięcia klawiszy, a to jest Ctrl + Shift - przytrzymuję Ctrl + Shift lewą ręką, wciąż je przytrzymuję, a prawą ręką wciskam ENTER i wykona całą matematykę funkcji WYSZUKAJ.PIONOWO. Czy to nie jest niesamowite? Zwróć uwagę, że na pasku formuły powyżej lub w tekście formuły jest on umieszczony wokół niego w nawiasy klamrowe. Nie wpisujesz tych nawiasów klamrowych, Excel umieszcza te nawiasy, aby powiedzieć: „Hej, w tym celu nacisnąłeś Ctrl + Shift + Enter”.
Teraz, hej, ten temat i wiele innych tematów znajduje się w tej książce: Power Excel z, wydanie 2017. Kliknij „Ja” w prawym górnym rogu, aby przeczytać więcej o książce.
Dziś pytanie od Rona: Jak zsumować wszystkie WYSZUKAJ.PIONOWO? Teraz większość ludzi zna funkcję WYSZUKAJ.PIONOWO, w której określasz wartość wyszukiwania, tabelę, kolumnę, a następnie PRAWDA lub FAŁSZ. A jeśli robisz - jeśli kwalifikujesz się - PRAWDZIWA wersja WYSZUKAJ.PIONOWO. możesz też zrobić to stare WYSZUKAJ. To nieparzyste, ponieważ zwraca ostatnią kolumnę tabeli, nie podajesz numeru kolumny i nie mówisz PRAWDA ani FAŁSZ. To zawsze PRAWDA. Dlaczego mielibyśmy tego używać? Ponieważ ma specjalną sztuczkę: możesz wykonać wszystkie wyszukiwania na raz, a on je zsumuje. Musisz nacisnąć Ctrl + Shift + Enter po wpisaniu tej formuły lub nie zadziała. A na koniec pokażę ci kolejną sztuczkę do LOOKUP.
Cóż, hej, chcę podziękować Ronowi za wysłanie tego pytania i chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
W porządku, skoro mówimy tutaj o funkcji WYSZUKAJ, inna rzecz, którą funkcja WYSZUKAJ może zrobić: Wiesz, mamy WYSZUKAJ.PIONOWO dla pionowego stołu takiego jak ten lub WYSZUKAJ.PIONOWO dla takiego poziomego stołu; WYSZUKAJ może działać w obie strony. więc możemy powiedzieć hej, chcemy = WYSZUKAJ tę wartość, D, w tej tabeli, a ponieważ stół jest szerszy niż wysoki, funkcja LOOKUP automatycznie przełącza się na wersję HLOOKUP, prawda? Więc w tym przypadku, ponieważ określamy 3 wiersze na 5 kolumn, wykona HLOOKUP. A ponieważ ostatni wiersz tutaj zawiera liczby, przyniesie nam tę liczbę. Więc mamy D, Date, daje nam 60. W porządku. Gdybym określił tabelę, która trafiła tylko do wiersza 12, zamiast tego otrzymam nazwę produktu. W porządku? Jest to więc interesująca mała funkcja. Myślę, że Pomoc programu Excel zwykła mówić: „Hej, nie używaj tej funkcji”, ale takw określonych sytuacjach, w których można użyć tej funkcji.
Zdjęcie tytułowe: grandcanyonstate / pixabay
