前回はパワークエリでマトリクス表をテーブル形式に変換する方法について書きました。
今回解説する事例は、給与計算システムから出した給与データから仕訳データに変換する方法です。
実際のインプットデータとアウトプットデータがこちら。
このような社員別の支給額・控除額データから仕訳データに変換していきます。
- パワークエリで給与データをテーブル形式に変換し、支給・控除項目と科目を紐づける。
- ピボットテーブルで科目ごとに集約・集計して仕訳データを完成させる。
では早速解説していきましょう。
Contents
パワークエリでの操作方法
事前準備
まずは給与データの支給・控除項目に対応する科目を設定し、科目対応表のテーブルを作成します。
今回は分かりやすいように科目名を入れてますが、本来ここには科目コードを入れるべきところですね。
で、給与データとそれに対応する科目対応表ができました。
この両者をパワークエリで取り込みます。
そして給与データの不要列（支給総額・）はここで削除しておきましょう。
今回の場合だと総支給額と控除額ですね。
で、次のステップに移ります。
給与データのピボット解除
今回ピボット解除しないといけないのは給与データの支給項目・控除項目の全てです。
操作方法としてはこんな感じ。
解除したくない列を選択、「その他の列のピボット解除」これで完了です。
右のような表になったはずです。
では次「科目対応表とのマージ」について見ていきましょう。
科目対応表とのマージ（VLOOKUP）
ここでは給与の支給・控除項目に勘定科目情報を追加して仕訳データっぽいテーブルデータを作り上げます。
まず、テーブルリボンより「クエリのマージ」を選択し、科目対応表テーブルの項目名と一致する列を選択します。
今回の場合だと、給与データの３列目と科目対応表テーブルの１列目が対応していることが分かります。
紐づけができれば、２つのテーブルをマージして展開します。
この列タイトルのテーブル２の右上ボタンを押します。
するとこうなります。
このように、仕訳で必要な科目情報が追加されました。
金額データ列の追加
あとは、金額を借方・貸方に合わせて転記することです。
ここでは「列の追加」から「条件列」を使って借方金額・貸方金額専用の列を作ります。
するとこんな感じになります。
さっきよりも仕訳っぽくなったと思います。
ここまでできれば、あとは閉じてエクセルに読込めば完了です。
パワークエリでやったこと（おさらい）
今回パワークエリでやったことはこの通り。
今回はこんな順番でやってみました。
では次にこのデータをピボットテーブルを使って加工していきましょう。
ピボットテーブルでの操作方法
ピボットテーブル挿入
さて、ここまでで個人別の仕訳用の元データができました。
ここから給与計上仕訳として必要な科目・金額に集約します。
操作としては簡単で、挿入タブからピボットテーブルを押します。
そして行の選択項目に「支給日、借方科目名、貸方科目名」を選択し、Σ値欄（集計フィールド）には「借方金額、貸方金額」を選択し、集計方法は「合計」を選択します。
するとこんな画面になると思います。
ここから使える仕訳データにするためにレイアウトをカスタマイズします。
ピボットテーブルのレイアウトのカスタマイズ
レイアウトを変えるための操作はこの通り。
これで小計を無くし、総計は列のみ、テーブル形式のレイアウトに変えることができます。
完成形がこちらです。
これで完成です。
仕訳なので当然借方合計・貸方合計は一致しています。
ま、総計欄は無くてもいいけど検算用にあった方が便利かもしれませんね。
まとめ
いかがだったでしょうか。
今回はピボット解除を使った実例について解説してみました。
この完成した仕訳は、このままでは使えないかもしれません。
しかし、システムに応じてレイアウトを変えると、このファイル自体が給与計算システムと会計システムを連携させるためのツールとなるわけです。
パワークエリやピボットテーブルはこういった使い方もできるので、ぜひ一度やってみてくださいね！
最後までお読み頂きありがとうございました！
パワークエリやパワーピボットの使い方をさらに知りたい方にはこちらの本がおすすめです！
