[[20140326172404]] 『土日に連なる祝祭日の判定』(さいき) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『土日に連なる祝祭日の判定』(さいき)

 お世話になっております
 手作業で行っていた事を誰でも更新できるように自動化させたく思い、質問させていただきます
 土日に連なる祝祭日(主に月金)にどうすればフラグをたてられるか教えてください

 たとえば2014年でいうと、3/21,5/5,5/6,7/21,9/15・・・がそれにあたります
 祝祭日に関しては祝日シートにリストを作り、それを参照しています

 前週末平均比というものを作っておりまして、月や金が祝祭日の場合はそれに含まれるのです
 【土日祝祭日判定】で週末のみの表を作成し、前週の判定に関しては【第何週目か】を使用してできたのですが、祝祭日が含まれません

 力技で【第何週目か】と【月金】のフラグで含ませることはできると思いますが、その場合、5月の連休(火曜)に対応できません
 手作業だと、土日に連なっているものを選択すればいいだけなのですが、関数でとなるとさっぱりです

 1行目に第何週か
 【 =WEEKNUM(C1,2) 】

 2行目に土日祝祭日判定 (土曜6、日曜7、祝祭日1)
 【 =IF(WEEKDAY(C1,2)>5,WEEKDAY(C1,2),COUNTIF(祝日!$A$2:$B$18,C1))  】

 3行目にシリアル値で日付

 で横にずらっと並んでいる場合、どうすればよいのでしょうか?

 ほかに、平日平均、土日祝平均、前月同日比があり、それをクリアする為に上記構成となりました
 そのために、月の最終日、月始めが第何週目か、今何週目かを示すセルも存在します

わかりにくいですが、よろしくお願いいたします

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 どうも表の形がつかめないので
 (さいき)さんなら応用できると思い簡単な形にしてみました。

 excel2010なら WORKDAY.INTL 関数も使えそうですが、使った事がないので普通に
 A2に
 2014/1/1  以下一年の日付カレンダー
 B2に(さいき)さんの式を生かして
=IF(COUNTIF(U$2:U$18,A2),B1,WEEKNUM(A2,2))
 土日に連なる祝祭日を含む週ナンバーが出ると思います。
 C2に =IF(B2<B3,1,"") でフラグ
 どちらも下にコピー

 U$2:U$18 は祝祭日リストの範囲です。
 手抜きしてごめんなさい。

(jun53 ) 2014/03/26(水) 21:19


 jun53さん、ありがとうございます
 教えていただく身でありながら手抜きした説明ですみません
 表を細かく説明しようとすると関係ない箇所もあり、無駄なスペースをくってしまうので省略してしまいました

 教えていただいたヒントをもとに、祝祭日シートに1年のカレンダーを作成いたしました

 U2:U18 祝祭日リスト

 A列 日付        シリアル値
 B列 第何週目か     =WEEKNUM(A2,2)
 C列 土日祝祭日判定   =IF(WEEKDAY(A2,2)>5,WEEKDAY(A2,2),COUNTIF($U$2:$U$18,A2))
 D列 土日に連なる祝日  =IF(COUNTIF($U$2:$U$18,A2),D1,WEEKNUM(A2,2))
 E列 連休フラグ     =IF(D2<D3,1,"")
 F列 週末(シリアル値)  =IF(OR(AND(OR(C1=7,C3=6),C2=1),AND(C2=1,E2=1),C2>5),A2,"")

 となっています

 もう少し簡略化(特にF列の力技……トホホ)できるとは思うのですが、 やはりこれだとほかの人がしんどいのではないかと、今さっき気づきました
 カレンダーを作成していて思いついたのですが、年次の仕事として最初に週末リストを作ってしまった方が簡単明瞭でユニバーサル(笑 上司の口癖)でしょうかね……
 私自身は関数の内容を読み解くのがパズル感覚でおもしろいのですが、他の人はそうじゃないですよね
 もう少し考えてみます

(さいき) 2014/03/27(木) 12:01


 結局フラグを立てたいのは「土日に連なる祝日」だけ?
 土日はいいのかな?

 WORKDAY関数を使って日曜日から見た「1営業日後」と「1営業日前」の日付がいつになるかを見ればいいのではないかと
 思ったりもしたのだけど。

 =IF(A2="","",IF(WEEKDAY(A2,2)>5,"",IF(OR(WORKDAY(A2-WEEKDAY(A2)+1,1,$U$1:$U$50)>A2,WORKDAY(A2-WEEKDAY(A2,2)+7,-1,$U$1:$U$50)<A2),1,"")))
 
 思いつきなんで、要検証。
(笑) 2014/03/27(木) 12:58 ちょっと数式をいじった 13:14

 (笑)さん、遅くなってすみません

 地獄の新年度新資料作成が終了し、ようやっと通常業務に戻ってきました
 これも新年度資料なのに、後回しにされ…… T_T

 さて、ええっと、ほんとにもう、なんとお礼を言えばよいのかわかりませんが、本当にありがとうございます!!
 フィルコピーした途端に「うおっ!!!」って声が出ました
 WORKDAY関数を理解するのに時間がかかったポンコツです……
 本当にありがとうございました!!!

 他の作業列を削除し、こちらの式一本で完了いたしました
 なので連なる祝日だけではなく土日も表示し、週末比を計算するために何週目かをわかるようにする為、以下としました

 B1 =WEEKNUM(A2,2) 
 B2 =IF(A2="","",IF(WEEKDAY(A2,2)>5,WEEKNUM(A2,2),IF(WORKDAY(A2-WEEKDAY(A2)+1,1,$U$1:$U$50)>A2,B1,IF(WORKDAY(A2-WEEKDAY(A2,2)+7,-1,$U$1:$U$50)<A2,B3,""))))

 ※さげられたらよかったのですが、需要がないのにあがってしまい、すみませんでした

(さいき) 2014/05/13(火) 13:54


 >=IF(A2="","",IF(WEEKDAY(A2,2)>5,WEEKNUM(A2,2),IF(WORKDAY(A2-WEEKDAY(A2)+1,1,$U$1:$U$50)>A2,B1,IF(WORKDAY(A2-WEEKDAY(A2,2)+7,-1,$U$1:$U$50)<A2,B3,""))))

 これだと、5/5は「18週目」、5/6が「19週目」になってるけどいいの?
 
(GobGob) 2014/05/13(火) 17:24

 >前週末平均比というものを作っておりまして、月や金が祝祭日の場合はそれに含まれるのです

 火曜日の祝日の場合は週が変化するのなら。

 =IF(WEEKDAY(A2,2)>5,WEEKNUM(A2,2),IF(AND(COUNTIF(U:U,A2),OR(SUM(COUNTIF(U:U,A2+{1,-1}))>0,WEEKDAY(A2+{1,-1},2)={6,7})),WEEKNUM(A2,12),""))

 でもいいね。

 ※と、いいながらよく検証してないけどw
 
(GobGob) 2014/05/13(火) 17:28 ※17:38 間違い修正

 GobGobさん
 のーーーーーーーーーーーー??!!!!!Σ(>д<;)
 この日にやった時は、なぜかばっちり5/6も18週目になっていたのです
 本日ひらいたところ、ばっちり土日以外が#VALUE!となっておりました

 数式考えていただきましてありがとうございます
 ですがご指摘どおり、5/6は18週目にならなければならないのです
 もう自分でも18週目、18週目って何を言っているかわかりませんが、がんばって時間作ってもう一度考え直します
 とりあえず、自分のアホさ加減に嫌気がさしてきた木曜の午後……
(さいき) 2014/05/15(木) 14:46

 WEEKNUMをいじくって・・・とか思ったけど、せっかく5/5が18を返してるんだから
 もったいないよね。

 ってことで

 どっかの2行目 =IF(WEEKDAY(A2,2)>5,WEEKNUM(A2,2),IF(AND(COUNTIF(U:U,A2),OR(SUM(COUNTIF(U:U,A2+{1,-1}))>0,WEEKDAY(A2+{1,-1},2)={6,7})),IF(N(B1)=0,WEEKNUM(A2,2),B1),""))
 
(GobGob) 2014/05/15(木) 14:59

 > この日にやった時は、なぜかばっちり5/6も18週目になっていたのです
 > 本日ひらいたところ、ばっちり土日以外が#VALUE!となっておりました

 どの式の話?

 ↓ これ?
 B2 =IF(A2="","",IF(WEEKDAY(A2,2)>5,WEEKNUM(A2,2),IF(WORKDAY(A2-WEEKDAY(A2)+1,1,$U$1:$U$50)>A2,B1,IF(WORKDAY(A2-WEEKDAY(A2,2)+7,-1,$U$1:$U$50)<A2,B3,""))))

 $U$1:$U$50 のどこかに文字列を入力してるとか?

 U1セルを見出しにしてるんだったら、それは含めずに
 $U$2:$U$50
 にしてみて。
(笑) 2014/05/15(木) 15:14

 GobGobさん  ありがとうございます!
 とりあえず#VALUE!値になっていた原因が判明いたしました!
 祝祭日リストのU1に文字(注意事項が勝手に)入力されていました(誰やねん余計なことを…)

 こんなことなら参照を$U$2:$U$50にしておけばよかったと後悔しつつ、U1の文字入力をDeleteしたところ、#VALUE!値はなくなり、5/6も18週目になっておりました
 それがなぜだかすぐにわからないのは私が本当の意味で理解していないからでしょう
 いただいた数式もパーフェクトに働いております
 なんとか時間ができたので、今から数式大解剖にとりかかります
 本当にありがとうございました!!

 とか電話とりながら書いていたら、更新されておりました……

 笑さん
 ご指摘のとおりU1に文字入力されておりました
 もう本当にズバリご指摘の通りです
 どうもありがとうございました!!!
 きちんと理解できるように今からがんばります

(さいき) 2014/05/15(木) 15:23


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.