Funkcja wyszukaj.pionowo – vlookup

wyszukaj.pionowo służy do słownikowego wyszukiwania wartości (zwracanej) po zadanym kluczu – wartości szukanej

Lista parametrów funkcji vlookup — Argumenty funkcji

szukana_wartość– wartość klucz po którym szukamy. Bazuje ona zawsze na pierwszej kolumnie z tablicy podanej w kolejnym parametrze. Dlatego też należy o tym pamiętać budując tabele danych, które w przyszłości planujemy przeszukiwać. Istotnym jest również zależność od typu danych. Oznacza to, że szukając liczb należy pamiętać aby przeszukiwany zakres miał ten sam format danych.
tabela_tablica – tablica w ramach, której następuje szukanie. W zasadzie bardziej prawidłową nazwą parametru w j. polskim mogłoby być tablica_z_tabeli, ponieważ tak naprawdę parametr, który wybieramy to obszar, czyli tablica z tabeli, którą chcemy przeszukiwać.
nr_indeksu_kolumny – przedstawia numer kolumny, z której wartość jest zwracana oprzez funkcję
przeszukiwany_zakres – przyjmuje wartości prawda lub fałsz, w prostej formie 1 lub 0. 0 oznacza dokładne przeszukiwanie, 1 przybliżone. Do zwykłego wyszukiwania należy stosować 0. Wyszukiwanie przybliżone oznacza szukanie w zasadzie szukanie po przedziałach wartości i zostanie omówione na dalszym etapie.
Wynikiem jest wartość, której poszukujemy lub N/A, w przypadku braku takiej wartości.

Wyszukiwanie z dokładnym dopasowaniem

Do przykładowego zastosowania funkcji wykorzystamy dane o budownictwie mieszkalnym ze strony https://bdm.stat.gov.pl/ o liczbie mieszkań oddanych do użytkowania za rok 2020 w rozłożeniu miesięcznym.

Mieszkania oddane do użytkowania 2020 — Budownictwo mieszkalne – przykład zastosowania vlookup

Kolumny delta i %delta oznaczają kolejno przyrost bezwzględny oraz procentowy z miesiąca na miesiąc.

Przykładowe zastosowanie

Przykład - wszykiwanie dokładne — Wyszukiwanie dokładne

Szukamy liczby mieszkań w sztukach oddanych do użytkowania za luty 2020.

Pierwszym parametrem będzie rok i miesiąc podane w kolumnie G.
Tablica jest równa tabeli, którą przeszukujemy, dlatego został wstawiamy cały zakres. Aczkolwiek do pozyskania jedynie liczby sztuk wystarczy Zakres kolumn A i B.
Następnie mamy numer kolumny, w naszym przypadku 2.
Kolejno przeszukiwany zakres równy 0, ponieważ interesuje nas dokładne odwzorowanie.

Indeks kolumny w przeszukiwanej tablicy — Zmieniając numer indeksu kolumny można w prosty sposób wyszukiwać wartości z kolejnych kolumn

Numer kolumny jako parametr

Jeżeli mamy wiele kolumn, który wartości chcemy odszukać, jednym z rozwiązań może być dodanie wartości z numerami kolumn w oddzielnym miejscu i wykorzystanie ich jako parametru, jednocześnie blokując wiersz oraz kolumnę na wartości szukanej – G3.

Wykorzystanie listy poprzez Poprawność Danych (Data Validation)

Kolejną opcją na jest wykorzystanie listy wyboru poprzez poprawność danych oraz stworzenie tabeli mapującej nazwę kolumny z numerem kolumny. Mówiąc wprost stosujemy wyszukaj.pionowo dwa razy. Raz do szukania wartości i drugi raz do szukania numeru kolumny.

Wykorzystanie poprawności danych - data valdiation — Tabela mapująca numer kolumny z nazwą kolumny, wraz z listą wyboru.

Dodanie listy wyboru — W ten sposób wybierając nazwę kolumny z listy w R2 będzie zmieniała się wartość zwracana w komórce S3.

Ustawienie wartości domyślnej na wypadek błędu

Wykorzystując funkcję jeżeli.błąd możemy ustawić domyślną zamiast zwracania błędu, np. Brak danych.

Ustawienie jeżeli.błąd — Umieszczenie wyszukaj.pionowo wewnątrz funkcji jeżeli.bląd.

Jest kilka aspektów, na które należy zwrócić uwagę korzystając z funkcji wyszukaj.pionowo:

Nasz klucz czyli wartość, po której szukamy powinien być unikalny w tablicy, którą przeszukujemy. Jeżeli tak nie jest, to nasza funkcja będzie zwracać zawsze pierwsze wystąpienie co niekoniecznie może być oczekiwanym wynikiem. Wtedy nasz wynik będzie zależny od kolejności wierszy w tabeli. Ponadto taka struktura danych będzie niejednoznaczna
Format danych między wartością szukaną a przeszukiwaną kolumną musi być taki sam. W przeciwnym razie funkcja może nie zwracać wartości. Przykładowo wartość liczbowa nie będzie się łączyć z polem tekstowym.
Szukanie wartości zawsze odbywa się po pierwszej kolumnie z zadanej tablicy. Nie można tego zmienić.

Wyszukiwanie z przybliżonym dopasowaniem

Kiedy ustawimy przeszukiwany zakres funkcji jako prawda lub 1, to mamy do czynienia z wyszukiwaniem przybliżonym. Takie wyszukiwanie działa na zupełnie innej zasadzie niż dopasowanie dokładne, a mianowicie nie przeszukujemy tablicy po dokładnej wartości, a po wartości przybliżonej. Oznacza to, że wartość szukana jest rzutowana na przeszukiwaną tablicę poprzez przedziały wartości zamiast wartości dokładnych.

Do przykładowego przedstawienia tej wersji wyszukaj.pionowo dodamy tabelkę określająca przedziały comiesięcznych wzrostów/spadków liczby mieszkań oddanych do użytkowania.

Szukanie przybliżone — Przeszukiwanie przybliżone

Wykorzystując ten rodzaj wyszukiwania należy zwrócić uwagę na poniższe:

Wartości budujące przedziały sortujemy rosnąco. W innym przypadku wartość zwracana może nie spełniać naszych oczekiwań.
Przedziały są lewostronnie domknięte, czyli -40% to jest duży spadek ale już 40,01% już nie będzie mieć dopasowania, -40% <= duży spadek < 20%.

Innym, bardziej praktycznym przykładem może być określenie rabatu w zależności od ilości sztuk nabytego produktu.

wyszukiwanie przybliżone - określanie rabatu według ilości sprzedanego produktu — Ustalanie rabatu po ilości sprzedanego produktu

Mając tabelę definiującą poziom rabatu w zależności od sprzedanych sztuk możemy szybko ustalić należny rabat.

Tego typu wyszukiwanie jest przydatne zawsze tam, gdzie przeszukiwane są zakresy. Mogą to być procesy nadawania ocen, premii, promocji lub inne, gdzie decyzyjność zapada w oparciu i ustalone wcześniej przedziały. Przykładowo ocenę 5 otrzyma uczeń w przypadku zaliczenia egzaminu na min. 90%. Kolejnym przykładem może być nadawanie premii handlowcowi po przekroczeniu 110% planu sprzedażowego.