INDIRECT 昨年の SUMIFS 関数集計表を 流用 して今年のを作る方法

エクセルシート（EXCEL Sheet）を別のBOOKへ移動やコピーすると、シートの参照先が元のBOOKを参照したままとなりますね。そうならずに、昨年の販売実績データ集計表をシートコピーするだけで、 流用 して今年の集計表を作成する方法を解説します。

みなさんも仕事で集計表を作成することがあるかも知れません。上司から集計表をつくれ！と言われて、集めたデータを日付毎や月毎に合計したり、商品別に合計したり、営業所毎に合計したりすると思いますが、色々と知恵を絞って、ああでもないこうでもないと、怒られながら苦労して作り上げますよね。

これらの集計表は毎月や、毎年といったあるサイクルで繰り返し作るというのが普通だと思います。業務の基本となる部分はシステム化して効率化するといったことが多いかもしれませんが、細かなところは費用対効果を考慮すると、なかなかシステム化まではできていない、といったものも多く残ると思います。

またシステムで作成される集計表はあっても、自分が思うような集計とちょっと違う場合や、説明のためにはもう少しこうしたのだが、、、といった、カスタマイズが必要な時もあり、どうしてもエクセルで集計したい場面はまだまだ多いのかも知れません。

そうして作成した、自分カスタマイズの集計表は繰り返しまた使いたいですし、一部修正・アップデートしながら鰻のタレのように使いたいですね。

こんな時に、エクセルではシートをコピー、またはシートを移動の機能が用意されており、前回の集計表シートを今回のBOOKにコピーしたりして使うととても便利です。

ただちょっと注意が必要で、エクセルのシートコピー（移動）は同じBOOKの中で行う分には、集計表の元データとなる参照先が同じBOOK内にありますので混乱は少ないと思いますが、今回考える、以前に作成した別のBOOKから集計表シートだけをコピーしてきた時は、コピー元のBOOKのデータを参照したままとなり、新しく作成したBOOKのデータを参照する様に変更したい時には、少し手間が掛かります。

データの構造が違う場合は仕方がないと諦めもつきますが、同じ構造のデータなのに、集計表をコピーしてきただけでは使えないのは、もったいない。できれば、なるべく手間をかけずに一発で瞬時に済ませたいところです。

このように、うまくいかない原因はシートを別のBOOKにコピーしてきた時に、前のBOOKを参照先してしまうことにありますので、集計表シートと参照先シートとの繋がりをエクセルの管理下では切り離してしまい、シートをコピーしてきたら、コピー元ではなく、こちらのBOOK内データを参照する様にしてしまえれば問題は解決します。

参照先をエクセルの管理下から切り離すには、INDIRECT関数を使います。INDIRECT関数を使うことにより参照先を文字列で入力するので、エクセル管理下から隠すことができるというわけです。

流用 の具体的な方法

2021年の集計表を2022年にコピーして使うとします。

コピー元の集計表をINDIRECT関数で置き換える

まず、2021年集計シートの数式をINDIRECT関数で修正しておきます。

修正前の集計関数は、次のようになっています。

C4:E6の範囲に配列式として入力しています。（配列式についてはこの記事）

=SUMIFS(data!D4:D8,data!C4:C8,B4:B6,data!E4:E8,C3:E3)

このまま、シートをコピーすると、2021年のdataシートを参照してしまいます。

複数の条件で集計するSUMIFS関数を使って、品名×販売月のマトリクス表を作成しています。
この式のうち、dataシートを参照している箇所（data!）の項目をINDIRECT関数で書き換えます。
次の赤文字の部分が追加した文字です。

=SUMIFS(INDIRECT("data!D4:D8"),INDIRECT("data!C4:C8"),B4:B6,INDIRECT("data!E4:E8"),C3:E3)

図のように入力したら、Ctrl Shift Enterで配列式を確定します。

配列式について分からない場合は、この記事を参照してください。

集計表シートを 流用 して 2022年のBOOKにシートコピーする

シートがコピーされました。数式を確認してみます。

数式は変わっていません。成功です。

一瞬、4月と5月に値が入っていないじゃないか？と思ったかもしれませんが、dataシートの販売商品の構成が2021年と変わっていますので、集計表もあわせて修正が必要でした。

集計する販売品目をdataシートから抜き出して、集計表へ貼り付ければ完成です。

このように、集計したい品目のみを抜き出して集計する、という使い方も可能です。

当たり前ですが、昨年と販売品目が同じであれば、この操作は必要ありませんから、一発で集計が出来たことになります。

まとめ

今回の例では、簡単なデータと集計でしたが、実際にはもっと大変な集計表を時間を掛けて作ることになると思います。

そんな大切な集計表を、何度も使って、使って使って、使いまくって、効率をアップしていきます。

コピーして、改善を加えて、また次にもコピーして、、、と焼き鳥のタレみたいに、自分だけの、簡単には人が真似できない集計表を作成してみてください。

それでは、以上です。

最後まで読んでいただき有り難うございました。

今回使った配列式、SUMIFS関数の使い方の記事も、良かったら参照してみてください。