Excel教程中數(shù)據(jù)透視表的用法實(shí)例

1、Excel教程中數(shù)據(jù)透視表的用法實(shí)例數(shù)據(jù)透視表是一個系列教程，IT部落窩小編會為大家逐步講解數(shù)據(jù)透視表和數(shù)據(jù)透視圖關(guān)聯(lián)的知識，配合實(shí)例加以講解，并附上案例的excel源文件供大家學(xué)習(xí)使用。數(shù)據(jù)透視表是excel教程中功能最大、使用最靈活、操作最簡單的工具。使用數(shù)據(jù)透視表不必輸入復(fù)雜的公式和函數(shù)，僅僅通過向?qū)Ь涂梢詣?chuàng)建一個交互式表格，從而自動提取、組織和匯總數(shù)據(jù)。如果將數(shù)據(jù)透視表和函數(shù)結(jié)合使用，更能創(chuàng)建出滿足各種需求的報表。什么是數(shù)據(jù)透視表呢?數(shù)據(jù)透視表就是一種交互式報表，可以快速分類匯總大量的數(shù)據(jù)，并可以隨時選擇頁、行和列中的不同元素，快速查看源數(shù)據(jù)的不同統(tǒng)計結(jié)果，同時還可以隨意顯示和打印出用

2、戶感興趣區(qū)域的明細(xì)數(shù)據(jù)，使分析、組織復(fù)雜的數(shù)據(jù)更加快捷有效。數(shù)據(jù)透視表的作用就是將用戶從創(chuàng)建復(fù)制公式、使用各種函數(shù)的煩瑣工作中解脫出來，使其迅速而準(zhǔn)確的對數(shù)據(jù)進(jìn)行處理分析，制作出漂亮的報告和圖表。以工作表數(shù)據(jù)制作數(shù)據(jù)透視表的注意事項有以下七點(diǎn)：以工作表數(shù)據(jù)制作數(shù)據(jù)透視表，這些工作表數(shù)據(jù)必須是一個數(shù)據(jù)清單。所謂數(shù)據(jù)清單，就是在工作表數(shù)據(jù)區(qū)域的頂端行為字段名稱（標(biāo)題），以后各行為數(shù)據(jù)（記錄），并且各列只包含一種類型數(shù)據(jù)的數(shù)據(jù)區(qū)域。這種結(jié)構(gòu)的數(shù)據(jù)區(qū)域就相當(dāng)于一個保存在工作表的數(shù)據(jù)庫。第一，數(shù)據(jù)區(qū)域的頂端行為字段名稱（標(biāo)題）。第二，避免在數(shù)據(jù)清單中存在有空行和空列。這里需指明以下，所謂空行，是指在某行

3、的各列中沒有任何數(shù)據(jù)，如果某行的某些列沒有數(shù)據(jù)，但其他列有數(shù)據(jù)，那么該行就不是空行。同樣，空列也是如此。第三，各列只包含一種類型數(shù)據(jù)。第四，避免在數(shù)據(jù)清單中出現(xiàn)合并單元格。第五，避免在單元格的開始和末尾輸入空格。第六，盡量避免在一張工作表中建立多個數(shù)據(jù)清單，每張工作表最好僅使用一個數(shù)據(jù)清單。第七，工作表的數(shù)據(jù)清單應(yīng)與其他數(shù)據(jù)之間至少留出一個空列和一個空行，以便于檢測和選定數(shù)據(jù)清單。在制作數(shù)據(jù)透視表之前，應(yīng)該按照以上7點(diǎn)來檢查數(shù)據(jù)區(qū)域，如果不滿足上面的要求，需要先進(jìn)行整理工作表數(shù)據(jù)從而使之規(guī)范。本文講解了三個知識點(diǎn)：第一，什么是數(shù)據(jù)透視表，第二，數(shù)據(jù)透視表的作用，第三以工作表數(shù)據(jù)制作數(shù)據(jù)透視表的

4、注意事項，下面一片文章，我們將以實(shí)例介紹如何整理數(shù)據(jù)清單：刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行的四種方法。刪除數(shù)據(jù)區(qū)域內(nèi)所有空行的方法有多種，比如排序、高級篩選、自動篩選、VBA編寫。下面小編就這幾種刪除空行的方法逐一介紹。本文實(shí)例為員工的工資和個稅清單。在這個數(shù)據(jù)清單中就存在一些空行，為了制造數(shù)據(jù)透視表，首先就需要將這些空行刪除掉。第一種刪除空行的方法：排序法第一步，在數(shù)據(jù)清單的右側(cè)插入一個輔助列，D列。第二步，在D列中輸入1,2,3,4,5,6,連續(xù)的自然數(shù)序列。第三步，單擊數(shù)據(jù)一一“排序，對職工姓名列（A列）進(jìn)行升序排序，這樣就將數(shù)據(jù)區(qū)域內(nèi)的所有空行排在了數(shù)據(jù)區(qū)域的底部。第四步，刪除數(shù)據(jù)區(qū)域內(nèi)底部的

5、所有空行。第五步，對D列進(jìn)行升序排列，恢復(fù)數(shù)據(jù)的原始位置。第六步，刪除輔助列，就得到刪除所有空行后的數(shù)據(jù)區(qū)域。第二種刪除空行的方法方法：高級篩選法在利用高級篩選工具篩選并刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行之前，首先要設(shè)置條件區(qū)域。進(jìn)行設(shè)置條件區(qū)域需要了解條件區(qū)域的設(shè)置規(guī)則。為了篩選并刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行，需要對數(shù)據(jù)區(qū)域內(nèi)各列的數(shù)據(jù)進(jìn)行判斷，也就是判斷在某行各列是否有數(shù)據(jù)。對于文本型數(shù)據(jù)，星號（*）表示有數(shù)據(jù)，對于數(shù)值型數(shù)據(jù)，不等于好（）表示有數(shù)據(jù)，這樣，就可以在原始數(shù)據(jù)區(qū)域之外的任意單元格設(shè)置條件區(qū)域。設(shè)置完成條件區(qū)域后，單擊數(shù)據(jù)”篩選”高級篩選”命令，彈出高級篩選對話框，在列表區(qū)域”文本框輸入列表

6、區(qū)域“$A$1:$C$20，在條件區(qū)域輸入“$E$2:$G$5，選中將篩選結(jié)果復(fù)制到其他位置，并在復(fù)制到輸入“$I$1:$K$1，單擊確定即可。第三種刪除空行的方法方法：自動篩選法第一步，單擊數(shù)據(jù)”篩選”自動篩選”命令。第二步，從姓名”單元格的下拉列表中選擇（非空白）選項，得到篩選結(jié)果。第三步，選取數(shù)據(jù)區(qū)域的所有單元格，按下F5鍵，彈出定位對話框，單擊定位條件，選擇可見單元格”，確定。第四步，復(fù)制，在需要保存數(shù)據(jù)的空白單元格單擊，粘貼。第五步，刪除原始數(shù)據(jù)區(qū)域。第四種刪除空行的方法方法：VBA代碼編寫下面一段出現(xiàn)，運(yùn)行這段程序，就可以迅速的將原始數(shù)據(jù)區(qū)域內(nèi)的所有空行刪除。SubDeleteEm

7、ptyRows（）DimLastRowAsLongDimrAsLongLastRow=ActiveSheet.UsedRange.Row-1+ActiveSheet.UsedRange.Rows.CountApplication.ScreenUpdating=FalseForr=LastRowTo1Step-1IfApplication.WorksheetFunction.CountA(Rows(r)=0ThenRows(r).DeleteNextrApplication.ScreenUpdating=TrueEndSub在數(shù)據(jù)透視表系列教程二，講解了一次性的刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行的幾種方法

8、。制作數(shù)據(jù)透視表之前必須把工作表中的空行空列都需要刪除，才能避免錯誤。本文就講解一次性的刪除數(shù)據(jù)區(qū)域內(nèi)的所有空列的兩種方法。第一種一次性刪除數(shù)據(jù)區(qū)域內(nèi)的所有空列的方法是借助輔助列和公式來刪除空列。這種方法是設(shè)計一個輔助列，并利用COUNTA函數(shù)統(tǒng)計各列不為空的單元格個數(shù)(如果為空列，那么不為空單元格的個數(shù)就是0)，然后用一個常量除以統(tǒng)計的單元格個數(shù)。當(dāng)某列為空列時，就會出現(xiàn)錯誤值“#DIV/O!，這樣，就可以利用定位工具定位到所有出現(xiàn)錯誤值的單元格，刪除出現(xiàn)錯誤值單元格所在的整列。實(shí)例如下圖所示：H16ABCDEFGH1地址電話聯(lián)系人應(yīng)收賬款到期日2翕戶h一北京AAA657762008-6-1

9、3客戶E上海*66666666BBB325472008-7-4客戶c南寧5客戶D石家莊*5644324CCC543762008-6-56客戶E北京*87687899436542008-8-12780.1666670.1666670.2f#div/qi0.25#DIV/O!0.20.2具體操作步驟如下：第一步，在數(shù)據(jù)區(qū)域下的任意一行，比如A8單元格輸入公式：=1/C0UNTA(A1:A6),然后向右填充復(fù)制到H8,得到計算結(jié)果，可以看到D、F兩行空列都是錯誤公式。第二步，單擊任意數(shù)據(jù)區(qū)域的單元格，按下F5鍵，彈出定位對話框，單擊定位條件,選擇“公式”選項組下面的“錯誤”復(fù)選框，確定。就可以將所有

10、錯誤公式的列選中。第三步，單擊編輯”刪除”整列”。第四步，刪除輔助行。第二種一次性的刪除數(shù)據(jù)區(qū)域內(nèi)的所有空列的方法是使用VBA代碼。下面是編寫的一段程序，只要運(yùn)行這段程序，就可以迅速將所有空列刪除。代碼如下：SubDeleteEmptyColumns()DimLastColAsLong,rAsLongLastCol=ActiveSheet.UsedRange.Column-1+ActiveSheet.UsedRange.Columns.CountApplication.ScreenUpdating=FalseForr=LastColTo1Step-1IfApplication.Workshee

11、tFunction.CountA(Columns(r)=0ThenColumns(r).DeleteNextrApplication.ScreenUpdating=TrueEndSub數(shù)據(jù)區(qū)域的所有小計行會在一定程度上影響數(shù)據(jù)透視表的統(tǒng)計匯總結(jié)果。盡管可以不在數(shù)據(jù)透視表中顯示這些小計，但這些小計項目的存在終究是多余的。實(shí)際上，數(shù)據(jù)透視表會自動添加各個類別項目的小計。如何一次性快速的刪除工作表中的小計行和全年的合計行呢，工作表如下圖所示。ABCD1月份銷售收入銷售成本銷售利潤21月618163992221S9432月6272330943317S042月6746036T&2306935第1季度小計

12、19199910762T0437264月610094795S1305175月757204140434316.?6月6198944072179179第2季度不計198718133434652S410了月61623415&520063118月64310454651SS45129月60590495531103713笫g季度小計1365231365S34的451410月712654069T3056S15口月6694449&S4172601612月733454045S32EE717笫4季度忝計2115543855417300018全年合計78S79941619S372601第一步，將光標(biāo)定位在工作表數(shù)據(jù)

13、區(qū)域，按下CTRL+F鍵，打開查找和替換對話框,在“查找”框中輸入“*計”，單擊“查找全部”按鈕，所有最后一個字為“計”的單元格都被查找出來了。查找和替換對話框激活狀態(tài)下，按下CTRL+A,即可選中所有小計行。第二步，單擊“編輯”“刪除”“整行”。在某些情況下，可能在某列中既輸入了數(shù)字型文本，有輸入了純數(shù)字，比如序號、電話號碼等，這樣，在利用數(shù)據(jù)透視表進(jìn)行匯總計算時，會將看起來相同但實(shí)際并不相同的序號等處理為兩種類別，從而造成匯總計算錯誤。因此，在這種情況，就必須將文本型數(shù)字和純數(shù)字混雜的行進(jìn)行統(tǒng)一處理，要么統(tǒng)一處理為文本型數(shù)字，要么統(tǒng)一處理為純數(shù)字。我們看下圖，B列的產(chǎn)品編號數(shù)據(jù)既有文本型數(shù)

14、字，也有純數(shù)字，制作的數(shù)據(jù)透視表如右邊所示，顯然，這樣的匯總計算結(jié)果是錯誤的。因此，我們對B列數(shù)據(jù)做如下處理。H7-推ABCDEF,C1日期產(chǎn)品編號數(shù)量日期(全部)T22009-6-10r100210032009-6-10r1004200產(chǎn)曲編r|數(shù)量42009-6-11r1003100100220052009-6-121003400100340062009-6-131002200100430072009-6-14W02500W0260082009-6-1410043001003WO9100420010總計1800為了能夠?qū)?shù)據(jù)進(jìn)行正確的處理和分析，必須將產(chǎn)品編號處理為統(tǒng)一類型的數(shù)據(jù)。首先，介紹文本型數(shù)字轉(zhuǎn)換為數(shù)字的方法比如，新建一列，輸入=VALUE(B2),然后下拉，或者使用公式=1*B2、=B2/1、=-B2,轉(zhuǎn)換后，再使用選擇性粘貼工具將公式轉(zhuǎn)換為數(shù)值，然后將原始的B列數(shù)據(jù)替換。第二種方法，也可以使用智能標(biāo)記中的轉(zhuǎn)換為數(shù)字”命令。第三種方法，使用選擇性粘貼的批量計算功能，對文本型數(shù)字批量修改的方法是：在任何一個空白單元格，