- ホーム >
- 応用操作 >
- エクセル(Excel)編 >
串刺し計算と統合 ~ 複数シートと異なるブックの集計 ~
複数の支店や事業所の業績を集計したり、月ごとに分けているシートのデータを年度集計したり、年度ごとにわけているブックからある項目を抽出して年度推移としてまとめるといった、後から集計というケースはよくあります。
本項では、そういった後から集計として便利な機能を学習していきたいと思います。
まずは、ポピュラーな、
串刺し計算
をやってみましょう。
串刺し計算は、文字通り複数のシートを串刺してまとめるようなイメージで、異なるシートを横断して集計する機能です。3D集計などとも呼ばれています。
ネーミングは難しそうですが、非常に簡単にできます。
下図のような、月ごとの推移を示した表を用いて集計してみましょう。
この表は「捜査一課」となっており、捜査一課の経費の推移を月ごとに示した表になります。名称や数字に意味はありませんが、このような表はどのような企業や団体でも作成されることと思います。
そして、課や事業部などの部門ごとに同じ表が作成されていきます。下図は「捜査二課」の表になります。
こうして同じ形式の表が作成されることで、グラフ化して比較したり、分析したりすることができます。このことは前項でも少し触れましたが、同じ形式で入力されているデータであるからこそ利用が容易になるのです。
こうしたデータはシート分けして作成されるのが一般的です。下図のように、シートタブに名前をつけて管理できるからです。
自分だけがデータを扱う場合はどのような形式でもかまいませんが、会社などの組織としてExcelデータを作成する場合には、誰が扱ってもわかるように引き継ぎやすい形式で作成することが重要です。シートなら容易に複製することができます。
このように、
複数のシートに同じ形式で入力されていることが串刺し計算の条件
になります。これだけでもう半分以上できているようなものです。
下図のように、「捜査三課」までシートを作成し、それら3つの課を集計する「総計」シートを作成しました。
これら4つのシートのフォーマットは全く同じです。全く同じでなければ串刺し計算をすることはできません。異なるシートの同じセル番地を串刺すように集計するため、セルが異なると計算は正しくできないのです。
では、「総計」シートを開きます。総計シートには何も値は入っていません。このシートに他3つのシートの合計値を出します。
通常、他のシートの値を計算しようとすると、「=シート名!セル番号+シート名!セル番号」のように1シートずつセルを選択していく方法になります。
この方法は、ピンポイントでセルを指定して計算するため、セル番号が異なっても正しく合計することができます。オートフィルでコピーすればこの方法でも簡単に集計することができますが、シートが5以上になる場合には、1セルで式を作成するだけでもなかなか面倒な作業になります。
串刺し計算で合計を求めてみましょう。非常に簡単と言いましたが、
串刺し計算は手順を間違えるとうまくいかない
ので、手順をしっかり覚えておきましょう。手順は非常に簡単です。
まずは、集計したいセルをすべて範囲指定します。そして、範囲指定した状態で「SUM」ボタンをクリックします。
すると、「B3」セルにSUM関数の式が入力状態となります。この時、選択している範囲(B3:N10)のうちであれば、どのセルを選択していてもかまいません。
少々わかりにくいですが、上図では「B3」セルが選択されており、範囲指定するときの始点のセルが選択状態となります。つまり、四つ角(「B3」、「B10」、「N3」、「N10」)のどれかのセルになります。
次に、集計する最初のシートタブを選択します。これも最終的にすべてのシートを選択するのでどのシートから選んでも構いませんが、通常は両端のシートを選択します。
ここでは「捜査一課」シートを選択し、同じ「B3」セルをクリックします。
ここまでで、「総計」シートの「B3」セルに他のシートの「B3」セルを串刺して集計せよという引数になっています。異なるセルを選択してしまうと「総計」シートの集計がずれてしまいます。(B3セルにN10セルの集計が出てしまうイメージ)
数式を見てみると、「=SUM(捜査一課!B3)」となっています。シートは「捜査二課」でも「捜査三課」でもかまいません。要は、合計値を表示する「総計」シートの「B3」セルと同じセル番号が選択されていることが重要になります。
次に、串刺しにするシートを選択します。選択したシートがすべて計算の対象となります。この場合はすべてのシートを選択します。複数シートの選択は「Shift」キーを押しながらクリックします。(「Ctrl」キーでは選択できません)
すると、式は「=SUM('捜査一課:捜査三課'!B3)」となっています。これですべての引数は入力されました。
最後に、もう一度「SUM関数」ボタンをクリックします。すると、「総計」シートにすべての集計が表示されます。
串刺し計算ではオートフィルコピーは必要ありません。一発ですべてのセルを集計することができます。「B3」セルしか指定していませんが、それぞれのセルでそれぞれ同じセルを串刺し計算しています。
手順を知らなければまずうまくいきませんが、知っていればとても簡単な操作です。
しかし、範囲指定を何度もしてしまったり、最後に「SUM関数」ボタンではなく「Enter」キーを押してしまったり、「Shiftキー」でシートを選択した後にまたセルをクリックしてしまったり、よく操作を間違えます。
集計する範囲を指定する→SUM関数ボタンをクリック→集計するどれかのシートの同じセルを選択する→集計したいシートをすべて選択する→SUM関数ボタンをクリック
という流れを覚えておけば問題ありませんが、操作の意味を理解しておくと忘れることなく身につくと思います。
以上で、異なるシート間の集計については終了です。次は、異なるブック間での集計をしてみましょう。
異なるブックではいくら同じフォーマットでも串刺し計算はできません。
異なるブックで集計するには、
統合
という方法を使います。
シートで分けずにブックで分けるのは、例えば以下のように「年度」で分けたり、「本店」、「支店」のように大分類を分けるケースが多いです。
基本的にブックもブックそのものをコピーして値のみ変更していくケースが多いので、同じ形式で入力されることが多いですが、統合に関しては全く同じ形式である必要はありません。著しく異なる場合は集計できませんが、少々位置が異なっている程度では問題なく集計することができます。
新しく「平均」というブックを作成し、それぞれのブックの平均値を「平均」ブックに集計してみます。
まず、統合を使わないで平均値を単純計算で出す場合は、シートと同様に「=([ブック名]シート名!セル番号+[ブック名]シート名!セル番号)/2」のように1シートずつセルを選択して計算していく方法になります。
AVERAGE関数を使う場合も引数に同様の記述がされます。
一つのセルで式が入力できたらオートフィルコピーで集計することが可能です。(ブックが異なる場合はセルを選択すると、上図のように絶対参照で式が記述されるので相対参照に修正する必要があります)
ただし、この方法ではブックが多くなると記述式が恐ろしく長くなってしまう上に、ブックをいくつも同時に開いていく必要があるので、セルやシートの選択間違いが起こりやすくなります。
統合機能を使ってみましょう。
まず、統合先の「平均」ブックのセルを選択した状態で操作を開始しますが、この時、下図のようにすでに入力フォーマットがある場合と、何もない場合で操作が変わってきます。
何もない場合というのは、まったくの白紙状態のことです。つまり、統合は白紙の状態でも良いのです。
ひとまず、ブックをコピーして利用する場合を想定して、値のみが空白の状態で統合してみましょう。空白セルを選択した状態で、「データ」タブの「統合」ボタンをクリックします。
すると、「統合の設定」画面が表示されます。
この画面ですべての統合を管理します。一画面で状況がわかるので長い数式を入れていくより確実です。
今回は平均を求めるので、「集計の方法」のリストメニューから「平均」を選択します。そして、「統合元範囲」にカーソルを置いた状態で統合する範囲をマウスで範囲指定します。
まず、「令和2年度」ブックの範囲を選択します。この場合は、項目名の部分は除いて値の部分のみ(「B3:N10」)を範囲指定します。
範囲が転記できたら「追加」ボタンをクリックし、「統合元」へ範囲を登録します。
これで「令和2年度」ブックの範囲が登録され、次の範囲を選択できるようになります。同様に「令和3年度」ブックの範囲を選択して「統合元」に登録します。
このようにして、範囲はいくらでも追加していくことができます。
今回はこれだけの範囲なので、次の「統合の基準」を選択しますが、ここは前述の白紙ページに統合する場合に必要になる項目になるので、後述します。
次の「統合元データとリンクする」のチェックは、それぞれのブックの値とリンクさせるかどうかのチェックになります。つまり、ここにチェックを入れると、統合元として指定した範囲のデータを変更したらともなって再計算してくれるようになります。
統合後にデータ修正の必要がなければチェックは不要、データ修正を見込む場合はチェックを入れるようにしましょう。ひとまずチェックしないで「OK」ボタンをクリックします。
すると、2つのブックの値が統合されたデータ(この場合は平均)が新しいブックに入ります。
このようにすべてのセルに値が一発で入るので非常に簡単です。
では次に、白紙のシートに統合してみましょう。白紙のシートの任意の位置にセルを置いた状態で、同様に「統合の設定」画面に範囲を登録していきます。この時、下図のように今度は「A列」と「行2」のタイトルセルも範囲に含みます。(「A2:N10」)
続いて、2つ目のブックも同様に範囲登録します。次に「統合の基準」で「上端行」と「左端列」にチェックを入れます。
もうおわかりのとおり、この場合の「上端行」は「月」、「左端列」は「項目名」のタイトルセルになっており、その文字列を基準として結合するという意味になります。
この状態で「OK」ボタンをクリックすると、空白シートにタイトル行、タイトル列を含めたすべての値が入ります。
また、ここにチェックを入れることで、タイトルセルが同じもの同士を統合するため、多少順番を変更しても同じタイトルの行や列で正しく結合してくれます。(項目名が一致していることが条件になります)
下側の表が左端行の順番を入れ替えて統合した表になりますが、まったく同じ結果になっています。
では最後に、「統合元データとリンクする」にチェックを入れて統合してみましょう。ここにチェックを入れて統合すると、下図のように「+」マークが現れるようになります。
これは、統合元のデータをExcelに埋め込んだ表示になっています。「2」もしくは「+」をクリックすると、展開して戻データを参照することができます。
元データの値を変更すると、埋め込まれたデータもともなって変更します。また、ブックを閉じた後に値が変更になった場合でも、統合したブックを開くと下図のメッセージが表示されるので、「更新する」を選択して最新の値を引用してくることができます。
そのため、ブックを開くたびに修正をかけることができます。
このように、統合はあまりメジャーな機能ではありませんが、非常に簡単にブック間の集計を行うことができます。年度ごとの集計や部署ごとの集計には非常に有用な機能です。
シート間の串刺し計算、ブック間の統合をしっかり覚えておいて、様々な場面で活用してみてください。
更新履歴
- 2021年10月21日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします!