エクセル関数２

質問

シート(1)
A B C D
作業列　　　　コード　　　　顧客名　　　　　金額
3 　　　　　　 300001　　　　　A 10,000
3 　　　　　　 300002 　　B 15,000
3 　　　　　　 300003 　　C 20,000
3 　　　　　　 300004 　　D 25,000
3 　　　　　　 300005 　　E 30,000
4 　　　　　　 400001 　　F 35,000
4 　　　　　　 400002 　　G 40,000
　4 400003 H 45,000
4 400004 I 50,000
4 400005 J 55,000

シート(2)
A B C D E F
「1」 「2」 「3」 「4」 「5」 「6」
10,000 35,000
15,000 40,000
20,000 45,000
25,000 50,000
30,000 55,000

※　シート(1)にコード、顧客名、金額を入力すると、シート(2)にあるように該当するコードの先頭行の列に表示するようにしたいと思っています。

シート１で作業列を作りその作業列を基にシート２でVLOOKUP関数を使い‥シート２の10,000のセルの場合、
VLOOKUP(C$2,sheet1!$A$3,$D$12,4,false)として、下にコピーすると同じ金額になってしまいます。

セルが空白にならず、シート１の金額が上から順番にシート２に表記されるようにするにはどうしたらよいでしょうか。マクロはできませんので、関数で詳しく教えて下さい。宜しくお願いします。

回答No.3

imogasi

既回答者は早く質問の意味が判ったようだが、私は苦労した。列などはOKWAVE画面で崩れていることもわかり難い。質問の書き方に実例＋説明文の説明文が無いためわかり難いと思う。
私は推定も入ってやや一般化してみた。
ーーー
私の表現なら
「コード」列の３０万、４０万などの先頭桁が変わるごとに、別場所に、３０万、４０万などの列を分けて、縦方向にに３０万台、４０万台だけの明細行の金額を並べたい。
ーー
例データ
A列　　　B列　　　C列　　　D列
番号 金額 300
300001 A 10,000 300
300002 B 15,000 301
300003 C 20,000 302
400001 F 35,000 400
400002 G 40,000 401
400003 H 45,000 402
400004 I 50,000 403
400005 J 55,000 404
500001 ー 1,000 500
510003 ー 3,000 501
510005 ー 5,000 502
530006 ー 15,000 503
550007 ー 16,000 504
570008 ー 17,000 505
590009 ー 18,000 506
590010 ー 19,000 507
590011 ー 20,000 508
元の作業列(A列）は使わず、上記では削除、D列に作業用データを使った。
B列は本質問に関係ないと考慮してない。
D1の３００はスタートの値として必要。
上記は例えば３０万台が２桁以上の数あってもOKのように考えた。
D列D2セルの式は　=IF(LEFT(A2,1)=LEFT(A1,1),D1+1,LEFT(A2,1)*100)
下方向に式を複写。
結果上記の通り。
便宜上、第２１行目からA21：C32
3 5 9
10000 35000 1000
15000 40000 3000
20000 45000 5000
50000 15000
55000 16000
17000
18000
19000
20000
ーーー
A21行に =SUMPRODUCT(($A2:$A18>(COLUMN()+2)*100000)*($A2:$A18<(COLUMN()+3)*100000)*1)
と入れてC21まで式を複写。結果は上記。
A22に式　=IF(ROW()-21>A$21,"",INDEX($A$2:$C$19,MATCH((COLUMN()+2)*100+ROW()-22,$D$2:$D$19),3))
と入れて、A32まで式を複写、A22:A32の式をC22:C32に複写。
結果
上記の通り。
ーー
もし上記で質問の主旨が合っているなら、この質問は関数の問題としてパズル的に難しい部類でしょう。
VBAなどを使えばこんなにごちゃごちゃすることは無い。関数は並べ替え、抜き出しなどは不得手なテーマです。作業列を使うことを許されたが、聡でないとまたぐっと難しくなろう。

回答No.2

hallo-2007

やりたいことは、コードの左一桁が横列、右一桁が縦行に分けたいということでしょうか？
Vlookup関数は、条件があった最初のデータしかでません。
シート１が
作業列　コード　顧客名　　金額
31　　　300001　　A　　　　10,000
32　　　300002　　B　　　　15,000
33　　　300003　　C　　　　20,000
34　　　300004　　D 　　　　25,000
35　　　300005　　E　　　　30,000
41　　　400001　　F　　　　35,000
42　　　400002　　G　　　　40,000
43　　　400003　　H　　　　45,000
44　　　400004　　I　　　　50,000
45　　　400005　　J　　　　55,000
シート２が
　　1　　　　2　　　3　　　4　　　5
1　#N/A　#N/A　10000　35000　#N/A
2　#N/A　#N/A　15000　40000　#N/A
3　#N/A　#N/A　20000　45000 　#N/A
4　#N/A　#N/A　25000　50000 　#N/A
5　#N/A　#N/A　30000　55000 　#N/A
でよければ
シート１のA列は　=LEFT(B2,1)&RIGHT(B2,1)　下フィル
シート2に　A列、1行目は文字列にして　１，２，３・・・
B2には　　　　　=VLOOKUP(B$1&$A2,Sheet1!$A$1:$D$11,4,FALSE)
右フィル、下フィルで出来ます。
VLOOKUP(B$1&$A2・・の$マークに注意してください。フィルした後の式を確認するとわかりやすいと思います。

お礼

ありがとうございました。
A列の文字列を０００～９９９にして重複がないよう検証しています。
また、中には一部不規則なものがありますので、それは手作業でもよいのかなと思っています。本当にありがとうございました。

回答No.1

keirika

VLOOKUP関数の引数としてC$2を指定しているようですが
行を固定した場合、下方向へコピーしても金額は同じになると思います。

一度＄を取って、お試しください。

勘違いでしたらご容赦ください。