2.3.1 數(shù)據(jù)透視表實(shí)現(xiàn)統(tǒng)計(jì)分析

數(shù)據(jù)透視表是Excel中強(qiáng)大的數(shù)據(jù)分析工具。用戶只需要通過簡(jiǎn)單拖拽就可以完成相對(duì)復(fù)雜的數(shù)據(jù)統(tǒng)計(jì)分析，相比函數(shù)的統(tǒng)計(jì)分析功能，數(shù)據(jù)透視表較為靈活方便。

數(shù)據(jù)透視表可以從數(shù)據(jù)源（工作表或數(shù)據(jù)庫等）中獲取數(shù)據(jù)，然后將多行多列的數(shù)據(jù)轉(zhuǎn)換成有意義的數(shù)據(jù)表現(xiàn)形式，方便用戶從多角度進(jìn)行數(shù)據(jù)分析工作。

數(shù)據(jù)透視表中可以插入公式創(chuàng)建新的計(jì)算字段從而實(shí)現(xiàn)指標(biāo)計(jì)算，也可以在每個(gè)維度層面進(jìn)行分類匯總，用戶可以展開或折疊整個(gè)字段，從而可以看到不同維度展示的數(shù)據(jù)。

基于數(shù)據(jù)透視表去開發(fā)業(yè)務(wù)報(bào)表也是非常輕松的事情，但數(shù)據(jù)透視表有一個(gè)顯著的缺點(diǎn)，當(dāng)數(shù)據(jù)源更新之后，透視表不會(huì)自動(dòng)更新，但用戶可以單擊“數(shù)據(jù)|連接|全部刷新”命令實(shí)現(xiàn)數(shù)據(jù)刷新。

1.數(shù)據(jù)透視表的創(chuàng)建

數(shù)據(jù)透視表要求數(shù)據(jù)的格式是矩形數(shù)據(jù)庫表。數(shù)據(jù)源的第一行為標(biāo)題，下面的每一行數(shù)據(jù)叫作記錄（或日志），用來描述數(shù)據(jù)的信息。數(shù)據(jù)的每一列是一個(gè)字段（包含維度和度量）。

● 維度：用于描述分析的字段。出現(xiàn)在數(shù)據(jù)透視表的行、列、篩選選項(xiàng)。

● 度量：用于匯總聚合的字段。出現(xiàn)的數(shù)據(jù)透視表的值選項(xiàng)。

以店鋪銷售明細(xì)表為例，數(shù)據(jù)如表2-19所示。此數(shù)據(jù)包含7個(gè)字段（店鋪名稱、訂單號(hào)、用戶ID、金額、日期、省份、城市），記錄數(shù)為12行。

（1）數(shù)據(jù)分析需求

基于此數(shù)據(jù)集的匯總分析需求如下：

● 匯總不同省份的訂單數(shù)、訂單金額。

● 匯總不同店鋪的訂單數(shù)、訂單金額。

● 江蘇省不同店鋪的訂單數(shù)、訂單金額。

● 不同省份不同城市的訂單數(shù)、訂單金額。

（2）操作步驟

1）選中區(qū)域A1:G13或數(shù)據(jù)區(qū)域內(nèi)的任意單元格，然后單擊“插入|表格|數(shù)據(jù)透視表”命令，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中，“選擇放置數(shù)據(jù)透視表的位置”選項(xiàng)可以選擇“新工作表”和“現(xiàn)有工作表”。選擇默認(rèn)選項(xiàng)為“新工作表”，數(shù)據(jù)透視表會(huì)生成在一張新建的工作表內(nèi)，選擇“現(xiàn)有工作表”選項(xiàng)，則需要輸入存儲(chǔ)數(shù)據(jù)透視表位置。這里選擇生成在當(dāng)前工作表的單元格I2，如圖2-103所示。

2）單擊“確定”按鈕，彈出“數(shù)據(jù)透視表字段”對(duì)話框，該對(duì)話框包含整個(gè)數(shù)據(jù)集的所有字段以及四個(gè)區(qū)域（篩選、行、列、值）。進(jìn)行匯總分析時(shí)，把需要分析的維度拖到行、列、篩選區(qū)域，匯總聚合的度量值拖到值區(qū)域。

3）將省份字段拖放到行區(qū)域，店鋪名稱（默認(rèn)計(jì)數(shù)）、付費(fèi)金額（默認(rèn)求和）字段拖到值區(qū)域，完成不同省份的訂單數(shù)、訂單金額的匯總分析，如圖2-104所示。

注意：

● 數(shù)據(jù)透視表為什么會(huì)對(duì)店鋪名稱字段實(shí)現(xiàn)計(jì)數(shù)，而對(duì)付費(fèi)金額字段實(shí)現(xiàn)求和呢？因?yàn)榈赇伱Q字段類型是字符串，值匯總依據(jù)默認(rèn)計(jì)數(shù)，而付費(fèi)金額字段類型是數(shù)值，值匯總依據(jù)默認(rèn)求和。原則上任何字段都可以拖到值區(qū)域進(jìn)行數(shù)值計(jì)數(shù)，但需要注意的是該字段一定不要有缺失值。

● 值匯總依據(jù)的切換有如下兩種方法。

方法一：通過選中數(shù)據(jù)透視表中對(duì)應(yīng)的需要修改的字段那一列的任意單元格，然后右擊選擇“值匯總依據(jù)”選項(xiàng)，下拉菜單里可以選擇需要的值匯總依據(jù)，如圖2-105所示。

方法二：在值區(qū)域找到對(duì)應(yīng)的需要修改的字段，單擊下拉按鈕，選擇“值字段設(shè)置“選項(xiàng)，然后在彈出的“值字段設(shè)置”對(duì)話框里面選擇需要的值字段匯總方式，如圖2-106所示。

4）將店鋪名稱字段拖放到行區(qū)域，店鋪名稱（默認(rèn)計(jì)數(shù)）、付費(fèi)金額（默認(rèn)求和）字段拖到值區(qū)域，完成不同店鋪的訂單數(shù)、訂單金額的匯總分析，如圖2-107所示。

5）將店鋪名稱字段拖放到行區(qū)域，店鋪名稱（默認(rèn)計(jì)數(shù)）、付費(fèi)金額（默認(rèn)求和）字段拖到值區(qū)域，省份字段拖到篩選區(qū)域，然后下拉菜單篩選“江蘇省”選項(xiàng)，完成江蘇省不同店鋪的訂單數(shù)、訂單金額的匯總分析，如圖2-108所示。

6）將省份、城市字段分別拖放到行區(qū)域（省份的位置在城市上面），店鋪名稱（默認(rèn)計(jì)數(shù)）、付費(fèi)金額（默認(rèn)求和）字段拖到值區(qū)域，完成不同省份不同城市的訂單數(shù)、訂單金額的匯總分析。然后選中數(shù)據(jù)透視表任意單元格，右擊選擇“數(shù)據(jù)透視表選項(xiàng)”，在“數(shù)據(jù)透視表選項(xiàng)”對(duì)話框中切換到“顯示”選項(xiàng)卡，勾選“經(jīng)典數(shù)據(jù)透視表布局（啟用網(wǎng)格中的字段拖放）”選項(xiàng)，單擊“確定”按鈕，實(shí)現(xiàn)將省份、城市這兩個(gè)維度拆到不同的兩列中。再選中數(shù)據(jù)透視表中省份列里任意單元格，右擊選擇“分類匯總省份”選項(xiàng)，單擊去掉“√”選項(xiàng)，省份維度的匯總則被刪除，如圖2-109所示。

注意：

● 在創(chuàng)建數(shù)據(jù)透視表時(shí)，可以通過單擊“插入|表格|推薦的數(shù)據(jù)透視表”命令，快速完成數(shù)據(jù)透視表的制作。“推薦的數(shù)據(jù)透視表”對(duì)話框會(huì)顯示一些縮略圖，推薦給用戶可以選擇的數(shù)據(jù)透視表。店鋪銷售明細(xì)表的“推薦的數(shù)據(jù)透視表”，如圖2-110所示。

● 當(dāng)數(shù)據(jù)源的行記錄增加時(shí)，希望通過執(zhí)行“數(shù)據(jù)|連接|全部刷新”命令實(shí)現(xiàn)數(shù)據(jù)透視表的刷新，有三種方法可以解決這個(gè)問題。以店鋪銷售明細(xì)表為例，第一種方法是將需要增加的記錄數(shù)據(jù)放置第二行到最后一行之間。第二種方法是在“創(chuàng)建數(shù)據(jù)透視表”的對(duì)話框里面“選擇一個(gè)表或區(qū)域”下的“表/區(qū)域”內(nèi)范圍修改為“$A:$G”，這樣就可以保證以后新增的記錄數(shù)據(jù)都在這個(gè)范圍之內(nèi)。第三種方法是選擇整個(gè)數(shù)據(jù)源區(qū)域，然后單擊“插入|表格|表格”，將數(shù)據(jù)源添加到表中。

2.手動(dòng)分組和自動(dòng)分組

數(shù)據(jù)透視表里面有一項(xiàng)功能是“組合”，它可以對(duì)“數(shù)據(jù)透視表字段”對(duì)話框里面的行和列字段進(jìn)行分組。Excel提供了兩種字段組合方式：

● 手動(dòng)組合：創(chuàng)建數(shù)據(jù)透視表之后，按〈Ctrl〉鍵同時(shí)選中需要組合的項(xiàng)，然后右擊選擇“組合”選項(xiàng)。或者通過單擊“分析|分組|分組選擇”命令來實(shí)現(xiàn)。

● 自動(dòng)組合：如果是日期或者數(shù)值字段，可以使用“組合”對(duì)話框指定項(xiàng)的組合方式。選中數(shù)據(jù)透視表中需要組合的字段對(duì)應(yīng)列里任意單元格，然后右擊選擇“組合”選項(xiàng)?；蛘咄ㄟ^單擊“分析|分組|分組選擇”命令來實(shí)現(xiàn)。

接下來仍以表2-19店鋪銷售明細(xì)表為例進(jìn)行說明。

手動(dòng)組合操作步驟如下：

1）創(chuàng)建第一個(gè)組，需要按住〈Ctrl〉鍵，同時(shí)選中“LZ旗艦店”“MBL旗艦店”“NWY旗艦店”，右擊選擇“組合”選項(xiàng)。

2）創(chuàng)建第二個(gè)組。按住〈Ctrl〉鍵，同時(shí)選中“SFL旗艦店”“XYBC旗艦店”，然后右擊選擇“組合”選項(xiàng)。

3）將默認(rèn)的“數(shù)據(jù)組1”和“數(shù)據(jù)組2”替換成“區(qū)域一”和“區(qū)域二”，結(jié)果如圖2-111所示。

當(dāng)字段包含數(shù)值、日期時(shí)，Excel可以自動(dòng)創(chuàng)建組。下面以日期字段為例實(shí)現(xiàn)自動(dòng)創(chuàng)建組。這里將日期字段拖放到行區(qū)域，店鋪名稱（默認(rèn)計(jì)數(shù)）、付費(fèi)金額（默認(rèn)求和）字段拖到值區(qū)域，完成不同日期維度下的訂單數(shù)、訂單金額的匯總分析。數(shù)據(jù)透視表會(huì)自動(dòng)將日期聚合到月份維度，如圖2-112所示。

如需同時(shí)顯示季度、月維度，選中數(shù)據(jù)透視表中需要組合的日期字段對(duì)應(yīng)列里任意單元格，然后右擊選擇“組合”選項(xiàng)，“組合”對(duì)話框里同時(shí)選擇“季度”“月”選項(xiàng)，然后單擊“確定”按鈕，如圖2-113所示。

按季度、月維度匯總的訂單數(shù)、訂單金額結(jié)果如圖2-114所示。

3.添加計(jì)算字段

當(dāng)創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)源不允許或不方便修改，但是又需要進(jìn)行簡(jiǎn)單公式運(yùn)算時(shí)，這時(shí)候就需要利用數(shù)據(jù)透視表的添加計(jì)算字段功能。以某企業(yè)的產(chǎn)品銷售訂單表為例，數(shù)據(jù)如表2-20所示。基于此數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表并匯總各大區(qū)的成本、收入之和，并添加計(jì)算字段“成本收入比”（成本收入比=收入/成本），小數(shù)部分四舍五入保留兩位。

添加計(jì)算字段的操作步驟如下：

1）選中表格所有數(shù)據(jù)，單擊“插入|表格|數(shù)據(jù)透視表”命令，在現(xiàn)有工作表的單元格F2位置創(chuàng)建數(shù)據(jù)透視表，然后將大區(qū)字段拖放到行區(qū)域，成本（默認(rèn)求和）、收入（默認(rèn)求和）字段拖放到值區(qū)域，如圖2-115所示。

2）選中數(shù)據(jù)透視表任意區(qū)域，然后單擊“分析|計(jì)算|字段、項(xiàng)目和集”命令，在下拉菜單里面選擇“計(jì)算字段”，在“插入計(jì)算字段”對(duì)話框中的“名稱”欄輸入“成本收入比”，“公式”欄輸入“=ROUND(收入/成本,2)”，然后單擊“確定”按鈕，如圖2-116所示。

3）基于數(shù)據(jù)透視表添加的計(jì)算字段“成本收入比”的結(jié)果如圖2-117所示。

4.切片器篩選數(shù)據(jù)透視表

創(chuàng)建完數(shù)據(jù)透視表之后，可以通過插入“切片器”來實(shí)現(xiàn)可視化篩選功能。下面以某企業(yè)不同大區(qū)對(duì)應(yīng)的數(shù)據(jù)為例，來實(shí)現(xiàn)添加切片器篩選功能。

此處仍以表2-20所示的產(chǎn)品銷售訂單表為例進(jìn)行說明。

切片器篩選數(shù)據(jù)透視表的操作步驟如下：

1）選中數(shù)據(jù)透視表任意區(qū)域，單擊“插入|篩選器|切片器”命令，在“插入切片器”對(duì)話框中勾選“大區(qū)”選項(xiàng)，然后單擊“確認(rèn)”按鈕，如圖2-118所示。

2）創(chuàng)建完成的“大區(qū)”切片器選項(xiàng)，右擊選擇“大小與屬性”選項(xiàng)，修改“位置與布局”下的“列數(shù)”參數(shù)為4，右擊選擇“切片器設(shè)置”選項(xiàng)，去掉“顯示頁眉”的勾選項(xiàng)。最后選中切片器，通過按住鼠標(biāo)左鍵拖拽方式調(diào)整切片器邊框大小，結(jié)果如圖2-119所示。

3）按住〈Ctrl〉鍵可以同時(shí)選中切片器中多個(gè)選項(xiàng)，實(shí)現(xiàn)數(shù)據(jù)透視表的篩選功能。切片器篩選“區(qū)域1”和“區(qū)域2”之后的數(shù)據(jù)結(jié)果如圖2-120所示。

5.數(shù)據(jù)透視圖的制作

數(shù)據(jù)透視圖是根據(jù)數(shù)據(jù)透視表中的數(shù)據(jù)制作的可視化圖表。數(shù)據(jù)透視圖和數(shù)據(jù)透視表之間具有很強(qiáng)的關(guān)聯(lián)性，此外，Excel里面的所有圖形在數(shù)據(jù)透視圖中都可以用來進(jìn)行繪制。

Excel提供了多種方法來創(chuàng)建數(shù)據(jù)透視圖，方法如下：

● 選中數(shù)據(jù)透視表任意區(qū)域，然后單擊“插入|圖表|數(shù)據(jù)透視圖|數(shù)據(jù)透視圖”命令來創(chuàng)建數(shù)據(jù)透視圖。

● 選中數(shù)據(jù)透視表任意區(qū)域，然后單擊“分析|工具|數(shù)據(jù)透視圖”命令來創(chuàng)建數(shù)據(jù)透視圖。

● 先單擊“插入|圖表|數(shù)據(jù)透視圖|數(shù)據(jù)透視圖”命令或單擊“插入|圖表|數(shù)據(jù)透視圖|數(shù)據(jù)透視圖和數(shù)據(jù)透視表”命令，然后需要在彈出的“創(chuàng)建數(shù)據(jù)透視圖”對(duì)話框或“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中輸入數(shù)據(jù)源范圍，輸入放置數(shù)據(jù)透視圖或數(shù)據(jù)透視表的位置，最后單擊“確定”按鈕，會(huì)同時(shí)出現(xiàn)數(shù)據(jù)透視表和數(shù)據(jù)透視圖的繪制界面。

（1）數(shù)據(jù)透視圖創(chuàng)建示例

以2017年的某企業(yè)四大區(qū)域（華東、華西、華南、華北）銷售業(yè)績(jī)表的部分?jǐn)?shù)據(jù)為例，創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖。字段包括日期、區(qū)域、業(yè)績(jī)，數(shù)據(jù)如圖2-121所示。

（2）數(shù)據(jù)透視圖創(chuàng)建的操作步驟

1）首先基于此數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表，將月份字段拖放到行區(qū)域（日期字段自動(dòng)按月分組），區(qū)域拖放到列區(qū)域，業(yè)績(jī)拖放到值區(qū)域，生成結(jié)果如圖2-122所示。

2）選中數(shù)據(jù)透視表任意區(qū)域，然后單擊“插入|圖表|二維柱形圖|簇狀柱形圖”命令，生成圖2-123所示的數(shù)據(jù)透視圖。

選中數(shù)據(jù)透視表中需要組合的日期字段對(duì)應(yīng)列里任意單元格，然后右擊選擇“組合”選項(xiàng)，在“組合”對(duì)話框里，選擇“季度”選項(xiàng)，然后單擊“確定”按鈕，此時(shí)數(shù)據(jù)透視表和數(shù)據(jù)透視圖同時(shí)發(fā)生了變化，展示的都是2017年的四大區(qū)域在不同季度對(duì)應(yīng)的銷售業(yè)績(jī)數(shù)據(jù)，如圖2-124所示。

（3）數(shù)據(jù)透視圖需要注意的幾點(diǎn)事項(xiàng)

● 數(shù)據(jù)透視圖和數(shù)據(jù)透視表都可以進(jìn)行字段篩選。

● 數(shù)據(jù)透視圖和數(shù)據(jù)透視表互相關(guān)聯(lián)，篩選數(shù)據(jù)透視圖，數(shù)據(jù)透視表會(huì)發(fā)生變化，篩選數(shù)據(jù)透視表，數(shù)據(jù)透視圖也會(huì)發(fā)生變化。

● 刪除基礎(chǔ)數(shù)據(jù)透視表，數(shù)據(jù)透視圖仍然存在。

● 一個(gè)數(shù)據(jù)透視表可以創(chuàng)建多個(gè)數(shù)據(jù)透視圖，可以分別操作這些數(shù)據(jù)透視圖。

● 數(shù)據(jù)透視圖可以使用切片器功能。

6.數(shù)據(jù)模型的建立

之前講述的數(shù)據(jù)透視表都是基于單表創(chuàng)建的，對(duì)于多表關(guān)聯(lián)之后創(chuàng)建的數(shù)據(jù)透視表，可以使用Excel里面的增強(qiáng)功能“數(shù)據(jù)模型”來實(shí)現(xiàn)。Excel 2016版本里集成了Power Pivot功能，它是數(shù)據(jù)透視表功能的增強(qiáng)版，可以實(shí)現(xiàn)多表之間的關(guān)聯(lián)，然后在合并后的大表基礎(chǔ)上進(jìn)行數(shù)據(jù)分析。此外，還包含新建度量值、新建KPI指標(biāo)等功能。

（1）Power Pivot功能建立數(shù)據(jù)模型

單擊“開發(fā)工具|加載項(xiàng)|COM加載項(xiàng)”命令，在彈出的“COM加載項(xiàng)”對(duì)話框中勾選“Microsoft Power Pivot for Excel”選項(xiàng)，然后單擊“確定”按鈕，這樣就可以調(diào)出“Power Pivot”選項(xiàng)卡，如圖2-125所示。

在某個(gè)工作簿里的三張不同工作表中存放有不同的數(shù)據(jù)，這里三張表分別是student_info（學(xué)生信息表）、student_score（學(xué)生成績(jī)表）、student_course（學(xué)生課程表）。學(xué)生信息表和學(xué)生成績(jī)表可以通過字段stuNo（學(xué)號(hào)）來關(guān)聯(lián)，學(xué)生成績(jī)表和學(xué)生課程表可通過字段CourseID（課程號(hào)）來關(guān)聯(lián)。目的是通過三張表的關(guān)聯(lián)分析不同性別、不同科目的平均成績(jī)，性別字段（sex）位于student_info（學(xué)生信息表）中，科目名稱字段（CourseName）位于student_course（學(xué)生課程表）中，成績(jī)字段（Score）位于student_score（學(xué)生成績(jī)表）中，數(shù)據(jù)如圖2-126所示。

（2）操作步驟

1）選中第一張工作表里的student_info（學(xué)生信息表），然后單擊“Power Pivot|表格|添加到數(shù)據(jù)模型”，在“創(chuàng)建表”對(duì)話框中勾選“我的表具有標(biāo)題”選項(xiàng)，單擊“確定”按鈕，這樣會(huì)把學(xué)生信息表的數(shù)據(jù)加載到“Power Pivot for Excel”對(duì)話框。同理，將student_score（學(xué)生成績(jī)表）、student_course（學(xué)生課程表）數(shù)據(jù)加載到“Power Pivot for Excel”對(duì)話框，結(jié)果如圖2-127所示。

2）在“Power Pivot for Excel”對(duì)話框里單擊“主頁|查看|關(guān)系圖視圖”命令，然后選中student_score（學(xué)生成績(jī)表）里的stuNo字段，右擊選擇“創(chuàng)建關(guān)系”選項(xiàng)，在“創(chuàng)建關(guān)系”對(duì)話框中“表1”的列中選擇stuNo字段，“表2”的列也選擇stuNo字段，單擊“確定”按鈕，這樣兩個(gè)表之間的關(guān)系就建立好了，如圖2-128所示。同理“表2”的列選擇CourseID字段，“表3”的列選擇CourseID字段，然后建立關(guān)系。

3）在“Power Pivot for Excel”對(duì)話框里單擊“主頁|數(shù)據(jù)透視表|數(shù)據(jù)透視表”命令，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中單擊“確定”按鈕，會(huì)在新工作表中生成一張數(shù)據(jù)透視表，如圖2-129所示。

4）在“數(shù)據(jù)透視表字段”對(duì)話框中，將科目名稱字段（CourseName）拖到行區(qū)域，性別字段（sex）拖到列區(qū)域，成績(jī)字段（Score）拖到值區(qū)域，然后修改Score的值匯總依據(jù)為“平均值”，最后選中整個(gè)數(shù)據(jù)透視表，右擊選擇“設(shè)置單元格格式”選項(xiàng)，選擇“數(shù)字|數(shù)值”選項(xiàng)，調(diào)整小數(shù)位數(shù)的數(shù)值為2。通過以上步驟實(shí)現(xiàn)了不同性別、不同科目平均成績(jī)的統(tǒng)計(jì)分析，結(jié)果如圖2-130所示。

7.數(shù)據(jù)透視表案例—字段去重計(jì)數(shù)

字段去重計(jì)數(shù)指的是將某個(gè)字段中的相同數(shù)值去重后統(tǒng)計(jì)個(gè)數(shù)。日常報(bào)表（日?qǐng)?bào)、周報(bào)、月報(bào)等）中都需要實(shí)現(xiàn)對(duì)客戶人數(shù)的統(tǒng)計(jì)，涉及人數(shù)的匯總統(tǒng)計(jì)，往往需要在不同維度層面上去除字段中的重復(fù)數(shù)據(jù)再進(jìn)行計(jì)數(shù)。例如，每日消費(fèi)人數(shù)指標(biāo)，需要統(tǒng)計(jì)每天不同的消費(fèi)用戶ID的數(shù)量。每周、每月消費(fèi)人數(shù)指標(biāo)，需要分別從周、月的維度去統(tǒng)計(jì)不同的消費(fèi)用戶ID的數(shù)量。下面以超市銷售明細(xì)表的數(shù)據(jù)為例，進(jìn)行詳細(xì)說明。

（1）案例分析

以某超市的銷售明細(xì)表為例，數(shù)據(jù)如表2-21所示。字段包括日期、用戶、商品名稱、單價(jià)、數(shù)量、金額。要求統(tǒng)計(jì)超市每日消費(fèi)人數(shù)、消費(fèi)次數(shù)、消費(fèi)金額。

對(duì)于消費(fèi)次數(shù)和消費(fèi)金額的統(tǒng)計(jì)，可以用之前介紹的COUNTIFS、SUMIFS函數(shù)進(jìn)行統(tǒng)計(jì)，但消費(fèi)人數(shù)的統(tǒng)計(jì)需要去重計(jì)數(shù)。下面提供三種方法來統(tǒng)計(jì)每日消費(fèi)人數(shù)：第一種方法是使用“數(shù)據(jù)”選項(xiàng)卡下的“刪除重復(fù)值”命令來實(shí)現(xiàn)；第二種方法是使用數(shù)據(jù)透視表中“值匯總方式”下的“非重復(fù)計(jì)數(shù)”方法來實(shí)現(xiàn)；第三種方法是使用“Power Pivot”選項(xiàng)卡下的“新建度量值”的命令來實(shí)現(xiàn)。

（2）方法一

復(fù)制日期、用戶兩個(gè)字段數(shù)據(jù)到空白列，然后同時(shí)選中這兩個(gè)字段的數(shù)據(jù)，單擊“數(shù)據(jù)|數(shù)據(jù)工具|刪除重復(fù)值”命令，這樣不同日期下的用戶僅出現(xiàn)一次。去重后的日期作為COUNTIFS函數(shù)的條件范圍進(jìn)行日期統(tǒng)計(jì)，結(jié)果如圖2-131所示。

方法一的公式與步驟如下：

● 統(tǒng)計(jì)消費(fèi)人數(shù)的公式。

1）將A列和B列數(shù)據(jù)復(fù)制到H列和I列，然后單擊“數(shù)據(jù)|數(shù)據(jù)工具|刪除重復(fù)值”命令進(jìn)行數(shù)據(jù)去重。

2）單元格L2內(nèi)輸入公式“=COUNTIFS(H:H,K2)”。

● 統(tǒng)計(jì)消費(fèi)次數(shù)的公式。

單元格M2內(nèi)輸入公式“=COUNTIFS(A:A,K2)”。

● 統(tǒng)計(jì)消費(fèi)金額的公式。

單元格N2內(nèi)輸入公式“=SUMIFS(F:F,A:A,K2)”。

（3）方法二

數(shù)據(jù)透視表可以很好地解決不同維度層面的人數(shù)去重計(jì)數(shù)的問題，在插入數(shù)據(jù)透視表進(jìn)行匯總分析時(shí)，一定要先把數(shù)據(jù)源加載到數(shù)據(jù)模型中然后再進(jìn)行計(jì)算。

方法二的操作步驟如下：

1）選擇所有數(shù)據(jù)，單擊“插入|表格|數(shù)據(jù)透視表”命令，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中選擇放置數(shù)據(jù)透視表的位置為“現(xiàn)有工作表”的單元格I2，并勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”，然后單擊“確定”按鈕，如圖2-132所示。

2）將日期字段拖到行區(qū)域，用戶、商品名稱、金額字段分別拖到數(shù)據(jù)透視表的值區(qū)域進(jìn)行度量值統(tǒng)計(jì)，如圖2-133所示。

3）單擊值區(qū)域里用戶計(jì)數(shù)的度量值對(duì)應(yīng)的下拉箭頭，在“值字段設(shè)置”對(duì)話框中的“值匯總方式”選項(xiàng)卡里選擇“非重復(fù)計(jì)數(shù)”，如圖2-134所示，統(tǒng)計(jì)結(jié)果如圖2-135所示。

從統(tǒng)計(jì)結(jié)果上看，每日消費(fèi)人數(shù)已經(jīng)實(shí)現(xiàn)了去重計(jì)數(shù)，消費(fèi)次數(shù)是對(duì)商品名稱字段出現(xiàn)的行數(shù)計(jì)數(shù)，消費(fèi)金額是對(duì)字段金額的求和，總計(jì)的消費(fèi)人數(shù)也同樣實(shí)現(xiàn)了去重計(jì)數(shù)。因此，在創(chuàng)建數(shù)據(jù)透視表的時(shí)候勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”，可以實(shí)現(xiàn)字段的去重計(jì)數(shù)。

（4）方法三

使用Excel 2016版本里集成的Power Pivot功能來實(shí)現(xiàn)用戶去重分析。使用Power Pivot功能，也需要將數(shù)據(jù)加載到數(shù)據(jù)模型后進(jìn)行計(jì)算，計(jì)算方法參照上面的方法二。下面介紹的是利用Power Pivot功能中“新建度量值”方法，使用其內(nèi)置的DISTINCTCOUNT函數(shù)來計(jì)算消費(fèi)人數(shù)。

方法三的操作步驟如下：

1）在使用這個(gè)功能之前，需要先加載出“Power Pivot”選項(xiàng)卡。單擊“開發(fā)工具|加載項(xiàng)|COM加載項(xiàng)”，在“COM加載項(xiàng)”對(duì)話框中勾選“Microsoft Power Pivot for Excel”選項(xiàng)，單擊“確定”按鈕，如圖2-136所示。這樣選項(xiàng)卡里面就會(huì)出現(xiàn)Power Pivot功能。

2）選擇所有數(shù)據(jù)，單擊“Power Pivot|表格|添加到數(shù)據(jù)模型”命令，在“創(chuàng)建表”對(duì)話框中勾選“我的表具有標(biāo)題”選項(xiàng)，單擊“確定”按鈕，如圖2-137所示，然后彈出“Power Pivot for Excel”對(duì)話框。

3）切換到Excel數(shù)據(jù)源工作表，單擊“Power Pivot|計(jì)算|度量值|新建度量值”命令，在“度量值”對(duì)話框中修改“度量值名稱”為消費(fèi)人數(shù)，“公式”里輸入公式“=DISTINCTCOUNT('表1'[用戶])”，然后單擊“確定”按鈕。此時(shí)，在“Power Pivot for Excel”對(duì)話框里面會(huì)顯示剛才編寫的度量值以及計(jì)算公式，如圖2-138所示。

4）切換到“Power Pivot for Excel”對(duì)話框，單擊“主頁|數(shù)據(jù)透視表|數(shù)據(jù)透視表”命令，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中選擇“現(xiàn)有工作表”里的單元格I2位置，然后單擊“確定”按鈕，如圖2-139所示。

5）在“數(shù)據(jù)透視表字段”對(duì)話框中展開“表1”的所有字段，會(huì)出現(xiàn)新建的消費(fèi)人數(shù)字段，然后將日期字段拖到行區(qū)域里面，消費(fèi)人數(shù)、商品名稱、金額字段分別拖到透視表的值區(qū)域里面進(jìn)行度量值統(tǒng)計(jì)。利用Power Pivot新建度量值字段的方法，也可以實(shí)現(xiàn)消費(fèi)人數(shù)去重計(jì)數(shù)，如圖2-140所示。

由此可見，使用“Power Pivot”選項(xiàng)卡下的“新建度量值”方法可以很好地解決字段去重計(jì)數(shù)。其實(shí)，此種方法的流程步驟可以進(jìn)行簡(jiǎn)化，省去使用DISTINCTCOUNT函數(shù)來“新建度量值”的步驟。在數(shù)據(jù)加載到數(shù)據(jù)模型之后，可以直接在“Power Pivot for Excel”對(duì)話框中單擊“數(shù)據(jù)透視表”命令，然后在數(shù)據(jù)源工作表中插入數(shù)據(jù)透視表來實(shí)現(xiàn)對(duì)消費(fèi)人數(shù)的去重計(jì)數(shù)。