エクセル関数で解決できるでしょうか

質問

たとえば、
150g20円と50g8円の真空パック食品があり、一回開封すると残りは捨てなければならないとします。

このとき、セルに
440ｇと入力すると、150gパック用セルに3、50gパック用セルに0
340ｇと入力すると、150gパック用セルに2、50gパック用セルに1
と表示させたいのです。

それに加え、上記の商品だけでなく別種の商品も同様に検索したい
のです。自分では商品・梱包量・価格をデータベース形式にして
保存し、そこのデータを引用する形に仕上げたいのですが、
考えがまとまりません。

VBA以外で何か良い考えがあればお授けください。

回答No.5

nobu555 · ベストアンサー

#2です。
申し訳ありません。式が不完全でした。

100gのとき、
150gパックが0、50gパックが2となり合計金額が16円ですが
本式では、1、0となり合計金額が20円となります。

#1の方の
>50g単位で切り上げ
で、もしやと思い検証したところ、式が不完全でした。
失礼しました。

お礼

ありがとうございます。

回答No.4

zap35

＃０３です
＞大袋が２０円、小袋が１５円などと金額が接近している場合は使えない場合があります。
は逆ですね。「大袋が２０円、小袋が５円などと金額が離れる場合は（小袋に多く詰める方が安くなるので）使えない」が正解です

なお＃０３の式では400ｇの時大袋２枚、小袋２枚になります（袋代56円）。これで期待にそいますか？

回答No.3

zap35

以下のような表で
　　　　Ａ列　　Ｂ列　　　Ｃ列
１行目　　　　　150　　　　50
２行目　440　　　　3　　　　0
３行目　340　　　　2　　　　1

Ｂ２の式は
　=IF(ROUNDUP(A2/B$1,0)*B$1-A2<C$1,ROUNDUP(A2/B$1,0),INT(A2/B$1))
Ｃ２の式は
　=IF(B2*B$1-$A2>=0,0,ROUNDUP(($A2-B2*B$1)/C$1,0))
になります。必要な行数分だけ下方向にコピーしてください。

１行目の値は袋の容量です。商品に応じて他テーブルから検索してセットしても良いと思います

ただしこの式は容量に着目して個数を計算しています。袋の値段を最低にするという式ではありません。ですから大袋が２０円、小袋が１５円などと金額が接近している場合は使えない場合があります。ご承知おきください。（その場合はシナリオ機能を利用するのが良いでしょう）

補足

ありがとうございます。
できたら
A1(商品名)　　A2(大袋単位)　　A3(小袋単位)
真空パック　　　　150　　　　　　50

とデータベース登録しておき、　　　
[商品名]・[グラム]　を入力するとデータベース検索をして、
[大袋単位][小袋単位]を表示させ、グラムに応じて個数決定、
というようにはならないでしょうか。
例
B1(商品名)B2(グラム)→B3(大単位)B4(数)B5(小単位)B6(個数)
真空パック　　440　　 →　　150　　　 3　　　　50　　　0

回答No.2

nobu555

440ｇと入力するセルをA1として

150gパック用セルをB2として下記の式を入力
=ROUND(A1/150)

50gパック用セルをB3として下記の式を入力
=IF(A1>150*B2,ROUNDUP(MOD(A1,150)/50,0),0)

で如何でしょうか？

補足

ありがとうございます。
対象商品をあらかじめセットしておく場合にはシンプルでよいですね。

回答No.1

mshr1962

150gと50gの組み合わせでは40gは不可能なので50g単位で切り上げということでしょうか？
A1=440,A2=150,A3=50 として
B2=INT(CEILING($A$1,$A$3)/A2)
B3=INT(MOD(CEILING($A$1,$A$3),A2)/A3)

お礼

ありがとうございました。

補足

ありがとうございます。
150と50で一番安くなる組み合わせパターンを探したいのです。