日曜＆祝日を除く7営業日後（EXCEL）

質問

EXCEL初心者です
EXCEL2007（12.0.6654 5503)SP2を使用しております
もしわかりましたら教えてください

A 　 B 　　 C　　　　　D
1 2012/6/1　　2012/6/9　　　　11日目　　2012/6/21　　

2 2012/6/4　　2012/6/12　　　　9日目

3 2012/6/9　　2012/6/18　　　　4日目

(1)セルA列に日付がはいってます
　セルB列にセルA列の7営業日後（日曜、祝日は休み。土曜日は営業）
　を自動的に表示されるようにしたい

(2)セルD1には今日の日付がはいってます
　セルC列にB列の日付から今日まで何日経ったか、営業日の日数（日曜、祝日は休み。土曜日は営業）
　で自動的に表示されるようにしたい

説明がわかりずらかったらすみません
補足しますのでその旨記載お願いします

回答No.5

MarcoRossiItaly

現在の日本の祝日は15日あるそうです。先に、それらの日付をF1:F15に入力。

A列に抜けている日はなく、日曜・祝日も含めて、カレンダーの全ての日が入力されている前提で。なお9,999行は、27.38年に相当。

B1　=small(index(a2:A$9999+99999*(((a2:A$9999)=0)+(weekday(a2:A$9999)=1)+countif(F$1:F$15,a2:A$9999)),),7)
C1　=IF((B1>=$D$1)+(B1=B2),"",IF(B2>$D$1,1&"日目",SUMPRODUCT(1/COUNTIF(OFFSET(B1,1,0,MATCH($D$1,B:B,1)-ROW(),1),B2:OFFSET(B1,MATCH($D$1,B:B,1)-ROW(),0,1,1)))+(COUNTIF(B:B,$D$1)=0)&"日目"))

B列はExcelが上手な人には難しいというほどでもないと思いますが、C列は思いのほか、面倒でしたね。まぁどちらも易しくはないです。

回答No.4

KURUMITO

分かり易く確実な方法は作業列をデータベースとなるシートに作成して対応する方法です。
データベーとなるシートをシート2としてA1セルには2012年のことでしたら2012と入力します。
A2セルから下方にはその年の祝日を入力します。会社の休日なども入力します。
B2セルには次の式を入力し下方にドラッグコピーします。

=DATE(A$1,1,ROW(A1))

B列にはその年の1月1日からの日付が表示されます。
C2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(WEEKDAY(DATE(A$1,1,ROW(A1)))=1,COUNTIF(A:A,DATE(A$1,1,ROW(A1)))>0),"",MAX(C$1:C1)+1)

その年の初めからの勤務日の積算値が表示されます。
これでいつの日が勤務の日であるかがはっきりと確認できますね。
以上でシート2での作業は終わってお求めの表がシート1であるとして7営業日後の日付はB1セルに次の式を入力して下方にドラッグコピーします。

=IF(A1="","",INDEX(Sheet2!B:B,MATCH(IF(INDEX(Sheet2!C:C,MATCH(A1,Sheet2!B:B,0))="",INDEX(Sheet2!C:C,MATCH(10^10,Sheet2!C$1:INDEX(Sheet2!C:C,MATCH(A1,Sheet2!B:B,0))))+8,INDEX(Sheet2!C:C,MATCH(A1,Sheet2!B:B,0))+7),Sheet2!C:C,0)))

11日目はC1セルに11と入力します。D1セルには次の式を入力して下方にドラッグコピーします。

=IF(B1="","",INDEX(Sheet2!B:B,MATCH(INDEX(Sheet2!C:C,MATCH(B1,Sheet2!B:B,0))+C1-1,Sheet2!C:C,0)))

補足

ありがとうございます。
関数のひとつひとつは調べてどんな関数なのかわかりました。

シート2にいれる関数は理解できます。
シート1にいれる関数がよくわかりません。

式の中にある「10^10」は10を10回かけるということですよね？
この式がなんのために必要かわかりません。

わかりやすく教えて頂けないでしょうか。
宜しくお願い致します。

回答No.3

MackyNo1

Excel初心者には分かりにくいかもしれませんが、以下のような数式で該当日を及び経過日数を表示できます（1か月の範囲のデータの例）。

B1セル
=SMALL(INDEX(((WEEKDAY(A1+ROW($A$1:$A$30))=1)+COUNTIF(祝日リスト,A1+ROW($A$1:$A$30)))*100000+A1+ROW($A$1:$A$30),),7)

C2セル
=SUMPRODUCT((WEEKDAY(B1+ROW($A$1:$A$30))<>1)*(COUNTIF(祝日リスト,B1+ROW($A$1:$A$30))=0)*(B1+ROW($A$1:$A$30)<=$D$1))+1

データが入力されていないときは空白表示するように適宜数式を修正してください。

回答No.2

mt2008

土曜日が営業日でなければ分析ツールアドインを有効にしてWORKDAY関数を使うとすごく楽な内容なんですけどね……。

遊びで土曜日を営業日として扱う「WORKDAY関数もどき」をユーザ関数として作ってみました。
使い方はWORKDAY関数と同じです。
＃デバッグをあまりちゃんとやれていませんので、業務で使うのでしたらいろいろなパターンでしっかり確認してください。

添付の図ではA6:A20に、日曜日以外の休日リストがあります。
標準モジュールに後述のユーザ関数のコードを入れ、
B1に　=SatWorkday(A1,7,$A$6:$A$20)
C1に　=D$1-B1-INT((D$1-B1-1+WEEKDAY(B1))/7)-COUNTIFS($A$6:$A$20,">"&B1,$A$6:$A$20,"<="&D$1)+1

と入れてB1:C1を下にコピーします。

「WORKDAY関数もどき」コード

Function SatWorkday(dStart As Date, nDay As Long, rHolday As Range) As Date
　　Dim dWork As Date
　　Dim nSundayCnt, nFlag, nHol

　　'nDay日後の日付を求める
　　dWork = dStart + nDay
　　'dStartの翌日～求めた日付　の間に日曜が何日あるか確認して求めた日付に加算
　　'その日付が日曜日ならさらに+1
　　nSundayCnt = Int((dWork - dStart - 1 + Weekday(dStart)) / 7)
　　dWork = dWork + nSundayCnt
　　If Weekday(dWork) = 1 Then dWork = dWork + 1

　　nFlag = 1
　　Do While (nFlag = 1)
　　　　nFlag = 0
　　　　nHol = 0
　　　　'dStart～求めた日付　の間の祝日数
　　　　nHol = WorksheetFunction.CountIf(rHolday, "<=" & dWork)
　　　　nHol = nHol - WorksheetFunction.CountIf(rHolday, "<=" & dStart)

　　　　'dStartの翌日～求めた日付　の間に日曜が何日あるか
　　　　nHol = nHol + Int((dWork - dStart - 1 + Weekday(dStart)) / 7)

　　　　'日曜祝日を除いた日数がnDayなければ1日加算
　　　　If (dWork - dStart - nHol) < nDay Then
　　　　　　dWork = dWork + 1
　　　　　　nFlag = 1
　　　　End If
　　Loop
　　SatWorkday = dWork
End Function

回答No.1

tsubuyuki

手順を全て説明しだすとかなりの長文になってしまいますので、
他人さまのサイトへのリンクで恐縮ですが、ご容赦くださいませ。

http://www.atmarkit.co.jp/fwin2k/win2ktips/383workday/workday.html