まずはPower Queryがどのようなもので、何ができるのかを確認していきましょう。
特別な設定は不要
Power QueryはExcel 2016以上のバージョンで利用できる機能です。特別な設定や追加機能のインストールなどを行うことなく利用することができます。
さまざまな場所からデータを収集できる
あらゆる場所からデータを自動収集し、自動でデータを加工することができます。以下はPower Queryをつかってデータを自動取得できる対象のうちのごく一部です。Excel形式のファイルだけでなく、例えばPDFファイルなどもファイル内に表形式のデータがあればそれをデータとして抽出・加工することができます。
属人化を防ぐことができ、業務利用に向いている
自動化という観点からはマクロやVBAといった機能が有名ですが、これらは習得に時間がかかり、それが故に、活用には業務の属人化を招くリスクがあります。また、セキュリティ上の理由から企業での利用が敬遠されることも多くあります。
Power Queryは基本的にプログラミング知識不要で使うことができ、どのような処理がなされているかを目で見て確認できるようになっているため、マクロやVBAに比べて業務の属人化が起こりにくい仕組みになっています。また、基本的に外部のプログラムを呼び出して処理を行うといった機能はないため、セキュリティ面からも安心して利用することができます。
大量データの取り扱いが可能
マクロやVBAだけでは限界があった大量データの取り扱いにも非常に長けています。通常のExcelシートでは最大約104万行までしかデータを格納できないのに対し、Power Queryとデータモデルという仕組みを組み合わせて利用することにより、1つのExcelファイルのなかで最大約20億行までのデータを取り扱うことができます。
経理担当者がPower Queryを活用することで、以下のような業務を自動化することができます。これはPower Queryを使うことで自動化できる業務のほんの一例です。
費用や収益の繰延処理
年間契約などの会計処理においては、1年分の費用や収益を各月に均して計上するために“繰延計上および取崩”といった処理を行う必要があります。このために必要な仕訳は、クロス集計表をテーブルの形に変換する「ピボット解除」という機能を使って自動で作成することができます。
費用の見越計上と振り戻し
請求書未着の取引がある場合、月末に費用を見越計上しておいて、その振り戻し仕訳を翌月1日に作成するといったケースがあります。このために必要な仕訳は、表と表を縦にくっつける、「クエリの追加」という機能を使って自動作成することができます。
銀行勘定調整
会計システムから出力した銀行補助元帳と銀行の入出金明細を突合して銀行勘定調整表を作成する、という業務が毎月発生している場合、Power Queryを活用すれば業務の自動化につながります。これは、表と表を横にくっつける「クエリのマージ」という機能によって自動化することができます。
為替差損益の計算
海外取引がある企業においては、毎月の為替差損益の計上は面倒な業務の1つです。Power Queryを使えば、銀行のWebサイトから最新の為替レートを取得し、会計システム側のデータと組み合わせて為替差損益を計上する仕訳を自動で作成する、といったことも可能です。これは外部のWebサイトなどから自動でデータを収集する「データの取得」という機能を使って実現することができます。
労務費の配賦処理
IT企業や専門サービス業では、プロジェクト別の労務費計算に苦労されている企業も多いのではないでしょうか。Power Queryを使えば、会計システムから出力した間接労務費の金額と、勤怠管理システムから出力したプロジェクト別の稼働工数を使って間接労務費をプロジェクト別に直接労務費や仕掛勘定に振り替える仕訳を自動作成する仕組みを構築することもできます。これは「グループ化」という機能を使って実現することができます。
上記例の1つ目、「費用や収益の繰延処理」をPower Queryを使って自動化していくイメージをステップバイステップで解説していきます。皆様もお手元のExcelを使って実際にやってみてくださいね。
※本記事の画像キャプチャで利用しているExcelのバージョンはExcel for Microsoft 365 (バージョン2206)です。
ステップ1：年間契約管理表を作成（図示）
まずは以下の要領で契約管理用のExcelシートを作成していきます。ここでは、タテ（行）に契約先と科目を、ヨコ（列）に年月を配置したマトリックスを作成します。
行と列が交わる部分には月別の費用計上予定（前払費用の取り崩し予定）金額を登録していきます。
表の形が出来上がったら、データがある場所にカーソルを置いた状態で挿入→テーブルをクリックします。いわゆるテーブル形式に変換することで、Power Queryで扱いやすくなります。
以下のダイアログが表示されたら、そのままOKを押します。
ステップ2：Power Queryを使ってデータ変換
契約管理表が出来上がったら、いよいよPower Queryを使ってデータを加工していきます。データがある場所にカーソルを置いた状態でデータ→テーブルまたは範囲からをクリックします。
以下の画面が表示されます。これがPower Queryを使うためのPower Queryエディターといわれる画面です。
最初の3列を選択（Shiftを押しながらクリック）した状態で、変換→列のピボット解除→その他の列のピボット解除をクリックします。
これにより、集計表（マトリックス）形式になっていた表が、集計前の状態（レコード形式）に変換されます。この“ピボット解除”はPower Queryを使う上で非常に重要な機能の1つです。
次に、属性や値となっている部分（列名）をダブルクリックしてわかりやすい名前に変えていきます。ここではそれぞれ、日付と金額に変更しましょう。
日付列の列名の左にあるABCという文字をクリックすることで列のデータ型を変更することができます。ここでは日付型に変更します。
最後に、不要な列を削除したり、列の並び順を変更するなどして仕訳形式にデータを加工していきます。
列はドラッグアンドドロップで左右に並び替えることができます。
作業が完了したら、ホーム→閉じて読み込むをクリックしてPower Queryエディターを閉じます。
ステップ3：契約管理表から仕訳を自動作成
Excel上に新しいシートが自動作成され、仕訳（に近い）形式に変換されたデータが表示されました。
Power Queryで行ったデータ加工手順は自動で記録されるため、一度仕組みを作ってしまえば同じ処理を自動化することができます。
例として、先ほど作成した契約管理表に1行追加してみましょう。この状態でデータ→すべて更新をクリックすると先ほどと同じデータ加工の処理が自動で実施されます。
もう1つのシートを見てみると、新たに追加した情報を盛り込んだ状態で仕訳データが再作成されていることが確認できます。
本来は科目コードや相手科目（前払費用）を指定する仕組みなども実装する必要がありますが、ここまでの処理の中でPower Queryでどのようなことができるのか、ある程度はご理解いただけたのではないでしょうか。
※本記事の内容は掲載日時点での情報です。
※「Excel」「Power Query」は、米国Microsoft Corporationの米国、日本およびその他の国における商標
または登録商標です。
