0
0 Produkty w koszyku

No products in the cart.

Excel — Kwota słownie formułą funkcja ZEZWALAJ — porada 403

W tym poście pokażemy jak uzyskać zapis słowny danej kwoty za pomocą formuły.

Formułę tą zapożyczyłem od Bila Szysza. Dzięki użyciu funkcji ZEZWALAJ możemy sobie pozwolić na stworzenie w miarę sensownej długości formułę (rys. nr 1).

Rys. nr 1 – formuła funkcji ZEZWALAJ
Rys. nr 1 – formuła funkcji ZEZWALAJ

Tak naprawdę możemy użyć gotowej formuły Billa Szysza. Wystarczy w niej podmienić jedną wartość, czyli odwołanie do komórki, z której kwotę chcesz przekształcić. Reszta formułe zadziała automatycznie.

Wcześniej takie rozwiązania widziałem tylko w VBA, które dla zwykłego użytkownika jest dużo trudniejsze. Ogólnie pojedyncze formuły są bardzo proste, problem stanowi połączenie tych formuł w jedną całość. Dlatego rozpisałem tą formułę krok po kroku, ponadto ponazywałem poszczególne elementy, żeby można to było łatwiej zrozumieć (rys. nr 2).

Rys. nr 2 – rozpisanie formuły krok po kroku
Rys. nr 2 – rozpisanie formuły krok po kroku

W pierwszym kroku musimy mieć liczbę, którą chcemy zapisać słownie, następnie musimy z niej wyciągnąć liczbę całkowitą, a w zasadzie moduł liczby, bo jest ona ujemna. Ucinamy końcówkę z groszami i pozbywamy się minusa z przodu. Trzy elementy formuły dają nam możliwość obliczenia tekstu dla jednostek, tysięcy i milionów (rys. nr 3).

Rys. nr 3 – wyliczenie tekstu dla jednostek, tysięcy i milionów
Rys. nr 3 – wyliczenie tekstu dla jednostek, tysięcy i milionów

Tak naprawdę są to te same formuły, tylko operują na innych częściach liczby. W kolejnym etapie musimy sobie przygotować rozpisaną dużą ilość liczb, żeby Excel wiedział jakiej końcówki słowa ma użyć. W języku polskim niestety zapis słowny ma wiele zmiennych końcówek (rys. nr 4).

Rys. nr 4 – rozpisany zapis słowny liczb
Rys. nr 4 – rozpisany zapis słowny liczb

W formułach musimy odwoływać się do tych przygotowanych zakresów, aby zapis słowny był prawidłowy z punktu widzenia języka polskiego.

Podsumowując, kiedy potrzebujesz uzyskać zapis słowny kwot pieniężnych, możesz użyć gotowej formuły. Wystarczy zmienić odwołanie do komórki z liczbą.


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

Excel — Ranking sprzedawców proste formuły tablicowe — porada 402

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

Excel — Ranking sprzedawców tablicowo 1 formuła z funkcją ZEZWALAJ Porada 401

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).

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

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 .

Rys. nr 2 – skomplikowana formuła z porady nr 400
Rys. nr 2 – skomplikowana formuła z porady nr 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).

Rys. nr 3 – okno Znajdowania i zamieniania
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
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:

=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.

Rys. nr 5 – wyniki działania funkcji ZEZWALAJ
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.

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

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.

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:

=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.

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:

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

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:

=–(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.

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.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.

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:

=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.

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:

=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.

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:

=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.

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.

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

Excel — Ranking sprzedawców tablicowo — porada 399

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
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:

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

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
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.

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