エクセルでシート１のセルに入力すると別シートのセルに表示させるには

依頼されて会員証をエクセル2003で作っている最中です。
SHEET1に下記の様に会員証のベースを作ました。

[●●●の会]
会員番号　[SHEET2の会員番号列］
住所　[SHEET2の住所列］
氏名　[SHEET2の氏名列］
電話番号　[SHEET2の電話番号列］
[管理社名]

SHEET2にまだ空欄ですが、会員番号・住所・氏名・電話番号のリスト表を作りました。

ここからが課題でして、SHEET2に会員番号、住所とセルに打ち込んでいくと

SHEET1のそれぞれに対応したセルに表示させる為に

SHEET1のセルに、[=Sheet2!A3]と打ち込めば、

SHEET2のA3のセルが対応されますが、

これではそのセルしか対応しません。

SHEET2の1行目に会員番号、住所から打ち込んだものは、

単純にSHEET1の表示させたいセルに[=SHEET2!○○]を打ち込めば、

それが対応されます。

ただSHEET2の2行目は空白ですので、新たに打ち込んで対応させようとしても出来ません。

毎度毎度SHEET1の表示させたセルに[=SHEET2!○○]をしていくのは大変です。

新規にリストを打ち込むたびにSHEET1の会員証に

表示させる方法はどういったものがあげられるのでしょうか？

或いは、SHEET1の会員証ベースに打ち込んでいくとSHEET2のリストに

自動的に登録される仕組みの方がやりやすいのでしょうか？

数式も教えていただければ大変有難いです。

アクセスの方がやりやすいかもしれませんが、

依頼した方のPCにはアクセスは入っていないですし、

私自身もアクセスはイマイチの部分があります。

エクセルのマクロなるものを使えません。

年始早々に使用したいとの事ですので、
なんとか仕上げてあげたいとは思います。
宜しくお願い致します。

No.9 ベストアンサー 回答者： web2525 回答日時： 2009/01/03 14:09

＞次のA3に会員番号00002として打ち込み、会員種類を法人、以下違う人の名前や電話番号などを打ち込んでも、SHEET1では会員番号が変わるだけで、他は1行の情報のままです。

。。。。。

=IF(B2="","",ROW())
こちらの式はA2セル入力の後下方向にコピーしてください、Sheet2の会員番号は会員種類に入力があると自動的に表示されます、直接入力はしないでください。
表示形式を00002としたいのであれば、Sheet2のA列とSheet1の会員番号セルをセルの書式設定のユーザー定義で00000としてください。

て入力で00002と入力してその表示になっているので有ればそれは文字列として設定されていると思います、文字列の場合はSheet1の会員番号の呼び出しのMAX関数が働きません。


＞OFFSETは基準の端から端までを考えなくても良いという事で、会員番号の最大値と最終行は等しいから、C2でも良いし、D2でも良いのでしょうか？
＞OFFSETの行数設定で（A:A)ではおかしい事になるのでしょうか？

OFFSETで求めているのはVLOOKUP関数の検索範囲の右下のセル番地です
http://officetanaka.net/excel/function/function/ …
OFFSETの図解説明のサイトです。

この回答へのお礼

サイト拝見しました。
けど、どうしてもOFFSET関数が理解できず、頭抱えました。
色々考えて、
何となくやっていたら
=VLOOKUP($L$3,SHEET2!$A:$G,2,1)←2は検索範囲の中の列番号

にたどり着き、試してみましたが、現時点では計算式は働いています。
OFFSET関数が理解できないのが釈然としないのですが
おいおい勉強して行きます。

長い事つき合わせて申し訳ありませんでした。
且つ新設丁寧にアドバイス頂き、
有難うございました。

お礼日時：2009/01/04 19:46

No.8 回答者： web2525 回答日時： 2009/01/02 12:36

さらに解説：

OFFSET(Sheet2!A1,B2,5)
基点がSheet2!A1と言うのはお分かりですね。
OFFSET関数は引数が（基点,行数,列数,高さ,幅）
今回は高さと幅に関しては入れていませんので
OFFSET(Sheet2!A1,B2,5,1,1)と同じ意味合い。

引数は基点はセル番地、その他は全て数値となります。
今回の様に行数にセルを指定した場合は、セルの内容が引数として参照されます。

VLOOKUP関数は引数に検索範囲の指定が必要となるが、今回の様に検索値が増えていく場合は通常、余分に範囲をとる訳ですが当初の予定より検索範囲が増える場合もあり、そのたびに範囲指定をし直す手間を考え、OFFSET関数でデータの最終位置を求めています。

列数はデータの最終列がG列、今回の検索範囲には”入会日”は必要ないのでF列まで伸ばすために列数=5(5以上であれば可)で固定。

今回の場合はデーターは下方向に増えていくため、VLOOKUPの検索値は行方向に変動、今回は会員番号の最大値とデータ最終行が等しいためB2セルの数値を行数の引数として利用してます。（会員番号を1番から始める場合は行番号-1になりますがOFFSETの引数は0を基準とするため、-1であっても最終行は指定できます)

=IF(B2="","",ROW())
B2にデータ未入力の場合は""（未入力状態)となります。
ROW()関数は引数無しの場合でそのセルの行番号を返します、連続した数値の自動入力などでよく利用されます。

この回答への補足

>OFFSET(Sheet2!A1,B2,5)
>基点がSheet2!A1と言うのはお分かりですね。

ここは判りました。

＞列数はデータの最終列がG列、今回の検索範囲には”入会日”は必＞要ないのでF列まで伸ばすために列数=5(5以上であれば可)で固定。

この部分も判りました。


今手元のsheet2は下記の様な形になっています。

　｜A　　　 |　B　　 |　 　C　|　 D　 |　　E　| F | G　　|
1 ｜会員番号|会員種類|　氏名 |郵便番号|　住所 | Tel ｜入会日|
2 | 00001 |個人　　|山田太郎|111-1111｜東京　|03-2 |2008/1/|
3 |　00002　|法人　　|山田花子|222-2222｜千葉　|23-2|2008/1/15


IF(B2="","",ROW())-1はA2に打ち込みしてあります。

この状態でSHEET1に教えて頂いた数式を当てはめても
次のA3に会員番号00002として打ち込み、会員種類を法人、以下違う人の名前や電話番号などを打ち込んでも、SHEET1では会員番号が変わるだけで、他は1行の情報のままです。。。。。。

>今回は会員番号の最大値とデータ最終行が等しいためB2セルの数値を行数の引数として利用してます。

確かに、会員番号の最大値と最終行は等しいです。
ここでの場合、OFFSETは基準の端から端までを考えなくても良いという事で、会員番号の最大値と最終行は等しいから、C2でも良いし、D2でも良いのでしょうか？
また、OFFSETの行数設定で（A:A)ではおかしい事になるのでしょうか？

補足日時：2009/01/03 01:57

No.7 回答者： web2525 回答日時： 2009/01/01 22:51

解説の訂正：

＞OFFSET(Sheet2!A1,B2,5)の場合は
＞Sheet2のA1から横方向+５移動、（F列)下方向+会員番号分移動（会員番号3だと4行目）＝F5セルを意味します。
＞Sheet2!A2:OFFSET(Sheet2!A1,B2,5)
＞これで
＞Sheet2!A2:F5と同じ意味になります。
誤りです。
正しくは
OFFSET(Sheet2!A1,B2,5)の場合は
Sheet2のA1から横方向+５移動、（F列)下方向+会員番号分移動（会員番号3だと4行目）＝F4セルを意味します。

会員番号　3（A2セル)の場合
Sheet2!A2:OFFSET(Sheet2!A1,B2,5)
これで
Sheet2!A2:F4と同じ意味になります。

これが正解です。

この回答への補足

もの判りが悪い為、頻繁に補足してすいません。

>OFFSET関数は、OFFSET（基点、行数、列数)の表記で
>基点から行数分上下移動、列数分横移動したセルを示します。
>OFFSET(Sheet2!A1,B2,5)の場合は
>Sheet2のA1から横方向+５移動、（F列)下方向+会員番号分移動
>（会員番号3だと4行目）＝F4セルを意味します。
>会員番号　3（A2セル)の場合
>Sheet2!A2:OFFSET(Sheet2!A1,B2,5)
>これで
>Sheet2!A2:F4と同じ意味になります。



A1のセルと列の幅を固定させ、行数だけ可変できる様にする、というやり方をやっていると思いますが、

>OFFSET(Sheet2!A1,B2,5)の場合は
>Sheet2のA1から横方向+５移動、（F列)下方向+会員番号分移動
>（会員番号3だと4行目）＝F4セルを意味します。

上記の数式と言葉との兼ね合いがどうも掴めません。
A1,B2,5
A1はタイトル会員番号でここは不動の位置となっていると思います。
横方向+5移動がB2,5を表しているのでしょうか？
横方向+5移動ですとA列を含めずに勘定してF列に当たり、
（F列)下方向+会員番号分移動（会員番号3だと4行目）＝F4セルの部分がどこの数式を言っているかが判りません。
B2はあまり絡みがない様に感じます。


もう一つ=IF(B2="","",ROW())の数式の意味と言葉の兼ね合いはどういった風でしょうか？
会員番号列A2に、この数式を当てはめると、A2セルが、もしB2（隣のセル）が何もなければ、ゼロ？、？？？？？？

補足日時：2009/01/02 00:36

No.6 回答者： web2525 回答日時： 2009/01/01 22:44

解説;

=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3),2,1)
Sheet1!$B$2ここに関してはただ　B2　で問題ないです、式作るのが面倒なので、コピーする時に便利なようにしただけ。
他の部分もドラッグしてコピーしなければ$は不必要。
=VLOOKUP(B2,Sheet2!A2:OFFSET(Sheet2!A1,B2,3),2,1)
でも動作するはずです。

＞VLOOKUPの式は検索値、範囲、列番号、検索の型となっていますが

今回の場合
検索値は会員番号(B2)
範囲は
Sheet2!A2:OFFSET(Sheet2!A1,B2,3)
列番号は
＜sheet2のリストには
＞A2から下へ会員番号
＞B2から下へ会員の種類（例：ゴールドとプラチナ）
＞C2から下へ氏名
＞D2から下へ郵便番号
＞E2から下へ住所
＞F2から下へ電話番号
＞G2から下へ入会日
の場合だと
住所の場合　5　
氏名の場合　3
電話番号は　6
になります。

OFFSET(Sheet2!A1,B2,3)の説明
これは検索範囲の終点を表します(始点はSheet2!A2）、Sheet2のデータが上記の場合は
OFFSET(Sheet2!A1,B2,5)
となります
OFFSET関数は、OFFSET（基点、行数、列数)の表記で
基点から行数分上下移動、列数分横移動したセルを示します
OFFSET(Sheet2!A1,B2,5)の場合は
Sheet2のA1から横方向+５移動、（F列)下方向+会員番号分移動（会員番号3だと4行目）＝F5セルを意味します。

Sheet2!A2:OFFSET(Sheet2!A1,B2,5)
これで
Sheet2!A2:F5と同じ意味になります。

最後に会員番号の
=IF(B2="","",ROW())　ですが
最初に会員番号が設定されているとデータの最終行の判断が出来なかったためで、データの入力がされていない部分の会員番号を表示しないようにしただけです。


最終的にデータ型式とあわせるとSheet1は
住所セルに
=VLOOKUP(B2,Sheet2!A2:OFFSET(Sheet2!A1,B2,5),5,1)
氏名セルに
=VLOOKUP(B2,Sheet2!A2:OFFSET(Sheet2!A1,B2,5),3,1)
電話番号セルに
=VLOOKUP(B2,Sheet2!A2:OFFSET(Sheet2!A1,B2,5),6,1)

と言う形でしょうかね。

No.5 回答者： web2525 回答日時： 2009/01/01 02:51

ちょっと修正

=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3),2,1)
氏名セルに
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3),3,1)
電話番号セルに
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3),4,1)
と入力

こうすると範囲指定も自動で出来ます。

この回答への補足

色々、助言していただき有難うございます。

こちらのシートの配列ですが

sheet1の
L３にsheet２の会員番号
L５にsheet２の氏名
L７にsheet２の住所
L９にsheet２の電話番号
L11にsheet２の入会日

sheet2のリストには
A2から下へ会員番号
B2から下へ会員の種類（例：ゴールドとプラチナ）
C2から下へ氏名
D2から下へ郵便番号
E2から下へ住所
F2から下へ電話番号
G2から下へ入会日

となっています。

=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:$D$25,2,1)

部分で躓きました。
まず何故＄を使ってセル固定が必要かがわかりませんが
VLOOKUPの式は検索値、範囲、列番号、検索の型となっていますが
検索値とは、どれを挿すのか、列番号はG2では駄目か、などわかりませんでした。

SHEET1にセルを変えてVLOOKUP関数を当てはめても[0]になってしまうので
四苦八苦しましたが、
sheet2の部分にセルを変えて
=IF(B2="","",ROW())を当てはめてた所、VLOOKUP関数の式が機能しました。
ただ、なぜ=IF(B2="","",ROW())がないと機能しなかったのかわかりません。


また、発展系として
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3),3,1)
が出ていましたが
Sheet2!$A$2:OFFSET(Sheet2!$A$1,$B$2,3)
のOFFETの見方が理解しにくいです。
調べると、
基準,行数,列数,高さ,幅
となっており、
基準は多分A1かと思い、3は多分列の幅の個数かと思いますが
それ以外はわかりません。
調べると別のサイトに
高さにCOUNTA(A:A)-1を使う様な事が書いてあり
ますますわかりにくくなりました。

補足日時：2009/01/01 19:41

No.4 回答者： web2525 回答日時： 2009/01/01 02:37

NO3の補足

Sheet2の会員番号は自動で表示されますので住所から入力してください。
会員番号を1番からスタートする場合は、A2セルの入力を
=IF(B2="","",ROW()-1)
にしてください。

No.3 回答者： web2525 回答日時： 2009/01/01 02:33

会員番号が一連番号で順に増えていく形式なら

Sheet１の
会員番号セルに
=MAX(Sheet2!A:A)
住所セルに
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:$D$25,2,1)
氏名セルに
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:$D$25,3,1)
電話番号セルに
=VLOOKUP(Sheet1!$B$2,Sheet2!$A$2:$D$25,4,1)
と入力

Sheet2の会員番号列（A列）に
=IF(B2="","",ROW())
を入力(タイトルの会員番号がA1に表示されてるとして)
下方向コピー

VLOOKUPの範囲は適当に設定してください。

これでSheet1は常に最新の会員登録証になります。

No.2 回答者： A88No8 回答日時： 2009/01/01 01:58

xiu-mingさん、こんにちは

手元に何の資料もない状況なので具体的に何もしてあげられないのですが、xiu-mingさんがしたいことを、もう少し具体化した方がアドヴァイスされる方の支援が受けられそうな気がしました。

xiu-mingさんがしたいことは、SHEET2に作る会員リスト（会員番号・住所・氏名・電話番号）に新規会員を登録したら、SHEET1の「会員証」フォームに表示させたいということですね。

たぶん、一度表示したら会員証を印刷するか画像ファイルに変換して保持するので、過去の会員の会員証シートを保存する必要は無いのですね。

ところで会員番号は、どのような構成なのでしょう？　例えば計算できる数値でしょうか？、先頭に０やアルファベットを含む数字と文字の組み合わせでしょうか？
できれば、会員リストに決して重なることのない連続した数値でシリアル番号（キー）が新規会員を登録するごとに登録すると簡単に解けるような気がします。　たとえば、MAX関数でキーの最大値を調べることによって会員証フォームに表示すべき会員リストの最後のレコードが特定できます。　関数に順序数をセル・アドレスに変換する関数がありますので。

この回答への補足

頭で悩んでいる事を

なかなか文章や言葉で伝えるのは

苦手なものでして………


過去に一度アクセスを使用し、

練習でテンプレートにあるフォームに

氏名等打ち込むとクリエ？（名前忘れました）に

次々登録されていくのを

やった事がありますが、

エクセルではやった事がありません。


使われる方の状況としては、

当日会員申込が殺到すると想定され、

その場で会員証を発行する必要があり、

且つ会員登録したものを

即データベース化したいので

その仕組みが出来ないものかと

思案しています。


会員番号については

書式設定のユーザー定義で『00000』としてあり、

『1』を入力すると『00001』と表示されます。

会員証印刷は

データベースに名前等記録されていれば

会員証シートの保存は必要ありません。

補足日時：2009/01/01 02:31

No.1 回答者： web2525 回答日時： 2009/01/01 01:42

Sheet2のリストの並びはどんな感じでしょうか?

A列から順に
会員番号・住所・氏名・電話番号
と横並びで下方向に増えていく状態でいいのですか?

Sheet1は会員登録証を印刷するためのものですか?
掲載された形式のまま下方向にデータが増えていくのでしょうか?

会員証の印刷が目的だけなら、Sheet2をデータベースとしてWordで差し込み印刷を行ったほうが早いと思います。

この回答への補足

sheet2の並びは

A1左から右へ横並びに、

会員番号、住所となります。

sheet1は印刷する為だけのものです。


ワードの差し込み印刷は

エクセルの完成された
データベースがあれば
出来るんですが、

使われる方の状況としては、

当日会員申込が殺到すると想定され、

その場で会員証を発行する必要があり、

且つ会員登録したものを

即データベース化したいので

その仕組みが出来ないものかと

思案しています。

補足日時：2009/01/01 02:17