Excelでのシート間でのデータ統合方法

質問

Sheet１とSheet2にはたくさんのデータが入っています。
これをSheet3のように統合してわかりやすくしたいのです。
手入力ですとあまりに時間がかかりすぎるために、
関数などで出来る方法を教えてください。

回答No.2

imogasi · ベストアンサー

商品ＩＤがＳｈｅｅｔ１とＳｈｅｅｔ２で同じものばかりなら、直接関数ＶＬＯＯＫＵＰでやるのも良いが、まずＳｈｅｅｔ１とＳｈｅｅｔ２の商品ＩＤの出現ＩＤ一覧を作る必要がある。
Ｓｈｅｅｔ２のＩＤ列をＳｈｅｅｔ１のあとに貼り付けて（＊）
Ｓｈｅｅｔ３において
データーフィルターフィルタオプションの設定ー
指定した範囲をＯＮ
リスト範囲　Ｓｈｅｅｔ１!A1:A7(データ数で変わる）
検索条件範囲　空白
抽出範囲　$A1:$Ａ$13(余分目に指定しておく）
重複するレコードは無視する（ここが肝心）にチェック
ＯＫ
で
商品ＩＤ
111
112
115
113
のようなＩＤの一覧が出来る。
基データ
Ｓｈｅｅｔ１
商品ＩＤ 購入日
111 1月1日
112 1月5日
115 1月29日
Ｓｈｅｅｔ２
商品ＩＤ 購入者
111 田中
113 山田
115 鈴木
と仮定して。
ーー
Ａ列で貼り付けた部分を抹消する（上記＊）。
ーー
Ｓｈｅｅｔ３のB2の式を　=VLOOKUP($A2,Sheet1!$A$1:$B$4,2,FALSE)
Ｃ２の式を　=VLOOKUP($A2,Sheet2!$A$1:$B$4,2,FALSE)
結果
商品ＩＤ 購入日 購入者
111 1月1日 田中
112 1月5日 #N/A
115 1月29日 鈴木
113 #N/A 山田
#N/Aがイヤなら式を
購入者の列の例では
=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$1:$B$4,2,FALSE)),"",VLOOKUP($A2,Sheet2!$A$1:$B$4,2,FALSE))
のようにする。

お礼

ＩＤの整理からすべてうまくいきました。数式ををいじることで他の項目も追加で来て思っていた結果が得られました。ありがとうございました。

回答No.5

kagakusuki

回答番号ANo.3です。

　Sheet3のA2セルに入力する数式を次の様な数式としますと、作業列が不要となり、Sheet4を設ける必要が無くなります。

=IF(MAX(A$1:A1)>=MAX(Sheet1!$A:$A,Sheet2!$A:$A),"",IF(ROWS($2:2)=1,MIN(Sheet1!$A:$A,Sheet2!$A:$A),MIN(IF(A1>=MAX(Sheet1!$A:$A),9E+99,SMALL(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,"<="&A1)+1)),IF(A1>=MAX(Sheet2!$A:$A),9E+99,SMALL(Sheet2!$A:$A,COUNTIF(Sheet2!$A:$A,"<="&A1)+1)))))

　Sheet3のB2セルとC3セルに入力する関数は、ANo.3と同様です。

お礼

ご丁寧にありがとうございます。
コピーペーストしてもなぜか数字が表示されず断念しました。

回答No.4

KURUMITO

VLOOKUP関数を使うことで容易に対応できるのですが問題はIDを自動的に整理し表示させることですね。
ここではIDはお示しのように必ず数値として入力している場合ですが次のようにすればよいでしょう。
例えばシート3のE列を作業列としてE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)<=COUNT(Sheet1!A:A),SMALL(Sheet1!A:A,ROW(A1)),IF(ROW(A1)<=(COUNT(Sheet1!A:A)+COUNT(Sheet2!A:A)),SMALL(Sheet2!A:A,ROW(A1)-COUNT(Sheet1!A:A)),""))

お求めの表ですがシート3のA1セルには商品ID、B1セルには購入日、C1セルには購入者の文字列があるとします。
A2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)=1,MIN(E:E),IF(MAX(A$1:A1)=MAX(E:E),"",SMALL(E:E,COUNTIF(E:E,"<="&A1)+1)))

B2セルには次の式を入力して下方にオートフィルドラッグします。なお、B列のセルの表示形式は日付を設定します。

=IF(OR(A2="",COUNTIF(Sheet1!A:A,A2)=0),"",VLOOKUP(A2,Sheet1!A:B,2,FALSE))

C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A2="",COUNTIF(Sheet2!A:A,A2)=0),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE))

お礼

ご丁寧にありがとうございます。
コピーペーストしてもなぜか数字が表示されず断念しました。

回答No.3

kagakusuki

もし、商品IDが数字のみで、アルファベット等の文字が含まれてはいない場合には、以下の様な方法があります。
　まず、適当な空きシート（ここでは仮にSheet4を使用するものとします）のA2セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),INDEX(Sheet1!$A:$A,ROW()),"")

　次に、Sheet4のB2セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(INDEX(Sheet2!$A:$A,ROW())),COUNTIF($A:A,INDEX(Sheet2!$A:$A,ROW()))+COUNTIF(B$1:B1,INDEX(Sheet2!$A:$A,ROW()))=0),INDEX(Sheet2!$A:$A,ROW()),"")

　次に、Sheet4のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$B),"",SMALL(Sheet4!$A:$B,ROWS($2:2)))

　次に、Sheet3のB2セルに次の数式を入力して下さい。（Sheet3のB2セルの書式はSheet1のB2セルと同じ書式として下さい）

=IF(OR($A2="",COUNTIF(Sheet1!$A:$A,$A2)=0),"",VLOOKUP($A2,Sheet1!$A:$B,2,FALSE))

　次に、Sheet3のC2セルに次の数式を入力して下さい。

=IF(OR($A2="",COUNTIF(Sheet2!$A:$A,$A2)=0),"",VLOOKUP($A2,Sheet2!$A:$B,2,FALSE))

　次に、Sheet3のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、統合された表が自動的に作成されます。

回答No.1

hallo-2007

Vlookup関数はこういったときに使います。
Sheet3のB2セルに
=Vlookup(A2,Sheet1!A:B,2,False)
Sheet1のA:B列で、左端（つまりA列）を検索して
合致した行の　２番目の列
といった意味です。

C2セルも同様で
=Vlookup(A2,Sheet2!A:B,2,False)
で行けるはずです。

お礼

Vlookup関数勉強になりました。ありがとうございます。