2つの表を目で見比べている人へ 入金もれと金額ちがいを関数で見つける

注文の控えと入金の記録。先月の名簿と今月の名簿。

2つの表を横に並べて1行ずつ目で追っていませんか。

件数が増えるほど見落としが出ます。何より時間がかかります。

ここでは2つの表を関数で突き合わせて違う所に色を付ける方法を書きます。

■ 突き合わせる「鍵」を決める

まず2つの表に共通する番号を探します。注文番号・伝票番号・社員番号などです。

これが「鍵」になります。名前や品名を鍵にすると表記ゆれで合わないことが多いので避けます。

例として、シート「控え」の A列に注文番号・B列に金額、シート「入金」の A列に注文番号・B列に入金額があるとします。

■ 相手の表から金額を引いてくる

「控え」の C2 に次の式を入れます。

=IFERROR(VLOOKUP(A2,入金!$A:$B,2,FALSE),"入金なし")

注文番号で「入金」の表を探して入金額を持ってきます。見つからなければ「入金なし」と出ます。

最後の FALSE は「ぴったり同じ番号だけ探す」という意味です。これを忘れると近い番号の金額を拾ってしまいます。

Microsoft 365 や Excel 2021 なら次の式でも同じです。

=XLOOKUP(A2,入金!$A:$A,入金!$B:$B,"入金なし")

■ 差を出して色を付ける

D2 に差を出します。

=IF(ISNUMBER(C2),C2-B2,"")

0 なら一致です。0 以外は金額ちがいです。

次に表を選んで「条件付き書式」→「新しいルール」→「数式を使用して…」を選び、次の式を入れて色を決めます。

=AND(ISNUMBER($D2),$D2<>0)

もう1つ「入金なし」の行に別の色を付けるルールも足します。

=$C2="入金なし"

これで見るべき行だけに色が付きます。

■ 反対側も確かめる

控えにあって入金に無い物は上の式で見つかります。

でも入金にあって控えに無い物は見つかりません。

「入金」のシートの C2 に =COUNTIF(控え!$A:$A,A2) と入れて 0 になる行を探します。0 の行は控えに無い入金です。

■ うまく合わないとき

番号が一方は数字・一方は文字になっていると同じ番号でも見つかりません。

左に寄っている番号は文字の可能性があります。片方にそろえてから式を入れます。

目で見比べる作業は慣れていても疲れます。

鍵になる番号さえあれば後は関数に任せられます。

関係する出品：Excelの集計や表の手直しを代わりにやります