[[20211117103604]] 『シフト表で複合の関数・・・』(たるやん) ページの最後に飛ぶ

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

 

『シフト表で複合の関数・・・』(たるやん)

現在エクセルでシフト表を作成しています。
希望していることは、シフト表の自動化で、
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 >


[[20211112172114]]
 まずはこちらで回答いただいているものにコメントをしてはいかがでしょう?
(*) 2021/11/17(水) 11:33

2つをつなげれば完成になるんでしょうね

(ひまじん) 2021/11/17(水) 12:42


更新漏れでした。ご返信ありがとうございます。
(たるやん) 2021/11/17(水) 13:12

 =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


早急なご返信ありがとうございます。
感謝申し上げます。
この関数で差し込んでみましたが、夜1の翌日「明け」翌々日「休み」にならないです・・・
ランダムのが優先されてダメなんでしょうか・・・
「明け」はでているのですが、どうもこれもランダムになっているようです。
(たるやん) 2021/11/17(水) 14:17

関数を見ればわかるはずですが、「夜1」というシフトは想定されていません。
あるのは"夜A","夜B","夜C"です。
(きまぐれおじさん) 2021/11/17(水) 14:40

失礼しました。夜A夜B夜Cで試しましたがやはり同じでした・・・
(たるやん) 2021/11/17(水) 14:44

こちらはABCを半角大文字にしましたが、もし違っているなら合わせてください。
乱数の優先順位は最も低くしてあるので、そうならないのは文字列が整合していないことが原因です。
(きまぐれおじさん) 2021/11/17(水) 14:48

もしかしたら、HLOOKUP関数内の「C7」を「C8」に修正すると直るかもしれません。
(きまぐれおじさん) 2021/11/17(水) 14:54

何度もご返信ありがとうございます。やはりだめでした。。。
一応サンプルファイル載せておきます。
https://xfs.jp/WCIxDV
(たるやん) 2021/11/17(水) 15:22

すみません、サンプルファイルは受け取れません。
関数を入力しているセルのアドレスと、その隣のセルアドレスは何ですか?
(きまぐれおじさん) 2021/11/17(水) 16:26

ご返信ありがとうございます。
別件の仕事で返事が遅れてすいません。
D8のセル
=IF(D$6="","",IF(COUNTIF($AJ8:$AN8,D$6)>0,"休",IFERROR(HLOOKUP(D8,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE),INDEX($O$1:$X$1,1,RANDBETWEEN(1,10)))))
E8のセル
=IF(E$6="","",IF(COUNTIF($AJ8:$AN8,E$6)>0,"休",IFERROR(HLOOKUP(E8,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE),INDEX($O$1:$X$1,1,RANDBETWEEN(1,10)))))

表記が0になってしまいます。

https://xfs.jp/WNiYXi
(たるやん) 2021/11/18(木) 10:29


 HLOOKUP(D8,{"夜A","夜B","夜C","明け";"明け","明け","明け","休"},2,FALSE)
         ↑ここはD8ではなくC8でお願いします。(左隣のセル)

(きまぐれおじさん) 2021/11/18(木) 10:51


早々のご返事ありがとうございます。
C8に変更し、右に月末まで値式をコピーすると、D8からE8・・・・とスライドはしていくのですが、
実際の勤怠表は1日から5日まで「0」の表記になり、6日以降はシフトがでるのですが夜勤明け休日に連動しません。
(たるやん) 2021/11/18(木) 10:58

"夜A","夜B","夜C","明け" ←これが全角/半角含め一致していないと連動してくれません。
6日以降にシフトが出るということは1〜5日の参照先セルが正しくありません。

一つ一つ確認ですが、
「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


No.4
 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.