Czyli mapa zależna od wyboru na liście rozwijanej

Chcemy analizować poziom hałasu w wybranych miastach, w wybranych miesiącach. Tabelę z danymi mamy przygotowaną w arkuszu, jednak wyniki chcemy zwizualizować na mapie w miły dla oka sposób. Naszym celem jest, aby użytkownik wybierał z listy rozwijanej miesiąc analizy, a odpowiednie wartości poziomu hałasu dla miast wyświetlą się na mapie. Dodatkowo, od razu chcemy również zobaczyć w których miejscach poziom hałasu został przekroczony – wartość ma zostać wtedy zaznaczona na czerwono. Chodzi o taki efekt:

Na pierwszy rzut oka wydaje się to niesamowicie skomplikowane, jednak w wersji minimalnej wystarczy do tego formatowanie warunkowe i WYSZUKAJ.PIONOWO. Zobaczcie 🙂

Co jest potrzebne?

Potrzebujemy 4 elementów:

1. Tabeli z danymi
2. Mapy (w tym wypadku Polski)
3. Listy rozwijanej
4. Tabeli dla etykiet widocznych na mapie

Wszystkie te elementy mogą się znajdować w arkuszu z danymi, jednak aby uzyskać bardziej szpanerski efekt – wszelki elementy robocze, czyli tabele (pkt. 1. i 4.) warto umieścić w roboczym arkuszu.

1. Tabela z danymi, którą polecam umieścić w arkuszu roboczym (u mnie ten arkusz nazywa się Źródło), wygląda tak:

Tabela z danymi

W kolumnach mamy miejscowości, które chcemy analizować, a w wierszach miesiące. Dodatkowo, poza tą tabelą jest komórka (C7) informująca o wartości granicznej hałasu: 40. Jeśli hałas będzie większy niż 40 – wartość pomiaru ma być podświetlana na czerwono.

2. Mapa, to element graficzny, zwykły obrazek jpg, png czy inny, który można wstawić do Excela. Ja wstawiłam go do arkusza głównego (u mnie nazywa się on Dane).

3. Lista rozwijana z miejscowościami. Tutaj mamy 2 opcje: sprawdzanie poprawności danych lub formant formularza (pole kombi). Obie listy zadziałają, natomiast trzeba będzie napisać nieco inne formuły, aby je wykorzystać. W obu opcjach podajemy jako źródło listy zakres miesięcy, czyli z arkusza Źródło: J6:J9.

a) Sprawdzanie poprawności (Dane/ Poprawność danych) ja umieściłam w arkuszu głównym (tam, gdzie jest mapa, Dane) do komórki C3:

Lista rozwijana ze sprawdzania poprawności

b) Formant formularza (pole kombi), najlepiej umieścić na mapie, aby user miał od razu tytuł mapy. Łącze komórki proponuję dać w jakiejś komórce w arkuszu roboczym (Źródło), np. C9:

Lista rozwijana jako formant formularza

To, jaką listę rozwijaną wybierzesz zależy w zupełności od Ciebie. Obie zdadzą egzamin.

4. Tabela do etykiet, widocznych na mapie. Temu poświęcę cały następny podrozdział tego artykułu, zatem czytaj dalej 🙂

Przygotowanie etykiet – formuła

Cały trik naszej interaktywnej mapy polega na tym, że przygotujemy ładne, dynamiczne etykiety w zwyczajnych komórkach arkusza, a następnie wkleimy je specjalnie jako obraz połączony na mapę. Tyle tylko, że aby te etykiety były dynamiczne – muszą być oparte na formułach zależnych od wybory miesiąca prze usera. A żeby ładnie wyglądały – komórki, w których się znajdują też muszą ładnie wyglądać. I tym teraz się zajmiemy – najpierw formułą, a w kolejnym rozdziale – formatowaniem.

Niezależnie od tego, którą metodę listy rozwijanej wybraliśmy – etykiety trzeba zrobić i ja umieszczę je w arkuszu roboczym (Źródło), w komórkach C4:H4:

Tabela etykiet – formatka

Zauważ, że kolumny tabeli zostały ponumerowane w wierszu 2. – będzie to potrzebne funkcji, którą zaraz użyjemy.

BTW: poniżej tej tabeli widać komórkę, w której zdefiniowane jest przekroczenie normy hałasu (C7).

a) Formuła dla listy rozwijanej: sprawdzanie poprawności

Ponieważ jestem zwolenniczką prostoty – opiszę najprostszą formułę, która wyświetli pomiar hałasu w zależności od wybranego przez usera miesiąca. Użyję bowiem WYSZUKAJ.PIONOWO.

Ta funkcja będzie szukała w tabeli z danymi (na początku artykułu) miesiąca wybranego przez usera i wyświetlała wartość poziomu hałasu dla wybranej miejscowości. Ale której miejscowości? Przecież mamy ich 6. Tak, i po to właśnie nam jest 6 komórek dla sześciu etykiet. Pierwsza komórka wyświetli wartość dla Warszawy, kolejna dla Łodzi itd. Stąd potrzebne nam będą numery kolumn tabeli z danymi, w której znajdują się miejscowości. Numery te mamy wpisane w wierszu drugim, nad naszymi etykietami, i do tego wiersza odwołamy się w WYSZUKAJ.PIONOWO. Wartości te wpisałam ręcznie – najprostsze rozwiązanie. I tak wiem: sztywne, natomiast ja nie planuję tutaj nić zmieniać i jednocześnie oszczędzę Excelowi pracę w postaci wyliczenia tego numerka ;).

Zatem do komórek C4:H4 wpiszemy następującą formułę:

=WYSZUKAJ.PIONOWO(Dane!$C$3;Źródło!$J$6:$P$9;C2;0)

Szukamy wartości wybranej przez usera (Dane!$C$3), w tabeli z danymi (Źródło!$J$6:$P$9), wyświetlamy wartość z kolumny o numerze, znajdującym się w komórce powyżej (C2) i szukamy dokładnie.

Oczywiście numer kolumny możesz znaleźć funkcją PODAJ.POZYCJĘ i wtedy formuła będzie wyglądała tak:

=WYSZUKAJ.PIONOWO(Dane!$C$3;Źródło!$J$6:$P$9;PODAJ.POZYCJĘ(C3;$J$5:$P$5;0);0)

Albo oczywiście wersja z funkcją INDEKS:

=INDEKS($K$6:$P$9;PODAJ.POZYCJĘ(Dane!$C$3;$J$6:$J$9;0);PODAJ.POZYCJĘ(C3;$K$5:$P$5;0))

Wszystkie wersje zwrócą te same wyniki.

b) Formuła dla listy rozwijanej: formant formularza (pole kombi)

Jeśli wolisz opcję z formantem formularza – formuła będzie miała jedną modyfikację: będziemy musieli odnaleźć słowną wersję miesiąca, wybraną przez usera z listy rozwijanej. W poprzedniej wersji mieliśmy ją podaną na tacy – teraz musimy sobie odszukać sami. Pomoże nam w tym funkcja INDEKS, którą zagnieździmy w WYSZUKAJ.PIONOWO. Formuła będzie taka:

=WYSZUKAJ.PIONOWO(INDEKS($J$6:$J$9;$C$9);Źródło!$J$6:$P$9;C2;0)

Niezależnie od tego, jaką opcję wybierzemy, uzyskamy następujący efekt:

Tabela etykiet – wynik formuły

Formatowanie warunkowe: przekroczenie

Jednak żeby spełnić wymaganie, że etykiety mają być kolorowane na czerwono w przypadku przekroczenia normy (40 – komórka C7), dodajmy do nich bardzo prościutkie formatowanie warunkowe. Niech wypełnienie komórki będzie czerwone, gdy jej wartość jest większa niż 40.

Narzędzia główne/ Formatowanie warunkowe/ Reguły wyróżniania komórek/ Większe niż…:

Tworzenie formatowania warunkowego

Zwróć uwagę, że odwołałam się do komórki C7, gdyż zawiera ona wartość graniczną: 40. Jeśli masz pewność, że ta wartość się nie zmieni – możesz ją wpisać z palca do tego okienka. Ja jednak lubię widzieć od razu wartości defninicyjne :).

Po zatwierdzeniu uzyskamy taki efekt:

Tabela etykiet z formatowaniem warunkowym

Naniesienie etykiet na mapę

Teraz pozostaje nam już tylko nanieść nasze piękne i kolorowe etykiety na mapę. To zrobimy ręcznie. Opiszę to na przykładzie Warszawy.

1. Zaznaczam komórkę C4, gdyż to jest moja etykieta dla Warszawy.
2. Kopiuję ją, Ctrl + C.
3. Przechodzę do arkusza z mapą i wklejam skopiowaną komórkę specjalnie, czyli: klikam prawym przyciskiem myszy na dowolną komórkę i z menu kontekstowego wybieram opcję Wklej jako obraz połączony:

Wklejanie etykiety jako obraz połączony

4. Umieszczam etykietę na mapie w tym miejscu, gdzie powinna być.

Te czynności powtarzamy dla wszystkich etykiet.

Na koniec tylko polecam wszystkie elementy: etykiety i mapę zgrupować, aby stanowiły jeden obiekt. Aby to zrobić należy je zaznaczyć (przytrzymując klawisz Ctrl). Następnie klikamy na to zaznaczenie prawym przyciskiem myszy i wybieramy opcję Grupuj.

Jeśli zaś chcesz mieć jeszcze inteaktywny tytuł mapy, wyświetlający np. nazwę wybranego miesiąca – wstaw na mapie kształt, np. prostokąt i postępuj tak, jakbyś tworzył dynamiczny tytuł wykresu (czynności te opisałam tutaj).

Tyle – mówiłam, że będzie łatwe? :). Pozostaje nam teraz cieszyć się cudowną interaktywną mapą :).

Na koniec mam do Ciebie prośbę: pomożesz mi dotrzeć z tym artykułem do szerszego grona? Jeśli zechcesz udostępnić ten artykuł na Facebooku – będę Ci bardzo wdzięczna! Bardzo się starałam, aby ten artykuł był dla Ciebie na tyle wartościowy, by wart był wzmianki ;). Oczywiście śmiało podeślij go innym osobom mailem lub Messengerem. Będzie mi bardzo miło:)

I wersja wideo wpisu: