エクセル2003 複数条件で重複を調べる方法

質問

エクセル2003を使用しています。

1000件ほどの物件のデータについて、物件名と価格が一致しているかどうかを調べたいのですが、うまくいきません。

例えば、A列に物件名、B列に価格、C列にも物件名、D列にも価格、というようなデータがあるとします。
A列（物件名）+B列（価格）と、C列（物件名）+D列（価格）のデータを比較して、物件名が一致していて、価格が違うものを探したいと考えています。一致しない場合は、どちらかの価格が誤っているので、修正が必要です。

COUNTIFSや、SUMPRODUCTで、複数条件で比較することは出来るのですが、同じ物件で、価格の違う別の部屋がある場合もFALSEが返ってきてしまうため、価格が誤っているものだけを探すことが出来ません…。。。

具体的には、下記のような式にしました。
=IF(SUMPRODUCT(($A$2:$A$1000=C2)*($B$2:$B$1000=D2)),"TRUE","FALSE")

A列「○○マンション」B列「3000（万円）」、C列「○○マンション」D列「2999（万円）」で一致しないため「FALSE」になる分にはいいのですが、

A列に「○○マンション」の「3000」と、同じ「○○マンション」の別の部屋で「2999」があり、B列、C列にも同じように「○○マンション」の「3000」、「○○マンション」の「2999」がある場合、それぞれの部屋で価格は合っていても、同じ物件名で価格が違うデータがあるため「FALSE」になってしまい、

同じマンションで別の部屋があることはよくあるので「FALSE」も多くなり、価格が誤っているものだけを探すのが難しい状態です。

A列＆B列をE列に入れて、C列＆D列をF列に入れ、E列とF列で、COUNTIFで比較してみても同じです。

部屋番号がわかればいいのですが、A列に部屋番号があって、C列にはない場合もあるので、部屋番号では比較ができません。

同じマンションの別部屋で価格が違う場合を含めずに、価格が誤っているデータだけを抽出する方法はないでしょうか？ちなみにエクセルは2003ですが、スプレッドシートでも、エクセル以外でもかまいません。

分かりにくい部分があれば補足します。よろしくお願いいたします。

回答No.5

nishi6 · ベストアンサー

大変そうですね。

まず。SUMPRODUCT関数が使われているのは、A列とC列が1対1の関係になっていないからだと思われます。
このような案件を扱ったことがありますが、数百名の12ヶ月分の勤務状況の2つのデータ照合でした。データの抜け、二重入力、データの不備等一様の処理ができない状態でした。データの整備から始めました。

まず、この質問の、
＞具体的には、下記のような式にしました。
＞=IF(SUMPRODUCT(($A$2:$A$1000=C2)*($B$2:$B$1000=D2)),"TRUE","FALSE")
が問題でしょう。

SUMPRODUCT関数の返り値が「1以上」でTrueになっています。回答添付図の例で、SUMPRODUCT関数の返り値は色々の値をとります。
　0：C列とD列に一致するA列、B列の組がない
　1：C列とD列に一致するA列、B列の組が1つある
　2以上：C列とD列に一致するA列、B列の組が2つ以上ある

0はFalseで良いとして、
1は部屋番号が不明とすれば、確実に正しいとはいえません。
2以上の場合、1つのデータが2つ以上のデータを参照しているので、これも正しいとはいえません。謝りの可能性が高いかもしれません。

回答添付図では、
　セルF2：=SUMPRODUCT(($A$2:$A$1001=C2)*($B$2:$B$1001=D2))
　セルG2：=IF(F2=0,"対応データなし",IF(F2=1,"OK?","要確認!"))
としています。

SUMPRODUCT関数を使ってのチェックはデータなし以外信用おけないことになります。判定する算式は、「部屋番号がある」前提で、ゆるく作る必要があるでしょう。判定項目がない状態では、どんな算式を使っても正しい判定はできません。

最良の方法は、３つ目のデータ項目「部屋番号」を設けて判定することでしょう。

お礼

回答ありがとうございます。

前の方の回答でもありましたが、部屋の階数なり広さなりの項目を追加すると出来るかもしれませんね！

何か出来そうな気がしてきました。ありがとうございます。

回答No.4

kon555

具体的なデータ状況や作業環境が不明なので何とも言い難いのですが、
『部屋番号がわかればいいのですが、A列に部屋番号があって、C列にはない場合もあるので、部屋番号では比較ができません』
との事ですが、では作業者は何を根拠にして「部屋違い」と「数字の不一致」とを見分けているのでしょうか？

「同じ物件名で価格が違うデータがあるため「FALSE」になってしまう」のは実際のところ正しい挙動ですよね。
さらに「A列に部屋番号があって、C列にはない場合もあるので、部屋番号では比較ができません」とくると、シンプルに考えると作業者としてもそれは数字の誤りとしか認識できないはずです。
逆に言えば作業者の判断基準をエクセル上に落とし込む事ができれば、ご希望の挙動は可能になると思います。

お礼

回答ありがとうございます。

2つのデータは、元は同じものなのですが、掲載するサイトが別々なので、それぞれのサイトの管理システムでデータを入力していて、

Aというサイトに掲載した時は部屋番号がわからず、例えば4Fとだけ入っていて、Bというサイトでは例えば部屋番号が402と入っていたりします。

価格変更があった時に、両方のサイトできちんと変更できているかを確認したいのですが、価格が大きく違わず、どちらかが1Fと102、どちらかが4Fと402であれば、それぞれの組み合わせが同じ部屋ではないかと判断します。同じ階でしたら、他にも広さが同じかで判断したりしますが…とすると、階数や広さを追加すれば絞りこめそうな気もしますね！

回答No.3

imogasi

質問条件が複雑なので参考になるかどうか？だが
２列を考慮して、重複しているデータを見つける、関数の方法
例データ　Ａ－Ｇ列（Ｄ，Ｆ列は空白列
府県 市名 結合文字列
東京都 府中市 3 東京都府中市
東京都 青梅市 1 東京都青梅市
東京都 八王子市 2 東京都八王子市
東京都 東村山市 1 東京都東村山市
千葉県 市川市 3 千葉県市川市
千葉県 千葉市 1 千葉県千葉市
千葉県 柏市 1 千葉県柏市
千葉県 木更津市 1 千葉県木更津市
東京都 八王子市 2 東京都八王子市 重複在り
東京都 八丈島 1 東京都八丈島
東京都 府中市 3 東京都府中市 重複在り
千葉県 市川市 3 千葉県市川市 重複在り
千葉県 市川市 3 千葉県市川市
東京都 府中市 3 東京都府中市
Ｃ２の式　=COUNTIFS(A$2:A$20,A2,B$2:B$20,B2)
Ｅ２の式　=A2&B2
Ｇ２の式　=IF(COUNTIF($E$2:E2,E2)=1,"",IF(COUNTIF($E$2:E2,E2)=2,"重複在り",""))
＝＝＝
方法１
一物一価だとして、
ＶＬＯＯＫＵＰ用の品物と価格の表を作って、違う価格のものでないか調べる方法。
＝＝
方法２
品物列と価格列の結合した列を作り、データーフィルター詳細設定ー「重複するレコードは無視する」にして、出てきた、品物ー価格の（ユニークな（意味はＷＥＢで調べてください））組み合わせを人間が睨んで、間違いの組み合わせを見つけ、その間違いの組み合わせ値でシートを検索し、どこにあるか知る。

お礼

回答ありがとうございます。

すみません、どこからどこまでがどの列になるのかよくわからなかったのですが、式の意味はなんとなくわかりました。

VLOOKUPはあまり使ったことがないので調べてやってみます。

回答No.2

HohoPapa

#1の方のように取り出し、Ａ列の昇順、Ｂの昇順に並べ替え
添付画像のように
C3=IF(AND((A2=A3),(B2<>B3)),"要チェック","")
と埋め、必要数下方向に複写するという解はいかがでしょうか。

お礼

回答ありがとうございます。

A列＆B列、C列＆D列の物件数が同じなら、教えて頂いた方法も可能かと思うのですが、どちらか一方にしか無い物件もあるので難しいかもしれません。

説明が足りなくてすみませんが、ありがとうございます。

回答No.1

oboroxx

A列とB列のペアとC列とD列のペアは、新旧とかという関係性がないのであれば、別シートにA列とB列をコピーして、そして、その下にC列とD列もくっつけてコピーして、並べ替えをすればすぐわかると思います。

お礼

回答ありがとうございます。

新旧などの関係性は無いのですが、件数が1000件と多いので、目視では少し大変かもしれません、、、