Excel。最低点を知りたい。だけど、0点は除いてね。って昔は大変だった【MINIFS】

 Excel。最低点を知りたい。だけど、0点は除いてね。って昔は大変だった

＜MINIFS関数＞

得点表があって、最低点が何点なのかを知りたければ、MIN関数を使えば、あっという間に算出することができます。

ただ、0点は除いてほしいという条件が付いてしまうと、昔は大変だったなぁ～と思うわけですね。

なんで、「昔は…」というのかというと、最近、MINIFS関数というとても便利な関数が登場したからなんですね。

MINIFS関数は、とても便利な関数で、今までMIN関数では苦手だった、「男性の参加者で最年少は何歳？」という条件がついた最低値を簡単に算出してくれます。

さて、次の得点表があります。

 

2名も0点がいますね。これを除いた最低点を算出していきます。

D2にMINIFS関数をつかって算出していきます。

手入力でも簡単に数式は作れますが、説明の為、MINIFS関数ダイアログボックスを使っていきます。

 

最小範囲ですが、これは、最小値を算出したい範囲ですから、B2:B11。

オートSUMボタンにある、最小値を算出できるMIN関数は、ここまでしかできなかったわけです。

条件をつけることができなかったのですが、MIN+IFSということで、複数条件に対応したのが、この「MINIFS関数」です。

条件範囲1は、B2:B11。

条件1は、”>0”。

「0（ゼロ）」より大きい数値が条件になりますので、”>0”という条件を設定します。

あとは、OKボタンをクリックして確認してみましょう。

 

D2の数式は、

=MINIFS(B2:B11,B2:B11,">0")

SUMIFS関数とかと同じような感じで算出することができる関数なんですが、このMINIFS関数や、MAXIFS関数は、Microsoft365のExcelか、Excel2019でないと、登録されていない新しい関数なんですね。

なので、Excel2016まではどのような数式を作っていたかというと、

D3に数式を作ってみました。

 

=SMALL(B2:B11,COUNTIF(B2:B11,0)+1)

SMALL関数とCOUNTIF関数のコラボレーション技で算出させていました。

SMALL関数は、下から何番目の数値を算出するの？という関数です。

なんで、COUNTIF関数をつかっているのか？というと、0点が複数いる場合の対応です。

0点の人が何人いるのかをCOUNTIF関数で算出します。今回は「2」と算出されます。

この「2」でSMALL関数をつかってしまうと、下から2番目の人のデータということになってしまうので、結局2人目の「0点」の人が該当してしまうので、「+1」する必要があるので「+1」しております。

Excel2016までは、SMALL関数をつかうことがあるパターンの一つとして紹介するのに「0（ゼロ）を除外した」最低点の算出方法だったのですが、今は、MINIFS関数が登場したので、IFS関数が登場したことで三分岐のIF+IF関数が減っていくのと同様に、今後は、SMALL関数の出番が、また減ってしまった感じがしますね。

Excel。0（ゼロ）を除いた最小値の算出方法がMINIFSで楽勝になった【Exclude zero】

Excel。0（ゼロ）を除いた最小値の算出方法がMINIFSで楽勝になった

＜MINIFS関数　SMALL+COUNTIF関数＞

次のような表があります。

B列のデータから0を除いた最小値を算出しようとすると、
思っている以上に面倒というか簡単に算出することが意外とできません。

最小値を算出するには、おなじみのMIN関数を使うことを考えますが、
当然0が含まれていれば、0が算出されてしまいます。

つまり、「0（ゼロ）を除いて」という条件を
MIN関数では付けることができません。

また、SMALL関数を使えば算出できそうな感じもしますが、
これもプラスしたアイディアが必要になります。

LARGE関数ならば、1とすれば最大値を算出することが可能ですが、
0（ゼロ）を除いた最小値が何番目にあるのかがわからないと、
SMALL関数のみでは算出することができません。

そこで考え方としては、0（ゼロ）の件数を数えて、
その数をデータ件数から減算すれば、
0（ゼロ）を除いた最小値の場所を求めることができますので、
SMALL関数で算出することができます。

では、E2をクリックして、SMALL関数ダイアログボックスを表示しましょう。

配列には、B2:B11
順位には、COUNTIF関数をネストしますので、カーソルを表示して、
COUNTIF関数ダイアログボックスを表示します。

範囲には、B2:B11
検索条件には0
としてOKボタンをクリックします。

ただし、数式としては作成途中です。

E2の数式は、
=SMALL(B2:B11,COUNTIF(B2:B11,0))

となっていますが、この次の順位が0（ゼロ）を除いた場所にあたりますので、
+1を追加しますので、数式を次のように修正します。

=SMALL(B2:B11,COUNTIF(B2:B11,0)+1)

これで、0（ゼロ）を除いた最小値を算出することができました。

ちなみに、次のような数式でも算出することができます。

=LARGE(B2:B11,COUNTIF(B2:B11,">0"))

最小値というコトバから逆転の発想で、0（ゼロ）より大きい件数を算出して、
大きい順でその算出された数値のところが、
0（ゼロ）を除いた最小値ということになります。

”+1”をしなくていいメリットはありますが、わかりにくいかもしれません。

ところが、Office365で追加されたMINIFS関数が登場して、
数式を作るのが簡単になりました。

【Office365では、MINIFS関数】

E3をクリックして、MINIFS関数ダイアログボックスを表示します。

このMINIFS関数ですが、引数の作り込みが、イマイチで、なぜか、
最小値なのに、最大値と表示されています。

最大値（最小値）には、B2:B11
条件範囲1には、B2:B11
条件1には、”>0”
比較演算子を引数内で使うときには、
ダブルコーテーションを忘れないようにしましょう。

では、OKボタンをクリックします。

このように簡単に算出することができました。

そりゃそうですね。
MIN関数に条件検索ができるようになった、
MINIFS関数なら0（ゼロ）以外という条件を付けるだけですからね。

なお、E3の数式は、
=MINIFS(B2:B11,B2:B11,">0")

ということで、Office365ならば、MINIFS関数をはじめ、
プラスされた関数を使うことで、
今まで面倒だったものが改善できるかもしれませんね。

Excel。条件付き最大値を求めることが出来る関数がoffice365のExcel2016で登場【MAXIFS】

Excel。条件付き最大値を求めることが出来る関数がoffice365のExcel2016で登場

＜MAXIFS関数・MINIFS関数＞

Excelで、最大値や最小値を算出するには、
MAX関数とMIN関数を基本的に使っていますね。

オートSUMボタンにあるぐらいに、
ビジネスでは頻度が高く使用していると思いますが、
このMAX関数やMIN関数は、
条件をつけた途端に使えなくなってしまう関数だったのですが、
MAXIFS関数・MINIFS関数というのが登場しました。

ただ残念ながら、office365のExcel2016に追加されたので、
通常のExcel2016には追加されておりませんので注意してください。

【MAXIFS関数】

次の表があります。

B列には店舗名。C列は東京か神奈川のブロック名。
D列は大型店なら○。E列は売上高という表ですね。

H3に大型店でブロックが東京の売上高の最大値を算出したいという表です。

このような条件に対応しようとすると、
オートフィルターを使うなど、アレコレ考えないといけなかったわけですが、
office365のExcel2016に追加された『MAXIFS関数』を使えば
とても簡単に算出することが出来てしますのです。

H3をクリックして、MAXIFS関数ダイアログボックスを表示しましょう。

最大値には、$E$2:$E$11
ここには、最大値を見つけたい範囲を設定します。

H4の神奈川もオートフィルで数式をコピーして算出させるので、
絶対参照を設定しています。

条件範囲1には、$C$2:$C$11
順番は前後して構いませんが、ここでは、東京かどうかを判断させますので、
ブロック名を範囲選択しています。
ここも絶対参照を設定しています。

条件1には、G3。
G3には東京と入力されています。
特にオートフィルで数式をコピーしないようならば、
直接”東京”と入力してもOKですね。

条件範囲2には、$D$2:$D$11
大型店かどうかを判断するための範囲選択ですね。
ここも絶対参照を設定しておきます。

条件2には、”○”
○があるセルを検索するということですね。

あとは、OKボタンをクリックして、
オートフィルで数式をコピーしてみましょう。

簡単に算出することが出来ましたね。

ちなみにH3の数式は、
=MAXIFS($E$2:$E$11,$C$2:$C$11,G3,$D$2:$D$11,"○")
なお、この引数の条件数は、最大126個まで設定できますが…126個って。

形としては、SUMIFSとかの～IFSと同じような引数をもっていますので、
わかりやすい関数ですね。

続いて、MINIFS関数も確認しておきましょう。

【MINIFS関数】

I3をクリックして、MINIFS関数ダイアログボックスを表示しましょう。

引数は同じですので、MAXIFS関数がMINIFS関数に変わっただけですね。

しかしなぁ～、引数。
最大値じゃなくて、MINIFSだから、最小値なんだろうけどね。

まぁ、office365のExcel2016なので、
今後修正されるかもしれませんね。

ということで、I3の数式は、
=MINIFS($E$2:$E$11,$C$2:$C$11,G3,$D$2:$D$11,"○")

オートフィルで数式をコピーすると、完成しましたね。

Office365のExcel2016に他にも、
新しい関数が追加されていますので、確認しておくといいかもしれませんね。