人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

●質問者: anihc
●カテゴリ:コンピュータ 学習・教育
○ 状態 :終了
└ 回答数 : 1/1件

▽最新の回答へ

質問者から

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


1 ● a-kuma3
●300ポイント ベストアンサー

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

=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.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ