WYSZUKAJ.PIONOWO to potężna funkcja. Ale często otrzymuję pytanie na jednym z moich seminariów Power Excel od kogoś, kto chce wiedzieć, czy WYSZUKAJ.PIONOWO może zwrócić wszystkie pasujące wartości. Jak wiesz, WYSZUKAJ.PIONOWO z Fałszem jako czwartym argumentem zawsze zwróci pierwsze znalezione dopasowanie. Na poniższym zrzucie ekranu komórka F2 zwraca 3623, ponieważ jest to pierwsze dopasowanie znalezione dla zadania J1199.
Pytanie zatem, czy WYSZUKAJ.PIONOWO może zwrócić wszystkie dopasowania?
WYSZUKAJ.PIONOWO nie będzie. Ale inne funkcje mogą.
Jeśli chcesz zsumować wszystkie koszty z zadania J1199, użyj =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Jeśli masz wartości tekstowe i chcesz połączyć wszystkie wyniki w jedną wartość, możesz użyć =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Ta formuła działa tylko w Office 365 i Excel 2019.
Lub może być konieczne zwrócenie wszystkich wyników dla jednego zadania do nowego zakresu arkusza. Zupełnie nowa =FILTER(B2:C53,A2:A53=K1,"None Found")funkcja, która pojawi się w Office 365 w 2019 roku, rozwiąże problem:
Czasami ludzie chcą wykonać wszystkie VLOOKUP i zsumować je. Jeśli twoja tabela przeglądowa jest posortowana, możesz użyć =SUM(LOOKUP(B2:B53,M3:N5)).
Jeśli chcesz zsumować wszystkie WYSZUKAJ.PIONOWO z wersją WYSZUKAJ.PIONOWO z dopasowaniem ścisłym, aby z niej skorzystać, musisz mieć dostęp do tablic dynamicznych =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Aby dowiedzieć się więcej o tablicach dynamicznych, zapoznaj się z tematem Tablice dynamiczne programu Excel prosto do punktu.
Obejrzyj wideo
Transkrypcja wideo
Learn Excel From, Podcast Episode 2247: Czy możesz zwrócić wszystkie wartości VLookUp?
Hej. Witamy ponownie w netcast. Jestem Bill Jelen. Dwa pytania pojawiły się podczas mojego seminarium w Appleton w stanie Wisconsin w zeszłym tygodniu - oba powiązane. Powiedzieli, hej, jak zwrócimy wszystkie WYSZUKAJ.PIONOWO, dobrze? W tym przypadku, jak J1199, ma kilka zapałek, a oni, no wiesz, chcą je wszystkie zwrócić, a moje pierwsze pytanie, gdy ktoś mnie zapyta, brzmi: cóż, co chcesz zrobić z zapałkami? Czy są to liczby, które chcesz dodać, czy jest to tekst, który chcesz połączyć? To zabawne. Dwa pytania z tego samego seminarium, jedna osoba chciała je zsumować, a druga chciała połączyć wyniki.
Przyjrzyjmy się więc obu z nich. Sprawdź w opisie YouTube, aby znaleźć spis treści, w którym możesz przejść do drugiego, jeśli chcesz zobaczyć wynik tekstu.
OK, więc po pierwsze, jeśli chcemy je wszystkie dodać, w ogóle nie będziemy używać funkcji WYSZUKAJ.PIONOWO. Użyjemy funkcji o nazwie SUMA.JEŻELI lub SUMA.WARUNKÓW, która zsumuje wszystko, co pasuje do tej pozycji. Więc SUMIFS. Oto wartości liczbowe, które chcemy zsumować i nacisnę F4, aby je zablokować. W ten sposób, kiedy to skopiuję, będzie wskazywał ten sam zakres, a następnie będziemy chcieli sprawdzić i zobaczyć, czy numer JOB w kolumnie A, znowu F4, jest = do wartości po lewej stronie nas - w tym przypadku E2 - i kiedy skopiujemy to, zobaczymy SUMA dla każdego elementu. (SUMIFS (2 $ B $: 53 $ B $, 2 $ A $: 53 $ A $, E2))
Sprawdźmy tutaj. J1199. Łącznie 25365. W porządku. Więc to działa. Jeśli to liczby i chcesz uzyskać wszystkie liczby i dodać je, przełącz się na SUMA.JEŻELI lub SUMA.WARUNKÓW, ale jeśli jest to tekst, w porządku, teraz ta funkcja jest nowa w Office 365 w lutym 2017 r. Jeśli więc masz Excel 2016 lub Excel 2013 lub Excel 2010 lub którykolwiek z tych starszych, nie będziesz mieć tej funkcji. To funkcja o nazwie TEXTJOIN. TEXTJOIN. To kolejna funkcja od (Joe McDade - 01:50), który właśnie przyniósł nam wszystkie te wspaniałe dynamiczne formuły tablicowe na Ignite w 2018 roku, a Joe upewnił się, że TEXTJOIN będzie działał z tablicami, co jest naprawdę świetne.
Tak więc ogranicznikiem będzie tutaj SPACJA, zdecydowanie zignoruj PUSTY. Chcemy tutaj zignorować EMPTY, ponieważ zamierzamy wygenerować wiele pustych wartości w następnej części, instrukcji IF. JEŚLI ten element nad A2, F4 jest = do tego numeru JOB tutaj, to chcę odpowiadającego elementu z kolumny C, F4, w przeciwnym razie chcę „” w ten sposób. Zamknij tę instrukcję IF. Zamknij TEXTJOIN. Czy muszę naciskać CONTROL + SHIFT + ENTER? Nie ja nie. Daje mi wszystkie produkty, które pasują do tego, dobrze? Więc zwracając wszystkie WYSZUKAJ.PIONOWO, jeśli chcemy je zsumować, tak, jeśli chcemy je połączyć, tak. (= TEXTJOIN („,”, True, IF (2 AUD 2: 53 AUD = E2, 2 C $: 53 C $, „”)))
W porządku, teraz jest jeszcze jedna możliwość, kiedy ludzie pytają mnie, czy mogą zwrócić wszystkie WYSZUKAJ.PIONOWO. Może to być problem, w którym chcemy sprawdzić każdy z tych kosztów tutaj i obliczyć KOSZT OBSŁUGI, a następnie podsumować je wszystkie. Na przykład nie chcę umieszczać WYSZUKAJ.PIONOWO tutaj, WYSZUKAJ.PIONOWO tutaj, WYSZUKAJ.PIONOWO tutaj i WYSZUKAJ.PIONOWO tutaj. Chcę tylko zrobić je w całości iw takim przypadku użyjemy funkcji SUMA, a następnie starej, starej funkcji WYSZUKAJ. LOOKUP mówi, że będziemy szukać wszystkich tych wartości w kolumnie B. Nie potrzebuję tutaj F4, ponieważ nigdzie go nie kopiuję. ,. Oto nasza tabela przeglądowa. ), zamyka SUMA, wychodzi i wykonuje poszczególne WYSZUKAJ.PIONOWO, a następnie sumuje je wszystkie w ten sposób. (= SUMA (WYSZUKAJ (B2: B53; K3: L5)))
Więc hej. Wszystkie te tematy to moja książka LIV: The 54 Greatest Tips Of All Time. Kliknij ja w prawym górnym rogu, aby dowiedzieć się więcej.
Więc pytanie brzmi: czy możesz zwrócić wszystkie WYSZUKAJ.PIONOWO? Cóż, w pewnym sensie, ale nie używam WYSZUKAJ.PIONOWO. Zamierzamy użyć SUMIF, TEXTJOIN, SUM lub LOOKUP, aby go rozwiązać.
Więc hej. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Wiesz, w porządku, mówię o tych dynamicznych tablicach od tygodnia. Chciałem zrobić jeden film, w którym nie dotykałem tablic dynamicznych, ponieważ wiem, że wiele osób jeszcze ich nie ma, ale oto jesteśmy. To ucieczka. Wiesz, to nie jest alfabetyczne. Byłoby o wiele lepiej, gdybyśmy mogli je posortować, a jeśli zdarzy się, że masz nowe tablice dynamiczne, możesz wysłać to do funkcji SORT, SORT w ten sposób i nacisnąć ENTER, a teraz wyniki zostaną posortowane w ten sposób.
Wiesz, nawet ta formuła mogłaby być lepsza dzięki dynamicznym tablicom. Wyszukiwanie wymaga użycia TRUE. A co by było, gdybyś chciał użyć FALSE? Moglibyśmy to zmienić na WYSZUKAJ.PIONOWO, przeszukać cały ten tekst w tej tabeli, 2,. W tym przypadku użyję TRUE, ale w innym przypadku możesz użyć FALSE. CONTROL + SHIFT + ENTER. Nie. To po prostu zadziała, dobrze? (= SUMA (WYSZUKAJ.PIONOWO (B2: B53; K3: L5,2; Prawda)))
Dynamiczne tablice, które pojawią się na początku 2019 r., Rozwiążą wiele problemów.
Dzięki za spędzenie czasu tutaj. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik programu Excel: can-you-return-all-vlookup-values.xlsx
Gdy ktoś zapyta „Czy funkcja WYSZUKAJ.PIONOWO może zwrócić wszystkie dopasowania, odpowiedź brzmi: nie”. Jest jednak wiele innych funkcji, które zasadniczo mogą robić to samo.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Normalizuj swoje dane tak, jakby inni normalizowali swoje dane za Ciebie”
Kevin Lehrbass
