Wyszukiwanie i Odwołania
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.
- PIONOWOEtykiety (nagłówki) są w pierwszym wierszu na samej górze. Dane rosną w dół (tworząc długie kolumny).
- POZIOMOEtykiety (nagłówki) są w pierwszej kolumnie po lewej stronie. Dane rosną w prawo (tworząc szerokie wiersze).
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.
3
INDEKS i PODAJ.POZYCJĘ: gdy szukasz w lewo
Funkcja WYSZUKAJ.PIONOWO ma jedno twarde ograniczenie, o którym CKE doskonale wie: potrafi patrzeć wyłącznie w prawo od kolumny z szukaną wartością. Jeśli w tabeli nazwisko stoi po lewej, a numer identyfikacyjny po prawej i musisz z numeru odczytać nazwisko — klasyczne wyszukiwanie nie zadziała. Wtedy sięgasz po parę INDEKS i PODAJ.POZYCJĘ.
PODAJ.POZYCJĘ odpowiada na pytanie „w którym wierszu to jest?”, a INDEKS — „co stoi w tym wierszu wybranej kolumny?”. Złożone razem robią to samo co wyszukiwanie pionowe, tylko bez ograniczenia kierunku.=PODAJ.POZYCJE(A2; Cennik!$C$2:$C$100; 0) -> numer wiersza, np. 17 =INDEKS(Cennik!$A$2:$A$100; 17) -> wartosc z tego wiersza =INDEKS(Cennik!$A$2:$A$100; PODAJ.POZYCJE(A2; Cennik!$C$2:$C$100; 0))
PODAJ.POZYCJĘ równy 0 oznacza dokładne dopasowanie — tak samo jak FAŁSZ w wyszukiwaniu pionowym. Pominięcie go to ten sam błąd i te same stracone punkty.WYSZUKAJ.PIONOWO zacznie zwracać dane z sąsiedniej kolumny.X.WYSZUKAJ, która szuka w obie strony i ma wbudowaną obsługę braku wyniku. Jeśli jest dostępna na stanowisku, można jej użyć — ale parę INDEKS i PODAJ.POZYCJĘ warto znać, bo działa wszędzie.4
Przedziały, błędy i wersja dla Calca
Ostatni argument ustawiony na PRAWDA (albo pominięty) zwykle jest błędem, ale ma jedno zastosowanie, które CKE lubi: przypisywanie przedziałów — ocena z liczby punktów, stawka podatku z dochodu, kategoria wiekowa z wieku.
Tabela progow (MUSI byc posortowana rosnaco po pierwszej kolumnie): A B 1 0 niedostateczny 2 50 dopuszczajacy 3 65 dostateczny 4 80 dobry 5 90 bardzo dobry =WYSZUKAJ.PIONOWO(C2; $A$1:$B$5; 2; PRAWDA) dla 72 punktow -> "dostateczny" (ostatni prog nie wiekszy niz 72)
Dwa warunki, bez których to nie zadziała: tabela progów musi być posortowana rosnąco, a w pierwszej kolumnie stoi dolna granica przedziału, nie górna. Alternatywą jest zagnieżdżone JEŻELI, ale przy pięciu progach formuła robi się nieczytelna i łatwiej się w niej pomylić.
Wynik #N/D nie jest usterką arkusza — to informacja, że szukanej wartości nie ma w tabeli. Zanim zaczniesz go ukrywać, sprawdź trzy najczęstsze przyczyny: literówkę, spację na końcu wartości oraz liczbę zapisaną jako tekst po imporcie z pliku.
=JEZELI.BLAD(WYSZUKAJ.PIONOWO(A2; Cennik!$A$2:$D$99; 3; 0); "brak w cenniku") =USUN.ZBEDNE.ODSTEPY(A2) usuwa spacje na koncu i poczatku =CZY.LICZBA(A2) sprawdza, czy to naprawde liczba
Uwaga na kolejność działań w zadaniu: jeśli polecenie mówi „policz, ile produktów nie występuje w cenniku”, to #N/D jest ci potrzebny i nie wolno go ukrywać — wtedy zliczasz błędy funkcją LICZ.JEŻELI z warunkiem CZY.BŁĄD. Zamaskowanie wyniku napisem sprawia, że odpowiedź przestaje być policzalna.
WYSZUKAJ.PIONOWO, INDEKS, PODAJ.POZYCJĘ, JEŻELI.BŁĄD. Różnice, o które można się potknąć, są trzy:Cennik.A2 zamiast Cennik!A2. Przy ręcznym pisaniu formuły to pierwsze źródło błędu; klikanie zakresu myszą tego problemu nie ma.X.WYSZUKAJ może nie być — pojawiła się dopiero w nowszych wydaniach. Para INDEKS i PODAJ.POZYCJĘ działa zawsze i dlatego jest bezpieczniejszym wyborem.Korepetycje z informatyki
Zdajesz maturę rozszerzoną z informatyki?
Prowadzimy indywidualne przygotowanie do matury z informatyki — algorytmy, Python i C++, bazy danych i arkusz kalkulacyjny. Zajęcia online, na arkuszach CKE. Zobacz program i cennik.
Zobacz korepetycje z informatykiUczysz się w technikum informatycznym? Prowadzimy też przygotowanie do kwalifikacji INF.02, INF.03 i INF.04. Zobacz egzaminy zawodowe →