如下圖所示的工作表，使用公式來統(tǒng)計單元格區(qū)域C2:C6中沒有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)的數(shù)量。

也就是說，單元格區(qū)域C2:C6中凡是沒有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)，都是無效數(shù)據(jù)。從工作表中可以明顯看出，單元格區(qū)域C2:C6中的“SX006”沒有出現(xiàn)在單元格區(qū)域A2:A9中，因此“SX006”為無效數(shù)據(jù)，即單元格區(qū)域C2:C6中的無效數(shù)據(jù)為1。

如何使用公式來統(tǒng)計呢？

先不看答案，自已動手試一試。

公式思路

在單元格區(qū)域A2:A9中查找單元格區(qū)域C2:C6中的每個值，統(tǒng)計沒有找到的值的數(shù)量。

公式解析

在單元格E1中輸入下面的數(shù)組公式：

=SUM(1*ISNA(MATCH(C2:C6,A2:A9,0)))

其值為1，表明單元格區(qū)域C2:C6中沒有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)數(shù)為1。如下圖所示。

公式中，MATCH函數(shù)在單元格區(qū)域A2:A9中依次查找C2:C6中的值，返回各個值在A2:A9中的位置，如果沒有找到則返回#N/A，結(jié)果為{2;6;2;8;#N/A}，將其作為ISNA函數(shù)的參數(shù)，得到結(jié)果{FALSE;FALSE;FALSE;FALSE;TRUE}，然后與1相乘，將其轉(zhuǎn)換為{0;0;0;0;1}，作為SUM函數(shù)的參數(shù)得到結(jié)果1。

ISNA函數(shù)檢測一個值是否為#N/A，返回TRUE或FALSE。檢測值可以是一個單元格、公式，或者是一個單元格、公式或數(shù)值的名稱。

小結(jié)

使用1與布爾值相乘，將布爾值轉(zhuǎn)換為0或1。

本例具有一定的實用性，即可以用于判斷輸入的數(shù)據(jù)是否是數(shù)據(jù)庫已經(jīng)規(guī)定的數(shù)據(jù)。例如，代表設(shè)備編碼庫的單元格區(qū)域A2:A9是已經(jīng)規(guī)定的合法數(shù)據(jù)，而單元格區(qū)域C2:C6是實際工作中輸入的數(shù)據(jù)，那么可以用這個公式來判斷實際輸入的數(shù)據(jù)是否是已規(guī)定的合法數(shù)據(jù)。

可以使用名稱來替換單元格區(qū)域，使公式更靈活。