Ｅｘｃｅｌ２００３でのシート結合について

質問

Ｅｘｃｅｌ２００３でのシート結合（共通項目による）についてご教示下さい。
二つのシート（ｓｈｅｅｔ１とｓｈｅｅｔ２）にそれぞれデータが有ります。
二つのシートの共通項目列（形名と製番）でｓｈｅｅｔ１とｓｈｅｅｔ２を結合させ、結果をｓｈｅｅｔ３に表示させたいのです。

例）ｓｈｅｅｔ１
形名、製番、納入日、納入先 →１６列項目が有ります
Ｘ１ ０１ １００１０１ 東京
↓
以下、３０００行有ります

ｓｈｅｅｔ２
形名、製番、検査日、検査完了 →２５列項目有ります
Ｘ１ ０１ １００１０２ 済
↓
以下、２０００行有ります

ｓｈｅｅｔ３
形名、製番、納入日、納入先 →１６列、検査日、検査完了 →２５列
Ｘ１ ０１ １００１０１ 東京 ・・・・・ １００１０２ 済

尚、ｓｈｅｅｔ１に有ってｓｈｅｅｔ２に無いデータも有ります。
この場合は、ｓｈｅｅｔ３には、ｓｈｅｅｔ１範囲の情報のみ表示される様にしたいです。
（逆の場合も有ります。）

皆様のご支援宜しくお願い致します。

回答No.3

MackyNo1 · ベストアンサー

きわめて複雑な配列数式を駆使すればA列とB列の項目を基準にデータを合体することができますが、元リストのサイズと表示データ数を考慮すると、実用的な方法ではありません。

このようなケースでは、エクセルの一般機能であるフィルタオプションの設定や補助列を使った数式で処理することをお勧めします。

私なら、集計対象となる2つのシートのA列とB列のデータだけを１つのシートにまとめたデータを作成しておき、このデータで「データ」「フィルタ」「フィルタオプションの設定」で「重複するデータは無視する」にチェックを入れてOKし、重複のないデータを作成し、この範囲をSheet３のA1セルに貼り付けます。

次にSheet1およびSheet2のA列に補助列を挿入し、「=B2&C2」のような数式を入力します。

そしてSheet3のC2セルに以下のような数式を入力して右方向および下方向にオートフィルします。

=IF(ISNA(VLOOKUP($A2&$B2,Sheet1!$A:$Q,COLUMN(D2),0)),"",VLOOKUP($A2&$B2,Sheet1!$A:$Q,COLUMN(D2),0))

Sheet2を参照するQ2セルにも上記と同様の参照範囲を変更した数式を入力します。

お礼

ご報告が遅くなり申し訳ございません。

ご教示頂いた内容で解決しました。
特に予めフィルタオプションの設定で重複のないデータを作成する点は
精度向上にもつながり大変助かりました。

この度はご支援頂き、誠に有難うございました。

回答No.2

KURUMITO

シート1では1行目に項目名があり、2行目から下方にデータがあるとします。
16列のＰ列まで入力されているとします。そこで作業列としてＲ2セルには次の式を入力して下方にオートフィルドラッグします。
=A2&B2
Ｓ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(R2="","",MAX(S$1:S1)+1)
シート2では作業列としてＡＡ2セルに次の式を入力して下方にオートフィルドラッグします。
=A2&B2
ＡＢ1セルには次の式を入力します。
=MAX(Sheet1!S:S)
ＡＢ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AA2="","",IF(COUNTIF(Sheet1!R:R,AA2)>0,"",MAX(AB$1:AB1)+1))
まとめの表のシート3ですが同じく1行目には項目名を入力し、Ａ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(ROW(A1)<=MAX(Sheet1!$S:$S),IF(COLUMN(A1)<=16,INDEX(Sheet1!$A:$P,MATCH(ROW(A1),Sheet1!$S:$S,0),COLUMN(A1)),IF(AND(COLUMN(A1)>=17,COUNTIF(Sheet2!$AA:$AA,$A2&$B2)>0),INDEX(Sheet2!$A:$Y,MATCH($A2&$B2,Sheet2!$AA:$AA,0),COLUMN(A1)-14),"")),IF(ROW(A1)<=MAX(Sheet2!$AB:$AB), IF(COLUMN(A1)<=2,INDEX(Sheet2!$A:$Y,MATCH(ROW(A1),Sheet2!$AB:$AB,0),COLUMN(A1)),IF(AND(COLUMN(A1)>2,COLUMN(A1)<17),"",INDEX(Sheet2!$A:$Y,MATCH(ROW(A1),Sheet2!$AB:$AB,0),COLUMN(A1)-14))),""))

お礼

ご報告が遅くなり申し訳ございません。

ご教示頂いた内容で解決しました。
この度はご支援頂き、誠に有難うございました。

補足

早速のご回答ありがとうございました。

出先の為、データが無く検証出来てませんが、サンプルを作成してみたところ
こちらのイメージ通りの結果が表示されました。

明日改めてご報告させて頂きます。
先ずはご回答に対し御礼申し上げます。

回答No.1

MackyNo1

Sheet3の１行目にまとめたい項目を入力しておき、A2セルに以下の数式を入力し、右方向および下方向にオートフィルします。

=IF(COUNTA(Sheet1!$A$2:$A$100)>ROW(A1),IF(ISNUMBER(MATCH(A$1,Sheet1!$1:$1,0)),INDEX(Sheet1!$A$2:$I$100,ROW(A1),MATCH(A$1,Sheet1!$1:$1,0)),""),IF(ISNUMBER(MATCH(A$1,Sheet2!$1:$1,0)),INDEX(Sheet2!$A$2:$I$100,ROW(A1)-9,MATCH(A$1,Sheet2!$1:$1,0)),""))

Sheet2に項目名がありデータ数を超える行には０と表示されますので、シートのオプションでセロ値を表示しない設定などにしてください。

されに複雑な数式となるため、今回は提示しませんでしたが、ゼロの表示は、もちろん数式だけで表示しないように設定できます（2つのシートのデータのカウント数が超える場合は空白文字列を返す数式にしてください）。

補足

早速のご回答ありがとうございました。
私の言葉足らずで誠に申し訳ございません。
教えて頂いた内容を実行したところ、ｓｈｅｅｔ１とｓｈｅｅｔ２の内容が各々ｓｈｅｅｔ３に表示されました。
私の希望としましては、ｓｈｅｅｔ１とｓｈｅｅｔ２に有る、列Ａ（形名）と列Ｂ（製番）でデータのマッチングを行い、ｓｈｅｅｔ１の１４項目とｓｈｅｅｔ２の２３項目のデータを横一直線に表示させたかったのです。

ｓｈｅｅｔ３の表示イメージ
形名、製番、ｓｈｅｅｔ１の１４項目、ｓｈｅｅｔ２の２３項目
Ｘ１　　 ０１ 　１４項目のデータ　　２３項目のデータ

宜しければ再度ご教示頂けませんでしょうか。
何卒宜しくお願い申し上げます。