Przed nami ostatni odcinek serii artykułów o prognozie demograficznej – pisałem już o prognozie demograficznej z użyciem dwóch kolorów oraz prognozie z zaznaczeniem przewag. Teraz czas na ten sam efekt uzyskany za pomocą opcji formatowanie warunkowe > paski danych. Jest to niedoceniana opcja wizualizacji w Excelu, która pozwala świetnie łączyć się nie tylko ze zwykłymi tabelami, ale też z tabelami przestawnymi.

Przygotowanie danych w programie Excel

Tak jak poprzednio, wykorzystam dane dot. liczby kobiet i mężczyzn w 2013 w poszczególnych grupach wiekowych oraz prognozy ich liczby w 2043 roku. Grupy wiekowe, które będę wyróżniał to 40 latkowie w 2013 roku oraz 70 latkowie w 2043.

Dane pochodzą z GUS. W poszczególnych przedziałach wiekowych mam podaną liczbę kobiet i mężczyzn. W tym momencie dane są uszeregowane według rosnących przedziałów wiekowych. By dane zaprezentować w sposób analogiczny jak w poprzednim artykule muszę odwrócić kolejność prezentowanych przedziałów (paski będę budować dokładnie na przekonwertowanych danych).

W tym celu wykorzystam połącznie funkcji ADRES oraz ADRES.POŚR. Funkcja ADRES stworzy adres komórki do której będę się odwoływać (na podstawie podanego numeru wiersza oraz kolumny) zaś funkcja ADRES.POŚR  pozwoli na odczytanie zawartości wskazanej komórki.

Poniżej funkcja, w komórce J3, którą posłużę się do uzyskania poszczególnych przedziałów wiekowych oraz widok na układ danych. Funkcją uzupełniam komórki od J3 do J22.

W komórkach obok wpisuję analogiczną formułę – wprowadzając dane dla kobiet i mężczyzn.

Formuła dla mężczyzn (dane po lewej od przedziałów wiekowych) w komórce I3:

Formuła dla kobiet (dane po prawej od przedziałów wiekowych) w komórce K3:

Poniżej , po lewej dane pierwotne, po prawej skonwertowane w sposób by stulatkowie byli na szczycie piramidy.

Formatowanie warunkowe w postaci pasków danych

Zaznaczam komórki K2:K22 – dane dotyczące kobiet > Narzędzia główne >Style >  Formatowanie warunkowe > Paski danych > Więcej reguł.

W oknie Nowa reguła formatowania, zaznaczam opcję pokaż tylko pasek (w komórkach będą tylko paski bez wartości liczbowych) > OK.

Poszerzam nieco kolumnę aby paski były lepiej widoczne. Zaznaczam całą kolumnę K, wybieram Narzędzia główne >Schowek > Malarz formatów > zaznaczam kolumnę I by sformatować ją w identyczny sposób, aby kolumny miały taką samą szerokość).

Poniżej otrzymany efekt.

Paski po lewej musimy zwrócić ku wartościom przedziałów. Wybieram Narzędzia główne > Style > Formatowanie warunkowe > Zarządzaj regułami > Pokaż reguły formatowania dla Ten arkusz.

Wybieram zakres który dotyczy kolumny I > Edytuj regułę.

Otrzymany efekt:

Aby zarówno z prawej jak i lewej strony mieć proporcjonalne paski danych, skalę dla pasków wartości ustalę na min 0 max 2000000. Narzędzia główne > Zarządzaj regułami > Ten Arkusz. Wybieram pierwszą z reguł, zaznaczam ją > Edytuj regułę. W oknie edytowanie reguły formatowania wybieram  w polu typ dla minimum i maximum liczbę, jako wartość odpowiednio 0 oraz 2 000 000. Powtarzam czynność dla drugiej z reguł formatowania warunkowego.

Dzięki czemu piramida nieco się zwęziła.

Pozostaje jedynie zaznaczenie wybranego przedziału (40 latków) kolorem. W tym celu zarówno dla wartości liczby kobiet jak i mężczyzn dla przedziały 40 latków usuwam formatowanie warunkowe > zaznaczam paski danych> Narzędzia główne > Formatowanie warunkowe > Wyczyść reguły > Wyczyść z zaznaczonych komórek.

Następnie oddzielnie dla wartości kobiet , oddzielnie dla mężczyzn zakładam nowe formatowanie warunkowe. O analogicznych ustawieniach jak we wcześniejszych pasach danych, jednak ze zmienioną kolorystyką.

Ustawienia dla części dotyczącej kobiet.

Ustawienia dla części dotyczącej mężczyzn.

Finalnie sformatowana piramida dla roku 2013 oraz 2043, kolorem zaznaczyłem liczbę kobiet i mężczyzn w wieku 40 a następnie 70 lat.

Plik Excel do pobrania

Pobierz plik Prognoza demograficzna przy pomocy pasków danych.xlsx i wstaw swoje dane.