仕訳データの集計を自動化！Power Query×Power Pivotで作業効率を改善する方法

先輩、この集計…また最初からやり直しですか？データちょっと増えただけなのに…

そのままの形で集計してると、毎回作り直しになるんだよね。でも、ちゃんと仕組みを作ればボタン1つで更新できるよ。

日々の業務で、同じような集計作業を何度も繰り返していたり、元データが更新されるたびに表を作り直していたりしませんか？「もっとラクにできそう」と思いつつ、そのままになっているケースも意外と多いものです。

• 同じような集計作業を何度も繰り返している
• 元データが更新されるたびに、手作業で表を作り直している

Excelはとても便利ですが、その一方で、気づかないうちに手作業が増えてしまいがちです。こうした手間は、データの形を少し整えて、集計の仕組みをあらかじめ作っておくだけで、ぐっと減らすことができます。

そこで今回の記事では、その一例として仕訳データをもとに貸借対照表を作成する方法を題材にしながら、

• データの整え方（Power Query）
• 集計の仕組みの作り方（Power Pivot）
• 見える化の方法（ピボットテーブル）

この流れを通して「繰り返し作業を減らすコツ」を具体的に見ていきます。

扱う題材は簿記データですが、ここで紹介する考え方は、売上集計や業務データの分析など、さまざまな場面で応用できます。

なんとなく手作業で集計を続けている方にとって、作業を“仕組み化”するヒントになればうれしいです。

スポンサーリンク

集計前のデータ整理

今回、用意した簿記の仕訳データは、次のような項目で構成されています。

• 日付
• 借方科目
• 借方金額
• 貸方科目
• 貸方金額
• 摘要

まず最初のステップとして、この仕訳データを集計しやすい形に整えていきます。
なお、「摘要」欄は今回の集計では使用しません。

集計しやすいデータの形とは？

Power Pivotやピボットテーブルで分析しやすいのは、次のように項目が縦に並んだ形式です。

• 日付
• 摘要
• 借貸区分（借方 or 貸方）
• 科目
• 金額

ポイントは、「借方科目・貸方科目」「借方金額・貸方金額」などの同じ属性の項目が横に分かれている状態」をやめて、「借貸区分」「科目」「金額」と1列ずつにまとめることです。この形にしておくと、あとでピボットテーブルでサクッと集計できるようになります。

Power Queryでデータを変換する

この形式への変換は、Power Queryを使って行います。
やり方はいくつかありますが、よくある方法としてはこのあたりです。

• 「列のピボット解除」を使う方法
• 借方データ、貸方データをそれぞれ作ってからマージする方法

列のピボット解除の場合

ここでは、実際に「列のピボット解除」を使った変換方法を見ていきます。
今回のデータは、

• 「借方科目・借方金額」
• 「貸方科目・貸方金額」

というように、「科目」と「金額」がセットで2組あります。

この状態だと、単純にピボット解除を実行しても、2列（科目・金額）を同時にきれいに変換することができません。そのため、少し工夫して処理を行います。

次の流れで変換を行います。

• 「借方金額」「貸方金額」の2列をピボット解除
• 「科目」列を条件に応じてまとめる
• 列名の変更や不要列の削除、データ型の調整

手順2では、「借方科目」と「貸方科目」を1つの列にまとめます。具体的には、「変換」→「値の置換」で作成された式をベースに、次のように調整します。

= Table.ReplaceValue(
    選択した列のみをピボット解除,
    each [借方科目],
    each if [属性] = "貸方金額" then [貸方科目]
         else [借方科目]
    ,Replacer.ReplaceValue,
    {"借方科目"}
    )

この式では、

• 属性が「貸方金額」のとき → 貸方科目を使う
• それ以外 → 借方科目を使う

という形で、「借方科目」列にまとめています。

もしこの式が少し難しく感じる場合は、別の方法でもOKです。
例えば、

• 「借方科目＋借方金額」
• 「貸方科目＋貸方金額」

をそれぞれ列のマージ（結合）してからピボット解除し、あとで列の分割をする方法でも同じ結果を作ることができます。

どの方法でもOKですが、あとから修正しやすい（メンテナンスしやすい）方法を選ぶのがポイントです。

変換後のテーブル

変換後のテーブルでは、

• 借貸区分
• 科目
• 金額

がそれぞれ1列に整理された状態になります。

このあとのステップでは、このテーブルを使って貸借対照表を作っていきます。

元データは、「借方科目・借方金額」「貸方科目・貸方金額」が横並びになっています。ただ、このままの形ではピボットテーブルやPower Pivotでの集計にはちょっと不向きです。

このデータ構造は「仕訳帳」としてはとても自然です。ただし、分析用途になると一気に扱いづらくなります。理由は、借方と貸方が別々の列に分かれているため、集計時に別々に扱う必要があるからです。

実際にこのままピボットテーブルで集計してみると、

• 「借方科目 × 貸方科目」の組み合わせごとに行が分かれる
• そのため、同じ科目でも相手科目が違うと別の行になる

といった状態になります。

その結果、例えば貸方科目に「現金」や「普通預金」があっても、バラバラに表示されてしまい、きれいに集計できません。

一方で、データを縦持ち（借貸区分・科目・金額）に変換すると、状況は一変します。

この状態でピボットテーブルを作成すると、

• 「科目」ごとにスパッと集計できる
• とてもシンプルで見やすい表になる

といった感じで、かなりスッキリした結果になります。

実はもうひとつ重要なポイントがあります。加工前のデータでは「科目」にあたる列が

• 借方科目
• 貸方科目

の2つに分かれています。この状態だと、あとで行う勘定科目マスタとのリレーションシップ設定がやりにくいという問題も出てきます。

ということで、最初にデータを縦持ちに整えておくのが、後工程をラクにするコツです。

勘定科目マスタとリレーションを組む

次のステップでは、勘定科目マスタと仕訳データをつなげていきます。

勘定科目マスタとは、例えばこんな情報を持っている表です。

• 勘定科目
• 区分（資産・負債・純資産・収益・費用 など）

このマスタをPower Pivotに読み込み（データモデルに追加）、先ほど作成した縦持ちの仕訳データと関連付け（リレーション）します。

ダイアグラム ビューを表示する

2つのテーブル（仕訳データ・勘定科目マスタ）をPower Pivotに取り込んだら、ダイアグラム ビューを開きます。

リレーションシップを作成する

ダイアグラム ビュー上で、

• 勘定科目マスタの「勘定科目」
• 仕訳データの「科目」

この2つをドラッグしてつなぐことで、リレーションシップを作成します。

「この科目はこのマスタの情報と対応していますよ」とPower Pivotに教えてあげるイメージです。

リレーション作成後の状態

リレーションシップを作成すると、2つのテーブルが線で結ばれた状態になります。

この状態になることで、

• 勘定科目の区分（資産・負債など）を使った集計ができる
• ピボットテーブルでマスタの情報も一緒に扱える

ようになります。

ここまでできれば、データモデルの土台は完成です。次は、このデータを使って貸借対照表の数値を計算をします。

Power Pivotでメジャーを作成して集計する

ここからは、いよいよ集計の中身となるメジャーを作成していきます。

今回作成するメジャーは、最終的に貸借対照表につながる重要な計算になります。ただ、内容自体はそこまで難しいものではなく、基本的な集計の組み合わせなので安心してください。

残高（差額）の計算

まずは基本となる「残高」を計算します。考え方はシンプルで、借方合計 − 貸方合計 = 残高 です。

借方合計 :=
CALCULATE(
    SUM([金額]),
    '仕訳データ'[借貸区分] = "借方金額"
)

貸方合計 :=
CALCULATE(
    SUM([金額]),
    '仕訳データ'[借貸区分] = "貸方金額"
)

残高:=[借方合計]-[貸方合計]

この「残高」をベースに、各区分の金額を作っていきます。

資産の部・負債の部の計算

次に、勘定科目マスタの「区分」を使って、資産と負債を分けて集計します。

資産の部 :=
CALCULATE(
    [残高],
    '勘定科目マスタ'[区分] IN {"資産","資産（評価）"}
)

ここで使っている IN は、「どれかに一致すればOK（OR条件）」という意味です。

負債の部はこちらです。

負債の部 :=
CALCULATE(
    -[残高],
    '勘定科目マスタ'[区分] = "負債"
)

ポイントは -[残高] の部分です。マイナスをかけて、符号を反転しています。
[残高] × -1 と同じ意味ですね。

当期純利益の計算

次に、収益と費用を集計して「当期純利益」を求めます。

収益合計 :=
CALCULATE(
    -[残高],
    '勘定科目マスタ'[区分] = "収益"
)

費用合計 :=
CALCULATE(
    [残高],
    '勘定科目マスタ'[区分] IN {"費用","費用（営業外）"}
)

当期純利益:=[収益合計] - [費用合計]

ここも考え方はシンプルで、収益 − 費用 = 利益をそのままメジャーで表現しています。

純資産の部の計算

最後に、純資産の部を計算します。

純資産の部 :=
CALCULATE(
    -[残高],
    '勘定科目マスタ'[区分] = "純資産"
)
+ [当期純利益]

純資産は、もともとの純資産（資本金など）＋ 当期純利益という構成になるので、このような形になります。

ここまでのメジャーでやっていることは、実はとてもシンプルです。

• SUM で金額を合計
• CALCULATE で条件を指定
• 必要に応じて符号を調整

これだけで、貸借対照表に必要な数値をしっかり作ることができます。

費用合計 :=
CALCULATE(
    [残高],
    '勘定科目マスタ'[区分] = "費用" ||
    '勘定科目マスタ'[区分] = "費用（営業外）"
)

費用合計 :=
CALCULATE(
    [残高],
    SEARCH("費用", '勘定科目マスタ'[区分], 1, 0) >= 1
)

費用合計 :=
SUMX(
    FILTER(
        '勘定科目マスタ',
        SEARCH("費用", '勘定科目マスタ'[区分], 1, 0) >= 1
    ),
    [残高]
)

借方資産合計 :=
CALCULATE(
    SUM([金額]),
    '仕訳データ'[借貸区分] = "借方金額",
    '勘定科目マスタ'[区分] IN {"資産","資産（評価）"}
)

メジャーを使ってピボットテーブルを作成する

ここまでで、必要なメジャーが一通りそろいました。

いよいよ、このメジャーを使ってピボットテーブルを作成していきます。

フィールドの配置

ピボットテーブルでは、次のようにフィールドを配置します。

• 行
  「大分類」→「小分類」→「区分」→「科目」
• 値
  「資産の部」「負債の部」「純資産の部」

なお、フィールド一覧の中で「Fx」と表示されているものがメジャーです。これはPower Pivotで作成した計算項目で、自動的に「Fx」が付くようになっています。

作成結果

この設定でピボットテーブルを作成すると、貸借対照表っぽい形の集計表が完成します。ここでは「ちゃんとそれっぽく出てる！」という感じになればOKです。

このやり方の一番のメリットはここです。

「元データが更新されても、ボタン1つで最新化できること」

仕訳データは日々どんどん増えていきますよね。通常なら集計をやり直す手間がありますが、「更新」ボタンをクリックするだけでピボットテーブルも自動で再計算されるという仕組みになっています。

日々変わるデータを扱うには、かなり相性のいい方法です。

まとめ

今回は、仕訳データを例にしながら、Power Query・Power Pivot・ピボットテーブルを組み合わせて集計する方法を紹介しました。

• Power Queryでデータを整え
• Power Pivotでマスタとリレーションを作成し
• メジャーで計算ロジックを作って
• ピボットテーブルで集計する

この流れを組み合わせることで、「更新に強い集計の仕組み」を作ることができます。見た目は少し手順が多く感じるかもしれませんが、やっていること自体はシンプルで「データを整えて、条件付きで集計する」という基本操作の積み重ねです。

今回の題材は貸借対照表でしたが、この考え方は売上データの集計や業務データの分析など、さまざまな場面に応用できます。一度仕組みを作っておけば、あとはデータを更新するだけで最新の集計結果が反映されるようになるため、日々の作業を大きく効率化できます。

毎回、同じような集計を繰り返していると感じている方は、こうした形で作業を「仕組み化」してみるのがおすすめです。少しの工夫で、日々の手間はぐっと減らせます。

今回の内容が、業務をラクにするヒントになればうれしいです。

最後まで読んでいただき、ありがとうございました！

これ作っておけば、あとは更新するだけってことですよね？めっちゃ楽じゃないですか！