エクセルで、多い数字のランク

質問

エクセル２００３を使っています。
教えていただきたいのですが、
打ち込んだ数字の中で、
１番多く出てくる数字。
２番目に多く出てくる数字。
３番目に多く出てくる数字。
を出すのはどうしたらいいのでしょうか？

実際には、数字は１０００行以上あるので、フルターで抜き出して数えるのも大変で・・
簡単な方法は無いでしょうか？

１位　６件で（数字の）５
２位　４件で（数字の）７　
という答えを出したいのですが・・・

よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんにちは！

一部配列数式を使っています。
データ数が1000行以上あるということなので、少しPCに負担がかかるかもしれませんが・・・
一例です。

↓の画像のように作業用の列を使っています。
作業列C2セルに
=IF(B2="","",IF(COUNTIF(B3:B$1500,B2),"",COUNTIF(B:B,B2)))
という数式を入れ、フィルハンドルの（＋）マークでダブルクリック、またはオートフィルで下へずぃ～～～！っとコピー！

そしてE2セルに
=IF(F2="","",RANK(F2,$F$2:F2)&"位")
F2セルに
=IF(COUNT(C:C)<ROW(A1),"",LARGE(C:C,ROW(A1)))
G2セル（この列だけが配列数式になってしまいます）に
=IF(F2="","",INDEX($B$1:$B$1500,SMALL(IF($C$1:$C$1500=F2,ROW($A$1:$A$1500)),COUNTIF($F$2:F2,F2))))
この画面からG2セルにコピー＆ペーストした後に、数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

最後にE2～G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、とりあえず1500行目まで対応できる数式にしてみました。
作業用の列を複数使用すれば配列数式にしなくても大丈夫だと思います。

以上、参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m

お礼

世の中にこんな事ができる人がいるんだね！と思わず主人と拍手してしまいました。
ありがとうございます。
早速仕事で使わせていただきます。

回答No.6

Wendy02

#3の回答者です。
>数字は、５桁なんです・・・
>９０００４とか、９０２４５とか・・・

私は、基本的には、途中で変更された質問に対しては、お答えしないことにしていますので、悪く思わないでください。後出しジャンケンのようです、そのたびにやり直しでは、質問--回答として成り立たないからです。

ただ、90000～99999 までの数字ですから、10000個の数値が間にあるのですから、その数字を探していくというのは、実際には配列数式では無理です。当然、配列関数を使うFREQUENCY関数も重すぎるので、上手くいくか疑問です。

>フルターで抜き出して数えるのも大変で

そんなはずはありません。マクロ以外の解決方法なら、フィルタオプションで、一意の数字を選び出し、それで、数を取って、並べ替えればよいと思います。こういうことは、Excelの初歩ですね。関数で処理しようとすれば、難しくなるのは当然かもしれません。

D列に、フィルタオプションで抜き出して、
=COUNTIF($B$2:$B$1500,D2)
として、オートフィルでコピーして、出てきた数を、降順に並べ替えれば済みます。

マクロで出す考え方は、まったく違います。

なお、申し訳ありませんが、#1さんの数式では、同位になった場合にエラーが発生するのと、最後尾の数字に対して上手く出てきません。直すことは可能ですが、こちらからは訂正しません。

お礼

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

回答No.5

MackyNo1

>補助列のC2セル
=IF(COUNTIF($B$1:$B2,B2)=1,COUNTIF($B$1:$B$28,B2)-B2/10^15,)
をコピー＆ペーストしました。
Ｃ２～Ｃ１００位、下にドラックすると、

２８行目から枠の左上に三角マークが出て、
カーソルを置くと、「！」マークが出るのですが・・・

失礼しました。

データ数が多い場合は、以下のように数式を変更してください。

=IF(COUNTIF($B$1:$B2,B2)=1,COUNTIF(B:B,B2)-B2/10^15,)

お礼

出来ました！
ありがとうございます。

回答No.4

imogasi

中間作業列が必要だがFREQUENCY関数を知っているものには自然な素直な解法である。
区間幅を指定できるとかの融通性がある。
ーー
まずデータの、重複せず、漏れの無いデータの１揃いを作る。
データーフィルターフィルタオプションの設定ー重複するレコードは無視する。結果は下記B列。C1はB１と同じ見出しを入れておくこと。
結果のC列を降順で並べ替え。結果はD列（プロセスが判りやすいようにC列をD列に貼り付け、別列D列でソートしている）
B列とD列に対し、FREQUENCY関数を使う。
E2:E11を範囲指定しE2セルに
=FREQUENCY(B1:B20,D2:D10)とれてCTRL+SHIFT+ENTERキーを同時押しする。
頻度表が出来る。下記E列
値だけをF列に複写し（E列の式を消すと言うこと）
それを降順に並べ替える。下記F列
全順位が出るので希望の上位を見てそれ以下を見なければ良い。
ーー
D列は区間を幅に指定もできる。昇順でも出来る。
例データ
B列　　　　C列　　　　　D列　　　　　E列　　　　　F列　　　　G列
データ データ ソート後 頻度表 D列 頻度表（値のみ、ソート後）
1 9 1 2 1 2
4 8 2 4 2 4
2 7 3 1 3 1
5 6 4 2 4 2
4 5 5 3 5 3
2 4 6 2 6 2
1 1 7 2 7 2
3 3 8 2 8 2
2 2 9 1 9 1
6 0 0
7
5
8
9
2
5
7
8
6
F,G列をG列で降順ソート
D列 頻度表（値のみ、ソート後）
2 4
5 3
1 2
4 2
6 2
7 2
8 2
3 1
9 1

お礼

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

回答No.3

Wendy02

私は、関数は得意ではないけれども、数字が、1から9までなら、このようになるかと思います。ROW($A$1:$A$9)として探しています。+ROW($A$1:$A$9)/10 というのが、「重み付け」というそうです。

#1さんの邪魔にならない所に置くとします。比べてみてください。（#2さんの回答は、アップロード時点では、まだ見ていませんでした。）

i1 多い数字

i2 数式

=RIGHT(LARGE(INDEX(COUNTIF($B$2:$B$1500,ROW($A$1:$A$9))+ROW($A$1:$A$9)/10,,),ROW(A1)),1)

J1 数

J2
=COUNTIF($B$2:$B$1500,I2)

お礼

ありがとうございます。
数字は、５桁なんです・・・
９０００４とか、９０２４５とか・・・

回答No.2

MackyNo1

補助列を使って計算負荷の少ない数式にするなら以下のような数式が良いかもしれません。

補助列のC2セル
=IF(COUNTIF($B$1:$B2,B2)=1,COUNTIF($B$1:$B$28,B2)-B2/10^15,)

F2セル
=IF(G2,COUNTIF(G:G,">"&G2)+1,"")

G2セル
=LARGE(C:C,ROW(A1))

H2セル
=IF(G2,INDEX(B:B,MATCH(G2,C:C,)),"")

G2セル以下の個数を表示するセルは、セルの書式設定で表示形式をユーザー定義にして「0;;;」としてください。

お礼

ありがとうございます。
サンプル通りにＣ２から数字を打ち込みましてた。

補助列のC2セル
=IF(COUNTIF($B$1:$B2,B2)=1,COUNTIF($B$1:$B$28,B2)-B2/10^15,)
をコピー＆ペーストしました。
Ｃ２～Ｃ１００位、下にドラックすると、

２８行目から枠の左上に三角マークが出て、
カーソルを置くと、「！」マークが出るのですが・・・