いつもお世話になります。
OS Win10 エクセル2016 です。
第二、第四の土曜の「休日出勤」は下記の F2 の数式で求められるのですが
第一、第三の土曜の「休日出勤」を求めるのにはどのように変更すればいいのでしょうか。
ご指導いただけませんでしょうか。
F2 =DATE(A1,C1,1)
G2 =IF(OR(AND(MOD(CEILING(DAY(F2),7),14)=0,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")
H2 =IF(ISNA(VLOOKUP(F2,祝,4,FALSE)),"",VLOOKUP(F2,祝,4,FALSE))
インターネット等で MOD CELLING を調べたりあの手この手で修正したりしました。
旨くゆきません。
この関数もインターネットから得た情報です。
※ 参照図は一部の行は 非表示 にしています。
No.8ベストアンサー
- 回答日時:
No.6です。
>3月31日はやはりだめでした。
>5週目はいずれの方式も営業とでます。
ん?
タイトルは第一・第三土曜となっていますが、
結局第一・第三土曜だけでなく、奇数土曜はすべて「休日出勤」としたい訳ですけね?
それではOR関数の条件を一つ加えるだけでいけるはずです。
G2セルの数式を
=IF(OR(WEEKDAY(F2)=1,COUNTIF(祝,F2),F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+7,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+21,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+35),"休日出勤","営業")
としてみてください。
※ 数式の細かい説明は割愛しますが、やり方としては
=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)
でF列に表示されている日付の前月最終土曜が求められますので、
その日付に7・21・35をプラスしたのが第一・第三・第五土曜になります。
F列シリアル値がそれに当てはまれば「休日出勤」のOR関数がTRUEとなります。
当方がお示しした数式は作業用の列は不要です。m(_ _)m
No.7
- 回答日時:
》 第一、第三の土曜の「休日出勤」を求めるのには
》 どのように変更すればいいのでしょうか。
参考までに、第1、2、3、4、5 の土曜日を求める式を示しておきます。
C2: =DATE($A2,C$1,$B2*7-WEEKDAY(DATE($A2,C$1,-5),3))
A列:年
1行目:月
B列:第n曜日の数値n
式中の「-5」の「5」は Q列に示す曜日指数です。
ちなみに、[条件付き書式]で空欄に見せているセルは第5土曜が存在しないことを指しています。
【独白】数時間前に同じ内容で投稿したつもりでいたけど見当たらないので、また投稿した次第です。
No.6
- 回答日時:
こんばんは!
第一・第三土曜日・日曜日・祝 の場合は「休日出勤」とし、
それ以外は「営業」になれば良いのですね?
G2セルに
=IF(OR(WEEKDAY(F2)=1,COUNTIF(祝,F2),F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+7,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+21),"休日出勤","営業")
という数式を入れフィルハンドルで下へコピー!
これでどうでしょうか?m(_ _)m
No.5
- 回答日時:
「F2の日を7の倍数に切り上げた数」と28のうち、小さい方を14で割った余りがゼロでない場合…という意味の式なのですが、chabi
んが入力した式には、「小さい方」が抜けていますよ!No.4
- 回答日時:
№2を修正しました。
G2に貼り付けて、コピーしてください。
=IF(OR($F2=DATE($a$1,$c$1,1)+(7-WEEKDAY(DATE($a$1,$c$1,1),1)),$F2=DATE($a$1,$c$1,1)+(7-WEEKDAY(DATE($a$1,$c$1,1),1)+14),$F2=DATE($a$1,$c$1+1,1)+(7-WEEKDAY(DATE($a$1,$c$1+1,1),1)),$F2=DATE($a$1,$c$1+1,1)+(7-WEEKDAY(DATE($a$1,$c$1+1,1),1)+14),WEEKDAY($F2)=1,$H2=1),"休日出勤","営業")
これで、2か月分対応できます。
3ヶ月以上を対応させたい場合は、全体的に修正しないといけませんね。
No.3
- 回答日時:
次の部分を書き換えれば、「第二第四土曜日」が「それ以外の土曜日」になります。
MOD(CEILING(DAY(F2),7),14)=0
↓
MOD(CEILING(DAY(F2),7),14)<>0
しかし、今年の3月みたいに第五土曜日がある場合は、それも休日出勤になってしまうので、第五土曜日を営業とするために、もうひと捻り加えます。
MOD(MIN(CEILING(DAY(F2),7),28),14)<>0
No.2
- 回答日時:
1ヶ月分(画像の5月分)だけであれば、こんな感じでしょうか。
G2に貼り付けて、コピーしてください。
=IF(OR($F2=$F$2+(7-WEEKDAY($F$2,1)),$F2=$F$2+(7-WEEKDAY($F$2,1)+14),WEEKDAY($F2)=1,$H2=1),"休日出勤","営業")
ただ月が変わると対応していません。画像の6月分は別シートに作ってください。
1シートで完結させるには、月の判定も必要ですね。
No.1
- 回答日時:
単純にカレンダーがA列にあり、B列に第一、第三土曜を「休日出勤」と表示させたいのであれば、B1に以下、後は下にドラッグ
=IF(AND(OR(INT((DAY(A1)-1)/7)=0,INT((DAY(A1)-1)/7)=2),WEEKDAY(A1)=7),"休日出勤","")
見当違いでしたらスルーして下さい。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの祝日に色が反映しない 4 2022/05/18 09:58
- Excel(エクセル) 出勤簿の土、日、休日に色付けできない 2 2022/08/04 20:10
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Visual Basic(VBA) VBAで列を削除 3 2023/02/01 11:00
- Excel(エクセル) 週毎の集計 2 2023/08/04 16:56
- Excel(エクセル) エクセル関数のXlookupのフィルハンドル機能(類した機能でも可)を知りたいです。 3 2022/09/20 20:02
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 数学 「FFTの基本は、DFTはサンプル数Nが偶数なら 2つのDFTに分解できるということ。 分解するとD 3 2022/03/31 21:01
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
第一、第三、土曜日のセルに条件付書式設定で色を付けたいのですが、そのよ
Excel(エクセル)
-
エクセルで毎月第2週日の水曜日に〇が表示される 関数の組み合わせを教えてくれませんか? 毎週水曜日な
Excel(エクセル)
-
エクセルで第2、第4土曜日を抽出する方法
Excel(エクセル)
-
-
4
Excel2010第二、第四土曜日に〇印を
その他(Microsoft Office)
-
5
エクセルの質問です。
Excel(エクセル)
-
6
エクセルの数式で第三水曜日に色を付ける
Excel(エクセル)
-
7
第2、第4火曜日を抽出する方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelカスタム関数(アドイン登...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
早速ご回答をいただきありがとうございます。
私の説明不足で済みません。
試しましたが第一と第二に「休日出勤」しか表示されません。
私のお願いは G列 の様に土日及び祝日の休みは 「休日出勤」 で
それ以外は 「営業」 と表示させたいのです。
ただし G列 の土曜日は第二、第四の日ですが。
再度確認させてください。
第一、第三の土曜日+日曜日+祝日 "休日出勤"
それ以外は "営業" に表示する関数です。
ご苦労ですが再度ご指導お願いできませんか。
※H列の1は祝日を表しています。
折角ご回答いただいて申し訳ありませんが月度をまたぎたいのです。
20日〆等からまたぎますので。
よろしくお願いします。
ご指導をありがとうございます
早速試しました。
3月4月と5月6月にして試し
↓この数式は 問題あり と警告が出ます
=IF(OR(AND(MOD(CEILING(DAY(F2),7),28),14,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")
↓この数式は 2週目に 「休日出勤」 になります
=IF(OR(AND(MOD(CEILING(DAY(F2),7),28),14)<>0,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")
3月は5週あれますが、3月31日がだめでした。
いつもお世話になります。
試しましたが3月は土曜が5週あり、「営業」と表示されました。
すごくうれしいです。
余計なことですが参考に見てください。 自画自賛です。(笑い)
折角皆様にご指導いただきながら恐縮ですが、
苦しんだ挙句私なりに考えたもので。
何かアドバイスをいただければ幸いです。
G2 =IF(N2=1,"休日出勤","")
I2 =IF(J2=1,COUNTIF($J$2:J2,J2),"")
J2 =WEEKDAY(F2,16)
K2 =IF(I2<>"",IF(MOD(I2,2)=0,"",1))
L2 =IF($J2=2,WEEKDAY($F2,1),"")
M2 =IF(ISNA(VLOOKUP(F2,祝,4,FALSE)),"",VLOOKUP(F2,祝,4,FALSE))
N2 =IF(SUM(K2:M2)>=1,1,"")
No.6さんに
かんちがいしていました。
3月31日はやはりだめでした。
5週目はいずれの方式も営業とでます。
ごめんなさい
お許しのほど
小生の考えたものは おっけい です。