エクセル ＶＬＯＯＰ関数

質問

エクセル初心者です。

エクセル2003を練習中なのですが
今、下画像の上段のようなデータのシートを作成しました。

シートは左の日付を3行セル結合しています。
このシートのデータを、画像下段の表のように
関数で表引きしたいのです。

下段の表は、左の日付を2行セル結合しています。

関数（ＶＬＯＯＰＵＰ関数とＩＮＤＥＸ関数）の使い方が、今一つ分かりませんでした。

どんな数式を入力していけばいいのでしょうか。

お願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.1

keithin · ベストアンサー

○方法その１
INDEX関数とMATCH関数を使います。
このとき常にアタマに入れておくのは，「結合セルはその左上のセルにデータを持っている」ということです。
結果の式を教わってハッピーじゃなく，途中の経緯をよく理解して数式を組みましょう。

1/1の大阪の販売金額を参照してみます。
まずシート１の1/1は「A5セルにある」ことを確認します
同様に，シート２の1/1はA3セルにある事を確認します。
すると
=MATCH(A5,Sheet2!A:A,0)　とすることで，
シート１A5の1/1は，シート２の３行目にあるよと判りましたね。

次にシート２の1/1の販売金額は「４行目」にあります。常に結合セルの左上のセルの，一つ下の行にあります
従って答えは
=INDEX(Sheet2!D:D,MATCH(A5,Sheet2!$A:$A,0)+1)
のように得られます。

○方法その２（関数の勉強ではなく，応用テクニック）
VLOOKUP関数は，必ず「同じ行のデータ」しか参照できません。
先にお話したようにシート２の販売日の実体データは結合セルの上のセルにありますから，VLOOUP関数は同じ行の返品金額だけしか参照してくる事が出来ません。
大阪の返品金額：
=VLOOKUP(A5,Sheet2!A:G,4,FALSE)

繰り返しになりますがVLOOKUPで販売金額を取ってこれないのは，販売金額の行は結合セルの下の行で，そこには「何も入っていない」からです。
逆に言うと，そこにも実は販売日のデータ（結合セルの上側セルの値）が「入っていれば」，問題なくVLOOKUPが使えます。

手順：
準備として，シート２のセル範囲A3:A8をコピー，H3セルを選んで型式を選んで貼り付けの「リンク貼り付け」をしておきます
2000/1/1
1900/1/0
2000/1/2
1900/1/0
2000/1/3
1900/1/0
と結果が並びます。ゼロのセルは，日付の書式では1900/1/0と表示されます。

H1セルを空っぽにしておきます
H2セルに　=H1+0.5　と式を入れます
H1:H2をコピーします
A3:A8を選び，型式を選んで貼り付けの数式にマーク，更に空白セルを無視するにチェックを入れて，ＯＫします。
先に準備したリンクの式が
2000/1/1
2000/1/1
2000/1/2
2000/1/2
2000/1/3
2000/1/3
と変わればとりあえず成功です。

更に，リンクの式のセルの書式設定を，日付と時刻に変更します。
2000/1/1 0:00
2000/1/1 12:00
2000/1/2 0:00
2000/1/2 12:00
2000/1/3 0:00
2000/1/3 12:00
のように計算できていれば，大成功です。

VLOOKUP関数で1/1の販売金額を参照してくるには，日付＋0.5を今記入したので
大阪の販売金額
=VLOOKUP(A5+0.5,Sheet2!A:G,4,FALSE)
のようになります。

回答No.2

imogasi

何処を所与のものとしてどうデータを引くのか質問に書いておくこと。
表引きと言うより、表の構成を少し変えるだけで、本来のVLOOKUP関数の使い方でない。
MATCH関数で日付を手がかりに、該当データを探すというのが、質問のニーズのようだ。
Sheet2の日付は入力済みとする。毎日ならオートフィルで入る。セル結合されていてもうまく行くようだ。
そしてセル結合されたセルの値は左上隅せる（この場合は１番上の行にあるせると理解すること。
Sheet2のC2セルの式はA2の日付によってSheet1のA列の同じ日を探してでーたを取る。
例データ　
Sheet1　A4以下
販売日付 東京 大阪
2010/1/1 10 20

10000 20000
2010/1/2 60 70

60000 70000
2010/1/3 110 120

110000 120000
・・・
結果
Sheet2　A1から
販売日付 東京 大阪
2010/1/1 返品金額 10 20
販売金額 10000 20000
2010/1/2 返品金額 60 70
販売金額 60000 70000
2010/1/3 返品金額 110 120
販売金額 110000 120000
・・・
C2の式　=INDEX(Sheet1!$A$1:$L$100,MATCH($A2,Sheet1!$A$1:$A$100,0),COLUMN()-1)
C3の式　=INDEX(Sheet1!$A$1:$L$100,MATCH($A2,Sheet1!$A$1:$A$100,0)+2,COLUMN()-1)
C2:C3のセルの式を＋ハンドルを出して下に引っ張る。
C列の式を右方向に＋ハンドルで引っ張る。
Sheet1!$A$1:$L$100のL列と１００行は適当にやっているから、地祭礼にあわせて修正して。
ーー
エクセルの関数の初心者なら
セル結合はなるべく避ける（エクセルに慣れるまでは）。
本件も初心者にしては色々な経験が要り、難しい部類なので、丸投げで、コピーで出来ましたとなって、勉強にならないかと思う。
ーー
今後質問するなら、例データは、画像貼り付けでなく、質問文の中にテキストで作ってほしい。（私の回答例のように）
回答者が例データを作りテストするのに、打ち直しになって、随分時間がかかるので、お願い。