VLOOKUP関数：別シートにある参照範囲が変動する場合

スポーツクラブで学童会員の名簿を作成しています。

【目的】
VLOOKUP関数を利用して、シート1「名簿」のA列に会員番号を
手入力すると
B列に学年、C列に氏名が各々自動入力されるようにしたい。

【設定】
・シート1を「名簿」とし、
　A列を会員番号、B列を学年、C列とD列を結合して氏名としました。
・シート2を「参照データ」とし、
　A列を会員番号、B列を学年、C列とD列（結合を解除）を氏名と
　しました。
・シート2の1行目はタイトル行とし、データが入力されているのは
　2行目からです。
・別シートのデータを参照するため、シート2のデータが入力されて
　いる範囲に「参照範囲1」という範囲名をつけました。

【状況】
現在設定している数式は　B2=VLOOKUP(A2,参照範囲1,2,FALSE)
なのですが、以下の場合は「参照範囲1」で設定した範囲とは
異なる範囲のデータを参照しなければなりません。
・入会者がいる場合：データを追加するため、行が増える
・退会者がいる場合：データを削除しブランクを解消するため、
　行が減る

【質問】
上記状況の場合、VLOOKUP関数に加えてどのような関数を使用すれば
別シートにある参照範囲の変動に対応できるのでしょうか？
会員が増減するたびに、新たに参照範囲を設定し範囲名をつけ直す
という作業は避けたいのですが・・・

ご教示いただけますよう、何卒宜しくお願い申し上げます。

No.3 ベストアンサー 回答者： mitarashi 回答日時： 2008/10/19 15:56

#1です。

実用上は#2さんの仰る通りですが、覚えておけば、ピボットテーブルや、グラフに動的な範囲を与える時に役に立つでしょう。
関数で直に与えるなら、下記のようにIndirectまたはOffsetを用いてできます。（動的名前に設定するのと同じ式ですが）
Sheet1
................A...............B
..1.............あ...............b
..2.............え...............c
式は、下記のどちらでもよいです。
B1の式=VLOOKUP(A1,INDIRECT("Sheet2!$A2:$B" & COUNTA(Sheet2!$A:$A)-1),2,FALSE)
B2の式=VLOOKUP(A2,OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,2),2,FALSE)
Sheet2
.................A...............B
..1.......見出A.......見出B
..2.............い...............a
..3.............あ...............b
..4.............え...............c
..5.............お...............d
..6.............か...............e
..7.............き...............f

この回答へのお礼

ご回答ありがとうございました！

ご指摘いただいたとおり、今後の勉強や実用のためには
様々な関数を身につけていこうという前向きの姿勢が
大切ですね。
Indirect、Offset、Counta関数についても勉強しようと
思います。

お礼日時：2008/10/19 17:35

No.4 回答者： mike_g 回答日時： 2008/10/19 16:25

Sheet1!B2: =VLOOKUP($A2,OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),4),2,FALSE)

Sheet1!C2: =VLOOKUP($A2,OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),4),3,FALSE)&" "&VLOOKUP($A2,OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),4),4,FALSE)

この回答へのお礼

ご回答ありがとうございました！

OFFSETとCOUNTAを使うのが正攻法なんでしょうね。
今後の参考にさせていただきます。

お礼日時：2008/10/19 17:54

No.2 回答者： hallo-2007 回答日時： 2008/10/19 15:35

別に

・別シートのデータを参照するため、シート2のデータが入力されて
　いる範囲に「参照範囲1」という範囲名をつけました。
を
シート2のA～D列の範囲を名前付ければよくありませんか？
=VLOOKUP(A2,シート2!A:D,2,FALSE)
でも良いような気がしますが。

この回答へのお礼

ご回答ありがとうございました！

ものすごくシンプルな解決法があったんですね。
最初から列全体を範囲に指定してしまえば
後からわざわざ範囲を変更せずに済む、というわけか~。

目からウロコな回答をありがとうございました。

お礼日時：2008/10/19 17:59

No.1 回答者： mitarashi 回答日時： 2008/10/19 15:32

データ数に応じて、参照範囲が動的に変化する「動的名前」を用いると良いです。

参考URL：http://office.microsoft.com/ja-jp/excel/HA011261 …