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 omówimy stworzenie rankingu sprzedawców tablicowo za pomocą jednej formuły z użyciem funkcji ZEZWALAJ. Jest to kontynuacja tematu z poprzednich dwóch postów, w których najpierw rozwiązywaliśmy to zagadnienie za pomocą kolumn pomocniczych, a następnie za pomocą jednej formuły (stworzonej z pojedynczych formuł, jakich używaliśmy w kolumnach pomocniczych). Przypomnimy najpierw przykładowe dane do tego zadania (rys. nr 1).
Skrócimy
powyższą formułę stosując funkcję ZEZWALAJ. Z mojego punktu widzenia, wygodniej
jest zastosować nazewnictwo funkcji ZEZWALAJ w już istniejącej formule
(zastępując poszczególne elementy). Zastępujemy poszczególne elementy, ponieważ
zazwyczaj kiedy odwołujemy się do wcześniejszej formuły stosujemy odwołanie do jej
wyników a nie wpisujemy (kopiujemy) jej formuły. Zapis formuły z poprzedniej
porady wygląda następująco:
Aby wstawić Enter
w formule (przejść do niższego wiersza) musimy w trybie edycji komórki użyć skrótu
klawiszowego Alt+Enter. Zaczynamy od znaku równa się, a następnie wpisujemy
nazwę funkcji ZEZWALAJ.
Musimy mieć
tutaj "bystre oczko", żeby zauważyć, jakie elementy powtarzają się w naszej
skomplikowanej formule. Możemy łatwo zauważyć, że często powtarza się zakres
tSprzedażk[Sprzedawca], dlatego ten zakres zastąpimy jako pierwszy mniej
skomplikowaną nazwą. Pierwszym argumentem funkcji ZEZWALAJ jest nazwa1,
czyli wybrany przez nas element (nazwiemy go np.SprzedW, czyli wszyscy
sprzedawcy. Drugi argument funkcji to wartość_nazwy1, czyli wartość
naszego nazwanego argumentu – tablica tSprzedażk[Sprzedawca].
Aby za
każdym razem nie szukać tej dłuższej nazwy tSprzedażk[Sprzedawca], żeby ją
podmienić, musimy ją sobie skopiować. Następnie zaznaczamy dwie komórki (jedną
z naszą formułą, a drugą spoza tego zakresu) i używając skrótu klawiszowego Ctrl+H,
otwieramy okno Znajdowania i zamieniania. W tym oknie przechodzimy na
zakładkę Zamień i w polu Znajdź wklejamy skopiowaną nazwę zakresy sprzed
zmiany, a następnie w polu Zamień na wpisujemy nową nazwę nadaną w
funkcji ZEZWALAJ dla tego zakresu – SprzedW. Tak ustawione parametry
zatwierdzamy przyciskiem Zamień wszystkie (rys. nr 3).
Rys. nr 3 – okno Znajdowania i zamieniania
Excel wyświetli nam komunikat o ilości takich zamian (rys. nr 4).
Rys. nr 4 – komunikat Excela o ilości zamienionych nazw
Otrzymaliśmy
błąd w formule #NAZWA?, który wynika z tego, że został zamieniony również drugi
argument funkcji ZEZWALAJ. Musimy go poprawić i wpisać pierwotną nazwę.
Trzeci
argument funkcji to obliczenie_lub_nazwa2. U nas będzie to kolejny
arguemnt nazwa2. Szukamy teraz kolejnego elementu, który się powtarza w naszej
długiej formule – będzie to kolejny zakres tSprzedażk[Sprzedaż]. W formule
ponownie naciskamy skrót Alt+Enter, aby przejść do kolejnego wiersza i tam
podajemy kolejny argument funkcji – (nazwa2) Sprzedaż, trzeci argument, czyli
wartość_nazwy2 to będzie tSprzedażk[Sprzedaż]. Ponownie zaznaczamy jedną
komórkę z zakresu z naszą formułą oraz jedną z sąsiadujących i za pomocą skrótu
klawiszowego Ctrl+H, otwieramy okno Znajdowania i zamieniania. W tym oknie
analogicznie jak dla pierwszej nazwy w polu Znajdź wpisujemy wartość_nazwy2 (tSprzedażk[Sprzedaż]),
a w polu Zamień na wpisujemy nazwa2 (Sprzedaż). Zatwierdzamy zamienianie
przyciskiem Zamień wszystkie. Ponownie wyświetli nam się komunikat Excela o
ilości zmian i otrzymamy błąd w formule, wynikający z tego ze został zamieniony
również argument wartość_nazwy2. Wystarczy to zmienić i formuła zadziała
prawidłowo. Na tym etapie formuła funkcji ZEZWALAJ powinna wyglądać następująco:
Fragmenty
danych, które nazywamy możemy również ręcznie zamieniać w formule bez
korzystania z polecenia Znajdywania i zamieniania. Jednak wykorzystanie go daje
nam gwarancję, że żadne ich wystąpienie nam nie umknie.
Kolejnym
powtarzającym się elementem jest funkcja UNIKATOWE(SprzedW) – argument wartość_nazwa3,
którą zastąpimy słowem Sprzedawcy (nazwa3). Znowu analogicznie jak dla
poprzednich dwóch nazw zastępujemy odpowiednie nazwy polecenie Znajdywania i
zamieniania.
Następną
powtarzającą się nazwą jest funkcja ILE.WIERSZY(Sprzedawcy), czyli nasz kolejny
argument funkcji ZEZWALAJ – wartość_nazwy4, który zamienimy na Wiersze (nazwa4).
Ponownie zamieniamy wartości w całej formule.
Kolejna
nazwa, którą zamienimy to SUMA.JEŻELI(SprzedW; Sprzedawcy; Sprzedaż). Powtarza
się ona dwa razy, więc podmienimy ją ręcznie. Przypiszemy jej nazwę Wyniki.
Zapis całej formuły będzie wyglądał następująco:
Po
zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – wyniki działania funkcji ZEZWALAJ
Podsumowując,
najłatwiej było zastosować funkcje ZEZWALAJ na istniejącej długiej formule.
Dzięki jej wykorzystaniu mogliśmy podmienić elementy powtarzające się w
formule.
Nasza
formuła nadal działa dynamicznie, czyli kiedy wprowadzimy zmiany do danych
wejściowych, wyniki automatycznie się zaktualizują.
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 tym poście stworzymy ranking sprzedawców tablicowo za pomocą jednej formuły.
W poprzednim poście omawialiśmy takie samo rozwiązanie tablicowe, ale z wykorzystaniem kolumn pomocniczych (porada 399 https://exceliadam.pl/excel/excel-ranking-sprzedawcow-tablicowo-porada-399 ). Dane do tego zadania oraz kolumny pomocnicze z poprzedniego postu zostały przedstawione na rys. nr 1.
Rys. nr 1 – dane do rozwiązania zadania
W poprzedniej poradzie rozpisaliśmy rozwiązanie na poszczególne formuły pomocnicze w osobnych kolumnach. Dzięki temu rozwiązanie było proste. W tym poście połączymy te formuły w jedną. Zasada ogólnie jest prosta, czyli będziemy kopiować poszczególne formuły. Formułę z poprzedniego kroku będziemy kopiować i wstawiać do kolejnej, podstawiając ją w miejscu, gdzie odwołujemy się do poprzedniej formuły. To wszystko w takim zamyśle było by proste, ale niestety funkcja POZYCJA.NAJW nie radzi sobie z obliczeniami w swoim argumencie odwołanie. Jeśli do tego argumentu dodamy wartość 0 (zero), czyli teoretycznie nie zmienimy wyniku, to Excel wyświetli nam komunikat o nieprawidłowej formule przedstawiony na rys. nr 2.
Rys. nr 2 – komunikat Excela
Podobnie
zachowuje się funkcja SUMA.JEŻELI. Obie te funkcje nie pozwalają na
obliczenia w swoich argumentach. Podsumowując, nie możemy wstawiać formuł z
poprzedniego kroku do kolejnego. Z tego względu musimy użyć innego rozwiązania,
które pozwoli nam wyciągnąć miejsca w rankingu poszczególnych sprzedawców.
Niestety rozwiązanie to będzie skomplikowane. Tak naprawdę, żeby sprawdzić,
które miejsce ma dany sprzedawca, to musimy sprawdzić, ilu sprzedawców ma
lepsze bądź równe miejsce. Kopiujemy sobie zapis funkcji SUMA.JEŻELI.
Zapis wygląda następująco:
I tworzymy
zapis, w którym sprawdzimy czy nasze wyniki funkcji SUMA.JEŻELI są większe bądź
równe wynikom funkcji SUMA.JEŻELI. Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 3.
Rys. nr 3 – wyniki porównania dwóch funkcji SUMA.JEŻELI
Otrzymaliśmy
wartości logiczne PRAWDA ponieważ funkcja porównała ze sobą poszczególne
wiersze. Nam chodzi o to, aby funkcja porównała każdy wiersz ze wszystkimi
innymi. Taki efekt możemy uzyskać za pomocą funkcji TRANSPONUJ. Dzięki
tej funkcji transponujemy jedną z naszych tablic. Sprawi to, że otrzymamy
wyniki przedstawione na rys. nr 4. Zapis formuły powinien wyglądać następująco:
Rys. nr 4 – wyniki porównania po transponowaniu jednej z tablic
Przy takiej
orientacji w pierwszym wierszu mamy informację, ile wartości z porównania jest
mniejszych od sprawdzanej aktualnie wartości. Dla pierwszego wiersza
(sprzedawca Maria) mamy 5 wartości logicznych PRAWDA i jedną wartość FAŁSZ,
oznacza to, że 5 osób z listy sprzedawców było od niej lepszych. Można
zauważyć, że wynik się zgadza, ponieważ w tabeli powyżej w kolumnie I mamy
informację, że Maria jest na 5‑tym miejscu w rankingu.
Teraz
wartości logiczne PRAWDA i FAŁSZ chcemy zamienić na wartości 0 i 1. Możemy to
zrobić za pomocą podwójnej negacji, czyli wstawiamy przed formułą dwa znaki
minus. Zapis powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – zamiana wartości logicznych na 0 i 1
Teraz musimy zsumować wartości w poszczególnych wierszach. Gdybyśmy użyli funkcji SUMA.ILOCZYNÓW to otrzymalibyśmy zliczoną całą tablicę. Możemy tutaj wykorzystać funkcję MACIERZ.ILOCZYN, żeby każdy wiersz przemnożył się przez kolumnę danych, którą pokażemy. Funkcja ta będzie mnożyć poszczególne elementy z wierszy pierwszej tabeli przez kolumny z drugiej tabeli, a następnie wyniki tego mnożenia doda do siebie, by wstawić je w pojedynczą komórkę. Potrzebujemy kolumny o takiej samej ilości komórek co wiersz. Cała formuła z poprzedniego kroku to będzie nasz pierwszy argument funkcji, czyli tablica1. Teraz musimy wstawić drugą tablicę (argument tablica2). Aby stworzyć drugą tablicę, musimy użyć funkcji SEKWENCJA, w której musimy mieć taką samą liczbę kolumn i wierszy. Pierwszy argument funkcji to wiersze, który możemy wyznaczyć za pomocą funkcji ILE.WIERSZYdla zakresu G2# (listy unikatowych sprzedawców). Drugi argument funkcji to kolumny, czyli wpisujemy wartość 1, bo potrzebujemy jednaj kolumny. Trzeci argument to początek, czyli od jakiej wartości chcemy zacząć – u nas wartość 1. Czwarty argument to krok, czyli wartość 0. Kiedy podejrzymy argument tablica2 funkcji MACIERZ.ILOCZYN to możemy zauważyć, że mamy same wartości 1 co widać na rys. nr 6.
Rys. nr 6 – podgląd wartości argumentu tablica2
Z podglądu
edycji formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Cały
zapis formuły powinien wyglądać następująco:
Powyższą
formułę zatwierdzamy i otrzymamy tablicę z rankingiem sprzedawców przedstawioną
na rys. nr 7.
Rys. nr 7 – tablica z rankingiem sprzedawców
Stworzona
przez nas formuła jest dużo bardziej skomplikowana niż funkcja POZYCJA.NAJW
użyta w rozwiązaniu z kolumnami pomocniczym. Ta skomplikowana formuła poradziła
sobie doskonale z wyznaczeniem miejsc w rankingu poszczególnych sprzedawców. W
tej formule użyliśmy odwołania do zakres G2#, więc odwołanie to musimy jeszcze
zastąpić formułą z komórki G2. Zapis formuły będzie wtedy wyglądał następująco:
Mamy
odpowiednie wyniki i teraz możemy przejść na kolejny poziom obliczeń (ten,
który uzyskaliśmy w kolumnie K w poprzednim poście). Użyjemy funkcji SEKWENCJA
z komórki K2 i wstawimy ją w funkcję X.WYSZUKAJ. Funkcja X.WYSZUKAJ
miała formułę przedstawioną poniżej:
=X.WYSZUKAJ(K2#;I2#;G2)
Teraz w
miejsce poszczególnych zakresów rozlanych formuł musimy wstawić odpowiednie
formuły. Zakres K2# zastąpimy formułą z komórki K2, czyli
SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Następnie
zamiast zakresu I2# nie możemy wstawić formuły z komórki I2, ponieważ funkcja
ta nie radzi sobie z obliczeniami w swoich argumentach. Musimy tutaj wstawić
skomplikowaną formułę, dzięki której wyznaczyliśmy ranking sprzedawców. Formuła
ta wyglądała następująco:
Powyższą
formułę zatwierdzamy i otrzymamy wyniki przedstawione na rys. nr 8.
Rys. nr 8 – wyniki skomplikowanej formuły funkcji X.WYSZUKAJ
Tak jak
zaznaczyłem na wstępie, chcemy mieć jedną formułę do przydzielania miejsc. A na
tą chwilę mamy dwie – w komórkach K2 i L2. Mamy w wynikach dwie kolumny, które
możemy połączyć za pomocą funkcji WYBIERZ. Najpierw kopiujemy całą
formułę funkcji X.WYSZUKAJ. Następnie w komórce K2 wpisujemy funkcję WYBIERZ,
której pierwszym argumentem jest nr_arg, czyli argumenty jakie chcemy
wybrać (w naszym przykładzie pierwszy i drugi argument {1\2}). Używamy tutaj backslasha,
żeby poinformować Excela, że chodzi nam o kolumny danych, a nie wiersze. Drugi
argument funkcji to wartość1, czyli zapis formuły, dzięki której
uzyskaliśmy pierwszą kolumnę danych. Formuła wygląda następująco: SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Kolejny
argument to wartość2, czyli druga kolumna danych uzyskana za pomocą
skomplikowanej formuły funkcji X.WYSZUKAJ.
Zapis całej
formuły funkcji WYBIERZ powinien wyglądać następująco:
Po zatwierdzeniu
powyższej formuły otrzymamy dwie kolumny danych jednocześnie w kolumnach K i L
(takie same jak na rys. nr 8).
Podsumowując,
za pomocą funkcji WYBIERZpołączyliśmy dwie kolumny danych. Jak widać
otrzymaliśmy bardzo skomplikowaną formułę. Dlatego często lepiej jest zrobić
sobie kolumny pomocnicze, by ewentualnie później tylko przekleić formuły w
odpowiednie miejsca do funkcji wyższego poziomu. Gdyby nie funkcja POZYCJA.NAJW,
która nie radzi sobie z obliczeniami w jej argumentach, sprawa była by bardzo
prosta. Jednak tutaj musieliśmy ją zastąpić innym rozwiązaniem.
Mamy wyciągnięte
dane, więc teraz tak jak w poprzednim wideo zmodyfikujemy dane wejściowe przez
doklejenie dodatkowych wierszy. Sprawdzimy w ten sposób, czy nasza formuła
działa dynamicznie. Po doklejeniu dodatkowych danych nasze wyniki się
rozszerzyły o nowe pozycje, co widać na rys. nr 9.
Rys. nr 9 – zmienione dane po zmianach w danych wejściowych
Do wyznaczenia rankingu unikatowych sprzedawców użyliśmy jednej formuły, która zwróciła nam dwie kolumny danych (rys. nr 10).
Rys. nr 10 – skomplikowana formuła zwracająca dwie kolumny danych
Podsumowując,
nie zawsze możemy sobie pozwolić na tworzenie kolumn pomocniczych w analizie
danych. Dlatego możemy sobie je stworzyć tylko po to, by użyć ich do stworzenia
skomplikowanej formuły a potem je skasować.
W kolejnej
poradzie (nr 401) pomówimy o tym jak można ponazywać zakresy danych, żeby
skomplikowana formuła stała się krótsza i bardziej zrozumiała na pierwszy rzut
oka. Użyjemy do tego funkcji ZEZWALAJ.
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 tym poście
nauczymy się jak można stworzyć ranking sprzedawców tablicowo, korzystając z
formuł kolumn pomocniczych. Ten film rozpoczyna cykl trzech filmów (porada 399,
400 i 401). W pierwszej poradzie będziemy budować listę sprzedawców tablicowo z
użyciem kolumn pomocniczych (każde kolejne obliczenie jest w osobnej kolumnie).
W drugiej poradzie będziemy chcieli te dane połączyć w całość, co niestety nie
jest tak proste jak by się mogło wydawać. Na koniec w trzeciej poradzie tą
połączoną formułę ponazywamy, żeby wykorzystać funkcję ZEZWALAJ.
Zadanie to
wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Zaczynamy od
wyciągnięcia poszczególnych elementów tablicowo, czyli dane będą się same rozlewać,
co oznacza, że nasze dane będą dynamiczne. Najpierw potrzebujemy unikatowych
sprzedawców. Użyjemy tutaj funkcji UNIKATOWE, której pierwszym
argumentem jest Tablica, czyli kolumna tabeli z rys. nr 1 o nazwie Sprzedawca
(tSprzedażk[Sprzedawca]). Pozostałe argumenty są opcjonalne i je pomijamy.
Zapis formuły powinien wyglądać następująco:
=UNIKATOWE(tSprzedażk[Sprzedawca])
Powyższą
formułę zatwierdzamy, otrzymamy listę unikatowych sprzedawców przedstawioną na
rys. nr 2.
Rys. nr 2 – unikatowa lista sprzedawców
Tutaj nie
musimy sortować sprzedawców. Dopiero w kolejnej tabeli pomocniczej wyznaczymy
ich miejsca. W kolejnym etapie musimy dla naszych unikatowych sprzedawców
podliczyć sprzedaż. Użyjemy do tego funkcji SUMA.JEŻELI. Pierwszym argumentem
funkcji jest zakres, czyli kolumna, po której będziemy sprawdzać nasze
kryterium (tSprzedażk[Sprzedawca]). Drugi argument funkcji to kryteria,
czyli nasz rozlany zakres z listą unikatowych sprzedawców (G2#). Według nowej
nomenklatury nazw w Excelu, w zapisie dokładamy znak hash (#) do komórki, w
której jest formuła z rozlanym zakresem. Trzeci argument funkcji to suma_zakres,
czyli kolumna, po której chcemy zsumować wartości (tSprzedażk[Sprzedaż]). Zapis
całej formuły powinien wyglądać następująco:
Po
zatwierdzeniu formuły otrzymamy zsumowane wartości sprzedaży przedstawione na
rys. nr 3.
Rys. nr 3 – zsumowane wartości sprzedaży dla unikatowych sprzedawców
Teraz chcemy
znaleźć pozycje poszczególnych sprzedawców w rankingu względem wysokości
sprzedaży. Wykorzystamy tutaj funkcję POZYCJA.NAJW. Pierwszym argumentem
funkcji jest liczba, czyli zaznaczamy tutaj rozlany zakres w kolumnie H
(H2#). Drugi argument funkcji to odwołanie, w którym kolejny raz
zaznaczamy nasz rozlany zakres z kolumny H (H2#). W tym zapisie chodzi o to, że
funkcja POZYCJA.NAJW zwróci nam wynik dla każdej wartości sprzedaży z naszej
tablicy. Zapis całej formuły powinien wyglądać następująco:
=POZYCJA.NAJW(H2#;H2#)
Po
zatwierdzeniu formuły otrzymamy pozycje poszczególnych wartości sprzedaży
przedstawione na rys. nr 4.
Rys. nr 4 – pozycja poszczególnych wartości sprzedaży
Teraz potrzebujemy
ułożyć pozycje z kolumny I w odpowiedniej kolejności, aby stworzyć ranking sprzedawców.
Musimy stworzyć sekwencję liczb od 1 do 6, bo tylu mamy unikatowych
sprzedawców. Użyjemy tutaj funkcji SEKWENCJA, której pierwszym
argumentem są wiersze, czyli musimy zliczyć ilość wierszy, których
potrzebujemy. Wiersze zliczymy za pomocą funkcji ILE.WIERSZY, której
argumentem jest tablica (zakres listy unikatowych sprzedawców – G2#).
Zapis całej formuły powinien wyglądać następująco:
=SEKWENCJA(ILE.WIERSZY(G2#))
Powyższą
formułę zatwierdzamy, otrzymamy sekwencję od 1 do 6 przedstawioną na rys. nr 5.
Rys. nr 5 – sekwencja liczb od 1 do 6 (ilość wszystkich unikatowych sprzedawców)
Pozostaje
nam przypisać do tych uporządkowanych miejsc (rys. nr 5) odpowiednich
sprzedawców. W Excelu tablicowym możemy wykorzystać funkcję X.WYSZUKAJ. Pierwszym
argumentem funkcji jest szukana_wartość, czyli kolumna z naszą sekwencja
w odpowiedniej kolejności (K2#). Drugi argument to szukana_tablica,
czyli kolumna, w której będziemy szukać naszych wartości (I2#). Trzeci arguemnt
funkcji to zwracana_tablica, czyli kolumna, z której chcemy otrzymać
wartość — nazwę sprzedawcy (G2#). Cały zapis formuły powinien wyglądać
następująco:
=X.WYSZUKAJ(K2#;I2#;G2#)
Po
zatwierdzeniu formuły otrzymamy dopasowanych sprzedawców do rankingu
przedstawionych na rys. nr 6.
Rys. nr 6 – lista sprzedawców dopasowana do rankingu
Każda z użytych dziś formuł działa prawidłowo a dodatkowo jest dynamiczna. Mianowicie mamy przygotowaną w przykładowych danych drugą tabelkę, którą teraz dokleimy do naszych danych bazowych (rys. nr 7).
Rys. nr 7 – dodatkowa tablica danych, którą dokleimy do danych bazowych
Po doklejeniu danych możemy zauważyć w naszych kolumnach pomocniczych zmiany, mianowicie dane automatycznie się zaktualizowały. Jak widać na rys. nr 8 zmieniła się lista unikatowych sprzedawców i ilość miejsc w rankingu.
Rys. nr 8 – automatycznie zaktualizowane dane
Podsumowując, jeśli mamy możliwość rozbicia sobie takich obliczeń na poszczególne kroki, to wtedy obliczenia są proste i przede wszystkim wszystkie te formuły działają dynamicznie. Za każdym razem, kiedy zmienią się dane bazowe, zmienią się również wyniki. W kolejnej poradzie połączymy ten zapis. Niestety funkcja POZYCJA.NAJW będzie nam w tym przeszkadzać, co znacznie utrudni obliczenia i dołoży nam kolejnych obliczeń.
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 nauczymy się jak znaleźć n‑tą wartość spełniającą warunki (kryteria). Zadanie to wykonamy na podstawie przykładowych danych z rysunku nr 1.
Rys. nr 1 – przykładowe dane
W wyzwaniu nr 17 pokazywaliśmy jak rozwiązać taki problem klasycznym Excelem i wtedy w ogóle nie wykorzystywaliśmy funkcji związanych z wyszukiwaniem oprócz funkcji INDEKS. Funkcje jakie zostały tam wykorzystane zostały przedstawione na rys. nr 2.
Rys. nr 2 – wyszukiwanie wartości spełniającej 2 warunki w klasycznym Excelu
Próbowałem rozwiązać taki problem za pomocą rozwiązania zaproponowanego na stronie www.chandoo.org, które opierało się na wykorzystaniu funkcji X.WYSZUKAJ, ale to rozwiązanie jest zbyt skomplikowane dla przeciętnego użytkownika Excela. Rozwiązanie zostało przedstawione na rys. nr 3.
Rys. nr 3 – rozwiązanie zadania za pomocą sposobu ze strony www.chandoo.org
Nasze
zadanie wykonamy w Excelu tablicowym, wykorzystamy tutaj funkcje FILTRUJ
i INDEKS. Użyjemy najpierw funkcji FILTRUJ, która w chwili nagrywania
filmu (wrzesień 2019) była dostępna tylko w programie niejawnych testów Offica
(Insider). Pierwszym argumentem funkcji FILTRUJ jest tablica, czyli cała
tabela z danymi od komórki A3. Tabela zawiera dużo danych więc aby ją
odpowiednio i szybko zaznaczyć, musimy ustawić aktywną komórkę w komórce A3 i
za pomocą skrótu klawiszowego Ctrl+Shift+strzałka w bok zaznaczyć cały wiersz,
a następnie za pomocą skrótu Ctrl+Shift+strzałka w dół zaznaczyć obszar do
ostatniego wiersza. W celu powrotu do komórki, gdzie wpisujemy formułę musimy
użyć skrótu klawiszowego Ctrl+Backspace. Drugi argument funkcji to uwzględnienie,
czyli testy logiczne sprawdzające (inaczej filtry). W naszym teście chcemy
sprawdzić czy w kolumnie Województwo znajduje się to wybrane przez nas
województwo (np. lódzkie). Podsumowując zaznaczamy cała kolumnę Województwo
(bez nagłówka) i przyrównujemy ją do wartości, którą chcemy sprawdzić, czyli
B3:B300=F5. Zapis całej formuły powinien wyglądać następująco:
=FILTRUJ(A3:D300;B3:B300=F5)
Kiedy
podejrzymy sobie wynik formuły w trybie edycji komórki za pomocą klawisza F9,
otrzymamy sporą tablicę wartości logicznych PRAWDA i FAŁSZ, której fragment
został pokazany na rys. nr 4.
Rys. nr 4 – podgląd wyników funkcji FILTRUJ
Z podglądu wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. T wynikach wartości logiczne PRAWDA otrzymamy tylko dla sytuacji, kiedy w kolumnie Województwo pojawi się województwo łódzkie, natomiast wartości FAŁSZ będą wszędzie tam gdzie będzie każde inne województwo. Funkcja FILTRUJ zwróci nam tablicę danych dla każdego wystąpienia województwa łódzkiego przedstawioną na rys. nr 5.
Rys. nr 5 – tablica zwrócona przez funkcje FILTRUJ
W związku z tym, że nie mamy nałożonego odpowiedniego formatowania wyniki w pierwszej kolumnie nie przypominają dat. Wystarczy zaznaczyć kolumnę z datami i na karcie Narzędziagłówne w kategorii Liczba wybrać z listy rozwijanej typ danych Data krótka jak na rys. nr 6.
Rys. nr 6 – szybkie formatowanie danych – typ Data krótka
Otrzymaliśmy
dane spełniające jeden warunek (wybrane województwo), a nam zależy na
otrzymaniu danych spełniających 2 warunki. Teraz w formule funkcji wystarczy
dołożyć drugi filtr (drugie kryterium) dotyczący sprzedawcy. Ze względu na to,
że operacje porównania są wykonywane w Excelu na końcu musimy zapisać je w
nawiasach. Aby otrzymać drugie kryterium (oba kryteria mają być spełnione
jednocześnie) musimy te dwa kryteria przemnożyć przez siebie. W drugim
kryterium musimy porównać dane z kolumny Sprzedawca do określonego (wybranego
przez nas) sprzedawcy, czyli zapis testu logicznego powinien wyglądać
następująco: C3:C300=G5. Zapis całej formuły funkcji FILTRUJ powinien wyglądać
następująco:
=FILTRUJ(A3:D300;(B3:B300=F5)*(C3:C300=G5))
Dzięki
takiemu zapisowi otrzymaliśmy filtrowanie danych po 2 kryteriach przedstawione
na rys. nr 7.
Rys. nr 7 – dane spełniające dwa kryteria
Otrzymaliśmy dane spełniające dwa kryteria, czyli każde wystąpienie województwa łódzkiego i sprzedawcy Kinga. Nasz nałożony filtr jest dynamiczny, mianowicie możemy zmienić sobie nazwę zarówno województwa jak i sprzedawcy rozwijając listy rozwijane w odpowiednich komórkach (rys. nr 8).
Rys. nr 8 – dynamiczny charakter nałożonego filtru
Podsumowując
funkcja FILTRUJ zwraca nam tablicę danych. Naszym celem jest znalezienie n‑tej
wartości spełniającej podane warunki. Załóżmy że chcemy wyciągnąć z danych 5‑ty
wiersz (n=5). Możemy to zrobić za pomocą INDEKS, wystarczy, że do
funkcji INDEKS włożymy tablicę którą otrzymaliśmy z funkcji FILTRUJ. Pierwszym
argumentem funkcji INDEKS jest tablica, czyli tablica otrzymana za
pomocą funkcji FILTRUJ. Drugi argument funkcji to nr_wiersza, czyli
wartość parametru n (w naszym przykładzie wartość 5 zapisana w komórce F2).
Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu formuły otrzymamy jeden wiersz (5‑ty wiersz) przedstawiony na
rys. nr 9.
Rys. nr 9 – 5‑ty wiersz zwrócony przez funkcję INDEKS
Ze względu
na to, że nas interesowała w tym przypadku tylko kwota a nie cały wiersz,
możemy ograniczyć zakres tablicy w formule funkcji FILTRUJ. Wystarczy zmienić
zakres danych A3:D300 na D3:D300. Zapis całej formuły powinien wyglądać
następująco:
Po
zatwierdzeniu formuły otrzymamy jeden konkretny wynik przedstawiony na rys. nr 10.
Rys. nr 10 – wynik funkcji INDEKS po zmianie zakresu w funkcji FILTRUJ
Jak łatwo zauważyć, wynik ma dziwną postać. Wystarczy zmienić formatowanie na walutowe, aby otrzymać prawidłowo pokazaną daną za pomocą skrótu klawiszowego Ctrl+Shift+4 (rys. nr 11).
Rys. nr 11 – zmiana formatowania na walutowe za pomocą skrótu klawiszowego
Podsumowując w tym przykładzie nie korzystaliśmy z funkcji X.WYSZUKAJ jak w klasycznym Excelu, aby odnaleźć n‑tą wartość, dzięki temu nasza formuła jest prosta (o wiele mniej skomplikowana niż te przedstawione na rys. nr 2 i 3). Czasami praca w Excelu polega na obliczeniu jakichś wartości w najłatwiejszy sposób, nie zawsze trzeba na siłę używać skomplikowanych funkcji, które na pierwszy rzut oka wydają się oczywiste do rozwiązania danego zadania.
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.
