こんにちは。
先日生徒様に、Excelの集計で値をランキング表示したいとのご相談をいただきました。
合計額のランキングならRANK.EQ関数を使いますが、今回は値の出現回数をランキング表示したいとのこと。
Excelでアンケート等の集計を行う際に、回答の値の最頻値を求める関数としてMODE.SNGL関数がよく使われます。
MODE.SNGL関数は、関数ライブラリの「統計」グループに分類される関数で、配列やセル範囲として指定されたデータの中で最も頻繁に出現する値（最頻値）を返してくれます。
とっても便利なのですが…じゃあ２番目に出現する値は？３番目は？となると、意外と手間がかかるのです。
例えば以下のようなアンケート結果を入力した表があるとします。
A列には通し番号、
B列には性別、
C列には年代、
D列に設問Aの回答
Ｅ列に設問Bの回答
Ｆ列に設問Ｃの回答
Ｇ列に設問Ｄの回答
Ｈ列に設問Ｅの回答
設問Ａ～Ｅの回答欄には1から5の数値が入っています。
この数値はアンケートによくある
1：非常に悪い、2：悪い、3：普通、4：良い、5：非常に良い
のようなものだとします。
この表から、設問Aの回答の最頻値を求める場合、
=MODE.SNGL(設問Aの列範囲）
で簡単に求められます。
但し、回答欄に空白セルが多くある場合、空白を最頻値として返してしまう場合があります。
空白セルを除外する場合は次の式になります。
=MODE.SNGL(FILTER(設問Aの列範囲, 設問Aの列範囲<>""))
さて、これに対して２番目に多く出現する値を簡単に求めようとすると、
別の場所に補助用の表を作り、設問Aの列範囲からCOUNTIF関数で「1」の数と「２」の数と「３」の数と「4」の数と「5」の数を数えて、その結果から目視またはRANK.EQ関数で２番目に多い数を求めて…のような面倒な方法になってしまいます。
補助列や行を増やさず、簡単な関数だけで解決するには？ということで、色々な関数で試した結果、一番簡単だった方法をお知らせしますね！
【設問Aの２番目に多く出現する値を求める式】
=MODE.SNGL(IF(設問Aの列範囲<>設問最頻値を求めたセル,設問Aの列範囲))
※空白セルを除外する場合は
=MODE.SNGL(IF((設問Aの列範囲<>設問A最頻値を求めたセル)*(設問Aの列範囲<>""),設問Aの列範囲))
この式では、最頻値として求められた値を除外した残りのデータの中で新たに最頻値を求めています。
同じ理屈で３番目に多く出現した値を求めていきます。
【設問Aの３番目に多く出現する値を求める式】
=MODE.SNGL(IF((設問Aの列範囲<>設問A最頻値を求めたセル)*(設問Aの列範囲<>設問A頻出２位を求めたセル),設問Aの列範囲))
※空白セルを除外する場合は
=MODE.SNGL(IF((設問Aの列範囲<>設問A最頻値を求めたセル)*(設問Aの列範囲<>設問A頻出２位を求めたセル)*(設問Aの列範囲<>""),設問Aの列範囲))
この式の設問「A」をB～Eの範囲に変えれば、それぞれの設問の最頻値～頻出３位までを求めることができます。
関数での解決方法はひとつではないため、色々なやり方がありますが、シンプルな式が一番ですよね。
集計方法でお困りの際はお気軽にスタッフにご相談くださいね♪