本篇文章純粹是要練習幾個 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)

vincent 發表在 痞客邦 留言(1) 人氣()