Office培訓(xùn)之?dāng)?shù)據(jù)透視表中的計(jì)算

1、Office培訓(xùn)之-數(shù)據(jù)透視表中級(jí)班謹(jǐn)以此文，獻(xiàn)給沒錢又沒論壇積分的兄弟姐妹。1 1、多匯總方式計(jì)算-11-11 種匯總方式2 2、自定義顯示方式-9-9 種顯示方式3 3、添加計(jì)算字段和計(jì)算項(xiàng)4 4、利用透視表函數(shù)、SQLSQL 語句、VBAVBA 在透視表中進(jìn)行計(jì)算（這方面的內(nèi)容將在以后的學(xué)習(xí)中會(huì)講）一：多種匯總方式計(jì)算-11種匯總方式如果要對(duì)數(shù)據(jù)區(qū)域中的同一字段同時(shí)使用多種匯總方式，只需要將字段列表中的字段多次拖到數(shù)據(jù)區(qū)域，在多次使用某字段時(shí)，數(shù)據(jù)透視表會(huì)自動(dòng)在字段后面加上序列號(hào)。然后單擊右鍵字段設(shè)置在“數(shù)據(jù)透視表字段”對(duì)話框的“匯總方式”下拉列表中選擇不同的匯一一|睡j j隱藏皿I:數(shù)

2、字電）選項(xiàng)5|5|數(shù)據(jù)透視表的 1111 種匯總方式分別為：求和，計(jì)數(shù)，平均值，最大值，最小值，乘積，數(shù)值計(jì)數(shù)，標(biāo)準(zhǔn)偏差，總體標(biāo)準(zhǔn)總方式。如圖1 1、題據(jù)是憫表字段源字段，生產(chǎn)數(shù)量名前皿）：匯總方式但）：偏差，方差，總體方差。A A、求和：計(jì)算所有數(shù)值數(shù)據(jù)的累加和。B B、 計(jì)數(shù)： 對(duì)所有單元格進(jìn)行計(jì)數(shù)， 包括數(shù)值、 文本和錯(cuò)誤的單元格(單元格內(nèi)有空格也會(huì)計(jì)算在內(nèi))。 與 ExceExcel l函數(shù)=Counta()=Counta()等同。C C、平均值：求平均值。D D最大值：顯示最大值。E E、最小值：顯示最小值。F F、乘積：將所有單元格的數(shù)值相乘。如果數(shù)據(jù)集的單元格的值分別是 3 3、

3、4 4、5,5,則乘積為6060。G G 數(shù)值計(jì)數(shù)：只計(jì)算數(shù)值單元格的個(gè)數(shù)，與 ExcelExcel 函數(shù)=Count()=Count()等同。H H、標(biāo)準(zhǔn)偏差和總體標(biāo)準(zhǔn)偏差：計(jì)算標(biāo)準(zhǔn)偏差。如果數(shù)據(jù)集包含全部成員，則要用“總體標(biāo)準(zhǔn)偏差”。如果數(shù)據(jù)集包含成員的一些樣本，則使用“標(biāo)準(zhǔn)偏差”。I I、方差和總體方差：計(jì)算統(tǒng)計(jì)方差。如果數(shù)據(jù)集包含全部成員，則要使用“總體方差”。如果數(shù)據(jù)集只是全體成員的抽樣，則使用“方差”估計(jì)方差?！坝?jì)數(shù)”和“數(shù)值計(jì)數(shù)”的區(qū)別在于，“計(jì)數(shù)”是只要不是空值的項(xiàng)就會(huì)進(jìn)一個(gè)空格也會(huì)進(jìn)行計(jì)數(shù)的統(tǒng)計(jì)，而“數(shù)值計(jì)數(shù)”則是只對(duì)是數(shù)值型的項(xiàng)進(jìn)行計(jì)數(shù)統(tǒng)計(jì)。另外，數(shù)據(jù)透視表的匯總方式默認(rèn)情

4、況下是以求和方式匯總，但是如果數(shù)據(jù)源中有空白或是非數(shù)值類型的數(shù)據(jù)則使用計(jì)數(shù)方式匯總。實(shí)例 1:1:多種方式統(tǒng)計(jì)員工的生產(chǎn)數(shù)量統(tǒng)計(jì)不同員工的生產(chǎn)總量、平均產(chǎn)量、最大產(chǎn)量以及最小產(chǎn)量。操作方法:1 1、用數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表，將“生產(chǎn)數(shù)量”字段拖到數(shù)據(jù)區(qū)域按“求和”匯總。即可統(tǒng)計(jì)各個(gè)生產(chǎn)員工的“生產(chǎn)總量”。如圖 2 2、2 2、再次將“生產(chǎn)數(shù)量”字段拖動(dòng)到數(shù)據(jù)區(qū)域，選中“生產(chǎn)數(shù)量 2 2”字段，單擊鼠標(biāo)右鍵字段設(shè)置選擇匯總方式為“平均值”確定。即可統(tǒng)計(jì)各個(gè)生產(chǎn)員工“生產(chǎn)數(shù)量”的“平均產(chǎn)量”。如圖 3 3、2員工姓名|三工號(hào)1TT生產(chǎn)忘量1平均值項(xiàng)生產(chǎn)數(shù)量為3 3、用同樣的方法也可以對(duì)“最大產(chǎn)量”和

5、“最小產(chǎn)量”進(jìn)行匯總。只需選擇不同的匯總方式即可。二：多種自定義顯示方式-9種顯示方式3安金帆A001A0014139517,375517,3754ADO2ADO231393139392,375392,375鼓勵(lì)獎(jiǎng)ADO3ADO33030源手段：生產(chǎn)數(shù)量名稱0)：曲5 5聲李恒前AD0626李士津A00744101112季延偉劉文超馬麗轆AooeA009A009A010A01053533R3R求L十和數(shù)耳最乘數(shù) 值I I值計(jì)數(shù)kI-，小項(xiàng)江習(xí)兼據(jù)透視表字段理院得A004A00441簧照猊A005A005I地)取消匯總方式制:數(shù)據(jù)透視表的自定義顯示方式可用不同的方式顯示數(shù)據(jù)透視表數(shù)據(jù)區(qū)域中的字段

6、項(xiàng)數(shù)據(jù)。操作方法：?jiǎn)螕粲益I字段設(shè)置選項(xiàng)在“數(shù)據(jù)顯示方式”下拉框中選擇式中的任意一種指定基本字段和基本項(xiàng)確定。如圖 4 4、數(shù)據(jù)透視表的 9 9 種自定義顯示方式分別為：普通、差異、百分比、差異百分比、按某一字段匯總、占同行數(shù)據(jù)總和的百分比、占同列數(shù)據(jù)總和的百分比、占總和的百分比、指數(shù)。A A、普通：數(shù)據(jù)區(qū)域字段按照默認(rèn)的匯總方式計(jì)算，默認(rèn)的匯總方式為求和或計(jì)數(shù)。B B、差異：數(shù)據(jù)區(qū)域字段與指定基本字段和基本項(xiàng)的差值，一般用來與標(biāo)準(zhǔn)進(jìn)行對(duì)比時(shí)使用。C C、百分比：數(shù)據(jù)區(qū)域顯示為基本字段和基本項(xiàng)的百分比，一般用來分析完成率和達(dá)成占比。H H 差異百分比：數(shù)據(jù)區(qū)域字段顯示為與基本字段項(xiàng)的差異百分比。

7、E E、按某一字段匯總：數(shù)據(jù)區(qū)域字段顯示為按某基本字段項(xiàng)的匯總。一般用于累計(jì)和排名等。9 9 種數(shù)據(jù)顯示方F F、占同行數(shù)據(jù)總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該行所有項(xiàng)總和的百分比。G G 占同列數(shù)據(jù)總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該列所有項(xiàng)總和的百分比。H H、占總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該字段所有項(xiàng)總和的百分比。I I、指數(shù)：公式：（單元格的值）* *（總體匯總之和）/ /（行匯總）* *（列匯總），），主要反映數(shù)據(jù)區(qū)域字段項(xiàng)的相對(duì)重要性。實(shí)例 2:2:自定義顯示方式測(cè)定員工的工時(shí)完成情況在數(shù)據(jù)透視表里設(shè)定“定額工時(shí)”為測(cè)定基準(zhǔn)，計(jì)算員工工時(shí)跟定額工

8、時(shí)的差異以及差異率。操作方法：1 1、根據(jù)數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表。將“工時(shí)數(shù)量”三次拖到數(shù)據(jù)區(qū)域，即對(duì)“工時(shí)數(shù)量”進(jìn)行三次求和計(jì)算。2 2、對(duì)數(shù)據(jù)進(jìn)行降序排序。右鍵單擊“員工姓名”字段字段設(shè)置在彈出的對(duì)話框中選擇“高級(jí)”按“工時(shí)數(shù)量”進(jìn)行降序排序確定。如圖 5 5、3 3、對(duì)“工時(shí)數(shù)量 2 2”進(jìn)行差異量的測(cè)定分析。選中“工時(shí)數(shù)量 2 2”單擊右鍵字段設(shè)置在彈出的“數(shù)據(jù)透視表字段”對(duì)話框中單擊“選項(xiàng)”按鈕選擇“差異”（根據(jù)需要也可選其它 9 9 種顯示方式）基本字段選擇“員工姓名”基本項(xiàng)選擇“定額工時(shí)”確定。如圖6 6、4 4、除了差異量的分析，如果還需要得到差異百分率的分析，其操作方式與上面基

9、本相同，只是在“選項(xiàng)”列表中，數(shù)據(jù)顯示方式選擇“百分比”即可。三、計(jì)算字段和計(jì)算項(xiàng)計(jì)算字段是通過對(duì)數(shù)據(jù)透視表中現(xiàn)有字段進(jìn)行計(jì)算后得到的新字段。據(jù)透視表中現(xiàn)有字段的項(xiàng)進(jìn)行計(jì)算后得到的新項(xiàng)。添加了新的計(jì)算字段后，新字段會(huì)同時(shí)添用數(shù)據(jù)透視表以外的單元格和數(shù)據(jù)，也不能使用自定義名稱的引用。加在字段列表中，與原來的數(shù)據(jù)源字段同樣使用,具有等同的作用。計(jì)算字段和計(jì)算項(xiàng)可以對(duì)數(shù)據(jù)透視表中現(xiàn)有的字段和字段項(xiàng)進(jìn)行引用運(yùn)算,也可進(jìn)行簡(jiǎn)單的函數(shù)運(yùn)算，但是不能引45678q10111213第因I此不能向此報(bào)表中添加計(jì)菖項(xiàng).如果要取消期含尊字羥，諳確保它依于行或列區(qū)域中，選定羽含字段的字鼠按觸 指向“數(shù)鹿透視表“菜躥定

10、I關(guān)閉I實(shí)例 3:3:添加字段進(jìn)行工時(shí)完成情況排序根據(jù)不同小組的員工的工時(shí)完成情況，進(jìn)行名次的排序。操作方法：1 1、根據(jù)數(shù)據(jù)源生成數(shù)據(jù)透視表，對(duì)“工時(shí)數(shù)量”字段進(jìn)行求和匯總。并將“員工姓名”費(fèi)人計(jì)算字段侮弛的刪除包名稱:公式通):號(hào)時(shí)工次段工工員名字點(diǎn)擊下拉箭頭.選中要修改或刑除的名核，最后點(diǎn)擊修改或刪除插入字段Q)字段根據(jù)“工時(shí)數(shù)量”進(jìn)行降序排序。如圖1111、2 2、利用添加計(jì)算字段方式實(shí)現(xiàn)名次的排序。選中數(shù)據(jù)區(qū)任意一個(gè)單元格單擊數(shù)據(jù)透視表工具欄選擇“公式”計(jì)算字段在“插入計(jì)算字段”對(duì)話框。將需要添加的計(jì)算字段名稱定義為“名次”，在“公式”中輸入“1 1”確定。即新增加了值為“1 1”的

11、“名次”的字段。如圖 1212、3 3、用自定義顯示方式進(jìn)行名次排序。選擇“名次”字段單擊右鍵字段設(shè)置選項(xiàng)在“數(shù)據(jù)顯示方式”列表中選擇“按某一字段匯總”基本字段選擇“員工姓名”確定。如圖 1313、注意：在進(jìn)行名次的排序前一定要先進(jìn)行降序的排序。實(shí)例 4 4:用百分比方式顯示計(jì)算結(jié)果如果需要用百分比顯示方式來表達(dá)對(duì)數(shù)據(jù)的計(jì)算結(jié)果，通過自定義顯示方式可以計(jì)算數(shù)據(jù)項(xiàng)在行或列數(shù)據(jù)中的百分比，也可計(jì)算數(shù)據(jù)項(xiàng)在所有數(shù)據(jù)總和中的百分比。操作方法：根據(jù)數(shù)據(jù)源生成數(shù)據(jù)透視表，選中數(shù)據(jù)區(qū)域的單元格右鍵單擊字段設(shè)置在數(shù)據(jù)顯示方式的下拉列表中選擇“占同行數(shù)據(jù)總和的百分比”確定。即可得到某一商品在不同地區(qū)的銷售百分比

12、的情況。如圖 1414、源字段；名次名稱眥；蔻那摩確定|取消：信值值1 1將品+如和皿計(jì)平最最乘勤I I數(shù)字除薇坦）能據(jù)顯示方式3：扇C-字段匯總一也可在數(shù)據(jù)顯示方式的下拉列表中選擇“占同列數(shù)據(jù)總和的百分比”或“占總和的百分比。實(shí)例 5 5:添加計(jì)算字段和計(jì)算項(xiàng)進(jìn)行分析需要同時(shí)顯示數(shù)據(jù)和所占費(fèi)用的百分比，并對(duì)兩年數(shù)據(jù)差異分析，就需要用到計(jì)算字段和計(jì)算項(xiàng)。操作方法：1 1、添加計(jì)算字段：選中要插入計(jì)算字段的單元格單擊數(shù)據(jù)透視表工具欄“數(shù)據(jù)透視表”公式計(jì)算字段在插入計(jì)算字段的名稱框中輸入“差異”在公式里輸入“=,2007=,2007年-2006-2006 年,”，（也可以在字段列表內(nèi)單擊再單擊插入

13、字段或雙擊字段名）添加確定。即完成計(jì)算字段添加。如圖1515、播人計(jì)算字段名稱:差異I公式：上的竹年前口碑I一月S憑證號(hào)數(shù)科目嗝嗎科目名稱摘要插入字段1期定i關(guān)閉2 2、添加計(jì)算項(xiàng)：選中要插入字段項(xiàng)的行字段單擊數(shù)據(jù)透視表工具欄中“數(shù)據(jù)透視表”公式計(jì)算項(xiàng)在名稱框中輸入計(jì)算項(xiàng)的新名稱在字段選擇框中選擇要添加計(jì)算項(xiàng)的隸屬字段在項(xiàng)中選擇計(jì)算的項(xiàng)添加確定。完成計(jì)算項(xiàng)添加。如圖 1616、注意：在添加了計(jì)算項(xiàng)后列總計(jì)的數(shù)據(jù)可能不準(zhǔn)確，解決辦法是另外再添加一個(gè)計(jì)算項(xiàng)進(jìn)行總計(jì)的計(jì)算。如圖1717、18182CF-JF.20072007播用屆性G可控費(fèi)用不可控匿用數(shù)據(jù)， 缸碑如匕567.567.505037ft

14、,34S37ft,34Sk k20072007年暉125125S6S6443443網(wǎng)3 37474在費(fèi)用扁性中指入計(jì)算字段名稱也公式也不可控費(fèi)用占比=不可控費(fèi)用/向控費(fèi)用+不可控費(fèi)用!_添加一硼麗宇段爾)定位不標(biāo)到此區(qū)域,再幕窗各算項(xiàng)J可控費(fèi)用_加出?.除|?插入字震區(qū))IMEEMEE呈口*日憑相科林2O2O-G-G美團(tuán)想入項(xiàng)(T)(T)期定不可控費(fèi)用四、常見問題分析1 1、為什么在透視表中使用 matchmatch 等函數(shù)卻得不到運(yùn)算結(jié)果?在數(shù)據(jù)透視表計(jì)算字段中可以使用簡(jiǎn)單的函數(shù)運(yùn)算（如 sum,ifsum,if, ,texttext 等），但數(shù)據(jù)透視表的3數(shù)器二4費(fèi)用屬性 u2 2。配年Z

15、OOTZOOT年差異5可控置用1 1915,125.56915,125.56-149,441.94-149,441.946可控費(fèi)用占比72.72.2 2限B4.76SB4.76S-7-750%50%7不可控費(fèi)用叩43439T9T W.W.563,563,7474T3,2197TS不可控費(fèi)用占比州瞰3S3S4K4K7.SO*7.SO*9息計(jì)1,31,334341 1gl2.gl2.4747L L陰為690.30690.30-76.,222.-76.,222.17171011例總計(jì)應(yīng)該等于可控費(fèi)用十不可控費(fèi)用，但是這里將四項(xiàng)全部疏各在內(nèi)，故不正確.在皿黃用屬性.中指人計(jì)算字段名稱:公式W：=可控費(fèi)

16、用+不用拄費(fèi)用字段（D D；項(xiàng) Sa憑證號(hào)數(shù)科目編碼科目名稱摘要2 2 的&年aDT年比占占用用用用費(fèi)費(fèi)前寬費(fèi)控控總控控可可用可可行不費(fèi)急人號(hào)段里“（通過添加,費(fèi)用總額”字段項(xiàng)叩可得到正確的匯總解定I關(guān)閉運(yùn)算是在透視表緩存中進(jìn)行的，因此不能使用函數(shù)對(duì)單元格進(jìn)行引用或自定義名稱等。也就是說，可以使用不需要使用單元格引用或自定義名稱的工作表函數(shù),元格引用。因此，不能在計(jì)算字段或計(jì)算項(xiàng)中使用。2 2、為什么添加了由“單價(jià)”* *“數(shù)量”得到的“總金額”新字段后，其合計(jì)數(shù)計(jì)算不正確？數(shù)據(jù)透視表總計(jì)結(jié)果總是先進(jìn)行求和再進(jìn)行其它運(yùn)算，因此，在計(jì)算總金額時(shí)，是將“單價(jià)”和“數(shù)量”先分別求和，然后再進(jìn)行相乘，因此計(jì)算結(jié)果不正確。如圖 1919、3 3、為什么我的透視表不能進(jìn)行行總計(jì)？數(shù)據(jù)透視表的行總計(jì)或列總計(jì)僅能計(jì)算由一維表數(shù)據(jù)項(xiàng)形成的列字段的數(shù)據(jù)，而對(duì)于二維的數(shù)據(jù)表創(chuàng)建的多列字段形成的列字段數(shù)據(jù)，則需要通過添加計(jì)算字段進(jìn)行求和或其它方式來實(shí)現(xiàn)。一維表就是只有一個(gè)可供計(jì)算的數(shù)據(jù)項(xiàng)，對(duì)由一維表形成的透視表只需打開“表格選項(xiàng)”中行列總計(jì)即可自動(dòng)進(jìn)行行列匯總。小結(jié)：本節(jié)課主要介紹了以下幾個(gè)方面的內(nèi)容 1 1、多種方式的計(jì)算，如（