エクセル2010以降バージョンについて教えてください。


A~P列、13319行のデータシートです。
E列に日付(表示形式・期間 1998/4/23~2018/5/7 シリアル時系列ですが、
同じ日付のデータが複数あったり、存在しない日付もあり)が入っています。
上記E列日付データをキーにして、国民の祝日・休日の関係で月曜日以外の曜日が週の開始日
となっている日の行データを抽出・表示する方法を教えてください。
(該当する例 2016/5/6(金)、2018/1/4(木)、2018/5/1(火)等)

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2018/05/09 12:12:31
  • 終了:2018/05/15 22:22:17
id:anihc

質問者から

anihc2018/05/10 10:24:17

可能ならVBA、関数でしょうか?ごくごく簡単なVBA、関数ならなんとか理解できるレベルです。

ベストアンサー

id:a-kuma3 No.1

a-kuma3回答回数4752ベストアンサー獲得回数20392018/05/10 14:40:20

ポイント300pt

「週の始まりが」ってところは、関数だとかなり力業です。

=CHOOSE(WEEKDAY($E2,2),
    FALSE,                                                          ' 月曜
    $E2 が休みじゃなくて、E2-1 が休み,                              ' 火曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 が休み,                     ' 水曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 と $E2-3 が休み,            ' 木曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 と $E2-3 と $E2-4 が休み,   ' 金曜
    FALSE,                                                          ' 土曜
    FALSE                                                           ' 日曜
)

WEEKDAY 関数 の第2引数に 2 を指定すると、「1 (月曜) ~ 7 (日曜) の範囲の整数」で曜日を表す数字を返します。

休みかどうかは、別にシートを用意しておいて、ISNA + VLOOKUP で判定します。
「祝日」という名前のシートの A列に祝日(含む、振替休日)の日付が並んでるとして、
「休みじゃない」の判定は、

=ISNA(VLOOKUP($e2,祝日!$A:$A,1,FALSE))

「休み」の判定は、その逆なので、

=NOT( ISNA(VLOOKUP($e2,祝日!$A:$A,1,FALSE)) )

これを、合成すると、

=CHOOSE(WEEKDAY($E2,2),
    FALSE,
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-4,祝日!$A:$A,1,FALSE)))
    ),
    FALSE,
    FALSE
)

一行で書くと

=CHOOSE(WEEKDAY($E2,2), FALSE, AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-4,祝日!$A:$A,1,FALSE)))), FALSE, FALSE)


この式を P列以降の適当な列の2行目に貼り付けて、下までコピーしてください。
フィルターで TRUE だけに絞り込むと、望む結果が得られると思います。


あとは、1998年から2018年の間の祝日一覧を手に入れる必要があります。

”祝日一覧” でググってみると、以下のようなページがみつかります。

振替休日も記載されているのですが、対象が祝日なので正月三が日が記載されていません(質問の例を見る限り、三が日は休みの扱いですよね)。
20年分なので、手打ちでも大したことはありませんが、unix 系の環境があれば、以下のワンライナーで手に入れられます。

seq 1998 2018 | awk '{print $0 "/1/2"}' | sed -e p -e 's/2$/3/'


この休日の定義(土日、祝日、振替休日、正月三が日)で、1998/4/23~2018/5/7 の全ての日付にたいして試してみると、条件を満たす日付は 129日ありました。
頻出の火曜を除くと、16日。

1998/5/6 (水)
1999/5/6 (木)
2001/1/4 (木)
2004/5/6 (木)
2006/1/4 (水)
2007/1/4 (木)
2008/5/7 (水)
2009/5/7 (木)
2009/9/24 (木)
2010/5/6 (木)
2012/1/4 (水)
2014/5/7 (水)
2015/5/7 (木)
2015/9/24 (木)
2017/1/4 (水)
2018/1/4 (木)

祝日と振り替え休日だけで四連休は今のところありませんから、金曜日はヒットしません。
質問では、該当する例として「2016/5/6(金)」が挙げられていますが、2016/5/2(月) が祝日ではないので、該当しないと思います。
実行したい対象のデータでは、GW が 5/1、5/2 も連休扱いということであれば、祝日シートにデータを追加することで対応できます。

その他の回答(0件)

id:a-kuma3 No.1

a-kuma3回答回数4752ベストアンサー獲得回数20392018/05/10 14:40:20ここでベストアンサー

ポイント300pt

「週の始まりが」ってところは、関数だとかなり力業です。

=CHOOSE(WEEKDAY($E2,2),
    FALSE,                                                          ' 月曜
    $E2 が休みじゃなくて、E2-1 が休み,                              ' 火曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 が休み,                     ' 水曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 と $E2-3 が休み,            ' 木曜
    $E2 が休みじゃなくて、E2-1 と $E2-2 と $E2-3 と $E2-4 が休み,   ' 金曜
    FALSE,                                                          ' 土曜
    FALSE                                                           ' 日曜
)

WEEKDAY 関数 の第2引数に 2 を指定すると、「1 (月曜) ~ 7 (日曜) の範囲の整数」で曜日を表す数字を返します。

休みかどうかは、別にシートを用意しておいて、ISNA + VLOOKUP で判定します。
「祝日」という名前のシートの A列に祝日(含む、振替休日)の日付が並んでるとして、
「休みじゃない」の判定は、

=ISNA(VLOOKUP($e2,祝日!$A:$A,1,FALSE))

「休み」の判定は、その逆なので、

=NOT( ISNA(VLOOKUP($e2,祝日!$A:$A,1,FALSE)) )

これを、合成すると、

=CHOOSE(WEEKDAY($E2,2),
    FALSE,
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE)))
    ),
    AND(
        ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)),
        NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE))),
        NOT( ISNA( VLOOKUP($e2-4,祝日!$A:$A,1,FALSE)))
    ),
    FALSE,
    FALSE
)

一行で書くと

=CHOOSE(WEEKDAY($E2,2), FALSE, AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE)))), AND( ISNA( VLOOKUP($e2,祝日!$A:$A,1,FALSE)), NOT( ISNA( VLOOKUP($e2-1,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-2,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-3,祝日!$A:$A,1,FALSE))), NOT( ISNA( VLOOKUP($e2-4,祝日!$A:$A,1,FALSE)))), FALSE, FALSE)


この式を P列以降の適当な列の2行目に貼り付けて、下までコピーしてください。
フィルターで TRUE だけに絞り込むと、望む結果が得られると思います。


あとは、1998年から2018年の間の祝日一覧を手に入れる必要があります。

”祝日一覧” でググってみると、以下のようなページがみつかります。

振替休日も記載されているのですが、対象が祝日なので正月三が日が記載されていません(質問の例を見る限り、三が日は休みの扱いですよね)。
20年分なので、手打ちでも大したことはありませんが、unix 系の環境があれば、以下のワンライナーで手に入れられます。

seq 1998 2018 | awk '{print $0 "/1/2"}' | sed -e p -e 's/2$/3/'


この休日の定義(土日、祝日、振替休日、正月三が日)で、1998/4/23~2018/5/7 の全ての日付にたいして試してみると、条件を満たす日付は 129日ありました。
頻出の火曜を除くと、16日。

1998/5/6 (水)
1999/5/6 (木)
2001/1/4 (木)
2004/5/6 (木)
2006/1/4 (水)
2007/1/4 (木)
2008/5/7 (水)
2009/5/7 (木)
2009/9/24 (木)
2010/5/6 (木)
2012/1/4 (水)
2014/5/7 (水)
2015/5/7 (木)
2015/9/24 (木)
2017/1/4 (水)
2018/1/4 (木)

祝日と振り替え休日だけで四連休は今のところありませんから、金曜日はヒットしません。
質問では、該当する例として「2016/5/6(金)」が挙げられていますが、2016/5/2(月) が祝日ではないので、該当しないと思います。
実行したい対象のデータでは、GW が 5/1、5/2 も連休扱いということであれば、祝日シートにデータを追加することで対応できます。

  • id:ken3memo
    >上記E列日付データをキーにして
    E列には
    2018/4/23(月)
    2018/4/23(月)など重複する日付もあって、
    2018/4/24
    2018/4/26↑25日の日付が無い場合もあります?
    2018/4/27(金)
    2018/5/1(火)※
    2018/5/2(水)
    2018/5/7(月)
    2018/5/8(火)
    が入っていて、E列の日付で週の始まりが月曜日以外のデータを判断して取り出す処理ですか?
    あっ違うか、この条件だと、月曜日にデータが抜けていたら国民の休日以外でも判断してしまうか・・・

  • id:usamin5885

    やりたいことが微妙に曖昧だと思います。

    2018/4/30(月祝)
    2018/5/1(火)
    の2つの行がある場合、4/30を抽出したいですか?それとも5/1を抽出したいですか?それとも、4/30と5/1どちらも抽出されないようにしたいですか?


    2018/4/30(月祝)
    の行はなくて、
    2018/5/1(火)
    2018/5/2(水)
    の2つの行がある場合、5/2は抽出せず、5/1だけを抽出したいですか?それとも、5/1と5/2どちらも抽出したいですか?


    2018/4/30(月祝)
    2018/5/1(火)
    の行はなくて、
    2018/5/2(水)
    の行がある場合、5/2を抽出したいですか?それとも、何も抽出されないようにしたいですか?
  • id:anihc
    みなさま

    かなり詳細なご回答、コメントありがとうございます。
    また、私のやりたいことが言葉足らずですみません。

    1)
    2018/4/30(月祝)2018/5/1(火)の2つの行がある場合、4/30を抽出したいですか?それとも5/1を抽出したいですか?それとも、4/30と5/1どちらも抽出されないようにしたいですか?
    →5/1だけを抽出したいです。

    2)
    2018/4/30(月祝)の行はなくて、2018/5/1(火)2018/5/2(水)の2つの行がある場合、5/2は抽出せず、5/1だけを
    抽出したいですか?それとも、5/1と5/2どちらも抽出したいですか?
    →5/1だけを抽出したいです。

    3)
    2018/4/30(月祝)2018/5/1(火)の行はなくて、2018/5/2(水)の行がある場合、5/2を抽出したいですか?それとも、
    何も抽出されないようにしたいですか?
    →何も抽出されないようにしたいです。

    4)その他
    ランダムに日が存在しなかったり、同じ日の行が複数存在しますが、土日、国民の祝日・休日、年末年始(12/29~1/3)の
    行は存在しないデータです。

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


  • id:Z1000S
    a-kuma3さんとはちょっと違ったアプローチで。
    祝日データを用意するのは一緒。

    1.別のワークシートに、祝日データを用意する。(仮に、Sheet2とする)
    http://addinbox.sakura.ne.jp/holiday_topic.htm
    ↑ここの祝日一覧で生成して、正月三ヶ日とか不足している分を追加しておく
    2.元のワークシート(仮にSheet1とする)のE列の日付の週の月曜日の日付を別の列に用意する。
    E1の場合、E1+2-Weekday(E1)
    3.COUNTIFS関数を使って、Sheet2の祝日データから、
    2項で求めた日付以上で、かつ
    E1の日付未満の数をカウントする。
    4.WEEKDAY(E1)>2でかつ
    3項の値が、Weekday(E1)-2と一致する行を抽出する。

    こんな感じでいいのかなぁ?
    いつもなら、VBAでゴリゴリやっちゃうんで、ワークシート関数はあまり自信がない。
    =IF(WEEKDAY(E1)<=2,"",IF(COUNTIFS(Sheet2!A$1:A$353,">="&E1+2-WEEKDAY(E1),Sheet2!A$1:A$353,"<"&E1)<>WEEKDAY(E1)-2,"","○"))

    Sheet2の範囲は、使用するデータに合わせて下さい。
  • id:Z1000S
    もしかして、「E1が祝日じゃない」も必要?
  • id:Z1000S
    上記内容をVBAでやると、以下のような感じ。

    Dictionaryを使用しているので、VBEの参照設定の画面で
    Microsoft Scripting Runtime
    にチェックマーク入れて下さい。
    方法がわからないようであれば
    http://www.relief.jp/docs/vba-refer-for-dictionary-object.html
    あたりを参考に。

    実行する環境に合わせて、最初の方のConstの値を確認して下さい。
    実行する際に影響のありそうなところは網羅したつもり。
    (コメントを参考にしていただければ、わかるとは思いますが)
    違うようであれば修正してから実行して下さい。
    抽出用マークを付ける列とかは、こちらの環境で適当に決めているので、要確認です。

    実行するのは、selectFirstWeekdayOfTheWeek
    イミディエイトウィンドウ(VBEの画面でショートカット Ctrl+G で表示されます)で、
    call selectFirstWeekdayOfTheWeek
    と入力して、Enterキーで実行してもらえれば、手っ取り早く実行できます。
    (Publicで宣言しているのが他にないから、これしか実行できないと思いますが。)

    エラー処理は入れていないので、エラーが出た場合は、コードなりデータなり(?)を修正して下さい。
    実行後に、フィルターかけて抽出して貰えれば、ご所望のデータが抽出されてる(かな?)

    Do Until sDate = ""
    あたりにブレークポイントを設定して、F8キーでステップ実行してみれば
    どういう処理をしているかわかりやすいかも・・・

    >|vb|
    Option Explicit

    '参照設定
    'Microsoft Scripting Runtime

    '祝日データのシート
    Private Const HOLIDAYS_SHEET_NAME As String = "Sheet2"

    '祝日データの列
    Private Const HOLIDAYS_COLUMN_SRC As String = "A"

    '祝日データのヘッダ行数
    Private Const HEADER_ROWS_SRC As Long = 0

    'チェックするデータのあるシート
    Private Const LIST_SHEET_NAME As String = "Sheet1"

    'チェックする日付データの列
    Private Const HOLIDAYS_COLUMN_LIST As String = "E"

    '抽出用マークを付ける列
    Private Const FIRST_WEEKDAY_OF_THE_WEEK_COL As String = "K"

    '抽出用マーク
    Private Const TARGET_MARK As String = "○"

    'チェックするデータのヘッダ行数
    Private Const HEADER_ROWS_LIST As Long = 0


    Public Sub selectFirstWeekdayOfTheWeek()

    Dim dicHoliday As New Dictionary
    Dim lRow As Long
    Dim lCol As Long
    Dim sDate As String
    Dim dtDate As Date
    Dim dtMonday As Date
    Dim dtWork As Date
    Dim lHolidays As Long

    '祝日をDictionaryに追加
    Call addHoliday(dicHoliday)

    '列変換(アルファベット→数値)
    lCol = convertColumnAlpha2Number(HOLIDAYS_COLUMN_LIST)

    With ThisWorkbook.Worksheets(LIST_SHEET_NAME)
    '開始行
    lRow = HEADER_ROWS_LIST + 1

    '日付取得
    sDate = .Cells(lRow, lCol).Value

    '日付データが無くなるまでループ
    Do Until sDate = ""
    dtDate = CDate(sDate)

    '取得した日付が祝日(Dictionaryに登録済み)でなければ処理する
    If dicHoliday.Exists(dtDate) = False Then
    '取得した日付が火曜日以降なら処理する
    If Weekday(dtDate) > vbMonday Then
    dtWork = dtDate
    '取得した日付のある週の月曜日
    dtMonday = getMondayOfTheWeek(dtDate)

    '祝日数クリア
    lHolidays = 0

    Do
    '前日を計算
    dtWork = DateAdd("d", -1, dtWork)

    If dicHoliday.Exists(dtWork) = True Then
    '祝日ならカウントアップ
    lHolidays = lHolidays + 1
    End If

    '月曜日までループ
    Loop Until dtWork = dtMonday

    If lHolidays = Weekday(dtDate) - 2 Then
    '対象マーク付加
    .Cells(lRow, FIRST_WEEKDAY_OF_THE_WEEK_COL).Value = TARGET_MARK
    End If
    End If
    End If

    lRow = lRow + 1

    sDate = .Cells(lRow, lCol).Value
    Loop
    End With

    Debug.Print "Done."

    End Sub

    Private Sub addHoliday(ByRef dic As Dictionary)

    Dim lRow As Long
    Dim lCol As Long
    Dim sHoliday As String

    lCol = convertColumnAlpha2Number(HOLIDAYS_COLUMN_SRC)

    With ThisWorkbook.Worksheets(HOLIDAYS_SHEET_NAME)
    lRow = HEADER_ROWS_SRC + 1

    sHoliday = .Cells(lRow, lCol).Value

    Do Until sHoliday = ""
    dic.Add CDate(sHoliday), ""

    lRow = lRow + 1

    sHoliday = .Cells(lRow, lCol).Value
    Loop
    End With

    End Sub

    Private Function getMondayOfTheWeek(ByVal dtTarget As Date) As Date

    Dim lOffset As Long

    lOffset = vbMonday - Weekday(dtTarget)

    getMondayOfTheWeek = DateAdd("d", lOffset, dtTarget)

    End Function

    Private Function convertColumnAlpha2Number(ByVal sAlpha As String) As Long

    convertColumnAlpha2Number = ThisWorkbook.Worksheets(1).Columns(sAlpha).Column

    End Function
    ||<

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません