Dowiedz się, jak automatycznie łączyć dane z wielu tabel. Opanuj WYSZUKAJ.PIONOWO (VLOOKUP) - absolutnego króla arkuszy maturalnych.
Wstęp: Sztuka łączenia tabel
Na maturze z informatyki rzadko kiedy dostajesz wszystkie informacje w jednym, wygodnym arkuszu. Standardem CKE jest rozbicie bazy danych na kilka powiązanych ze sobą zakładek. W arkuszu "Rejestr" masz tylko ID Produktu, ale żeby policzyć całkowity zysk, potrzebujesz jego ceny, która ukryta jest w arkuszu "Cennik". Ręczne kopiowanie wartości zajęłoby dni. Właśnie tutaj wkraczają funkcje wyszukiwania i odwołań, które automatycznie "tłumaczą" kody na konkretne wartości.
🔍 Spis treści: Wyszukiwanie i Odwołania
1
WYSZUKAJ.PIONOWO (VLOOKUP): Król Matury
To prawdopodobnie najważniejsza funkcja, jakiej kiedykolwiek nauczysz się w Excelu. Jej działanie przypomina szukanie w książce telefonicznej: masz nazwisko znajomego (wartość szukana), otwierasz książkę (tabela), jedziesz palcem w dół, aż znajdziesz to nazwisko, a następnie przesuwasz palec w prawo, by odczytać przypisany do niego numer telefonu (wynik).
=WYSZUKAJ.PIONOWO(szukana_wartość; tabela_tablica; nr_indeksu_kolumny; [przeszukiwany_zakres])
A2).FAŁSZ (lub 0), aby wymusić dokładne dopasowanie.Podczas pisania formuły WYSZUKAJ.PIONOWO niemal zawsze przeciągasz ją w dół. Twój "słownik" (Tabela_tablica) nie może się przesuwać! Zawsze, absolutnie zawsze, po zaznaczeniu zakresu słownika wciśnij F4.
ŹLE: Cennik!A1:C100
DOBRZE: Cennik!$A$1:$C$100
Ostatni argument w tej funkcji jest w Excelu opcjonalny. Jeśli go zignorujesz (nie wpiszesz nic), arkusz domyślnie przyjmie wartość PRAWDA (dopasowanie przybliżone).
Skutek? Jeśli arkusz nie znajdzie idealnie takiego samego ID produktu, dobierze cenę produktu o "podobnym" ID. To zrujnuje całe zadanie. Zawsze kończ formułę wpisując na końcu FAŁSZ (lub matematyczne 0).
| A (Kod) | B (Nazwa) | C (Cena) |
|---|---|---|
| M101 | Myszka | 120 |
| K205 | Klawiatura | 350 |
| M102 | Monitor | 800 |
| A (Wpisany Kod) | B (Wynik VLOOKUP) |
|---|---|
| K205 | 350 |
| M101 | 120 |
| Z999 | #N/D |
# Formuła wpisana w arkuszu REJESTR w komórce B2:
=WYSZUKAJ.PIONOWO(
A2; # Czego szukam? (Kodu "K205")
Cennik!$A$1:$C$4; # Gdzie? (Cała tabela cennika zablokowana kłódką $)
3; # Z której kolumny? (Cena jest w trzeciej kolumnie cennika)
0 # Jak? (0 to to samo co FAŁSZ - szukaj dokładnego kodu)
)
Co oznacza błąd #N/D? (Niedostępne). Arkusz zgłasza, że na liście w Cenniku nie ma kodu "Z999". To poprawna i pożądana reakcja arkusza na nieistniejące dane.
2
WYSZUKAJ.POZIOMO (HLOOKUP): Maturalny "Curveball"
Podczas gdy WYSZUKAJ.PIONOWO to król, WYSZUKAJ.POZIOMO (z ang. Horizontal Lookup) to rzadsza, ale wciąż wymagana przez CKE alternatywa. Użyjesz jej w sytuacji, gdy układ tabeli źródłowej ("słownika") jest odwrócony: nagłówki nie znajdują się na górze, lecz po lewej stronie, a dane rozwijają się w prawo.
Składnia jest niemal identyczna jak w przypadku wersji pionowej. Różnica polega na tym, że arkusz przeszukuje pierwszy (najwyższy) wiersz zaznaczonej tabeli, a wynik pobiera z podanego wiersza w dół.
# Zwróć uwagę na 3 argument:
=WYSZUKAJ.POZIOMO(szukana; tabela; nr_indeksu_wiersza; [zakres])
nr_indeksu_wiersza: Jeśli zaznaczysz tabelę obejmującą wiersze od 1 do 5, a interesujący Cię wynik leży w wierszu 3 tego zaznaczenia, wpisujesz po prostu 3.
Jak szybko ocenić na egzaminie, której funkcji użyć? Spójrz na tabelę, z której chcesz pobrać dane.
Wyobraźmy sobie arkusz, w którym musisz przypisać kwotę premii na podstawie uzyskanego przez pracownika stopnia (A, B lub C). Tabela ze stopniami jest niestety "położona".
| A (Wiersz 1) | Stopień A | Stopień B | Stopień C |
| B (Wiersz 2) | 1500 zł | 1000 zł | 500 zł |
| A (Imię) | B (Stopień) | C (Wynik HLOOKUP) |
|---|---|---|
| Anna | Stopień B | 1000 |
| Jan | Stopień C | 500 |
# Formuła wpisana u pracownika Anny (w komórce C2):
=WYSZUKAJ.POZIOMO(
B2; # Czego szukam? (Słowa "Stopień B")
Premie!$B$1:$D$2; # Gdzie? (Położona tabela zablokowana kłódką $)
2; # Z którego wiersza? (Kwota jest w 2. wierszu tej tabeli)
0 # Jak? (Zawsze dokładne dopasowanie, czyli FAŁSZ/0)
)
Co zrobić, gdy na maturze trafisz na tabelę poziomą, wpadniesz w panikę i zapomnisz składni WYSZUKAJ.POZIOMO? Możesz po prostu obrócić tę tabelę!
Zaznacz całą poziomą tabelę, skopiuj ją (Ctrl+C), a następnie kliknij w puste miejsce w arkuszu prawym przyciskiem myszy i w Opcjach wklejania wybierz ikonkę Transponuj (Transpose). Tabela natychmiast zamieni wiersze z kolumnami, stając się klasyczną, pionową tabelą, gotową na Twoje ulubione WYSZUKAJ.PIONOWO.