Excel。複数シートの同じ位置にある値を、別のシートに簡単にまとめて表示したい
＜INDIRECT関数＞
集計シートなど、シートごとの値をまとめるシートを作るときに、シート名を含めたセル参照は簡単ですが、面倒な作業といえます。
例えば、次のようなファイルをつかって説明していきます。
集計シートは、日々の売上高のデータをまとめたものです。
B2には、11月1日のシートの値をセル参照させています。
11月1日のシートは、次のようになっています。
11月2日以降の各シートも同じレイアウトをしています。
つまり、各シートのB4に売上高の合計値があるというわけです。
これを集計シートにまとめた、一覧表を作成したいのが目的です。
では、B2に数式を作って、オートフィルで数式をコピーしてみます。
B2の数式は
='11月1日'!B4
として問題は無いのですが、これをオートフィルで数式をコピーしたら、「0（ゼロ）」と算出されてしまいました。
原因は、
B3をクリックすればすぐにわかります。
数式が、
='11月1日'!B5
となっています。
当然ですが、オートフィルで数式をコピーしても、シート名が連動して変わってくれることはありません。
セル番地のB4がB5になっただけです。
これでは、オートフィルで数式をコピーことはできないので、1シートずつ、セル参照で数式を作らないといけないわけで、3日程度ならば、パワープレイでも、いいかもしれませんが、30日分となれば、30回同じ作業を繰り返すのは、面倒でしかありません。
シート名を修正するとしても、シート数が増えれば、これまた面倒な作業でしかありません。
かといって、Excel VBAでつくるとしても、シート間を操作しなければならず、プログラム文が多くなりそうです。
そこで、「INDIRECT関数」をつかった数式をつくることで、オートフィルで数式をコピーすることができます。
そこで、INDIRECT関数をつかった数式に変更していきます。
B2には、
=INDIRECT(TEXT(A2,"m月d日")&"!b4")
という数式を設定しました。
オートフィルで数式をコピーすると、今度は、希望通りの値を表示することができました。
数式自体も、長くないこともあり、手早く、簡単に処理することができます。
使用しているINDIRECT関数は、文字列をそのまま数式などで使うことができる関数です。
INDIRECT関数の引数にTEXT関数をネストしています。
このTEXT関数をつかっている理由は、シート名が日付だからです。
INDIRECT関数で日付を参照させると、シリアル値になってしまうので、「11月1日」という文字で使用することができません。
そこで、TEXT関数で表示形式を「月日」に変更することで「11月1日」という文字としてつかえるようになります。
数式にシート名が含まれると、修正などの作業も面倒になることがありますので、アイディアが必要になることがあります。
