WYSZUKAJ.PIONOWO wielokrotnego użytku (WYSZUKAJ.PIONOWO)

Spis treści

Posiadamy listę zamówień z numerami i nazwami towarów. Chciałbym np. wyciągnąć z tabeli po numerze zamówienia wszystkie towary, które się w niej znajdują. Mniej więcej tak:

 

Wspaniała funkcja WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) w takiej sytuacji tylko częściowo pomoże, bo jest w stanie wydobyć dane dopiero przy pierwszym znalezionym dopasowaniu, czyli da nam tylko Jabłka. Aby znaleźć i wyodrębnić wszystkie elementy z tabeli, lepiej jest użyć formuły tablicowej. Jak ten:

=INDEKS($ B$2:$B$16;NAJMNIEJ(IF($ E $ 2=A2: A16;LINE(B2:B16)-1;»»);LINE()-5)

Należy go wpisać w następujący sposób:

  1. zaznacz komórki, w których mają być wyświetlane wyniki (w naszym przykładzie jest to zakres D6:D20)
  2. wprowadź (skopiuj formułę do pierwszej komórki) zakresu
  3. naciśnij Ctrl + Shift + Wchodzę

Odejmowanie jednostek we fragmencie ŁAŃCUCH(B2:B16)-1 odbywa się z powodu nagłówka tabeli. Z tego samego powodu, aby skompensować przesunięcie w wynikowym zakresie w stosunku do pierwotnego, liczba pięć we fragmencie jest odejmowana ŁAŃCUCH ()-5

Aby ukryć #NUM! błąd, który pojawi się w pustych komórkach w wynikowym zakresie D6:D20, możesz skorzystać z funkcji sprawdzania błędów IF i EOSH, zastępując naszą formułę nieco bardziej złożoną:

=JEŻELI(EOSZ(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

W Excelu 2007 pojawiła się wygodniejsza funkcja IFERROR – pozwala na bardziej zwięzłe rozwiązanie problemu:

=JEŻELIBŁĄD(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

W angielskiej wersji programu Excel te funkcje będą wyglądać tak:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

  • Używanie funkcji WYSZUKAJ.PIONOWO do wyszukiwania danych w tabeli
  • Ulepszona wersja funkcji WYSZUKAJ.PIONOWO2, która umożliwia wyszukiwanie w dowolnej kolumnie, a nie tylko w pierwszej wartości
  • Funkcje VLOOKUP2 i VLOOKUP3 z dodatku PLEX
  • Co to są formuły tablicowe i do czego służą?

 

Dodaj komentarz