仕事でエクセルを使って、
「各営業所における営業日の実績データ」を収集しているのですが、
PCを得意としていない人たちが入力していることもあり、
休業日など目的と違うところにデータを入力してしまうことがあります。
そこで、視覚的に入力するところを分かりやすくし、
それでも間違ったところに入力してしまうのを防ぐため、
条件書式と入力規則を使って、カレンダーを作りました。
年月を入力すれば自動的に日付が変わるカレンダーを作り、
日付は正しく反映されているのですが、
営業日と休業日が上手く反映されず、その原因が分かりません・・・。
当社の営業日設定なのですが、
基本的に平日と日曜が営業日で、休業日は土曜と祝日になります。
ただし、平日が祝日だった場合は休業ですが、
日曜日と祝日が重なった場合は営業日になります。
なお、年末年始(12/31~1/3)は休業日になります。
さらにややこしくなってしまうのですが、
GW中(4/28~5/7)の日曜日、年末年始(12/30~1/5)の日曜日は、
長期連休ということで休業日になっています。
エクセルのデータですが、
列Aには日付(A3が1日)、列Bには曜日が入力してあり、
列Cと列Dに実績データを入力してもらう形にしてあり、
ここに条件書式と入力規則を設定しています。
また、セルV2~W33までに各祝日のリストとその振替休日、
加えてそれ以外の休業日(12/30、1/2、1/3)の日付を記入してあります。
前置きが長くなってしまって申し訳ございません。
以下が、私の考えた条件書式と入力規則の流れです。
まず休業日を考え、入力規則のユーザー設定に、
=OR(
COUNTIF($V$2:$W$33,$A3)<>0,
MOD($A3,7)=0
)(※祝日リストに日付があるか、土曜日である)
として、さらにGW・年末年始休業の部分を、
AND(MOD($A3,7)=1,AND(MONTH($A3)=4,DAY($A3)>=28)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=5,DAY($A3)<=7)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=12,DAY($A3)>=30)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=1,DAY($A3)<=5)),
と考えました。また祝日かつ日曜日は営業日となることを、
AND(COUNTIF($V$2:$W$33,$A3)<>0,MOD($A3,7)=1),
と考えて、その逆が休業日であることから、
=OR(
COUNTIF($V$2:$W$33,$A3)<>0,
MOD($A3,7)=0,
OR(COUNTIF($V$2:$W$33,$A3)=0,MOD($A3,7)<>1),
AND(MOD($A3,7)=1,AND(MONTH($A3)=5,DAY($A3)<=7)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=4,DAY($A3)>=28)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=12,DAY($A3)>=30)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=1,DAY($A3)<=5))
)
上記を休業日として入力規則に入力しようとしましたが、
長過ぎて入らないようなので、GW・年末年始休業の部分を、
必要な該当月ごとに書き換えてみましたが、うまくいきませんでした。
ちなみに、その逆が営業日であるので、条件付き書式の数式に、
=AND(
COUNTIF($V$2:$W$33,$A3)=0,
MOD($A3,7)<>0,
AND(COUNTIF($V$2:$W$33,$A3)<>0,MOD($A3,7)=1),
OR(MOD($A3,7)<>1,OR(MONTH($A3)<>12,DAY($A3)<30))
)
と入力し、確認しましたが、やはりダメでした。
(ちなみに、「祝日かつ日曜日」があり「年末年始休業日」もある
2012年12月に設定を入力しながら結果を確認していました。)
いろいろ試行錯誤した結果、一つ一つはどうやら正しいので、
組み合わせたときに、「祝日かつ日曜日」と「祝日でない」が
同居しているのが悪いと思うのですが、何か解決策はないでしょうか?
長い文章になってしまい恐縮ですが、ご教授お願い致します。
No.2ベストアンサー
- 回答日時:
お休みの条件を整理しましょう
1.土曜日
2.日曜日ではない祝日
3.年末年始(12/31~1/3)
4.GW中(4/28~5/7)の日曜日
5.年末年始期間(12/30~1/5)の日曜日
1. =(WEEKDAY(A3)=7)
2. =AND(COUNTIF($V$2:$W$33,$A3)>0,WEEKDAY(A3)>1)
3. =OR((A3=DATEVALUE(YEAR(A3)&"/12/31")),(A3<=DATEVALUE(YEAR(A3)&"/1/3")))
4. =AND((A3>=DATEVALUE(YEAR(A3)&"/4/28")),(A3<=DATEVALUE(YEAR(A3)&"/5/7")),WEEKDAY(A3)=1)
5. =AND(OR((A3>=DATEVALUE(YEAR(A3)&"/12/30")),(A3<=DATEVALUE(YEAR(A3)&"/1/5"))),WEEKDAY(A3)=1)
この5つの条件のどれかが成立した時が休業日になります。
添付の図では、各条件をF~J列に入れて、判り易くするため1を掛けてTRUE/FALSEを1/0で表示しています。
また、E列には =MAX(F3:J3)と入れて休業日条件が1つでも成立している日に1を表示させています。
回答を参考にして無事目的通りできました!
これはエクセルの問題というより、国語の問題でしたね(^^;
画像まで付けてご丁寧に回答していただき、ありがとうございました!
No.1
- 回答日時:
こんばんは!
外していたらごめんなさい。
↓の画像で説明させていただきます。
当方使用のExcel2003の場合です。
実際の祝日・休日データがどのようになっているか判らないので
勝手に一般的な祝日のみのデータを表示しています。
(方法だけ理解していただければ良いかな!って思っています。)
画像の祝日データはV2~W24セルまではいっていますが
質問ではこの範囲を変更していただければ対応できると思います。
このデータ内に一般的な祝日以外に貴社の休日データを追加しておけば問題ないと思います。
A3セルの表示形式をユーザー定義から d としておきます。
A3セルは
=IF(MONTH(DATE($A$1,$C$1,ROW(A1)))=$C$1,DATE($A$1,$C$1,ROW(A1)),"")
B3セルの表示形式はユーザー定義から aaaa としています。
B3セルに
=IF(A3="","",A3)
という数式を入れ、B3・C3セルを範囲指定しC3セルのフィルハンドルで
31日まで下へコピーします。
そして条件付書式の件ですが
まず、日曜日と祝日が重なる部分を条件1とします。
A3~B33セルを範囲指定し、
数式がの数式欄に
=AND(WEEKDAY(A3)=1,COUNTIF($V$2:$W$24,A3))
として「書式」パターンから「色なし」を選択します。
続いて条件2の数式欄に
=OR(WEEKDAY(A3)=7,COUNTIF($V$2:$W$24,A3))
として書式 → パターン → 「赤」を選択すると
↓の画像のような感じになります。
これで日曜日と祝日が重なっている日は色なしになり、
土曜日と祝日は赤になると思います。
以上、長々と書きましたが
参考になれば幸いです。
的外れなら読み流してくださいね。m(__)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) Excelの相談 4 2023/03/03 09:51
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) 出勤簿の土、日、休日に色付けできない 2 2022/08/04 20:10
- Excel(エクセル) エクセルの当番表を作っていますが教えてください 4 2023/01/06 12:12
- その他(Microsoft Office) エクセルについて質問です。 イフとイフカウントの組み合わせで =IF(COUNTIF(A3:A9,” 2 2023/02/24 22:13
- Visual Basic(VBA) このVBAでExcelアプリケーションを作成は必要ですか? 3 2023/07/19 21:13
- Excel(エクセル) 【EXCEL】=で同じ文字列が表示されない 4 2023/06/04 22:38
- Excel(エクセル) エクセルのvlookupについて質問です 3 2023/01/05 15:15
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
Excel 日付を比較したら、同じ...
-
Microsoft365の「お支払いを更...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelで空白以外の値がある列の...
-
エクセルのシフト表を簡単にGoo...
-
理由を教えてください。
-
VBA
-
web上にあるエクセルをショート...
-
バソコンが二台とも壊れ後換装...
-
【マクロ】文字を1文字づつ、...
-
Excelのセルの重複チェックが出...
-
マイクロソフト 一時使用コード...
-
office365って抵抗感ないですか?
-
Outlook 電源OFFの受診の仕方
-
エクセルで例えば、A1に㈱ベ...
-
自分の専門分野の仕事。初見で...
-
excelの画面のグリッド線の消滅。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報