エクセルでしたいこと 複数条件

質問

下記の条件で答えを導き出したいのですが計算式が思いつきません。
どなたかよろしくお願いします。

例えば100個のみかんがあります。これを基本54個入りの段ボールに詰めていきます。さて段ボールは何箱いるでしょう？というのを導き出したいのです。

これだけならすごく簡単な作業です。

ここから条件がついてきます。
最後の1箱の段ボールが27個以下なら最後から2番目の段ボールに一緒に詰めてしまうという条件がつきます。

みかんが100個の場合と120個の場合を計算式を考えてみたいと思います。
100個の場合
=if((100-rounddown(100/54,0)*54)>27,roundup(100/54,0),rounddown(100/54,0))
と求めると　２　が導き出されます。

120個の場合も同様に計算すると
=if((120-rounddown(120/54,0)*54)>27,roundup(120/54,0),rounddown(120/54,0))　
と求めると　２　が導き出されます。

ここまではいいのですが
問題はみかんの数が少ない場合です。
たとえばみかんが10個の場合
同様の計算式で計算するとrounddownされて段ボールの数が0になってしまいます。実際は最低1つは段ボールが1箱必要になります。これを解消したいのですがなかなかいい計算方法が思い当たりません。
皆様知恵を貸していただけないでしょうか。

長くなってしまいましたがよろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

#1です。
IF(A1<=0,0,MAX(箱数,1)) :IF文でみかんが1個以上で箱数が0の時に1個にする
ROUNDUP(A1/B1,0):箱数
MOD(A1-1,B1):余りみかん数[-1しているのは余り0を除外するため]
INT(B1/2):余りを一箱に詰める制限数
(MOD(A1-1,B1)<INT(B1/2)):減らす箱数、みかんが制限数以内なら1、制限数以上なら0
箱数=箱数-減らす箱数となり、これを最初のIF文に代入

MOD関数は商の余りを求める関数です。
120-rounddown(120/54,0)*54)はMOD(120/54)と同じ答えになります。

回答No.4

noname#79209

こんな式でもいけるかと．．．
A1:数量、B1:1箱あたりの入り数、C1:無理に詰め込む余り数の上限
=CEILING(A1/B1,1)-AND(MOD(A1,B1)<=C1,MOD(A1,B1)>0,A1>B1)*1

回答No.2

grumpy_the_dwarf

27個も余分に詰め込める箱ってのも凄いですが、まず、最初の54個
はどうしても1箱必要なので、max(0,みかん-54)について考えること
にします。最初が100個なら46個ですね。これを詰めるには

=int(残り/54)+if(mod(残り,54)>27,1,0)
箱必要なので、最初の1個を足して整理すると、

=int(max(0,みかん-54)/54)+if(mod(max(0,みかん-54),54)>27,1,0)+1
箱になります。みかんが0個でも箱が1つ必要なところが間抜けです
が、そこは空白にしてif(A1="","",…みたいに逃げて下さい。

回答No.1

mshr1962

A1にみかんの個数
B1にダンボールに入れる基本個数
C1=IF(A1<=0,0,MAX(ROUNDUP(A1/B1,0)-(MOD(A1-1,B1)<INT(B1/2)),1))

お礼

ありがとうございます。解決しました。
本当にありがとうございます

もしよろしければ式の説明していただければうれしいのですが。
お願いできますでしょうか。