Countifよりも早く重複数をカウントする方法ありますか？

1行目が項目、
2行目以降にデータが入っています。
合計30万行位あり、B列の重複数をカウントしたいのですが、とても時間がかかります。
数式は、
=countif(B:B,B2)
で、重複してる行全てに同じ数字を入れる必要があります。
（昇順にして上から1ずつ増やしていくカウント方式では駄目。)
同じデータが6個ある場合は、6行とも”６”が入っている必要あり。)

行った工夫としては
=countif($B1:$B300000,B2)

しかし、カウントする列が3列もあるので大変です。
もっと早く結果を出す方法は無いでしょうか？
VBAでも構わないのですが、、、

No.8 回答者： 銀鱗 回答日時： 2022/07/04 17:13

なんなら、ピボットテーブルでセルの個数を数えさせれば良い。

ぶっちゃけ重複に使った数式は無駄になるけど、【データがあるセル】という役割を担うのでダミーのデータでも良いんだ。

この回答へのお礼

ピボットで数える、という方法を思いつきませんでした。
確かにそっちのが全然早く結果が出ますね。
それをVlookで戻せば良いですね。
試してみます。ありがとうございます。

お礼日時：2022/07/04 17:34

No.7 回答者： 銀鱗 回答日時： 2022/07/04 17:08

(´・ω・`)

だから、応用にしようぜ

D列に重複データに連番を入れる数式を入れた。
でもってD列に対してオートフィルタで ”１” のデータを表示するようにした。

一発だろ？

No.6 回答者： ひでオンジ 回答日時： 2022/07/04 16:59

説明が足らずすみません

こんな感じです
①メインシートのB1～B300000をSheet2にコピペ
②Sheet2のコピペ内容の重複を削除
③②でユニークになった内容をメインシートのC列にコピペ
④D列にCountIfの数式を埋め込む
　参考：=CountIF($B:$B,$C2)

No.5 回答者： mike_g 回答日時： 2022/07/04 16:30

「30万行位」を図示する馬鹿はいないけど、例として

“20行くらい”を例示したのが添付図です。
♪黄色いサクランボ♪ならぬ“黄色い矢印”で過程を示しましたが、
左端図を最終的に右端図にすることを希望しておられますか？

この回答へのお礼

図示していただいた方法の流れがイマイチ理解できずすみません。。
しかし、左端図の結果を得たいだけです。
図の赤枠に示して頂いた「=countif(B:B,B2)」の結果を得たいだけです。

現在の方法は、
・一度B列でソートする
・C2に「=if(B1=B2,C1+1,1」を入れて下方にコピー
　→すると重複グループがカウントされて一番下がカウント数となる
・B列C列を作業用に別シートに値貼付け
・重複数を降順にソートして、B2に「=vlookup(A2,別シート!A:B,2,false)」で最大値のみを入れ込み直す。
と考えています。結果的に大分早く処理完了します。

上記作業をB列、F列、J列の値で同様の作業を行う事になります。
もっと良い案があれば助かります。

お礼日時：2022/07/04 16:50

No.4 回答者： 銀鱗 回答日時： 2022/07/04 15:24

ん？

「重複しているセルの数」だけを数えるってことで良いんですよね？
どのデータがどれだけ重複しているかなんて個別に考えなくていいんですよね？

・・・本題・・・

普通は、
　=countif(B:B,B2)
ではなく
　=countif(B$2:B2,B2)
と、B2セルに入力して、
最終行までB2セルを複製したうえで、
最終行までの中で ”1” が返ってくる数を数えることで、
　「重複しない数」
を調べることができるのですが……。
Excelの最終行(1,048,576行)まで検査するからおかしなことになる。
B列のセルを選択して、
　Ctrl+↓　
で入力されている最終行までいっきにジャンプできるので、その行の数式を入れる列のセルを選択後に
　Shift+Ctrl+↑
でB3セルまでの範囲を選択し、
その後に
　Shift+↑
でB2セルを含んだ範囲を選択、
あとは
　Ctrl+D　
でセルを複製すれば終わる。

あとはデータが入力されている行の数から ”1” が返ってくるセルの数を引けば良い。
別のセルでCOUNT関数を使って ”1” の数を数えるんだ。

キーボードショートカットを使えばこんなのが3列あっても1分もあればできてしまう。


・・・

これは重複しているデータに連番を付けるときに使われるテクニックです。　
”1” が返るB列の値に対する最大値を求めても良いんですけど煩雑な考え方になり面倒なので
前置きで条件を絞って、引き算することを薦めています。

……まあ、自分が否定したことをやりたいのだろうと邪推してますけど……
C列にこの数式を入力したら、D列に
　=countif(B:B,B2)
で、C列に対してオートフィルタをかけて ”1” の行だけ表示。
そして、D列の値を読めば良い。

都度、値を読み取る必要はありますが、質問者さんレベルであればこの程度にしておかないと
【誤った使い方で違うデータを導き出してしまったとしても気づかない】
と思うんです。

この回答へのお礼

データ個別に重複数をカウントしたいです。

例を挙げると
B列、C列を以下の様な結果を得たいのです。
----------
鈴木、6
佐藤、2
阿部、3
田中、2
鈴木、6
鈴木、6
井出、1
阿部、3
木下、1
鈴木、6
佐藤、2
田中、2
鈴木、6
阿部、3
鈴木、6

お礼日時：2022/07/04 16:54

No.3 回答者： ひでオンジ 回答日時： 2022/07/04 15:05

循環参照しませんか？

B1~B300000迄の値を別のシートにコピペして
データメニューの重複の削除をクリック
それでユニークになった値を元のセルの隣にコピペしてcountifで重複チェックで…(；^ω^)

この回答へのお礼

重複を削除してユニークにしてしまったら、重複数を数えられなくなります。

お礼日時：2022/07/04 16:40

No.2 回答者： tatsumaru77 回答日時： 2022/07/04 14:20

私が回答できるわけではありませんが、

添付図のように
B列に３が３件、４が２件ある場合、どこに、どのような結果を表示されたいのでしょうか。


「カウントする列が3列もある」ということは、ほかにも、チェックしたい列があるということでしょうか？
もし、あるとして、その場合は、その列内での重複数をカウントするのですか、それとも、
B列の内容も含めて、重複数をカウントするのですか。

No.1 回答者： ShowMeHow 回答日時： 2022/07/04 14:06

速くなるかどうかはやってみないことにはわかりませんが、

ADOでアタッチして、SQLで処理してみるのが良いかもしれません。
３０万行というのは、エクセルで処理するサイズではないと思います。
アクセスなどのデータベースも考慮すべきだと思います。