関数を使用して、エクセルの勤務表から、勤務形態別に担当者を抽出したい

A　　　　　　　　　　B　　 C　　 D　 　E　 　F
1　　R2.12　　　　　１　　２　　３　　４　　５
2　　　　　　　　　　火　　水　　木　　金　　土
3　　高井　　　　　　夜　　明　　公　　早　　遅2
4　　新野　　　　　　明　　公　　早　　公　　公
5　　小野　　　　　　公　　遅　　早2　　遅　　夜
6　　強井　　　　　　早　　夜　　明　　公　　早
7　　浅池　　　　　　遅　　早　　夜　　明　　公
8　　青矢　　　　　　公　　公　　遅　　夜　　明
9　　宮賀　　　　　　明　　公　　公　　遅　　公
10　　富井　　　 　　 公　　公　　早○　　夜　　明
11　　引下　　　　　　遅　　夜　　明　　公　　早
12　　石井　　　　　　遅　　公　　公　　早○　夜
13　　新見　　　　　　公　　遅　　夜　　明　　公
14　　山下　　　　　　夜　　明　　公　　遅　　日
15　　村上　　　　　　公　　早　　遅　　公　　日

　シート1に、A列３行目から名前（実際は45名います）、B列から右に1～31日までの勤務表です。
勤務内容は、早・早2・早○・日(勤)・遅(番)・遅2・夜(勤)・明(け)・公(休)があります。
（早が2人いたり、いなかったり…公休が5人だったり4人だったり、日勤がいたり、いなかったりします…）

　シート2に、以下のようにしたいのです。（または同じシートの右側や下の方でもいいです）

　　　A　　　　　　　B　　　C　　　D　　　E　　　F
1　　R2.12　　　　　１　　　２　　　３　　　４　　　５
2　　　　　　　　　　火　　　水　　　木　　　金　　　土
3　　早　　　　　　強井　　浅池　　新野　　高井　　強井
4　　　　　　　　　　　　　村上　　　　　　　　　　引下
5　　早2　　　　　　　　　　　　　小野　　　　　　
6　　早○　　　　　　　　　　　　　富井　　石井　　
7　　日勤　　　　　　　　　　　　　　　　　　　　　山下
8　　　　　　　　　　　　　　　　　　　　　　　　　村上
9　　遅番　　　　　浅池　　小野　　青矢　　小野　　
10　　　　　　　　　引下　　新見　　村上　　宮賀　　
11　　　　　　　　　石井　　　　　　　　　　山下　　
12　　遅2　　　　　　　　　　　　　　　　　　　　　　高井
13　　夜勤　　　　　高井　　強井　　浅池　　青矢　　小野
14　　　　　　　　　山下　　引下　　新見　　富井　　石井
15　　明け　　　　　新野　　高井　　強井　　浅池　　青矢
16　　　　　　　　　宮賀　　山下　　引下　　新見　　富井
17　　公休　　　　　小野　　新野　　高井　　新野　　新野
18　　　　　　　　　青矢　　青矢　　宮賀　　強井　　浅池
19　　　　　　　　　富井　　宮賀　　石井　　引下　　宮賀
20　　　　　　　　　新見　　富井　　山下　　村上　　新見
21　　　　　　　　　村上　　石井　　　　　　　　　　　　

このようなことはできるのでしょうか。（今日の早番は誰なのか？今日の遅番は誰と誰なのか？見たいのです。）
マクロは使えませんので、関数で何とか行いたいのですが、どなたかご教授ください。
どうぞ宜しくお願い致します。

列がズレてしまうかもしれません。ご容赦ください。

質問者からの補足コメント

• エクセルの2016です。
  よろしくお願いいたします。

    補足日時：2020/12/17 15:19

• 早：3人、早2：2人、早○：4人、日(勤)：5人、遅(番)：4人、遅2：1人、夜(勤)：5人、明(け)：5人、公(休)：17人となります。
  どうぞよろしくお願いいたします。

    補足日時：2020/12/17 15:45

• No.9を行ってみましたらこのようになってしまいました。

  
    補足日時：2020/12/19 18:26

• こんにちは。すみません。お手数をお掛けしております。
  補足画像を貼ります。宜しくお願い致します。

  
    補足日時：2020/12/22 17:45

• すみません。もう一度最初から作ってみました。
  上の②の画像と今回の下の画像では「名前の管理」の「値」が違っていました。
  そのまま続けていって、回答No.15の12と13の操作を行いますとオートフィルしたセルは白のフォントですが、背景を黒にしても何も表示されません。
  回答No.15の14で、標準書式のセル B3 に式 =MOD(ROW(A1)-1,5)+1 を入力して、此のセルのフィルハンドルを「エイヤッ!」とダブルクリック→とのことですが、ここでフィルハンドルをダブルクリックしても、オートフィルになりません。左側にデータがないからでしょうか？B3:B47までオートフィルしても良いでしょうか？
  ご指導よろしくお願いします。

  
    補足日時：2020/12/22 19:44

No.10 回答者： mike_g 回答日時： 2020/12/19 20:09

》 すべて＃REF！になってしまいました。

Excelを使ふ人は曖昧表現は避けて下さい。
貴方が仰る「すべて」は範圍 C2:AG10 とセル B12 だけですね？

この回答へのお礼

すみません。仰る通りです。
範囲C2:AG10とB12です。
どうぞよろしくお願いします。

お礼日時：2020/12/19 20:19

No.9 回答者： mike_g 回答日時： 2020/12/19 17:07

添附圖參照(Excel 2019)

最終結果は Sheet2 に示すけど、其の前に Sheet3 で準備作業をします。

列A は 貴方が示した Sheet2 の A列 の範圍 A3:A17 の項目名を順序通りに隙間無く並べ、列B は其れ等の順序番號を附しただけ。
範圍 C1:AG1 は貴方の Sheet1 の１行目の日附に符合して居ます。

３．式 =COUNTIF(INDEX(dbase,,C$1),$A2) を入力したセル C2
￣￣を右方に 30列(AG列迄)、下方に 8行(10行目迄)オートフィル
￣￣します。
４．セル B12 に入力した式 =MAX(C2:AG10) の戻り値が既述の
￣￣テンデンバラバラの最大値である事を承知されたい。

此処迄で不明な點等があれば教へて下さい。其の内容に依って、此の書き込みの繼續/中斷を決めます。

この回答へのお礼

お世話になっております。
Sheet1は回答No.8の通りできています。
Sheet2はまだ何も未入力です。
Sheet3に回答No.9の通り行うと、画像なような数字にならず、すべて＃REF！になってしまいました。
すみません。どこがおかしいのでしょうか？
画像を貼ってみようと思います。

お礼日時：2020/12/19 18:25

No.8 回答者： mike_g 回答日時： 2020/12/18 21:59

[No.3お礼]へのコメント、

》 …しているのだそうです
なあ～んだ、貴方自身の仕事では無いンだと分かってヤルキが半減しました。(^_^)
なので、説明は出來るだけ端折りますネ。でも長くなりそう。

先ずは、Sheet1 に於ける下準備を。
１．セル A2 に“名前”と入力⇒範圍 A2:A15 を選擇⇒Alt+MC⇒“上端行”だけにチェック入れ⇒[OK]
２．範圍 B3:AF15 を選擇⇒[名前ボックス]にマウスカーソルを放り込み⇒其処に在った B3 を文字列 dbase に上書き⇒Enterキーをパシーッ
(續く)

この回答へのお礼

こんにちは。お世話になっております。
他部署の勤務表なのですが、当部署にも関係しており、この状況が打破できれば法人としての業務の改善に繋がります。どうかよろしくお願いいたします。
上記のアドバイスまでは入力することが出来ました。
その後の手順をどうかご教授いただけますと幸いです。
宜しくお願い致します。

お礼日時：2020/12/19 11:25

No.7 回答者： banzaiA 回答日時： 2020/12/18 13:18

Microsoft　365 ならすぐにできるのですが。

この回答へのお礼

こんばんは。
これが出来るんですね！凄い！
これが求めていた内容なのです。
これはエクセル2016では出来ないということなんですね…。残念です。
本当にありがとうございました。

お礼日時：2020/12/18 18:02

No.6 回答者： d-q-t-p 回答日時： 2020/12/18 08:47

> 1セル１名で表記をできればと思います。

ならマクロを組むしかないと思いますが VBAの知識はあるのでしょう
か。というのもあまり単純ではない VBAプログラムを質問掲示板の誰
かに作ってもらったとしてメンテナンスできるスキルがないと後で破
綻しますよ。

こうでなければいけないという仕様が決まっていて プログラムでやろ
うと決めたのなら外注するのも手だと思います。

自作する場合 実力に見合わないことはやらない方がいいと思います。
続けるのが難しいのもそうですが引き継ぎでも苦労します。

> その人は入浴当番とし、１カ月でだいたいみんなが平均になるよう
> にするのです。
それはセルの色分けでないとできないことでしょうか。
当番担当者の文字列を太字にするとか「★」を入れるとか やりようは
あるように思います。

この回答へのお礼

マクロは出来ません。
皆さまのおっしゃる通り、そもそもの勤務表のルールを見直すのが先決かと思いました。ありがとうございました。

お礼日時：2020/12/18 17:51

No.5 回答者： d-q-t-p 回答日時： 2020/12/17 17:08

以下クエリでやる場合の手順です。

元の表にテーブル設定を入れて [データ]→[テーブルまたは範囲から]

PowerQueryエディタで
・「1」～「31」列を選択して [ピボット解除]
・「氏名」列のフィルタで「null」以外を抽出
・「属性」「値」列を選択して [グループ化]
　→新しい列名「グループ」で [すべての行]を指定
・カスタム列を追加
　→新しい列名「氏名」で「=Text.Combine([グループ][氏名], ",")」
・グループ列を削除
・「属性」「値」列を選択して [列のピボット]
　→値列を「氏名」にして 詳細設定オプション「集計しない」

以上で読み込み。

この回答へのお礼

ありがとうございます。
使ったことのない方法なので後ほど試させていただきます。

ただできましたら先ほどのお礼にも記入しましたが、1セル１名で表記をできればと思います。出勤人数が日によってバラバラなので、難しいと思います。空欄が出来てしまっても良いので何とかなりませんでしょうか？

何卒宜しくお願い致します。

お礼日時：2020/12/17 17:54

No.4 回答者： d-q-t-p 回答日時： 2020/12/17 17:01

氏名を「,」で繋いで表示していいなら PowerQueryでできますけど。

勤務内容の順番を指定したい場合は 別途順番を指定するマスタが必要
ですが。

この回答へのお礼

このようなことができるのですね。
この方法はどのようにやるのでしょうか？

ただ、実際はその表を更に色分けして（例えば、遅番３人の内１人のセルを黄色を塗って、その人は入浴当番とし、１カ月でだいたいみんなが平均になるようにするのです。
難しいのが、日曜日など、出勤が少ない時などには、遅番がいないので日勤がその黄色の入浴当番になるということもあるため、できれば、セル1つに１名の名前が出ると助かります。

どうぞよろしくお願いいたします。

お礼日時：2020/12/17 17:38

No.3 回答者： mike_g 回答日時： 2020/12/17 15:54

》 このようなことはできるのでしょうか

惡い事は云ひません、そんな事(一種類の勤務内容の人數が日に依ってテンデンバラバラ⇒行數が不定)は、特に、初心者の貴方には無理難題です。

この回答へのお礼

仰る通り、テンデンバラバラなのです。
他部署の勤務表で、スタッフから相談を受けました。
現在はこの作業を目視で表に記入しなおしているのだそうです。
何とか皆様に教えていただけましたら光栄です。

お礼日時：2020/12/17 17:26

No.2 回答者： banzaiA 回答日時： 2020/12/17 15:29

何回も失礼。

早・早2・早○・日(勤)・遅(番)・遅2・夜(勤)・明(け)・公(休)
これらの最大人数はどうなりますか？

この回答へのお礼

早：3人、早2：2人、早○：4人、日(勤)：5人、遅(番)：4人、遅2：1人、夜(勤)：5人、明(け)：5人、公(休)：17人となります。
どうぞよろしくお願いいたします。

お礼日時：2020/12/17 17:20

No.1 回答者： banzaiA 回答日時： 2020/12/17 15:05

Excelのバージョンは何ですか？

この回答へのお礼

エクセルの2016です。
よろしくお願いいたします。
gooで初めて、今回、質問させてもらいました。こちらに入力すればよいのかわかりませんでした。すみません。

お礼日時：2020/12/17 17:20