跳到主要內容

在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中創建熱圖圖表


最佳辦公效率工具


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

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的選項卡,就像 Chrome、Edge 和 Firefox 一樣。
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
sorry,I cancel.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations