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

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.20 ベストアンサー 回答者： mike_g 回答日時： 2020/12/25 15:13

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

貴方こそ、良くぞ頑張りましたネ(ﾊﾟﾁﾊﾟﾁ…)

》 感謝してもしきれません。
♪ほんとにそうなら(うれしいね)♪

この回答へのお礼

本当にそうです！！！
ユーモアも交え、心根の優しい方に教えて頂き、とても有意義で達成感のある8日間でした。
本当にありがとうございました！
評価つけるなんておこがましいですが、ベスト オブ ザ ベストアンサーです！

お礼日時：2020/12/25 17:48

No.19 回答者： mike_g 回答日時： 2020/12/24 20:34

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

》 「13.」での「次式」というものをご教授いただけますでしょうか？
又、抜けてましたか、此れぢゃ囘答者失格ですね、御免なさい。m(_._)m
=IF(ROW(A1)>MAX(NBR)*MAX,"",OFFSET(Sheet3!A$2,(ROW(A1)-1)/MAX,))
でした。

この回答へのお礼

ベストアンサーに選ばせていただくと全て終了となってしまうようです。
明日ベストアンサーを押させてください。
もし、なにか最後にお叱りの言葉など頂けましたら光栄です。

お礼日時：2020/12/25 13:00

No.18 回答者： mike_g 回答日時： 2020/12/24 16:31

「すみません。

もう一度最初から作ってみました」で始まる貴方の[補足]の「補足日時」は2020/12/22 19:44だから、此の時點では私の[No.17](回答日時：2020/12/22 21:53)は未讀ですよね。
氣が向いたなら、私の[No.17]を讀んだ感想を先に御聞かせ下さい。

この回答へのお礼

mike_g様

こんにちは！
　教えていただいた手順で、すべて期待していた通りにできました！
すごいです！これでかなりの業務改善が出来ました。
　素人の私に根気強く、１週間以上に渡り丁寧に教えていただき、本当にありがとうございました。
感謝してもしきれません。
　コロナ禍ですが、お身体大切にお過ごしくださいますよう、心よりお祈り申し上げます。
　本当にありがとうございました。

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

No.17 回答者： mike_g 回答日時： 2020/12/22 21:53

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

》 ５．範圍 B1:B1 を選擇⇒Alt+MC⇒…
》 →こちらを行おうとすると…。…となってしまいました。
御免なさい、「範圍 B1:B1」は「範圍 B1:B10」の間違いでした。m(_._)m

》 これならできましたが間違えているでしょうか？
御見事！其れ以降全部正しいです。(ﾊﾟﾁﾊﾟﾁ…)

其れから、
[No.15]に記載したステップ11～16を、確認、實行、再確認しましたか？
御返事下さい。

》 質問下の補足画像①のように…、…が表示されません」
貴方の畫像の事は必要ない限り忘れて、當面は「No.15の画像のようにな」る事を目指します。

序で乍ら、貴方が質問で記載した「シート1」の範圍 B3:F15 と「シート2」の範圍 A3:A17 とでは、勤務内容データ名が全て同じで無い事に御氣附でせうか？データ等には首尾一貫性を持たせませう。「テメーこそ」と云ふ怒聲あり。m(_._)m

この回答へのお礼

こんばんは。ご連絡ありがとうございます。
質問の補足があと5回しかできないのと、回答を頂いていないと、こちらから「お礼」欄を通してお聴きする術がなくなってしまうのではないかとの恐怖で返信できませんでした。すみません。

回答15の中の
「13．次式を入力したセル A3 を下方にズズーッと(47行目迄)オートフィル」→とありますが、ここの「次式」という部分が分かりません。

「13.」での「次式」というものをご教授いただけますでしょうか？

それと、回答17の最後の勤務内容データ名については、お恥ずかしい限りです。ご指摘ありがとうございます。

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

お礼日時：2020/12/24 17:50

No.16 回答者： mike_g 回答日時： 2020/12/21 09:58

[No.15]の添附圖に於いて、

17．範圍 A3:AH47 を選擇⇒Alt+C⇒選擇範圍の上でマウスの
￣￣右クリック⇒[貼り付けのオプション]直下の[値](123)アイ
￣￣コンをツン⇒範圍 A2:AG2 を選擇⇒Alt+AT2⇒セル AG2 の
￣￣[オートフィルタ]ボタン(▼)をツンして、最大値(此処では 31)
￣￣を選擇⇒抽出された空白行だけを選擇(２行目直下の行を選擇
￣￣して、Ctrl+Shift+↓を實行)⇒選擇範圍内でマウスの右ク
￣￣リック⇒[行の削除]⇒Alt+AT2
其の結果が此処の添附圖です。

この回答へのお礼

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

私がどこかで間違えているので、No.15の画像のようになりません。質問下の補足画像①のようにA3から下の勤務名と、各日の職員名が表示されません。
B3:AF47は配列數式になっていると思います。どこかをDeleteしようとすると「配列の一部を変更することはできません」となります。

No.13からもう一度確認しました。Sheet3 に於いて、
５．範圍 B1:B1 を選擇⇒Alt+MC⇒“上端行”だけにチェック入れ⇒[OK]→こちらを行おうとすると「この選択は適切ではありません。コピー領域と同じサイズかつ同じ形状で…」となってしまいました。下の6を参考にB1:B10を選択し、Alt+MC⇒“上端行”だけにチェック入れ⇒[OK]してみました。これならできましたが間違えているでしょうか？

６．範圍 A12:B12 を選擇⇒Alt+MC⇒“左端列”だけにチェック入れ⇒[OK]→出来ました。

７．Sheet3 に於いて、先程迄セル B12 を選擇したとき、[名前ボックス]に B12 と表示されて居たのが、今は MAX と表示されて居ますよね？→MAXとなっています。

８．その[名前ボックス]の右端に在る小さなナビスコマーク(▼)をツンした時、此処の添附圖の樣に、dbase、MAX、NBR、名前 と云ふリストが表示されますか？そして、Sheet1 でも同じ結果ですか？→同じです。

９．Alt+MN を實行した時の dbase の[参照範囲]が =Sheet1!$B$3:$AF$15 と成って居ますか？→同じです。ここのところの画像が②の内容です。他の参照範囲が間違えているでしょうか？

お礼日時：2020/12/22 17:44

No.15 回答者： mike_g 回答日時： 2020/12/20 21:07

Sheet2 を作成します。

11．セル A1 に 2020/12/1 を入力して、ge.m に書式設定⇒式
￣￣=DATE(YEAR($A1),MONTH($A1),COLUMN(A1)) をセル
￣￣B1 に入力して、d に書B式設定⇒式 =B1 を入力して、aaa
￣￣に書式設定⇒範圍 B1:B2 を右方にズズーッと(AF列迄)オー
￣￣トフィル
12．セル A3 に下記の[条件付き書式]を設定
￣￣￣ルールの種類:“数式を…を決定”
￣￣￣ルールの内容↓
￣￣￣￣数式→=A3=A2、書式→白のフォント色
13．次式を入力したセル A3 を下方にズズーッと(47行目迄)オー
￣￣トフィル
14．標準書式のセル B3 に式 =MOD(ROW(A1)-1,5)+1 を入力
￣￣して、此のセルのフィルハンドルを「エイヤッ!」とダブル
￣￣クリック⇒範圍 B3:B47 を選擇⇒Ctrl+C⇒セル AI3 を選擇
￣￣⇒マウスの右クリックから[明貼り付けのオプション]直下の
￣￣[値](123)アイコンをチョーン⇒範圍 B3:B47 を選擇⇒Del
￣￣キーをパシーッ15．次式を入力したセル B3 を右方にズズー
￣￣ッと(AF列迄)オートフィル⇒選擇状態の儘の範圍 B3:Af3 の
￣￣フィルハンドルを「エイヤッ!」とダブルクリック
￣￣ =IFERROR(INDEX(名前,SMALL(IF(INDEX(dbase,0,DAY(B$1))=$A3,ROW(名前)),$AI3)-2),"")
￣￣【御斷はり】上式は必ず配列數式として入力の事
16．式 =COUNTIF(B3:AF3,"") を入力したセル AH3 のふぃfるハ
￣￣ンドルを「エイヤッ!」とダブルクリック⇒セル AH2 に文字列
￣￣BLK を入力⇒列AG を削除
以上の結果が此処の添附圖なり～！

私は、此れを最終圖とされる事を推奨します。
次は空白行を削除する手順になるけど、３行目以降は全て値化されて♪おさらば東京♪ならぬ“オサラバ數式”に成ります。其れでも空白行削除したいですか？

この回答へのお礼

こんにちは。ありがとうございます。
昨日は夜勤のため、返信がおそくなりすみません。
教えていただいた内容を行ってみます。
またご連絡致します。宜しくお願い致します。

お礼日時：2020/12/21 15:45

No.14 回答者： mike_g 回答日時： 2020/12/20 14:25

貴方が質問文直下に於ける 2020/12/19 18:26 附の[補足]を何氣無く眺めて居て「アッ!」と聲を擧げました。

セル C1以右のデータが私のと似ても似つかぬ表示に成ってるではアーリマセンか！
貴方の「Sheet3に回答No.9の通り行うと」は嘘っぱちだったンですよ。此処を正整數値 1、2、3、…に書き直してみたらだう成りますか？

この回答へのお礼

こんにちは。
すみません！整数にしたらできました！
せっかく教えていただいて、もともとできていたものを、
私がいい加減でミスをした為にお手数をおかけしてしまいました。
本当にすみません。ご容赦ください。
何卒、以後もよろしくお願いいたします。

お礼日時：2020/12/20 16:14

No.13 回答者： mike_g 回答日時： 2020/12/20 10:57

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

》 「Sheet1 に於ける下準備」で、…押しています。
了解。
だうやら、私のミスで、御迷惑を掛けて居るのかも。濟みません。m(_._)m
其のチェックをする前に、次の操作を實行願ひます。

Sheet3 に於いて、
５．範圍 B1:B1 を選擇⇒Alt+MC⇒“上端行”だけにチェック入れ
￣￣⇒[OK]
６．範圍 A12:B12 を選擇⇒Alt+MC⇒“左端列”だけにチェック
￣￣入れ⇒[OK]

扨て、チェック開始です。

７．Sheet3 に於いて、先程迄セル B12 を選擇したとき、[名前ボッ
￣￣クス]に B12 と表示されて居たのが、今は MAX と表示されて
￣￣居ますよね？
８．その[名前ボックス]の右端に在る小さなナビスコマーク(▼)をツ
￣￣ンした時、此処の添附圖の樣に、dbase、MAX、NBR、名前 と
￣￣云ふリストが表示されますか？そして、Sheet1 でも同じ結果で
￣￣すか？
９．Alt+MN を實行した時の dbase の[参照範囲]が
￣￣=Sheet1!$B$3:$AF$15 と成って居ますか？

以上のチェック結果に問題が無ければ、私は最早オテアゲで退散せざるを得ません。m(_._)m

No.12 回答者： mike_g 回答日時： 2020/12/19 21:25

貴方が補足に附けた畫像、見ました。

私の Excel 2019 では、ワークシートを表示させておいて、Altキーを0.5～2秒位押し續けて居ると、此処の添附圖の樣にメニューコマンドの傍らに白抜き黒豆腐が表示されますが、貴方の Excel 2016 でも同じ現象を呈しますか？
つまり「Alt+MC」の意味が分かりますか、と問ふて居るのです。

この回答へのお礼

夜分までありがとうございます。すみません。
職場に居られず、自宅のPC（こちらは Excel 2013です）で同じことを行っています。
職場のExcel 2016でも、自宅のExcel 2013でもどちらも、白抜き黒豆腐が表示されます。

それと「Sheet1 に於ける下準備」で、Sheet1のA2:A15 を選択し、「Alt+MC」を行って、【上端行】だけにチェックし⇒[OK]をしています。
Sheet1の、B3:AF15 を選択し⇒[名前ボックス]にある B3 を dbase に書き換えてEnterキー押しています。

Sheet3 での準備作業の確認をしました。
Sheet3のB12 の、[数式バー]計算式も=MAX(C2:AG10)となっています。
Sheet3のC2も、=COUNTIF(INDEX(dbase,,C$1),$A2)となっています。

すみませんがどうぞよろしくお願いいたします。

お礼日時：2020/12/19 23:08

No.11 回答者： mike_g 回答日時： 2020/12/19 21:11

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

》 範囲C2:AG10とB12です。
了觧。
セル B12 を選擇した時の姿が[No.9]の添附圖です。
貴方もセル B12 を選択して措いて、[数式バー]を眺めて下さい。
添附圖と同じに(「同じような」等と云は無いッ!) =MAX(C2:AG10) と表示されますか？
次に、セル C2 を選擇ときは、だう當該バー上に表示されますか？
ステップ３に示した式と全く同じですか？
當該式には dbase と云ふ文字が使はれて居ます。

扨て、[No.8]に書いていた「Sheet1 に於ける下準備」中のステップ1、２を實行して居ましたか？
今に成って「Alt+MC」の意味が分から無かった、などとホザカ無いッ！