エクセルで試合結果を入力し、各シート（チーム毎）にまとめ、データベースを自動作成する方法教えてください。

質問

例えば、Ｊリーグの全試合結果をシート１に入力します。
A列に日付、Ｂ列にホームチーム名、Ｃ列にアウェイチーム名、Ｄ列に得点（ホーム）Ｅ列に失点（ホーム）、Ｆ列に得点（アウェイ）、Ｇ列に失点（アウェイ）とします。

Ａ　　　　Ｂ　　　　Ｃ　　　Ｄ　　Ｅ　　Ｆ　　Ｇ
３月２日　東京　　名古屋　　２　　１　　１　　２
３月２日　大阪　　福岡　　　１　　０　　０　　１
３月９日　福岡　　東京　　　３　　１　　１　　３
３月９日　名古屋　大阪

シート２には、チーム毎の結果、例えば東京とすると、東京との対戦相手の結果のみが抽出される。
Ａ　　　　Ｂ　　　　Ｃ　　　Ｄ　　Ｅ　　Ｆ　　Ｇ
３月２日　東京　　名古屋　　２　　１　　１　　２
３月９日　福岡　　東京　　　３　　１　　１　　３
そして、シート３を大阪とすると、大阪との対戦相手の結果のみが抽出され表示される。
このような、シート１に試合結果を入力すると各シートに表(チーム毎の表)が自動作成される関数を教えてください。

さらに、欲を言えば、シート２を東京と限定するので、Ｂ列に対戦相手、Ｃ列に場所、Ｄ列に得点、Ｅ列に失点というように一挙にまとめた表ができる関数があるとよいのですが・・・
Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　Ｅ　
３月２日　名古屋　　ホーム　　２　　１　　
３月９日　福岡　　　アウェイ　１　　 ３　　

上記のようなデータベース作成は、エクセルで無理なのでしょうか？
またエクセルのマクロだと出来るのでしょうか？
よろしくお願いいたします。

回答No.1

noname#262398

まず、シート2のA1かどこかに、東京　と入力。
シート2以降の各チームの結果を、2行目から出力するとします。

G列、H列、I列を作業列として、
G2=MATCH($A$1,OFFSET(Sheet1!$B$2,I1,):Sheet1!$B$700,0)+I1
H2=MATCH($A$1,OFFSET(Sheet1!$C$2,I1,):Sheet1!$C$700,0)+I1
I2==MIN(IF(ISNUMBER(G2),G2,H2),IF(ISNUMBER(H2),H2,G2))
35行目(?)までコピー。

A2=IF(ISNA(I2),"",INDEX(Sheet1!$A$2:$G$700,$I2,1))
B2=IF(ISNA(I2),"",INDEX(Sheet1!$A$2:$G$700,$I2,3-($C2="アウェイ")))
C2=IF(ISNA(I2),"",IF($G2=$I2,"ホーム","アウェイ"))
D2=IF(ISNA(I2),"",INDEX(Sheet1!$A$2:$G$700,$I2,6-($C2="ホーム")*2))
E2=IF(ISNA(I2),"",INDEX(Sheet1!$A$2:$G$700,$I2,7-($C2="ホーム")*2))
同じく35行目(?)までコピー。

これでご質問の一番下の表のような感じになると思います。