- 相關(guān)推薦
excel多條件不重復(fù)數(shù)據(jù)
excel表格是我們工作時(shí)常會(huì)用到的，那么關(guān)于excel多條件不重復(fù)數(shù)據(jù)你又清楚操作嗎，以下是pincai小編搜集并整理的有關(guān)內(nèi)容，希望在閱讀之余對大家能有所幫助!
excel多條件不重復(fù)數(shù)據(jù)
→ 解決方案1：
使用1/COUNTIF與SUM函數(shù)組合統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù)。
→ 操作方法
在C2單元格輸入以下2個(gè)公式之一：
公式1 {=SUM(1/COUNTIF(A2:A16,A2:A16))}
公式2 =SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))
→ 原理分析
1.使用COUNTIF函數(shù)進(jìn)行條件統(tǒng)計(jì)，返回A2:A16單元格區(qū)域內(nèi)每個(gè)數(shù)據(jù)出現(xiàn)次數(shù)的數(shù)組：
{2;2;1;3;2;1;3;2;1;1;2;1;1;2;3}
被1除后，生成數(shù)組：
{1/2;1/2;1;1/3;1/2;1;1/3;1/2;1;1;1/2;1;1;1/2;1/3}
即出現(xiàn)N次重復(fù)的，就變成N個(gè)1/N，求和就是1，達(dá)到重復(fù)值只算1次的目的。
2.最后使用SUM或SUMPRODUCT函數(shù)求和即可得到區(qū)域內(nèi)不重復(fù)值的個(gè)數(shù)。
→ 知識(shí)擴(kuò)展
1/COUNTIF函數(shù)統(tǒng)計(jì)不重復(fù)值的優(yōu)缺點(diǎn)
1.統(tǒng)計(jì)區(qū)域內(nèi)不得有空單元格，否則返回#DIV/0!錯(cuò)誤。可以使用嵌套IF函數(shù)的數(shù)組公式解決這個(gè)問題，公式如下：
{=SUM(IF(A2:A16<>"",1/COUNTIF(A2:A16,A2:A16)))}
2.因?yàn)镋XCEL浮點(diǎn)運(yùn)算可能產(chǎn)生誤差而造成答案不正確，即公式返回值比正確值小。對于這種情況，可以用嵌套R(shí)OUND函數(shù)修正。
3.COUNTIF函數(shù)對數(shù)據(jù)類型沒有要求，文本、數(shù)值、邏輯值、錯(cuò)誤值均可，每一種錯(cuò)誤值算作一個(gè)不重復(fù)數(shù)據(jù)。
4.統(tǒng)計(jì)區(qū)域不限于單行或單列(即一維引用)，可以是多行多列的矩形區(qū)域，但必須是對單元格區(qū)域的引用，而不能是非引用類型的數(shù)組。
→ 解決方案2：
使用MATCH=ROW比較判斷統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù)。
操作方法
在C2單元格輸入下列2個(gè)公式之一：
公式1 {=SUM(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))}
公式2 =SUMPRODUCT(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))
→ 原理分析
1.使用MATCH函數(shù)返回區(qū)域內(nèi)每個(gè)數(shù)據(jù)第一次出現(xiàn)的位置數(shù)組：
{1;2;3;4;1;6;4;8;9;10;8;12;13;2;4}
2.然后與其對應(yīng)的行號(hào)位置比較，因?yàn)橹挥械谝淮纬霈F(xiàn)的位置才會(huì)一致，所以統(tǒng)計(jì)的是不重復(fù)個(gè)數(shù)。
3.使用減負(fù)運(yùn)算將判斷結(jié)果返回的邏輯值轉(zhuǎn)換為1、0，最后使用SUM或SUMPRODUCT求和即可得出不重復(fù)數(shù)據(jù)的個(gè)數(shù)。
→ 知識(shí)擴(kuò)展
MATCH=ROW法統(tǒng)計(jì)不重復(fù)值的優(yōu)缺點(diǎn)
1.統(tǒng)計(jì)區(qū)域內(nèi)不得有空單元格，否則MATCH函數(shù)返回#N/A錯(cuò)誤。對于包含空單元格的區(qū)域，可以用文本合并進(jìn)行相應(yīng)的處理，公式如下：
=SUMPRODUCT((A2:A16<>"")*(MATCH(A2:A16&"",A2:A16&"",0)=ROW(2:16)-1))
其中，(A2:A16<>"")用于防止將空單元格&""后算作一個(gè)空文本數(shù)據(jù)。
2.不會(huì)有浮點(diǎn)運(yùn)算誤差。
3.數(shù)據(jù)類型可以是文本、數(shù)值、邏輯值，但不得包含錯(cuò)誤值。
4.數(shù)據(jù)可以是內(nèi)存數(shù)組，也可以是單元格區(qū)域的引用，但必須是單行或單列。
→ 解決方案3：
使用FREQUENCY函數(shù)統(tǒng)計(jì)不重復(fù)數(shù)字個(gè)數(shù)。
→ 操作方法
在C2單元格輸入下列2個(gè)公式之一：
公式1 =COUNT(1/FREQUENCY(A2:A16,A2:A16))
公式2 =SUM(--(FREQUENCY(A2:A16,A2:A16)>0))
→ 原理分析
1.使用FREQUENCY函數(shù)返回統(tǒng)計(jì)區(qū)域內(nèi)數(shù)字的分布頻率數(shù)組：
{2;2;1;3;0;1;0;2;1;1;0;1;1;0;0;0}
由此可見，第一次出現(xiàn)的數(shù)字位置返回?cái)?shù)字出現(xiàn)個(gè)數(shù)，而第2次出現(xiàn)就返回0，因此只要統(tǒng)計(jì)非0的個(gè)數(shù)即可。
2.公式1使用1/FREQUENCY將0轉(zhuǎn)換為#DIV/0!錯(cuò)誤值，再利用COUNT函數(shù)忽略錯(cuò)誤值的特性統(tǒng)計(jì)數(shù)組中非0數(shù)字的個(gè)數(shù)，公式2使用--(FREQUENCY>0)將數(shù)組大于0判斷得到的邏輯值轉(zhuǎn)換為數(shù)值1、0，再使用SUM函數(shù)求和，由此實(shí)現(xiàn)統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù)。
3.由于FREQUENCY函數(shù)默認(rèn)返回?cái)?shù)組，COUNT、SUM函數(shù)均將其視為常量數(shù)組代入計(jì)算，因而無需按組合鍵形成數(shù)組公式。
→ 知識(shí)擴(kuò)展
FREQUENCY函數(shù)統(tǒng)計(jì)不重復(fù)值的優(yōu)缺點(diǎn)
1.統(tǒng)計(jì)區(qū)域可以有空單元格。因?yàn)镕REQUENCY函數(shù)將忽略空白單元格和文本。
2.使用公式2沒有浮點(diǎn)運(yùn)算誤差。
3.數(shù)據(jù)必須為數(shù)值，如果是一維的文本數(shù)據(jù)，可以借助MATCH函數(shù)轉(zhuǎn)換為序列號(hào)數(shù)值再進(jìn)行統(tǒng)計(jì)。
4.參數(shù)不受引用或數(shù)組的尺寸范圍限制，可以支持多行多列的數(shù)值數(shù)據(jù)的不重復(fù)統(tǒng)計(jì)。
數(shù)據(jù)去重必學(xué)函數(shù)UNIQUE函數(shù)介紹
功能：UNIQUE函數(shù)可以去除重復(fù)值保留唯一值
語法：=UNIQUE(數(shù)組，[按列]，[僅出現(xiàn)一次])
第1參數(shù)：數(shù)組就是返回唯一值的數(shù)組數(shù)據(jù)區(qū)域；
第2參數(shù)：按列是可選參數(shù)，指定比較的方式，設(shè)置為TRUE將比較列并返回唯一值，設(shè)置為FALSE (或省略) 將比較行并返回唯一值；
第3參數(shù)：[僅出現(xiàn)一次]可選參數(shù)，一般直接省略即可。
備注：FILTER函數(shù)需更新至WPS Office最新版本使用
基本用法：
在目標(biāo)單元格中輸入公式：
=UNIQUE(A1:A8)
然后點(diǎn)擊回車，即可
解讀：
公式中A1:A8就是要去重的數(shù)據(jù)區(qū)域；省略了第二參數(shù)默認(rèn)按行去重并返回唯一值；省略的三參數(shù)默認(rèn)是FALSE返回唯一選項(xiàng)。
當(dāng)然如果是去重?cái)?shù)據(jù)是按行橫向排列的話，就必須把第二參數(shù)設(shè)置為TRUE，就是按列去重。
在目標(biāo)單元格中輸入公式：
=UNIQUE(B2:I2,TRUE)
高級(jí)用法一：計(jì)數(shù)不重復(fù)人數(shù)
在目標(biāo)單元格中輸入公式：
=COUNTA(UNIQUE(A2:A8))
然后點(diǎn)擊回車即可
當(dāng)然如果想計(jì)算重復(fù)的人數(shù)，可以使用姓名總數(shù)減去唯一值的個(gè)數(shù)即可
公式：=COUNTA(A2:A8)-COUNTA(UNIQUE(A2:A8))
高級(jí)用法二：動(dòng)態(tài)提取不重復(fù)數(shù)據(jù)
在目標(biāo)單元格中輸入公式：
=DROP(UNIQUE(A:A),-1,0)
然后點(diǎn)擊回車即可，如果參會(huì)名單變動(dòng)，提取的不重復(fù)數(shù)據(jù)也會(huì)自動(dòng)更新。
解讀：
�、偕厦婀绞褂肈ROP函數(shù)和UNIQUE函數(shù)組合，主要是為了實(shí)現(xiàn)根據(jù)參會(huì)人員變動(dòng)，達(dá)到動(dòng)態(tài)提取不重復(fù)數(shù)據(jù)的效果。
②首先使用UNIQUE(A:A)函數(shù)提取A列參會(huì)人員名稱中不重復(fù)的數(shù)據(jù)，因?yàn)檫x擇的是這列數(shù)據(jù)，使用UNIQUE函數(shù)后會(huì)在結(jié)果下方多出一個(gè)數(shù)字0。出現(xiàn)這種情況的原因是UNIQUE函數(shù)在對A列最底部的空白單元格去重時(shí)，會(huì)默顯示數(shù)字0。
�、圩詈笤偈褂肈ROP函數(shù)去掉去重?cái)?shù)據(jù)的最后一行數(shù)據(jù)0，第2參數(shù)-1就是從下往上刪除一行；第3個(gè)參數(shù)為0，表示刪除0列，這樣就最終得到我們想要的去重的結(jié)果了。
高級(jí)用法三：按條件提取不重復(fù)數(shù)據(jù)
在目標(biāo)單元格中輸入公式：
=UNIQUE(FILTER(B2:B9,A2:A9=E2,"無數(shù)據(jù)"))
然后點(diǎn)擊回車即可
解讀：
1、公式中首先通過FILTER函數(shù)，按條件篩選出指定門店的值班經(jīng)理名單，然后再通過UNIQUE函數(shù)提取出不重復(fù)的名單數(shù)據(jù)即可。
2、上面實(shí)例中是按單個(gè)條件提取不重復(fù)數(shù)據(jù)，我們也可以進(jìn)行多條件提取不重復(fù)數(shù)據(jù)，只需正確設(shè)置FILTER函數(shù)第2個(gè)參數(shù)即可
�、偃绻枰鄠�(gè)條件同時(shí)滿足，就用*把多個(gè)條件連接
條件1*條件2*條件N
例如：(A2:A9=E2)*(C2:C9=D2)
�、谌绻枰鄠�(gè)條件滿足任意一個(gè)，就用+把多個(gè)條件連接
條件1+條件2+條件N
例如：(A2:A9=E2)+(C2:C9=D2)
【excel多條件不重復(fù)數(shù)據(jù)】相關(guān)文章：
excel函數(shù)怎么條件篩選06-24
Excel數(shù)據(jù)庫函數(shù)介紹06-24
Excel數(shù)據(jù)庫函數(shù)簡介06-24
Excel 2007的數(shù)據(jù)分析介紹07-10