Excel (一般機能)

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

 
(Windows 10 Home : Excel 2016)
お知恵をお借しください。(指定年月の講習日を求めたい)
投稿日時: 20/04/01 07:36:01
投稿者: カールおじさん

使用Excelのバージョンは2019ですが、選択肢にないので2016にしました。
A1に西暦年。B1に月データを指定します。
指定年月の毎週水曜日と金曜日を講習日としてその日付を日付順にA5:A14の範囲(1ヶ月の水・金は最大10回あり、1回目が水曜日になることも金曜日になることもあり、第2週から始まることもあります。)に表示したいです。
但し、祝日を含む指定休講日リスト(範囲名:休講日)に合致する水曜日・金曜日は除外する。
A5に1回目の日をA6に2回目の日を求め、A6の式をA7〜A14にオートフィルで求めたいです。
ご教示ください。

回答
投稿日時: 20/04/01 10:04:00
投稿者: WinArrow
投稿者のウェブサイトに移動

一度にいくつものことを解決しようとせず、
いくつかのブロックに分けて考えましょう。
(分けると、それなりにネット検索できる可能があります)
 
(1)年月を指定して、第x曜日を求める方法
 
(2)(1)で求めた日付が、祝日に当たるか?
 
※ 1ヶ月分(1〜31)のリストを作成しておいて、
 右隣のセルに「第x講習日」を表示する方が簡単です。(オートいる他も使えます)
 

回答
投稿日時: 20/04/01 11:06:10
投稿者: WinArrow
投稿者のウェブサイトに移動

ヒント
 
A1:西暦年
B1:月
 
A7:==IF(MONTH(DATE($A$1,$B$1,ROW(A1)))=$B$1,DATE($A$1,$B$1,ROW(A1)),"")
 表示形式:d(aaa)
A37までフィルコピー
 
B7:=IF(AND(OR(TEXT(A7,"aaa")="水",TEXT(A7,"aaa")="金"),IF(IFNA(VLOOKUP(A7,休講日リスト,1,FALSE),FALSE),TRUE)=FALSE),"第"&COUNTIF($B$1:B6,"*講習*")+1&"回目講習","")
B37までフィルコピー

回答
投稿日時: 20/04/01 11:07:04
投稿者: んなっと

      A B
 1   2020 4
 2       
 3       
 4       
 5  4/1(水)  
 6  4/3(金)  
 7  4/8(水)  
 8 4/10(金)  
 9 4/15(水)  
10 4/17(金)  
11 4/22(水)  
12 4/24(金)  
13 4/29(水)  
14       
 
A5
=IFERROR(AGGREGATE(15,6,TEXT(WORKDAY.INTL(DATE(A$1,B$1,0),ROW($1:$31),"1101011",休講日),"[<"&DATE(A$1,B$1+1,1)&"]0;")*1,ROW(A1)),"")
下方向・↓

回答
投稿日時: 20/04/01 11:17:03
投稿者: んなっと

例えば"水金"の部分を、"月水金"に増やすときは
"1101011" → "0101011" に変更。
 
詳しくはWORKDAY.INTLのヘルプ参照。

回答
投稿日時: 20/04/01 11:28:43
投稿者: んなっと

あと、条件付き書式の表示形式で見えなくする方法も。
 
      A B
 1   2020 4
 2       
 3       
 4       
 5  4/3(金)  
 6  4/8(水)  
 7 4/15(水)  
 8 4/17(金)  
 9 4/22(水)  
10 4/29(水)  
11       
12       
13       
14       
 
A5
=AGGREGATE(15,6,WORKDAY.INTL(DATE(A$1,B$1,0),ROW($1:$31),"1101011",休講日),ROW(A1))
条件付書式:数式を〜▼
=MONTH(A5)<>B$1
→書式→表示形式→ユーザー定義
;;;
 

回答
投稿日時: 20/04/01 12:02:18
投稿者: Mike

  A   B        B       B
1 2020   4        2       7
2                   
3                   
4                   
5    04/01      02/05     07/01
6    04/03      02/07     07/03
7    04/08      02/12     07/08
8    04/10      02/14     07/10
9    04/15      02/19     07/15
10   04/17      02/21     07/17
11   04/22      02/26     07/22
12   04/24      02/28     07/24
13   04/29             07/29
14                  07/31
 
A5: =MIN(DATE(A1,B1,7-WEEKDAY(DATE(A1,B1,-2),3)),DATE(A1,B1,7-WEEKDAY(DATE(A1,B1,-4),3)))
A6: =IFERROR(IF(MONTH(B5+2*(MOD(B5,7)=4)+5*(MOD(B5,7)=6))<>B$1,"",B5+2*(MOD(B5,7)=4)+5*(MOD(B5,7)=6)),"")

回答
投稿日時: 20/04/01 12:06:13
投稿者: んなっと

この質問は、以下の部分が大きな問題です。

引用:
但し、祝日を含む指定休講日リスト(範囲名:休講日)に合致する水曜日・金曜日は除外する

回答
投稿日時: 20/04/01 14:24:02
投稿者: Mike

改訂版です。
 
  A   B C  D    E  F   G   H    I
1  2020 4           Nholiday
2                2020/01/01 元日
3                2020/01/13 月 成人の日
4       作業列1 作業列2  2020/02/11 火 建国記念の日
5 04/01    04/01  04/01  2020/02/23 日 天皇誕生日
6 04/03    04/03  04/03  2020/02/24 月 振替休日
7 04/08    04/08  04/08  2020/03/20 春分の日
8 04/10    04/10  04/10  2020/04/15 水 休講日
9 04/17    04/15  #N/A  2020/04/29 昭和の日
10 04/22    04/17  04/17  2020/05/03 日 憲法記念日
11 04/24    04/22  04/22  2020/05/04 月 みどりの日
12       04/24  04/24  2020/05/05 火 こどもの日
13       04/29  #N/A  2020/05/06 振替休日
14       05/01  #N/A  2020/07/23 木 海の日
15               2020/07/24 スポーツの日
16               2020/08/10 月 山の日
17               2020/09/21 月 敬老の日
18               2020/09/22 火 秋分の日
19               2020/11/03 火 文化の日
20               2020/11/23 月 勤労感謝の日
 
1.範囲 G1:G20 選択 ⇒ Alt+MC ⇒“上端行”だけにチェック入れ ⇒ Enter
2.次の各セルに夫々右側の式を入力
 ̄ ̄ D5: =MIN(DATE(A1,B1,7-WEEKDAY(DATE(A1,B1,-2),3)),DATE(A1,B1,7-WEEKDAY(DATE(A1,B1,-4),3)))
 ̄ ̄ D6: =D5+2*(MOD(D5,7)=4)+5*(MOD(D5,7)=6)
3.セル D6 を下方にズズーッと(14行目まで)オートフィル
4.式 =IF(OR(MONTH(D5)<>B$1,COUNTIF(Nholiday,D5)>0),NA(),D5) を入力したセル E5 のフィルハンドルを「エイヤッ!」とダブルクリック
5.式 =IFERROR(AGGREGATE(15,6,E$5:E$14,ROW(A1)),"") を入力したセル A5 を下方にズズーッと(14行目まで)オートフィル
[/color][/color]

回答
投稿日時: 20/04/01 17:13:47
投稿者: んなっと

これもあり。
 
A5
=IFERROR(TEXT(WORKDAY.INTL(IF(ROW()=5,DATE(A$1,B$1,0),A4),1,"1101011",休講日),"[<"&DATE(A$1,B$1+1,1)&"]0;")*1,"")
下方向・↓

投稿日時: 20/04/02 06:03:32
投稿者: カールおじさん

んなっとさんはじめ皆さんありがとうございました。
AGGREGATE関数やWORKDAY.INTL関数は使ったことがないのですが、これで何とかできそうです。
やってみます。