ACLの活用：統計分析手法
執筆者 上野 哲司
マネージャー
■国際認定CAATs技術者（ICCP）
■公認会計士
■システム監査技術者
大手監査法人において、公認会計士として上場企業等に対する財務諸表・内部統制監査およびシステム監査業務に従事。2020年より三恵ビジネスコンサルティング株式会社に入社し、現在は、CAATs導入支援やCAATs技術者の育成に関する研修講師を担当。
●統計分析手法の特徴
統計分析手法では、各データが平均とどれくらい離れているか、あるいは、データ相互間の相関関係などを把握することができます。そのため、例えば、販売単価の「平均」から、各売上実績レコードの販売単価がどれだけ離れているかを把握して、「異常な販売単価」となっている取引を識別する場合などに役立ちます。
そこで、今回は統計分析手法の1つである「偏差値」を利用して、「異常な販売単価」となっている取引を識別する方法をご紹介します。
●その前に、偏差値ってなんだっけ ～偏差値のおさらい～
学生時代からお世話になっている「偏差値」も、「平均とどれくらい離れているか」を把握する方法の1つです。当時（思い出したくもない）、私が受験勉強をしていたころ、国語や数学、英語などの学力を測る基準に「偏差値」を利用していました。偏差値は、科目ごとに平均点が異なっていても、偏差値という同じメジャーで比較できるため、異なる科目において自分の学力がどの程度なのかを把握しやすかったですよね（私は見たくもなかったです）。
●偏差値を利用して分析シナリオを策定してみよう
このように、平均が異なるものを同じメジャーで比較できるのであれば、販売単価が異なる商品を同じメジャーで比較できるので、偏差値が異常に低いor高い商品単価の取引データというのは、「異常な販売単価」となっている取引だ、と言うことができそうですよね。
そこで、実際に販売取引データをもとに分析シナリオを策定してみました。
「売上実績データから商品別販売単価の偏差値の算出により、偏差値が異常な販売単価の売上実績データを抽出し、得意先、営業担当者、商品等を特定する。」
それでは、上記分析シナリオについて、ACLを活用して偏差値を算出する方法を見ていきたいと思います。
●ACLで実際に販売単価の偏差値を算定してみよう
ACLでは、下記の3Stepで販売単価の偏差値を算定します。
Step1「要約」コマンドにより、販売単価の[平均]と[標準偏差]を算出したテーブルを作成する。
Step2「結合」コマンドにより、Step1で算出した販売単価の[平均]と[標準偏差]を結合したテーブルを作成する。
Step3「演算フィールド」により、Step2で結合した[平均]と[標準偏差]から「偏差値」を算出する。
では、「売上実績データ」を入手したと仮定して、各売上実績データの商品別販売単価の偏差値を算定してみたいと思います。完成イメージは下記になります。
作成するACLスクリプトは、以下の3手順で作成していきます。
手順①入手したソースデータをインポートする。（6行目～8行目）
手順②以下の3Stepで偏差値を算出する。（10行目~27行目）
Step1「要約」コマンドにより、販売単価の[平均]と[標準偏差]を算出したテーブルを作成する。
Step2「結合」コマンドにより、Step1で算出した販売単価の[平均]と[標準偏差]を結合したテーブルを作成する。
Step3「演算フィールド」により、Step2で結合した[平均]と[標準偏差]から「偏差値」を算出する。
手順③販売単価の偏差値から異常取引を抽出する（29行目～35行目）
今回は、上記手順②の商品別の販売単価の偏差値を算出する部分を詳しく解説したいと思いますので、手順②の各Stepを詳しく見ていきたいと思います。
＜Step1「要約」コマンドにより、販売単価の[平均]と[標準偏差]を算出したテーブルを作成＞
Step1では、販売単価の偏差値を算出するための基礎となる販売単価の[平均]と[標準偏差]を作成します。作成方法は、ACL上の分析メニューの「要約」コマンドを利用すると1発で作成することができます。
【注意点！】
「要約」コマンドを使う際は、ダイアログ上で「平均、最小、最大」チェックボックスと、「標準偏差、フィールドの％」チェックボックスにチェックを入れ忘れないようにご注意くださいね。
なお、「要約」コマンドで販売単価の[平均]と[標準偏差]が作成できますが、それぞれ下記のフィールドとして作成されます。
＜Step2 「結合」コマンドにより、Step1で算出した販売単価の[平均]と[標準偏差]を結合したテーブルを作成＞
Step2では、売上実績データに、Step1で作成した販売単価の[平均]と[標準偏差]を結合していきます。作成方法は、ACL上のデータメニューの「結合」コマンドを利用します。
「結合」コマンドにより、Step1で算出した販売単価の[平均]と[標準偏差]を結合する場合は、
副テーブルには、Step1で作成した「KT02_1_標準偏差」テーブルを指定する必要があります。
【注意点！】
副フィールドは、上述のように「名前」と「見出し」が異なりますが、ACLスクリプトでは「名前」（フィールド名）を使用することに注意が必要です。
※もし、ACLスクリプト上で直接「見出し」（表示名）を記述した場合は、エラーが表示されます。
＜Step3 「演算フィールド」により、Step2で結合した[平均]と[標準偏差]から「偏差値」を算出＞
Step3では、Step2で結合した販売単価の[平均]と[標準偏差]を使って、ACLの「（条件付き）演算フィールド」で偏差値フィールドを追加していきます。
式：偏差値＝（値－平均）÷標準偏差×10+50 ※値・・・各売上実績レコードの販売単価
【Hint】
本件では販売単価が0円のレコードは「条件付き演算フィールド」で偏差値を50としていますが、販売単価が0円のレコードがある場合は、Step1における[標準偏差]の算定に影響を及ぼす可能性があるため、事前にソーステーブルから販売単価が0円のレコードを除外しておくことをおススメします。
※もちろん、販売単価が0円のレコードの妥当性検証は別途実施することをおススメします。
●まとめ
以上で、販売単価の偏差値の算定が完了しました。
おさらいとなりますが、作成するACLスクリプトは、以下の3手順で作成していきます。
＜手順＞
手順①入手したソースデータをインポートする。（6行目～8行目）
手順②以下の3Stepで偏差値を算出する。（10行目~27行目）
Step1：「要約」コマンドにより、販売単価の[平均]と[標準偏差]を算出したテーブルを作成する。
Step2：「結合」コマンドにより、Step1で算出した販売単価の[平均]と[標準偏差]を結合したテーブルを作成する。
Step3：「演算フィールド」により、Step2で結合した[平均]と[標準偏差]から「偏差値」を算出する。
手順③販売単価の偏差値から異常取引を抽出する（29行目～35行目）
是非とも統計分析手法の1つである偏差値を利用して、対象データが平均とどれくらい離れているかを把握し、「異常な●●」となっている取引を識別する場合などに役立ててくださいね。