excelで2つのシートを比較し、新しいシートへ

質問

初めまして。
同じような内容で、いくつか調べさせていただいたのですが、その内容を自分の内容に照らし合わせてトライしてみたのですが、うまくいかなかったため、質問させていただきます。

Excel2010 での関数について質問です。

１：シートが2つあります。（(1)と(2)とします。）
　　両方のシートには1000行くらいのデータが両方入っています。列の項目は一緒です。
２：シート(3)に(1)と(2)のＢ列を比較して、
　　・(1)に合って、(2)にない時は(1)のデータ
　　・(1)にも(2)にもある場合は(2)のデータ
　　・(2)にのみある場合は(2)のデータ　　　をシート(3)に作ります。

＊この際、(1)と(2)の両方にＢの数値があった場合には、さらにＡ列を比較し、Ａ列とＢ列がシート(1)と(2)で完全に一致した場合には(2)のデータ。Ｂの数値が一緒でも、Ａ列のデータが違う場合には(1)も(2)もシート３に表示したいです。
イメージとして画像を添付しました。

シート３に関数を入れるだけで上記条件が表示できればいいのですが、出来なければシート１，２にkeyとなる列を挿入して、それを軸としてシート３にするものなのか。
元々の理解が薄いため、購入した本も今回はどの関数を使用すべきか判断が付きませんでした。

同じような質問がある。とお叱りがあるかもしれませんが、いくつか試してみて出来なかったため質問させていただきます。

今回、急ぎで解決が必要なため、初めて登録し投稿させていただきます。
追加で必要な情報等ありましたら、ご指摘いただけますと幸いです。

宜しくお願い致します。

回答No.5

KURUMITO

配列数式などを含む式でカッコ良く処理する方法もあるでしょうが、データの数が多い場合には計算に負担を掛けない方法として、また、分かりやす方法として作業列を作って対応するのがよいでしょう。作業列が目障りでしたら作業後にその列を選択して右クリックし「非表示」を選択すればよいでしょう。
お示しの表がシート1とシート2にあってA列からE列の1行目に項目名が、2行目から下方にデータが有るとします。
シート1のF2セルには次の式を入力します。

=A2&B2

シート1のE2セルには次の式を入力します。

=IF(B2="","",IF(OR(COUNTIF(Sheet2!B:B,B2)=0,AND(COUNTIF(Sheet2!B:B,B2)>0,COUNTIF(Sheet2!F:F,F2)=0)),MAX(G$1:G1)+1,""))

F2セルとE2セルを選択してから右端下の■をクリックしてそれらの式を下方にドラッグコピーします。
次にシート2ではシート1と同じように2行目から下方にデータが有るとします。
シート2のF2セルには次の式を入力します。

=A2&B2

シート2のG１セルには次の式を入力します。

=MAX(Sheet1!G:G)

シート2のG2セルには次の式を入力します。

=IF(B2="","",IF(COUNTIF(Sheet1!B:B,B2)>=0,MAX(G$1:G1)+1,""))

上の式は　=IF(B2="","",MAX(G$1:G1)+1)　でもよいことになりますね。
F2セルとE2セルを選択してから右端下の■をクリックしてそれらの式を下方にドラッグコピーします。

シート3はお求めのシートで1行目は項目名などを入力するとしてA2セルには次の式を入力してE2セルまで横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)<=MAX(Sheet1!$G:$G),INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)),IF(ROW(A1)<=MAX(Sheet2!$G:$G),INDEX(Sheet2!$A:$E,MATCH(ROW(A1),Sheet2!$G:$G,0),COLUMN(A1)),""))

補足

詳細な説明をありがとうございます。
現在、こちらの説明とにらめっこしながら一つずつ確認させていただいております。
いくつか質問をさせてください。

(1)：数式上の検索列について
　　実際に検索する列はN列（型番：第一優先）とJ列（地域：第二優先）となる場合には
　　上記示していただいたAをNにBをJに変更すればよいということですよね。

検索列を2列追加して、AとBは空欄にしてあります。（ここにアドバイスいただいた数式をもとに変更しながら入力予定。）そして実際の検索列はNとJになります。
検索列AとBを追加して、列はA~EC列まで、また実際の数字は5行目以降にデータが入っています（1000行程度：毎月増えていきます。）

(2)上記アドバイスいただいた数式について
-------------------------------------------------------------------------------
シート1のE2セルには次の式を入力します。
=IF(B2="","",IF(OR(COUNTIF(Sheet2!B:B,B2)=0,AND(COUNTIF(Sheet2!B:B,B2)>0,COUNTIF(Sheet2!F:F,F2)=0)),MAX(G$1:G1)+1,""))
-------------------------------------------------------------------------------
この部分に関しまして、E2ではなくG2に入力ということでよいでしょうか。
その際Sheet1のG1には何も入力は必要ないでしょうか？上記のMax(G$1:G1)+1の部分はSheet1のG1のことを指しているのであれば、何か入力が必要なのか？と思い再度ご質問させていただきました。

Sheet２のG1には=MAX(Sheet1!G:G)　と記載されていましたので、やっぱりいらないのかな・・と
試行錯誤しておりますが、なかなか答えが見つかりません。

まだ先の部分はトライしていませんので、再度ご質問させていただくことになるかと思いますが、どうかもう少しご教授いただけますと幸いです。
一度で、的確な質問ができませんで、お手数をおかけして申し訳ありません。

よろしくお願いいたします。

回答No.4

kagakusuki

同じシート内に重複するデータが存在している場合は、取り敢えずの話として、Sheet (1)のデータに関しては、Sheet (2)に重複するデータが存在していない場合にのみSheet (3)に表示し、Sheet (1)内に重複するデータが存在していてSheet (2)に重複するデータが存在していない場合には、重複していても構わずに全てSheet (3)に表示するものとし、Sheet (2)のデータに関しては、Sheet (1)、Sheet (2)の何れのシート内に重複するデータが存在していても、構わずに全てSheet (3)に表示するものとします。
　又、Sheet (4)のA～D列を作業列として使用するものとします。

　まず、Sheet (4)のA1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(INDEX(Sheet (1)!$B:$B,ROW())),COUNTIFS(Sheet (2)!$A:$A,INDEX(Sheet (1)!$A:$A,ROW()),Sheet (2)!$B:$B,INDEX(Sheet (1)!$B:$B,ROW()))=0),INDEX(Sheet (1)!$B:$B,ROW())&"■"&INDEX(Sheet (1)!$A:$A,ROW()),"")

　次に、Sheet (4)のB1セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet (2)!$B:$B,ROW())),INDEX(Sheet (2)!$B:$B,ROW())&"■"&INDEX(Sheet (2)!$A:$A,ROW()),"")

　次に、Sheet (4)のC1セルに次の関数を入力して下さい。

=IF($A1="","",COUNTIF(A:B,"<"&$A1)+COUNTIF($A$1:$A1,$A1))

　次に、Sheet (4)のD1セルに次の関数を入力して下さい。

=IF($B1="","",COUNTIF(A:B,"<"&$B1)+COUNTIF($A:$A,$B1)+COUNTIF($B$1:$B1,$B1))

　次に、Sheet (4)のA1～D1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet (3)のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet (4)!$C:$D),"",IF(COUNTIF(Sheet (4)!$C:$C,ROWS($2:2)),INDEX(Sheet (1)!A:A,MATCH(ROWS($2:2),Sheet (4)!$C:$C,0)),INDEX(Sheet (2)!A:A,MATCH(ROWS($2:2),Sheet (4)!$D:$D,0))))

　次に、Sheet (3)のA2セルをコピーして、Sheet (3)の表内の2行目以下の全てのセル範囲に貼り付けて下さい。

　以上です。

補足

丁寧な語説明ありがとうございます。
シート１でBが重複している場合は、Aが重複している可能性はありません。
型番が重複していても同じシート内であれば地域が違う。というようになっています。

シート１でBが重複していて、Aが重複していないデータがあった場合で（○、Xとします）、シート２でシート１の○重複していた場合には、シート３にはシート１のXとシート２の○がシート３に表示されてほしいです。

いくつか回答をいただいており、現在一つずつトライさせていただいております。
見慣れない関数もあり、一つずつ分解して何をしようとしているのかを調べながらのため、回答をいただいた後にさらに質問。という形になることもあると思いますが、どうぞよろしくお願いいたします。

回答No.3

kagakusuki

>また、Ｂ列も重複している場合もある。ということを確認しました。

との事ですが、それでは同じシート内に重複するデータが存在している場合には、どのデータを表示して、どのデータは表示しないようにせねばならないのでしょうか？
　B列のみが重複している場合と、A列とB列の双方が重複している場合の、各々に関して御教え願います。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

画像が添付されなかったのでもう一度
>アートフィルターで「○」を抽出
オートフィルターで「○」を抽出

回答No.1

某HN クロメート(Chromate)（@CoalTar）

解像度が低くて画像は良くわかりません。

(1)をA1:C8、(2)をE1:G8と仮定し
E1:G8をA10に貼り付け
A2:C8をA18に貼り付け
データ - 並べ替え 最優先B列の項目名 2番目A列の項目名
D11セル ○
D12セル =IF(AND(B12=B11,A12=A11),"×","○")
下へオートフィル
アートフィルターで「○」を抽出
同一シートでのB列の重なりは抽出されないので注意

お礼

画像が粗く、分かりにくく申し訳ありませんでした。一度消してＵＰしなおそうと思いましたが、すみません。わからず断念しました。

シートの１と２は毎月追加で入力していきますため、それらのデータをコピーしたり張り付けたりはせず、あくまでのデータを入力するシートとして使用する。ということでした。
また、Ｂ列も重複している場合もある。ということを確認しました。

毎月１と２にに追加されていくので、シート３にそれぞれ上記質問に記載した条件に合うような関数がないかと思っております。

説明が足りず、申し訳ありませんでした。

上記アドバイス頂いた方法も別で使用できそうであったため、大変ありがとうございました。