Vlookup函數(shù)是大家公認(rèn)的NO.1函數(shù)，但它只能用于查找。在Excel中還有一個(gè)函數(shù)比它更有用，它就是:

Countif函數(shù)

本文示例

• 一對(duì)一對(duì)比兩列數(shù)據(jù)

• 多對(duì)多對(duì)比兩列數(shù)據(jù)

• 禁止重復(fù)輸入

• 輸入時(shí)必須包含指定字符

• 幫助Vlookup實(shí)現(xiàn)一對(duì)多查找

• 統(tǒng)計(jì)不重復(fù)值的個(gè)數(shù)

1

一對(duì)一核對(duì)兩列數(shù)據(jù)

【例】如下圖所示，要求對(duì)比A列和C列的姓名，在B和D列出哪些是相同的，哪些是不同的。

公式：

B2 =IF(COUNTIF(D:D,A2)>0,'相同','不同')

D2 =IF(COUNTIF(A:A,D2)>0,'相同','不同')

2

多對(duì)多核對(duì)兩列數(shù)據(jù)

【例】如下面的兩列數(shù)據(jù)，需要一對(duì)一的金額核對(duì)并用顏色標(biāo)識(shí)出來(lái)。

步驟1在兩列數(shù)據(jù)旁添加公式，用Countif函數(shù)進(jìn)行重復(fù)轉(zhuǎn)化。

=COUNTIF(B$2:B2,B2)&B2

步驟2 按ctrl鍵同時(shí)選取C和E列，開始 - 條件格式 - 突出顯示單元格規(guī)則 - 重復(fù)值。

設(shè)置完成后后，紅色的即為一一對(duì)應(yīng)的金額，剩下的為未對(duì)應(yīng)的。如下圖所示

3

禁止重復(fù)錄入

數(shù)據(jù) - 有效性（2016版為數(shù)據(jù)驗(yàn)證） - 序列 - 輸入公式

=countif(f$3:f$15,f3)=1

播放GIF

4

輸入內(nèi)容必須包括指定字符

【例】在列輸入的內(nèi)容，必須包含字母A。

=COUNTIF(H1,'*A*')=1

如果輸入不含A的字符就會(huì)警示并無(wú)法輸入

5

幫助Vlookup函數(shù)實(shí)現(xiàn)一對(duì)多查找

【例】如下圖所示左表為客戶消費(fèi)明細(xì)，要求在F:H列的藍(lán)色區(qū)域根據(jù)F2的客戶名稱查找所有消費(fèi)記錄。

步驟1在左表前插入一列并設(shè)置公式，用countif函數(shù)統(tǒng)計(jì)客戶的消費(fèi)次數(shù)并用&連接成 客戶名稱+序號(hào)的形式。

A2: =COUNTIF(C$2:C2,C2)&C2

步驟2在F5設(shè)置公式并復(fù)制即可得到F2單元格中客戶的所有消費(fèi)記錄。

=IFERROR(VLOOKUP(ROW(A1)&$F$2,$A:$D,COLUMN(B1),0),'')

6

計(jì)算唯一值個(gè)數(shù)

【例】統(tǒng)計(jì)A列產(chǎn)品的個(gè)數(shù)

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))