意外に知られていない素人臭いExcel講座です。
今回は大量のシートを集計して推移表を作る時に、INDIRECT関数を使ってシート名を自動的に参照させる方法です。
状況説明
Excelで1つのシートに1ヶ月分のデータ（今回の場合は試算表）があり、それが大量に（今回は12ヶ月分）並んでいます。
この状態から、一つシートを追加して以下のような12ヶ月通期の推移表（以下通期シート）を作りたいと考えています。とりあえず以下の通り、表の枠だけ作りました。
この状況から各月の勘定科目別の金額を入力していくわけですが、まず、1セル1セル値貼り付けで入力するのは、個別のシートに変更があった時に通期シートにその変更が反映されないので採用できません。
というわけで、vlookup関数を使ってみるかということで、1月分を以下のようにvlookup関数で参照してみました。ここまではうまくいっています。
しかし、ここからが問題です。1月の列の数式をそのままコピーして12月のところまで一気に引き伸ばしたいところですが・・・
ところが、やってみるとわかりますが、シート名が1月、2月、3月・・・と綺麗に並んでいても、コピーした数式はそんな都合よくシート名を参照してくれません。以下のように、1月のシートを参照したままになってしまいます。
できればあと11回もvlookupを記述したくありません。ということでどないしましょうということに相成りました。
INDIRECT関数とは
さて、今回のテーマのINDIRECT関数です。
INDIRECT関数というのは、平たく言うと、文字列を関数の中に”関数の一部として”取り込むことを可能にするものです。例えば以下のように、E2セルに入力されているセル名A1を参照したいときに、”＝INDIRECT(E2)”と記述することで、”E2セルの内容を介して”A1セルを参照することができます。
これを応用すると以下のようなことができます。ちなみに”1月”シートのA1セルには”みかん”と入力されています。
上の式を説明しますと、if関数の「～だったら」の部分は「”1月”シートのA1セルが”みかん”だったら」を表しています。”INDIRECT(D2&”!A1″)”の部分はそのまま関数の一部になります。普通だと、「’1月’!A1」と記述するべき部分です。
ややこしいのは、普通に記述したら出てくる「’」（シングルクオーテーション）がなくなって、！A1を「”」（ダブルクオーテーション）で囲う必要があるということです。
ダブルクオーテーションで囲う理由は、「!A1」が文字列なんですよ！ということを明示するためです。原田なりに一応この２つの理由を考えてみたのですが、あんまり納得いく説明はできそうにないので、そういうもんだと思って覚えてしまっています。
なお、「INDIRECT(D2&”!A1″)」の部分を「INDIRECT(D2)&”!A1″」としてしまうとうまくいきませんので、ご注意ください。!A1も含めて、INDIRECT()の中にあるものをまとめて関数の一部にしときますね、ということですね。
解決策
というわけで解決策です。INDIRECT関数を使って、通期シートを楽々で作ってしまいましょう。
まずどこか一つのセル（例えば1月の売上のところ）に「=VLOOKUP($B3,INDIRECT(C$2&”!$B:$C”),2,FALSE)」と入力します。以下パーツごとに説明します。
- ＄B3：”売上”をキーにしています。常にB列の勘定科目を引っかけるために、「B」の前に＄マークをつけています（絶対参照）。
- INDIRECT(C$2&”!$B:$C”) ：「C$2」の部分が「1月」です。常に2行目を参照するために「2」の前に＄マークをつけています(絶対参照）。
- 2：参照したい数字はb:cの範囲の2列目ですので「2」です。
- FALSE：あいまい参照しないので”FALSE”です。
さて、準備が整いました。
このC3セルを他のすべての月、すべての勘定科目のところにまとめてコピーしましょう。そうすると、INDIRECT関数の中の部分もまとめて相対参照で「ずらして」くれます。
原田は初めてこの関数を知った時、中々の感動を覚えました。みなさんも機会があれば是非やってみてください。
コメント