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”.
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
Wykres schodkowy dla dat jest prosty do stworzenia (patrz porada 301). Jednak gdy na wykresie przykładowo chcemy przedstawić stan czujnika w poszczególnych godzinach, to sprawa jest trudniejsza. Przede wszystkim nie będziemy mogli skorzystać z wykresu liniowego, bo nie rozpoznaje on czasu, tak jak rozpoznaje dat. Dlatego będziemy musieli skorzystać z wykresu punktowego.
Standardowo musimy odpowiednio przygotować dane. Mamy dane źródłowe, które pokazują o której godzinie stan czujnika się zmienił i na jaki – są 3 stany –„OK”, „Czuwa” i „Błąd”.
Stanów zapisanych jako tekst nie jesteśmy w stanie przedstawić na wykresie, dlatego przypisujemy im wartości liczbowe w tabeli pomocniczej. Ważne, w późniejszym kroku, że jedna z tych wartości jest dodatnia, druga ujemna, a trzecia wynosi zero.
Za pomocą funkcji WYSZUKAJ.PIONOWO odnajdujemy odpowiednie wartości dla poszczególnych punktów.
=WYSZUKAJ.PIONOWO(B2;Tabela13;2;0)
Podobnie jak w poradzie 301 przygotowane dane kopiujemy w inne miejsce dwukrotnie pod sobą (ważne, że nasze dane są posortowane od najmniejszych czasów do największych).
Analogicznie jak wcześniej chcemy usunąć pierwszą komórkę z czasem (pomalowaną na żółto) oraz ostatnie komórki związane z wartościami (2 żółte komórki na powyższym obrazie). Oba usuwania z przesunięciem do góry. Analogicznie jak przy datach spowoduje to, że dla każdej godziny (poza pierwszą) będą dwa stany czujnika przed i po zmianie.
Po usunięciu komórek musimy dane posortować ponownie rosnąco po czasie i dopiero możemy wstawić wykres punktowy z liniami i znacznikami, żeby wyglądał jak wykres schodkowy. Zaznaczamy do niego kolumnę z czasem i wartościami (stanu nie zaznaczamy).
Ten wykres musimy mocniej sformatować, żeby lepiej się prezentował. Przede wszystkim zaczynamy monitorować stan czujnika dopiero po godzinie 6:00, a kończymy około 21:00, więc musimy przypisać odpowiednie wartości minimum i maksimum dla osi. Musimy tutaj pamiętać, że Excel przechowuje czas jako liczbę dziesiętną (część dnia, która minęła) i w opcjach osi nie możemy wpisać 6:00, bo Excel tego nie zrozumie, tylko odpowiadającą tej godzinie liczbę 0,25 (=6/24). Analogicznie 21:00 to 0,875.
Pozostaje w analogiczny sposób dopasować jednostki główne na 0,125 (3 godziny).
Musimy również dopasować jednostki na osi y, bo nasze maksimum to 1, minimum -1, a przeskok (jednostka główka to 1). Przy okazji możemy usunąć linie siatki (uważam, że utrudniają odczyt wykresu) i trochę zmniejszyć wysokość wykresu.
Wykres schodkowy dla czasu jest już gotowy, ale do pełni szczęścia brakuje nam, żeby na osi y zamiast wartości 1, 0 i -1 na wykresie były zapisane odpowiednie stany licznika. Możemy uzyskać taki efekt dzięki niestandardowemu formatowaniu liczbowemu.
Zaznaczamy kolumnę z wartościami i naciskamy Ctrl + 1, żeby otworzyć okno Formatowania komórek. Na zakładce liczby, przechodzimy do kategorii niestandardowej i wpisujemy taki kod formatowania liczbowego.
"OK";"Błąd";"Czuwa"
Zatwierdzamy formatowanie liczbowe przyciskiem OK i teraz zamiast liczb Excel wyświetla w komórkach tekst. Wystarczy jedna zwrócić uwagę na sposób wyrównania tego tekstu (do prawej), żeby wiedzieć, że Excel traktuj go jako liczb, bo faktycznie w tej komórce jest przechowywana liczba (wynik funkcji WYSZUKAJ.PIONOWO).
Trzeba powiedzieć kilka słów o niestandardowym formatowaniu liczbowym – przede wszystkim jest dzielone na sekcje za pomocąśredników. Pierwsza sekcja oznacza jak mają być formatowane liczby dodatnie. W naszym przykładzie wpisaliśmy tekst „OK”, czyli Excel zamiast każdej liczby dodatniej (w tym przykładzie plus jedynka) będzie wyświetlał słowo OK. druga sekcja odpowiada za liczby ujemne. Tutaj chcemy wyświetlać słowo „Błąd” (w tym przykładzie minus jedynka). Trzecia sekcja to sposób formatowania zera – w tym przykładzie słowo „Czuwa”. Jest jeszcze czwarta sekcja formatująca tekst, ale tutaj jest nam niepotrzebna.
W zależności od Twojej wersji Excela, wykres może zrozumieć zmianę formatowania danych (komórek) źródłowych lub nie. Jeśli zrozumiał, to na wykresie na osi y zamiast wartości liczbowych będą wyświetlały się słowa. Jeśli Excel nie zrozumiał tej zmiany musisz wstawić/dodać ją ręcznie, czyli zaznaczamy oś y, naciskamy Ctrl + 1 i odszukujmy sekcję formatowania liczb. Tam wpisujemy nasz niestandardowy kod formatowania liczbowego i klikamy przycisk Dodaj.
Na koniec można jeszcze wspomnieć, że stworzony przez nas wykres nadaje się to przedstawienia stanu jednego czujnika, jeśli dołożymy dane drugiego czujnika to wykres przestanie być czytelny. Dlatego jeśli potrzebujemy monitorować więcej czujników, to żeby wynik był czytelny każdy czujnik przedstawiamy na innym wykresie. A później ustawiamy je równo pod sobą.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Dziś chcemy stworzyć w Excelu wykres schodkowy, czyli taki, który obrazuj, w którym momencie (w jaki dniu/dacie) nastąpiła zmiana parametru.
Omówimy go na prostym przykładzie stanu konta – mamy przykładowy zbiór danych, który ilustruje ile wynosiłstan konta w danym dniu, czyli 2017‐04‐04 stan konta wynosił 540zł i nie zmienił się do 2017‐04‐10, kiedy to zmienił się na 1630zł itd.
Jeśli od razu zamieścilibyśmy nasze dane na wykresie liniowym to prezentowałyby sięźle, ponieważ wyglądałoby, że stan konta zmienia się z dnia na dzień, a faktycznie tak nie jest.
Czyli zamiast wznoszącej się linii przez pierwsze 6 dni powinna być pionowa linia skoku dopiero dnia 2017‐04‐10.
Jak możemy zmienić nasze dane, żeby dobrze się prezentowały? Przede wszystkim ważne jest, żeby były posortowane od najstarszej daty do najnowszej. Następnie skopiujemy je sobie obok, żeby zachować oryginał. Kolejnym krokiem będzie ponowne skopiowanie danych, ale już bez nagłówków, tuż pod naszej tabeli.
Dodatkowo od razu wypełniliśmy dwie komórki na żółto, bo będziemy chcieli je usunąć z przesunięciem w górę.
Dlaczego je usuwamy? Bo potrzebujemy oryginalnej serii danych razem z serią, która jest przesunięta o 1 punkt.
Czyli, po przesunięciu serii/usunięciu komórek, mamy zaczynając od pierwszej daty parę:
2017‐04‐04 540 zł
Chociaż ta para nie jest już w pierwszym wierszu danych, ale ważne że kolejnym datom przypisane są dwie wartości:
2017‐04‐10 540 zł
2017‐04‐10 1 630 zł
Jedna z poprzedniej daty i druga – zmieniony stan na dany dzień.
To już wszystkie zmiany jakich potrzebujemy – zaznaczamy zakres danych i wstawiamy wykres liniowy ze znacznikami (żebyśmy widzieli położenie punktów danych).
Pozostaje nam co najwyżej dopracowanie szczegółów, jak zmiana kolorów, tytułu, wartości maksymalnych itp.
Uwaga! Czasami Excel może nie rozpoznać, że ma do czynienia z datami na osi x i musimy to sami zmieniać, czyli klikamy w oś x, naciskami Ctrl + 1 i we właściwościach osi zmieniamy ją na oś daty.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Chcemy stworzyć w Excelu wykres analogiczny jak mamy przygotowany w szablonach funkcjonalności. Przypomina on wykres koła życia, ale opiera się o doświadczenie firmy EI i nosi nazwę SPOCO Puls.
W poprzedniej poradzie omawiałem jak można analogiczny wykres stworzyć za pomocą wykresu pierścieniowego. W tym wpisie omówimy sposób wykorzystujący wykres typu SunBurst (od Excela 2016).
Jak już było wspomniane w poprzednim wpisie nad wykresem typu SunBurst mamy mniejszą kontrolę, ale tworzy się go zdecydowanie szybciej.
Zaczniemy od wyjaśnienia czym w ogóle jest wykres SunBurst. Razem wykresem mapy drzewa należy do wykresów schematów hierarchii i właśnie do pokazywania hierarchii pomiędzy wartościami w poszczególnych kategoriach i podkategoriach nadaje się najbardziej.
Stworzymy sobie szybko wykres SunBurst na podstawie danych z przykładu Microsoftu opisującego ten wykres.
Widać, że mamy 3 poziomy hierarchii w przykładowych danych. Pierwszy to Kategoria, później jest podkategoria, a na końcu jest podział tematyczny.
Przyglądając się danym można zauważyć, że nie wszystkie podkategorie mają przypisany temat. Nie jest to konieczne do prawidłowego stworzenia wykresu SunBurst i nawet pod poziomem kategorii (najwyższym) nie musimy uzupełniać hierarchii jeśli jej nie ma. Excel odpowiednio dopasuje wykres.
W zrozumieniu hierarchii powyższych danych ułatwiają puste pole w miejscach, gdy wartość w poziomie hierarchii się powtarza, albo dany poziom hierarchii nie istnieje.
W kolumnie Przychód znajduje się wartość przychodu związana z danym poziomem hierarchii (nie zawsze oznacza to najniższy poziom).
Ponieważ dane zostały zebrane w formie tabeli wystarczy zaznaczyć dowolną komórkę, a następnie wstawić wykres SunBurst (w polskim Excelu nazywa się pierścieniowy, ale działa całkiem inaczej).
Na podstawie przygotowanej hierarchii wykres SunBurst tworzy się błyskawicznie. Trzeba zaznaczyć, że hierarchie są uporządkowane malejąco od tych, które na najwyższym poziomie zgromadziły największy przychód, czyli w tym przykładzie jest to kategoria — Children's Books. Gdyby wartości były równe to wykres SunBurst sortowałby się w odwrotnej kolejności niż są wpisane hierarchie.
Poznaliśmy zasadę działania wykresu SunBurst, ale jak za jego pomocą stworzyć koło życia lub SPOCO Puls? Przede wszystkim to kwestia przygotowania danych. Wystarczą nam 4 serie plus domyślne 5 poziomów dla naszego przykładu. Przykładowo jeśli serii społeczność przypiszemy poziom 3, to chcemy, żeby wypełniła się do tego poziomu, czyli ma być wartość w kolumnie C, D i E odpowiednio 1,2,3, a kolumny F i G powinny być puste. Dopiero kolumna H zawiera wartości przypisane do danego poziomu hierarchii. Żeby zachować kolejność z danych na wykresie zaczynamy od wartości 1000 i zmniejszamy ją systematycznie o 1. Taka różnica nie powinna być widoczna na wykresie przy dużych liczbach i wszystkie serie powinny być tych samych rozmiarów.
Formuła wykorzystana do wygenerowania punktów serii:
=JEŻELI($A2>=C$1;C$1;"")
Gdy mamy gotowe dane zaznaczamy zakres B2:H5 i z karty Wstawianie wstawiamy wykres SunBurst (Systemy hierarchii -> wykres pierścieniowy). Do tego wykresu dokładamy legendę i etykiety (z zielonego plusa obok wykresu, gdy jest zaznaczony wykres) i nasz wykres wygląda w dużej mierze jak koło życia.
Przy tym typie wykresu łatwo zmienia się kolor dla serii (promienia) wystarczy kliknąć na niego dwa razy i wybrać dowolny kolor wypełnienia, obramowania itp.
Trzecie kliknięcie spowoduje zaznaczenie serii od najniższego poziomu (najbardziej z brzegu okręgu) do konkretnego punktu z serii (promienia), w który kliknęliśmy.
Po zmianie kolorów wypełnień, obramowań i ewentualnie etykiet uzyskujemy gotowy wykres, na którymwidać nasze numery poszczególnych poziomów zaczynając od nazw serii, które pojawiają się jeśli jest wystarczająco dużo miejsce na ich wyświetlenie w przepisanym im punkcie serii.
Mamy jednak problem jeśli wykres ten chcemy zbliżyć do szablonu SPOCO Puls, poniewż wykres SunBurst jest nietypowym wykresem, który został dodany do Excela 2016 i nie ma wielu funkcjonalności, które mają‘klasyczne’ wykresy np.: nie możemy jego tytułu połączyć z komórką arkusza. Nie da się też wkleić w niego obrazu, taki obraz trzeba by grupować, a takie rozwiązanie jest mniej wygodne.
Dlatego tworząc wykres SPOCO pulsu ograniczamy się raczej tylko do legendy, której kolory mogą ciut się różnic od kolorów na wykresie w zależności, od którego schemat wykresu wybraliśmy.
Istotne jest też, że wykres SunBurst jako maksymalną wartość prezentowaną uznaje najwyższy punkt w seriach jakie ma, czyli jeśli zmienimy punkty przydzielone serii ludzie z 5 na 4, to wykres będzie prezentował się odpowiednio inaczej, bo żadna seria nie ma poziomu ‘5’.
W tym przykładzie potrzebowaliśmy 5 poziomów, a ze względu na przygotowanie danych uzyskaliśmy 6 (nazwy serii i numery od 1 do 5). Jeśli bardzo by Ci zależało, żeby pominąć poziom nazw serii, to byłoby to możliwe tylko wtedy, gdyby kolejne serie na najwyższym poziomie miały naprzemiennie inne nazwy.
Ponieważ zależy nam, żeby te nazwy wyglądały jako jedynki, to pierwsza seria mogła by być„1” (samą jedynką), a druga „1 „ (jedynką ze spacją), trzecia znów tylko „1” itd. Tylko, że pojawia się problem, gdy przypiszemy 0 punktów, któreś z serii. Jeśli wszystkie etykiety poziomów hierarchii będą pustymi komórkami Excel zacznie przykładowo pokazywać 3 promienie zamiast 4, więc osobiście odradzam takie kombinowanie i pozostanie z rozwiązaniem, gdzie poziomem najwyższym (najgłębiej w okręgu) są nazwy serii. Te punkty będą zawsze widoczne na wykresie ponieważ są wpisane na stałe.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Chcemy stworzyć w Excelu wykres analogiczny jak mamy przygotowany w szablonach funkcjonalności. Przypomina on wykres koła życia, ale opiera się o doświadczenie firmy EI i nosi nazwę SPOCO Puls.
W poradzie 118 opisywałem jak można szybko stworzyć koło życia za pomocą wykresu radarowego, ale trochę więcej doświadczenia i chce się więcej, bardziej precyzyjnie i ładniej 😉
Głowiąc się nad problemem uzyskania w Excelu wykresu SPOCO Pulsu udało mi się zbliżone rezultaty osiągnąć za pomocą dwóch rodzajów wykresów:
- pierścieniowego
— sunburst (Excel 2016)
W tym wpisie omówimy pomysł korzystający z wykresu pierścieniowego. Jest on bardziej mozolny przy tworzeniu, ale jest bardziej zbliżony do efektu, który chcemy osiągnąć i umożliwia większą kontrolę nad tym co znajduje się na wykresie.
Wiemy już, że będziemy korzystali z wykresu pierścieniowego. Teraz potrzebujemy przygotować odpowiednie dane. W przykładzie omówimy uproszczony wykres, żebyśmy poznali zasadę tworzenia i nie musieli się zbytnio zmęczyć przy jego tworzeniu. Dlatego będziemy mieli tylko 4 główne serie punktowane (społeczność, oferta, ludzie, organizacja) od 0 do 5 punktów. Oprócz głównych serii potrzebujemy jeszcze serii dopełniających do maksymalnej wartości punktowej, czyli w sumie musi być to 8 serii.
Ważna jest zasada na jakiej są przypisywane wartości – w kolumnie A mamy ilośćpunktów uzyskaną w danej dziedzinie, a z prawej strony odpowiednio wypełnione wiersze jedynkami i zerami. Odpowiednio dla głównych serii wpisujemy 1 do kolumn z punktacją równą lub mniejszą od osiągniętą, a powyżej wpisujemy zera.
Przykładowo na obrazie powyżej dla społeczności wpisaliśmy 3 punkty, czyli w kolumnach C, D, E (1,2,3) wpisujemy jedynki, a dla kolumn F i G (4 i 5) wpisujemy zera. Dla serii dopełniających odwrotnie, czyli w kolumnach C, D, E wpisujemy zero, a F i G jeden. Zapewniamy to sobie dzięki odpowiednim formułą– dla serii głównej:
=JEŻELI($A2>=C$1;1;0)
a dla serii pomocniczej:
=JEŻELI($A3 mniejsze niż C$1;1;0)
Gdy mamy już przygotowane dane możemy je zaznaczyć (zakres B2:G9) i wstawić wykres pierścieniowy (pracujemy na Excelu 2016, ale we wcześniejszych wersjach poszczególne kroki są analogiczne).
Domyślnie wykres pierścieniowy ma zbyt dużą dziurę w środku i musimy to zmienić. Klikamy w dowolną serię danych i naciskamy Ctrl + 1, żeby przejść do właściwości wykresu i w opcjach osi ustawić rozmiar otworu pierścienia na np.: 15% (z założenia chcemy mieć możliwość wstawianie w ten otwór rysunku, jeśli tego nie potrzebujesz śmiało możesz ustawić 0%).
Widzimy, że kolorowane są zarówno serie główne jak i dopełniające, a nam zależy, żeby serie dopełniające były przezroczyste, a główne wypełnione wybranym przez nas kolorem. Oba typy serii mają mieć obramowanie (ciemne, żeby było widoczne). To jest kłopotliwe ponieważ seria na wykresie jest rozpoznawana jako pierścień (dane w kolumnie), a nie jak interpretowaliśmy wcześniej (jako wiersz). Z tego powodu identyczne kolorowanie poszczególnych kategorii (tak będziemy teraz odwoływać się do danych w wierszu) jest mozolne.
Zaczniemy od tego, że wszystkim serią zmienimy obramowanie na ciemne i kolor wypełnienia na brak wypełnienia.
Zacznijmy od najbardziej zewnętrznego pierścienia – zaznaczmy go klikając w niego raz, a następnie zmieniają kolor krawędzi na dowolny ciemny (np.: ciemny szary lub automatyczny).
Następnie klikamy na kolejny pierścień i naciskamy klawisz F4 – spowoduje to powtórzenie przez Excela ostatnio wykonanej czynności, czyli zmiany koloru obramowania. Tak robimy dla wszystkim pierścieni. Prawdopodobnie będzie trzeba zmienić grubość kontury, czego nie możemy wykonać jednocześnie ze zmianą koloru konturu, dlatego robimy to w kolejnej sekcji kroków (też wspierając się klawiszem F4, żebyśmy nie musieli rozwijać za każdym razem poleceń).
Analogicznie zmieniamy też kolor wypełnienia na brak wypełnienia.
Teraz nasz wykres jest brzydki/niefunkcjonalny, bo zawsze jest pusty niezależnie od wpisanych wartości, bo zmiana koloru obramowania i wypełnienia serii (pierścieni) zadziałała na wszystkie punkty.
Musimy zmienić teraz poszczególnych punktów dla kategorii głównych, żeby wykres wyglądał tak jak chcemy. Zanim do tego przejdziemy zmieniamy wartości przydzielonych punktów na maksymalną założoną (w tym przykładzie 5). Teraz klikamy na serię (pierścień) raz i ponownie na konkretny punkt w nim ponownie. Powinien zaznaczyć się tylko ten punkt, czyli w danej serii konkretna kategoria główna (bo dopełniające są niewidoczne przez zera). Możemy się tego upewnić zaglądając na kartę Formatowanie Narzędzi Wykresów.
Dalej pomagamy sobie klawiszem F4, tylko musimy uważać, żeby mieć zaznaczony tylko pojedynczy punkt, bo jak pomalujemy całą serię (pierścień) za pomocą klawisza F4, to nawet po cofnięciu będzie zawsze zamalowywał serię (pierścień), nawet jeśli zaznaczymy tylko pojedynczy punkt.
Jeśli udało nam się nadać odpowiedni kolor wszystkim punktom kategorii głównych, to możemy zmienić wartości im przypisanym i zobaczymy, że wykres prezentuje się dużo lepiej – widzi, że zapełnia się mniej lub bardziej od wpisanych wartości.
Widzimy kolory przypisane poszczególnym kategoriom w legendzie, w której nie potrzebujemy widzieć kategorii dopełniających. Możemy je łatwo usunąć klikając na konkretną kategorię dwa razy. Pierwsze kliknięcie zaznaczy całą legendę, a drugi już konkretną kategorię. Niepotrzebne wpisy w legendzie kasujemy przyciskiem Delete.
Nasz wykres nabiera kolorów, ale przydałaby się na nim jeszcze informacja na temat ilości punktów przypisanych poszczególnym pierścieniom. Można kombinować z etykietami danych, ale one wyświetlają się na środku punktu (fragmentu pierścienia kategorii) co nie odpowiada wyglądowi naszego wzorca, dlatego wykorzystamy inne rozwiązania, a mianowicie wkleimy do wykresu odpowiedni obrazek z liczbami.
Zrobimy tylko przykład dla numerowania w prawo. Czyli potrzebujemy serii danych w komórkach od 1 do 5. Przy okazji odznaczmy w karcie Widok pole wyboru Linie siatki, żeby nie pokazywały się przy kopiowaniu zakresu do obrazu.
Teraz kopiujemy komórki z naszą serią i wklejamy je jako obraz (ewentualnie jako obraz połączony – od updatu Excela na początku sierpnia 2017 roku można już wklejać obrazy połączone jako część wykresu, wcześniej wklejały się jako statyczne obrazy, ominięcie tego polegało na zgrupowaniu wykresu i dynamicznego obrazu, ale wygodniej jest mieć obraz jako część wykresu niż jako grupę)
Po wklejeniu tego obrazu kopiujemy go, zaznaczamy wykres i wklejamy skopiowany obraz do wykresu. Od teraz obraz jest częścią wykresu, czyli będzie się przesuwał z wykresem, a nawet domyślnie zmieniał rozmiar (chociaż tu ciężko zachować proporcje do obszaru wykresu i samego wykresu, żebyśmy nie musieli robić korekty obrazu po zmianie rozmiaru wykresu).
Teraz wstawiliśmy punktację na wykresie tak jak chcieliśmy. Kolejne serie punktów do wykresu możemy dodać kopiując obrazek i obracając go, albo jeśli nie pasować nam będzie orientacja liczb, kopiować inne zakresy komórek np.: pionowe.
Na koniec jeszcze standardowa sztuczka z połączeniem tytułu wykresu z wartością z komórki – zaznaczamy tytuł wykresu naciskamy klawisz równa się, następnie klikamy komórkę z tekstem, który chcemy mieć w tytule wykresu i klikamy Enter.
Nasz docelowy wykres SPOCO® Pulsu ma więcej kategorii, punktów i obrazów (oraz wordartów podpisujących kategorie zamiast legendy) w sobie, ale to po prostu więcej tej samej pracy by uzyskać efekt –wow–„To mogę stworzyć taki wykres w Excelu i zmienia się dynamicznie w zależności od wartości jakie wpiszę?” TAK!
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
W internecie znajdziesz wiele stron pozwalających wygenerować losowy PESEL. Ponieważ skupiam się na Excelu, dlatego postanowiłem zrobić taki generator w Excelu (przy okazji również generatory NIP i REGON).
Żeby wygenerować losowy PESEL musimy znać jego strukturę. Większość z nas wie, że pierwsze 6 cyfr odpowiada za datę urodzenia. Już nie wszyscy wiedzą, że numery miesiąca zmieniają się w zależności od stulecia. Nas interesuje tylko XX wiek – lata 1900–1999 oraz XXI wiek – lata 2000–2099. XX wiek nie zmienia nic w numerowaniu miesięcy, ale XXI wiek dodaje liczbę 20 do numeru miesiąca w roku. Czyli osoba urodzona w 1910‐05‐23 i 2010‐05‐23 będzie miała początek PESEL odpowiednio 100523 i 102523.
Jak to zrobić w Excelu? Przede wszystkim potrzebujemy wylosować datę urodzenia. Świetnie się tu sprawdza funkcja LOS.ZAKR. Której wpisujemy w cudzysłowach daty minimalną i maksymalną pomiędzy którymi chcemy losować:
=LOS.ZAKR("1900–01-01";"2010–01-01")
Zanim zaczniemy przekształcać odpowiednio tą datę wylosujmy jeszcze płeć naszego posiadacza PESEL (od tego zależy 10 cyfra PESEL). Posłużymy się tutaj funkcją INDEKS, której na stałe wpiszemy tablicę liter ‘k’ i ‘m’ i będziemy losować albo 1 albo 2 pozycję tablicy (funkcja LOS.ZAKR):
=INDEKS({"k";"m"};LOS.ZAKR(1;2))
Teraz zacznijmy przekształcać datę. Będziemy musieli ją podzielić na część odpowiedzialną za rok, miesiąc i dzień. Rok będzie najprostszy, bo wystarczy odpowiednio sformatować wylosowaną datę jako rok za pomocą funkcji TEKST:
=TEKST([Data];"rr")
Powyższy zapis wstawia tylko 2 ostatnie cyfry z roku. Teraz przyszła pora na miesiąc. Z nim jest trudniej – możemy wyciągnąć miesiąc z daty za pomocą funkcji MIESIĄC, ale musimy dołożyć mechanizm, który dodawałby 20 dla XXI wieku (inne stulecia poza dwoma wcześniej ustalonymi nas nie interesują). W taki razie wystarczy sprawdzić, czy ROK z daty jest większy od 1999 i jeżeli tak to zwrócić 20, a jeśli nie to 0 za pomocą funkcji JEŻELI. Potrzeba do tego dodać jeszcze numer miesiąca z daty:
JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data])
Niestety to jeszcze nie wystarczy ponieważ dla miesięcy z XX wieku mogą nam wyjść wyniki 1 cyfrowe, a my potrzebujemy mieć zawsze 2 cydry, dlatego powyższą formułę musimy wstawić do funkcji TEKST, która będzie wymuszała wiodące 0. Tak uzyskaną formułę łączymy z cyframi roku:
Teraz zajmijmy się wyznaczeniem 4 kolejnych cyfr. Powiedzieliśmy sobie, że 10 cyfra oznacza płeć. Dokładnie liczby {0;2;4;6;8} oznaczają kobietę, a {1;3;5;7;9} mężczyznę. 3 wcześniejsze cyfry to kolejność urodzenia w danym dniu, czyli wystarczy funkcja LOS.ZAKR z odpowiednimi granicami. A musimy pamiętać, że to zawsze muszą być 3 cyfry, więc dodajemy funkcję TEKST, która zapewni ewentualne wiodące zera.
=TEKST(LOS.ZAKR(1;999);"000")
Jak jednak ustalić numer odpowiedzialny za płeć.
Jak się mu przyjrzymy to możemy zobaczyć, że jeśli będziemy losować liczbę z przedziału od 0 do 4 i przemnożymy ją przez 2 to zawsze wyjdzie nam kobieta. W takim razie wystarczy, że sprawdzimy za pomocą funkcji JEŻELI, czy wylosowaliśmy wcześniej kobietę czy mężczyznę i odpowiednio zwracali 0 lub 1 i tą wartość dodali do naszego losowania.
LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]="k";0;1)
Łącząc powyższe dwie formuły mamy 4 kolejne cyfry numeru PESEL:
Teraz czeka nas najtrudniejsze zadanie – musimy obliczyć cyfrę kontrolną. Zasada jej obliczania wymaga przemnożenia każdej cyfry przez przypisaną jej wagę, a następnie ich zsumowanie. Wagi możemy zapisać w tablicy {1;3;7;9;1;3;7;9;1;3} ważne, że ta tablica ma w Excelu orientację pionową (każdy średnik oznacza nowy wiersz).
Już kilka razy wyciągaliśmy pojedyncze znaki z tekstu (np.: porada 295 i 296) z numerem PESEL jest analogicznie, a nawet prościej, bo zawsze mamy 10 cyfr (musimy je tylko połączyć z wcześniejszych obliczeń) i możemy zapisać na stałe tablicę {1;2;3;4;5;6;7;8;9;10} (ta sama orientacja co tablica z wagami, żeby mieć pewność poprawnych wyników). Czyli nasze mnożenie i sumowanie wygląda tak:
To jeszcze nie koniec naszych obliczeń, bo z takiego mnożenia i sumowania (swoją drogą dzięki wpisaniu tablic na stałe możemy zatwierdzać tą formułę normalnie, a nie jako formułę tablicową) daje wynik większy od 1 cyfry. W obliczeniach liczby kontrolnej musimy wyciągnąć resztę dzielenia przez 10 (funkcja MOD) i odjąć ją od 10. Ponieważ czasami wynik może wyjść 10, to jeszcze raz musimy go wstawić do funkcji MOD:
