Excel：カレンダーシートに反映させたい

質問

例として、
1つ目のシートは社員名簿です。
A列・社員番号
B列・氏名
C列・希望日（ここが空白になっていて、希望日を後で入れる）
…
のように縦に100名程度続いています。

2つ目のシートに添付画像のような縦カレンダーを作りました。
上で入れた希望日をカレンダーの日付ごとに振り分けてくれる数式はありますでしょうか？
画像のように1つの日につき4名まで横に氏名が並ぶようにしたいです。
1日につき4名の枠を超えてしまった時は…などは考慮しなくても結構です。シート1を入力する時点で1日の定員は超えないように管理されているものとします。

回答No.7

imogasi · ベストアンサー

関数でやってみた。複雑になって、関数としては難しいレベルの課題だろう。
まず、下記をそっくりそのままやってみて、こんな課題なら、質問者の課題に合わせて式を修正してください。
例データ　Sheet3
B1:D11（社員番号列データは空白・省略）とした。）
社員番号 氏名 希望日 仕事
鈴木 2020/8/12 レジ
大野 2020/8/15 調理
木村 2020/8/12 レジ
神田 2020/8/15 調理
田中 2020/8/2 会計
鈴木 2020/8/3 会計
木村 2020/8/4 会計
上田 2020/8/5 会計
新川 2020/8/6 調理
大野 2020/8/1 レジ
日付けと担当者と担当職のマトリックス的な表。
完成形
Sheet５　A1:H16　以下とI列より右は省略
田中 木村 鈴木 大野 神田 新川 上田
2020/8/1 レジ
2020/8/2 会計
2020/8/3 会計
2020/8/4 会計
2020/8/5 会計
2020/8/6 調理
2020/8/7
2020/8/8
2020/8/9
2020/8/10
2020/8/11
2020/8/12 レジ レジ
2020/8/13
2020/8/14
2020/8/15 調理 調理
関数式
Sheet6のB2の式
=IF(SUMPRODUCT((Sheet3!$C$1:$C$30=$A2)*(Sheet3!$B$1:$B$30=B$1))=0,"",INDEX(Sheet3!$D$1:$D$30,SUMPRODUCT((Sheet3!$C$1:$C$30=$A2)*(Sheet3!$B$1:$B$30=B$1),ROW(Sheet3!$E$1:$E$30)),))
横方向にH列まで式複写
B2:H2の式を縦方向に１６行まで式複写。
本番ではSheet3のデータ行数に合わせて、必要か所を修正必要です。
ーーー
http://office-qa.com/Excel/ex352.htm
「複数列の条件に合致した値を取り出す(条件エリア不要）」
などが参考になるだろう。

回答No.8

HohoPapa

１つ目のシートのD列を作業列に使い、
２つ目のシートのB3、C3、D3、E3を1,2,3,4としてよければ

添付画像のように
１つ目のシートのD2に
=TEXT(C2,"YYYYMMDD") & TEXT(COUNTIF(C2:$C$12,C2),"0")
と埋め、必要数下方向に複写
２つ目のシートのB3に
=IFERROR(INDEX(Sheet1!$B:$B,MATCH(TEXT($A3,"YYYYMMDD") & B$2,Sheet1!$D:$D,0),1),"")
と埋め、横方向に３列、下方向に必要数複写する対応はいかがでしょうか。

なお、式にある$C$12は、
１つ目のシート、Ｃ列最終行の絶対アドレスです。

回答No.6

msMike

[No.5]の續き、
次式を入力したセル B3 を右３列及び下方にズズーッとオートフイル
=IFERROR(INDEX(Name,MATCH(SMALL(IF(INT(PrefDateB)=$A3,PrefDateB),COLUMN(A1)),PrefDateB,0)),"")
【御斷はり】上式は必ず配列數式として入力の事

回答No.5

msMike

Sheet1 に於いて、
式 =C2+ROW(A1)/1000 を入力したセル D2 を下方にズズーッとオートフイル→範圍 A1:D101 を選擇⇒Alt+MC⇒"上端行"のみにチェック入れ⇒[OK]
(次囘に續く)

回答No.4

SI299792

Sheet1
A1: 社員番号、B1: 氏名、C1: 希望日
２行目からデータが入っているとします。
Ｄ列にワークエリアを作ります。
D2: =C2&COUNTIF(C$1:C2,C2)
下へコピペ。目障りなら非表示にして下さい。

Sheet2
B2～E2を1 2 3 4 の様に数字にします。
セルの書式設定、ユーザー定義「"予約第"0」 にします。
B3: =IFERROR(INDEX(Sheet1!$B:$B,MATCH($A3&B$2,Sheet1!$D:$D,0)),"")
右下へコピペ。

回答No.3

imogasi

勝手な例だが参考に。
Sheet1　A1:D5
社員番号 氏名 希望日 仕事
123 鈴木 2020/8/12 レジ
125 大野 2020/8/15 調理
124 木村 2020/8/12 レジ
126 神田 2020/8/15 調理
Sheet1にA1:G6
田中 木村 鈴木 大野 神田 新川　以下右略
2020/8/1
2020/8/2
2020/8/3
2020/8/4
2020/8/5
以下日付略
VBAコード　標準モジュールに
Sub test01()
Set sh1 = Worksheets("Sheet2")
Set sh2 = Worksheets("Sheet1")
'---
For r = 2 To 10　’10は現実のデータ行数を取得するコードあり、ここ々では略
dt = sh1.Cells(r, "C") '日付セル
nm = sh1.Cells(r, "B") '名前セル
tk = sh1.Cells(r, "D") 　’担当職務
’－－
書き込むべきセルの行と列を結締見つける
dr = sh2.Range("A1:A40").Find(what:=dt).Row
nc = sh2.Range("A1:Z1").Find(what:=nm).Column
sh2.Cells(dr, nc) = tk　’担当を書き込み
Next r
End Sub
Find機能と「Cells(行, 列) = データ」のVBAのしくみだけを使ったもの。
’---
結果　Sheet1
田中 木村 鈴木 大野 神田
2020/8/1
2020/8/2
2020/8/3
2020/8/4
2020/8/5
2020/8/6
2020/8/7
2020/8/8
2020/8/9
2020/8/10
2020/8/11
2020/8/12 レジ レジ
2020/8/13
2020/8/14
2020/8/15 調理 調理
2020/8/16

回答No.2

imogasi

エクセル関数しか経験できてないレベルだろうね。
エクセルは決めた（目標の）セルに値を入れるのは難しい。
その決めたセルが行と列は割り出せる場合と仮定してだ。
勿論A1に”A"と入れるのは、人間がA1に手（カーソル）を持って行くから易しいが。
VBAなら落下傘的に、行と列が決まれば、指定セルに値をセットできる（プログラムを１行で書ける）のだが。
ーー
こういう多人数のスケジュールをまとめた表を作りたい場合、エクセルのフリーソフトでもWEB上にないか、
あるいはOUTLOOKが予定スケジュールを扱っているので、そのまとめのソフトとかがないか、探したらどうか。
ーー
タイプ的に似たような質問がある。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1117334231
エクセルは、「表の組み換え」的課題は、あまり、対応されてないように思う。
まだまだ多人数に配布するなどの業務は、エクセルを用いてでは、質問者には早すぎるのでは。

回答No.1

中京区 桑原町（@a4330）

>シート1を入力する時点で1日の定員は超えないように管理されているものとします。

そんな事が出来るなら、添付してある表に直接社員番号を入力すればよい
そうすれば定員の管理も簡単です

補足

回答ありがとうございます。
日付と氏名だけならシート2だけでいいのではないかということですね。補足します。
シート1は社員番号・氏名・日にちだけではなく、他にも業務上必要なデータの入力があり、H列くらいまで使います。
また、日付順ではなく社員番号順の社員名簿がもとになっており、最初から社員の名前が並んでいるその横に日付を記入していきます。

ですので、回答者様が教えていただいたように、シート2に直接氏名（ないし社員番号）を打ち込んでいくというやり方は今まさに私がやっているやり方で、私のところに届いたシート1を基に、私がシート2に地道にコピペしてカレンダーを完成させています。

私はこのシート1だけで管理上事足りているのですが、
シート2は現場が使うカレンダー名簿で、どうしてもこの形がいいとこだわるのです。
（A41枚のカレンダーで、その日に誰が割り振られているか横に並ぶ形がいい、と）

現状、コピペの繰り返しの手作業でシート2を完成させていますがシート1を作った段階でシート2も同時にできるような数式や関数などあればと思い質問しました。