[[20080902171921]]　『vlookupで近似値の大きい方をだしたい』（かいり）　ページの最後に飛ぶ

『vlookupで近似値の大きい方をだしたい』（かいり）

いつも拝見させていただいてます。
過去ログ等参考にさせていただいたのですが、理解できなかったもので教えてください。

 重量によって変わる『仕入値』と『売値』をvlookupの近似値を使い表示させようとしました。

重量　仕入値　売値

 100     1900    2300
 300     4000    4500
 500     6140    6800
1000    11380   12500

といったように続きます。

 ”検索値 未満で最も大きい値”ではなく・・・次の値にしたいのです。（説明が下手ですいません）
 重量250で検索すると『仕入れ値』は1900になってしまうのですが、4000にしたいのです。
 MATCH、INDEX,ABS関数などいろいろ目にしましたので、使い方を調べてみたりもしたのですが・・・。
 わかる方がおられましたら、教えてください。
 よろしくお願いいたします。

 1行ずらすでは
 重量の数値の多少の変更も必要かも　（こまかい説明がないから？？？）
 リスト表如何でVLOOKUPでできますよ。

 index match関数で

 近似値検索の使用例を載せています。
http://miyahorinn.fc2web.com/faq/faq002.html#4b
　
ご提示のリストで250を検索すると、「250は300未満だから、その次の最大値100」の
行を検索してしまいます。
　
＞ index match関数で
リストがA1:C5の範囲、検索値E2に入力と仮定して次のような数式例。
=INDEX($B$2:$B$5,MATCH(E2,$A$1:$A$4,1))
　
あとは、「重量」のフィールドを書式設定でごまかして表示するとか。
（100の部分は0を入力しておいて表示形式で　"100";;;　などとしておく）
ただ、表示と入力値を違えておくのは混乱の元になるかもしれません。
　
(みやほりん)(-_∂)b

 重量の列を降順にしてINDEX関数と、MATCH関数の照合の型を-1にする方法や
 =INDEX(B:B,MATCH(検査値,A:A,-1))

 そのままの表であれば
 INDEX関数とCOUNTIF関数の利用など
 =INDEX(B:B,COUNTIF(A:A,"<"&検査値)+1)
 または
 =INDEX(B:B,COUNTIF(A:A,"<="&検査値)+1)

 因みに、100未満はどのような結果になればいいのかな?
 また、100,300,500などはどちらにはいるのかな?

By

 VLOOKUP関数
 表　A列	B列	入力数値	       結果
 　　　0　 5	0～４	          5
 　　　5	10	５～９	         10
 　　 10	15	１０～１４	15

 　　　0	 5	０～５	          5
 　　　6	10	６～１０   	10
 　　 11	15	１１～１５	15

 　　　0	 0	０～５	          0
 　　　6	 5	６～１０    	 5
 　　 11	10	１１～１５	10
 3表比較で
 表作成の仕方で結果が変わる　と言うことで

 ”検索値 未満で最も大きい値”になるようにA列の数値の振り分けで
　　で様は　○～△なら　□　になる　
（検索値 未満で最も大きい値）という
　言葉にまどはされずに
　条件が細かく説明されているなら　表を提示できますが！！
 （だんｄｙ）

おはようございます。
みなさん、コメントありがとうございます。

やはりindex match関数というのがキーとなっているのですね。

みやほりんさん、ありがとうございます。

 時間が取れ次第、教えていただいた例を試してみます。

BYさん、ありがとうございます。

 100未満はないものと設定しております。
 また、100　の場合は仕入値= 1900 売値= 2300
 300　の場合は仕入値= 4000 売値= 4500　とその行の値を使用したいと思っております。
 ただ、100を1だけでも超えた場合は300の行の値を使用できたらなと思っております。

だんdyさん、ありがとうございます。

 一行増やすということですね。（理解力が乏しいもので、違っていたらすいません）
 このような場合はVLOOKUP関数のみでいいのでしょうか？

時間がかかりそうですが、いろいろ試してみます。
よろしくお願いいたします。
（かいり）

 重量　 仕入値　 売値

 100     1900    2300
 300     4000    4500
 500     6140    6800　　　　　（１００～２９９の値で検索　で1900）
1000    11380   12500　　　　重量250で検索すると『仕入れ値』は1900

 重量　 仕入値　 売値

 ０    1900    2300
100　　4000    4500
300 　 6140    6800　　　　　　（１００～２９９の値で検索　で4000）
 500  11380   12500          重量250で検索すると『仕入れ値』は4000に
１行ずらしました　　　　　　　確認して納得を
だんdy

 >ただ、100を1だけでも超えた場合は300の行の値を使用できたらなと思っております
 =INDEX(B:B,COUNTIF(A:A,"<"&検査値)+1)
 でそのようになりませんか?
 (範囲は実際のものにあわせてください)

By

 ０    1900    2300
101　　4000    4500
301 　 6140    6800　　　　　　（１０1～３００の値で検索　で4000）
501  11380   12500

 てな事
だんdy

Byさん、ご回答ありがとうございます。

すいません。もう少し教えていただけますか？

実際の表は列等配置が少し異なるもので、理解しやすように例であげた表を作り試してみました。

＞=INDEX(B:B,COUNTIF(A:A,"<"&検査値)+1)
の検索値は実際にはセル番地でいいのでしょうか？

 Byさんにご提示いただいたものをコピペし検索値のみかえてみましたが、重量250で試すと仕入値は1900になってしまいます。
 検索値セルをE3とし
 =INDEX(B:B,COUNTIF(A:A,"<="&E3)+1)
 です。

理解しようと自分で入力してみたのですが・・・

 +1と有りますが、これは"INDEXの列番号"のところへ入力でよいのでしょうか？
 "INDEXの列番号"のところへ"+1"と入力しても"1"に勝手に訂正されてしまいます。

よろしくお願いいたします。

 >(範囲は実際のものにあわせてください) 
 =INDEX(B2:B10,COUNTIF(A2:A10,"<="&E3)+1)

 列指定の場合どの行からデータが入力されているかによって「+1」
 の値を変更(調整)します
 =INDEX(B:B,COUNTIF(A:A,"<"&検査値)+2)

By

できましたぁ～。

みやほりんさん、Byさん、だんdyさん。本当にありがとうございました。
どのやり方でも希望の結果がでました。
みなさん、”すごすぎ”です。

みやほりんさん。

 INDEX関数とMATCH関数はイロイロと使えると聞いてはいたのですが、難しくて避けていました。
 これからがんばってみます。

Byさん。

 ＞列指定の場合どの行からデータが入力されているかによって「+1」の値を変更(調整)します
 解りやすい説明まで・・・ありがとうございます。
 まだ若干、関数の組み合わせ方が理解できてない部分もあるのですが・・・。
 がんばります。

だんdyさん。

 発想がすごいです。
 とても頭が柔らかいのですね。その柔軟性に感謝です。

PC教室も卒業し、そこそこの関数なら使える気になっていましたが・・・。

 PC教室と実務では、ぜんぜん違いますね。
 これからも教えていただきに来てしまうとは思うのですが、よろしくお願いいたします。
 ありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20080902171921]] 『vlookupで近似値の大きい方をだしたい』（かいり） ページの最後に飛ぶ

[[20080902171921]]　『vlookupで近似値の大きい方をだしたい』（かいり）　ページの最後に飛ぶ