複数のシートに分かれたデータを集計しようとして、どの方法を使えばよいのか分からず、手作業で一つずつ足し合わせてしまう、という経験はないでしょうか。シートの構成や更新頻度に合わない方法を選んでしまうと、後で集計のやり直しが必要になることがあります。
この記事では、複数シートを集計する前に確認しておきたいデータ構造から、関数による集計方法、3D集計を使えるケースと使えないケース、Power Queryでの複数シートの統合、関数とPower Queryを使い分ける基準、ChatGPTへ集計方法を相談するときの伝え方、そして集計結果を元シートと照合する方法までを順番に解説します。読み終える頃には、自分のデータ構成に合った集計方法を選べる状態になります。
複数シートを集計する前にデータ構造を確認する
ChatGPTに相談する前に、各シートのデータ構造を確認しておきます。
各シートの列構成が同じか確認する
まず、各シートの列構成が同じかどうかを確認します。列の並びや項目名が揃っていないと、単純な集計方法では正しい結果を得られません。
集計したい項目と単位を決める
集計したい項目と、その単位を決めます。金額なのか件数なのかによって、適した集計方法や関数が変わってきます。
シート追加や更新の頻度を確認する
今後シートが追加される頻度や、データが更新される頻度を確認します。頻繁に更新が発生するかどうかは、後で集計方法を選ぶ際の重要な判断材料になります。
関数で複数シートを集計する方法
データ構造が確認できたら、関数を使った集計方法を確認します。
シートの構成が揃っているかを伝えると、使える関数が絞られます。
あなたはExcelの集計に詳しい担当者です。次の複数シートを関数で集計する方法を教えてください。
・シートの数と名前：〇〇
・各シートの表の構成：〇〇
・集計したい値の位置：〇〇
・シート構成が全て同じか：〇〇出力は数式と、その数式が前提としているシート構成を書いてください。構成が揃っていない場合に起きる問題も示してください。
少数の固定シートをセル参照で集計する
シートの数が少なく、構成が固定されている場合は、セル参照を使って集計します。シートが増減しない前提であれば、シンプルな参照だけで十分対応できます。
SUMなどの関数で同じ項目を合計する
SUM関数などを使って、各シートの同じ項目を合計します。シートごとに同じ位置に同じ項目があることが、この方法を使う前提になります。
シート名や参照範囲の変更に注意する
シート名の変更や参照範囲の変更があった場合、数式に影響が出ないかを注意深く確認します。名前を変更すると、参照が正しく更新されないことがあります。
3D集計を使えるケースと使えないケース
関数の使い方が分かったら、3D集計が使える場面かどうかを見極めます。
同じ位置のセルをまとめて集計する
複数シートの同じ位置にあるセルを、まとめて集計します。3D集計は、シート間でレイアウトが完全に一致している場合に適した方法です。
シート構成が揃っているか確認する
対象となるシートの構成が、すべて揃っているかを確認します。一つでもレイアウトが異なるシートがあると、3D集計では正しい結果を得られません。
項目位置が変わるデータでは別の方法を選ぶ
シートごとに項目の位置が異なる場合は、3D集計ではなく別の方法を選びます。無理に3D集計を適用すると、意図しない項目同士が合算されてしまう可能性があります。
Power Queryで複数シートを統合する方法
3D集計が使えない場合は、Power Queryでの統合を検討します。
シートごとの違いを伝えると、統合前に必要な整形が分かります。
あなたはPower Queryに詳しい担当者です。次の複数シートを1つの表へ統合する手順を教えてください。
・シートの数：〇〇
・シートごとに異なる点：〇〇
・統合後に必要な列：〇〇出力は手順の順に番号を付けて書いてください。統合前に揃えておくべき点があれば先に示してください。
各シートのデータを取り込む
対象となる各シートのデータを、Power Queryへ取り込みます。取り込む範囲を明確にしておくことで、不要なデータの混入を防げます。
列名とデータ型を揃えて結合する
取り込んだデータの列名とデータ型を揃えたうえで結合します。列名や型が揃っていないと、結合後のデータに不整合が生じてしまいます。
更新時に再利用できるクエリにする
データが更新された際に、再利用できるクエリとして作成します。一度作成したクエリを使い回せる形にしておくことで、次回以降の集計作業を大幅に減らせます。
関数とPower Queryを使い分ける基準
統合の方法が分かったら、関数とPower Queryをどちらを使うべきかの基準を確認します。
固定された小規模集計は関数を候補にする
シート数が少なく、構成が固定されている小規模な集計には、関数を候補にします。シンプルな集計であれば、関数だけで十分対応できる場合があります。
シート追加や定期更新が多い場合はPower Queryを検討する
シートが頻繁に追加されたり、定期的な更新が発生したりする場合は、Power Queryを検討します。更新のたびに数式を組み直す手間を減らせることが大きな利点です。
利用者のスキルと保守方法も含めて決める
実際にこの表を扱う利用者のスキルや、今後の保守のしやすさも含めて方法を決めます。高度な仕組みを作っても、扱える人が限られていては運用が続きません。
ChatGPTへ集計方法を相談するときの伝え方
使い分けの基準が分かったら、ChatGPTへ相談する際の伝え方を確認します。
作業の頻度と使う人を伝えると、関数とPower Queryのどちらが向くか判断してもらえます。
あなたはExcelの集計に詳しい担当者です。次の作業を関数とPower Queryのどちらで行うべきか判断してください。
・集計したい内容：〇〇
・シートの数と増減：〇〇
・作業の頻度：〇〇
・ファイルを扱う人のExcel習熟度：〇〇出力は推奨する方法と、その理由、もう一方を選ぶとしたらどんな場合かを書いてください。
シート構成と集計目的を具体的に伝える
シートの構成と、この集計で達成したい目的を具体的に伝えます。情報が詳しいほど、状況に合った集計方法を提案してもらいやすくなります。
更新頻度とシート数を伝える
データの更新頻度と、対象となるシート数もあわせて伝えます。これらの情報は、関数とPower Queryのどちらが適しているかを判断する材料になります。
提案された手順を自分のExcel環境で確認する
提案された手順を、自分のExcel環境で実際に確認します。バージョンや設定によって、提案された機能が利用できない場合があります。
集計結果を元シートと照合する方法
集計方法が決まり実行できたら、最後に結果を元シートと照合します。
元シートの合計と集計結果を比較する
元の各シートで個別に計算した合計と、集計結果を比較します。両者が一致しない場合、集計方法のどこかに誤りがある可能性があります。
抽出漏れや二重集計がないか確認する
特定のシートが抽出から漏れていたり、逆に二重に集計されていたりしないかを確認します。シートの追加や削除があった際に、こうした誤りが発生しやすくなります。
シート追加後も正しく更新されるか試す
新しくシートを追加した後も、集計結果が正しく更新されるかを試します。実際の運用を想定した確認を行うことで、将来的なトラブルを未然に防げます。
まとめ
ChatGPTでExcelの複数シートを集計する際は、データ構造を確認したうえで、関数や3D集計、Power Queryのうち状況に合った方法を選ぶことが大切です。使い分けの基準を踏まえてChatGPTへ具体的に相談し、集計結果を元シートと照合することで、実務で使える集計に仕上げられます。
まずは各シートの列構成が同じかどうかを確認するところから始めてみてください。 Excel業務全体の効率化は、AIでExcel業務を効率化する方法で解説しています。
