- ベストアンサー
Workday関数のような感じで・・・
先日、VBAでのWorkday関数の使い方について教えて頂いたのですが これだと土日は自動的に省かれてしまう（省いてくれる？）ということに 今になって気がつきました。 土日が稼働日の場合もあるため Workday関数で祭日を指定する時のような感じでリストを作成し リストに書いてある日付はすべて、曜日に関わらず休日とみなす というような処理の方法はありますでしょうか？ よろしくお願いします。
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (6)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。Wendy02です。 もう既に、#6 のshiotan99 さんが、お作りなっているようですし、ちょっと見てみましたが、同じようなもので、質問者さんを混乱させたくもありませんので、私のものは、アップロードは取りやめることにしました。 ただ、私個人のやり方なら、カレンダーマスターの取得は、以下のようなコードを使って、配列変数で処理することになるだろうと思います。ただ、今、現在のままでは出来ません。加工が必要です。 それと、補足説明を読んで感じたのは、あまり、がっちりしたVBAで作るのは、ある程度の技術に自信がないと難しいように感じます。できれば、ワークシート1つをフルにご利用なさって作られたほうがよいのではないか、と思います。 '--------------------------------------------- Dim myDates() As Long Sub myDatesImport() Dim FileName As String FileName = ThisWorkbook.Path & "\" & "[カレンダーマスター.xls]" ShName = "Sheet1" For i = 1 To 20 ReDim Preserve myDates(1 To i) myDates(i) = ExecuteExcel4Macro("INDEX('" & FileName & "Sheet1'!R1C1:R20C1," & i & ",1)") If myDates(i) = 0 Then ReDim Preserve myDates(1 To i - 1) Exit For End If Next End Sub '---------------------------------------------
お礼
Wendy02さん、どうもありがとうございました！ おっしゃる通り、まだまだ初心者で全く自信のかけらもありませんので 皆さんのおかげで、本当に助かっています！ どうもありがとうございました！！！
- imogasi
- ベストアンサー率27% (4737/17070)
もしエクセルのシート上に、土日の日付が必要なら、 下記をやってみてください。一瞬で出来上がります。 相当判りやすく、単純にしてあるつもりです。 Sub teat01() j = 1 For i = #1/1/2005# To #12/31/2005# If Weekday(i) = 1 Or Weekday(i) = 7 Then Cells(j, "A") = i Cells(j, "B") = Format(i, "aaa") j = j + 1 End If Next i End Sub 年によって、期間によって #1/1/2005# To #12/31/2005# を変えてください。 Cells(j, "A") = i の”A"を出したい列の記号に変えてください。 Cells(j, "B") = Format(i, "aaa")の”B"も同じです。 j = 1 をスタート行により変えてください。 ーーー ツールーマクローVBEをクリックして 挿入ー標準モジュールで出てきた画面に上記を貼り付ける。 必要あれば上記で改造。 ーーー あと祝日や会社休業日を、上記の最下行の次から追加する。 そして、＃１でもおっしゃっいるように、VLOOKUPで引いて、土日祝休に当たるか判別できます。土日祝休の種別も判ります。
お礼
imogasiさん、ご親切にありがとうございます！ 今回の処理では、教えて頂いた内容は必要ではありませんが こんなこともできるんだと、とても感動しました！ 何かの時に利用できればと、思っています。 「もし○○が必要なら・・・」って、すごい心遣いですよね！ 自分は必要と思っていなくても、教えて頂いて「あっ！それ、使える！」なんてこともあると思いますので そういうお心遣いはとても嬉しいです！ ありがとうございました。 そして、お礼が遅くなり申し訳ありませんでした。
- shiotan99
- ベストアンサー率68% (140/203)
No.2です。 質問が理解できていなかったようです、ゴメンナサイ。 WORKDAY関数と何の関係があるのかと思っていましたが、 休日を除いた稼働日数後の日付を求めるということだったんですね。 今さらという感じですが、一応試しにつくってみました。 A1：= 開始日 A2：= 日数 ★ 休日リストが、Sheet2 の A1：A200 の範囲内にあるとして、 A3 に稼動日数後（ 稼働日数前 ）の日付を出します。 WORKDAY関数と同じように、A2（ 日数 ）が マイナスの数値なら、稼動日数前の日付を求めるようにしています。 また、A1 の開始日が休日の場合にも（ そんなケースがあるかどうかは別にして ）対応できると思います。 ◇ ↓を標準モジュールに貼り付け、test を実行してみてください。 ★ 休日リストのシート名・範囲は変更してください。 （ Sheet2 の A1：A200 になっています ） '--------------------------------- Sub test() Dim myDate As Date, Nissu As Integer If IsDate(Range("A1").Value) And IsNumeric(Range("A2").Value) Then myDate = Range("A1").Value Nissu = Range("A2").Value Range("A3").Value = fWorkday(myDate, Nissu) Else Range("A3").Value = "" End If End Sub Function fWorkday(d As Date, n As Integer) As Date Dim cntDay As Integer, ret As Integer Dim myHoliday As Range '---休日リスト Set myHoliday = Worksheets("Sheet2").Range("A1:A200") Do Until cntDay = n If n > 0 Then d = d + 1 Else d = d - 1 End If ret = WorksheetFunction.CountIf(myHoliday, d) If n > 0 Then If ret = 0 Then cntDay = cntDay + 1 End If Else If ret = 0 Then cntDay = cntDay - 1 End If End If Loop fWorkday = d Set myHoliday = Nothing End Function '--------------------------------
補足
shiotan99さん、わざわざ２度もありがとうございます。 質問内容を相手にわかるように伝えるのって、難しいですね・・・ そのせいで、shiotann99さんを混乱させてしまい申し訳ありませんでした。 教えて頂いた通りのやり方で試してみましたら、ものの見事にうまく行きました！ しかし！なのですが、またまた始めの説明の時に書いていなくて申し訳なかったのですが 開始日、日数、戻り値が１つの場合はこれでうまく行くのですが、私がやりたかったのは下記でありまして・・・ 開始日は１つ 日数１→開始日-4→戻り値１ 日数２→開始日-3→戻り値２ ・ ・ ・ これを、教えて頂いた方法を応用して使用しますと 戻り値１は大丈夫なのですが、戻り値２以降が違った値になってしまいます。 こんな説明でわからないかもしれませんので いちおう、No.3のWendy02さんへの補足に書かせて頂いていますので ホントによろしければで結構ですので お目を通して頂き、お教え下さいませ。 よろしくお願いします。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。 Wendy02 です。前回(No.1810554)は、失礼しました。締められてしまったので、ここで前回のことに触れますが、私の書いた Workday関数の問題は、以前から、ずっと引きずっていたことが頭に浮かんだので、関係ないことを書いてしまいました。ただ、実際、VBAでは、Workday関数は、私の知っている限りですが、アドイン化させるためのグローバル化するコードが抜けていると思います。でなければ、参照設定したら、Application.Run を入れなくても、そのまま使えるはずです。 そこで、今回は、ご要望のような自家製、myWorkDay 関数を作ってみました。 WorkDay関数と、遜色はないと思いますが、祭日(Holiday)ではなく、休日(WorkOffDay)をセルに入れてください。 =MYWORKDAY(Start_Date,DateCount,WorkOffday) Function myWorkDay(Start_Date As Variant, DateCount As Integer, Optional WorkOffDay As Range) 'MYWORKDAY(開始日,日数,休日) Dim myDate As Long Dim StartDate As Date Dim WorkOffDays() As Long Dim i As Integer Dim j As Integer Dim m As Integer Dim c As Variant Dim ret As Integer If IsDate(Start_Date) Then StartDate = CDate(Start_Date) End If If Not WorkOffDay Is Nothing Then For Each c In WorkOffDay If IsDate(c) Then ReDim Preserve WorkOffDays(i) WorkOffDays(i) = c.Value2 i = i + 1 End If Next End If Do myDate = StartDate + m On Error Resume Next ret = 0 ret = WorksheetFunction.Match(myDate, WorkOffDays(), 0) On Error GoTo 0 If ret = 0 Then j = j + 1 End If m = m + 1 Loop Until j > DateCount myWorkDay = StartDate + m - 1 'ループの最後で足したものを引く End Function 使い方は、VBAで使うにしても、ワークシートで使う場合でも、同じですが、VBAで使う場合は、サブルーチンで使ってください。 =MYWORKDAY("2005/12/1",10,F1:F20) 開始日、日付式に自動的に変更されます。 ワークシート上は、最初に、大文字で入れると、大文字になり、小文字で入れると後も小文字になります。
補足
Wendy02さん、またまたありがとうございます。 前回のことまで覚えていて下さって、ありがとうございます。（気にして下さっていたのに、早々に締め切ってしまって、ごめんなさい！） 素晴らしいコードをありがとうございます！ 実は私ごときレベルには理解不能でありまして、そこでよろしければ、教えて頂きたいのですが…。 書いて下さったコードは、どこに書けば良いのでしょうか？ Function myWorkDay・・・はなんとなくわかるのですが はじめの「=MYWORKDAY(Start_Date,DateCount,WorkOffday)」をどうすれば良いものか・・・。 ワークシートで使う場合、この関数（？）を戻り値を表示させたいセルに入れれば良いということなのでしょうか？ 元々説明していなかったので申し訳ありませんが、実は私が行いたい処理は下記のようなものになります。 --------------------------------------- 受注一覧「Workbooks("受注一覧").Sheets("Sheet1")」 ユーザーフォーム「Workbooks（"生産計画".xls)」 カレンダーマスター「Workbooks("カレンダーマスター").Sheets("Sheet1")」 受注一覧の23列目→出荷日 カレンダーマスターに休日が登録されているのは、Sheet1のセルA1から。（下にA2、A3・・・と） [ユーザーフォーム] 受注一覧のオーダーNo.列をキーにして項目を呼び出す。 (ここまではできてますので、これ以降を教えて下さい） ↓ Label1.Captionに出荷日 Textbox1.Textに出荷日-4の日付 Textbox2.Textに出荷日-3の日付 Textbox3.Textに出荷日-2の日付 Textbox4.Textに出荷日-1の日付 を表示させたい。 カレンダーマスターにある日付は除いて算出する。 --------------------------------------- というものなのですが、この場合でも教えて頂いた方法でできますでしょうか？ 説明がわかりにくければすみません。 もし、コードが大幅に変わるようでしたら、せっかく作成して頂きましたのに、二度手間をおかけしまして申し訳ありません。 お時間がおありになる時で結構ですので、よろしければ教えて下さい。よろしくお願いします。
- shiotan99
- ベストアンサー率68% (140/203)
- zenjee
- ベストアンサー率47% (50/106)
>リストを作成しリストに書いてある日付はすべて、曜日に関わらず休日とみなすというような処理の方法はありますでしょうか？ 休日一覧表を別シートに作成し、VLOOKUP関数等でその日が休日であるかどうか検索する方法が一般的だと思います。 私の過去の例では、10年分ぐらいの祝日一覧表に日曜日と会社休日（休日となる土曜日、年末年始、お盆など）の日付をすべて挿入した休祝日一覧表を作って置き、作業シートの要件に応じ、計上した日付が休祝日に該当するかどうかをVLOOKUP、MATCH、IF、ISERRORなどの関数を使い分けて表示しておりました。 なお、祝日一覧表は下記ＵＲＬでＤＬ可能です。 http://www.vector.co.jp/download/file/win95/personal/ff367806.html 具体的な関数の使い方は多分お分かりと思いますので、ここでは省略しますが、ご希望があれば別途回答させていただきます。
お礼
zenjeeさん、早速ご回答頂きましたのに、お礼が遅くなり申し訳ありません。 実はその日が休日であるかどうかだけでなく その日を除いて、○日前の日付を表示させたかったのです。 質問が不十分で申し訳ありません。 No.3以降の方々が書いて下さっていますので、それを参考にさせて頂くことにします。 どうもありがとうございました。
お礼
shiotan99さん、どうもありがとうございました。 私がやりたかったことが、スッキリ解決しました！ 本当に本当に大感謝しております。 どうもありがとうございました！！！