WYSZUKAJ.PIONOWO w programie Excel: oto, co może zrobić funkcja

Zastosowanie i definicja tej funkcji Excel

VLOOKUP to funkcja Excela, za pomocą której użytkownik może wyszukiwać i oceniać zawartość tabeli. Ta funkcja jest dostępna w wersjach od Excel 2007 dla Windows i Mac.

Co to jest WYSZUKAJ.PIONOWO?

Możliwe zastosowania funkcji WYSZUKAJ.PIONOWO wyjaśnimy tutaj na przykładzie: W tym przypadku jesteś wielkim fanem literatury i dlatego stworzyłeś swój własny arkusz kalkulacyjny Excel, w którym możesz starannie sortować zebrane książki. Do każdej pracy wpisywane są informacje z następujących kategorii:

• autor

• tytuł

• Numer strony

• Rok wydania

Teraz chciałbyś dać przyjacielowi wskazówkę dotyczącą książki, którą może zabrać ze sobą na następne spotkanie. Niestety, można pomyśleć tylko o autorze, a nie o tytule książki. W tym miejscu do gry wchodzi WYSZUKAJ.PIONOWO, ponieważ może użyć tej wartości wejściowej, aby za jednym zamachem wyrzucić informacje, których szukasz.

Jak jest używana funkcja WYSZUKAJ.PIONOWO?

Zanim nawet pomyślimy o formułowaniu formuł, należy ustalić, gdzie później będzie znajdować się pole wejściowe i różne pola wyjściowe. Aby to zrobić, sensowne jest utworzenie oddzielnej tabeli, która początkowo jest pusta, a tym samym zapewnia miejsce na wspomniane informacje. Jeśli zaprojektujesz tę nową tabelę na podstawie przykładu istniejącej, zyskasz później przewagę w zakresie oszczędności czasu.

Na tej podstawie formuła WYSZUKAJ.PIONOWO może być utworzona ręcznie lub automatycznie wygenerowana przez Excel. Dla początkujących warto zastosować to drugie podejście, aby stopniowo poznawać strukturę i działanie formuły. W tym celu w zakładce „Formuły” wybiera się przycisk „Wstaw funkcję”. Funkcja WYSZUKAJ.PIONOWO jest ukryta w oknie, które się otworzy. Po zatwierdzeniu ponownie otwiera się okno, w którym można uzupełnić cztery parametry formuły. To są:

• Kryterium wyszukiwania

• matryca

• Indeks kolumny

• Obszar_odniesienia

Surowy projekt formuły wygląda zatem tak:

= WYSZUKAJ.PIONOWO (kryterium wyszukiwania, macierz, indeks kolumny, link_zakresu)

i w jednej możliwej aplikacji takiej jak ta:

= WYSZUKAJ.PIONOWO (H3; A3: E40; 5)

Kryterium wyszukiwania

Aby funkcja wiedziała, która wartość ma być użyta jako punkt wyjścia, wiersz, który został wybrany jako pole wejściowe dwa kroki wcześniej, jest zaznaczany w polu „Kryterium wyszukiwania”. W naszym przykładzie jest tam wpisane nazwisko autora książki „Phillip Pulmann”. Dzięki temu formuła jest elastyczna i nie trzeba jej ponownie dostosowywać, gdy tylko zmieni się wprowadzona wartość.

matryca

Pole wejściowe „Matrix” opisuje tabelę, w której można znaleźć informacje do wyprowadzenia. Ta specjalna matryca zawiera zatem również kolumny z tytułem książki, numerem strony i rokiem wydania.

Macierz jest w całości zaznaczona jednorazowo bez nagłówków od górnego lewego do dolnego prawego marginesu. W ten sposób Excel wie, jakie treści należy wziąć pod uwagę podczas oceny.

Indeks kolumny

Pole wejściowe dla "indeksu kolumny" prosi użytkownika o zdefiniowanie kolumny macierzy, w której znajduje się tylko poszukiwana wartość. Przyporządkowanie kolumn jest ponumerowane chronologicznie. Oznacza to, że pierwsza kolumna tabeli otrzymuje wartość 1, druga wartość 2 itd. W naszym przykładzie odpowiada to indeksie kolumny 1 dla autora, indeksie kolumny 2 dla tytułu, indeksie kolumny 3 dla numeru strony oraz indeks kolumny 4 dla roku publikacji.

Aby tabela była jak najbardziej elastyczna, zamiast numeru można połączyć nagłówek kolumny. Ma to tę zaletę, że formułę można bez problemu przenieść również do innych wierszy, ponieważ nagłówek kolumny można za każdym razem elastycznie dostosowywać.

Uwaga: Funkcja WYSZUKAJ.PIONOWO odczytuje macierz od lewej do prawej, dlatego indeks kolumny musi być umieszczony po prawej stronie kolumny dla kryterium wyszukiwania, aby funkcja została uwzględniona!

Obszar_odniesienia

Parametr „Range_Lookup” uzupełnia formułę WYSZUKAJ.PIONOWO, określając dokładność, z jaką oceniana jest tabela. Różni się jednak od wcześniej wymienionych składników formuły, ponieważ jest opcjonalny. Jeśli wartość 0 zostanie wprowadzona dla „nieprawidłowego”, program Excel wyszuka tylko wartość określoną jako kryterium wyszukiwania. Jednak z wartością 1 dla „prawda”, wyszukiwanie jest kontynuowane dla oczywistych wartości, jeśli nie można znaleźć dokładnej wartości.

Określenie tego parametru jest opcjonalne, ponieważ wartość 1 jest ustawiona domyślnie. To ustawienie przyda się później w zaawansowanej funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami wyszukiwania.

Łączenie

Po skonfigurowaniu wszystkich niezbędnych parametrów można użyć funkcji WYSZUKAJ.PIONOWO. Po wprowadzeniu kryterium wyszukiwania i zatwierdzeniu funkcji, w wierszu, który został zdefiniowany jako pole wyjściowe, pojawia się szukana wartość.

W naszym przykładzie wyświetlany jest teraz tytuł książki „Złoty kompas”, który odpowiada wprowadzonemu autorowi. Aby szybko znaleźć numer strony i rok publikacji, nie trzeba nic więcej robić niż przeciąganie istniejącej formuły WYSZUKAJ.PIONOWO do kolejnych komórek. Jest to tak łatwe, ponieważ indeks kolumn funkcji WYSZUKAJ.PIONOWO został połączony z nagłówkiem kolumny pierwszej tabeli, a druga tabela również ma taką samą strukturę.

W przypadku, gdyby tabele miały się różnić od siebie lub mimo wszystko wystąpił błąd, formułę WYSZUKAJ.PIONOWO można również zmienić ręcznie. Aby to zrobić, przedostatnia cyfra indeksu kolumny musi być dopasowana do kolumny nowej wartości, która ma być wyprowadzona.

WYSZUKAJ.PIONOWO z wieloma kryteriami wyszukiwania

Często zdarza się, że jedno kryterium wyszukiwania nie wystarcza do dokładnej oceny dużej tabeli Excela. Wtedy sensowne jest uruchomienie funkcji WYSZUKAJ.PIONOWO z kilkoma kryteriami wyszukiwania. W tym celu należy uzupełnić istniejącą formułę o dodatkową funkcję JEŻELI. W ten sposób podczas składania wniosku można wziąć pod uwagę do ośmiu różnych kryteriów wyszukiwania.

WYSZUKAJ.PIONOWO w wielu arkuszach kalkulacyjnych Excel

Jeśli kryterium wyszukiwania można znaleźć nie tylko w jednej tabeli, ale być może również w innej, formułę WYSZUKAJ.PIONOWO można odpowiednio dostosować. W tym celu przed istniejącą formułą należy umieścić zarówno funkcję if, jak i funkcję CZY.BŁĄD. Potrzebnych jest do tego pięć parametrów:

• Kryterium wyszukiwania

• Macierz 1 i Macierz 2

• Indeks kolumny1 i indeks kolumny2

Wynik wygląda tak:

= JEŻELI (CZY.BŁĄD (WYSZUKAJ.PIONOWO (kryterium-wyszukiwania, macierz1, indeks kolumny1, 0));
WYSZUKAJ.PIONOWO (kryterium wyszukiwania; macierz2; indeks kolumny 2,0); WYSZUKAJ.PIONOWO (kryterium wyszukiwania; macierz1; indeks kolumny1;))

i w jednej możliwej aplikacji takiej jak ta:

= JEŻELI (BŁĄD (WYSZUKAJ.PIONOWO (E5, A5: B9,2,0)), WYSZUKAJ.PIONOWO (E5,A13:B17,2,0),WYSZUKAJ.PIONOWO (E5,A5:B9,2,0))

Kryterium wyszukiwania służy do wstawienia szukanej wartości w dwóch tabelach. Matrix1 i Matrix2 definiują odpowiednie obszary komórek obu tabel. Kolumna index1 i kolumna index2 służą do bardziej szczegółowego określenia, które kolumny odpowiednich tabel mają być przeszukiwane.

Jeśli szukana wartość występuje w obu tabelach, program Excel wyświetli wynik z pierwszej tabeli. Jeśli jednak wartość nie zostanie znaleziona w żadnej z dwóch tabel, pojawi się komunikat o błędzie. Zaletą formuły jest to, że dwie listy nie muszą mieć tej samej struktury ani mieć tego samego rozmiaru.

Przypisz wartości do kategorii za pomocą funkcji WYSZUKAJ.PIONOWO

Dodatkowa funkcja WYSZUKAJ.PIONOWO umożliwia automatyczne dzielenie wymienionych wartości na wybrane litery i predykaty. W naszym poprzednim przykładzie należy wstawić dodatkową kolumnę tabeli dla typu książki. Książki o długości do 50 stron powinny należeć do gatunku opowiadania, natomiast do noweli przypisane są książki od 51 do 150 stron, a do powieści od 151 stron. Aby było to możliwe, w funkcji WYSZUKAJ.PIONOWO nie jest wymagana żadna dodatkowa formuła, wystarczy użyć nawiasów klamrowych „{}”. Gotowa formuła wygląda tak:

= WYSZUKAJ.PIONOWO (B1; {1. „Krótka historia”; 51. „Nowa”; 151. „Powieść”}; 2)

Treść nawiasów klamrowych wskazuje na macierz, która określa obszar danego typu książki. Przyporządkowanie długości boku do odpowiedniego rodzaju mieści się zatem w nawiasach klamrowych. Formuła wykorzystuje pary wartości, każda oddzielona punktem. Macierz {1. „Krótka historia”; 51. „Nowa”; 151. „Powieść”} odczytuje się następująco:

"Od 1 pokaż opowiadanie, od 51 pokaż nowelę, od 151 pokaż powieść."

Matrycę tę można łatwo dostosować do różnych zadań. Dotyczy to z jednej strony wielkości i liczby matryc oraz ich przeznaczenia. Tak więc możliwe jest wyprowadzanie jako wyniku ciągów lub liczb zamiast pojedynczych liter. Wszystko, co musisz zrobić, to dostosować litery w formule.

WYSZUKAJ.PIONOWO w wielu arkuszach roboczych

Inna funkcja WYSZUKAJ.PIONOWO umożliwia użytkownikom łączenie treści znajdujących się w różnych arkuszach kalkulacyjnych. W naszym przykładzie ta opcja może być przydatna, gdy informacje są najpierw sortowane w różnych arkuszach roboczych, a następnie aktualizowane w tabeli podsumowującej.

Wyobraź sobie, że oprócz książek, w arkuszu kalkulacyjnym Excela umieszczasz także listę zebranych filmów. Następnie łączysz obie kolekcje w jednym dużym stole.

Zaletą tej procedury jest nie tylko zwiększona kolejność, ale także uniknięcie potencjalnych błędów. Jeśli chcesz utworzyć nowy wpis lub zaktualizować istniejący, nie musisz przeszukiwać dużej tabeli, ale możesz uzyskać dostęp do mniejszych. Wartości są następnie automatycznie przenoszone do podsumowującej tabeli Excela. To sprawia, że przepisywanie w dużej tabeli jest zbędne, co w najlepszym przypadku pozwala uniknąć niefortunnego ruchu i następującego po nim łańcucha komunikatów o błędach.

Jak wygląda formuła?

Ta funkcja jest ponownie możliwa dzięki wstawieniu innej formuły. Chociaż wyszukiwanie z wieloma kryteriami wymagało dodatkowej formuły JEŻELI, praca z wieloma arkuszami wymaga formuły ADR.POŚR. Umożliwia to określenie zakresu z innego arkusza kalkulacyjnego dla macierzy WYSZUKAJ.PIONOWO.

= WYSZUKAJ.PIONOWO (kryterium wyszukiwania; POŚREDNIA (macierz); indeks kolumny; łącze_zakresu)

Uwaga: Ta formuła będzie działać tylko wtedy, gdy poszczególne tabele w różnych arkuszach mają takie same nazwy, jak nagłówki kolumn tabeli ogólnej. Całe tabele można nazwać w polu „Nazwa” w lewym górnym rogu nad siatką komórek. Tabele, które zostały już nazwane, można przeglądać za pomocą kombinacji klawiszy Ctrl + F3.

Radzenie sobie z pojawiającymi się komunikatami o błędach

Praca z połączonymi tabelami Excela może prowadzić do niepożądanych problemów. Dotyczy to w szczególności wyprowadzania błędnych wartości. W przypadku, gdy zostanie wyprowadzona niewłaściwa wartość 0, w ustawieniach programu Excel występuje mały problem, który można szybko naprawić.

Z drugiej strony, powszechny komunikat o błędzie #NV jest celową funkcją WYSZUKAJ.PIONOWO, która wskazuje użytkownikowi, że wymagana wartość nie jest dostępna. Ta notatka może być zaprojektowana inaczej za pomocą formuły.

WYSZUKAJ.PIONOWO - przegląd

WYSZUKAJ.PIONOWO to przydatna funkcja programu Excel, której można używać do wyszukiwania i oceny tabel. Jego zalety są widoczne w przyjaznej dla użytkownika i elastycznej aplikacji. W ten sposób każdy, kto regularnie pracuje z tabelami Excela, może skorzystać z tej funkcji. Czy to prywatny kolekcjoner, który tworzy własne małe tabele, czy duża firma, która przetwarza znacznie większe zbiory danych.

Jeśli, z drugiej strony, nadal masz żądania, których nie udało się spełnić VLOOKUP, możesz spodziewać się dodatkowej opcji Excela: Microsoft oferuje użytkownikom Excel 365 nową funkcję XLOOKUP od początku 2022-2023. Bazuje to na kompetencjach WYSZUKAJ.PIONOWO i uzupełnia je o dodatkowe, czasem nawet prostsze funkcje. Dlatego w tym momencie otwiera się również nowa procedura oceny danych.