辦公自動化高級應(yīng)用案例教程（Office 2016）（第2版） 課件 項目10、11 最優(yōu)方案設(shè)計、制作交互式分析面板

演講人2023-08-18項目10最優(yōu)方案設(shè)計最優(yōu)方案設(shè)計01項目背景02EXCEL數(shù)據(jù)建模過程04規(guī)劃求解05項目簡介03目錄01最優(yōu)方案設(shè)計最優(yōu)方案設(shè)計02項目背景項目背景在企業(yè)生產(chǎn)管理經(jīng)營決策過程中，如何在既有條件下，充分考慮現(xiàn)有人力、物力、財力和各項限制條件，取得最大的經(jīng)營效益，即達(dá)到產(chǎn)量最高、花費最小、收益最高，毫無疑問，給出最優(yōu)方案，在輔助企業(yè)經(jīng)營決策中有重要的作用。小張負(fù)責(zé)生產(chǎn)過程排產(chǎn)工作，現(xiàn)接到了一個新的生產(chǎn)任務(wù)，需要生產(chǎn)甲和乙產(chǎn)兩種產(chǎn)品，根據(jù)已有數(shù)據(jù)可知，甲產(chǎn)品每件利潤為150元，生產(chǎn)單件產(chǎn)品工人的工時數(shù)為2個工時，需要耗費的原材料為3分，車床耗時5小時；乙產(chǎn)品每件利潤為210元，生產(chǎn)單件產(chǎn)品工人的工時數(shù)為3個工時，需要耗費的原材料為4份，車床上耗時也為5小時。根據(jù)任務(wù)要求，工人的總工時數(shù)不能超過100工時，原材料不能超過120份，并且車床總的運行時間不能超過150小時，小張分別生產(chǎn)多少件甲、乙兩種產(chǎn)品，才能使得利潤最大呢？項目背景這么復(fù)雜的條件，僅憑直觀是很難找到最優(yōu)方案的，我們可以利用Excel的“規(guī)劃求解”，快速得到最優(yōu)方案。03項目簡介項目簡介“規(guī)劃求解”是Excel中的一個加載宏，借助“規(guī)劃求解”，可以求得工作表上某個單元格（被稱為目標(biāo)單元格）中公式結(jié)果的最優(yōu)值?！耙?guī)劃求解”通過調(diào)整與目標(biāo)單元格直接或間接相關(guān)的一組單元格（稱為可變單元格）中的數(shù)據(jù)，以期在目標(biāo)單元格公式中獲得期望的結(jié)果，比如獲得目標(biāo)單元格的最大值、最小值或指定值（稱為目標(biāo)函數(shù)）。在用“規(guī)劃求解”獲取最優(yōu)方案過程中，必須首先對數(shù)據(jù)進(jìn)行建模，在創(chuàng)建模型過程中，可以對可變單元格的數(shù)值應(yīng)用“約束條件”。“約束條件”是指“規(guī)劃求解”過程中設(shè)置的限制條件，可將約束條件應(yīng)用于可變單元格、目標(biāo)單元格或其他與目標(biāo)單元格直接或間接相關(guān)的單元格，約束條件可用與目標(biāo)相關(guān)的等式或不等式表示。04EXCEL數(shù)據(jù)建模過程EXCEL數(shù)據(jù)建模過程采用Excel線性規(guī)劃求解問題，我們需要設(shè)計一個工作表，將問題中的相關(guān)系數(shù)填入該工作表之中，這個過程也叫“線性規(guī)劃數(shù)據(jù)建模”，可按照如下過程來完成數(shù)據(jù)的建模：（1）確定目標(biāo)函數(shù)存放的單元格，此案例中目標(biāo)函數(shù)為

，將其系數(shù)輸入目標(biāo)單元格之中，此項目的系數(shù)為150和210；（2）確定決策變量存放的單元格，可在其輸入任何一組數(shù)據(jù)。此案例的決策變量為甲、乙產(chǎn)品的生產(chǎn)數(shù)量和，預(yù)先輸入數(shù)據(jù)1，以便檢驗接下來的運算是否正確。（3）確定約束條件左端各項系數(shù)存放的單元格，并在單元格中輸入左端各項系數(shù)。根據(jù)數(shù)學(xué)模型，第一項約束條件的系數(shù)為2,3；第二項約束條件系數(shù)為3,4；第三項約束條件的系數(shù)為5,5；第四項約束條件無系數(shù)，不用填寫。（4）在約束條件左端項系數(shù)存放單元格右邊的單元格中輸入約束條件左端項的計算公式，計算出約束條件左端項對應(yīng)于目前決策變量的函數(shù)值，可用Excel的SUMPRODUCT函數(shù)實現(xiàn)。SUMPRODUCT函數(shù)是在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。語法形式為SUMPRODUCT(array1,[array2],[array3],...)，數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)將返回錯誤值#VALUE!。EXCEL數(shù)據(jù)建模過程（5）在第（4）步完成后的右側(cè)單元格右邊輸入約束條件右端項，此處分別為常數(shù)100,120和150。建模后的表格如REF_Ref33263793\h圖10-2所示。E6單元格公式為：=C3*C6+D3*D6或=SUMPRODUCT(C$3:D$3,C6:D6)，第一個參數(shù)采用絕對引用，方便E7和E8單元格拖動填充。E7單元格公式為：=C3*C7+D3*D7或=SUMPRODUCT(C$3:D$3,C7:D7)E8單元格公式為：=C3*C8+D3*D8或=SUMPRODUCT(C$3:D$3,C8:D8)C10單元格公式為：=C2*C3+D2*D3或=SUMPRODUCT(C2:D2,C3:D3)EXCEL數(shù)據(jù)建模過程logo數(shù)據(jù)建模表格05規(guī)劃求解規(guī)劃求解安裝Office的時候，默認(rèn)不會安裝“規(guī)劃求解”宏，需要用戶根據(jù)自己需要選擇安裝。加載“規(guī)劃求解”宏在已建模的Excel文件中，切換到【文件】，選擇【選項】命令，彈出“Excel選項”對話框，選擇左側(cè)【加載項】項目，在“管理”列表中選擇【Excel加載項】，點擊【轉(zhuǎn)到】按鈕接下來在“加載宏”對話框中，勾選【規(guī)劃求解加載項】前的復(fù)選框，點擊【確定】按鈕，如所示。加載“規(guī)劃求解”宏加載成功后，在Excel的【數(shù)據(jù)】選項卡下，顯示【規(guī)劃求解】圖標(biāo)加載“規(guī)劃求解”宏logo“規(guī)劃求解”圖標(biāo)設(shè)置規(guī)劃求解參數(shù)當(dāng)加載“規(guī)劃求解”宏完畢后，即可以使用“規(guī)劃求解”功能，設(shè)置各項參數(shù)，找到最優(yōu)方案。在數(shù)據(jù)建模Excel文件中，切換到【數(shù)據(jù)】選項卡，點擊【規(guī)劃求解】圖標(biāo)，彈出“規(guī)劃求解參數(shù)”設(shè)置對話框，設(shè)置目標(biāo)單元格、通過可更改可變單元格（決策變量），選擇最大、最小或固定值，設(shè)置約束條件等。本項目要求取得最大值，目標(biāo)單元格為C10，決策變量單元格為C3:D3，設(shè)置后的參數(shù)如圖所示。設(shè)置規(guī)劃求解參數(shù)規(guī)劃求解參數(shù)設(shè)置添加約束條件在“規(guī)劃求解參數(shù)”對話框，點擊“遵守約束”旁的【添加】按鈕，添加約束條件。添加“生產(chǎn)產(chǎn)品總工時不超過100個工時”的條件限制。鼠標(biāo)定位“添加約束”對話框中的“單元格引用”的文本框中，用鼠標(biāo)點擊E6單元格，“約束”文本框中選擇F6，在關(guān)系符號下拉表中選擇“<=”，點擊“確定”按鈕完成當(dāng)前條件添加，或者點擊“添加”按鈕繼續(xù)添加新的約束條件。添加約束條件

添加生產(chǎn)工時不超過100約束添加耗費原材料總份數(shù)不超過120份的約束?！皢卧褚谩边x擇E7單元格，“約束”選擇F7單元格，關(guān)系選擇“<=”添加約束條件添加原材料總數(shù)不超過120約束添加車床總時間不超過150小時約束?！皢卧褚谩边x擇E8單元格，“約束”選擇F8單元格，關(guān)系選擇“<=”添加約束條件添加車床時間不超過150小時約束添加約束條件

添加生產(chǎn)數(shù)量大于等于0約束?！皢卧褚谩边x擇“C3:D3”單元格，關(guān)系選擇“>=”，“約束”寫0，表示生產(chǎn)量不為負(fù)數(shù)添加約束條件“單元格引用”選擇C3:D3單元格，中間關(guān)系處選擇“int”，點擊【確定】按鈕。添加生產(chǎn)數(shù)量為整數(shù)約束。添加約束條件規(guī)劃求解參數(shù)設(shè)置結(jié)果添加約束條件在“規(guī)劃求解參數(shù)”對話框中，點擊【求解】按鈕，彈出“規(guī)劃求解結(jié)果”對話框，選擇“保留規(guī)劃求解的解”，選中【運算結(jié)果報告】，點擊【確定】按鈕添加約束條件規(guī)劃求解結(jié)果分析規(guī)劃結(jié)果報告打開“運算結(jié)果報告1”工作簿，從運算結(jié)果看出以下信息：（1）“目標(biāo)單元格（最大值）”目標(biāo)函數(shù)值從“初值”360變成了“終值”6300；（2）“可變單元格”列出了兩種產(chǎn)品生產(chǎn)量的初值和終值。（3）“約束”列表中，生產(chǎn)產(chǎn)品的工時數(shù)未達(dá)到限值，還有10個工時未使用，原材料份數(shù)和生產(chǎn)車間時間已達(dá)到限值。（4）發(fā)現(xiàn)甲產(chǎn)品在方案中未安排生產(chǎn)，可能與實際并不相符，在實際生產(chǎn)過程中，除了僅考慮利潤因素之外，還需考慮產(chǎn)品的最低生產(chǎn)要求。結(jié)果:規(guī)劃求解找到一解，可滿足所有的約束及最優(yōu)狀況。

規(guī)劃求解引擎

..................

目標(biāo)單元格(最大值)

單元格名稱初值終值

$C$10目標(biāo)函數(shù)值3606300

可變單元格

單元格名稱初值終值整數(shù)

$C$3決策變量10整數(shù)

$D$3決策變量130整數(shù)

約束

單元格名稱單元格值公式狀態(tài)型數(shù)值

$E$6左端系數(shù)約束條件左端項的值90$E$6<=$F$6未到限制值10

$E$7單件耗費原材料份數(shù)約束條件左端項的值120$E$7<=$F$7到達(dá)限制值0

$E$8單件產(chǎn)品車床時間約束條件左端項的值150$E$8<=$F$8到達(dá)限制值0

$C$3決策變量0$C$3>=0到達(dá)限制值0

$D$3決策變量30$D$3>=0未到限制值30

$C$3:$D$3=整數(shù)

拓展訓(xùn)練-利用“規(guī)劃求解”確定最優(yōu)生產(chǎn)方案公司新上三種產(chǎn)品生產(chǎn)，經(jīng)前期數(shù)據(jù)統(tǒng)計得知，生產(chǎn)產(chǎn)品1、產(chǎn)品2和產(chǎn)品3的成本分別為55元、85元和115元，每生產(chǎn)一種產(chǎn)品的時間耗費分別為3分鐘、6分鐘和7分鐘，銷售一件產(chǎn)品分別獲利50元、70元和95元?，F(xiàn)研究決定，企業(yè)為這三種產(chǎn)品最多投入15000元的材料經(jīng)費，機(jī)器每天運轉(zhuǎn)時間不超過15個小時，同時，根據(jù)市場需求調(diào)研，產(chǎn)品1每天產(chǎn)量不得少于60件，產(chǎn)品2不得少于45件，產(chǎn)品3每天不得少于60件，現(xiàn)要求設(shè)計出最優(yōu)的生產(chǎn)方案，即根據(jù)現(xiàn)有的生產(chǎn)條件和市場需求如何分配三種產(chǎn)品的生產(chǎn)比例才能獲得最大的利潤。解決思路：解決思路：將產(chǎn)品1、產(chǎn)品2和產(chǎn)品3的產(chǎn)量以

代表，列出目標(biāo)函數(shù)和限制條件，以便統(tǒng)一檢查條件是否滿足。（1）列出目標(biāo)函數(shù)和限制條件，建立數(shù)學(xué)模型拓展訓(xùn)練-利用“規(guī)劃求解”確定最優(yōu)生產(chǎn)方案（2）在Excel中建立模型根據(jù)數(shù)據(jù)建模過程，在Excel中建立模型設(shè)置“規(guī)劃求解參數(shù)”logo“規(guī)劃求解參數(shù)”拓展訓(xùn)練—利用“規(guī)劃求解”最低運輸費用現(xiàn)有北京、武漢、成都三地生產(chǎn)某種商品，需要運往甲、乙、丙、丁四個地方銷售，各產(chǎn)地的產(chǎn)量、各地的需求量，以及從產(chǎn)地運送單一貨品到各地方銷售的單位貨物運輸費用如下表所示?，F(xiàn)需要物流公司需要設(shè)計既滿足各地銷量需求，又滿足產(chǎn)量要求，同時運輸成本最低的方案。即設(shè)計一種方案，將產(chǎn)品從3個產(chǎn)地送到4個銷售地，滿足銷量和需求，又使運費最低。解決思路：目的地產(chǎn)地甲乙丙丁產(chǎn)量北京41241116武漢2103910成都8511822需求量8141214理清規(guī)劃求解所需關(guān)鍵參數(shù)約束條件：運往各銷售地的數(shù)量不能超過產(chǎn)量，同時不能低于需求量，為簡便起見，約定從產(chǎn)地運往銷售地的貨物數(shù)量為整數(shù)；目標(biāo)函數(shù)：從產(chǎn)地運往各銷售地的運費。建立EXCEL數(shù)據(jù)模型建立如下的EXCEL模型，其中黑體部分標(biāo)明了相應(yīng)單元格所需要的公式和要求設(shè)置規(guī)劃求解參數(shù)選擇目標(biāo)單元格到“最小值”，設(shè)置決策變量單元格，添加約束條件后的規(guī)劃求解參數(shù)分析規(guī)劃結(jié)果報告查看規(guī)劃求解為表格所填寫的決策變量，以及目標(biāo)函數(shù)（即最低運費）數(shù)據(jù)分析運算結(jié)果報告。拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問題某工廠要用四種合金T1，T2，T3和T4為原料，經(jīng)熔煉成為一種新的不銹鋼G，這四種原料含有元素鉻（Cr）、錳（Mn）和鎳（Ni）的含量（%）、單價以及新的不銹鋼材料G所要求的Cr，Mn和Ni的最低含量（%）如下表所示。設(shè)熔煉時重量沒有損耗，要熔煉成100公斤不銹鋼G，應(yīng)選用原料T1，T2，T3和T4各多少公斤，使成本最小。問題思路：（1）建立數(shù)學(xué)模型（2）建立Excel模型參照10.1建立Excel模型T1T2T3T4GCr3.214.532.191.763.20Mn2.041.123.574.332.10Ni5.823.064.272.734.30單價（元/公斤）115978276拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問題配料問題EXCEL建模拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問題配料問題規(guī)劃求解參數(shù)設(shè)置拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問題配料問題規(guī)劃求解結(jié)果查看分析結(jié)果報告，分析能否通過調(diào)整約束條件或數(shù)據(jù)，再次規(guī)劃求解，使得成本更低。謝謝11

EXCEL數(shù)據(jù)可視化—制作交互式數(shù)據(jù)分析面板01EXCEL數(shù)據(jù)可視化—制作交互式數(shù)據(jù)分析面板內(nèi)容導(dǎo)圖02項目背景項目背景現(xiàn)有某商品經(jīng)營公司，有9個銷售門店，分別位于城東、城西、城北和城南區(qū)域，經(jīng)營近90種商品，3中類別，5個等級，表中收集了每個銷售發(fā)生的時間、購買客戶信息、產(chǎn)品和價格信息、銷售員及銷售信息近1萬條?，F(xiàn)公司銷售經(jīng)理希望通過數(shù)據(jù)的統(tǒng)計、分析，挖掘客戶的消費特征和產(chǎn)品的銷售情況。部分示例數(shù)據(jù)如下：日期客戶編號客戶姓名性別門店編號門店名稱門店區(qū)域產(chǎn)品名稱產(chǎn)品編號產(chǎn)品種類產(chǎn)品等級產(chǎn)品單價購買數(shù)量銷售人員購買金額2019/1/1C0165客戶165女ST8門店8城東產(chǎn)品44PROC44類別2三級8508銷售員868002019/1/1C0208客戶208女ST1門店1城南產(chǎn)品76PROC76類別1二級10002銷售員120002019/1/1C0649客戶649男ST7門店7城西產(chǎn)品38PROC38類別2四級7207銷售員75040.................................................03項目簡介項目簡介為從不同角度對數(shù)據(jù)進(jìn)行解讀，利用EXCEL強(qiáng)大的分析功能，以圖、表等形式展示數(shù)據(jù)所蘊(yùn)含的規(guī)律或趨勢，可將多個圖、表以“面板”形式集中在一起，提供一定的交互性，為用戶提供集中的數(shù)據(jù)查看界面。本項目采用EXCEL數(shù)據(jù)透視圖/表功能，只需使用鼠標(biāo)拖放，無需編程，即可快速建立交互性強(qiáng)、使用方便的數(shù)據(jù)面板。本面板制作按照以下過程完成：明確數(shù)據(jù)分析目標(biāo)從領(lǐng)導(dǎo)、同事等角度，了解數(shù)據(jù)分析需要達(dá)到的目標(biāo)，需要從哪些維度了解分析結(jié)果，也可從數(shù)據(jù)本身出發(fā)，探索可從數(shù)據(jù)中獲得哪些分析內(nèi)容。準(zhǔn)備數(shù)據(jù)從多角度準(zhǔn)備數(shù)據(jù)，構(gòu)思以何種圖表以滿足目標(biāo)需求，同時應(yīng)檢查數(shù)據(jù)的準(zhǔn)確性與完整性、規(guī)范性，否則，一旦基礎(chǔ)數(shù)據(jù)錯誤，將導(dǎo)致分析結(jié)果錯誤。繪制儀表盤草圖從目標(biāo)出發(fā)，通過任何簡單快速的方式繪制草圖，如果有必要，可就此草圖與需求方（領(lǐng)導(dǎo)、同事）等進(jìn)行溝通，講解展示思路和方法，以迅速調(diào)整面板內(nèi)容和結(jié)構(gòu)。制作面板通過透視圖/透視表、切片器、匯總圖表等技術(shù)手段，快速完成儀表盤的制作檢查、美化儀表盤檢查切片器的數(shù)據(jù)關(guān)聯(lián)是否正確，規(guī)整頁面排版，美化圖表設(shè)置，設(shè)置打印規(guī)格與打印預(yù)覽等發(fā)布文件設(shè)置中間數(shù)據(jù)的隱藏與保護(hù)，測試儀表盤，最后發(fā)布文件。結(jié)合數(shù)據(jù)，總結(jié)出來將要完成的分析要求和目標(biāo)、采用的圖表如下：分析方向從業(yè)務(wù)角度：需要分析的內(nèi)容和目標(biāo)展示形式及圖表銷售概況銷售總量、銷售總額、任務(wù)完成率、銷售趨勢KPI、趨勢圖產(chǎn)品分析銷售了哪些類別的產(chǎn)品、每類產(chǎn)品的銷量、哪些產(chǎn)品最暢銷柱形圖、產(chǎn)品TOP10客戶分析客戶的性別統(tǒng)計、來自于哪些區(qū)域、最大客戶的特征餅圖、圓環(huán)圖、客戶TOP10門店分析各門店銷售情況、各門店各類別產(chǎn)品銷售情況條形圖、切片器篩選、訂單分析訂單金額分布情況按訂單金額分級別統(tǒng)計04繪制面板草圖繪制面板草圖根據(jù)分析目標(biāo)，結(jié)合個人喜好，采用手工或者電腦繪制儀表盤布局草圖。根據(jù)一般儀表盤的操作和布局，現(xiàn)規(guī)劃出本案例的儀表盤草圖如下：【小貼士】根據(jù)注意力矩陣，注意力集中關(guān)注的重要性依次為圖表1>圖表2、4、5>圖表3、7、8>圖表9，讀者可將重要的內(nèi)容置于關(guān)注區(qū)域高的位置。05數(shù)據(jù)準(zhǔn)備數(shù)據(jù)準(zhǔn)備針對不同的數(shù)據(jù)來源可采用不同的數(shù)據(jù)整合方法，本案例僅介紹數(shù)據(jù)來源于單一的數(shù)據(jù)表，若數(shù)據(jù)來源于不同數(shù)據(jù)表，可參考本章拓展訓(xùn)練部分內(nèi)容。另外，為了確保在數(shù)據(jù)源改變后，面板的分析結(jié)果能自動更新，應(yīng)采用適當(dāng)?shù)姆椒?，確保在制作圖表之前，所選擇的數(shù)據(jù)能包含在將來更新的數(shù)據(jù)。一種簡單的辦法是使用“套用表格格式”后的表作為透視圖的數(shù)據(jù)源。在制作數(shù)據(jù)透視表/圖時，選擇【插入】-【數(shù)據(jù)透視表】命令，在“創(chuàng)建數(shù)據(jù)透視表”對話框中，選擇的“表/區(qū)域”默認(rèn)為現(xiàn)有表的數(shù)據(jù)。數(shù)據(jù)準(zhǔn)備采用此“絕對定位”方法作為數(shù)據(jù)源的缺點是，當(dāng)數(shù)據(jù)增加之后，數(shù)據(jù)透視表/圖并不會自動更新。為了解決數(shù)據(jù)更新后透視表/圖自動更新問題，比較簡單的方式是采用“套用表格格式”命令，以套用格式后的表作為透視圖/表的數(shù)據(jù)源。數(shù)據(jù)準(zhǔn)備選中表格，點擊【開始】，選擇【樣式】組中的【套用表格格式】按鈕，在彈出的格式框中任選一種格式。光標(biāo)定位到表格內(nèi)，依次點擊【插入】-【數(shù)據(jù)透視表】，彈出的對話框中“表/區(qū)域”自動顯示為“表1”，此處的表1，即為套用格式后EXCEL為該表的命名，以此為透視圖/表的數(shù)據(jù)源，當(dāng)數(shù)據(jù)更新后，透視圖/表將自動更新。06KPI分析KPI分析此處KPI指標(biāo)包括銷售總額，產(chǎn)品銷售總量，購買訂單數(shù)三個指標(biāo)。插入數(shù)據(jù)透視表，以“表1”為數(shù)據(jù)源，將透視表放入到新的工作簿中，將此工作簿命名為“KPI”。在“數(shù)據(jù)透視表字段”面板，將“購買金額”、“購買數(shù)量”和“銷售人員”字段拖入到求和項目框中KPI分析由于“銷售人員”為文本字段，在“銷售人員”字段旁的下三角形點擊，選擇【值字段設(shè)置】命令，在彈出的對話框中的“計算類型”選擇【計數(shù)】選項，如圖所示，表示對該項計數(shù)，可得到訂單數(shù)量。KPI分析修改透視表標(biāo)題。透視表的標(biāo)題默認(rèn)情況下不能修改，可將透視表通過選擇性粘貼中的“粘貼鏈接”命令復(fù)制到其他位置后修改標(biāo)題。選擇透視表區(qū)域，復(fù)制，點擊【開始】-【粘貼】下的倒三角形符號，選擇【粘貼鏈接】按鈕KPI分析設(shè)置單元格格式，可將訂單數(shù)量、產(chǎn)品銷量和銷售總價字體設(shè)置大字號07月銷售趨勢圖月銷售趨勢圖月銷售趨勢圖采用按月銷售額透視表來實現(xiàn)。在新的工作簿中插入數(shù)據(jù)透視表，將此工作簿命名為“月趨勢分析”。將日期拖入行、購買金額拖入求值字段。在行字段有月和日期兩個維度，我們主要關(guān)注按月統(tǒng)計，可點擊“日期”字段右邊的倒三角形符號，選擇【刪除字段】按鈕，僅保留“月”字段月銷售趨勢圖制作月銷售額透視折線圖。光標(biāo)置于透視表內(nèi)，選擇【分析】-【數(shù)據(jù)透視圖】命令，在彈出的對話框中選擇“折線圖”，

將縱坐標(biāo)最小值設(shè)為0。為更好的展示趨勢圖，需將縱坐標(biāo)最小值設(shè)為0。右擊縱坐標(biāo)，選擇【設(shè)置坐標(biāo)軸格式】，在“坐標(biāo)軸”組中，將“最小值”改為008添加計算字段添加計算字段“計算字段”可在透視圖中產(chǎn)生新的字段，新字段可在原有字段的基礎(chǔ)上進(jìn)行新運算，從而得到新的值，新字段并不會改變基礎(chǔ)表的數(shù)據(jù)。本項目中，利用“計算字段”來將縱坐標(biāo)的單位改為萬元。操作如下：光標(biāo)置于透視表內(nèi)，依次點擊【分析】-【字段、項目和集】-【計算字段】，如圖所示。添加計算字段彈出“插入計算字段”對話框，在“名稱”框中輸入新字段名稱，此處為“購買金額（萬元）”，在公式欄中，輸入公式為“=購買金額/10000”，其中“購買金額”可從下方字段列表中雙擊自動獲取，點擊【添加】按鈕后，確定添加計算字段用計算字段替換原有求和項。將透視表中求和項原有的“購買金額”字段拖出，拖入“購買金額（萬元）”字段，將新字段作為求和項隱藏透視圖中的字段按鈕透視圖的字段按鈕可以實現(xiàn)篩選功能，本項目將在后期提供統(tǒng)一的篩選區(qū)域，可將透視圖中的字段按鈕隱藏起來，讓圖形顯得更整潔。點擊透視圖，在【設(shè)計】選項卡下【顯示/隱藏】組中，點擊【字段按鈕】，可顯示或隱藏透視圖中的篩選按鈕。09柱狀透視圖柱狀透視圖新建透視表，將透視圖置于新工作簿中。按上述方法，將“產(chǎn)品類別”字段拖入到行，“銷售金額（萬元）”字段拖入到求和字段，將此工作簿命名為“產(chǎn)品類別”柱狀透視圖右擊透視圖，點擊【更改圖表類型】在彈出的對話框中選擇“簇狀柱狀圖”，完成產(chǎn)品類別柱狀透視圖10餅狀透視圖餅狀透視圖新建透視表，置于新的工作簿。將產(chǎn)品等級作為行值，購買金額（萬元）作為求和項。更改圖表類型為“餅圖”，添加數(shù)據(jù)標(biāo)注，設(shè)置標(biāo)注格式，將此工作簿命名為“產(chǎn)品等級”，完成后的產(chǎn)品等級餅狀透視圖11客戶性別購買量分析客戶性別購買量分析新建透視表，置于新工作簿。將“性別”字段拖入行，求和項仍選“購買金額（萬元）”字段，更改透視圖類型為餅圖，添加數(shù)據(jù)標(biāo)注，設(shè)置標(biāo)注樣式，完成按客戶性別購買量分析。將此工作簿命名為“客戶性別”，制作完成12條形透視圖條形透視圖新建透視表，置于新工作簿。將“門店區(qū)域”字段拖入行，求和項選擇“購買金額（萬元）”字段。更改透視圖類型為條形圖。將此工作簿命名為“銷售區(qū)域”。01添加數(shù)據(jù)標(biāo)注，設(shè)置標(biāo)注樣式，將橫坐標(biāo)起始值設(shè)為0，完成門店區(qū)域銷售情況分析0101條形排名透視圖新建透視表，置于新工作簿中，將“門店名稱”字段拖入到行，求和字段不變，將此工作簿命名為“門店排名”。在透視表數(shù)據(jù)區(qū)域點擊右鍵，選擇【排序】-【升序】條形排名透視圖透視圖類型選擇條形圖，橫坐標(biāo)起始值設(shè)為0，完成門店排名分析。13暢銷單品TOP10透視圖暢銷單品TOP10透視圖新建透視表，置于新工作簿中，“產(chǎn)品名稱”作為行字段，求和字段為“購買金額（萬元）”，將此工作簿命名為“暢銷單品TOP10”。在透視表行名稱點右鍵，選擇【篩選】-【前10個】暢銷單品TOP10透視圖對篩選出的數(shù)據(jù)根據(jù)求和項按升序排序，透視圖類型為條形圖，設(shè)置數(shù)據(jù)標(biāo)簽格式，設(shè)置橫坐標(biāo)起始值為0，完成暢銷單品分析如REF14客戶購買TOP10透視圖客戶購買TOP10透視圖新建透視表，置于新工作簿中，將客戶姓名拖入行，求和項為“購買金額（萬元）”。將工作簿命名為“客戶購買TOP10”。按上述方法，篩選前10的客戶，設(shè)置為條形圖，設(shè)置橫坐標(biāo)起始值為0，完成客戶購買TOP10分析15交叉分析透視圖交叉分析透視圖交叉分析，指的是數(shù)據(jù)表中不同字段之間的分析。實際應(yīng)用中，將一個或多個字段置于行，另一各或多個字段置于列，其他求和或計數(shù)字段置于交叉位置。新建透視表，置于新工作簿中，將性別字段拖入列，門店區(qū)域字段拖入行，購買金額（萬元）字段拖入求值項交叉分析透視圖16匯總圖表匯總圖表通過上述各步驟，我們已經(jīng)將數(shù)據(jù)分析面板所需要的圖表制作完畢，接下來，將按照面板規(guī)劃草圖添加面板的各項內(nèi)容。新建空白表格，按面板規(guī)劃草圖，將各工作簿圖表匯總復(fù)制到面板頁，調(diào)整各圖的大小和位置，使得整個頁面整潔美觀。【小貼士】在復(fù)制訂單數(shù)、產(chǎn)品銷量和銷售總價（萬元）等KPI指標(biāo)時，將會出現(xiàn)引用錯誤提示，可采用選擇性粘貼組中的【鏈接的圖片】粘貼選項，按“圖片”方式鏈接到透視表的數(shù)據(jù)，黨透視表數(shù)據(jù)變化后，“鏈接的圖片”數(shù)據(jù)自動變化，匯總圖表在面板內(nèi)添加標(biāo)題和LOGO，更改圖表的標(biāo)題，設(shè)置標(biāo)題字體大小，并去掉網(wǎng)絡(luò)線條，使得面板更簡潔。點擊【視圖】，去掉【網(wǎng)絡(luò)線】前的復(fù)選框匯總圖表匯總數(shù)據(jù)圖表17插入篩選切片器插入篩選切片器使用切片器可方便、直觀的對透視表、數(shù)據(jù)透視圖的數(shù)據(jù)進(jìn)行篩選，實現(xiàn)數(shù)據(jù)圖、表之間的聯(lián)動，是生成動態(tài)圖表的利器，也是用戶交互的主要工具之一。在面板中任意選中一個透視圖，點擊【分析】-【插入切片器】【小貼士】若要完成日期的篩選，可選擇【插入日程表】，讀者可自行嘗試。插入篩選切片器按照規(guī)劃，將實現(xiàn)按門店、產(chǎn)品類別和門店區(qū)域篩選。在“插入切片器”對話框中選擇“門店名稱”、“門店區(qū)域”、“產(chǎn)品種類”字段根據(jù)草圖，將切片器放置于面板左側(cè)，調(diào)整大小和位置，使得頁面整潔美觀。18連接切片器與報表連接切片器與報表為了實現(xiàn)切片器對數(shù)據(jù)的篩選，必須要將切片與與透視圖/表相關(guān)聯(lián)起來。選擇左側(cè)“門店名稱”切片器，點擊【選項】-【報表連接】。根據(jù)圖表和切片器的關(guān)系，本項目的報表連接如下：設(shè)置完畢后，要仔細(xì)再檢查一遍，對無價值的報表連接，刪除即可。切片器名連接的報表名（即工作簿名）門店名稱產(chǎn)品等級、產(chǎn)品類別、暢銷單品TOP10、客戶購買TOP10、客戶性別、銷售區(qū)域、性別區(qū)域分析、月趨勢分析產(chǎn)品種類產(chǎn)品等級、暢銷單品TOP10、客戶購買TOP10、客戶性別、銷售區(qū)域、性別區(qū)域分析、月趨勢分析門店區(qū)域產(chǎn)品等級、產(chǎn)品類別、暢銷單品TOP10、客戶購買TOP10、客戶性別、門店排名、性別區(qū)域分析、月趨勢分析連接切片器與報表【小貼士】讀者可選擇切片器，點擊【選項】，在【切片器樣式】組中快速設(shè)置切片器樣式，也可新建切片器樣式，美化切片器。還可在此處設(shè)計每個圖表、頁面背景等，使得頁面更美觀，此處不再贅述。點擊左邊的篩選器，右邊相關(guān)圖表會自動發(fā)生變化。點擊篩選器右上角的×符號，可去掉此篩選。連接切片器與報表交互式數(shù)據(jù)分析面板19發(fā)布文件發(fā)布文件在將文件發(fā)布或者發(fā)送給其他同事之前，需要將頁面做一些設(shè)置，以方便其他用戶操作。設(shè)置打印頁面選擇【頁面布局】-【紙張方向】-【橫向】，再根據(jù)需要設(shè)置頁邊距。隱藏多余的工作簿在制作面板之前，有很多其他工作簿，為簡化界面，可將多余的工作簿隱藏起來（注意：不是刪除）。選中工作簿，在工作簿上點右鍵，選擇【隱藏】即可。設(shè)置切片器屬性選擇切片器，點右鍵，選擇【大小和屬性】，在“格式切片器”面板的“屬性”組中，取消【鎖定】前的復(fù)選框，以避免在頁面保護(hù)環(huán)境下不能選擇切片器內(nèi)容設(shè)置切片器屬性同時，為防止用戶移動切片器位置和改變大小，造成不好的體驗，可在“格式切片器”面板的“位置和布局”組中，勾選“禁用調(diào)整大小和移動”前的復(fù)選框，禁止用戶調(diào)整位置和大小設(shè)置頁面保護(hù)采用頁面保護(hù)，可避免其他用戶破壞整個頁面各圖表區(qū)域布局。點擊【審閱】-【保護(hù)工作表】，確保勾選“保護(hù)工作表及鎖定的單元格內(nèi)容”前的復(fù)選框，確定設(shè)置頁面保護(hù)【小貼士】若未取消切片器屬性的【鎖定】復(fù)選框反選，在設(shè)置頁面保護(hù)后，將不能選擇切片器內(nèi)容，無法實現(xiàn)交互式查詢功能。拓展訓(xùn)練—基于多表的透視表/圖制作相互關(guān)聯(lián)的多表格示例在制作透視表/圖過程中，我們常常會遇到數(shù)據(jù)來源于不同的數(shù)據(jù)表，如：從銷售信息系統(tǒng)中導(dǎo)出來四個表：產(chǎn)品表、產(chǎn)品類別表、訂單表和銷售人員表，表之間存在一定的關(guān)聯(lián)，各表的結(jié)構(gòu)、示例數(shù)據(jù)和關(guān)聯(lián)基于多表創(chuàng)建透視圖/表基本思路如何以這些表為基礎(chǔ)，進(jìn)行表之間的“拼接”，進(jìn)行數(shù)據(jù)的匯總和透視圖/表呢？主要思路有：（1）利用EXCELVlookup函數(shù)，將以上各表“拼接”為一個寬表，再建立透視圖/表。這種方法對表格關(guān)系較少，比較簡單時尚可，但對關(guān)系復(fù)雜時，很容易出錯；（2）利用數(shù)據(jù)庫軟件，如微軟OFFICE套件中的數(shù)據(jù)庫軟件ACCESS，導(dǎo)入excel表，建立關(guān)系，通過查詢建立一個“寬表”，再以此為基礎(chǔ)，建立透視圖/表。本方法需要讀者掌握一定的數(shù)據(jù)庫基礎(chǔ)知識。（3）利用Excel2016自帶的數(shù)據(jù)分析功能插件PowerQuery、PowerPivot和PowerView，僅進(jìn)行少量的設(shè)置，無需數(shù)據(jù)庫知識，只需鼠標(biāo)操作，即可完成數(shù)據(jù)的導(dǎo)入、建模和透視圖創(chuàng)建，可以大大提高數(shù)據(jù)分析效率?；诙啾韯?chuàng)建透視圖/表基本思路本項目以第（3）種方式為例，介紹如何基于多表，快速創(chuàng)建透視圖/表分析。PowerQuery來是Excel的一個插件，功能強(qiáng)大，在Excel2010之前需要單獨下載，但從Excel2016開始，該功能已經(jīng)內(nèi)置，可通過【數(shù)據(jù)】選項卡下的“獲取和轉(zhuǎn)換”組中直接使用。PowerPivot是Excel2016插件，可用于執(zhí)行強(qiáng)大的數(shù)據(jù)分析和創(chuàng)建復(fù)雜的數(shù)據(jù)模型。當(dāng)然，此處的“建模”，主要是指定表格之間的“關(guān)系”，通過字段將不同的表連接起來而已?？赏ㄟ^PowerPivot解析來自各種來源的大量數(shù)據(jù)，快速執(zhí)行信息分析?；诙啾韯?chuàng)建透視圖/表基本思路PowerView也是Excel2016的插件，用于創(chuàng)建各種可視化效果，展示分析的結(jié)果?！拘≠N士】PowerQuery、PowerPivot、PowerView已經(jīng)集成在微軟強(qiáng)大的數(shù)據(jù)分析軟件PowerBI中，操作更為簡潔，展示效果更為豐富和強(qiáng)大，有興趣的讀者可下載免費的PowerBIdesktop，體驗強(qiáng)大的數(shù)據(jù)分析功能。下載地址："/zh-cn/desktop/"/zh-cn/desktop/加載數(shù)據(jù)分析功能插件要使用PowerPivot和PowerView，需在Excel2016中打開該功能：選擇Excel2016【文件】選項卡，選擇【選項】。在“Excel選項”對話框中左側(cè)選擇“加載項”，在“管理項”下拉列表中選擇“COM加載項”，點擊【轉(zhuǎn)到】按鈕加載數(shù)據(jù)分析功能插件在“COM加載項”對話框中，選中“MicrosoftPowerMapforExcel”、“MicrosoftPowerPivotforExcel”和“MicrosoftPowerViewforExcel”三個選項，點擊【確定】按鈕即可。加載數(shù)據(jù)分析功能插件【小貼士】選中“MicrosoftPowerMapforExcel”選項，可用三維地圖的方式展示數(shù)據(jù)的分布。開啟該功能后，可在【插入】選項卡下的“演示”組中，顯示【三位地圖】按鈕查看效果。當(dāng)Excel表格中包含地址內(nèi)容時，可將數(shù)據(jù)以適當(dāng)?shù)姆绞斤@示在地圖上。加載數(shù)據(jù)分析功能插件當(dāng)打開上述選項后，將在Excel功能區(qū)出現(xiàn)【PowerPivot】和【PowerView】兩個選項卡，加載數(shù)據(jù)分析功能插件【小貼士】若功能區(qū)未出現(xiàn)上述選項卡，點擊【文件】-【選項】，選擇左側(cè)的“自定義功能區(qū)”，在“從下列位置選擇命令”下拉列表中，選擇“主選項卡”，分別選中“PowerView”和“PowerPivot”，點擊【添加】按鈕，添加到右側(cè)即可。當(dāng)上述準(zhǔn)備工作做好之后，即可以開始制作了。新建文件導(dǎo)入數(shù)據(jù)新建Excel文件，將在此文件中完成數(shù)據(jù)的匯總和透視表/圖的制作。導(dǎo)入數(shù)據(jù)。點擊【PowerPivot】-【管理】命令，進(jìn)入“PowerPivotforExc