Wykres dynamiczny – czyli taki, który podmienia dane po jednym kliknięciu

Jako materiał wybrałem dane o produkcie krajowym brutto w przeliczeniu na osobę z poszczególnych krajów świata. Chciałem mieć arkusz w którym wybieramy z rozwijanego menu parę krajów, a wykres się aktualizuje.

Plik roboczy jest dostępny tutaj: Jeśli chcecie pobawić się nim – możecie zrobić własną kopię i ją edytować.

Wklejamy tabelę danych i tłumaczymy na polski.

Tym razem importujemy dane poprzez plik Excela, więc możemy po prostu otworzyć plik i wyciąć niepotrzebne dane. Pobrałem je ze strony data.worldbank.org.

Jedyna istotna modyfikacja to przetłumaczenie nazw państw z angielskiego na polski.

Arkusze mają piękną funkcję do tego. To GOOGLETRANSLATE.

Pełna składnia to:

=GOOGLETRANSLATE(tekst; [język źródłowy; język docelowy]

• tekst – tu możemy wpisać ciąg znaków w cudzysłowie lub odnośnik do komórki.

• język źródłowy – skrót dwuliterowy (dla angielskiego to “EN”)

• język docelowy – skrót dwuliterowy (dla polskiego to “PL”)

Szeroka lista kodów języków używanych google jest tutaj.

Jednak ta funkcja jest bardzo sprytna. Jeśli wpiszemy po prostu =GOOGLETRANSLATE(A5) to wykryje język użyty w komórce A5 i zrobi nam tłumaczenie na język, który ustawiony jest w koncie google.

Tworzymy listę rozwijaną używając opcji: Sprawdzanie poprawności danych

Drugi krok to zrobienie listy rozwijanej, żeby szybko wybierać dostępne kraje. Wybieram komórkę w której chcę mieć rozwijaną listę i wchodzę w menu Dane –> Sprawdzanie poprawności danych (ang. Data –> Data validation).

Następnie w dziale ‘Kryteria’ wybieramy odpowiedni zakres (Q5:Q268)

Tworzymy tabelę dla wykresu i nadajemy nazwę zakresowi

Ten etap jest nieobowiązkowy, ale warto pamiętać, że Arkusze Google pozwalają nazywać zakresy i w formułach używać ich nazw. To poprawia czytelność dokumentów. Stworzenie takiej zmiennej oszczędza nam czas jeśli np. zakres tabeli się zmienił – wówczas zmieniamy to w jednym miejscu, zamiast we wszystkich.

Nazywanie funkcji kryje się w menu Dane –> Zakresy nazwane

Pod nazwami zakresów można chować tabele, ale można też poprawiać czytelność funkcji nazywając konkretne komórki np. ‘kursdolara’,’VAT’,’cenabenzyny’, etc. Wówczas na pierwszy rzut oka widać co jest obliczane.

Budda

Szykujemy dane dla wykresu i rysujemy wykres

Wykresy w Google Sheets nie są w stanie zmieniać komórek z których pobierają dane. Więc nie mogę poprosić by sięgały raz po wiersz 30, a raz 45. Ale mogę zrobić dla wykresu specjalne miejsce, gdzie te dane będą się pojawiać i zmieniać, a wykres będzie je czytał.

To co potrzebujemy to dane przeniesione z dużej tabeli i ułożone tak by wykres je pobrał. W tym celu kopiujemy nazwy wybranych państw, oraz dane z tabeli przy pomocy funkcji vlookup (wyszukaj.pionowo) (zobacz wpis na jej temat).

=vlookup($C$27;tabela;column();false) – o co tu chodzi?

Taka sama formuła jest skopiowana do wszystkich kolumn z danymi w wierszu 27.

Przypominam składnię:

=VLOOKUP(kryteria_wyszukiwania; zakres; indeks; posortowany)

$C$27 – to proste. Vlookup ma szukać wiersza tabeli, w któym pierwsza komórka to ‘Argentyna’

tabela – to zdefiniowana przez nas nazwa zakresu Data!Q4:AD268

column() – tu jest sztuczka. Funkcja COLUMN() (NR.KOLUMNY() w polskiej wersji )zwraca aktualny numer kolumny. Ponieważ jesteśmy w kolumnie D, będzie to liczba 4. Tak się składa, że dane z 2008 roku są w 4 kolumnie zdefiniowanej przez nas tabeli. Mogłem wpisać po prostu 4, ale kopiując formułę musiałbym zmieniać tą wartość – dać 5 dla danych z 2009, 6 dla 2010, itp.

false – mówi, że funkcja vlookup szuka konkretnie słowa ‘Argentyna’ i nie zadowoli jej żadne przybliżenie

Rysujemy wykres

W ostatnim etapie zaznaczamy przygotowaną tabelę i wybieramy Wstaw —> Wykres z menu.

Bawimy się danymi

Pozostaje się pobawić. Ściągnięte dane pokazują jak poszczególne kraje rozwijały swoją gospodarkę w przeciągu dekady. Na załączonym przykładzie widać, że w 2008 roku statystyczny obywatel Polski i Argentyny miał podobną siłę nabywczą. 10 lat później już o 50% większą. Tymczasem Argentyna pozostała na zbliżonym poziomie. Ten post oczywiście nie służy dyskusjom ekonomicznym czy politycznym i komentarze na ten temat będą ignorowane. Ale poprzestawiać wykresy zawsze miło!

Przypominam, że działający plik można otworzyć tutaj – po zrobieniu kopii możemy się dowolnie bawić.