Excel 重複データを含むデータ数のカウント

質問

Excel2003を使用しています。

重複データを1つとして数え、セルK12：K473の範囲(空白セルあり)にあるデータの個数を求める数式を過去の質問を参考にして、下記のように入力しました。

【数式1】
=COUNT(INDEX(1/(MATCH(K12:K473,K12:K473,0)=ROW(K1:K462)),0))

→結果は44でした。

空白セルを除いた状態のK列のデータをM列にコピペし、これも過去の質問を参考にして、下記のように入力しました。

【数式2】
=SUMPRODUCT((MATCH(M12:M84,M12:M84,0)=ROW(M1:M73))*1)

→結果は43でした。

職場で使用しているシステムへの入力漏れがないかをチェックしたくて、上記のようなことをしたのですが、入力した件数をシステムで検索した結果は43件でした。
最初は【数式1】で得た結果とシステムでの検索結果が合わないので、ひとつひとつ確認したのですが、入力漏れもなかったので、試しに【数式2】で調べてみると、システムでの検索結果と合致しました。

【数式2】では空白セルを含む場合はエラーが出るようでしたので、【数式1】でチェックしようとしたのですが、正しい結果が得られなかったのは、私の入力した【数式1】が間違っているのでしょうか？

回答No.2

MackyNo1 · ベストアンサー

>空白セルを除いた状態のK列のデータをM列にコピペし、

どのような操作で空白セルをのぞいたのでしょうか？

元データがエクスポートしたデータ数式や得られた値を「値」に変換した場合は、空白セルが文字数０の文字列となることがあります。
このとき、オートフィルタで「空白セル以外」を選択すると、本当の空白以外の空白文字列も選択対象になる（実際の空白セルと同じとみなされる）ので、実際に文字列が入力されたデータだけを抽出できます。

しかし、元データには空白文字列が混入している場合は、実際の値よりも1大きい値が表示されることになるわけです。

このような場合は、その列を選択して。「データ」「区切り位置」で「完了」すると、空白文字列を本当の空白セルに変更できます。

これを確認するには、どこかのセルに「=TYPE(空白に見えるセル)」の数式を入力するとで２が返ります。

お礼

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

> どのような操作で空白セルをのぞいたのでしょうか？

オートフィルタで「空白以外のセル」を指定して、空白セルを除いた状態にしました。

MackyNo1 さんの回答にもありましたように、K列のデータがIF式(G列が空欄じゃなかったら、B列のデータを表示、空欄だったら空欄)の結果なので、K列に複数ある空欄も1件とカウントされ、【数式1】では実際の件数より1大きい結果になったのではないかと解釈しています。

回答No.5

tom04

No.3です！
たびたびごめんなさい。

>最初に現れた空欄に対して“1”が表示されていましたので・・・

とありましたので再び顔を出してしまいました。

間違っていたらごめんなさい。

もしかしてそのセルは見た目は「空白」であっても、
「スペース」が一つでも入力してあれば、空白ではないと判断されます。

今一度そのセルをアクティブにして、Deleteキーを押してみてください。
もし、それで数値が表示されなくなったのであれば、
「スペース」が入力されていたと考えられます。

以上、余計なお世話かもしれませんが、
ちょっと気になったのでお邪魔しました。m(__)m

お礼

再度のアドバイスありがとうございます。

>>最初に現れた空欄に対して“1”が表示されていましたので・・・

↑このときにも今回アドバイスいただいたように、そのセルをアクティブにしてDeleteキーを押したりして、いろいろ試してみましたが、スペースは入力されているということではなさそうです。

IF式で最初に『""』の結果が出たセルに“1”が表示されるようですので、文字数0のひとつのデータとして扱われて、カウントされているのかな？と思ったわけです。

回答No.4

imogasi

私はもっと簡単な式（意味が判りやすい式）で検証をしたほうが良いと思う。
質問の式は質問者が自分が言っているように、何処かから探してきた式で、すばらしいと思う式だが、エクセルの熟達者が経験のすえ見つけた、高等な式で、自分が考え付くような式ではない。
当然こういう合わない場合など原因追求に苦労する。
ただ私には、
第1、２式でなぜMATCHとROWを使う必要があるのか理解できてないが。
参考　重複をのぞいて個数
http://oshiete1.goo.ne.jp/qa4454805.html
ただし空白行があると旨く行かない式も在る。
ーー
そしてエラーや不一致の原因究明は
読者の側にデータがない
試行錯誤できない
という事で非常に難しく、このコーナーに向かない課題と思う。
ーー
例えば、作業列を使うと式が簡単になる場合がある。
この場合は一例で
=IF(COUNTIF($A$1:A1,A1)=1,1,"")
を入れて下方向に式を複写して、その式を入れた列の合計が件数。
空白行が２つ以上合っても狂わない。
これで何処（の行）がおかしいかチェックで出来るだろう。
ーー
=IF(A1="","",1/COUNTIF($A$1:$A$10,A1))と入れて下方向に式を複写して、その列の合計を出すと件数。
空白行があることを前提にしている。

お礼

アドバイスありがとうございます。

参考に記載していただいたURLも拝見し、他の回答者様のアドバイスと併せて
自分なりに解釈できました。

ありがとうございました。

回答No.3

tom04

こんばんは！
直接の回答にはならないかもしれませんが・・・

1列作業用の列を使って（仮にとなりのJ列が使えるのであれば、J12セルに、もし使えないのであれば遠く離れた列でも良いですので
12行目に）

=IF(COUNTIF($K$12:K12,K12)=1,1,"")
という数式を入れ、フィルハンドルの（＋）マークでダブルクリック
またはJ473までオートフィルでコピー

その後、J列の数値をSUM関数で合計する方法はダメでしょうか？

これで重複と空白を無視したデータ数が出ると思います。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

お礼

アドバイスありがとうございます。

教えていただいた数式で試してみたところ、結果は44でした。
K列のデータがIF式(G列が空欄じゃなかったら、B列のデータを表示、空欄だったら空欄)の結果で、K列に複数ある空欄のうち、最初に現れた空欄に対して“1”が表示されていましたので、空欄も1件とカウントされ、【数式1】では実際の件数より1大きい結果になったのではないかと解釈しています。

教えていただいた数式のおかげで、自分なりに納得できて良かったです。
ありがとうございました！

回答No.1

mt2008

空白セルだと思って削除したセルの中にスペースが入力されているセルが有ったのではないでしょうか？
見た目では空白とスペースは区別がつきませんが、数式１ではちゃんと区別しますから……。

お礼

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

> 空白セルだと思って削除したセルの中にスペースが入力されているセルが有ったのではないでしょうか？

オートフィルタで「空白以外のセル」を指定して、空白セルを除いた状態にしましたので、スペースが入力されているセルを削除したということはなさそうです。