こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル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ですが、スプレッドシートでも、エクセル以外でもかまいません。

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

投稿日時 - 2019-11-26 15:48:47

QNo.9683394

暇なときに回答ください

質問者が選んだベストアンサー

大変そうですね。

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-11-27 14:16:11

お礼

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

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

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

投稿日時 - 2019-11-28 18:50:29

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

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

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

投稿日時 - 2019-11-26 21:28:57

お礼

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

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

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

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

投稿日時 - 2019-11-28 18:48:37

ANo.3

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

投稿日時 - 2019-11-26 21:24:23

お礼

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

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

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

投稿日時 - 2019-11-28 18:39:58

ANo.2

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2019-11-26 21:06:23

お礼

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

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

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

投稿日時 - 2019-11-28 18:19:58

ANo.1

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

投稿日時 - 2019-11-26 15:57:59

お礼

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

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

投稿日時 - 2019-11-26 17:10:29

あなたにオススメの質問