Excel。複数のシートから1枚のシートに転記させたい時はVLOOKUPが使えます。
VLOOKUP関数+INDIRECT関数
複数のシートから１枚の集計シートに転記したいけど、セル参照では、とってもたいへんなので、
何か効率的な方法はありませんかね？
というご質問がありまして、何かいい方法はと考えてみたのですが、
ここは、VLOOKUP関数をつかってみても面白いかと思い、ちょっとそれをご紹介。
では、どのような表なのかと言いますと…
シート名は、山田で、A4:E6に 山田という名前の定義をしております。
シート名は、鈴木で、A4:E6に 鈴木という名前の定義をしております。
シート名は、佐藤で、A4:E6に 佐藤という名前の定義をしております。
そして、集計表。
やりたいことは、
それぞれの科目の平均値をこの集計表シートに転記していきたいという訳なのですが、
例えば、セル参照を使ってもいいのですが、
それぞれのセルごとにセル参照を一つずつやらないといけない訳でして、それが、面倒ということ。
具体的に考えてみると、例えば、集計表のB4には山田さんの国語の平均値が入るわけです。
=山田!E4
という数式になるわけですが、これを左側、あるいは、下側にオートフィルハンドルを使って数式をコピーをしても、
うまくいきません。
C4の数式は、=山田!F4 となってしまいます。本当は、=山田!E5にしたいわけです。
さらに、下側ですと、=鈴木!E4 という数式。セル参照をしないといけません。
そこで、何かいい方法はないかというご質問の訳ですね。
確かに3名ぐらいなら、根性でどうにか出来ますが、30人だったら、ちょっと根性では…。
で、今回はVLOOKUP関数とINDIRECT関数を使うと、
意外とこのご希望を叶えることが出来るんですよ。
では、早速作っていきましょう。
集計表のシートのB4をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。
まずは、検索値ですが、B$3。常に3行目を参照させたいので、複合参照にしておきます。
範囲には、INDIRECT関数をネストしますので、INDIRECT関数ダイアログボックスを表示しましょう。
参照文字列には、$A4 と入力します。
INDIRECT関数は、その物自体を使えることが出来ます。
A4には、山田と入力されていますので、その文字を引数として使いたいわけです。
で、なぜ、山田なのかというと、各シートの範囲に名前の定義で、
設定してあるものを使うことが出来るわけです。
実は、名前の定義も適当につけてあるわけではないのです。
では、VLOOKUP関数に戻りましょう。
列番号は、5 と入力します。
検索方法は、完全一致なので、FALSE と入力するか、0（ゼロ）と入力します。
あとは、OKボタンをクリックしましょう。
数式は、
=VLOOKUP(B$3,INDIRECT($A4),5,FALSE)
となります。では、あとは、オートフィルハンドルを使って数式をコピーしていきましょう。
7行目の平均を算出して、それぞれのデータを小数点第2位までにしましょう。
これで、完成しましたね。
このように、VLOOKUP関数はアイディア次第で、こんなところにも使うことが出来るんですよ。
