Czy wiesz, że możesz wstawić wykres z Excela do Power Pointa i automatycznie odświeżać swoje prezentacje? Za każdym razem, gdy zmienią się Twoje dane w Excelu – będziesz miał aktualne wykresy na swoich slajdach.
To nie wszystko. Możesz szybko stworzyć bardzo dużą liczbę wykresów i dodać je do swojej prezentacji. Jeśli regularnie prezentujesz swoje dane, w kolejnych miesiącach Twoja praca ograniczy się do odświeżania tabel przestawnych. Jeśli to brzmi dla Ciebie ciekawie, to zapraszam do lektury.
Przygotuj dane
Zacznijmy od tego, że nasze dane zawierają kraj odbiorcy: Polska, Niemcy i Anglia. I właśnie w taki sposób – wg kraju odbiorcy – chcemy je prezentować na wykresach i slajdach.
Pierwszą rzeczą jaką zrobimy, będzie automatyczny podział tych danych. Aby to zrobić, stwórz z danych tabelę przestawną. Dobrą praktyką jest tworzenie danych z tabel strukturalnych Excela. Aby zamienić zakres danych w taką tabelę, użyj skrótu CTRL + T. Dzięki temu, Twoja tabela przestawna będzie zawsze aktualna, nawet jeśli dodasz nowe dane.
Teraz utwórz tabelę przestawną. Nasze dane będziemy analizować pod względem krajów odbiorcy i handlowców.
Zatem, kolejne kroki wyglądają następująco:
pole „Handlowiec” do pola wierszy
pole „Kraj odbiorcy” do pola filtrów raportu
„Wartość netto” do pola wartości
Podziel automatycznie i stwórz wykres przestawny
Tak przygotowaną tabelę przestawną podzielimy automatycznie, względem kraju odbiorcy. Aby tego dokonać, skorzystamy z opcji POKAŻ STRONY FILTRU RAPORTU, która pozwoli podzielić dane na kolejne arkusze z naszymi 3 państwami z listy. O tym jak dokładnie skorzystać z tej opcji przeczytasz w tym artykulelub zobaczysz na filmie umieszczonym powyżej.
Po zatwierdzeniu, otrzymasz trzy nowe arkusze z: Anglią, Niemcami i Polską.
Teraz, w każdym z tych arkuszy, które zawierają tabelę przestawną wg ustalonego przez nas wzorca – stwórz wykres. Przejdź na wstążkę ANALIZA > WYKRES PRZESTAWNY. Wybierz dowolny wykres i sformatuj go wg swoich potrzeb i upodobań.
Aby wykres nie wyglądał jak wykres tabeli przestawnej, usuń pola filtrów. Naciśnij prawym przyciskiem myszy na polu filtra na wykresie i wybierz UKRYJ WSZYSTKIE PRZYCISKI PÓL NA WYKRESIE. Oczywiście tę czynność powtarzasz na wszystkich wykresach.
Wykres z Excela do Power Pointa
W efekcie powyższych działań, mamy przygotowane dane i wykresy. Teraz możemy „połączyć się” z plikiem PowerPoint. Do przygotowanego wcześniej szablonu prezentacji, wstawimy łącza do kolejnych wykresów.
Przejdź do Excela i zaznacz pierwszy wykres.
Skopiuj go, używając skrótu CTRL + C.
Przejdź do prezentacji Power Point, na wybrany slajd.
Na wstążce głównej, wybierz WKLEJ > WKLEJ SPECJALNIE > WKLEJ JAKO ŁĄCZE.
Powtórz punkty 1-4 dla każdego wykresu.
Dzięki powyższym krokom, na Twojej prezentacji będą zawsze aktualne dane. Jeśli zmienią się dane w danych źródłowych w Excelu, odzwierciedlenie tych zmian będzie automatycznie pokazane na prezentacji Power Point.
Podsumowanie
W tzw. pracy biurowej często używamy aplikacji z pakietu Office. Tworzymy dokumenty w Word, analizujemy dane w Excelu i przygotowujemy super prezentacje w Power Point.
Prawdę powiedziawszy, często zapominamy, że wszystkie te aplikacje dobrze ze sobą współpracują. Działania takie jak: osadzanie wykresów i tabel Excela w dokumencie Word, czy na slajdach Power Point – to przykład takiego udanego połączenia.
Mam nadzieję, że ten artykuł rozwiązał część Twoich problemów z przygotowaniem prezentacji na korporacyjne spotkania z szefem czy Zarządem 🙂 Jeżeli uważasz, że wiedza tutaj zawarta jest tego warta – udostępnij go proszę w swoich mediach społecznościowych. Kliknij poniżej.
Odchylenie standardowe to jedna z podstawowych miar statystycznych, która pomoże Ci lepiej zrozumieć analizowane przez Ciebie dane. Na pewno często liczysz średnią arytmetyczną. Interpretacja wyników z samej średniej może być (i często jest) niewystarczająca. Odchylenie standardowe „poinformuje” Cię jak zmieniają się wyniki, wokół tej średniej. A to może mieć kluczowe znaczenie dla dalszych obliczeń i wniosków.
Średnia, średniej nierówna
Mamy taką sytuację. Prowadzimy miesięczny rejestr wydatków na hobby dla kilku osób. W kolejnych kolumnach umieścimy miesiące, w których odnotujemy wydatki. Na końcu możemy oczywiście podsumować wydatki roczne.
Jednym z najprostszych sposobów na sumowanie jest zaznaczenie danych oraz pustej komórki po prawej stronie, gdzie mamy umieścić wynik. Teraz użyj skrótu klawiszowego ALT + =.
Po podsumowaniu zobaczysz, że każda z osób wydała 4 200 zł. Jednak w tabeli widzimy, że każda osoba inaczej wydawała te pieniądze w poszczególnych miesiącach. Suma nie jest zatem wystarczającą miarą, aby opisać nasze dane.
Pierwszą podstawową miarą jakiej możemy użyć jest średnia arytmetyczna. Jej użycie pozwoli nam sprawdzić jak średnio, w każdym miesiącu wyglądały wydatki każdej osoby.
W wyniku widzimy, że każda osoba wydawała średnio w miesiącu 350 zł na swoje hobby. Jeśli przyjrzysz się danym, to znowu okazuje się, że ta miara nie jest wystarczająca, aby opisać różnorodność wydatków w poszczególnych miesiącach. Za chwilę okaże się że jedna średnia „nie równa” innej średniej.
Odchylenie standardowe
Zobacz, że Ania w każdym miesiącu wydawała bardzo podobnie. Kasia przez pierwsze pół roku nie wydała nawet złotówki, natomiast później wydawała bardzo dużo. Z kolei u Tomka bywało różnie. Czasami nie wydał nic, czasami 700 zł, a czasami jego wydatki były bliskiej średniej.
Aby opisać tego typu rozbieżności możesz użyć odchylenia standardowego. Ta miara pokaże jak wydatki w poszczególnych miesiącach, przeciętnie różniły się od średniej arytmetycznej.
Excel oczywiście ma wbudowanych wiele funkcji statystycznych, w tym funkcję odchylenia standardowego:
=ODCH.STAND.POPUL(zakres komórek)
W przypadku naszych danych, otrzymamy następujące wyniki:
Ania 50 zł
Kasia 350 zł
Tomek 189,30 zł
Jak je interpretować?
Możemy powiedzieć, że średnie wydatki na hobby Ani w poszczególnych miesiącach różniły się od średniej arytmetycznej przeciętnie o 50 zł. Za to w przypadku Kasi, było to aż 350 zł.
Zatem, im mniejsza wartość odchylenia standardowego tym wydatki w poszczególnych miesiącach są bardziej zbliżone do średniej. Większe odchylenie standardowe, oznacza że wydatki w poszczególnych miesiącach bardziej odbiegają od średniej arytmetycznej.
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
Dzięki zastosowaniu odchylenia standardowego możesz w łatwy sposób określić czy Twoje dane są stabilne czy zmienne. To może mieć duże znaczenie dla interpretacji wyników.
Przykładowo, jeżeli masz dwie grupy pracowników i w każdej z grup średnia ocen wynosi 3, to czy tylko na tej podstawie możesz powiedzieć, że obie grupy są do siebie podobne? Zastosowanie odchylenia, powie Ci znacznie więcej.
Jeśli wiedza zawarta w tym artykule i filmie okazała się dla Ciebie przydatna – udostępnij go proszę w swoich mediach społecznościowych. Kliknij na ikonę, poniżej.
Jak pobrać dane z internetu za pomocą Excel Power Query
Excel Power Query pozwoli Ci na automatyczne pobieranie danych z internetu wprost do arkusza. Jeżeli do tej pory kopiowałeś i wklejałeś te dane to jest lepszy, bo automatyczny sposób. Wystarczy raz ustawić połączenie i pozwolić Excelowi zrobić robotę. Zobacz jak.
Excel Power Query: połącz się ze stroną www
Wyobraź sobie, że prowadzisz sklep internetowy z produktami, które importujesz z zagranicy. Oprócz sprzedaży w Polsce, zdarza Ci się sprzedawać również za granicę. W takiej działalności ceny towarów wyrażone w różnych walutach są podstawą zarządzania sprzedażą.
W jaki sposób codziennie pobierać aktualne kursy walut i zarządzać na tej podstawie sprzedażą? Ręczne kopiowanie i wklejanie wartości do arkuszy jest czasochłonne i nieefektywne. Wykorzystamy narzędzie Power Query Excela, aby połączyć się ze stroną NBP, z oficjalnymi kursami walut.
Oto co musisz zrobić:
Przejdź na wstążkę DANE i z sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH, wybierz POBIERZ DANE i Z SIECI WEB.
W okienku Z SIECI WEB wklej adres url strony, która zawiera interesujące Cię dane i kliknij OK. W tym przypadku, będzie to strona z notowaniami kursów walut NBP.
Z NAWIGATORA wybierz tabelę, która zawiera interesujące Ciebie dane. W naszym przypadku, to „Table 0”
Jeżeli nie chcesz przekształcać w żaden sposób danych zawartych w tej tabeli, wybierz przycisk ZAŁADUJ. My potrzebujemy przekształcić nasze kursy walut, ponieważ czasami są one wyrażone w jedności (np. 1 USD), a czasami w większej jednostce (np. 100 JPY) Dlatego wybieramy opcję PRZEKSZTAŁĆ DANE.
Przekształcanie danych w Power Query
Uruchomimy edytor Power Query, w którym będziemy mogli przekształcić nasze dane do odpowiadającej nam postaci.
Musimy sprowadzić wszystkie kursy do tego samego mianownika, czyli do jedności. Aby to zrobić, należy oczywiście podzielić średni kurs przez podane jednostki. Problem w tym, że obok jednostki mam podany kod waluty, czyli tekst np. 1 EUR. Dlatego też, musimy rozdzielić jednostkę wyrażoną liczbowo od kodu wyrażonego tekstem. Użyjesz narzędzia podobnego do TEKST JAKO KOLUMNY, z Excela.
Zaznacz kolumnę „Kod waluty” i wybierz opcję PODZIEL KOLUMNY > WG OGRANICZNIKA. Znajdziesz ją na wstążce STRONA GŁÓWNA.
Excel domyślił się, że w tym przypadku ogranicznikiem jest SPACJA, więc pozostaje Ci zatwierdzić domyślny wybór.
Powstały dwie kolumny. Jedną z nich – z jednostkami – wykorzystasz do dalszych obliczeń.
Ze wstążki DODAJ KOLUMNĘ, wybierz opcję KOLUMNA NIESTANDARDOWA.
W okienku dialogowym wprowadź swoją nazwę nowej kolumny, oraz ustawiając się w polu FORMUŁA KOLUMNY NIESTANDARDOWEJ kliknij dwukrotnie na kolumnę z kursem średnim, wprowadź znak dzielenia i ponownie dwuklik na kolumnie z jednostką waluty.
=[Kurs średni]/[Kod waluty.1]
Możesz już usunąć dwie kolumny, które wykorzystałeś do obliczenia jednostkowego kursu. Kliknij na nie prawym przyciskiem myszy i wybierz USUŃ KOLUMNY.
Teraz załaduj obrobione dane do arkusza. Przejdź na STRONĘ GŁÓWNĄ i wybierz opcję ZAMKNIJ I ZAŁADUJ DO…
Jak zautomatyzować pracę na pobieranych danych
Po wybraniu docelowego miejsca, gdzie mają się pojawić Twoje dane możesz ustawić opcję automatycznego odświeżania. Dzięki temu, nie będziesz musiał się martwić o aktualność danych.
Zaznacz tabelę z danymi i po prawej stronie zobaczysz Twoje zapytanie Power Query. Kliknij na nie prawym przyciskiem myszy i wybierz WŁAŚCIWOŚCI. Teraz w polu ODŚWIEŻ CO – ustaw wybrany interwał w minutach. Od teraz dane będą pobierane automatycznie we wskazanych przez Ciebie odstępach czasu.
Samo pobranie danych to nie wszystko. Możesz je wykorzystać, aby pokazywać ceny produktów w przeliczeniu na wartość w różnych walutach.
Stwórz listę rozwijaną z oznaczeniem symboli poszczególnych walut. Teraz dla wybranej z listy rozwijanej waluty, pobierzemy odpowiedni kurs waluty z naszej tabeli z kursami walut. W tym celu wykorzystaj funkcję WYSZUKAJ PIONOWO.
W kolejnym kroku wystarczy, że podzielisz cenę towaru w zł, przez komórkę w której znajduje się kurs wybranej aktualnie waluty.
Możesz jeszcze stworzyć formułę, która obok przeliczonej wartości będzie wyświetlała także symbol waluty. Aby, to osiągnąć pobierz symbol waluty na podstawie komórki w której wybierana jest waluta danego kraju. Ponownie, wykorzystaj funkcję WYSZUKAJ PIONOWO.
Następnie zmodyfikuj formułę przeliczającą wartość produktu w zł do wybranej waluty w taki sposób (tekst w cudzysłowie oczywiście musisz zastąpić odpowiednim adresem komórki):
=ZAOKR("komórka z wartością w PLN"/"komórka z kursem";2)& " " &"komórka z symbolem waluty"
Efekt końcowy, powinien wyglądać tak:
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
Jak widzisz pobieranie danych z internetu, z wykorzystaniem Power Query może znacząco przyspieszyć Twoją pracę. Co więcej, zyskujesz możliwość analizowania danych – w zasadzie online.
Mam do Ciebie prośbę – jeśli uważasz ten artykuł za wartościowy – udostępnij go proszę na swoim Facebooku.
Formatowanie warunkowe w zależności od zawartości innej komórki
Formatowanie warunkowe to jedna z podstawowych dróg, aby uatrakcyjnić Twoje arkusze. Jeśli to dla Ciebie nowy temat zachęcam do lektury poprzedniego artykułu. Przeczytasz w nim o najważniejszych możliwościach jakie daje to narzędzie.
W tym artykule przeczytasz o nieco bardziej zaawansowanych trickach, takich jak formatowanie warunkowe w zależności od komórki.
Proste formatowanie warunkowe
Formatowanie warunkowe umożliwia formatowanie komórek w zależności od zawartości danej komórki. Co to w praktyce oznacza? Wyobraź sobie, że chciałbyś żeby komórka zawierająca wartość sprzedaży zmieniała tło na zielone, w sytuacji kiedy wprowadzisz tam wartość powyżej 100 000 zł. Na takie operacje pozwala właśnie formatowanie warunkowe.
Nie jest to formatowanie statyczne tylko dynamiczne. To znaczy, że jest wykonywane „w locie”. Excel na bieżąco sprawdza zawartość komórki i w zależności od ustawionych warunków – włącza, bądź wyłącza formatowanie.
Co musisz zrobić aby skorzystać z tej funkcjonalności?
Po pierwsze, wskaż obszar na którym ma działać formatowanie warunkowe. Załóżmy, że mamy rejestr sprzedaży w firmie. Chcielibyśmy teraz w naszej bazie sprzedaży, w grupie towaru wyróżnić jakąś konkretną grupę np. AGD.
W standardowy sposób musielibyśmy je najpierw posortować albo przefiltrować aby osiągnąć cel. Przy formatowaniu warunkowym nie musimy wykonywać żadnych dodatkowych czynności, aby wyróżnić komórki spełniające nasze warunki. Excel zrobi to za nas.
Przejdźmy do praktycznego przykładu. Zaznacz zakres komórek, na którym ma działać formatowanie. Przejdź na NARZĘDZIA GŁÓWNE i z sekcji STYLE wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.
Nasze formatowanie ma zadziałać wtedy, gdy w komórce pojawi się wyraz „AGD”. Zatem wybieramy opcję RÓWNE i wpisujemy warunek, czyli słowo AGD. Po prawej stronie mamy style formatowania. Możesz wybrać z kilku opcji stylów zdefiniowanych. Jeżeli żaden z nich nie spełnia Twoich oczekiwań, możesz wybrać FORMAT NIESTANDARDOWY i samodzielnie określić sposób formatowania.
Formatowanie warunkowe w zależności od komórki
Jeżeli chciałbyś teraz zmienić reguły powyższego formatowania i zamiast słowa „AGD” wybrać na przykład „Meble” musiałbyś przejść do menu formatowania warunkowego i edytować reguły.
Oczywiście jest lepszy sposób. Załóżmy, że chcesz teraz wyróżniać „kraj odbiorcy”. Chcemy uniknąć „sztywnego” określania konkretnego kraju. Dlatego zrobimy parametr w wybranej komórce. Parametr oznacza, że będziesz mógł zmieniać zawartość tej komórki, a formatowanie warunkowe będzie uzależnione od wartości z tej komórki.
Jeśli np. wpiszesz „Polska” to „zapalą się” tylko te komórki, które zawierają to słowo. Zmieniając zawartość komórki na „Anglia” sformatujesz komórki które zawierają ten kraj.
Jak widzisz to dużo ciekawsze rozwiązanie i łatwiejsze w obsłudze rozwiązanie. Jak to zrobić?
Zaznacz obszar, który chcesz sformatować.
Ze wstążki NARZĘDZIA GŁÓWNE, wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.
Wybierz opcję RÓWNE…
Teraz nie wpisujesz statycznej nazwy kraju, tylko odwołujesz się do komórki, w której został umieszczony wspomniany wcześniej parametr.
Pozostaw domyślny lub zmień styl formatowania.
Zatwierdź wybór.
Teraz, gdy zmienisz wartość komórki – parametru, automatycznie spowoduje to zmianę formatowania na właściwych komórkach. Prawda, że bardzo elastyczne rozwiązanie?
Połącz formatowanie warunkowe z listą rozwijaną
W poprzednim przykładzie, w komórce która była parametrem wartości zmieniałeś ręcznie. Byłoby dobrze to nieco zautomatyzować.
Załóżmy, że masz tabelkę w której chciałbyś wyróżniać wybranych klientów. Nie wiesz ilu ich dokładnie jest, a tym bardziej nie pamiętasz ich wszystkich. Pomysł polega na tym, że utworzysz listę rozwijaną z nazwami klientów. Komórka, w której znajdzie się ta lista – będzie Twoim parametrem do formatowania warunkowego.
W pierwszej kolejności – przygotuj listę unikatowych klientów. Skopiuj wszystkich klientów w oddzielne miejsce. Teraz, korzystając z narzędzia USUŃ DUPLIKATY na wstążce DANE, przygotujesz listę unikatowych wartości.
Na podstawie tych wartości stwórz listę rozwijaną. O tym jak to zrobić przeczytasz w poprzednim artykule.
Teraz, gdy powiążesz komórkę z listą rozwijaną z formatowaniem warunkowym, będziesz mógł wybierać klientów z listy, a formatowania będą automatycznie się zmieniały.
Formatowanie warunkowe, które podświetla cały wiersz
Byłoby jeszcze ciekawiej, gdyby po wybraniu parametru, w tabeli z danymi podświetlały się całe wiersze, które zawierają wybrany parametr. Zatem do dzieła.
Zaznacz cały obszar z danymi. Robisz tak dlatego, że formatowanie ma dotyczyć całych wierszy.
Przejdź do menu FORMATOWANIA WARUNKOWEGO i wybierz opcję NOWA REGUŁA. W standardowym menu nie ma opcji pozwalającej na podświetlenie całych wierszy. Musisz stworzyć własną regułę.
W menu nowej reguły formatowania wybierz opcję UŻYJ FORMUŁY DO OKREŚLANIA KOMÓREK, KTÓRE NALEŻY SFORMATOWAĆ.
Teraz stwórz taką formułę, która gdy warunki formatowania zostaną spełnione to wynik formuły da PRAWDĘ, a w przeciwnym razie da FAŁSZ.
Załóżmy, że chcesz aby podświetlił Ci się cały wiersz wtedy, kiedy wpis w kolumnie klient, będzie równy temu co jest wpisane w komórce F1. Wpisz:
=$B7=$F$1
Taka formuła oznacza, że porównujesz zawartość komórki B7 (tam masz nazwę klienta) z Twoim parametrem w komorce F1. Zauważ, że wybierając komórkę B7, Excel domyślnie wstawił wszędzie dolary (czyli zmienił adresowanie komórki na bezwzględne) W tym momencie, to nie jest działanie prawidłowe dla naszych potrzeb.
Chcemy stworzyć taką formułę, która dla kolejnych komórek w kolumnie B będzie sprawdzała czy wpis równa się zawartości komórki F1. Czyli dla całego obszaru zawsze będziemy odnosić się do komórki F1, ale porównywana komórka będzie się zmieniać. To oznacza, że adres komórki w sprawdzanej kolumnie B musi mieć postać adresu mieszanego, czyli blokujemy kolumnę B znakiem $, ale numery wierszy pozostawiamy niezablokowane (bez znaku dolara)
Excel będzie przynosił sprawdzanie warunku na kolejne kolumny i gdybyśmy nie zablokowali kolumny B w naszym warunku porównywanie nie miałoby sensu. Dlaczego? Ponieważ kazalibyśmy Excelowi porównywać np. nazwę klienta z grupą towaru.
Dzięki temu, że zablokowaliśmy kolumnę B, w każdej kolejnej kolumnie sprawdzany jest właśnie ten warunek – właściwy dla tej kolumny.
Kolejne kroki są już analogiczne do poprzedniego postępowania. Wybierz FORMATUJ i ustaw preferowany przez Ciebie styl formatowania. Teraz, gdy wybierzesz z listy rozwijanej kolejnych klientów, na Twojej liście danych będą podświetlały się całe wiersze, właściwe dla wybranego klienta.
Podsumowanie
Formatowanie warunkowe daje całą masę możliwości na ciekawą wizualizację Twoich danych. Natomiast formatowanie w zależności od zawartości innej komórki, daje również opcję na interaktywność w arkuszu.
Jestem ciekawy jak Ty wykorzystujesz formatowanie warunkowe w swoich arkuszach. Podziel się Twoimi sposobami w komentarzu. A jeżeli artykuł pomógł Ci ulepszyć Twoje arkusze – podziel się nim proszę w Twoich mediach społecznościowych.
Dynamiczna lista rozwijana, czyli jak przyspieszyć pracę
Listy rozwijane możesz zrobić na kilka sposobów. Dzięki lekturze tego artykułu dowiesz się, jak dzięki nim zautomatyzować pracę. Dynamiczna lista rozwijana pozwoli Ci ją przyspieszyć.
Na koniec dowiesz się jak tworzyć hierarchiczne listy rozwijane. Hierarchiczne, to znaczy takie, gdzie lista wyboru z jednej listy zależy od wyborów z innej. Brzmi ciekawie? Zapraszam do lektury!
Wszystkie opisywane w artykule funkcjonalności znajdziesz w filmie przygotowanym przez Jacka.
Prosta lista rozwijana
Załóżmy, że masz taką sytuację – dysponujesz bazą sprzedaży i chciałbyś zliczać łączną wartość sprzedaży netto dla poszczególnych grup towarów. Przygotujemy tabelę, w której będzie możliwe wybranie poszczególnych towarów z listy oraz automatyczne podsumowanie sprzedaży, wg tego wyboru.
W kolejnych krokach pokażę Ci jak się za to zabrać.
Przygotuj wszystkie unikatowe elementy listy, które mają pokazywać się na liście rozwijanej. Następnie ustaw się na komórce, gdzie ma się znaleźć lista rozwijana i wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.
W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI. O co tutaj chodzi? Domyślnie do każdej komórki Excela możesz wpisać dowolną wartość. Natomiast my chcemy, aby do komórki z listą wyboru, mogły być wpisane tylko wartości zdefiniowane wcześniej przez nas.
Dlatego, wybieramy polecenie LISTA i klikając na ikonę ŹRÓDŁO wskazujemy zakres komórek, gdzie umieściliśmy nasze unikalne kategorie towarów. Zatwierdzamy i nasza lista rozwijana jest gotowa.
Dynamiczna lista rozwijana
Prosta lista rozwijana to nie koniec możliwości. Pewnie zauważyłeś, że w momencie dodania kolejnej wartości do listy unikalnych towarów, lista rozwijana nie zaktualizuje się o te dodatkowe wartości.
Stworzymy zatem listy dynamiczne. Co to oznacza? Gdy dodamy kolejną grupę towarów – to nie wykonując żadnych dodatkowych czynności – nowy wpis automatycznie pokaże się na naszej liście rozwijanej. Jak się do tego zabrać?
Pierwszą rzeczą jaką musisz zrobić to przygotować listę. Kolejnym elementem całej układanki jest przerobienie tej listy na tabelę excelową. Jednym z najprostszych sposobów jest użycie skrótu CTRL + T.
Dzięki temu, że ten obszar jest teraz tabelą, dodanie kolejnych elementów będzie w niej automatycznie uwzględniane. Jeśli podłączysz teraz sprawdzanie poprawności do tabeli, automatycznie dodane rekordy będą pojawiać się na liście.
Początek drogi jest analogiczny jak poprzednio. To znaczy:
Ustaw się na komórce, gdzie ma się znaleźć lista rozwijana.
Wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.
W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI.
Wybierz polecenie LISTA i klikając na ikonę ŹRÓDŁO wskaż tabelę, którą utworzyłeś aletylko elementy listy bez nagłówka. Excel rozpozna, że chodzi tylko o same wpisy, a nie o nagłówek.
Zatwierdź klikając OK.
Teraz, gdy dodasz kolejny wpis na liście, Twoja lista rozwijana automatycznie uwzględni tę wartość!
Hierarchiczne listy rozwijane
Naprawdę ciekawym rozwiązaniem są hierarchiczne listy rozwijane. Co to takiego? Załóżmy że, mamy główne grupy towaru, a w każdej grupie towaru znajdują się poszczególne towary. Na przykład w grupie “AGD”, mamy: lodówka, mikrofalówka, okap. W grupie “Meble” mamy: biurko, fotel biurowy, itd.
Cały trick polega na tym, aby stworzyć listy rozwijane w taki sposób, że jeśli wybierzesz główną grupę towaru (na przykład AGD), to pokażą się tylko towary przypisane do tej grupy.
Wbrew pozorom, takie rozwiązanie nie jest trudne. Tak jak poprzednio, musisz przygotować dane, tyle że tym razem nagłówki tabel będą oznaczały grupę produktów, a poniżej będą poszczególne towary, czyli elementy listy rozwijanej.
Kolejny krok to nazwanie obszarów dla poszczególnych grup towarów. Zaznacz wszystkie zakresy łącznie z nagłówkami. Musisz to zrobić używając przycisku CTRL na klawiaturze oraz lewego przycisku myszy. Następnie przejdź na wstążkę FORMUŁY i wybierz z sekcji NAZWY ZDEFINIOWANE polecenie UTWÓRZ Z ZAZNACZENIA. Sprawdź czy jest wybrana opcja GÓRNY WIERSZ i zatwierdź wybór.
Utworzyłeś 3 nazwy grup towarów (AGD, meble i oświetlenie) Sprawdzisz to w MENEDŻERZE NAZW na wstążce FORMUŁY.
Przejdź teraz do komórki, gdzie ma powstać lista rozwijana z możliwością wyboru grupy towarów. W tym miejscu tworzysz listę w standardowy, opisany wcześniej sposób. Oczywiście jako zakres danych dla tej listy wskażesz tylko nagłówki z nazwami grup towarów.
Następnie przejdź do komórki, gdzie ma pojawić się lista rozwijana z towarami. Zawartość tej listy będzie zależna od wybranej w poprzedniej komórce grupy towarów.
Podobnie jak wcześniej, przejdź do polecenia POPRAWNOŚĆ DANYCH i wybierz LISTĘ. W źródło danych nie możesz wpisać, ani wskazać zakresu z: “AGD, meble, oświetlenie” ponieważ Excel potraktowałby to jako elementy listy.
Zamiast tego wykorzystaj dodatkową funkcję ADR.POŚR, która niejako “przetłumaczy” wcześniej nazwany zakres komórek (np. AGD) na konkretny zakres komórek, w której znajdują się towary z tej właśnie grupy. Jako argument tej funkcji musisz oczywiście wskazać komórkę, w której umieściłeś listę rozwijaną z grupami towarów. Zatwierdzasz i gotowe! Wybierając z listy rozwijanej grupa towarów “Oświetlenie”, zobaczysz tylko produkty, które należą do tej kategorii.
Podsumowanie
Ten artykuł pokazał Ci jak wiele ciekawych funkcjonalności kryje w sobie prosta lista rozwijana. Jak w większości przypadków, połączenie jej z innymi funkcjonalnościami Excela daje najlepsze efekty.
Listy są często wykorzystywane, w tworzeniu interaktywnych raportów i dashboardów. Mam nadzieję, że uda Ci się wykorzystać opisywane tu możliwości w Twoich zestawieniach i raportach. Opisz proszę w komentarzu, do czego Ty wykorzystujesz listy rozwijane..
Zanim zaczniesz – jedno pytanie, które zmienia wszystko
Gdy przez lata prowadziłem szkolenia z Excela, bardzo często zaczynałem od jednego, pozornie prostego pytania skierowanego do uczestników:
“Tworzysz Excela czy go odczytujesz?”
Reakcje bywały różne. Niektórzy marszczyli czoło, inni od razu wiedzieli, o co chodzi. Ale to proste rozróżnienie w kilka minut pokazywało mi, z kim mam do czynienia i jak powinien wyglądać program nauki. Bo codzienna praca z Excelem ma zupełnie inny charakter w zależności od roli, którą pełnisz.
Jedni budują narzędzia, raporty, systemy. Inni analizują, sprawdzają i interpretują dane przygotowane przez kogoś innego. I choć obie grupy pracują w tym samym programie – potrzebują zupełnie innych kompetencji i zupełnie innej ścieżki rozwoju.
W tym artykule przeprowadzę Cię przez całość: od zrozumienia swojej roli, przez dobór formy nauki, aż po konkretne etapy ścieżki. Bez obietnic magicznych sztuczek – za to z doświadczeniem setek godzin szkoleń i wiedzą, co naprawdę działa.
1. Dwie role, dwie ścieżki – sprawdź, kim jesteś
Zanim zdecydujesz, czego i jak się uczyć, musisz odpowiedzieć sobie na to fundamentalne pytanie. Nie ma jednej dobrej odpowiedzi. Obie role są wartościowe i potrzebne. Ale mylenie tych ścieżek to jeden z najczęstszych błędów, który sprawia, że nauka Excela staje się frustrującą stratą czasu.
Rola 1: Analityk danych – odczytujesz i interpretujesz
Jeżeli najczęściej odbierasz pliki przygotowane przez innych, Twoje potrzeby są konkretne i dobrze zdefiniowane. Pracujesz na gotowych strukturach danych i Twoim zadaniem jest wyciąganie wniosków, nie budowanie narzędzi.
W tej roli rzadko budujesz złożone formuły, rzadko tworzysz walidacje danych czy rozbudowane formatowanie warunkowe. Jeszcze rzadziej sięgasz po Power Query, model danych czy VBA. I to jest w pełni wystarczające pod warunkiem, że znasz dobrze swój zestaw narzędzi.
Rola 2: Twórca – budujesz i projektujesz
Jeżeli jesteś osobą, która tworzy narzędzia, raporty i systemy sytuacja wygląda zupełnie inaczej. Excel przestaje być arkuszem do przeglądania danych, a staje się środowiskiem produkcyjnym.
Twój zakres odpowiedzialności obejmuje:
budowanie formuł i złożonych wyrażeń logicznych
projektowanie struktury danych i relacji
tworzenie logiki przekształceń i transformacji
pracę z Power Query (język M) i Power Pivot (DAX)
tworzenie dashboardów i zaawansowanych wykresów
automatyzację z użyciem VBA lub Power Automate
Twoja odpowiedzialność nie kończy się na analizie. Odpowiadasz za to, czy rozwiązanie działa stabilnie, jest skalowalne i może być rozwijane przez innych.
Jeżeli jesteś osobą decyzyjną i widzisz, że pliki trafiające na Twoje biurko mogłyby być lepiej zaprojektowane luka kompetencyjna może leżeć nie w Tobie, ale w zespole. Warto to diagnozować świadomie.
2. Trzy przeszkody w nauce Excela – i jak je pokonać
Przez lata pracy z ludźmi uczącymi się Excela zaobserwowałem, że przeszkoda prawie zawsze jest jedna z trzech. Albo brakuje czasu, albo wytyczonej ścieżki, albo systematyczności. Rzadko kiedy problemem jest poziom inteligencji czy zdolności – Excel jest narzędziem, które można opanować. Pytanie tylko: jak podejść do tego mądrze?
Przeszkoda 1: Brak czasu
To najczęstsza wymówka ale też często realny problem. Klucz polega na tym, żeby nie uczyć się Excela obok pracy, ale przy okazji pracy. Jeżeli w najbliższym tygodniu masz zadanie, do którego potrzebujesz tabeli przestawnej to jest Twój moment. Nie szukaj czasu na naukę. Znajdź problem, który wymaga nowej umiejętności i rozwiąż go ucząc się.
Przeszkoda 2: Brak ścieżki
Nieokreślony cel to prosta droga do frustracji. Ktoś otwiera kurs albo książkę, przerabia pierwsze lekcje i… nie wie, po co mu to. Dlatego zanim zaczniesz się uczyć, zdefiniuj cel: co chcesz umieć za 3 miesiące? Do jakiej konkretnej pracy będzie Ci to potrzebne? Poniżej znajdziesz gotowe etapy, które możesz ułożyć w swoją ścieżkę.
Przeszkoda 3: Brak systematyczności
Systematyczność nie oznacza codziennej nauki przez dwie godziny. Oznacza regularny kontakt z Excelem w kontekście realnych zadań. Nawet 20 minut dziennie przy prawdziwym problemie daje więcej niż weekendowy maraton na kursie, po którym wracasz do codzienności i nic nie ćwiczysz.
3. Formy nauki Excela – co wybrać i kiedy
Szkolenie stacjonarne, kurs online, książka, blog, YouTube – każda forma ma swoje miejsce. Kluczem jest dopasowanie do Twojego stylu uczenia się i etapu, na którym jesteś.
Szkolenia zamknięte i otwarte
To forma, która sprawdza się najlepiej, gdy potrzebujesz szybkiego, spójnego zakresu materiału z możliwością zadawania pytań na bieżąco. Dobry trener poprowadzi Cię od punktu A do punktu B, a ćwiczenia praktyczne utrwalą materiał. Wadą jest ograniczony czas na konsultacje po szkoleniu. Jeśli nie będziesz ćwiczyć – większość wiedzy zniknie w ciągu tygodnia.
Kiedy warto: gdy Twój zespół ma konkretną lukę kompetencyjną lub gdy przygotowujesz się do nowej roli.
Kursy online
Mają wszystkie zalety szkolenia stacjonarnego. Poza fizycznym kontaktem z trenerem. Ważną zaletą jest możliwość dostosowania tempa nauki. Część kursów oferuje dostęp do grupy lub konsultacje z mentorem, co znacznie podnosi ich wartość. Wymaga jednak od Ciebie samodyscypliny.
Kiedy warto: gdy uczysz się samodzielnie i potrzebujesz struktury, ale chcesz elastyczności czasowej.
Książki
Klasyczny podręcznik sprawdza się świetnie u osób, które lubią pogłębione, linearne przyswajanie wiedzy. Słabością jest to, że obok treści przydatnych znajdziesz mnóstwo takich, które nigdy Ci się nie przydadzą. Klasyczną pozycją godną polecenia jest seria Excel. Biblia Johna Walkenbacha. Ale uczciwie: to lektura dla tych, którzy naprawdę chcą rozumieć Excela w głębi.
Kiedy warto: jako uzupełnienie praktycznej nauki lub do pogłębienia konkretnego tematu.
Blogi, artykuły i tutoriale wideo
To niezastąpione źródło rozwiązań konkretnych problemów. Gdy wpisujesz w Google „harmonogram w Excelu” i trafiasz na gotowy tutorial – to jest dokładnie właściwe zastosowanie tej formy. Nie nauczysz się z tego Excela kompleksowo, ale szybko znajdziesz odpowiedź na konkretne pytanie.
Kiedy warto: zawsze, gdy pojawia się konkretny problem do rozwiązania.
4. Ścieżka nauki Excela – etapy dopasowane do celu
Poniżej znajdziesz cztery etapy, z których możesz ułożyć swoją własną ścieżkę. Etapy A–D nie mają charakteru ściśle chronologicznego. Możesz przejść z A bezpośrednio do C, jeśli Twoja rola tego wymaga. Każdy etap zakłada, że znasz absolutne podstawy środowiska Excela.
Etap A: Podstawy, które każdy powinien znać
Nawigacja, formatowanie, podstawowe operacje na komórkach
Jeśli jesteś analitykiem, który dostaje gotowe pliki, etap C może dać Ci więcej niż cokolwiek innego. Znajomość tabel przestawnych i Power Query bez zaawansowanych formuł z etapu B potrafi zrobić z Ciebie najcenniejszą osobę w zespole. Możesz uzupełniać wiedzę o formułach stopniowo, przy okazji realnych zadań.
5. Jak się uczyć skutecznie? Dwie metody, które działają
Wybrałeś ścieżkę. Wiesz, kim jesteś: twórcą czy analitykiem. Teraz czas na metodę nauki. Na podstawie własnych doświadczeń, zarówno jako uczącego się, jak i trenera – polecam dwa podejścia.
Metoda 1: Najpierw podstawy teoretyczne nowego tematu
Zanim zaczniesz pracować z nowym narzędziem – powiedzmy Power Query – warto poświęcić 30–60 minut na zrozumienie, czym to narzędzie jest i do czego służy. Jakie są jego możliwości? Jakie problemy rozwiązuje? Jakie są podstawowe reguły działania?
Nie musisz znać wszystkiego. Potrzebujesz wystarczająco dużo, żeby wiedzieć, że możesz to zrobić i żeby rozumieć kierunek działania. Tę wiedzę możesz zdobyć ze szkolenia, artykułu, tutorialu wideo – cokolwiek pasuje do Twojego stylu.
Metoda 2: Praktyka na realnym problemie
Mając tę podstawową wiedzę – znajdź powód, żeby używać jej na co dzień. To kluczowa zasada, która odróżnia osoby, które naprawdę opanowują Excela, od tych, które kończą kursy i wracają do starych nawyków.
Najlepszą metodą nauki jest praktyka związana z realnym zadaniem. Jeśli masz do przekształcenia dane z pięciu arkuszy do jednego raportu – to jest Twój moment na Power Query. Jeśli szef poprosił Cię o analizę sprzedaży – to jest Twój moment na tabele przestawne.
Z doświadczenia: nigdy nie uczyłem się nowych funkcji na zasadzie „kolejny do kolekcji‟. Zawsze, gdy poznawałem nowe rozwiązanie – było to związane z konkretnym zadaniem lub problemem. Podstawą jest ciekawość i postawa: „jak się nie da, to jak się da?‟
6. Najczęstsze błędy w nauce Excela
Po setkach godzin szkoleń i rozmowach z osobami uczącymi się Excela widzę te same błędy. Może uda Ci się ich uniknąć.
Uczenie się wszystkiego po kolei. Nie ma sensu przerabiać całego Excela od deski do deski, jeśli 70% materiału nigdy Ci się nie przyda. Ucz się pod cel.
Start od VBA bez znajomości podstaw. Automatyzacja ma sens dopiero wtedy, gdy rozumiesz, co automatyzujesz. Zacznij od zrozumienia logiki Excela.
Nauka bez ćwiczenia na własnych danych. Sztuczne ćwiczenia z kursu to dobry start, ale prawdziwa nauka dzieje się na Twoich plikach, w Twoim kontekście.
Brak refleksji po ukończeniu kursu. Co umiesz teraz, czego wcześniej nie umiałeś? Gdzie jest Twoja następna luka? Bez tej refleksji trudno budować kompetencje systematycznie.
Uczenie się roli, której nie pełnisz. Jeżeli analizujesz dane, a spędzasz miesiące na nauce VBA – tracisz czas. Najpierw zidentyfikuj swoją rolę.
7. Co po Excelu? Kierunki dalszego rozwoju
Excel to świetne narzędzie ale nie jedyne. Jeżeli czujesz, że go opanowałeś lub że ogranicza Cię w tym, co chcesz robić, warto pomyśleć o kolejnym kroku.
Power BI – jeśli tworzysz raporty i dashboardy, Power BI to naturalna ewolucja. Łączy Excel z możliwościami zaawansowanej wizualizacji i publikacji raportów online.
Python lub R – jeśli pracujesz z dużymi zbiorami danych i potrzebujesz bardziej elastycznej analizy statystycznej, warto rozważyć jeden z tych języków.
SQL – jeśli Twoje dane siedzą w bazach danych, znajomość SQL pozwoli Ci pobierać je bezpośrednio, bez pośredników.
Dobra wiadomość: nie musisz znać Excela w 100%, żeby przejść do kolejnych narzędzi. Wystarczy, że rozumiesz logikę pracy z danymi. To, czego nauczyłeś się w Excelu, przełoży się na każde następne narzędzie analityczne.
Podsumowanie: Twoja ścieżka nauki Excela
Nauka Excela nie musi być udręką. Ale musi być świadoma. Zanim sięgniesz po kurs, książkę czy tutorial – odpowiedz sobie na trzy pytania:
Kim jestem w pracy z Excelem – twórcą czy analitykiem?
Jaki konkretny efekt chcę osiągnąć i w jakim czasie?
Jak mogę ćwiczyć nowe umiejętności na realnych zadaniach?
Mając te odpowiedzi wybierz formę nauki, dopasuj etapy ścieżki do swojej roli i ruszaj. Nie czekaj na idealny moment. Najlepsza nauka zaczyna się od pierwszego realnego problemu, który chcesz rozwiązać.
Masz pytania o ścieżkę nauki lub chcesz podzielić się swoim doświadczeniem? Zostaw komentarz poniżej. Chętnie podyskutuję. Każda historia jest inna i warto wymieniać się spostrzeżeniami.
Jak zrobić wykres świecowy? Od danych giełdowych po siatki płac.
Wykresy świecowe to stara, japońska technika analizy i prezentacji danych. Możesz ją wykorzystać zarówno do analizy danych giełdowych, jak i stworzenia wizualnej prezentacji siatki płac. Zastosowań jest wiele, bo wykres świecowy niesie ze sobą więcej informacji niż wykres liniowy czy kolumnowy. Pod tym względem „świece” przypominają wykresy pudełkowe [ang. box-plot]
W artykule opisuje jak zrobić taki wykres, z czego się składa i jak wykorzystać go do wizualizacji danych na przykładzie siatki płac.
Z czego składa się wykres świecowy
Wykresy świecowe często wykorzystuje się do analizy danych giełdowych. Dlatego, zaczniemy od przygotowania takiego wykresu na podstawie notowań spółki NEUCA. Ściągnij plik z danymi początkowymi.
Świeca reprezentuje 4 informacje. Cenę otwarcia i zamknięcia oraz minimum i maksimum z danego okresu. W sytuacji, gdy cena otwarcia jest mniejsza niż cena zamknięcia – mamy do czynienia ze wzrostem ceny i świeca jest biała. W odwrotnej sytuacji – cena otwarcia jest wyższa od ceny zamknięcia – mamy spadek i świeca jest czarna.
Istnieje wiele kombinacji (formacji) i możliwych wariantów świec, które mają dawać sygnały do kupna lub sprzedaży. Ale nie o inwestowaniu to post 🙂
W pliku znajdziesz kursy spółki NEUCA z kilkunastu dni. Aby stworzyć wykres świecowy, dane muszą mieć układ: otwarcie, maksimum, minimum i zamknięcie. Mając tak przygotowaną tabelę, wystarczy, że:
Zaznaczysz tabelę z danymi (łącznie z datami).
Na karcie WSTAWIANIE, wybierzesz POLECANE WYKRESY.
Z zakładki WSZYSTKIE WYKRESY wybierzesz GIEŁDOWY i drugi typ wykresu od lewej.
Klikniesz OK.
Po utworzeniu wykresu zobaczysz jedną niedogodność. Notowania akcji nie odbywają się każdego dnia i w związku z tym na wykresie pojawią się źle wyglądające luki.
Możesz temu zaradzić, zamieniając typ osi z daty na tekst. Kliknij prawym przyciskiem myszy na oś czasu i wybierz opcję OŚ TEKSTU.
Jak stworzyć wykres pokazujący siatkę płac
Opisywany wykres świecowy możesz także wykorzystać do zobrazowania siatek płac. Główna różnica polega na tym, że zamknięcie jest równe maksimum, a otwarcie – minimum. Można to łatwo uwzględnić w tabeli z danymi.
Ponadto, ponieważ na osi poziomej nie będzie czasu ale kategorie zaszeregowania – nieco inaczej będzie wyglądało tworzenie wykresu. Zamiast zaznaczać całą tabelę, łącznie z kolumną „Kategoria zaszeregowania”, zaznacz tylko kolumny z danymi o wynagrodzeniach. Kategorie na oś poziomą wprowadzisz później. Poza tym wykres utworzysz dokładnie tak samo jak opisany powyżej wykres świecowy.
Po stworzeniu wykresu, zauważysz że kategorie nie rosną od lewej do prawej. Możesz to zmienić klikając prawym przyciskiem na oś poziomą i wybierając opcję KATEGORIE W KOLEJNOŚCI ODWROTNEJ.
Całą operację tworzenia wykresu zobaczysz na filmie poniżej.
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 pewny, że wykres świecowy możesz wykorzystać do innych zadań niż „rysowanie” siatek płac czy wykresów giełdowych. Mam nadzieję, że artykuł dał Ci potrzebną wiedzę i inspirację do działania. Będę wdzięczny, jeżeli podzielisz się swoimi pomysłami na wykorzystanie „świec” w komentarzu.
Po co zamieniać kolumny na wiersze? Najczęściej po to, żeby przygotować dane do tabeli przestawnej. Z taką sytuacją masz do czynienia, kiedy masz tabelę z danymi w kolejnych kolumnach. Natomiast w tabeli przestawnej najlepiej mieć dane w postaci bazodanowej.
Zanim pojawił się POWER QUERY, trzeba było kombinować z różnymi, najczęściej czasochłonnymi metodami (np. z wykorzystaniem funkcji TRANSPONUJ) Teraz jest na to znacznie prostszy sposób i co ważne – pozwalający na automatyczną aktualizację. Zainteresowany?
Wyobraź sobie, że masz stworzyć budżet wynagrodzeń dla kilku działów. Najwygodniej będzie działać, dodając wartości w kolejnych kolumnach reprezentujących miesiące. Szczególnie, jeżeli będziesz używał formuł obliczających potrzebne wartości.
Zatem w czym problem?
Po stworzeniu budżetu i niezbędnych akceptacjach, trzeba będzie przenieść dane do bazy i raportów. Dzięki temu będziesz w stanie porównywać plan z wykonaniem. Najczęściej i najłatwiej robi się to za pomocą tabeli przestawnej. A powyższy format nie jest najlepszy dla tabel. Dużo lepiej sprawdza się układ bazodanowy, tak jak poniżej.
Kolumny na wiersze, czyli zrób UNPIVOT
Doprowadzenie do takiego układu danych, szczególnie gdy masz setki czy tysiące wierszy wymagało sporo zachodu. Wymagało, bo na szczęście mamy POWER QUERY, aka GET & TRANSFORM (od wersji 2016)
Poniżej znajdziesz kolejne kroki jakie powinieneś zrealizować. W nawiasie zaznaczyłem, jeżeli krok jest opcjonalny i wynika z tego jak wyglądają moje dane. Oczywiście możesz pobraćplik startowy i przećwiczyć kolejne etapy.
Potrzebny czas: 3 minuty
Jak zamienić kolumny na wiersze w POWER PIVOT
Zamień dane do postaci tabeli.
Kliknij w dowolnym miejscu z danymi i przekształć je do postaci tabeli. NARZĘDZIA GŁÓWNE > FORMATUJ JAKO TABELĘ lub skrót CTRL + L
Dodaj tabelę do POWER QUERY.
Przejdź na kartę POWER QUERY i wybierz polecenie Z TABELI/ ZAKRESU. W nowszych wersjach odpowiednie polecenie znajdziesz na karcie DANE, w sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH.
Usuń niepotrzebne kolumny (opcjonalnie).
Usuń ostatnią kolumnę RAZEM. Podsumowanie nie będzie potrzebne. Kliknij prawym przyciskiem myszy i wybierz USUŃ.
Zamień „null” na zera (opcjonalnie).
Zamieniając wartości null na 0, spowodujesz że dane dla tych kategorii pojawią się w docelowym układzie danych. W przeciwnym razie zostaną pominięte. Zaznacz kolumny z miesiącami. Przejdź na wstążkę PRZEKSZTAŁĆ, wybierz ZAMIENIANIE WARTOŚCI i w pierwszym okienku wpisz null, a w drugim 0.
Anuluj przestawianie innych kolumn.
Teraz zasadnicza część całej operacji. Zaznacz kolumny „Dział” i „Grupa Kosztów”. Te kolumny pozostaną niezmienione. Kliknij prawym przyciskiem myszy i z menu kontekstowego wybierz polecenie ANULUJ PRZESTAWIANIE INNYCH KOLUMN.
Zmień nazwę kolumn (opcjonalnie).
Kliknij dwukrotnie w nagłówek kolumny „Atrybut” i zmień nazwę na „Miesiąc”
Zapisz i załaduj do arkusza.
Przejdź na wstążkę NARZĘDZIA GŁÓWNE i wybierz ZAMKNIJ I ZAŁADUJ. Wybierając miejsce w nowym arkuszu, Twoje dane zostaną załadowane w przekształconej formie do wskazanego miejsca.
Na filmie poniżej zobaczysz wszystkie opisane kroki. Dodatkowo przekształcam dane z kolumny „Grupa kosztów”, aby pozbyć się niepotrzebnych liczb z opisów kosztów.
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
Zamiana kolumn na wiersze, dzięki POWER QUERY jest naprawdę prosta. Przekształcanie danych do takiej postaci jest wręcz koniecznością, jeżeli chcesz efektywnie pracować na tabelach przestawnych.
Jest jeszcze jedna zaleta opisanego mechanizmu. Jeśli dodasz kolejne dane w tabeli źródłowej, wystarczy że później klikniesz ODŚWIEŻ, a przekształcona tabela docelowa zostanie automatycznie zaktualizowana.
Jednym z atrybutów wyróżniających dane może być kolor. Z artykułu dowiesz się jak utworzyć wykres kolumnowy, który automatycznie „pokoloruje” wartość minimalną i maksymalną.
Dlaczego takie wyróżnienie może być ważne? No cóż, podobno masz 7 sekund na zrobienie pierwszego wrażenia, a w przypadku złożonych wizualizacji, pierwsze wrażenie formułuje się w mniej niż 250 milisekund!!! Powinieneś zatem ułatwiać odbiór Twoich danych tworząc wykresy czy dashboardy.
Wykres kolumnowy
Zacznij od pobrania pliku z danymi. Na ich podstawie utworzymy wykres kolumnowy. Tego typu wykres dobrze nadaje się do porównań, rozkładów czy szeregów czasowych. Więcej o różnych typach wykresów przeczytasz w tym artykule.
Dane dotyczą liczby klientów w poszczególnych dniach tygodniach.
Stwórz wykres kolumnowy. Jak widzisz, najwięcej klientów było w środę, a najmniej w sobotę. Mógłbyś zaznaczyć pojedynczą kolumnę danych i zmienić jej kolor. Problem polega na tym, że w kolejnym tygodniu, z nowymi danymi wartość maksymalna lub minimalna może być w zupełnie innym dniu. Musiałbyś ponownie, ręcznie zmieniać kolory. A przecież w Excelu, nie chodzi o ręczną robotę! 🙂
Wykres kolumnowy z dynamicznym MIN i MAX
Jak zatem automatycznie wyróżniać wartości minimalne i maksymalne? Odpowiedzią są dodatkowe serie danych. Zadaniem jednej z nich będzie pokazywanie maksymalnej wartości. Natomiast druga ma wyświetlać tylko minimalne.
Użyjemy funkcji MAX, MIN i JEŻELI, żeby pokazać na dodatkowych seriach właściwe wartości. Dodatkowo, trzeba użyć funkcji BRAK co spowoduje, że wartości zerowe / puste nie będą widoczne na wykresie.
=JEŻELI(C$8=MIN($C$8:$I$8);C$8;BRAK())
=JEŻELI(C$8=MAX($C$8:$I$8);C$8;BRAK())
Mając przygotowane dane pod dwie dodatkowe serie, po prostu dodajemy je na wykres, na oś główną. Kolejnym krokiem jest zmiana w OPCJACH SERII wartości NAKŁADANIE SERII na 100%. To spowoduje, że zarówno podstawowa seria, jak i serie MIN i MAX „nałożą się” na siebie. Dodatkowo możesz zmienić SZEROKOŚĆ PRZERWY, aby dostosować szerokość kolumn.
Następnym krokiem jest zmiana koloru serii z minimalną i maksymalną wartością. Zaznacz dowolną kolumnę, a następnie kursorem (strzałka w górę lub w dół) możesz przeskakiwać pomiędzy kolejnymi elementami wykresu. W momencie kiedy będziesz miał aktywną właściwą serię – zmień kolor.
Wykres kolumnowy – czy można jeszcze inaczej?
Czy możemy jeszcze jakoś poprawić nasz wykres? Na pewno warto pozbyć się z wykresu elementów nieistotnych. Prościej znaczy lepiej. Ale czy można wzbogacić wizualną stronę wykresu, nie zaśmiecając go i nie zmieniając podstawowego przekazu?
Pokażę Ci sposób, który jakiś czas temu zobaczyłem u Leily Gharani. Polecam Ci jej kanał na You Tube.
Idea jest prosta, bo polega na przekształceniu zwykłych kolumn w kolumny z zaokrąglonymi rogami. Nie wiem dlaczego, ale kojarzą mi się ze sztachetami w płocie 🙂 Aby to zrobić, zwykły wykres kolumnowy musimy zmienić w skumulowany. Pomysł polega na tym, że w górną serię wykresu skumulowanego zostaje wklejony wcześniej przygotowany kształt. To pozwala na zaokrąglenie słupka wykresu.
Nieco trudniejsze jest odpowiednie przygotowanie danych, ponieważ łącznie mamy ich 6 serii. Dla serii danych podstawowych, maksimum oraz minimum musimy mieć po 2 serie, które reprezentują dolną i górną część słupka. Całość oczywiście musi zmieniać się dynamicznie, dlatego konieczne jest użycie odpowiednich formuł.
Na przykład, dane dla dolnej serii kolumny z wartością maksymalną powstaną w wyniku takiej formuły:
Sprawdzamy każdą komórkę danych podstawowych i jeżeli jest ona równa wartości maksymalnej z tego zakresu, to bierzemy wartość minimalną z tego zakresu i dzielimy na pół.
Dlaczego tak? Chodzi o to, aby dolna część kolumny nie była wyższa niż wartość minimalna w zakresie. W przeciwnym razie nie można byłoby uzyskać efektu zaokrąglenia. Spróbuj sam zobaczyć jak wygląda wykres, kiedy nie zrobisz dzielenia, jak również jak zmieniają się zaokrąglenia kolumn, jeżeli zrobisz dzielenie przez 3 lub 1,5.
Kolejne kroki tworzenia wykresu znajdziesz na poniższym filmie. Na końcu filmu zobaczysz co zrobić, aby pomimo ukrycia tabeli z danymi, serie danych na wykresie były nadal widoczne.
Podsumowanie
Poznałeś jedną z dziesiątek metod na wyróżnienie danych. Wykres kolumnowy jest wyjątkowo efektywny jeżeli chodzi o wizualną prezentację, tego na co chcemy zwrócić uwagę odbiorcy. Potrafi być także wyjątkowo efektowny, jeżeli włożymy w niego nieco więcej pracy.
A Ty, jakie znasz sposoby na wyróżnianie danych? A może masz ciekawe pomysły, których nie mogłeś do tej pory zrealizować? Podziel się z nami w komentarzu. Może wspólnie stworzymy coś wyjątkowego.
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:
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.
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.
<
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.
![Jak skutecznie uczyć się Excela? Kompletny przewodnik [2026]](https://staging.excelness.com/wp-content/uploads/2020/03/create-a-detailed-and-high-resolution-image-illustrating-a-data-analyst-1.png)
