Tag: Zaawansowany

Mediana w tabeli przestawnej
Mediana w tabeli przestawnej na 3 sposoby

Tabela przestawna to jedno z najcenniejszych narzędzi Excela. Jednak, jak prawie wszystko podlega pewnym ograniczeniom. Jednym z nich jest możliwość prostego policzenia niektórych miar statystycznych, takich jak: mediana (wartość środkowa) czy dominanta (wartość występująca najczęściej).

Mediana w tabeli przestawnej to było jedno z wyzwań jakie miałem analizując zawodowo wynagrodzenia. Jak wiadomo, zazwyczaj mediana jest dużo lepszym miernikiem do analizy wynagrodzeń, niż średnia. Stąd możliwość jej wykorzystania w tak elastycznym narzędziu jak tabela przestawna, było dla mnie więcej niż pożądane.

W tym artykule pokażę Ci 3 sposoby, na połączenie elastyczności tabeli przestawnej z medianą.

Mediana w tabeli przestawnej, a w zasadzie obok tabeli

Oczywiście jak zawsze, zachęcam Cię do jednoczesnych ćwiczeń, wraz z lekturą artykułu. Pobierz plik i działaj ze mną. Gotowe rozwiązania otrzymują subskrybenci naszego newslettera.

Na początku wykonajmy standardowe kroki, aby stworzyć tabelę przestawną. Następnie ułożymy dane, tak aby analizować wynagrodzenia z perspektywy działów i kategorii zaszeregowania (grade’ów). Gdybyśmy, chcieli wyciągnąć średnie wynagrodzenie dla takiego układu, to nasza tabela mogłaby wyglądać tak:

Oczywiście nam zależy na medianie. Najprostszym ale mało eleganckim i mało elastycznym sposobem, będzie wyliczenie mediany w kolumnie obok pivota. A zatem, troszeczkę tutaj oszukujemy, bo mediana nie jest częścią tabeli przestawnej, a jedynie „korzysta” z danych w niej zawartych. Zmieniając dane w tabeli, automatycznie wylicza się mediana i pozornie (przy odpowiednim formatowaniu) całość wygląda ok.

W tym przypadku, aby dokonać tych wyliczeń skorzystamy z formuł tablicowych. Odpowieni zapis może wyglądać tak jak niżej (dane źródłowe mają postać tabeli strukturalnej):
```
{=JEŻELI.BŁĄD(MEDIANA(JEŻELI(Dane[[#Wszystko];[Dział]]=$A4;JEŻELI(Dane[[#Wszystko];[Grade]]=$B4;Dane[[#Wszystko];[Wynagrodzenie]])));"")}
```
Taki sposób podlega licznym ograniczeniom. Oczywistym jest, że jeżeli zmienisz układ tabeli, np. dodasz kolejną kategorię danych, wg których mediana ma być wyliczana – całość się posypie. A zatem ta metoda jest ok, ale tylko jeżeli „na szybko” chcesz pokazać medianę w statycznym układzie danych.

Mediana w tabeli przestawnej – sposób (nieco) lepszy

Korzystając z formuły tablicowej możemy policzyć medianę (i inne statystyki) dla wielu warunków. Zamiast pokazywać ją obok tabeli przestawnej – dodajmy kolejną kolumnę w tabeli z danymi źródłowymi. Następnie z całości stworzymy tabelę przestawną. Kolejne kroki będą wyglądąły tak:
1. Liczymy medianę w kolumnie (obok danych źródłowych) dla podanych warunków, czyli dla danego działu i grade’u. Formuła może wyglądać następująco:
```
{=JEŻELI.BŁĄD(MEDIANA(JEŻELI(Dane[[#Wszystko];[Dział]]=[@Dział];JEŻELI(Dane[[#Wszystko];[Grade]]=[@Grade];Dane[[#Wszystko];[Wynagrodzenie]])));"")}
```
1. Z całości danych tworzymy tabelę przestawną.
2. Układamy dane w pożądany przez nas sposób, dodając kolumnę z medianą do pola wartości.
3. W oknie dialogowym USTAWIENIA POLA WARTOŚCI wybierz ŚREDNIĄ w PODSUMUJ POLE WARTOŚCI WEDŁUG.
4. Zmień nazwę pola np. na „Mediana wynagrodzeń”. Nazwa musi być różna od nazwy kolumny w tabeli z danymi źródłowymi.
Efekt jest teraz taki, że mediana jest częścią naszej tabeli przestawnej. Jednak i ten sposób podlega pewnym istotnym ograniczeniom. Tak naprawdę mamy bowiem do czynienia ze średnią z mediany! Jeżeli używasz tego sposobu świadomie to ok. Ale oczywiście wystarczy jedna zmiana w układzie tabeli, aby wpłynąć na wyliczenia i błędnie zinterpretować uzyskane informacje.

Jeżeli chcesz liczyć medianę efektywnie i bezbłędnie – takie podejście również nie jest wystarczające. Przejdźmy zatem do najlepszego rozwiązania.

Mediana w tabeli przestawnej – profesjonalnie!

Aby osiągnąć nasz cel wykorzystamy Power Pivot. Stworzymy tabelę przestawną opartą o model danych, a medianę wyliczymy jako tzw. miarę. Jeżeli dopiero teraz poczułeś niepewność, to spokojnie… nie będziemy robili niczego szczególnie skomplikowanego 🙂

Więcej na temat Power Pivot i modelu danych przeczytasz w cyklu artykułów:
Aby włączyć dodatek Power Pivot, idziemy na wstążkę i wybieramy PLIK > OPCJE > DODATKI > DODATKI COM. Klikamy przycisk przejdź, a następnie zaznaczamy Microsoft Power Pivot. Na wstążce zobaczysz nową kartę.

Ponownie utwórz tabelę przestawną ale tym razem, w menu tworzenia tabeli zaznacz opcję DODAJ TE DANE DO MODELU DANYCH.

Ułóż dane w taki sposób jak wcześniej, tzn. umieszczając działy i grade’y w polach wierszy. Teraz przejdź na kartę POWER PIVOT i wybierz polecenie ZARZĄDZAJ.

To właśnie tutaj utworzymy medianę w postaci miary. Kliknij w dowolne pole pod tabelą z danymi i wpisz nazwę nowej miary.

Miary są tworzone z wykorzystaniem języka DAX. Medianę możemy policzyć w taki sposób:
```
Mediana:=MEDIAN([Wynagrodzenie])
```
To wszystko co należało zrobić w Power Pivocie. Przechodzimy z powrotem do Excela. W tabeli przestawnej automatycznie pojawi się nowa miara, którą teraz przeniesiemy do pola wartości.

W ten sposób pozbyliśmy się wszystkich wad poprzednich sposobów i mamy wszelkie zalety tabeli przestawnej. Możemy zmieniać, dodawać, usuwać kolejne kategorie, a mediana będzie na bieżąco wyliczana.

Oczywiście tabele przestawne, miary i model danych są powszechnie wykorzystywane w interaktywnych dashboardach. Jeżeli chcesz takie tworzyć, a nie masz doświadczenia zacznij od naszego bezpłatnego ebooka i gotowego szablonu.

Podsumowanie

Opisałem 3 sposoby na medianę w tabeli przestawnej. Oczywiście możesz użyć innych miar statystycznych, które nie są standardowo dostępne w tabeli przestawnej. Model danych i możliwość korzystania z miar jest fantastycznym sposobem na zaawansowane kalkulacje i analizy danych.

Starałem się, aby artykuł okazał się pomocny. Jeżeli też tak uważasz – proszę podziel się nim w swoich mediach społecznościowych, albo pokaż naszego bloga kolegom w pracy 🙂

Jeżeli natomiast znasz inne sposoby na wyliczenie mediany w tabeli przestawnej – koniecznie podziel się z nami w komentarzu.

Łukasz Pietrzak
23 listopada 2021
Jak stworzyć dowolną mapę w Excelu?
Jak stworzyć dowolną mapę w Excelu? (działa na starszych wersjach)

Użytkownicy nowych wersji Excela (2019 czy 365) mają fajnie. Mogą korzystać z nowych, ciekawych funkcjonalności. Na przykład z wykresu typu kartogram. Dzięki temu, za pomocą kilku kliknięć mogą wizualizować swoje dane na mapach. Wystarczy mieć odpowiednie dane geolokalizacyjne (np. kraj, województwo).

No i fajnie. Ale co ma zrobić użytkownik starszej wersji? Jak, przykładowo ma pokazać dane na mapie Polski w Excelu? Nie załamujemy rąk 🙂 Mamy rozwiązanie, które jest na tyle elastyczne, że będziemy mogli z niego skorzystać w dowolnej wersji Excela.

Mało tego, dzięki tej metodzie – pokażesz dane na dowolnej mapie czy schemacie. Oczywiście taka elastyczna metoda, wymaga nieco więcej pracy i kombinowania. Ale nie na tyle, aby nie spróbować. Do dzieła!

Mapa Polski: przygotowanie danych

Będziesz potrzebował pliku graficznego z mapą oraz danych. Powiedzmy, że w naszym przykładzie będziemy chcieli pokazać wielkość sprzedaży dla poszczególnych województw.

Wstawiamy obrazek naszej mapy do arkusza. Na wstążce WSTAWIANIE wybieramy OBRAZY > TO URZĄDZENIE i szukamy mapy, którą wcześniej zapisaliśmy na dysku.

Mamy już dwie niezbędne rzeczy, aby stworzyć naszą mapę Polski. Obrazek, który ją reprezentuje oraz dane, które mają się na niej pojawić. No właśnie, ale jak to zrobić?

Posłużymy się pewnym trikiem. Dane przedstawimy w formie wykresu bąbelkowego, a następnie nałożymy w odpowiedni sposób taki wykres na mapę. Wykres podobnie jak mapa jest dwuwymiarowa, a więc ma jakieś współrzędne X i Y. Wykorzystamy ten fakt.

Mapa Polski z wykorzystaniem wykresu bąbelkowego

Każdy bąbel na wykresie ma jakieś współrzędne X i Y. Jeżeli dla każdego województwa mamy dane, to powstanie 16 bąbli, których położeniem możemy sterować zmieniając parametry X i Y.

Tworzymy zatem dwie kolumny pomocnicze do naszych danych sprzedażowych.

Na razie wpisz dane „na oko” – tak jak mniej więcej rozłożone są województwa na osiach X i Y. Chodzi o to, że np. województwo lubelskie będzie miało większą wartość współrzędnej X niż zachodniopomorskie.

Teraz na podstawie trzech kolumn, tj. wartość sprzedaży, współrzędna X i współrzędna Y – stwórz wykres bąbelkowy. Następnie zmień rozmiar tego wykresu dopasowując go idealnie do rozmiaru Twojej mapy. Wykres powinien być nałożony na mapę, tak aby nie było jej widać.

Po wykonaniu tych kroków możesz zobaczyć mniej więcej taki obrazek jak niżej. Od razu widać kilka problemów. Po pierwsze bąble nie są porozkładane tak jak poszczególne województwa. Po drugie są za duże. W końcu – nie ma informacji o wielkości sprzedaży czy nazwy województwa. Za chwilę, krok po kroku poradzimy sobie z tymi problemami.

Dostosowujemy wykres bąbelkowy

Formatujemy nasz wykres:
1. Przede wszystkim zmieniamy ustawienia serii. Zaznacz dane i dla serii 1 – edytuj wartości X. Będą to współrzędne X z naszej tabeli danych. Z wartościami Y postępujemy analogicznie.
2. Wielkość bąbelków to wartości sprzedaży z tabeli danych. Teraz mają domyślną (za dużą) wielkość. Zmieniamy ich skalę, żeby nie były takie duże: FORMATUJ SERIĘ DANYCH > SKALUJ ROZMIAR BĄBELKA (np. do 30)
3. Na tym etapie możemy pobawić się trochę w dostosowywanie położenia bąbli na mapie, tak aby odpowiadały położeniom województw.
4. Usuwamy tytuł wykresu, oś X i Y. Kasujemy wszystkie linie pomocnicze.
5. Jako wypełnienie wybieramy BRAK WYPEŁNIENIA. Dzięki temu osiągniemy efekt przezroczystości i pod warstwą naszego wykresu ukaże się mapa Polski. Efekt naniesienia danych na mapę mamy prawie osiągnięty.
6. Teraz zrobimy tak, żeby zamiast bąbelków pokazywały się wartości i nazwy województw. Robimy to w następujący sposób: klikamy na mapę – klikamy „plusik” w prawym górnym rogu (elementy wykresu) i wybieramy polecenie ETYKIETY DANYCH > WIĘCEJ OPCJI. Przechodzimy na opcje etykiety. Wyłączamy wartości X i włączamy wartość bąbelka. Nazwy województw pobierzemy z komórek. Zaznaczamy WARTOŚĆ Z KOMÓREK i wskazujemy nazwy województw z tabeli.
7. Na koniec możemy rozdzielić nazwę województwa i wartość sprzedaży, tak aby pokazywały się w osobnych liniach. W związku z powyższym jako SEPARATOR wybieramy NOWY WIERSZ. Formatujemy etykiety wg uznania, tak aby były dobrze widoczne na mapie.
Na koniec zgrupujemy wykres z mapą, tak żeby był to jeden element. Zaznaczamy mapę i z wciśniętym klawiszem CTRL, zaznaczamy wykres. Prawy przycisk myszy i wybieramy polecenie GRUPUJ.

Podsumowanie

Jak widzisz można stworzyć mapę w Excelu nie posiadając najnowszej wersji programu i możliwości jakie daje kartogram. Owszem kosztuje to nieco więcej pracy ale efekt jest zadowalający.

Ponadto jest to metoda na tyle elastyczna, że możesz ją stosować także w innych niż wykres przypadkach. Na przykład jeśli musisz pokazać dane na jakimś schemacie czy projekcie. Wystarczy odpowiedni obrazek i trochę pracy na wykresach.

Znasz inne podejście? Napisz w komentarzu.
9 marca 2021
Dynamiczna etykieta danych na wykresie liniowym
Dynamiczna etykieta danych na wykresie liniowym

Powiedzmy, że co miesiąc aktualizujesz jakiś raport. Masz podpięty wykres, który pokazuje plan i wykonanie w kolejnych okresach. Na wykresie standardowo legenda, pokazująca że „ta linia” to plan, a „ta” to wykonanie.

Czy nie byłoby świetnie, gdyby zamiast konieczności odczytywania legendy, etykiety opisujące dane znalazły się bezpośrednio na wykresie. Najlepiej na końcu i to bez względu na to ile kolejnych okresów dodasz?

Dynamiczna etykieta danych to sposób na rozwiązanie powyższego problemu. Przeczytaj i zobacz jak ją stworzyć na wykresie liniowym.

Dane

Do każdego wykresu możesz podpiąć etykiety i pokazywać na nich na przykład nazwę serii danych. Jeśli zastosujesz standardowy sposób, to nazwa tej serii pokaże się w każdym punkcie danych. Ale jak wspomniałem na początku artykułu, my chcemy aby etykieta zawsze pokazywała się tylko na końcu. Zatem do dzieła. Pobierz plik i działaj ze mną.

Pierwszą rzeczą jaką musimy zrobić to dodać dwie pomocnicze kolumny. Pierwsza kolumna to będzie „Wykonanie”. Drugą kolumną będzie „Plan”. Pomocnicze kolumny pozwolą wyłapać zawsze ostatni wiersz w naszych danych. To ważne, bo właśnie dzięki temu będzie możliwe pokazanie na końcu linii dynamicznej etykiety danych.

Zatem, zgodnie z powyższym obrazkiem – ostatnim wierszem dla wykonania roku bieżącego jest sierpień. I właśnie ta wartość powinna się pokazać w kolumnie pomocniczej. Dla uproszczenia przyjmijmy, że wartości planu mamy na cały rok i od razu możemy przypisać adres komórki z grudnia.

W naszym przykładzie w komórce I39 użyję po prostu formuły:
```
=D39
```
Formuła

Nieco trudniej będzie z formułą wychwytującą ostatnią wpisaną wartość w kolumnie z wykonaniem. Jest kilka metod, aby sobie poradzić z tym zadaniem. My skorzystamy ze sposobu z formułą, która wykorzystuje poniższe funkcje:
- ILE.NIEPUSTYCH
- ILE.WIERSZY
- BRAK
- JEŻELI.
Pomysł na formułę jest następujący:
1. Funkcją ILE.NIEPUSTYCH sprawdzimy ile komórek w kolumnie „Wykonanie” jest niepustych (czyli ma wpisaną jakąś wartość)
2. Następnie porównamy te wartość z numerem wiersza, w zakresie naszych danych.
3. Jeżeli te dwa elementy będą równe, to będzie oznaczać, że jest to nasza ostatnia użyta komórka. Do porównania wykorzystamy oczywiście funkcję JEŻELI.
4. Jeśli wartości z punktów 1 i 2 nie będą równe – w komórce powinien się pojawić błąd #N/D!. Aby tak się stało – użyjemy formuły BŁĄD. Dlaczego ma się pojawić błąd? O tym nieco później.
Formuła w komórce H28 będzie wyglądała tak;
```
=JEŻELI(ILE.NIEPUSTYCH($C$28:$C$39)=ILE.WIERSZY($C$28:C28);C28;BRAK())
```
Przeanalizujmy poszczególne fragmenty formuły. Zazwyczaj bardziej złożone formuły warto rozkładać na czynniki pierwsze.
```
=JEŻELI(
```
W dalszej części formuły sprawdzamy dwie rzeczy. Po pierwsze ile jest niepustych wierszy, a po drugie ile jest wierszy w danym obszarze. Musimy przeprowadzić test logiczny, czyli po prostu sprawdzić czy obie wartości są sobie równe. Jak wiadomo najlepiej takie zadanie zrealizuje funkcja JEŻELI.
```
ILE.NIEPUSTYCH($C$28:$C$39)=ILE.WIERSZY($C$28:C28)
```
W tym fragmencie następuje porównanie, o którym mowa powyżej. Zauważ, że adres w funkcji ILE.WIERSZY ma postać mieszaną. To dlatego, że formułę będziemy kopiować w dół i zależy nam, aby początkowa komórka była zawsze taka sama. Zmieniać się będzie końcowa komórka zakresu. I właśnie tu zadziała funkcja ILE.WIERSZY. Zobacz na przykładzie:
```
;C28;BRAK())
```
Właściwie reszta powinna być już prosta. Funkcja ILE.NIEPUSTYCH policzy maksymalną liczbę użytych komórek, natomiast funkcja ILE.WIERSZY wskaże kolejne numery wierszy. Po teście, czy obie wartości są sobie równe za pomocą funkcji JEŻELI – w kolejnym fragmencie wskazujemy co ma się wydarzyć jeżeli to prawda, a co jeżeli fałsz. Jeśli obie wartości będą równe to chcemy wskazać wartość komórki z roku bieżącego. To będzie ostatnia użyta komórka. W przypadku fałszu, ma zadziałać funkcja BŁĄD(), która da wynik #N/D!. Potrzebujemy właśnie błędu, bo dzięki temu na wykresie nie będzie widać wartości zerowych.

Na podstawie tak przygotowanych danych możemy stworzyć wykres.

Wykres

Zaznaczamy zakres danych B27:D39 oraz H27:I39 (z wciśniętym klawiszem CTRL), a zatem obszar naszych danych podstawowych i dwie pomocnicze kolumny z etykietami. Wybieramy wykres liniowy na wstążce WSTAWIANIE. Teraz możemy go odpowiednio sformatować, czyli przykładowo:
- usuwamy linie siatki, obramowanie i tło,
- dostosowujemy tytuł,
- zmieniamy kolory i grubość linii.
Następnie dodajemy etykiety danych. Najlepiej kliknij plusik obok wykresu i z menu wybierz odpowiednią opcję.

Po włączeniu etykiet, zauważysz że włączyły się etykiety dla wszystkich danych. Nam zależy na tym, aby widoczna była tylko etykieta z opisem linii i to zawsze na końcu tej linii. W końcu ma to być dynamiczna etykieta danych, a więc ma się przesuwać wraz z ich zmianą.

To co musisz teraz zrobić, można sprowadzić do następujących kroków:
1. Dla serii „Plan” i „Rok bieżący” – czyli danych podstawowych – kliknij prawym przyciskiem myszy na etykietę danych i wybierz FORMATUJ ETYKIETY DANYCH.
2. Wyłącz wartości w opcjach etykiet.
3. Powyższe kroki wykonaj dla obu serii.
4. Dla serii obu kolumn pomocniczych – kliknij prawym przyciskiem myszy na etykietę danych i wybierz FORMATUJ ETYKIETY DANYCH.
5. Włącz nazwę serii w opcjach etykiet.
6. Wyłącz wartości.
Finalnie możesz (i w sumie powinieneś) usunąć legendę z wykresu. Cała idea tej metody polega właśnie na tym, aby informacje opisujące dane znalazły się bezpośrednio na wykresie – bez potrzeby dodatkowego ich opisywania w legendzie.

Wypadałoby jeszcze ukryć dane pomocnicze w kolumnach. Użyj magicznej białej czcionki 🙂 lub ukryj kolumny. Pamiętaj jednak, aby w tym drugim przypadku wybrać z opcji wykresu polecenie POKAŻ DANE W UKRYTYCH WIERSZACH I KOLUMNACH.

Podsumowanie

Opisany powyżej wykres możesz z powodzeniem wykorzystywać w różnego rodzaju raportach i dashboardach. Jest np. użyteczny przy pokazywaniu zmian w czasie w stosunku do jakieś bazowej wartości (wykonanie do budżetu, rok bieżący do poprzedniego itd.)

Co równie ważne, dynamiczna etykieta danych na takim wykresie jest dobrym sposobem na ograniczenie ilość informacji do niezbędnego minimum. To bardzo ważne, bo nie zapominajmy że wykresy i wizualizacje mają przede wszystkim przekazywać istotne informacje.

Daj znać w komentarzu, czy masz pomysł na wykorzystanie albo już wykorzystałeś podobny wykres w swojej pracy. Jeśli uważasz, tę wiedzę za wartościową – udostępnij ją na swoim Facebooku lub LinkedIn.
26 stycznia 2021
Przycisk opcji na dashboardzie
Przycisk opcji na dashboardzie

Istnieje kilka sposobów na wprowadzenie interaktywności do arkuszy Excela. Na przykład – kontrolki formularza. Przycisk opcji jest jedną z nich. W tym wpisie pokażemy Ci jak możesz użyć przycisku opcji w swoich dashboardach.

Poniżej znajduje się wersja tekstowa filmu. Więcej poradników w wersji wideo, znajdziesz na naszym kanale na You Tube.

Cześć. Jacek z Excelness.

W dzisiejszej filmie pokażę ci jak stworzyć i wykorzystać kontrolki formularza, a dokładnie przycisk opcji, które będziesz mógł użyć na dashboardzie. Dzięki temu będziesz mógł sprawdzać i analizować różne plany sprzedażowe na kolejny rok.

Wstążka DEWELOPER i kontrolki

Aby móc korzystać z kontrolek formularza musisz mieć na wstążce włączoną zakładkę DEWELOPER. Jeżeli tego nie masz, kliknij prawym przyciskiem myszy na wstążce. Wybierz DOSTOSUJ WSTĄŻKĘ i zaznacz DEWELOPER.

Przejdź teraz na wstążkę DEWELOPER > WSTAW. Masz tam do wyboru różne kontrolki formularza. W dzisiejszym filmie pokażę ci jak wykorzystać przycisk opcji. Rysujesz pierwszy z nich. Zmienimy jego nazwę na Plan nr 1.

Taki przycisk musisz oprogramować. Robimy to w następujący sposób. Prawy przycisk myszy na przycisku i wybierz FORMATUJ FORMANT. Musimy tutaj ustalić łącze komórki z którym będzie powiązany nasz przycisk. Chodzi o to, żebyśmy wiedzieli który plan został wybrany. Komórkę łącza ustawię z arkuszem tymczasowym. Powiedzmy, że będzie to komórka H9.

Następnie skopiuję ten element jeszcze trzy razy. Docelowo będę chciał mieć cztery plany. Używam klawiszy CTRL + C i CTRL + V. Teraz odpowiednio poustawiam przyciski. Jednym ze sposobów jest zaznaczenie kontrolek z klawiszem CTRL i poprzesuwanie ich. Jeśli chcemy idealnie poustawiać wszystkie kontrolki, to zaznaczamy je z CTRL i przechodzimy na FORMATOWANIE. Wybieramy opcje: WYRÓWNAJ DO LEWEJ i ROZŁÓŻ W PIONIE.

Dzięki temu mamy ładnie ułożone kontrolki. Sprawdźmy jak to działa. Klikając plan numer jeden – w arkuszu tymczasowym, mamy wybraną 1. Klikamy kolejny plan i zmienimy nazwę na: plan numer 2, plan numer 3, plan numer 4. Prawym przyciskiem myszy robimy EDYTUJ TEKST. No i zobaczmy jak to działa…Wszystko działa poprawnie.

Tabela pomocnicza i formuły

Teraz przygotuję pomocniczą tabelę, która będzie mi wstawiać plany przypisane do odpowiednich przycisków. Kopiuje etykiety grup. Wpisuje 4 plany. I dla każdego planu wstawiam moje prognozy sprzedaży. Mam już przygotowane jakieś prognozy, więc je po prostu wkleję.

Moim celem będzie pokazanie odpowiedniego planu. Muszę stworzyć formułę, która pobierze mi odpowiednie wartości planu dla wybranej opcji. Wykorzystam do tego funkcje WYSZUKAJ.POZIOMO.

Aby to zrobić jedną formułą, przygotuję tutaj komórki pomocnicze 2, 3, 4, 5 i 6. Są to liczby, które odpowiadają za numer wiersza w tej tabelce. To jest drugi wiersz, trzeci, czwarty, piąty i szósty itd. Wykorzystam to do funkcji. Ma mi wyszukać tę 3. Będę chciał kopiować tę formułę w dół, w związku z powyższym muszę zablokować tutaj wiersz. Ma mi wyszukać w tej tabeli, a więc muszę również muszę zablokować tutaj wiersze. Do numeru wiersza wykorzystam właśnie tę pomocniczą kolumnę, dzięki której będę mógł to zrobić za pomocą jednej formuły. Ostatni parametr – wyszukiwanie dokładne czyli 0. Kopiuję teraz formułę w dół.
```
=WYSZUKAJ.POZIOMO(H$8;B$8:E$13;G9;0)
```
Teraz ta tabelka pomocnicza jest tak przygotowana, że wybierając tutaj odpowiedni plan zmieniają mi się te wartości. Wystarczy, że powiąże te komórki z moją przygotowaną tabelą. I skopiuje niżej.

W ten sposób stworzyłem mini dashboard, dzięki któremu będę mógł w fajny i ciekawy sposób analizować różnego rodzaju plany sprzedaży. Dodatkowo mogę ukryć arkusz Temp, dzięki czemu cały raport będzie wyglądał bardziej elegancko.

Jeżeli podobał ci się film udostępnij go innym W kolejnym pokażę Ci inne wykorzystanie kontrolek formularza. Zatem oglądaj nasze filmy i czytaj nasze artykuły na Excelness.com.
12 stycznia 2021
Napisz swój własny scenariusz w Excelu
Napisz swój własny scenariusz w Excelu

Z tego artykułu dowiesz się jak stworzyć scenariusz w Excelu. Oczywiście nie filmowy 🙂 Na przykładzie planowania budżetów, pokażę Ci jak szybko sprawdzić różne wersje takich budżetów. Dzięki temu, będziesz mógł sprawnie analizować różne warianty i wybrać taki, który będzie najbardziej efektywny.

Scenariusze budżetowe

Załóżmy, że Twoim celem będzie ustalenie budżetów wynagrodzeń dla poszczególnych działów przykładowej firmy. Masz przygotowaną tabelę z aktualnymi wartościami wynagrodzeń (ostatni rok) oraz planem na kolejny rok.

Jednak, chciałbyś w miejscu planu wprowadzać różne wartości, aby później wybrać najbardziej optymalny wariant. Po wprowadzeniu danych będą aktualizowane także inne obliczenia, czyli: odchylenie wartościowe i procentowe. Całość „ubierzesz” w dashboa rd, np. taki jak poniżej.

Tworzenie scenariuszy

Zatem przejdźmy do tworzenia scenariuszy. Chcesz przeanalizować trzy rodzaje budżetów. Stworzysz scenariusz za każdego z nich. W każdym, będą różne wartości dla poszczególnych działów.

Aby utworzyć scenariusz przejdź na DANE > ANALIZA WARUNKOWA > MENEDŻER SCENARIUSZY. Otworzy się okienko dialogowe, które umożliwi Ci wprowadzanie różnego rodzaju scenariuszy.

W pierwszym scenariusz zakładasz, że budżet będzie na poziomie wykonania. Użyj przycisku DODAJ i wprowadź nazwę scenariusza – “Budżet nr. 1”. Następnie wskaż zakres zmiennych komórek. Będą to te komórki, których zawartość będzie się zmieniała w zależności od scenariusza.

Teraz możesz wprowadzić wartości dla poszczególnych działów. Jak wspomniałem – w tym scenariuszu będą to wartości z aktualnego roku.

Możesz wprowadzić kolejne scenariusze, czyli budżety nr. 2, 3 i 4. Nie jesteś „skazany” tylko na ręczne wprowadzanie wartości scenariuszy. Możesz również wprowadzać dane za pomocą formuł.

Na przykład, jeśli chcesz aby w scenariuszu 2 – budżet był większy od aktualnego wykonania o 10%, wpisz:
```
=C31*1,1
```
Czyli, komórkę z wykonaniem dla Działu Finansowego (C31) przemnażasz przez 1,1 (co oczywiście daje wartość o 10% wyższą) Podobnie możesz zrobić z kolejnymi wartościami.

Nic nie stoi na przeszkodzie, aby wprowadzać różne formuły dla różnych działów, czy stosować „styl mieszany” – to znaczy, że część wartości wprowadzisz ręcznie, a część za pomocą formuł.

Po wprowadzeniu danych, do wszystkich scenariuszy możesz swobodnie przełączać się pomiędzy scenariuszami – pokazując wpływ wybranego wariantu na Twoim dashboardzie. Po prostu zaznacz dany scenariusz i kliknij przycisk POKAŻ.

Możesz również stworzyć w menu górnym zakładkę, gdzie będziesz mógł przełączać się pomiędzy różnego rodzajami budżetami. Takie rozwiązanie znajdziesz w pliku do artykułu (patrz niżej)

Chcesz plik Excel z omawianym w artykule przykładem?

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Scenariusz w Excelu – podsumowanie

Oglądanie wyników każdego ze scenariuszy na pewno jest użyteczne. Ale co jeśli chciałbyś zobaczyć naraz wszystkie scenariusze (budżety)? Tu również menedżer scenariuszy przyjdzie Ci z pomocą. Możesz zrobić podsumowanie wszystkich budżetów i pokazać je w jednej tabeli. Oczywiście nie ręcznie.

Ponownie przejdź do DANE > ANALIZA WARUNKOWA > MENEDŻER SCENARIUSZY i kliknij w przycisk PODSUMOWANIE. Masz do wyboru czy chcesz zobaczyć podsumowanie w formie tabeli przestawnej, czy też w formie „zwykłej tabeli”. Następnie decydujesz, które komórki to “Komórki wynikowe”. To znaczy, musisz wskazać ten zakres komórek, który reprezentuje wyniki dla Ciebie istotne z punktu widzenia analizowanego tematu. W poniższym przykładzie to kolumna z „Odchyleniem”, a więc różnicą między budżetem, a obecnym wykonaniem.

Powyżej widzisz podsumowanie scenariuszy w formie „zwykłej tabeli” . Od razu widać wszystkie budżety równocześnie oraz bieżące wartości (wykonanie). Oczywiście widzisz również odchylenia od wykonania dla każdego budżetu, czyli to na czym nam najbardziej zależało w tym przykładzie.

Podsumowanie

Jak widzisz stworzenie takiego raportu ze scenariuszami nie jest szczególnie czasochłonne. Natomiast pozwala na szybką analizę różnych wariantów i wybór najlepszego.
20 października 2020
Solver, czyli cichy bohater optymalizacji
Solver, czyli cichy bohater optymalizacji

Jeżeli kiedykolwiek zastanawiałeś się jak maksymalnie wykorzystać ograniczone zasoby, to ten artykuł jest zdecydowanie dla Ciebie! Odpowiedzią może być fantastyczne i jednocześnie rzadko wykorzystywane narzędzie Excela – SOLVER.

Bez względu na to, czy chodzi budżet, materiały do produkcji czy zasoby ludzkie – wyzwanie może być podobne. Jakie decyzje podjąć, aby przy ograniczonych zasobach i określonych warunkach – osiągnąć jak największą korzyść.

Solver, czyli co?

Dodatek Solver to narzędzie do optymalizacji. Mówiąc konkretniej, do poszukiwania maksymalnej, bądź minimalnej wartości – przy określonych warunkach i ograniczeniach.

W praktyce wygląda to tak, że podajesz jakiś cel (wpisujesz wartość w tzw. komórce celu). Celem może być np. maksymalizacja zysku, przychodu, ale też minimalizacja środków niezbędnych do wyprodukowania X produktów. Dodatkowo, podajesz zmienne czy też limity / ograniczenia jakim ten cel podlega. Zadaniem Solvera jest takie dopasowanie tych zmiennych, aby spełnić podane przez Ciebie limity, jednocześnie maksymalizując lub minimalizując podany przez Ciebie cel.

Aby wszystko stało się zupełnie jasne, jak zwykle przećwiczymy to na konkretnym przykładzie.

Optymalizacja budżetu reklamowego z użyciem dodatku Solver

W przykładzie wykorzystamy temat optymalnej alokacji kosztów na reklamę. Załóżmy taką sytuację: jesteś szefem marketingu i Twoim zadaniem jest wybrać najlepsze kanały reklamy, mając do dyspozycji określony budżet. Dodatkowo, masz pewne ograniczenia:
- nie chcesz reklamować się na więcej niż 5 kanałach jednocześnie,
- jeśli wybierzesz reklamę w Google to równocześnie musisz skorzystać z kanału e-mail,
- jeśli wybierzesz kanał Facebook to nie możesz wybrać kanału Instagram,
- musisz wybrać tylko jeden kanał radiowy i tylko jeden kanał telewizyjny.
Jeśli jesteś ekspertem od online marketingu i te warunki wydają Ci się dziwne – wybacz 🙂 Chodzi o przykładowe pokazanie jak skorzystać z narzędzia, a same parametry i ograniczenia będziesz mógł dobrać w dowolny sposób.

Wszyscy lubimy podejmować decyzje w oparciu o fakty. Ty także dysponujesz pewnymi danymi, które pokazują koszt reklamy w danym kanale oraz szacowaną ilość odsłon na 1000 wyświetleń.

Dysponujesz budżetem na poziomie 3000 zł. Jakich wyborów powinieneś dokonać, aby liczba wyświetleń była maksymalna?

Odpowiedź na to pytanie bez użycia odpowiedniego narzędzia, skazuje Cię na wielokrotne testowanie różnych kombinacji, czyli metodę prób i błędów. Nawet jeśli byłbyś w stanie zrobić to ręcznie, to jest dość mało prawdopodobne że będziesz wystarczająco wytrwały, aby znaleźć najlepsze rozwiązanie. Dlatego jako doświadczony użytkownik Excela – użyjesz Solvera, który takie zadanie rozwiąże w kilka sekund 🙂

Jak zdefiniować warunki w Solverze

Wyboru danego kanału będziemy dokonywać za pomocą jedynki i zera – wprowadzając odpowiednie wartości w kolejnych komórkach przy nazwie danego kanału.

To co musimy teraz zrobić, to zapisać podane wyżej ograniczenia w „języku” zrozumiałym dla Excela i Solvera, czyli za pomocą formuł. Do dzieła!
1. Jedna reklama w radio i jedna w TV.
Oznacza to, że suma dwóch komórek przy kanałach TV musi się równać jeden. I podobnie w przypadku radia – suma komórek przy obu kanałach radiowych musi równa się 1. Możemy to zapisać po prostu jako zwykłą formułę, dodającą do siebie dwie komórki:
```
=D5+D6
```
1. Jeśli wybierzemy kanał Google to musimy również wybrać e-mail.
Takie ograniczenie zapiszemy jako: wybór dla Google powinien być mniejszy lub równy od wyboru dla e-maila, czyli:
```
=D7<=D11
```
Logika jest następująca – jeżeli nie wybierzemy Googla (wartość 0) to kanał e-mail możemy, ale nie musimy wybrać (0 albo 1) Natomiast jeżeli wybierzemy Google (1), to aby równanie było prawdziwe – przy kanale e-mail – również powinno pojawić się 1.
1. Jeżeli Facebook to nie Instagram.
Oznacza to że suma wartości przy kanale Facebook i kanale Intagram – musi być większa bądź równa 1. Zapiszemy to jako:
```
=D4+D10<=1
```
Mamy już zdefiniowane wszystkie ograniczenia. Znamy budżet, który wynosi 3000 zł oraz przyjęliśmy, że chcemy się reklamować w 5 kanałach. Czas zaprząc Solvera do pracy!

Dodatek Solver w praktyce

Uruchom dodatek Solver. Znajdziesz go na wstążce DANE w sekcji ANALIZA. Jeśli go tam nie widzisz – musisz go najpierw włączyć dodatkach (PLIK > OPCJE > DODATKI).

Po uruchomieniu, pojawi się okno, w którym będziesz musiał powpisywać wszystkie konieczne warunki (wskazać komórki, w których te warunki definiowałeś)

Wiemy, że naszym celem jest znalezienie maksymalnej ilości odsłon. A zatem naszą komórką celu będzie C13, która ma zostać zmaksymalizowana.

Nasze zmienne to kanały reklamy, gdzie Solver będzie wstawiał wartości 0 lub 1, dobierając kanały tak, aby zrealizować powyższy cel.

W kolejnym kroku definiujemy warunki i ograniczenia. Musimy m.in. „powiedzieć Excelowi”, żeby w komórkach przy kanałach reklam wstawiał zera i jedynki. W tym celu klikamy DODAJ i zaznaczając odpowiedni zakres komórek, wybieramy w kolejnym polu wartość BIN (od binary), czyli wartości zerojedynkowe.

Kolejnym ograniczenia zapiszemy jak na poniższym screenie. Myślę, że będzie dla Ciebie jasne, że to zdefiniowanie warunków, które wcześniej zapisywaliśmy w postaci formuł w arkuszu.

Mamy zdefiniowane wszystkie parametry. Nie pozostaje nic innego jak kliknąć przycisk ROZWIĄŻ.

Solver podał, że wybierając takie kanały jak: Facebook, Radio1, TV1 i e-mail możemy liczyć na 1975 odsłony, przy koszcie reklamy 3000 zł. Teraz możesz eksperymentować – zmieniając budżet, czy inaczej definiując ograniczenia – aby szybko sprawdzać zupełnie nowe scenariusze. Za każdym razem możesz liczyć na optymalne rozwiązanie.
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie

Jestem ciekawy czy używałeś do tej pory Solvera? Daj znać w komentarzu.

Jeśli nie, to mam nadzieję, że ten artykuł pozwoli Ci wykorzystać to narzędzie do rozwiązywania Twoich problemów z optymalizacją zasobów.

Na koniec mam do Ciebie prośbę – skopiuj link do tego artykułu i podziel się nim w swoich mediach społecznościowych. Być może komuś jeszcze pomożesz. A dla nas to będzie dowód, że tworzymy przydatne i wartościowe treści.
6 października 2020
Regresja liniowa w Excelu – prognozuj przyszłość
Regresja liniowa w Excelu, czyli prognozuj przyszłość

Regresja liniowa w Excelu pozwala na prognozowanie przyszłych wyników. Brzmi fantastycznie? Oczywiście, że tak ale nie jest to takie proste. Jednak warto znać tę metodę i sposób jej wykorzystania w Excelu, aby poszerzyć zakres swoich analitycznych możliwości.

Ten artykuł nie wyjaśnia wszystkich statystycznych zależności metody. Jeśli chcesz wiedzieć więcej o regresji liniowej, od strony teoretycznej odsyłam do innych stron. Tutaj dowiesz się jak szybko i w praktyce zastosować tę metodę w Excelu, aby prognozować przyszłe wyniki.

Regresja liniowa: podstawy

Funkcja regresji liniowej odpowiada na pytanie: „Jaką wartość przyjmie dane zjawisko (zmienna objaśniana), w zależności od wartości innych zjawisk (zmienne objaśniające)” W nazwie mamy pojęcie „liniowa”, zatem metoda zakłada, że zależności pomiędzy zjawiskiem objaśnianym oraz zmiennymi objaśniającymi jest właśnie liniowa.

Tyle teorii 🙂 Przejdźmy do praktyki. Mamy przykład, w którym będziemy chcieli przewidzieć wielkość sprzedaży dla kolejnych okresów, w zależności od nakładów na marketing. Na marginesie zawsze podobał mi się pewien cytat, dotyczący marketingu:

„Połowa pieniędzy wydawanych na reklamę jest wyrzucana w błoto. Problem w tym, że nie wiem, która to połowa”
John Wanamaker

Ponieważ mamy zależność tylko od jednej zmiennej – nakładów na marketing – funkcja przyjmie postać:
```
y = m1*x1 + b
```
gdzie m1 to tzw. współczynnik kierunkowy, a b to wyraz wolny.

Regresja liniowa z jedną zmienną

Aby obliczyć teoretyczną wielkość sprzedaży w następnym okresie, ustawiamy się w komórce w której chcemy mieć tę wartość i wykorzystujemy funkcję REGLINX.LINIOWA.

Funkcja ma 3 parametry:
1. x – w naszym przypadku to nakłady na marketing jakie chcemy ponieść w kolejnym okresie,
2. znane y – to znana przez nas, historyczna wielkość sprzedaży w poprzednich okresach
3. znane x – to znana przez nas, historyczna wielkość nakładów na marketing w poprzednich okresach
Oczywiście podając zakresy komórek, w której znajduje się historyczna sprzedaż oraz nakłady na marketing, użyj adresowania bezwzględnego, aby odwoływać się cały czas do tych samych danych. Funkcja powinna wyglądać następująco:

Jak interpretować wyniki? Przy założeniu, że w styczniu 2019 roku wydamy 5 352 zł na marketing, to nasza przewidywana wielkość sprzedaży wyniesie 4 409 072 zł. Oczywiście dane są całkowicie teoretyczne. W rzeczywistości raczej nie osiągamy takich zwrotów z inwestycji 🙂

Możesz teraz skopiować funkcję na kolejne okresy, aby znaleźć odpowiedź na pytanie jak będzie wyglądała sprzedaż w kolejnych okresach. Poniższy wykres pokazuje zależność pomiędzy nakładami na marketing, a wielkością sprzedaży – właśnie na podstawie powyższych obliczeń.

Na sprzedaż wpływa wiele parametrów

Życie nie jest tak proste, aby sprzedaż zależała tylko od nakładów na marketing. W większości przypadków na dane zjawisko wpływa wiele zmiennych. Dlatego teraz pokażę Ci, jak wykorzystać funkcję regresji liniowej z użyciem wielu zmiennych.

W naszym przykładzie, przyjmiemy że wielkość sprzedaży zależy od następujących zmiennych:
- ilość sklepów
- ilość promocji w okresie
- nakłady na marketing
- ilość pracowników w sklepie
Podobnie jak wcześniej, na podstawie historycznych danych będziemy chcieli wyliczyć wielkość sprzedaży w kolejnych okresach. Aby tego dokonać, wykorzystamy inną funkcję – REGLINP.

Funkcja przyjmuje postać:
```
y = m1*x1 + m2*x2 + … + mn*xn + b
```
Regresja liniowa z wieloma zmiennymi

Aby łatwiej korzystać z tej funkcji, przygotuj sobie tabelę pomocniczą. Wypisz w niej wszystkie zmienne ale w odwrotnej kolejności niż są umieszczone w tabeli z danymi. To znaczy, że jeżeli mamy zmienną: “ilość sklepów” na pierwszym miejscu w tabeli z danymi, to w tabeli pomocniczej jest ona na ostatnim miejscu. I tak dalej.

Teraz za pomocą funkcji REGLINP wyliczymy współczynniki kierunkowe dla poszczególnych zmiennych. Ponieważ funkcja ma zwrócić nam wektor ze współczynnikami kierunkowymi i wyrazem wolnym (b) – zaznacz cały obszar w tabeli pomocniczej i wpisz funkcję. W przypadku naszych danych przykładowych, przyjmie ona taką postać:
```
{=REGLINP(D4:D27;E4:H27;PRAWDA;FAŁSZ)}
```
Pierwsza zmienna to znane y, czyli wielkości sprzedaży w poprzednich okresach. Następnie znane x – tutaj możesz podać cały zakres danych, gdzie znajdują się dane o: ilości sklepów, promocji, nakładów na marketing i pracownikach.

Trzeci argument funkcji to tzw. stała, gdzie mówimy w jaki sposób ma być obliczana funkcja. Wpisz PRAWDA (1), co oznacza normalne obliczanie funkcji (bez wchodzenia w szczegóły – na tym nam w tym przypadku zależy)

Ostatni parametr mówi nam czy funkcja ma zwracać nam dodatkowe statystyki. Nie skorzystamy z opcji dodatkowych statystyk, dlatego wybieramy FAŁSZ (0)

Całą formułę musimy zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER, ponieważ jest to formuła tablicowa. W ten sposób obliczyliśmy współczynniki kierunkowe naszej funkcji.

Teraz możemy je wykorzystać w kolejnym okresie, aby przewidzieć teoretyczną wartość sprzedaży. W tym celu podstawiamy dane do wzoru i tworzymy formułę:
```
=$I$36+E28*$H$36+F28*$G$36+G28*$F$36+H28*$E$36
```
Czyli mnożymy w niej poszczególne współczynniki kierunkowe, ze znanymi wartościami poszczególnych zmiennych, takich jak: ilość sklepów, pracowników itd.

Finalnie otrzymamy teoretyczną wielkość sprzedaży w kolejnych miesiącach uzależnioną od czterech zmiennych. Wpisana przez nas formuła jest oczywiście dynamiczna – czyli możesz zmieniać wartości poszczególnych zmiennych, co wpłynie na przewidywaną wartość sprzedaży.

Możesz teraz symulować jak zmieni się sprzedaż, w zależności od wielkości pewnych zjawisk w Twojej firmie.
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie

Czy musisz znać skomplikowane teorie, aby w praktyczny sposób wykorzystywać różne metody statystyczne? Stawiam tezę, że nie. Na pewno powinieneś wiedzieć jak interpretować wyniki i jakie są ograniczenia danej metody.

W omawianym w artykule przykładzie pokazałem Ci, jak w praktyczny sposób zastosować regresję liniową w Excelu. Możesz szybko wykorzystać tę wiedzę w swojej pracy. Oczywiście o ile rozumiesz, że nie warto iść do Twojego szefa z tezą, że wzrost nakładów na marketing o x zł, spowoduje wprost wzrost sprzedaży do y zł 😉

Jeżeli uznasz wiedzę zawartą w tym artykule za przydatną – udostępnij proszę artykuł w swoich social mediach, używając przycisku poniżej.
21 lipca 2020
Formuły tablicowe, gdy standardowa funkcja nie wystarczy
Formuły tablicowe – gdy standardowa funkcja nie wystarczy

Formuły tablicowe wykorzystasz do rzeczy pozornie niemożliwych. Spróbuj za pomocą standardowej funkcji policzyć medianę wynagrodzeń dla handlowców, pracujących w regionie północ, od co najmniej 3 lat. Nie pomoże nawet tabela przestawna. A to tylko prosty przykład.

Formuły tablicowe pomogą Ci w tym i wielu innych złożonych obliczeniach. Co ciekawe, możesz zaangażować do pracy w ten sposób (tablicowy) wiele „standardowych” funkcji, np. ŚREDNIĄ, MEDIANĘ, JEŻELI etc.

Formuła tablicowa, czasami nazywana też CSE od CTRL + SHIFT + ENTER, które kończy jej wprowadzanie to sposób działania, a nie jakaś pojedyncza, tajemnicza funkcja. W artykule wyjaśnię Ci praktyczne działanie na przykładach, ale wykorzystanie formuł tablicowych to prawdziwy „temat rzeka”.

Źrócło: Canva.com

Formuła tablicowa: podstawowe reguły

Używając na przykład funkcji JEŻELI, wprowadzasz ją do pojedynczej komórki, a sama funkcja pobiera argumenty również z pojedynczych komórek.

Formuła tablicowa tym różni się od standardowej funkcji, że może:
- dokonywać „w tle” wielu obliczeń, zwracając wynik do pojedynczej komórki,
- zwracać wynik swoich obliczeń do więcej niż jednej komórki jednocześnie.
W artykule skupimy się na tym pierwszym przypadku. Zanim przejdziemy do konkretnego przykładu, musisz pamiętać o kluczowej zasadzie – każdą formułę tablicową zatwierdzasz kombinacją klawiszy CTRL + SHIFT + ENTER. Bez tego formuła nie zadziała. W wyniku tego, na końcu i na początku formuły pojawiają się nawiasy klamrowe { }, na przykład:
```
{=SUMA(JEŻELI(A:A="Północ";JEŻELI(B:B="Klient X";C:C)))}
```
Formuły tablicowe: przykład z użyciem ŚREDNIEJ i JEŻELI

Aby zrozumieć działanie formuł tablicowych posłużmy się prostym przykładem. Będzie łatwiej, jeżeli przed dalszą lekturą pobierzesz ten plik. Masz 10 przypadków sprzedaży dla różnych handlowców, w różnych miastach. Twoim zadaniem jest policzyć średnią sprzedaż dla Marcina z Łodzi. Oczywiście możesz to samo zrobić np. w tabeli przestawnej, ale wykorzystamy ten uproszczony przykład, żeby zobrazować działanie formuł tablicowych.

Gdy wprowadzisz całą formułę do pojedynczej komórki i zatwierdzisz klawiszami CSE, „w tle” będą się działy pewne operacje 🙂 Przede wszystkim, formuła korzystając z właściwości funkcji JEŻELI, sprawdzi:
- wszystkie komórki w kolumnie Miasto i jeżeli napotka tam Łódź – wirtualnie zaznaczy tam 1 (czyli prawda),
- wszystkie komórki w kolumnie Handlowiec i napotykając Marcina – podobnie postawi 1,
- sprzedaż pozostaje bez zmian, bo nie mamy tam żadnego warunku.
Jak widzisz w tabelce powyżej, są 3 wiersze w których warunek z miastem i handlowcem jest spełniony jednocześnie. Podczas moich szkoleń tłumaczę to jako mnożenie przez siebie każdej komórki w danym wierszu. I tak na przykład: w wierszu 1, mamy 0 x 1 x 26900. Wynik, rzecz jasna to 0, więc formuła nie weźmie tej wartości sprzedaży w końcowym wyniku. Z kolei, w wierszu 5, mamy 1 x 1 x 50680 – co daje wynik 50680. I oczywiście ta wartość sprzedaży zostanie uwzględniona przy wyliczaniu średniej.

No właśnie…, a gdzie tu średnia zapytasz? Bo do tej pory to tylko sprawdzamy warunki i mówimy PRAWDA lub FAŁSZ – jak w przypadku funkcji JEŻELI. Aby policzyć średnią, na wybranych w powyższy sposób wartościach trzeba użyć (niespodzianka!) funkcji ŚREDNIA 🙂

To co musisz zrozumieć, to fakt że ŚREDNIA zadziała na końcu (po wybraniu odpowiednich wartości spełniających warunki) ale w zapisie formuły umieścimy ją na początku. Generalnie w formułach Excela, funkcje umieszczone najbardziej wewnątrz działają jako pierwsze, a te na zewnątrz (od lewej) jako ostatnie. Jeżeli wiesz dlaczego tak się dzieje, podpowiedz innym w komentarzu.
```
{=ŚREDNIA(JEŻELI(Q15:Q24=Q10;JEŻELI(R15:R24=R10;S15:S24)))}
```
Opisane powyżej kroki dzieją się w ułamku sekundy. Chyba, że danych jest bardzo dużo i używasz wielu formuł tablicowych. W takich przypadkach odczujesz spowolnienie, w działaniu arkusza.

Formuły tablicowe: przykłady z użyciem MEDIANY i KWARTYLI

Powyższy przykład to tylko wierzchołek góry lodowej. Formuły tablicowe to naprawdę potężne narzędzie i potrafią znacznie więcej. Pokażę Ci jeszcze dwa przykłady, które mam nadzieję będą dla Ciebie użyteczne.

Liczenie średnich z wynagrodzeń jest kiepskim pomysłem. Ja częściej używam MEDIANY, KWARTYLI, DECYLI i DOMINANTY (w Excelu odpowiednia funkcja to WYST.NAJCZĘŚCIEJ.WART)

Trudność pojawia się, gdy trzeba policzyć medianę dla kilku różnych typów stanowisk, lokalizacji, czy kategorii zaszeregowania. Na poniższym przykładzie zobaczysz jak można sobie z tym poradzić, dzięki formułom tablicowym.
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie

Formuły tablicowe to „level master” jeżeli, chodzi o umiejętność posługiwania się funkcjami Excela. Jednak zrozumienie podstawowej koncepcji działania nie powinno sprawić dużych kłopotów. Mam nadzieję, że tym artykułem pomogłem Ci osiągnąć właśnie ten cel.

Życzę Ci owocnego wykorzystania „tablicówek” do wszelkich obliczeń, gdzie zawiedzie standardowa funkcja czy tabela przestawna.
21 stycznia 2020
Wykres kołowy Excel – jak zamienić go w prędkościomierz
Jak zamienić wykres kołowy Excel w prędkościomierz?

Wykres kołowy Excel [ang. pie chart] jest często używany do prezentacji różnych danych. Ale trzeba go używać z głową. W 9 przypadkach na 10, lepiej sprawdzi się na przykład wykres słupkowy.

Jednak standardowe typy wykresów możesz przekształcać w zupełnie inne, ciekawe formy. Jednym z takich przykładów jest zmiana wykresu kołowego na wykres typu prędkościomierz.

Przygotuj dane pod wykres kołowy Excel

Zacznij od zwykłego wykresu kołowego. Jednak już teraz musisz mieć zaplanowane to jak będzie wyglądał Twój docelowy wykres.

Kojarzysz prędkościomierz na tablicy rozdzielczej samochodu? To mniej więcej jest Twój model docelowy 🙂 Dla uproszczenia zrobimy go w formie półkola. Natomiast wskazówka, która przesuwa się wraz z tym jak wciskasz gaz w samochodzie – w naszym przypadku zostanie zastąpiona przez fragment wykresu, który pokaże wynik, czy też postęp w realizacji.

Mając to w głowie oraz obraz wykresu kołowego, musisz odpowiednio przygotować dane. Wykres będzie podzielony na 3 części.
1. Dolna część wykresu, czyli 50% z całego koła będzie docelowo niewidoczna
2. Pierwszy fragment z górnej części wykresu, ma pokazywać wynik – podobnie jak wskazówka prędkościomierza przesuwająca się po zegarze. Różnica będzie polegała na tym, że u nas ten fragment będzie się w całości wypełniał kolorem.
3. Drugi fragment z górnej części to właściwa tarcza prędkościomierza.
Dlatego też potrzebujemy w sumie 4 wartości, aby stworzyć odpowiedni wykres.
- Komórka C9, będzie zawierała właściwy % realizacji zadania w zakresie 0-100%
- W komórce C14, będziemy mieli dolną połowę wykresu, czyli 50% (połowa koła)
- Do komórki C13, wprowadź formułę =50%-C12. Zadaniem tej formuły jest pokazywanie różnicy, pomiędzy właściwym wynikiem (postępem w realizacji), a drugą połową koła, która jest naszą tarczą prędkościomierza.
- Komórka C12 powinna zawierać natomiast formułę =MIN(100%;$C$9)/2. Formuła, za sprawą funkcji MIN, wybierze mniejszą z dwóch wartości (100%, albo tę z komórki C9). Następnie ta wartość zostanie podzielona na 2. Dlaczego? Ponieważ działamy tylko na połowie koła 🙂
Stwórz zwykły wykres kołowy

Mając tak przygotowane dane, jesteś gotowy aby utworzyć zwykły wykres kołowy.
Jak utworzyć wykres kołowy Excel?
1. Zaznacz komórkę z danymi.
  W naszym przypadku jest to komórka C12:C13.
2. Na wstążce WSTAWIANIE, wybierz WYKRESY, a następnie wykres kołowy.
3. Kliknij PRAWYM PRZYCISKIEM MYSZY (PPM) na dowolną serię na wykresie i wybierz FORMATUJ SERIĘ DANYCH.
4. W opcjach serii zmień KĄT PIERWSZEGO WYCINKA na 270 stopni.
  Ten krok pozwala obrócić wykres w taki sposób, aby można go było sformatować jako tarczę prędkościomierza.
5. Usuń niepotrzebne elementy, takie jak legenda czy tytuł wykresu.
  Zaznacz element i kliknij DELETE na klawiaturze.
6. Możesz usunąć jeszcze tło i obramowanie na wykresie. Docelowo wykres powinien wyglądać jak na poniższym obrazku.
  Aby usunąć tło i obramowanie, kliknij PPM na wykresie i wybierz FORMATUJ OBSZAR WYKRESU, a następnie zaznacz: BRAK WYPEŁNIENIA i BRAK LINII.
Przekształć wykres kołowy w prędkościomierz

Teraz już będzie z górki. Obiecuję 🙂 Jedyne co pozostało zrobić, to odpowiednio sformatować nasz wykres kołowy. Kolejne kroki zobaczysz na poniższym filmie. W skrócie, chodzi o to, abyś:
- usunął tło z dolnej połówki wykresu kołowego,
- zmienił kolory fragmentów na górnej połowie wykresu, tak aby przypominały tarczę zegara,
- dodał i odpowiednio sformatował etykietę danych, która pokazuje % realizacji. Uwaga! Tu jako % realizacji musisz podać wartość z komórki C9, a nie C12.
Zobacz:

Co jeszcze możesz zrobić

Oczywiście, to co powyżej zrobiliśmy to nie koniec tylko początek możliwości 🙂

Pierwsze co przychodzi do głowy, to chęć wprowadzenia wskaźnika jak na prawdziwym prędkościomierzu. To wymaga zmiany w danych źródłowych. Jeśli uważnie przeszedłeś przez akapity dotyczące przygotowywania danych, to pewnie już rozumiesz że trzeba wprowadzić jeszcze jedną wartość, która będzie odpowiadała za grubość wskaźnika.

Powiedzmy, że chciałbyś wykorzystać wykres, aby pokazywać wyniki zaangażowania pracowników. Wskaźnik naszego prędkościomierza może poruszać się po na tle takich obszarów jak: niskie, średnie i wysokie zaangażowanie. W takim przypadku, nie wystarczy że użyjesz wykresu kołowego. Różne zakresy wyników łatwo pokażesz na wykresie pierścieniowym [ang. donut chart] O tym jak tworzyć takie wykresy pisałem w artykule: Wykres pierścieniowy – jak pokazać postęp w realizacji zadania.

Potem, pozostaje Ci odpowiednie połączenie obu typów wykresów. Spróbuj sam. Tego typu wykresy możesz później wykorzystać w swoim interaktywnym raporcie, czy inaczej mówiąc dashboardzie.

Podsumowanie

Jak widzisz z każdego wykresu w Excelu można „ulepić” coś całkiem nowego. To jedna z tych rzeczy, za które najbardziej lubię ten program. A poza tym, mam trochę bzika na punkcie wizualizacji danych i sam szukam stale nowych możliwości. Znasz jakieś? Podziel się proszę w komentarzu.
26 listopada 2019
Dashboard: interaktywny raport z efektem WOW [2/2]
Dashboard, czyli interaktywny raport z efektem WOW [2/2]

W tej części dowiesz się jak wykorzystać fragmentator oraz zadbać o to, aby cały raport był spójny i logiczny. W kolejnych 3 krokach:
- wprowadzisz interaktywność za pomocą fragmentatorów,
- utworzysz powiązania pomiędzy nimi, a tabelami przestawnymi,
- zadbasz o to, aby powiązania były logiczne i spójne z celami całego raportu.
W poprzedniej części opisałem czym jest dashboard, jak go zaprojektować i przygotować odpowiednie dane. Umieściłem tam plik początkowy, dzięki któremu mogłeś – krok po kroku – rozpocząć tworzenie przykładowego raportu. Gorąco zachęcam Cię do rozpoczęcia lektury właśnie od części pierwszej.

Krok 4: Wprowadź interaktywność

Jak do tej pory wszystko wygląda pięknie…ale jest statyczne. Ponadto wymaga od odbiorcy raportu, chociażby minimalnej znajomości obsługi tabel / wykresów przestawnych.

Mając to na uwadze, wprowadzimy narzędzie, które nazywa się FRAGMENTATOR (ang. SLICER) Dzięki temu, będzie można klikać wybrane kategorie, a wykresy przestawne będą się automatycznie dostosowywać do wybranego fragmentu danych.

Kliknij w dowolny wykres przestawny, a następnie na wstążce ANALIZA, wybierz polecenie WSTAW FRAGMENTATOR.

Teraz zdecyduj, jakie kategorie będą miały wpływ na fragmentowanie danych. Ja wybrałem: miesiąc, region oraz przedstawiciela handlowego. Dlaczego właśnie te? Za chwilę wyjaśnię. Zanim o tym, to jeszcze „dwa słowa” o tym co możesz zrobić z fragmentatorem.

Klikając na dowolny fragmentator, wywołasz osobną wstążkę OPCJE. Znajdziesz tam sporo możliwości, które pozwolą Ci na sterowanie jego: wyglądem (np. style, rozmiar), widocznością czy precyzyjnie pozycjonować położenie na arkuszu.

Podobnie klikając prawym przyciskiem myszy możesz wywołać z menu kontekstowego USTAWIENIA FRAGMENTATORA. Pozostawiam Ci samodzielne sprawdzenie co dają zawarte tam opcję. Gdybyś miał pytania – napisz proszę w komentarzu.

Krok 5: Stwórz połączenia fragmentatora z tabelami przestawnymi

Jeżeli zdążyłeś już kliknąć na wybrane pola na utworzonych wcześniej fragmentatorach, z pewnością zauważyłeś dwie rzeczy. Po pierwsze – działają 🙂 To znaczy, że wraz z wybraną kategorią w danym fragmentatorze – zmienia się zakres danych prezentowanych na wykresie. Po drugie, to działanie ograniczone jest tylko do jednego wykresu / tabeli przestawnej – tej, która była zaznaczona, gdy wybierałeś opcję WSTAW FRAGMENTATOR.

Oczywiście Tobie (i mnie) zależy na czymś więcej. Chcemy, aby klikając na wybrane pola w dowolnym fragmentatorze, zmieniały się wszystkie wykresy. No właśnie…czy wszystkie? O tym dalej.

Aby połączyć dany fragmentator z dowolną tabelą przestawną:
- kliknij prawym przyciskiem myszy na fragmentatorze,
- wybierz opcję POŁĄCZENIA RAPORT,
- zaznacz te tabele, z którymi chcesz powiązać fragmentator.
Krok 6: Zaprojektuj logiczne powiązania

To nad czym powinieneś zastanowić się na etapie projektowania dashboardu (ale możesz i powinieneś także teraz) to logiczne powiązanie pomiędzy możliwościami filtrowania danych jakie daje fragmentator, a tym co chcesz prezentować na wykresach. Co konkretnie mam na myśli?

Zdecydowaliśmy, że na wykresach będą prezentowane następujące informacje:
- sprzedaż per region,
- najlepszych 5 przedstawicieli pod kątem wielkości sprzedaży,
- zysk per miesiąc.
Natomiast dane te, będziemy filtrować za pomocą takich fragmentatorów jak:
- miesiąc,
- przedstawiciel,
- region.
Weźmy pierwszy przykład. Prezentujesz sprzedaż per region. Tytuł wykresu to…”Sprzedaż per region” Czy zatem na pewno chcemy powiązać fragmentator Region z tym wykresem? Raczej nie. Odbiorca może przecież wybrać tylko jeden region i wtedy tytuł wykresu jest jakby nieadekwatny do prezentowanych danych.

Jeszcze wyraźniej zobaczysz to w przypadku wykresu „Zysk per miesiąc” Jeżeli powiążesz go z fragmenatorem Miesiąc, a użytkownik raportu wybierze tylko czerwiec… Rozumiesz, prawda?

Zachęcam Cię gorąco do przemyślenia zarówno tego co chcesz zaprezentować na wykresach, jak i tego w jaki sposób umożliwisz filtrowanie (fragmentowanie) danych. Raport powinien przecież stanowić spójną i logiczną całość.

Podsumowanie

Dowiedziałeś się jak stworzyć bardzo prosty (bo oparty na tabelach przestawnych i fragmentatorach) interaktywny raport, zwany dashboardem. Prosty, nie znaczy że nie efektowny. Jeżeli do tej pory robiłeś zwykłe statyczne tabelki, Twój szef z pewnością doceni jeżeli jutro pokażesz mu, że może „wyklikać” sobie dowolną informację z Twojego raportu 😉

Mam nadzieję, że zachęciłem Cię do wypróbowania tego narzędzia. To zaledwie czubek góry lodowej, bo dashboardy mogą przyjąć naprawdę fantastyczne formy i wykorzystywać inne, nieco bardziej skomplikowane funkcje, narzędzie i wykresy.
18 czerwca 2019