荷捌作業効率をあげるためのエクセル関数を教えてください。

荷捌作業効率をあげるためエクセルで次の計算調書を作成したいと考えてます。
倉庫業を行っていて、入庫荷物が到着したら平パレットとボックスパレットに積込ます。
それぞれのパレットに品名・数量を明記した札を貼り付けています。
その札を作成をするため現在電卓で計算してますが、エクセルで瞬時に下記の事例の計算をすることが出来ますでしょうか。
〈事例〉
A商品入庫：９００個を平パレットには３６個、ボックスパレットには３３個を積み込みます。
なお、積み込む順番は倉庫にフォークリフトで４段重ねるため、ボックスパレット２枚→平パレット２枚→以下この繰り返しで積み込みます。
この方法で積み込めばボックスパレット１４枚、平パレット１２枚に積んで、端数６個となり９００個となります。この１４枚、１２枚、６個の答えをエクセルで瞬時に欲しいです。
よろしくお願いします。
※実現場では、物によってはパレットに積む数も変わりますし、荷物崩れ防止のためボックス３段、平１段の時もあります。重ねる段数は４段までです。

No.7 ベストアンサー 回答者： goomania 回答日時： 2022/10/08 18:49

タマタマポチさんの回答で解決しているかも知れませんんが、

ローテクではありますが、実際のパレット積載をシミュレートしてみました。
とりあえず数式を作ってみただけなので、もう少し考えれば、もっとすっきりしたものになるかも知れませんがお許しください。

>平パレットには３６個、ボックスパレットには３３個の荷を積載し、
>フォークリフトで４段重ねるため、ボックスパレット２枚→平パレット２枚
>→以下この繰り返しで積み込みます。

ということなので「ボックスパレット２枚、平パレット２枚」のかたまりを
1ブロックと数えることにします。

添付画像をご覧ください。


まず、ボックスパレットと平パレットの各パレット規定積載数をC2セル～F2セルに登録します。A4セルから下方向にNo.を表示しています。
A4セルに

=ROW(A1)

という数式をを記述し、下方向へコピーしています。
「ボックスパレット２枚、平パレット２枚」のかたまりで置いていくとするとNo.は置場所の位置に番号を付けているイメージになります。
M2セルに

=INT(L2/SUM(C2:F2))

という数式を記述し、上記のかたまり(ブロック)で置ける最大ブロック数を計算しています。

C4セルから右方向、下方向に実際に当該パレットに積載した荷の数を表示しています。
C4セルに

=IFS(($A4<=$M$2)+$L3>=SUM($C$2:C$2),C$2,$L3-SUM($B$2:B
$2)>0,$L3-SUM($B$2:B$2),TRUE,0)

という数式をを記述し、右方向、下方向へコピーしています。
さらに、L2セルには荷の積載総数を入力するものとします。直接L3セルに入力してもよいのですが、レイアウトの都合上L3セルに=L2を記述しています。その上でL4セルに

=IF(L3>=SUM(C4:F4),L3-SUM(C4:F4),0)

という数式をを記述し、下方向へコピーしています。
これで、実際のパレットへの積載状態をシミュレートできるようになります。

シミュレート結果から、実際にパレットの使用状況を集計するため
C3セルに

=COUNTIF(C4:C300,C2)

という数式をを記述し、右方向へコピーしています。
とりあえず、300行目までシミュレート可能な数式にしています。

これで各パレットの使用状況が集計できたので、ご質問者が知りたいのは

>この方法で積み込めばボックスパレット１４枚、平パレット１２枚に積ん
>で、>端数６個となり９００個となります。
>この１４枚、１２枚、６個の答えをエクセルで瞬時に欲しいです。

ですから、
H2セルに

=SUMPRODUCT((LEN($C$1:$F$1)<>LEN(SUBSTITUTE($C$1:$F$1,"box","")))*$C$3:$F$3)

I2セルに

=SUMPRODUCT((LEN($C$1:$F$1)<>LEN(SUBSTITUTE($C$1:$F$1,"flat","")))*$C$3:$F$3)

J2セルに

=SUMPRODUCT((C4:F300)*(ROW(C4:F300)=MATCH(0,L:L,0))*(C4:F300<>$C$2:$F$2))

という数式を記述しています。

これで、黄色に塗られたセルにご希望の値が表示されます。

L2のの入力値を変更することで、

>答えをエクセルで瞬時に欲しいです。

という状態になると思います。

また、

>実現場では、物によってはパレットに積む数も変わりますし、
>荷物崩れ防止のためボックス３段、平１段の時もあります。
というような場合、

パレットに積む数が変わる場合は表の規定積載数を変更すれば対応できると思います。
上記のようにボックス３段、平１段の時は、
E1セルのflat1という見出しをbox3に変更し、E2セルの規定積載数を
33に修正することで計算できると思います。

この回答へのお礼

goomania様
私が今回教えていただきたいエクセルによる、所期の目的をクリアーできる内容だと判断いたしました。
H２～J２に必要となるパレット数と端数も導きされておりますし、トラックが積んで来た数を任意に変更して、一枚あたりのパレットに積める数も任意に変更できるので目的通りです。
ご多忙の中、ここまで教えていただきありがとうございます。

お礼日時：2022/10/09 22:57

No.8 回答者： tatsumaru77 回答日時： 2022/10/09 10:25

商品個数=137の場合ですが

案１
①ボックスパレット1個を割り当て
137-33=104
②ボックスパレット1個を割り当て
104-33=71
③平パレット1個を割り当て
71-36=35
④平パレット1個を割り当て
35個なので割り当て不能

よって、
ボックスパレット個数＝2
平パレット個数＝1
余り＝35

であってますか？

案２
それとも、余り=35はボックスパレット1個に割り当て可能なので、
ボックスパレット１個を割り当て
35-33=2 とし、
ボックスパレット個数＝3
平パレット個数＝1
余り＝2
とするのでしょうか？


案１，案２のどちらでしょうか。

この回答へのお礼

案１を希望しております。

お礼日時：2022/10/09 22:48

No.6 回答者： tatsumaru77 回答日時： 2022/10/08 11:46

自作関数で良ければ提供可能です。

自作関数の使い方は、以下のようなイメージです。
A1セルが900の場合、
B1にボックスパレット数
C1に平パレット数
D1に余りの個数
を表示したい場合、
B1へ =自作関数(A1,1)
C1へ =自作関数(A1,2)
D1へ =自作関数(A1,3)
のように記述します。
自作関数は１つの値のみ返すので、どの値を返すのか、
その種類を1,2,3で指定します。

自作関数は、ユーザー定義関数といって、実際にはマクロで記述します。
従って、あなたのexcelがマクロの実行が可能な状態になっている必要があります。
又、マクロを含むブックを保存する場合は、拡張子が.xlsmになります。

上記で良ければ、自作関数を提供可能ですが、いかがでしょうか。

この回答へのお礼

皆様からアドバイスいただいており感謝しております。

お礼日時：2022/10/09 22:46

No.5 回答者： tatsumaru77 回答日時： 2022/10/07 14:03

>※実現場では、物によってはパレットに積む数も変わりますし、荷物崩れ防止のためボックス３段、平１段の時もあります。

重ねる段数は４段までです。

上記のことも、考慮した結果が欲しいということでしょうか。

「荷物崩れ防止のためボックス３段、平１段の時もあります。」
ついては、ボックスパレット1枚→ボックスパレット1枚→ボックスパレット1枚→平パレット１枚の順に計算すれば、良いと思います。

「物によってはパレットに積む数も変わります」については、
どのように変わるのかわからない為、計算のしようがありません。

この回答へのお礼

何度もご助言ありがとうございます。

お礼日時：2022/10/09 22:42

No.4 回答者： タマタマポチ 回答日時： 2022/10/07 12:01

> 電卓では、単純に９００-36-36-33-33-36-36-33・・で単純計算しておりました。

かなり鈍くさいですが、その通りの表にしました。
（単純な計算式は、私では無理）
以下からダウンロードして下さい。
B2に数量を入れれば、その右にボックスパレット数と平パレット数が表示されます。
（B2です、A2はB2が反映されます）
　
http://dtbn.jp/7bkLseYR

この回答へのお礼

お世話になっております。
ダウンロードを何度トライしても、保存データ０バイトとなっておりダウンロード出来ませんでした。

お礼日時：2022/10/09 22:40

No.3 回答者： tatsumaru77 回答日時： 2022/10/07 11:36

タマタマポチ様へ

私が関数式を回答できるわけではありませんが
質問者に代わって計算方法を述べます。
前提
パレットを4段重ねにする場合、
ボックスパレットを２段、平パレットを２段使用します。
４段重ねにしたものを１ブロックと呼ぶことにします。
そうすると１ブックでは、
ボックスパレット２段＝33個×2＝66個
平パレット　　　２段＝36個×2＝72個
合計　138個
使用します。

商品900個の場合、何ブロックになるかは、
900÷138の商＝6
900÷138の余＝72
なので６ブロックになります。
1ブロックにボックスパレットは２つなので、6×2＝12個（ボックスパレットの数）
1ブロックに平パレットは２つなので、6×2＝12個（平パレットの数）

余りについては、①②③の順に計算を行います。
①余りからボックスパッレト１つ分が取れるなら、１つ分を割り当てる。
　割り当て可能なので、割り当てる。
　72-33＝39（余り）
　ボックスパレット数＝12+1=13
②余りからボックスパッレト１つ分が取れるなら、１つ分を割り当てる。
　割り当て可能なので、割り当てる。
　39-33＝6（余り）
　ボックスパレット数＝13+1=14
③余りから平パッレト１つ分が取れるなら、１つ分を割り当てる。
　割り当て不能なので、割り当てない。

よって、商品900個の場合
ボックスパレット数＝14
平パレット数＝12
余り=6
となる。


質問者様へ
上記の説明であってますでしょうか。

この回答へのお礼

tatsumaru77様
　質問者がこの様に明記すべきところ、代わって記載いただき感謝してます。
　ありがとうございます。
　本記載のとおりの荷捌実務となっております。

お礼日時：2022/10/08 11:13

No.2 回答者： タマタマポチ 回答日時： 2022/10/07 09:36

あなたが電卓で計算する計算方法（過程）を提示して下さい。

単純計算では、あなたが求める物にならない。
関数は教えますが、ややこしい計算方法まで考えるのは？

この回答へのお礼

タマタマポチ様
　早速ありがとうございます。
　電卓では、単純に９００-36-36-33-33-36-36-33・・で単純計算しておりました。

お礼日時：2022/10/07 10:21

No.1 回答者： タマタマポチ 回答日時： 2022/10/07 09:28

> この１４枚、１２枚、６個の答え

あなたが計算する計算式を示さなければ、わからない（わかりにくい）です。
単純に計算すると13枚、13枚、余3個になってしまう。
　
多分「４段重ねるため、ボックスパレット２枚→平パレット２枚」これがミソだと思いますが、ここまで回答者に考えろというのは？
計算の過程を示した方が親切でしょ。