請求と支払を照合する技術 - DXの現場を支える完全外部結合の力 -

こんにちは。コグラフ株式会社データアナリティクス事業部の塩見です。
みなさんは請求と支払の照合は得意ですか。難しいと感じる方が多いのではないでしょうか。私も以前は苦手でしたが、SQLの完全外部結合について学ぶことで、この課題を克服することができました。

この記事では、完全外部結合を知っていると照合作業がとても簡単になることをご紹介します。照合は業務のあらゆるところに登場しますから、応用範囲が広い強力な武器となります。

照合作業の何が難しいのか

難しい点は以下の通りです：

1. 請求はあるが支払がない場合

2. 請求はないが支払がある場合

注文番号3：請求はあるが支払がない、注文番号4：請求はないが支払がある

これらの両方のパターンを考慮する必要があります。私の経験では、多くの人が請求か支払かのどちらかを起点にして考える傾向があります。たとえば、Excelを使用して請求データにVLOOKUP関数を適用し、支払データを横に並べて比較する方法がありますが、この手法には欠点があります。具体的には、請求データには存在しないが支払データには存在するケースを見逃してしまうのです。つまり支払データを起点に逆向きの照合も必要です。

請求データを起点に照合：請求はないが支払がある「注文番号4」を見逃した

このように、照合作業には両方向のアプローチが必要です。これが、照合作業を混乱させる要因だと考えています。

完全外部結合(FULL OUTER JOIN)とは

簡単に説明しますと、請求データと支払データ、どちらの欠損、余剰も見逃さずにもれなく照合してくれる演算です。例えばこのような請求データと支払データがあるとして

請求データと支払データ

これらを完全外部結合(FULL OUTER JOIN)するとこうなります。

請求データと支払データを完全外部結合した結果

このように、すべての注文番号を見逃さずに照合することができます。

SQLで照合してみる

SQLというプログラミング言語では完全外部結合を使って、以下のようにとても簡単に記述することができます。このコードは、注文番号を照合のキーとして使って、請求データと支払データを完全外部結合(FULL OUTER JOIN)してくださいということを書いています。

SELECT * FROM 請求データ FULL OUTER JOIN 支払データ USING(注文番号)

Accessで照合してみる

左外部結合(LEFT OUTER JOIN)と右外部結合(RIGHT OUTER JOIN)をUNIONで統合すると完全外部結合と同じ結果を得ることができます。この原理を利用すれば、完全外部結合をサポートしていないAccessでも同じことを実現できます。

SELECT * FROM 請求データ LEFT OUTER JOIN  支払データ USING(注文番号) --左外部結合
UNION
SELECT * FROM 請求データ RIGHT OUTER JOIN 支払データ USING(注文番号) --右外部結合

このSQLを参考にAccessのクエリを作成します。左外部結合と右外部結合をそれぞれ作成してください。

デザインビュー：左外部結合

データシートビュー：左外部結合

デザインビュー：右外部結合

データシートビュー：右外部結合

左外部結合と右外部結合が作成できたので、これらをUNIONで統合します。AccessではUNIONを使うクエリはデザインビューでは作成できませんので、SQLビューで直接SQLを書いてください。これで完成です。

SQLビュー：完全外部結合

データシートビュー：完全外部結合

Excelで照合してみる

ピボットテーブルを使って、完全外部結合と同じ照合を行うことができます。図のようにデータを縦方向に並べて入力してください。そして区分という列をつくって、請求か支払か区別できるようにしておいてください。

データを縦方向に並べて入力する

このデータからピボットテーブルを作成します。行に注文番号、列に区分、値に金額をそれぞれ配置してください。

フィールドの配置

するとこのようなピボットテーブルが完成です。完全外部結合と同じ結果を得ることができました。

完成したピボットテーブル

他の業務でも使ってください

請求と支払の照合の他にも、予算と実績の照合、在庫と棚卸の照合のように、照合は業務のあらゆるところに登場します。これらに共有するのは、別のシステム、組織、担当者から発生したデータであるということです。このような、発生源の異なるデータ同士の照合では、完全外部結合が活躍します。

この記事では、照合を簡単に考えられる枠組みをご紹介しました。あとはこの枠組みを活用していくことがDX（デジタルトランスフォーメーション）です。注文番号を顧客と共通化したり、支払データをもらえるように顧客に交渉することが、あなたの取り組むべき課題です。完全外部結合という強力な武器を活用して、業務をどんどん変革していきましょう。

データ分析に興味のある方募集中！

コグラフ株式会社データアナリティクス事業部ではPythonやSQLの研修を行った後、実務に着手します。
研修内容の充実はもちろん、経験者に相談できる環境が備わっています。
このようにコグラフの研修には、実務を想定し着実にスキルアップを目指す環境があります。
興味がある方は、下記リンクよりお問い合わせください。

X(Twitter)もやってます！

コグラフデータ事業部ではX(Twitter)でも情報を発信しています。
データ分析に興味がある、データアナリストになりたい人など、ぜひフォローお願いします！

📢Wantedly新掲載！

「データに興味がある」
「データに携わる仕事がしたい」
そこのあなた！
私たちと一緒にデータ分析しませんか？
IT業界未経験の方も大歓迎です☺️#エンジニア転職 #データ分析 #駆け出しエンジニアと繋がりたいhttps://t.co/S9o7VSjGRt

— アラリコ@コグラフ株式会社 | データ事業部 (@CographData) July 14, 2023