Excel (一般機能)

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

 
(Windows 10 Home : Excel 2016)
表参照での時間帯内の該当データ抽出方法について
投稿日時: 19/12/06 15:23:03
投稿者: タカヒロ

お世話になります。
下表のG列、H列に左表(A1からE4)のA1からB4までのデータを抽出して
表示したいと考えています。
 
下表のG列とH列はデータ抽出のイメージを記載いたしました。
 
@I1からJ6に時間を記載するとG列に機種、H列に数をエクセル関数でデータを抽出する。
AI列始時間からJ列終時間までの時間帯とDの始時間からEの終時間の重なる時間帯の
多い行(A列セル、B列セル)を参照。
B該当のデータが無い場合は「無」と記載。
 
よろしくお願いいたします。
 
 

	A	B	C	D	E	F	G	H	I	J
1	機種	数	作業者	始時間	終時間		機種	数	始時間	終時間
2	AA	2	X氏	8:00	11:00		AA	2	8:30	10:30
3	ZZ	1	Y氏	10:25	11:00		AA	2	8:30	10:30
4	PP	3	Q氏	13:00	14:30		ZZ	1	10:40	12:00
5							無	無	12:45	14:00
6							PP	3	14:00	15:00

回答
投稿日時: 19/12/06 15:49:55
投稿者: コナミ

5行目はなぜPPに該当しないのですか?

回答
投稿日時: 19/12/06 16:23:16
投稿者: んなっと

☆は無ではありません。PPが1:00の重なりです。
 
   A  B    C    D    E F   G  H    I    J
1 機種 数 作業者 始時間 終時間   機種 数 始時間 終時間
2  AA  2  X氏  8:00  11:00    AA  2  8:30  10:30
3  ZZ  1  Y氏  10:25  11:00    AA  2  8:30  10:30
4  PP  3  Q氏  13:00  14:30  AA,ZZ 2,1  10:40  12:00 ←★
5                     PP  3  12:45  14:00 ←☆
6                     PP  3  14:00  15:00
 
それからどうでもいいかもしれませんが、★は該当するものが2つあります。
どちらも0:20の重なりです。

投稿日時: 19/12/07 05:25:31
投稿者: タカヒロ

例表の記載時の確認ミスがあり申し訳ありませんでした。
 
・コナミ様
 記入例の5行目の「無」はPPにあたりますが、私の記載間違いです。
 左表(A1〜E4のD列・E列の始時間と終時間)に(I列・G列の始時間と終時間の範囲に)当てはまる時間帯がなけえば「無」とG列、H列の該当セルに記載としたいと考えています。
 
・んなっと様
不手際があり申し訳ありませんでした。
 
(1)*の内容について
5行目は掲載の例表の間違いでした。
この例では「PP」で間違いありません。申し訳ありません。
I5、J5セル間の時間帯がD列からE列観の時間帯に重なりが無い場合は「無」とG列、H列の該当セルに「無」と記載したいです。
 
(2)★の内容について
抽出データを1つにデータを絞りたいですので下記の条件を追加させていただきます。
 
・追加条件1:左表中と重なる時間帯が複数件あった場合、左表の始時間、終時間の所要時間が長いものの該当行の「機種」と「数」を抽出
 
・追加条件2:左表中と重なる時間帯が複数件あり、左表の始時間、終時間の所要時間の長さが上位2件が同じだった場合、左表の始時間の早い時間の該当データ行の「機種」と「数」を抽出。 ※((早い)0時00分→23時59分(遅い))
 
・追加条件3:左表中と重なる時間帯が複数件あり、左表の始時間、終時間の所要時間及び左表の始時間も同じだった場合、左表の該当データ行の一番上段行の「機種」と「数」を抽出。
 
 
よろしくお願いいたします。

回答
投稿日時: 19/12/07 08:34:06
投稿者: んなっと

贅沢な内容。条件2は無視。
 
   A  B    C    D    E F   G  H    I    J    K
1 機種 数 作業者 始時間 終時間   機種 数 始時間 終時間     
2  AA  2  X氏  8:00  11:00    AA  2  8:30  10:30 120.125
3  ZZ  1  Y氏  10:25  11:00    AA  2  8:30  10:30 120.125
4  PP  3  Q氏  13:00  14:30    AA  2  10:40  12:00  20.125
5                     PP  3  12:45  14:00 60.0625
6                     PP  3  14:00  15:00 30.0625
7                     無 無  14:30  15:00  0.0625
8                     無 無  14:40  15:00 -9.9375
 
K2
=AGGREGATE(14,6,1440*(TEXT($E$2:$E$500,"[<"&$J2&"]標準;"""&$J2&"""")-TEXT($D$2:$D$500,"[>"&$I2&"]標準;"""&$I2&""""))+$E$2:$E$500-$D$2:$D$500,1)
下方向・↓
G2
=IF($K2>1,INDEX(A$2:A$500,MATCH($K2,INDEX(1440*(TEXT($E$2:$E$500,"[<"&$J2&"]標準;"""&$J2&"""")-TEXT($D$2:$D$500,"[>"&$I2&"]標準;"""&$I2&""""))+$E$2:$E$500-$D$2:$D$500,),0)),"無")
右方向・→下方向・↓

投稿日時: 19/12/07 10:50:16
投稿者: タカヒロ

んなっと様
早速のご回答ありがとうございました。
条件2は確かに贅沢な話でした。
とても助かりました。
ご回答の内容を活用させていただきます。
ありがとうございました。