Excel(エクセル)で複数のシートをまとめて集計したいのに、思うようにうまくいかない…そんな悩みを抱えていませんか？
「商品別や店舗別にシートを作ったけれど、合計を出す方法が分からない」「=SUM関数を入れてみたけれどエラーになってしまう」「1枚ずつ手入力するしかないの？」
実は、エクセルにはシートをまたいで数値をまとめて集計できる便利な方法が用意されています。
それが「シート間集計（串刺し集計）」と呼ばれるやり方です。
ひとつのシートで数式を入力すれば、複数のシートのデータを一括して合計できます。
これを知っているかどうかで、作業効率は大きく変わります。手入力やコピー＆ペーストに頼る必要もなくなり、計算ミスの防止にもつながります。
そこで今回の記事では、シートをまたいでSUM関数を使う方法や、効率よく正しく操作するためのコツ、さらに実際によくある失敗例とその対策までを詳しく紹介します。
ぜひ参考になさってください。
Excelのシート間集計って難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
シートをまたぐSUM関数の準備とルール
シートの準備
エクセルで複数のシートに分かれたデータを集計する場合、まずは各シートの準備が大切です。
ポイントは、「最初のシートを作成してから、そのシートをコピーして集計に必要なシートを作る」という方法です。
たとえば、1月分の売上表を作るとします。
まずシート1枚目に売上表を作り、必要な行や列、見出しの位置を決めます。
その後、このシートのコピーを作り、シート名を2月、3月…と変更します。
こうすることで、全ての月のシートで表のレイアウトをそろえることができ、後でSUM関数を使った集計も簡単になります。
ポイントは、コピーした後に表の行や列の位置を変えないことです。
もし1枚目のシートと列や行の位置がずれてしまうと、後で合計を計算する時に正しく集計できなくなります。
今回の例では、横浜店・大阪店・博多店の3店舗分の売上シートと、3店舗分の合計を集計するシートの計4枚を作成します。
まず、1枚目のシート「横浜店」を作成します。
シートを作成する時は、数式を入力したり、罫線などの書式まで設定して完成させましょう。
1枚目のシートが完成したら、作成したシートをコピーします。
「Ctrl」キーを押しながら、「横浜店」のシートの見出しを右方向にドラッグします。
これで1枚目のシートを簡単にコピーすることができます。
コピーされたシートは「横浜店(2)」のように、番号が最後に表示されます。
※この操作の他に、「横浜店」のシートの見出しを右クリックし、メニューの一覧から「移動またはコピー」で、「コピーを作成する」にチェックを入れて「OK」をクリックすると、同じシートを複製することができます。
コピーしたシートの売上金額が入力されているセル範囲のデータを削除します。
「Ctrl」キーを押しながら、「横浜店(2)」のシートの見出しを右方向にドラッグします。
同じ操作を繰り返すことで「横浜店(3)」「横浜店(4)」と複製することができます。
コピーして作成したシート名をそれぞれ、「大阪店」「博多店」「売上集計」に変更します。
シート名を変更する時は、シートの見出しをダブルクリックしてシート名を編集状態にすることができます。
集計以外の各シートに必要な売上データを入力します。
データを入力すれば、数式が入力されているセルに自動で計算結果が表示されます。
集計のシートはデータを削除したままにしておきましょう。
ここでは、シートの見分けのため、「売上集計」シートのタイトルと、表の塗りつぶしの色を変更しています。
このような感じで、1枚目のシートをコピーすれば、同じレイアウトで必要な表を作成することができます。
シート間集計のルール
エクセルで複数のシートをまとめて合計する場合、「シートをまたぐ集計」や「串刺し集計」と呼ばれる方法を使います。
この方法は、シートのレイアウトが完全に一致していることが前提です。
具体的には、1月～3月の売上データを合計する場合、各シートの同じ位置にあるセルの値を集計します。
たとえば、売上データがセル【B2】から始まっている場合、各シートのセル【B2】の値が合計されます。
もし表の開始位置がシートごとにずれていたり、行や列の順序が違ったりすると、SUM関数で正しく集計できません。
そのため、シートをコピーして作る方法を使うこと、見出しや表の配置を全シートでそろえることがとても大事です。
シートをまたぐSUM関数は便利ですが、最初に準備をしっかりしておかないと集計ミスにつながります。
エクセルに慣れていない方でも、この「最初に1枚作ってコピーする」という手順を守るだけで、後で簡単に集計ができるようになります。
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
複数シートをまたいだ集計方法
各シートのレイアウトが全く同じになっていることを確認したら、集計のシートに数式を入力しましょう。
【複数シートをまたいだ集計の手順】
- 集計先のシートのセルを選択します
- セルにSUM関数を挿入します
- 左端のシートを選択します
- 合計する数値が入力されたセルを選択します
- 「Shift」キーを押しながら、右端のシートをクリックします
- 数式バーに「=SUM('左端のシート名：右端のシート名'!セル番地)」の形式になっていることを確認します
- 「Enter」キーを押して確定します
- 下方向と横方向に数式をコピーします
これで複数のシートをまとめて集計することができます。
シート「横浜店」「大阪店」「博多店」、この3枚の売上をシート「売上集計」に出します。
まず、シート「売上集計」をクリックし、セル【C4】をクリックして選択します。
次に「ホーム」タブをクリックし、「編集」グループにある「オートSUM」をクリックします。
セル【C4】にSUM関数が挿入されます。
左端にあるシート「横浜店」の見出しをクリックします。
すると、数式バーに「=SUM(横浜店!)」と表示されます。
数式を入力しているシートから、シートを切り替えているため、入力中の数式の確認はこの数式バーで行いましょう。
さらに続けて、セル【C4】をクリックしましょう。
すると、数式バーに「=SUM(横浜店!C4)」と表示されます。
数式バーに「=SUM(横浜店!C4)」と表示されますが、これは 「シート『横浜店』のセル【C4】の値を合計する」 という意味です。
エクセルでは、他のシートを参照するときに
シート名 + 「!」 + セル番地 という書き方をします。
つまり「=SUM(横浜店!C4)」は、通常の「=SUM(C4)」と同じくセル【C4】を合計しますが、その対象が「今いるシート」ではなく「シート『横浜店』」にあるセル【C4】になる、という違いです。
次に、「Shift」キーを押しながら、右端のシート「博多店」をクリックします。
※この操作により、「横浜店」から「博多店」までのすべてのシートが範囲として選択されます。
「Shift」キーを押しながらシートの見出しをクリックしたら、数式バーを確認します。
今回は、数式バーには「=SUM('横浜店:博多店'!C4」と表示されます。
「=SUM('左端のシート名:右端のシート名'!セル番地」の形式で表示されていることを確認し、「Enter」キーを押して確定しましょう。
「=SUM('横浜店:博多店'!C4)」は、「シート『横浜店』からシート『博多店』までのすべてのシートにある、セル【C4】の値を合計する」 という意味です。
ポイントは3つです。
- '横浜店:博多店'
→ シート名を「:（コロン）」でつないでいます。
これは「横浜店から博多店まで、間にあるシートもすべて含む」という範囲指定をしています。
もし「横浜店」「大阪店」「博多店」の順で並んでいれば、3つのシートすべてが対象になります。 - !（びっくりマーク）
→ 「シートの中を見るよ」という区切りを表します。
その後ろにセル番地が続きます。 - C4
→ 各シートで同じ位置にあるセル【C4】を参照します。
シートごとに値が入っていれば、それを合計するということになります。
これで、シート「売上集計」に1月の商品Aの3店舗分の売上金額を合計することができました。
セル【C4】を選択し、オートフィルを使って、下のセルに数式をコピーします。
相対参照で、下のセルにも合計が表示されます。
続けて、選択した範囲を右方向にドラッグして、オートフィルで合計を表示しましょう。
「Shift」キーを使うメリット
SUM関数を入力する際、次のような方法でシート間のセルを合計する人も少なくありません。
- 「=SUM(横浜店!C4,大阪店!C4,博多店!C4)」のように、半角カンマで区切って入力する方法
- 「=SUM(横浜店!C4+大阪店!C4+博多店!C4)」のように、「+」で足し算のように入力する方法
- SUM関数を使わずに、「=横浜店!C4+大阪店!C4+博多店!C4」のように、単純に足し算だけで数式を書く方法
これらの方法でも計算はできますが、「Shift」キーを押しながらシートを選択する方法には次のメリットがあります。
- 数式が短く、シンプルになる
→ 「=SUM('横浜店:博多店'!C4)」と書くだけで済みます。 - 合計したいシートが多くても入力が楽
→ 例えば、10枚や20枚のシートの合計を出すために、10回も20回もシートとセルをクリック、「+」を入力という操作を繰り返さなくて済みます。
→ 参照するシートが多いほど、この方法が効率が良くなります。 - シートが増えても修正が不要
→ 間に「名古屋店」など新しいシートを追加しても、範囲内にあれば自動で集計に含まれます。
→ カンマや「+」でつなぐ方法だと、新しいシートを追加するたびに数式を修正しなければなりません。
例えば、新しくシート「広島店」を追加した場合です。
「Shift」キーを使って、左端のシートから右端のシートの合計を出すように「=SUM('横浜店:博多店'!C4)」と入力した場合は、間に新しいシートが追加された場合に、自動で追加したシートの値が合計されます。
しかし、SUM関数を使わずに、「=横浜店!C4+大阪店!C4+博多店!C4」のように、単純に足し算だけで数式を入力した場合は、シートとセルを明確に指定して足し算をしているため、数式で参照されたシートとセルしか合計されません。
そのため、新しくシートを追加しても数式中にはない参照なので、数式を修正しないと合計されません。
Excelのシート間集計って難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
シート間集計が上手くいかない原因
数式を入力している途中で集計シートに戻ってしまう
よくあるのが、「=SUM(」と入力後に左端シートを選択し、「Shift」キーを押しながら右端シートを選択したあと、数式の確認のために集計用シートに戻ってしまうケースです。
すると式が 「=SUM(売上集計!C4) 」のようになってしまい、エラーや意図しない数式になります。
解決方法・ポイント
範囲を選択したあとは、そのままセルをクリックして確定しましょう。
集計シートに戻る必要はありません。
確認したいときは、数式バーを見るか、「Enter」キーで数式を確定してから見直すと安全です。
シート間集計では、集計シートに数式を入力しますが、「Enter」で確定する前に、数式の確認のために集計のシートに戻ってしまうと・・・
先ほどまで合っていた数式が変わってしまい、正しい結果が表示されなくなってしまいます。
エクセルで数式を入力中、参照したセルの周囲が点線でくるくると回っている状態は、「確定前」を表しています。
そのため、クリックしたセルやシートがそのまま数式に反映されてしまいます。
シート間集計では、入力中の数式は数式バーで確認することができますので、数式を確定するまでは不要なシートをクリックするなどの手順は無い方がトラブルが少なく済みます。
セル番地の選択を忘れてしまう
「横浜店」シートをクリックし、「Shift」キーを押しながら「博多店」シートをクリックするところまではできたのに、セル【C4】をクリックし忘れると数式は「=SUM('横浜店:博多店'!)」のようになってしまいます。
これではどのセルを合計するのか分からず、エラー表示になります。
解決方法・ポイント
シート範囲を選んだら、最後に必ずセルをクリックすることを習慣にしましょう。
「シートの範囲」と「セル番地」の両方を指定して、初めて正しい数式になります。
シートを選択した後に、そのまま「Shift」キーを押しながら右端のシートをクリックすると、セル番地の情報が不足してしまいます。
このまま確定してしまうと・・・
このように、メッセージが表示されてしまいます。
基本的には、左端のシートを選択したらそのすぐあとにセルを参照する癖をつけておくのがおすすめですが、セルの参照は「Shift」キーを押しながら右端のシートを選択したあとでも大丈夫です。
セルをクリックすれば、数式バーに反映されます。
大切なのは、数式バーの数式を確認してから、正しいことを確認して「Enter」キーで確定することです。
「Shift」キーではなく「Ctrl」キーを押してしまう
操作に慣れていないと、「Shift」キーの代わりに「Ctrl」キーを押してしまいがちです。
その結果、「=SUM(横浜店!C4, 博多店!C4)」のように自動で入力されることもありますが、範囲指定の便利さは活かせません。
また、エラーが出てしまい、シートが選択できない状況になってしまいます。
解決方法・ポイント
範囲指定は必ず「Shift」キーです。
「Ctrl」キーは「離れたシートを一枚ずつ選ぶ」動作になるため、今回のような連続範囲には使えません。
SUM関数の中で「+」を入力してしまう
SUM関数を使わずに「=横浜店!C4+博多店!C4」と入力する方法に慣れている人ほどやりがちなミスです。
SUMの中で「=SUM(横浜店!C4+'横浜店:博多店'!)」のように「+」を入力してしまい、エラーが出ます。
解決方法・ポイント
SUM関数は「,（カンマ）」で区切るか、「範囲」を指定する形で使います。「+」は不要です。
連続したシートを参照した数式は、「=SUM('横浜店:博多店'!C4)」というように、シート名の間にコロンが表示されます。
シート名・コロン・シート名・セル番地という順番で表示されているかを数式バーで確認しましょう。
SUM関数を入れ忘れる
「=」から始めて、シート範囲とセル番地を入力しただけの数式、例えば「='横浜店:博多店'!C4」のように入力すると、単なるセル参照になり、計算はされません。
1つのセルの値しか返さないので「集計にならない」というトラブルになります。
この場合、四則演算もSUM関数も入力されていないので計算をするための入力にはなっていません。
解決方法・ポイント
必ず「SUM関数」で囲むようにしましょう。
数値の合計をしたいときは、「=SUM(...)」の形を崩さないことが大事です。
シート間集計に必要な「Shift」キーを押しながらシートを選択するという手順に、ついつい意識が行ってしまい、最初にSUM関数を入力するのを忘れてしまう…ということがあります。
このまま確定すると・・・
このようにエラーが表示されてしまいます。
シート「横浜店」からシート「博多店」までのセル【C4】ということは入力出来ていますが、その範囲のセルを「合計したい」という指示が入っていないためです。
まずは合計を出すためのSUM関数を入力してから、落ち着いて数式を入力しましょう。
循環参照になってしまう
SUM関数を挿入したシート自体を範囲に含めてしまうと、循環参照エラーが発生します。
例えば、集計用シートに数式を入れているのに、「Shift」キーを押しながら、シート「横浜店」を選んでしまうと、集計シート自身も含まれてしまいます。
シート間集計に慣れていないと起こってしまうトラブルですが、結果として、数式が自分自身を参照する形になり、計算ができません。
解決方法・ポイント
集計用のシートは「範囲指定に含めない」ことを意識しましょう。
対象シートは必ず「横浜店」「大阪店」「博多店」など、データが入っているシートだけに限定します。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
シート間集計 よくある質問
シートの順番を入れ替えたら数式はどうなりますか？
「=SUM('横浜店:博多店'!C4)」のように範囲指定している場合、「横浜店」と「博多店」の間にあるシートすべてが対象になります。
「横浜店」と「博多店」の間にシートを移動させれば計算の対象になりますし、その間に無ければ計算の対象外となります。
順番を入れ替えると範囲に含まれるシートが変わるため、意図しない集計になることがあります。
シート順を変えるときは注意しましょう。
一部のシートだけ集計から外したい場合は？
「Shift」キーを使った方法で範囲指定をすると、連続したシートの範囲が対象となるため途中のシートもすべて含まれてしまいます。
特定のシートを外したい場合は、カンマ区切りで指定しましょう。
例：「=SUM(横浜店!C4, 博多店!C4)」
この方法なら、大阪店を除外して集計できます。
集計するセルをコピーしたら、数式も自動で変わりますか？
はい、基本的には相対参照なのでコピー先に応じて自動的にセル番地が変わります。
例えば、セル【C4】を集計する式を横にコピーすると、D4・E4と自動的に変わります。
縦方向にコピーしても同じように行番号が変わります。
集計した数式をコピーしたら、循環参照のエラーが出ました。なぜですか？
集計用のシートも範囲指定に含めてしまった可能性があります。
循環参照とは、自分自身を参照する形のことです。
数式バーに集計のシート名が入っていないかを確認しましょう。
また、数式の入力前に集計のシートとその他のシートを同時に選択してグループになっている場合も同様に循環参照のエラーが表示されます。
画面上部のブック名の横に「グループ」と表示されている場合は、注意が必要です。
シートをまたぐSUM関数のまとめ
いかがでしたか？
エクセルで複数シートをまたいで集計する方法は、決して複雑な仕組みではありませんが、初めて取り組むと混乱しやすい部分です。
特に「=SUM('横浜店:博多店'!C4)」のような数式は、普段1つのシートだけを扱っている方にとっては難しく感じることがあります。
シート間集計の最大のメリットは、同じレイアウトのシートを作っておけば、あとは自動的に横断的な集計ができるという点にあります。
商品別・月別・地域別など、どのような切り口であっても、同じ形式の表を各シートにそろえておけば、集計シートを作るのは非常にスムーズです。
手入力で足し算を繰り返したり、カンマ区切りで一つひとつのシートを指定したりする必要がなくなり、作業効率も大幅に向上します。
「Shift」キーを活用した入力方法は、余計な手打ちを減らし、正確な数式を素早く作成できるメリットがあります。
もちろん、手作業でシート名をカンマで区切って指定しても計算は可能ですが、入力の負担が増え、ミスの可能性も高まります。
効率面と確実性の両方で、「Shift」キーを使った範囲選択は簡単で効率の良い方法ですね。
一方で、今回ご紹介した「よくある失敗例」や「エラーの原因」を知っておくことも大切です。
誤って集計シートを範囲に含めてしまう循環参照や、セルの選択忘れなどは、多くの人がつまずくポイントです。
シート間集計は、手順もそうですが、数式バーをよく確認して操作することがとても重要です。
Excel（エクセル）が苦手であればパソコン教室で学んでみませんか？
Excel（エクセル）はかなり奥が深く、知識を得ただけは操作が身に付きにくいものです。
そんな時には、弊社のようなパソコン教室を利用するのも一つの手です。
「苦手な方専門のパソコン教室パレハ」のレッスンは、動画ではなく、直接講師に質問できる生レッスンです。
生レッスンでありながらリーズナブルに学べるのが人気の秘密です。
また、苦手な方に特化しているのでパソコンが特に苦手だと言う方には最適です。
パソコンが苦手な方でも苦労なく上達できる仕組みがあります。
詳細は下記をご覧ください。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
で複数のシートをまとめて集計したいのに、思うようにうまくいかない…そんな悩みを抱えていませんか？ 「商品別や店舗別にシートを作ったけれど、合計を出す方法が分からない」「=SUM関){kind=link}