Excel: 近い値のセルの隣接セル同士の参照・抽出について

質問

Excel: 近い値のセルの隣接セル同士の参照・抽出について
よろしくお願い致します。
題目ではうまく説明できず申し訳ありません。今、
X1 　　Y1　　 X2 　　 Y2
1 　　 35 　　 1.1　　 　50
2 　　 40 　　 2.9　　 81
3　　 20　　 　 5.3　　 2
4　　 10 　　 5.6 　　 15
5 　　 22 　　 10.0 　　25
6 　　11 　　 18.4 　　 10
(中略)
100 　　32 　　 250.2 　　14
といった二つのデータ(X1,Y1)と(X2,Y2)があります(位置合わせがうまくできずすみません)。X1は整数、他は実数です。X2の値と値の間隔は不定です。
X2の値と最も近い値のX1の隣のY1を参照し、Y1×Y2の値をY3として出力したいと思っています。
具体的には、
X1 　　 Y1 　　 X2 　　 Y2 　　 Y3
1 　　 35 　　 1.1 　　 50→ 　　(1.1≒1)35×50=1750
2 　　 40 　　 2.9 　　 81→ 　　 (2.9≒3)20×81=1620
3 　　 20 　　 5.3 　　 2→ 　　 (5.3≒5)22× 2=44
4 　　 10 　　 5.6 　　 15→ 　　(5.6≒6)11×15=165
5 　　 22 　　 10.0 　　 25 　　…
6 　　 11 　　 18.4 　　 10
(以下略)
と計算し、
X2 　　 Y3
1.1 　　 1750
2.9 　　 1620
5.3 　　 44
5.6 　　 165
(以下略)
といったデータにしたいのです。
Excelでこのような操作を行うことができるでしょうか。御教示ください。
できればマクロなどを使わない方法でお願いします。
よろしくお願い致します。

回答No.1

soixante · ベストアンサー

2行目からデータとして、Ｙ３の列の2行目に、

=VLOOKUP(ROUND(C2,0),$A$2:$B$100,2,0)*D2

と入れて、下まで引っ張れば終わりです。

ROUND関数で、Ｘ２　の値を四捨五入し、
それを検索値として、Ｙ１を求め、
Ｙ２を乗じています。

お礼

御回答頂きありがとうございます。疑問の内容を適切な検索語にできず、解答にたどりつけずに困っておりました。HPで詳細を調べましたが、vlookup関数はうってつけですね。round関数も便利そうです。

回答No.5

tom04

こんばんは！
アドインソフトになってしまいますが・・・
↓のURLのようなものもあるみたいです。

http://kiyopon.sakura.ne.jp/soft/minx.htm

m(__)m

お礼

御解答頂きありがとうございますm(__)m　無料ソフトウェアを使うやり方もあるのですね。難しいですが、勉強になります。エクセルは奥が深い…。

回答No.4

MackyNo1

X1の数値の種類や並び順に関係なく差の絶対値の最小値を検索する汎用性のある数式にするなら以下の配列数式にしてください。

=INDEX(B:B,MAX((MIN(ABS(C2-$A$2:$A$100))=ABS(C2-$A$2:$A$100))*ROW($A$2:$A$100)))*D2

配列数式ですので、入力後Ctrl＋Shift+Enterで確定してください。

お礼

いくつも御教示頂きありがとうございます。関数を調べ切れていないのですが、汎用性のあるやり方のようですね。勉強になります。

回答No.3

MackyNo1

X2のデータが必ずX1のデータに入っているなら（四捨五入した値がX1に必ず存在するなら）、Y3の値は以下の式で表示できます。

=INDEX(B:B,MAX(INDEX((ROUND(C2,0)=$A$2:$A$100)*ROW($A$2:$A$100),)))*D2

お礼

御解答頂きありがとうございます。勉強させて頂きます。

回答No.2

imogasi

基本的にはＶＬＯＯＫＵＰのＦＡＬＳＥ型でＸ１の数に当たる数で表のＢ列を引き、Ｂ列の値でＤ列の値をかければよい。検索が済めば、ここからは簡単。
Ａ列（Ｘ１）は正整数と断言できますか？はっきり質問に書くべきでは。
ーー
ＶＬＯＯＫＵＰ関数にＴＲＵＥがたという質問に近い関数の種類があるが、超えない最大値、とかで少し違うので使えない。四捨五入で良いのですか？
ーー
値　　１とに差　２との差
1.1 0.1 0.9
1.2 0.2 0.8
1.3 0.3 0.7
1.4 0.4 0.6
1.5 0.5 0.5
1.6 0.6 0.4
1.7 0.7 0.3
1.8 0.8 0.2
1.9 0.9 0.1
１．５（両者の差０．５で同じ）の場合どうすべきですか？小数点以下２桁以下の端数が付いて
実際ではこういうことが起こらず、差さを採って採用を決めるべきですか。

補足

御回答頂きありがとうございます。説明が足らず申し訳ありません。
X1は今回は自然数ですが、今後負の数で計算する場合もあると思い整数とさせて頂きました。
四捨五入に関しては仰る通り端数の桁が多く、1.5ちょうどといったx1はほとんどないので、問題ありません。