トピックス

2021 November

この記事は2021年11月発行の「JBA JOURNAL」に掲載されたものです。内容及びプロフィール等は掲載当時の情報となります。

Excelパワーピボット・パワークエリの
業務への活用
今すぐできる作業効率化

本島大樹Taiki Motojima

ジャパン・ビジネス・アシュアランス株式会社
公認会計士、アクチュアリー会研究会員

有限責任監査法人トーマツにて、銀行・証券会社の監査業務を経験。２０１７年にジャパン・ビジネス・アシュアランス株式会社へ入社し、ＧＡＡＰコンバージョン支援や決算作業の効率化支援等の会計コンサルティング業務に従事。

今や業種・職種問わず当たり前に利用されているMicrosoft Office Excel、その新たな機能であるパワーピボットあるいはパワークエリをご存じだろうか。新しいとはいっても実はこれら機能はＥｘｃｅｌ２０１６から標準搭載されている機能であり、それ以前のＥｘｃｅｌ２０１０、Ｅｘｃｅｌ２０１３でもアドイン機能として存在していたものであるが、本稿執筆時点で少なくとも筆者の周りでは認知度が高いとは言えない。しかし、これら機能は実務での幅広い応用可能性を有しており、またその敷居の低さから、今後目にする機会が増えていくのではないかと思われる。
今回はＥｘｃｅｌの機能であるパワーピボット・パワークエリの内容をその業務における適用場面も交えて紹介する。

Ｅｘｃｅｌパワーピボット・パワークエリとは

Ｅｘｃｅｌの機能でパワーピボットと聞くと、従来のピボットテーブルに関連する何かを連想するかもしれないパワーピボットとはざっくりと言えば、図表のように「複数のインプットデータ（テーブル）を基にしてピボットテーブル（レポート）を作成する仕組み」といえる。従来のピボットテーブルは単一のテーブルを基礎として多面的な分析を可能とする機能であるが、基となる単一のテーブルを用意するために場合によってはその都度複雑で労力のいる手順を踏む必要があった。
パワーピボットでは、複数のテーブルがあるならば、それらを取り込むロジックを一度構築してしまえば、以降の労力は不要となる。そしてそれら複数のテーブルを、手作業を介さずＥｘｃｅｌに取り込む機能がパワークエリである。

図表:従来の手法とパワーピボット・パワークエリとの比較

Point1 | 状況設定

次のような状況をイメージしてもらいたい。日々の業務の中で、複数のテーブルについて加工を行い、最終的に単一のテーブルを作りあげ、その単一テーブルを用いてピボットテーブルやピボットグラフなどのレポートを作成する作業があったとする。例えば、「日付」、「売上店舗番号」、「商品番号」、「売上金額」の４項目が存在するＣＳＶ形式の売上取引データを販売管理システムから出力し、売上店舗番号と所在都道府県の情報が記載された売上店舗マスタ（Ｅｘｃｅｌファイル）を活用して、都道府県別、四半期別の売上レポートを作りたいとする。また、この売上取引データは、出力元システムの制約上、各取引別の売上レコードの他に月毎の売上小計のレコードも記録されて出力されてしまうものとする。

Point2 | 従来のＥｘｃｅｌ機能の弱点

従来のＥｘｃｅｌの機能で目的のレポートを作成する場合、①出力したＣＳＶファイルをコピーして売上レポート用のＥｘｃｅｌファイルに貼り付け、②フィルタ機能などを用いて売上小計レコードの削除、③「日付」項目をもとにＩＦ関数やＭＯＮＴＨ関数などのＥｘｃｅｌ関数を活用して売上年度、売上四半期の算出、④「売上店舗番号」を検索値としてＶＬＯＯＫＵＰ関数で売上店舗マスタから売上店舗の所在都道府県を取得し、各レコードに紐付け、⑤以上により作成された単一のテーブルをピボット集計、というような手順が必要となる。
これは単純な例だが、それでも①のコピー範囲の漏れ、②手順自体の失念、③や④で関数のオートフィルの失念、⑤のピボット集計範囲の漏れといった手作業に起因するエラーの可能性を孕んでおり、レポートの当初作成者とは別の第三者がその作業を引き継ぐ場合などは特に注意が必要となる。
またデータの総量次第ではＶＬＯＯＫＵＰ関数などの計算量が膨大となり実用不可能なファイルとなる場合もある。

Point3 | パワークエリの活用

この例におけるパワーピボット・パワークエリの利用を考える。パワークエリには、Ｅｘｃｅｌのマクロの記録機能のように、その処理手順が記録され保存される仕組みがある。
Microsoft Office Accessの知識がある読者であれば、Ａｃｃｅｓｓのクエリ機能を想定していただければよい。つまり最初に一度、インプットデータの取り込みロジックを構築しアウトプットとなるピボットテーブルの形を整えてしまえば、あとは売上レポートを作成したいタイミングでインプットデータを最新のものに差し替えるとともに、ピボットテーブルの更新ボタンを押すだけで毎度のレポート作成は完了してしまう。
ではそのロジック作成はＥｘｃｅｌＶＢＡのようにプログラミングの知識が必要かというとそうでもない。パワークエリでの各処理手順（ステップ）ではＭ言語というあまり馴染みのないプログラミング言語が使用されているが、それを使わなくとも簡単なマウス操作でインプットデータソースの指定、不要な行の削除、必要な列の追加といった各手順を構築でき、インプットデータ（テーブル）同士の紐づけも行える。そして、その一つひとつの手順はパワークエリの設定画面「クエリエディター」上で「適用したステップ」として可視化され、各手順（ステップ）ごとの実行結果を加工の対象となるテーブル上で確認できる。そのため、それぞれの手順に適切な名前（例えば、「小計レコードの削除」等）をつけておけば、第三者からも何を行っているか理解しやすいものとなる。
「適用したステップ」は簡単に取り消すことや手順と手順の間に別の手順を差し込むこともできるため、複雑な手順が必要な場合でも全体像を見失わずにロジック構築を行いやすい。
また、パワークエリによってＥｘｃｅｌファイルにインプットデータを取り込む場合、従来のようにワークシート上で目に見えるテーブルとして取り込むのではなく、データモデルというＥｘｃｅｌ内部の格納領域に取り込むことも可能だ。この手法によればデータ量が多すぎてＥｘｃｅｌでまともに扱えなかったデータであっても分析が可能という大きなメリットもある。

最後に

本稿では紙面の関係でパワーピボット・パワークエリの具体的な取扱い方法を記載することは出来なかったが、難解なプログラミング言語を習得する必要はなく、使い慣れたＥｘｃｅｌ上の画面操作で多くを実現できる機能ということもあって、Ｅｘｃｅｌにある程度慣れていればＥｘｃｅｌＶＢＡやＡｃｃｅｓｓよりも習得しやすいツール（機能）であると考えている。それでいてその応用場面は多様であり、また、Ｅｘｃｅｌ２０１６以降のバージョンが手元にあればすぐにでも試すことができる。本稿をきっかけに興味を抱かれた読者の方々は是非パワーピボット・パワークエリ機能の習得と実務への応用に挑戦していただきたい。