Excel數據透視表使用方法精要12點090512019年精選文檔

1、Excel數據透視表使用方法精要12點1、Excel數據透視表能根據時間列和用戶自定時間間隔對數據進行分組統(tǒng)計，如按年、季度、月、日一周等，即你的數據源表中只需有一個日期字段就足夠按照（任意）時間周期進行分組了；。2、通常，透視表項目的排列順序是按升序排列或取決于數據在源數據表中的存放順序；3、對數據透視表項目進行排序后，即使你對其進行了布局調整或是刷新，排序順序依然有效；4、可以對一個字段先進行過濾而后再排序；5、內部行字段中的項目是可以重復出現的，而外部行字段項目則相反；6、 通過雙擊透視表中匯總數據單元格，可以在一個新表中得到該匯總數據的明細數據，對其可以進行格式化、排序或過濾等等常規(guī)編

2、輯處理；決不會影響透視表和 源數據表本身；7、 以上第6點對源數據是外部數據庫的情況尤其有用，因為這時不存在單獨的直觀的源數據表供你瀏覽查閱；8、透視表提供了多種自定義（計算）顯示方式可以使用；9、如果源數據表中的數據字段存在空白或是其他非數值數據，透視表初始便以“計數”函數對其進行匯總（計算“計數項”）；10、透視表在進行 TOP 10排序時會忽略被過濾掉的項目，因此在使用此功能時要特別注意；11、在一個透視表中一個（行）字段可以使用多個“分類匯總”函數；12、在一個透視表數據區(qū)域中一個字段可以根據不同的“分類匯總”方式被多次拖動使用。Excel -數據透視表-動態(tài)數據求和項單位品名匯總24

3、7訂書機392|鋼筆364鉛筆185總計1203使用一個動態(tài)數據你可能使用一個動態(tài)的公式定義數據透視表的數據源.當一個新的條目添加到表格中時，數據源自動擴展.1. 命名一個區(qū)域1. 選擇插入 名稱自定義2. 輸入一個范圍名稱，例如Database3. 在引用位置框中，輸入一個Ofset公式定義范圍大小，這樣做的前提必須是品 名列不能有空白單元格存在.,例如：=OFFSET銷肖售額!$A$1,0,0,COUNTA（銷售額!$A:$A）,7）本例中，列表在一個名稱為銷售額的工作表中，起始單元格為A1.公式中使用的參數是：1. 引用單元格:銷售額!$A$12. 行偏移：03. 列偏移：04. 行數：

4、COUNTS售額!$A:$A)5. 列數：7注意：如果要使用動態(tài)的列數，請將7替換成：COUNTA銷售額!$1：$1)4. 點擊確定日期C品名91011122004-1-2 Jones 2004-1-2 Jones 勸D4-旦一2004d*2 20Q4-1-2 2004-1-2 2004*1*2 2004-1-2 2004-1-2 2004 斗 2 2004-1-213 | 2004-1-15Til 20041-15751 2004-5-1516 2004-1-1517 |弓TO! 2004-1-15訂書機 鋼筆D黒芭黑色E單趙340F單價$ ig.gg廠.29GS 51.30鉛筆紅色17 $

5、1.99$33.33192004-1 15 Jones2將定義的名稱范圍用于數據透視表1. 選擇數據庫中的一個單元格2. 選擇數據 數據透視表和圖表報告3. 選擇'Microsoft Excel數據清單或數據庫',并點擊下一步4. 在選定區(qū)域框內，輸入范圍名稱，例如Database5. 點擊下一步6. 點擊算式按鈕7. 放置適當按鈕到行，列和數據區(qū)域8. 點擊確定，點擊完成FL 日朋 業(yè)務賞 總名 隊色22004-1-2 Jonas 訂書機 黑色20Q4d2004-12004-12004-12004-120Q4-12 J&n&s2 Gill2 GUI2卜單位 單

6、價 合計3 $ 19.99 $ 59.97 則$ 伽 $ 乩別 28 g 4.99 j S 139.72 _21l_S4 HP !R 1F4 R72004-120Q4- -2 卜2QO4-12004422004-1-15 20口4-伯528 S 19.99Gill2OD+-1-15 Jones 血 2004-1-15 Jones 171 20Q4-1-15gIII13S 1.29$ 10.7 7aS 4.S9$ 39,9212$4.旳樂 59.36$ 559 72Excel - 數據透視表-字段整理復合字段為了便于讀取數據，你可以重新如果你在數據透視表的數據區(qū)域垂直放置了兩個字段 整理表格.列

7、©品老求和項:數量行逗）114 l1 111,1 1 1 1 III 1 1 1 11 1 1 1 d 1 1 1 1 1 1 L 1 1 1 1 11 1 1 1 111 1 1 1 11 1 114玄和項金訓 1 數據迦1. 將鼠標指向字段的灰色按鈕2. 按著左鍵并拖拉數據按鈕到有“匯兌” 一詞的單元格3. 松開鼠標左鍵ABC1顏色（全部）節(jié)2"!” ir inrua3品名 數據匯盤艮4筆記本求和項1數量宅5求和項:金額1232.56訂書機茨和項一數量3927求和項:金額5729.298鋼筆農和項數童3849求和項金額1287.16to鉛筆求和項一數童18511求和項:

8、金額368.1512求和項:數量的求和120813求和項:金額的求和9617.13此時兩個數據字段成為水平位置排列A1(:全部-23頻據I4品名 *求和項她求和頂一金菰5筆11*2471232.636訂書機3925729.297鋼筆瑚1237 163105368159總計12060617.13重命名字段當你向數據區(qū)域添加字段后，它們被重命名，例如數量變成了 求和項數量'.有 很多改變其名稱的途徑，但下面的操作是最簡便易行的1. 選擇數據透視表中的標題單元格2. 輸入新標題.3. 按回車鍵.ABC1顏色P全詐)”23數據-4品名數重舍計求和項:金樹筆記本2471232.53訂書機3925

9、729.29鋼筆3041207J6廿鉛筆185368.15總計12083617.13手動清除原有的數據項從列表中手動清除原有的數據項操作方法：將數據透視字段拖拉到數據透視表以外的區(qū)域點擊數據透視表工具欄上的更新按鈕將數據透視字段拖拉回到數據透視表區(qū)域Excel -數據透視表-字段設置手動隱藏或顯示分類匯兌手動隱藏一個字段的分類匯兌：1. 雙擊字段按鈕，打開數據透視表字段對話框2. 在分類匯兌下選擇“無”3. 點擊確定手動顯示一個字段的分類匯兌：1. 雙擊字段按鈕，打開數據透視表字段對話框.2. 在分類匯兌下選擇“自定義”3. 從列表中選擇一種函數，例如平均數4. 點擊確定AB1飄色(全部)W2

10、DEF31 雙擊匡里4品名*X總本機記書筆筆瑩訂鋼鉛5 6 7 89總計廠聚水二一製羞一口 31011 -T分類匠總r自動 r自運文< ?E (S)確定 | 敗消 |值值值 豹數均大水和 求計It乘芋段居：隔16顯示沒有數據的項目默認情況下，數據透視表僅顯示有數據的項目 下面顯示的示例中 并不是每天都有各種顏色的商品出售你也許希望查看每天的各種商品的品名，盡管其中有些沒有數據1. 雙擊字段按鈕，打開數據透視表字段對話框.2. 將“顯示沒有數據的項目”復選框選中3. 點擊確定2 :34 '5 '68_910111213筆記本 訂書機 鋼筆 鉛筆 ¥iF1415CD

11、EFHR I僅顯示前幾個項目通常，數據透視表顯示的是據有數據項目，但你可以限制它僅顯示前幾項(或后幾項)數據1. 雙擊字段按鈕，打開數據透視表字段對話框.2. 點擊高級按鈕3. 在自動顯示下，選擇自動.4. 在'顯示'框中，選擇最大或最小5. 點擊滾動按鈕或直接輸入一個要顯示的數6. 點擊確定7. 再次點擊確定數據透視表字段高級選項?|x|/同時命所有頁宇鮒艮的外部數據f減少透視次數J 1矗r當選樣頁手穀w*檢素外趣據攜丫節(jié)省肉存)廠暫停對該手段進行透視 程謖 (s)自動排庫選頂口 WJrirJ j 心;4貝席人工妙廠人工邂r遞增R自動廣謹減業(yè)n QD|璟大J|2使用字段：使用

12、字段(U):求和項:數量確定J取消1Excel -數據透視表-獲得透視數據產生透視數據在Excel 2019及以后版本中，當你輸入一個等號并點擊數據透視表中的一個單元 格時，你可以看到一個替代單元格引用的產生透視數據公式ABCD1RegionOntario 234Sum of Unit'RepTotal6AndrewsfYd；6Jardine7697Kivell858Morgan809Smith1210(blank)67211Grand Total1688121314=GErPIVOTDATAC，Unitsh,.SA$3."Rep".,JAndrews,)|1F如果

13、你愿意使用單元格引用，也可以做到*輸入引用，例如=$B$5或者在工具欄添加獲得透視數據按鈕 操作方法：1. 選擇工具 自定義2. 選擇命令標簽下種類框中的數據在右側列表中選擇 獲得數據透視數據命令 手動這個命令到工具欄345疋AD2MOBD2?| xl21111J=GETPIVDTBATA t$A$3,陳和項:數量5641S5在Excel 2019及更高版本中，當你引用數據透視表中單元格時，這個公式是自動創(chuàng)F面的示例是在Excel 2000 或Excel 97版本中必需使用的參數,它將返回銷售數獲得透視數據公式從數據透視表中提取數據，你可以使用獲得透視數據函數函數參數依據你使用的在Excel

14、2000或Excel 97,你可以手動創(chuàng)建獲得透視數據公式，方法與創(chuàng)建其它公入一個等號、函數名稱和必須的參數點擊關閉To add a comma nd to a toolbar: select 匚 ategory and drag the command out of this dialog box to a toolbar Categories:Commands:File EditViewIrrsert FormatToolsV/indo1 and Hekp Drawing AutoShapesSelected 匚ortimandBCOntario 工Cancel RefreshRefres

15、h AlRefresh StatusP- Generat-z 3e：Pr. otDdtcProperty Fields.3 求和巫:數星匯總12084 品名5 ；筆記本6 訂書機 鋼筆8!鉛筆9虛計10 I3*Sum of Unit：I4RepTotal1 Region5Andrewst70l6Jardine7697Ki veil85BMorganSO9Smith1210(blank) 67211Grand Total1688121314=GETPIVOTDATA('Units$A53Rep'/Andrews'd1FrToolbarsgSmnands | Options

16、 jExcel - 數據透視表分組數據在數據透視表中，你可以對行或列字段的數據項進行分組例如，日期字段可以以月分組 ，年齡字段可以以數字10進行分組.AE1 ;性剔2R3求和頂:記弭inn4日期-匯總251月TT3求和項:記分62月1344年齡匯總73月47517-2&19784月14462T-36陰95月13373T-472B3：10總計535g總計535日期分組分組日期字段數據項1. 右鍵點擊日期字段按鈕.2. 選擇“組及分級顯示”|“組合”性別（鋅）TC求和項:記分 J日期如 04-1T數據透視閤9 20C10 £004-£-1511 |E0C12 12004

17、-3-1513 2t14 .2004-4-1 :|回向導9）.!更新數據157 8 9 01± 1± lA KJi左口。4-4-12|20C4-5-12004-5-521總計22爼艮分級顯示也）公式追順序表選項© 龜分頁顯示圧）隠藏明細數據01）顯示明F田數據$3. 在分級對話框中，從“依據”列表中選擇一個或多個選項 4. 要限制分組的日期范圍，你可以通過在“起始于”和“終止于”框中輸入 日期決定起止日期5. 點擊確定自動辭起貼于:而終止于：OV1-1|200V5-&'確定 I眠肖32d依周對日期進行分組操作步驟：1. 右鍵點擊日期字段按鈕2. 選

18、擇“組及分級顯示”|“組合”3. 在分級對話框中，從“依據”列表中選擇“日”4. 在'天數是，選擇75. 星期的起始范圍取決于“起始于”框中的日期，必要時請進行調整.下圖示例中因為2003年12月29日為星期一，因此，請將“起始于”前面自 動自選框清空，并在此框中輸入這個日期作為起始日期，這樣的分組結果符合才常規(guī)。6. 點擊確定自動廠起始于應終止于®:(2CO4-5-6依據天數妁:分組數據時遇到的問題當你試圖對一個日期或字段進行分組時，你可能會得到一個錯誤信息警告容為"選定區(qū)域不能分組."當字段中包含有空白日期/數字字段或是文本日期/數字字段解決這個問題的

19、 方法是：*在空白單元格填充日期/數字(如有必要填充一個假的日期/數字)如果在日期/數字字段存在文本，移除它4如果數字被視為了文本，請用這個技巧改變它查看技巧.統(tǒng)計不重復的數據項ABC1客戶123數據-4PA-S 求和項:客戶計數計數項:數量5筆訶本At&6訂書機理10137 315呂鉛筆 669總計3142在數據透視表中，你可能想知道有 多少不同的顧客定購某種品名的產 品數據透視表不能統(tǒng)計不重復的 項目然而，你可以在數據庫中添 加一列，并將這個字段添加到數據 透視表中例如，要統(tǒng)計定單中客戶的不重復 項目，請在數據庫中添加一列，標 題為'客戶數 在數據庫資料第一行輸入一個引用到

20、顧客和品名列的公式例如：=IF(SUMPRODUCT($A$2:$A2=A2)*($D$2:$D2=D2)>1,0,1)向下復制這個公式到數據庫所有行 然后添加字段到數據區(qū)域本例中，你可以看出有7個不同顧客8次定購了訂書機如果表格列結構相同，在創(chuàng)建數據透視表時，你可以使用來自同一工作簿的不同 工作表或不同工作簿中的數據。但假設你不想得到相同的版式，則需要使用單一 區(qū)域的數據ABC DEFGH1馭I（全部）23求和項:值按行丁單價金額日期數量顏色業(yè)務員總計00n0G00命機記書筆筆 筆訂Iffl娜5 6 7 8343698 34461767.24571883 18229260.0641 2

21、11284.13342086207161.096087.364561338567 B51295.53570173'34729.94950.12228032180300.698617.13 1596483 120801606608 82數據源品名日期 巫蓉嬴變色2004-1-2程香宙紅色9101 2 3 J 5 6 7-J1 1 11 1 1-本機機 本 機 需蠶蠶蠶蠶專蠶2004-1-2 Jones2004-1-2 程龍2004-1-2 H0黑色2004-1-2 Ho黑色2004-1-2 Jones色2Q04-1-15 Jones藍色2004-1-15 Jones紅色2004-1-15

22、 Jones紅色2004-1-1 5 Jones2004-1-15j HO2004-1-152004-1-1 52004*1-292004-1-29JonesHOHDJones2004-1-29 Jones紅色藍色st恥單價金叢3¥13 93¥ 59.9740¥1.29V 51.609¥8.99V 80,9139¥ 1S 09¥779.614¥1.29¥5.1649¥ 4.99¥244.5148¥199¥ 95 5213¥1.29¥ 16773¥

23、 4.99¥ 39.9217¥1.99V 33 8332¥10.99¥639 6940Y1.2SV51.6Q41¥ 4 99¥ 204.5923Vl'g'99rT459.7731¥1.29¥ 39.9938¥ 4.99¥ 1B9.62G1. 選擇數據|數據透視表和圖表報告2. 選中多重合并計算數據區(qū)域，點擊下一步3. 選擇一種頁字段選項，點擊下一步4. 選擇每個范圍，并點擊添加5. 在第步驟2a中選擇的是自定義頁字段',你可以逐個選擇范圍，并在步 驟2b中指定字段名稱6.

24、 點擊下一步7. 為數據透視表選擇一個位置并點擊完成8. 在按列下拉框中，隱藏包含無意義數據的列23求和項值按行-筆記本6|訂書機7鋼筆8鉛筆910總計111213ECD冽確定員 價額期量色務 單金日數顏業(yè) 回 " C 復合范圍限制本例中，品名是數據源區(qū)域中的第一列，并且數據透視表行標題顯示品名名稱保留的字段將顯示在列區(qū)域 你可以改變求值的函數 （例如SUM,操作的方法是在數據透視表中點擊右鍵，在彈出的菜單中選擇 字段設置并從數據字段設置 對話框中選擇你需要的一種匯兌方式）,但據有列只能使用同一種函數 在數據透視表中包含有一些無意義的數據，比如求和項時在數據庫列包含文本時數據透視表中

25、對應列將以0填充.晉謖置單元鶴構丈電. 宙設置JR告格式（I）數據if觀國©隱嚴）圍向導（更新數據銀）選定爼及分級顯示國公式追）原序弦孚段設3（1） 恚選項鬍分更顯示®數據遶視義字段源字段:名稱血:匯總方式:aBcDEFGH1馭-（全部）耳曽3駛和項:值按列 |按行單價金額日期數量顏色業(yè)務員總計筆記本41.211284.1334208628700343698.346訂書機161.895087.35456132386C0461767 247鋼筆67.651295.5357017334700571883.1B8鉛筆29.94950.1222809218800229260.06g

26、總計300 698517.1315964831208001606608 82為此，你可以重新整理數據庫列，使得你要加總的數據列僅包含你需要的列.如果可能，移動你的資料到另一工作表，或者貯存到一個數據庫中，例如MSAccess,這樣你將更加靈活地創(chuàng)建數據透視表.Excel -數據透視表-打印打印 數據透視表頁字段中的每個數據項下面的代碼將能夠實現打印頁字段中的每個數據項的功能（假定為一個頁字段）請使用打印預覽測試.準備打印時，請去掉ActiveSheet.PrintOut 代碼前的單引號，并在代碼ActiveSheet.PrintPreview前添加一個單引號.打印數據透視表頁字段下每個數據項的

27、透視圖下面的代碼將能夠實現打印頁字段中的每個數據項的透視圖功能（假定為一個頁字段）請使用打印預覽測試準備打印時,請去掉ActiveSheet.PrintOut 代碼前的單引號,并在 代碼ActiveSheet.PrintPreview前添加一個單引號.自定義計算每個示例有兩個工作表1.第一個工作表是在自定義計算前的數據透視表2.第二個是添加自定義計算后的數據透視表知識卡片：你在數據透視表中可以使用的自定義計算中文函數英文結果普通Normal關閉自定義計算。差異Differe nee From顯示的值為“基本字段”中“基本 項”值的差。百分比% Of顯示的值為“基本字段”中“基本 項”值的百分比

28、。差異 百分 比% Differe nee From顯示的值為“基本字段”中“基本 項”值的百分比差值。按某字 段匯兌Running Total in顯示的值為 基本字段”中連續(xù)項 的 匯總。占同 行數 據總 和的 百分 比% Of Row顯示每一行或每個類別的值相對于 該行或該類別總計的百分比。占同 列數 據總 和的 百分 比% Of Colu mn顯示每列或系列的所有值相對于列 或系列的匯總的百分比。占總 和的 百分 比% Of Total顯示的值為報表中所有值或所有數 據點的總計的百分比。指數In dex按下式計算值：（單元格中值）x （總計）/ （ 行總計）x （列總計）可以使用的匯總函數函數功能Sum對數值求和。這是數值的默認函數。Cou nt求數值的個數。Count匯總函數的作用與 COUNTA工作表函數相同。Count是數字 以外的數值的默認函數。Avera ge求數值平均值。Max求最大值。Min求最小值。Pro