エクセル関数で日にちを出すには?

Question

IF関数とLEFT関数とWORKDAY関数で納期の日にちから○営業日には入材から完成までを表したく、検査あり・なしで日程が変わるのですが、図のように4パターンを関数で表せません。（※WORKDAY関数は別シートに社休日として入れてあります）

詳しい方おられましたら教えて頂けないでしょうか?よろしくお願いいたします。

tatsumaru77 · Accepted Answer

ハイフン以降をカットして比較するようにしました。前のは破棄してください。 --------------------------------------------- Option Explicit Const TL2row As Long = 10 '見出し２の行番号 Const Maxcol As Long = 15 '日付の最大列（１番右側の日付の列番号） Dim maxrow1 As Long '納期側最大行 Dim maxrow2 As Long '型番・検査毎の日数側 Dim ws As Worksheet '処理対象シート Public Sub 納期対応日付設定() Dim wrow As Long Dim trow As Long Dim wcol As Long Dim nouki As Variant Dim nisuu As Variant Dim addr As String Dim kata As Variant Set ws = Worksheets("進捗1") maxrow1 = ws.Cells(TL2row - 1, "B").End(xlUp).row 'B列最大行取得（納期側） maxrow2 = ws.Cells(Rows.Count, "B").End(xlUp).row 'B列最大行取得（日数側） addr = ws.Cells(maxrow1, Maxcol).Address(False, False) ws.Range("E5:" & addr).Value = "" '日付をクリア For wrow = 5 To maxrow1 nouki = ws.Cells(wrow, "A").Value If nouki <> "" Then kata = Split(ws.Cells(wrow, "B").Value, "-") trow = getKataRow(kata(0), ws.Cells(wrow, "C").Value) If trow = -1 Then MsgBox ("<" & kata(0) & "><" & ws.Cells(wrow, "C").Value & ">に対応する日数が未登録です") Exit Sub End If For wcol = 5 To Maxcol nisuu = ws.Cells(trow, wcol).Value If nisuu <> "" And IsNumeric(nisuu) = True Then ws.Cells(wrow, wcol).Value = WorksheetFunction.WorkDay(nouki, nisuu, Worksheets("社休日").Range("B2:B1000")) End If Next End If Next MsgBox ("完了") End Sub '型番・検査有無に対応する行番号を取得 Private Function getKataRow(ByVal kata As String, ByVal kensa As String) As Long Dim wrow As Long getKataRow = -1 For wrow = TL2row + 2 To maxrow2 If kata = ws.Cells(wrow, "B").Value And kensa = ws.Cells(wrow, "C").Value Then getKataRow = wrow Exit Function End If Next End Function

tatsumaru77 · Answer

>最初の画像には入れてなかったのですが、型式の後、機番が続くのですが、「*LF26」のみの情報を照合させるようにはできますでしょうか。

画像がよく見えないのですが、
上の型式は　＊LF26-215
下の型式は　＊LF26
のようになっていると理解してあってますか。
型式と機番はハイフン(-)でつながれていることで間違いないでしょうか。
それであれば、ハイフン以降をカットして比較すれば可能です。

tatsumaru77 · Answer

以下のマクロを標準モジュールに設定してください。

Const TL2row As Long = 10 '見出し２の行番号
は見出し２の開始行番号です。画像の通り現在は１０行目にしてあります。
もし、１００行目にしたい場合は、１０を１００に変えてください。
Const Maxcol As Long = 15 '日付の最大列
は日付を設定する最大列です。現在はO列に対応しています。
もし、列が１つ増えてP列まで設定したいなら、１５を１６に変えてください。

------------------------------------
Option Explicit

Const TL2row As Long = 10 '見出し２の行番号
Const Maxcol As Long = 15 '日付の最大列（１番右側の日付の列番号）
Dim maxrow1 As Long '納期側最大行
Dim maxrow2 As Long '型番・検査毎の日数側
Dim ws As Worksheet '処理対象シート
Public Sub 納期対応日付設定()
Dim wrow As Long
Dim trow As Long
Dim wcol As Long
Dim nouki As Variant
Dim nisuu As Variant
Dim addr As String
Set ws = Worksheets("進捗1")
maxrow1 = ws.Cells(TL2row - 1, "B").End(xlUp).row 'B列最大行取得（納期側）
maxrow2 = ws.Cells(Rows.Count, "B").End(xlUp).row 'B列最大行取得（日数側）
addr = ws.Cells(maxrow1, Maxcol).Address(False, False)

ws.Range("E5:" & addr).Value = "" '日付をクリア
For wrow = 5 To maxrow1
nouki = ws.Cells(wrow, "A").Value
If nouki <> "" Then
trow = getKataRow(ws.Cells(wrow, "B").Value, ws.Cells(wrow, "C").Value)
If trow = -1 Then
MsgBox ("<" & ws.Cells(wrow, "B").Value & "><" & ws.Cells(wrow, "C").Value & ">に対応する日数が未登録です")
Exit Sub
End If
For wcol = 5 To Maxcol
nisuu = ws.Cells(trow, wcol).Value
If nisuu <> "" And IsNumeric(nisuu) = True Then
ws.Cells(wrow, wcol).Value = WorksheetFunction.WorkDay(nouki, nisuu, Worksheets("社休日").Range("B2:B1000"))
End If
Next
End If
Next
MsgBox ("完了")
End Sub
'型番・検査有無に対応する行番号を取得
Private Function getKataRow(ByVal kata As String, ByVal kensa As String) As Long
Dim wrow As Long
getKataRow = -1
For wrow = TL2row + 2 To maxrow2
If kata = ws.Cells(wrow, "B").Value And kensa = ws.Cells(wrow, "C").Value Then
getKataRow = wrow
Exit Function
End If
Next
End Function

tatsumaru77 · Answer

>1、別々の日数を適用したいです。後、型式なんですが分かり易くする為に*Aと表記したのですが、実際の型式は*LF25と表記しております。

ということは、全桁比較という形でよろしいでしょうか。
納期側へ
*LF25　あり
*LF25　なし

日数側へ
*LF25　あり
*LF25　なし
と登録すれば、*LF25専用の日数が適用されます。

tatsumaru77 · Answer

マクロの場合、シート名も指定したほうが間違いがなくてよいです。
この進捗表のシート名はなんでしょうか。

tatsumaru77 · Answer

１．念のため確認ですが型式の先頭２桁で比較すると
*A1000と*A2000は同じ日数が適用されますが、問題ないでしょうか。
もし、別々の日数を適用したいなら、全桁分比較すれば可能です。
その場合は、日数を定義する側に
*A1000　あり
*A2000　あり
*A1000　なし
*A2000　なし
のように２つの型番ぶんを登録する必要があります。
型番があまりにも多いなら、先頭２桁が良いですが、
型番が少ないなら全桁比較でも問題ないかとおもいます。

２．見出しの部分ですが
１）納期側の見出し（３～４行）はこれで問題ありません。
２）型番・検査有無対応の日数側（１０～１１行、１５～１６行）は
２つありますが、これを１つにすることは可能でしょうか。
１０～１１行の見出し行は残して、１５～１６行は、データ行として使いたいのですがいかがでしょうか。
現在は１０～１１行が見出し行ですが、これを１００～１０１行のように変えることは可能です。
この値は、マクロ内の定義を変えれば簡単に変えられるように作り込みます。

３．A列の納期が空白であることは、問題ないですが、
B,C列の型式、検査有無が空白のケースは認められません。（歯抜けの状態）
その場合は、その行へ後ろの行を詰めることになりますが宜しいでしょうか。

tatsumaru77 · Answer

No3です。
マクロで実現することも可能です。
マクロにした場合の利点は、マクロを実行したときだけ動作するのでパソコンに負荷がかからないことです。
ユーザー定義関数にした場合の利点は、A列の日付を変えると自動的にE～O列の日付が設定されるので、いちいち、マクロを呼び出さなくて済むことです。

A列の日付を変えたとき、マクロを呼び出すのがおっくうでなければ、マクロの方が良いと思います。
マクロにした場合は、レイアウトも今後のことを考えて見直した方がよいと思います。（今のままでも可能ですが）
現在、日付を設定したいのは５～８行ですが、将来的に、型式が増えることはないのでしょうか。増えるとすると、もっと、下へ増やせるようなレイアウトの方がよいと思います。

１つ、確認なのですが、
型式及び検査のありなしで１２，１３、１７、１８行のどれかを決定するとき、型式の先頭２文字及びありなしの先頭２文字で判定していますが、これは理由があるのですか。
型式に「*A1000」「*A2000]などがあって両方を同じように扱いたいならわかりますが、そのようには見えません。
また、検査の有り無しについては、先頭２文字でなく単純に"あり"、"なし"で判断すれば良いと思いますが、いかがでしょうか。

tatsumaru77 · Answer

>ユーザー定義関数というものがあるのですね。vbaに関数を入れるのでしょうか？
はい。そうです。
標準モジュールに
Public Function MyFunction()
・・・・
End Function
のように登録し、それを関数として、excelから呼び出します。

そのため、登録したブックはマクロを含むため、拡張子はxlsxでは保存できません。xlsmとして保存します。
book1.xlsxではなくbook1.xlsmになります。

tatsumaru77 · Answer

ユーザー定義関数で良ければ提供可能ですが、いかがでしょうか。
あなたのexcelの環境がマクロの使用を許していれば、ユーザー定義関数が使えます。
使い方は
E5へ
=MyFunction()
のように設定し、O８まで、オートフィルすればOKです。（関数名は適当な１例です。この関数名である必要はありません）