目次
条件に一致した範囲の最頻値などで注意が必要なEXCEL配列数式
EXCELでよく使う、MODE（最頻値）、MEDIAN（中央値）などの関数ですが、意外に難しく落とし穴があるのが「条件に一致した値の範囲でのみ求める」ケースです。
AVERAGE(平均）だと、AVERAGEIF、AVERAGEIFSなどの条件付き計算の関数もあるのですが、今回は、それらのないものについて書きます。
全体の最頻値・中央値を求めるだけなら簡単
例えば、以下のような表があるとします。
この全体の最頻値や中央値をとるだけなら簡単です。
最頻値なら
=MODE.SNGL(B2:B13)
または
=MODE(B2:B13)
です。
中央値なら
=MEDIAN(B2:B13)
です。
これで表示される答えは
最頻値は、20だけでなく100も1000も1200も同じ2回出現じゃないかと思うかもしれませんが、MODE.SNGLは最初の一つだけ取得するので、こうなります。
条件に一致したものの中での最頻値・中央値
同じデータを使って、例えばキーが「B001」であるものの中での最頻値・中央値を求めたいとなったときには、IF文が使えます。
最頻値なら
=MODE.SNGL(IF(A2:A13 = "B001",B2:B13))
中央値なら
=MEDIAN(IF(A2:A13 = "B001",B2:B13))
のようにすると、それぞれ以下のような答えが表示されます。
今の僕の自宅PCのEXCEL（2016）だと、このようにうまくいきます。
でも、うまくいかない環境もあります。
例えば、仕事先でPCをお借りしたときに、普段使っているバージョンと異なるEXCELを使ったときに、IFの条件文が有効に働かず全体の最頻値・中央値が取得されてしまったりします。
EXCELの「配列数式」
うまくいったり、いかなかったりする現象のキーワードは「配列数式」です。
どうも、上記のようにMODEやMEDIANの中にIF文を書くような式は「配列数式」というものが関係しているようなのです。
配列数式は「 Ctrl + Shift + Enter キー」を押して入力します。
上記の例だと例えば数式バーの
のおしりのところにカーソルをあてた状態で「 Ctrl + Shift + Enter キー」を押します。
すると。
のように「{}」で式全体が囲まれた感じになります。
これが「配列数式」なのですが、この「{}」を手で入力してもダメですし、上記の状態になっていても、再度そこにカーソルをもっていくと解除されてしまったりするので結構扱いが面倒でもあります。
ただ、はっきりしていることはMODEやMEDIANの中でIF文を使って条件に一致したものの中だけで処理させようとしてうまくいかない場合は、このように「配列数式」にしてやれば、ちゃんと動くということです。
まあ、覚えておいて損はありません。
なぜうまくいく場合といかない場合があるのか・・については、マイクロソフトのサポート文書に以下のような記述があります。
バージョンの違いということみたいですね。
現在のバージョンのMicrosoft 365がある場合は、出力範囲の左上のセルに数式を入力し、Enter キーを押して数式を動的配列数式として確認するだけです。
それ以外の場合、最初に出力範囲を選択し、出力範囲の左上のセルに数式を入力し、CTRL+SHIFT+ENTERキーを押して確定し、従来の配列数式として数式を入力する必要があります。 Excel によって、数式の先頭と末尾に中かっこが挿入されます。
おまけ：MODE.MULTIについて
最近のEXCELには最頻値（MODE）には２つのバリエーションがあります。
- MODE.SNGL：最頻値が複数あっても最初のひとつのみ取得する
- MODE.MULT：最頻値が複数あったらすべて取得する
なのですが、MODE.MULTも「配列数式」前提の関数です。
配列数式が「Enter」キーだけで有効になる環境、もしくは、「 Ctrl + Shift + Enter キー」を押して明示的に「配列数式」を有効にした場合だと、最初の例
だと
=MODE.MULT(B2:B13)
で
のように４つある最頻値すべてが取得できます。
これも「配列数式」が有効でない状態で動かすと、MODE.SNGLとまったく同じ動きをしてしまうので、違いがよくわからない・・なんてことになります。
今回はこんなところで。
ではでは。
