Excel (一般機能)

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

 
(Windows 7 Professional : Excel 2010)
入力した日を参照して、同一週の初日と最終日を取得したい
投稿日時: 18/08/10 08:35:01
投稿者: おで

教えてください。
 
A1セルに日付を入力すると、
稼働日カレンダーを元に、B1・C1セルにその週の初日と最終日が表示されるようにしたいです。
 
稼働日カレンダーとは、下表で、その日付がその月の何週なのか記載されてます。
 
日付       月    週
2018/8/24    8    3
2018/8/27    8    4
2018/8/28    8    4
2018/8/29    8    4
2018/8/30    8    4
2018/8/31    8    4
2018/9/3    9    1
2018/9/4    9    1
2018/9/5    9    1
2018/9/6    9    1
2018/9/7    9    1
2018/9/10    9    2
※稼働日カレンダーは8/24-9/10までを抜粋したものです
 
たとえば、
 
A1セルに 『2018/8/28』 と入力すると、
 
B1セルに 『2018/8/27』、  ・・・同一週の初日
C1セルに 『2018/8/31』   ・・・同一週の最終日
 
と表示できるようにしたい。
 

回答
投稿日時: 18/08/10 11:03:13
投稿者: WinArrow
投稿者のウェブサイトに移動

考え方(ヒント)
(1)稼働日カレンダーの週の右側に1列(作業列)追加します。
(2)作業列のセルに
 例 "8-3"となるような数式を入力します。
(3)稼働日カレンダー(追加した列を含めて)に名前を定義します。例:CALENDAR
   
セルA3
=VLOOKUP(A1,CALENDAR,4,0)
セルA4(A3の先頭行)
=MATCH(A3,INDEX(CALENDAR,,4),0)
セルA5(最終行までの行数)
=COUNTIF(INDEX(CALENDAR,,4),A3)-1
   
セルA6(先頭日付)
=INDEX(CALENDAR,A4,1)
セルA7(最終日付)
=INDEX(CALENDAR,A4+A5,1)
 セルA6、A7の表示形式を日付形式を設定します。

回答
投稿日時: 18/08/10 11:19:59
投稿者: ブルー

その稼働日カレンダーというのは土日を抜いてるだけですか?
 
B1にその週の月曜日、C1にその週の金曜日を表示させたいだけなら
カレンダーを参照しなくてもできますね。
 
それとも月曜が祝日なら、B1にはその翌日の火曜日を表示ということでしょうか?
 
そうだとしても祝日一覧をどこかに入力しておけば、WORKDAY関数でできそうな気がします。
 
1週間まるまる非稼動の週もあるんでしょうか?

投稿日時: 18/08/10 11:26:13
投稿者: おで

WinArrow さん>
 回答ありがとうございます。これから内容確認したいと思います。
 
ブルー さん>
 
 回答ありがとうございます。
 
 >その稼働日カレンダーというのは土日を抜いてるだけですか?
 
 土曜日が稼働日になることもあれば、平日が休みになることもありますので、
 1週間まるまる非稼働という週もあります。
 稼働日カレンダーは、非稼働日を抜いたものになります。
 
 

回答
投稿日時: 18/08/10 14:01:54
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 

引用:
セルA3
=VLOOKUP(A1,CALENDAR,4,0)

 
ここでは、セルA1に指定した日が稼働日カレンダーに存在しないことを考慮していません。
 
必要ならば、数式を改変してください。
以下のセルの数式も同様です。

回答
投稿日時: 18/08/12 07:18:51
投稿者: Mike

おで さんの引用:
稼働日カレンダーとは、下表で、その日付がその月の何週なのか記載されてます。
参考までに確認させてください。
「下表」に示されている「週」はどのようにして算出された数値ですか?それとも数式に頼らずに貴方が暗算した結果とか?
それから、「2018/8/24」の「週」が3なら、2018/8/1(水)の「週」は何になりますか?

回答
投稿日時: 18/08/24 13:34:46
投稿者: TAKA君

関数ではどうやればいいのか分かりません、、、
VBAならそれほど難しくないので、一応コードを挙げておきます。
 

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Fc As Range, i As Long, S As Long, L As Long
        If Not Intersect(Range("A1"), Target) Is Nothing Then
            Set Fc = Range("A10:A21").Find(Target.Value, LookAT:=xlWhole)
            If Not Fc Is Nothing Then
                For i = 1 To 5
                    If S = 0 And Cells(Fc.Row, "C") <> Cells(Fc.Row - i, "C") Then S = Fc.Row - i + 1
                    If L = 0 And Cells(Fc.Row, "C") <> Cells(Fc.Row + i, "C") Then L = Fc.Row + i - 1
                Next i
                Range("B1") = Cells(S, "A").Value
                Range("C1") = Cells(L, "A").Value
            End If
        End If
    End Sub

 
 
Range("A10:A21")←これは実際に稼働日カレンダーがあるデータ範囲にかえてください。
 

回答
投稿日時: 18/08/25 12:51:20
投稿者: sy

       A         B         C         D         E         F         G
1  2018/8/28 2018/8/27 2018/8/31              日付       月        週
2                                           2018/8/24    8         3
3                                           2018/8/27    8         4
4                                           2018/8/28    8         4
5                                           2018/8/29    8         4
6                                           2018/8/30    8         4
7                                           2018/8/31    8         4
8                                           2018/9/3     9         1
9                                           2018/9/4     9         1
10                                          2018/9/5     9         1
11                                          2018/9/6     9         1
12                                          2018/9/7     9         1
13                                          2018/9/10    9         2

上記のようなレイアウトなら
 
B1 =INDEX(E:E,MATCH(VLOOKUP(A1,E:G,2,0)&VLOOKUP(A1,E:G,3,0),INDEX(F:F&G:G,0),0))
C1 =LOOKUP(1,1/((F:F=VLOOKUP(A1,E:G,2,0))*(G:G=VLOOKUP(A1,E:G,3,0))),E:E)

 

投稿日時: 18/09/06 14:17:59
投稿者: おで

回答遅くなり申し訳ざいません。
みなさん色々とご回答いただきまして、ありがとうございました。
syさんの関数を使って算出できました。
解決とさせていただきます。