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

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

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

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

添加生產(chǎn)數(shù)量大于等于0約束。“單元格引用”選擇“C3:D3”單元格，關(guān)系選擇“>=”，“約束”寫(xiě)0，表示生產(chǎn)量不為負(fù)數(shù)添加約束條件“單元格引用”選擇C3:D3單元格，中間關(guān)系處選擇“int”，點(diǎn)擊【確定】按鈕。添加生產(chǎn)數(shù)量為整數(shù)約束。添加約束條件規(guī)劃求解參數(shù)設(shè)置結(jié)果添加約束條件在“規(guī)劃求解參數(shù)”對(duì)話框中，點(diǎn)擊【求解】按鈕，彈出“規(guī)劃求解結(jié)果”對(duì)話框，選擇“保留規(guī)劃求解的解”，選中【運(yùn)算結(jié)果報(bào)告】，點(diǎn)擊【確定】按鈕添加約束條件規(guī)劃求解結(jié)果分析規(guī)劃結(jié)果報(bào)告打開(kāi)“運(yùn)算結(jié)果報(bào)告1”工作簿，從運(yùn)算結(jié)果看出以下信息：（1）“目標(biāo)單元格（最大值）”目標(biāo)函數(shù)值從“初值”360變成了“終值”6300；（2）“可變單元格”列出了兩種產(chǎn)品生產(chǎn)量的初值和終值。（3）“約束”列表中，生產(chǎn)產(chǎn)品的工時(shí)數(shù)未達(dá)到限值，還有10個(gè)工時(shí)未使用，原材料份數(shù)和生產(chǎn)車(chē)間時(shí)間已達(dá)到限值。（4）發(fā)現(xiàn)甲產(chǎn)品在方案中未安排生產(chǎn)，可能與實(shí)際并不相符，在實(shí)際生產(chǎn)過(guò)程中，除了僅考慮利潤(rù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ù)約束條件左端項(xiàng)的值90$E$6<=$F$6未到限制值10

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

$E$8單件產(chǎn)品車(chē)床時(shí)間約束條件左端項(xiàng)的值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)計(jì)得知，生產(chǎn)產(chǎn)品1、產(chǎn)品2和產(chǎn)品3的成本分別為55元、85元和115元，每生產(chǎn)一種產(chǎn)品的時(shí)間耗費(fèi)分別為3分鐘、6分鐘和7分鐘，銷售一件產(chǎn)品分別獲利50元、70元和95元?，F(xiàn)研究決定，企業(yè)為這三種產(chǎn)品最多投入15000元的材料經(jīng)費(fèi)，機(jī)器每天運(yùn)轉(zhuǎn)時(shí)間不超過(guò)15個(gè)小時(shí)，同時(shí)，根據(jù)市場(chǎng)需求調(diào)研，產(chǎn)品1每天產(chǎn)量不得少于60件，產(chǎn)品2不得少于45件，產(chǎn)品3每天不得少于60件，現(xiàn)要求設(shè)計(jì)出最優(yōu)的生產(chǎn)方案，即根據(jù)現(xiàn)有的生產(chǎn)條件和市場(chǎng)需求如何分配三種產(chǎn)品的生產(chǎn)比例才能獲得最大的利潤(rù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ù)建模過(guò)程，在Excel中建立模型設(shè)置“規(guī)劃求解參數(shù)”logo“規(guī)劃求解參數(shù)”拓展訓(xùn)練—利用“規(guī)劃求解”最低運(yùn)輸費(fèi)用現(xiàn)有北京、武漢、成都三地生產(chǎn)某種商品，需要運(yùn)往甲、乙、丙、丁四個(gè)地方銷售，各產(chǎn)地的產(chǎn)量、各地的需求量，以及從產(chǎn)地運(yùn)送單一貨品到各地方銷售的單位貨物運(yùn)輸費(fèi)用如下表所示?，F(xiàn)需要物流公司需要設(shè)計(jì)既滿足各地銷量需求，又滿足產(chǎn)量要求，同時(shí)運(yùn)輸成本最低的方案。即設(shè)計(jì)一種方案，將產(chǎn)品從3個(gè)產(chǎn)地送到4個(gè)銷售地，滿足銷量和需求，又使運(yùn)費(fèi)最低。解決思路：目的地產(chǎn)地甲乙丙丁產(chǎn)量北京41241116武漢2103910成都8511822需求量8141214理清規(guī)劃求解所需關(guān)鍵參數(shù)約束條件：運(yùn)往各銷售地的數(shù)量不能超過(guò)產(chǎn)量，同時(shí)不能低于需求量，為簡(jiǎn)便起見(jiàn)，約定從產(chǎn)地運(yùn)往銷售地的貨物數(shù)量為整數(shù)；目標(biāo)函數(shù)：從產(chǎn)地運(yùn)往各銷售地的運(yùn)費(fèi)。建立EXCEL數(shù)據(jù)模型建立如下的EXCEL模型，其中黑體部分標(biāo)明了相應(yīng)單元格所需要的公式和要求設(shè)置規(guī)劃求解參數(shù)選擇目標(biāo)單元格到“最小值”，設(shè)置決策變量單元格，添加約束條件后的規(guī)劃求解參數(shù)分析規(guī)劃結(jié)果報(bào)告查看規(guī)劃求解為表格所填寫(xiě)的決策變量，以及目標(biāo)函數(shù)（即最低運(yùn)費(fèi)）數(shù)據(jù)分析運(yùn)算結(jié)果報(bào)告。拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問(wèn)題某工廠要用四種合金T1，T2，T3和T4為原料，經(jīng)熔煉成為一種新的不銹鋼G，這四種原料含有元素鉻（Cr）、錳（Mn）和鎳（Ni）的含量（%）、單價(jià)以及新的不銹鋼材料G所要求的Cr，Mn和Ni的最低含量（%）如下表所示。設(shè)熔煉時(shí)重量沒(méi)有損耗，要熔煉成100公斤不銹鋼G，應(yīng)選用原料T1，T2，T3和T4各多少公斤，使成本最小。問(wèn)題思路：（1）建立數(shù)學(xué)模型（2）建立Excel模型參照10.1建立Excel模型T1T2T3T4GCr3.214.532.191.763.20Mn2.041.123.574.332.10Ni5.823.064.272.734.30單價(jià)（元/公斤）115978276拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問(wèn)題配料問(wèn)題EXCEL建模拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問(wèn)題配料問(wèn)題規(guī)劃求解參數(shù)設(shè)置拓展訓(xùn)練—利用“規(guī)劃求解”配料配置問(wèn)題配料問(wèn)題規(guī)劃求解結(jié)果查看分析結(jié)果報(bào)告，分析能否通過(guò)調(diào)整約束條件或數(shù)據(jù)，再次規(guī)劃求解，使得成本更低。謝謝11

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

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