関数を教えて下さい。

質問

月曜日　　佐藤　　高橋　　黒田
　9：00　　　佐藤　　　　　　黒田
10：00　　　佐藤　　高橋　　黒田
11：00　　　佐藤　　高橋　　黒田
12：00　　　佐藤　　　　　　　黒田

火曜日　　　山田　　田中　　右田　　黒田
　9：00　　　山田　　　　　　　右田　　黒田
10：00　　　山田　　　　　　　右田　　黒田
11：00　　　山田　　田中　　　　　　　黒田
12：00　　　山田　　田中

出勤表として上記のような表を作り管理しています。別の名簿シートに出勤している曜日の範囲に名前があるとその曜日を返して表示しているシートがあるのですが現在はそこに手で時間を入力しています。しかし100名以上在籍しており勤務時間もよく変動するので入力間違いが生じます。その為その下に勤務時間も自動で表示したいと考えています。しかしどのような関数を入れれば良いのか頭を絞って考えましたが分かりません。どなたか力を貸していただけないでしょうか

今はこのようになっています。〔曜日は自動表示になっています〕
佐藤　　　月　　0　
高橋　　　月　　0　
田中　　　　0　火
黒田　　　月　 火　
右田       0　   火
山田　　　0　　火

それをこのように自動表示したいです。

佐藤　　　　　月　
　　　　　9：00　　12：00　
高橋　　　　　月
　　　　　10：00　　11：00　　
田中　　　　　火
　　　　　11：00　　12：00　
黒田　　　　　月　　　　　　　　　　　 火
　　　　　9：00　　12：00　　　9：00　　11：00　　　　　　　　
右田             火
　　　　　9：00　　11：00
山田　　　　　　火
　　　　　9：00　　12：00

佐藤さんの9：00を表示する計算式と12：00を表示する計算式を教えてください。よろしくお願いいたします

回答No.3

KURUMITO

お示しの元の表がシート1に有って、A1セルに曜日が、その下方に時刻が、さらに数行空けて曜日が、その下方に時効が・・・のように入力され、曜日の右側の列には氏名が、また、時刻の行では該当するところの列に同じ氏名が入力されているとします。
そこで例えばH列までが利用されているとして、J列から右の列を作業列とします。
J1セルから右のセルには、佐藤、高橋、田中、黒田・・・のようにセルごとに氏名を入力します。
J2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(NOT(ISNUMBER($A2)),"",IF(OR(J$1="",COUNTIF($B2:$H2,J$1)=0),"",IF(OR(AND(ISNUMBER($A2),NOT(ISNUMBER($A1))),AND(ISNUMBER($A1),INDEX($B:$H,ROW()-1,MATCH(J$1,$B2:$H2,0))="")),INT(MAX(J$1:J1))+1,IF(AND(ISNUMBER($A2),INDEX($B:$H,ROW()+1,MATCH(J$1,$B2:$H2,0))=""),MAX(J$1:J1)+0.1,""))))

次にお求めの表ですがシート2に表示させるとしてA1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(MOD(ROW(A1),2)=0,INDEX(Sheet1!$J$1:$XX$1,ROUNDUP(ROW(A1)/2,0))=0),"",INDEX(Sheet1!$J$1:$XX$1,ROUNDUP(ROW(A1)/2,0)))

B1セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNTA(Sheet1!$J$1:$XX$1)*2,"",IF(MOD(ROW(A1),2)=1,IF(COUNTIF(INDEX(Sheet1!$J:$XX,1,MATCH($A1,Sheet1!$J$1:$XX$1,0)):INDEX(Sheet1!$J:$XX,1000,MATCH($A1,Sheet1!$J$1:$XX$1,0)),ROUNDUP(COLUMN(A1)/2,0))=0,"",IF(MOD(COLUMN(A1),2)=1,INDEX(Sheet1!$A:$A,ROUNDUP(COLUMN(A1)/2,0)*10-9),"")),IF(AND(MOD(ROW(A1),2)=0,MOD(COLUMN(A1),2)=1,COUNTIF(INDEX(Sheet1!$J:$XX,1,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)):INDEX(Sheet1!$J:$XX,1000,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)),ROUNDUP(COLUMN(A1)/2,0))=1),INDEX(Sheet1!$A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0),INDEX(Sheet1!$J:$XX,1,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)):INDEX(Sheet1!$J:$XX,1000,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)),0)),IF(AND(MOD(ROW(A1),2)=0,MOD(COLUMN(A1),2)=0,COUNTIF(INDEX(Sheet1!$J:$XX,1,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)):INDEX(Sheet1!$J:$XX,1000,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)),ROUNDUP(COLUMN(A1)/2,0)+0.1)=1),INDEX(Sheet1!$A:$A,MATCH(ROUNDUP(COLUMN(A1)/2,0)+0.1,INDEX(Sheet1!$J:$XX,1,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)):INDEX(Sheet1!$J:$XX,1000,MATCH(OFFSET($A1,-1,0),Sheet1!$J$1:$XX$1,0)),0)),""))))

次にB列から右横の列を選択して右クリックし、「セルの書式設定」から「表示形式」で時刻を選択してOKします。
最後にはB1セルとC1セルを結合セルに、B2セルとC2セルは文字を中央に配置する操作をして、それからB1セルとC2セルを範囲としてコピーし、右横方向にドラッグし、「書式のみコピー」で決定します。
その後はB1セルから最後の列の2行目までを範囲として選択し、下方にドラッグ操作をして「書式のみコピー」で決定すれば完成です。
複雑な関数です。初めはこちらの指示通りで一度試験してみてください。その後にご自分のケースに当てはめてください。

から下方のセルには1行おきに

お礼

すぐにお返事できずに申し訳ありませんでした。ご指導いただいている様に一度やってみます。簡単なLEN関数？等で出来るのでは？？と思っていたのですが全く筋違いであったようでお恥ずかしいばかりです。おっしゃっていただいているように1つのシートの中に全曜日のシフトが入力されています。このエクセルが私たちの業務の全ての主軸になっている状況でこのエクセルで人時の管理がなされており現在非常に複雑な構成になっています。基本的には出勤人数・応援人数・欠勤人数などを把握する為の表です。一度やってみます

補足

すいません。excel2003だからでしょうか？上記の式をB1にいれましたが「式が長すぎます」とエラーが出てしまいます。行き詰まってしまいました。

回答No.2

imogasi

これは表の組み換えのタイプ（型）だと思うが、そのコーナーの熟練回答者ならいざ知らず、質問者のレベル（質問の仕方や、表の構成を作る前によく考えていない、初心者レベルと思う）難しいと思う。エクセルの関数は表の組み換えは苦手の分野（得意は計数の合計や件数のカウント）。まあ表設計を変えるか、あきらめてください。
こういうのはＶＢＡででも出来るかどうかぐらい、難しい。
本件特有のデータの構成ルールに依存したものになるから。
経験がないと、ＶＢＡもこれらが出来るレベルになるのは時間がかかる。
例データ
月曜日 佐藤 高橋 黒田
9:00 佐藤 黒田
10:00 佐藤 高橋 黒田
11:00 佐藤 高橋 黒田
12:00 佐藤 黒田

火曜日 山田 田中 右田 黒田
9:00 山田 右田 黒田
10:00 山田 右田 黒田
11:00 山田 田中 黒田
12:00 山田 田中
ーーー
１つのやり方だが、
これをＶＢＡで構成しなおして（分解して）、並べ替える
ＶＢＡコードは省略する。
火曜日 9:00 右田
火曜日 10:00 右田
月曜日 10:00 高橋
月曜日 11:00 高橋
火曜日 9:00 黒田
火曜日 10:00 黒田
火曜日 11:00 黒田
月曜日 9:00 黒田
月曜日 10:00 黒田
月曜日 11:00 黒田
月曜日 12:00 黒田
月曜日 9:00 佐藤
月曜日 10:00 佐藤
月曜日 11:00 佐藤
月曜日 12:00 佐藤
火曜日 9:00 山田
火曜日 10:00 山田
火曜日 11:00 山田
火曜日 12:00 山田
火曜日 11:00 田中
火曜日 12:00 田中
ーーーーーーーーーーー
このデータに基づいて希望の表を作ることになる。
ＶＢＡコードは省略する。

お礼

ありがとうございます。お察しのとおり表の構成を作る前から全く考えれてはおらず専任者も移動でコロコロ変わる事も重ねてかなりのツギハギExcelシートになっています。私も初心者でVBAレベルだろうな…という風にも思っておりました。しかし今から表の構成し直しとは目をふさぎたくなるデータ量なのでなんとか簡易でできないものか…と質問にいたりました。鋭いご指摘でビックリしました。ありがとうございます

回答No.1

KURUMITO

最初にお示しの表は同じシート内に曜日を作って入力しているのでしょうか？それとも曜日ごとに別のシートに入力しているのでしょうか？元のデータがあるシートの内容を具体的に教えてください。

お礼

色々お時間割いて頂きありがとうございました。結局解決には至っておりませんがまた、自身で学習し質問の仕方も学びます。