Note: The other languages of the website are Google-translated. Back to English

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

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 Niestandardowe zamówienie opcja po lewej stronie Wszystkie produkty w polu listy, a następnie wprowadź ;;; do Typ pole tekstowe, patrz zrzut ekranu:

6. Następnie kliknij OK przycisk, a wszystkie liczby są ukryte, jak pokazano na zrzucie ekranu:

Uwagi: 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 Kontrola 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:

=INDEX(data1!$B$1:$I$13,ROW(),$I$1+COLUMNS($B$1:B1)-1)

Uwagi: 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 ! 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 Kontrola 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:

Uwagi: 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 Kontrola 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;
  • Wybierać Skala 3-kolorowa z Styl formatu Lista rozwijana;
  • Wybierz Formuła Typ 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.

Uwagi: 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

Chcesz szybko i perfekcyjnie wykonywać swoją codzienną pracę? Kutools dla programu Excel oferuje zaawansowane zaawansowane funkcje 300 (łączenie skoroszytów, sumowanie według koloru, dzielenie zawartości komórek, konwersja daty itd.) I oszczędzanie 80% czasu.

  • Zaprojektowany z myślą o scenariuszach pracy 1500, pomaga rozwiązać 80% problemów z programem Excel.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 edycji wielu dokumentów.
  • Wprowadza wydajne zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (1)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
przepraszam, anuluję.
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje