エクセル 数量二倍時の計算結果を表示

質問

エクセル2003を使っています（2007も持ってます）。
取扱商品は、数量により単価が変わります。
商品Ｂは、商品Ａの数量を二倍にした金額で出荷します。
数量により、異なる引数・数式を使って計算した数列（商品Ａ売価）の中から、
二倍の数量のときの計算結果を引用して、別の列（商品Ｂ売価）に表示させたいです。（下の表で行くと商品Ｂの列です）
商品Ｂの列に入力する数式を教えていただけますでしょうか。
（できれば考え方も簡単にお願いします）
複数の関数の組み合わせと思いますが、あまり経験がなく、手に負えません。

数量　　　　商品A　　　　　商品B
10　　　　　\4,500　　　　　\5,500
20　　　　　\5,500　　　　　\7,500
30　　　　　\6,500　　　　　\9,400
40　　　　　\7,500　　　　　\11,200
50　　　　　\8,500　　　　 　…
60　　　　　\9,400
70　　　　　\10,300
80　　　　　\11,200

回答No.6

KURUMITO · ベストアンサー

解答Ｎｏ５です。
商品Ａの数量ごとの売価を表示していますが基本的な式　売価＝基本料金＋(原単価×数量）があるのでしたらそれを使ってＡ列の数量に対する売価をＢ列に表示させるようにして、それらの数値の処理では例えばROUNDDOWN関数などを使って10の位以下の数値は0にする操作をすればよいでしょう。
商品Ｂについても売価を商品Ａについての基本的な式で数量を2倍にした　売価＝基本料金＋(原単価×数量×2）の式を使って計算するようにしてはどうでしょう。
ROUNDDOWN関数の使え方は次のようになりますね。
=ROUNDDOWN(基本料金＋(原単価＊数量＊2）,-2)
この基本的な式がどのようになっているか、ある数量からはこの基本的な式が変わるのかなどが示されませんと詳細に述べることはできませんね。

お礼

ご親切に何度も回答くださり、本当にありがとうございます。
どうもNo.3の方も提案されたように、一度商品Aの詳細な料金表を作り、そこから引用したほうが早いようですね。

教えていただいたエクセルの関数の考え方、これからも参考にさせていただき、生かしていきたいと思います。

回答No.5

KURUMITO

Ｎｏ4です。考えてみるとＢ列にお示しのデータは商品の単価ではなく合計の金額ですね。
だとしたらＣ2列に次の式を入力して下方にオートフィルドラッグして単価を出します。
=IF(A2="","",ROUND(B2/A2,0))
この式は単価を計算して、小数点以下は四捨五入して表示するための式です。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(D2="","",IF(D2*2<=A$2,C$2,IF(D2*2>=MAX(A:A),MIN(C:C),VLOOKUP(D2*2,A:C,3,TRUE))))

補足

KURUMITO様、ご回答ありがとうございます。試してみました。

F2セルの数式で、Dの数値を2倍してやることで、商品Ｂの金額が出せました。
C列を小数点第二桁まで表示させることで、かなり求めるものに近付くことができました。
No.2の方に教えていただいた方法よりも細かく出せるのですが、
商品Ａの各数量での売価＝基本料金＋(原単価×数量）
になり、商品Ａの実際の売単価は、売価の全体額を数量で割ったものになるので、連続的に変動するものなのです。

数量欄が500個以上となると、扱い数量が100個刻みとなるのですが、
例えば商品Ｂで260個の場合（引用は商品Ａの数量表示の無い520個）、300個未満と同じ単価ではなく、
連続的に単価を表示することは難しいでしょうか？
単価の下落ステップが大きいので、250個のときより金額が小さくなってしまうのです。

計算表の数量のステップを細かくすればよいのですが、表をなるべくシンプルにしたいと思っています。
お手数ですがお知恵がありましたらお願いいたします。

回答No.4

KURUMITO

商品Ｂの単価は商品Ａでの商品Ｂの数量の2倍の数量における単価であるとの前提で話を進めます。また、商品Ａでの単価の味方ですが10未満の数量でも4500円、20未満の数量でも4500円とし、数量20以上30未満では5500円、数量30以上40未満では6500円・・・・・のようであるとします。表では数量80以上で11200円であることになります。
以上の考え方を商品Ｂに当てはめて計算をします。
Ａ列の2行目から商品Ａでの数量がＢ列の2行目からは商品Ａの単価があるとします。
Ｄ2セルから下方に商品Ｂの数量があるとしてＥ2セルから下方にその数量における単価を表示させるとしたらＥ2セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。
=IF(D2="","",IF(D2*2<=A$2,B$2,IF(D2*2>=MAX(A:A),MAX(B:B),VLOOKUP(D2*2,A:B,2,TRUE))))
基本的にはVLOOKUP関数を使うのですがその前の条件としてＤ2セルが空であれば空の表示にして、Ｄ2セルの値の2倍の値がＡ2セルの値以下であれば、Ｂ2の値を、また、Ｄ2の値の2倍がＡ列での最大値(最後の行)以上であればＢ列での最大値を、それ以外の場合にはVLOOKUP関数を使ってＤ2の値の2倍の値をＡ列から探し、その行でのＢ列の値を取り出しなさい。もし該当の数値が無い場合にはＤ2の2倍の値よりも小さい位置でのＢセルでの値を取り出しなさい。ということになります。
Ｄ2以降に数量を入力すれば単価がＥ列に表示されますので総合の値段はF2セルに=D2*E2を下方にオートフィルドラッグすることで計算できますね。

回答No.3

root_16

数量が表示された中間の値をとる場合に商品Bの単価を
求める数式が不明
（例えば数量12の時に倍は24ですが、そのとき商品Bの
単価は数量20のところの単価なのか30のところの単価になるのか分からない）
なのでなんとも言えませんが、
商品Aの金額算出式が分かっているのであれば、
いっそのこと数量1～100までとかの1個単位の
単価表を作ったほうが早い気もします。

お礼

ご親切に何度もご回答くださり、本当にありがとうございました。
おっしゃるように、商品Aの数量ごとの詳細な金額表を作り、そこから引用するようにしてみます。
今後もエクセルの関数の考え方、参考にさせていただきます。

回答No.2

root_16

勘違いしてました。
Lookup関数を使って
例えば、LOOKUP(A1*2,A:A,B:B)
とかすればいいですね。
数量の上限があれば、IF関数を使ってください。

お礼

早速のご回答、本当にありがとうございます。
早速試してみたいと思います。
初めて使う関数なので、不明な点が出てきたらまた教えていただけると助かります。

補足

試したところ、見事、商品Ａ列から該当する数値が選んで表示されました。ありがとうございました♪！
が、ウッカリしておりまして、商品Ａ列の数量の中間になる場合がありました。
この場合、商品Ａ列に入れた数式を引用して、Ａ列に無い中間の数値も計算結果を表示できるでしょうか？
関数ほぼ初心者ですが、再度ご教授いただけるとありがたいです。

回答No.1

root_16

良く分かりませんが
商品名、数量、商品単価（AorB）、売価（数量×単価）
の形で表を組みなおした方が簡単じゃないですか？
この形であれば、商品名Aのときと、商品名Bのときで
IF文を書き、それぞれ数量の区分ごとに分岐して
単価を決めればいいと思います。