エクセルVBAを使ってデータの抽出をしたいのですが

質問

タンク容量のデータベースがあります。（以下のような）
　　　Ａ　　　Ｂ　　　Ｃ......←タンクＮｏ
０　　100　 200 　500　.....
２　　90　　180　　460 　......
４　　80　　160 　　420　 .....
６　　 70　 140 　　400　.....　
・
・
・
↑タンク上部からの空寸

このようなデータからたとえば、タンクＢの160は空寸が4ですが、この空寸4を抽出するにはどうすればいいのか教えてください。
ちなみに、データはシート２にあり、シート１のＡ１セルにタンクＮ０、Ｂ１セルに容量を入力し、コマンドボタンを押すとＣ１セルに対応する空寸がでるようにしたいのですが。。
また、容量はぴったり一致するときもありますが、無いときは最も近い値の空寸を持ってきたいのです。
どうか、よろしくお願いします。

回答No.3

maruru01 · ベストアンサー

再びmaruru01です。

まず、Sheet2の表の位置を確認したいのですが、
A1は空白で、A2～縦方向にA1225までに「空寸」、
逆に、B1～横方向にAP1までに「タンクNo」となっていますか？
つまり、容量はB2～AP1225までに入力されていることになります。
それなら、No.1の補足欄の数式でいいはずですが。
あと、MATCH関数では、タンクNoの位置を検索しています。
したがって、データに関係なく、第2引数は「Sheet2!$1:$1」のままにしておいて下さい。

お礼

参考にさせて頂いて、何とか解決できました。
ありがとうございました。

結局、ＶＢＡでやってみました。

回答No.2

imogasi

（データ）Ｓｈｅｅｔ２のＡ１：Ｄ５
Ａ　 Ｂ　 Ｃ.
0 100 200 500
2 90 180 460
4 80 160 420
6 70 140 400
（指定）
Ｂ９セル
（ＶＢＡ）
Sub test01()
Worksheets("sheet2").Select
Select Case Cells(9, 2) 'B9セル
Case Is < 100
c = 2
Case Is < 200
c = 3
Case Is < 500
c = 4
End Select
MsgBox c
For i = 1 To 10
If Cells(i, c) < Cells(9, 2) Then
If Cells(9, 2) - Cells(i, c) > Cells(i - 1, c) - Cells(9, 2) Then
MsgBox Cells(i - 1, c)
Else
MsgBox Cells(i, c)
End If
Exit Sub
Else
End If
Next i
End Sub
（テスト）
９４の時２
１０１の時６
１７１の時２
４９０のとき０
（お詫び）
この解答はＢ列の最低が１００を越える、またＣ列の最低
（最下行）が２００を越えることを前提にしています。
また急いだため、指定を同じシートのＢ９に設定して手抜きをしています。ヒントに使ってください。
またテストが十分でありませんことをお詫びします。
上記仮定が不可の場合は、おっしゃって頂ければ考えなおします。

補足

お返事ありがとうございます。maruru01さんが教えてくれるワークシート関数でやってみようかと思っていますが、VBAも勉強したいと思っているものですから、教えてください。

Worksheets("sheet2").Select
Select Case Cells(9, 2) 'B9セル
Case Is < 100
c = 2
Case Is < 200
c = 3
Case Is < 500
c = 4

ですが、タンク容量のデータシートにはタンクの番号が200本以上あります。1から200番まで整理されて並んでいるわけでなく番号が不揃いで1052や12、502、500、821といったようにランダムにまた抜けた番号もあります。また今回はシート2のタンクのデータで対処できるのですが今後シート3やシート4にもタンク容量のデータがありそちらも利用できたらとも思っています。
説明不足で申し訳ありませんがよろしくお願いいたします。

回答No.1

maruru01

こんにちは。maruru01です。

VBAでなくても、ワークシート関数でも出来ます。
シート1のC1に、

=IF(ISERROR(MATCH($A$1,Sheet2!$1:$1,0)),"",INDEX(Sheet2!$A:$A,MIN(IF(ABS(OFFSET(Sheet2!$A$2:$A$5,0,MATCH($A$1,Sheet2!$1:$1,0)-1)-$B$1)=MIN(ABS(OFFSET(Sheet2!$A$2:$A$5,0,MATCH($A$1,Sheet2!$1:$1,0)-1)-$B$1)),ROW(Sheet2!$A$2:$A$5)))))

と入力して、[Ctrl]＋[Shift]＋[Enter]で確定します。
（数式の両端に「{}」が付いて、配列数式になります。）
なお、Sheet2のデータ範囲は2～5行なので、適宜変更して下さい。
また、シート1のB1の値が2つのデータの中間の場合は、上の行が優先されます。
例えば、「Ｂ」で「170」なら、「３」ではなく「２」になります。

補足

返事が遅くなってしまい申し訳ありません。
maruru01さん、いつも教えていただいてありがとうございます。

MATCH関数がよくわからないのですが、MATCH($A$1,Sheet2!$1:$1,0)の$1:$1はどういう意味なのでしょうか？
タンクのデータはA1からAP1225に入っているのですが$1:$1の部分をA1:AP1225に変えればいいのですか？

また、”Sheet2のデータ範囲は2～5行なので、適宜変更して下さい。”ということだったので最初
｛=IF(ISERROR(MATCH($A$1,Sheet2!$1:$1,0)),"",INDEX(Sheet2!$A:$A,MIN(IF(ABS(OFFSET(Sheet2!$A$2:$A$1225,0,MATCH($A$1,Sheet2!$1:$1,0)-1)-$B$1)=MIN(ABS(OFFSET(Sheet2!$A$2:$A$1225,0,MATCH($A$1,Sheet2!$1:$1,0)-1)-$B$1)),ROW(Sheet2!$A$2:$A$1225))))) ｝としましたがだめでしたどこがいけないのでしょうか？

一つ一つ解釈しながら勉強させて頂いてます。すいませんが、よろしくおねがいいたします。