会社によっては、SAP以外の会計システムを利用しており、仕訳データを出力すると、以下のように借方と貸方とが並んだ状態で出力されることも多いと思います。
| 年度 | 伝票番号 | 借方科目 | 借方金額 | 貸方科目 | 貸方金額 |
|---|---|---|---|---|---|
| 2021 | 899546 | 1111 | 2,350 | 3111 | 2,000 |
| 2021 | 899546 | 3112 | 350 |
今回はこんなデータをQlikViewで簡単に分析・集計する方法をご紹介します！
なお、以前SAP仕訳データをQlikViewで分析・集計する方法をご紹介しましたが、大部分はほとんど同じです！
ただ、今回の仕訳データは借方・貸方とデータ列が分かれているので、そこを集計し、集約することが必要になります。
できること
- 借方・貸方と分かれたデータを集約することができる
- 仕訳ごとのPLインパクトを一瞬で集計できる
- 仕訳ごとの勘定科目の組合せ（仕訳の型）を一瞬で集計できる
簡単なデータでやってみる
仕訳データを用意
以下のようなデータを用意し、「仕訳データ.xlsx」として保存します。
保存先はQlikViewのqvwファイルの保存先と同一のフォルダとします。
| 年度 | 伝票番号 | 借方科目 | 借方金額 | 貸方科目 | 貸方金額 |
|---|---|---|---|---|---|
| 2020 | 393639 | 2111 | 1,000 | 1111 | 1,000 |
| 2020 | 427981 | 4111 | 3,000 | 2111 | 3,000 |
| 2020 | 892457 | 1111 | 5,000 | 3111 | 5,000 |
| 2021 | 393639 | 2111 | 1,000 | 1111 | 1,000 |
| 2021 | 427981 | 4111 | 3,000 | 2111 | 3,000 |
| 2021 | 892457 | 1111 | 5,000 | 3111 | 5,000 |
| 2021 | 899546 | 1111 | 2,350 | 3111 | 2,000 |
| 2021 | 899546 | 3112 | 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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | //科目マスタを作成して読込 科目マスタ: LOAD * FROM 科目マスタ.xlsx (ooxml, embedded labels); //科目マスタより、科目→科目名変換用のマッピングを生成 map_科目名: Mapping LOAD 科目, 科目名 Resident 科目マスタ; //科目マスタより、科目→PLインパクト係数変換用のマッピングを生成 map_PLインパクト係数: Mapping LOAD 科目, PLインパクト係数 Resident 科目マスタ; //マッピング生成完了後に不要となった科目マスタを削除 DROP Table 科目マスタ; //仕訳データを仕訳データ1として読込 //仕訳データ1は何も加工されていないデータ 仕訳データ1: LOAD * FROM 仕訳データ.xlsx (ooxml, embedded labels); //仕訳データ1から借方科目・借方金額に関連するデータを読込 仕訳データ2: LOAD *, 借方科目 as 科目, 借方金額 as 貸借金額 Resident 仕訳データ1 Where not IsNull(借方科目); //仕訳データ1から貸方科目・貸方金額に関連するデータを読込み、追加（join） join LOAD *, 貸方科目 as 科目, -貸方金額 as 貸借金額 Resident 仕訳データ1 Where not IsNull(貸方科目); //不要になったデータは即削除 DROP Table 仕訳データ1; //各マッピングと紐づけ、科目名・PLインパクトを計算 仕訳データ: LOAD *, ApplyMap('map_科目名',科目,'不明科目') as 科目名, ApplyMap('map_PLインパクト係数',科目,'不明科目') * 貸借金額 as PLインパクト Resident 仕訳データ2; //不要になったデータは即削除 DROP Table 仕訳データ2; //年度・伝票番号をキーとして仕訳データを集計 //年度と伝票番号を組み合わせれば一意の伝票を指定することができるのが通常なので、両者をキーとして集計します。 仕訳型サマリ: LOAD 年度, 伝票番号, concat(DISTINCT 科目名, ' | ', 科目) as 仕訳型, sum(PLインパクト) as PLインパクト（伝票ごと） Resident 仕訳データ Group by 年度, 伝票番号; |
リロード
リロードで読み込めば、完了です！
あとは、見たいデータをテーブルに表示したり、集計したい金額を集計したり、フィルターをかけたり、様々な分析が軽々できます！
たとえば、こんな感じにテーブルやチャート（pivot table）を出して分析するとよいと思います！
コメント