Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2010)
時間毎の箱発注表作成について
投稿日時: 21/03/02 01:51:18
投稿者: まさ@まさ

お世話になっております。
 
昼夜交代勤務がある職場です。
 今回、ご質問させていただく内容は
生産計画(1か月)から当日、翌日の生産計画を抽出した表を
1日(昼夜)の予測進捗を立て、進捗に合わせ製品を入れる箱を
発注する表を作成したくご質問させて頂きました。
 
表1は生産計画(1か月)から当日、翌日を抽出した表となります。
 
   B  C    D  E    F  G
30      2/5(金)    2/8(月)   
31 昼 (1)    あ 100    あ 500
32 昼 (2)    う 400       
33 昼 (3)              
34 夜 (1)    う 200    う 256
35 夜 (2)    え 300       
36 夜 (3)              
37                   
38 昼 (1)    か 500    き 100
39 昼 (2)    き  36    お  7
40 昼 (3)           か 460
41 夜 (1)    き 512    き 256
42 夜 (2)              
43 夜 (3) 
 
下記が表2で
表1を元に当日の進捗を入れた物になります。

  
      A     B     C        D         E         F       G     H       I       J       K
1            昼勤                     夜勤   翌昼勤     

2        箱  8:30    9:15   10:15   11:15   12:45   12:45   14:00 

3              9;15   10:15   11:15   12:00   14:00   15:00   16:00 

4              8:30    9:15   10:15   11:15   12:45   12:45   14:00 

5     
6
7   1号  3     45        60      60       45

8        3                                      30      60       60

9        2

10 2号  4     30         50     50       50     50

11      横4                                     20       60

12

上記が予想進捗となり夜勤、翌昼勤、他のラインは抜粋しています。
 
今回したいのが下記表2を元に表3を作成し
B列の箱(入数)の注文になります。
表2と同じフォームが見やすいかと思いますのでそちらで
作成しようと考えておりますが、他に案がありましたらお教えください。
表3と、したいことは下記の通りです。
 
(上段は少し解説つき、下段は解説なし、2,3,4行目は進捗の計算と時間表示に必要な行となっています)

      A   B   C     D      E      F     G       H     I     J     K
1            昼勤                     夜勤   翌昼勤    前日保有

2        箱  8:30  9:15  10:15  11:15  12:45  12:45  14:00 

3             9;15  10:15 11:15  12:00  14:00  15:00  16:00 

4             8:30  9:15  10:15  11:15  12:45  12:45  14:00 

5    
6
7   1号  3           40                                     40

             40*3=120台分、-45-60=15台(÷3で5箱残り、40箱入荷し45箱あまり

8        3      45箱あまり×3=135台分         5                                 0

9        2                                                                          0

10 2号  4           20      20                                                    20

11      横4                         5       15                                      0

12

 
 

      A   B   C     D      E      F     G       H     I     J     K
1            昼勤                      夜勤   翌昼勤    前日保有

2        箱  8:30  9:15  10:15  11:15  12:45  12:45  14:00 

3             9;15  10:15 11:15  12:00  14:00  15:00  16:00 

4             8:30  9:15  10:15  11:15  12:45  12:45  14:00 

5    
6
7   1号  3           40                                      40

8                                            5                                    0

9        2                                                                            0

10 2号  4           20      20                                                      20

11      横4                        5       15                                         0

12

フォームを崩さないように前日保有を表の右側にもってきていますが
特にこだわりはありません。また、上記納入、使用をした後の理論保有も分かればありがたいです。
 
説明が下手ですがよろしくお願い致します。
 
 

回答
投稿日時: 21/03/02 09:38:00
投稿者: Suzu

引用:
今回したいのが下記表2を元に表3を作成し
B列の箱(入数)の注文になります。
表2と同じフォームが見やすいかと思いますのでそちらで
作成しようと考えておりますが、他に案がありましたらお教えください。
表3と、したいことは下記の通りです。
 
(上段は少し解説つき、下段は解説なし、2,3,4行目は進捗の計算と時間表示に必要な行となっています)

 
 
ご質問は、『他のフォーマットで良い案があるか?』 と言う事でしょうか?
 
 
1. レイアウトが崩れていて、どこに何があるのか判りません。
   スペースで見た目列方向を整えるなり、コードタグを使用するなり 判断できる様にしてください。
 
2. その表中の、値が何を表すのか 説明がなく、見出しも無いので、判らない
 
3. 数式が提示されているが、数式中の値がどこから来ていのか 判らない
 
4. 表の目的、管理したい事が判らないので、案の出しようもない
    (見出しや、値の意味が判れば おのずと判るのかもしれませんが、
     少なくとも、現時点の当方には判りかねます)
 
上記について、判らないと細かい部分については、回答しかねます。
 
ただ、発注数が判れば良いのであれば、それを横軸 時間帯ごとに出す必要性が判りません。
『前日保有を表の右側にもってきていますが』とありますから、
時間帯毎に、箱がジャストインタイムにて入荷するのでは無いと思いました。
それであれば、シフト或いは日ごと でも 良いと思えるのですがいかがでしょうか?

回答
投稿日時: 21/03/02 09:49:43
投稿者: んなっと

配置と数字の意味を正しく伝えてくれれば、回答できると思います。
それまで待ちます。
こちらからの推測で整理することも可能ですが、今回はやめておきます。

投稿日時: 21/03/02 22:45:05
投稿者: まさ@まさ

お世話になっております。
 
プレビューで確認したつもりでしたが
ずれており、見にくくなっており申し訳ございません。
 
表2表3がずれているので修正したものを下記に載せます。
 
表2

      A      B          C          D         E            F          G        
1                     昼勤                     夜勤       

2          箱       8:30      9:15      10:15       11:15      12:45   

3                      9;15      10:15     11:15       12:00      14:00   

4                      8:30      9:15      10:15       11:15      12:45   

5     
6
7   1号  3             45          60         60         45

8        3                                               30         60      

9        2

10 2号  4             30         50          50         50        50

11      横4                                                        20   

12

 
表3(解説入り)
  
    A      B        C        D        E        F         G             保有数
1                昼勤                     
2        箱      8:30     9:15     10:15     11:15    12:45  
 
3                 9;15     10:15    11:15     12:00    14:00   

4                 8:30     9:15     10:15     11:15    12:45  

5    
6
7   1号   3        40                                      40

  C列入荷状況   40箱(保有箱数)×3個入り=120台分、-45台(使用数)-60台(使用数)
         =15台(÷3個入りで5箱残り、40箱入荷し45箱あまり

8        3      45箱あまり×3=135台分         5                                 0

9        2                                                                          0

10 2号  4           20      20                                                    20

11      横4                          5       15                                    0

12


  表3
  

      A     B     C       D        E        F       G          保有数    
1                昼勤                       
2          箱    8:30    9:15    10:15    11:15    12:45  
3                 9;15    10:15   11:15    12:00    14:00  
  
4                 8:30    9:15    10:15    11:15    12:45  
5    
6
7   1号     3       40                                40

8                                       5                                    0

9           2                                                                 0

10 2号     4       20       20                                              20

11         横4                 5        15                                    0

12


 
表2が各ラインの予想進捗(出来高)
表3が箱入荷数になっています。
 
表2の進捗を元に表3の箱を注文します。
(箱は時間毎に入荷される仕組みになります)
 
進捗に合わせ不足無いように箱を自動注文する表を作成したく。
 

回答
投稿日時: 21/03/02 23:29:30
投稿者: んなっと

数字の意味は?もっと頑張りましょう

投稿日時: 21/03/03 00:03:41
投稿者: まさ@まさ

お世話になっております。
 
数字の意味についてですが
2列目3列目はその時間の出来高という意味で
4列目は時間毎の出来高を計算するのに入力している時間です
 
 
表2表3の箱(B列)は各機種で梱包箱が違うので
1箱に何個入るか(箱の種類を示しています)
 
表3の飛び飛びの数字は
その時間帯に入荷する箱の数になっており
数値×箱(B列)が箱に入る台数となります。
 

回答
投稿日時: 21/03/03 09:31:37
投稿者: んなっと

少しはましになっていますが、まだまだ不足しています。
今回はこちらから助け舟は出しません。
 
身近なほかの人に見せてみてください。
「ここを詳しく具体的に書くといいよ」といったアドバイスがもらえるかもしれません。

回答
投稿日時: 21/03/03 10:01:17
投稿者: Suzu

引用:
表2が各ラインの予想進捗(出来高)
表3が箱入荷数になっています。
表2の進捗を元に表3の箱を注文します。
(箱は時間毎に入荷される仕組みになります)
 
進捗に合わせ不足無いように箱を自動注文する表を作成したく。

 
表2、表3 の B7〜B11 は箱の種類
表2 C7〜G11 は 1号ライン・2号ライン の 各箱毎の 生産個数
 
表3 C7〜G11 は 1号ライン・2号ライン の 各箱毎の 箱の入荷数
表3 H7〜H11 は 1号ライン・2号ライン の 在庫生産前の箱の保持数
 
表3 の C7:G11 の値を自動で計算したい と言う事でしょうか?
 
 
引用:
C列入荷状況 40箱(保有箱数)×3個入り=120台分、-45台(使用数)-60台(使用数)
         =15台(÷3個入りで5箱残り、40箱入荷し45箱あまり
引用:
45箱あまり×3=135台分

 
これは、表3 の C7 の 40 の 数値を算出する為の計算式でしょうか?
その時の、45台、60台 は、表2 の C7:D7 の 45、60 でしょうか?
とすると、表2 D7 の生産分までの箱が確保でき、その段階で、135台分の箱が在庫として存在するはずなので
その後の E7:F7 の生産分 60+45=105台分まで の分を確保できるから良い?
 
だとして、
8行目
表2 の F8:G8 の 生産数 90 に対し
表3 の E8 の箱入荷数 5箱、 H8 の 箱在庫は 0 → 3x5=15台分
     生産数の方が大きいですが? 良いのですか?
 
10行目
表2 C10:G10 合計 230
表3 C10:H10 合計 60箱 60箱x4 = 240 なので OK
 
  表2 C10:D10 生産数 80 に対し、
  表3 C10段階での箱数 在庫20+入荷20=40箱 40x4=160個分
   ただし、、箱の 最小在庫を考えるなら、もっと納入を分散させるのでは?
   或いは、輸送コストを下げるのであれば、C列の納入数 20を 30程度に増やし、
                                            D列の納入数 20 を E列に変える
          :
        etc の検討があるのでは?
 
 
引用:
自動注文する表を作成

の為には、その 注文する数値を決めるロジックを明確にし、数式に組み込む必要があります。
 
そんな検討事項を数式に組み込むよりは、そのタイミングで箱がいくつ入ってきたら
次の時間帯にいくつの箱の在庫が回せるか ? が判る表を作り、
箱の注文数を入力させる様にしては?
 
	A	B	C	D	E	F	G	H	I	J
7	1号		3	生産		45	60	60	45	
8				箱入荷		40				
9				箱在庫	40	65	45	25	10	10
10			3	生産					30	60
11				箱入荷				5		
12				箱在庫	0	0	0	5	-5	-25
13			2	生産						
14				箱入荷						
15				箱在庫	0	0	0	0	0	0
16	2号		4	生産		30	50	50	50	50
17				箱入荷		20	20			
18				箱在庫	20	32.5	40	27.5	15	2.5
19		横	4	生産						20
20				箱入荷				5	15	
21				箱在庫	0	0	0	5	20	15

 
各列 生産 は入力済み
E列 箱在庫は手入力
各列 箱入荷は手入力
F〜 箱在庫は 計算式 F9: =((E9+F8)*$C7-F7)/$C7

回答
投稿日時: 21/03/03 10:02:39
投稿者: Suzu

んなっと さんの引用:
少しはましになっていますが、まだまだ不足しています。
今回はこちらから助け舟は出しません。
 
身近なほかの人に見せてみてください。
「ここを詳しく具体的に書くといいよ」といったアドバイスがもらえるかもしれません。

 
あ、、、、書き込んじゃいました。。すいません。

投稿日時: 21/03/03 22:36:45
投稿者: まさ@まさ

お世話になっております。
 
言葉足らずで申し訳ありません
 
セルの意味等はおっしゃる通りです。
 
今回の箱注文表は
不足は避けたいが在庫は最小限に抑えたい。
 
という意図がありました。
 
Suzu さんがご提案してくださった箱の在庫数から発注数を計算する表も分かりやすいと思いました。
 
ご提案ありがとうございます。
 
 

投稿日時: 21/03/04 00:58:47
投稿者: まさ@まさ

お世話になっております。
 
入荷数と在庫数の表を簡易的に作成しトライしてみましたが
ライン数も多く表が縦に長くなり見にくくなってしまいました。
 
今回、ご質問させていただいた表3のフォーマットを下記のように修正させていただきたく
 
結論として箱の種類毎に時間毎に使用数がわかり在庫数がわかる表が見やすいのかなと
思いますした。
 
行列のセルは番号は無視し下記のような表で箱の入荷、使用、在庫を管理したく
 

                                     8:30      9:15      10:15
箱(種類)                    9:15     10:15      11:15

3          使用数                  10       40          60
          入荷数                  50
                   在庫数    10            40       10         -50
2

4

横向き4

6

4クッション付き

 
 
「箱」と入力されている列は箱の種類を入力
在庫数のとなりに入力している10の数値は朝一在庫(前日理論在庫)
使用数は表2を元に箱の種類で時間毎の使用累計が計算される
入荷数の行は、その時間毎に入荷される箱を入力
時間毎の在庫数は過不足を示すようにしたいと思います。
 
まず、使用数を箱単位で示す関数は「sumproduct」関数が適しているでしょうか?
入荷数を自動入力させようとしたとき、自動入力は可能なのでしょうか
またはどんな関数を使用すればよいかお教えいただきたく
 
 
 
 
 
 
 

回答
投稿日時: 21/03/04 10:15:14
投稿者: Suzu

引用:
まず、使用数を箱単位で示す関数は「sumproduct」関数が適しているでしょうか?

「適する」かどうかを決めるのは、貴方です。
 
話を拝見する限り、生産数/[箱に入る個数] ではないのでしょうか?
 
21/03/02 01:51:18 の まさまささんの投稿で言うなら B列の 3,2,4,4横 の 内の 数値の部分と判断しました。
そこから、21/03/03 10:01:17 の当方の投稿では、数値として扱い易い様に、B列に「横」C列に「4」と分けた次第です。
 
 
引用:
入荷数を自動入力させようとしたとき、自動入力は可能なのでしょうか
またはどんな関数を使用すればよいかお教えいただきたく

 
おっしゃっている事が破綻しています。
引用:
今回の箱注文表は
不足は避けたいが在庫は最小限に抑えたい。

という事は、使用数分=入荷数=注文数 で良くなります。
 
それだけを考えて入荷数を決めている訳ではありませんよね?
条件もなにも判らないのに、どんな関数かなんて、回答者には判りません。
 
それらの条件を全て提示頂けるとは思えませんし
提示頂けたとして、数式にて固めてしまうと融通が利かなくなる
引用:
表2と同じフォームが見やすいかと思いますのでそちらで
作成しようと考えておりますが、他に案がありましたらお教えください。

 
以上から
 
発注数を手入力をし、全体を見ながらバランスをとり 管理する為のフォーマットを提示しました。
 
数式が必要なのであれば、その条件を提示下さい。

投稿日時: 21/03/05 21:50:11
投稿者: まさ@まさ

こんばんは
 
お世話になっております。
 
おっしゃる通り、条件も無く計算式や表を作成するのは
難しいですよね。
すみません。
 
前と同じ表を下記に載せ、補正値行を追加しました。
(一部計算がおかしいところ、補正値がわかりやすいように数値を変更してます)
 
条件としては
@不足無く、在庫も最小限に抑えたい
A使用数分=入荷数=注文数で基本的には良いのですが
 使用タイミング(時間)と同じ時間に入荷する仕様であれば間に合わない
 可能性があるのと、時間帯によれば同じ種類の箱を複数ラインで使用し
 減りが早い可能性があるため、使用数が入っている時間帯の在庫数が使用数の
 半分以上あれば同一時間の入荷でも可。半分以下であれば前の時間帯に入荷する
 (入荷数は使用数の半分程度)
 
B上記条件が難しい場合は補正値を入力し前の時間帯に入荷するようにする
C計算式を用い、ある程度、自動算出させたい
D一目でわかるようにできれば縦スクロール無しでみれるフォーマットが好ましい
E使用数(箱数)で表示されるようにしたい(表1は箱数では無く台数で入力されている)
 

 

                                     8:30      9:15      10:15
箱(種類)                    9:15     10:15      11:15

3          使用数                  10       40          90
          入荷数                  50
                   在庫数    10            50       10         -80
          補正値
2

4

横向き4

6

4クッション付き



 
上記表(前回と同じ)の9:15〜10:15の在庫が10箱となっており次の時間帯には90箱使用するので
在庫に不安がある
 
 
補正値使用例
      
                                     8:30      9:15      10:15
箱(種類)                    9:15     10:15      11:15

3          使用数                  10       40          90
          入荷数                  50       45
                   在庫数    10            50       55         -35
          補正値                           45
2

4

横向き4

6

4クッション付き


 
9:15〜10:15に補正値を入力し次の時間帯の半分以上の在庫を保有するようにした。
-35と表示されている時間帯に35箱以上(次の時間帯の使用数にもよるが)入荷するように
すれば間に合う計算
 
補正値行が無くても自動計算されるに越したことはありませんが
ある程度の自由度も踏まえ入れ込みました。
自動計算するような計算式はありますでしょうか?

回答
投稿日時: 21/03/08 09:21:13
投稿者: Suzu

引用:

条件としては
@不足無く、在庫も最小限に抑えたい
A使用数分=入荷数=注文数で基本的には良いのですが
 使用タイミング(時間)と同じ時間に入荷する仕様であれば間に合わない
 可能性があるのと、時間帯によれば同じ種類の箱を複数ラインで使用し
 減りが早い可能性があるため、使用数が入っている時間帯の在庫数が使用数の
 半分以上あれば同一時間の入荷でも可。半分以下であれば前の時間帯に入荷する
 (入荷数は使用数の半分程度)
 
B上記条件が難しい場合は補正値を入力し前の時間帯に入荷するようにする
C計算式を用い、ある程度、自動算出させたい
D一目でわかるようにできれば縦スクロール無しでみれるフォーマットが好ましい
E使用数(箱数)で表示されるようにしたい(表1は箱数では無く台数で入力されている)

 
引用:
9:15〜10:15に補正値を入力し次の時間帯の半分以上の在庫を保有するようにした。
-35と表示されている時間帯に35箱以上(次の時間帯の使用数にもよるが)入荷するように
すれば間に合う計算

 
2、3 の 条件がまだ曖昧です。
引用:
使用数が入っている時間帯の在庫数が使用数の
 半分以上あれば同一時間の入荷でも可。半分以下であれば前の時間帯に入荷する
 (入荷数は使用数の半分程度)
ここで言う 「在庫数」は、その 時間帯の最終在庫。その前の 時間帯の在庫 どちらでしょう?
 
引用:
上記条件が難しい場合は補正値を入力し前の時間帯に入荷するようにする

 
 
何にしても、1 の条件があるので、最小在庫は、0 となります。
その上で、次の時間帯の使用がある場合は、その使用数の半分を持っていれば良いのですから
基本、 本時間帯の在庫数は、次使用数の半数 を持っていれば良い事になりませんか?
 
そうなる様、数式を組むだけです。
条件が決まっているのですから、「補正」が必要な要素が見当たりません。
 
 
	A	B	C	D	E
1			8:30	9:15	10:15
2			9:15	10:15	11:15
3	使用数		10	40	90
4	入荷数		20	65	45
5	在庫数	10	20	45	0

 
B5 及び 3行目 使用数 が手入力 とすれば
C4    =C3-B5+D3/2
C5    =B5-C3+C4
両方 右へ オートフィル
 
で良いのではないでしょうか。
 
補正が必要なのであれば、補正行を加え、入荷数の数式に 補正の値を加える様にすれば良いでしょう。

回答
投稿日時: 21/03/08 09:23:22
投稿者: Suzu

引用:
D一目でわかるようにできれば縦スクロール無しでみれるフォーマットが好ましい
E使用数(箱数)で表示されるようにしたい(表1は箱数では無く台数で入力されている)

については、まさ@まさ さん の方でやり方は考えてください。

トピックに返信