VLOOKUPの応用方法

質問

既出の質問かもしれませんが、お願いします。
エクセル2003使用です。

A列の数値×係数D列といった簡単な式なのですが
A列の数値によって選択する係数D列が変わってきます。D列の係数は7つ以上あり、数値はランダムです。

詳細は

A列に任意の数値（2桁～4桁の数値）が入っています。
A1：180
A2：1250
A3：75
など

行番号 C列 D列
1 100 2.2
2 200 3.5
3 300 4.8

A列の数値がC列の数値以下であるときは、
該当するC列と同行のD列の数値と掛けます。

例えば、A1であれば200以下の数値なのでC2見て、A1*D2としたい。

上記に書いたとおり、係数(D列)は7つ以上ありIF関数は使えません。

VLOOKUP関数に条件を加えて、うまく出来ないかと思案しています。

上記の説明でわかりにくいかもしれませんが、ぜひ
お知恵を貸してください。

回答No.4

mshr1962 · ベストアンサー

C列の数値が等倍ならCEILING関数で切り上げ処理を行い
=A1*VLOOKUP(CEILING(A1,100),$C$1:$D$3,2,FALSE)
とする。
違う場合は1列追加して
行番号 C列 D列 E列
1 0 100 2.2
2 100 200 3.5
3 200 300 4.8
にして
=A1*VLOOKUP(A1,$C$1:$D$3,3,TRUE)
これなら0以上100未満は2.2,100以上200未満は3.5となります。

お礼

ありがとうございます。
ごめんなさい。下記に補記しましたが、C列の値は、
等倍ではないのです。
100
200
400
600
800
1000
1200
1600
2000
10000
というような感じになります。
先に、詳しくご説明すればよかったですね。
上記の方法も、後学のためになりました。

補足

わかりました！！
ご説明頂いている意味がわかりました！！
ということは、101以上200以下にしたい時は
C D E
100 201 3.5 とすればいいんでしょうか？
VLOOKUPの性質がよくわかってないのですが、
近似値ではなく、●以上●未満の数値を検索してくれるということですか？

回答No.7

sige1701

=検査値A1に対し検査する範囲をC列、抽出範囲をD列とした場合、下記式は同じことをします。

=VLOOKUP(A1,C:D,2)
=INDEX(D:D,MATCH(A1,C:C))

VLOOKUP関数は検査する範囲が範囲の一番左の列にする
必要がありますが、
INDEX、MATCH関数を使用することで、（検査する範
囲、抽出範囲を指定していますので）逆にすることが
出来ます。

対応表を、降順にすると
=INDEX(D:D,MATCH(A1,C:C,-1))

COUNTIF(範囲,検索条件)ヘルプより抜粋
検索条件 計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。

お礼

ご回答ありがとうございました。
まだまだ勉強不足を痛感致しました。
細かい説明、大変為になりました。
ありがとうございます。

回答No.6

Yosha

>VLOOKUP関数に条件を加えて、うまく出来ないかと思案しています。
VLOOKUP関数を使用した一例です。

あなたのケースでVLOOKUP関数を使用する場合の条件、注意点は、
1. 取り出したいデータが含まれている範囲の第１列目の値を必ず昇順に並べること。
2. 検索値を超えない最大値が選ばれます。
の２点です。

2.の条件は、例えばＡ列の値が１８０である場合、１８０より小さい最大値の１００が対象になりますので倍率は２.２になり、予定している２００-３.５の値を引き出すことが出来ませんので、例にある表はそのままでは使えません。
したがって、>A列の数値がC列の数値以下であるときは、該当するＣ列と同行のＤ列の数値と掛けます ・・・ でなくて「Ｄ列の次の段の数値未満または以下のとき、該当するＣ列と同行のＤ列の数値と掛けます」と考え方を変えて表を作成します。例えば以下のように。

行番号 C列 D列
1 0 2.2
2 100 3.5
3 200 4.8
4 300 6.1 など。

もう１つ。
>Ａ列の数値がＣ列の数値以下であるとき・・・
以下か未満かをきっちり決めてかかりましょう。

Ｂ列に計算結果を入れることにすると、Ｂ１セルに次の式を書きます。

=A1*VLOOKUP(A1-1,$C$1:$D$4,2)

Ｂ１セルを選択して右下の小さな■をダブルクリックすると、式が下の全セルにコピーされます。

上の式の括弧の中の(-1)は、Ａ列の数値がＣ列の数値以下のときです。未満のときは不要です。
また、参照される表の範囲は、行、列共に$C$1:$D$4 と絶対参照にします。

余談ですが、この表を使って得られた答えは、１００,２００,３００の値のところで大きな不連続点が発生しますね。例えば、

Ａ列の値 結果
100 220
101 353.5

これを解消するには、表と計算式を作り直すことになります。ちょっと大変かも。

お礼

ありがとうございます。
参考になりました。

回答No.5

sige1701

=INDEX(D:D,COUNTIF(C:C,"<"&A1)+1)

お礼

数式の意味がわかりませんが、皆さんのご意見で作り直したVLOOKUPの結果と同じものが得られました！！
ありがとうございます。
INDEXについてと"<"の意味を勉強したいと思います。
よろしければ教えてください。

回答No.3

Poer

=A1*VLOOKUP(ROUNDDOWN(A1+99,-2),C:D,2,0)
でしょうか？

お礼

ご回答ありがとうございます。
参考にさせて頂きます。

回答No.2

wafumofu

CとDの数値を変更して下記式を入力してはいかがでしょう？
※VLOOKUP関数は180の場合C列の100の行を参照します。

行 _C _D
1 ___0 2.2
2 100 3.5
3 200 4.8
4 300 xxx
・
・
・
n n00 xxx

=(VLOOKUP(A1,C1:Dn,2))*A1

_は空欄の代用

補足

VLOOKUPは検索値に一番近い値を検索するということでしょうか？その辺がよく理解できていません。
ちなみに、C列とD列を上記のように書き直した場合、
最終行のD列の値が空欄になりませんでしょうか？

回答No.1

noname#204879

》 A1であれば200以下の数値なのでC2見て、A1*D2と…

分かンな～い！200以下なら C1 見て、A1*D1 なのでは？
それから VLOOKUP関数を使う場合は、A1 は 200「未満」の数値なので、という表現が正解になります。

補足

A1は180です。200以下となるので、参照するC列はC2となります。よって、A1*D2となります。
例えば、A列の値が285という数値であれば、C3を参照して、285*4.8(D3)としたいのです。
A列の数値は4桁まで任意で200件ほどあります。
また、C列の範囲の指定も100ずつとは限っていません。

A列がC列の数値以下の場合、同行のD列と掛け算をする
数式が知りたいのです。お願いします。