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 |
ロードスクリプトを作成
ロードスクリプトの参考例です。
できるだけ平易なスクリプトにしています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
//科目マスタを作成して読込 科目マスタ: 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）を出して分析するとよいと思います！
コメント