W tym poście wykonamy na podstawie przykładowych danych z rys. nr 1 ranking sprzedawców tablicowo. Tym razem omówimy prostą metodę. W poprzednich poradach (399–401) szliśmy torem myślenia Excela klasycznego, używaliśmy kolumn pomocniczych i różnych skomplikowanych funkcji żeby pokazać możliwości łączenia tych funkcji w jedną formułę.

rys1 - przykładowe dane
rys1 — przykładowe dane

Używaliśmy skomplikowanych formuł, żeby wyciągnąć dane do rankingu sprzedawców. Poprzednie posty są ważne, bo pokazują pewien tok myślenia jak łączyć dane, jak przekształcać poszczególne elementy. W tym poście opiszemy sposób, jak wyciągnąć ranking sprzedawców w jak najprostszy sposób, żeby jednak kolumna odpowiadała niejako za jedną kolumnę danych. Zaczniemy od listy unikatowych sprzedawców. Jesteśmy w Excelu tablicowym, więc skorzystamy z funkcji UNIKATOWE. Argumentem funkcji UNIKATOWE jest Tablica, czyli zakres z którego chcemy wyciągnąć unikatowe wartości (kolumna A – Sprzedawca). Jest to formuła tablicowa, więc mamy tutaj nomenklaturę tablicową, czyli kolumnę zapiszemy jako tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:

=UNIKATOWE(tSprzedażk[Sprzedawca])

Po zatwierdzeniu formuły otrzymamy listę unikatowych sprzedawców przedstawioną na rys. nr 2.

Rys. nr 2 – lista unikatowych sprzedawców
Rys. nr 2 – lista unikatowych sprzedawców

Ilość unikatowych sprzedawców nie zmieni się, kiedy zmienimy ich kolejność. Z tego względu możemy im dołożyć numerację w kolumnie Miejsce. Użyjemy do tego funkcji SEKWENCJA. Pierwszym argumentem funkcji są wiersze, czyli ilość wierszy wypełnionych listą unikatowych sprzedawców. Ilość tych wierszy obliczymy za pomocą funkcji ILE.WIERSZY. Argumentem tej funkcji jest tablica, czyli zakres, w którym chcemy obliczyć ilość wierszy (jesteśmy w Excelu tablicowym więc rozlany zakres zapiszemy H2#). Zapis całej formuły będzie wyglądał następująco:

=SEKWENCJA(ILE.WIERSZY(H2#))

Po zatwierdzeniu powyższej formuły otrzymamy numerację wierszy przedstawioną na rys. nr 3.

Rys. nr 3 – numeracja wierszy unikatowych sprzedawców
Rys. nr 3 – numeracja wierszy unikatowych sprzedawców

Mając listę unikatowych sprzedawców, możemy podliczyć (zsumować) ich wyniki sprzedaży. Zrobimy to przy użyciu funkcji SUMA.JEŻELI. Pierwszym argumentem funkcji jest zakres, czyli nasza kolumna B – Sprzedawca (tSprzedażk[Sprzedawca]). Drugi argument to kryteria, czyli lista naszych unikatowych sprzedawców z rozlanej formuły (H2#). Trzeci argument to suma_zakres, czyli kolumna po jakiej chcemy zsumować wartości (tSprzedażk[Sprzedaż]). Zapis formuły powinien wyglądać następująco:

=SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż])

Po zatwierdzeniu powyższej formuły otrzymamy wyniki sprzedaży poszczególnych sprzedawców przedstawione na rys. nr 4.

Rys. nr 4 – wartości sprzedaży poszczególnych sprzedawców
Rys. nr 4 – wartości sprzedaży poszczególnych sprzedawców

W Excelu tablicowym mamy możliwość posortowania jednej kolumny według drugiej, czyli możemy posortować listę unikatowych sprzedawców według kolumny Sprzedaż. Trzeba tylko odpowiednio te dane połączyć. Zaczniemy od skopiowania formuły funkcji SUMA.JEŻELI za pomocą skrótu klawiszowego Ctrl+C. Teraz w kolumnie Sprzedawca wpiszemy funkcję SORTUJ.WEDŁUG. Pierwszym argumentem funkcji jest tablica, czyli kolumna którą chcemy posortować, w naszym przypadku będzie to lista unikatowych sprzedawców uzyskana dzięki funkcji UNIKATOWE. Drugi argument funkcji to według_tablicy1, czyli kolumna według której chcemy posortować pierwszą kolumna. W naszym przykładzie będzie to kolumna z podsumowaniem sprzedaży – kolumna Sprzedaż, uzyskana dzięki funkcji SUMA.JEŻELI. Jednak w tej formule odwołujemy się do kolumny, która się rozlewa (H2#), więc musimy ją poprawić. Kolejny argument funkcji to kolejność_sortowania, gdzie mamy do wyboru sortowanie rosnące (wartość 1) lub malejące (wartość ‑1). Wybieramy wartość ‑1. Zapis formuły powinien wyglądać następująco:

=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż]);-1)

Jeśli zatwierdzimy formułę bez zmiany zakresu H2# w funkcji SUMA.JEŻELI, to pojawi się nam komunikat Excela o odwołaniach cyklicznych przedstawiony na rys. nr 5.

Rys. nr 5 – komunikat Excela
Rys. nr 5 – komunikat Excela

Powyższy komunikat potwierdzimy przyciskiem OK i otrzymamy dane przedstawione na rys. nr 6.

Rys. nr 6 – wyniki sortowania według
Rys. nr 6 – wyniki sortowania według

Jak widać na rysunku powyżej zepsuła nam się lista unikatowych sprzedawców. Wynika to z tego, że nie zmieniliśmy odwołania do rozlanej formuły (H2#) w formule funkcji SUMA.JEŻELI dla funkcji SORTUJ.WEDŁUG. Aby formuła zadziałała prawidłowo, musimy podmienić zakres H2# na formułę, ż której ten zakres powstał, czyli na UNIKATOWE(tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:

=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca];tSprzedażk[Sprzedaż]);-1)

Po zatwierdzeniu powyższej formuły otrzymamy prawidłowe wyniki sortowania według przedstawione na rys. nr 7.

Rys. nr 7 – prawidłowe wyniki sortowania
Rys. nr 7 – prawidłowe wyniki sortowania

Wykonaliśmy nasze zadanie za pomocą dwóch prostych formuł. Mamy ranking (numerację), posortowanych sprzedawców oraz wartości sprzedaży, które same się posortowały, dopasowując się do miejsca konkretnego sprzedawcy w rankingu. Jest to dużo prostszy sposób i bardziej zrozumiały niż formuły z poprzednich postów, gdzie używaliśmy wielu różnych funkcji.


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

Książka Mistrz Excela reklama