2016年2月15日 星期一

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID)

本篇文章純粹是要練習幾個 Excel 函數的應用,是否有實用性沒有考量到。參考下圖,有兩個儲存格想要比較其中內容,每一個位元的字元是否相同。
一、儲存格中的內容全為數字
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

(1) 計算各位元相同者
儲存格C5:
=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))=VALUE(MID($B2,
COLUMN(A:H),1))))
COLUMN(A:H):在 SUMPRODUCT 函數中代表數字 1 ~ 8。
MID($A2,COLUMN(A:H),1):在 SUMPRODUCT 函數中以陣列方式取出儲存格A2的第1個字元至第8個字。
VALUE(MID($A2,COLUMN(A:H),1)):將上式的結果(文字型態的數字)轉換為數值。
VALUE(MID($B2,COLUMN(A:H),1)):原理同上式。
SUMPRDUCT 函數中的『1*』,其作用為透過『乘以1』的運算動作,將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(2) 計算各位元不同者
儲存格C6:
=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))<>VALUE(MID($B2,
COLUMN(A:H),1))))
此公式和是將儲存格C5公式中的『=』改為『<>』,原理相同。

(3) 在對應儲存格中顯示各個字元是否相同,傳回『TRUE/FALSE』
儲存格C2:
=XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),1)))
複製儲存格C2,貼至儲存格C2:J2。
XOR 函數可以判斷兩個數是否相同,相同傳回 TRUE,不同傳回 FALSE

(4) 在對應儲存格中顯示各個字元是否相同,傳回『相同/不同』
儲存格C3:
=IF(XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),
1))),"不同","相同")
複製儲存格C3,貼至儲存格C3:J3。

二、儲存格中的內容全為文字
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)
儲存格C5:
=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)=MID($B2,COLUMN(A:H),1)))
儲存格C6:
=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)<>MID($B2,COLUMN(A:H),1)))
儲存格C2:
=MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1)
儲存格C3:
=IF(MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1),"不同","相同")
Excel在判斷文字是否相同時,會將同一字母大寫和小寫視為相同。而相同公式在儲存格內容全改為數字時仍可使用,也就是可以取代一、中的公式。這個公式比較簡短,也沒有用到XOR函數。其中數字可以被視為文字來處理。
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

沒有留言:

張貼留言

好康東東