Excelで複数条件を突合する方法｜COUNTIFS・SUMIFSの使い方

伝票番号だけで検索したら、取引先の違う行まで候補に出る。伝票番号と取引先をつないだ補助列を作ったら、別の組み合わせが同じ文字列になっていた。複数条件の突合は、どの列を合わせて1件とみなすのかを先に決めないと、金額が合っていても別の行を結び付けることがあります。

取引先・伝票番号・明細番号で行を特定するなら、COUNTIFSで一致件数を数えます。0件は相手側に未登録、1件は一意候補、2件以上は重複または分割明細の確認が必要です。SUMIFSは件数と業務上の粒度を確かめた後に使います。なお、Microsoft公式サポートの関数仕様ページは2026-10-06に参照しています。

取引先・伝票・明細番号で照合する例

次の架空データでは、同じ伝票の2行目が台帳で2行に分かれています。取引先、伝票番号、明細番号の3列をキーにします。

「請求」シートのA1:D6：

取引先	伝票番号	明細番号	請求金額
X社	2026-041	1	12000
X社	2026-041	2	8000
Y社	2026-041	1	12000
Z社	2026-041	1	9500
Q社	2026-041	1

「台帳」シートのA1:D7：

取引先	伝票番号	明細番号	台帳金額
X社	2026-041	1	12000
X社	2026-041	2	7500
X社	2026-041	2	500
Y社	2026-041	1	12000
W社	2026-041	1	7000
Q社	2026-041	1	0

式を使う前に、請求金額と台帳金額が数値として入っているか確認します。次の2式が0でなければ文字列の金額が混ざっています。修正してから照合してください。空欄は検査結果に含まれません。

=COUNTA(請求!$D$2:$D$6)-COUNT(請求!$D$2:$D$6)
=COUNTA(台帳!$D$2:$D$7)-COUNT(台帳!$D$2:$D$7)

請求シートのE1:I1に「台帳件数」「金額入力件数」「台帳金額合計」「請求キー件数」「判定」と入力し、2行目へ次の式を入れて下へコピーします。COUNTIFSは複数条件をすべて満たす行を数えます。条件範囲は同じ行数にそろえます。Microsoft COUNTIFSの仕様

請求シートのE2で台帳側にある複合キーの件数を数えます。

=COUNTIFS(台帳!$A$2:$A$7, A2, 台帳!$B$2:$B$7, B2, 台帳!$C$2:$C$7, C2)

F2で該当行の金額入力数、G2で台帳金額の合計を求めます。

=COUNTIFS(台帳!$A$2:$A$7, A2, 台帳!$B$2:$B$7, B2, 台帳!$C$2:$C$7, C2, 台帳!$D$2:$D$7, "<>")

=SUMIFS(台帳!$D$2:$D$7, 台帳!$A$2:$A$7, A2, 台帳!$B$2:$B$7, B2, 台帳!$C$2:$C$7, C2)

H2で請求側の複合キー件数を数えます。I2で両側の件数を見て判定します。各式を指定したセルに入力し、下へコピーしてください。

=COUNTIFS($A$2:$A$6, A2, $B$2:$B$6, B2, $C$2:$C$6, C2)

=IF(H2>1, "請求で重複", IF(E2=0, "台帳に未登録", IF(E2>1, "複数行を確認", IF(F2=0, "台帳金額未入力", IF(D2="", "請求金額未入力", IF(D2=G2, "金額一致", "金額差異"))))))

取引先・伝票・明細	台帳件数	金額入力件数	台帳金額合計	請求キー件数	判定
X社・2026-041・1	1	1	12,000	1	金額一致
X社・2026-041・2	2	2	8,000	1	複数行を確認
Y社・2026-041・1	1	1	12,000	1	金額一致
Z社・2026-041・1	0	0	0	1	台帳に未登録
Q社・2026-041・1	1	1	0	1	請求金額未入力。0円と扱わない

X社の2行目は台帳件数が2ですが、合計は請求金額と同じです。これは分割計上として認められる場合も、誤った重複の場合もあります。合計が同じという理由だけで一致とせず、明細を分けてよい業務ルールか確認します。

金額が空欄の行は、SUMIFSだけでは合計0と区別しづらいため、金額入力件数も数えています。請求側の金額が空欄で台帳側が数値の0でも「請求金額未入力」となります。文字列の金額は事前検査で修正し、0件・複数件・金額空欄を確認してから比較してください。

未登録の確認は反対側にも行う

上の式は請求シートの各行に対して台帳を検索しています。台帳側でもキーの重複と請求側への登録を確認します。台帳シートE1に「台帳内キー件数」、F1に「請求件数」と入力し、E2とF2に次の式を入れてそれぞれ下へコピーします。E列の2以上は台帳側の重複、F列の0は請求側に未登録です。

=COUNTIFS($A$2:$A$7, A2, $B$2:$B$7, B2, $C$2:$C$7, C2)

=COUNTIFS(請求!$A$2:$A$6, A2, 請求!$B$2:$B$6, B2, 請求!$C$2:$C$6, C2)

台帳の取引先・伝票・明細	台帳内キー件数	請求件数	判定の目安
X社・2026-041・1	1	1	1件ずつ存在
X社・2026-041・2	2	1	台帳側の複数行を確認
Y社・2026-041・1	1	1	1件ずつ存在
W社・2026-041・1	1	0	請求側に未登録
Q社・2026-041・1	1	1	金額入力と0円を区別

請求側の重複は請求シートH列で確認します。片方向の検索だけでは、もう一方にだけ存在するキーは見つかりません。

複合キーを連結するときの衝突

取引先名と伝票番号をそのまま連結すると、項目の境目が消えることがあります。

取引先	伝票番号	区切りなしで連結
AB	C	ABC
A	BC	ABC

区切り文字を入れた場合も、その文字が元データに含まれれば同じ結果になることがあります。可能なら列を連結せず、COUNTIFSやSUMIFSで条件を別々に指定します。

VLOOKUPなど単一の検索値が必要な方法を使うときは、連結後のキーが衝突しない形式か検証し、先頭ゼロや数値・文字列の違いもそろえます。キーを作っただけで一意性が保証されるわけではありません。

COUNTIFSとSUMIFSの役割を分ける

確認したいこと	関数	判定の考え方
条件に合う行が何件あるか	COUNTIFS	0件・1件・複数件を確認する
条件に合う金額を合計する	SUMIFS	1対多の合計が業務上許されるときに使う
大文字・小文字を分けた文字比較	EXACT	通常の条件検索で区別されるとは限らないため追加確認する

COUNTIFSのテキスト条件は大文字・小文字を区別せず、ワイルドカードのアスタリスクや疑問符として扱う場合があります。IDにこれらの文字が含まれる場合は条件の解釈を確認してください。COUNTIFのワイルドカードと大小文字

SUMIFSは指定条件に合う値を合計します。金額を合算する前に、同じキーが複数回出る理由を確認してください。Microsoft SUMIFSの仕様

照合キーを決めるときの確認表

項目	確認すること
取引先	略称や表記違いではなく、同じ識別ルールか
伝票番号	年度・拠点など、同じ番号が再利用される条件を含めたか
明細番号	1行を識別する番号か。伝票内で振り直されていないか
データ型	左右の列が両方とも文字列、または同じ数値・日付として読み込まれているか
先頭ゼロ	00123と123を別IDとして扱うか、同じと扱うか
行の粒度	片方だけ合計行・分割行になっていないか
重複	複数行が正しい明細か、二重計上の候補か

日付や数値を含める場合も、見た目の表示だけでなく、左右の値が同じデータ型・同じ意味で読み込まれているかを確認します。複数の業務キーで大量データを更新する場合は、Power Queryで2表を突合する手順で複数列のマージを確認できます。

使う関数や確認範囲を選ぶ

1条件の有無や重複を調べるならCOUNTIF、複数条件ならCOUNTIFS、条件付きの合計ならSUMIFSを使います。関数の使い分けはExcelの突合関数8種類の比較に整理しています。

納品書と請求書の明細では、伝票番号だけでなく品目・数量・単価・受領記録も確認します。書類ごとの確認範囲は納品書と請求書の照合手順をご覧ください。PDFや画像から表へ転記した場合、抽出結果は下書きとして扱い、人が元資料を確認して確定します。関連するExcel転記の自動化方法も参考になります。

式の結果は照合の候補を整理するものです。0件や複数件を業務上どう扱うか決め、根拠がそろってから差異を確定してください。突合.com