エクセルの学校


[[20061023084343]] 『勤務表20日締め』(ねね) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

|
| 全文検索 | 過去ログ | HOME ]

 

『勤務表20日締め』(ねね)
 いつもお世話になっています。
 勤務表を作りたいのですが、20日締めなのでスタートが21日になるようにしたいです。
 別のシートに祝祭日と会社の休みのリストがあります。
 年月の入力をすると自動的に日にちと曜日が表示されるようにしたいです。
 祝祭日および休日は自動的に網掛けにしたいです。
 あと、1日の勤務時間の計算を休憩時間を引いて合計●時●●分としたいです。
 
     A     B     C      D       E    F
 1 2006  年    10     月
 2
 3                            氏名:○○○
 4    日  曜日 始業時間 就業時間 休憩時間 実労働時間
 5  21日 土
 6  22日 日
 7  23日 月
 8   24日 火
 9  25日 水
 10 26日 木
 :
 :
 37                               月合計   ○時間○○分
 38                               勤務日数   ○○日

 祝祭日と会社の休日のリストには名前の定義「祝祭日」としました。

 「A5」には「=DATE($A$1,$C$1,ROW()-2)」だと1日からになります。

 祝祭日と会社の休日の網掛けは
 条件書式で数式が=MONTH(A5)>$C$1

           数式が=OR(WEEKDAY($A5,3)>4,COUNTIF(祝祭日,$A5)>0)

 よろしくお願いします。


こんにちは〜♪

仕事中ですので
とりあえず〜。。。

 >20日締めなのでスタートが21日になるようにしたいです。

こちらだけで〜す。。

 A5: =IF(DATE($A$1,$C$1+1,20)<DATE($A$1,$C$1,ROW(A21)),"",DATE($A$1,$C$1,ROW(A21)))

下にコピーしてください。。。

。。。。(Ms.Rin)でした〜♪♪


 衝突したけど、そのままアップ

「A5」には「=DATE($A$1,$C$1,ROW()-2)」だと1日からになります。
 に、なりますか?
 この式のままだと、A5は、3日になると思いますが。
 とりあえず、自分なら
 A5=DATE($A$1,$C$1,21)
 で、いいのでは?
 以下は、A6=A5+1 として、必要分フィルコピー

 >祝祭日と会社の休日の網掛けは
 >数式が=OR(WEEKDAY($A5,3)>4,COUNTIF(祝祭日,$A5)>0)
 で、いいのでは。
 ただ、その上の
 >数式が=MONTH(A5)>$C$1
 は何?。何か意味があるのかな?。

 時間計算は、余り手を出したくない。(^^ゞ
 良くあるのが、後から色々な条件を出してくる。
 とりあえず現状与えられている条件では、
 F5=D5-C5-E5 書式設定で、「[h]時間mm分」
    (TORI)


Ms.Rinさん、TORIさんありがとうございます。

 すみません!
 「A5」に「=DATE($A$1,$C$1,ROW()-2)」だと「3日」になります。
 Rinさんの数式を入力したら、曜日が全部土曜日になってしまいます(T.T)。
 「B5」の式は「=DATE($A$1,$C$1,$A5)」で 下にコピーではだめですか?

 TORIさんの数式を入力すると、31日がない月のTIME SHEETに21日が2度表示されます。

 網掛けの数式の=MONTH(A5)>$C$1は「31日」がない月は「31日」を表示しない
 というのだと思います。(以前教えてもらったので(;^_^A)

 よろしくお願いします。


 使っているものから必要部分だけ抜き出したので、レイアウトが多少違います。
 また、曜日、罫線等は書き込んでません。
 1月、2月、3月、〜12月と言った名前のシートがあり、
 「休暇、祝日、出勤日」といったシートがあること。
 (Jaka)

 「休暇、祝日、出勤日」シートのレイアウトがこんな感じになっているとして...。
 祝日、振替等についてはは、下記参照(来年からの国民の休日改正には対応してません)
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=37;id=FAQ

 「休暇、祝日、出勤日」シート
      B            C                 F
  1 2006年

  2 2006年の祝日、休暇	            出勤日
  3 元日	2007/01/01                  2006/2/11
  4 振替	
  5 成人の日	2007/01/08
  6 建国記念の日	2006/02/11
  7 振替	                            出勤日記載は、確かここまでのF3:F7
  8 春分日	2006/03/21
  9 振替	
 10 昭和の日	2006/04/29
 11 振替	
 12 憲法記念日	2006/05/03
 13 みどりの日	2006/05/04
 14 こどもの日	2006/05/05
 15 振替	
 16 海の日	2006/07/17
 17 敬老の日	2006/09/18
 18 国民の休日	
 19 秋分の日	2006/09/23
 20 振替	
 21 体育の日	2006/10/09
 22 文化の日	2006/11/03
 23 振替	
 24 勤労感謝の日	2006/11/23
 25 振替	
 26 天皇誕生日	2006/12/23
 27 振替	
 28 年末年始	2006/12/31
 29	
 30 特別休暇	2006/01/02
 31  〃         2006/01/03
 32  〃         2006/01/04
 33  〃         2006/08/14
 34  〃         2006/08/15
 35  〃         2006/08/16
 36  〃         2006/12/29
 37  〃
 38  〃
 39  〃

 あ、1月度は、2006/12/21〜2007/1/20まで、2月度は2006/2/21〜2006/3/20

 エクセル関数と条件付書式だけでもできるんですが、マクロのほうを
 コード

 Sub DateWrite()
    Dim i As Long, End1D As Integer, End1M As Integer, End1Y As Integer
    Dim YD1 As Long, YMDay As Date, ii As Long, Cel As Range
    Dim S1 As Integer, S2 As Integer, DateDbl As Double, CNT As Integer
    Dim Cmb2St As String, Cmb3St As String, Txt1St As String, MGTL As String

    Cmb3St = 2006   '作成する年

    For i = 1 To 12
        If i > 1 Then
           YD1 = Cmb3St
        Else
           YD1 = Cmb3St + 1
        End If
        YMDay = YD1 & "/" & i & "/1"
        End1Y = Year(YMDay - 1)
        End1M = Month(YMDay - 1)
        End1D = Day(YMDay - 1)
        With Worksheets(i & "月")
            .Range("D3").Value = Cmb3St
            .Range("A4:A34").ClearContents
            .Range("R4:T10,R13:S13").ClearContents
            .Range("R4:T33,R34:R36").Interior.ColorIndex = xlNone
            .Range("A4:A34").NumberFormatLocal = "d"
            .Range("A4:Q34").Interior.ColorIndex = xlNone
            With .Range("A4:A" & 3 + End1D - 20)
                .Cells(1).Value = End1Y & "/" & End1M & "/" & 21
                .DataSeries , Step:=1
            End With
            With .Range("A15:A34")
                .Cells(1).Value = YD1 & "/" & i & "/" & 1
                .DataSeries , Step:=1
            End With
            For Each Cel In .Range("A4:A34")
                If Cel.Value <> "" Then
                   If Holiday(Cel.Value2) Then
                      Cel.Resize(, 17).Interior.Color = RGB(0, 200, 255)
                   End If
                End If
            Next

            CNT = 21
            For S1 = 4 To 34 Step 3
                For S2 = 18 To 20
                    If CNT >= 21 Then
                       If CNT <= End1D Then
                          .Cells(S1, S2).Value = CNT
                          DateDbl = CDbl(CDate(End1Y & "/" & End1M & "/" & CNT))
                          If Holiday(DateDbl) Then
                            .Cells(S1, S2).Resize(3).Interior.Color = RGB(0, 200, 255)
                          End If
                       End If
                       If CNT = 31 Then
                          CNT = 1
                       Else
                          CNT = CNT + 1
                       End If
                    Else
                       .Cells(S1, S2).Value = CNT
                       DateDbl = CDbl(CDate(YD1 & "/" & i & "/" & CNT))
                       If Holiday(DateDbl) Then
                         .Cells(S1, S2).Resize(3).Interior.Color = RGB(0, 200, 255)
                       End If
                       If CNT = 20 Then Exit For
                       CNT = CNT + 1
                    End If
                Next
            Next

        End With
    Next
    MsgBox "終わりました。"
 End Sub

 Private Function Holiday(YMDate As Double) As Boolean
    Dim WKday As Variant, Hlday As Variant, WeekNo As Integer
    WKday = Application.Match(YMDate, Sheets("休暇、祝日、出勤日").Range("F4:F8"), 0)
    EndR = Sheets("休暇、祝日、出勤日").Range("B65536").End(xlUp).Row
    If IsError(WKday) = False Then
       Holiday = False
    ElseIf Weekday(YMDate) = 1 Or Weekday(YMDate) = 7 Or _
       IsError(Application.Match(CDbl(YMDate), Sheets("休暇、祝日、出勤日").Range("C4:C" & EndR), 0)) = False Then
       Holiday = True
    ElseIf Weekday(YMDate) = 2 And _
       IsError(Application.Match(CDbl(YMDate - 1), Sheets("休暇、祝日、出勤日").Range("C4:C" & EndR), 0)) = False Then
       Holiday = True
    End If
 End Function


 確認です。
 ねねさんの10月は、10月21日〜11月20日で、いいのですか?

 >網掛けの数式の=MONTH(A5)>$C$1は「31日」がない月は「31日」を表示しない
 この数式の意味を解っていると思ってました。
 この数式のままでは、1ヶ月を1日〜末日としている場合に有効になります。
 20日締めでは、翌月1日〜20日までがすべて対象になってしまいます。
 =MONTH(A5)>$C$1は → MONTH(A5-20)>$C$1
 これを最終行(36行目から上に3行設定)

 >Rinさんの数式を入力したら、曜日が全部土曜日になってしまいます(T.T)。
 自分の確認では、ちゃんと表示されますが。
 B列はどういった数式を設定してますか?
 B5=A5 書式設定「aaa」 又は、=TEXT(A5,"aaa")
 で、いいのでは?
     (TORI)


 Jakaさん、ご回答ありがとうございます。
 せっかく回答いただいたのですが、マクロはまったく理解できていないのでわかりません。
 ごめんなさい。

 TORIさん、
 網掛けの意味を理解していなくてすみません!!
 曜日の設定ですが、「B5」=「A5」で表示されました。
 網掛けもできました。ありがとうございました。

 もうひとつ教えて下さい。
 別シートの「A1」「A27」に祝祭日の日付を入れた表があります。
 これを参照して、個人で設定した休みも網掛けしたいのですが、
 A1:A27を指定して、挿入→名前→定義→祝祭日
 で対応できないのでしょうか?


 >A1:A27を指定して、挿入→名前→定義→祝祭日
 >で対応できないのでしょうか? 
 それは、
 >数式が=OR(WEEKDAY($A5,3)>4,COUNTIF(祝祭日,$A5)>0)
 の、COUNTIF(祝祭日,$A5)>0 の部分で、出来ているのでは?
 上記式は、土日又は祝祭日範囲に日付データがあるもの
 ということになってますよ。
   (TORI)


 TORIさん、
 いろいろ祝祭日リストの日付を変えても、網掛けになりません。
 例えば、2006/5と指定すると、リストは会社が5/1メーデー休みと5/2一斉休暇とあるのに
 網掛けにならないです。
 2006/12と指定して、1/5を休みとリストに追加すると網掛けになります。
 なにが違うのでしょうか?


 C3:C39の祝日の日付が入ったセル範囲に「休日」と名前をつけておく。
 F3:F8の出勤日の日付が入ったセル範囲に「出勤日」と名前をつけておく。

 作業列用に1列追加(A列を作業列とします。)
 年をB1に
 月をC1に記入するとして

 B5
 =EDATE(DATE(B1,C1,1),-1)+20

 B6
 =IF(B5="","",IF(MONTH(B5+1)=$C$1,"",B5+1))
 これを15行目までフィル。

 B16
 =DATE(B1,C1,1)
 B17
 =B16+1
 これを35行目までフィル。
 =B16+1

 A5
 =IF(B5="","",IF(ISNA(MATCH(B5,出勤日,0))=FALSE,"",IF(ISNA(MATCH(B5,休日,0))=FALSE,1,IF(WEEKDAY(B5)=1,1,IF(WEEKDAY(B5)=7,2,"")))))
 これを35行目までフィル。

 日付の色は、条件付書式でA列の値が1なら赤、2なら青とでもしておけば良いです。
 (Jaka)

 Jakaさん、ありがとうございます。
 やり方はいろいろあるのですね。参考にさせていただきます。

 TORIさん、ごめんなさい!!よく確認したら指定どおり網掛けになっていました。
 21日始まりなので5月と指定したら、その表の1日は6月ということでした。
 勘違いしてもうしわけございませんでした。

 ご回答頂いた先生方、最後までありがとうございました。

 	A	B	C		
1	2006		6		
2					
3		A列書式 d  (ユーザー定義)			
4		B列書式 aaa (ユーザー定義)			
5	21	水	A5: =DATE(A1,C1,21)		
6	22	木	A6: =A5+1	 下へフィールコピーA32まで	
7	23	金			
8	24	土			
9	25	日			
10	26	月			
11	27	火			
12	28	水			
13	29	木			
14	30	金			
15	1	土			
16	2	日			
17	3	月			
18	4	火			
19	5	水			
20	6	木			
21	7	金			
22	8	土			
23	9	日			
24	10	月			
25	11	火			
26	12	水			
27	13	木			
28	14	金			
29	15	土			
30	16	日			
31	17	月			
32	18	火	20日以降空白にする		
33	19	水	A33: =IF(MONTH($A13)>$C$1,"",A32+1)
34	20	木	   下へフィールコピー A35まで 		
35					

 >祝祭日と会社の休日の網掛けは
 >数式が=OR(WEEKDAY($A5,3)>4,COUNTIF(祝祭日,$A5)>0)
 (ANA)					

コメント:

[ 一覧(最新更新順) |

]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.