今回は会計システムにインポートするための仕訳データを作る時のお話です。
売上一覧表などから仕訳データを作るってことがありますよね。
こんなやつです。
そしてこの表を使って会計システムにインポートする用の仕訳データを作りたい、と。
ここで厄介なのが、インポート用仕訳データの様式が決まっているため転記するのが結構めんどくさいことなんですよね。
上の図の場合だと仕訳データの項目数（列数）がそれほど多くないのでそれほど大変とは思いませんが、これに消費税の情報や部門情報も必要となれば、その数20とかになっても不思議ではありません。
しかも一覧表に変更があった場合、その更新はとにかくめんどくさい。
というわけで今回のお話です。
パワークエリを使って、データの更新を簡単にするだけでなく、そのメンテナンスも簡単にできる方法を紹介したいと思います。
Contents
今回の作業ステップ
今回の手順を図で示すとこんな感じです。
項目対応表を使って、一覧表をインポート用仕訳データの様式に合わせて切り貼りし、次の作業に移ります。
切り貼りしたら、次は固定値を展開、そして必要に応じて、列の内容を他の列にコピーします。ここでは借方の内容を貸方にコピーする、といった具合です。
で、完成形こちらです。
そして今回の作業ステップです。
- 一覧表と仕訳データの列項目対応表の作成
- 項目対応表の加工（列の移動と行列入替）
- 項目対応表と一覧表の合体（クエリの追加）
- 固定値の展開（下方向へのフィル）
- 列タイトルを更新（一行目をタイトルに）
- 不要行の削除（フィルタ）
- とある列の内容を別の列にコピー（値の置き換え）←これ、鬼門
仕訳データへの変換
一覧表と仕訳データの列項目対応表の作成
まず、一覧表データとインポート用仕訳データの様式を確認し、対応関係を明らかにします。そして出来上がったデータがこちら。
このように、インポート用仕訳データと一覧表データの対応関係を表にします。
あわせて、３列目に固定値を配置します。
例えば売上明細の場合、借方と貸方の勘定科目は固定されているので、それぞれ固定されている値を入力します。
そしてこれをデータタブの[データの取得と変換][テーブルまたは範囲から]よりクエリエディターに取り込みます。あわせて売上一覧表も取り込んでおきましょう。
余談ですが、データを取り込むと勝手に各列のデータ型が変更されるのですが、このステップが邪魔になることもあるので、変更しないように設定変更しておくことをオススメします。
クエリエディターのファイルタブ[オプションと設定][クエリのオプション]から設定を帰れます。
詳細が↑こちらで、型の検出のところを３つ目のやつに変更しておきます。
項目対応表の加工（列の移動と行列入替）
さっき取り込んだ項目対応表を加工していきます。
まず取り込んだ状態のものがこちらです。
項目対応表を取り込んだら、2列目を選択してドラッグして１列目と入れ替えます。
次に行列入れ替えでこんな状態にします。
で、一行目をタイトルにします。
ここまでで項目対応表の編集は完了です。
項目対応表と一覧表の合体
次に、先に編集した項目対応表に売上一覧を追加します。
ホームタブ[クエリの追加]で売上一覧表を追加します。
選択するのは2つのテーブルで、クエリには売上一覧表を選びます。
対応表に売上一覧を追加した状態がこちらです。
固定値の展開
次に固定値の展開方法をみておきます。
ここは簡単、固定値の列を選択して、変換タブ[フィル]の下方向を選択します。
するとこうなります。
では次。
列タイトルを更新
ここで列項目を、売上一覧表の項目名から仕訳用の項目名に変えておきましょう。
と言っても簡単、ホームタブ[１行目をヘッダーとして使用]を押すだけです。
これでタイトル行が仕訳データの項目に更新されました。
不要行の削除
ここで不要な行である１行目を削除します。これも簡単、日付などの列のフィルマークを押して、nullのチェックを外すだけです。
とある列の内容を別の列にコピーする
最後のステップです。
今回は、借方補助の内容と借方金額を貸方補助と貸方金額にそれぞれ複写します。
これって、列を追加するのであれば簡単なんです。
数式のところにコピー元の列名を入れたらいいだけなので。
そうではなくて、すでにある列に別の列の内容を複写する、これが一筋縄ではいかないのですが、「値の置き換え」機能を応用してやってみましょう。
まず、コピー先である貸方補助列を選択し、ホームタブ[値の置換]から、検索に「null」置き換え後にコピー元の列名「[借方補助]」と入力します。
するとこのようになります。
それがこちら。
”[借方補助]”をeach[借方補助]に書き換えることで、コピー元の列を参照する形になりました。
詳しい説明を省きますが、eachを入れることで項目名と認識させることができるようになったというわけです。
借方金額も同じように、値の置換機能を使って貸方金額列にコピーします。
貸方金額列もコピーして、仕訳の形になりました。あとは日付の型を変更するなどして整形するだけです。
まとめ
いかがだったでしょうか。
今回、一覧表と仕訳項目の対応表を使うことで面倒な並び替え作業を簡単にしたわけですが、お勧めの方法としては、一覧表で仕訳に必要な項目をあらかじめセットするように設計しておき、パワークエリではそれを並び替えるだけ、とした方がツールとしての耐久性は高いように感じます。
というのも、パワークエリで計算させるとnullレコードがあった場合に加算できないなどの不測のエラーを避けるためです。
今回は１行１仕訳の場合の一覧表を作成しましたが、１行他仕訳みたいなことも可能ですので、それについてはまた次回解説してみたいと思います。
最後までお付き合い頂き、ありがとうございました！
