W tym poście omówimy wyszukiwanie po dwóch lub więcej kryteriach. Ze względu na nową funkcję w Excelu tablicowym przedstawimy 4 różne sposoby rozwiązania takiego problemu.
Problem polega na tym, że cena produktu zależy nie tylko od nazwy produktu, ale również od kraju, z jakiego pochodzi ten produkt. Musimy wyszukać cenę produktu po 2 kryteriach. Pierwsze rozwiązanie wykorzystuje kolumnę pomocniczą (rys. nr 1).
Rys. nr 1 – przykładowe dane do pierwszego rozwiązania
Musimy napisać formułę, w której połączymy nazwę owocu z krajem jego pochodzenia. Użyjemy do tego znaku ampersand (&) i pionowej kreski (|), aby na pewno rozdzielić dwa ciągi tekstowe. Zapis formuły powinien wyglądać następująco:
=B3&"|"&C3
Po zatwierdzeniu formuły i przeciągnięciu jej na komórki poniżej otrzymamy dane przedstawione na rys. nr 2.
Rys. nr 2 – połączone dane z dwóch kolumn (Owoc i Kraj)
Otrzymaliśmy połączone niejako dane z pierwszej i drugiej kolumny rozdzielone pionową kreską. Dla Excela nie jest to potrzebne, ale my mamy lepszą wizualną formę danych. Mamy pomocniczą kolumnę z połączoną informacją i teraz możemy skopiować z niej formułę za pomocą skrótu klawiszowego Ctrl+C i wkleić w kolumnę pomocniczą z Cennikiem. Istotne jest tutaj, że zarówno dane z naszej Tabeli sprzedaży jak i w Cenniku muszą mieć ten znak rozdzielający (pionową kreskę) – rys. nr 3.
Rys. nr 3 – pionowa kreska rozdzielająca nazwę owocu i jego kraj pochodzenia w obu tabelach
Po wklejeniu formuły do komórki I3, zatwierdzamy ją i kopiujemy na komórki poniżej. Otrzymamy dane przedstawione na rys. nr 4.
Rys. nr 4 – wypełniona kolumna pomocnicza w Cenniku
Teraz musimy wyszukać tylko jedną informację (połączone dwie wartości), dlatego możemy skorzystać ze standardowej funkcji WYSZUKAJ.PIONOWO. Pierwszym argumentem funkcji jest szukana_wartość, czyli dane z kolumny Pomoc z Tabeli sprzedażowej. Drugi argument funkcji to tabela_tablica, czyli kolumny Pomoc i Cena z tabeli Cennik (zablokowane bezwzględnie). Trzeci argument to nr_indeksu_kolumny, czyli u nas wartość 2, bo chcemy wyciągnąć informację z kolumny Cena, która jest druga w kolejności z zakresu tabela_tablica. Ostatni argument (opcjonalny) to przeszukiwany_zakres, w którym musimy zdecydować, czy robimy wyszukiwanie przybliżone, czy dokładne. W większości sytuacji chcemy mieć dopasowanie dokładne, bo nie mamy pewności, czy dane są posortowane, czyli wpisujemy wartość logiczną FAŁSZ lub wartość z nią tożsamą (0). Zapis formuły powinien wyglądać następująco:
=WYSZUKAJ.PIONOWO(D3;$I$3:$J$10;2;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane z dopasowaną ceną według dwóch kryteriów przedstawione na rys. nr 5.
Rys. nr 5 — dane z dopasowaną ceną według dwóch kryteriów (pierwszy sposób – Excel klasyczny)
Teraz omówimy drugie rozwiązanie, które również będzie niejako wykorzystywało kolumnę pomocniczą, ale zbudowaną wewnątrz funkcji. Rozwiązanie to omówimy na podstawie przykładowych danych z rys. nr 6.
Rys. nr 6 przykładowe dane do rozwiązanie drugim sposobem
W klasycznym Excelu mogliśmy użyć funkcji PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, w którym szukamy owocu połączonego z krajem pochodzenia za pomocą znaku ampersand (&). Drugi argument to przeszukiwana_tab, czyli znowu dwie połączone za pomocą znaku & kolumny tym razem z tabeli Cennik zablokowane bezwzględnie. Jeśli na tym etapie podejrzymy wyniki formuły za pomocą klawisza F9, zobaczymy połączone dane z obu kolumn (rys. nr 7).
Rys. nr 7 – podejrzane wyniki formuły w trybie edycji komórki
Wychodzimy z podglądu wyników formuły za pomocą skrótu klawiszowego Ctrl+Z. Pozostaje nam ostatni argument, czyli typ_porównania. U nas będzie to wartość 0 odpowiadająca dopasowaniu dokładnemu. Zapis formuły powinien wyglądać następująco:
=PODAJ.POZYCJĘ(N3&O3;$R$3:$R$10&$S$3:$S$10;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Pokazują nam się wartości w złotówkach, ale to tylko dlatego, że mamy sformatowaną kolumnę walutowo. Tak naprawdę pokazuje nam się pozycja szukanej ceny (rys. nr 8).
Rys. nr 8 – pozycje szukanych cen sformatowane walutowo
Mamy znalezioną pozycję właściwej ceny. Teraz na podstawie tej pozycji musimy wyszukać pasującą cenę. Taki efekt możemy uzyskać za pomocą funkcji INDEKS. Pierwszym argumentem funkcji jest tablica, czyli kolumna, z której chcemy dostać zwróconą cenę zablokowana bezwzględnie. Drugi argument funkcji to nr_wiersza, czyli wynik naszej funkcji PODAJ.POZYCJĘ. Zapis formuły powinien wyglądać następująco:
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy ceny produktów wyszukane według 2 kryteriów przedstawione na rys. nr 9.
Rys. nr 9 — ceny produktów wyszukane według 2 kryteriów (drugi sposób – Excel klasyczny)
Trzecie rozwiązanie wykonamy w Excelu tablicowym. Przykładowe dane do tego zadania zostały przedstawione na rys. nr 10.
Rys. nr 10 – przykładowe dane do rozwiązania w Excelu tablicowym (trzecie rozwiązanie)
Wykorzystamy tutaj funkcję X.WYSZUKAJ. Pierwszym argumentem funkcji jest szukana_wartość, czyli połączone dwie wartości z kolumny Owoc i Kraj za pomocą znaku & z Tabeli sprzedażowej. Drugi argument funkcji to szukana_tablica, czyli połączone kolumny Owoc i Kraj za pomocą znaku &, ale z tabeli Cennik i zablokowane bezwzględnie. Kolejny argument to zwracana_tablica, czyli wartości z kolumny Cena z tabeli Cennik zablokowane bezwzględnie. Funkcja X.WYSZUKAJ działa na zasadzie dopasowania dokładnego, więc nie musimy podawać argumentów opcjonalnych. Zapis formuły powinien wyglądać następująco:
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy ceny wyszukane na podstawie 2 kryteriów przedstawione na rys. nr 11.
Rys. nr 11 — ceny wyszukane na podstawie 2 kryteriów (trzeci sposób – Excel tablicowy)
Pozostało nam czwarte rozwiązanie, w którym będziemy bazować na tym, jak się nasze dane układają i jak często się powtarzają. Przykładowe dane zostały przedstawione na rys. nr 12.
Rys. nr 12 – przykładowe dane (czwarte rozwiązanie)
Możemy łatwo zauważyć, że każdy wiersz z tabeli Cennik jest unikatowy, czyli występuje tylko 1 raz. Na tej podstawie w tym rozwiązaniu możemy wykorzystać całkiem inną funkcję niż funkcje wyszukujące, których do tej pory używaliśmy. Wykorzystamy funkcję SUMA.WARUNKÓW. Pierwszym argumentem funkcji jest suma_zakres, czyli u nas kolumna z Ceną z tabeli Cennik zablokowana bezwzględnie za pomocą klawisza F4. Drugi argument funkcji to kryteria_zakres1, czyli zakres, na którym budujemy pierwsze kryterium – kolumna Owoc z tabeli Cennik zablokowana bezwzględnie. Kolejny argument to kryteria1, czyli dane z kolumny Owoc z Tabeli sprzedażowej. Kolejny argument to kryteria_zakres2, czyli drugie kryterium – kolumna Kraj z tabeli Cennik zablokowana bezwzględnie. I ostatni argument kryteria2, czyli wartość z kolumny Kraj z Tabeli sprzedażowej. Zapis formuły powinien wyglądać następująco:
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy ceny ustalone według 2 kryteriów przedstawione na rys. nr 13.
Rys. nr 13 – ceny ustalone na podstawie 2 kryteriów (czwarte rozwiązanie)
Jak widać powyżej możemy to samo zadanie wykonać w Excelu na kilka sposobów, wykorzystując różne funkcje. Każde z przedstawionych rozwiązań zwróciło te same wartości. Do nas należy decyzja i ocena, które rozwiązanie jest najlepsze czy najszybsze.
W tym poście obliczymy kwotę, jaką należy wypłacić pracownikowi za ilość przepracowanych godzin. Trudność polega na tym, że stawka godzinowa wzrasta wraz z ilością przepracowanych godzin przez danego pracownika.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Jak widać na rysunku powyżej, jeśli pracownik przepracował mniej niż 100 godzin, to stawkę bierzemy z kolumny F. Jeśli przepracowana została ilość godzin między 100 a 200 stawka godzinowa jest brana z kolumny G, a powyżej 200 godzin, mamy stawkę z kolumny H. Nasze zadanie polega na obliczeniu kwoty, jaka należy się pracownikowi za przepracowane godziny.
Zaczniemy od funkcji WYSZUKAJ.PIONOWO, za której pomocą odnajdziemy wiersz, w którym są dane dla wybranego pracownika. Pierwszy argument funkcji to szukana_wartość, czyli konkretny pracownik (komórka A2). Drugi argument funkcji to tabela_tablica, czyli dane w tabeli z rozpisanymi stawkami godzinowymi (zakres zablokowany bezwzględnie za pomocą klawisza F4, bo będziemy kopiować formułę w dół $E$2:$H$5). W tym zakresie nie zaznaczamy nagłówka, bo nie jest potrzebny. Trzeci argument funkcji to nr_indeksu_kolumny, czyli z której kolumny będziemy wybierać dane. Na tą chwilę wpiszemy tu na stałe wartość 2, czyli dane z drugiej kolumny dla ilości przepracowanych godzin do 100. W miejsce tego argumentu będziemy musieli napisać formułę, która będzie wybierać odpowiednią kolumnę w zależności od ilości przepracowanych godzin. Czwarty argument to przeszukiwany_zakres, czyli rodzaj dopasowania. Wpisujemy tutaj wartość 0 dla dopasowania dokładnego co widać na rys. nr 2.
Rys. nr 2 – wybór dopasowania w funkcji WYSZUKAJ.PIONOWO
Zapis formuły powinien wyglądać następująco:
=WYSZUKAJ.PIONOWO(A2;$E$2:$H$5;2;0)
Zatwierdzamy powyższą formułę i kopiujemy w dół. Otrzymamy stawki godzinowe dla poszczególnych pracowników przedstawione na rys. nr 3.
Rys. nr 3 – stawka godzinowa dla poszczególnych pracowników
Wyciągnęliśmy stawki godzinowe dla poszczególnych pracowników. Ale ta stawka jest wyciągnięta z kolumny F, czyli dla ilości przepracowanych godzin do 100. Stawka ta jest zależna od ilości przepracowanych godzin każdego pracownika podanych w kolumnie B. Aby uwzględnić te zmiany, musimy w miejsce wpisanego na stałe argumentu nr_indeksu_kolumny wstawić funkcję PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli liczba przepracowanych godzin (B2). Drugi argument to przeszukiwana_tab, czyli nagłówki naszych danych z progami ilości godzin zablokowane bezwzględnie ($F$1:$H$1). Trzeci argument to typ_porównania, czyli mniejsze niż w naszym przykładzie – wpisujemy wartość 1 (rys. nr 4).
Rys. nr 4 – typy porównania w funkcji PODAJ.POZYCJĘ
Funkcja PODAJ.POZYCJĘ będzie szukała w której kolumnie znajduje się ilość przepracowanych godzin przez danego pracownika. Jeśli np. ilość godzin będzie wynosiła 200 to funkcja poda numer kolumny H. Musimy zwrócić uwagę, że wynik funkcji PODAJ.POZYCJĘ jest o 1 mniejsza niż ilość kolumn, których potrzebujemy w funkcji WYSZUKAJ.PIONOWO, bo zaznaczaliśmy o jedną kolumnę mniejszy zakres (tylko kolumny od F do H). Dlatego do tego argumentu musimy dodać wartość 1.
Zapis całej formuły powinien wyglądać następująco:
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane przedstawione na rys. nr 5.
Rys. nr 5 – stawka godzinowa pracowników według ilości przepracowanych godzin
Otrzymaliśmy prawidłowe stawki godzinowe dla poszczególnych pracowników. Pozostaje nam pomnożyć stawkę godzinową przez ilość przepracowanych godzin. Zapis formuły powinien wyglądać następująco:
W subskrypcji
Insider mamy nową funkcję do testowania – ZEZWALAJ, która pozwala
niejako na nazywanie formuł wewnątrz niej samej. Zaczniemy od prostych
przykładów z równaniami matematycznymi. Dane do tego zadania zostały
przedstawione na rys. nr 1.
Rys. nr 1 – dane do zadania
Pierwszym argumentem funkcji jest nazwa1, czyli w naszym przykładzie x. Co istotne, nie musimy nazwy pisać w podwójnych cudzysłowach. Drugi argument funkcji to wartość_nazwy1, czyli odwołanie do komórki z wartością argumentu X lub możemy również wartość tą wpisać na stałe ręcznie. Trzeci argument funkcji to obliczenie_lub_nazwa2, czyli np. równanie które chcielibyśmy obliczyć. Istotne jest, że nasza nazwa argumentu x jest nazwą wykorzystywaną tylko wewnątrz funkcji (rys. nr 2).
Rys. nr 2 – nazwa wykorzystywana wewnątrz funkcji
W trzecim argumencie
wpisujemy proste równanie x^2 + 2*x +3.
Zapis całej formuły powinien wyglądać następująco:
=ZEZWALAJ(x;A2;x^2+2*x+3)
Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 3.
Rys. nr 3 – wynik funkcji ZEZWALAJ
Jak widać na
rysunku powyżej otrzymaliśmy wynik po tym, jak Excel w miejsce każdego znaku x
w równaniu wstawił wartość x=5.
Dzięki
zastosowaniu funkcji ZEZWALAJ możemy budować różne równania i w łatwy sposób
otrzymać gotowy wynik. Zrobimy teraz kolejny przykład. W tej formule w drugim argumencie
funkcji zamiast odwołania do komórki, wpiszemy stałą wartość x (5). A równanie
w trzecim argumencie zapiszemy w następujący sposób: x^3+2*x^2+3*x+2. Zapis
całej formuły powinien wyglądać następująco:
=ZEZWALAJ(x;5;x^3+2*x^2+3*x+2)
Po zatwierdzeniu
formuły otrzymamy wynik przedstawiony na rys. nr 4.
Rys. nr 4 – wynik funkcji ZEZWALAJ
Funkcja
ZEZWALAJ pozwala nam również obliczać bardziej skomplikowane zadania np. z
dwoma niewiadomymi (x i y). W pierwszym argumencie funkcji podamy wartość x,
której w drugim argumencie przypiszemy wartość z komórki A8. Następnie podajemy
argument nazwa2, czyli argument y i kolejny argument wartość_nazwy2
równą wartości w komórce B8. Dopiero po wpisaniu tych czterech argumentów, możemy
wpisać argument obliczenie. Zapis równania w argumencie obliczenie będzie
wyglądał następująco: x^2+y^2+x*y. Zapis całej formuły będzie wyglądał następująco:
=ZEZWALAJ(x;A8;y;B8;
x^2+y^2+x*y)
Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 5.
Rys. nr 5 – wynik funkcji ZEZWALAJ dla dwóch wartości
Co istotne
jeśli w zapisie funkcji w argumencie wartość_nazwa1 wykorzystamy odwołanie
do komórki, to funkcja będzie dynamiczna. Oznacza to, że jeśli zmienimy wartość
x w komórce z odwołania to automatycznie zmieni nam się wynik. Jeśli natomiast
wpiszemy konkretną wartość, nic nam się da zmiana w tabeli z danymi.
Z funkcji ZEZWALAJ
możemy korzystać nie tylko do obliczania równań. Pokażemy teraz jej inne możliwości.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 6.
Rys. nr 6 – przykładowe dane do zadania
Tę funkcję
możemy wykorzystać tam, gdzie chcemy coś wyszukać, a funkcja WYSZUKAJ zwraca
nam nie to, co chcemy pokazać użytkownikom. W przykładowych danych mamy tabelkę
z produktami, w której oprócz cen mamy informację, że coś jest niedostępne,
brak lub nie ma. Nie chcemy pokazywać klientowi tych informacji, tylko żeby
wyświetlała się informacja np. – (myślnik). Gdybyśmy chcieli to rozwiązać za
pomocą innej funkcji, użylibyśmy funkcji WYSZUKAJ.PIONOWO. Zapis funkcji
wyglądałby następująco:
=WYSZUKAJ.PIONOWO(A2;$G$2:$H$9;2;0)
Jeśli funkcja
WYSZUKAJ.PIONOWO zwróci nam tekst (nie ma, brak czy niedostępny, to chcemy aby
wyświetlał się znak myślnika. Natomiast jeśli funkcja WYSZUKAJ.PIONOWO zwróci
liczbę, to chcemy aby ta liczba się wyświetlała, czyli żeby został wynik
funkcji.
W tym
przykładzie nie zadziała funkcja JEŻELI.BŁĄD, bo wynik funkcji
WYSZUKAJ.PIONOWO nie jest błędny, tylko dla nas nie odpowiedni. Musimy napisać
funkcję JEŻELI, która będzie sprawdzała, czy wynik funkcji
WYSZUKAJ.PIONOWO jest tekstem. Użyjemy tutaj funkcji CZY.TEKST.
Argumentem
funkcji CZY.TEKST jest wartość, czyli sprawdzamy c k funkcji
WYSZUKAJ.PIONOWO jest tekstem. Zapis funkcji CZY.TEKST powinien wyglądać następująco:
=CZY.TEKST(WYSZUKAJ.PIONOWO(A2;$G$2:$H$9;2;0))
Tak zapisaną
funkcję CZY.TEKST wstawiamy w miejsce pierwszego argumentu funkcji JEŻELI(argument
test_logiczny). Drugi argument funkcji JEŻELI to wartość_jeżeli_prawda,
czyli wartość jaką chcemy otrzymać, jeśli warunek z testu logicznego jest
spełniony. W tym argumencie wpisujemy znak myślnika w podwójnych cudzysłowach ("-").
Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli wartość, jaką
chcemy uzyskać, jezli test logiczny nie jest spełniony. W tym przypadku chcemy
uzyskać wynik funkcji WYSZUKAJ.PIONOWO. Zapis całej formuły powinien wyglądać
następująco:
Powyższą
formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wyniki funkcji
JEŻELI przedstawione na rys. nr 7.
Rys. nr 7 – wyniki funkcji JEŻELI
ZEZWALAJ. Pierwszym
argumentem funkcji jest nazwa1, czyli np. Cena. Drugi argument to wartość_nazwy1,
czyli tutaj wklejamy wcześniej skopiowaną formułę funkcji WYSZUKAJ.PIONOWO za
pomocą skrótu klawiszowego Ctrl+V. W argumencie obliczenie wpisujemy
formułę funkcji JEŻELI, w której sprawdzamy, czy Cena jest tekstem za
pomocą funkcji CZY.TEKST (argument funkcji JEŻELI – test_logiczny). W argumencie
wartość_jeżeli_prawda chcemy otrzymać "-" (myślnik), natomiast
w argumencie wartość_jeżeli_fałsz chcemy otrzymać wartość Cena. Zapis
całej formuły powinien wyglądać następująco:
=ZEZWALAJ(Cena;
WYSZUKAJ.PIONOWO(A2;$G$2:$H$9;2;0); JEŻELI (CZY.TEKST(Cena; "-";Cena))
Powyższą
formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wyniki, lecz w
formie zaokrąglonej. Musimy zmienić formatowanie ogólne danych na formatowanie
walutowe za pomocą skrótu klawiszowego Ctrl+Shift+4. Otrzymamy wyniki
funkcji ZEZWALAJ przedstawione na rys. nr 8.
Rys. nr 8 – wyniki funkcji ZEZWALAJ
Teraz zajmiemy się ostatnim przykładem, który przygotowałem, na wykorzystanie funkcji ZEZWALAJ. Przykładowe dane do tego przykładu zostały przedstawione na rys. nr 9.
Rys. nr 9 – przykładowe dane do zadania
W tych
danych mamy wykorzystaną funkcję tablicową FILTRUJ, która wyciąga
przefiltrowane dane po sprzedawcy. Wykorzystujemy tutaj również funkcję CZY.PUSTA.
W tym zadaniu chodzi o to, aby puste komórki zastąpić znakiem myślnik. Sama
funkcja FILTRUJ w miejsce pustych komórek zwraca wartość 0 (zero). A my nie
chcemy pokazywać wartości zero, chcemy ją zastąpić.
W tym celu
wykonujemy analogiczny test logiczny jak w przykładzie powyżej (za pomocą funkcji
JEŻELI). W pierwszym argumencie funkcji zapisujemy test_logiczny,
sprawdzający czy funkcja zwróci nam wartość 0. Jeśli warunek jest spełniony,
chcemy otrzymać znak myślnik (argument wartość_jeżeli_prawda). Jeśli warunek
nie jest spełniony chcemy otrzymać wyniki funkcji FILTRUJ (argument wartość_jeżeli_fałsz).
Zapis formuły powinien wyglądać następująco:
=JEŻELI(CZY.PUSTA(FILTRUJ(B1:C25;A1:A25=F2));"-";
FILTRUJ(B1:C25;A1:A25=F2))
Po zatwierdzeniu formuły otrzymamy wyniki
przedstawione na rys.
Rys. nr 10 – wyniki funkcji JEŻELI z użyciem funkcji FILTRUJ
Analogicznie
możemy wykonać to zadanie za pomocą funkcji ZEZWALAJ. W niej
zagnieździmy funkcję FILTRUJ, w której możemy nazwać wynik funkcji –
Filtr. Dzięki temu w argumencie obliczenie mamy uproszczoną formułę i
będzie ona bardziej zrozumiała dla osób, które będą korzystały z naszych
obliczeń. Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 11.
Rys. nr 11 – wyniki funkcji ZEZWALAJ
Prawdziwa potęga funkcji ZEZWALAJ pojawia się przy bardzo skomplikowanych formułach (jak to pokazywał Bill Jelen). Dane do tego zadania zostały przedstawione na rys. nr 12.
Rys. nr 12 – przykładowe dane
Mamy tutaj do czynienia z bardzo skomplikowaną formułą, wykorzystującą wiele funkcji (rys. nr 13).
Rys. nr 13 – formuła z użyciem funkcji ZEZWALAJ
Pracując nad tą skomplikowaną formułą, skróciłem odwołanie do zakresu nazywając go tb. Zapis formuły bez wykorzystania funkcji ZEZWALAJ jest dużo dłuższy co widać na rys. nr 14.
Rys. nr 14 – zapis formuły bez użycia funkcji ZEZWALAJ.
Zapis ten ma długość 4 linijek i wykorzystuje wiele różnych funkcji. Dzięki użyciu funkcji ZEZWALAJ udało się go skrócić do trzech niepełnych linijek. Zrobiłem małe podsumowanie. Mianowicie zapis funkcji JEŻELI jest wykorzystany 6 razy, a zapis funkcji WIERSZ, która zwraca numer wiersza wykorzystana została 2 razy (rys. nr 15).
Rys. nr 15 – ilość razów wykorzystania fragmentu formuły
Podsumowując, wykorzystywanie funkcji ZEZWALAJ pozwala w znacznym stopniu skracać skomplikowane formuły.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście poznamy funkcję BD.POLE, która jest
alternatywą dla funkcji WYSZUKAJ.PIONOWO. Poznamy jej wady i zalety.
Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Kiedy w komórce zaczniemy pisać formułę =BD. Pojawi nam się
cała rodzina funkcji bazodanowych. Wszystkie działają na podobnej zasadzie,
czyli na filtrach zaawansowanych, na których podstawie budujemy kryteria dla funkcji
(rys. nr 2).
rys. nr 2 — Funkcje bazodanowe
W tym poście zajmiemy się funkcją BD.POLE, dzięki której
wyszukamy z bazy danych pojedynczy rekord. Zapis funkcji będzie wyglądał
następująco:
=BD.POLE($A$1:$D$21;3;$G$1:$G$2)
Pierwszym argumentem
funkcji jest Baza, czyli nasza tabela z danymi, zablokowana bezwzględnie
($A$1:$D$21). Należy pamiętać, że przy tej funkcji musimy zaznaczyć dane
łącznie z nagłówkami. Kolejnym argumentem jest Pole, czyli kolumna z której
chcemy wyciągnąć informacje. Zakładamy że w naszym przykładzie chcemy wyciągnąć
kierownika dla danej fabryki. Kolumna, w której znajdują się dane kierowników
jest trzecia z kolei więc w formułę wpiszemy liczbę 3 (rys. nr 3).
rys. nr 3 — Drugi argument funkcji — Pole
Trzecim argumentem funkcji są Kryteria, które budujemy tak
jak przy filtrach zaawansowanych, czyli musimy zaznaczyć nagłówek i wartość pod
spodem, następnie blokujemy bezwzględnie klawiszem F4 ($G$1:$G$2). Formułę
zatwierdzamy Ctrl+Enter i otrzymujemy szukany wynik dla fabryki Żuczek2 (rys.
nr 4).
rys. nr 4 — Wynik działania funkcji BD.POLE
Jak wspominaliśmy na początku jest to funkcja tylko dla
pojedynczych rekordów. Jeśli zmienimy nazwę fabryki na Żuczek, która w danych
występuje kilka razy, to Excel zwróci nam błąd #LICZBA! (rys. nr 5).
rys. nr 5 — Błąd funkcji przy wielu rekordach
Gdybyśmy, zamiast funkcji BD.POLE, w analogicznym przypadku
użyli funkcji WYSZUKAJ.PIONOWO zwróciłaby ona nazwę pierwszego kierownika.
Zaletą funkcji BD.POLE jest to że radzi sobie dużo lepiej z
większą ilością kryteriów. Pokażemy teraz czym możemy sobie zastąpić cyfrę 3,
jako argument Pole w formule funkcji. Nie zawsze numerowanie pól jest wygodne,
bo możemy mieć bardzo dużą ilość danych. W takiej sytuacji lepszym rozwiązaniem
jest odwołanie do komórki, która przechowuje nagłówek szukanej wartości (tekst nagłówka – w naszym przykładzie
komórka H1 z nazwą Kierownik). Po zatwierdzeniu otrzymujemy ten sam wynik jak
wyżej. Zapis funkcji w tym przykładzie wygląda następująco:
=BD.POLE($A$1:$D$21;H1;$G$1:$G$2)
Innym sposobem jest hardkodowanie wartości, czyli wpisanie szukanej
wartości na stałe ("Kierownik"). Zatwierdzamy i po raz kolejny
otrzymamy ten sam wynik. Zapis funkcji będzie wyglądał następująco:
=BD.POLE($A$1:$D$21;"Kierownik";$G$1:$G$2)
Wadą funkcji BD.POLE jest to iż znajduje i działa ona tylko wtedy
gdy w danych znajduje się tylko pojedynczy rekord odpowiadający ustalonym
kryteriom. Zaletą natomiast jest to, że możemy zaznaczyć więcej kryteriów
jednocześnie. Jako trzeci argument czyli Kryteria wpiszemy zakres $G$7:$H$8.
Zapis funkcji będzie wyglądał następująco:
=BD.POLE($A$1:$D$21;I7;$G$7:$H$8)
Jako wynik otrzymamy błąd ponieważ nie wpisaliśmy kryterium
Region (rys. nr 6).
rys. nr 6 — Błąd ze względu na brak podania kryterium
Kiedy wpiszemy Region np. zachód otrzymamy wynik – sprzedawca
Ewa (rys. nr 7).
rys. nr 7 Wynik działania funkcji dla dwóch kryteriów
Dla każdej kombinacji nazwy Fabryki i regionu mamy tylko
jednego sprzedawcę więc otrzymamy prawidłowy wynik.
Ciekawostką jest, że wszystkie funkcje bazodanowe mają tą
samą strukturę. Musimy podać trzy argumenty Bazę, Pole i Kryteria.
Przykładowo dla funkcji BD.SUMA podamy takie same argumenty.
Zapis będzie wyglądał następująco:
=BD.SUMA($A$1:$D$21;J7;$G$7:$H$8)
Jako Kryterium podaliśmy nazwę fabryki Motylek, wynikiem
będzie natomiast suma przychodów dla tej fabryki (rys. nr 8)
rys. nr 8 — Funkcja BD.SUMA
Funkcje te spełnia swe zadanie dla jednego rekordu nawet przy
bardzo rozbudowanych kryteriach. Kryteria te możemy łatwo budować bez potrzeby
tworzenia skomplikowanych formuł tablicowych.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszej poradzie omówimy wyszukiwanie binarne, czyli
wyszukiwanie przybliżone w Excelu. Temat ten omówimy na podstawie przykładowych
danych z rysunku nr 1. Wpis ten jest niejako kontynuacją tematu z poprzedniego
postu, dotyczącego funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym.
Opisaliśmy wtedy obrazowo jak działa dopasowanie przybliżone.
rys. nr 1 — Przykładowe dane
Dopasowanie przybliżone jest trochę bardziej skomplikowane od
dokładnego, ale działa szybciej. Musimy pamiętać, żeby zawsze mieć posortowane
dane (rosnąco, od najmniejszej do największej lub od A do Z). Kiedy nasze dane
nie będą posortowane to funkcja WYSZUKAJ.PIONOWO z dopasowaniem dokładnym
znajdzie wynik dla Miś (szuka od góry do dołu), ale jeśli mamy tych wyników
mamy bardzo dużą ilość zajmie to sporo czasu. Funkcja WYSZUKAJ.PIONOWO z
dopasowaniem przybliżonym działa dużo szybciej, ale wymogiem jest, aby dane
były posortowane, bo inaczej funkcja może zwrócić nam nieprawidłowy wynik.
Aby dobrze zrozumieć dlaczego może pojawić się nieprawidłowy
wynik opiszemy to na konkretnym przykładzie. Wyszukiwanie binarne można
powiedzieć, że dzieli na pół nasze tablice, listy danych. Załóżmy, że szukamy
Misia, w dopasowaniu przybliżonym nie zaczynamy go szukać od początku tylko od
środka listy danych. W naszym przykładzie na środku jest Kowboj, K jest
mniejsze od M (bo w alfabecie K jest przed M), więc wszystkie wartości powyżej
przestają nas interesować (rys. nr 2).
rys. nr 2 — Działanie dopasowania przybliżonego
Po
pominięciu wyników powyżej zostaje nam mniejsza tabelka, dokładnie Pastereczka,
Dinozaur i Lalka. Teraz na środku znajduje się Dinozaur, D jest mniejsze od M,
więc po raz kolejny pomijamy wszystkie wyniki powyżej Dinozaura (czyli tego
który aktualnie sprawdzamy). Zostaje nam jeden wynik – Lalka i otrzymujemy jako
wynik całej funkcji cenę Lalki (rys. nr 3).
rys. nr 3 — Sposób działania funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym
Dzieląc tak listy na pół, możemy bardzo szybko znaleźć
szukaną wartość, bo nie musimy sprawdzać niejako wszystkich wyników.
Pamiętajmy, że funkcja WYSZUKAJ.PIONOWO znajduje tylko jedną wartość – pierwszą
na którą trafi.
Podsumowując funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym
może zwrócić nam nieprawidłowy wynik dla nieposortowanych danych.
W drugim przykładzie mamy posortowane dane. Zaczynamy szukać
od polowy listy i trafiamy akurat na szukaną nazwę, więc nie kontynuujemy tej
drogi z poprzedniego przypadku, bo Miś=Miś (rys. nr 4).
rys. nr 3 — Wyszukiwanie binarne
Należy pamiętać, że Excel robi coś jeszcze przy dopasowaniu
przybliżonym nie kończy wyszukiwania gdy
znajdzie wartość tylko sprawdza dane poniżej, czy nie powtórzyła się szukana
wartość. Jeśli pod spodem jest więcej danych o takiej samej nazwie (Miś), to
Excel zawsze jako ostateczny wynik poda nam wartość przypisaną dla ostatniego
wystąpienia tej nazwy (rys. nr 5), ale takie same wartości muszą być tuż pod
sobą. Jeśli nazwa Miś powtórzy się drugi raz, ale w odstępie kilku nazw to
Excel już jej nie znajdzie.
rys. nr 5 — Ostatnia znaleziona wartość jako stateczny wynik
Analogicznie wygląda sytuacja z liczbami, a nawet jest dużo
prostsza do zrozumienia (rys. nr 6).
rys. nr 6 — Wyszukiwanie z dopasowanie przybliżonym na wartościach liczbowych
Szukamy wartości 62, aby wystawić ocenę. Sprawdzamy wartość w
połowie tabelki, otrzymujemy 70. Widzimy, iż 70>62, więc pomijamy wyniki
poniżej wartości którą rozpatrywaliśmy (rys. nr 7). Skracamy nasze dane do
wartości powyżej 70.
rys. nr 7 — Zasada działania wyszukiwania z dopasowaniem przybliżonym na liczbach
Rozpatrujemy teraz górną połowę wyników, sprawdzamy wartość
50 (w środku danych), wiemy że 50<60, więc pomijamy wartości powyżej 50 w
tabeli. Pozostaje nam jedna wartość 60, w ten sposób otrzymujemy wynik –
Przeciętnie.
Myślę,
że wyjaśniłem wystarczająco temat wyszukiwania binarnego. Jeśli
chcesz poznać bardziej precyzyjnie zasady działania wyszukiwania przybliżonego
w Excelu przeczytaj post na forum Billa Szysz https://www.excelforum.pl/topics1/formula-zwracajaca-tekst-ze-zbioru-komorek-vt51013.htm
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
