[[20210621091512]]　『INDEX関数の日付の空白（スケジュールチャートの香x（AKKO）　ページの最後に飛ぶ

『INDEX関数の日付の空白（スケジュールチャートの作成）』（AKKO）

関数レベルがまだまだで苦戦しています。
どなたかご教授よろしくお願いいたします。

スケジュールチャート表を作りたいです。

シートは「入力シート」と「1か月」の2枚です

「入力シート」は左から
作業列1,集計月,作業列2,項番,内容,担当,予定in,予定out,実績in,実績outです
「1か月」は左から
集計月,項番,内容,担当,予定in,予定out,実績in,実績out,1日,2日,3日,4日...

全体のつくりは入力シートに入力し、
1か月のシートのA1に2021、B1に7を入力するとINDEX関数で
日付のどれか一つでも7月日付が入っているものを取り出すようにしています。
あとは条件付き書式で右側の日付の欄に色がつくようチャート表示にしています。

問題点は
「1か月」シートのG4に
=IF(,"",IF(COUNT(入力シート!$B:$B)<ROW($B1),"",INDEX(入力シート!I:I,SMALL(入力シート!$B:$B,ROW($B1)))))
とすると日付が入っている場合は正しく表示されますが、
空欄の場合「1/0」となってしまします。
これを空白にしたくて&""をつけると今度は空欄の場合空欄になるのですが
日付が入っていると「44383」といったシリアル値でしか表示されません。

日付がある場合は日付を、空欄の場合は空欄にしたいです。

補足
「入力シート」
作業列1：すべての日付が空白の行を対象外とする作業
予定in,予定out,実績in,実績outどれか一つにでも日付が入っている場合「1」となります。
集計月：
予定in,予定out,実績in,実績outのAverageで対象月を割り出しています
作業列2：INDEX抽出用
集計月が「1か月」シートの集計月と合致する場合行Noを返しています。
=IF(OR('1行 (1か月)'!$B$1="",MONTH(B12)<>'1行 (1か月)'!$B$1),"",ROW())

作業列3つも入れてスマートではないですが
とりあえず正しい日付と空白が返せればと思っています。
Officeは365なのでFILTER 関数の方がきれいにできるならもちろんそれでもかまいません。

どうぞよろしくお願いいたします。

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

 =IFERROR((式&"")*1,"")
 ではどうだろうか？
 文字列の数字は1をかけると数値に戻り、""は1をかけるとエラーになるのでIFERROR関数で""が返る。
（ねむねむ） 2021/06/21(月) 10:29

 ちょっと確認だけ

 ＞=IF(,"",IF(COUNT(入力シート!$B:$B)<ROW($B1),"",INDEX(入力シート!I:I,SMALL(入力シート!$B:$B,ROW($B1)))))

 頭の部分、IF(,"",　の意味がわかりませんので無視します。
 空白が返ってくること自体がそもそもおかしいのでは？

 入力シートのB列、I列にはそれぞれ何が入力されてるんですか？

 ＞作業列2：INDEX抽出用
 ＞=IF(OR('1行 (1か月)'!$B$1="",MONTH(B12)<>'1行 (1か月)'!$B$1),"",ROW())

 この式には問題がありますが、それもちょっと置いといて
 これを入力シートのどの列に入れてるんですか？

  ＞Averageで対象月を割り出しています
 これも意味がわかりません。

 今のやり方にこだわらないのなら
 たぶん FILTER 関数でできるとは思いますけど･･･

 以上
（笑） 2021/06/21(月) 11:49

（笑）さん（ねむねむ）さん

ありがとうございます。

（ねむねむ）さんの =IFERROR((式&"")*1,"")はやってみましたが、
日付が入っているところも空欄になってしまいました。
すみません、 =IFERRORの関数もしっかり理解しきれていないので
もう一度自分で調べてから確認します。

（笑）さん

 ＞＞=IF(,"",IF(COUNT(入力シート!$B:$B)<ROW($B1),"",INDEX(入力シート!I:I,SMALL(入力シート!$B:$B,ROW($B1)))))
 ＞頭の部分、IF(,"",　の意味がわかりませんので無視します。
 ＞空白が返ってくること自体がそもそもおかしいのでは？
 ＞入力シートのB列、I列にはそれぞれ何が入力されてるんですか？

IF(,"",　は無視してください。
入力シートのB列は「集計月」、I列は「実績in」です。

 ＞＞作業列2：INDEX抽出用
 ＞＞=IF(OR('1行 (1か月)'!$B$1="",MONTH(B12)<>'1行 (1か月)'!$B$1),"",ROW())
 ＞ この式には問題がありますが、それもちょっと置いといて
 ＞これを入力シートのどの列に入れてるんですか？

C列です。（C12）

 ＞＞Averageで対象月を割り出しています
 ＞これも意味がわかりません。

説明が足りずすみません。

空白を空白で返す話とは少しそれます。

そもそもなぜこんなややこしいことになったのか。
予定in,予定out,実績in,実績outの日付は入っていたり空欄だったりまちまちです。
「内容」と「担当」だけ入っていて日付は全部空欄の行もあります。

どれか一つだけでも7月の日付がある場合その行を7月で抽出したかった。
(A)予定in7/1,予定out7/10→7月で抽出
(B)予定in7/1,予定out7/10,実績in7/7,実績out7/16→7月で抽出
(C)予定in7/1,予定out7/10,実績in7/25,実績out8/10→7月でも8月でも抽出

けどそれが調べてもわからなかったので妥協案としてB列で日付をAverageにした。
(A)7/5　(B)7/8　(C))7/19
この時点で(C)を8月で抽出することは一旦断念しました。
これがAverageにした理由です。

しかし、自分で書いてみて今気づきました。
予定in7/20,予定out9/10→8月になってしまいますね･･･(>_<)
これも失敗です。

この日付の部分で苦戦しているのでINDEX関数,FILTER関数どちらにしろ
複雑になってしまいあちこちでエラーが出てしまいます。

何かいい手立てはないでしょうか。

（AKKO） 2021/06/21(月) 18:05

 「作業列2」の式はC列とのことですけど
 INDEXの式でC列を参照していないのはなぜ？

 具体的な値を入れた表形式で説明してくれませんか？

 シート名がよくわからないので、「1か月」シートは Sheet2 だとして

 ■入力シート
	G	H	I	J
11	予in	予out	実in	実out
12	6/14	6/17		
13	6/30	7/10	7/7	7/16
14	7/17	7/23		
15	7/24	7/27	7/31	8/10
16	9/4	9/10		
17

 ■Sheet2
 　A1が「2021」、B1が「7」だったら ↓ のようにしたいってこと？

	A	B	C	D	E	F	G	H	
1	2021	7							
2									
3					予in	予out	実in	実out	
4					6/30	7/10	7/7	7/16	
5					7/17	7/23			←空白にしたい！！
6					7/24	7/27	7/31	8/10	
7									
8

 入力シートのB列を作業列
 B13 =IF(SUMPRODUCT(N(TEXT(G13:J13,"yyyy-m;;")=Sheet2!$A$1&-Sheet2!$B$1)),ROW(),"")
 下コピー
 ※365なら SUMPRODUCT → SUM でいいかも

 ねむねむさん回答の　=IFERROR((式&"")*1,"")　というのは ↓ なこと
 Sheet2
 E4 =IFERROR((INDEX(入力シート!G:G,SMALL(入力シート!$B:$B,ROW(A1)))&"")*1,"")
 表示形式「日付」で右・下コピー

 以上、参考まで
（笑） 2021/06/21(月) 23:17

 補足

 たぶん FILTER 関数でもできると思います。

 ついでに･･･
 ＞空欄の場合「1/0」となってしまします。

 E4セルから、表示形式～ユーザー定義　m/d;;　←セミコロン「;」を2つつける
 このようにしても「1/0」は表示されません。
 もちろん日付が入力されていたらその日付が表示されます。

 ただしその場合、「1/0」が表示されなくても、そのセルの値はあくまで「0」です（空白文字列「""」ではない）

 以上
（笑） 2021/06/21(月) 23:47

 訂正

 ＞入力シートのB列を作業列
 ＞B13 =IF(SUMPRODUCT(N(TEXT(G13:J13,"yyyy-m;;")=Sheet2!$A$1&-Sheet2!$B$1)),ROW(),"")

 12行目からでした。

 B12 =IF(SUMPRODUCT(N(TEXT(G12:J12,"yyyy-m")=Sheet2!$A$1&-Sheet2!$B$1)),ROW(),"")

 365なら
 B12 =IF(SUM(N(TEXT(G12:J12,"yyyy-m")=Sheet2!$A$1&-Sheet2!$B$1)),ROW(),"")

 ※訂正前の式に付けていた、TEXT 関数の中のセミコロンはなくても同じ

 以上
（笑） 2021/06/22(火) 07:54

（笑）さん、（ねむねむ）さん

ありがとうございます。

【入力シート】
＞365なら
＞B12 =IF(SUM(N(TEXT(G12:J12,"yyyy-m")=Sheet2!$A$1&-Sheet2!$B$1)),ROW(),"")

この式で入力シートは作業列1列だけのスマートなシートになりました。

【1か月シート】
＞ E4セルから、表示形式～ユーザー定義　m/d;;　←セミコロン「;」を2つつける

表示は空白にできましたが、おっしゃる通りあくまで値は「0」なので
右側に色づく条件付き書式の方がうまくいきませんでした。
＞E4 =IFERROR((INDEX(入力シート!G:G,SMALL(入力シート!$B:$B,ROW(A1)))&"")*1,"")
＞表示形式「日付」で右・下コピー
この方法で空白にすることができました。

本当にありがとうございました。
たくさん勉強になりました。もっと頑張ります。

（AKKO） 2021/06/23(水) 09:47

追加。

1か月のチャートができましたが、
加えて3か月連続のシートも欲しいとのこと。

入力シートのA列に3か月作業用の1列を追加しました。

3か月シートの1行目は下記どちらでもよいです

（１）

	A	B	C	D	E	F	
1	2021	7	2021	8	2021	9

（２）

	A	B	C	D		
1	2021	7	8	9

or関数を入れるだけだと思ったのですがうまくいきません。
何が間違っているのでしょうか

（１）
=IF(SUM(N(TEXT(F2:I2,"yyyy-m")=(OR('3か月'!$A$1&-'3か月'!$B$1,'3か月'!$C$1&-'3か月'!$D$1,'3か月'!$E$1&-'3か月'!$F$1)))),ROW(),"")

（２）
=IF(SUM(N(TEXT(F2:I2,"m")='3か月'!B1:D1)),ROW(),"")
=IF(SUM(N(TEXT(F2:I2,"m")=(OR('3か月'!$B$1,'3か月'!$C$1,'3か月'!$D$1)),ROW(),"")

（AKKO） 2021/06/24(木) 11:48

 ちょっと確認ですけど

 入力シートの日付はすべて同じ年？
 2021年ならすべて2021年の日付で、別の年（2020年とか2022年）の日付が混在することはない？

 つまり、年は無視して条件の「月」だけを見ればいいんですか？

 だとしたら･･･
 3か月シートは ↓

	A	B	C	D
1	2021	7	8	9

 入力シート
 A2 =IF(SUM(COUNTIF('3か月'!$B$1:$D$1,TEXT(F2:I2,"m;;")*1)),ROW(),"")
 下コピー

 365以外なら SUM → SUMPRODUCT

 以上
（笑） 2021/06/24(木) 13:34

 ＞入力シート
 ＞A2 =IF(SUM(COUNTIF('3か月'!$B$1:$D$1,TEXT(F2:I2,"m;;")*1)),ROW(),"")
　                                                      ~~~~
 波線部の「*1」は不要かも

 A2 =IF(SUM(COUNTIF('3か月'!$B$1:$D$1,TEXT(F2:I2,"m;;"))),ROW(),"")

 以上
（笑） 2021/06/24(木) 13:40

コメント返信：

[ 一覧(最新更新順) ]

[[20210621091512]] 『INDEX関数の日付の空白（スケジュールチャートの香x（AKKO） ページの最後に飛ぶ

[[20210621091512]]　『INDEX関数の日付の空白（スケジュールチャートの香x（AKKO）　ページの最後に飛ぶ