COUNTIF関数がうまく行かないとき

質問

Excel 2013です。
37385行14列の表で、二つの列のデータが重複している行を抽出したいとなりました。
二つの列のデータは分類番号のようなもので、ゼロから始まるものもあるので文字列として入力された数字です。
そこで、その二列をCONCATENATE関数で繋いだ数字の並び（N列に配置）をCOUNTIF関数で調べる（O列）ことにしました。
すると、N列の値が異なるセルを同じとみなしてカウントしたようなO列の値がいくつか見られました。
そこで、N列のセルにNUMBERVALUE関数を追加し数値にしたところ、一見うまくいっているようにみえます。
これはなぜ起きたのでしょうか？
また、臨んだ結果が得られていると確認する方法がありますでしょうか？
N列は文字列のときは文字列長0～18までありました。
よろしくお願いします。

回答No.2

chie65536（@chie65535） · ベストアンサー

＞すると、N列の値が異なるセルを同じとみなしてカウントしたようなO列の値がいくつか見られました。

「2つの列を連結したデータを作って、それで重複を調べる場合」に、注意しなければならない事があります。

それは「単純に連結しただけでは、重複してないデータを重複していると誤解してしまう」と言う問題です。

例えば

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
00010　　12660　　0001012660
000101　　2660　　0001012660

という２つのデータです。

AとBは「異なる行」ですが「連結した結果が同一」になってしまっています。

こういう場合は、単純に連結するのではなく「データ中に出現しない文字を、区切り文字として、間に挟む」必要があります。

例えば、データ中に「@」と言う文字が出現しないのであれば

=A1 & "@" & B1

などのように「@を挟んで連結」して、誤認識を防ぎます。

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
00010　　12660　　00010@12660
000101　　2660　　000101@2660

そうすると、上記のように「連結結果が異なる値」になり、誤認識しません。

＞そこで、N列のセルにNUMBERVALUE関数を追加し数値にしたところ

NUMBERVALUE関数で対処するのは「間違い」である可能性が高いです。「偶然、上手くいっているように見える」だけのような気がします。

こういう「連結して重複を調べる場合」は「各データの桁数を揃える」とか「区切り文字を挟む」などの工夫が必要です。

・各データの桁数を揃える必要がある場合

例えば「000100」と「00100」と「0100」と「100」の４つを「同一の値」としたい場合、データの「桁数」を揃える必要があります。

上記の例で、データの最大桁数が「７桁」なのであれば、「000100」と「00100」と「0100」と「100」を「0000100」になる様に加工します。

例えば「=RIGHT("000000” & A1,7)」と言う式で「頭に必要なだけのゼロを補った７桁の文字列」に出来ます。

もし「A列が最大９桁、B列が最大１１桁」なのであれば

=RIGHT("00000000” & A1,9) & RIGHT("0000000000" & B1,11)

と言う式を「N列」に書きます。

この式では「間に区切り文字を入れてない」ですが「桁数を揃えてから連結している」ので、以下のようになり、問題は発生しません。

A　　　　B　　　　桁を揃えたAとBの連結結果
--------------------------------------------------------------
00010　　12660　　00000001000000012660
000101　　2660　　00000010100000002660

・区切り文字を挟む場合

例えば「000100」と「00100」と「0100」と「100」の４つを「異なる値」としたい場合、データの「桁数」を揃えません。「あえて桁数を揃えない」事により「異なるデータとして判断させる」のです。

この場合は、最初の例のように「データに現れない文字」を区切り文字として挟みます。

つまり

=A1 & "@" & B1

という式を「N列」に書きます。

この式では、前述の通り、以下のようになり、問題は発生しません。

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
00010　　12660　　00010@12660
000101　　2660　　000101@2660

以下蛇足。

「000100」と「00100」と「0100」と「100」の４つを「同一の値」としたい場合、データの「桁数」を揃える以外に「どれも数値の100として評価する」と言う方法で「同一の値として扱う」ことが出来ます。

それが「NUMBERVALUE関数を使って数値化する」と言う方法です。

但し「普通にNUMBERVALUE関数を追加しただけではダメ」です。

=NUMBERVALUE(CONCATENATE(A1,B1))

と書いた場合

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
0010　　 12660　　1012660
00010　　12660　　1012660
000101　　2660　　1012660
00010　0012660　　1000012660

１行目と２行目は「同一の値」になって「重複を正常に検知」しますが、やはり「３行目のデータを重複だと誤解」してしまいます。また「１行目、２行目、４行目」を重複だと判断しないといけないのに「４行目は異なるデータ」だと誤解してしまっています。

NUMBERVALUE関数の位置を変えて

=CONCATENATE(NUMBERVALUE(A1),NUMBERVALUE(B1))

と言う式にした場合も、やはり「失敗」します。

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
0010　　 12660　　1012660
00010　　12660　　1012660
000101　　2660　　1012660
00010　0012660　　1012660

今度は「１、２，４行目が同一」と判断されるようになりますが、最初と同じく「異なる筈の３行目が、同一だと誤解」されてしまっています。

正しく処理させるには

=CONCATENATE(NUMBERVALUE(A1),"@",NUMBERVALUE(B1))

のように、間に区切り文字が必要です。これにより

A　　　　B　　　　AとBの連結結果
--------------------------------------------------
0010　　 12660　　10@12660
00010　　12660　　10@12660
000101　　2660　　101@2660
00010　0012660　　10@12660

のようになり、正しく判断できます。

お礼

ありがとうございました。
当初発生していた問題は別の要因があったことが調べてわかりましたが、間に記号を挟んで連結することによって解決できました。現在COUNTIFSが時間がかかりすぎていて、使えないと判断されればこちらの方法で行くことになりそうです。

回答No.5

imogasi

すでに回答者が言っているように単純に２項目をくっつけてはだめだろう。
普通は２列（Ｘ，Ｙ）は独自の属性を背後に持つものであって、定桁に、また三右揃えなどして、桁をそろえたうえで結合し比較しないとならないと思う。
各列の値比較などもＥＸＡＣＴ関数なども活用して、注意深く比較すべきでしょう。
書式設定されているセルの「見た目」も単純に考えては、しくじる場合があるように思う。
関数は、扱う（問題にする）のは「セルの値」だけですよ。

お礼

ありがとうございます。

回答No.4

bunjii

>二つの列のデータは分類番号のようなもので、ゼロから始まるものもあるので文字列として入力された数字です。
>そこで、その二列をCONCATENATE関数で繋いだ数字の並び（N列に配置）をCOUNTIF関数で調べる（O列）ことにしました。
Excel 2013であればCOUNTIFS関数で二つの列に対してAND条件のカウントができますので二つの列を連結する必要はありません。
例えばA列とB列に分類番号が入力されているとして次の数式で重複の有無が検出できます。
O2=COUNTIFS(A:A,A2,B:B,B2)

>そこで、N列のセルにNUMBERVALUE関数を追加し数値にしたところ、一見うまくいっているようにみえます。
分類番号には","や"."が含まれているのであればNUMBERVALUE関数で取り除いた数字のみの文字列に置き換えできますがそのような配慮は重複条件が増えるだけで逆効果になります。（入力ミスによる分類番号のチェックであれば有効）

>また、臨んだ結果が得られていると確認する方法がありますでしょうか？
>N列は文字列のときは文字列長0～18までありました。
「臨んだ」は「望んだ」の誤変換ですよね？
行番号が必要なのでは？
COUNTIF関数やCOUNTIFS関数は同じ条件の個数を求めるだけなので行番号に繋がりません。
INDEX関数とLARGE関数、ROW関数を組み合わせることで重複する行番号をもろめられます。
具体的な数式は模擬データを提示して頂ければ提示できます。

お礼

ありがとうございます。COUNTIFS関数を使ってみます。

回答No.3

tsubu-yuki

コンキャティネイト関数・・難しいものをお使いですね（汗）。

大筋、２番さんが仰る通りで間違いないと思います。
とりあえず、
> これはなぜ起きたのでしょうか？
単純にお考え下さいね。例えば以下、
　01　&　23　⇒　0123
　012　&　3　⇒　0123
プロセスは別ですが、結果は同一ですね。
つまり、ごくごく単純に、そういう事です。
コレは、NUMBERVALUE関数に逃げても結果は同一である・・
というのは（上の例を見れば）想像に難くないことだと思います。
どちらも結果は「123」で同一ですね。
なので「うまくいっているようにみえるだけ」です。
> 臨んだ結果が得られていると確認する方法が
確認するまでも無く、ダメだと思いますよ。

なので、回避策は単純に「両列の間に何か入れてやる」です。
「＠」でも良いでしょうし、「－」でも良いでしょう。
が、何かあった時に誰かがフッと見て
「何かの演算？」と勘違いしないようにした方が良いような気がするので
私なら「＿」を使おうかな、というところですね。
で、元のデータが文字列であることを考慮して、
念のためにTRIM関数を使って余分な空白を取り除いておきます。
つまり、例えば
　　式：=TRIM(A1&"_"&B1)
　　　　=TRIM(CONCATENATE(A1,"_",B1))
くらいですかね。
もっとしっかりやるなら
　　式：=TRIM(A1)&"_"&TRIM(B1)
ですか。
「空白も含めて比較したい」ならTRIM関数は不要です。

・・・とおもったのですが、よく見ると2013をお使いですね。
もっと単純にCOUNTIFS関数で解決できそうです。
例えば検査する列をＡ列とＢ列として、
検査結果をＯ列に表示したい場合は
　Ｏ１セル：=COUNTIFS($A:$A,A1,$B:$B,B1)
として、必要分フィル。
なんとなくですが、コレで一発なような気がします。

お礼

ありがとうございます。やり方の問題点がわかりました。

回答No.1

noname#232800

２つのセルが同じ値の場合　true、違ってる場合Fales

で関数を作る。comp_data( cell1 as long,cell2 as long)

0200 0210 Fales
0311 0311 True
0311 0550 Fales

お礼

ありがとうございます。
書き方が悪くてすみませんでした。二つの列の値が共通の行を調べたいという趣旨でした。