エクセルの２つの表を一つにまとめるには

質問

エクセルで作成した、内容の異なる表を１つの表にまとめることは可能でしょうか。
たとえば、それぞれ作成した表の列に「電話番号」の欄を設けて、
その「電話番号」が同じな順番に並びかえて２つの表を１つの表にまとめる場合です。

回答No.6

tom04 · ベストアンサー

No.3・4・5です！
ほんとうに何度もごめんなさい。
たぶん今回で最後になると思います。

前回のように難しく考える必要はありませんでした。
Sheet1のデータがSheet2になくても普通に性別と趣味の欄だけが空白になる方法で、
Sheet1の作業列は不要ですので削除してください。
（Sheet2の作業列は前回の数式そのままが必要です）

前回の画像の表そのままで
Sheet3のA2セルを
=IF(COUNTA(Sheet1!$A$2:$A$1000)>=ROW(A1),Sheet1!A2,"")
としてB2セルまでオートフィルでコピー

C2セルを
=IF(COUNTA(Sheet1!$C$2:$C$1000)>=ROW(A1),Sheet1!C2,IF(COUNTA(Sheet1!$C$2:$C$1000)+COUNT(Sheet2!$D$2:$D$1000)>=ROW(A1),INDEX(Sheet2!$C$2:$C$1000,SMALL(Sheet2!$D$2:$D$1000,ROW(A1)-COUNTA(Sheet1!$C$2:$C$1000))),""))
という数式に変更してください。

D2・E2は前回のままでOKかと思います。

これでA2～E2セルを範囲指定し、E2セルのフィルハンドルで下へコピーすれば大丈夫だと思います。
尚、数式はSheet1・2とも1000行まで対応できるようにしていますので
後からデータが増えてもSheet3に反映されるはずです。

親の仇のように、「これでもかっ！」というくらい顔を出してしまいました。

ほんとうにごめんなさいね。m(__)m

回答No.7

KURUMITO

解答Ｎｏ２です。
お示しの図を参考にして解答２の式を変形すれば次のようになるでしょう。
ただし、お示しのシート３のまとめの表はあまりのも不自然です。通常はＡ列が２つのシートの共通の項目にして、すなわち電話番号にして右の列には氏名、住所、性別、趣味などとすることです。後々の操作を考えるならばＡ列はデータの最後の行まで詰まっていることが理想です。
ところで、シート１およびシート２での電話番号は同じシート内では重複する電話番号は無いとします。
そこで例えばシート１では１行目に項目名がありＣ列には電話番号の項目名がありＣ列までの表とします。シート２ではＣ１セルに電話番号と入力されＣ列までの表であるとします。
まとめの表をシート３に作るとします。
初めにシート１のＣ列をコピーしてシート３のＡ１セルに貼り付けます。その後にシート２のＣ２セルから下方をコピーしてシート３のＡ列のデータの後に貼り付けます。
その後にＡ列を選択してからエクセル２００７でしたら「データ」タブで「重複の削除」を選択して重複の電話番号がない状態にします。
エクセル２００３でしたらＡ列を選択してから「データ」メニューから「フィルタ」さらに「フィルタオプションの設定」と進んで、表示される画面で「重複するレコードは無視する」にチェックをしてＯＫします。この操作によって重複のない形での電話番号がＡ列に並ぶことになります。
その後にＢ１セルから右の列にはシート１での項目名、ついで、シート２での項目名を必要なものだけ貼り付けます。例えばＢ１セルには氏名、Ｃ１セルには住所、Ｄ１セルには性別、Ｅ１セルには趣味と入力します。
最後にＢ２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet1!$C:$C,$A2)=0,COUNTIF(Sheet1!$1:$1,B$1)=0),"",INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$C:$C,0),MATCH(B$1,Sheet1!$1:$1,0)))&IF(OR(COUNTIF(Sheet2!$C:$C,$A2)=0,COUNTIF(Sheet2!$1:$1,B$1)=0),"",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2!$C:$C,0),MATCH(B$1,Sheet2!$1:$1,0)))
この式で
OR(COUNTIF(Sheet1!$C:$C,$A2)=0,COUNTIF(Sheet1!$1:$1,B$1)=0),"",の意味はシート１のＣ列でＡ２セルにある電話番号が無い場合、および、シート１でＢ１セルにある項目名が無い場合には、すなわち氏名の文字が無い場合にはセルの値は空にしなさいを意味します。
INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$C:$C,0),MATCH(B$1,Sheet1!$1:$1,0))の意味はシート１のＡ列からＣ列の範囲で、Ｃ列でＡ２セルにある電話番号に一致する行、Ｂ１セルの値に一致する項目のある列の値を求めることです。
同様のことをシート２でも行います。氏名などの場合にはシート１のデータが表示されるように、また性別などの場合にはシート２のデータが表示されるように二つの式を＆で結びつけています。
なお、ＶＬＯＯＫＵＰ関数を使って対応しようとしていますがその関数ではできません。VLOOKUP関数は範囲の左端の列を検索して
検索値を含む行にある指定列番号のセルの値を取り出すための関数です。
郵便番号を検索値とする場合には一番左端に郵便番号の列が無ければなりません。お示しのような場合には郵便番号はＣ列にありますのでＡ，Ｂなどの列のデータを取り出すことはできません。
お示しのVLOOKUP(D1,A!$A$1:$D$85,4,FALSE)の式はＤ１セルの値をＡシートのＡ１セルからＡ８５セルの範囲で検索し、４列目すなわちＤ列の値を取り出すことを意味しています。仮に郵便番号がＤ１セルにあるとしたら、その値をＡシートのＡ列で検索できなければできません。すなわちＡシートのＡ列に郵便番号が無ければなりません。

回答No.5

tom04

ほんとぉ～～～！っに！ごめんなさい。
No.3・4です！

画像を見てやっと質問の内容が理解できた感じです。

今までの回答は無視してください。

質問の画像ではSheet1のデータは必ずSheet2にあるようなので
配列数式にする必要なないと思います。
（と言うことはSheet1の作業用の列は不要になります）
万一、Sheet1にSheet2のデータにないものがあれば配列数式にしなくてはならないような気がします。
しかし、数式を作った後気が付いたのでとりあえずそのままの数式を書かせていただきます。

↓の画像でSheet1の作業列D2は
=IF(C2="","",IF(COUNTIF(Sheet2!$C$2:$C$1000,C2),ROW(A1),""))
としてオートフィルでコピー

Sheet2の作業列D2は
=IF(C2="","",IF(COUNTIF(Sheet1!$C$2:$C$1000,Sheet2!C2),"",ROW(A1)))
として同じくオートフィルでコピー

そして、Sheet3のA2セルは
=IF(ISERROR(INDEX(Sheet1!A$2:A$1000,MATCH($C2,Sheet1!$C$2:$C$1000,0))),"",INDEX(Sheet1!A$2:A$1000,MATCH($C2,Sheet1!$C$2:$C$1000,0)))
としてB2セルまでオートフィルでコピー

C2セルは
=IF(COUNTA(Sheet1!$C$2:$C$1000)>=ROW(A1),INDEX(Sheet1!$C$2:$C$1000,SMALL(Sheet1!$D$2:$D$1000,ROW(A1))),IF(COUNTA(Sheet1!$C$2:$C$1000)+COUNT(Sheet2!$D$2:$D$1000)>=ROW(A1),INDEX(Sheet2!$C$2:$C$1000,SMALL(Sheet2!$D$2:$D$1000,ROW(A1)-COUNTA(Sheet1!$C$2:$C$1000))),""))

D2セルは
=IF(ISERROR(INDEX(Sheet2!A$2:A$1000,MATCH($C2,Sheet2!$C$2:$C$1000,0))),"",INDEX(Sheet2!A$2:A$1000,MATCH($C2,Sheet2!$C$2:$C$1000,0)))
としてE2セルまでコピー

最後にA2～E2セルを範囲指定し、E2セルのフィルハンドルで
下へコピーすると画像のような感じになります。

今回は希望に近い形になったでしょうか？

尚、前回までのアップした画像は大きく外しておりましたので
削除させていただきます。
どうも何度も何度もごめんなさいね。m(__)m

回答No.4

tom04

No.3です！
たびたびごめんなさいね。

前回は的外れみたいで失礼しました。

もう1回↓のように表を作ってみました。
今回は表1・表2はSheet1にあり、それをSheet2に表示させるようにしてみました。

表1・表2の作業列は前回の回答同様の数式にしています。

Sheet2のA2セル（配列数式になりますので前回同様の操作をしてください）に
=IF(COUNT(Sheet1!$A$2:$A$100)>=ROW(A1),INDEX(Sheet1!$B$2:$B$100,SMALL(Sheet1!$A$2:$A$100,ROW(A1))),IF(COUNTA(Sheet1!$B$2:$B$100)>=ROW(A1),INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100="",ROW($A$1:$A$99)),ROW(A1)-COUNT(Sheet1!$A$2:$A$100))),IF(COUNTA(Sheet1!$B$2:$B$100)+COUNT(Sheet1!$F$2:$F$100)>=ROW(A1),INDEX(Sheet1!$G$2:$G$100,SMALL(Sheet1!$F$2:$F$100,ROW(A1)-COUNTA(Sheet1!$B$2:$B$100))),"")))

という数式を入れ　Shift＋Ctrl＋Enter

氏名のB2セル（配列数式ではありません）に
=IF(COUNTA(Sheet1!$B$2:$B$100)>=ROW(A1),VLOOKUP($A2,Sheet1!$B$2:$D$100,COLUMN(B1),0),"")
という数式を入れ、隣のC2セルまでコピー

性別のD2セルに
=IF(ISERROR(VLOOKUP($A2,Sheet1!$G$2:$I$100,COLUMN(B1),0)),"",VLOOKUP($A2,Sheet1!$G$2:$I$100,COLUMN(B1),0))
という数式を入れ、となりのE2セルまでコピー

最後にA2～E2セルを範囲指定し、E2セルのフィルハンドルで
下へコピーするとSheet2のような表になります。

こんな感じが希望だったのでしょうか？

以上、お役に立てれば幸いですが、
これも的外れであったり、他の良い方法があれば
読み流してくださいね。

何度も失礼しました。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

tom04

こんばんは！
解釈が違っていたらごめんなさい。

表1と表2の電話番号データの重複をまず、表示させ
表1と表2の残りのデータを続けて表示させれば良い訳ですかね？

一応そういうことだとしての回答になります。

色々方法はあるかと思いますが、
一例としてです。

↓の画像で説明させていただくと
表1・表2とも作業列を使わせてもらいます。

表1のA2セルは
=IF(B2="","",IF(COUNTIF($G$2:$G$100,B2),ROW(A1),""))
としてオートフィルで下へコピーします
（今回は100行まで対応できる数式にしていますので、100行目くらいまでコピーしてもOKです）

表2のF2セルに
=IF(G2="","",IF(COUNTIF($B$2:$B$100,G2),"",ROW(A1)))
として同じくオートフィルで下へコピー

結果を表示させたいセル、K2セルにかなり長い数式になりますが、

=IF(COUNT($A$2:$A$100)>=ROW(A1),INDEX($B$2:$B$100,SMALL($A$2:$A$100,ROW(A1))),IF(COUNTA($B$2:$B$100)>=ROW(A1),INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100="",ROW($A$1:$A$99)),ROW(A1)-COUNT($A$2:$A$100))),IF(COUNTA($B$2:$B$100)+COUNT($F$2:$F$100)>=ROW(A1),INDEX($G$2:$G$100,SMALL($F$2:$F$100,ROW(A1)-COUNTA($B$2:$B$100))),"")))

とし、この列だけは配列数式になってしまいますので、
この画面から数式をコピー＆ペーストで貼り付けただけではエラーになると思いますので、
貼り付け後、F2キーを押すか、数式バー内で一度クリックし、編集可能にします。
そして、Shift＋Ctrl＋Enterキーで確定します。
これで数式の前後に｛　｝マークが入り配列数式になります。

そして、L2セル（ここは配列数式ではありません！）に
=IF($K2="","",IF(COUNTA($B$2:$B$100)>=ROW(A1),VLOOKUP($K2,$B$2:$D$100,COLUMN(B1),0),VLOOKUP($K2,$G$2:$I$100,COLUMN(B1),0)))
とし、オートフィルでM2セルまで数式をコピーします。

最後にK2～M2セルを範囲指定し、M2セルのフィルハンドルで下へコピーすると画像のような感じになります。

以上、参考になれば幸いですが、
的外れであったり、他に簡単で良い方法があれば読み流してくださいね。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

丁寧な回答ありがとうございました。
画像を添付することができることを知らなくて、質問の意味を
正確に伝えることができませんでした、申し訳けありません。
　とりあえず、伝わるかわかりませんが、文字のみで表現しますと、「表１」と「表２」の項目が異なる表を「電話番号」で「識別」させ
表を１つにしたいということです。
例えば、「表１」の項目が「電話番号、氏名、住所」、「表２」の項目が「電話番号、性別、趣味」だった場合。
表１と表２をまとめて、「表３」として「電話番号、氏名、住所、性別、趣味」の４項目からなる表を作成したいという意味です。

回答No.2

KURUMITO

シート１およびシート２での電話番号は同じシート内では重複する電話番号は無いとします。
そこで例えばシート１では１行目に項目名がありＣ列には電話番号の項目名があるとしてＤ列までの表であるとします。シート２ではＤ１セルに電話番号と入力されＥ列までの表であるとします。
まとめの表をシート３に作るとします。
初めにシート１のＣ列をコピーしてシート３のＡ１セルに貼り付けます。その後にシート２のＤ２セルから下方をコピーしてシート３のＡ列のデータの後に貼り付けます。
その後にＡ列を選択してからエクセル２００７でしたら「データ」タブで「重複の削除」を選択して重複の電話番号がない状態にします。
エクセル２００３でしたらＡ列を選択してから「データ」メニューから「フィルタ」さらに「フィルタオプションの設定」と進んで、表示される画面で「重複するレコードは無視する」にチェックをしてＯＫします。
その後にＢ１セルから右の列にはシート１での項目名、ついで、シート２での項目名を必要なものだけ貼り付けます。
最後にＢ２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet1!$C:$C,$A2)=0,COUNTIF(Sheet1!$1:$1,B$1)=0),"",INDEX(Sheet1!$A:$D,MATCH($A2,Sheet1!$C:$C,0),MATCH(B$1,Sheet1!$1:$1,0)))&IF(OR(COUNTIF(Sheet2!$D:$D,$A2)=0,COUNTIF(Sheet2!$1:$1,B$1)=0),"",INDEX(Sheet2!$A:$E,MATCH($A2,Sheet2!$D:$D,0),MATCH(B$1,Sheet2!$1:$1,0)))
これで電話番号ごとにシート１とシート２の表がまとめられて表示されます。

補足

すみません
何度も、丁寧な回答をいただきありがとうございます。
かなり私の質問の意図が通じてきたように思います。
ようやく、画像添付の方法がわかったので、画像を添付しましたので、申し訳けありませんが、もう一度回答をお願いできないでしょうか、
よろしくお願いします。

「VLOOKUP」関数を用いて以下のとおり自分なりに考えてみました。
例えば「VLOOKUP(D1,A!$A$1:$D$85,4,FALSE)」（セル名、シート名は無視してください）では
表２の１行（例えば、性別の行のみ）を表３くっつけることはできましたが、２行とも（性別と趣味）を表３にまとめることができません。
「列番号」の「4」に問題があるように思っております。
質問としましては、表１、２を表３のような形にまとめられることができれば、良いです、あとは、エクセル関数に不慣れなため手作業で修正しても良いと思っております。
もし、御提示されております数式を用いなとできない場合は、お手数ですが、関数の意味が不明なものが多くて理解できないため、関数の意味まで御教示いただけないでしょうか。よろしくお願いいたします。

回答No.1

suzukikun

VLOOKUPで検索とかヘルプを見てみてください。