Excel (一般機能)

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

 
(Windows 7 Professional : Excel 2010)
関数にてカレンダー内から各週毎に基準月の週始め・終わりを求める方法
投稿日時: 18/05/08 13:25:53
投稿者: tokuto

お世話になっております。
 
現在、週初めと終わりを算出する数式を検討しているところです。
 
まず現在の状況ですが・・・
 
H30.2月の日曜日始まりのカレンダーから週ごとの開始・終了を算出しています。
 
※表にある数式には、単純に右のカレンダーから週始めと終わりを参照させています。
1週目(自)=A4(至)=B4+6
 
質問は、基準月(ここでは2月)に該当する週の開始と終了を求めたいと考えております。
条件は「日曜始まり」「週は6週」「かつ各週に基準月に当たる、週始め・終わりを算出」
 
変な質問ですが、皆さまアイディア、アドバイスの保よろしくお願い致します。
 
期待する値は
1週目(自)2/1 (至)2/3
2週目(自)2/4 (至)2/10
3週目(自)2/11(至)2/17
4週目(自)2/18(至)2/24
5週目(自)2/25(至)2/28
6週目(自)  (至)  
 
┌──────────────────┐
│H30.02.01  H30.02.28   28 │
├──────────────────┤
│ 2018 年   2 月   │  (基礎データ)
├──────────────────┤ ┌─────────────┐
│ (週)   (自)   (至) │ │日 月 火 水 木 金 土│
├──────────────────┤ ├─┬─┬─┬─┬─┬─┬─┤
│ 1 週目  H30.01.28   H30.02.03 │ │28│29│30│31│1 │2 │3 │
│                  │ ├─┼─┼─┼─┼─┼─┼─┤
│ 2 週目  H30.02.04   H30.02.10 │ │4 │5 │6 │7 │8 │9 │10│
│                  │ ├─┼─┼─┼─┼─┼─┼─┤
│ 3 週目  H30.02.11   H30.02.17 │ │11│12│13│14│15│16│17│
│                  │ ├─┼─┼─┼─┼─┼─┼─┤
│ 4 週目  H30.02.18   H30.02.24 │ │18│19│20│21│22│23│24│
│                  │ ├─┼─┼─┼─┼─┼─┼─┤
│ 5 週目  H30.02.25   H30.03.03 │ │25│26│27│28│1 │2 │3 │
│                  │ ├─┼─┼─┼─┼─┼─┼─┤
│ 6 週目  H30.03.04   H30.03.10 │ │4 │5 │6 │7 │8 │9 │10│
└──────────────────┘ └─┴─┴─┴─┴─┴─┴─┘
 

投稿日時: 18/05/08 13:36:20
投稿者: tokuto

すみません、追加情報でカレンダ内の28日と表示されている部分の数式は以下
  
=$A$1-WEEKDAY($A$1,1)+1
  
29日以降は=E4+1・・・・と続きます
 

回答
投稿日時: 18/05/08 15:28:11
投稿者: ゆーたん

うーん、ちょっとシートの構成でわからない所も多いですが、
とりあえずのあんまりスッキリしない案です。(^_^;)
 
    A     B     C 
1  2018     9       
2         自     至 
3 1週目  2018/9/1  2018/9/1 
4 2週目  2018/9/2  2018/9/8 
5 3週目  2018/9/9 2018/9/15 
6 4週目 2018/9/16 2018/9/22 
7 5週目 2018/9/23 2018/9/29 
8 6週目 2018/9/30 2018/9/30 
 
西暦年(A1)と月(B1)だけ手入力数値で入れます。
B3 =DATE($A$1,$B$1,1)
C3 =DATE($A$1,$B$1,8-WEEKDAY(DATE($A$1,$B$1,1),17))
B4 =IFERROR(IF(MONTH(C3+1)=$B$1,C3+1,"-"),"-")
下フィルコピー。
C4 =IF(B4="-","-",IF(MONTH(C3+7)=$B$1,C3+7,DATE($A$1,$B$1+1,0)))
下フィルコピー。

回答
投稿日時: 18/05/08 15:55:36
投稿者: ゆーたん

> =$A$1-WEEKDAY($A$1,1)+1
> 29日以降は=E4+1・・・・と続きます
そっか、A1セルを参照してE4セルに直前の日曜日をもとめて、
E列以降にシリアル値でカレンダーができているのですね。(^_^;)
それならMONTH関数で月を比較して、まるまる使えばいいんじゃないでしょうか?
 
     A    B    C D  E  F  G  H  I  J  K 
1 09/2/1 09/2/28                      
2  2009    2                      
3        自    至   日 月 火 水 木 金 土 
4  1週目  09/2/1  09/2/7    1  2  3  4  5  6  7 
5  2週目  09/2/8 09/2/14    8  9 10 11 12 13 14 
6  3週目 09/2/15 09/2/21   15 16 17 18 19 20 21 
7  4週目 09/2/22 09/2/28   22 23 24 25 26 27 28 
8  5週目    -    -    1  2  3  4  5  6  7 
9  6週目    -    -    8  9 10 11 12 13 14 
 
A1だけその月の一日をシリアル値で手入力。
B1 =EDATE(A1,1)-1
A2 =YEAR(A1)
B2 =MONTH(A1)
B4 =A1
B5 =IF(MONTH(E5)=B$2,E5,"-")
下フィルコピー。
C4 =IF(B4="-","-",IF(MONTH(K4)=B$2,K4,B$1))
下フィルコピー。
E4 =$A$1-WEEKDAY($A$1,1)+1
F4 =E4+1
よこフィルコピー。
E5 =E4+7
たてよこフィルコピー。

回答
投稿日時: 18/05/08 17:43:46
投稿者: WinArrow
投稿者のウェブサイトに移動

数式&条件付き書式を使います
 
セルA1:手入力
 
セルB3
=IF(B2="",A1,C2+1)
セルC3
=MIN(B3+7-WEEKDAY(B3),EOMONTH($A$1,0))
B3:C3を下へフィルドラッグ
 
条件付き書式
セルB3:C8を選択

=OR(MONTH($A$1)<>MONTH(B3),$C3<$B3)
書式は文字色を「白」にする

回答
投稿日時: 18/05/08 23:45:08
投稿者: sy

ゆーたんさんのレイアウトをお借りして、以下の数式でも出来ます。

     A    B    C D  E  F  G  H  I  J  K  
1 09/2/1 09/2/28                       
2  2009    2                       
3        自    至   日 月 火 水 木 金 土  
4  1週目  09/2/1  09/2/7    1  2  3  4  5  6  7  
5  2週目  09/2/8 09/2/14    8  9 10 11 12 13 14  
6  3週目 09/2/15 09/2/21   15 16 17 18 19 20 21  
7  4週目 09/2/22 09/2/28   22 23 24 25 26 27 28  
8  5週目              1  2  3  4  5  6  7  
9  6週目              8  9 10 11 12 13 14 

B4 =IF(E4>B$1,"",MAX(A$1,E4))
C4 =IF(E4>B$1,"",MIN(B$1,K4))
それぞれ下にフィルコピー

回答
投稿日時: 18/05/09 00:25:30
投稿者: Mike

お示しのレイアウトとは若干(?)異なるけど、参考までに
 
  A  B   C   D  E  F  G
2 2018 年   2 月
3  日  月  火  水  木  金  土
4  28  29  30  31  1  2  3
5   4  5  6  7  8  9  10
6  11  12  13  14  15  16  17
7  18  19  20  21  22  23  24
8  25  26  27  28  1  2  3
9   4  5  6  7  8  9  10
10
11
12 週  自  至
13  1 2/1 2/3
14  2 2/4 2/10
15  3 2/11 2/17
16  4 2/18 2/24
17  5 2/25 2/28
18  6
 
1.aaa に書式設定した範囲 A3:G3 の左から数値 1、2、3、…、7 を入力
2.範囲 A4:G9 を d に書式設定
3.次式を入力したセル A4 を右に6列オートフィル
 ̄ ̄ =DATE($A2,$C2,A3)+1-WEEKDAY(DATE($A2,$C2,1))
4.式 =A4+7 を入力したセル A5 を右に6列オートフィル
5.範囲 A5:G5 を下に4行オートフィル
6.範囲 B13:C18 を m/d に書式設定
7.セル B13 に次式を入力
 ̄ ̄ =IF(MONTH(A4)=C2,A4,EOMONTH(A4,0)+1)
8.式 =IF(MONTH(A5)=C$2,A5,"") を入力したセル B14 を下に4行オートフィル
9.次式を入力したセル C13 を下に5行オートフィル
 ̄ ̄ =IF(B13="","",IF(MONTH(G4)=C$2,G4,EOMONTH(B13,0)))

投稿日時: 18/05/09 09:02:54
投稿者: tokuto

ゆーたんさん
syさん
WinArrowさん
Mikeさん
 
皆さまありがとうございます。
分かりやすいように、丁寧な説明感謝感謝です。
各々のパターンでやってみました。思った以上の動作でした。
 
もう一つよろしいでしょうか?
 
カレンダー内の当月以外の日にちを""非表示にする方法もアドバイスお願いできますでしょうか。
 
欲張りで申し訳ございません。
 

回答
投稿日時: 18/05/09 09:25:05
投稿者: ゆーたん

今、右のカレンダーを作ってから左側を計算していると思うので、
カレンダーの数式をいじらずに、条件付き書式を設定したらどうかしら?(^_^;)
 
条件付き書式で「数式を使用して…」を使って、
自分のセルに対してMONTH関数つかって、比較演算子の「<>」で月のセル(B2かな?)と
比較して、異なる月ならTRUEを返すので、フォントを白(背景色と同じ)にするか、
表示形式ユーザー定義で「;;;」みたいにするかかな?

回答
投稿日時: 18/05/09 09:50:17
投稿者: ゆーたん

Chiquilinさんのサイトにわかりやすいのあったので、
参考にリンクをはっておきます。(^_^;)
 
●Chiquilin Site■17.12.28_Excel:カレンダーの作り方
http://www.geocities.jp/chiquilin_site/data/171228_calendar3.html
 
※WORKDAY.INTL関数知りませんでした。
好きな曜日を休みにできる関数なんですね。(^_^;)

回答
投稿日時: 18/05/09 11:29:35
投稿者: Mike

tokuto さんの引用:
カレンダー内の当月以外の日にちを""非表示にする方法
下記の手順で[条件付き書式]を設定すると、下表のように表示されます。
範囲 A4:G9 を選択 ⇒ Alt+HLN ⇒ “数式を…を決定”を選択 ⇒ 枠内に式 =MONTH(A4)<>$C$2 を
入力 ⇒ [書式]をツン ⇒ [フォント]の[色]で“白”パレットをツン ⇒ [OK] ⇒ [OK]
  A  B   C   D  E  F  G
2 2018 年   2 月
3  日  月  火  水  木  金  土
4             1  2  3
5   4  5  6  7  8  9  10
6  11  12  13  14  15  16  17
7  18  19  20  21  22  23  24
8  25  26  27  28
9

回答
投稿日時: 18/05/09 14:45:04
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
カレンダー内の当月以外の日にちを""非表示にする方法もアドバイスお願いできますでしょうか

 
>当月以外の日にちを""非表示にする方法
の使い方にもよりますが、
単純に見えなくするのであれば、条件付き書式が最適と思います。
 
条件付き書式に関しては、
Mikeさんからレスが付いています。
また、私の前レスにも条件付き書式が書かれています。
(試したのではないのかな?)
 
私見ですが、見栄えだけの話ではなく、
他のセルで参照するにしても
データは日付けいしきにしておいた方が扱いやすいと思います。
 
 

回答
投稿日時: 18/05/13 15:13:50
投稿者: WinArrow
投稿者のウェブサイトに移動

私の投稿日時: 18/05/08 17:43:46のレスは
右側のカレンダーを無視しています。
 
ですから、右側のカレンダーより
週の先頭/最終を求めるレスは、 sy さんのレスになると思います。
 

トピックに返信