Jak utworzyć mapę cieplną w programie Excel – przewodnik krok po kroku

Mapa cieplna w programie Excel jest wizualną reprezentacją, która szybko pokazuje porównawczy widok zbioru danych.

na przykład w poniższym zestawie danych mogę łatwo zauważyć, które miesiące, w których 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 jest od Zielonego do żółtego do Czerwonego z wysokimi wartościami uzyskującymi kolor zielony, a niskimi wartościami uzyskującymi kolor czerwony.

Tworzenie mapy ciepła w programie Excel

podczas gdy możesz utworzyć mapę ciepła w programie Excel, ręcznie kodując kolorami komórki. Jednak będziesz musiał go ponownie wykonać, gdy wartości się zmienią.

zamiast pracy ręcznej możesz użyć formatowania warunkowego, aby wyróżnić 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ł w formatowaniu warunkowym.

w tym samouczku dowiesz się, jak:

• szybko utworzyć 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 Excela.

zaczynajmy!

Tworzenie mapy cieplnej 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ą.

to jednak byłaby statyczna mapa cieplna, ponieważ kolor nie zmieniłby się po zmianie wartości w komórce.

dlatego formatowanie warunkowe jest właściwym sposobem, ponieważ powoduje zmianę koloru w komórce po zmianie wartości w niej.

Załóżmy, że masz zestaw danych, jak pokazano poniżej:

oto kroki, aby utworzyć mapę cieplną przy użyciu tych danych:

• Wybierz zestaw danych. W tym przykładzie będzie to B2: D13.
• przejdź do strony głównej –> Formatowanie warunkowe –> skale kolorów. Pokazuje różne kombinacje kolorów, które można wykorzystać do podświetlenia danych. Najczęstszą skalą kolorów jest pierwsza, w której komórki o wysokich wartościach są podświetlone na zielono, a niskie na Czerwono. Należy pamiętać, że po najechaniu kursorem myszy na te skale kolorów można zobaczyć podgląd na żywo w zestawie danych.
  

to daje 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 wartości. Tak więc istnieje gradient z różnymi odcieniami trzech kolorów w oparciu o wartość.

teraz, co jeśli nie chcesz gradientu, a chcesz tylko pokazać czerwony, żółty i zielony. Na przykład, chcesz podświetlić wszystkie wartości mniejsze niż powiedzmy 700 na Czerwono, niezależnie od wartości. Więc 500 i 650 mają ten sam czerwony kolor, ponieważ jest mniejszy niż 700.

aby to zrobić:

• przejdź do strony głównej –> Formatowanie warunkowe –> skale kolorów –> więcej opcji.
• w oknie dialogowym Nowa reguła formatowania wybierz „skala 3 Kolorów” z listy rozwijanej styl formatowania.
• teraz możesz określić wartość minimalną, średnią i maksymalną oraz przypisać do niej kolor. Ponieważ chcemy podświetlić wszystkie komórki o wartości poniżej 700 na Czerwono, Zmień typ na liczbę 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ń czerwonego koloru.

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 zmiana w arkuszu roboczym, formatowanie warunkowe zostanie ponownie obliczone. Chociaż wpływ może być znikomy w przypadku małych zestawów danych, może prowadzić do powolnego skoroszytu programu Excel podczas pracy z dużymi zestawami danych.

tworzenie dynamicznej Mapy cieplnej w programie Excel

ponieważ formatowanie warunkowe zależy od wartości w komórce, gdy tylko zmienisz wartość, formatowanie warunkowe przeliczy się i zmieni.

umożliwia to wykonanie dynamicznej mapy cieplnej.

przyjrzyjmy się dwóm przykładom tworzenia map ciepła przy użyciu interaktywnych kontrolek w Excelu.

przykład 1: mapa cieplna za pomocą paska przewijania

oto przykład, w którym mapa cieplna zmienia się natychmiast po użyciu paska przewijania do zmiany roku.

ten typ dynamicznych map ciepła 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 ciepła

Jak stworzyć tę dynamiczną mapę ciepła?

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 wklej je z oryginalnych danych).
• przejdź do programisty –> 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 deweloper).
• kliknij prawym przyciskiem myszy pasek przewijania i kliknij przycisk Formatuj.
• w oknie dialogowym Kontrola formatu wprowadź następujące zmiany:
  • minimalna wartość: 1
  • Maksymalna wartość 5
  • Link komórki: Sheet1!$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 wpisz formułę: = INDEX (Sheet1!$B$1:$H$13,ROW (), Sheet1!$J$1 + kolumny (Sheet2!$B$1:B1) -1)
• Zmień rozmiar i umieść pasek przewijania na dole zbioru danych.

teraz po zmianie paska przewijania, wartość w arkuszu1!$J $ 1 ulegnie zmianie, a ponieważ formuły są połączone z tą komórką, zaktualizuje się, aby wyświetlić prawidłowe wartości.

Ponadto, ponieważ formatowanie warunkowe jest zmienne, gdy tylko zmieni się wartość, jest również aktualizowane.

Obejrzyj wideo-Dynamiczna mapa cieplna w Excelu

przykład 2: Tworzenie dynamicznej Mapy cieplnej w programie Excel za pomocą przycisków radiowych

oto kolejny przykład, w którym można zmienić mapę cieplną, wybierając przycisk radiowy:

w tym przykładzie można wyróżnić górne/dolne wartości 10 na podstawie wyboru przycisku radio/opcja.

Kliknij tutaj, aby pobrać szablon Mapy cieplnej

Tworzenie mapy cieplnej w tabeli przestawnej programu Excel

Formatowanie warunkowe w tabelach przestawnych działa tak samo jak w przypadku zwykłych danych.

ale jest coś ważnego, co musisz wiedzieć.

pozwól, że wezmę przykład i pokażę Ci.

Załóżmy, że masz tabelę przestawną, jak pokazano poniżej:

aby utworzyć mapę cieplną w tej tabeli przestawnej Excela:

• Wybierz komórki (B5:D14).
• przejdź do strony głównej- > Formatowanie warunkowe –> skala kolorów i wybierz skalę kolorów, którą chcesz zastosować.

spowoduje to natychmiastowe utworzenie mapy cieplnej w tabeli przestawnej.

problem z tą metodą polega na tym, że jeśli dodasz nowe dane do zaplecza 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 niego stosowane.

dzieje się tak, gdy zastosowaliśmy formatowanie warunkowe tylko do komórek B5:D14.

Jeśli chcesz, aby ta mapa cieplna była dynamiczna, tak aby aktualizowała się po dodaniu nowych danych, oto kroki:

• Wybierz komórki (B5:D14).
• przejdź do strony głównej- > Formatowanie warunkowe –> skala kolorów i wybierz skalę kolorów, którą chcesz zastosować.
• ponownie przejdź do strony głównej –> 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 pokazujące wartości „sprzedaży” Dla „daty” i „klienta”.

teraz formatowanie warunkowe będzie aktualizowane po zmianie danych zaplecza.

Uwaga: formatowanie warunkowe zniknie, jeśli zmienisz pola wiersza/kolumny. Na przykład, jeśli usuniesz pole daty i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.

Możesz również polubić następujące samouczki Excela:

• jak wyróżnić każdy inny wiersz w Excelu.
• Utwórz 100% Wykres kolumny stosu przy użyciu formatowania warunkowego.
• Jak liczyć kolorowe komórki w Excelu.
• jak wyróżnić puste komórki w programie Excel.
• Excel Sparklines-kompletny przewodnik