Excelで重複しているセルを数えたい

質問

よろしくお願いします。
表題の通りです。

A
あ
い
う
え
お
あ
い
か
き

A列の中にいくつ重複しているものがあるのか数えたいです。
上記だと2を返したいのですが、関数で上手く書けません。

コツを教えていただけると助かります。よろしくお願いします。

回答No.2

keithin · ベストアンサー

重複が「必ず２個ずつしかない」ならもっと簡単ですが、折角なので
Ａ１：Ａ９に
あ
い
う
い
お
あ
い
か
き

のような並びからでも２種類と計算させてみます

=SUMPRODUCT(COUNT(0/(FREQUENCY(MATCH(A1:A9,A1:A9,0),MATCH(A1:A9,A1:A9,0))>1)))

＃２個ずつ必ずなら
=COUNTA(A1:A9)-SUMPRODUCT(1/(COUNTIF(A1:A9,A1:A9)))
とかで。

お礼

ありがとうございました。
SUMPRODUCTとFREQUENCYの使い方がいまいちわからなかったのですが、なんとなく理解を深めることができました。助かりました。

回答No.4

KURUMITO

分かり易くてしかもデータの量が多くなっても計算に負担のかからない方法は作業列を作って対応する方法です。
例えばお示しのようなデータがA２セルから下方に入力されているとします。
B2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2)>1),MAX(B$1:B1)+1,"")

B列には重複するデータで最初に見られた行のみに番号が表示されます。B列を昇順で並べ替えをすれば重複しているデータが上段に並べられることになりますね。

お求めの重複の数は　=MAX(B:B) で求めることができます。

回答No.3

tsubuyuki

せっかくなので、もう少し詳細に突っ込んで。

Ａ列のどこかにフォーカスを置き、
※ただし、Ａ１セルには適当でも良いので「項目名」をつけておきましょう。
リボンの挿入タブからピボットテーブルをクリック。
「テーブル範囲」が全行を網羅しているのを確認し、ＯＫ。

「行ラベル」に、「項目名」をドラッグ・ドロップ。
「値」にも「項目名」をドラッグ・ドロップ。
基本的にはこれだけで「データの個数」を数えてくれます。

「値」が「データの個数」で無い場合は、値の右の▼から値フィールドの設定、
集計方法を「データの個数」にしてやりましょう。

ピボットテーブルは便利な機能ですから、
バージョンを問わずに基本だけでも覚えておくと楽しく使えますよ。

関数で頑張るなら、例えばＢ２セルに
　　=COUNTIF(A:A,A2)　　　　←Ａ２セルから下にデータがあるとして
として、必要な行までフィル。
オートフィルタ機能で、Ｂ列に「２以上」を指定してやれば
重複しているデータだけを抽出してくれます。
ただし、これだけだと重複している行数分だけ表示されてしまうので
（例えば、「あ」が１００回出てくると、それだけで１００行）、
あまり簡便とは言えません。

そこで一工夫。
　　=IF(COUNTIF($A$2:A2,A2)=>2,"",COUNTIF(A:A,A2))
　　※その行までにすでに重複するものが在ったら空白、無かったら全件数
としてやると、上記現象を防げます。
これでフィルタをかけると、各データ１回ずつの抽出で済みます。

ＩＮＤＥＸ関数やＭＡＴＣＨ関数など、ちょっと複雑な式を使えば
フィルタ機能やピボットテーブルを使わずにもできますが・・・
まぁ、これが主題の案件であればそこまで頑張らなくても良いかなぁ、と思います。

回答No.1

angkor_h

ピボットテーブルを利用すれば、A列セル内容とその個数の一覧がすぐに出ます。

####
最近excel2007でピボットテーブルを使ったら使えなかった。ネット検索でやっと使えた。1時間以上も費やした。
excelヘルプは使えない、と言うよりMSのヘルプ全般が「MSのおばか」。ネット検索のほうが親切です！！
excel2003のピボットテーブルは使いやすかったのに、なんで使い方をこうも変えてしまったのだろうか。