Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。
＜INDIRECT関数/INDIRECT＋SUBSTITUTE＋(LEFT＋CELL関数＞
月ごとに集計されているシートがあります。
それぞれのシートの合計値をまとめた、「年間」シートに数値を設定して年間合計を算出した集計シートをつくりたいわけです。
今回のケースは、サンプルなので、2枚のシートですんでいますが、年間となれば12枚のシートですし、もっと多くのシートからデータをコピーする必要があるとすれば、面倒な作業となってきます。
Excel VBAでプログラムをつくってもいいのですが、数式レベルで、効率よくコピーする方法はないのでしょうか？
このような場合、どのようなパターンがあるのか、ないのかを見つけるところから考えていくといいですね。
集計先のシートのB2に、セル参照の数式を設定してみましょう。
='1月'!B5
となっています。
横方向に、オートフィルで数式をコピーすれば、
='1月'!C5
='1月'!D5
と、横方向は、うまく参照結果が表示されていますが、縦方向にオートフィルで数式をコピーしても、当たり前ですが、うまくいきません。
理由は、シート名が違うからです。
シート名を変更する”だけ”かもしれませんが、とても面倒な作業です。
よくみると、シート名と、A列のデータが同じになっています。
シート名をA列に入力されている値そのものを使うことができれば、上手くいきそうです。
そこで、登場するのが、「INDIRECT関数」です。
B2にINDIRECT関数をつかって、次のような数式に変更してみましょう。
B2の数式は、
=INDIRECT($A2&"!b5")
この関数は、
='1月'!B5
という数式をどうやったらつくれるのかをイメージして作る関数です。
A2に1月という文字があるので、それをつかいたいわけです。
オートフィルで数式をコピーすることを前提としていますので、列固定の複合参照にしています。
そして、「&（アンパサンド）」で「"!b5"」という文字を結合させています。
縦方向に、オートフィルで数式をコピーすると、きちんと参照してくれたのですが、横方向にオートフィルで数式をコピーしたら、うまくいきません。
原因は、「"!b5"」。
文字になってしまっているので、オートフィルで数式をコピーしても「b」が「C」に自動的に変わってくれることはありません。
本当は、変わってくれれば、いいのですが…
そこで、どうやったら、「b」を「c」に出来るのかを考えて、B2の数式を次のように修正しました。
=INDIRECT($A2&"!"&SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")&5)
なんか、長くなっちゃいましたが、この数式を、縦方向。
横方向にオートフィルで数式をコピーすると、綺麗に、参照することができました。
では、この長くなった数式の説明をしてきます。「b」を「c」にするための数式が、
SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")
です。
内側から説明しないと、わからないので、最初は、CELL("address",B1)
CELL関数は、セルの情報を算出してくれる関数で、引数にaddressをつかうと、絶対参照がついたセル番地を文字として、算出してくれます。
B1のセル情報なので、「$B$1」という文字が算出されます。
LEFT(CELL("address",B1),2)
は、LEFT(“$B$1”,2)ということなので、LEFT関数をつかって、左から2文字分を抽出します。
これで、「$B」という文字が算出されています。
「$AA$1」だったら3文字分を抽出する必要があります。
SUBSTITUTE($B,"$","") という状態になっていることがわかります。
SUBSTITUTE関数は、置換することが出来る関数なので、「$」を空白に置換させます。
これで、「B」だけを抽出することができるます。
ということで、シート名を参照できるセルをつくることで、INDIRECT関数をつかうことで、別シートから必要なデータを参照することができます。
