學不完．教不停．用不盡

網友詢問：在 Excel 中，如何比對兩組姓名是否有重覆並且標示性別是否相同。

參考下圖，在 Excel 的工作表裡的二組姓名裡，共有二個姓名重覆，其中一組性別相同，一組性別不同。

【公式解析】

儲存格G3：=IF(SUMPRODUCT((E3=$B$3:$B$27)*1),IF(SUMPRODUCT((E3=$B$3:$B$27)*(F3=$C$3:$C$27)),"相同性別","不同性別"),"")

將公式內容以結構化的表示：

=IF(

    SUMPRODUCT((E3=$B$3:$B$27)*1),

    IF(SUMPRODUCT((E3=$B$3:$B$27)*(F3=$C$3:$C$27)),

        "相同性別",

        "不同性別"

    ),

    ""

)

其中，欄位 B3:B27 放的是「姓名」資料，欄位 C3:C27 放的是「性別」資料。目前在儲存格 E3 有我們要查詢的姓名、F3 有我們要查詢的性別。

1. 判斷「姓名」是否存在於名單中

SUMPRODUCT((E3=$B$3:$B$27)*1)

(E3=$B$3:$B$27)會產生一個 TRUE/FALSE 的陣列，長度跟 B3:B27 一樣長。

利用「*1」，可以把 TRUE 轉成 1、FALSE 轉成 0。

SUMPRODUCT(...) 會把所有 1 加總在一起，得到「符合 E3 的姓名」出現的次數。

如果這個加總不為 0，就代表 E3 在 B3:B27 有出現；如果為 0，代表名單中沒有這個人名。

2. 若名單中有此姓名，進一步判斷性別是否也一致

SUMPRODUCT((E3=$B$3:$B$27)*(F3=$C$3:$C$27))

這裡先用 (E3=$B$3:$B$27) 找出跟 E3 相同的姓名位置，再用 (F3=$C$3:$C$27) 判斷 F3 與該筆姓名的性別是否也相同。

在陣列運算時，只有同一列「姓名相同 AND 性別相同」時，才會得到 TRUE*TRUE=1；其他不符合的列會是 0。

SUMPRODUCT(...) 會將符合的列加總，若結果大於 0，就代表至少有一筆「姓名和性別都相同」。

3. IF 條件判斷

IF(SUMPRODUCT((E3=$B$3:$B$27)*1), ...)：如果姓名出現次數 > 0，才執行下一個判斷；否則給空白字串 ""。

IF(SUMPRODUCT((E3=$B$3:$B$27)*(F3=$C$3:$C$27)), "相同性別", "不同性別")：如果同時符合「姓名相同」與「性別相同」的次數 > 0，顯示「相同性別」。否則顯示「不同性別」。

總結

SUMPRODUCT((E3=$B$3:$B$27)*1) 用來偵測 E3 是否出現在 B3:B27 這個範圍裡。

如果出現，進入下一個 IF，利用 SUMPRODUCT((E3=$B$3:$B$27)*(F3=$C$3:$C$27)) 檢查是否有筆資料同時「姓名與 E3 相同」且「性別與 F3 相同」。

若有找到相同姓名、相同性別，就顯示「相同性別」；若沒有，就顯示「不同性別」。

如果根本沒有此姓名（第一次 SUMPRODUCT 為 0），那就直接顯示空白 ""。