「串刺し集計」で複数のワークシートをまたぐ計算を実行する

今回は、別々のワークシートにある「同じセル番地」の合計や平均などを求める「串刺し集計」の使い方を紹介する。関連するデータを複数のワークシートに分けて管理している場合に必須となるテクニックなので、前回の「串刺し編集」と合わせて使い方を覚えておくとよい。

「串刺し集計」とは？

前回の連載で紹介した「串刺し編集」は、別々のワークシートにある「同じセル番地」に対して、編集作業を一括指定する操作。一方、今回紹介する「串刺し集計」は、「同じセル番地」の合計や平均などを求めるときに活用できる手法だ。

ちなみに、「串刺し集計」という言葉は正式名称ではなく、一般的によく呼ばれている通称である。「串刺し集計」のことを「3D集計」と呼ぶ場合もあるので、念のため覚えておくとよい。

「串刺し集計(3D集計)」は、以下の図のようなイメージ。

• 「串刺し集計」のイメージ

たとえば、「支社別のデータ」や「月別のデータ」などを複数のワークシートに分けて管理している場合、「各支社の合計」や「各月の平均」などを求めるには、ワークシートをまたいだ計算を行わなければならない。こういった場合に活用できるのが「串刺し集計」だ。

「串刺し集計」を行う関数SUMの入力

それでは、さっそく「串刺し集計」の使い方を解説していこう。以下の図は、「新宿」「上野」「恵比寿」の3会場について来場者数をまとめた表だ。各会場のデータは、3枚のワークシートに分けて「同じ形式」で作成されている。

• 「新宿」のワークシート

• 「上野」のワークシート

• 「恵比寿」のワークシート

今回は、このデータを元に「全会場の合計」を求める。まずは集計用のワークシートを新たに用意し、各会場のデータと同じ形式で表を作成する。

• 「集計用」のワークシート

このとき、新たにワークシートを作成するのが面倒な場合は、既存のワークシートをコピーし、数値データだけを削除して、集計用のワークシートとしても構わない。

これで「串刺し集計」の準備は完了。今回は「各データの合計」を求めるので、関数SUMを使って以下のように操作を進めていく。

(手順1)
まずは、「06/17の第1回」について「各データの合計」を求める。C4セルを選択し、「オートSUM」をクリックすると「=SUM()」という関数が入力される。

• 関数SUMの入力

(手順2)
続いて、関数SUMの引数に「合計する範囲」を指定していく。先頭セルの指定は、最初のワークシート(新宿)を選択し、C4セルを選択すればよい。

• 先頭セルの指定

(手順3)
次は、「合計する範囲」の末尾セルを指定する。この操作は、「Shift」キーを押しながら最後のワークシート(恵比寿)を選択すればよい。セル番地はC4セルのまま変化しないので、セルの選択を行う必要はない。

• 最終セルの指定

(手順4)
これで「合計する範囲を」指定できた。そのまま「Enter」キーを押すと関数SUMの入力が確定され、集計用のワークシートに計算結果(合計)が表示される。

• 「串刺し集計」の計算結果(合計)

C4セルを選択して関数SUMの記述を確認してみると、
=SUM(新宿:恵比寿!C4)
という関数が入力されているのを確認できる。念のため、この引数の意味について解説しておこう。

「！」より前にある文字は「ワークシート名」。今回の例では「新宿:恵比寿」と記述されているので、「新宿」から「恵比寿」のワークシートを示している。その後、「！」の後に「C4」セルが指定されている。

つまり、この関数SUMの記述は、「新宿」から「恵比寿」のワークシートにある「C4」セルを合計せよ、という意味になる。

このように、「:」(コロン)を使って「ワークシートの範囲」を引数に指定することも可能だ。今回は「オートSUM」を使って関数SUMを入力したが、同様の記述を手入力で指定しても構わない。

もちろん、SUM以外の関数にも同様の手法が活用できる。たとえば、AVERAGE(新宿:恵比寿!C4)と記述すると、「新宿」から「恵比寿」のワークシートにある「C4」セルの平均値を求めることができる。

関数SUMの一括入力

先ほどの集計表を完成させるには、C4以外のセルについても同様の操作を繰り返し、各データの合計を求めていく必要がある。しかし、これはかなり手間のかかる作業。そこで、関数を一括入力する操作方法も覚えておくとよい。簡単に紹介していこう。

まずは、関数を入力する「セル範囲」を選択する。この状態のまま、先ほど示した(手順1)から(手順3)の操作を行い、関数SUMの引数を指定する。

• セル範囲の選択と関数SUMの入力

• 関数SUMの引数の指定

続いて、関数SUMの入力を確定させるが、このとき単に「Enter」キーを押すのではなく、「Ctrl」＋「Enter」キーを押すのポイント。すると、「最初に選択していたセル範囲」に、関数を一括入力することが可能だ。もちろん、それぞれの引数は、セルの位置関係に応じて自動調整される仕組みになっている。

• 一括入力された関数SUM

これで、同じ形式の関数を一括入力できる。関数を1つずつ入力していく場合に比べて、かなり作業時間を短縮できるはずだ。「それぞれの関数が正しく入力されているか？」が気になる場合は、適当なセルを選択して関数の記述を確認しておくとよいだろう(上図を参照)。

ワークシートの統合

Excelには、ワークシートの数値データを自動集計できる「統合」という機能も用意されている。後々のことを考えると、自分で関数を入力した方が使い勝手はよくなるが、念のため「統合」の使い方も紹介しておこう。

「統合」の機能を利用する場合も、あらかじめ集計用のワークシートを作成しておく必要がある。続いて、数値データを集計する「先頭セル」を選択し、「データ」タブにある「統合」をクリックする。

• セル範囲を選択し、「統合」をクリック

すると、以下のような画面が表示されるので、最初に計算方法を選択。続いて、「統合元範囲」の「↑」ボタンをクリックし、統合元の範囲を指定していく。今回の例では、「新宿」のワークシートを選択し、「C4:G8」のセル範囲を指定すればよい。

• 計算方法の指定

• 統合元の指定(1シート目)

「統合の設定」の画面に戻るので、「追加」ボタンをクリックして2シート目のセル範囲を追加指定する。今回の例では、「上野」のワークシートにある「C4:G8」のセル範囲を指定すればよい。

• 統合元の追加

• 統合元の指定(2シート目)

同様の手順を繰り返して、必要なだけ統合元のワークシート(セル範囲)を追加指定していく。統合元を正しく指定できたら、「OK」ボタンをクリックする。

• 「統合元の確認」と「ワークシートの統合の実行」

自動的に計算が行われ、その結果が集計用のワークシートに表示される。

• 自動集計された計算結果(合計)

このように、「統合」の機能を使って「串刺し集計」と同じ結果を得ることも可能である。ただし、「統合」の機能を使った場合は、計算結果が「数値データ」として入力されることに注意する必要がある。このため、元データの数値を変化させても、合計や平均などの数値は再計算されない。

集計結果を元データと連動させるには、この連載の前半で示したように、関数を利用する必要がある。後で数値が変更される可能性を考慮するなら、関数で「串刺し集計」を行うのが基本といえるだろう。