Excel — Ranking sprzedawców 1 formuła tablicowa — porada 400
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.
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.
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:
=SUMA.JEŻELI(tSprzedażk[Sprzedawca];G2#; tSprzedażk[Sprzedaż])
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:
=SUMA.JEŻELI(tSprzedażk[Sprzedawca];G2#; tSprzedażk[Sprzedaż])>= SUMA.JEŻELI(tSprzedażk[Sprzedawca];G2#; tSprzedażk[Sprzedaż])
Po zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 3.
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:
=TRANSPONUJ(SUMA.JEŻELI(tSprzedażk[Sprzedawca];G2#; tSprzedażk[Sprzedaż]))>= SUMA.JEŻELI(tSprzedażk[Sprzedawca];G2#; tSprzedażk[Sprzedaż])
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:
=–(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ż]))
Po zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 5.
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.WIERSZY dla 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.
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:
=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(G2#;1;1;0)))
Powyższą formułę zatwierdzamy i otrzymamy tablicę z rankingiem sprzedawców przedstawioną na rys. nr 7.
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:
=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))
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:
=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))
Na koniec w miejsce zakresu G2# wstawiamy funkcję z komórki G2, czyli UNIKATOWE(tSprzedażk[Sprzedawca])
Zapis całej formuły funkcji X.WYSZUKAJ powinien wyglądać następująco:
=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]))
Powyższą formułę zatwierdzamy i otrzymamy wyniki przedstawione na rys. nr 8.
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:
=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])))
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 WYBIERZ połą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.
Do wyznaczenia rankingu unikatowych sprzedawców użyliśmy jednej formuły, która zwróciła nam dwie kolumny danych (rys. nr 10).
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.
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
