Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Home Premium : Excel 2013)
ガントチャート
投稿日時: 18/02/28 09:37:38
投稿者: タカヒロ

エクセル管理でガントチャートを作成しました。
単位は作業時間(時、分)です。
作業時間帯(作業開始、作業終了)に色を塗っていくようにしています。
24時間管理で5分刻みで表示したいと思っていますが、
深夜0:00以降が上手く表示できません(24H表示にて入力)。
1:00、2:00・・・のように入力すると表示が出来なくなります。
仕方がないので23:00、25:00のような(下記の条件入力で)
記載方法で表現をしようとしていますが、1:00、2:00〜のような
分かり易い表現(入力で記載したと思っています。)
 
 
エクセルの上に時間帯、左枠に作業開始時間、終了時間を記載し、
色塗りの時間帯枠(ガントチャートグラフ部)には条件付き書式で指定範囲
=$J$9:$KL$849
塗り潰し条件を
=AND($B9+TIME(0,1,0)<K$7,J$7<$C9,$H9="PC登録作業")
としました。
+time(0,1,0)の個所が23:59までの範囲しか使用できないと知り
関数を試してみましたがよくわかりません。
 
教えていただけませんでしょうか?

回答
投稿日時: 18/02/28 10:37:18
投稿者: 細雪

ご質問のジャンルで有効かどうかは確かめていないですが、
日本語で言う「時間」を表示するには、
表示形式「[h]:mm」で指定します。
 
h:mm は「時刻」。
日本語的には明確に違う言葉ですね。
 

回答
投稿日時: 18/02/28 12:14:58
投稿者: Suzu

引用:
仕方がないので23:00、25:00のような(下記の条件入力で)
記載方法で表現をしようとしていますが、1:00、2:00〜のような
分かり易い表現(入力で記載したと思っています。)

 
具体的に、
・キーボードでは、どう入力し
・画面表示では、どう表示させたい
 
のでしょうか。
 
 
引用:
色塗りの時間帯枠(ガントチャートグラフ部)には条件付き書式で指定範囲
=$J$9:$KL$849
塗り潰し条件を
=AND($B9+TIME(0,1,0)<K$7,J$7<$C9,$H9="PC登録作業")
としました。

 
セル範囲の説明もなく、式のみを提示されても、内容は理解できませんよ。
 
 
引用:
+time(0,1,0)の個所が23:59までの範囲しか使用できないと知り
関数を試してみましたがよくわかりません。

 
これが最初の【23:00、25:00のような(下記の条件入力で)】とつながるのでしょうか?
数式の中では、≪0:01≫を数式の中で固定で指定していますよね?
 固定と言うのは、ユーザーがセルに値を入力し、そのセルの値を参照させている訳では無い と言う意味です。
繋がりが見えません。。
ユーザーに入力させたセルを参照させたいという事でしょうか??

回答
投稿日時: 18/02/28 13:23:37
投稿者: んなっと

必ず具体例と望んでいる結果を添えて質問してください。
以下のように。
 
【質問例】
下の←の2行のように日付をまたいだ作業の場合、
「必ず同じ行」の先頭と末尾に分けて色付けしたいのです。
どうすればよいでしょうか。
 
    B   C    J   K   L   M   N   KG   KH   KI   KJ   KK  KL
 7          0:00 0:05 0:10 0:15 0:20 23:35 23:40 23:45 23:50 23:55 24:00
 8                                            
 9  0:05  0:20       ■  ■  ■                        
10 23:40 23:50                       ■   ■          
11 23:45  0:00                          ■   ■   ■   
12 23:45  0:05    ■                      ■   ■   ■  ■ ←
13 23:55  0:10    ■  ■                          ■  ■ ←
 
J9:KL849選択→条件付き書式→数式
=AND(OR(($B9+"0:01"<K$7+{0,1})*(J$7+{0,1}+"0:01"<$C9+($B9>$C9))),$H9="PC登録作業")

投稿日時: 18/03/14 10:45:15
投稿者: タカヒロ

返信が遅くなりまして申し訳ありませんでした。
内容をしたのように記載しましたが情報が醜くくて申し訳ありません。
お手数ですが、PCのメモ帳にコピーしたものを貼り付けていただければ分かり易くなると思います。
よろしくお願い致します。
 
 
 
 
1 A,       B,               C,              D,               E,       F,                     G,            H,   I,    J,  
2         工程名
3          作業 年 月 日
4
5                                                                時                     8                 
6※(この行は非表示設定)                                                                              2018/03/01/08:30 =G6+TIME(0,5,0) =H6+TIME(0,5,0) =I6+TIME(0,5,0)
7 作業者名     作業開始時間         作業終了時間           時間※(作業時間を表示)     作業名          分                     =minute(G6)     =minute(H6) =minute(I6) =minute(J6)
8 ※(作業者名を記入) ※(作業開始時間を入力)  ※(作業開始時間を入力)    =IF(C8-B8>0,C8-B8,C8-B8-B8+1)       ※(作業名を記入)   ※(F列は上記の『時』『分』以外は空欄)
9 ※ 伊達直人       8:35          8:40              0:05                   PC入力                                            ■←(作業時間の範囲を表しているセルの範囲を横棒グラフ状に塗り潰し※(セル内は空欄))                               
10※(作業者名を記入) ※(作業開始時間を入力)  ※(作業開始時間を入力)    =IF(C10-B10>0,C10-B10,C10-B10-B10+1)    ※(作業名を記入)                                        







                                                                                                                                                                  ※(補足説明)(G8セル〜KI851セル迄 空欄。
                                                                                      エクセルの『条件付き書式』設定で作業時間内の範囲のセルを塗りつぶします。
                                                                                      時間は24時間記録を取り、12時間表示。深夜1:00より塗り潰しができない。⇒TIME関数が11:59までしか使用できないため ※問題点:0:00以降も横棒グラフの作業時間範囲を示すセルの塗り潰しをしたい。
                                                                                      時間の横棒グラフの空欄部分には下記の条件付き書式入力を実施。※(セルに直接、エクセル関数を入れずにエクセル機能の条件付き書式で自動で横棒グラフが 各作業を色別で 作業範囲内を表示するように作成した。しかし、朝(8:30)から11:59までのは使用できたが、0:00〜8:30の範囲がまったく表示できない。)
                                                                                        ・条件付き書式ルールの管理 :書式ルールの表示(s):現在の選択の範囲、 ルール:※手をつけていません。、書式※(各作業工程別に色を決めて設定。エクセルの横棒グラフには各作業名の色で作業時間内のセルを塗り潰して表示)  適用先:=$G$8:$KI$851   ※条件付き書式のルールは複数の作業名ごとに色分けして横棒グラフが表示されるよう同様のルールを複数 新規ルールにて作成する。
                                                                                          
                     
                                                                                        ・書式ルールの編集 : ルールの書類を選択してください(S):⇒数式を使用して、書式設定するセルを決定
                                                                                                    ルールの内容を編集してください(E) :次の数式を満たす場合に値を書式設定(O) AND($B8+TIME(0,1,0)<H$6,G$6<$C8,$E8="PC入力")                     

回答
投稿日時: 18/03/14 13:11:47
投稿者: んなっと

まったく伝わらなかったようなので、重要な部分に色を付けて同じ内容の書き込みをします。
 
必ず具体例と望んでいる結果を添えて質問してください。
以下のように。
  
【質問例】
下の←の2行のように日付をまたいだ作業の場合、
「必ず同じ行」の先頭と末尾に分けて色付けしたいのです。
どうすればよいでしょうか。
  
    B   C    J   K   L   M   N   KG   KH   KI   KJ   KK  KL
 7          0:00 0:05 0:10 0:15 0:20 23:35 23:40 23:45 23:50 23:55 24:00
 8                                            
 9  0:05  0:20       ■  ■  ■                        
10 23:40 23:50                       ■   ■          
11 23:45  0:00                          ■   ■   ■   
12 23:45  0:05    ■                      ■   ■   ■  ■ ←
13 23:55  0:10    ■  ■                          ■  ■ ←
   
J9:KL849選択→条件付き書式→数式
=AND(OR(($B9+"0:01"<K$7+{0,1})*(J$7+{0,1}+"0:01"<$C9+($B9>$C9))),$H9="PC登録作業")

回答
投稿日時: 18/03/14 14:51:30
投稿者: Suzu

引用:
PCのメモ帳にコピーしたものを貼り付けていただければ分かり易くなると思います。

 
なりませんよ。確認してみてください。
 
 
内容(数式)を 解読する気になれません。
 
何となく・・
 
キーボード入力は、28:00
画面表示 は、 4:00
セルの値 は、1900/1/1 4:00:00
 
だとして、
  細雪 さんの 部分と重なりますが、セルの書式設定 で、 [h]:mm
  条件付き書式を使用し
    指定の値を含むセルだけを書式設定
      [セルの値] [次の値以上] [=1] 書式を、 h:mm

回答
投稿日時: 18/03/14 22:08:59
投稿者: んなっと

ここからは質問例ではなく、状況を想像しての回答です。
  
おそらくこんな感じでできるのではないでしょうか。
  
J7に 8:30 と直接入力
  
K7
=J7+"0:05"
右方向・→
   
J9:KL849選択→条件付き書式→数式
=AND($B9+"0:01"+("8:29"-$B9>0)<K$7,J$7+"0:01"<$C9+("8:29"-$C9>0),$H9="PC登録作業")
   
 6   B   C     J   K   L   GJ   GK   GL   GM   GN   GO
 7          8:30 8:35 8:40 23:40 23:45 23:50 23:55 24:00 24:05
 8                                        
 9  8:30  8:40    ■  ■                         
10 23:40 23:50              ■   ■              
11 23:45  0:00                  ■   ■   ■       
12 23:45  0:05                  ■   ■   ■   ■    
13 23:55  0:10                         ■   ■   ■
14 8:00  8:10
      ↑これなら24:10とか32:10と入力する必要はない。

投稿日時: 18/03/15 08:28:17
投稿者: タカヒロ

 
 
なんどもすみません。
表の下2つの矢印の横棒グラフで表示をしたいと考えています。
表中のセルの色塗りはB列、C列、E列の情報を条件付き書式で反映させたいです。
よろしくお願い致します。
 
 
 
 
 
                                   夜勤(日付をまたいで表は続く)
                                   ↓  
 6   B   C  E   G   H   I   GG   GH   GI   GJ   GK   GL   GW   GX
 7           8:30 8:35 8:40 23:40 23:45 23:50 23:55  0:00  0:05  1:00 1:05
 8                                        
 9  8:30  8:40 A作業 ■  ■                         
10 23:40 23:50 B作業          ■   ■              
11 23:45  0:00 C作業              ■   ■   ■       
12 23:45  0:05 D作業              ■   ■   ■   ■   ■ ←  
13 23:55  0:10 E作業                   ■   ■    ■ ←
14 23:50 1:05 PC入力 ■ ■   ■    ■ ■ ■ ←
      

回答
投稿日時: 18/03/15 08:50:15
投稿者: んなっと

簡単ですよ。
手順の最初の部分を
 
K7
=J7+"0:05"
書式 表示形式 ユーザー定義
[h]:mm
右方向・→
 
とするだけです。

回答
投稿日時: 18/03/15 14:29:45
投稿者: んなっと

下のような「具体例」の方がはるかにわかりやすいですよね。
 
                      夜勤(日付をまたいで表は続く)
                                    ↓  
 6   B   C  E   G   H   I   GG   GH   GI   GJ   GK  GL   GW  
 7           8:30 8:35 8:40 23:40 23:45 23:50 23:55 0:00 0:05  1:00
 8                                        
 9  8:30  8:40 A作業 ■  ■                         
10 23:40 23:50 B作業           ■  ■              
11 23:45  0:00 C作業              ■   ■   ■       
12 23:45  0:05 D作業              ■   ■   ■   ■   
13 23:55  0:10 E作業                     ■   ■  ■
14 23:50  1:05 PC入力                  ■   ■   ■  ■   ■  
 
「具体例と望んでいる結果」を要求したとき、
なぜ最初から上のような書き込みをしなかったのですか。
理由を答えてください。

投稿日時: 18/03/16 08:17:44
投稿者: タカヒロ

お世話になります。
最初に絵にしなかったのは写真を貼るなどの手段で表現する発想が、まずありませんでした。
セルの表現の発想が出てきませんでした。
メモ帳に図を描いて完成して貼ったのですが、ご指摘を戴きHPに掲載したものをコピペして確認致しますと
絵がずれていました。
以降、直接HPに記載致しました。
 
が実際に当該ガントチャートは使用しておりまして、『条件付き書式』に使用する関数を使用すると、時間標記が右方向に累積して数字が増さなければ数式が成立しません。
よって、上部分の時間標記を8:30より開始し、日付をまたぐ部分を0:00、1:00、2:00と目で見てわかるものと、エクセル関数上使用する表現として24:00、25:00・・・と記載し、例えば朝の6:00は30:00で関数の数式上仕様しています。
([h]:mmはテストで使用したことがありますが、上手くいきませんでした。条件付き書式の関数式が良くないのかもしれませんが、よくわかりません。)
 
左手の作業開始時間と作業終了時間の記載は上記の通り、例えば23:30〜1:00ですと23:30〜25:00という具合に転記して横棒グラフを作成しています。
2:00〜4:00ですと26:00〜28:00という具合に転記しています。
 

回答
投稿日時: 18/03/16 08:53:26
投稿者: んなっと

具体例があれば...
わざわざ具体例のサンプルまで作成したのに...
 
表示形式、h:mmに修正。
アドレスも最終の形に近づけました。
 
    B   C  G   H   I   J   K  GG   GH   GI   GJ  GK  KG
 7        8:30 8:35 8:40 8:45 8:50 23:40 23:45 23:50 23:55 0:00 8:20
 8                                         
 9  8:30  8:40  ■  ■                            
10 23:40 23:50                 ■   ■             
11 23:45  0:00                    ■   ■   ■      
12 23:45  0:05                    ■   ■   ■  ■   
13  8:20  8:25                                 ■
 
G7に 8:30 と直接入力
 
H7
=G7+"0:05"
表示形式 ユーザー定義
h:mm
右方向・→
 
G9:KH849選択→条件付き書式→数式
=AND($B9+"0:01"+("8:29"-$B9>0)<H$7,G$7+"0:01"<$C9+("8:29"-$C9>0))

投稿日時: 18/03/17 04:13:02
投稿者: タカヒロ

 
投稿者: Suzuさま
 
お世話になります。
もともと引用して作ったものなので、内容を理解していませんでした。
当初の質問内容に
『塗り潰し条件を
=AND($B9+TIME(0,1,0)<K$7,J$7<$C9,$H9="PC登録作業") 』
と記載していました。
 
下の図に改めさせてもらいますと
=AND($B9+TIME(0,1,0)<H$6,G$6<$C9,$E9="PC登録作業")
となります。
 
当初意味合いがよくわかりませんでした。
上記の式中の$B9+TIME(0,1,0)<H$6の
部分は時間の境を6行目に記載されている表示より
1分足すことによって8:31〜8:35、8:36〜8:40と
いうような時間のくくりにしたい意図があるとわかりました。
 
よって、先にんなっと様が記載されました『+TIME(0,1,0)』『+"0:01"』に
置き換えて記載することによって解決できます。
 
 
※(実際は日付がかわりますが、勤務上(表では)は8:30以降翌日の7:00まで連続した管理になります。)                                        |
             ↓―(1日のはじまり)                   ↓
    B   C  E  G   H   I   J   K  GG   GH   GI   GJ  GK  KG
 6        8:30 8:35 8:40 8:45 8:50 23:40 23:45 23:50 23:55 0:00 8:20
 7
 8                                         
 9  8:30  8:40 PC入力 ■  ■                            
10 23:40 23:50 A作業                ■   ■  ■          
11 23:45  0:00 B作業                     ■  ■   ■  ■   
12 23:45  0:05 C作業                     ■   ■  ■  ■   
13  8:20  8:25 D作業  ■
 
 
 
−−−−−−−−−−−−−−−−−−−−−−−
引用:
+time(0,1,0)の個所が23:59までの範囲しか使用できないと知り
関数を試してみましたがよくわかりません。
 
  
これが最初の【23:00、25:00のような(下記の条件入力で)】とつながるのでしょうか?
数式の中では、≪0:01≫を数式の中で固定で指定していますよね?
 固定と言うのは、ユーザーがセルに値を入力し、そのセルの値を参照させている訳では無い と言う意味です。
繋がりが見えません。。
ユーザーに入力させたセルを参照させたいという事でしょうか??
 
⇒上表中でのB,C列はユーザーの記載項目になります。E列はリスト選択項目です。
   G9〜KI852まではエクセルのセルに枠線を描いているだけで
 エクセルの機能である『条件付き書式』に範囲をG9〜KI852、各作業別に色設定をして
 書式設定のルールを『数式を仕様して書式設定をするセルを設定』
 にして、ルールの内容の編集の欄に『=AND($B9+TIME(0,1,0)<H$6,G$6<$C9,$E9="PC登録作業")』
と入力するものでした。
 この条件付き書式の条件によって、ユーザが記載したB、C、E列の条件に応じて G9〜KI852の枠のセルが上の時間区分の標記の範囲分だけ横棒グラフ状に各作業内容の色分けで塗りつぶさるようになります。
 
 元々のものですと8:30〜23:59までは問題なくそのまま表示されます。
 
(質問内容)
@B,C,E列のユーザーの記載内容を反映させたガントチャートを作成させたい。
 (6行目は時間区分を記載。G6〜KI6)
Aガントチャートは8:30〜(翌)7:00の範囲。0:00をまたいで横棒グラフが作れるものを
 つくりたい。

 

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

回答
投稿日時: 18/03/17 08:02:52
投稿者: んなっと

今度は5分刻みの時刻が6行目に変わっている...
故意にやっているのでしょうか。
 
    B   C   G   H   I   J   K   L   M   N   O   P   Q
 6 ←どっち?    8:30 8:35 8:40 8:45 8:50 8:55 23:45 23:50 23:55 0:00 8:20
 7 ←どっち?
 8                                          
 9  8:30  8:40   ■  ■                             
10 23:40 23:50                  ■   ■             
11 23:45  0:00                     ■   ■   ■      
12 23:45  0:05                     ■   ■   ■  ■   
13  8:20  8:25                                  ■
 
 
G6に 8:30 と直接入力(時刻のみで、絶対に日付は入れない)
  
H6
=G6+"0:05"
表示形式 ユーザー定義
h:mm
右方向・→
 
G9 条件付き書式→数式
=AND($B9+"0:01"+("8:29"-$B9>0)<H$6,G$6+"0:01"<$C9+("8:29"-$C9>0),$E9="PC登録作業")

回答
投稿日時: 18/03/17 11:48:43
投稿者: んなっと

引用:
もともと引用して作ったものなので、内容を理解していませんでした。

なるほど。
だから質問文に自信を持てなくて、質問のしかたの不備を指摘されると
「(現在の式なら)問題なくそのまま表示されます」
「(ほかのどなたか)よろしくお願い致します」としか返せないんですね。

投稿日時: 18/03/19 08:04:55
投稿者: タカヒロ

お世話になります。

引用:
今度は5分刻みの時刻が6行目に変わっている...
故意にやっているのでしょうか。

 
すみません。
6行目の時間はエクセル関数上に使用している時間でした。7行目は24時間表示の際に時間を変換したものを
分かり易くする為の行でした。6行目は非表示にしていた為、気がつきませんでした。

回答
投稿日時: 18/03/19 11:05:53
投稿者: んなっと

とにかく新規ブックで試してみてください。

投稿日時: 18/03/20 09:07:51
投稿者: タカヒロ

んなっと様
 
お世話になります。
教えていただいた関数で問題が解決いたしました。
分かりにくい質問内容で申し訳ありませんでした。
ありがとうございました。