Excel2016數(shù)據(jù)透視表應(yīng)用大全

通過條件格式可以將數(shù)據(jù)透視表的重點(diǎn)數(shù)據(jù)快速標(biāo)識(shí)出來，提高報(bào)表的可讀性。通過對(duì)Excel2016中條件格式的“數(shù)據(jù)條”“圖標(biāo)集”和“色階”的應(yīng)用，可大大增強(qiáng)數(shù)據(jù)透視表的可視化效果。

突出顯示數(shù)據(jù)透視表中的特定數(shù)據(jù)

示例突出顯示“銷售額”未達(dá)“計(jì)劃目標(biāo)”值的產(chǎn)品名稱

圖5-40所示為某公司各店鋪的銷售額計(jì)劃目標(biāo)和現(xiàn)階段完成情況的數(shù)據(jù)透視表。若要突出顯示未達(dá)計(jì)劃目標(biāo)的店鋪，具體操作步驟如下。

圖5-40設(shè)置條件格式前的數(shù)據(jù)透視表

步驟1選中“店鋪名稱”字段的所有數(shù)據(jù)項(xiàng)，在【開始】選項(xiàng)卡中單擊【條件格式】下拉按鈕，選擇【新建規(guī)則】命令，如圖5-41所示。

圖5-41對(duì)數(shù)據(jù)透視表應(yīng)用條件格式

步驟2在彈出的【新建格式規(guī)則】對(duì)話框的【選擇規(guī)則類型】列表框中選擇【使用公式確定要設(shè)置格式的單元格】規(guī)則類型，在彈出的對(duì)話框中的【為符合此公式的值設(shè)置格式】下方的編輯框中輸入“=C3<b3”，單擊【格式】按鈕，如圖5-42所示。

圖5-42新建格式規(guī)則

步驟3在彈出的【設(shè)置單元格格式】對(duì)話框中選擇【填充】選項(xiàng)卡，在【背景色】顏色庫(kù)中選擇“紅色”，單擊【確定】按鈕關(guān)閉【設(shè)置單元格格式】對(duì)話框，再次單擊【確定】按鈕完成設(shè)置，如圖5-43所示。最終完成的數(shù)據(jù)透視表如圖5-44所示。

圖5-43新建格式規(guī)則

圖5-44突出顯示“銷售額”未達(dá)“計(jì)劃目標(biāo)”值的店鋪

數(shù)據(jù)透視表與“數(shù)據(jù)條”

將條件格式中“數(shù)據(jù)條”應(yīng)用于數(shù)據(jù)透視表，可幫助用戶查看某些項(xiàng)目之間的對(duì)比情況。“數(shù)據(jù)條”的長(zhǎng)度代表單元格中值的大小，越長(zhǎng)表示值越大，越短表示值越小。在觀察、比較大量數(shù)據(jù)時(shí)，此功能尤為有用，如圖5-45所示。

示例用數(shù)據(jù)條顯示銷售情況

以圖5-45所示的數(shù)據(jù)透視表為例，具體操作步驟如下。

步驟1選中數(shù)據(jù)透視表中“銷售吊牌額”字段下的任意一個(gè)單元格（如C2），在【數(shù)據(jù)】選項(xiàng)卡中單擊【降序】按鈕，完成對(duì)銷售吊牌額的降序排序，如圖5-46所示。

圖5-45數(shù)據(jù)透視表與數(shù)據(jù)條

圖5-46對(duì)數(shù)據(jù)透視表進(jìn)行排序

步驟2選中“銷售吊牌額”字段下的任意一個(gè)單元格（如C2），在【開始】選項(xiàng)卡中單擊【條件格式】按鈕，選擇【新建規(guī)則】命令，如圖5-47所示。

圖5-47調(diào)出【新建格式規(guī)則】對(duì)話框

步驟3在彈出的【新建格式規(guī)則】對(duì)話框中設(shè)置規(guī)則應(yīng)用范圍為【所有為“品牌”顯示“銷售品牌額”值的單元格】，在【格式樣式】下拉列表中選擇【數(shù)據(jù)條】選項(xiàng)，數(shù)據(jù)條的外觀設(shè)置為【漸變填充】，顏色設(shè)置為“紅色”，單擊【確定】按鈕即可完成設(shè)置，如圖5-48所示。

圖5-48設(shè)置條件格式的規(guī)則

用戶也可以在步驟2時(shí)通過選中數(shù)據(jù)透視表中C2:C6單元格區(qū)域，在【開始】選項(xiàng)卡中單擊【條件格式】按鈕，選擇【數(shù)據(jù)條】命令，在展開的【實(shí)心填充】列表框中選擇【紅色數(shù)據(jù)條】命令完成設(shè)置，如圖5-49所示。

圖5-49設(shè)置“數(shù)據(jù)條”條件格式

兩種方式設(shè)置的區(qū)別在于條件格式應(yīng)用范圍不同。前者當(dāng)數(shù)據(jù)透視表中“品牌”字段內(nèi)容增加時(shí)，條件格式設(shè)置可依據(jù)品牌的增加而自動(dòng)調(diào)整應(yīng)用范圍。后者只應(yīng)用于當(dāng)前所選定的區(qū)域，即C2:C6單元格區(qū)域，而品牌如果增加，則需要重新設(shè)置。

數(shù)據(jù)透視表與“圖標(biāo)集”

利用條件格式中的“圖標(biāo)集”顯示樣式可以將數(shù)據(jù)透視表的重點(diǎn)數(shù)據(jù)快速標(biāo)識(shí)出來，使數(shù)據(jù)透視表可讀性和專業(yè)性更強(qiáng)。

在圖5-50所示的數(shù)據(jù)透視表中應(yīng)用圖標(biāo)集對(duì)“售罄率”字段進(jìn)行了設(shè)置，售罄率值高于70%，顯示綠色

；售罄率值低于50%，顯示紅色

；售罄率值在50%～70%，不顯示圖標(biāo)。具體設(shè)置方法如下。

圖5-50應(yīng)用“圖標(biāo)集”的數(shù)據(jù)透視表

步驟1選中“售罄率”字段下的任意一個(gè)單元格（如D2），在【開始】選項(xiàng)卡中單擊【條件格式】按鈕，選擇【新建規(guī)則】命令，彈出【新建格式規(guī)則】對(duì)話框。

步驟2在【新建格式規(guī)則】對(duì)話框的【規(guī)則應(yīng)用于】下選中【所有為“品牌”顯示“售罄率”值的單元格】單選項(xiàng)按鈕，在【格式樣式】下拉列表中選中【圖標(biāo)集】，【根據(jù)以下規(guī)則顯示各個(gè)圖標(biāo)】具體設(shè)置為【類型】為“數(shù)字”，【值】為“0.7”，【圖標(biāo)】選擇“

”；【類型】為“數(shù)字”，【值】為“0.5”，【圖標(biāo)】設(shè)置為“無單元格圖標(biāo)”，將最后一個(gè)圖標(biāo)設(shè)置為“

”，單擊【確定】按扭完成設(shè)置，如圖5-51所示。

圖5-51設(shè)置圖標(biāo)集

數(shù)據(jù)透視表與“色階”

顏色漸變作為一種直觀的指示，可以幫助用戶了解數(shù)據(jù)的分布和變化。圖5-52所示的數(shù)據(jù)透視表中應(yīng)用了“綠-黃-紅色階”，在色階中，綠、黃和紅3種顏色的深淺漸變表示值的高低：較高值單元格的顏色更綠，中間值單元格的顏色更黃，而較小值則顯示紅色。

圖5-52設(shè)置色階后的數(shù)據(jù)透視表

選中數(shù)據(jù)透視表中需要應(yīng)用圖標(biāo)集的單元格區(qū)域（如C2:C6），在【開始】選項(xiàng)卡中單擊【條件格式】下拉按鈕，在選擇【色階】選項(xiàng)，在展開的列表中選擇“綠-黃-紅色階”，如圖5-53所示。

圖5-53設(shè)置色階

單擊如圖5-54所示的填充柄，可對(duì)條件格式應(yīng)用的區(qū)域進(jìn)行選擇。

圖5-54通過填充柄選擇條件格式應(yīng)用區(qū)域

修改數(shù)據(jù)透視表中應(yīng)用的條件格式

如果用戶希望對(duì)數(shù)據(jù)透視表中已經(jīng)應(yīng)用的條件格式進(jìn)行修改，可以通過使用【條件格式規(guī)則管理器】來進(jìn)行，具體操作步驟如下。

步驟1選中數(shù)據(jù)透視表中的任意一個(gè)單元格（如B2），在【開始】選項(xiàng)卡中單擊【條件格式】下拉按鈕，選擇【管理規(guī)則】命令，如圖5-55所示。

圖5-55打開【條件格式規(guī)則管理器】的操作步驟

步驟2在彈出的【條件格式規(guī)則管理器】對(duì)話框中選擇需要編輯的條件格式規(guī)則，然后單擊【編輯規(guī)則】按鈕，彈出【編輯格式規(guī)則】對(duì)話框，用戶可以在對(duì)話框內(nèi)對(duì)已經(jīng)設(shè)置的條件格式進(jìn)行修改，如圖5-56所示。

圖5-56打開【編輯格式規(guī)則】對(duì)話框

若是同時(shí)在【條件格式規(guī)則管理器】中設(shè)有多個(gè)規(guī)則，可以通過【刪除規(guī)則】按鈕右側(cè)的【上移】和【下移】?jī)蓚€(gè)按鈕來調(diào)整不同規(guī)則的優(yōu)先級(jí)，如圖5-57所示。單擊【刪除規(guī)則】按鈕，可以將當(dāng)前已經(jīng)選定的條件格式規(guī)則刪除，如圖5-58所示。

圖5-57調(diào)整不同規(guī)則的優(yōu)先級(jí)

圖5-58刪除已經(jīng)設(shè)定的條件格式規(guī)則

此外，也可以利用【清除規(guī)則】子菜單中的各種選項(xiàng)來刪除條件格式規(guī)則，如圖5-59所示。

圖5-59利用【清除規(guī)則】命令清除已經(jīng)設(shè)定的條件格式規(guī)則