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).
W tym poście pokażemy jak stworzyć ranking unikatowych sprzedawców za pomocą dużo mniej skomplikowanej formuły, niż ta, którą stworzyliśmy w poradzie nr 400 (rys. nr 2) https://exceliadam.pl/excel/excel-ranking-sprzedawcow-1-formula-tablicowa-porada-400 .
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:
=WYBIERZ({1\2}; SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]))); X.WYSZUKAJ(SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]))); MACIERZ.ILOCZYN(–(TRANSPONUJ(SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca]); tSprzedażk[Sprzedaż]))>= SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca]); tSprzedażk[Sprzedaż]));SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]));1;1;0)); UNIKATOWE(tSprzedażk[Sprzedawca])))
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).
Excel wyświetli nam komunikat o ilości takich zamian (rys. nr 4).
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:
=ZEZWALAJ(SprzedW; tSprzedażk[Sprzedawca];
Sprzedaż; tSprzedażk[Sprzedaż];
WYBIERZ({1\2}; SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW))); X.WYSZUKAJ(SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW))); MACIERZ.ILOCZYN(–(TRANSPONUJ(SUMA.JEŻELI(SprzedW; UNIKATOWE(SprzedW); Sprzedaż))>= SUMA.JEŻELI(SprzedW; UNIKATOWE(SprzedW); Sprzedaż));SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW));1;1;0)); UNIKATOWE(SprzedW)))
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:
=ZEZWALAJ(SprzedW; tSprzedażk[Sprzedawca];
Sprzedaż; tSprzedażk[Sprzedaż];
Sprzedawcy; UNIKATOWE(SprzedW);
Wiersze; ILE.WIERSZY(Sprzedawcy);
Wyniki; SUMA.JEŻELI(SprzedW; Sprzedawcy; Sprzedaż);
WYBIERZ({1\2}; SEKWENCJA(Wiersze); X.WYSZUKAJ(SEKWENCJA(Wiersze); MACIERZ.ILOCZYN(–(TRANSPONUJ(Wyniki)>= Wyniki);SEKWENCJA(Wiersze;1;1;0)); Sprzedawcy))
Po zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 5.
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.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
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.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY