Jak znaleźć polską nazwę funkcji makr 4.0 za pomocą VBA – porada #306

W poradzie 305 wspominałem o funkcjach makr 4.0. Był to dla mnie nowy temat i zaciekawił na tyle, że chciałbym móc używać tych funkcji w VBA, ale oczywiście w prosty sposób się nie da. Podobnie jak funkcji makr 4.0 nie da się wpisać bezpośrednio do komórki arkusza, tak nie można ich użyć bezpośrednio w kodzie VBA. Potrzeba użyć formuły (ExecuteExcel4Macro) wywołującej funkcje makr 4.0, czyli np. takiego kodu:

Powyższy kod w aktywną komórkę (ActiveCell) wpisuje jej odległość od lewej krawędzi Excela (GET.CELL(42))

Funkcji makr 4.0 są dziesiątki i warto przejrzeć chociaż ich listę żeby wiedzieć, kiedy mogą się przydać. Taką listę z opisem ich funkcjonalności i przykładami użycia (niestety po angielsku) możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions

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

Lista plików z katalogu za pomocą funkcji makr 4.0 – porada #305

Przed nami niezwykle trudne zadanie – potrzebujemy zrobić w Excelu listę wszystkich plików ze wskazanego katalogu. Spokojnie nie będzie, aż tak źle. Nawet nie użyjemy VBA, a przynajmniej nie zajrzymy do edytora VBA i nie będziemy tworzyć, żadnych nowych makr.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 01

Rys. 1 – Lista plików z katalogu

Technika, na wypisanie wszystkich plików ze wskazanego katalogu, jaką chcę Ci przedstawić opiera się na funkcjach makr 4.0.
Informacja o nich trafiła do mnie w przeciągu miesiąca z dwóch źródeł.
1) Ze strony http://www.myonlinetraininghub.com/, z której dostałem olbrzymi plik o funkcjach makr 4.0
2) Oraz ze strony https://trumpexcel.com/ gdzie był opisany przedstawiony problem wypisania wszystkich plików ze wskazanego folderu:

Na podstawie tego problemu chcę Ci przybliżyć sposób działania funkcji makr 4.0 i opowiedzieć o trudnościach, z nimi związanymi, jakie czekają polskiego użytkownika Excela (i innych nie angielskich narodowości).

Pierwsza trudność jest taka, że nie możesz ich wpisać bezpośrednio w komórki Excela. Może znasz funkcję DATA.RÓŻNICA, której Excel nie podpowiada, że istnieje, ale jak wpiszesz ją w komórkę Excela to ona działa. Funkcje makr 4.0 nie działają w komórkach arkusza Excela.

Żeby z nich skorzystać, chyba najprostszym sposobem, jest stworzenie na ich podstawie nazwy (Menadżer nazw karta dane).

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 02

Rys. 2 – Menadżer nazw

Przy tworzeniu tej nazwy pojawia się kolejny problem, bo ciężko znaleźć nazwę tych funkcji po polsku (ogólnie w innym języku niż angielski). Źródła, które podałem na początku podają potrzebną nam funkcję w języku angielskim – nazywa się FILES. Ale jeśli spróbujemy z niej skorzystać polski Excel jej nie rozpozna. Potrzebujemy jej nazwę po polsku. W wielu przypadkach zadziała bezpośrednie tłumaczenie – tak mi się udało znaleźć polski odpowiednik dla tej funkcji – PLIKI. O innych sposobach pomówimy na koniec wpisu. Teraz zajmiemy się rozwiązaniem postawionego problemu.

Wiemy już jak funkcja nazywa się po polsku (PLIKI), ale nie znamy jej składni. Excel nam jej nie podpowie. Na szczęście składnia tej funkcji jest prosta – wystarczy, że podasz pełną ścieżkę do katalogu, z którego chcesz uzyskać listę plików (pamiętaj na jej końcu dodać gwiazdkę, która oznacza, że bierzemy wszystko):

C:\Users\PC\Documents\Pliki\*

Ścieżkę do katalogu najlepiej, żebyś wstawił do komórki Excela, a dopiero w funkcji makr 4.0 PLIKI się do niej odwoływał. Ułatwi Ci to jej modyfikację.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 03

rys. 3 – Wstawiona ścieżka do komórki A2

Czasami może być ciężko odnaleźć dokładną ścieżkę katalogu, bo nie widać jej na pierwszy rzut oka, ale wystarczy, że w ekspoatora Windowsa i klikniesz na jego górę, gdzie widać ścieżkę w formie bardziej graficznej.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 04

rys. 4 – eksplorator windows i kopiowanie ścieżki

Wyświetli Ci się wtedy pełna ścieżka (nawet jeśli jesteś w swoich dokumentach) i będziesz mógł ją łatwo skopiować.
Mamy ścieżkę, mamy nazwę funkcji, więc możemy ją w końcu stworzyć w Menadżerze nazw (Ctrl + F3). Naciskamy przycisk nowy i przechodzimy do okna tworzenia nazwy:

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 05

rys. 5 – nowa nazwa

Jak stworzyliśmy nazwę możemy się do niej odwoływać w komórkach Excela. Ponieważ funkcja PLIKI zwraca listę, więc nie możemy jej bezpośrednio wstawić do komórki Excela, bo zobaczymy tylko pierwszą wartość (pierwszy plik) z listy. Na szczęście wystarczy skorzystać z funkcji INDEKS, której będziemy mówić, który element z listy ma wyświetlać. Najczęściej w podobnych sytuacjach korzystam z funkcji ILE.WIERSZY i dynamicznego zakresu danych, który się rozrasta jak przeciągamy formułę:

=INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4))

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 06

rys. 6 – Lista plików za pomocą funkcji

Jeśli przeciągniemy formułę wystarczająco daleko w dół zaczną się pojawiąć błędy adresu – #ADR!, ponieważ nie ma już elementów na liście. Najprościej sobie z tym poradzić wstawiając naszą formułę do funkcji JEŻELI.BŁĄD, które karzemy zwracać pusty ciąg znaków (dwa podwójne cudzysłowy), jeśli wyjdziemy poza listę.

=JEŻELI.BŁĄD(INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4));””)

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 07

rys. 7 – jeżeli.błąd

Możemy nawet policzyć ilość pliku w katalogu (w stworzonej przez nas nazwie) np. za pomocą funkcji ILE.NIEPUSTYCH:

=ILE.NIEPUSTYCH(WszystkiePliki)

Udało nam się rozwiązać postawione na początku zadanie i dochodzimy do zapisywania pliku. Pojawia się kolejny problem – funkcje makr 4.0, to jednak makra i skoroszyt je zawierający musi być zapisany w formacie, który obsługuje makra.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 08

rys. 8 – błąd przy zapisywaniu pliku z funkcjami makr 4.0

Na koniec wpisu warto wspomnieć, że przy funkcji PLIKI działają proste filtry, a dokładniej symbole wieloznaczne. Musieliśmy napisać na końcu ścieżki gwiazdkę (rys. 3), żeby pobrać wszystkie pliki. W podobny sposób możemy ograniczyć wybór plików z katalogu tylko do plików Excela ustawiając końcówkę:

C:\Users\PC\Documents\Pliki\*.xls*

Końcówka (*.xls*) oznacza, że nazwa pliku może zaczynać się od dowolnego ciągu znaków, po którym pojawia się ciąg tekstu ‘.xls’, a za nim może się jest coś pojawić, ale nie musi (symbol wieloznaczny gwiazdka oznacza dowolny ciąg tekstowy – nawet pusty). Efekty tak nałożonego filtru widać od razu w wynikach:

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 09

rys. 9 – przefiltrowane pliki

Drugim, oprócz gwiazdki, symbolem wieloznacznym w Excelu jest znak zapytania ‘?’ – zastępuje on dowolny pojedynczy znak.
Mieliśmy problem z odnalezieniem nazwy funkcji makr 4.0 po polsku, ale mamy źródła, które podają te nazwy po angielsku. Wystarczy, że zmienimy język w Excelu na angielski (Menu Plik -> Opcje -> zakładka Język), zapiszemy plik z angielską nazwą pliku, a następnie zmienimy język Excela na polski i ponownie otworzymy plik – teraz już z polską nazwą funkcji.
Dodanie nowego języka do Excela różni się w zależności od wersji Excela, którą masz, dlatego podaję link do strony Microsoftu dokładnie opisujący proces w zależności od Twojej wersji Excela.

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

Ile grup jest powyżej progu – porada #304

Dzisiaj chce porozmawiać o problemie zliczania grup powyżej progu. Żeby lepiej zobrazować problem pokaże dane sformatowane warunkowo (komórki wypełnione na żółto) i ich prezentację na wykresie z linią progu.

Porada 304 - Ile grup powyżej progu - 01

Najlepiej można zobaczyć grupy (góry), które wyrastają ponad próg na wykresie. Łatwo jest je też policzyć – jest ich 4. Ale nie chcemy tego liczyć ręcznie za każdym razem, gdy dane się zmienią, albo gdy mamy dużo więcej danych niż w przykładzie. Co chcemy zrobić oczywiście wykorzystać formułę Excela, ale najpierw wyjaśnijmy jak sobie możemy wykryć grupę (górę).

Są 2 możliwości, albo patrzymy na zbocze wznoszące, albo opadające, czyli albo sprawdzamy, że wartość jest mniejsza od progu, a kolejna większa lub równa progowi (znak równości może być przy pierwszym lub drugim punkcie, który sprawdzamy w zależności, czy próg uznajemy już za naszą ‘górkę’ czy jeszcze nie), albo pierwsza wartość większa lub równa progowi, a kolejna mniejsza od progu. W tym przykładzie będziemy szukać zboczy wznoszących.

Wiemy co chcemy znaleźć, ale jak to znaleźć w Excelu za pomocą formuły? Mamy operacje porównania jako warunki i chcemy je zliczać, więc świetnie do tego nadaje się funkcja LICZ.WARUNKI. Wystarczy, że sprawdzimy, czy nasze dane, od pierwszej do przedostatniej wartości, są mniejsze od progu, a następnie sprawdzimy, czy dane od drugiej do ostatniej wartości są większe bądź równe progowi:

=LICZ.WARUNKI(A2:A14;”<"&E1;A3:A15;">=”&E1)

Porada 304 - Ile grup powyżej progu - 02

Czyli sprawdzamy obszary, które w stosunku do siebie są przesunięte o 1 komórkę.

Niestety wynik jaki otrzymujemy jest błędny – brakuje nam jednej ‘górki’. Taka sytuacja występuje wtedy kiedy nasze dane zaczynają się już od wartości większej (lub równej) progowi. Na szczęście bardzo łatwo skorygować naszą formułę. Wystarczy, że dodamy warunek sprawdzający, czy pierwsza dane jest większa równa od wyznaczonego progu. Musimy pamiętać o kolejności działań i dodatkowe porównanie zapisać w nawiasach (dodatkowe obliczenie poza funkcją LICZ.WARUNKI):

=LICZ.WARUNKI($A$2:$A$14;”<"&$E$1;$A$3:$A$15;">=”&E1)+(A2>=$E$1)

Porada 304 - Ile grup powyżej progu - 03

Operacja porównania (A2>=$E$1) daje w wyniku wartość PRAWDA i FAŁSZ, które w wyniku dodawania są konwertowane odpowiednio na 1 i 0, czyli jeśli pierwsza wartość w danych przekracza wartość progu, to mamy jedną górkę ekstra i dodajemy ją do wyniku funkcji LICZ.WARUNKI, ale jeśli nie przekracza progu to nic nie dodajemy – nie ma górki na początku danych.

Odpowiednio wizualizując sobie problem udało nam się znaleźć proste rozwiązanie w Excelu 🙂

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

Wykres lejkowy w komórkach arkusza – porada #303

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.

WykresLejkowyWKomórkach 01

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

WykresLejkowyWKomórkach 02

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.

WykresLejkowyWKomórkach 03

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

WykresLejkowyWKomórkach 04

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

WykresLejkowyWKomórkach 05
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ł:

=JEŻELI.BŁĄD(INDEKS($B$4:$B$8;A1-4)-INDEKS($B$4:$B$8;A1-3);0)

Formuła do komórki B2:

=JEŻELI.BŁĄD(B4-INDEKS($B$4:$B$8;A1-3);0)

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

WykresLejkowyWKomórkach 06

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

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