Porada
Spróbuj użyć nowych funkcji X.WYSZUKAJ i X.DOPASUJ , ulepszonych wersji funkcji opisanych w tym artykule. Te nowe funkcje działają w dowolnym kierunku i domyślnie zwracają dokładne dopasowania, dzięki czemu są łatwiejsze i wygodniejsze w użyciu niż ich poprzedniczki.
Załóżmy, że masz listę numerów lokalizacji biur i musisz wiedzieć, którzy pracownicy są w każdym biurze. Arkusz kalkulacyjny jest ogromny, więc może Ci się wydawać, że jest to trudne zadanie. W rzeczywistości jest to dość łatwe do wykonania przy użyciu funkcji wyszukiwania.
Funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO wraz z funkcjami INDEKS i PODAJ.POZYCJĘ to jedne z najbardziej przydatnych funkcji programu Excel.
Uwaga
Funkcja Kreator odnośników nie jest już dostępna w programie Excel.
Oto przykład użycia funkcji WYSZUKAJ.PIONOWO.
=WYSZUKAJ.PIONOWO(B2;C2:E7;3;PRAWDA)
W tym przykładzie B2 jest pierwszym argumentem — elementem danych potrzebnym do działania funkcji. W przypadku funkcji WYSZUKAJ.PIONOWO ten pierwszy argument jest wartością, którą chcesz znaleźć. Ten argument może być odwołaniem do komórki lub stałą wartością, taką jak "maj" lub 21 000. Drugi argument jest zakresem komórek C2-:E7, w których należy wyszukać szukaną wartość. Trzeci argument określa kolumnę we wskazanym zakresie, która zawiera szukaną wartość.
Czwarty argument jest opcjonalny. Wprowadź wartość PRAWDA lub FAŁSZ. Jeśli wprowadzisz wartość PRAWDA lub pozostawisz argument pusty, funkcja zwróci przybliżone dopasowanie wartości określonej w pierwszym argumencie. Jeśli wprowadzisz wartość FAŁSZ, funkcja dopasuje wartość pierwszego argumentu. Innymi słowy, pozostawienie czwartego argumentu pustego — lub wprowadzenie wartości PRAWDA — zapewnia większą elastyczność.
W tym przykładzie przedstawiliśmy działanie tej funkcji. Po wprowadzeniu wartości w komórce B2 (pierwszy argument) funkcja WYSZUKAJ.PIONOWO przeszukuje komórki z zakresu C2:E7 (drugi argument) i zwraca najbliższe przybliżone dopasowanie z trzeciej kolumny w zakresie, czyli kolumny E (trzeci argument).
Czwarty argument jest pusty, więc funkcja zwraca przybliżone dopasowanie. Wpisanie w tym argumencie wartości FAŁSZ wymaga podania jednej z wartości z kolumn C lub D, aby funkcja w ogóle zwróciła wyniki.
Jeśli dobrze znasz funkcję WYSZUKAJ.PIONOWO, równie łatwa w użyciu będzie funkcja WYSZUKAJ.POZIOMO. Wprowadzasz te same argumenty, ale wyszukiwanie odbywa się w wierszach, a nie w kolumnach.
Używanie funkcji INDEKS i PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO
Funkcja WYSZUKAJ.PIONOWO ma pewne ograniczenia — funkcja WYSZUKAJ.PIONOWO może wyszukiwać wartość tylko od lewej do prawej. Oznacza to, że kolumna zawierająca szukaną wartość powinna zawsze znajdować się po lewej stronie kolumny zawierającej zwracaną wartość. Jeśli arkusz kalkulacyjny nie jest zbudowany w ten sposób, nie należy używać funkcji WYSZUKAJ.PIONOWO. Zamiast tego należy użyć kombinacji funkcji INDEKS i PODAJ.POZYCJĘ.
W tym przykładzie przedstawiono krótką listę, na której szukana wartość (Szczecin) nie znajduje się w pierwszej kolumnie od lewej strony. Dlatego nie można użyć funkcji WYSZUKAJ.PIONOWO. Zamiast niej wyszukamy wartość Szczecin w zakresie B1:B11 przy użyciu funkcji PODAJ.POZYCJĘ. Wartość zostanie znaleziona w wierszu 4. Następnie funkcja INDEKS użyje tej wartości jako argumentu wyszukiwania i znajdzie populację Szczecina w czwartej kolumnie (kolumnie D). Użyta formuła jest wyświetlana w komórce A14.
Spróbuj użyć tej funkcji
Jeśli chcesz poeksperymentować z funkcjami wyszukiwania, zanim użyjesz ich na własnych danych, oto kilka przykładowych danych.
Przykład zastosowania funkcji WYSZUKAJ.PIONOWO
Skopiuj następujące dane do pustego arkusza kalkulacyjnego.
Porada
Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do C na 250 pikseli, a następnie kliknij przycisk Zawijaj tekst (karta Narzędzia główne , grupa Wyrównanie ).
| Gęstość | Lepkość | Temperatura |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Formuła | Opis | Wynik |
| =WYSZUKAJ.PIONOWO(1;A2:C10;2) | Wyszukuje wartość 1 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona największa wartość mniejsza niż lub równa 1 w kolumnie A (czyli 0,946) jest używana do określenia wiersza kolumny B, z którego jest zwracana wartość. | 2,17 |
| =WYSZUKAJ.PIONOWO(1;A2:C10;3;PRAWDA) | Wyszukuje wartość 1 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona największa wartość mniejsza niż lub równa 1 w kolumnie A (czyli 0,946) jest używana do określenia wiersza kolumny C, z którego jest zwracana wartość. | 100 |
| =WYSZUKAJ.PIONOWO(0,7;A2:C10;3;FAŁSZ) | Wyszukuje wartość 0,7 w kolumnie A za pomocą dopasowania dokładnego: nie istnieje dokładne dopasowanie w kolumnie A, dlatego funkcja zwraca błąd. | #N/D! |
| =WYSZUKAJ.PIONOWO(0,1;A2:C10;2;PRAWDA) | Wyszukuje wartość 0,1 w kolumnie A za pomocą dopasowania przybliżonego: wartość 0,1 jest mniejsza niż najmniejsza wartość w kolumnie A, dlatego funkcja zwraca błąd. | #N/D! |
| =WYSZUKAJ.PIONOWO(2;A2:C10;2;PRAWDA) | Wyszukuje wartość 2 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona w kolumnie A największa wartość mniejsza niż lub równa 2 (czyli 1,29) jest używana do określenia wiersza kolumny B, z którego jest zwracana wartość. | 1,71 |
Przykład funkcji WYSZUKAJ.POZIOMO
Skopiuj wszystkie komórki w tej tabeli i wklej je w komórce A1 w pustym arkuszu programu Excel.
Porada
Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do C na 250 pikseli, a następnie kliknij przycisk Zawijaj tekst (karta Narzędzia główne , grupa Wyrównanie ).
| Osie | Łożyska | Śruby |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Formuła | Opis | Wynik |
| =WYSZUKAJ.POZIOMO("Osie";A1:C4;2;PRAWDA) | Wyszukuje słowo „Osie” w pierwszym wierszu i zwraca wartość z drugiego wiersza, który znajduje się w tej samej kolumnie (kolumnie A). | 4 |
| =WYSZUKAJ.POZIOMO("Łożyska";A1:C4;3;FAŁSZ) | Wyszukuje słowo „Łożyska” w pierwszym wierszu i zwraca wartość z trzeciego wiersza, która znajduje się w tej samej kolumnie (kolumnie B). | 7 |
| =WYSZUKAJ.POZIOMO("Ł";A1:C4;3;PRAWDA) | Wyszukuje „Ł” w pierwszym wierszu i zwraca wartość z trzeciego wiersza, która znajduje się w tej samej kolumnie. Ponieważ nie znaleziono dokładnego dopasowania „Ł”, użyta jest największa wartość w wierszu 1, która jest mniejsza niż „Ł”: „Osie” w kolumnie A. | 5 |
| =WYSZUKAJ.POZIOMO("Sworznie";A1:C4;4) | Wyszukuje słowo „Sworznie” w pierwszym wierszu i zwraca wartość z czwartego wiersza, która znajduje się w tej samej kolumnie (kolumnie C). | 11 |
| =WYSZUKAJ.POZIOMO(3;{1;2;3\"a";"b";"c"\"d";"e";"f"};2;PRAWDA) | Wyszukuje liczbę 3 w trzywierszowej stałej tablicowej i zwraca wartość z drugiego wiersza w tej samej (w tym przypadku trzeciej) kolumnie. W stałej tablicowej są trzy wiersze wartości oddzielone od siebie ukośnikiem odwrotnym (\). Zwracana jest wartość „c”, ponieważ znajduje się w drugim wierszu i w tej samej kolumnie co liczba 3. | c |
Przykłady funkcji INDEKS i PODAJ.POZYCJĘ
W ostatnim przykładzie zastosowano razem funkcje INDEKS i PODAJ.POZYCJĘ, aby zwrócić numer najwcześniejszej faktury i odpowiadającą jej datę dla każdego z pięciu miast. Data jest zwracana jako liczba, dlatego za pomocą funkcji TEKST sformatowano ją jako datę. Funkcja INDEKS w rzeczywistości używa jako argumentu wyniku funkcji PODAJ.POZYCJĘ. Połączenie funkcji INDEKS i PODAJ.POZYCJĘ jest stosowane dwukrotnie w każdej formule — najpierw w celu zwrócenia numeru faktury, a następnie w celu zwrócenia daty.
Skopiuj wszystkie komórki w tej tabeli i wklej je w komórce A1 w pustym arkuszu programu Excel.
Porada
Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do D na 250 pikseli i kliknij przycisk Zawijaj tekst (karta Narzędzia główne , grupa Wyrównanie ).
| Faktura | Miasto | Data faktury | Najwcześniejsza faktura wg. miasta, z datą |
|---|---|---|---|
| 3115 | Warszawa | 07.04.12 | ="Warszawa = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Warszawa";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Warszawa";$B$2:$B$33;0);3);"d/m/yy") |
| 3137 | Warszawa | 09.04.12 | ="Poznań = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Poznań";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Poznań";$B$2:$B$33;0);3);"d/m/yy") |
| 3154 | Warszawa | 11.04.12 | ="Gdańsk = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Gdańsk";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Gdańsk";$B$2:$B$33;0);3);"d/m/yy") |
| 3191 | Warszawa | 21.04.12 | ="Nowy Targ = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Nowy Targ";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Nowy Targ";$B$2:$B$33;0);3);"d/m/yy") |
| 3293 | Warszawa | 25.04.12 | ="Rzeszów = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Rzeszów";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Rzeszów";$B$2:$B$33;0);3);"d/m/yy") |
| 3331 | Warszawa | 27.04.12 | |
| 3350 | Warszawa | 28.04.12 | |
| 3390 | Warszawa | 01.05.12 | |
| 3441 | Warszawa | 02.05.12 | |
| 3517 | Warszawa | 08.05.12 | |
| 3124 | Poznań | 09.04.12 | |
| 3155 | Poznań | 11.04.12 | |
| 3177 | Poznań | 19.04.12 | |
| 3357 | Poznań | 28.04.12 | |
| 3492 | Poznań | 06.05.12 | |
| 3316 | Gdańsk | 25.04.12 | |
| 3346 | Gdańsk | 28.04.12 | |
| 3372 | Gdańsk | 01.05.12 | |
| 3414 | Gdańsk | 01.05.12 | |
| 3451 | Gdańsk | 02.05.12 | |
| 3467 | Gdańsk | 02.05.12 | |
| 3474 | Gdańsk | 04.05.12 | |
| 3490 | Gdańsk | 05.05.12 | |
| 3503 | Gdańsk | 08.05.12 | |
| 3151 | Nowy Targ | 09.04.12 | |
| 3438 | Nowy Targ | 02.05.12 | |
| 3471 | Nowy Targ | 04.05.12 | |
| 3160 | Rzeszów | 18.04.12 | |
| 3328 | Rzeszów | 26.04.12 | |
| 3368 | Rzeszów | 29.04.12 | |
| 3420 | Rzeszów | 01.05.12 | |
| 3501 | Rzeszów | 06.05.12 |
Zobacz również
Podręczna karta informacyjna: podsumowanie wiadomości o funkcji WYSZUKAJ.PIONOWO
Funkcje wyszukiwania i odwołań (informacje)
Używanie argumentu tabela_tablica w funkcji WYSZUKAJ.PIONOWO