例えば、
{a, b, b, c, c, c, d, d, e, e, e, e}
というようなデータがあったとして、eを表示したい。これをExcelでやりたい。一時計算用のセルなどを使わずに。
最初は簡単そうに思えたのだが、ちょっと試行錯誤した程度では思いつかなかったので検索。結果、3つほど方法が見つかった。
- INDEXとMODEとMATCHを使うやり方(一番多く表示のある値(文字列)を、Excel関数で#3864609 【OKWave】)
- LOOKUPと配列数式とCOUNTIFを組み合わせるやり方(『列の文字列の中で最も頻度が高いものを返す方法』(ぶんた) エクセル Excel [エクセルの学校])
- INDEXなどを使うやり方(エクセルで、もっとも多く記入した会社の記入数を表示するには? - オフィス系ソフト - 教えて!goo)
結論から言うと3つ目のやり方が正しかったのだが(一部修正が必要だったが)、割と理解に時間がかかってしまったのでメモ。
INDEXとMODEとMATCHを使う
=INDEX(対象範囲, MODE(MATCH(対象範囲,対象範囲,0)))
リンク先コメントにもあるようにスマートなやり方に見える。
MODE関数は数値にしか使えないので、MATCHを使って対象データを数値に変換している。MATCHは対象範囲内での相対位置を返すから、INDEX関数に渡してやれば最頻項目が分かる。
入力もシンプルで良いやり方に思えるが、問題がいくつかある。
第一に空白を評価できないということ。MATCH関数は項目が見つからないとエラーを返すが、エラーを含む引数をMODEに渡すとエラーになってしまう。
第二に引数を255個までしか渡せないということ。これはMODE関数がショボイのが悪い*1。
また、重複する値が無いとMODE関数はエラーを返す。とにかくMODE関数がヤバイ。
LOOKUPと配列数式とCOUNTIF
=LOOKUP( MAX(COUNTIF(対象範囲,対象範囲)), COUNTIF(対象範囲,対象範囲), 対象範囲)
上記の式を配列数式として確定する(Ctrl+Shift+Enter)。
COUNTIFを配列数式として使うことで、出現回数の一覧を得て、これの最大値でLOOKUPすることで最頻項目の位置を求める。
これも上手なやり方で、大抵上手くいく。空白セルも問題ない。ただ、LOOKUP関数がこの使い方で正しく動くという保証が無い。
LOOKUP関数はヘルプを見ると分かるのだが、検索対象として渡す範囲もしくは配列の要素が昇順に並べられていないといけない*2、。びっくりだ。
また、配列数式はしばしば問題の種になるのであまり使わないほうが良いと思う*3
。ただ、これは次に述べるやり方で一応回避できる。
あとこれはどのような動作が求められるかによるが、このやり方では空文字列を無視できない。
INDEXなどを使うやり方
これはちょっと長くて読むのに時間がかかる。
= INDEX( 範囲を含む列全体, MAX( INDEX( (COUNTIF(範囲, 範囲) = MAX(INDEX((LEN(範囲)>0)* COUNTIF(範囲, 範囲), ))) * ROW(範囲),)))
COUNTIFの最大値とCOUNTIFの結果を照合してブール値の配列を得て、これをROWで取得した行の番地に掛け合わせることで目的とする行の絶対位置を得ている。
相対位置ではないので、一番外のINDEX関数には行全体を渡す必要がある。
中をよく見るとINDEX関数で無駄にCOUNTIFを包んでいる様に見えるが、こうすると引数を配列として処理できる。要するに、配列数式として確定する必要がなくなる。もちろんこれはINDEXの正規の使い方ではないので、分かりにくい配列数式とどっちを使うべきかは微妙なところである。
ちなみに、LEN(範囲)>0の部分で空白セルと空文字列を除外しているが、最頻項目の個数と空文字列の個数が等しいと空文字列の方が選ばれる場合があるので、一つ目のCOUNTIFにもLEN(範囲)>0を掛けたほうが良い。
他のやり方
上のやり方と入力の手間は大差無いが、=で比較せずMATCHを使えば相対位置が得られるので、多少理解しやすくなると思う。
=INDEX(範囲, MATCH(MAX(INDEX((LEN(範囲)>0)* COUNTIF(範囲,範囲),)), INDEX((LEN(範囲)>0)* COUNTIF(範囲,範囲),), 0) )
*1:MODE関数は新しいバージョンのMODE.SNGL関数とMODE.MULT関数があるが、新しいにもかかわらず引数は254個までに減っている。ヤバイ。
*2:ちなみにLOOKUPよりVLOOKUPかHLOOKUPを使うことが推奨されているが、これら2つの関数でも引数次第では昇順並べ替えが要求される。
*3:ちなみにマイクロソフトも他の人が使うファイルでは使わないほうが良いと言っている。配列数式のガイドラインと例 - Excel