プロが教えるわが家の防犯対策術!

IF関数とLEFT関数とWORKDAY関数で納期の日にちから○営業日には入材から完成までを表したく、検査あり・なしで日程が変わるのですが、図のように4パターンを関数で表せません。(※WORKDAY関数は別シートに社休日として入れてあります)

詳しい方おられましたら教えて頂けないでしょうか?よろしくお願いいたします。

「エクセル 関数で日にちを出すには?」の質問画像

質問者からの補足コメント

  • うれしい

    tatsumaru77様。すごいです。このようなやり方があることに感動してます。ありがとうございます。

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

    よろしくお願いいたします。

    「エクセル 関数で日にちを出すには?」の補足画像1
    No.8の回答に寄せられた補足コメントです。 補足日時:2020/11/25 00:38

A 回答 (10件)

ハイフン以降をカットして比較するようにしました。


前のは破棄してください。
---------------------------------------------
Option Explicit

Const TL2row As Long = 10 '見出し2の行番号
Const Maxcol As Long = 15 '日付の最大列(1番右側の日付の列番号)
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
    • good
    • 0
この回答へのお礼

天才やな

早急な対応ありがとうございます。
完璧です。

ありがたく使わせて頂きます。

これをベストアンサーとさせて頂きます。

お礼日時:2020/11/25 09:00

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



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

助かりました

合ってます。

ハイフン以降をカットせず表示した上で下表の*LF26を読み取り上表で日付を表示する方法はありませんでしょうか?

お礼日時:2020/11/25 06:43

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


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

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

Const TL2row As Long = 10 '見出し2の行番号
Const Maxcol As Long = 15 '日付の最大列(1番右側の日付の列番号)
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
この回答への補足あり
    • good
    • 0

>1、別々の日数を適用したいです。

後、型式なんですが分かり易くする為に*Aと表記したのですが、実際の型式は*LF25と表記しております。

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

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

マクロの場合、シート名も指定したほうが間違いがなくてよいです。


この進捗表のシート名はなんでしょうか。
    • good
    • 0
この回答へのお礼

tatsumaru77様。シート名は進捗1でよろしくお願いいたします。

お礼日時:2020/11/24 21:58

1.念のため確認ですが型式の先頭2桁で比較すると


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

2.見出しの部分ですが
1)納期側の見出し(3~4行)はこれで問題ありません。
2)型番・検査有無対応の日数側(10~11行、15~16行)は
2つありますが、これを1つにすることは可能でしょうか。
10~11行の見出し行は残して、15~16行は、データ行として使いたいのですがいかがでしょうか。
現在は10~11行が見出し行ですが、これを100~101行のように変えることは可能です。
この値は、マクロ内の定義を変えれば簡単に変えられるように作り込みます。

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

tatsumaru77様。遅くなり申し訳ありません。

1、別々の日数を適用したいです。後、型式なんですが分かり易くする為に*Aと表記したのですが、実際の型式は*LF25と表記しております。
tatsumaru77様から教えて頂けたらそれに型式を当てはめて使用できたらと考えております。

2と3に関しましてはtatsumaru77様の提案に合わしたいと思います。

お礼日時:2020/11/24 21:57

No3です。


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

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

1つ、確認なのですが、
型式及び検査のありなしで12,13、17、18行のどれかを決定するとき、型式の先頭2文字及びありなしの先頭2文字で判定していますが、これは理由があるのですか。
型式に「*A1000」「*A2000]などがあって両方を同じように扱いたいならわかりますが、そのようには見えません。
また、検査の有り無しについては、先頭2文字でなく単純に"あり"、"なし"で判断すれば良いと思いますが、いかがでしょうか。
    • good
    • 0
この回答へのお礼

助かりました

tatsumaru77様
ご返答ありがとうございます。

マクロでできるのであれば教えて頂けるととても助かります。

型式なども増える予定なので12,13,17,18,行にあるものをもっと下の行に移すか違う列にするなど、レイアウト変更はしたいと思います。


>1つ、確認なのですが、
型式及び検査のありなしで12,13、17、18行のどれかを決定するとき、型式の先頭2文字及びありなしの先頭2文字で判定していますが、これは理由があるのですか。

「*A1000」「あり」の場合と「*A1000」「なし」の場合のパターンに対応できるようにしたいです。

>また、検査の有り無しについては、先頭2文字でなく単純に"あり"、"なし"で判断すれば良いと思いますが、いかがでしょうか。

仰る通りです。単純にあり、なしで判断すればいいですね。

ありがとうございます。初心者なので大変勉強になります。

お礼日時:2020/11/24 12:35

>ユーザー定義関数というものがあるのですね。

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

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

ユーザー定義関数で良ければ提供可能ですが、いかがでしょうか。


あなたのexcelの環境がマクロの使用を許していれば、ユーザー定義関数が使えます。
使い方は
E5へ
=MyFunction()
のように設定し、O8まで、オートフィルすればOKです。(関数名は適当な1例です。この関数名である必要はありません)
    • good
    • 0
この回答へのお礼

どう思う?

こんばんは。ご返答ありがとうございます。
ユーザー定義関数というものがあるのですね。vbaに関数を入れるのでしょうか?

お礼日時:2020/11/23 22:44

    • good
    • 0
この回答へのお礼

こんばんは、コメントありがとうございます。表のようにE5:O8にA列の納期の日にちからB列の型式とC列の検査あり・なしを判別し、入材日から完成日までの日にちが現れるようにしたいのです。
E5を以下のようにしてみたのですが、上手くいきません。
=IF($A5="","",IF(AND(LEFT($C5,2)="なし",LEFT($B5,2)="*A"),WORKDAY($A5,E$12,社休日!$B$2:$B$1000),IF(AND(LEFT($C5,2)="あり",LEFT($B5,2)="*A"),WORKDAY($A5,E$17,社休日!$B$2:$B$1000),IF(AND(LEFT($C5,2)="なし",LEFT($B5,2)="*B"),WORKDAY($A5,E$13,社休日!$B$2:$B$1000),IF(AND(LEFT($C5,2)="あり",LEFT($B5,2)="*A"),WORKDAY($A5,E$18,社休日!$B$2:$B$1000))))))

お礼日時:2020/11/23 00:21

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング