Wykres schodkowy czasu – porada #302

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.

Porada 302 - Wykres schodkowy czasu 01

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

Porada 302 - Wykres schodkowy czasu 02

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.

Porada 302 - Wykres schodkowy czasu 03

Za pomocą funkcji WYSZUKAJ.PIONOWO odnajdujemy odpowiednie wartości dla poszczególnych punktów.

=WYSZUKAJ.PIONOWO(B2;Tabela13;2;0)

Porada 302 - Wykres schodkowy czasu 04

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

Porada 302 - Wykres schodkowy czasu 05

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

Porada 302 - Wykres schodkowy czasu 06

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

Porada 302 - Wykres schodkowy czasu 07

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.

Porada 302 - Wykres schodkowy czasu 08

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”

Porada 302 - Wykres schodkowy czasu 09

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

Porada 302 - Wykres schodkowy czasu 10

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.

Porada 302 - Wykres schodkowy czasu 11

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ą.
Porada 302 - Wykres schodkowy czasu 12

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wykres schodkowy daty – porada #301

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.

Porada 301 - Wykres schodkowy daty 01

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.

Porada 301 - Wykres schodkowy daty 02

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.

Porada 301 - Wykres schodkowy daty 03

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.

Porada 301 - Wykres schodkowy daty 04

Dodatkowo od razu wypełniliśmy dwie komórki na żółto, bo będziemy chcieli je usunąć z przesunięciem w górę.

Porada 301 - Wykres schodkowy daty 05
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ń.

Porada 301 - Wykres schodkowy daty 06

To już wszystkie zmiany jakich potrzebujemy – zaznaczamy zakres danych i wstawiamy wykres liniowy ze znacznikami (żebyśmy widzieli położenie punktów danych).
Porada 301 - Wykres schodkowy daty 07

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

Wykres sunburst jako ‚koło życia’ (SPOCO Puls) – porada #300

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (1)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (2)

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

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (3)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (4)

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;””)

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (5)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (6)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (7)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (8)

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (9)

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

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (10)

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

Wykres pierścieniowy jako ‚koło życia’ (SPOCO Puls) – porada #299

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 01

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 02

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

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 03

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

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 04

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

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 05

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 06

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 07

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 08

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 09

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 10

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

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 11

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

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 12

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!

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 13

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Generator PESEL NIP i REGON – porada #298

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

Porada 298 - Generator PESEL 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))

Porada 298 - Generator PESEL 02

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

Porada 298 - Generator PESEL 03

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:

=TEKST([Data];”rr”)&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);”00″)

Porada 298 - Generator PESEL 04

Potrzebujemy jeszcze dodać 2 cyfry jako dni. Robimy to analogicznie jak rok tylko wpisujemy do funkcji TEKST kod odpowiedzialny za formatowanie dni:

TEKST([Data];”dd”)

W ten sposób mamy pełną formułę budującą 6 cyfr numeru PESEL.

=TEKST([Data];”rr”)&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);”00″)&TEKST([Data];”dd”)

Porada 298 - Generator PESEL 05

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:

=TEKST(LOS.ZAKR(1;999);”000″)&LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]=”k”;0;1)

Porada 298 - Generator PESEL 06

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:

=SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3})

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:

=MOD(10-MOD(SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3});10);10)

Porada 298 - Generator PESEL 07

Uff. Mamy wszystkie elementy teraz musimy tylko połączyć je razem:

=[@DataP]&[@PłećP]&[@Kontrolna]

Porada 298 - Generator PESEL 08

W załączonym pliku znajdziesz jeszcze mechanizmy losujący numery NIP i REGON.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP