案例數(shù)據(jù)

這是公司的部分銷售訂單表，主要涉及省份、城市、業(yè)務(wù)類別和金額。

要求：

統(tǒng)計(jì)公司三大業(yè)務(wù)覆蓋的省份數(shù)量，這是典型的非重復(fù)計(jì)數(shù)的案例。

函數(shù)法

本文介紹經(jīng)典的組合函數(shù)

SUMPRODUCT(1/COUNTIF)

先不考慮業(yè)務(wù)類別的因素，僅僅統(tǒng)計(jì)表中共有幾個(gè)省份

在H7單元格中，輸入公式：

=SUMPRODUCT(1/COUNTIF(C2:C23,C2:C23))

在Office 365版本中，可以用UNIQUE函數(shù)獲取唯一值的列表，再用COUNTA函數(shù)統(tǒng)計(jì)。

H7=COUNTA(UNIQUE(C2:C23))

那要加上業(yè)務(wù)類別的限制怎么辦？

公式要做調(diào)整，COUNTIF變成COUNTIFS

組合函數(shù)為

SUMPRODUCT(條件1*（1/COUNTIF(條件1范圍，條件1范圍，統(tǒng)計(jì)范圍，統(tǒng)計(jì)范圍)))

條件1范圍和統(tǒng)計(jì)范圍在函數(shù)中的位置可以互換。

I7單元格中輸入公式：

=SUMPRODUCT(($E$2:$E$23=H5)*(1/COUNTIFS($C$2:$C$23,$C$2:$C$23,$E$2:$E$23,$E$2:$E$23)))

當(dāng)然，用365版本公式要簡(jiǎn)單的多，除了用COUNTA和UNIQUE函數(shù)外，還要用到動(dòng)態(tài)篩選函數(shù)FILTER

公式變成了

=COUNTA(UNIQUE(FILTER($C$2:$C$23,$E$2:$E$23=H5)))

數(shù)據(jù)模型透視法

如果用Excel默認(rèn)的透視表來(lái)做，會(huì)發(fā)現(xiàn)統(tǒng)計(jì)結(jié)果還是重復(fù)計(jì)數(shù)，非重復(fù)計(jì)數(shù)是灰色的。

怎么辦？

怎么辦？

怎么辦？

其實(shí)，特別簡(jiǎn)單，只需勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”即可。

數(shù)據(jù)模型功能建議至少用2016版本。

這樣，在“值匯總依據(jù)”中可以用“非重復(fù)計(jì)數(shù)”功能了。

結(jié)果也就顯示出來(lái)了。

看來(lái)，數(shù)據(jù)模型做的透視表還真不一樣。數(shù)據(jù)模型支持多表建立關(guān)系，Excel數(shù)據(jù)不再像信息孤島一樣，僅僅通過(guò)VLOOKUP建立聯(lián)系。而是組團(tuán)作戰(zhàn)，多張表可以形成互相關(guān)聯(lián)的數(shù)據(jù)庫(kù)，也就是模型。

根據(jù)數(shù)據(jù)模型可以實(shí)現(xiàn)多表關(guān)聯(lián)透視，我稱之為“超級(jí)透視”。

所以，將來(lái)表哥表姐見(jiàn)面了，會(huì)問(wèn)到“你用超級(jí)透視了嗎？”

Power Query數(shù)據(jù)查詢法

Power Query是從Excel 2016開始軟件內(nèi)置的數(shù)據(jù)查詢工具，我稱之為Excel最最強(qiáng)大的后臺(tái)，也就是我們要逐步更新一種觀念，Excel數(shù)據(jù)處理分為前臺(tái)和后臺(tái)兩種工具，我們平時(shí)看到的更多的就是Excel的前臺(tái)表格。

下圖顯示的是Office 2016和Office 365版本中Excel的Power Query功能。

Excel 2016

Excel 365

在Excel中與Power Query有關(guān)的功能都集中在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中，不同版本功能菜單名稱略有差別。

利用上面的工具我們可以開始數(shù)據(jù)加載，在進(jìn)行查詢編輯的時(shí)，Excel也會(huì)自動(dòng)打開“Power Query編輯器”。

回到我們的案例，首先要將數(shù)據(jù)導(dǎo)入PQ編輯器中

進(jìn)入PQ編輯器，點(diǎn)擊“分組依據(jù)”進(jìn)行分類匯總

按業(yè)務(wù)類別進(jìn)行計(jì)數(shù)

默認(rèn)的匯總結(jié)果如下，很顯然沒(méi)有去除重復(fù)數(shù)據(jù)。

這時(shí)候，需要改變上方的函數(shù)語(yǔ)句

原始語(yǔ)句為

= Table.Group(更改的類型, {'業(yè)務(wù)類別'}, {{'計(jì)數(shù)', each Table.RowCount(_), Int64.Type}})

更改為

= Table.Group(更改的類型, {'業(yè)務(wù)類別'}, {{'省份數(shù)量', each List.Count(List.Distinct(_[省份])), Int64.Type}})

最后，關(guān)閉并上載即可。

就將數(shù)據(jù)傳送到了Excel前臺(tái)表格中，還支持一鍵刷新。

Power Pivot度量值法

Power Pivot在Excel 作為一個(gè)“COM加載項(xiàng)”提供，默認(rèn)沒(méi)有啟用。下面我們介紹啟用改加載項(xiàng)的方法。

步驟1：在Excel的“文件”選項(xiàng)卡中選擇【選項(xiàng)】，出現(xiàn)的對(duì)話窗中左邊選擇【加載項(xiàng)】，右側(cè)窗口選擇【管理：COM加載項(xiàng)】，然后點(diǎn)擊【轉(zhuǎn)到】按鈕。

步驟2：出現(xiàn)的對(duì)話窗中勾選“Microsoft Power Pivotfor Excel”選項(xiàng)。

這里大家也可以選擇其它ExcelPower 加載項(xiàng)。完成加載后可以在功能區(qū)上看到對(duì)應(yīng)的工具選項(xiàng)卡。

首先需要將數(shù)據(jù)添加到數(shù)據(jù)模型中

添加后的Power Pivot窗口

退出Power Pivot窗口后，點(diǎn)擊“新建度量值”

度量值名稱：不重復(fù)省份數(shù)量

公式中輸入

=DISTINCTCOUNT('訂單表'[省份])

DISTINCT函數(shù)可以去除重復(fù)值

DISTINCTCOUNT函數(shù)是統(tǒng)計(jì)去除重復(fù)值后的數(shù)量

這些都是Power Pivot模型中的DAX函數(shù)。

建立好度量值后，就可以創(chuàng)建數(shù)據(jù)透視表，需要從數(shù)據(jù)模型中創(chuàng)建。

會(huì)發(fā)現(xiàn)透視表的字段列表中增加了剛剛建立的度量值

fx不重復(fù)省份數(shù)量

前面有fx標(biāo)記

將度量值和業(yè)務(wù)類別拖動(dòng)到對(duì)應(yīng)的統(tǒng)計(jì)位置即可

那么，問(wèn)題來(lái)了

透視表中的總計(jì)為什么不是13，而是7？可以在文末留言。

好了，這次關(guān)于不重復(fù)計(jì)數(shù)的教程就全部結(jié)束了，希望對(duì)你有幫助。