Excel（エクセル）活用コミュのルールに基づいた計算式

フローチャートを見直したほうがいい気もしますが。。。

とりあえず、

>最後になりましたがグループＤとＥの合計は違いますのでＥが８０になった時点でそれ以上はすべてグループＤに入ります。

この条件は最初にもってきておいたほうがいいんじゃないですか？

そうですね。
振り分けの条件は一番最初に決めておくべき事ですね。
ありがとうございます。


この条件を最初にもってきてもう一度トライしてみます。

また行き詰まった際は質問させていただきます。
ありがとうございます。

ちなみに、まっきぃさんがこのような計算式を作る場合は別の場所で行って最終的な結果をそれぞれの箱に表示するというような方法はいいとおもいますか？

この方法の場合はそれぞれの箱の計算を一つのセルで行う必要がない為、あとあとメンテナンスをする場合にわかりやすいかと思います。

自分の場合は他のセルをを参照するやり方が増えるとメンテナンスの際に計算がおかしくなったりする可能性が増えるので好まないのですが…

メンテナンスの際に計算がおかしくなる、というのがよくわかりませんが、私はいくつかのセルにわけると思います。
どこまで正しく動いてどこで間違っているかがはっきりしやすいのと、あまり長い数式にしてしまうと後でわからなくなってしまうので。。。

どんな業務かまったくイメージできませんが
条件を整理してみました
01.箱(1)〜(6)がある
02.箱の最大容量は物体A40個分
03.物体Aには種類が2つある
04.物体Aのうち40%が物体B
05.物体Aのうち物体B以外が物体C
06.箱を2つのグループにわける
07.箱(1)〜(4)をグループDとする
08.箱(5)〜(6)をグループEとする
09.グループ2つに物体Aを等分する
10.箱にBとCをAとの比率で案分する
11.グループ内で空箱がある場合は
　半端分と最も先頭寄りの空箱で等分する
12.Eに入り切らない分はDに入れる

とりあえずご質問の
「出来るか出来ないか」については、
「できません」となります。
なぜかというと、
上の整理した条件 12. が
常に条件 09. に背反するためです

条件 09. が必ずしも満たされなくても
問題なければ、以下のようにすることで計算できます。
また、計算時に端数がでる場合の処理も考慮に入れました。

■結果エリア
A1=MID(REPT("D",$B$11)&REPT("E",$B$10-$B$11),ROW(),1)
A1をA8までコピー
B1=VLOOKUP($A1&COUNTIF($A$1:$A1,$A1),$E$1:$G$28,COLUMN(),FALSE)
B1をB1:C8にコピー

■説明エリア
A10=箱の総数
A11=Dグループの箱の数
A12=箱の容量
A13=Aの個数
A14=AにおけるB,Cの比率
A15=B,Cの個数
A16=箱ごとの上限
A17=Dへの割り当て個数
A18=Eへの割り当て個数
A19=Eの必要箱数
A20=Eのオーバーフロー
A21=Dへの最終的な割り当て個数
A22=Dの必要箱数
A23=Dのオーバーフロー

■入力エリア
B10=6
B11=4
B12=40
B13=100
B14=40%

■ベース計算エリア
B15=ROUNDUP($B$13*$B$14,0)
B16=ROUNDUP($B$12*$B$14,0)
B17=ROUNDUP($B$15*0.5,0)
B18=$B$15-$B$17
B19=INT(B$18/B$16)+IF(MOD(B$18,B$16)>0,1,0)
B20=IF($B$19>$B$10-$B$11,(INT(B$18/B$16)-($B$10-$B$11))*B$16+MOD(B$18,B$16),0)
B21=B$17+B$20
B22=INT(B$21/B$16)+IF(MOD(B$21,B$16)>0,1,0)
B23=IF($B$22>$B$11,(INT(B$21/B$16)-$B$11)*B$16+MOD(B$21,B$16),0)
C14=1-$B$14
C15=$B$13-B15
C16=$B$12-$B$16
C17=ROUNDUP($C$15*0.5,0)
C18=$C$15-$C$17
C20=IF($B$19>$B$10-$B$11,(INT(C$18/C$16)-($B$10-$B$11))*C$16+MOD(C$18,C$16),0)
C21=C$17+C$20
C23=IF($B$22>$B$11,(INT(C$21/C$16)-$B$11)*C$16+MOD(C$21,C$16),0)

■結果算出エリア
E1=TEXT(ROW(),"@")
E2=TEXT(ROW(),"@")
E3=TEXT(ROW(),"@")
E4=TEXT(ROW(),"@")
E5=TEXT(ROW(),"@")
E6=TEXT(ROW(),"@")
E7=TEXT(ROW(),"@")
E8=TEXT(ROW(),"@")
E9="D"&IF($B$22>$B$11,$B$11-4,$B$22-4)
E10="D"&IF($B$22>$B$11,$B$11-3,$B$22-3)
E11="D"&IF($B$22>$B$11,$B$11-2,$B$22-2)
E12="D"&IF($B$22>$B$11,$B$11-1,$B$22-1)
E13="D"&IF($B$22>$B$11,$B$11,$B$22)
E14="D"&IF($B$22>$B$11,$B$11+1,$B$22+1)
E15="D"&IF($B$22>$B$11,$B$11+2,$B$22+2)
E16="D"&IF($B$22>$B$11,$B$11+3,$B$22+3)
E17="D"&IF($B$22>$B$11,$B$11+4,$B$22+4)
E18="D"&IF($B$22>$B$11,$B$11+5,$B$22+5)
E19="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11-4,$B$19-4)
E20="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11-3,$B$19-3)
E21="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11-2,$B$19-2)
E22="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11-1,$B$19-1)
E23="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11,$B$19)
E24="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11+1,$B$19+1)
E25="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11+2,$B$19+2)
E26="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11+3,$B$19+3)
E27="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11+4,$B$19+4)
E28="E"&IF($B$19>$B$10-$B$11,$B$10-$B$11+5,$B$19+5)
F1=""
F2=""
F3=""
F4=""
F5=""
F6=""
F7=""
F8=""
F9=B$16
F10=B$16
F11=B$16
F12=IF($B$22>$B$11,B$16,ROUNDUP((B$21-($B$22-2)*B$16)*0.5,0))
F13=IF($B$22>$B$11,B$16,(B$21-($B$22-2)*B$16)-F$12)
F14=0
F15=0
F16=0
F17=0
F18=0
F19=B$16
F20=B$16
F21=B$16
F22=IF($B$19>$B$10-$B$11,B$16,ROUNDUP((B$18-($B$19-2)*B$16)*0.5,0))
F23=IF($B$19>$B$10-$B$11,B$16,(B$18-($B$19-2)*B$16)-F$22)
F24=0
F25=0
F26=0
F27=0
F28=0
G1=""
G2=""
G3=""
G4=""
G5=""
G6=""
G7=""
G8=""
G9=C$16
G10=C$16
G11=C$16
G12=IF($B$22>$B$11,C$16,ROUNDUP(((C$21-($B$22-2)*C$16))*0.5,0))
G13=IF($B$22>$B$11,C$16,(C$21-($B$22-2)*C$16)-G$12)
G14=0
G15=0
G16=0
G17=0
G18=0
G19=C$16
G20=C$16
G21=C$16
G22=IF($B$19>$B$10-$B$11,C$16,ROUNDUP((C$18-($B$19-2)*C$16)*0.5,0))
G23=IF($B$19>$B$10-$B$11,C$16,(C$18-($B$19-2)*C$16)-G$22)
G24=0
G25=0
G26=0
G27=0
G28=0

■動作例
画像１：Aが100個の場合
画像２：Aが173個の場合
画像３：Aが173個で箱が7個の場合

なかなかおもしろいですね。しかし、ルールの解釈に間違いがあるか、
もしくはルールが間違っていますね。

> ここまでは出来るのですが、それぞれのグループに空きの箱がある場合はそれぞれのグループの最後に入っている箱と次の空の箱を使って半分にするというものです。
>
> この例の場合は１の箱にはＢは１６、Ｃは２４、２の箱と３の箱にはそれぞれＢが２、Ｃが３が入ります。

1の箱にいれる時点で同じグループの次の箱があいていますから、
このルールが正しいとすると、1にBは16はいらず、1にBが10、2にBが10入ります。
inconsistentですね。

仮にルールが正しいと仮定します。
N(A)、N(B)、N(C)をそれぞれA、B、Cの全体の個数とすると、

N(B) = N(A)/3
N(C) = N(B) * 1.5
(ただし、N(A)、N(B)、N(C)は正の整数)

という制約条件が与えられる。
つぎに、Nn(B)、Nn(C)をそれぞれ nの箱に入るB、Cの箱の数とすると、
Nn(B)のとりうる値は0以上、16以下の整数である。
またNn(C) = Nn(B) * 1.5

このとき、

N5(B) = N6(B) = IF((N(B)/2 > 16, 16, N(B)/2)

そして、N1(B) + N2(B) + N3(B) + N4(B) = N(B) - N5(B)*2
であるから、

N1(B) = IF( ((N(B)-N5(B)*2)/2 > 16), 16, (N(B)-N5(B)*2)/2)
Nn(B) = IF ( (Nn-1(B) < 16), Nn-1(B), IF( (N(B)-N5(B)*2-16*(n-1))/2 > 16), 16, N(B)-N5(B)*2-16*(n-1))/2))

ちょっと見直していませんが、だいたいこんな感じのはず。

うーん、だいぶまちがってるなあ
まだまだ修行が足りない!

■訂正
F1 =""
F2 =""
F3 =""
F4 =""
F5 =""
F6 =""
F7 =""
F8 =""
F9 =B$16
F10=B$16
F11=B$16
F12=B$16
F13=IF($B$22>=$B$11,B$16,IF($B$22+1>$B$11,(B$21-($B$22-1)*B$16),ROUNDUP((B$21-($B$22-1)*B$16)*0.5,0)))
F14=IF($B$22>$B$11,B$16,(B$21-($B$22-1)*B$16)-F$13)
F15=0
F16=0
F17=0
F18=0
F19=B$16
F20=B$16
F21=B$16
F22=B$16
F23=IF($B$19>$B$10-$B$11,B$16,IF($B$19+1>($B$10-$B$11),(B$18-($B$19-1)*B$16),ROUNDUP((B$18-($B$19-1)*B$16)*0.5,0)))
F24=IF($B$19>$B$10-$B$11,B$16,(B$18-($B$19-1)*B$16)-F$23)
F25=0
F26=0
F27=0
F28=0
G1 =""
G2 =""
G3 =""
G4 =""
G5 =""
G6 =""
G7 =""
G8 =""
G9 =C$16
G10=C$16
G11=C$16
G12=C$16
G13=IF($B$22>=$B$11,C$16,IF($B$22+1>$B$11,(C$21-($B$22-1)*C$16),ROUNDUP((C$21-($B$22-1)*C$16)*0.5,0)))
G14=IF($B$22>$B$11,C$16,(C$21-($B$22-1)*C$16)-G$13)
G15=0
G16=0
G17=0
G18=0
G19=C$16
G20=C$16
G21=C$16
G22=C$16
G23=IF($B$19>$B$10-$B$11,C$16,IF($B$19+1>($B$10-$B$11),(C$18-($B$19-1)*C$16),ROUNDUP((C$18-($B$19-1)*C$16)*0.5,0)))
G24=IF($B$19>$B$10-$B$11,C$16,(C$18-($B$19-1)*C$16)-G$23)
G25=0
G26=0
G27=0
G28=0
(他はそのままでいけます)

ルール整理しときながら、数式組むときに思いっきり勘違いしてました。

しｐへｒさん

ありがとうございます。
解説もわかりやすく計算式も参考になり勉強にました。

ただ僕のルール説明に至らないところがあり申し訳ありません。

まず条件１２が条件９に反するという事に関してはＡの数が１６０までは各グループで均等に、それ以上は９の条件は適用されません。

またそれぞれのグループで空きがある場合に次の箱を使って均等にするのは４０未満の場合のみです。
したがってＡが１００の場合を例にした場合には１の箱は４０を越えてしまいますので１６と２４、残りが半分に分けるということになります。

業務の内容は機内食を入れるカートなんですが二つの部屋の合計数が異る事。
クルーがサービスするにあたり各カートに決められた割合でセットしている事（１６と２４）
端数が発生する場合は空きカートを使って半分わけること（クルーが手分けしてサービスする為に仕事量を揃える）
４０以上は分けない、端数のみ分けるというのがエアラインからの指示です。

いまＰＣが手元にないため、しｐへｒさんの計算式を試していませんが自分の考え方と違うのですごく参考になります。

ありがとうございます。

N(A)、N(B)、N(C)をそれぞれA、B、Cの全体の個数とすると、

N(B) = N(A)*2/5
N(C) = N(B) *3/5
(ただし、N(A)、N(B)、N(C)は正の整数)

という制約条件が与えられる。
つぎに、Nn(B)、Nn(C)をそれぞれ nの箱に入るB、Cの箱の数とすると、
Nn(B)のとりうる値は0以上、16以下の整数である。
またNn(C) = Nn(B) * 1.5

このとき、 Eグループは

N5(B) = N6(B) = IF((N(B)/4 > 16, 16, N(B)/4)

そして、N1(B) + N2(B) + N3(B) + N4(B) = N(B) - N5(B)*2
であるから、

R1(B) = N(B) - N5(B)*2
Rn(B) = Rn-1(B)-Nn-1(B)
と定義すると、Dグループは、

N1(B) = N2(B) = IF( (R1(B)/2 > 16), 16, R1(B)/2)
N3(B) = IF( (R3(B)/2)>16, 16, R3(B)/2)
N4(B) = IF( R4(B)>16, 16, R4(B))

こうだな!

keicさん

ルール説明の不備のご指摘ありがとうございます。
こうやって指摘をしていただく事で考え方のヒントになります。

ありがとうございます。

> またそれぞれのグループで空きがある場合に次の箱を使って均等にするのは４０未満の場合のみです。

なんだ、ルールが間違っていたのか...

しかし、アプリケイションを聞いたら一気に面白くなくなったな^^;;

まだルールが明確でない気がします。

例えばEグループにBが8個しかない場合、どうなりますか?

4に8個、5に0個?
それとも、4に4個、5に4個?

kenicさん

ＥグループにＢが８しかない場合は５に４、６に４で分かれます。

現在の比率でこのようなケースが発生するかはわかりませんが今後３０％、７０％といった比率になった場合や各カートに入れる数が変わった場合はそうなります。

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

B eef と C hecken か！

とりあえず今出ている条件は
4 のコメントの分と 7 のコメントの訂正
を加えたシートを作れば満たせそうです

A=100の場合
1(D) 16 24
2(D) 2 3
3(D) 2 3
4(D) 0 0
5(E) 16 24
6(E) 4 6
でいいんですよね？

しｐへｒさん

はい！
BEEF、CHICKEN等です
各カートへの入れ方も14のコメント例で大丈夫です

シーズンによってメニューや比率が変わるのでよくメンテナンスが発生します。
またエアラインによってもルールが様々、入り数も違います。
今取り組んでいるのが特に難しく行き詰まっていました。

今まではそれぞれのセルで全てを計算するのに慣れていたのですが別にしたほうがメンテナンスがしやすいんですね

しｐへｒさん

今PCで計算式を入力してみたのですが
200までの数は正しい計算結果が出るのですが
201以上の場合に結果がおかしくなるようです。
まだ計算式を全て理解出来ていないので
どうすれば修正出来るのかわからないです。
すみません、、、

■訂正
はじめのIFの等号が間違っていました

□誤
F13=IF($B$22>=$B$11,B$16,IF($B$22+1>$B$11,(B$21-($B$22-1)*B$16),ROUNDUP((B$21-($B$22-1)*B$16)*0.5,0)))
↓
□正
F13=IF($B$22>$B$11,B$16,IF($B$22+1>$B$11,(B$21-($B$22-1)*B$16),ROUNDUP((B$21-($B$22-1)*B$16)*0.5,0)))

□誤
G13=IF($B$22>=$B$11,C$16,IF($B$22+1>$B$11,(C$21-($B$22-1)*C$16),ROUNDUP((C$21-($B$22-1)*C$16)*0.5,0)))
↓
□正
G13=IF($B$22>$B$11,C$16,IF($B$22+1>$B$11,(C$21-($B$22-1)*C$16),ROUNDUP((C$21-($B$22-1)*C$16)*0.5,0)))

しｐへｒさん

ありがとうございます！
それぞれの部屋に必要な箱の数から端数を分ける、分けないを判別するんですね。
全く思いつきませんでした。
また計算結果を参照する先をVLOOKUPを使用した方法などで変えたり・・・
とても勉強になります。

今までは計算結果を表示するセルで全てを行うためIF分を繰り返し使用し
複雑になりメンテナンスにも時間を要していました。

今後の資料作成にも参考になり感謝いたします。

(#19は私です)
たびたびすみません
端数がちょうど箱の容量と一致したときにも
等分してしまっていたので直しました
例）A=200

あと、
端数の算出方法が(正しいものの)意味がわかりにくい数式になっていたので
MODに置き換えて判りやすくしました。

■追加
A24="Dの半端"
B24=MOD(B$21,B$16)
C24=MOD(C$21,C$16)
A25"Eの半端"
B25=MOD(B$18,B$16)
C25=MOD(C$18,C$16)

■訂正
F13=IF(OR($B$22>$B$11,B$24=0),B$16,IF($B$22+1>$B$11,B$24,ROUNDUP(B$24*0.5,0)))
G13=IF(OR($B$22>$B$11,C$24=0),C$16,IF($B$22+1>$B$11,C$24,ROUNDUP(C$24*0.5,0)))
F14=IF(OR($B$22>$B$11,B$24=0),0,B$24-F$13)
G14=IF(OR($B$22>$B$11,C$24=0),0,C$24-G$13)
F23=IF(OR($B$19>$B$10-$B$11,B$25=0),B$16,IF($B$19+1>($B$10-$B$11),B$25,ROUNDUP(B$25*0.5,0)))
G23=IF(OR($B$19>$B$10-$B$11,C$25=0),C$16,IF($B$19+1>($B$10-$B$11),C$25,ROUNDUP(C$25*0.5,0)))
F24=IF(OR($B$19>$B$10-$B$11,B$25=0),0,B$25-F$23)
G24=IF(OR($B$19>$B$10-$B$11,C$25=0),0,C$25-G$23)

しｐへｒさん

コメント20の訂正を加えてみましたが
A=201や161の時のように端数が出ない時から出る時へ
切り替わる時に計算結果がおかしくなるようです。

私の方こそ自分で修正出来なくてすみません、、、
もう一度お願いできますか？

あちゃー
テスト不足でしたね。

■訂正
F13=IF(OR($B$22>$B$11,B$24+C$24=0),B$16,IF($B$22+1>$B$11,B$24,ROUNDUP(B$24*0.5,0)))
G13=IF(OR($B$22>$B$11,B$24+C$24=0),C$16,IF($B$22+1>$B$11,C$24,ROUNDUP(C$24*0.5,0)))
F14=IF(OR($B$22>$B$11,B$24+C$24=0),0,B$24-F$13)
G14=IF(OR($B$22>$B$11,B$24+C$24=0),0,C$24-G$13)
F23=IF(OR($B$19>$B$10-$B$11,B$25+C$25=0),B$16,IF($B$19+1>($B$10-$B$11),B$25,ROUNDUP(B$25*0.5,0)))
G23=IF(OR($B$19>$B$10-$B$11,B$25+C$25=0),C$16,IF($B$19+1>($B$10-$B$11),C$25,ROUNDUP(C$25*0.5,0)))
F24=IF(OR($B$19>$B$10-$B$11,B$25+C$25=0),0,B$25-F$23)
G24=IF(OR($B$19>$B$10-$B$11,B$25+C$25=0),0,C$25-G$23)

半端が 0 のときは(B,Cそれぞれの)最大値を設定するようにしていましたが、
Bだけ、Cだけでチェックしていると半端が 1,0 や 0,1 のときに
誤って最大値を設定してしまうようになっていました。
これに対応するため半端が出ているかどうかを
BとCの和でチェックするように変えました。

難しすぎてスルーしながら↑

そうか〜　「ミートですか？フィッシュですか？〜」　と

・・・思いながら読んでました　皆さん　えらい！！

しｐへｒさん

ありがとうございます。
今、外出中なので帰って訂正してみます。

ようやくしｐへｒさんの計算式が理解出来てきました。

今後は自分でメンテナンス出来ないとダメですから頑張ります。

ひとつひとつのセルで計算を仕上げる＝シンプルではないんですね。

非常にわかりやすいです。

planet★さん

関数ひとつひとつは理解できても組み合わせや裏技的な使い方で複雑な計算式がシンプルになったり…

使えば使うほど奥が深いって思いますね。

ちなみに今回はBEEFとCHICKENでした

しｐへｒさん

ありがとうございます

この計算式を導入しエアラインの指示通りのマニュアルに仕上げることが出来ます。
しかし今までの発想と全く違った方法だったので関心しきりです。
大変勉強になりました。

まだまだ作らないといけない資料、マニュアルがあるのですが
今回のみなさんのアドバイスを元に頑張っていこうと思います。