[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シフト表で複合の関数・・・』(たるやん)
現在エクセルでシフト表を作成しています。
希望していることは、シフト表の自動化で、
No.1 10種の勤務(早出・日勤・夜勤・休み等)が自動で割り振られる。
No.2 希望休を優先して休みにする
No.3 「No.1」で夜勤が選ばれると翌日が「明け」翌々日が「休(明休)」になる。
これらを自動でした後に、微調整を手で行うようにしたいのですが、「No.2」までは何とか出来たのですが、「No.3」を登録する場合どこに入れればいいか全く分かりません。
もしわかるようであればご助力頂ければと思います。
【勤怠表】
C D E F G H I・・・AH
6 1 2 3 4 5 6・・・
7 曜 日 月 火 水 木 金 土・・・
8 名 前
9 名 前
10
【関数用シート(勤務体系表)】
O P Q ・・・X
1 日勤 夜勤 休み 等
【関数用シート(希望休表)】
AJ〜AN
8 各マス(最大5日)に希望日を入れる
※希望休を入れると勤怠表で希望日は固定されて優先され他が自動です
「No,1とNo.2」まで出来ている関数
=IF(D$6="","",IF(COUNTIF($AJ8:$AN8,D$6)>0,"休",SWITCH(RANDBETWEEN(1,10),1,$O$1,2,$P$1,3,$Q$1,4,$R$1,5,$S$1,6,$T$1,7,$U$1,8,$V$1,9,$W$1,10,$X$1)))
「No.3」だけでみると
=IF(前のセル="夜1","明け",IF(前のセル="明け","休み",""))
でいけたのですが、組み合わせが分かりません。。。
もし上記が解決できるようであればもう一点、
夜勤ではなく、夜A 夜B 夜Cというシフトが選択された場合は、
翌日が明け、翌々日が休が自動選択できれば完璧です。
※ちなみにシフト名は名前が変更は可能ですので、文字選択の関数なども利用可能です。
< 使用 Excel:Office365、使用 OS:unknown >
まずはこちらで回答いただいているものにコメントをしてはいかがでしょう? (*) 2021/11/17(水) 11:33
(ひまじん) 2021/11/17(水) 12:42
=IF(D$6="","",IF(COUNTIF($AJ8:$AN8,D$6)>0,"休",IFERROR(HLOOKUP(C7,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE),INDEX($O$1:$X$1,1,RANDBETWEEN(1,10)))))
一例です。SWITCHのところは勝手ながらINDEXに変更しています。
(きまぐれおじさん) 2021/11/17(水) 13:40
表記が0になってしまいます。
https://xfs.jp/WNiYXi
(たるやん) 2021/11/18(木) 10:29
HLOOKUP(D8,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE) ↑ここはD8ではなくC8でお願いします。(左隣のセル)
(きまぐれおじさん) 2021/11/18(木) 10:51
一つ一つ確認ですが、
「IF(D$6="",""」とあるように、6行目の値が空白なら空白になります。
「IF(COUNTIF($AJ8:$AN8,D$6)>0,"休"」とあるように、AJ〜AN8に6行目の値と等しいものがあれば「休」になります。
「HLOOKUP(C8,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE)」
とあるように、「"夜A","夜B","夜C","明け"」のいずれかの値であれば「"明け","明け","明け","休"」の対応する値になります。
「INDEX($O$1:$X$1,1,RANDBETWEEN(1,10))」とあるように、O1〜X1のうちいずれかランダムな値になります。
式全体でうまく動作しない場合、これらの要素を一つ一つ動作検証してみてください。
(きまぐれおじさん) 2021/11/18(木) 11:16
(たるやん) 2021/11/18(木) 11:22
根本的には、自動でシフトが割り振られ、微調整を手動でするというシフト表
No.1 10種の勤務(早出・日勤・夜勤・休み等)が自動で割り振られる。
No.2 希望休を優先して休みにする
No.3 夜勤は「夜A」「夜B」「夜C」とあり、「No.1」で自動で割り振られると、こちらも自動で
夜勤の翌日は「明け」その次の日は「休み」が自動で選択される
ここまでは、きまぐれおじさんの多大なる協力を得て下記式でなんとかいけました。
=IF(E$6="","",IF(COUNTIF($AK8:$AO8,E$6)>0,"休",IFERROR(HLOOKUP(D8,{"夜1","夜2","夜3","明け";"明け","明け","明け","休"},2,FALSE),INDEX($P$1:$Y$1,1,RANDBETWEEN(1,10)))))
ここに追加で下記に対応するような関数を入れる事は可能でしょうか?
No.4 自動割り振り時、休みの間が3日以内(例・・・2日が休みなら次の休みは6日までに計算される)
No.5 縦(社員20名だった場合)O1〜X1までに記載されている10種の勤務体系が1つづつ選ばれる
例) 5日 6日 7日
Aさん 早出 夜勤 明け 休み
Bさん 遅出 早出 夜勤
Cさん 夜勤 明け 休み
Dさん 日勤 休み 遅出
Eさん 休み 日勤 休み
Fさん 休み 休み 日勤
↑休み以外被らない
考え方としては、休み以外10種(内一つは休みなので実際は9種)は縦にランダムでひとつづつ選ばれて他は休みで自動で割り振られる
※この際、No.3の「夜勤」「明け」「休み」は優先
こんな感じにしたいのですが可能なものでしょうか?
≪参考データ≫
【勤怠表】
C D E F G H I・・・AH
6 1 2 3 4 5 6・・・
7 曜 日 月 火 水 木 金 土・・・
8 名 前
9 名 前
10
【関数用シート(勤務体系表)】
O P Q ・・・X
1 日勤 夜勤 休み 等
【関数用シート(希望休表)】
AJ〜AN
8 各マス(最大5日)に希望日を入れる
※希望休を入れると勤怠表で希望日は固定されて優先され他が自動です
(たるやん) 2021/11/25(木) 11:52
IF(COUNTIF($AK8:$AO8,E$6)>0,"休" ↓ IF(OR(COUNTIF($AK8:$AO8,E$6)>0,AND(COLUMN(E8)>8,COUNTIF($B8:$D8,"休")=0)),"休"
No.5(Office365のみ)
RANDBETWEEN(1,10) ↓ SORTBY(SEQUENCE(10),RANDARRAY(10))
でできそうな気がしますが、No.5はこちらOffice365環境でないので検証できません。
他の部分も単独セル参照からセル範囲参照に変更が必要かもしれません。
※旧環境では乱数からRANK関数で順位付けして重複なし乱数にするような方法になり補助列または補助行が必要になります。
(きまぐれおじさん) 2021/11/25(木) 13:57
(たるやん) 2021/11/25(木) 14:44
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.