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

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

要求：

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

函數(shù)法

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

SUMPRODUCT(1/COUNTIF)

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

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

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

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

H7=COUNTA(UNIQUE(C2:C23))

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

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

組合函數(shù)為

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

條件1范圍和統(tǒng)計范圍在函數(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)))

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

公式變成了

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

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

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

怎么辦,？

怎么辦？

怎么辦,？

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

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

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

結(jié)果也就顯示出來了,。

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

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

所以,，將來表哥表姐見面了,，會問到“你用超級透視了嗎？”

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

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

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

Excel 2016

Excel 365

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

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

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

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

按業(yè)務類別進行計數(shù)

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

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

原始語句為

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

更改為

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

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

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

Power Pivot度量值法

Power Pivot在Excel 作為一個“COM加載項”提供，默認沒有啟用,。下面我們介紹啟用改加載項的方法,。

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

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

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

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

添加后的Power Pivot窗口

退出Power Pivot窗口后,，點擊“新建度量值”

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

公式中輸入

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

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

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

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

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

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

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

前面有fx標記

將度量值和業(yè)務類別拖動到對應的統(tǒng)計位置即可

那么，問題來了

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

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