エクセル、別シート間での重複チェックで空白を一致させない方法。

マスタシートと別シートがあります。

別シートに会社名などを入力したときに、マスタと1つでも重複している項目があれば、
TRUEで返すように、重複がなければFALESになるようにしたいのですが、
1つも重複していなくても、空白があれば一致と認識されて、TRUEが返ってしまいます。

画像例）下のシート4行目の「重複してない会社」はどの項目とも重複していないに
TRUEになってしまうが、空白を一致とみなさないでFALESを返したい。

今入れている関数は、
=OR(B2=マスタ!B:B,C2=マスタ!C:C,Sheet2!D2=マスタ!D:D,Sheet2!E2=マスタ!E:E,Sheet2!F2=マスタ!F:F)


3万行以上あるデータで今はsumproductを使用しているのですが、
少しでも軽くなれば良いなと思っています。
(エクセルで扱うデータ量ではないのは承知なのですが…。)


どなたかお知恵を貸して頂ければ幸いです。
宜しくお願い致します。

No.4 回答者： CoalTar 回答日時： 2016/05/28 12:30

どこが重複項目かも判断してA2セルに

=IF(B2<>"",ISNUMBER(MATCH(B2,マスタ!B:B,0))*10000)
+IF(C2<>"",ISNUMBER(MATCH(C2,マスタ!C:C,0))*1000)
+IF(D2<>"",ISNUMBER(MATCH(D2,マスタ!D:D,0))*100)
+IF(E2<>"",ISNUMBER(MATCH(E2,マスタ!E:E,0))*10)
+IF(F2<>"",ISNUMBER(MATCH(F2,マスタ!F:F,0)))

で重複しているマスタの行番号を返す
(複数項目に重複が及ぶ場合は、左の項目優先で)
=IF(A2=0,"",MATCH(INDEX(B2:F2,5-INT(LOG(A2,10))),INDEX(マスタ!B:F,,5-INT(LOG(A2,10))),0))

余談ですが
>=OR(B2=マスタ!B:B,C2=マスタ!C:C,Sheet2!D2=マスタ!D:D,Sheet2!E2=マスタ!E:E,Sheet2!F2=マスタ!F:F)
は Sheet2!A2に入力しているとして
=OR(B2=マスタ!B2,C2=マスタ!C2,D2=マスタ!D2,E2=マスタ!E2,F2=マスタ!F2)
と同じ計算になります。

No.3 回答者： WindFaller 回答日時： 2016/05/28 11:32

こんな数式ではどうでしょうか？

配列数式ですと、そのセルのメモリの計算が積になってしまいますから、1セルで16バイト+文字数×2バイトとしても、一度に使用すると、Excelの個々の機能で与えられているメモリ容量を遥かに越えてしまうのではないでしょうか。

A2において、フィルハンドル(＋）をダブルクリックすれば、数式がコピーされるはずです。(一応、横はF列までにしました）

=SUM(COUNTIF($B$1:B2,B2),COUNTIF($C$1:C2,C2),COUNTIF($D$1:D2,D2),COUNTIF($E$1:E2,E2),COUNTIF($F$1:F2,F2))>COUNTA(B2:F2)

これは、後に出てくる方にTRUEが出る仕組みになっています。
かならず、2行目からの始まりにしてください。1行目のセルをダミーにしています。

No.2 回答者： ママチャリ 回答日時： 2016/05/28 06:11

No1 くんこばさんの云うとおり、一項目毎にチェックした方が早そうです。

項目毎に「会社名の重複結果」「TELの重複結果」…をそれぞれ設けた方が、重複箇所がわかるので、更に便利ですよ。
どの関数が早いかは、やってみないと分からないのですが、感覚的にはMatchですかね。

No.1 回答者： くんこば 回答日時： 2016/05/28 00:21

この関数では、会社名以外にも電話番号やFAX番号でも重複チェックしてるんですね？どれかがヒットすれば重複ありと。

とりあえず、会社名の重複チェックだけに絞って試してみては？
あと重複チェックですが、Vlookup関数やMatch関数、CountIf関数など、他にも関数はいろいろあると思いますよ？