タグ別アーカイブ: 串刺し演算

複数シートを自動集計~3D参照~

筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました

月ごとの数字を管理するのに、ひたすらシート名+シート名+シート名のつるべ打ちをしていたのです

この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです

一方、今回の回で紹介する3D参照では、一気に複数シートをSUM関数で集計することができます

シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます

下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています

この3D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます

それでは3D参照についてのポイントを2つ、以下に解説します

A.文法

以下は複数シートをシート名のつるべ打ちで集計する数式です

シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか?

 =’シート名’!セル名+’シート名’!セル名+・・・

今回の3D参照では、+でシート名を足す代わりに「’と’!」の中で、通常のSUM関数と同じ様に”:”で範囲を指定するのです

こんな↓感じです

B.ワイルドカード*

シート名を工夫した上での前提ですが、*カードを使って更に複数シートの集計作業の効率化を行うことができます

例えば、シート名を四半期単位で集計できるように工夫しておきます 

そうすれば、ワイルドカードを使った集計が行えるようになります

例えば、シート名が1Q_4月、1Q_5月、といった具合に並んでいる場合は、1Q以降を*で置き換えます

このワイルドカードを使えば、SUM関数内に1つのシートでも指定すれば、後は必要箇所を*で置き換えるだけですので、とても効率的です

ポイントを2点、文法とワイルドカード*について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください

サンプルファイルは完成と演習の2つのシートがあります

演習のシートでは実際の操作の様子を参照できるように、2つの方法を用意してあります

どちらもフリーアドインを使用した方法です

①Web Video Player

このアドインはエクセルシート上で動画を開くことができます

演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください

➁QRコード

演習シート上のQRコードをスマホで読み込んでください

動画が自動的にスマホ上で開きます

<まとめ>

今回は、複数シートの集計を、シート名+シート名のつるべ打ちではなく、3D参照によりSUM関数で一気に集計する方法を学びました

3D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます

ですから、’や!の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます!

後は、サンプルファイルを基にして、とにかく手を動かしてみましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村