Blog o Excelu i VBA
Tabela przestawna to świetne narzędzie Excela pozwalające na szybką i sprawną agregację i analizę danych. Ma to znaczenie szczególnie wtedy, gdy danych jest dużo.
Do pokazania kolejnych kroków tworzenia tabeli przestawnej użyłam pobranego ze strony PKW pliku kandydatów do sejmu w wyborach 2019. Na wszelki wypadek (RODO) usunęłam nazwiska i imiona, zastępując je inicjałami.
A tu filmik
Tradycyjnie już przypominam, że czas w Excelu to liczba. Wszystko inne to tylko ozdobniki sposobu wyświetlania. Jeżeli komórki wyświetlające czas mają być używane do dalszych obliczeń – muszą być prawidłowo zapisane.
Choć oczywiście patrząc na dane np. w grafiku – zapisanie pracy w godzinach 0,3333333… do 0,66666667.. jest jednak mało przyjazne. Zdecydowanie trzeba to sformatować.
Oprócz dostępnych standardowych formatów czasu, można oczywiście sformatować komórkę niestandardowo, dopasowując wygląd do własnych potrzeb.
Kody niestandardowego formatowania czasu to:
| g |
wyświetla godzinę |
| gg |
wyświetla godzinę z zerami uzupełniającymi |
| m | wyświetla minuty |
| mm |
wyświetla minuty z zerami uzupełniającymi |
| s |
wyświetla sekundy |
| ss |
wyświetla sekundy z zerami uzupełniającymi |
| [] | wyświetla liczbę godzin większą niż 24 oraz liczbę minut i sekund większą niż 60 |
| AM lub PM |
wyświetla liczbę godzin w formie zegara 12-godzinnego |
W praktyce wygląda to tak:
| Liczba | Wyświetlanie | Kod formatowania niestandardowego |
| 0,333333 | 8:00 | g:mm |
| 0,333333 | 08:00 | gg:mm |
| 1,25 | 30:00 | [gg]:mm |
| 0,6 | 02:24 PM | gg:mm A/P”M” |
| 0,333333 | 08:00 AM | gg:mm A/P”M” |
Na wszelki wypadek przypominam, że data w Excelu to liczba – to ważne. Z doświadczenia wiem, że kreatywność użytkowników w tym zakresie bywa niczym nie ograniczona i pojawiają się problemy, gdyż formuły nie działają prawidłowo, trudno zagregować dane czy nawet je posortować. Warto więc przypilnować, aby w komórkach była data prawidłowo zapisana, a wszystkie ewentualne ozdobniki czy nietypowe zapisy pokazać za pomocą formatowania komórek (czyli zmiany sposobu wyświetlania).
Kody niestandardowego formatowania daty to:
| m | wyświetla miesiąc jako liczbę |
| mm | wyświetla miesiąc jako liczbę dwucyfrową (czyli dla numeru miesiąca mniejszego od 10 z dodaniem zera na początku) |
| mmm | wyświetla skrót nazwy miesiąca |
| mmmm | wyświetla pełną nazwę miesiąca |
| mmmmm | wyświetla pierwszą literę nazwy miesiąca |
| d |
wyświetla dzień jako liczbę |
| dd |
wyświetla dzień jako liczbę z zerem uzupełniającym |
| ddd |
wyświetla skrót nazwy dnia tygodnia |
| dddd |
wyświetla pełną nazwę dnia tygodnia |
| rr |
wyświetla rok w postaci 2 cyfr |
| rrrr | wyświetla rok w postaci 4 cyfr |
W praktyce wygląda to na przykład tak (dla daty dzisiejszej czyli 2019-09-09.
| kod | Wynik |
| dddd, d mmm rrrr | poniedziałek, 9 wrz 2019 |
| mmm, ddd | wrz, pon |
| dd”\”mm”\”rrrrr |
09\09\2019 |
Warto pamiętać też, że wprawdzie data to liczba, ale liczb ujemnych nie można sformatować w takim f0rmacie daty. Poza tym – wprowadzona data nie może być wcześniejsza niż 1 stycznia 1900r.
Przy definiowaniu niestandardowego formatowania liczb stosowane są następujące kody:
| # | Symbol zastępczy cyfry. Nie wyświetla nieznaczących zer. |
| 0 | Symbol zastępczy cyfry. Wyświetla również zera nieznaczące. |
| ? | Symbol zastępczy cyfry. Dodaje odstępy dla cyfr nieznaczących, aby uzyskać efekt wyrównanie przecinka dziesiętnego. |
| e- e+ E- E+ | format naukowy |
| \ | wyświetlany jest następny znak z kodu |
| * | powtarzany jest następny znak |
| _ | odstęp |
| “ciąg znaków” | wyświetlany jest ciąg znaków zawarty w cudzysłowie |
| @ | symbol zastępczy tekstu |
| [Czerwony] | znaki wyświetlane są w kolorze czerwonym (dostępne inne kolory to: czarny, niebieski, błękitny, zielony, amarantowy, biały, żółty) |
| [Kolor n] | znaki wyświetlane są w kolorze n, będącym liczbą całkowitą od 0 do 56, oznaczającą numer koloru z palety |
A jak to wygląda w praktyce? Na przykład tak:
| Liczba | Wyświet-lanie | Kod formatowania niestandardowego |
| 123 | PLN 123,00 | “PLN”_# ###,00 |
| 123 | 1,23 E+02 | 0,00\ E+00 |
| 123000 | 123 tys. | # ### ” tys.” (# ###spacja” tys.”) |
| 12000000 | 12 mln | # ### ” mln” (# ###spacja spacja” mln”) |
| 12000000 | 12,0 mln | # ###,0 ” mln” (# ###,0 spacja spacja” mln”) |
Można spróbować dopasować formatowanie komórek do własnych potrzeb. Warto pamiętać, że to tylko kwestia wizualna – do wszelkich formuł i obliczeń zawsze pobierane są wartości rzeczywiste w komórce.
W arkuszu Excela bardzo często stosujemy formatowanie występujących w komórkach liczb, chcąc zaprezentować występujących w nich dane w bardziej “elegancki” sposób niż wartości rzeczywiste (polecam notkę: Formatowanie komórek – liczby). Czasami jednak pojawia się potrzeba takiej wizualizacji danych, która przekracza standardowe możliwości oferowane przez Excel. W takich przypadkach możemy zastosować formatowanie niestandardowe.
Własny format danych definiujemy w polu Typ:
Generalnie można określić tu różne formaty liczb dla wartości dodatnich, ujemnych, zerowych i tekstowych. Poszczególne części kodu są oddzielone średnikami w kolejności:
Format liczb dodatnich; Format liczb ujemnych; Format wartości zerowych; Format wartości tekstowych
Nie musimy definiować wszystkich formatów, można nawet jeden. Wówczas będzie obowiązywać dla wszystkich wartości w komórce. Jeżeli zdefiniujemy dwa formaty – pierwszy będzie stosowany przy wartościach nieujemnych (czyli liczby dodatnie i zero), a drugi – dla wartości ujemnych. Przy trzech sekcjach kodu formatowania – pomijane jest ostatnie formatowanie czyli wartości tekstowe.
Dla każdej z tych sekcji można też inny kolor czcionki. Na początku każdej z sekcji trzeba po prostu wpisać nazwę koloru w nawiasie kwadratowym.
Np. w widocznej na screenie kolumnie
wstawiłam taki format:
A jak zakodować konkretne formaty liczb? To temat na następną notkę.
Patrząc na zawartość komórek arkusza w Excelu musimy pamiętać, że nie zawsze to, co widzimy jest tożsame z rzeczywistością wartością danej komórki. Wszystko zależy od formatowania danej komórki.
Na screenie widoczny jest banalny przykład liczby sformatowanej (wyświetlanej) z dokładnością do 1 miejsca po przecinku czyli 0,3. Faktycznie jednak, jak jest to widoczne na pasku formuły – jest tam wartość wynikająca z podzielenia liczby z komórki A1 przez B1, a więc 1/3 czyli 0,33333333…
To, o czym musimy pamiętać to fakt, że do dalszych obliczeń zawsze brana jest rzeczywista wartość komórki, a nie to, co jest wyświetlane na ekranie. Czyli jeżeli w innej komórce wstawimy formułę np.10 * C1 – otrzymamy wynik 3,3333333… Choć oczywiście ta docelowa komórka też może być sformatowana. Na przykład jako ułamek zwykły:
Takich wbudowanych formatów liczbowych (a także związanych z nimi walutowych czy księgowych, procentowych itp.) jest więcej. Możemy je wykorzystywać, aby w najlepszy dla nas sposób prezentować dane.
Można też stosować formatowanie niestandardowe i samemu określić sposób wyświetlania danych. No, ale to już temat na następną notkę.
W niektórych plikach Excela znajdują się łącza do innych plików. Najczęściej są to działania świadome i celowe, pozwalające np. na agregację danych z różnych arkuszy/skoroszytów. Czasem jednak zdarza się, że łączy teoretycznie nie powinno być, a jednak są widoczne.
O ich istnieniu dowiadujemy się z komunikatów wyświetlanych przy otwarciu skoroszytu. W dodatku ich odnalezienie jest trudne, nie można ich także przerwać.
Wprawdzie można wyłączyć aktualizację łączy, ale to tylko maskuje, a nie rozwiązuje problem. Brak komunikatów nie załatwia sprawy.
W tego typu przypadkach najczęściej okazuje się, że łącza do innego pliku wcale nie są wstawione bezpośrednio w komórkach, a nieco bardziej “zagnieżdżone”. Na początek warto więc sprawdzić:
Tego typu sytuacje mogą się zdarzyć, gdy na przykład kopiujemy jakiś zakres komórek z innego skoroszytu, zapominając o tym, że jest tam zdefiniowana lista poprawności danych (przećwiczyłam to na sobie, zajęło mi to sporo czasu).
Jeśli plik jest mało rozbudowany – można to znaleźć ręcznie, choć czasem trzeba się naszukać. Gorzej, gdy plik zawiera wiele arkuszy, w każdym jest sporo danych, tabel i wykresów. Wówczas znalezienie takich odwołań może być naprawdę bardzo pracochłonne. Na takie przypadki jest specjalny dodatek Billa Manville’a:
Zainstalowałam go i całkiem często go wykorzystuję. Naprawdę jest przydatny, polecam wszystkim.
W poprzedniej notce opisywałam w jaki sposób można uruchomić wbudowane do Excela dodatki Solver i Analysis ToolPack. W Excelu możemy jednak zainstalować również własne dodatki – stworzone przez nas lub pobrane gdzieś z sieci (tu zdecydowanie zalecam rozwagę, choć jest wiele sprawdzonych stron i dodatków).
Jak i po co to zrobić? Tworzenie własnych dodatków będzie tematem kolejnych notek. Generalnie jednak jest to wykorzystanie funkcjonalności (formuł czy procedur) skoroszytu zapisanych w formacie *.xlam (lub w wersjach starszych *.xla):
Po zainstalowaniu takiego dodatku zapisane w nim formuły będą działać w naszym Excelu w każdym pliku.
Instalacja takiego dodatku jest przedstawiona tu:
Przykłady ciekawych dodatków to temat na kolejne notki.
Dodatki programu Excel – czym są i po co są stosowane? Najogólniej mówiąc są to części oprogramowania dołączane do Excela (i nie tylko) po to, aby zwiększyć jego funkcjonalność. Standardowymi dodatkami w własnymi Excela są:
Po zainstalowaniu Excela dodatki te nie są automatycznie włączone, trzeba je dodać:
Karta Plik –> Opcje –> Dodatki
Otwiera się formularz, w którym wybieramy dodatki dostępne w domyślnym katalogu (Microsoft –> Dodatki) lub wyszukujemy dodatki znajdujące się w innym miejscu.
Dodatek włączamy poprzez zaznaczenie go na liście i zatwierdzenie przyciskiem OK.
Dodatki można też włączać bezpośrednio na karcie Developer, w grupie opcji Dodatki.
Tu też znajdują się wszystkie własne i niestandardowe dodatki.
Standardowe(czyli wbudowane) dodatki Excela są widoczne na karcie Dane, w grupie Analiza.
