筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました
月ごとの数字を管理するのに、ひたすらシート名＋シート名＋シート名のつるべ打ちをしていたのです
この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです
一方、今回の回で紹介する３D参照では、一気に複数シートをSUM関数で集計することができます
シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます
下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています
この３D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます
それでは３D参照についてのポイントを２つ、以下に解説します
A.文法
以下は複数シートをシート名のつるべ打ちで集計する数式です
シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか？
＝’シート名’！セル名＋’シート名’！セル名＋・・・
今回の３D参照では、＋でシート名を足す代わりに「’と’！」の中で、通常のSUM関数と同じ様に”：”で範囲を指定するのです
こんな↓感じです
B.ワイルドカード＊
シート名を工夫した上での前提ですが、＊カードを使って更に複数シートの集計作業の効率化を行うことができます
例えば、シート名を四半期単位で集計できるように工夫しておきます
そうすれば、ワイルドカードを使った集計が行えるようになります
例えば、シート名が１Q_4月、1Q_5月、といった具合に並んでいる場合は、１Q以降を＊で置き換えます
このワイルドカードを使えば、SUM関数内に１つのシートでも指定すれば、後は必要箇所を＊で置き換えるだけですので、とても効率的です
ポイントを2点、文法とワイルドカード＊について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください
サンプルファイルは完成と演習の２つのシートがあります
演習のシートでは実際の操作の様子を参照できるように、２つの方法を用意してあります
どちらもフリーアドインを使用した方法です
①Web Video Player
このアドインはエクセルシート上で動画を開くことができます
演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください
➁QRコード
演習シート上のQRコードをスマホで読み込んでください
動画が自動的にスマホ上で開きます
＜まとめ＞
今回は、複数シートの集計を、シート名＋シート名のつるべ打ちではなく、３D参照によりSUM関数で一気に集計する方法を学びました
３D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます
ですから、’や！の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます！
後は、サンプルファイルを基にして、とにかく手を動かしてみましょう！
コメントを残す