Tym razem będziemy nagrywać makro, którego zadaniem będzie transponowanie danych, które zostały zaimportowane w małych blokach w jednej kolumnie. Na raz chcemy transponować pojedynczy blok danych.
Musimy pamiętać, żeby nasze makro było na tyle uniwersalne, żeby uwzględniało różną długość bloków danych (ilość wierszy danych w jednym bloku). Najważniejsze jednak w tym przykładzie jest zastanowienie się nad tym, gdzie nasze makro powinno się zaczynać, a gdzie kończyć, żebyśmy je mogli wykonywać cyklicznie. Czyli zaraz po skończeniu makra chcemy je uruchomić ponownie, by transponować kolejny blok.
Zwróć tu uwagę, że w niektórych makrach musisz założyć, że kod sam powinien przejść do konkretnej komórki, ale w tych najprostszych – pisanych dla siebie, możesz założyć, że użytkownik (TY ;)) zaznaczy poprawnie pierwszą komórkę. W naszej sytuacji powinien po być pierwszy numer ID, a żeby nasze makro można było uruchamiać wielokrotnie po sobie, powinniśmy skończyć na kolejnym numerze ID.
Przy tym makrze praktycznie cały czas możemy mieć włączone odwołania względne w makrach, bo gdy będziemy korzystać ze skrótów klawiszowych nie będzie to miało znaczenia, ponieważ w większości sytuacji, skrót klawiszowy uruchamia konkretny mechanizm – zapisuje się jako konkretna „funkcja” w VBA.
Zacznijmy w końcu nagrywać nasze makro. Nazwijmy je sobie Transponowanie i przypiszmy do niego skrót klawiszowy Ctrl + Shift + T, żebyśmy mogli je szybko uruchamiać.
Teraz możemy przejść przez kolejne kroki naszego makra. Ponieważ rozpoczynamy od numeru ID, to żeby zaznaczyć cały blok danych wystarczy, że skorzystamy ze skrótu klawiszowego Ctrl + Shift + strzałka w dół.
Teraz wystarczy go skopiować (Ctrl + C) i przejść do komórki nad numerem ID. Ponieważ zaczynaliśmy od numeru ID, to jego komórka jest aktywną, więc możemy albo nacisnąć strzałkę w górę, albo od razu kliknąć na komórkę powyżej prawym przyciskiem myszy (przypominam, że mają być włączone odwołania względne).
Jeśli klikniemy prawym przyciskiem myszy, to od Excela 2010, z podręcznego menu możemy wybrać opcję transponowania.
W Excelu 2007 najbezpieczniejszą drogą jest zaznaczenie komórki nad numerem ID, a potem rozwinięcie polecenia Wklej na karcie Narzędzia dane i wybranie z listy opcji Transpozycji.
Po wklejeniu przetransponowanych danych, będziemy musieli usunąć wklejony blok danych.
Ponieważ będzie teraz aktywna komórka nad ID, to wystarczy strzałka w dół lub kliknięcie na komórkę poniżej myszką (edytor VBA zarejestruje te operacje tak samo). Następnie znów skrót Ctrl + Shift + strzałka w dół, by zaznaczyć cały blok danych. Teraz będziemy chcieli go usunąć. Możesz albo skorzystać ze skrótu klawiszowego Ctrl + - (Ctrl i minus), albo kliknąć prawym przyciskiem myszy na zaznaczenie i z podręcznego menu wybrać polecenie usuń.
Pojawi się okno usuwanie, gdzie chcemy się upewnić, że jest zaznaczona opcja: Przesuń komórki do góry.
Po usunięciu danych będzie aktywna komórka o jeden wiersz wyżej niż chcieliśmy, dlatego musimy jeszcze nacisnąć strzałkę w dół i możemy zatrzymać rejestrowanie naszego makra.
Teraz chcemy sprawdzić, czy zarejestrowane przez nas makro działa, czyli naciskamy skrót klawiszowy, który do niego przypisaliśmy Ctrl + Shift + T.
Dane powinny się odpowiednio transponować. Przytrzymaj teraz tą kombinację klawiszy, aż pokaże się komunikat o błędzie run-time error ‘1004’.
Komunikat ten daje nam możliwość zakończenia makra (przycisk End), albo przejścia do linijki kodu, która spowodowała błąd (przycisk Debug).
Przejdźmy sobie do debugowania kodu. Linijka podświetlona na żółto, to ta wywołująca błąd.
Po słowie PasteSpecial można się zorientować, że wklejamy tutaj dane, ale dlaczego nasze makro, które działało poprawnie, aż do tego momentu, teraz powoduje błąd?
Problemu nie rozwiążemy patrząc na kod VBA, bo tutaj się nic nie zmienia, ale jeśli spojrzysz na arkusz Excela, to zobaczyć, że do kopiowania został zaznaczony bardzo duży zakres pustych komórek. Tak zadziałała skrót klawiszowy Ctrl + Shift + strzałka w dół, kiedy zaczynasz od pustej komórki i nie ma żadnych danych pod spodem.
Czyli kod VBA chce teraz transponować ponad milion wierszy. To jest nie możliwe, bo kolumn jest znacznie mniej. Dlatego też pojawia się błąd przy uruchomieniu kodu, bo dopiero pewna kombinacja danych, czy też ich braku, powoduje zaistnienie takiej sytuacji, a w większości sytuacji ten kod zadziała poprawnie.
Ponieważ zaczynamy dopiero naukę makr, nie będziemy tutaj próbować obsługiwać tego błędu, po prostu zakończmy makro, bo przecież transponowaliśmy już wszystkie bloki danych, czyli wykonaliśmy naszą pracę 🙂
Zapamiętaj sobie w tym momencie jedno:
Najważniejsze, żeby Twój kod działał! Nie musi być idealny!
Czasem nawet może powodować błąd jeśli TY go obsługujesz, bo szkoda tracić czasu na jego dopieszczanie. Bo jak to w życiu – kolejne zadania czekają za rogiem.
W tym przykładzie moglibyśmy dopisać pętlę i obsługę błędu, żeby makro somo się wykonywało do końca danych poprawnie, ale nie jest nam to potrzebne na tym etapie znajomości makr.
To co zrobiliśmy działa i z tego powinniśmy się cieszyć :D. Dopiero z czasem, jeśli będzie to Ci potrzebne, nauczysz się dodawać odpowiednie kawałki kodu, a w następnym odcinku zobaczysz dokładny opis kodu.
Potrzeba powiedzieć kilka rzeczy o wykresach liniowych i wykresach punktowych, czym od siebie się różnią i kiedy powinieneś stosować wykres liniowy, a kiedy punktowy.
Jako przykład posłuży nam sytuacja, gdzie chcemy przedstawić zależność 3 parametrów od jednej zmiennej (częstotliwości). Dodatkowo będziemy chcieli jeszcze dodać jeszcze 1 parametr do wykresu, którego parametry były wyliczony dla innych wartości zmiennej (częstotliwości).
Jeśli na podstawie pierwszej tabeli wstawisz wykres liniowy, to będą na nim 4 serie – częstotliwość zostanie dodana jako czwarta fioletowa seria.
Musisz ją zaznaczyć i usunąć. Wystarczy, że naciśniesz klawisz Delete po jej zaznaczeniu.
Ponieważ chcesz, żeby wartości częstotliwości były pokazane na osi poziomej, to musisz wybrać polecenia Zaznacz Dane z karty Projektowanie, a następnie musisz edytować oś kategorii.
Później wystarczy, że zaznaczysz zakres komórek z częstotliwością:
Po zatwierdzeniu edycji osi kategorii każdy punkt zostanie odpowiednio podpisany, ale jeśli przyjrzysz się uważnie punktom, to zauważysz, że wszystkie są w tej samej odległości, mimo, że nie zawsze różnica pomiędzy poszczególnymi wartościami jest taka sama.
Czyli wykres liniowy nie nadaje się do sytuacji, gdy chcesz prezentować zależności parametrów od właściwości (y od x). Za to świetnie nadaje się, gdy odstępy czasu lub liczb są identyczne, albo to tekstowe pozycje z listy.
Czyli do tego przykładu powinniśmy wykorzystać wykres punktowy (dokładnie punktowy z prostymi liniami i znacznikami), tylko Excel może niepoprawnie rozmieścić dane na wykresie.
Wynika to z tego, że Excel przy wstawianiu wykresu punktowego pierwszą kolumnę interpretuje jako oś poziomą (x), a pozostałe kolumny wkłada jako serie na oś pionową (y).
Dlatego w tym przykładzie musimy zaznaczyć kolumnę z częstotliwością, złapać z krawędź jej zaznaczenia i przytrzymując klawisz Shift odpowiednio ją przesuną w lewo. Po przesunięciu Excel poprawnie zinterpretuje dane i będziesz mógł zauważyć, że niektóre punkty znajdują się bliżej siebie.
Przydałoby się jeszcze pewnie zmniejszyć maksimum na osi poziomej (x) do 10, ale ważniejsze jest dla nas dodanie nowej serii danych. Ma ona całkiem inne punkty i jest ich mniej niż dla wcześniejszych trzech parametrów. Niemożliwe byłoby ich poprawne dodanie do wykresu liniowego, ale do punktowego to całkiem prosta sprawa. Wystarczy, że ponownie klikniesz w polecenie Zaznacz dane z karty Projektowanie narzędzi wykresów, tylko tym razem wybierzesz przycisk dodaj Serię:
Ponieważ jest to wykres punktowy, więc będą aż trzy pola do wypełnienia – nazwa serii, wartości x i y.
Teraz wystarczy zatwierdzić wpisane dane i już masz poprawny wykres z dodanym czwartym parametrem (fioletowym). Możesz zobaczyć, że nie dochodzi ona aż tak daleko jak poprzednie trzy, bo nie była mierzona dla tych wartości częstotliwości.
Dostałeś polecenie podziału kolumny na wiersze. Ale o co dokładnie chodzi?
W przykładowych danych możesz zobaczyć w pierwszej kolumnie imię Pracownika, a w drugiej, rozdzielone przecinkami, ID Klientów, z którymi współpracuje/których obsługuje.
Dane takie są bardzo trudne do analizy, dlatego potrzeba je podzielić tak jak to widać w tabeli obok, czyli dla każdego numeru ID Klienta ma być osobny wiersz i przypisany mu odpowiedni pracownik.
Najprościej to zrobić za pomocą PowerQuery. Najpierw załaduj dane z tabeli do zapytania PowerQuery (polecenie z tabeli).
Kolejnym krokiem, już w zapytaniu PowerQuery, będzie podział kolumny ID Klienta po przecinku.
Tu musisz wybrać ogranicznik, czyli przecinek i w tym przykładzie podział ma nastąpić przy każdym jego wystąpieniu.
Gdy już dokonałeś podziału na kolumny, nie ma tu znaczenia, że część danych jest interpretowana jako liczby, a część jako tekst, to możesz podzielić kolumny na wiersze, czyli klikasz prawym przyciskiem myszy na nazwę pierwszej kolumny i z podręcznego menu wybierasz polecenie Anuluj przestawienie innych kolumn.
Teraz jesteś już na finiszu – wystarczy usunąć zbędną kolumnę.
Zmienić nazwę drugiej kolumny na ID Klienta i jeszcze ewentualnie posortować dane. Pamiętaj, że w PowerQuery kolejność sortowania jest odwrotna niż w Excelu, czyli najpierw sortujesz najważniejszą kolumnę, a potem kolejne.
Po tych kosmetycznych zmianach możesz już Zamknąć i załadować zapytania do Excela:
Wybieramy opcję poniżej, żeby móc wybrać miejsce docelowe:
Czekasz chwilę, żeby dane załadowały się do Excela i już masz wykonaną całą operację.
Teraz jak zmienią się dane wystarczy, że odświeżysz zapytanie PowerQuery i odpowiednio zmieni się wynik. Uważaj tylko jeśli podepniesz więcej klientów pod jednego pracownika, niż było Twoje wcześniejsze maksimum. Zapytanie PowerQuery może ograniczyć się do podziału tylko do wcześniejszego maksimum, co zmusi Cię do usunięcia poszczególnych kroków zapytania i wykonaniu ich ponownie, ale znasz już całą ścieżkę 😉
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Twoje zadanie na dziś to przedstawić na wykresie temperatury mierzone co przykładowe 10 minut. Jak masz dane odpowiednio uporządkowane, to sprawa jest prosta.
Zaznaczasz pojedynczą komórkę w danych, a następnie wstawiasz wykres liniowy.
Niestety prezentuje się on fatalnie ze względu na dane na osi poziomej (kategorii). Wynika to z tego, że są tutaj daty z godziną w ciągu dnia, a minimalny okres czasu jaki Excel systemowo potrafi pokazywać na osi wykresu to dzień.
Dlatego musimy zmienić sposób wyświetlania na osi poziomej. Wystarczy, że w nią klikniesz (zaznaczysz ją), a następnie naciśniesz Ctrl + 1, żeby wyświetliło się okno właściwości.
Wystarczy, że zmienisz typ osi, na oś tekstu i już dane będą wyglądały znacznie lepiej.
Ewentualnie możesz jeszcze w części etykiet możesz zmienić jednostkę interwału na dwanaście, zamiast domyślnej, to wtedy będzie pokazywał się czas w odstępach dwóch godzin.
Powyższe obrazy były z Excela 2013, ale w Excelu 2010 i 2007 wygląda to podobnie tylko opcje są w ciut innych miejscach, ale też dostajesz się do nich przez zaznaczenie poziomej osi (kategorii) i naciśnięcie skrótu Ctrl + 1.
Szef zlecił Ci zadanie (może ma na imię Robert ;)), żeby wyciągnąć tekst, który się znajduje pomiędzy przedostatnim, a ostatnim backslashem (\). To zadanie wymaga trochę pracy. Żeby było łatwiejsze podzielimy je sobie na trzy części. Najpierw znajdziemy pozycję przedostatniego i ostatniego backslasha, a potem już łatwo wyciągniemy tekst pomiędzy nimi.
Żeby odnaleźć pozycję przedostatniego i ostatniego backslasha najpierw będziemy potrzebować policzyć ile ich jest. Zrobimy to podmieniając wszystkie backslashe w tekście na pusty ciąg tekstowy, czyli po prostu usuniemy je. To uzyskamy za pomocą funkcji PODSTAW:
=PODSTAW(A2;"\";"")
Kolejnym krokiem będzie obliczenie długości tekstu/ścieżki, po usunięciu backslashy, to da nam funkcja DŁ, a żeby uzyskać liczbę backslashy potrzebujemy odjąć tą długość od faktycznej długości tekstu:
=DŁ(A2)-DŁ(PODSTAW(A2;"\";""))
Założyliśmy sobie, że najpierw odszukamy pozycję przedostatniego backslasha, więc musimy tu jeszcze odjąć jedynkę. Teraz możemy, ten konkretny numer znaku zastąpić takim ciągiem tekstowym, który na pewno nie powtórzy się w przeszukiwanym tekście np.: #@, to znów będzie funkcja PODSTAW:
Teraz jak mamy już pozycję ostatniego i przedostatniego znaku to łatwo wyciągnąć to co jest pomiędzy nimi za pomocą funkcji FRAGMENT.TEKSTU. Trzeba tylko pamiętać dodać i odjąć jeden, żeby nie wyciągnąć również backslashy:
