前回の続きです。
前回は残高列の作り方について書きました。
今回は、それを使って元帳の作り方を中心に書いていこうと思います。
Contents
この記事でやりたいこと
改めて書くまでもないことかもしれませんが、
上段仕訳データから下段総勘定元帳（現金）を、パワークエリを使って作りたい！
これがこの記事でやりたいことです。
仕訳データから総勘定元帳までの流れ
操作方法に入る前に、まずは仕訳データから総勘定元帳に仕上げるまでの流れを確認しておきましょう。
まず、仕訳データを借方データと貸方データに分解し、元帳形式に変換します。
ここでのポイントは２つ、借方科目・貸方科目を検索科目と相手科目という関係に置き換えること、相手科目の金額をゼロとして分解することです。
このようにすれば分かりやすいでしょうか。借方サイドと貸方サイドに分けるといった具合です。
次に、この分解した借方データと貸方データを結合します。
そしてこれを並び替えて、仕訳データと同じ並びにしておきます。
あとはこれを元帳にする科目でフィルターして残高列を追加すれば完成です。
以上が仕訳データから元帳データを作る流れです。
では、パワークエリでの操作方法を見ていきましょう。
パワークエリの操作
事前準備
まず、データタブ「データの取得と変換」から対象となる仕訳データを取り込みます。
取込が完了すれば、画面右「クエリの設定」のプロパティよりクエリの名称を変更します。
ここでは分かりやすいように「仕訳データ」としました。
仕訳データの分解（１）借方仕訳クエリの作成
画面左のクエリフィールドの「仕訳データ」クエリを右クリックし、参照を選択します。
すると、「仕訳データ（２）」というクエリが作成されます。
ここでは数式バーに注目です。
「=仕訳データ」とありますよね。これが「仕訳データ」クエリを「参照」している、ということです。
ここでクエリの「参照」と「複製」の違いを見ておきましょう。
「参照」は元クエリ（クエリA）を引き継いでいるのに対し、「複製」は元クエリをコピーしているにすぎません。
違いは、元クエリを変更したときにあります。
元クエリであるクエリAを変更した場合、参照の場合はクエリAを「参照」しているので変更が反映されます。
一方で「複製」の場合クエリＡの変更はクエリＢに反映されません。
これが、大きな違いです。
今回、仕訳データの分解では仕訳データクエリを参照して借方データを作成し、それを複製して貸方クエリを作っています。
それぞれの関係を示すとこのようになります。
では、引き続き借方仕訳クエリの作成に進みましょう。
仕訳データの時と同じように、まずクエリ名を変更します。
そしてここから仕訳データから元帳用のデータへと変換します。
改めて変換内容の確認を。
では、進めていきましょう。
まず、借方科目と借方金額を並び替えます。
貸方科目を選択し、ドラッグ&ドロップで借方科目の横に移動します。
次に、借方データにおいて貸方金額は「０」にしないといけないので、まず貸方金額列を削除します。
貸方列を選択して右クリックし、削除を選択します。
そして列の追加タブよりカスタム列を選択し、新しい列名を「貸方金額」、カスタム列の式を「＝０」とします。
で、この追加された貸方金額列を借方金額の横に移動します。
最後に列名を変えます。借方科目を「検索科目」、貸方科目を「相手科目」とします。
これで、借方仕訳クエリの完成です。
仕訳データの分解（２）貸方仕訳クエリの作成
クエリフィールドより、借方仕訳を選択して右クリックし、「複製」を選択します。
さすれば「借方仕訳（２）」というクエリができますので、これを「貸方仕訳」に変更します。
あとは借方仕訳の時と同じように編集・並び替えすればいいのです。
が、これすでにステップとしては出来上がってるのでそれを使わない手はありません。
つまり数式バーの中身を貸方仕訳用に変更すればＯＫです。
例えば並び替えのステップはこんな感じです。
上段が変更前、下段が変更後です。
こんな感じで各ステップの数式内容を編集していきます。
借方仕訳クエリとの違いはこの表のとおりです。
ここまで直すと、このように貸方仕訳クエリの完成です。
おっとここで問題発生！
借方仕訳・貸方仕訳ともに修正したいことが出てきました。
修正事項はこの2点。
- 日付列の表示形式を日付だけの形式に変更したい
- 並び替えようにインデックス列を追加したい
これ、借方仕訳・貸方仕訳双方に共通する修正事項です。
では仕訳データクエリを開きましょう。
このように形式変換・列追加のステップを追加したとします。
その上で借方仕訳・貸方仕訳の両クエリを確認してください。
この通り、仕訳データの変更が、そのクエリを参照する借方仕訳・貸方仕訳の双方に反映されていることが分かったと思います。
これが参照を使うメリットです。（再掲）
ここまでで仕訳の分解ができたので、次に進みましょう。
借方・貸方仕訳クエリの結合と並び替え
まず、クエリフィールド内で右クリックし、「新しいクエリ」「結合」「クエリを新規クエリとして追加」を選択します。
追加に際しては2つのテーブルにチェックを入れ、「借方仕訳」「貸方仕訳」を選択します。
するとこのようにテーブルが結合されたクエリが作成されます。
これもわかりやすいようにクエリの名称を変更しておきましょう。ここでは「元帳用データ」としています。
そしてインデックス列をキーに昇順で並び替えます。
並び替えたらインデックス列は使いませんので削除します。
元帳用データの作成はここまでです。
勘定科目の絞り込みと残高列の追加
ここまでくればあと一息です。
まず、検索科目列を使って元帳を作りたい科目で絞り込みます。
例えば現金で絞り込むとこのようになるので、続けてインデックス列（１から）を追加します。
続けて、カスタム列の追加から残高列を追加します。
この辺りは前回の記事に詳しく書いてますので、そちらも参照してください。
これで、残高列が追加されました。先ほど追加したインデックス列はこれ以降使いませんので削除します。
元帳データの読込
あとはパワークエリを閉じるだけです。
ただ「閉じて読み込む」を選ぶとすべてのクエリを読み込みますので、「閉じて次に読み込む」を選択します。
そして「接続の作成のみ」を選択してＯＫします。
で、このままだと使えませんので、「クエリと接続」にあるクエリ一覧から「元帳データ」クエリを右クリックし、読込先を指定して読み込みます。
これでシートへの貼り付けまでできました。
最後に
いかがだったでしょうか。
仕訳データから総勘定元帳を作る方法について書いてみました。
ポイントは仕訳データを借方貸方に分解してくっつけることでしょうか。
総勘定元帳の作り方としては今回の前編・後編でおおむね大事なところは書けたかと思います。
あとは検索科目を指定する方法や期首残高がある場合など、捕捉したい項目もいくつかあるので引き続き書いていこうと思います。
