[[20210611222728]]　『ガントチャート（日付の色付け）』（syou）　ページの最後に飛ぶ

『ガントチャート（日付の色付け）』（syou）

初心者です。
ガントチャートを作成したいのですが、うまくいきません。

表１

  　A　　　　 B 　　　　 C  　　　D 　　 　　E　　　　F        G
1 
2 
3 注文番号　 工程1    分類　　　工程2    　分類  　　工程3   分類       
4 A〇〇〇 　2021/5/20   1      2021/5/24　　1      2021/5/24   2  
5 A×××　 2021/5/20   1      2021/5/21    1      2021/5/21   1  
6 A△△△ 　2021/5/20   1      2021/5/26    1      2021/6/1  　1
　
　　日付の元のデータでは時間まで表示されています

表２

    H   I   J   K   L   M   N   O   P   Q   R   S   T
1   5   5   5   5   5   5   5   5   5   5   5   5   6  (月)
2  20  21  22  23  24  25  26  27  28  29  30  31   1　(日)
3  木  金  土  日  月  火  水  木  金  土  日  月  火　(曜日)
4
5
6

・表1と表2は同じシート内にあり実際は繋がっています
・表1の年月日は別の表から関数（INDEXとSUMPRODUCT）で持ってきた日付（ネット情報の見よう見まねですが）
・注文番号ごと、一行に色分けした各工程名（工程1、工程2、工程3）を、表1と表2で一致した日付の列に表示したい
・表2には条件付き書式の「指定の値を含む書式設定」で、
　各工程名を色分けして、セルに各工程名があれば色がつく設定。
・表2の4行目からのセルには、=IF(B4=H2,"工程1","") こんな感じに入れています

色を付けて表示したい、ですがまず表1の日付の何かが違うのか一致しません。
表1に手入力で関数で出した同じ日付を入れると、表2の該当するセルに色がついた工程名が表示されます。
この色付け設定では無理でしょうか。
どこをどのように変えるといいのでしょうか。

一応全文検索をしましたが、見つけられなかったので質問させていただきます。
よろしくお願いします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

手打ちの日付では希望の結果が得られるとのことですから
「別の表から関数（INDEXとSUMPRODUCT）で持ってきた日付」に問題があります。
セルの表示形式に特段の設定がなければ手打ちの日付ならセルの値はシリアル値ですが、
関数で持ってきた日付はシリアル値になっていますか。文字列になっていませんか。
（きまぐれおじさん） 2021/06/12(土) 02:17

きまぐれおじさん様

返信ありがとうございます。

日付の表示形式は「別の表から関数（INDEXとSUMPRODUCT）で持ってきた日付」は日付になっています。
（一応...元データの方はユーザー定義でyyyy/m/d h:mm　が選択されています）
ISTEXT関数で調べると、FALSEでした。

日付の表示形式を数値に変えてみたところ、
5/20の場合、
「別の表から関数（INDEXとSUMPRODUCT）で持ってきた日付」が 44337、
表2の日付　が　44336　と表示されました。
これのせいでしょうか？
（syou） 2021/06/12(土) 09:01

 お邪魔します。。。
後ろの　h:mm　を消してみてはどうでしょうか？
yyyy/m/d h:mm

 あっ表示だけ変えてもだめっぽいですね。。
もう整数になっているので問題ないとおもいますが、、
INTでくくってみてはどうでしょうか？
（SoulMan） 2021/06/12(土) 09:49

 すみません。全然、違いますね(^^;
5/20の場合、4436　が正解で
4437　は、小数点以下が丸まって繰り上がっているのでしょうね？
なので　関数で持ってきた　4437　をINTでくくって　4436　になればいいのかもしれませんね。。。
（SoulMan） 2021/06/12(土) 10:27

SoulMan様

ご返信ありがとうございます。
INTでくくったところ色付きで表示されました！
解決しました。

ありがとうございました！
（syou） 2021/06/12(土) 12:13

先ほどご回答いただいたお二方大変ありがとうございました。

新たに質問した方がよいのかもしれませんが…

表1の工程名、表2と一致する日付が複数ある場合について質問です。
IF関数やCOUNTIF関数、MAX関数など使うと出来るかと思いましたが閃かず悩んでいます。

・表2の日付との一致数が1個　→　表2のその日付列に工程名を表示する。
　2個以上　→　重複する日付の中でセルが一番右側にある工程名を表示する。
　（工程が5つある中で、工程2 と工程3 の日付が重複する場合、工程3を表示したいです)

お知恵をお貸しください。
よろしくお願いします。

（syou） 2021/06/12(土) 18:18

 ちょっと確認

 1）最初の質問で INT 関数を使ったのは、表1の INDEX の式？
 2）表2の2行目はシリアル値？（なんで月と日を分けてるのか知りませんけど）

 だとして･･･

 H4 =IFERROR(LOOKUP(1,0/($B4:$F4=H$2),$B$3:$F$3),"")
 右・下コピー

 ＞工程が5つある中で
 3つでは？

 ところで
 ＞INDEXとSUMPRODUCT
 これは具体的にどんな式なんですか？

 以上
（笑） 2021/06/12(土) 20:14

 補足

 上の LOOKUP の式は、表1の「分類」の列(C列とE列）に「5桁の数値」は入らないことを前提にしています。

 以上
（笑） 2021/06/12(土) 21:34

（笑）様

返信ありがとうございます。

教えていただいた方法で表示されました！
ありがとうございました。

前提の件は承知しました。
今のところ入る場合でも１桁のみだと思います。

 1）最初の質問で INT 関数を使ったのは、表1の INDEX の式？
＞INDEXとSUMPRODUCT
 これは具体的にどんな式なんですか？

INTはINDEXの式の頭につけました。
一部変更しましたが、このように式を入れています。

=INT(IF(OR($O7=0,$F7=""),"",INDEX(Sheet3!$H:$H,SUMPRODUCT((テーブル1[[#すべて],[注文番号]]?$F7)*(テーブル1[[#すべて],[分類]]?=$O7)*(テーブル1[[#すべて],[工程]]?=$N$6),ROW(Sheet3!$H:$H)))

参考元はこちらです。
https://whiteleia.com/index%E9%96%A2%E6%95%B0-match%E9%96%A2%E6%95%B0-%E8%A4%87%E6%95%B0%E6%9D%A1%E4%BB%B6-2/

質問で載せている表1、表2は実際は長い為、
言葉や質問には不要な部分をカット、変更し縮めたものです。

 2）表2の2行目はシリアル値？（なんで月と日を分けてるのか知りませんけど）

ISNUMBER関数ではTRUEと出ます。
なぜ月と日を分けているのかは、元々表を作成したのは自分ではない為分かりません。
その方が作成者の使い勝手が良いのかもしれません。

 ＞工程が5つある中で
 3つでは？

例えの一つとして挙げましたが余計でした。申し訳ありません。

（syou） 2021/06/12(土) 23:07

INDEXの式に？が入っていますが、Excelの数式バーでは？は入っていません。
（syou） 2021/06/12(土) 23:11

 ■INDEXとSUMPRODUCTの式について
 参考元を見ました。 ↓ の式ですね。
 =INDEX($D$1:$D$7,SUMPRODUCT(($A$2:$A$7=F2)*($B$2:$B$7=G2)*($C$2:$C$7=H2),ROW($A$2:$A$7)))

 これは条件に該当するものが元表に「1つ（1行）しかない」というのが大前提。
 それに加えて、戻り値が数値（日付＋時刻を含む）なら SUMIFS の方がいいと思います。

 =SUMIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,G2,$C$2:$C$7,H2)
 =SUMIFS(D:D,A:A,F2,B:B,G2,C:C,H2)

 ＞=INT(IF(OR($O7=0,$F7=""),"",INDEX(Sheet3!$H:$H,……

  　　　　　　　　INT は ↓ につけた方がいいでしょう
 =IF(OR($O7=0,$F7=""),"",INT(INDEX(Sheet3!$H:$H,……

 SUMIFSでも同じ
 =IF(OR($O7=0,$F7=""),"",INT(SUMIFS(……

 ■シリアル値について
 ＞ISNUMBER関数ではTRUEと出ます。
 これだけではシリアル値とは言い切れません。

 表2の2行目
 ・単に「20」と入力しているだけ
 ・「2021/5/20」と入力し、表示形式～ユーザー定義を d にしている

 どっちなのかを知りたかったんですが、ISNUMBER ではどちらも TRUE になります。 
 一度「Excel 日付 シリアル値」等でネット検索し、シリアル値について調べてみてください。

 簡単に言うと「2021/5/20」と「44336」は同じもの。
 分類の列に「5桁の数値」が入ることを想定していないのはそのためです（意味わかりますか？）

 以上、参考まで
（笑） 2021/06/13(日) 12:00

 ＞それに加えて、戻り値が数値（日付＋時刻を含む）なら SUMIFS の方がいいと思います。

 2019なら MAXIFS の方がいいかも

 参考元の例で言うと
 =MAXIFS($D$2:$D$10,$A$2:$A$10,F2,$B$2:$B$10,G2,$C$2:$C$10,H2)
 =MAXIFS(D:D,A:A,F2,B:B,G2,C:C,H2)

 時刻を切り捨てる場合は INT を使ってください。

 参考まで
（笑） 2021/06/13(日) 14:27

（笑）様

返信ありがとうございます。
返事が遅くなり申し訳ありません。

■INDEXとSUMPRODUCTの式について

なるほど…
SUMIFSでも同じ答えが返ってきました。
INTの位置もSUMIFSの頭に変更し、色も問題なく表示されました。
数値の合計を求める関数でも、今回は条件に合致する値はひとつなので結局同じになりますね。

 ■シリアル値について

 ＞ISNUMBER関数ではTRUEと出ます。
 これだけではシリアル値とは言い切れません。
 表2の2行目
 ・単に「20」と入力しているだけ
 ・「2021/5/20」と入力し、表示形式～ユーザー定義を d にしている

勉強不足で申し訳ありません。
日付も時刻も、もともとシリアル値で管理されているのですね。
表2の2行目は後者の、表示形式～ユーザー定義を d にしている　です。
1行目、3行目もユーザー定義で m と aaa です。
分類列には 1～9 の数値が入り、常に標準表示です。操作ミス等の間違いがなければですが…

自分が普段使用するパソコンは2019なのですが、
2016の方で使用する可能性があるので、SUMIFSにします。

丁寧に解説していただき本当にありがとうございます。

（syou） 2021/06/13(日) 18:43

コメント返信：

[ 一覧(最新更新順) ]

[[20210611222728]] 『ガントチャート（日付の色付け）』（syou） ページの最後に飛ぶ

[[20210611222728]]　『ガントチャート（日付の色付け）』（syou）　ページの最後に飛ぶ