エクセルでデータの「どこで重複か」を見つける

質問

エクセルでデータが「どこで重複しているか」を見つける方法として
以前質問した際に教えていただいたのが

Ｂ１セルに次の式を入力して下方にオートフィルドラッグします。
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)<=1,"","重複No."&COUNT(A$1:A1,A1)))
これで重複があれば上の行から数えてその行のＡ列に有るデータが何番目の重複であるかがＢ列に表示されます。

ということでしたがこれには漏れがありました。

=IF(A1="","",IF(COUNTIF(A:A,A1)<=1,"","重複No."&COUNT(A:A,A1)))

とすると漏れはありませんが、No.はすべて「０」となるのです。

何番目の重複かが分かる方法はありませんか。

回答No.9

guchi_yama · ベストアンサー

No.3です。

＞次に出てくる重複部分はカウントが重複No.0と出ます。
については、COUNT()を使用しているからだと思われます。
本来COUNT関数は数字しかCOUNTされない為、文字が入っていても0個です。
文字の個数を出す場合はCOUNTA()です。

あと、
＞下記の補足にも記入いたしましたが、すべて「２」となりました。
＞たとえば一度目の　あああもＮｏ２　、二番目の　あああもＮｏ２となるといった具合です。
＞○番目が出せるというのは一度目の　あああはＮｏ１　三度目の　あああはＮｏ３　となるのではないのでしょうか？

ですが、
質問者様の例題に基づき補足されているとすれば、
「ああああ」も「あい」2つ目に出ているのでNo.２と出ると思われます。
1つ目の「ああああ」にNo.２と出ていると言われる場合、
一度その画面を添付して頂けるとすぐに解答が見つかるかも知れません。

と言う事で、
今までの補足に基づき回答させて頂くと、
1つ目の重複データに対しても重複No.１としたい場合、

B1=IF(COUNTIF(A:A,A1)>1,"重複No."&COUNTIF(A$1:A1,A1),"")

と入れて下にフィルで出来ると思います。

添付の図を参考に、自分の思いと違う部分を補足下さい。

お礼

できました！！

つたない説明でしたが、思うような式をご提示いただき、
大変感謝しています。

理解はなかなかできたとはいいづらいですが
作業ができるので大変ありがたいです。

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

回答No.10

layy

答えは出たと思いますので
B3においてはA3と等しいものがA1:A3にあればカウントして1
B7においてはA7と等しいものがA1:A7にあればカウントして2
B9においてはA9と等しいものがA1:A9にあればカウントして2

論点違う追加質問は良くないですが、それでも、質問の直接の回答がベストでいいと思いますね。

お礼

何度もご記入ありがとうございました。

回答No.8

layy

以前の質問の回答、漏れがあると疑った式の後半で
COUNT(A$1:A1,A1)
COUNTIF(A$1:A1,A1)
を実行すると
『あああああ』についてB7に重複NO2
『あい』についてB9に重複NO2
となっていますか。

仮に
A10が『あい』にならB10に重複NO3という想定で目的に合う感じです。

『すべて』、の回答ではこちらはどれだけ？となるのでセル表現等のがいいです。

お礼

何度もご回答いただきありがとうございました。

回答No.7

layy

結果出ても、理解中途半端であればできれば締め切りしないことです。

不明なところを聞けるときに、コピーだけして聞かずに終わる、それは残念な話です。

どうやったら中身まで伝わって理解できるのか、次回の質問は無くなるか、回答するにも課題ありですよ。

補足

すみませんでした。しかしちょっと今回事情が違うのです。

前回は質問に対するお答えは別にあり、この回答を望んでいたわけではなかったのです。
（質問に対しての直接の回答については、理解中途半端にしていません）

意図しなかった回答があって、それができるならと使ってみたところが
実際使う段になってみたところが使えなかったので
改めて（質問も違うことですし）お尋ねしたということです。

質問が違う際、その場でお尋ねすると
ベストアンサーが分かれていってしまうので
（そして正しい答えをくれた人両方をベストアンサーにできない）
さらに質問することは控えたほうがよいのではないかと、
私が回答者であるときは思うのですが。

回答No.6

kagakusuki

>これには漏れがありました。

とありますが、それはどの様な漏れなのでしょうか？
　一見した限りでは、御質問文中にある

=IF(A1="","",IF(COUNTIF(A$1:A1,A1)<=1,"","重複No."&COUNT(A$1:A1,A1)))

という式でも重複箇所の重複回数を表示するのであれば、漏れは無い様に思えるのですが、質問者様がおやりになりたい事と、どの様に違っているため、満足出来ないでおられるのでしょうか？
　その点を明確にして頂かないと、推測を基にして回答せざるを得なくなりますから、見当違いの回答をしてしまう可能性が高くなりますので、追加情報を補足して頂けないでしょうか。

　余談ですが、御質問文中の数式は、以下の様にまとめる事が出来ます。

=IF(OR(A1="",COUNTIF(A$1:A1,A1)<2),"","重複No."&COUNT(A$1:A1,A1))

お礼

丁寧に不明点をひろっていただきありがとうございました。
おかげさまで上記、お答えをいただくことができました。

補足

ご指示ありがとうございます。

＞漏れは無い様に思えるのですが、質問者様がおやりになりたい事と、どの様に違っているため、満足出来ないでおられるのでしょうか？

私のデータでの見え方だけなのかもしれないのですが

=IF(COUNTIF(A:A, A2)>1,"重複","○")　という式を入れたときに「重複」と返す、
あきらかに重複であるデータに対して何も表示されないケースが散見されます。
「散見」というのはつまり、全部出ないわけではなく、微妙に出ないので困りました。
また、その際「○番目である」情報は「0」と出るだけなので、それなら
=IF(COUNTIF(A:A, A2)>1,"重複","○")　となんらかわらず、「重複回数」も「重複順」も
わからないので、分かる方法として教えてくださったのにどこか不備なのかなあと思っているところです。

余談で書いていただいた数式ですが、やはりこちらも重複部分の最初に出てくる箇所はなにも表示されず
次に出てくる重複部分はカウントが重複No.0と出ます。

回答No.5

layy

COUNT
範囲全体の数を数えますから総数に使います。

COUNTIF
さらに条件つきで数を数えますから何番目カウントに使います。

以前の回答の式でCOUNTをCOUNTIFにしただけは試しましたか。

全部２、これが追加要望の総数になっているのでは。

COUNTで何
COUNTIFで何
それぞれが求めているものを整理するとみえてきませんか。

補足

ありがとうございます。

＞以前の回答の式でCOUNTをCOUNTIFにしただけは試しましたか。

下記の補足にも記入いたしましたが、すべて「２」となりました。
たとえば一度目の　あああもＮｏ２　、二番目の　あああもＮｏ２となるといった具合です。
○番目が出せるというのは一度目の　あああはＮｏ１　三度目の　あああはＮｏ３　となるのではないのでしょうか？

回答No.4

imogasi

#1です。
うまく行かないということですが、私の例ではテスト済みです。
一度私の挙げた例でテストしてみてください。何か感じるかもしれません。
（あるいはデータが特殊なのかもしれない。見た目以外のデータが入っているとか。）
この質問も、当初一見して、なぜ質問する必要があるのかな（以前の回答を、自力で少し修正したらしまいの課題ではないか。元の式の意味がつかめてないのではないか）と思いました。だから不適切個所の、修正方法がわからないのでしょう。
質問者のデータを質問にも書いてない質問で（質問異はデータなど例示するべきだと思う質問者のシートを使えるわけでもないので、改良（修正）をすることが出来ない。
回答を無視（スキップ）してください
ーー
ついでに、回答後に気になったが、A列で、２個以上出現する文字列で、最初の行の１も出す場合は
=IF(COUNTIF(A:A,A2)>=2,IF(COUNTIF($A$2:A2,A2)>1,COUNTIF($A$2:A2,A2),1),"")
例
a 1
s
d
a 2
x 1
a 3
f
x 2

お礼

たびたびご回答いただきありがとうございました。

補足

例がないということで申し訳ありませんでした。 ＊＊＊以下に後述します。

提示してくださっている例でもやはり何も表示されず、わかりません。
元の式の意味ですが、正直言ってなんとなくしかわかっていません。

知識に乏しい者でも
結果だけ知りたいという質問も受け付けてくれるのが
こういったサイトのありがたい点だと思いますし
もしお気にさわるようでしたら、
この程度の理解の者に対してでも
お答えくださる方をお待ちしたいと思います。

＊＊＊
▼（当時の質問）
http://oshiete.goo.ne.jp/qa/6215602.html
エクセルで質問があります。A列に単語や文章の入っているデータ（それだけを切り取って作業することができます）があって、その単語や文章の重複がないか調べる簡単な方法はありますか？

あいう
あい
あああああ
かきく
あいうえ
けこ
あああああ
おいう
あい

で、「あああああ」「あい」を見つけ出す方法です。よろしくお願いします。

▼（当時いただいた回答。間違いかもしれませんがそのまま書きます）
Ｂ１セルに次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(COUNTIF(A$1:A1,A1)<=1,"","重複No."&COUNT(A$1:A1,A1)))

これで重複があれば上の行から数えてその行のＡ列に有るデータが何番目の重複であるかがＢ列に表示されます。

回答No.3

guchi_yama

ご質問にある、
＞=IF(A1="","",IF(COUNTIF(A$1:A1,A1)<=1,"","重複No."&COUNT(A$1:A1,A1)))
で漏れがあり、

＞=IF(A1="","",IF(COUNTIF(A:A,A1)<=1,"","重複No."&COUNT(A:A,A1)))
で漏れが解消された、

と言う事は、$（絶対参照）により、A列にあるデータに対して、
何番目に出現したかを見たい訳ではなく、
A:A（A列全体）を範囲とした重複個数を知りたいイメージですか？
それによって回答が随分と変わってきます。

あと、
imogasiさんからのご指摘にもあるように、
＞=IF(A1="","",IF(COUNTIF(A:A,A1)<=1,"","重複No."&COUNT(A:A,A1)))
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑ここはCOUNTIF()の間違いですよね。

お礼

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

補足

いえ、目的としてはA列にあるデータに対して、
何番目に出現したかを見たいのですが、絶対参照をはずすと出てくる重複があって
それが外せばわかった、ということが言いたかったのです。

でも重複個数がわかる式もあれば　それはそれで知りたいです。

お二人にご指摘いただいた部分を訂正してみましたが今度は全部Ｎｏ２と出てくるようになりました。

回答No.2

layy

こういう繰り返しはたぶんダメなんでしょうね。

前のは見てないが、察するに教える方も教わる方も出来てない典型。

『教えてあげる』じゃなく『作ってあげる』だし、『教えてもらった』つもりでも何やっているのか『わかってない』。だからまた質問になる始末。

『COUNTIF』が何求めているか知ろうとしない。

解読、そこからです。

コピーなんてのは進歩ないですよ。

回答No.1

imogasi

例データ
A列　　　B列
a ＜－第２行
x
c
a 2
s
d
x 2
z
a 3
B２の式
=IF(COUNTIF($A$2:A2,A2)>1,COUNTIF($A$2:A2,A2),"")
これで良いのかな
＞COUNT(A:A,A1
ここはCOUNTIF　　だろう。

お礼

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

使ってみましたが重複に対してひとつも反応しません。
コピペしたんですが…