Utwórz wykres mapy ciepła w programie Excel
W programie Excel wykres mapy cieplnej wygląda jak tabela, która jest wizualną reprezentacją pokazującą porównawczy widok zbioru danych. Jeśli w twoim arkuszu znajduje się duży zestaw danych, naprawdę trudno jest zidentyfikować niższe lub wyższe wartości na pierwszy rzut oka, ale na mapie ciepła wartość komórki jest pokazana w innym wzorze kolorów, abyśmy mogli zobaczyć większe dane lub mniejsze dane szybko i łatwo, jak pokazano poniżej.
- Utwórz prosty wykres mapy cieplnej z formatowaniem warunkowym
- Utwórz dynamiczny wykres mapy ciepła w programie Excel
- Przykład 1: Utwórz dynamiczną mapę cieplną za pomocą paska przewijania
- Przykład 2: Utwórz dynamiczną mapę cieplną za pomocą przycisków radiowych
- Przykład 3: Utwórz dynamiczną mapę cieplną za pomocą pola wyboru
- Pobierz przykładowy plik mapy ciepła
- Wideo: Utwórz wykres mapy ciepła w programie Excel
Utwórz prosty wykres mapy cieplnej z formatowaniem warunkowym
W programie Excel nie ma bezpośredniego narzędzia do tworzenia mapy ciepła, ale z potężnym Formatowanie warunkowe funkcję, możesz szybko utworzyć mapę cieplną, wykonaj następujące czynności:
1. Wybierz zakres danych, do którego chcesz zastosować Formatowanie warunkowe.
2. A następnie kliknij Strona główna > Formatowanie warunkowe > Skale kolorów, a następnie wybierz jeden styl, którego potrzebujesz z rozwijanego menu po prawej stronie (w tym przypadku wybiorę Skala kolorów zielono-żółto-czerwona) patrz zrzut ekranu:
3. Teraz tworzona jest mapa cieplna, która wyróżnia komórki na podstawie ich wartości, kolor zielony reprezentuje najwyższe wartości, a kolor czerwony przedstawia najniższe wartości, a pozostałe wartości mają kolor gradientu między zielonym a czerwonym. Zobacz zrzut ekranu:
4. Jeśli chcesz ukryć cyfry i pozostawić tylko kolory, wybierz zakres danych i naciśnij Ctrl + 1 klawisze, aby otworzyć Formatowanie komórek okno dialogowe.
5, w Formatowanie komórek okno dialogowe w obszarze Numer tab, kliknij Zamówienia Indywidualne opcja po lewej stronie Kategoria w polu listy, a następnie wprowadź ;;; do Rodzaj Nieruchomości pole tekstowe, patrz zrzut ekranu:
6. Następnie kliknij OK przycisk, a wszystkie liczby są ukryte, jak pokazano na zrzucie ekranu:
Note: Aby podświetlić komórki innymi kolorami, które lubisz, wybierz zakres danych, a następnie kliknij Strona główna > Formatowanie warunkowe > Zarządzaj regułami , aby przejść do Menedżer reguł formatowania warunkowego okno dialogowe.
Następnie kliknij dwukrotnie istniejącą regułę, aby otworzyć plik Edytuj regułę formatowania okno dialogowe, a następnie zresetuj regułę do swoich potrzeb, zobacz zrzut ekranu:
Utwórz dynamiczny wykres mapy ciepła w programie Excel
Przykład 1: Utwórz dynamiczną mapę cieplną za pomocą paska przewijania
Jeśli w arkuszu znajduje się wiele kolumn danych, ale chcesz je wyświetlić w ograniczonej przestrzeni, w takim przypadku możesz wstawić pasek przewijania do arkusza, aby dynamicznie utworzyć mapę cieplną, jak pokazano poniżej.
Aby utworzyć ten typ dynamicznego wykresu mapy ciepła, wykonaj następujące czynności:
1. Wstaw nowy arkusz, a następnie skopiuj pierwszą kolumnę miesięcy z oryginalnego arkusza do tego nowego arkusza.
2. Następnie kliknij Deweloper > wstawka > Pasek przewijaniazobacz zrzut ekranu:
3. Następnie przeciągnij myszą, aby narysować pasek przewijania pod skopiowanymi danymi, kliknij prawym przyciskiem myszy pasek przewijania i wybierz Kontrola formatuzobacz zrzut ekranu:
4, w Formatuj obiekt okno dialogowe w obszarze Control ustaw wartość minimalną, maksymalną, zmianę przyrostową, zmianę strony i połączoną komórkę w oparciu o zakres danych, jak pokazano poniżej:
5. Następnie kliknij OK aby zamknąć to okno dialogowe.
6. Teraz w komórce B1 tego nowego arkusza wprowadź następującą formułę i naciśnij Wchodzę klucz do uzyskania pierwszego wyniku:
Note: W powyższym wzorze, data1! $ B $ 1: $ I $ 13 to oryginalny arkusz z zakresem danych bez nagłówka wiersza (miesiące), 1 $ XNUMX $ to komórka, z którą łączy się pasek przewijania, 1 B $: B1 to komórka, w której wyprowadzasz formułę.
7. Następnie przeciągnij tę komórkę z formułą do pozostałych komórek, jeśli chcesz pokazać tylko 3 lata w arkuszu, przeciągnij formułę z B1 do D13, zobacz zrzut ekranu:
8. A następnie zastosuj Skala kolorów ukończenia Formatowanie warunkowe funkcja do nowego zakresu danych, aby utworzyć mapę cieplną, teraz po przeciągnięciu paska przewijania mapa ciepła zostanie przeniesiona dynamicznie, patrz zrzut ekranu:
Przykład 2: Utwórz dynamiczną mapę cieplną za pomocą przycisków radiowych
Możesz również utworzyć dynamiczną mapę ciepła za pomocą przycisków opcji, wybierz jeden przycisk opcji, aby podświetlić największe n wartości, a wybranie innego przycisku opcji podświetli najmniejsze n wartości, jak pokazano poniżej:
Aby zakończyć dynamiczną mapę cieplną tego typu, wykonaj następujące czynności:
1. Kliknij Deweloper > wstawka > Przycisk opcji (kontrolka formularza), następnie przeciągnij myszą, aby narysować dwa przyciski opcji i edytuj tekst zgodnie z potrzebami, patrz zrzut ekranu:
 |
 |
 |
2. Po wstawieniu przycisków opcji kliknij pierwszy z nich prawym przyciskiem myszy i wybierz Kontrola formatu, W Kontrola formatu okno dialogowe w obszarze Control tab, wybierz komórkę, która jest wyłożona przyciskiem opcji, zobacz zrzut ekranu:
3. Kliknij OK aby zamknąć okno dialogowe, a następnie powtórz powyższy krok (krok 2), aby połączyć drugi przycisk opcji z tą samą komórką (komórka M1).
4. Następnie powinieneś zastosować formatowanie warunkowe dla zakresu danych, wybierz zakres danych i kliknij Strona główna > Formatowanie warunkowe > Nowa zasadazobacz zrzut ekranu:
5, w Nowa reguła formatowania okno dialogowe, wybierz Użyj formuły, aby określić, które komórki sformatować z Wybierz typ reguły pole listy, a następnie wprowadź następującą formułę: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE)) do Formatuj wartości tam, gdzie ta formuła jest prawdziwa pole tekstowe, a następnie kliknij utworzony aby wybrać kolor. Zobacz zrzut ekranu:
6. Kliknij OK przycisk, spowoduje to podświetlenie największych 15 wartości kolorem czerwonym po wybraniu pierwszego przycisku opcji.
7. Aby zaznaczyć najmniejsze 15 wartości, zachowaj wybrane dane i przejdź do Nowa reguła formatowania okno dialogowe, a następnie wpisz tę formułę: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE)) do Formatuj wartości tam, gdzie ta formuła jest prawdziwa pole tekstowe i kliknij utworzony , aby wybrać inny potrzebny kolor. Zobacz zrzut ekranu:
Note: W powyższych wzorach 1 mln USD czy komórka jest połączona z przyciskami radiowymi, 2 $ B $: I $ 13 to zakres danych, dla którego chcesz zastosować formatowanie warunkowe, B2 jest pierwszą komórką zakresu danych, liczbą 15 to konkretna liczba, którą chcesz wyróżnić.
8. Kliknij OK aby zamknąć okno dialogowe, teraz, po wybraniu pierwszego przycisku opcji, największych 15 wartości zostanie podświetlonych, a po wybraniu drugiego przycisku opcji, najmniejsze 15 wartości zostanie podświetlonych, jak pokazano poniżej:
Przykład 3: Utwórz dynamiczną mapę cieplną za pomocą pola wyboru
W tej sekcji wprowadzę dynamiczny wykres mapy ciepła, używając pola wyboru, które może pomóc w wyświetleniu lub ukryciu mapy ciepła w zależności od potrzeb. Jeśli zaznaczysz to pole wyboru, mapa cieplna zostanie wyświetlona, jeśli odznacz pole wyboru, zostanie od razu ukryta, zobacz poniższe demo:
1. Najpierw należy przekonwertować zakres danych na format tabeli, który może pomóc w automatycznym zastosowaniu formatowania warunkowego podczas wstawiania nowego wiersza danych. Wybierz zakres danych, a następnie naciśnij Ctrl + T klucze razem, aby otworzyć Utwórz tabelę okno dialogowe, zobacz zrzut ekranu:
2. Kliknij OK aby zamknąć okno dialogowe, a następnie kliknij Deweloper > wstawka > Pole wyboru (kontrolka formularza), następnie przeciągnij myszą, aby narysować pole wyboru i edytuj tekst zgodnie z potrzebami, jak pokazano poniżej zrzuty ekranu:
 |
|
 |
3. Następnie kliknij prawym przyciskiem myszy pole wyboru i wybierz Kontrola formatu, W Formatuj obiekt okno dialogowe w obszarze Control kartę, wybierz komórkę, która jest wyłożona do pola wyboru, zobacz zrzut ekranu:
4. Kliknij OK aby zamknąć okno dialogowe, wybierz zakres danych, dla którego chcesz utworzyć mapę cieplną, i kliknij Strona główna > Formatowanie warunkowe > Nowa zasada , aby przejść do Nowa reguła formatowania okno dialogowe.
5, w Nowa reguła formatowania w oknie dialogowym, wykonaj następujące operacje:
- Wybierz Sformatuj wszystkie komórki na podstawie ich wartości opcja z Wybierz typ reguły skrzynka na listy;
- Dodaj Skala 3-kolorowa z Styl formatu Lista rozwijana;
- Wybierz Formuła Rodzaj Nieruchomości pudełka pod Minimum, Punkt środkowy i Maksymalny listy rozwijane osobno;
- A następnie wprowadź następujące formuły do trzech wartość pola tekstowe:
- minimalna: = JEŻELI ($ M 1 $ = PRAWDA, MIN (2 $ B $: I $ 13), FAŁSZ)
- Punkt środkowy: = JEŻELI ($ M $ 1 = PRAWDA, ŚREDNIA ($ B $ 2: $ I $ 13), FAŁSZ)
- Maksymalny: = JEŻELI ($ M 1 $ = PRAWDA, MAKS (2 $ B $: I $ 13 $), FAŁSZ)
- Następnie określ kolory podświetlenia z Kolor sekcja do twoich potrzeb.
Note: W powyższych wzorach 1 mln USD to komórka połączona z polem wyboru, 2 $ B $: I $ 13 to zakres danych, dla którego chcesz zastosować formatowanie warunkowe.
6. Po zakończeniu ustawień kliknij OK przycisk, aby zamknąć okno dialogowe, teraz po zaznaczeniu pola wyboru mapa ciepła zostanie wyświetlona, w przeciwnym razie zostanie ukryta. Zobacz poniżej demo:
Pobierz przykładowy plik mapy ciepła
Wideo: Tworzenie wykresu mapy ciepła w programie Excel
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
