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

在Excel中創建熱圖圖表

在Excel中,熱圖圖表看起來像一個表格,是一個直觀的表示形式,顯示了數據集的比較視圖。 如果工作表中的數據集很大,那麼一眼就能識別出較低或較高的值確實很困難,但是在熱圖中,單元格值以不同的顏色模式顯示,以便我們可以看到較大的數據或較小的數據,快速輕鬆地顯示,如下圖所示。


使用條件格式創建簡單的熱圖圖表

Excel中沒有直接的熱圖圖表實用程序,但是功能強大 條件格式 功能,您可以快速創建熱圖,請執行以下步驟:

1。 選擇要應用的數據范圍 條件格式.

2。 然後點擊 首頁 > 條件格式 > 色標,然後從右側展開的下拉菜單中選擇一種您需要的樣式(在這種情況下,我將選擇 綠色–黃色–紅色色標),請參見屏幕截圖:

3. 現在,將創建一個熱圖,該熱圖根據其值突出顯示單元格,綠色代表最高值,紅色代表最低值,其餘值顯示綠色和紅色之間的漸變顏色。 看截圖:

4。 如果要隱藏數字而只保留顏色,請選擇數據范圍,然後按 Ctrl + 1 鍵打開 單元格格式 對話框。

5。 在 單元格格式 對話框中的 標籤,點擊 習俗 左邊的選項 類別 列錶框,然後輸入 ;;; 類別 文本框,請參見屏幕截圖:

6。 然後,點擊 OK 按鈕,所有數字都被隱藏,如下圖所示:

備註:要用其他喜歡的顏色突出顯示單元格,請選擇數據范圍,然後單擊 首頁 > 條件格式 > 管理規則條件格式規則管理器 對話框。

然後,雙擊現有規則以打開 編輯格式規則 對話框,然後根據需要重置規則,請參見屏幕截圖:


在Excel中創建動態熱圖圖表

示例1:使用滾動條創建動態熱圖

如果工作表中有多列數據,但您希望在有限的空間中顯示它們,在這種情況下,您可以在工作表中插入滾動條以動態製作熱圖,如下面的演示所示。

要創建這種類型的動態熱圖,請執行以下步驟:

1。 插入一個新的工作表,然後將前幾月從原始工作表複製到該新工作表。

2。 然後,點擊 開發人員 > 插入 > 滾動條,請參見屏幕截圖:

3。 然後,拖動鼠標在復制的數據下方繪製一個滾動條,然後右鍵單擊該滾動條,然後選擇 格式控制,請參見屏幕截圖:

4。 在 格式化對象 對話框中的 控制 標籤,根據您的數據范圍設置最小值,最大值,增量更改,頁面更改和鏈接的單元格,如下圖所示:

5。 然後點擊 OK 關閉此對話框。

6。 現在,在此新工作表的單元格B1中,輸入以下公式,然後按 Enter 獲得第一個結果的關鍵:

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

備註:在以上公式中, data1!$ B $ 1:$ I $ 13 是原始表,其數據范圍不包括行標題(月), $ I $ 1 是滾動條鏈接的單元格, $ B $ 1:B1 是您輸出公式的單元格。

7。 然後,將此公式單元格拖到其餘單元格中,如果您只想在工作表中顯示3年,請將公式從B1拖到D13,請參見屏幕截圖:

8。 然後,應用 色階條件格式 功能到新的數據范圍以創建熱圖,現在,當您拖動滾動條時,熱圖將動態移動,請參見屏幕截圖:


示例2:使用單選按鈕創建動態熱圖

您還可以使用單選按鈕創建動態熱圖,選擇一個單選按鈕將突出顯示最大的n個值,選擇另一個單選按鈕將突出顯示最小的n個值,如下所示的演示:

要完成此類型的動態熱圖,請按以下步驟操作:

1。 點擊 開發人員 > 插入 > 選項按鈕(表單控件),然後拖動鼠標以繪製兩個單選按鈕,然後根據需要編輯文本,請參見屏幕截圖:

2。 插入單選按鈕後,右鍵單擊第一個,然後選擇 格式控制格式控制 對話框中的 控制 標籤,選擇一個與單選按鈕對齊的單元格,請參見屏幕截圖:

3。 點擊 OK 按鈕以關閉對話框,然後重複上述步驟(步驟2)以將第二個單選按鈕鏈接到同一單元格(單元格M1)。

4。 然後,您應該對數據范圍應用條件格式,請選擇數據范圍,然後單擊 首頁 > 條件格式 > 新規則,請參見屏幕截圖:

5。 在 新格式規則 對話框中選擇 使用公式來確定要格式化的單元格 來自 選擇規則類型 列錶框,然後輸入以下公式: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE))格式化此公式為真的值 文本框,然後單擊 格式 按鈕選擇一種顏色。 看截圖:

6。 點擊 OK 按鈕,當您選擇第一個單選按鈕時,它將以紅色突出顯示最大的15個值。

7。 要突出顯示最小的15個值,請保持選中數據並進入 新格式規則 對話框,然後鍵入以下公式: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE))格式化此公式為真的值 文本框,然後單擊 格式 按鈕選擇您需要的另一種顏色。 看截圖:

備註:在以上公式中, $ M $ 1 是單元格鏈接到單選按鈕, $ B $ 2:$ I $ 13 是您要應用條件格式的數據范圍, B2 是數據范圍的第一個單元格,即數字 15 是您要突出顯示的特定數字。

8。 點擊 OK 要關閉對話框,現在,當選擇第一個單選按鈕時,將突出顯示最大的15個值,而選擇第二個單選按鈕時,將突出顯示最小的15個值,如下圖所示:


示例3:使用複選框創建動態熱圖

在本節中,我將通過使用複選框來介紹動態熱圖圖表,該複選框可以幫助您根據需要顯示或隱藏熱圖。 如果選中該複選框,則將顯示熱圖;如果取消選中該複選框,它將立即被隱藏,請參見以下演示:

1。 首先,您應該將數據范圍轉換為表格格式,這可以幫助您在插入新數據行時自動應用條件格式。 選擇數據范圍,然後按 Ctrl + T 鑰匙一起打開 創建表格 對話框,請參見屏幕截圖:

2。 點擊 OK 關閉對話框,然後單擊 開發人員 > 插入 > 複選框(表單控件),然後拖動鼠標繪製一個複選框,然後根據需要編輯文本,如下所示的屏幕截圖:

3。 然後,右鍵單擊複選框,然後選擇 格式控制 格式化對象 對話框中的 控制 標籤,選擇一個與復選框對齊的單元格,請參見屏幕截圖:

4。 點擊 OK 關閉對話框,然後選擇要創建熱圖的數據范圍,然後單擊 首頁 > 條件格式 > 新規則新格式規則 對話框。

5。 在 新格式規則 對話框,請執行以下操作:

  • 選擇 根據其值格式化所有單元格 選項從 選擇規則類型 列錶框;
  • 選擇 3色刻度 來自 格式樣式 下拉列表;
  • 選擇 公式類別 下框 最低限度, 中點最大值 分別下拉列表;
  • 然後,將以下公式輸入到三個 文字框:
  • 最低配置: = IF($ M $ 1 = TRUE,MIN($ B $ 2:$ I $ 13),FALSE)
  • 中點: = IF($ M $ 1 = TRUE,AVERAGE($ B $ 2:$ I $ 13),FALSE)
  • 最大值: = IF($ M $ 1 = TRUE,MAX($ B $ 2:$ I $ 13),FALSE)
  • 然後,從 顏色 您需要的部分。

備註:在以上公式中, $ M $ 1 是鏈接到復選框的單元格, $ B $ 2:$ I $ 13 是您要應用條件格式的數據范圍。

6。 完成設置後,單擊 OK 按鈕關閉對話框,現在,當您選中復選框時,將顯示熱圖,否則將被隱藏。 參見下面的演示:


下載熱圖圖表樣本文件


視頻:在Excel中創建熱圖圖表


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速,完美地完成日常工作嗎? Kutools for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (1)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
對不起,我取消。
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0  字符
推薦地點