Office高級(jí)應(yīng)用課件：Excel數(shù)據(jù)分析與處理

第3章

通過(guò)

Excel創(chuàng)建

并處理電子表格第3章通過(guò)Excel創(chuàng)建并處理電子表格3.1Excel制表基礎(chǔ)3.2工作簿與多工作表操作3.3Excel公式和函數(shù)3.4在

Excel中創(chuàng)建圖表3.5Excel數(shù)據(jù)分析與處理3.6Excel與其他程序的協(xié)同與共享23.5Excel數(shù)據(jù)分析與處理本節(jié)案例背景：Excel提供了豐富的數(shù)據(jù)處理功能。小蔣是一位中學(xué)教師，在教務(wù)處做后勤管理工作，主要負(fù)責(zé)初一年級(jí)學(xué)生的成績(jī)管理以及學(xué)生的校外活動(dòng)輔導(dǎo)工作，小蔣老師通過(guò)Excel來(lái)統(tǒng)計(jì)學(xué)生成績(jī)及各項(xiàng)學(xué)生活動(dòng)情況。本節(jié)將通過(guò)對(duì)學(xué)生成績(jī)單、學(xué)生勤工儉學(xué)情況統(tǒng)計(jì)表、本量利分析表的統(tǒng)計(jì)、分析、處理，幫助小蔣老師實(shí)現(xiàn)以下工作目標(biāo)：學(xué)會(huì)如何對(duì)分處不同工作表的數(shù)據(jù)進(jìn)行合并。對(duì)數(shù)據(jù)進(jìn)行各類排序操作。對(duì)數(shù)據(jù)進(jìn)行自動(dòng)篩選、設(shè)計(jì)條件實(shí)現(xiàn)高級(jí)篩選。通過(guò)分類匯總功能，對(duì)數(shù)據(jù)進(jìn)行分級(jí)顯示及統(tǒng)計(jì)。利用數(shù)據(jù)透視表和數(shù)據(jù)透視圖對(duì)數(shù)據(jù)進(jìn)行匯總、分析、瀏覽。對(duì)數(shù)據(jù)進(jìn)行模擬運(yùn)算與分析。第3章通過(guò)Excel創(chuàng)建并處理電子表格33.5Excel數(shù)據(jù)分析與處理準(zhǔn)備工作：數(shù)據(jù)表的構(gòu)建規(guī)則本節(jié)的功能全部是在正確的數(shù)據(jù)列表基礎(chǔ)上實(shí)現(xiàn)的，需要重點(diǎn)強(qiáng)調(diào)一下數(shù)據(jù)列表的構(gòu)建規(guī)則。數(shù)據(jù)列表一般是一個(gè)矩形區(qū)域，應(yīng)與周圍的非數(shù)據(jù)列表內(nèi)容用空白行列分隔開，也就是說(shuō)一組數(shù)據(jù)列表中沒(méi)有空白的行或列。數(shù)據(jù)列表要有一個(gè)標(biāo)題行，作為每列數(shù)據(jù)的標(biāo)志，標(biāo)題應(yīng)便于理解數(shù)據(jù)的含義。標(biāo)題一般不能使用純數(shù)值，不能重復(fù)，也不能分置于兩行中。數(shù)據(jù)列表中不能包括合并單元格，標(biāo)題行單元格中不能插入斜線表頭。每一列中的數(shù)據(jù)格式一般應(yīng)該統(tǒng)一。第3章通過(guò)Excel創(chuàng)建并處理電子表格43.5.1合并計(jì)算若要匯總和報(bào)告多個(gè)單獨(dú)工作表中數(shù)據(jù)的結(jié)果，可以將每個(gè)單獨(dú)工作表中的數(shù)據(jù)合并到一個(gè)主工作表。所合并的工作表可以與主工作表位于同一工作簿中，也可以位于其他工作簿中。注意：參與合并計(jì)算的數(shù)據(jù)區(qū)域應(yīng)滿足數(shù)據(jù)列表的條件且應(yīng)位于單獨(dú)的工作表中，不要放置在合并后的主工作表中，同時(shí)確保參與合并計(jì)算的數(shù)據(jù)區(qū)域都具有相同的布局。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格53.5.1合并計(jì)算打開準(zhǔn)備放置合并數(shù)據(jù)的主工作表“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“合并計(jì)算”按鈕在“函數(shù)”下拉框中，選擇一個(gè)匯總函數(shù)在“引用位置”框中設(shè)定合并區(qū)域單擊“添加”按鈕，增加引用位置?？商砑佣鄠€(gè)合并數(shù)據(jù)區(qū)域在“標(biāo)簽位置”組中指定標(biāo)簽所在位置3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格63.5.1合并計(jì)算提示：當(dāng)所有合并計(jì)算數(shù)據(jù)所在的源區(qū)域具有完全相同的行列標(biāo)簽時(shí)，無(wú)需選中“標(biāo)簽位置”。當(dāng)一個(gè)源區(qū)域中的標(biāo)簽與其他區(qū)域都不相同時(shí)，將會(huì)導(dǎo)致合并計(jì)算中出現(xiàn)單獨(dú)的行或列。操作：打開案例工作簿文檔“3.5分班級(jí)學(xué)生成績(jī)單”，利用合并計(jì)算功能將工作表“初一（1）班”、“初一（2）班”、“初一（3）班”中數(shù)據(jù)進(jìn)行“求和”合并，其中“標(biāo)簽位置”指定為“首行”和“最左列”。合并后的數(shù)據(jù)存放在工作表“全年級(jí)”中?？蓪?duì)合并后的工作表進(jìn)行修改完善。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格73.5.2對(duì)數(shù)據(jù)進(jìn)行排序?qū)?shù)據(jù)進(jìn)行排序有助于快速直觀地組織并查找所需數(shù)據(jù)，可以對(duì)一列或多列中的數(shù)據(jù)文本、數(shù)值、日期和時(shí)間按升序或降序的方式進(jìn)行排序。還可以按自定義序列、格式（包括單元格顏色、字體顏色等）進(jìn)行排序。1.快速簡(jiǎn)單排序通常情況下，參與排序的數(shù)據(jù)列表需要有標(biāo)題行、且為一個(gè)連續(xù)區(qū)域，很少只單獨(dú)對(duì)某一列進(jìn)行排序。選擇排序的數(shù)據(jù)區(qū)域→“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“升序”或“降序”按鈕。操作：打開案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單”，對(duì)工作表“期末成績(jī)”中的數(shù)據(jù)按“總分”由高到低進(jìn)行排序。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格83.5.2對(duì)數(shù)據(jù)進(jìn)行排序2.復(fù)雜多條件排序選擇要排序的數(shù)據(jù)區(qū)域→“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“排序”按鈕，打開“排序”對(duì)話框→設(shè)置“主要關(guān)鍵字”及“次序”→“添加條件”按鈕，設(shè)置第二、第三……排序依據(jù)→“選項(xiàng)”按鈕，可進(jìn)一步設(shè)置排序條件。操作：打開案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單”，對(duì)工作表“期末成績(jī)”中的數(shù)據(jù)先按“總分”由高到低進(jìn)行排序，總分相同的再按語(yǔ)文成績(jī)由高到低進(jìn)行排序。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格93.5.2對(duì)數(shù)據(jù)進(jìn)行排序3.按自定義列表進(jìn)行排序只能基于數(shù)據(jù)（文本、數(shù)值以及日期或時(shí)間）創(chuàng)建自定義列表，而不能基于格式（單元格顏色、字體顏色等）創(chuàng)建自定義列表。首先，通過(guò)“文件”選項(xiàng)卡→“選項(xiàng)”→“高級(jí)”→“常規(guī)”→“編輯自定義列表”按鈕創(chuàng)建一個(gè)自定義序列?！皵?shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“排序”按鈕→在排序條件的“次序”列表中，選擇“自定義序列”。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格103.5.3從數(shù)據(jù)中篩選通過(guò)篩選功能，可以快速?gòu)臄?shù)據(jù)列表查找符合條件數(shù)據(jù)或者排除不符合條件的數(shù)據(jù)。篩選條件可以是數(shù)值或文本，可以是單元格顏色，還可以根據(jù)需要構(gòu)建復(fù)雜條件實(shí)現(xiàn)高級(jí)篩選。數(shù)據(jù)列表中的數(shù)據(jù)經(jīng)過(guò)篩選后，將僅顯示那些滿足指定條件的行，并隱藏那些不希望顯示的行。對(duì)于篩選結(jié)果可以直接復(fù)制、查找、編輯、設(shè)置格式、制作圖表和打印。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格113.5.3從數(shù)據(jù)中篩選1.自動(dòng)篩選使用自動(dòng)篩選來(lái)篩選數(shù)據(jù)，可以快速而又方便地查找和使用數(shù)據(jù)列表中數(shù)據(jù)的子集。定位要篩選的數(shù)據(jù)列表。在“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“篩選”按鈕，進(jìn)入到自動(dòng)篩選狀態(tài)。單擊列標(biāo)題中的篩選箭頭，打開一個(gè)篩選器選擇列表。指定篩選條件。其中選擇“數(shù)字篩選”或“文本篩選”命令→“自定義篩選”命令，可自行設(shè)定篩選條件。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格123.5.3從數(shù)據(jù)中篩選操作：打開案例工作簿“3.5全年級(jí)學(xué)生成績(jī)單”，對(duì)工作表“期末成績(jī)”中的數(shù)據(jù)進(jìn)行雙重嵌套篩選：先篩選全年級(jí)總分前8名；然后篩出總分前8名中的1班學(xué)生，結(jié)果如圖3.88所示。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格13圖3.88自動(dòng)篩選出總分前8名的1班學(xué)生共4名3.5.3從數(shù)據(jù)中篩選2.高級(jí)篩選通過(guò)構(gòu)建復(fù)雜條件可以實(shí)現(xiàn)高級(jí)篩選。所構(gòu)建的復(fù)雜條件需要放置在單獨(dú)的區(qū)域中，可以為該條件區(qū)域命名以便引用。用于高級(jí)篩選的復(fù)雜條件中可以使用下列運(yùn)算符比較兩個(gè)值：=（等號(hào)） >（大于號(hào)）<（小于號(hào)） >=（大于等于號(hào)）<=（小于等于號(hào)） <>（不等號(hào)）3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格143.5.3從數(shù)據(jù)中篩選1）創(chuàng)建復(fù)雜篩選條件構(gòu)建復(fù)雜條件的原則：條件區(qū)域中必須有列標(biāo)題、且與包含在數(shù)據(jù)列表中的列標(biāo)題一致；表示“與（and）”的多個(gè)條件應(yīng)位于同一行中，意味著只有這些條件同時(shí)滿足的數(shù)據(jù)才會(huì)被篩選出來(lái)；表示“或（or）”的多個(gè)條件應(yīng)位于不同的行中，意味著只要滿足其中的一個(gè)條件就會(huì)被篩選出來(lái)。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格153.5.3從數(shù)據(jù)中篩選為案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單”，構(gòu)建復(fù)雜篩選條件，要求：查找1班中數(shù)學(xué)高于100分且總分高于650分的學(xué)生，以及3班中語(yǔ)文高于95分且總分高于620分的學(xué)生。所構(gòu)建的條件如表3.5中所列3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格16表3.5

構(gòu)建高級(jí)篩選條件注意：在“班級(jí)”條件列中，由于希望在單元格中顯示的條件本身為“=1班”這個(gè)文本串，為了與公式輸入相區(qū)別，要求在構(gòu)建篩選條件時(shí)以類似“="=1班"”的方式作為字符串表達(dá)式的條件，以免產(chǎn)生意外的篩選結(jié)果。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格17

ABCD1班級(jí)數(shù)學(xué)語(yǔ)文總分2="=1班">100

>6503="=3班"

>95>6203.5.3從數(shù)據(jù)中篩選2）依據(jù)復(fù)雜條件進(jìn)行高級(jí)篩選方法：在獨(dú)立區(qū)域創(chuàng)建一組篩選條件→“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“高級(jí)”按鈕→設(shè)定篩選條件。操作：打開案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單（1）”，按照“篩選條件”工作表中已經(jīng)事先創(chuàng)建好的篩選條件對(duì)工作表“期末成績(jī)”中的數(shù)據(jù)列表進(jìn)行篩選，篩選結(jié)果與數(shù)據(jù)列表存放在同一工作表中。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格183.5.3從數(shù)據(jù)中篩選3.清除篩選清除某列的篩選條件：在自動(dòng)篩選列表中選擇“從“××”中清除篩選”命令。清除工作表中的所有篩選條件并重新顯示所有行：“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“清除”按鈕。退出自動(dòng)篩選狀態(tài)：在自動(dòng)篩選狀態(tài)下，選擇“數(shù)據(jù)”選項(xiàng)卡→“排序和篩選”組→“篩選”按鈕。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格193.5.4分類匯總與分級(jí)顯示分類匯總是將數(shù)據(jù)列表中的數(shù)據(jù)先依據(jù)一定的標(biāo)準(zhǔn)分組，然后對(duì)同組數(shù)據(jù)應(yīng)用分類匯總函數(shù)得到相應(yīng)的統(tǒng)計(jì)或計(jì)算結(jié)果。分類匯總的結(jié)果可以按分組明細(xì)進(jìn)行分級(jí)顯示，以便于顯示或隱藏每個(gè)分類匯總的明細(xì)行。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格203.5.4分類匯總與分級(jí)顯示1.插入分類匯總3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格21選擇數(shù)據(jù)區(qū)域?qū)Ψ纸M依據(jù)列進(jìn)行排序“數(shù)據(jù)”選項(xiàng)卡→“分級(jí)顯示”組→“分類匯總”按鈕在“分類匯總”對(duì)話框中設(shè)置匯總條件：選擇“分類字段”設(shè)定“匯總方式”“選定匯總項(xiàng)”設(shè)置匯總數(shù)據(jù)顯示位置3.5.4分類匯總與分級(jí)顯示操作：打開案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單”，利用分類匯總功能計(jì)算工作表“期末成績(jī)”中每個(gè)班各科的平均成績(jī)。分類匯總結(jié)果參見(jiàn)案例工作簿文檔“3.5全年級(jí)學(xué)生成績(jī)單（3）”。2.刪除分類匯總定位分類匯總的數(shù)據(jù)區(qū)域→在“數(shù)據(jù)”選項(xiàng)卡上→“分級(jí)顯示”組→“分類匯總”→“全部刪除”按鈕3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格223.5.4分類匯總與分級(jí)顯示3.分級(jí)顯示使用分級(jí)顯示可以快速顯示摘要行或摘要列，或者顯示每組的明細(xì)數(shù)據(jù)。分類匯總的結(jié)果可以形成分級(jí)顯示?？梢詾閿?shù)據(jù)列表自行創(chuàng)建分級(jí)顯示，最多可分8級(jí)。經(jīng)過(guò)了分類匯總后，在數(shù)據(jù)區(qū)域的右側(cè)出現(xiàn)了分級(jí)顯示符號(hào)，如圖3.91所示，其中數(shù)字表示分級(jí)的級(jí)數(shù)及級(jí)別，數(shù)字越大級(jí)別越小；加號(hào)表示可展開下級(jí)明細(xì)，減號(hào)表示可收縮下級(jí)明細(xì)。單擊不同的分級(jí)顯示符號(hào)將顯示不同的級(jí)別。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格233.5.4分類匯總與分級(jí)顯示圖3.91工作表中的分級(jí)顯示符號(hào)3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格243.5.4分類匯總與分級(jí)顯示1）顯示或隱藏組的明細(xì)數(shù)據(jù)2）展開或折疊特定級(jí)別的分級(jí)顯示3）自行創(chuàng)建分級(jí)顯示定位數(shù)據(jù)列表。對(duì)作為分組依據(jù)的數(shù)據(jù)列進(jìn)行排序。在每組明細(xì)行的緊下方或緊上方插入帶公式的匯總行，輸入摘要說(shuō)明和匯總公式。選擇同組中的明細(xì)行或列（不包括匯總行）→“數(shù)據(jù)”選項(xiàng)卡→“分級(jí)顯示”組→“創(chuàng)建組”按鈕。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格253.5.4分類匯總與分級(jí)顯示4）復(fù)制分級(jí)顯示的數(shù)據(jù)當(dāng)在分級(jí)顯示的數(shù)據(jù)列表收縮部分明細(xì)后，如果只希望復(fù)制顯示的內(nèi)容，可進(jìn)行下列設(shè)置：使用分級(jí)顯示符號(hào)隱藏不需要復(fù)制的明細(xì)數(shù)據(jù)。選擇要復(fù)制的數(shù)據(jù)區(qū)域?！伴_始”選項(xiàng)卡→“編輯”組→“查找和選擇”按鈕→“定位條件”命令→選中“可見(jiàn)單元格”單選項(xiàng)。通過(guò)“復(fù)制”→“粘貼”命令將選定的分級(jí)數(shù)據(jù)復(fù)制到其他位置，被隱藏的明細(xì)數(shù)據(jù)將不會(huì)被復(fù)制。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格263.5.4分類匯總與分級(jí)顯示5）刪除分級(jí)顯示3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格27“數(shù)據(jù)”選項(xiàng)卡“分級(jí)顯示”組“取消組合”按鈕下方的箭頭“清除分級(jí)顯示”命令。3.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)數(shù)據(jù)透視表是一種可以從源數(shù)據(jù)列表中快速提取并匯總大量數(shù)據(jù)的交互式表格。用于匯總、分析、瀏覽數(shù)據(jù)以及呈現(xiàn)匯總數(shù)據(jù)，以便從不同的角度查看數(shù)據(jù)，并對(duì)相似數(shù)據(jù)進(jìn)行比較。數(shù)據(jù)透視表是根據(jù)源數(shù)據(jù)列表生成的，源數(shù)據(jù)列表中每一列都成為匯總多行信息的數(shù)據(jù)透視表字段，列名稱為數(shù)據(jù)透視表的字段名。該源數(shù)據(jù)區(qū)域必須具有列標(biāo)題，并且該區(qū)域中沒(méi)有空行。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格283.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)1.創(chuàng)建一個(gè)數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表所依據(jù)的源數(shù)據(jù)列表。定位數(shù)據(jù)源區(qū)域?！安迦搿边x項(xiàng)卡→“表”組→“數(shù)據(jù)透視表”按鈕，打開“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框。指定數(shù)據(jù)來(lái)源以及數(shù)據(jù)透視表存放的位置。向數(shù)據(jù)透視表中添加字段。在數(shù)據(jù)透視表中篩選字段。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格293.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)圖3.95在新工作表中插入空白的透視表并顯示數(shù)據(jù)透視表字段列表窗口3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格30數(shù)據(jù)透視表區(qū)字段列表區(qū)布局區(qū)域3.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)操作：打開案例工作簿文檔“3.5學(xué)生勤工儉學(xué)情況統(tǒng)計(jì)”，為工作表“初一銷售統(tǒng)計(jì)”中的統(tǒng)計(jì)數(shù)據(jù)創(chuàng)建一個(gè)數(shù)據(jù)透視表，透視表中只匯總各個(gè)班級(jí)每個(gè)時(shí)間段的電子產(chǎn)品的銷售額，并放置在新工作表。提示：在字段列表中依次選中“班級(jí)”和“銷售額”兩個(gè)字段，再將“商品名稱”字段拖動(dòng)到“報(bào)表篩選”區(qū)中，將“時(shí)間”字段拖動(dòng)到“列標(biāo)簽”區(qū)中，最后從報(bào)表篩選字段“商品名稱”中選擇“電子產(chǎn)品”。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格313.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)2.對(duì)數(shù)據(jù)透視表進(jìn)行更新和維護(hù)在數(shù)據(jù)透視表中的任意單元格中單擊，功能區(qū)中將會(huì)出現(xiàn)“數(shù)據(jù)透視表工具”的“選項(xiàng)”和“設(shè)計(jì)”兩個(gè)選項(xiàng)卡，在如圖3.97所示的“選項(xiàng)”選項(xiàng)卡下可以對(duì)數(shù)據(jù)透視表中進(jìn)行多項(xiàng)操作。1）刷新數(shù)據(jù)透視表2）更改數(shù)據(jù)源圖3.97“數(shù)據(jù)透視表工具”的“選項(xiàng)”選項(xiàng)卡3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格323.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)3.設(shè)置數(shù)據(jù)透視表的格式可以像對(duì)普通表格那樣對(duì)數(shù)據(jù)透視表進(jìn)行格式設(shè)置；還可通過(guò)如圖3.99所示“數(shù)據(jù)透視表工具”的“設(shè)計(jì)”選項(xiàng)卡為數(shù)據(jù)透視表快速指定預(yù)置樣式。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格33圖3.99“數(shù)據(jù)透視表工具”的“設(shè)計(jì)”選項(xiàng)卡3.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)4.創(chuàng)建一個(gè)數(shù)據(jù)透視圖數(shù)據(jù)透視圖以圖形形式呈現(xiàn)數(shù)據(jù)透視表中的匯總數(shù)據(jù)，其作用與普通圖表一樣，可以更為形象化地對(duì)數(shù)據(jù)進(jìn)行比較、反映趨勢(shì)。為數(shù)據(jù)透視圖提供源數(shù)據(jù)的是相關(guān)聯(lián)的數(shù)據(jù)透視表，在透視表中所做的更改，會(huì)立即反映在數(shù)據(jù)透視圖中。除了數(shù)據(jù)源來(lái)自數(shù)據(jù)透視表以外，數(shù)據(jù)透視圖與標(biāo)準(zhǔn)圖表組成元素基本相同，與普通圖表的區(qū)別在于，當(dāng)創(chuàng)建數(shù)據(jù)透視圖時(shí)，數(shù)據(jù)透視圖的圖表區(qū)中將顯示字段篩選器，以便對(duì)基本數(shù)據(jù)進(jìn)行排序和篩選。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格343.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)①在數(shù)據(jù)透視表中單擊。②“數(shù)據(jù)透視表工具”的“選項(xiàng)”選項(xiàng)卡→“工具”組→“數(shù)據(jù)透視圖”按鈕→“插入圖表”對(duì)話框。③選擇相應(yīng)的圖表類型和圖表子類型。④通過(guò)圖表區(qū)中的字段篩選器，可更改圖表中顯示的數(shù)據(jù)。⑤在數(shù)據(jù)透視圖中單擊，功能區(qū)出現(xiàn)“數(shù)據(jù)透視圖工具”中的“設(shè)計(jì)”、“布局”、“格式”和“分析”四個(gè)選項(xiàng)卡。通過(guò)這四個(gè)選項(xiàng)卡，可以對(duì)透視圖進(jìn)行修飾和設(shè)置，3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格353.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)提示：在數(shù)據(jù)透視圖中，可以使用除XY散點(diǎn)圖、氣泡圖或股價(jià)圖以外的任意圖表類型。操作：打開案例工作簿文檔“3.5學(xué)生勤工儉學(xué)情況統(tǒng)計(jì)（1）”，為工作表“數(shù)據(jù)透視”中的數(shù)據(jù)表區(qū)域創(chuàng)建一個(gè)柱形圖中的“堆積柱形圖。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格363.5.5通過(guò)數(shù)據(jù)透視表分析數(shù)據(jù)5.刪除數(shù)據(jù)透視表或數(shù)據(jù)透視圖刪除數(shù)據(jù)透視表定位數(shù)據(jù)透視表→“數(shù)據(jù)透視表工具”的“選項(xiàng)”選項(xiàng)卡→“操作”組→“選擇”按鈕→“整個(gè)數(shù)據(jù)透視表”命令→按Delete鍵。刪除數(shù)據(jù)透視圖在要?jiǎng)h除的數(shù)據(jù)透視圖中的任意空白位置單擊，然后按Delete鍵。刪除數(shù)據(jù)透視圖不會(huì)刪除相關(guān)聯(lián)的數(shù)據(jù)透視表。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格373.5.6模擬分析和運(yùn)算模擬分析是指通過(guò)更改某個(gè)單元格中數(shù)值來(lái)查看這些更改對(duì)工作表中引用該單元格的公式結(jié)果的影響的過(guò)程。通過(guò)使用模擬分析工具，可以在一個(gè)或多個(gè)公式中試用不同的幾組值來(lái)分析所有不同的結(jié)果。Excel附帶了三種模擬分析工具：方案管理器、模擬運(yùn)算表和單變量求解。方案管理器和模擬運(yùn)算表可獲取一組輸入值并確定可能的結(jié)果。單變量求解則是針對(duì)希望獲取結(jié)果的確定生成該結(jié)果的可能的各項(xiàng)值。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格383.5.6模擬分析和運(yùn)算1.單變量求解單變量求解用來(lái)解決以下問(wèn)題：先假定一個(gè)公式的計(jì)算結(jié)果是某個(gè)固定值，當(dāng)其中引用的變量所在單元格應(yīng)取值為多少時(shí)該結(jié)果才成立。輸入基礎(chǔ)數(shù)據(jù)→“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“模擬分析”按鈕→“單變量求解”命令→設(shè)置用于單變量求解的各項(xiàng)參數(shù)。參數(shù)含義：公式所在的單元格為“目標(biāo)單元格”，其所引用的變量所在單元格為“可變單元格”，希望達(dá)到的計(jì)算結(jié)果為“目標(biāo)值”。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格393.5.6模擬分析和運(yùn)算操作：打開案例工作簿文檔“3.5本量利分析”，在“單變量求解”工作表中輸入如圖3.101中所示的基礎(chǔ)數(shù)據(jù)，其中C7單元格輸入的是利潤(rùn)的求解公式：“利潤(rùn)=（單價(jià)-成本）×銷量”。進(jìn)行單變量求解：試測(cè)算當(dāng)利潤(rùn)為15000時(shí)，銷量是多少。其中含有利潤(rùn)公式的C7為“目標(biāo)單元格”，含有變量銷量值的C6作為“可變單元格”。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格403.5.6模擬分析和運(yùn)算圖3.101輸入單變量求解的基礎(chǔ)數(shù)據(jù)3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格413.5.6模擬分析和運(yùn)算2.模擬運(yùn)算表的創(chuàng)建和應(yīng)用模擬運(yùn)算表的結(jié)果顯示在一個(gè)單元格區(qū)域中，它可以測(cè)算將某個(gè)公式中一個(gè)或兩個(gè)變量替換成不同值時(shí)對(duì)公式計(jì)算結(jié)果的影響。模擬運(yùn)算表最多可以處理兩個(gè)變量，但可以獲取與這些變量相關(guān)的眾多不同的值。模擬運(yùn)算表依據(jù)處理變量個(gè)數(shù)的不同，分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表兩種類型。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格423.5.6模擬分析和運(yùn)算1)單變量模擬運(yùn)算表若要測(cè)試公式中一個(gè)變量的不同取值如何改變這些公式的結(jié)果，可使用單變量模擬運(yùn)算表。在單列或單行中輸入變量值后，不同的計(jì)算結(jié)果便會(huì)在公式所在的列或行中顯示。輸入基礎(chǔ)數(shù)據(jù)與公式→選擇創(chuàng)建模擬運(yùn)算表的單元格區(qū)域→“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“模擬分析”按鈕→“模擬運(yùn)算表”命令，→指定變量值所在的單元格→在模擬運(yùn)算表中輸入不同的變量值進(jìn)行結(jié)果測(cè)試。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格433.5.6模擬分析和運(yùn)算操作：打開案例工作簿文檔“3.5本量利分析”，在“模擬運(yùn)算”工作表中輸入如圖3.104所示的基礎(chǔ)數(shù)據(jù)與公式，其中D4單元格中輸入的是利潤(rùn)的求解公式：“利潤(rùn)=（單價(jià)-成本）×銷量”。對(duì)本量利進(jìn)行單變量模擬運(yùn)算，目的是測(cè)算不同單價(jià)下利潤(rùn)值的變化情況。其中A4單元格中的單價(jià)為變量值，不同的單價(jià)將會(huì)在A列中輸入。D4單元格是引用了該變量的利潤(rùn)計(jì)算公式，3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格443.5.6模擬分析和運(yùn)算圖3.104輸入單變量模擬運(yùn)算表的基礎(chǔ)數(shù)據(jù)3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格453.5.6模擬分析和運(yùn)算2）雙變量模擬運(yùn)算表若要測(cè)試公式中兩個(gè)變量的不同取值如何改變這些公式的結(jié)果，可使用雙變量模擬運(yùn)算表。在單列和單行中分別輸入兩個(gè)變量值后，計(jì)算結(jié)果便會(huì)在公式所在區(qū)域中顯示。輸入基礎(chǔ)數(shù)據(jù)與公式→輸入變量值→選擇創(chuàng)建模擬運(yùn)算表的單元格區(qū)域→“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“模擬分析”按鈕→“模擬運(yùn)算表”命令→指定變量值所在的單元格→在模擬運(yùn)算表中更改單價(jià)或銷量，測(cè)算對(duì)應(yīng)的利潤(rùn)值。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格463.5.6模擬分析和運(yùn)算操作：打開案例工作簿文檔“3.5本量利分析”，在“模擬運(yùn)算”工作表中輸入如圖3.106所示的基礎(chǔ)數(shù)據(jù)與公式，其中G6單元格中輸入的是利潤(rùn)的求解公式：“利潤(rùn)=（單價(jià)-成本）×銷量”，G3單元格中為單價(jià)變量、G5單元格中為銷量變量。對(duì)本量利進(jìn)行雙變量模擬運(yùn)算，目的是測(cè)算不同單價(jià)、不同銷量下利潤(rùn)值的變化情況：從H6單元格開始沿行輸入銷量值；從G7單元格開始沿列輸入單價(jià)值。G6單元格是引用了兩個(gè)變量的利潤(rùn)計(jì)算公式。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格473.5.6模擬分析和運(yùn)算圖3.106輸入雙變量模擬運(yùn)算表3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格483.5.6模擬分析和運(yùn)算3.使用方案管理器如果要分析兩個(gè)以上的變量，應(yīng)使用方案管理器。一個(gè)方案最多獲取32個(gè)不同的值，但是卻可以創(chuàng)建任意數(shù)量的方案。方案管理器作為一種分析工具，每個(gè)方案允許建立一組假設(shè)條件、自動(dòng)產(chǎn)生多種結(jié)果，并可以直觀地看到每個(gè)結(jié)果的顯示過(guò)程，還可以將多種結(jié)果存放到一個(gè)工作表中進(jìn)行比較。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格493.5.6模擬分析和運(yùn)算1）建立分析方案由于電子產(chǎn)品的成本上漲了10%，導(dǎo)致利潤(rùn)下降，為了抵消成本上漲帶來(lái)的影響，小蔣老師擬采取兩種措施：第一種，提高單價(jià)5%，因此導(dǎo)致銷量減少10%；另一種，是降低單價(jià)3%，這使得銷量增加15%。表3.6中顯示了上述三種不同的方案情況。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格50表3.6建立三種不同的方案3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格51項(xiàng)

目方案1方案2方案3單價(jià)增長(zhǎng)率0.00%8.00%-3.00%成本增長(zhǎng)率10.00%10.00%10.00%銷量增長(zhǎng)率0.00%-5.00%20.00%3.5.6模擬分析和運(yùn)算目標(biāo)：通過(guò)方案管理器來(lái)建立分析方案，幫助小蔣老師分析一下價(jià)量不變、提價(jià)、降價(jià)這三種方案對(duì)利潤(rùn)額的影響。方法：輸入基礎(chǔ)數(shù)據(jù)與公式→“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“模擬分析”→“方案管理器”命令→“添加”按鈕→在“添加方案”對(duì)話框中輸入方案名、設(shè)置可變單元格區(qū)域→在“方案變量值”對(duì)話框中輸入方案的變量值→繼續(xù)添加其他方案。3.5Excel數(shù)據(jù)分析與處理第3章通過(guò)Excel創(chuàng)建并處理電子表格523.5.6模擬分析和運(yùn)算操作：打開案例工作簿“3.5本量利分析”，在“方案管理”工作表中輸入如圖3.108所示的基礎(chǔ)數(shù)據(jù)與公式。說(shuō)明：D5、D6、D7三個(gè)