関数のみで異なるブックに時間表をまとめたい。

質問

自宅にエクセルがないため手書き画像でしつれいします。

・ブックA(時間表作成用ファイル)
　日付は2行ずつあり、同じ日が二回ある人は1行目、2行目それぞれに時間を入れたい。

・ブックB（各人から提出されたシートをまとめたファイル）
　同じ日がある人は2行に増やして提出されてくる。

INDEX関数とMATCH関数を使い、日付をキーにしてブックAにまとめようと試したところ、同じ日付が無いところは入りましたが、同じ日付があるところ（画像6/1、6/2）は2行ともに同日付の1行目の時刻が反映されてしまいます。
countifなどでどうにかして2行目も反映できないでしょうか？
※ブックBのシートは加工せず、ブックAのみでまとめたいです。

回答No.2

SI299792 · ベストアンサー

行数、列数が入っていませんが、A1から始まっていて、１行目がタイトルとします。
B2に入れます。
=IF($A2=INDEX([BookB.xlsx]池田!$A:$A,MATCH($A2,[BookB.xlsx]池田!$A:$A,0)+COUNTIF($A$1:A1,$A2)),INDEX([BookB.xlsx]池田!$B:$B,MATCH($A2,[BookB.xlsx]池田!$A:$A,0)+COUNTIF($A$1:$A1,$A2)),"")
但し、横にコピペできません。横のコピペできる式です。
=IF($A2=INDIRECT("[BookB.xlsx]"&B$1&"!A"&MATCH($A2,INDIRECT("[BookB.xlsx]"&B$1&"!A:A"),0)+COUNTIF($A$1:$A1,$A2)),INDIRECT("[BookB.xlsx]"&B$1&"!B"&MATCH($A2,INDIRECT("[BookB.xlsx]"&B$1&"!A:A"),0)+COUNTIF($A$1:$A1,$A2)),"")

数式作成途中経過です。入れる必要はありませんが、入れれは動きがわかります。メンテナンスの為載せておきます。
上の式。
D2 =MATCH($A2,[BookB.xlsx]松野!$A:$A,0)+COUNTIF($A$1:$A1,$A2)
E2 =INDEX([BookB.xlsx]松野!$A:$A,D2)
F2 =INDEX([BookB.xlsx]松野!$B:$B,D2)
G2 =IF($A2=E2,F2,"")
下の式
D2 =MATCH($A2,INDIRECT("[BookB.xlsx]"&B$1&"!A:A"),0)+COUNTIF($A$1:$A1,$A2)
E2 =INDIRECT("[BookB.xlsx]"&B$1&"!A"&D2)
F2 =INDIRECT("[BookB.xlsx]"&B$1&"!B"&D2)
G2 =IF($A2=E2,F2,"")

写真、光が当たりすぎて見にくいです。フラッシュを切った方がいいです。

お礼

ご回答ありがとうございます。
上の式を使いましたところ、無事にまとめることができました！
この表を作るに当たって勉強した関数だけでうまく作動したのがとても嬉しいです。
本日作成ファイル提出の締切だったため、取り急ぎ上の式を使いましたが、横にコピーできるINDIRECT関数の式も使い方を勉強したいと思います。
ありがとうございました。

追伸
写真が見づらくて申し訳ありませんでした。

回答No.3

msMike

[A.xlsx]Sheet1!B2: =IFERROR(IF(INDEX([B.xlsx]池田!$B$1:$B$1000,SMALL(IF([B.xlsx]池田!$A$1:$A$1000=$A2,ROW(A$1:A$1000)),MOD(ROW(A1)-1,2)+1))="","",INDEX([B.xlsx]池田!$B$1:$B$1000,SMALL(IF([B.xlsx]池田!$A$1:$A$1000=$A2,ROW(A$1:A$1000)),MOD(ROW(A1)-1,2)+1))),"")

[A.xlsx]Sheet1!C2: =IFERROR(IF(INDEX([B.xlsx]松野!$B$1:$B$1000,SMALL(IF([B.xlsx]松野!$A$1:$A$1000=$A2,ROW(B$1:B$1000)),MOD(ROW(B1)-1,2)+1))="","",INDEX([B.xlsx]松野!$B$1:$B$1000,SMALL(IF([B.xlsx]松野!$A$1:$A$1000=$A2,ROW(B$1:B$1000)),MOD(ROW(B1)-1,2)+1))),"")

【お断り】上式は何れも必ず配列数式として入力のこと

お礼

ご回答いただきありがとうございます。
本日ファイル作成の期限だったため、取り急ぎで途中まで作っていた式を加工して提出いたしましたが、ご回答いただいた式も今後のために勉強させていただきたいと思います。
ありがとうございました！

回答No.1

kon555

現状の関数に、if関数での条件付けを工夫すればいけると思います。
ブックA側の1行目は現状のまま。2行目にif関数を入れ、ブックBで同日付が存在する場合は、対象とするセルを1つズラす。存在しない場合は空白にする、というような作りにすればいいかと。
同日付が存在するかについては、現在の関数で対象セルを捕まえる事は出来ているみたいなので、その1つ下との一致を確認すればいいでしょう。

ただ可能は可能なのですが、別ブック間を関数で渡すのは、ファイル移動やリネームで崩れる事もあり危険な使い方ではあります。
状況が許すなら、別ブックではなく1つのブックの別シートとしてまとめてしまうか、VBAでデータだけ写すようにするかした方がいいと思います。

お礼

早速のご回答ありがとうございました。
ブックのリンク切れは確かにあり得る&危険なため、上司に相談してシートをまとめる許可をいただきました。ご助言いただきありがとうございました。