経理業務における予算実績管理は、多くの企業で手間のかかる作業です。
特に、会計データから予算実績の差異分析シートへの転記は、手作業や複雑な関数設定に依存している場合があります。この課題を解決し、業務改善を進める方法として、ExcelのPower QueryとFILTER関数を組み合わせた自動化の仕組みを紹介します。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
予算実績管理における課題とExcel Power Queryの役割
多くの企業では、日々の会計データから月次で締めを行い、試算表などの形で出力しています。
会計データを予算実績の差異分析シートへ転記する作業は、手作業で行うと入力ミスが発生しやすく、時間がかかります。また、過去のExcelでは複雑な関数を組み合わせる必要があり、担当者の負担が大きい実情がありました。
しかし、新しいExcelの機能であるPower QueryとFILTER関数を使うことで、上記の課題を解決できます。
Power Queryとは、複数のデータソースからデータを取得し、整形・結合・集計を行うための機能です。
Power Queryによるデータ準備
会計データは、勘定科目や日付など、詳細な情報が含まれています。予算実績管理を行う場合、詳細な科目を「販管費」のような大項目にまとめたり、複数月のデータを一つのシートに結合したりする必要があるでしょう。
Power Queryは、これらのデータ準備作業を自動化します。
具体的な手順は下記のとおりです。
- マスタデータの読み込み
- 月次データの結合
- 列の分割とマージ
- データのグループ化と集計
まず、勘定科目を大項目にまとめるための「読み替えマスタ」をExcelブックから読み込みます。例えば「役員報酬」や「広告宣伝費」といった細かい科目を「販管費」という一つのカテゴリにまとめるための定義です。
次に、会計データが保存されているフォルダから、複数月の月次データを読み込みます。ExcelファイルだけでなくCSVデータも同様に読み込むことができ、Power Queryが自動的に複数の月次データを一つに結合します。
結合されたデータから、ファイル名などに含まれる日付情報を「列の分割」機能を用いて年月に分割します。その後、「クエリのマージ」機能を利用して、読み込んだ会計データと読み替えマスタを結合します。この結合により、会計データの勘定科目を、読み替えマスタで定義したカテゴリ（例: 販管費）に変換できます。
また、「グループ化」機能を活用すると、指定した項目（例: 年月日と読み替え後の勘定科目）ごとに金額を合計できます。例えば、4月分のすべての「販管費」の合計金額を算出することが可能です。
以上の手順で整形・集計されたデータは、Power Queryを閉じて読み込むことでExcelシート上に展開されます。
FILTER関数を活用した実績データの自動転記
Power Queryで準備したデータをもとに、予算実績管理シートの実績欄を自動で埋めるために、Excelの「FILTER関数」を活用します。
FILTER関数は、条件に合致するデータを抽出する機能です。従来のVLOOKUP関数では難しかった複数条件でのデータ取得を、簡潔に実現できます。
FILTER関数の基本設定
予算実績管理シートにFILTER関数を設定する手順は下記のとおりです。
- FILTER関数の構造：=FILTER(配列, 含む, [空の場合])
「配列」には、取得したい値がある列（今回の場合は金額の列）を指定します。この際、関数をコピーする際に列がずれないように絶対参照を設定します。
「含む」には、データを抽出するための条件を設定します。複数条件を設定する場合は、各条件をかっこで囲み、それらをアスタリスク（*）でつなげます。アスタリスクはAND条件を表します。
「空の場合」は任意の設定です。条件に該当するデータがない場合に表示する値を指定します。例えば「0」と設定すると、データがない場合にエラー表示ではなく「0」が表示されるようになります。
- 複数条件の設定：予算実績管理シートの実績欄に金額を転記するために、主に二つの条件を設定
勘定科目の一致：予算実績管理シートの勘定科目（例: 売上高）と、Power Queryで作成したデータの「読み替え」列の勘定科目が一致するという条件です。関数を下にコピーしても勘定科目列が固定されるように、B列にドルマークをつけ、行は相対参照とします。
年月日の日付一致：予算実績管理シートの「年月日」データ（表示上は年月）と、Power Queryで作成したデータの「年月日」列のデータが一致するという条件です。右にコピーしても年月日の行が固定されるように、行番号にドルマークをつけ、列は相対参照とします。
これらの条件が両方とも一致するデータが一つに絞り込まれ、該当する金額が実績欄に自動入力されます。
自動更新の仕組みと応用事例
一度FILTER関数を組み込めば、その後の運用は容易です。
新しい月の会計データをPower Queryの参照元フォルダに保存し、Excelの「データ」タブにある「すべて更新」をクリックするだけで、実績データが自動的に予算実績管理シートに反映されます。
この仕組みは予算実績管理だけでなく、様々なデータ管理に応用できます。
例えば、在庫管理において、製品番号や日付を軸とした出荷依頼データを自動転記し、最新の在庫数を把握する業務にも活用できます。手入力で何時間もかかっていた作業が、ボタン一つで数分で完了するようになり、大幅な時間削減につながるでしょう。
Power Query利用時の注意点
Power QueryとFILTER関数を組み合わせた自動化は非常に便利ですが、いくつかの注意点があります。
Power Queryエラーの主な原因
Power Queryでエラーが発生する主な原因として、下記の点に注意してください。
| エラー原因の項目 | 詳細な説明 |
| 元データの保存場所 | Power Queryの裏側で実行されるプログラミングコードには、元データの保存場所が特定されて記述されています。そのため、元データの保存場所を変更するとエラーが発生します。 |
| Excelブックの名前 | 元のExcelデータのブック名（ファイル名）が変更されると、設定されている名前でコードが書かれているため、エラーとなります。 |
| Excelシートの名前 | 元のExcelデータのシート名が変更されると、Power Queryで参照できなくなりエラーが発生します。 |
| データ列の名前（ヘッダー） | Power Queryは、データ内の列名をヘッダーとして認識し、コードにその列名が含まれます。列名が変更されると、参照できなくなりエラーとなります。 |
これらのエラーが発生した場合でも、Power Queryの設定画面で古い名前を新しい名前に置き換えることで復旧が可能です。
ExcelのPower QueryとFILTER関数を組み合わせて予実管理を自動化しよう
ExcelのPower QueryとFILTER関数を組み合わせることで、予算実績管理のような手間のかかるデータ集計・転記作業を自動化できます。
Power Queryで複数の会計データを整形・統合し、FILTER関数で必要な実績データを自動転記することで、業務を大幅に改善するでしょう。
ただし、Power Queryの運用では、元データの保存場所、ブック名、シート名、列名の変更はエラーの原因となるため、注意が必要でしょう。これらの点を把握し適切に対応することで、効率的なデータ管理を実現できます。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
コメント