Mapa Temperatur w programie Excel to wizualna reprezentacja, która szybko pokazuje porównawczy widok zestawu danych.
Na przykład w poniższym zestawie danych mogę łatwo zauważyć, w których miesiącach sprzedaż była niska (podświetlona na czerwono) w porównaniu z innymi miesiącami.
W powyższym zestawie danych kolory są przypisywane na podstawie wartości w komórce. Skala kolorów to od zielonego do żółtego do czerwonego, przy czym wysokie wartości oznaczają kolor zielony, a niskie wartości - kolor czerwony.
Tworzenie mapy ciepła w Excelu
Chociaż możesz utworzyć mapę cieplną w programie Excel, ręcznie kodując komórki kolorami. Jednak będziesz musiał to powtórzyć, gdy wartości się zmienią.
Zamiast pracy ręcznej możesz użyć formatowania warunkowego, aby podświetlić komórki na podstawie wartości. W ten sposób, w przypadku zmiany wartości w komórkach, kolor/format komórki automatycznie zaktualizuje mapę cieplną na podstawie wstępnie określonych reguł formatowania warunkowego.
W tym samouczku dowiesz się, jak:
- Szybko utwórz mapę cieplną w programie Excel przy użyciu formatowania warunkowego.
- Utwórz dynamiczną mapę cieplną w programie Excel.
- Utwórz mapę cieplną w tabelach przestawnych programu Excel.
Zacznijmy!
Tworzenie mapy ciepła w programie Excel przy użyciu formatowania warunkowego
Jeśli masz zestaw danych w programie Excel, możesz ręcznie podświetlić punkty danych i utworzyć mapę cieplną.
Byłaby to jednak statyczna mapa cieplna, ponieważ kolor nie zmieniałby się po zmianie wartości w komórce.
Dlatego formatowanie warunkowe jest właściwą drogą, ponieważ zmienia kolor w komórce po zmianie w niej wartości.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Oto kroki, aby utworzyć mapę cieplną przy użyciu tych danych:
- Wybierz zbiór danych. W tym przykładzie byłoby to B2:D13.
- Przejdź do Home -> Formatowanie warunkowe -> Skale kolorów. Pokazuje różne kombinacje kolorów, które można wykorzystać do wyróżnienia danych. Najpopularniejsza skala kolorów to pierwsza, w której komórki o wysokich wartościach są podświetlone na zielono, a niskie na czerwono. Zwróć uwagę, że po najechaniu myszą na te skale kolorów możesz zobaczyć podgląd na żywo w zestawie danych.
To da ci mapę cieplną, jak pokazano poniżej:
Domyślnie program Excel przypisuje kolor czerwony do najniższej wartości, a kolor zielony do najwyższej wartości, a wszystkie pozostałe wartości otrzymują kolor na podstawie tej wartości. Tak więc istnieje gradient z różnymi odcieniami trzech kolorów w zależności od wartości.
A co, jeśli nie chcesz gradientu i chcesz pokazywać tylko kolor czerwony, żółty i zielony. Na przykład chcesz podświetlić wszystkie wartości mniejsze niż powiedzmy 700 na czerwono, niezależnie od wartości. Tak więc 500 i 650 mają ten sam czerwony kolor, ponieważ jest to mniej niż 700.
Aby to zrobić:
- Przejdź do Strona główna -> Formatowanie warunkowe -> Skale kolorów -> Więcej opcji.
- W oknie dialogowym Nowa reguła formatowania wybierz „Skala 3-kolorowa” z menu rozwijanego Styl formatu.
- Teraz możesz określić minimalną, środkową i maksymalną wartość i przypisać do niej kolor. Ponieważ chcemy podświetlić wszystkie komórki o wartości poniżej 700 na czerwono, zmień typ na Liczba i wartość na 700.
- Kliknij OK.
Teraz otrzymasz wynik, jak pokazano poniżej. Zauważ, że wszystkie wartości poniżej 700 mają ten sam odcień koloru czerwonego.
Słowo ostrzeżenia: Chociaż formatowanie warunkowe jest wspaniałym narzędziem, niestety jest niestabilne. Oznacza to, że za każdym razem, gdy nastąpi jakakolwiek zmiana w arkuszu, formatowanie warunkowe zostanie przeliczone. Chociaż wpływ może być znikomy w przypadku małych zestawów danych, może prowadzić do spowolnienia skoroszytu programu Excel podczas pracy z dużymi zestawami danych.
Tworzenie dynamicznej mapy ciepła w Excelu
Ponieważ formatowanie warunkowe zależy od wartości w komórce, po zmianie wartości formatowanie warunkowe jest ponownie obliczane i zmieniane.
Umożliwia to stworzenie dynamicznej mapy cieplnej.
Przyjrzyjmy się dwóm przykładom tworzenia map ciepła przy użyciu interaktywnych kontrolek w programie Excel.
Przykład 1: Mapa cieplna za pomocą paska przewijania
Oto przykład, w którym mapa termiczna zmienia się, gdy tylko użyjesz paska przewijania do zmiany roku.
Ten typ dynamicznych map cieplnych może być używany w pulpitach nawigacyjnych, w których masz ograniczone miejsce, ale nadal chcesz, aby użytkownik miał dostęp do całego zestawu danych.
Kliknij tutaj, aby pobrać szablon Mapy Temperatur
Jak stworzyć dynamiczną mapę cieplną?
Oto kompletny zestaw danych, który jest używany do tworzenia tej dynamicznej mapy cieplnej.
Oto kroki:
- W nowym arkuszu (lub w tym samym arkuszu) wprowadź nazwy miesięcy (po prostu skopiuj i wklej je z oryginalnych danych).
- Przejdź do Deweloper -> Sterowanie -> Wstaw -> Pasek przewijania. Teraz kliknij w dowolnym miejscu arkusza roboczego, a wstawi pasek przewijania. (kliknij tutaj, jeśli nie możesz znaleźć zakładki programisty).
- Kliknij prawym przyciskiem myszy pasek przewijania i kliknij Kontrola formatu.
- W oknie dialogowym Kontrola formatu wprowadź następujące zmiany:
- Minimalna wartość: 1
- Maksymalna wartość 5
- Link do komórki: Arkusz1!$J$1 (Możesz kliknąć ikonę po prawej stronie, a następnie ręcznie wybrać komórkę, którą chcesz połączyć z paskiem przewijania).
- Kliknij OK.
- W komórce B1 wprowadź formułę: =INDEX(Arkusz1!$B$1:$H$13,ROW(),Arkusz1!$J$1+KOLUMNY(Arkusz2!$B$1:B1)-1)
- Zmień rozmiar i umieść pasek przewijania na dole zestawu danych.
Teraz, gdy zmienisz pasek przewijania, wartość w Sheet1! $ J $ 1 zmieni się, a ponieważ formuły są połączone z tą komórką, zaktualizuje się, aby pokazać prawidłowe wartości.
Ponadto, ponieważ formatowanie warunkowe jest niestabilne, gdy tylko wartość się zmieni, również zostanie zaktualizowana.
Obejrzyj wideo - dynamiczna mapa cieplna w programie Excel
Przykład 2: Tworzenie dynamicznej mapy cieplnej w Excelu za pomocą przycisków radiowych
Oto kolejny przykład, w którym możesz zmienić mapę cieplną, wybierając przycisk opcji:
W tym przykładzie możesz podświetlić 10 górnych/dolnych wartości w oparciu o wybór przycisku opcji/opcji.
Kliknij tutaj, aby pobrać szablon Mapy Temperatur
Tworzenie mapy ciepła w tabeli przestawnej programu Excel
Formatowanie warunkowe w tabelach przestawnych działa tak samo, jak w przypadku normalnych danych.
Ale jest coś ważnego, o czym musisz wiedzieć.
Pozwól, że wezmę przykład i pokażę.
Załóżmy, że masz tabelę przestawną, jak pokazano poniżej:
Aby utworzyć mapę cieplną w tej tabeli przestawnej programu Excel:
- Wybierz komórki (B5:D14).
- Przejdź do Strona główna -> Formatowanie warunkowe -> Skale kolorów i wybierz skalę kolorów, którą chcesz zastosować.
Spowoduje to natychmiastowe utworzenie mapy ciepła w tabeli przestawnej.
Problem z tą metodą polega na tym, że jeśli dodasz nowe dane w zapleczu i odświeżysz tę tabelę przestawną, formatowanie warunkowe nie zostanie zastosowane do nowych danych.
Na przykład, gdy dodałem nowe dane w zapleczu, dostosowałem dane źródłowe i odświeżyłem tabelę przestawną, widać, że formatowanie warunkowe nie jest do niej stosowane.
Dzieje się tak, ponieważ zastosowaliśmy formatowanie warunkowe tylko do komórek B5:D14.
Jeśli chcesz, aby ta mapa cieplna była dynamiczna i aktualizowała się po dodaniu nowych danych, wykonaj następujące czynności:
- Wybierz komórki (B5:D14).
- Przejdź do Strona główna -> Formatowanie warunkowe -> Skale kolorów i wybierz skalę kolorów, którą chcesz zastosować.
- Ponownie przejdź do Strona główna -> Formatowanie warunkowe -> Zarządzaj regułami.
- W Menedżerze reguł formatowania warunkowego kliknij przycisk Edytuj.
- W oknie dialogowym Edytuj regułę formatowania wybierz trzecią opcję: Wszystkie komórki z wartościami „Sprzedaż” dla „Data” i „Klient”.
Teraz formatowanie warunkowe zostanie zaktualizowane po zmianie danych zaplecza.
Notatka: Formatowanie warunkowe znika po zmianie pól wiersza/kolumny. Na przykład, jeśli usuniesz pole Data i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.
