Absolwenci na walizkach

Funkcja wyszukaj.pionowo – łączenie danych z różnych źródeł

06/07/2016Aktualizacja: 10/07/2018Excel Tutoriale Komentarze (8) Wrzuć na FB

Pracując niegdyś w korporacji nie wyobrażałem sobie dnia bez użycia w Microsoft Excel wybornej funkcji WYSZUKAJ.PIONOWO. Dość trudna w użycia formuła z 3 lub 4 argumentami. Praktyka czyni mistrza. Używając jej codziennie niemal z zamkniętymi oczami wprowadzałem argumenty.

Funkcja WYSZUKAJ.PIONOWO służy do łączenia i uzupełnianie danych, przeszukiwanie danego zakresu, aby znaleźć dokładnie to, czego szukamy. Możemy szukać zarówno:

  • dokładnego odpowiednika,
  • najlepszego dopasowania.

Obie sytuacje pokażę na przykładzie.

Na końcu artykułu znajdziesz plik do pobrania z przykładowymi zadaniami.

PRZYPADEK 1 – szukamy dokładnego odpowiednika

Mamy 2 zbiory danych, 2 tabele z różnych źródeł, których łączy KLUCZ, wartość wspólna dla obu. Nasze zadanie polega na tym, aby kolumnę C uzupełnić o dane z tabeli 2, czyli kolumny G.

Przy takiej ilości danych można oczywiście obrobić to ręcznie, ale będzie to niezwykle trudne w realizacji przy np. 100 000 rekordów 🙂

Zbawienną pomoc oferuje nam funkcja WYSZUKAJ PIONOWA, która składa się z 4 argumentów (czasem można użyć tylko 3).

Warto przeklikać przez pola, aby poznać znaczenie każdego argumentu.

W komórce C2 nasza formuła będzie miała postać:

=WYSZUKAJ.PIONOWO(A2;$F$2:$G$7;2;FAŁSZ)

  • argument pierwszy: A2 – odnosimy go do tej wartości, po której będziemy szukać w innym zbiorze danych, inaczej jest to nasz klucz w tabeli, której dane chcemy poszerzyć o dane z inne tabeli
  • argument drugi: $F$2:$G$7 – zakres komórek wszystkich danych z 2. tabeli, dolary nie są przypadkowe, blokujemy bezwzględnie komórki (najszybciej jest to zrobić poprzez użycie F4), kiedy będziemy kopiować formułę na dół, to współrzędne zakresu nie ulegną zmianie, nie będzie wtedy błędów (wystarczy oczywiście w tym przypadku zablokowanie wierszy – adresowanie mieszane)
  • argument trzeci: – to numer indeksu kolumny z 2. tabeli; wybieramy kolumnę z 2. kolumny, której wartości chcemy wyświetlić w 1. tabeli, 1. kolumna 2. tabeli ma indeks 1, 2. kolumna 2. tabeli ma indeks 2 itd.
  • argument czwarty – aby znaleźć dokładny odpowiednik trzeba wpisać FAŁSZ

Życie jest piękne – udało się 🙂

Niektóre komórki zawierają komunikat o błędzie #N/D!

Oznacza to brak danych. To jedyny błąd, który nie musi nas stresować 🙂 Przyjrzyj się tabeli danych i zobacz, że nie ma określonego dostawcy gruszek i stąd jest ten komunikat.

 

PRZYPADEK 2 – szukamy najlepszego dopasowania

Rozpatrujemy teraz taki przypadek, gdzie 2. tabela zawiera przedziały.

Naszym jest wpisanie w kolumnie C wartości rabaty na podstawie przedziałów określonych w 2. tabeli.

Nie bądź leniwy(a) – tabela z rabatami niech będzie już ładnie posortowana 🙂

Ponieważ 2. tabela jest posortowana, nasza formuła wygląda tak:

=WYSZUKAJ.PIONOWO(B2;$E$2:$F$6;2)

i małe wyjaśnienie:

  • argument pierwszy: B2 – dopasowujemy rabaty po wartości zamówienia
  • argument drugi: pewnie jest to już jasne!
  • argument trzeci: to też powinno być jasne!
  • argument czwarty: jeśli tabela z rabatami jest posortowane wg kolumny E, to ten argument pomijamy; w przeciwnym wypadku wpisujemy tu PRAWDA

Pobierz tutaj plik XLSX z zadaniami omawianymi w tym artykule.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

Komentarze

Twój komentarz wkrótce zostanie opublikowany
  • Ewelina pisze:

    Witam, czy istnieje mozliwość wyszukiwania wg. dwóch ‚argumentów pierwszych’? Załóżmy, że w tabelce z przykładu 1-szego, za kolumną ‚Owoce’ dokładamy kolumnę ‚Rozmiar’, a owoce o różnych rozmiarach mają innych dostawców – czy możliwe jest w takim wypadku wyszukanie po kryteriach Owoce+Rozmiar?

  • Gość pisze:

    A po co kombinować z funkcją, przecież wystarczy zastosować Ctrl+f, i kliknąć „znajdź wszystko”.

    • Absolwenci na walizkach pisze:

      Dla znalezienia 1 przypadku z pewnością wystarczy zrobić tak, jak napisałaś. Ta funkcja jest jednak do sytuacji bardziej złożonych. Wyobraź sobie, że masz w tabeli 3000 wierszy z danymi klientów, którym masz przypisać rabat wg jakiegoś wzoru. Będziesz robić to wtedy ręcznie? 🙂 Nie chodzi o to, że masz zawsze stosować funkcję WYSZUKAJ.PIONOWE, ale wtedy, kiedy jest ona niezbędna.

  • jan pisze:

    Witam.dziekuje za informacje.
    Ja z kolei nie rozumiem argumentu 3 z formuły-w jaki sposób wiadomo która kolumna ,ma jaki indeks. Czy mozna to jakośc prościej wytłumaczyć?

    • Dariusz Grochocki pisze:

      Indeks kolumny , to po prostu numer kolumny. Jak masz w tym zbiorze 3 kolumny, to pierwsza z nich ma indeks 1, druga – indeks 2, trzecia – indeks 3. To najprostszy argument tej formuły!

  • Patrycja pisze:

    Znalazłam ten artykuł w sieci, bo właśnie zastanawiałam się, do czego jest przydatn vlookup. Powiem Ci, że dobrze wyjaśniłeś jego funkcję 🙂 Natomiast mam problem z ostatnim elementem, tzn. „FAŁSZ” lub „PRAWDA”, o co w tym chodzi? Nie rozumiem, na jakiej to zasadzie działa.
    A tak przy okazji, gdzie mogę dowiedzieć się czegoś więcej o tych sierpniowych warsztatach z excela?

    • Dariusz Grochocki pisze:

      Cześć Patrycjo! Odnośnie szkoleń zerknij tutaj https://absolwencinawalizkach.pl/krakow-szkolenie-microsoft-excel-w-sierpniu/
      Jeśli chodzi o prawdę/fałsz – jest to wartość logiczna określająca, czy funkcja WYSZUKAJ.PIONOWO ma znaleźć dopasowanie dokładne, czy przybliżone:
      1) PRAWDA — powoduje wyszukanie najbliższej wartości przy założeniu, że pierwsza kolumna w tabeli jest posortowana liczbowo lub alfabetycznie. Jeśli nie określisz metody, ta metoda jest stosowana domyślnie.
      2) FAŁSZ — powoduje wyszukanie dokładnej wartości w pierwszej kolumnie.
      Kiedy będziesz już stosować tę funkcję, to przetestuj zarówno z „prawdą”, jak i „fałszem”, a zobaczysz różnicę 🙂 I wtedy obserwacje porównaj z tym, co napisałem w tym tutorialu.