エクセル関数 頻度順に並べる方法は？

質問

excel関数についてですが
度数の多い順に並べる方法はありますか？

例えば、果物名前がたくさん並んでいるデータを考えます。

1　りんご
2　バナナ
3　なし
4　りんご
5　メロン
6　もも
7　ぶどう
8　りんご
9　ぶどう
10　バナナ
11　りんご
12　ぶどう

このデータから、度数の多い順に並べたいです。

[欲しい結果]

1　りんご
2　ぶどう
3　バナナ
...といった具合です。

同率で並ぶ場合は含めなくてもよいですが。。。
できれば、データ列を作らずに関数式で表示させたいのです。

回答No.2

noname#52504

ピボットテーブルで処理するのが標準的な方法ですが、
参考までに数式を使って処理する方法を。

以下、元データがSheet1の$A$1:$A$99にあり、Sheet2に抽出するとします。
（元データのシートは変更しません）
-----------------------------------------------------------------
●Ａ案：作業列を使って数式で処理

　１．Sheet2のA1セルを、
　　=IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)=1,COUNTIF(Sheet1!$A$1:$A$99,Sheet1!A1)+1/(ROW()+1),"")
　　として、99行目まで下方にフィル　　

　２．Sheet2のB1セルを、、
　　=INDEX(Sheet1!$A$1:$A$99,1/MOD(LARGE($A$1:$A$99,ROW()),1)-1)
　　として、#NUM!が返るまで下方にフィル

※初出のデータについて、度数に行番号の逆数を加えた値を振る
※度数が大きいものから順に選び、端数の逆数(行番号)をとって、該当行の値を返す
-----------------------------------------------------------------　
●Ｂ案：作業列を使わず数式一発で処理

　１．B1セルを選択

　２．数式バーに下記の数式を入力
　　=INDEX(Sheet1!$A$1:$A$99,1/MOD(LARGE(((MMULT((Sheet1!$A$1:$A$99=TRANSPOSE(Sheet1!$A$1:$A$99))*(ROW(Sheet1!$A$1:$A$99)>TRANSPOSE(ROW(Sheet1!$A$1:$A$99))),ROW(Sheet1!$A$1:$A$99)))=0)*(Sheet1!$A$1:$A$99<>"")*(COUNTIF(Sheet1!$A$1:$A$99,Sheet1!$A$1:$A$99)+1/(ROW(Sheet1!$A$1:$A$99)+1)),ROW()),1)-1)

　３．Ctrl+Shift+Enterで確定

　４．B1セルを＃DIV/0!が返るまで下方にフィル

通常の数式はEnterキーで確定しますが、
こちらの数式は配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

参考として書いてみましたが、複雑、長い、重い、の三重苦ですね。
また、MMULTを使っているので元データが5460を超える場合には対応できません。
-----------------------------------------------------------------
エラー処理については、必要に応じて、
・IFERROR関数をかぶせる（Excel2007）
・条件付き書式で白フォントにする
・ページ設定で印刷しないようにする（Excel2002以降)
・=IF(ISERROR(数式),"",数式)で、長さゼロの文字列を返す
・気にしない
その他ご自由に。

いずれもExcel2003で動作確認済。

回答No.1

imogasi

（１）データ内容（りんごなど）のユニークな列（1回階しか現れない列）を同一シートに作る。
フィルタオプションで「重複するレコードは無視する」を使う。
参考
http://q.hatena.ne.jp/1135141460の２番回答
（２）Countif関数で件数を出す。
（３）（１）の列＋（２）の列でソートする
ーー
色々注文をつけているようだが、上記で不満な点は何かな。
ーー
フィルタを使わないなら
第１行は空白にして
例データ　A2:A１１
A列　　B列
a 1
b 2
a
a
a
b
c 3
a
b
c
B列は　=IF(COUNTIF($A$2:A2,A2)=1,MAX($B$1:B1)+1,""
と入れて下方向に式を複写する。
これに対しSheet2ナリで、
=INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!B:B,0),1)
と入れて下方向に式を複写する。
結果
Sheet2
a
b
c
#N/A
以上imogasi方式でGoogle照会してもらえれば、類似質問が沢山出る
し＃N/Aの消し方もわかると思う。
しかしエクセルは操作のソフトであり、関数でやるのはどうかと思うが、何でも関数の質問者も多い。
（２）またピボットテーブルででもできる
（３）関数FREQUENCYでは文字列の出現頻度は無理のよう。
（４）VBAでもそれほどロジックは難しくない。
初回の出現時にセルに書き出し（表）件数は１とし、毎回（行のデータについて）セルのその表を調べて、該当すれば件数を＋１していく。その後はソートする。

お礼

どうもありがとうございました。
（１）とても参考になりました。
（２）ピボットテーブルの使い方も学習してみようと思います。
（３）数字でしたら可能なようですね。当初、この方法でできないかと考えていました。
（４）ＶＢＡの知識はまったくありませんので、またの機会にしたいと思います。

補足

今回質問させていただいたのは、
多数のデータシートごとに集計するにあたって、
元のデータシートをできるでけ変更せずに、
（個々のデータシートにデータ列を作るのは非常に手間がかかり、
かつミスを生じる原因となるので．．．）
なるべく簡単かつ確実に集計結果を参照できないか
と考えたためです。
関数式を希望したのは、このような理由からでした。