Excel (一般機能)

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

 
(指定なし : 指定なし)
ガントチャートの非稼働日設定
投稿日時: 19/11/13 15:24:17
投稿者: ナナカマド

工場の稼働を見るためのガントチャートを作成してます。
まず基礎を作る手間を省くためネット上で配布されてるブックをダウンロードしました。
  
シートが二枚。
一枚目は表、
二枚目は業務の開始日と非稼働日を入力するシート
  
表のほうを少し触って
 
 
  A B   C   D   E  F   G   H   I   J   K...
1 □ ■  ■  ■  ■  ■  ■  ■  ■  ■  ■...
  
2     (月)
 
3     (日付)。。。。。。。。
 
4工程1
 
5工程2
 
6工程3
 
7工程4
 
 
1. 各工程名A1:A6セルのところはそれぞれ色を振って塗りつぶしてます
  
2. B4に数字(製造個数)を入力したら、C5,D6,E7,F8,G9,H10,I11,に同じ数字が入力されるように
  =IF(B4="","",B4)
  と関数を入れていきました。(階段状になります)
  
3. 各工程の色がセルに入るように条件付き書式を入れました。
  =C4<>""
  範囲は
  =$C$4:$CO$4(表の横です)
 
4. A1は更新時刻が児童に入るようにVBAと関数を入れてます
  Function LastSaveTime()
Application.Volatile
LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last save time").Value
End Function
 
=LastSaveTime()
 
5. 2行目は日付が出るように二枚目のシートから 
  =非稼働日入力シート!B3
  ↑の指揮を入れることで引っ張ってきて、以降の日は+1表記にしてます
 
6. 土日祝などの非稼働日はC3〜の日付と結びづくように条件付き書式を
  =MATCH(C$4,非稼働日,0)>0
  列に反映されるように入れました(グレーに塗りつぶされます)
  
↑2と3は製造固定は物によって数が変わるので6〜9行をセットにして下に複数コピペをしました
  
次の点の解決策を教えてください
  
(1)シート2枚目「Confing」で設定された非稼働日が1枚目のシートに反映されますが、
 階段状に出るセルが非稼働日に重なってしまいます。
 非稼働日を避けるようにしたいです。
  
(2)表の上に一行入れて「3つ目の工程」(物によっては4つ目か五つ目)を一日に何個こなすかカウントするVBAと関数を入れました。
 Function ColorSum(R1 As Range, C As Range)
    Dim r As Range
    Application.Volatile
    ColorSum = 0
    For Each r In R1
        If r.Interior.Color = C.Interior.Color Then
            ColorSum = ColorSum + r.Value
        End If
    Next r
End Function
関数は
=colorsum(B5:B41,B2)
  
 
【追記】B2の上に一行追加しに「この色だよ」と一行塗りつぶしてます(他の方が見るときに必要な行です)
  
ですが、
=IF(B4="","",B4)の関数で出た数字、条件付き書式の色には反応がありません
色付きセルの数字をカウントする方法はありませんか?
 
  
よろしくお願いします。
 

回答
投稿日時: 19/11/13 16:51:23
投稿者: 半平太

2行目と3行目にあるべきサンプルデータを入れて貰えませんか?
※ 言葉の説明だけではクリアーに理解できません。
 

 行  _______A_______  _B_  _C_  _D_   E    F 
  1  2019/11/13(水)   □   ■   ■   ■   ■ 
  2  (月)                                  
  3  (日付)                                
  4  工程1            4                    
  5  工程2                 4               

回答
投稿日時: 19/11/13 18:04:21
投稿者: んなっと

具体例というのは、下のように必要な文字や数値が示されたものです。
あなたのは空きが多すぎます。質問文、書き直し。
 
     A   B   C   D   E   F   G   H   I   J   K   L
 1     11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 2   11  金  土  日  月  火  水  木  金  土   日   月
 3    1                                  
 4  工程1  125                               
 5  工程2  80        125                      
 6  工程3  70        80  125                   
 7  工程4           70  80  125                
 8  工程5              70  80  125             
 9  工程6                 70  80            125
10  工程7                    70            80
11                                      
12                                      
13                                      
14 P工程1  30                               
15 P工程2  50        30                      
16 P工程3  45        50  30                   
17 P工程4           45  50  30                
18 P工程5              45  50  30             
 
C5
=IF(COUNTIF(非稼働日,C$1),"",IFERROR(--(INDEX($B:$B,TEXT(ROW()-NETWORKDAYS($B$1,C$1,非稼働日)+1,"[>"&LOOKUP(10^9,ROW($A$3:$A4)/(LEFT($A$3:$A4)<>LEFT($A5)))&"]0;;"))&""),""))
右方向・→下方向・↓

回答
投稿日時: 19/11/13 23:25:11
投稿者: YuuSa

こんばんわ

引用:

(1)シート2枚目「Confing」で設定された非稼働日が1枚目のシートに反映されますが、
 階段状に出るセルが非稼働日に重なってしまいます。
 非稼働日を避けるようにしたいです。
   
(2)表の上に一行入れて「3つ目の工程」(物によっては4つ目か五つ目)を一日に何個こなすかカウントするVBAと関数を入れました。
 Function ColorSum(R1 As Range, C As Range)
    Dim r As Range
    Application.Volatile
    ColorSum = 0
    For Each r In R1
        If r.Interior.Color = C.Interior.Color Then
            ColorSum = ColorSum + r.Value
        End If
    Next r
End Function
関数は
=colorsum(B5:B41,B2)
   
  
【追記】B2の上に一行追加しに「この色だよ」と一行塗りつぶしてます(他の方が見るときに必要な行です)
   
ですが、
=IF(B4="","",B4)の関数で出た数字、条件付き書式の色には反応がありません
色付きセルの数字をカウントする方法はありませんか?

 
(1)関数で行うのなら、非稼働日を除いた日付で計算した後に、表示用のシートで非稼働日を
 戻してやれば簡単かと思います。(非稼働日を除くのに少し労力がかかりますが)
 VBAで行うのなら方法はいろいろありますが、始めたばかりなのでしたら、
 非稼働日の時は1列ずつ前の値をみてやれば答えは出せます。
 
(2)ちょっと意味がわかりませんが、そのコードはセルの書式設定で行う塗りつぶしの色が同じ時に、
 関数の第一引数に指定したセル範囲の数値を加算するものです。
 条件付き書式の判定をみている訳ではありません。
 ですので、条件付き書式ではなく、セルの塗りつぶしを第二引数のセルと同じにしてやれば反応する
 はずです。

回答
投稿日時: 19/11/14 11:08:22
投稿者: んなっと

むむ、2行目が日付と書いてありますね。ごめんなさい、修正。
 
     A   B   C   D   E   F   G   H   I   J   K   L
 2   11 11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 3    1  金  土  日  月  火  水  木  金  土   日   月
 4  工程1  125                               
 5  工程2  80        125                      
 6  工程3  70        80  125                   
 7  工程4           70  80  125                
 8  工程5              70  80  125             
 9  工程6                 70  80            125
10  工程7                    70            80
11                                      
12                                      
13                                      
14 P工程1  30                               
15 P工程2  50        30                      
16 P工程3  45        50  30                   
17 P工程4           45  50  30                
18 P工程5              45  50  30             
 
C5
=IF(COUNTIF(非稼働日,C$2),"",IFERROR(--(INDEX($B:$B,TEXT(ROW()-NETWORKDAYS($B$2,C$2,非稼働日)+1,"[>"&LOOKUP(10^9,ROW($A$3:$A4)/(LEFT($A$3:$A4)<>LEFT($A5)))&"]0;;"))&""),""))
右方向・→下方向・↓

投稿日時: 19/11/26 17:18:35
投稿者: ナナカマド

遅くなってごめんなさい
少し修正をして完成のイメージとしてはこちらです
 
   A      B    C   D   E   F   G   H   I   J   K   L   M
1 更新日時 工程3数  
2 ■■■■       
3            1月
4            1   2   3   4   5   6   7   8   9   10   11
5           金  土  日  月  火  水  木  金   土   日   月
6 入荷         125                        
7 工程2         80        125            
8 工程3         70        80   125
9 工程4                   70  80   125
10 工程5                     70   80  125
11 工程6                        70  80  125  
12 出荷                           70  80         125
13                                      
 
二枚目のシートは開始日と非稼働日を入力しています、これが一枚目のシート、表に条件書式で反映されグレーで塗りつぶされます
 
   A B      C     D    E
1
2 開始日   非稼働日   曜日
3  2020/1/1 2020/1/1 =WEEKDAY(C3,1)
4 2020/1/2
5 2020/1/3
6 2020/1/4
7 2020/1/5
8 =C7+6
9 =C8+1
10 =C9+6
 
シート一枚目のカレンダーは1/4だと=MATCH(C$4,非稼働日,0)>0と入っており以降よこに+1としてます
 
月のところC3のセルには=C4、それ以降隣のセルには=IF(DAY(D4)=1,MONTH(D4),"")と続いています
 
非稼働日を塗りつぶす条件付き書式は =MATCH(C$4,非稼働日,0)>0
 
追加した関数は
 
A2■のVBAは
  Function LastSaveTime()
Application.Volatile
LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last save time").Value
End Function
 
=LastSaveTime()
 
C1:J1◇のVBAは
    Dim r As Range
    Application.Volatile
    ColorSum = 0
    For Each r In R1
        If r.Interior.Color = C.Interior.Color Then
            ColorSum = ColorSum + r.Value
        End If
    Next r
End Function
関数は
=colorsum(C6:C41,C2)
 
C2:M2にかけて「この色だよ」とセルを先に塗りつぶしています
 
表に出る数字は「入荷」の行に個数を入れたら=IF(C6="","",C6)の式で自動的に出現、条件書式で塗りつぶしになります
 
次の点が解決できません
●D6などの「非稼働日入力シートからの塗りつぶし」をしてある土日(まれに祝)を飛ばしてセルが埋まってくれません。土日を飛ばす方法が知りたいです。
 
●C1から横行に入れてあるセルに反応がありません。セルの色で反応するように設定したのはカウントする数が工程2だったり3だったりとそのたびに代わるからです。
 
『条件付き書式ではなく、セルの塗りつぶしを第二引数のセルと同じにしてやれば反応する』
とはどのような意味、手順でしょうか。

回答
投稿日時: 19/11/26 17:40:03
投稿者: んなっと

●二枚目のシート
 
      B      C
2   開始日  非稼働日
3 2019/11/1  2019/11/2
4       2019/11/3
5       2019/11/8
6       2019/11/9
7       2019/11/10
 
●一枚目のシート
 
     A B   C   D   E   F   G   H   I   J   K   L   M
 4       11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5        金  土  日  月  火  水  木  金  土   日   月
 6  工程1   125                               
 7  工程2    80        125                      
 8  工程3    70        80  125                   
 9  工程4             70  80  125                
10  工程5                70  80  125             
11  工程6                   70  80            125
12  工程7                      70            80
13                                        
14                                        
15                                        
16 P工程1    30                               
17 P工程2    50        30                      
18 P工程3    45        50  30                   
19 P工程4             45  50  30                
20 P工程5                45  50  30             
 
D7
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C:$C,TEXT(ROW()-NETWORKDAYS($C$4,D$4,非稼働日)+1,"[>"&LOOKUP(10^9,ROW($A$5:$A6)/(LEFT($A$5:$A6)<>LEFT($A7)))&"]0;;"))&""),""))
右方向・→下方向・↓

回答
投稿日時: 19/11/27 10:12:18
投稿者: んなっと

土日も関係なく、ただ非稼働日にある日付だけ休みにするなら
 
      B     C
3 2019/11/1 2019/11/4
4       2019/11/5
5       2019/11/8
 
 
     A B   C   D   E   F   G   H   I   J   K   L   M
 4       11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5        金  土  日  月  火  水  木  金  土   日   月
 6  工程1   125                               
 7  工程2    80  125                            
 8  工程3    70  80  125                         
 9  工程4       70  80        125                
10  工程5          70        80  125             
11  工程6                   70  80     125       
12  工程7                      70     80  125    
13                                        
14                                        
15                                        
16 P工程1    30                               
17 P工程2    50  30                            
18 P工程3    45  50  30                         
19 P工程4       45  50        30                
20 P工程5          45        50  30             
 
 
D7
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C:$C,TEXT(ROW()-D$4+$C$4+SUMPRODUCT(COUNTIF(非稼働日,$C$4:D$4)),"[>"&LOOKUP(10^9,ROW($A$5:$A6)/(LEFT($A$5:$A6)<>LEFT($A7)))&"]0;;"))&""),""))

投稿日時: 19/11/27 11:53:56
投稿者: ナナカマド

んなっと さんの引用:
土日も関係なく、ただ非稼働日にある日付だけ休みにするなら
 
      B     C
3 2019/11/1 2019/11/4
4       2019/11/5
5       2019/11/8
 
 
     A B   C   D   E   F   G   H   I   J   K   L   M
 4       11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5        金  土  日  月  火  水  木  金  土   日   月
 6  工程1   125                               
 7  工程2    80  125                            
 8  工程3    70  80  125                         
 9  工程4       70  80        125                
10  工程5          70        80  125             
11  工程6                   70  80     125       
12  工程7                      70     80  125    
13                                        
14                                        
15                                        
16 P工程1    30                               
17 P工程2    50  30                            
18 P工程3    45  50  30                         
19 P工程4       45  50        30                
20 P工程5          45        50  30             
 
 
D7
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C:$C,TEXT(ROW()-D$4+$C$4+SUMPRODUCT(COUNTIF(非稼働日,$C$4:D$4)),"[>"&LOOKUP(10^9,ROW($A$5:$A6)/(LEFT($A$5:$A6)<>LEFT($A7)))&"]0;;"))&""),""))

 
少し行を追加しました
6行目に素材入荷の会社名
7行目に部品名です
 
C1〜入れていたVBAと関数は代用方法があったので取り消しました
 
非稼働日に反応するこっちのほうが使いやすいかなと思います
この式が少しわかりにくいのですが「どういう指示を」「どこのセルにしてるのか」をおしえてもらえませんか?
 
またこの式はD7に入ってますがこれを非稼働日の各セルに振っていくということでしょうか?

回答
投稿日時: 19/11/27 12:06:04
投稿者: んなっと

     A B   C   D   E   F   G   H   I   J   K   L   M
 4       11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5        金  土  日  月  火  水  木  金  土   日   月
 6                                        
 7                                        
 8  工程1   125                               
 9  工程2    80  125                            
10  工程3    70  80  125                         
11  工程4       70  80        125                
12  工程5          70        80  125             
13  工程6                   70  80     125       
14  工程7                      70     80  125    
15                                        
16                                        
17                                        
18 P工程1    30                               
19 P工程2    50  30                            
20 P工程3    45  50  30                         
21 P工程4       45  50        30                
22 P工程5          45        50  30             
 
D9
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C:$C,TEXT(ROW()-D$4+$C$4+SUMPRODUCT(COUNTIF(非稼働日,$C$4:D$4)),"[>"&LOOKUP(10^9,ROW($A$5:$A8)/(LEFT($A$5:$A8)<>LEFT($A9)))&"]0;;"))&""),""))
右方向・→下方向・↓とコピーするだけですよ?

投稿日時: 19/11/27 15:56:18
投稿者: ナナカマド

D9に入れてみて横と縦にD9:CU13まで試しに入れてみたのですがうまく反応してくれません
平日までコピー&ペーストしたからでしょうか?
 
試しに平日の関数は元のまま=IF(C8="","",C8) ,
 H8に「個数」5と入れてみたところ
   A    B    C   D   E   F   G   H   I   J   K   L   N 
3            1月
4            1   2   3   4   5  6   7   8   9   10   11
5           水  土  日  月  火  水  木  金   土   日   月
6 会社名 
7 品名
8 入荷                       5
9 工程1                          5
10 工程2                             5
11 工程3      
12 工程4
13 出荷                                       5
 
となってしまいます
 
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C:$C,TEXT(ROW()-D$4+$C$4+SUMPRODUCT(COUNTIF(非稼働日,$C$4:D$4)),"[>"&LOOKUP(10^9,ROW($A$5:$A8)/(LEFT($A$5:$A8)<>LEFT($A9)))&"]0;;"))&""),""))
 
そこで気になったのが
[>"&LOOKUP(10^9,ROW($A$5:$A8)/(LEFT($A$5:$A8)<>LEFT($A9))
 
なぜここでA列を範囲指定しているのでしょうか?
 
よろしくお願いします。

回答
投稿日時: 19/11/27 16:30:17
投稿者: んなっと

違います。最初にC8:C20に以下のように初期条件を入力しておいてください。
また、A列は必ずA8から下に●●1,●●2,●●3...のように最後の数字だけ変化させて入力しましょう。
シートの構成をこちらの回答に合わせてください。時間の無駄につながります。
 
     A B   C   D   E   F   G   H   I   J   K   L   M
 4       11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5        金  土  日  月  火  水  木  金  土   日   月
 6                                        
 7                                        
 8  工程1   125                               
 9  工程2    80                               
10  工程3    70                               
11  工程4                                    
12  工程5                                    
13  工程6                                    
14  工程7                                    
15                                        
16                                        
17                                        
18 P工程1    30                               
19 P工程2    50                               
20 P工程3    45                               
21 P工程4                                    
22 P工程5                                    

回答
投稿日時: 19/11/27 19:43:46
投稿者: YuuSa

んと?
 
やり方変更につき色の部分の説明は要らないということかな?
そちらは省略しますね。
 
連続した稼働日のみのシート

	1	2	3	4
工程1	125			
工程2	80	125		
工程3	70	80	125	
工程4		70	80	125
工程5			70	80
工程6				70

これだと自分で数式は組めますか?
 
非稼働日込みにしてみるシート
月	1					
日	1	2	3	4	5	6
稼働日	1			2	3	4
工程1	125					
工程2	80			125		
工程3	70			80	125	
工程4				70	80	125
工程5					70	80
工程6						70

さて、非稼働日の時上記のように連続した稼働日の日数を計算することは出来ますが?
IF関数で非稼働日の判断は出来ますよね。
後は、自分のセルの左側の範囲に数値が何個並んでいるか数えればいいのです。
数値の個数はcount関数で数えられます。
 
後は、hlookupかindex、match関数の組み合わせで、
連続する稼働日のシートから検索してやれば良いですよ。

回答
投稿日時: 19/11/28 10:49:03
投稿者: んなっと

引用:
2. B4に数字(製造個数)を入力したら、C5,D6,E7,F8,G9,H10,I11,に同じ数字が入力されるように
  =IF(B4="","",B4)
  と関数を入れていきました。(階段状になります)
   
↑2と3は製造固定は物によって数が変わるので6〜9行をセットにして下に複数コピペをしました

ということなのでA列に工程1,工程2,...、間をあけてP工程1,P工程2,...と2種類製造工程を並べました。
 
     A   B   C   D   E   F   G   H   I   J   K   L
 1     11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 2   11  金  土  日  月  火  水  木  金  土   日   月
 3    1                                  
 4  工程1  125                               
 5  工程2  80        125                      
 6  工程3  70        80  125                   
 7  工程4           70  80  125                
 8  工程5              70  80  125             
 9  工程6                 70  80            125
10  工程7                    70            80
11                                      
12                                      
13                                      
14 P工程1  30                               
15 P工程2  50        30                      
16 P工程3  45        50  30                   
17 P工程4           45  50  30                
18 P工程5              45  50  30             
 
 
もしもA列の工程が一種類だけなら、少し簡単になります。
 
    A B   C   D   E   F   G   H   I   J   K   L   M
 4      11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 11/11
 5       金  土  日  月  火  水  木  金  土   日   月
 6       
 7                                       
 8 工程1   125                               
 9 工程2    80  125                            
10 工程3    70  80  125                         
11 工程4       70  80        125                
12 工程5          70        80  125             
13 工程6                   70  80     125       
14 工程7                      70     80  125    
 
D9
=IF(COUNTIF(非稼働日,D$4),"",IFERROR(--(INDEX($C$8:$C$14,TEXT(ROW(D1)-D$4+$C$4+SUMPRODUCT(COUNTIF(非稼働日,$C$4:D$4))+1,"0;;;"))&""),""))
右方向・→下方向・↓
 
それからもう一つ。
引用:
2. B4に数字(製造個数)を入力したら、C5,D6,E7,F8,G9,H10,I11,に同じ数字が入力されるように
  =IF(B4="","",B4)
  と関数を入れていきました。(階段状になります)

とあるので、B列に初期条件を入力する前提のはずですよね?
にもかかわらず、
引用:
H8に「個数」5と入れてみたところ
(省略)
となってしまいます。

...H8に数値を入力してはだめです。H8には関数しか入れてはいけません。わかりますよね?

トピックに返信