月別や担当者別に分かれた複数のワークシートを、集計用の1枚へまとめたい場面は少なくありません。
コピーと貼り付けでも統合できますが、行数が増えたり元データが更新されたりすると、手作業では転記漏れや重複が起きやすくなります。
Excelには、数式で一覧化する方法、データの統合機能、Power Query、VBAなど、用途に応じた複数の方法があります。
少ない件数をすぐまとめるならコピーと貼り付け、集計値を合算するなら統合、繰り返し更新する一覧ならPower Queryが便利です。
この記事では、見出し行が1行目にある売上データを例に、複数シートを1つにまとめる操作と、失敗を防ぐための確認点を解説します。
サンプルでは、各シートのA列に日付、B列に商品名、C列に担当者、D列に売上額が入力されているものとします。
複数シートを1つにまとめる基本操作
| シート名 | A列 | B列 | D列 |
|---|---|---|---|
| 4月 | 日付 | 商品名 | 売上額 |
| 5月 | 日付 | 商品名 | 売上額 |
それではまず、複数シートのデータを1枚へ集める基本操作について解説していきます。
同じ列構成の明細データを縦方向へつなげる場合は、集計先シートを作成し、見出しを一度だけ配置してから各シートの明細を順に貼り付けます。
統合先シートの準備
最初に、新しいワークシートを追加し、名前を「統合データ」など分かりやすい名称に変更します。
統合先の1行目には、元シートと同じ見出しを入力します。
このとき、各元シートの見出しが完全に一致していることが大切です。
日付、商品名、担当者、売上額という列の並びが異なると、貼り付け後に列の意味がずれるため、作業前にそろえましょう。
統合先の1行目には見出しだけを置き、2行目以降へ各シートのデータ本体を追加します。
テーブル形式にしたい場合は、統合が終わってから範囲を選択し、挿入タブのテーブルで設定すると管理しやすくなります。
データ本体のコピーと追記
続いては、最初の元シートから2行目以降のデータ範囲を選択してコピーします。
統合データシートのA2セルを選び、貼り付けを実行します。
次のシートでは、統合データシートの最終行の次の行を選択してから、同じように2行目以降を貼り付けます。
見出し行まで含めて貼り付けると、途中に同じ項目名が混ざり、並べ替えや集計で不具合が出ます。
コピーする範囲は必ず2行目から開始することが、手作業で統合する際の重要なルールです。
データ件数が少なく、一度だけ提出用の一覧を作るなら、この方法でも十分に対応できます。
統合後の件数確認
統合後は、各シートの明細件数と統合先の件数が合っているかを確認します。
たとえば4月シートが50件、5月シートが60件なら、見出しを除く統合データは110件になるはずです。
確認式の例は次のとおりです。
=COUNTA(A2:A111)
この式はA列の空白ではないセルを数えるため、日付が必ず入力されるデータでは件数チェックに使えます。
ただし途中に空白行がある場合や、日付列に数式で空文字が返る場合は、別の必須列も確認すると安心です。
【操作のポイント】貼り付け前に各シートの最終行を確認し、統合後は合計件数と照らし合わせましょう。
Power Queryによるデータ統合
| 元データ | 更新頻度 | おすすめ操作 |
|---|---|---|
| 月別シート | 毎月 | Power Queryで追加 |
| 担当者別シート | 毎週 | Power Queryで追加 |
続いては、更新を繰り返すデータに向くPower Queryによる統合を確認していきます。
Power Queryは、複数の表を取り込み、列をそろえ、縦に追加した結果を作成できるExcelの機能です。
一度設定すれば、元データを更新した後に更新ボタンを押すだけで統合表を作り直せる点が大きな利点です。
元データのテーブル化
Power Queryで安定して統合するには、各シートのデータ範囲をテーブルに変換しておく方法が適しています。
各シートで表の中のセルを選択し、挿入タブからテーブルを選択します。
「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して設定します。
テーブル名は、tbl_April、tbl_Mayのように重複しない名前へ変更しましょう。
テーブル名には空白や似た名称を避け、月や部署が識別できる規則を決めると、後からクエリを修正しやすくなります。
すべての元テーブルで列見出しとデータ型を統一しておくと、日付や金額の扱いも安定します。
クエリへの追加操作
続いては、テーブルをPower Queryへ読み込み、1つのクエリに追加する操作を確認していきます。
データタブの「テーブルまたは範囲から」を選び、最初のテーブルをPower Queryエディターへ読み込みます。
閉じて読み込むの横にある選択肢から、「接続の作成のみ」を選ぶと、元データをそのままシートへ複製せずに済みます。
2つ目以降のテーブルも同様に接続として作成します。
その後、データタブの「クエリの結合」から「クエリの追加」を選択し、追加するテーブルを指定します。
縦に明細を増やす操作は「クエリの追加」であり、共通項目を横に結び付ける「クエリのマージ」とは用途が異なります。
追加後に「閉じて読み込む」を実行すれば、統合結果を新しいシートへ出力できます。
更新時の注意点
元シートへ新しい行を追加した後は、統合先の表で右クリックし、「更新」を選択します。
ブックを開いたときに更新したい場合は、クエリのプロパティで更新設定を調整できます。
ただし、テーブル範囲の外へデータを入力すると、新しい行が取り込まれないことがあります。
テーブルの最終行の直下に入力すれば、通常はテーブルが自動拡張され、次回更新時に追加分も統合されます。
列名を途中で変更した場合も、クエリがエラーになる原因になります。
日常運用では、見出し名を固定し、入力する列の順番も変えないことが重要です。
【操作のポイント】毎月同じ形式の表を追加する業務では、Power Queryを使うと転記作業を大幅に減らせます。
統合機能による集計表の作成
| 項目 | 4月 | 5月 | 合計 |
|---|---|---|---|
| 商品A | 120,000 | 140,000 | 260,000 |
続いては、複数表の数値を合算するExcelの統合機能について確認していきます。
この機能は明細行を並べるためではなく、同じ位置または同じ見出しにある数値を集計する用途に向きます。
統合機能の対象範囲
統合機能では、4月シートと5月シートに同じレイアウトの売上表がある場合、対応するセルの数値を合計できます。
たとえば商品Aの売上が両シートのD5セルに入力されていれば、それぞれを足した数値を集計表へ表示できます。
明細を縦に結合したい場合には統合機能ではなく、コピーまたはPower Queryを使います。
目的が一覧作成なのか、合計値の作成なのかを先に分けると、方法を選びやすくなります。
参照の追加手順
統合先シートの左上セルを選択し、データタブから「統合」を選択します。
集計の方法では「合計」を選び、参照欄の右端にあるボタンで4月シートの集計範囲を指定します。
追加を選択して参照一覧へ登録したら、5月シートの範囲も同じように追加します。
見出しを使って集計する場合は、「上端行」や「左端列」のラベルを使用する設定にチェックを入れます。
集計範囲の左端に商品名、上端に月名などの見出しがある場合は、ラベルの設定を有効にすると対応関係を認識しやすくなります。
リンク設定と再集計
「元データとリンクする」を有効にすると、元表の変更に連動する集計表を作成できます。
ただし、集計結果にはアウトラインの記号が表示され、構成が複雑になることがあります。
一度だけ合計表を作りたい場合は、リンクを付けずに確定値として出力する方法も選べます。
元データを更新し続ける集計表では、参照範囲の追加漏れがないかを定期的に見直しましょう。
【操作のポイント】統合機能は同じ項目の合計に適し、取引明細を連結する機能ではありません。
数式による複数シート参照
| セル | 入力内容 | 結果 |
|---|---|---|
| B2 | =SUM(4月:5月!D2) | 同一セルの合計 |
続いては、数式で複数シートのセルを参照してまとめる方法を確認していきます。
数式は、同じ位置にある数値を合計したい場合や、特定のセルだけを集計表へ表示したい場合に便利です。
3次元参照の入力
連続して並ぶシートの同じセルを合計するには、3次元参照を使います。
=SUM(4月:5月!D2)
この式は、4月シートから5月シートまでの間にあるすべてのシートについて、D2セルを合計します。
シート名に空白がある場合は、シート名をシングルクォーテーションで囲む必要があります。
3次元参照は、対象シートがタブ上で連続していることが前提です。
途中に別用途のシートを挟むと、そのシートも計算対象になるため注意しましょう。
数式のオートフィル
集計表のB2セルへ数式を入力したら、セル右下のフィルハンドルを下方向または右方向へドラッグします。
参照先のセル番地が相対参照で変化し、商品別や月別の合計をまとめて計算できます。
たとえばD2を固定したい場合は、数式内の参照を$D$2のように絶対参照へ変更します。
=SUM(4月:5月!$D2)
この形なら列Dを固定しつつ、下へコピーしたときだけ行番号を変えられます。
オートフィル前に相対参照と絶対参照の違いを確認すると、想定外の計算ずれを防げます。
数式方式の限界
数式による参照は軽快ですが、元シートごとに行数が違う明細データを自動的に縦へ並べる用途には不向きです。
また、新しい月のシートを追加しても、3次元参照の範囲外なら自動では集計されません。
シートを追加する運用では、開始用と終了用の空シートを用意し、その間に月別シートを置く設計もあります。
ただし管理ルールが必要になるため、更新の自動化を優先するならPower Queryも検討しましょう。
【操作のポイント】数式は固定位置の集計に向き、可変行数の明細統合には別の方法を選びます。
統合前後のデータ整形
| 確認項目 | 統一例 |
|---|---|
| 日付形式 | yyyy/m/d |
| 売上額 | 数値形式 |
続いては、統合時のエラーや集計ミスを減らすデータ整形について確認していきます。
見た目が似ている表でも、文字列と数値、全角と半角、日付の形式が混在すると、集計結果が正しくならないことがあります。
列名と列順の統一
各シートの見出しは、表記、順番、不要な空白をそろえます。
「売上」「売上金額」「売上額」は人には同じ意味でも、Power Queryでは別の列として扱われることがあります。
列名は完全一致させることが、複数シート統合の基本です。
列の順番が異なる場合でもPower Queryは見出し名で調整できますが、手作業の貼り付けでは誤配置の原因になります。
日付と金額のデータ型
日付が文字列になっていると、日付順の並べ替えや月別集計が期待どおりに動かない場合があります。
セルの表示形式を変えるだけでは、文字列の日付が本当の日付へ変換されないケースもあります。
金額も「12,000円」のように文字が含まれると、SUM関数で合計できません。
数値へ変換した金額の合計式は次のとおりです。
=SUM(D2:D111)
式の結果が0になるときは、対象セルが数値として認識されているかを確認しましょう。
重複と空白行の確認
複数シートを統合すると、同じ伝票や同じ顧客データが重複して入ることがあります。
伝票番号のような一意の列があるなら、データタブの重複の削除を使って確認できます。
削除前にはファイルを複製するか、操作を元に戻せる状態で進めると安心です。
空白行、途中の小計行、タイトル行も統合対象へ混ざりやすいため、元データの表を連続した一覧に整えましょう。
【操作のポイント】統合の前に見出し、日付、数値形式、重複の4点を確認すると後工程が安定します。
まとめ エクセルで複数シートを1つにまとめる方法
エクセルで複数シートを1つにまとめる方法は、作りたい結果と更新頻度で選ぶことが重要です。
明細データを一度だけ一覧化するなら、統合先シートへ見出しを1回だけ置き、各シートの2行目以降を追記する方法が分かりやすいでしょう。
毎月や毎週のように同じ作業を繰り返す場合は、Power Queryでテーブルを追加する仕組みを作ると、更新だけで統合表を再作成できます。
同じ位置の数値を合算したい場合は統合機能またはSUM関数を使い、明細を連結したい場合とは区別しましょう。
また、列名、列順、日付形式、金額のデータ型がそろっていなければ、どの方法でも正確な結果につながりません。
まずは元データの形式を標準化し、目的に合った統合方法を選ぶことが、見やすく再利用しやすいExcelファイルを作る近道です。
