HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2021)
個別シートからガントチャートにデーターを表示したい
投稿日時: 23/03/26 20:47:15
投稿者: ももいろのことり

こんばんは。いつもお世話になり大変助かっております。
 
1日のどの時間帯に人が少なかったのかを知るために、出勤したメンバーのタイムカードの情報をまとめたガントチャートを作成したいと思います。
 
（あ）（い）のファイルからデーターを読みだして（う）を完成させたいです。
現在は1人分ずつリンク貼付けで対応しています。
 
（あ）社員マスタ
（い）タイムカードの記録を給与計算に使っている表
（う）ガントチャート（現在作成中のもの）
 
（あ）
　　Ａ　　　Ｂ　　　　Ｃ　　Ｄ　Ｅ　　　…
１　ＩＤ　　氏名　　　電話　〒　住所…
２　A101　ナカムラ　…
３　A102　タナカ　　…
４　A103　ヤマモト　…
 
 
（い）ナカムラのシートの場合
Ａ1に年、Ｄ1に月が入力されている
 　 Ａ　　Ｂ　Ｃ　　　　Ｄ　　　　Ｅ　　Ｆ　　　　Ｇ…
１　…
２　日　曜日　出社時刻　退社時刻　休憩　開始時刻　終了時刻　…
３　１　水　　21:00　　23:45　　0:30　21:00　　23:30　　…
４　２　木　　08:37　　19:20　　0:45　09:00　　19:15　　…
 
↑のようなシートが従業員分あり、これで給料の計算もしています。
上記の範囲外に、ＩＤを入力すると（あ）の社員マスタから氏名を読み出せるようにVLOOKUP関数が使用されています。
 
 
（う）
2023年3月2日の場合
　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
１　日付
２　ＩＤ　　氏名　　　開始時刻　終了時刻　09:00　10:00　11:00　…
３　A101　ナカムラ　　09:00　　19:15　　■　　　■　　　■　　…
４　A102　タナカ　　　10:00　　11:00　　　　　　■　　　■　　…
５　A103　ヤマモト　…
 
■で表されている部分には条件付き書式で塗りつぶしが設定されています。
=AND($C4<=E$3, E$3<=$D4)
 
完成させたいのは（う）の表で、日付と社員IDを入力すれば、（い）の各社員のシートから開始時刻と終了時刻を参照してC列とD列に表示させたいのです。
 
お力をお貸しいただけませんでしょうか。
どうぞよろしくお願いいたします。

回答
投稿日時: 23/03/27 07:24:45
投稿者: んなっと

2023年3月.xlsxのような月単位のブックなのでしょうか？
 
●ナカムラ シート
 
　　　A　　 B　　　　 C　　　　 D　　 E　　　　 F　　　　 G
1　2023　　　　　　　　　　　　 3　　　　　　　　　　　　　
2　　日　曜日　出社時刻　退社時刻　休憩　開始時刻　終了時刻
3　　 1　　水　　 21:00　　 23:45　0:30　　 21:00　　 23:30
4　　 2　　木　　　8:37　　 19:20　0:45　　　9:00　　 19:15
 
●タナカ シート
 
　　　A　　 B　　　　 C　　　　 D　　 E　　　　 F　　　　 G
1　2023　　　　　　　　　　　　 3　　　　　　　　　　　　　
2　　日　曜日　出社時刻　退社時刻　休憩　開始時刻　終了時刻
3　　 1　　水　　 21:00　　 23:45　0:30　　 21:00　　 23:30
4　　 2　　木　　　8:37　　 19:20　0:45　　 10:00　　 11:00
 
●2日 シート
 
　　　　　A　　　　 B　　　　 C　　　　 D
1　2023/3/2　　　　　　　　　　　　　　　
2　　　　　　　　氏名　開始時刻　終了時刻
3　　　　　　ナカムラ　　　9:00　　 19:15
4　　　　　　　タナカ　　 10:00　　 11:00
 
C3
=IFERROR(VLOOKUP(DAY($A$1),INDIRECT($B3&"!A:G"),COLUMN(C3)+3,FALSE),"")
右方向・→下方向・↓
 
※氏名ではなくIDで管理するべきですが、
シート名が氏名なのでそちらを使って検索しています。
投稿日時: 23/03/27 22:00:07
投稿者: ももいろのことり

ご返信ありがとうございます。
 

んなっと さんの引用:
2023年3月.xlsxのような月単位のブックなのでしょうか？

 
おっしゃる通りです。月単位で作成しているブックです。
先頭シートに社員マスタ（あ）、個人名シート（い）が続き、最終シートにガントチャート（う）を作成しています。
 
実際の（い）シートはセルがあちこち結合されていて、複雑になっているためか、ご提示いただいた数式を入力するとセルが空白になりましたので、数式を眺めて下記のように変更してみました。
 
INDIRECT($B3&"!A:G")
ナカムラシートのA列〜G列を参照する？ということかなと思い、実際のシートの開始時刻がN列、終了時刻がQ列でしたので
INDIRECT($B3&"!A:Q")に変更しました。
 
関係ないかもしれませんが、欄外に
=INDIRECT($B3&"!A:Q") と入力してみると
#スピル!　と表示されました。
浅い経験ですがエラーが日本語で表示されるのをはじめてみました… Shocked
 
COLUMN(C3)+3
列番号を指定する部分でしょうか。ここで頓挫しています。
ひとつも理解できていなくてお恥ずかしい次第です。
 
どこをどのように書き換えればよろしいのでしょうか。
引き続きよろしくお願いいたします。
回答
投稿日時: 23/03/28 07:01:03
投稿者: んなっと

いったんC3:D4は消去して
 
C3
=IFERROR(VLOOKUP(DAY(A$1),INDIRECT(B3&"!A:Q"),{14,17},FALSE),"")
下方向・↓
 
左端の検索列A列から数えて14列目と17列目という意味。
投稿日時: 23/03/28 20:28:17
投稿者: ももいろのことり

んなっと様
 
さっそく新しい数式をご提示いただきありがとうございました。
 
まずは、ご提示いただいた数式をそのまんまをコピーしてC3に貼り付けてみました。

引用:
=IFERROR(VLOOKUP(DAY(A$1),INDIRECT(B3&"!A:Q"),{14,17},FALSE),"")

C3は空白になりました。
 
B3に入力されている式　
=VLOOKUP(A3,社員マスタ!A2:F5,2,FALSE)　を削除して
ナカムラ　と入れてみましたが、結果は変わらず空白になります。
 
開始時間はNOP、終了時間はQRSが結合されています。
この給与計算の表は先代の人が作ったもので、
「EXCEL勤怠テンプレート」さんのものを参考に作られているようです。
 
どこでつまずいているのかわからなくて申し訳ないです。
 
私の説明もしくはご提示いただいた数式の使い方が悪いのだと思いますが、途方に暮れています。
どうぞお力をお貸しください。
回答
投稿日時: 23/03/28 20:54:22
投稿者: んなっと

新規ブックに以下の2枚のシートを作成(そのうち1枚のシート名はナカムラ)、
5か所入力・1か所数式貼り付け。
この程度のことならすぐにできますよね?
試してみてください。
 
●ナカムラ
 
　　　A　　　　　　　　　　　　　　　N　　　　　　Q
1　　　　　　　　　　　　　　　　　　
2　
3　
4　　 2　　　　　　　　　　　　　 9:00　　　　19:15
 
●2日
 
　　　　　A　　　　 B　　 C　　　D
1　2023/3/2　　　　　　　　　　　　
2　　　　　　　　
3　　　　　　ナカムラ　
 
C3
=IFERROR(VLOOKUP(DAY(A$1),INDIRECT(B3&"!A:Q"),14,FALSE),"")
表示形式　時刻
 
結果報告お待ちしています。
投稿日時: 23/03/28 21:08:17
投稿者: ももいろのことり

んなっと様
 
9:00:00　と表示されました！！Very Happy
 
あれ〜ぇ？本番のではどこが違ったのでしょうか…。
もう少し頑張ってみます！！
 
つまずくか、成功するか…また報告に来ます。
しばらくの間は閉じずにおいて置かせてください。

回答
投稿日時: 23/03/28 21:17:58
投稿者: んなっと

あとは5つのセルを比較しましょう。考えられるのは
 
・[ナカムラ]のA4が実は 2023/3/2 という日付で、表示形式で 2 に見せかけている
・[2日]のA1が、実は文字列で 2023.3.2 や 3.2 となっている
・[ナカムラ]の開始時刻がN列ではなくて、O列などになっている
投稿日時: 23/03/28 21:50:22
投稿者: ももいろのことり

んなっと様
 

引用:
・[ナカムラ]のA4が実は 2023/3/2 という日付で、表示形式で 2 に見せかけている
・[2日]のA1が、実は文字列で 2023.3.2 や 3.2 となっている
・[ナカムラ]の開始時刻がN列ではなくて、O列などになっている

 
さすがです！おっしゃる通りでした。
 
シートの保護を解除して確認すると
日付の1日の部分には
=DATE(A1,D1,1)
それ以降には
=A16+1
という風に1ずつ足されており、条件付き書式で色が設定されていました。
 
先ほどのテスト用のブックの日付の部分に同じものを設定してみると、ガントチャート側のC3は空白になりました。
 
大の月、小の月で表示される日付が自動的に変わるようになっていました。（曜日も）
 
ここを変えるのは作業が大変なので。。。（腰抜けで申し訳ないです。）
ガントチャート側のA1の日付を年、月、日をセルを分けて入力するようにすればよいでしょうか？
回答
投稿日時: 23/03/28 21:57:27
投稿者: んなっと

=IFERROR(VLOOKUP(A$1,INDIRECT(B3&"!A:Q"),{14,17},FALSE),"")
　
投稿日時: 23/03/28 22:18:28
投稿者: ももいろのことり

んなっと様
 
できました！テスト用のブックも本番用も！
 
100匹くらいの狐につままれまくった感じです！
 
いろいろとお骨折りいただき、ありがとうございました。
また困ったときにはよろしくお願いいたします。