2017年6月23日 星期五

Excel-比對二組資料在指定起始和終止欄位內計算相同個數(SUMPRODUCT,INDIRECT)

網友提問:在 Excel 的工作表中有二組資料(如下圖),如何指定起始和終止欄位,並計算在這個區間中二組相同和不相同的個數?
Excel-比對二組資料在指定起始和終止欄位內計算相同個數(SUMPRODUCT,INDIRECT)

【公式設計與解析】
儲存格B6:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))=
(INDIRECT(B4&"2:"&B5&"2"))))
(1) INDIRECT(B4&"1:"&B5&"1")
利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D1:H1)。
(2) INDIRECT(B4&"2:"&B5&"2"):
利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D2:H2)。
(3) (INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))
在 SUMPRODUCT 函數中判斷第(1)式和第(2)式的儲存格陣列是否相同,傳回 TRUE/FALSE
(4) 1*(INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))
將第(3)式傳回的 TRUE/FALSE,轉換為 1/0
第(4)式所有 1 的個數和,即為第1列和第2列相同的個數。

同理:
儲存格B7:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))<>
(INDIRECT(B4&"2:"&B5&"2"))))

沒有留言:

張貼留言

好康東東