〔経理・会計監査〕仕訳データをQlikViewで仕訳型別に集計すれば分析・集計も簡単！①

SAPを会計システムとして利用されている会社が多いのではないでしょうか。

データ量の多い仕訳データを分析・集計するのにエクセルで集計するのはよほど仕訳の少ない会社でないと厳しいです。

また、会計監査においても仕訳検証、仕訳検討を不正リスク対応手続きとして実施する際の集計に苦慮されている方も多いと思います。

セルフBIツールQlikViewを使えば、そんな作業は一瞬で完了できます！

しかも試用版は無料で使えます！（個人利用に限り）

というわけで、SAPデータをQlikViewで集計する方法をご紹介します。

ただし、基本的なQlikViewの使い方については、Google検索すればいくらでもヒットするのでここでは割愛します。

できること
簡単なデータでやってみる

できること

仕訳ごとのPLインパクトを一瞬で集計できる
仕訳ごとの勘定科目の組合せ（仕訳の型）を一瞬で集計できる

簡単なデータでやってみる

仕訳データを用意

以下のようなデータを用意し、「仕訳データ.xlsx」として保存します。
保存先はQlikViewのqvwファイルの保存先と同一のフォルダとします。

年度	伝票番号	科目	貸借金額
2020	393639	1111	-1,000
2020	393639	2111	1,000
2020	427981	2111	-3,000
2020	427981	4111	3,000
2020	892457	3111	-5,000
2020	892457	1111	5,000
2021	393639	1111	-1,000
2021	393639	2111	1,000
2021	427981	2111	-3,000
2021	427981	4111	3,000
2021	892457	3111	-5,000
2021	892457	1111	5,000
2021	899546	3112	-2,350
2021	899546	1111	2,350

科目マスタを用意

以下のようなデータを用意し、「科目マスタ.xlsx」として保存します。
保存先はQlikViewのqvwファイルの保存先と同一のフォルダとします。

※PLインパクト係数は仕訳データ貸借金額×PLインパクト係数＝PLインパクトとなるように手入力したものです。PL科目には-1、BS科目には0として分類します。

科目	科目名	PLインパクト係数
1111	売掛金	0
2111	買掛金	0
3111	売上	-1
4111	仕入	-1
3112	売上2	-1

ロードスクリプトを作成

ロードスクリプトの参考例です。
できるだけ平易なスクリプトにしています。

//科目マスタを作成して読込
科目マスタ:
LOAD  *
FROM
科目マスタ.xlsx
(ooxml,  embedded  labels);

//科目マスタより、科目→科目名変換用のマッピングを生成
map_科目名:
Mapping  LOAD  科目,  科目名
Resident  科目マスタ;

//科目マスタより、科目→PLインパクト係数変換用のマッピングを生成
map_PLインパクト係数:
Mapping  LOAD  科目,  PLインパクト係数
Resident  科目マスタ;

//マッピング生成完了後に不要となった科目マスタを削除
DROP  Table  科目マスタ;

//仕訳データを読込
//各マッピングと紐づけ、科目名・PLインパクトを計算
仕訳データ:
LOAD  *,  
          ApplyMap('map_科目名',科目,'不明科目')  as  科目名,
          ApplyMap('map_PLインパクト係数',科目,'不明科目')  *  貸借金額  as  PLインパクト
FROM
仕訳データ.xlsx
(ooxml,  embedded  labels);

//年度・伝票番号をキーとして仕訳データを集計
//SAPでは年度と伝票番号を組み合わせれば一意の伝票を指定することができるので、両者をキーとして集計します。
仕訳型サマリ:
LOAD  年度,
	  伝票番号,
	  concat(DISTINCT  科目名,  '  |  ',  科目),
	  sum(PLインパクト)
Resident  仕訳データ
Group  by  年度,  伝票番号;

//科目マスタを作成して読込

科目マスタ:

LOAD *

FROM

科目マスタ.xlsx

(ooxml, embedded labels);

//科目マスタより、科目→科目名変換用のマッピングを生成

map_科目名:

Mapping LOAD 科目, 科目名

Resident 科目マスタ;

//科目マスタより、科目→PLインパクト係数変換用のマッピングを生成

map_PLインパクト係数:

Mapping LOAD 科目, PLインパクト係数

Resident 科目マスタ;

//マッピング生成完了後に不要となった科目マスタを削除

DROP Table 科目マスタ;

//仕訳データを読込

//各マッピングと紐づけ、科目名・PLインパクトを計算

仕訳データ:

LOAD *,

ApplyMap('map_科目名',科目,'不明科目') as 科目名,

ApplyMap('map_PLインパクト係数',科目,'不明科目') * 貸借金額 as PLインパクト

FROM

仕訳データ.xlsx

(ooxml, embedded labels);

//年度・伝票番号をキーとして仕訳データを集計

//SAPでは年度と伝票番号を組み合わせれば一意の伝票を指定することができるので、両者をキーとして集計します。

仕訳型サマリ:

LOAD 年度,

伝票番号,

concat(DISTINCT 科目名, ' | ', 科目),

sum(PLインパクト)

Resident 仕訳データ

Group by 年度, 伝票番号;

リロード

リロードで読み込めば、完了です！
あとは、見たいデータをテーブルに表示したり、集計したい金額を集計したり、フィルターをかけたり、様々な分析が軽々できます！

たとえば、こんな感じにテーブルやチャート（pivot table）を出して分析するとよいと思います！