2021年度以降の4直3交代のシフト表を作成したいです。
2020年のベースはこの写真のようになっています。
これは今年の初めにExcelの数式をなどを全く理解していない時に手打ちで地道に作ったものです。
シフトのサイクルは
1111休2222休3333休休です。
2021年の1月1日のシフトは
A班 休(1の次の日です)
B班 1(1日目)
C班 3(3日目)
D班 2(4日目)
としてスタートしたいです。
このシフト表をベースにして「I1」を2021.2022などと変えると自動的に1月なら3行目の曜日、5~8行目がその年の曜日やシフトに合わせて自動変換、それを12月まで変換出来れば助かります。
ちなみにシフト表の日付は固定しておきたいです。(1月なら4行目、2月なら11行目)
このようにできる数式があれば教えていただけるとありがたいです。
説明不足なところがあればおっしゃってください。よろしくお願いしますm(_ _)m
No.17ベストアンサー
- 回答日時:
No.16です。
前回の投稿にタイプミスがありました。
tatsumaru77さんのマクロについての説明部分で
「シフト表」の「I1」に対象西暦年を入力した状態使います。(誤)
「シフト表」の「I1」に対象西暦年を入力した状態で使います。(正)
なお、1月より後のシフト表作成の説明部分の
作成した1月分の「B5からAF8」までの全てのセルをコピーし、・・・・・・、
は
私の回答またはenunokokoroさんの回答の数式でシフト表を作成した場合、作成した1月分の「B5からAF8」までの全てのセルをコピーし、
という意味です。
また、1月の曜日の条件付書式設定を他の月にコピーする説明の
あとは、この条件付書式設定をコピーし、各月の曜日表示のセルに・・・・・・
は
あとは、この1月の曜日表示セルを選択した状態で(条件付書式設定を)コピーし、各月の曜日表示のセルに・・・・・・
という意味です。
No.16
- 回答日時:
>「B5」にも数式を入れてオートフィルタで「AF8」までやると1月分は出来ました。
>2月分からはどうすればいいですか?
作成した1月分の「B5からAF8」までの全てのセルをコピーし、「B12」のセルを選択して貼り付けしてください。
以下「B19」「B26」「B33」・・・と順次、月毎に貼り付けします。これで、各月のシフト表が作成できるはずです。
>今年は29日があったので「AD」列に29日の枠を作っていました。「I1」を2021年に変えてみると「AD11」が1日になってしまいます。
>これはどうしょうもないでしょうか?
ということなので、この課題を解決します。
AD11には「日付」を表示できるシリアル値が数式で入っているものとします。
まず、AD10からAD15までを選択した状態で、「条件付書式」を設定します。
条件付書式>新しいルール>数式を使用して、書式設定するセルを決定>次の数式を満たす場合に値を書式設定の数式欄に
=MONTH($AD$11)<>$A$10
という数式を記述します。
次に書式ボタンを押し、「フォント」タブを選択し、添付画像①のように、文字の「色」を「白色」にします。
さらに、「罫線」タブを選択し、添付画像②のように左罫線のみ罫線表示にし、あとは「罫線なし」にします。
設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
これで、条件付書式により2/29がある年とない年の両方に対応できる書式になったはずです。
添付画像③は2020年のシフト表で条件付書式設定をした後、I1に「2021」と入力したものです。2/29が表示されていないことが確認できます。
ただし、これはあくまでも文字を白色にし外見上見えなくしているだけなので、「値コピーして他のシートに貼る」などといった操作をした場合は、ご質問者の現在の状況と同様に「AD11」が1日になってしまいます。
さて、ここまで来たら、土日祝日の曜日に色付けするところまで説明します。
まず、祝日の一覧表をtatsumaru77さんがNo.15の回答で示されたように「祝日」という名前の別シートに作成します。1月の曜日を表示しているセルの全てを選択し、上記の「2/29」の場合と同様に、条件付書式を設定します。
次の数式を満たす場合に値を書式設定の数式欄に
=WEEKDAY(B3)=1
という数式を記述し、書式の「フォント」タブから、文字色を「赤色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
次に、同じ手順で条件付書式設定の数式欄に
=WEEKDAY(B3)=7
という数式を記述し、書式の「フォント」タブから、文字色を「青色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
さらに、同じ手順で条件付書式設定の数式欄に
=COUNTIF(祝日!$A:$A,B3)>0
という数式を記述し、書式の「フォント」タブから、文字色を「赤色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
これで、曜日に色をつける条件付書式設定の1月分が完了しました。
あとは、この条件付書式設定をコピーし、各月の曜日表示のセルに「貼り付けのオプション」から「書式設定」を選択して、貼り付けすれば完了です。
また、tatsumaru77さんのマクロは、色付けのみを行うものではなく、シフト表を作成するものです。予め「シフト表」の各セルに書式設定しておくと使い易いと思います。「シフト表」の「I1」に対象西暦年を入力した状態使います。このマクロを実行できるスキルがあれば、上記のような煩雑な設定は不要になります。
No.15
- 回答日時:
マクロが実行できる環境になったということなので、マクロを提供します。
実行時の注意点です。
1.シート名:シフト表 を作成しておいてください。そこにカレンダーが作成されます。
もし、既にシフト表があるとそれは、上書きされますので、バックアップをとるか、新規で「シフト表」のシートを作成しておいてください。
2.シート名:祝日 来年以降の祝日を登録しておいてください。添付画像を参照ください。2行目以降に、祝日を登録してください。1行目は見出しです。
3.シフト表の罫線、及びセル結合はあなたが行ってください。
マクロは罫線、及びセル結合は行いません。
4.I1に指定可能な年は2020~2099までです。
(今年もできるのでマクロで作成しものと、あなたが手入力したものを比較してください)
以下のマクロを標準モジュールに登録してください。
---------------------------------------------------------
https://ideone.com/9LD0lF
ここに投稿するとエラーになるので上記URLに投稿しました。
上記URLのマクロをコピペしてください。
No.14
- 回答日時:
No.10です。
とりあえず、ご質問者の疑問にお答えし、2/29問題は後ほど回答させいてただきます。
>気になったのが「B1」に日付が入っていることです。
B1に日付が入っている添付画像①は、あくまでも数式を検証するためのテスト画像であって、
「B1に日付を入れて表をつくりましょう」という意味ではありません。
ご質問者が数式を応用する場合の参考としていただくためのものです。
このため、現在の作表には必要ありません。
よろしく御願いします。
No.13
- 回答日時:
貴方が示した添附圖が示す形式のカレンダーを私の貧弱な知識で完成するには困難かも(さう思ふ最大の理由は、各月に依ってカレンダーの横幅がマチマチ)。
代わりに此処の添附圖に示した物なら、と云ふ事でアップロードして措きます。取り敢えず祝日も含めて、2023年迄對應させた物です。
(左端圖に措いて、3行目を下方にズズーッと1463行目迄オートフィル)
セル A2、B2 の年、月の數値を變更すればカレンダーの(3行目の)起点が該當年月の朔日から始まります。貴方が補足で應へて呉れた
》 1/1は12/31の続きになります。
》 2021年1月1日からのシフトです
の通りに成って居るのが判讀出來ますか?
土曜、日曜の行は夫々青、赤色の塗りつぶし、祝日は橙色ですが同色の日曜は翌日以降に振替休日(此れも祝日扱い)が在る事を示しています。
御望みのモノじゃ無いので、此れ以上の解説は遠慮させて下さい。m(_._)m
失禮しました。
No.11
- 回答日時:
No.1の回答者です。
皆さんの色々な案が出てきていて、私も参考になります。
私の案については補足がないので、あくまで参考としてくださいね。
とりあえず、No.9の回答者さんが使用する関数以外では同じもので
具体的に説明しているので、この回答は補足程度の追加回答です。
日付部分について。
No.9の回答者と基本的に同じです。
=DATE($I$1,$A3,COLUMN()-1)
として、日付を各月分用意しています。
曜日または日付どちらかで使用すれば済みますね。
曜日のほうで設定したら、表示形式で対応してください。
日付は
=B3
とすれば、各月を用意するときにオートフィルで対応できます。
シフト表について。
B5セルには、数式としては長いですが
=CHOOSE(HEX2DEC(RIGHT(DEC2HEX(INDIRECT("R[-"&MOD(ROW()+2,7)+1&"]C",FALSE)-(MOD(ROW()+2,7)*4+1)),1))+1,1,1,1,1,"ヤ",2,2,2,2,"ヤ",3,3,3,3,"ヤ","ヤ")
を入れています。
No.9の回答にある方法のほうが、コンパクトで良いですね。
日付の色などについて
条件付き書式で対応するだけなので難しくはないと思います。
http://www4.synapse.ne.jp/yone/excel2010/excel20 …
添付画像は、上記条件で作成した1月分だけ3年分並べたものです。
各月の分も正しく表示されますし、年指定を変えれば対応できます。
No.10
- 回答日時:
No.9です。
前回の回答で漏らしたことを追記します。前回回答の一覧用のシートでは、曜日の行および日付の行にはそれぞれ書式設定が必要です。
曜日の行にはシリアル値(日付)が入るものとして、「ユーザー設定」で「aaa」を設定することで曜日を表示させます。
日付の行にもシリアル値(日付)が入るものとして、「ユーザー設定」で「d"日"」を設定することで「○日」と表示させます。
No.9
- 回答日時:
このご質問は、No.1さん、No.4さんのご指摘のとおり、16個のシフトパターンが繰返すというところに注目すれば、解決できると思います。
No.1さんは16進数とCHOOSE関数を組合せ、No.4さんは基本テーブルの作成で対応しておられますが、本回答ではMID関数、MOD関数、FIND関数を組合せてみました。
日付セルに対応したA、B、C、D各班のシフト状況を表示するには添付画像①を例にとれば、B2に
=MID("ヤ1111ヤ2222ヤ3333ヤ",MOD(B$1+FIND($A2,"DCBA")*4,16)+1,1)
という数式を記述すればよいということになります。
要は日付セル(シリアル値であることが前提)を参照して1~16の値を導き、この値と班の区別によって、その日のシフトを決定するというロジックを作ればよいわけです。
そこで、シリアル値を16で割った余りをMOD関数で求めて1を加えることで1~16までを繰返すパターンを発生させます。
この値がMID関数の「文字列の中の位置」を指定してくれるように調整します。
A、B、C、D各班のシフト状況を示す、MID関数の「文字列の中の位置」と上記の1~16の数字には「ずれ」がありますのでこの「ずれ」を修正する必要があります。
「FIND($A2,"DCBA")*4」を使ってこのずれを調整しています。
このため添付画像でいうA列の「班名」とFIND関数に記述する"DCBA"は半角・全角、大文字・小文字等の相違があったり、A列に「A班」などとアルファベット以外の文字をつけたりしないようにして、必要な場合は「書式設定」で対応してください。
添付画像①として一日分のシフト状況を表示するサンプルを作成したものを投稿しておきます。
日付欄に任意の年月日を入力してみて、ご質問者が既に作成した一覧表の内容と一致するか確認してみてください。
一覧表のフォーマットは色々考えられると思いますが、年度を指定すると日付と曜日が自動的に表示されるカレンターを作成し、そのカレーンダーの日付のシリアル値を参照する上記数式を記述した表を作成すればよいことになります。
2021年分の一覧表をご質問者の作成されたフォーマットに近いもので作成してみました(添付画像②)。
この場合のB5に記述する数式は、
=MID("ヤ1111ヤ2222ヤ3333ヤ",MOD(OFFSET(B5,(2-MOD(ROW()-2,7)),0)+FIND($A5,"DCBA")*4,16)+1,1)
とすればオートフィル等でコピーしても修正不要になります。
セルの色づけについては他の回答者さんにお任せしたいと思います。
No.8
- 回答日時:
マクロを登録して実行するためには
あなたのPCのexcelをマクロが実行できるようにしておく必要があります。
こちらを一通り、読んで、実際に確認してみてください。
https://www.tipsfound.com/vba/01004
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
ロボットの住む世界で流行ってる罰ゲームとは?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
先日、年間の4直3交代のシフト表の作成をお願いしたのですが今回は年間のシフトを元に週間の予定表に自動
Excel(エクセル)
-
Excelで4勤2休のカレンダーを作りたい
Excel(エクセル)
-
4勤2休のシフト作成
その他(コンピューター・テクノロジー)
-
-
4
「3交替5勤2休」ってどういうこと?
アルバイト・パート
-
5
4直3交替の勤務時間について
その他(暮らし・生活・行事)
-
6
年間仕事用シフトカレンダーに、日勤 夜勤 休み に色分けをした。
Excel(エクセル)
-
7
3交替について
転職
-
8
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
9
工場勤務3交代。年間休暇。悩んでます
就職
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
今まで文字化けなく開けていたc...
-
スプレッドシートで指定された...
-
ExcelでASCを使って全角を半角...
-
マクロの処理が遅くなった
-
Excel関数について教えてくださ...
-
作成した数式を値として表示し...
-
エクセルの質問です。 F列からL...
-
エクセルでファイルの最終更新...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルのセル内に分数などの...
-
Excelの警告について
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
シートの情報を別のシートへま...
-
Excelマクロで空白セルを詰めて...
-
【マクロ】フォルダ内にあるPDF...
-
index関数の説明をお願いします。
-
重複しない値を取り出したい
-
エクセルの数式バーのフォント...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報
mike_gさん
1/1は12/31の続きになります。
2021年1月1日からのシフトです(1番左)
A→ヤ2222ヤ3333ヤヤ1111…
B→1111ヤ2222ヤ3333ヤヤ…
C→33ヤヤ1111ヤ2222ヤ33…
D→2ヤ3333ヤヤ1111休22…
※2020年12月31日は1番右です。
tatsumaru77さん
2.出来れば祝日も色を付けたいです。自分で準備が出来るのか自身がありませんがもし複雑そうならカレンダーを見て自分で色付けしたいです。
3.2020年はAグループに所属しましたが2021年からは全グループの人が均等に見れるようなシフト表にするつもりです。
4.シフト表は「ヤ」の表記でお願いします。
色々と手間をおかけしてすいません。よろしくお願いします。
tatsumaru77さん
マクロ...。すいません初心者なのでよく分からないのですがファイル名の後に付いてるやつでしょうか??
ファイル名も本日は休日で分からないため明日に会社に行き調べてみます。
祝日の件に関しては題名は祝日で構いません。
よろしくお願いします(^.^)
皆様、多数のご回答頂きありがとうございますm(_ _)m
tatsumaru77さん
遅くなり申し訳ありません。マクロ実行できるように設定を行いました。先にシフト表を設定してから色付けをやっていきたいと考えています。
enunokokoroさん、goomaniaさん
教えて頂いたわかる範囲で数式を入れてみました。
気になったのが「B1」に日付が入っていることです。
月の変換、曜日、日付の設定はお陰様で出来ました。
「B5」にも数式を入れてオートフィルタで「AF8」までやると1月分は出来ました。
2月分からはどうすればいいですか?
あと気になったのが2月29日ですね。
今年は29日があったので「AD」列に29日の枠を作っていました。「I1」を2021年に変えてみると「AD11」が1日になってしまいます。
これはどうしょうもないでしょうか?
何度もすいません。