「片方の一覧にしかない番号を探したい」「同じ番号が二重に入っていないか確かめたい」。そんなときは、相手の一覧に同じ番号が何件あるかを数えると、確認する行を整理できます。
この記事では、COUNTIFS関数で件数を数え、フィルターで対象を絞り、反対側からも確認する手順を紹介します。動画と同じ架空データを使って、ご自身のExcelで練習できます。
学べることは、次の3つです。
- 相手の一覧に見当たらない番号を探す
- 同じ番号が複数ある行を確認する
- AからB、BからAの両方向を調べる
練習用Excelをダウンロードする
Excelファイルには、次の4シートが入っています。マクロや外部データ接続は使っていません。ダウンロード後は、練習用に別名で保存してください。
| シート | 使い方 |
|---|---|
| 00_使い方 | ファイルの使い方と練習の順番 |
| 01_練習 | 記事を見ながら数式を入力するシート |
| 02_完成 | 入力済みの数式と結果を確認する解答シート |
| 03_検証 | 解答の計算値と期待値を見比べるシート |
この記事は、このファイルのセル位置で説明します。Excelの版や環境によってメニューの表示が異なる場合があります。
今回照合する2つの一覧
「01_練習」を開いてください。左側が資料A、右側が資料Bです。番号はすべて文字列として用意しています。
| 行 | 資料Aの番号：A列 | 資料Bの番号：F列 |
|---|---|---|
| 5 | 001 | 001 |
| 6 | 002 | 002 |
| 7 | 003 | 002 |
| 8 | ― | 004 |
資料Aは3件、資料Bは4件です。表の「―」は説明上の表記で、ExcelのA8は空欄です。資料Aを数える範囲にはA8を含めません。
このデータで確認したいのは、次の3点です。
- 資料Aの「003」は、資料Bに見当たりません。
- 資料Bの「002」は、2行あります。
- 資料Bの「004」は、資料Aに見当たりません。
最初はこの少ない件数で、数式と結果のつながりを確かめましょう。
手順1：Aの番号がBに何件あるか数える
B5へ数式を入力する
「01_練習」のB5を選び、次の式を入力してEnterで確定します。
=COUNTIFS($F$5:$F$8,A5)
この式は、F5からF8の中に、A5と同じ番号が何件あるかを数えます。
| 数式の部分 | この練習での意味 |
|---|---|
$F$5:$F$8 |
探す場所。資料Bの番号4件 |
A5 |
探す番号。資料Aの同じ行の番号 |
$は、式をコピーしたときに参照先を固定するための記号です。ここでは、探す場所をF5:F8に固定します。探す番号のA5には$を付けず、行ごとに変わるようにします。
B7までコピーする
B5をコピーし、B6:B7へ貼り付けます。得たい結果は、次のとおりです。
| 資料Aの番号 | Bにある件数 |
|---|---|
| 001 | 1 |
| 002 | 2 |
| 003 | 0 |
B6の式は、次の形になります。探す範囲は同じままで、条件がA6に変わっています。
=COUNTIFS($F$5:$F$8,A6)
0件・1件・2件以上を、確認する理由として分けることが大切です。
| 件数 | 読み取れること | 次に確認すること |
|---|---|---|
| 0件 | 指定した範囲に同じ番号が見当たらない | 転記漏れ、表記、範囲指定など |
| 1件 | 指定した範囲に同じ番号が1つある | 必要に応じて内容や金額など |
| 2件以上 | 指定した範囲に同じ番号が複数ある | 二重入力か、正当な分割明細かなど |
1件は番号の存在を確認した結果です。取引内容や金額の正しさは、別に確かめます。
手順2：見当たらない番号と複数ある番号を分けて表示する
一覧の件数が増えたときは、件数列のフィルターを使うと確認する行を絞れます。
- B4「Bにある件数」のフィルターボタンを開きます。
- 選択項目のうち、0だけを選んで確定します。
- 資料Aの「003」の行が残ることを確認します。
- フィルターをクリアし、全3行へ戻します。
- 今度は2だけを選び、「002」の行を確認します。
- 確認後は、もう一度フィルターをクリアします。
今回の複数件は2件だけなので、2を選んでいます。実際の一覧で3件以上もある場合は、数値フィルターで「1より大きい」などの条件を指定します。
手順3：Bの一覧そのものの重複も確認する
資料Bの「002」が2行あることを、資料B側でも確認してみましょう。
色で重複を見つける
- F5:F8を選択します。
- 「ホーム」から「条件付き書式」を開きます。
- 「セルの強調表示ルール」から「重複する値」を選びます。
- 読みやすい書式を選んで確定します。
「002」の2つのセルが強調されることを確認します。色の付いた行を削除する前に、なぜ同じ番号があるのかを元資料で調べます。
自分の一覧に何件あるかも数える
H5に次の式を入力し、H8までコピーします。
=COUNTIFS($F$5:$F$8,F5)
今回は資料B自身の中を探すため、探す番号にもF5を指定しています。
| 資料Bの番号 | B内の件数 |
|---|---|
| 001 | 1 |
| 002 | 2 |
| 002 | 2 |
| 004 | 1 |
重複している番号の種類は「002」の1種類で、該当する行は2行です。種類の数と行数を区別して読みます。
手順4：反対に、Bの番号がAに何件あるか数える
ここまでは主に、資料Aを起点に調べました。次は資料Bを起点にして、資料Aを探します。
G5へ次の式を入力し、G8までコピーしてください。
=COUNTIFS($A$5:$A$7,F5)
得たい結果は、次のとおりです。
| 資料Bの番号 | Aにある件数 |
|---|---|
| 001 | 1 |
| 002 | 1 |
| 002 | 1 |
| 004 | 0 |
G列で0件を表示すると、「004」が確認できます。AからBへ調べるだけでは、この番号を拾えません。
また、「002」のAにある件数は1ですが、B内の件数は2です。相手側に何件あるかと、自分側で重複しているかは、それぞれ確認します。
解答と比べて、結果を確かめる
「02_完成」には、今回の式と確認メモを入れています。A側の「A内の件数」と両側の「確認メモ」は、結果を読みやすくするための補助列です。まずは記事で扱ったB列・G列・H列が合っていれば、基本の練習ができています。
「03_検証」では、解答シートの計算値と期待値を比較できます。自分で作った練習シートは、このシートで自動採点される仕組みではありません。「01_練習」の結果を、記事の表や「02_完成」と見比べてください。
追加練習：番号を1つ変更してみる
- 「02_完成」のF8を、004から003へ変更します。
- B7「資料Aの003がBにある件数」が、0から1になるか確かめます。
- G8「資料Bの003がAにある件数」も、1になるか確かめます。
- 確認したら、F8を004へ戻します。
この間、「03_検証」は元データの期待値と異なるため、一部が「要確認」になります。F8を004へ戻すと、元の期待値と比較できる状態に戻ります。
うまくいかないときの確認ポイント
| 状況 | 確認すること |
|---|---|
| 式をコピーすると結果がおかしくなる | 探す範囲に$が付き、行ごとの条件だけが変わっているか |
| 数式が文字のまま表示される | 入力先が文字列形式になっていないか。必要なら標準に変更して式を入れ直す |
| 行が見えなくなった | フィルターが残っていないか。両側の表を全件表示へ戻す |
| 同じに見える番号が気になる | 前後の空白、全角・半角、元資料の番号の表し方を確認する |
| 行を追加したのに結果へ入らない | 数式の範囲が、追加した行まで含んでいるか |
| 解答の数字が更新されない | Excelの計算方法と再計算後の結果を確認する |
先頭の0を含む番号は、入力や取り込みの段階で文字列としてそろえると管理しやすくなります。すでに失われた桁数は、元資料で確かめます。
実際の資料で使う前に確認したいこと
今回の式は、A5:A7とF5:F8に範囲を固定した学習用です。空欄や記号を含まない架空の番号を使っています。
- 会社別・年度別・月別に番号を付け直す場合は、番号と一緒に何を照合するかを決めます。
- 空欄を照合対象に含める場合は、空欄の意味と集計条件を別途確認します。
*や?はCOUNTIFSの条件で特別な意味を持つ記号です。そのような番号に今回の式をそのまま使わず、条件の指定方法を確認してください。- 大文字と小文字を区別する番号の照合は、別の方法を検討します。
- 行を増やして使うときは、参照範囲も見直します。
- 入金の確定、二重登録の削除、取引内容の判断は、元資料と運用に照らして行います。
まとめ：両方向と重複を確認する
2つの一覧を照合するときは、相手側に同じ番号が何件あるかを数え、0件と複数件を分けて確認します。反対側からも調べ、自分の一覧にある重複も確かめましょう。
気になる行が分かったら、元資料に戻って理由を確認します。まずは練習ファイルの3件と4件で、式と結果のつながりを試してみてください。
