Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
Oglądając film z kanału YT Goodly o wykresie lejkowym, który formatuje się warunkowo w zależności od komórki danych, w którą klikniemy stwierdziłem, że też chcę stworzyć taki wykres, ale prościej 😉 Przede wszystkim mój lejek miał się znajdować w komórkach Excela, a nie na wykresie. Pomogły mi informacje, jakie zdobyłem tworząc kurs o wizualizacji i Dashboaradach w Excelu. Uzyskałem taki efekt.
Oczywiście powyższy wykres podświetla na czerwono wiersz, w który klikniemy. Jak uzyskać taki efekt?
Przede wszystkim musimy stworzyć poziomy „lejka” w komórkach. Robimy to za pomocą odpowiedniej formuły i zmiany czcionki.
Zaczniemy od formuły – potrzebujemy powtórzyć pewną ilość razy znak pionowej kreski "|". Robimy to za pomocą funkcji POWT.
Musimy tylko tak dopasować ilość powtórzeń, żeby każdy kolejny poziom lejka miał odpowiednią szerokość, czyli normalizujemy liczbę powtórzeń. Załóżmy, że maksymalnie chcemy powtórzyć 60 razy znak "|" – dla najwyższego poziomu. Każdy kolejny poziom ma mieć proporcjonalnie mniej znaków. Czyli wystarczy, że wartość z aktualnego poziomu podzielimy przez maksymalną wartość ze wszystkich poziomów (domyślnie przez wartość z najwyższego/pierwszego poziomu). Oczywiście musimy jeszcze przemnożyć przez 60:
=POWT("|";B4*60/MAX($B$4:$B$8))
Zwróć uwagę, że czcionka formuły wygląda nietypowo. To dlatego, że standardowa czcionka by się tutaj nie sprawdziła – byłyby za duże odstępy pomiędzy poszczególnymi pionowymi liniami. Dlatego w przykładzie wykorzystana jest czcionka Haettenschweiler (możesz m.in. skorzystać też z czcionek: Britannic Bold, Nyala, Playbill, Stencil)
Mamy paski, ale nie przypominają one lejka. Rozwiązanie jest proste – wystarczy wyrównać tekst do środka.
Zanim zaczniemy malować wiersze dołóżmy jeszcze proste formuły, które będą obliczały procentowy spadek do pierwszego i poprzedniego kroku. Są one bardzo podobne:
=1‑B5/$B$4 i =1‑B5/B4
Różnica polega tylko na tym czy w pierwszej formule zablokujemy bezwzględnie odwołanie do komórki B4 czy nie.
Teraz możemy zająć się krokami potrzebnymi do tego, żeby nasz mały Dashboard formatował się na czerwono, gdy klikniemy w komórkę danego wiersza. Niestety będziemy potrzebowali skorzystać z kodu VBA. Na szczęście to bardzo prosty fragment kodu, który musimy umieścić w module arkusza, na którym znajduje się nasz „lejek”.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub
Powyższy kod VBA uruchamia się za każdym razem, gdy zmienimy zaznaczenie na arkuszu i wstawia do komórki A1 numer wiersza aktywnej komórki.
Może Ci się wydawać, że kod ten nie działa na załączonym pliku, ale wynika to tylko z zastosowanej sztuczki – zmianie formatowania liczbowego komórki A1.
Jeśli zaznaczysz komórkę A1 i naciśniesz skrót Ctrl + 1 zobaczysz, że liczby w komórce zostały sformatowane niestandardowo tak, żeby zawsze pokazywać tekst "Lejek sprzedażowy w komórkach".
Bazując na wartości w komórce A1 tworzymy formatowanie warunkowe (na zakresie naszego lejka – A4:E8) korzystające z formuły:
=$A$1=WIERSZ()
Formuła jest bardzo prosta sprawdza czy aktualny wiersz komórki jest równy wartości przechowywanej w komórce A1 jeśli tak, to nakłada formatowanie. I już gotowe — główne zadanie zostało zrealizowane – lejek zmienia kolor.
My jednak chcemy dołożyć jeszcze parę dodatkowym informacji na naszym mini Dashboardzie. Chcemy podawać liczbę klientów straconych do pierwszego i poprzedniego kroku.
Najczęściej podobne informacje podaje się w polach tekstowych – najpierw w jakiejś odległej (niewidocznej na dashboardzie) komórce wykonuje obliczenia, a potem wstawie pole tekstowe i wpisuje się w pasku formuły, że równa się ono komórce z formułą (nie można wpisać formuły bezpośrednio do pola tekstowego).
W naszym przykładzie robimy ciut inną rzecz – w komórce D2 mamy już jedną z potrzebnych formuł:
ale dodatkowo pod nią (a niejako w niej) znajduje się pole tekstowe z tekstem. Komórce zmieniliśmy kolor czcionki na czerwoną i wyrównaliśmy tekst do góry, a w polu tekstowym zostawiliśmy domyślny kolor czcionki.
W tym przykładzie wygląda to w miarę OK, ale korzystanie z komórki ogranicza położenie wyświetlanych wartości, dlatego częściej stosuje się pole tekstowe, które odwołuje się do komórki z odpowiednią formułą/obliczeniem oraz drugiego pola tekstowego, które funkcjonuje jako podpis.
Na koniec jeszcze jedna czynność często wykorzystywana przy Dashboardach – wyłączanie linii siatki (pole wyboru na karcie Widok).
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Czasami potrzebujesz porównać ze sobą wartości "przed" i "po" dla wielu np.: produktów. Jednym ze sposobów na to jest skorzystanie z tzw. slopegraphu, czyli wykresu, którego pomysłodawcą Edward Tufte.
Ten wykres opiera się na wykresie liniowym, ale ważnym jego elementem są etykiety danych na końcach linii. Standardowe podejście tworzenia tych etykiet uwzględnia dużo ręcznej pracy, dlatego na podstawie swoje wiedzy o wykresach spróbowałem swoich sił i chce zaproponować inne podejście. Do tego potrzebujemy stworzyć kolumny pomocnicze, które będą zawierały tylko wartość 1 lub 2 oraz jeszcze jedną kolumnę pomocniczą, która będzie przechowywać połączone wartości, które chcemy wyświetlać w etykietach danych.
=KWOTA(C3;0)&" "&A3
Korzystamy tu z funkcji KWOTA, żeby liczbę przedstawić jako walutę w złotówkach z dokładnością do zera miejsc po przecinku.
Slopegraph opiera się o wykres liniowy, więc od niego zaczniemy – zaznaczymy zakres komórek od A2 do C11 i z karty Wstawianie wstawimy wykres liniowy.
Tylko nie jest to wykres liniowy jakiego potrzebujemy, bo są na nim tylko 2 serie, a my potrzebujemy serii dla każdego produktu. Dlatego musimy kliknąć w polecenie Przełącz wiersz/kolumnę z karty Projektowanie narzędzi wykresów, żeby zamienić miejscami serie i kategorie.
Uzyskujemy już wykres, który bardzo przypomina slopegraph, ale musimy go jeszcze oczyścić z wszystkich niepotrzebnych rzeczy. Nie są nam potrzebne:
• Legenda
• Osie siatki
• Etykiety osi y
Dlatego wszystkie je możemy usunąć zaznaczając je i naciskając klawisz Delete.
Warto tu wspomnieć o pewnej sztuczce, żeby wartości 2014 i 2017 zostały użyte jako etykiety na osi x, a nie zostały dołożone do serii danych – przed nimi wstawiamy pojedynczy cudzysłów ' dzięki czemu Excel traktuje te liczby jako tekst i nie dodaje je do serii danych.
Slopegraph powinien być wyższy dlatego klikamy na obszar kreślenia i odpowiednio go poszerzamy.
Teraz potrzebujemy dodać jeszcze jedną serię, której zaraz będziemy musieli zmienić typ wykresu, więc nie ma dużego znaczenia jakie będzie miała ona wartości, żeby tylko nie została podpięta pod którąś z istniejących serii. Jednym z prostszych sposobów na to jest skopiowanie komórki A2 (Ctrl + C) z tekstem, zaznaczenie wykresu i wklejenie skopiowanego obszaru (Ctrl + V). Nie powinno być widać żadnych widocznych zmian na wykresie, ale jeśli skorzystasz z polecenia Zaznacz dane z karty Projektowanie to powinieneś zobaczyć, że dodała się nowa seria (Seria 10) do serii wykresu.
Ta seria posłuży nam do dodawania etykiet danych, ale musimy jej zmienić typ wykresu na punktowy. Czyli najpierw musimy ją zaznaczyć. Najprościej to zrobić korzystając z listy rozwijanej na karcie Formatowanie narzędzi wykresów.
Teraz możemy skorzystać z polecenie Zmień typ wykresu z karty Projektowanie narzędzi wykresów.
Dużo prościej wykonuje się to od wersji Excela 2013, bo istnieją wtedy wykresy kombi, gdzie możemy łatwo kontrolować, jakiego rodzaju wykresami są poszczególne serie.
Też musimy upewnić się, żeby nasza seria nie trafiła na oś pomocniczą.
Teraz jak zmieniliśmy typ wykresu dla serii możemy w końcu wskazać dla niej prawidłowe dane, czyli wchodzimy w polecenie Zaznacz dane z karty Projektowanie, a następnie odszukujemy naszą serię i ją Edytujemy i zaznaczamy odpowiednie zakresy danych i zatwierdzamy ją. Kolumna z 1 musi trafić na oś x. Wtedy nasza seria będzie "zaznaczać" wszystkie punkty serii z lewej strony, czyli pierwszej punkty.
Nawet jeśli nie korzystamy z wykresu punkowego to każda wartość wykresu ma swoją określoną pozycję na osi y i osi x. Właśnie z tej właściwości korzystamy, żeby móc szybciej dodawać inne etykiety z lewej i prawej strony serii.
Zaraz po dodaniu serii z lewej strony możemy od razu dodać serię z prawej strony, ponieważ Excel domyślne skorzysta z poprzednio dodanego typu wykresu, czyli punktowego, którego potrzebujemy, żeby oznaczyć końce serii z prawej strony (dane z kolumny C – oś y i kolumny E – oś x).
Jak mamy dodane punkty z lewej i prawej strony możemy im dodać etykiety danych (na razie zostawimy punkty, ale na koniec sprawimy, żeby były niewidoczne).
W etykietach danych chcemy mieć dwie wartości – nazwę serii odpowiadającą konkretnemu punktowi (nie jest to nazwa serii punktów) i wartość liczbową dla punktu. Od Excela 2013 jest to dużo prostsze, bo możemy w etykietach danych umieszczać dane z komórek – wystarczy, że zaznaczymy na etykiety danych, naciśniemy Ctrl + 1, żeby przejść do właściwości etykiet i w zakładce Opcje etykiet zaznaczyć checkbox Wartości z komórek. Wtedy otworzy się okno z polem, w którym możesz zaznaczyć zakres komórek, z którego będą pobierane wartości do etykiet osi.
Przed Excelem 2013 musiałbyś dla każdego punktu (etykiety) tworzyć połączoną wartość, która ma się wyświetlać w etykiecie i żeby tą połączoną wartość pobrać do konkretnej etykiety musiałbyś ją zaznaczyć, nacisnąć równa się i kliknąć na komórkę, której wartość chcesz, żeby pojawiła się w etykiecie, a na koniec zatwierdzić swój wybór Enterem. Dlatego nie miałoby sensu tworzenie tych dodatkowych serii tylko od razu dodawać i modyfikować etykiety dla pojedynczych linii.
Po dodaniu etykiet musimy jeszcze ewentualnie poszerzyć wykres i poprzesuwać trochę etykiety, żeby nie nachodziły na siebie. Od Excela 2013 możemy zmieniać rozmiar pojedynczej etykiety danych, a wcześniej jest ona mocno powiązana z rozmiarem wykresu i obszaru kreślenia.
Podobnie postępujemy z serią punktów po prawej stronie, czyli też dodajemy do nich etykiety, tylko przy nich potrzebujemy pobrać z Wartości komórek z kolumny F, ponieważ chcemy żeby liczba wyświetlała się bliżej linii.
Teraz możemy ukryć punkty, czyli zaznaczamy serię punktów, naciskamy Ctrl + 1 i we właściwościach, na zakładce Opcje znaczników, zaznaczamy pole wyboru Brak.
Na koniec jeszcze możemy uzupełnić nazwę naszego wykresu, a dokładniej, żeby była ona połączona z komórką A1, czyli zaznaczamy tytuł, piszemy znak równa się, klikamy komórkę A1 i zatwierdzamy nasz wybór Enterem. Uzyskujemy tytuł powiązany z wartością komórki. Podobnie jak wcześniej tworzyliśmy etykiety danych.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Jeśli chcesz pokazać wartości na osi wykresu w tysiącach lub milionach potrzebujesz zmienić odpowiednią opcję we właściwościach osi wykresu.
Najpierw zaznacz oś , a następnie upewnij się, że jesteś na zakładce Opcje osi. Następnie musisz odnaleźć listę rozwijaną Jednostki wyświetlania i wybrać z niej jak chcesz zmodyfikować sposób wyświetlania liczb na osi, czyli czy np: chcesz je pokazać w tysiącach czy milionach.
Możesz ewentualnie odznaczyć pole wyboru (checkbox) Pokaż jednostki wyświetlania na wykresie, jeśli nie chcesz, żeby obok osi pokazywała się informacja, że pokazywane liczby to miliony, jeśli zmodyfikowałeś inaczej ich formatowanie.
Potrzebujesz wstawić punkty na wykresie liniowym w pionowych liniach, ponieważ odpowiadają one rozproszeniu danego elementu, czyli z założenia chcesz mieć kilka takich elementów, czyli serii, na wykresie.
Najważniejszym krokiem w tym jest odpowiednie przygotowanie danych. Przy liniach pionowy, x musi być takie samo, a y musi się zmieniać:
Ponieważ Excel słabo sobie radzi z interpretacją takich danych do wykresu punktowego:
To stworzymy go sobie na pustej komórce – nie będzie miał wtedy, żadnych danych. Będziemy musieli je dodać ręcznie. Wystarczy, że odnajdziesz polecenie Zaznacz dane na karcie Projektowania Narzędzi Wykresów.
Następnie w oknie, które się otworzy musisz dodać serię danych:
I podać tytuł Serii, zakres punktów x i y
Dodajesz w ten sposób tyle serii ile potrzebujesz i zatwierdzasz je przyciskiem OK. Ewentualnie pozostaje dodanie legendy i inne modyfikacje wykresu, żeby wyglądał tak jak potrzebujesz.
Jeśli chcesz, żeby na jedną serię składały się dwie (lub więcej) pionowych linii, to potrzebujesz odpowiednio zmodyfikować dane:
Jeśli potrzebujesz stworzyć wykres liniowy w Excelu, to przede wszystkim potrzebujesz przygotować dane. Przykładowo jeśli chcesz przedstawić na wykresie dane sprzedażowe dwóch pracowników przez okres roku (po miesiącach). To wystarczy, że w pierwszej kolumnie wpiszesz poszczególne miesiące, a w dwóch pozostałych wartości sprzedaży. Warto też zadbać o nagłówki danych:
Teraz potrzebujesz zaznaczyć całość danych i z karty Wstawianie wybrać odpowiedni rodzaj wykresu (załóżmy, że chcemy wstawić wykres liniowy ze znacznikami). Ponieważ od Excel 2013 zaszło trochę zmian jeśli chodzi o operacje z wykresami pod spodem zobaczysz obrazy dla Excela 2013 i 2010.
Excel 2013 i nowszy:
Excel 2010 i wcześniejszy:
W ten prosty sposób wstawiłeś wykres liniowy ze znacznikami. Jeśli chciałbyś go zmienić na inny, to gdy jest zaznaczony wystarczy, że wybierzesz inny z dostępnych na karcie Wstawianie wykresów.
W Excelu 2013 lub wyższym powinieneś już mieć na wykresie jego tytuł, ale jeśli by Ci się nie pojawił to musisz rozwinąć zielony plus, który znajduje się obok wykresu (tam znajdują się elementy wykresu, które wcześniej były na karcie Układ).
Przed Excelem 2013 będziesz musiał dodać tytuł z karty Układ:
Zmiana tytułu już przebiega analogicznie – wystarczy, że zaznaczysz tytuł wykresu i zaczniesz pisać tekst jaki chcesz, żeby się pojawił jako tytuł wykresu. Zmiany nie zobaczysz od razu na wykresie. To co wpisujesz będziesz widział w pasku formuły, a na wykresie pojawi się dopiero po zatwierdzeniu Enterem.
Jeśli chcesz zmienić tylko fragment tytułu to wystarczy, że po tym jak zaznaczysz tytuł wykresu klikniesz w niego jeszcze raz. Będziesz wtedy mógł edytować tekst podobnie jak zawartość komórki.
