Excelで最頻値の次に多い数値を求める方法（配列数式、マクロを使わない）

タイトルの通りですが、配列数式やマクロを使わないで、最頻値の次に多い数値を求める方法を
教えてください。

また、最頻値が２項目（またはそれ以上）ある場合に、それらを表示する方法も分かれば
教えてください。

質問者からの補足コメント

• Excelのバージョン表記をしていませんでした。
  Excel 2002(XP)です。
  
  また、配列数式を試用しない理由は、他の人も使うワークシートであり
  そのセルをうっかり編集モードにしてしまった際に、
  [Ctrl]+[Shift]+[Enter]キーを押してもらえる可能性がほぼゼロに近いと思われるからです。

    補足日時：2016/11/22 13:25

• やはり作業列が必要ですか？
  実際の作業は、マークシート方式のテストの採点で、
  その後の解説のため、どんな誤答が多いのかを調べたいというものです。
  対象は８００人近くで、テストは１００問近くあるため、作業列を作るには
  列数の制限があるので別シートを使うしか無いですかね。
  
  １行の式でできる方法があれば助かります。

    補足日時：2016/11/22 14:37

• できるだけ簡略化したい理由は、
  
  ・自分しか使用しないものではない。
  　（他の人に使用させる必要もある）
  
  ・自分が担当しなくなっても、他の人でも簡単に理解して編集（変更）できるようにしたい
  　（マクロを知っている人はほとんどいない）
  
  というもので、できるだけ迷惑をかけないで済むようにしたいからです。
  その為、配列数式も使用したくないのです。
  他の人が作った資料で、読み解くのに本当に苦労した経験があるので、
  それをくり返したくないのです。

    補足日時：2016/11/23 07:37

• せっかく教えて頂いたのですが、２番目に多い値にはならず正しい結果が出ませんでした。
  
  たとえば､データがＡ１セルから縦に　1,1,1,1,1,2,2,2,3,3,3,3 と12個はいっている場合
  B1は、=MODE(A1:A12) 　を入れて　「1」という結果が出ます。
  B2には、=MODE(A1:A12+((A1:A12=B1)+(A1:A12=""))/ROW($1:$12))　を
  入れてやると、「2」という結果になります。
  本当は「3」じゃないと駄目ですよね。

  No.11の回答に寄せられた補足コメントです。 補足日時：2016/11/24 15:18

• 個人的には、難解な数式ではなくて、「ああ、そうか！　そこに気づけば良かったか！」という
  感じの数式にならないだろうか、という気持ちで質問しました。
  
  別のシートで度数表を作成しておいて、RANK関数で順位を出しておくだけでも良いのかなという
  気になってきました。

  No.10の回答に寄せられた補足コメントです。 補足日時：2016/11/24 15:37

No.12 ベストアンサー 回答者： fujillin 回答日時： 2016/11/24 17:00

ANo10です。

回答の意図はご理解いただけたようですね。

言わずもがなですが、多分、質問者様が充分に理解できる「別シートで集計」する方法が良いのではないかと思います。

一方で、
＞難解な数式ではなくて、「ああ、そうか！　そこに気づけば良かったか！」という～
そうだろうと考えて、
エクセルにはFrec()という関数があって、お求めの値は =Frec(B2:B800, 2) 等とすることで2番目の頻度の値が簡単に求められますよ。
　・・・というのが、ユーザ定義関数という回答の意味です。

残念ながら、実際にはFrecは組込み関数として用意されてはいませんので、別シートで計算する代わりに、VBAで同じ計算をしてしまうという案でした。

No.11 回答者： d-q-t-p 回答日時： 2016/11/24 13:58

> できるだけ簡略化したい理由は

「簡略化」と書いていますが 結局のところ「あなたが理解でき
るかどうか」だけの話ではないのでしょうか。

A1:A100にデータがあるとして B1に「=MODE(A1:A100)」が
入っている場合

=MODE(A1:A100+((A1:A100=B1)+(A1:A100=""))/ROW($1:$100))

これで２番目に頻度の高い値は一応出ますけど 多分何をしてい
るか分からないのでは？

因みに MODE関数は 配列確定に Ctrl + Shift + Enterなんてそ
もそもいりません。名前定義すれば 無駄な論理計算すらいらな
くなりますが 簡略化したとしても あなたが理解できないなら仕
方ないと思います。

> 他の人でも簡単に理解して編集（変更）できるようにしたい
だったらピボットテーブルで データ個数降順で集計すればいい
と思います。元々 数式でやることではないと思います。

No.10 回答者： fujillin 回答日時： 2016/11/24 11:19

ANo9です。

だいたいの雰囲気はつかめたように思いますが…

＞読み解くのに本当に苦労した経験があるので、それをくり返したくないのです
という意図であるなら、難しい式を求めるご質問は、その意図とは矛盾しているように感じます。

通常は、作業列を無くして一行にするだけで（今回可能かは不明ですが）、式は一気に複雑で長いものになります。当然の結果として、わかりにくく難しいものになりがちです。
誰にでも分かり易いものを目指すのなら、質問者様が容易に考えられる範囲の式で構成しておいた方が、結果的に他の方にも分かり易いものになるのではないでしょうか？

想像するところ、800人×100問程度のテストの結果そのものの表があって、そのわきにでも、頻度順で上位何位かの回答内容が表示できれば、目的に合うのではないかと思います。

すぐに思いつく方法としては・・・
マークシートでの回答とのことなので、回答の種類はせいぜい2桁（0～99）と仮定します。
（これより多くても、少なくても同様です）
上記の入力シートとは別のシートに、各設問毎の回答度数表が集計されるようにしておきます。
100問あるなら、100問×100答の表で済みますし、ほとんどが式のフィルコピーで作成できますよね？
その外側に、出現頻度順に必要な数だけ（1位～5位までとか）、の回答が出るように設定しておきます。

この結果の部分だけを、最初のシートの欄外に参照表示するようにしておけばよいですね。
多分、これで十分に目的に合うのではないかと想像しますが？
通常は途中集計用のシートは見る必要もないので、非表示にしておいてもかまいませんね。
何よりも、このようなものなら、質問者様が簡単に作成できるでしょうし、エクセルの式をある程度使える人なら充分に理解可能なものになると想像します。

他の方への配慮をなさっておきたいのであれば、一番肝心なこととして、作成した途中集計表等の内容が何を意味しているのかを、タイトルや項目名を適切に設定することで、わかりやすく、誤解なく伝わるようにしておくことではないかと思います。


さて、もう一方のユーザ定義関数に関してですが…
質問者様は、エクセルの組み込み関数に頻度を求めるFREQUENCY関数があるのをご存知だと思いますが、この組み込み関数の仕様を変更して今回の目的に合うものに変えたいとは思わなかったものと想像します。

提案したユーザ関数もエクセルの組み込み関数の一種だと思ってしまえば、その内容を他の方が変更するなどということまでを想定する必要は無いのではないでしょうか？
意味さえ分かれば、誰でもその関数を利用可能ですし、使うのにVBAの知識は一切不要です。
（もちろん、内容が解る方には変更できますが、それだけの技量の方なら自力で問題解決できるでしょう）
単純に、『出現頻度の順位を指定すると、その値が得られる関数』として組み込まれている（都合の良い？）関数を利用していると考えれば良いだけではないでしょうか？

とは言うものの、実際はユーザ定義関数なので、この関数についてのヘルプや参考資料は存在しません。
他の方への配慮を行うなら、この関数の仕様説明や使い方を別シートにでも添付しておいてあげると親切かもしれませんね。（要は、ヘルプ機能を作成しておくことなので、方法は問いませんが…）

No.9 回答者： fujillin 回答日時： 2016/11/22 17:39

こんにちは

使用上の制約などがイマイチよくわからないので、方法としてのアイデアのみですが・・・

マクロはダメとは書いてありましたが、
＞１行の式でできる方法があれば助かります。
を実現するために、ユーザ定義関数を利用なさってはいかがでしょうか？

例えば、関数Freqを定義しておいて
　=Freq(範囲、順位）
のような使い方で、指定された範囲のセル値の頻度を調べて、出現頻度順で指定された順位の値を返すといったイメージです。
もちろん、通常の関数と同じようにフィルコピーなども可能です。

少し具体化してみると、
　=Freq(C2:C100、1）
であれば、指定範囲（C2:C100）の最頻値が求められ、
　=Freq(C2:C100、2）
では、ご質問にあるような2番目の頻度の値が返されるといったイメージです。

該当する最頻の値が2つ以上ある場合の処理も、ユーザ関数であれば好きなように決められます。
例えば、それぞれ（順不同で）別の値を返すようなことも考えられますし、まとめて返す（例えば文字列で"5, 4"などと）ようにしておいて、順位1の場合も2の場合も同じ複数の値を返すというようなことも可能です。
（この場合は、関数の返り値は文字列ということになりますが…）

※ ご質問の条件を満たしているのか不明なので、アイデアとしてのイメージだけで回答しました。
※ ユーザ定義関数は、マクロと同様にVBAを利用した関数になりますが、プログラムを組むので、比較的自由な内容にすることが可能です。
※ 同じ範囲に対して大量に関数を使用するような場合は、その回数だけ同じ計算をすることになりますので、効率的には宜しくないことになります。

No.8 回答者： d-q-t-p 回答日時： 2016/11/22 15:56

> [Ctrl]+[Shift]+[Enter]キーを押してもらえる可能性がほぼゼロ

「配列数式」＝「Ctrl + Shift + Enterで確定」という訳ではありません。

Enter確定ならいいというだけの話なら 数式を名前定義して使えば済む
話です。他にも方法はありますが それが一番早いでしょう。

No.7 回答者： jmuiny 回答日時： 2016/11/22 15:35

作業列なしでは無理だと思います。

他の人が数式を触ってしまう、云々を懸念しているなら、むしろマクロにしてしまった方がいいと思います。ボタン一つで実行しますし、マクロコードを触られる心配はまずないと思います。

No.6 回答者： tom04 回答日時： 2016/11/22 14:20

No.5です。

投稿後気づきました。
作業列1、B2セルの数式はもっと簡単に
=IF(A2="","",COUNTIF(A:A,A2))
に変更してください。
表示結果は同じです。

どうも失礼しました。m(_ _)m

No.5 回答者： tom04 回答日時： 2016/11/22 14:04

こんにちは！

配列数式も使わずに！というコトですので一例です。
↓の画像のように作業用の列を2列設けてみました。

作業列1にB2セルに
=IF(A2="","",COUNTIF(A$2:A$1000,A$2:A$1000))

作業列2のC2セルに
=IF(OR(A2="",B2<>LARGE(B:B,2)),"",IF(COUNTIF(A$2:A2,A2)=1,ROW(),""))

という数式を入れB2・C2セルを範囲指定　→　C2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。

そしてE2セルに
=IF(COUNT(C:C)<ROW(A1),"",INDEX(A:A,SMALL(C:C,ROW(A1))))
という数式を入れフィルハンドルで下へコピーしています。m(_ _)m

No.4 回答者： jmuiny 回答日時： 2016/11/22 13:44

修正しました。

D1　先の回答と同じです
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1),"")

E1　変更
=IF(COUNTIF($D$1:D1,D1)=1,D1,"")
下へコピー

F1　変更
=LARGE(E:E,2)

G1　追加
=IF(D1=$F$1,ROW(A1),"")
下へコピー

B1
=IFERROR(INDEX(A:A,SMALL(G:G,ROW(A1))),"")
下へコピー

No.3 回答者： jmuiny 回答日時： 2016/11/22 13:33

No.2です。

最頻値が複数件ある場合うまくいきませんでした。
このサイトは回答を削除できないようなので、すみませんが一旦スルーして下さい。