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.2 回答者： jmuiny 回答日時： 2016/11/22 13:27

配列数式NGだと、作業列を使わないと出来ませんでした。

A列に数値が並んでいるとします。
B列に2番目に頻度が大きい数を表示します。

作業列
D1
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1),"")
下へコピー
E1
=LARGE(D:D,2)
F1
=IF(D1=$E$1,ROW(A1),"")
下へコピー

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

No.1 回答者： WindFaller 回答日時： 2016/11/22 13:09

配列数式やマクロを使わない理由を教えていただきたく思います。

どんな理由があるのでしょうか。例えば、下位バージョンであるとか。

ただ、私は関数は不得意ですから、もっと良い解答があるはずだと思います。
④のRANK.EQ は、2003等の下位バージョンではなかったと思います。


①
A1:A50 が乱数で1～99までの数字があるとします。

②
B1から、B50 まで、COUNTIFで、

=COUNTIF($A$1:$A$50,A1)
B1を50行目までクリックコピー（セルの右端下の黒丸をクリック)

③
C1から、C50 まで、RANK 関数で、順位を取ります。
=RANK(B1,$B$1:$B$50)
C1を50行目までクリックコピー

④
D1に以下の数式を置き、
=RANK.EQ(A1,$A$1:$A$50)

D2 から、D50 までを、RANK.EQ　関数で、順位の位置を取ります。
=IF(COUNTIF($D$1:D1,RANK.EQ(A2,$A$1:$A$50)),"",RANK.EQ(A2,$A$1:$A$50))
D2を50行目までクリックコピー

⑤
E1から、E50 まで、以下の数式を置きます。
=IF(D1="","",C1+D1/100)
E1を50行目までクリックコピー

⑥
F1から、適当な位置まで、以下の数式を置きます。
=SMALL($E$1:$E$50,ROW(A1))
F1をドラッグコピー

⑦
G1から、適当の場所まで、ドラッグコピーします。
=INDEX($A$1:$A$50,MATCH(F1,$E$1:$E$50,0))

上から順に頻度の高いものが出てきます。
F列の整数値が同じものは、同列です。

以下は、その計算イメージです。