[[20201231001800]]　『シフト表、出退勤時刻の自動入力について』（takao293）　ページの最後に飛ぶ

『シフト表、出退勤時刻の自動入力について』（takao293）

お世話になります。

当社は数多くシフトがあり出退勤時刻もバラバラ、日付を跨ぐ夜勤もあり、月のシフトと出退勤時刻は個人で一覧表を見て手帳などに書いて管理しています。Outlookで個別に出退勤時刻を送信する事が出来ないが試行錯誤しているところです。

シフト表自体は管理職が社内の管理システムから紙ベースで印刷する為、データなどを弄る事が難しいです。

入力した番号にその番号が対象となる時刻を自動で入力し、Outlookで送信できる様にしたいです。宜しくお願い致します。

名前　日付　1 2 3 4 5 6 7 8 9 10

           曜日   金 土 日 月 火 水木
           番号
　　　出勤　
　　　退勤

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 番号別出退勤表があり、A列に番号、B列に出勤時刻、C列に退勤時刻があったとします
 番号の入力はC3とした場合
 出勤時刻=VLOOKUP(C$3,番号別出退勤表!$A:$C,2,0)
 退勤時刻=VLOOKUP(C$3,番号別出退勤表!$A:$C,3,0)

 メールの送信方法は
https://dekiru.net/article/15009/
 こちらに詳しく書いてあります
（稲葉） 2020/12/31(木) 06:07

ありがとうございます。何とか出来ましたが日付を跨ぐ夜勤などの場合退勤時刻を翌日の列に反映させる方法がわからず苦労してます。IF関数の組み合わせなど調べつつやっていますが出退勤時刻が休日や祝日が出来ましたが平日と変わる為そちらもどの様にすれば良いかわからず頭を抱えている次第です。もし何かアドバイス頂けたらと思います。
（takao293） 2021/01/02(土) 22:46

 ＞夜勤などの場合退勤時刻を翌日の列に反映させる
 出社日と同じ列に、「翌5:00」って表示させたほうが早いんじゃないですか？
 22:00-5:00が2日連続した場合、翌日の列に反映させちゃうと見にくくないですか？

（稲葉） 2021/01/03(日) 12:36

すみません、最初の説明が不十分でした。最終的にOutlookのスケジュールに反映させたいのでワークシートのPDFを送るだけならそのやり方でも良いのですがスケジュールへ反映となるとおかしくなるのではと思い(まだそこまで辿り着けていませんが)悩んでいるところです。アドバイスありがとうございます。
（takao293） 2021/01/05(火) 14:40

Outlookに予定を登録したい、という事であれば、
予定表の形式（項目名等）に合わせないと予定として認識されません。

Outlookから「予定表」データを「エクスポート」してみてください（１日分で大丈夫です）。
どういった項目になっているかがわかります。
※あらかじめ適当に予定を入れておくと、どのように入力されているかも判ります。

作業としては、
１．紙で出力されたデータを見ながら「エクスポート」データの項目に直接入力して、担当者ごとに予定表を登録する
２．紙で出力されたデータを見ながら現在作成しているExcelの表に入力、何らかの方法で「エクスポート」データ項目に合わせたシートを作成しデータを転記、担当者別ごとに予定表を登録する
３．メールで送らずに各担当者名でCSVファイルを作成し、担当者が自分で「予定表」に「インポート」する。
というのが考えられるかと思います。
※シフト表がPDFで出力できるならば（有料のAcrobat等必要ですが）Excelに変換も可能かなと思います。

Outlookへの登録用コードはこちらが参考になるのではないかなと思います。
https://extan.jp/?p=2275

ちなみに、Outlookの使用環境によっては動作しないこともあり得ます。
（you） 2021/01/05(火) 21:06

ありがとうございます！試してまたご報告させて頂きます。
（takao293） 2021/01/06(水) 09:05

ひとまずOutlook送信は置いておいて出退勤時刻の自動入力を試しています。
H13列を土日休日の意味で休日を入力、出退勤時刻表の範囲を指定した出退勤表から
出勤時刻
=IF(H13="休日",VLOOKUP(H$10,[出退勤表]出退勤時刻表!$F:$G,2,0),IF(H13="",VLOOKUP(H$10,[出退勤表]出退勤時刻表!$B:$C,2,0)))
これは上手くいきました

次に退勤時刻ですが、夜勤をB54:B82の範囲に番号を指定
夜勤の場合翌日に退勤時刻が入る様に指定したのですが
=IF(AND(H10="B54:B82",I13="休日"),VLOOKUP(I$12,[出退勤表]出退勤時刻表!$F:$H,3,0),IF(AND(H10="B54:B82",I13=""),VLOOKUP(I$12,[出退勤表]出退勤時刻表!$B:$D,3,0),IF(I13="休日",VLOOKUP(I$10,[出退勤表]出退勤時刻表!$F:$H,3,0),IF(I13="",VLOOKUP(I$10,[出退勤表]出退勤時刻表!$B:$D,3,0)))))
こちらは上手くいかず、なかなか良い方法が見つかりません。何かアドバイスあればお願いします。
（takao293） 2021/01/12(火) 15:20

 「こうしたときに、こうしたい」という結果図をください。

 ＞=IF(AND(H10="B54:B82",
 こういう指定はできません。
 これだと、　H10が「B54:B82」という文字の場合、Trueという意味です。

 ＞[出退勤表]出退勤時刻表!$F:$H
 こちらもどのような作りになっているか教えて下さい。

 差し支えなければ、
[[20110209184943]]　『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ）　
 こちらを使って、シートレイアウトをこちらに載せてください。

（稲葉） 2021/01/12(火) 16:04

シフト表（シート１）

     |[A]|[B]         |[C]   |[D]  |[E] |[F]  |[G]  |[H]  |[I] |[J]|[K] |[L]  |[M] |[N]  |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA]|[AB]|[AC]|[AD]|[AE]|[AF]|[AG]|[AH]
 [1] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [2] |   |勤務指定表  |      |     |    |     | 2021|     |年  |  1|月  |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [3] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [4] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [5] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [6] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [7] |   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [8] |   |氏名        |日    |    1|   2|    3|    4|    5|   6|  7|   8|    9|  10|   11|  12|  13|  14|  15|  16|  17|  18|  19|  20|  21|  22|  23|  24|  25|  26|  27|  28|  29|  30|  31
 [9] |   |            |曜    |金   |土  |日   |月   |火   |水  |木 |金  |土   |日  |月   |火  |水  |木  |金  |土  |日  |月  |火  |水  |木  |金  |土  |日  |月  |火  |水  |木  |金  |土  |日  
 [10]|   |　○○　□□|指　定|  22 |-   |   12|   14|   33|    |-  |    |   44|    |   44|    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [11]|   |            |出勤  |12:57|    |8:06 |9:06 |15:39|#N/A|   |#N/A|17:17|#N/A|17:17|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A
 [12]|   |            |退勤  |6:53 |    |16:41|19:13|10:37|#N/A|   |#N/A|12:15|#N/A|12:15|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A|#N/A
 [13]|   |            |備考  |休日 |休日|休日 |     |     |    |   |    |休日 |休日|休日 |    |    |    |    |休日|休日|    |    |    |    |    |休日|休日|    |    |    |    |    |休日|休日
 [14]|   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    
 [15]|   |            |      |     |    |     |     |     |    |   |    |     |    |     |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |

出退勤時刻表（シート２）

     |[B] |[C]  |[D]  |[E]|[F] |[G]  |[H]  
 [3] |平日|     |     |   |休日|     |     
 [4] |行路|出勤 |退勤 |   |行路|出勤 |退勤 
 [5] |   1|4:18 |12:06|   |   1|4:18 |11:21
 [6] |   2|4:58 |13:21|   |   2|5:04 |13:35
 [7] |   3|5:12 |13:15|   |   3|5:32 |14:05
 [8] |   4|5:18 |13:35|   |   4|5:52 |14:15
 [9] |   5|5:43 |14:41|   |   5|6:01 |14:21
 [10]|   6|6:02 |14:55|   |   6|6:29 |14:25
 [11]|   7|6:26 |15:01|   |   7|7:00 |14:41
 [12]|   8|7:36 |16:35|   |   8|7:26 |15:41
 [13]|   9|7:37 |17:21|   |   9|7:40 |16:01
 [14]|  10|7:51 |17:34|   |  10|     |     
 [15]|  11|7:58 |17:51|   |  11|7:46 |16:21
 [16]|  12|8:11 |17:43|   |  12|8:06 |16:41
 [17]|  13|8:35 |18:46|   |  13|8:20 |17:01
 [18]|  14|9:06 |19:13|   |  14|8:34 |17:35
 [19]|  15|10:17|20:35|   |  15|9:06 |18:01
 [20]|  16|10:48|21:21|   |  16|9:17 |18:25
 [21]|  17|11:17|21:23|   |  17|9:57 |18:41
 [22]|  18|12:06|21:43|   |  18|11:46|20:01
 [23]|  19|12:06|21:54|   |  19|11:57|20:35
 [24]|  20|12:17|8:41 |   |  20|12:26|6:05 
 [25]|  21|12:26|8:26 |   |  21|12:37|6:43 
 [26]|  22|12:37|9:15 |   |  22|12:57|6:53 
 [27]|  23|12:46|9:15 |   |  23|13:17|6:39 
 [28]|  24|12:57|9:10 |   |  24|13:26|7:20 
 [29]|  25|13:26|9:35 |   |  25|13:26|8:01 
 [30]|  26|13:37|9:47 |   |  26|13:37|8:21 
 [31]|  27|14:46|9:23 |   |  27|13:46|8:32 
 [32]|  28|14:46|10:09|   |  28|13:46|8:45 
 [33]|  29|14:57|10:35|   |  29|13:57|8:58 
 [34]|  30|15:06|10:46|   |  30|14:37|9:21 
 [35]|  31|15:26|10:50|   |  31|14:46|9:18 
 [36]|  32|15:28|10:35|   |  32|15:06|9:25 
 [37]|  33|15:39|10:37|   |  33|15:29|11:05
 [38]|  34|15:46|11:06|   |  34|15:49|10:45
 [39]|  35|15:49|10:25|   |  35|15:57|11:15
 [40]|  36|15:48|10:39|   |  36|16:06|11:05
 [41]|  37|15:57|11:35|   |  37|16:08|11:45
 [42]|  38|16:08|11:25|   |  38|16:13|10:35
 [43]|  39|16:08|11:41|   |  39|16:17|11:25
 [44]|  40|16:19|11:55|   |  40|16:19|11:25
 [45]|  41|16:36|12:01|   |  41|16:26|12:01
 [46]|  42|16:46|11:45|   |  42|16:37|11:55
 [47]|  43|16:56|12:25|   |  43|17:06|12:21
 [48]|  44|17:10|12:35|   |  44|17:17|12:15
 [49]|  45|17:06|12:45|   |  45|17:26|12:25

作業中のシートからコピーしてしまったのでわかりにくいかもしれませんが、シフト表の「指定」の項が勤務の番号の様なもので、1/1の22番は夜勤です。1/2は夜勤明けの日で「‐」で表記されています。
シート2の出退勤時刻表に平日・休日の出退勤時刻がそれぞれ書いてあります。

今のところ出来ていることは出退勤時刻の平日・休日それぞれの出退勤時刻の自動入力です。

やりたいことは
・夜勤の場合、例えば休日‐平日の二日にわたる夜勤の場合休日の出勤時刻と翌日の退勤時刻をそれぞれ休日or平日の時刻で参照し自動で入力
・Outlookで予定表の送信、出退勤時刻をOutlookのスケジュールに反映
です。なかなか難しいと思いますが、お知恵をお貸しいただけたら嬉しいです。

（takao293） 2021/01/13(水) 20:41

 ＞夜勤の場合、例えば休日‐平日の二日にわたる夜勤の場合休日の出勤時刻と翌日の退勤時刻をそれぞれ休日or平日の時刻で参照し自動で入力
 これがよくわからないです
 日曜に20番が入力されていた場合、どうなればいいか教えて下さい

（稲葉） 2021/01/14(木) 06:50

すみません、説明がわかりにくいですね

例えば20番の時に日曜-月曜の夜勤になった場合、日曜日(休日の出勤時刻)で退勤は月曜日(平日の退勤時刻)を反映させたいのです。　
表だと20番の休日の出勤時刻12:26から翌日の平日退勤時刻6:05を反映させたいです。
（takao293） 2021/01/14(木) 09:09

＞表だと20番の休日の出勤時刻12:26から翌日の平日退勤時刻6:05を反映させたいです。

すみません、平日の退勤時刻は8:41でした汗
（takao293） 2021/01/14(木) 12:04

 必要なところだけ抜き出します。
 １）出退勤時刻表の形を変更します。
 　　下図「出退勤時刻表」のような形にしてください。
 ２）出退勤時刻表の
 　　E4=IF(C4>D4,"夜勤","")
 　　H4=IF(F4>G4,"夜勤","")
 　　として、下方向にコピー
 ３）シフト表に作業用の行を設けます。
 　　　D6=IF(D7="","",INDEX((出退勤時刻表!$E:$E,出退勤時刻表!$H:$H),D7,,IF(D13="休日",2,1)))
 　　　D7=IFERROR(MATCH(D10,出退勤時刻表!$B:$B,0),"")
 　　　右方向ニコピー
 ４）出勤と退勤の計算式
 　　　D11=IF(D7="","",INDEX((出退勤時刻表!$C:$C,出退勤時刻表!$F:$F),D7,,IF(D13="休日",2,1)))
 　　　D12=IF(D7="","",INDEX((出退勤時刻表!$D:$D,出退勤時刻表!$G:$G),D7,,IF(IF(D6="夜勤",E13,D13)="休日",2,1)))
 　　　右方向ニコピー

 以上で、下記の結果になりました。

 ■結果図
     |[B]       |[C]       |[D]   |[E] |[F]   |[G]|[H]|[I]|[J]|[K]   |[L] 
 [6] |          |夜勤フラグ|夜勤  |    |夜勤  |   |   |   |   |夜勤  |夜勤
 [7] |          |航路行番号|    23|    |    23|   |   |   |   |    23|  47
 [8] |氏名      |日        |     1|   2|     3|  4|  5|  6|  7|     8|   9
 [9] |          |曜        |金    |土  |日    |月 |火 |水 |木 |金    |土  
 [10]|○○　□□|指　定    |    20|-   |    20|   |   |   |-  |    20|  44
 [11]|          |出勤      |12:26 |    |12:26 |   |   |   |   |12:17 |    
 [12]|          |退勤      |6:05  |    |8:41  |   |   |   |   |6:05  |    
 [13]|          |備考      |休日  |休日|休日  |   |   |   |   |      |休日
 [14]|          |          |休→休|    |休→平|   |   |   |   |平→休|

 ■出退勤時刻表
     |[B] |[C]  |[D]  |[E]       |[F]  |[G]  |[H]       
 [2] |    |平日 |     |          |休日 |     |          
 [3] |行路|出勤 |退勤 |夜勤フラグ|出勤 |退勤 |夜勤フラグ
 [4] |   1|4:18 |12:06|          |4:18 |11:21|          
 [5] |   2|4:58 |13:21|          |5:04 |13:35|          
 [6] |   3|5:12 |13:15|          |5:32 |14:05|          
 [7] |   4|5:18 |13:35|          |5:52 |14:15|          
 [8] |   5|5:43 |14:41|          |6:01 |14:21|          
 [9] |   6|6:02 |14:55|          |6:29 |14:25|          
 [10]|   7|6:26 |15:01|          |7:00 |14:41|          
 [11]|   8|7:36 |16:35|          |7:26 |15:41|          
 [12]|   9|7:37 |17:21|          |7:40 |16:01|          
 [13]|  10|7:51 |17:34|          |     |     |          
 [14]|  11|7:58 |17:51|          |7:46 |16:21|          
 [15]|  12|8:11 |17:43|          |8:06 |16:41|          
 [16]|  13|8:35 |18:46|          |8:20 |17:01|          
 [17]|  14|9:06 |19:13|          |8:34 |17:35|          
 [18]|  15|10:17|20:35|          |9:06 |18:01|          
 [19]|  16|10:48|21:21|          |9:17 |18:25|          
 [20]|  17|11:17|21:23|          |9:57 |18:41|          
 [21]|  18|12:06|21:43|          |11:46|20:01|          
 [22]|  19|12:06|21:54|          |11:57|20:35|          
 [23]|  20|12:17|8:41 |夜勤      |12:26|6:05 |夜勤      
（稲葉） 2021/01/14(木) 15:10

稲葉さん、ありがとうございます！試してまたご報告させて頂きます
（takao293） 2021/01/14(木) 15:53

 稲葉様

お陰様で以下の形で平日、休日を跨ぐ夜勤の場合でも出退勤を反映させることができました！

少しアドバイス頂いた形と違いますが

出勤のセル=IF(D14="休日",VLOOKUP(D$10,出退勤時刻表!$F:$G,2,0),IF(D14="",VLOOKUP(D$10,出退勤時刻表!$B:$C,2,0)))

退勤のセル=IF(AND(E14="休日",D15="泊勤務"),VLOOKUP(D$10,出退勤時刻表!$F:$H,3,0),IF(AND(E14="",D15="泊勤務"),VLOOKUP(D$10,出退勤時刻表!$B:$D,3,0),IF(AND(D14="休日",D15=""),VLOOKUP(D$10,出退勤時刻表!$F:$H,3,0),IF(AND(D14="",D15=""),VLOOKUP(D$10,出退勤時刻表!$B:$D,3,0)))))

アドバイス頂いた作業用の行を設けた結果、以下の形になりました。
1月1日が休日～休日の22で出退勤が休日：休日
1月3日が休日～平日の22で出退勤が休日：平日

自動で反映させることが出来ました。

その他アドバイス頂いた皆様、本当にありがとうございました。

     |[A]|[B]             |[C]   |[D]   |[E] |[F]   |[G]|[H] |[I]|[J]  |[K]  |[L]   |[M] 
 [7] |   |                |      |      |    |      |   |    |   |     |     |      |    
 [8] |   |氏名            |日    |     1|   2|     3|  4|   5|  6|    7|    8|     9|  10
 [9] |   |                |曜    |金    |土  |日    |月 |火  |水 |木   |金   |土    |日  
 [10]|   |　○○　□□    |指　定|   22 |-   |    22|-  |    |-  | 7060|公   |    44|-   
 [11]|   |                |出勤  |12:57 |    |12:57 |   |#N/A|   |9:39 |     |17:17 |    
 [12]|   |                |退勤  |6:53  |    |9:15  |   |#N/A|   |16:39|FALSE|12:15 |    
 [13]|   |                |      |      |    |      |   |    |   |     |     |      |    
 [14]|   |                |平/休 |休日  |休日|休日  |   |    |   |     |     |休日  |休日
 [15]|   |                |泊勤務|泊勤務|    |泊勤務|   |    |   |     |     |泊勤務|    
 [16]|   |                |行番号|    26|  74|    26| 74|    | 74|   56|   75|    48|  74
 [17]|   |                |      |      |    |      |   |    |   |     |     |      |    
 [18]|   |                |      |      |    |      |   |    |   |     |     |      |

（takao293） 2021/01/21(木) 22:12

コメント返信：

[ 一覧(最新更新順) ]

[[20201231001800]] 『シフト表、出退勤時刻の自動入力について』（takao293） ページの最後に飛ぶ

[[20201231001800]]　『シフト表、出退勤時刻の自動入力について』（takao293）　ページの最後に飛ぶ