849

在儲存格範圍找出是否有重複
SUMPRODUCTCOUNTACOUNT函數

示範檔

範例檔

849.XLSX

結果檔

849F.XLSX

下面是一個政黨意見調查表,我們想要知道是否在五個選項中有重複選到同一個政黨?若有則顯示【O】符號,若無則顯示【X】符號,如下圖所示。

這對Excel來說可以非常容易解決這個問題。

步驟1:點取H3儲存格輸入公式「=IF(SUMPRODUCT(COUNTIF(B3:F3,B3:F3))>COUNTA (B3:F3),"","")」後,按Enter完成輸入。

COUNTIF函數解析

COUNTIF(Range,Criteria)  計算儲存格範圍內符合修件的儲存格數目

Range 為欲計算符合指定條件儲存格數目的範圍

Criteria 為比較條件,可以是數字、文字,用以指定哪些儲存格會被計算

COUNTA函數解析

COUNTA(Value1,Value2…..) 計算儲存格範圍中非空白儲存格數目

Value1,Value2….. `1255個參數,代表欲計算的值和儲存格

SUMPRODUCT函數解析

SUMPRODUCT(Array1,Array2….)  傳回各個陣列或範圍中相對應元素乘積之總和

Array1,Array2…. 為起2255個陣列,用以求其乘積後再加總,所有陣列大小須相同。

公式解析

=IF(SUMPRODUCT(COUNTIF(B3:F3,B3:F3))>COUNTA (B3:F3),"","") 將指定儲存格範圍與範圍中的儲存格作統計(COUNTIF(B3:F3,B3:F3)),透過SUMPRODUCT函數統計出現次數(SUMPRODUCT(COUNTIF(B3:F3,B3:F3))),以第四列為例傳回{1,2,1,2,1},加總後的值為8,若大於目前資料範圍的儲存格數目(COUNTA (B3:F3)),則傳回(""),若等於目前資料範圍的儲存格數目,則傳回("")

步驟2:將滑鼠指標移到H3儲存格右下角拖拉方塊上方,待指標變為「+」後,按滑鼠左鍵二下或按住滑鼠左鍵不放拖曳至H12儲存格,將H3儲存格公式複製H4:H12儲存格中,如下圖所示。

今天的教程就到這裡啦。希望大家能有收穫!


arrow
arrow

    錦子老師 發表在 痞客邦 留言(0) 人氣()