文字列の出現頻度が高い順のリストを作成する方法

質問

よろしくお願い致します。

Excel の計算式のみを用い、文字列の出現頻度が高い順のリストを作成する方法についてお知恵をお貸しください。

やりたいことは以下になります。
A列にはりんごが2回、ばななが2回、みかんが1回なのでB列の通り出現頻度が高い順番に表示ｓれます。
ここで、りんごとばななが同数ですが、上にある方（りんご）を先に表示させ、下にある方（ばなな）は、あとに表示させます。以降は同じロジックです。

A　　　　B
りんご　 りんご　　　　　
ばなな　　ばなな
みかん　　みかん
りんご
ばなな

以上、よろしくお願い致します。

回答No.5

msMike

添付図参照
D1: =COUNTIF(A$1:A1,A1)
E1: =IFERROR(IF(D1>1,"",MAX((A:A=A1)*(D:D)))-ROW()/10000,"")
F1: =LARGE(E:E,ROW(A1))
B1: =IFERROR(INDEX(A:A,MATCH(F1,E$1:E$5,0)),"")
【お断り】セル E1 の式のみ配列数式です。

回答No.4

neKo_quatre

No.2です。
失礼しました。
頻度数えてなくて、単に出現順のリストでした。

回答No.3

kagakusuki

>りんご
>ばなな
>みかん

> の順番に出来たほうが望ましいですが、可能でしょうか？

　並べる順番を間違えてしまい、大変失礼致しました。
　Sheet2のB列に入力する関数を以下のものと差し替えて下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,COUNTIF(Sheet1!$A:$A,INDEX(Sheet1!$A:$A,ROW()))*10000000-ROW(),""))

お礼

私の環境でも実現できました。
ありがとうございました。本当に感謝です！

回答No.2

neKo_quatre

作業列を使うなら、

B列：空白

C列:A1～対象行まで、対象業の項目をCOUNTIF。初出の項目のみ1になる。
C1:=COUNTIF($A$1:$A1,A1)
以降コピペ

D列:C列が1(初出)の項目をカウントアップ。
D1:=1
D2:=IF(C2=1,D1+1,D1)
以降コピペ

E列:VLOOKUP用に項目名をそのまま
E1:=A1
以降コピペ

F列:空白

G列:連番を振っておく
G1:=1
G2:=G1+1
以降コピペ

H列:G列の連番をD列から探して(上から完全一致)E列の項目を引っ張る
H1:=VLOOKUP(G1,D1:E13,2,FALSE)
以降コピペ

とか。
#N/Aとか処理してません。
左に作業列作れば、E列なんかも不要です。

回答No.1

kagakusuki

>Excel の計算式のみを用い

との事ですが、作業列を使用する方法でも宜しいのでしょうか？それとも駄目なのでしょうか？
　もし、作業列を使用する方法でも宜しいのでしたら以下の様な方法が御座います。

　例えば仮の話として、御質問の

　A　　　　 B
りんご　　りんご
ばなな　　ばなな
みかん　　みかん
りんご
ばなな

という表が存在しているシートがSheet1であったとして、Sheet2のA列を作業列として使用して、Sheet1のB列に結果を表示させるものとします。
　その場合、まずSheet2のA1セルに次の様な関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,COUNTIF(Sheet1!$A:$A,INDEX(Sheet1!$A:$A,ROW()))*10000000+ROW(),""))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。

　次に、Sheet1のB1セルに次の様な関数を入力して下さい。

=IF(ROWS(B$1:B1)>COUNT(Sheet2!$A:$A),"",INDEX($A:$A,MATCH(LARGE(Sheet2!$A:$A,ROWS(B$1:B1)),Sheet2!$A:$A,0)))

　次に、Sheet1のB1セルをコピーして、Sheet1のB2以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございました。
私の環境でも確認しましたが、画像の通り表示されました。
しかしながら、細かいことを言うと、

りんご
ばなな
みかん

の順番に出来たほうが望ましいですが、可能でしょうか？
同数の場合は、上から表示したいと思っております。