excel（エクセル）で範囲内の頻出単語・最頻値を調べる方法/Mode・Mode.sngl・Countif関数の使い方

mode IT
2018.11.04

excelデータの中でどのデータが多く登場しているのかを検出する方法です。実行環境は2007でMode関数を使用していますが、2010以降ではMode.sngl関数に名前が変わっています。
下のほうでCountif関数の解説もしています。

スポンサーリンク

mode関数を使う方法

Mode関数(Mode.sngl関数)とは、範囲内で「最も登場回数が多い数字」を戻り値として返す関数です。検出対象が数字の場合は、こちらの方法が簡単です。
実際にやってみましょう。
まずExcelに適当な数字の羅列を作り、計測したい範囲に対して、下の関数を入力します。

最頻値の検出
=mode(範囲)
（2010以降は=mode.sngl(範囲)）

ここでは範囲A2～E14の中を検索しています。

mode1
=mode(A2:E14)（A2～E14の中で最も登場回数の多い数字を表示する）と入力します（excelのバージョンが2010以降の場合=mode.sngl(A2:E14)）。

一番よく出てくる「10」が表示されました。

mode2
少し数値を変更し、「10」と「1」がそれぞれ3回ずつ登場するようにしたところ、「10」だけが表示されています。このように、同率一位もエラーにならず、いずれかひとつだけが表示されます。
mode3
「1」の数を増やしてみます。出現数が追い越されると、ちゃんと反映されることが確認できました。
スポンサーリンク

mode関数で数字以外の頻出具合を調べたい場合

Mode（Mode.sngl）関数では、数字以外は検出できません。
なのでもし文字などの頻出単語を調べたい場合、あらかじめ文字を数字に換算しておき、別の関数を使用して、数字に対応した別の単語に置換して表示することになります。暗号表みたいなものです。
今回は左に数字、右に数字から置換したい文字を置き、Vlookup関数を使用します。

検索値に対応した戻り値を返す
=vlookup(検索値、参照する範囲、範囲内で戻り値を参照する列、検索方法)
modevlook1
まずは範囲内の最頻値を求める表を作っておきます。
modevlook2
範囲内で最も多い「1」が検出されました。
modevlook3
今度は右の表を参照して、最頻値の下に数字に対応した文字を表示したいと思います。1であればその右のA、2であればBです。
modevlook4
今回は=vlookup(C5,E1:F8,2,FALSE)(C5を参照し、E1～F8の左端の列から完全一致する値を探す。見付けたら数字の一つ右にある値を表示する)と入力します。vlookupに慣れないうちは、2,falseは固定ぐらいに思っておいて問題ないです。
modevlook5
「1」を見付けることができたので、その右にある「A」が表示されました。
modevlook6
最頻値が変わると、表示される文字も変わります。

文字を数字に変換する方法

連番を作るだけの単純なものですが、たとえばこんな感じで変換可能です。もし元のデータをいじりたくない場合は、他のシートにコピーなどしてご対応ください。

ayc1
まず文字を一列に並べます。
ayc2
次に「並べ替えとフィルタ」で、昇順または降順（同じ文字が続いて表示されればいい）に並べ替えます。
ayc3
一番最初のセルの隣に「1」を記入します。
ayc4
次のセルに=if(A1=A2,B1,B1+1)
（A1とA2が同じならB1、そうでないならB1+1を返す）と記入します。
ayc5
これを一番下のセルの隣までオートフィルでコピーします。
スポンサーリンク

方法2:Countif関数を使う方法

Countif関数を使うと自動で文字の登場回数を数えることができます。検出対象が文字の場合、文字と数字が混ざってる場合などはこちらがおすすめです。

範囲内の指定文字の数をカウント
=countif(範囲,指定文字)）
cif1
この表で、A列にそれぞれの文字が何回ずつ登場しているかを数えてみたいと思います。D列のように各文字がひとつずつある表は、A列をコピー→一意の文字のみ抽出など行うと簡単に作れます。
cif2
D列文字の左に、=countif(A:A,D2)（A列の中でD2と一致する文字の数をカウントする）と記入します。左に書くのはあとでvlookup関数で使用するためです。
A:AはA列全てを意味します。もちろんA1:A10のように書いてもOKです。
cif3
Enterを押すと、catはA列に2つあることがわかりました。
cif4
下までオートフィルすると、各文字の登場回数が表示されます。どうやらsheepが最も多いようです。
cif5
範囲内の最も大きい数字は、Max関数で検出することができます。ここでは=max(C2:C7)と記述し、C列の中で最大の数字を表示しています。
cif6
=vlookup(E2,C2:D7,2,FALSE)(E2を参照し、C2～C7の中で完全一致する文字の右にある値を返す)と記述します。
cif7
頻出単語であるsheepが検出されました。
cif8
単語の出現回数を変えると、このように結果も変わります。
cif9
VlookupでE2を参照していますが、これはC2～C7の最大値を関数で参照しているだけなので、=以外をコピーして……。
cif10
関数の「E2」を「max(C2:C7)」に差し替えても動きます。
Excel（エクセル）：数える、カウント
Excelで個数を数える、カウントする方法まとめです。 セルの数を数える 範囲内の数字の入ったセルの個数、データの入ったセルの個数を数えるには、それぞれCount関数、CountA関数を使います。 空白セルの個...
sunagitsune.com
2021.04.29
関数・演算子・メソッド・プロパティ名から探すExcel/VBA（マクロ）使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。
sunagitsune.com
2018.12.30
スポンサーリンク

コメント