數(shù)據(jù)透視表使用方法精要12點(diǎn)090516

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

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

3、;  使用一個動態(tài)數(shù)據(jù)你可能使用一個動態(tài)的公式定義數(shù)據(jù)透視表的數(shù)據(jù)源. 當(dāng)一個新的條目添加到表格中時(shí)，數(shù)據(jù)源自動擴(kuò)展. 1. 命名一個區(qū)域1. 選擇插入>名稱>自定義 2. 輸入一個范圍名稱, 例如 Database3. 在引用位置框中, 輸入一個Offset公式定義范圍大小, 這樣做的前提必須是品名列不能有空白單元格存在. ,例如: =OFFSET(銷售額!$A$1,0,0,COUNTA(銷售額!$A:$A),7)本例中, 列表在一個名稱為'銷售額'的工作表中, 起始單元格為A1. 公式中使用的參數(shù)是: 1. 引用單元格: 銷售額!$A$12.

4、 行偏移: 03. 列偏移: 04. 行數(shù): COUNTA(銷售額!$A:$A)5. 列數(shù): 7  注意: 如果要使用動態(tài)的列數(shù),      請將7 替換成: COUNTA(銷售額!$1:$1)4. 點(diǎn)擊確定  2. 將定義的名稱范圍用于數(shù)據(jù)透視表1. 選擇數(shù)據(jù)庫中的一個單元格 2. 選擇數(shù)據(jù)>數(shù)據(jù)透視表和圖表報(bào)告 3. 選擇'Microsoft Excel數(shù)據(jù)清單或數(shù)據(jù)庫', 并點(diǎn)擊下一步. 4. 在選定區(qū)域框內(nèi), 輸入范圍名稱, 例如Database 5. 點(diǎn)擊下一步 6. 點(diǎn)擊算式按鈕 7. 放置

5、適當(dāng)按鈕到行,列和數(shù)據(jù)區(qū)域 8. 點(diǎn)擊確定, 點(diǎn)擊完成 Excel - 數(shù)據(jù)透視表 字段整理復(fù)合字段如果你在數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域垂直放置了兩個字段. 為了便于讀取數(shù)據(jù)，你可以重新整理表格.1. 將鼠標(biāo)指向字段的灰色按鈕2. 按著左鍵并拖拉數(shù)據(jù)按鈕到有“匯兌”一詞的單元格.' 3. 松開鼠標(biāo)左鍵此時(shí)兩個數(shù)據(jù)字段成為水平位置排列.  重命名字段當(dāng)你向數(shù)據(jù)區(qū)域添加字段后, 它們被重命名, 例如 '數(shù)量' 變成了 '求和項(xiàng)數(shù)量'. 有很多改變其名稱的途徑,但下面的操作是最簡便易行的.1. 選擇數(shù)據(jù)透視表中的標(biāo)題單元格. 2. 輸入新標(biāo)題. 3

6、. 按回車鍵. 手動清除原有的數(shù)據(jù)項(xiàng)從列表中手動清除原有的數(shù)據(jù)項(xiàng)操作方法:將數(shù)據(jù)透視字段拖拉到數(shù)據(jù)透視表以外的區(qū)域. 點(diǎn)擊數(shù)據(jù)透視表工具欄上的更新按鈕 將數(shù)據(jù)透視字段拖拉回到數(shù)據(jù)透視表區(qū)域 Excel - 數(shù)據(jù)透視表 字段設(shè)置手動隱藏或顯示分類匯兌手動隱藏一個字段的分類匯兌:1.雙擊字段按鈕, 打開數(shù)據(jù)透視表字段對話框. 2.在分類匯兌下選擇“無”3.點(diǎn)擊確定 手動顯示一個字段的分類匯兌:1.雙擊字段按鈕, 打開數(shù)據(jù)透視表字段對話框. 2.在分類匯兌下選擇“自定義”3.從列表中選擇一種函數(shù), 例如 '平均數(shù)' 4.點(diǎn)擊確定 顯示沒有數(shù)據(jù)的項(xiàng)目默認(rèn)情況下, 數(shù)據(jù)透視表僅

7、顯示有數(shù)據(jù)的項(xiàng)目. 下面顯示的示例中, 并不是每天都有各種顏色的商品出售. 你也許希望查看每天的各種商品的品名, 盡管其中有些沒有數(shù)據(jù).1. 雙擊字段按鈕, 打開數(shù)據(jù)透視表字段對話框. 2. 將“顯示沒有數(shù)據(jù)的項(xiàng)目”復(fù)選框選中. 3. 點(diǎn)擊確定 僅顯示前幾個項(xiàng)目通常，數(shù)據(jù)透視表顯示的是據(jù)有數(shù)據(jù)項(xiàng)目,但你可以限制它僅顯示前幾項(xiàng)（或后幾項(xiàng)）數(shù)據(jù).1. 雙擊字段按鈕, 打開數(shù)據(jù)透視表字段對話框. 2. 點(diǎn)擊高級按鈕 3. 在 '自動顯示'下, 選擇自動. 4. 在'顯示'框中, 選擇最大或最小 5. 點(diǎn)擊滾動按鈕或直接輸入一個要顯示的數(shù). 6. 點(diǎn)擊確定7.

8、 再次點(diǎn)擊確定 Excel - 數(shù)據(jù)透視表 - 獲得透視數(shù)據(jù)產(chǎn)生透視數(shù)據(jù)在Excel 2002及以后版本中, 當(dāng)你輸入一個等號并點(diǎn)擊數(shù)據(jù)透視表中的一個單元格時(shí), 你可以看到一個替代單元格引用的產(chǎn)生透視數(shù)據(jù)公式. 如果你愿意使用單元格引用，也可以做到:· 輸入引用,例如 =$B$5或者在工具欄添加獲得透視數(shù)據(jù)按鈕.操作方法:1. 選擇工具>自定義 2. 選擇命令標(biāo)簽下種類框中的數(shù)據(jù) 3. 在右側(cè)列表中選擇獲得數(shù)據(jù)透視數(shù)據(jù)命令 4. 手動這個命令到工具欄5. 點(diǎn)擊關(guān)閉獲得透視數(shù)據(jù)公式 從數(shù)據(jù)透視表中提取數(shù)據(jù), 你可以使用獲得透視數(shù)據(jù)函數(shù). 函數(shù)參數(shù)依據(jù)你使用的EXCEL版

9、本而定.在Excel 2002及更高版本中, 當(dāng)你引用數(shù)據(jù)透視表中單元格時(shí)，這個公式是自動創(chuàng)建的.在Excel 2000或Excel 97, 你可以手動創(chuàng)建獲得透視數(shù)據(jù)公式, 方法與創(chuàng)建其它公式一樣，輸入一個等號、函數(shù)名稱和必須的參數(shù).下面的示例是在 Excel 2000 或Excel 97版本中必需使用的參數(shù), 它將返回銷售數(shù)量總計(jì). 在獲得透視數(shù)據(jù)中使用單元格引用 你可以在獲得數(shù)據(jù)透視數(shù)據(jù)公式參數(shù)中使用用工作表的單元格引用替換輸入的數(shù)據(jù)項(xiàng)或字段名稱. 右圖示例是在Excel 2002中, 單元格A10 包含一個參數(shù), 并且公式引用了這個單元格.右圖是在Excel 2000中的示例, 單元格

10、A10 包含一個參數(shù), 并且公式引用了這個單元格.在"Units "后有一個空格. 如果沒有它, 公式將返回#N/A錯誤.在第二個示例中, 兩個參數(shù)都來自單元格引用, 且中間有一個空白間隔, 使用 &符號連接。    Excel - 數(shù)據(jù)透視表 - 分組數(shù)據(jù)在數(shù)據(jù)透視表中, 你可以對行或列字段的數(shù)據(jù)項(xiàng)進(jìn)行分組. 例如，日期字段可以以月分組, 年齡字段可以以數(shù)字10進(jìn)行分組. 日期分組分組日期字段數(shù)據(jù)項(xiàng)1. 右鍵點(diǎn)擊日期字段按鈕. 2. 選擇“組及分級顯示” | “組合”3. 在分級對話框中,從“依據(jù)”列表中選擇一個或多個選項(xiàng).

11、 4. 要限制分組的日期范圍, 你可以通過在“起始于”和“終止于”框中輸入日期決定起止日期 5. 點(diǎn)擊確定   依周對日期進(jìn)行分組操作步驟：1.   右鍵點(diǎn)擊日期字段按鈕. 2. 選擇“組及分級顯示” | “組合”3. 在分級對話框中,從“依據(jù)”列表中選擇“日”.4. 在'天數(shù)'是, 選擇7 5. 星期的起始范圍取決于“起始于”框中的日期，必要時(shí)請進(jìn)行調(diào)整.下圖示例中因?yàn)?003年12月29日為星期一，因此，請將“起始于”前面自動自選框清空，并在此框中輸入這個日期作為起始日期，這樣的分組結(jié)果符合才常規(guī)。6. 點(diǎn)擊確定 分組數(shù)據(jù)時(shí)遇到的問題

12、當(dāng)你試圖對一個日期或字段進(jìn)行分組時(shí), 你可能會得到一個錯誤信息警告, 內(nèi)容為"選定區(qū)域不能分組."當(dāng)字段中包含有空白日期/數(shù)字字段或是文本日期/數(shù)字字段. 解決這個問題的方法是：· 在空白單元格填充日期/數(shù)字 (如有必要填充一個假的日期/數(shù)字). · 如果在日期/數(shù)字字段存在文本, 移除它. · 如果數(shù)字被視為了文本, 請用這個技巧改變它 查看技巧. 統(tǒng)計(jì)不重復(fù)的數(shù)據(jù)項(xiàng)在數(shù)據(jù)透視表中,你可能想知道有多少不同的顧客定購某種品名的產(chǎn)品. 數(shù)據(jù)透視表不能統(tǒng)計(jì)不重復(fù)的項(xiàng)目. 然而, 你可以在數(shù)據(jù)庫中添加一列, 并將這個字段添加到數(shù)據(jù)透視表中.

13、 例如, 要統(tǒng)計(jì)定單中客戶的不重復(fù)項(xiàng)目, 請?jiān)跀?shù)據(jù)庫中添加一列, 標(biāo)題為'客戶數(shù)'在數(shù)據(jù)庫資料第一行輸入一個引用到顧客和品名列的公式. 例如:   =IF(SUMPRODUCT($A$2:$A2=A2)*($D$2:$D2=D2)>1,0,1)向下復(fù)制這個公式到數(shù)據(jù)庫所有行.然后添加字段到數(shù)據(jù)區(qū)域.本例中, 你可以看出有7個不同顧客8次定購了訂書機(jī).  Excel - 數(shù)據(jù)透視表 -復(fù)合范圍 如果表格列結(jié)構(gòu)相同，在創(chuàng)建數(shù)據(jù)透視表時(shí)，你可以使用來自同一工作簿的不同工作表或不同工作簿中的數(shù)據(jù)。但假設(shè)你不想得到相同的版

14、式，則需要使用單一區(qū)域的數(shù)據(jù).  數(shù)據(jù)源 1. 選擇數(shù)據(jù) | 數(shù)據(jù)透視表和圖表報(bào)告2. 選中多重合并計(jì)算數(shù)據(jù)區(qū)域, 點(diǎn)擊下一步3. 選擇一種頁字段選項(xiàng),點(diǎn)擊下一步4. 選擇每個范圍, 并點(diǎn)擊添加5. 在第步驟2a中選擇的是 '自定義頁字段', 你可以逐個選擇范圍, 并在步驟2b中指定字段名稱 6. 點(diǎn)擊下一步  7. 為數(shù)據(jù)透視表選擇一個位置并點(diǎn)擊完成8. 在按列下拉框中,隱藏包含無意義數(shù)據(jù)的列.  復(fù)合范圍限制本例中, 品名是數(shù)據(jù)源區(qū)域中的第一列, 并且數(shù)據(jù)透視表行標(biāo)題顯示品名名稱. 保留的字段將顯示在列區(qū)域.你可以改變求值的

15、函數(shù) (例如 SUM,操作的方法是在數(shù)據(jù)透視表中點(diǎn)擊右鍵，在彈出的菜單中選擇字段設(shè)置并從數(shù)據(jù)字段設(shè)置對話框中選擇你需要的一種匯兌方式), 但據(jù)有列只能使用同一種函數(shù). 在數(shù)據(jù)透視表中包含有一些無意義的數(shù)據(jù), 比如求和項(xiàng)時(shí)在數(shù)據(jù)庫列包含文本時(shí)數(shù)據(jù)透視表中對應(yīng)列將以0填充.為此, 你可以重新整理數(shù)據(jù)庫列, 使得你要加總的數(shù)據(jù)列僅包含你需要的列. 如果可能, 移動你的資料到另一工作表, 或者貯存到一個數(shù)據(jù)庫中, 例如MS Access, 這樣你將更加靈活地創(chuàng)建數(shù)據(jù)透視表. Excel - 數(shù)據(jù)透視表 - 打印 打印數(shù)據(jù)透視表頁字段中的每個數(shù)據(jù)項(xiàng)下面的代碼將能夠?qū)崿F(xiàn)打印頁字段中的每個數(shù)據(jù)項(xiàng)的

16、功能(假定為一個頁字段).請使用打印預(yù)覽測試. 準(zhǔn)備打印時(shí), 請去掉 ActiveSheet.PrintOut代碼前的單引號, 并在代碼ActiveSheet.PrintPreview前添加一個單引號. 打印數(shù)據(jù)透視表頁字段下每個數(shù)據(jù)項(xiàng)的透視圖下面的代碼將能夠?qū)崿F(xiàn)打印頁字段中的每個數(shù)據(jù)項(xiàng)的透視圖功能(假定為一個頁字段).請使用打印預(yù)覽測試. 準(zhǔn)備打印時(shí), 請去掉 ActiveSheet.PrintOut代碼前的單引號, 并在代碼ActiveSheet.PrintPreview前添加一個單引號.自定義計(jì)算每個示例有兩個工作表1.第一個工作表是在自定義計(jì)算前的數(shù)據(jù)透視表2.第二個是添加自

17、定義計(jì)算后的數(shù)據(jù)透視表知識卡片：你在數(shù)據(jù)透視表中可以使用的自定義計(jì)算普通Normal關(guān)閉自定義計(jì)算。差異Difference From顯示的值為“基本字段”中“基本項(xiàng)”值的差。百分比% Of顯示的值為“基本字段”中“基本項(xiàng)”值的百分比。差異百分比% Difference From顯示的值為“基本字段”中“基本項(xiàng)”值的百分比差值。按某一字段匯兌Running Total in顯示的值為“基本字段”中連續(xù)項(xiàng) 的匯總。占同行數(shù)據(jù)總和的百分比% Of Row顯示每一行或每個類別的值相對于該行或該類別總計(jì)的百分比。占同列數(shù)據(jù)總和的百分比% Of Column顯示每列或系列的所有值相對于列或系列

18、的匯總的百分比。占總和的百分比% Of Total顯示的值為報(bào)表中所有值或所有數(shù)據(jù)點(diǎn)的總計(jì)的百分比。指數(shù)Index按下式計(jì)算值： (單元格中值) x (總計(jì)) / (行總計(jì)) x (列總計(jì)) 可以使用的匯總函數(shù)Sum對數(shù)值求和。這是數(shù)值的默認(rèn)函數(shù)。Count求數(shù)值的個數(shù)。Count 匯總函數(shù)的作用與 COUNTA 工作表函數(shù)相同。Count 是數(shù)字以外的數(shù)值的默認(rèn)函數(shù)。Average求數(shù)值平均值。Max求最大值。Min求最小值。Product求數(shù)值的乘積。Count Nums求數(shù)字型數(shù)值的個數(shù)。Count Nums 匯總函數(shù)的作用與 COUNT 工作表函數(shù)相同。StDev估算總體的標(biāo)準(zhǔn)偏差，樣本為總體的子集。StDevp計(jì)算總體的標(biāo)準(zhǔn)偏差，其中的總體是指要匯總的所有數(shù)值。Var估計(jì)總體方差，樣本為總體的子集。Varp計(jì)算總體的方差，其中的總體是指要匯總的所有數(shù)值。在數(shù)據(jù)透視表頂部顯示