エクセル関数：シフト表を超過勤務表へ入力したい

質問

エクセルシート1に勤務表、シート2に超過勤務一覧表を作っています。

勤務形態としてはA,B,C,AB,BCの5種類あり、
週2回A,B,Cの各2名の6人体制（7時間労働が６人）、週5回はAB2名,BC2名の4人体制(8時間労働が４名)で動いています。
A,B,Cという勤務体制とAB,BCという勤務体制が混ざることはありません（例：2月10日に田中さんがA,同日に佐藤さんがBCという勤務体制はあり得ない）

この週5回のAB,BCという勤務体制の日については1時間の残業が発生しているため、その管理を超過勤務一覧表にて管理しています。（A,B,Cという勤務体制の日については残業が発生していないため管理はしていません。）

超過勤務一覧表ではセルA1,2,3,,,に日付、B1,2,3,,,に名前、C1,2,3,,,に勤務時間（例：ABであれば19:00~20:00、BCであれば20:00~21:00というように記載）、D1，2，3,,,に残業時間数(毎回ほぼ決まって1時間)を記入しています。

現在はシート1の勤務表をプリントアウトし、それを片手に持ちシート2の超過勤務一覧表へ毎回手入力していますが、少しでも業務負担をはかりたく、
IF関数やVLOOK関数などについて調べてみましたがうまくできませんでした。

勤務表にて2月10日に田中さんと佐藤さんがABという勤務形態、鈴木さんと田中さんがBCという勤務形態として働いた場合、それを上述のような一覧表に一部分でもいいので自動入力できるような関数はありませんでしょうか？
すべて手入力の状況から少しでも脱することができればと思っています。よろしくお願いします。

回答No.5

Prome_Lin

関数では、大変なので、「VBScript」でプログラムを書きました。

前提条件は、「勤務表.xls（.xlsx）」（名前は何でも問題ありません）の「シート１」は、
2月勤務表（←セル「A1」ですが、「B1」などとセル結合していても可、必ず頭に「月」を入れておいてください）
◆10◆11（←「A2」は空白で「B2」から日付が入っている）
曜日◆水◆木（←３行目は「曜日」）
田中◆AB◆AB（←４行目から人名や勤務体制）
佐藤◆BC◆AB
鈴木◆AB◆BC
高橋◆BC◆BC

「シート２」（左から２番目にシート）の、７行目には「（セル「A7」は空白◆日付◆氏名◆業務内容◆超過勤務時間◆時間数◆備考」という項目名がすでに入っているものとします（セル「B7」から「日付」、「氏名」～と入っている、ということ）。

以下のプログラムを範囲指定して、「Ctrl＋c」（コントロールキーを押しながら「c」（コピー・記憶）を押す）、テキストエディタかメモ帳を立ち上げて、そこで「Ctrl＋v」（ペースト・貼り付け）します。

名前は何でもいいのですが、拡張子は必ず「～.vbs」というように「vbs」とします（例えば、「Shukei.vbs」。

適当なフォルダを作り、「勤務表.xlsx」と、「Shukei.vbs」だけをそのフォルダに入れ、「Shukei.vbs」ファイルをダブルクリック（もしくは、シングルクリック→「Enter」の方がより確実）すると、「シート１」の「A1」セルから「月」を取り込んで、「～月勤務表.xlsx」（例えば「2月勤務表.xlsx」）という名前で同じフォルダに保存します。

「2月勤務表.xlsx」を開き、「シート２」を開いてみてください。

できるだけのことはしてあります。

Option Explicit
Dim a, b, c, d, e, i, j, k, m, n, u, v, w, x, y, z
Set u = CreateObject("Scripting.FileSystemObject")
Set v = u.GetFolder(".")
Set w = CreateObject("Excel.Application")
For Each a In v.Files
b = LCase(u.GetExtensionName(a.Name))
If b = "xls" or b = "xlsx" Then
Set x = w.Workbooks.Open(v & "\" & a.Name)
Set y = x.Worksheets(1)
Set z = x.Worksheets(2)
m = Left(y.Range("A1").Value, InStr(y.Range("A1").Value, "月"))
n = y.Range("A3").End(-4121).Row - 3
c = 7
d = 1
For j = 2 to y.Range("B2").End(-4161).Column
d = d + 1
For k = 4 to 3 + n
e = UCase(Trim(y.Cells(k, d).Value))
If e = "AB" or e = "BC" Then
c = c + 1
z.Cells(c, 1).Value = c - 7
z.Cells(c, 2).Value = m & y.Cells(2, j).Value & "日" & _
"(" & y.Cells(3, j).Value & ")"
z.Cells(c, 3).Value = y.Cells(k, 1).Value
z.Cells(c, 4).Value = "２人勤務のため"
z.Cells(c, 6).Value = "1h"
z.Cells(c, 7).Value = y.Cells(k, d).Value
End If
Next
Next
x.SaveAs(v & "\" & m & "勤務表")
x.Close
w.Quit
End If
Next
Set z = Nothing
Set y = Nothing
Set x = Nothing
Set w = Nothing
Set v = Nothing
Set u = Nothing

結果は、こんな感じです。

◆日付◆氏名◆業務内容◆超過勤務時間◆時間数◆備考
1◆2月10日(水)◆田中◆２人勤務のため◆◆1h◆AB
2◆2月10日(水)◆佐藤◆２人勤務のため◆◆1h◆BC
3◆2月10日(水)◆鈴木◆２人勤務のため◆◆1h◆AB
4◆2月10日(水)◆高橋◆２人勤務のため◆◆1h◆BC
5◆2月11日(木)◆田中◆２人勤務のため◆◆1h◆AB
6◆2月11日(木)◆佐藤◆２人勤務のため◆◆1h◆AB
7◆2月11日(木)◆鈴木◆２人勤務のため◆◆1h◆BC
8◆2月11日(木)◆高橋◆２人勤務のため◆◆1h◆BC

回答No.4

bunjii

補足内容では処理に必要な要件を満たしていません。
画像を貼付して頂きましたが質問の文言と一致していません。
また、備考の「1？AB」の？の部分が読めません。

当方で勝手解釈の内容を貼付画像のように処理してみました。
日付のB列
Sheet2!B8=IFERROR(INDEX(Sheet1!$B$2:$AF$2,SMALL(INDEX(((Sheet1!$B$4:$AF$7="AB")+(Sheet1!$B$4:$AF$7="BC"))*(COLUMN(Sheet1!$B$4:$AF$4)-1)+(Sheet1!$B$4:$AF$7<>"AB")*(Sheet1!$B$4:$AF$7<>"BC")*100,0),ROWS(B$8:B8))),"")
氏名のC列
Sheet2!C8=IFERROR(INDEX(Sheet1!$A$1:$A$7,SMALL(INDEX(((INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))="AB")+(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))="BC"))*ROW(B$4:B$7)+(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))<>"AB")*(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))<>"BC")*100,0),MOD(ROWS(C$8:C8)-1,4)+1)),"")
備考のG列に"AB"または"BC"の文字列のみのとき
Sheet2!G8=IFERROR(INDEX(INDEX(Sheet1!$B$1:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0)),SMALL(INDEX(((INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))="AB")+(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))="BC"))*ROW(B$4:B$7)+(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))<>"AB")*(INDEX(Sheet1!$B$4:$AF$7,0,MATCH(B8,Sheet1!$B$2:$AF$2,0))<>"BC")*100,0),MOD(ROWS(C$8:C8)-1,4)+1)),"")
その他の項目については条件が不足していますので数式の提言ができません。

回答No.3

Prome_Lin

「シート１」の勤務表がどのように入力されているのか補足してください。

そのとき、必ず、具体的にお願いします。

例えば、１行目は項目行で、
「Ａ」列が名前、「Ｂ」列が「A, B, C, AB, BCの別」、「Ｃ」列が勤務時間。

また、勤務時間のどのように入力しておられるのか、具体的に補足してください。

補足

ありがとうございます。サンプル画像を張り付けてみました。ピンクで色分けしているのは色付きAB,BCと色なしAB,BCとでペアになって動くという意味であり、これを超過勤務一覧表に表記する際に上から順にAB,BC,AB,BCとなっていなくても、たとえばAB,AB,BC,BCとなっていて構いません。引き続き回答賜りますとうれしいです。

回答No.2

bunjii

>勤務表にて2月10日に田中さんと佐藤さんがABという勤務形態、鈴木さんと田中さんがBCという勤務形態として働いた場合、それを上述のような一覧表に一部分でもいいので自動入力できるような関数はありませんでしょうか？
田中さんは同姓の2名でしょうか？
同姓であればフルネームで提示してください。
タイプミスであれば代わりの姓を提示してください。
2月10日
田中さん=AB　←┐
佐藤さん=AB　　│
鈴木さん=BC　　│
田中さん=BC　←┘
尚、勤務表と超過勤務一覧表の模擬データを提示して頂かないと具体的な数式の提案が難しいでしょう。
無駄な提案を避けたいので模擬データを提示されてから提案します。

補足

ありがとうございます。サンプル画像を張り付けてみました。ピンクで色分けしているのは色付きAB,BCと色なしAB,BCとでペアになって動くという意味であり、これを超過勤務一覧表に表記する際に上から順にAB,BC,AB,BCとなっていなくても、たとえばAB,AB,BC,BCとなっていて構いません。引き続き回答賜りますとうれしいです。

回答No.1

f272

シート1の勤務表にはどのように記載されているのかわからないと，具体的な式は作れません。しかし，シート1を目で見ながら超過勤務一覧表に書き込めるのだから，それを自動化することは可能です。

補足

ありがとうございます。サンプル画像を張り付けてみました。ピンクで色分けしているのは色付きAB,BCと色なしAB,BCとでペアになって動くという意味であり、これを超過勤務一覧表に表記する際に上から順にAB,BC,AB,BCとなっていなくても、たとえばAB,AB,BC,BCとなっていて構いません。引き続き回答賜りますとうれしいです。