墨墨鋒INKACUITY

墨鋒 › 教學 › Excel

Excel教學約 3 分鐘4 個段落

Excel 用 COUNTIF 比對兩份名單,找出缺漏的人

報名表對繳費名單、點名表對問卷回覆,用眼睛一個個對很容易漏。只要一個 COUNTIF 公式,結果為 0 的就是對方名單裡沒有的人,幾秒鐘就能對完。

墨鋒 Inkacuity 整理 · 更新於 2026-11-13

分享這篇教學LINEFacebookXThreads

01原理:COUNTIF 數出「有沒有」

COUNTIF(範圍, 條件) 會計算範圍內符合條件的儲存格數量。把 A 名單的每個名字拿去 B 名單裡數,數到 0 代表 B 名單沒有這個人,數到 1 以上代表有出現。

假設 A 名單在 A2:A50(要檢查的完整名單),B 名單在 D2:D40(對照名單)。我們要找出『A 名單裡,沒出現在 B 名單的人』。

02步驟:在旁邊加一欄公式

  1. 先清理資料:在空白欄輸入 =TRIM(A2) 並往下填滿,再複製、以值貼回,去掉名字前後多餘的空格(B 名單也一樣處理)。
  2. 在 A 名單右邊的 B2 輸入:=COUNTIF($D$2:$D$40, A2)。其中 $D$2:$D$40 要加上 $ 固定範圍,向下填滿時才不會位移。
  3. 將公式往下填滿到 A 名單最後一列(雙擊儲存格右下角的小方塊即可)。
  4. 結果為 0 的列,就是這個人不在 B 名單裡;結果大於 1 代表 B 名單內重複出現。
  5. 用篩選功能:選取標題列→資料→篩選,在公式欄只勾選 0,就能一次看到所有缺漏的人。

小技巧 想讓結果更直覺,可改寫成 =IF(COUNTIF($D$2:$D$40,A2)=0,"缺","已有"),直接顯示文字。

卡住了? 公式或表格卡住了?傳檔案說明需求,文書製作含基礎 Excel,NT$300 起。

取得報價 →

03常見問題:明明有,卻顯示 0

  • 多餘空格:名字前後或中間有空白,看起來一樣但 Excel 視為不同字串,先用 TRIM 清理。
  • 全形半形差異:例如英文名字的大小寫沒問題,但全形空白( )TRIM 不會處理,可用 SUBSTITUTE 把全形空白換成空字串。
  • 名字有錯字或不同寫法:例如『王小明』與『王曉明』,需要人工確認,公式無法判斷。
  • 範圍沒鎖定:忘了加 $,往下填滿後範圍位移,導致後面的人都算成 0。
  • 名字含 * 或 ? 符號:COUNTIF 會把它們當萬用字元,這種情況改用 SUMPRODUCT(--(範圍=A2)) 比較保險。

04反過來也要對一次

上面只找出『A 有、B 沒有』的人。若想知道 B 名單裡有沒有 A 名單不存在的人(例如繳費了但沒報名),把兩份名單的位置對調,再做一次同樣的公式即可。兩邊都對過,才算真正完成對帳。

如果你需要的不只是有沒有,還要把對方的其他欄位(例如繳費金額)帶過來,可以改用查表函數,參考 Excel VLOOKUP 怎麼用?查表公式教學;查不到時想顯示自訂文字,請看 Excel IFERROR 搭配 VLOOKUP。更多 COUNTIF 的基本用法,可參考 Excel 常用函數:SUM、IF、COUNTIF 一次學會。

Q&A常見問題

COUNTIF 比對兩份名單,名字重複怎麼辦?

結果大於 1 就代表對照名單內有重複。可用篩選找出大於 1 的列,再人工確認是否為同名同姓或重複登錄。

可以用顏色標示缺漏的人嗎?

可以。選取 A 名單後,到常態格式設定→新增規則→使用公式,輸入 =COUNTIF($D$2:$D$40,A2)=0 並設定填滿色,缺漏者就會自動變色。

兩份名單在不同工作表也能比對嗎?

可以,把範圍改成 工作表名稱!$D$2:$D$40 的形式即可,例如 =COUNTIF(名單B!$D$2:$D$40,A2)。

名單有幾千筆,COUNTIF 會很慢嗎?

幾千筆通常沒問題。若到數萬筆以上覺得卡頓,可考慮先排序再用查表函數,或改用樞紐分析表整理。

覺得有用?分享給需要的人。

分享這篇教學LINEFacebookXThreads

不想自己弄?交給墨鋒

文書製作,NT$300 起。資料整理與文件製作,也接基礎 Excel:表格整理、常用公式、樞紐分析與圖表。

相關教學

X

Excel VLOOKUP 怎麼用?查表公式教學

Excel VLOOKUP 教學:用學號查姓名、用代碼查價格,四個參數一次看懂;找不到時顯示 #N/A 的原因與解法,以…

Excel · 約 3 分鐘 →
X

Excel 樞紐分析表教學

Excel 樞紐分析表入門:把一大串資料依類別加總、計數、算平均,拖拉欄位就能做出統計表,再一鍵做成樞紐分析圖。附資料整…

Excel · 約 3 分鐘 →
X

Excel 常用函數:SUM、IF、COUNTIF 一次學會

Excel 基礎函數整理:加總 SUM、平均 AVERAGE、四捨五入 ROUND、判斷 IF、條件計數 COUNTIF…

Excel · 約 3 分鐘 →