一致しない取引先は何社か（SUMPRODUCT で2列を比べる）
テストケース
1
2
3
リセット
提出
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ 準備完了 ハイライトされたセルに数式を入力し、「提出」を押してください。
テストケース
1
2
3
リセット
提出
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ 準備完了 ハイライトされたセルに数式を入力し、「提出」を押してください。
問題
問題文
月次締めの前に、入金額 C2:C6 が請求額 B2:B6 と一致しない取引先を数えます。件数を Mismatched セル E2 に入れてください。
1つの式で2列を行ごとに比べます。COUNTIFS は範囲同士を比べられません。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Vendor | Invoiced | Paid | Mismatched |
| 2 | Acme | 5000 | 5000 | |
| 3 | Bolt | 3200 | 3000 | |
| 4 | Cobalt | 1800 | 1800 | |
| 5 | Dana | 900 | 950 | |
| 6 | Eli | 400 | 0 |
ポイント解説
SUMPRODUCT(--(B2:B6<>C2:C6)) は2列を行ごとに比べ、違いを 1 にして合計します。これは COUNTIFS にできない仕事です。COUNTIFS の条件は定数でしかなく、「B列がC列と等しくない」とは書けません。入金 0 も不一致として数えます。誰も払っていない状態が最も重い不一致だからです。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 対象セルは E2
- 列全体ではなく行ごとに比べる
- <> は異なる行を TRUE にする
- 形：=SUMPRODUCT(--(? <> ?))
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
方法1：異なる行をそのまま数える
=SUMPRODUCT(--($B$2:$B$6 <> $C$2:$C$6))
Bolt（3200 と 3000）、Dana（900 と 950）、Eli（400 と 0）の3行が異なるので答えは 3 です。Acme と Cobalt は一致し 0 を足します。
方法2：総行数から一致した行を引く
=ROWS($B$2:$B$6) - SUMPRODUCT(--($B$2:$B$6 = $C$2:$C$6))
5行から一致した2行を引くと、残りは不一致の3行です。結果は同じで、一致件数も併記したいときに検算しやすくなります。
よくある質問
Excel で2列を行ごとに比べるには？
=SUMPRODUCT(--(A2:A10<>B2:B10)) で異なる行数が返ります。COUNTIFS の条件は定数なので、この比較はできません。
入金が空白や 0 でも不一致ですか？
はい。請求額と等しくないためです。月末締めでは、まさに拾いたいケースです。未入金が最も重い不一致です。