Wykresy złożone w Excelu

Wykresy złożone pozwalają wyświetlać informacje wyrażane w różnych skalach. Jest to przydatne w wielu typowych sytuacjach - posłużmy się tu prostym przykładem.

W tabeli umieszczamy dane o wielkości PKB w Polsce i Niemczech, a ściślej, o stosunku do średniej w Unii Europejskiej, którą przyjmujemy za 100, w okresie 12 lat, od 2001 do 2012 roku. Chcąc się dowiedzieć, jaka jest relacja Polski do Niemiec, w osobnej kolumnie obliczamy iloraz obu wielkości, czyli stosunek wielkości dla Polski do wielkości dla Niemiec.

Utworzymy jeszcze wykres ilustrujący te relacje. Skorzystamy z wykresu kolumnowego.

Widzimy kolumny dla Polski i Niemiec, ale nie widzimy kolumny z relacją P/N. Przyczyna jest oczywista - relacja jest mniejsza od 1, więc danych tych po prostu nie widać w skali, w której górną wartością na osi Y jest 140.

Musimy wyświetlić dane z tej kolumny w inny sposób -  innym typem wykresu i w innej skali. Klikamy obszar wykresu, przechodzimy na kontekstową kartę Układ i w grupie bieżące zaznaczenie klikamy Obszar wykresu, a następnie na rozwijanej liście wybieramy Seria danych “Relacja P/N”.

Seria Relacja P/N zostaje zaznaczona i widać uchwyty jej kolumn.

Przechodzimy na kartę Projektowanie i w grupie Typ klikamy przycisk Zmień typ wykresu. Wybieramy wykres liniowy.

Na razie niewiele widzimy, gdyż dane kolumny wyświetlane są linią położoną niemal na zerowej wysokości.

Zaznaczamy tę linię myszką, klikamy prawym przyciskiem myszy i wybieramy polecenie Formatuj serię danych.

W oknie Formatowanie serii danych, na karcie Opcje serii, zaznaczamy radiowe pole wyboru Oś pomocnicza.

Po kliknięciu Zamknij ukazuje się linia trendu i nowa oś z danymi procentowymi po prawej stronie.

Możemy jeszcze zaznaczyć myszką oś z procentami i pod prawym klawiszem myszy wybrać polecenie Formatuj oś.

W oknie Formatowanie osi, na karcie Opcje osi, przy Minimum zaznaczamy Stała 0,0, a przy Maksimum Stała 1.

Oś będzie zawierać skalę nie od 0 do 60%, lecz od 0 do 100%, a linia przesunie się niżej. Na kontekstowej karcie Układ, w grupie Etykiety, możemy jeszcze włączyć tabelę danych. I oto ostateczny efekt - jak widać, tym razem lepszy informacyjnie:

Wykresy skumulowane w Excelu

W programie Microsoft Excel ma miejsce pewna niejasność terminologiczna, niezgodność z używanymi w statystyce pojęciami. Warto wyraźnie pokazać problem i wyjaśnić, jak sobie z nim poradzić (w artykule mowa o wersji 2007).

Przedstawiając dane statystyczne, posługujemy się nierzadko wartościami skumulowanymi. To cenna metoda przedstawiania danych, zwłaszcza na wykresach, obrazuje bowiem, jaka część populacji znajduje się poniżej pewnej wartości, a jaka powyżej. Prosty przykład: na roku akademickim na jakimś wydziale z 80 studentów płci męskiej 14 ma do 165 cm wzrostu, 28 ma do 170 cm, 45 do 175 cm, 68 do 180 cm, 76 do 185, a 80 do 190 cm. Chodzi o wartości łączne, a nie w pojedynczych przedziałach.

Co oczywiste, gdybyśmy przedstawili dane w częstościach lub procentach, górna wartość wynosiłaby nie 80, lecz odpowiednio 1 lub 100% (dane się kumulują).

Naturalnie dane wyjściowe przedstawiane są przede wszystkim w postaci statystycznego szeregu przedziałowego - to najczęściej spotykana forma tabelarycznej prezentacji danych. Pokażmy teraz przykład spółdzielni mieszkaniowej, w której mieszkania mają różne powierzchnie i chcemy się dowiedzieć, jak wygląda rozkład danych, jaka jest liczebność mieszkań o powierzchni w danym przedziale.

Tabela pokazuje dokładne liczby, natomiast wykres wizualizuje poglądowo liczebność poszczególnych przedziałów powierzchni i relacje tych liczb. Widać, że najczęściej występują mieszkania o powierzchni w granicach 60-80 metrów kwadratowych.

Wykres sporządziliśmy bardzo prosto, sięgając do pierwszego wariantu typu kolumnowego, jako najczęściej chyba stosowanego w praktyce (i jeszcze “dosmaczając” go nieco graficznie na kontekstowej karcie Projektowanie).

I tu dochodzimy do sedna sprawy. Staje oto przed nami zadanie zbadania i pokazania na wykresie, ile mieszkań ma do 60 metrów kw., a ile do 120. Już na pierwszy rzut oka widać, że trzeba dodać do siebie liczebności kilku przedziałów - w pierwszym przypadku dwóch poniżej/do 60 metrów, a w drugim pięciu poniżej/do 120 metrów.

Zaznaczamy więc myszką wykres i na karcie Projektowanie, w grupie Typ, klikamy przycisk Zmień typ wykresu. Na ekranie pojawia się okno Zmienianie typu wykresu. Na liście wykresów na drugim miejscu jest wariant skumulowany kolumnowy.

Zaznaczamy wariant, klikamy OK i… nic się nie dzieje. Wykres pozostaje niezmieniony. Dlaczego?

Ulegliśmy tutaj nieporozumieniu terminologicznemu - w języku Excela wykres skumulowany jest czym innym niż w języku statystyki. Pokażmy to znowu na przykładzie.

Dodajemy drugą spółdzielnię i badamy rozkład powierzchni jej mieszkań. Dane widzimy w kolumnie C. Gdy teraz zechcemy zwizualizować rozkład i sięgniemy do typu kolumnowy skumulowany stanie się jasne, że kumulowany znaczy w Excelu coś innego. Excel kumuluje wartości dwóch (lub więcej) szeregów statystycznych, a nie kolejne wartości jednego szeregu. Tutaj kumuluje poziomo, “w poprzek”, a nie pionowo w kolumnie. A więc sumuje liczby mieszkań spółdzielni A i B osobno w przedziale 20-40, osobno w 40-60 itd. Na wykresie to suma niebieskiego i czerwonego prostokąta wyliczona dla poszczególnych przedziałów.

W statystyce natomiast kumulacja oznaczałaby sumy komórek w danej kolumnie (w jednym szeregu statystycznym), najpierw B2, potem B2+B3, potem B2+B3+B4 itd. W ten sposób otrzymalibyśmy tzw. dystrybuantę empiryczną - jej przykład jest na pierwszej ilustracji.

Jak więc poradzić sobie z problemem?

Musimy utworzyć w tabeli dodatkową kolumnę zliczającą kolejne takie sumy. Zadanie jest banalne.

• Ustaw kursor w pierwszej komórce kolumny liczebności skumulowanych (tutaj C2) i wpisz adres pierwszej komórki z liczbą mieszkań, np. =b3.
• Ustaw kursor w kolejnej komórce kolumny liczebności skumulowanych (tutaj C3) i wpisz formułę dodającą drugą komórkę z liczbą mieszkań i pierwszą komórkę w kolumnie liczebności skumulowanych, np. =b3+c4 (na ilustracji formuła podkreślona jest żółtym kolorem).
• Ustaw kursor w trzeciej komórce kolumny liczebności skumulowanych (tutaj C4) i wpisz formułę dodającą trzecią komórkę z liczbą mieszkań i drugą komórkę w kolumnie liczebności skumulowanych, np. =b4+c3. Rozumiesz już mechanizm? Kumulujesz w ten sposób liczby z kolumny B.

Tak naprawdę wystarczy skopiować już pierwszą formułę w komórce C3, zaznaczyć blokiem wszystkie komórki w dół i wkleić formułę (Ctrl+v). Excel wypełni automatycznie komórki i natychmiast obliczy wartości.

Co nam pozostaje? Już tylko zrobienie wykresu naszej dystrybuanty empirycznej. Zaznaczamy dane z kolumny A (od A1 do A7), wciskamy klawisz Ctrl i zaznaczamy dane z kolumny C, po czym tworzymy zwykły wykres kolumnowy. Dla porządku należałoby jeszcze nazwać inaczej przedziały, gdyż chodzi już nie o wielkości w przedziałach lecz do określonej wysokości. Pomijamy to jednak w tym miejscu.

W ofercie rozmaitych firm produkujących dodatki do Excela (to potężny przemysł programistyczny) są naturalnie automaty generujące takie wykresy bez żadnych dodatkowych zabiegów. Warto się przy okazji rozejrzeć w Internecie - okazuje się, że wbudowane w Excela czy Calca wykresy, choć wystarczają w typowych zastosowaniach, to zaledwie niewielka część możliwości, jakie można uzyskać dzięki wyspecjalizowanym dodatkom (add-ins).

Formatowanie warunkowe w Excelu - paski danych

Dane tabelaryczne powinny być uzupełniane wizualizacjami, gdyż znacznie lepiej się “wchłaniają”, by użyć farmaceutycznej metafory. Typowy sposób wizualizowania to wykresy, ale warto też pamiętać o dość rzadko wykorzystywanej technice, jaką jest formatowanie warunkowe. Przeprowadziłem małą kwerendę wśród skomputeryzowanych znajomych i nie siląc się na precyzję takiego badania mogę stwierdzić, że mało kto w ogóle wie o tej technice.

Prosty przykład ilustrujący jedną z technik formatowania warunkowego, czyli paski danych.

Te czerwone paski pokazują umownie relacje między danymi - tutaj, wielkością PKB w dolarach kilkunastu krajów Unii Europejskiej. Najniższa wartość jest ledwo zaznaczona w komórce, pozostałe układają się według pewnego algorytmu aż do najwyższej.

Jak wprowadzamy takie paski?

Na karcie Narzędzia główne, w grupie Style, jest rozwijany przycisk Formatowanie warunkowe (uwaga: opisujemy tu Excel 2007). Gdy po zaznaczeniu blokiem komórek rozwiniesz przycisk, a potem przesuniesz kursor myszy nad Paski danych i jeden z sześciu gotowych wzorców, zobaczysz na podglądzie paski w danym kolorze. Kliknięcie wzorca spowoduje zastosowanie go w tabeli (uważaj, aby formatowanie samej tabeli np. tła komórek, nie nakładało się na formatowanie pasków).

Warto sięgnąć do polecenia Więcej reguł…, aby ustalić sposób formatowania, np. zmienić proporcje widocznych pasków.

Gdy w sekcji Edytuj opis reguły jako najkrótszy pasek przyjmiesz jako typ Liczba, a wartość 0, paski będą miały proporcjonalne długości, gdyż dolną wartością długości paska będzie 0.

W poniższym przykładzie porównaj relacje pasków danych z trzech lat, gdzie dla roku 2012 dolną granicą (najkrótszy pasek) jest najniższa wartość w grupie (Łotwa, 18600), dla roku 2013 przyjęliśmy Liczba = 10000, a dla roku 2014 - Liczba = 0. Proporcje pasków urealniają się, gdy dolna granica obniża się do zera.

Poeksperymentuj z różnymi opcjami, aby sprawdzić ich działanie, np. zachowanie w przypadku procentu czy zmiany wartości najdłuższego paska. Sprawne posługiwanie się tym narzędziem wymaga po prostu wprawy.

Menedżer reguł formatowania warunkowego pozwala usuwać i modyfikować utworzone reguły.

Microsoft Excel - tabele przestawne

Rok akademicki się rozpędza, studenci uczą się korzystania z narzędzi informatycznych, w tym zwłaszcza arkuszy kalkulacyjnych. Jedna z najcenniejszych technik analitycznych Excela to tabele przestawne - w YouTube znajdzie czytelnik umieszczony tam niecały rok temu 9-minutowy wykład pokazujący poglądowo posługiwanie się tym narzędziem. Mam nadzieję, ze pozwoli wielu osobom opanować to znakomite narzędzie, tak jak udało się już wielu dotychczasowym widzom tego screencastu.

Polecam oglądanie bezpośrednio w YouTube - link: http://www.youtube.com/watch?v=2d8gQ2G6c3U. Najlepiej w rozdzielczości 720p.

Microsoft Excel - panuj nad wykresami

Kilka miesięcy temu nagrałem screencast zatytułowany "Microsoft Excel - panuj nad wykresami". Przypominam go tutaj z okazji zbliżającego się roku akademickiego - studenci to akurat grupa zawodowa szczególnie często posługująca się arkuszami kalkulacyjnymi, a Excelem w szczególności. Prezentacja danych to dziś wręcz sprawność cywilizacyjna na studiach, zarówno w naukach przyrodniczych, jak i społecznych, jak zwłaszcza nauki ekonomiczne.

Zrozumienie technik Excela jest tu niezbędne, a z doświadczenia wiem, że to potężne narzędzie potrafi onieśmielić wiele osób (kłopoty znajomych są tu moją motywacją) - chyba większość użytkowników ogranicza tworzenie wykresów do najbardziej elementarnych technik, zwykle gotowców.

W screencaście staram się wytłumaczyć logikę tworzenia wykresów, pokazać wyraźnie trzy etapy tego procesu i odpowiadające im narzędzia. Czy mi się to udało, nie mnie oceniać, tym niemniej jest duża szansa, że zainwestowanie 12 minut w uważne obejrzenie tego filmu pozwoli zrozumieć, o co tu chodzi, a oszczędzić też dziesiątek godzin pracy i zbędnej frustracji. Zrozumienie pozwala zapamiętać, a odwrotna zależność nie jest prawdziwa. Nagrywając screencast, posługiwałem się jedną z dwóch najpopularniejszych dziś wersji Excela, 2007.

Oryginalny film ma wymiary 1280x720 px, maksymalną rozdzielczość 720p (HD), którą warto włączyć. Polecam jak największy interfejs w YouTube.

Bezpośredni link: https://www.youtube.com/watch?v=jk09uMp9-uc

Microsoft Excel - wykresy przestawne

"Wykresy przestawne" - film w YouTube, wykład uzupełniający "Tabele przestawne".

Bezpośredni link: http://www.youtube.com/watch?v=PWn6hCyiinA

Pokalkulujmy

Pamiętam, jak rozmawiałam z kandydatkami na zastępstwo, ponieważ byłam w ciąży. Zacznę od tego, że wszystkie osoby wpisały w CV dobrą znajomość Excela. A jak zaczęłam sprawdzać ich umiejętności, to potrafiły wyliczyć sumy końcowe w tabelkach i niewiele więcej.

Już kilka razy mówiłem przy różnych okazjach, że akurat Excela uważam za najważniejszy program komputerowy w historii, za killer application. Jestem przekonany (tzw. silne wewnętrzne przekonanie, choć bez badań), że osoba z dobrą znajomością arkusza jest pierwsza do zatrudnienia (mówię naturalnie o miejscach, w których używa się arkuszy, nie o brygadzie kopaczy rowów), zaś ostatnia do zwolnienia. Nie obsługa Facebooka liczy się dziś w pracy biurowej, lecz obsługa arkusza kalkulacyjnego.

Excel jest bardzo rozległym funkcjonalnie programem, o niezwykle szerokich zastosowaniach. Jest wielowarstwowy i daje duże możliwości kreatywnego użycia. Nie powalę przyszłego szefa na kolana umiejętnością zbudowania bibliografii w Wordzie czy efektownych animacji w PowerPoincie, ale gdy powiem, że znam dobrze tabele przestawne, Solvera, Co-jeśli, łączenie arkusza z bazami danych na serwerze, ze potrafię stosować formatowanie warunkowe, że w jednym palcu mam formuły finansowe i statystyczne, że panuję nad formatami liczb i biegle tworzę formatowanie niestandardowe komórek, że wreszcie swobodnie posługuję się wizualizacjami danych, to z całą pewnością zyskam co najmniej zainteresowanie.

Obsługa Excela nie wykracza poza sprawność intelektualną studenta (średnie IQ żaka to zapewne jedno odchylenie standardowe powyżej średniej w kraju) - nie ma żadnego powodu, by po czterech latach studiów nie potrafić swobodnie się nim posługiwać, aczkolwiek znowu moje silne przekonanie podpowiada mi, że potrafi to może co dziesiąty absolwent, choć pewnie każdy umie chociaż podsumować komórki w tabeli.

Posłużenie się arkuszem w różnych sytuacjach to sprawność cywilizacyjna. Skorzystam tu z okazji, by zauważyć, że w naszym podręczniku dla szkół podstawowych arkusze (Excel i Calc konkretnie) zajmują w piątej klasie kilkadziesiąt stron podręcznika, a i filmów przygotowaliśmy też kilkanaście, by dać dzieciakom dobry start, by zachęcić, a może nawet wręcz zafascynować tym narzędziem. Efekty tego będą jednak za sześć, osiem czy dziesięć lat.

Na marginesie przypuszczam, że dobra znajomość narzędzi biurowych, a Excela w szczególności, mogłaby też być szansą na zdalną pracę dla wielu osób o dysfunkcjach fizycznych, które ze zdobyciem jej mają poważne kłopoty i są często zdane na niewystarczające renty. Ale to temat na osobną dyskusję, bo wymaga szerzej zakrojonych działań organizacyjnych.