excel rządzi, excel radzi, excel nigdy cię nie zdradzi

Wpis

wtorek, 08 czerwca 2010

Excel funkcja: wyszukaj.pionowo

Zobaczyłem dziś rzecz straszną. Siedzi pani i ma Excela włączonego - w jednym arkuszu tabelka do wypełnienia, w drugim - jakieś wartości. Pani przegląda wiersz po wierszu pierwszą tabelkę i sprawdza w kolumnie C wartości, po czym przełącza się na drugi arkusz, szuka wartości i przepisuje na poprzedni arkusz inną wartość, zależną od tej pierwszej.

Na moje pytanie ile czasu jej to zajmuje i jak często to robi - odparła "Co tydzień, no ze 2-3 godziny schodzą na to, zależy ilu klientów mieliśmy".

Kto ją zatrudnił nie zapytam, ale czy można inaczej? Można. Jest taka funkcja, która się nazywa WYSZUKAJ.PIONOWO (vlookup) i która robi w mgnieniu oka to, co tamta pani robi co tydzień przez kilka godzin.

Funkcja wyszukaj.pionowo ma 4 argumenty (parametry), o nieco nietrafionych i mylących nazwach:

  1. szukana_wartość - wymagany - to jest coś, co tak na prawdę mamy i znamy, od czego zależy jakaś inna wartość (której nie znamy, ale mamy w innej tabelce)
  2. tabela_tablica - wymagany - zakres komórek, gdzie w pierwszej kolumnie tego zakresu znajdują się wartości, wśród których możemy odnaleźć szukana_wartość, a w pozostałych kolumnach znajdują się jakieś inne wartości.
  3. nr_indeksu_kolumny - wymagany - liczba - numer kolumny z tabela_tablica, z której chcemy żeby funkcja wyszukaj.pionowo zwróciła wartość
  4. przeszukiwany_zakres - opcjonalny - prawda/fałsz albo 1/0 - jeśli ten parametr jest pusty albo 1 albo PRAWDA, to funkcja będzie szukała szukana_wartość, ale jeśli jej nie znajdzie - weźmie najbliższą mniejszą; czyli takie wyszukiwanie "przybliżone"; natomiast gdy FAŁSZ albo 0 - funkcja będzie szukała tylko szukana_wartość, nic innego, a jak nie znajdzie - zwróci błąd "wartość niedostępna"

Przykład

Mam tabelkę (A1:D6)

Produkt Ilość Cena Do zapłaty
A 3 ? ?
B 2

C 4

D 5

E 6

Oraz drugą tabelkę, cennik: (G5:H10) - tabelka powinna być posortowana wg. pierwszej kolumny (dotyczy głównie przypadku z ostatnim parametrem równym 1 lub PRAWDA).

Produkt Cena
A 10
B 20
C 15
D 7
E 8

Jak policzyć cenę i do zapłaty:

cena: =WYSZUKAJ.PIONOWO(A2 ; $G$5:$H$10 ; 2 ; 0)
do zapłaty: = B2*C2

Prawda, że proste? Małe wyjaśnienie słowne. Cena zależy od nazwy produktu, więc skoro wypełniam kolumnę "cena", to szukana_wartość to znana mi nazwa produktu z komórki A2. Gdzie szukam tej nazwy? W pierwszej kolumnie (tego się nie da zmienić) tabelki w zakresie $G$5:$H$10. Dlaczego z $? Ponieważ za chwilę będę kopiował moją formułę liczącą cenę do kolejnych wierszy i chcę, by cennik był taki sam w kolejnych wierszach. Wypełniam kolumnę cena, więc z której kolumny w cenniku chcę pobierać wartości? Oczywiście z drugiej, więc wpisuję jako trzeci parametr 2. Czy gdyby produktu Z nie było na liście, to może on kosztować tyle, co produkt X? Nie powinien - wtedy chcę zobaczyć błąd. Dlatego ostatni parametr to zero.

Wynik działania:

Produkt Ilość Cena Do zapłaty
A 3 10 30
B 2

B 4

C 5

E 6

Teraz mogę zaznaczyć komórki C2:D2 i skopiować je w dół do kolejnych wierszy. Jeśli wszystko poszło ok - ostateczny wynik:

Produkt Ilość Cena Do zapłaty
A 3 10 30
B 2 20 40
B 4 20 80
C 5 15 75
E 6 8 48

Zagadka: do czego w takim razie można użyć wersji tej funkcji z ostatnim parametrem pustym albo równym 1 bądź PRAWDA.

PS. tabelki z wpisu można zaznaczyć, skopiować do schowka po czym wkleić w Excelu. Ale uwaga - zwykłe wklejanie może nie działać - trzeba użyć "wklej specjalnie" i wybrać "tekst".

 

Szczegóły wpisu

Tagi:
Kategoria:
Autor(ka):
originalhmm
Czas publikacji:
wtorek, 08 czerwca 2010 12:13

Kanał informacyjny

tak, błąd jest zamierzony ;)