Excelで1000件以上のIDリストを管理していて、別ファイルに保存していた「確認済みID（476件）」を元ファイル側に反映し忘れた――そんなときでも、手作業で1件ずつ探す必要はありません。条件付き書式・関数・Power Queryを使って、共有ファイル環境でも“確認済み”を一括で素早く判別する方法を、実務目線でまとめます。
今回の問題を整理：やりたいことは「含まれているか」を一括判定するだけ
やるべきことはシンプルです。
- 元ファイル（1000件超）のIDが
- 別ファイル（確認済み476件）のID一覧に
- 含まれているかどうか
これをExcelに判定させれば、確認済みの印（色・チェック・ラベル）を一括で付けられます。以降は、速度優先の方法から、運用しやすい方法、繰り返し突合に強い方法まで順に解説します。
| 方法 | おすすめ度 | 向いている状況 | 強み | 注意点 |
|---|---|---|---|---|
| 条件付き書式（色付け） | 非常に高い | とにかく今すぐ見分けたい／一度きり | 最速・直感的 | 「色」だけだと後工程で管理しづらい場合あり |
| 関数（判定列を作る） | 高い | 確認済み／未確認を列で管理したい | フィルター・集計が簡単 | 参照範囲・データ型のズレに注意 |
| Power Query（マージ） | 中〜高 | 突合作業を繰り返す／更新したい | 更新ボタンで再突合 | 基本はデスクトップ版Excelが必要 |
最速で見分ける：条件付き書式で「一致ID」を強調表示する
「どれが確認済みかを一気に目で見たい」なら、条件付き書式が最短ルートです。特に共有ファイルで作業時間が限られる場合、まずはこれが現場で強いです。
準備：確認済み476件を“同じブック内”の空き列に貼り付ける
外部ファイル参照でもできますが、リンクや権限・更新の問題が出やすいので、まずは同一ブック内に貼り付けるのが安全です。
- 元ファイルを開く（IDが列Aにある想定）
- 同じシートの空き列（例：列D）を用意
- 別ファイルの「確認済みID（476件）」を列Dに貼り付け
これで「元ID（列A）」と「確認済みID（列D）」が同じブック内に並びます。
手早い：重複する値ルールで色付けする
Excel標準の「重複する値」を使うと、数クリックで一致IDが浮き上がります。
- 元IDの範囲（例：A2:A1200）を選択
- Ctrlを押しながら確認済みIDの範囲（例：D2:D477）も追加選択
- 「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」
- 色（塗りつぶし）を選んでOK
この方法は速い反面、次のようなケースでは少しだけ注意が必要です。
- 元ID列の中に同じIDが2回以上出てくる（元データ側の重複も色が付く）
- 確認済みID側にも重複が混ざっている（貼り付け時の重複など）
「一致したものだけを元ID側で確実に色付けしたい」なら、次の“式を使う条件付き書式”がより実務向きです。
実務で強い：COUNTIF式で「確認済みだけ」を正確に色付けする
元ID（列A）だけを対象にして、「確認済みID（列D）に存在するか」を式で判定して色付けします。元ID列内の重複に引っ張られないのがメリットです。
手順
- 元IDの範囲（例：A2:A1200）だけを選択
- 「ホーム」→「条件付き書式」→「新しいルール」
- 「数式を使用して、書式設定するセルを決定」を選択
- 数式に次を入力（例：確認済みがD2:D477の場合）
=COUNTIF($D$2:$D$477,$A2)>0- 書式（塗りつぶし色など）を設定してOK
このルールで、列Aのうち「列Dに存在するID」だけが強調されます。見分けたい目的に直結していて、手戻りが少ない方法です。
ポイント（大事）
- 参照範囲は可能なら「必要行まで」に絞る（全列参照は重くなりやすい）
- $A2 の「列だけ固定・行は相対」の形にする（範囲全体に正しく適用される）
後からの管理が圧倒的に楽：判定列を作って「確認済み／未確認」を出す
色付けは速いのですが、次の作業（未確認だけ担当者に渡す、件数を集計する、チェック状況を更新する）に進むと「列で持っておいた方が圧倒的に楽」になります。
そこでおすすめなのが、元IDの隣に判定列（例：B列）を作って、関数でラベルを出す方法です。
一番シンプルで壊れにくい：COUNTIFで判定する
Microsoft 365/Office 365でなくても動く基本形で、現場ではこれが最も安定します。
例：B2に入れて下までコピー
=IF(COUNTIF($D$2:$D$477,$A2)>0,"確認済み","未確認")この形の良いところは次の通りです。
- 外部ブック参照にしなくても動く（同一ブック内に貼り付けておけばOK）
- 「確認済み／未確認」が文字で残るので、フィルター・集計が超簡単
- 一致しない理由があるときも、後述の“データ整形”で改善しやすい
チェックマークにしたい場合
=IF(COUNTIF($D$2:$D$477,$A2)>0,"✓","")チェックがある行だけフィルターで抽出する、という運用もしやすくなります。
Office 365なら高速：XMATCHで「見つかるか」を判定する
Microsoft 365/Office 365で使えるXMATCHは、見つかった位置を返せるため、判定用途にも向きます。
=IF(ISNUMBER(XMATCH($A2,$D$2:$D$477)),"確認済み","未確認")件数がさらに増えていく運用（数千〜数万行）でも、COUNTIFより扱いやすい場面があります。とはいえ、まずはCOUNTIFでも十分なケースが多いので、環境に合わせて選ぶのが現実的です。
一致したら「情報」も引きたい：XLOOKUPで確認日・担当者を返す
確認済みファイル側に「確認日」「担当者」「メモ」などがある場合、XLOOKUPでそれらを元リストに引っ張ってくると、管理レベルが一段上がります。
例：確認済みIDがD列、確認日がE列にある場合（見つからなければ空欄）
=IFNA(XLOOKUP($A2,$D$2:$D$477,$E$2:$E$477),"")例：見つからなければ「未確認」と表示する
=IFNA(XLOOKUP($A2,$D$2:$D$477,$D$2:$D$477),"未確認")さらに実務では、判定列と情報列を分けると運用が綺麗になります。
- B列：確認ステータス（確認済み／未確認）
- C列：確認日（XLOOKUPで引く）
- D列：担当者（XLOOKUPで引く）
関数の選び方がすぐ分かる比較表
| 関数 | できること | メリット | 向いているケース |
|---|---|---|---|
| COUNTIF | 存在するか（件数） | シンプル・互換性が高い | まず確実に判定したい／共有環境で安定させたい |
| XMATCH | 存在するか（位置） | 判定が分かりやすい／比較的高速 | Microsoft 365で作業、範囲が大きい |
| XLOOKUP | 存在判定＋情報取得 | 確認日・担当者なども一緒に戻せる | 突合だけでなく、管理列を育てたい |
共有ファイルで「自分が所有者ではない」場合の現実的な進め方
Office 365の共有ファイル（SharePoint/OneDrive上のExcelなど）だと、次の制約にぶつかりがちです。
- 編集権限がなく、列追加や貼り付けができない
- 勝手に書式を変えると嫌がられる（監査・運用ルール）
- 外部ブック参照のリンクが切れる／更新が面倒
その場合でも、次の回避策があります。
編集できるなら：元ファイルに「作業用の列」を最小限だけ足す
編集権限があるなら、以下のどれかで十分です。
- 空き列に確認済みIDを貼り、条件付き書式で色付け
- 空き列に判定列（確認済み／未確認）を作る
特に判定列は、関係者が見ても意味が分かりやすく、後工程（フィルター・集計）が楽なのでおすすめです。
編集できない（閲覧のみ）なら：自分用にコピーして突合する
閲覧権限のみで元ファイルに手を入れられない場合は、次の方針が安全です。
- 元ファイルのID列を自分のブックにコピー（またはエクスポート）
- 確認済み476件も同じ自分のブックに貼る
- 自分のブック上で照合して、結果（未確認一覧など）だけ共有する
「元ファイルを汚さない」「権限問題に引っかからない」という意味で、組織運用ではこの形が通りやすいです。
どうしても元ファイル側に反映が必要なら：「未確認一覧」だけ渡す
共有ファイルの所有者（または編集権限が強い担当者）が別にいるなら、あなたが作業しやすいのは次です。
- 自分のブックで照合
- 「未確認IDだけ」の一覧を抽出
- その一覧を所有者に渡して、元ファイルに印を付けてもらう
元ファイル側を直接編集できないときの落とし所として、最も揉めにくい運用です。
突合がうまくいかない原因の大半は「IDの見た目は同じでも中身が違う」
「確かに同じIDのはずなのに、COUNTIFで0になる」「条件付き書式で一致しない」――この手のトラブルは珍しくありません。原因の多くは、IDの表記揺れ・空白・データ型の違いです。
よくある不一致パターン
| 症状 | 原因 | 対策例 |
|---|---|---|
| 見た目は同じなのに一致しない | 前後に空白が入っている（半角/全角） | TRIMで前後空白を除去 |
| 一部だけ一致しない | 途中に見えない制御文字が混ざっている | CLEANで制御文字を除去 |
| 0から始まるIDが一致しない | 片方が数値、片方が文字列（先頭0が消える） | TEXTで文字列化／形式を統一 |
| ハイフンあり／なしで一致しない | 記号の表記揺れ | SUBSTITUTEで記号を除去 |
| 英字の大文字小文字で一致しない | ケース違い（ID仕様による） | UPPER/LOWERで統一 |
実務向け：突合前に「正規化（整形）ID」を作る
元IDと確認済みIDの両方に、同じ整形をかけた列（正規化列）を作ってから照合すると、ミスが激減します。例えば次のようにします。
空白と制御文字を除去して、文字列として扱う例
=TRIM(CLEAN(A2))ハイフンを取り除く例（-を消す）
=SUBSTITUTE(TRIM(CLEAN(A2)),"-","")英字を大文字に統一する例
=UPPER(TRIM(CLEAN(A2)))照合は「整形した列同士」で行うのがコツです。元データは触りたくない場合でも、隣に正規化列を作るだけで済みます。
先頭ゼロが大事なIDの注意点
「00123」のようなIDは、Excelが数値として扱うと「123」になり、別ファイル側と一致しなくなります。次のどれかで統一してください。
- ID列の表示形式を「文字列」にする（入力前が理想）
- 正規化列でTEXTを使って桁数を固定する（仕様が決まっているなら強い）
例：5桁固定で文字列化
=TEXT(A2,"00000")桁数が不定の場合は、表示形式の統一や、元データ仕様の確認が必要です（ここが曖昧なまま突合すると、後で必ず揉めます）。
未確認だけを一瞬で拾う：フィルター運用が最強
判定列（確認済み／未確認）が作れたら、次にやるべきは「未確認だけを抽出して作業対象を絞る」ことです。
- 判定列を含む表全体を選択
- 「データ」→「フィルター」
- 判定列で「未確認」だけを選択
これだけで、次の作業が速くなります。
- 未確認分だけを関係者に依頼する
- 未確認の件数をすぐ報告する
- 未確認のIDだけ別シートにコピーする
おまけ：未確認だけ別シートに“一覧化”したい場合
Microsoft 365ならFILTER関数で一覧を作れます（元データに手を入れたくないときに便利です）。
=FILTER(A2:A1200, B2:B1200="未確認")繰り返し突合するならPower Queryで「マージ」して自動化する
「今後も確認済みリストが増える」「毎週・毎月同じ突合をする」なら、Power Queryのマージ（結合）が強力です。やっていることはExcelの関数と同じ“突合”ですが、更新が圧倒的に楽になります。
Power Queryが向いているケース
- 確認済みIDが今後も増減する
- 元データが定期的に更新される
- 突合の手順を固定して、ミスを減らしたい
- 関数だらけのブックにしたくない
基本手順：2つの表を取り込んでマージする
ここでは「元ID一覧」と「確認済みID一覧」の2つがある想定で、最小構成の流れを示します。
- 元IDの範囲をテーブル化（範囲選択→Ctrl+T）し、テーブル名を付ける（例：tbl_master）
- 確認済みIDの範囲もテーブル化（例：tbl_verified）
- 「データ」→「テーブルまたは範囲から」それぞれをPower Queryに取り込む
- Power Queryエディターで「クエリの結合（マージ）」を選択
- 結合キー（ID列）を両方指定し、結合の種類は「左外部結合（第一をすべて、第二を一致）」を選ぶ
- 結合結果の列を展開し、「一致したかどうか」が分かる列を作る
- 「閉じて読み込む」でExcelに戻す
マージ後、「一致した行＝確認済み」としてフラグ列を作れば、関数をコピーし続ける必要がなくなります。確認済みリストが更新されたら、基本は“更新”するだけです。
注意点
- Power Queryは環境によってはExcelのデスクトップ版での作業が前提になることがあります
- 共有ブックの運用ルールによっては、クエリの追加が嫌がられることもあるため、事前に確認できると安全です
現場でありがちな「やり直し」を防ぐ小ワザ集
確認済みIDの貼り付け列は、別シートに隔離すると揉めにくい
同じシートに貼ると見た目が崩れたり、他の人が誤って触るリスクが上がります。次のどちらかが安全です。
- 「作業用」シートを作ってそこに貼る
- 元シートの右端に貼って、列を非表示にする（編集権限・運用ルールが許すなら）
条件付き書式は「対象範囲」を絞るだけで軽くなる
列全体（A:A）にルールを当てると、共有環境では重くなりがちです。実データがある範囲に絞るだけで体感が変わります。
「一致したのに未確認」になったら、まず正規化列を疑う
突合ミスの原因の大半は、空白・データ型・表記揺れです。関数を疑う前に、次の順でチェックすると早いです。
- LEN（文字数）で差がないか
- TRIM/CLEANで改善するか
- 先頭0や数値/文字列の混在がないか
例：文字数チェック（差分発見に便利）
=LEN(A2)ステータスを固定したいなら「値貼り付け」もあり
一度きりの突合で、今後は変化しない（確認済みリストも更新しない）なら、判定列の結果をコピーして「値として貼り付け」して固定してしまうのも有効です。リンクや参照切れの心配がなくなります。
おすすめの結論：目的別の“最短ルート”はこれ
最後に、状況別におすすめの選び方をまとめます。
| 状況 | 最適解 | 理由 |
|---|---|---|
| 今すぐ確認済みを見分けたい（一次対応） | 条件付き書式（COUNTIF式） | 速くて正確、元IDだけ色付けできる |
| 未確認だけ抽出して作業したい | 判定列（COUNTIF または XMATCH）＋フィルター | 運用が楽、報告・依頼がしやすい |
| 確認日・担当者も管理したい | XLOOKUPで情報列も取得 | 照合から“管理台帳化”まで一気に進む |
| 突合を繰り返す／更新が前提 | Power Queryでマージ | 更新ボタンで再突合、手順が固定できる |
まとめ：476件の確認済みIDは、Excelに判定させれば数分で片付く
「別ファイルに確認済みIDがあるのに、元ファイル側で印を付け忘れた」という状況でも、Excelなら一括照合で一気に解決できます。
- 最速で見分けるなら、条件付き書式（COUNTIF式）で一致だけ色付け
- 運用するなら、判定列を作ってフィルターで未確認だけ抽出
- 確認日や担当者まで管理するなら、XLOOKUPで情報も引く
- 繰り返し突合するなら、Power Queryでマージして更新型にする
まずは「同一ブック内に確認済み476件を貼って、COUNTIFで判定」から始めるのが、最短で失敗しにくい王道ルートです。
コメント