MINIFS関数の使い方について

Question

はじめまして。

MINIFS関数の使い方について、皆様のお知恵を借りできればと思います。

例

(各セルとその入力値)

A1：2、C1：5、E1：8、F1：13、H1：18、K1：任意の値を入力できる

このような前提のもと

L1セルに「A1、C1、E1、F1、H1の値の中で、K1を超える最小値」を表示させる方法は

あるでしょうか。

(たとえば、K1に１を入力すると2、7を入力すると８、20を入力すると#N/Aなどが表示される)

検索範囲は上記のように「連続ではない」と考えてください。

MINIFS関数を用いようと考えたのですが

・引数1(最小範囲)で不連続範囲を指定できない

・引数3(条件指定)でセル番地を用いた指定(>=K1}ができない

状況に陥っています。

もしよろしければ、ご教示ください。

Answer 1

こんにちは。横合いから失礼します。

セルに表示される値は、「文字列」（空白の文字列を含む）か「数値」に大別されますので、規則性のない飛び飛びのセル範囲と考えずに、連続したセル範囲の中で「数値」の入っているセルを比較対象とする、というように考えてみてはいかがでしょう？。

下記は、この考え方を基に作成した数式の一例です。MINIFS 関数は使っていませんので、ご了解のほど。

仮に、A1:J1 のセル範囲を比較対象としています。

=IF(SUM(ISNUMBER(A1:J1)*(A1:J1>K1))=0,"範囲外",MIN(IF(ISNUMBER(A1:J1)*(A1:J1>K1),A1:J1)))

※この数式は入力後（あるいはコピー・貼り付け後）に Ctrl＋Shift＋Enter で確定して配列数式にする必要があります。

※配列数式になると、自動的に数式全体が { 　 } で囲われますので、必ずそうなっていることを確認してください。

＜数式の動作概要＞

A1:J1 のセル範囲内で、「数値」が入っているセルの配列（ TRUE ／ FALSE ）と、K1セルの値より大きな値のセルの配列（ TRUE ／ FALSE ）との積を取った配列（ 1 ／ 0 ）を作り、これを IF 関数で実際の値の配列（ セルの値 ／ FALSE ）に変更し、その中の最小値を表示させています。

尚、セル範囲内に K1セルの値より大きな値のセルが一つも無い場合には "範囲外" と表示させるようにしています。

図１は、色々なデータを入れた表を作成し、上記の数式を L1セルに入れた結果です。

※ B1セルには空白の文字列「 ="" 」を入れ、D1セルには何も入力していない状態にしています。

・図１：K1セルを区別するため背景色を黄色にしています。

Excel2019 は持ち合わせていないため、Windows10 と Excel2016 の組み合わせ、及び、Web 版の Excel（最新版の Excel ）で動作確認しています。

こういった関数を組み合わせた数式を使う方法もご希望ではないのかもしれませんが、よろしければ、お試しになってみてください。

ご参考になれば幸いです。

＜文章の修正＞

＜数式の動作概要＞の文章について、省略しすぎて意味不明な説明になってしまっている箇所がありましたので、修正いたしました。大変失礼いたしました。

＜追記＞

もしも、指定したセル範囲内の「数値」の全てを比較対象としたくはない、といった場合には、マスク用の配列（ 1 ／ 0 ）を作り、この配列と上記数式の ISNUMBER(A1:J1) の箇所を入れ替えれば可能です。（マスクという言葉は、意味が分かりやすいように便宜的に使っています。以下同様。）

例えば図１で、C1セルと H1セルの値のみを比較対象としたい場合、マスク用の配列は {0,0,1,0,0,0,0,1,0,0} となります。

=IF(SUM({0,0,1,0,0,0,0,1,0,0}*(A1:J1>K1))=0,"範囲外",MIN(IF({0,0,1,0,0,0,0,1,0,0}*(A1:J1>K1),A1:J1)))

※この数式も入力後（あるいはコピー・貼り付け後）に Ctrl＋Shift＋Enter で確定して配列数式にする必要があります。

※配列数式になると、自動的に数式全体が { 　 } で囲われますので、必ずそうなっていることを確認してください。

マスク用の配列の数字の意味は、「 0 」比較対象としない（マスクする）、「 1 」比較対象とする（マスクしない）、ということを意味しています。

また、この場合の数字の並んでいる順番は、左端から A1セル,B1セル,・・・,J1セルに対応しています。

尚、マスク用の配列の要素数は、指定したセル範囲（ A1:J1 ）のセルの個数に合わせ、この場合ならば 10個にします。

ただし、こういった方法は、マスク用の配列の要素数が多くなってくると、マスク位置の指定を間違えやすくなるので実用的ではなくなってきます。

ご参考まで。

Answer 2

​MS251 さん、こんにちは。
マイクソロフト コミュニティをご利用いただきありがとうございます。

不連続の範囲で条件を指定した最小値を表示させたいということですね。

・引数3(条件指定)でセル番地を用いた指定(>=K1}ができない
こちらは、">"&K1​ と引数を指定すれば可能です。

ただ色々と試してみましたが、不連続の範囲、条件指定のどちらかであれば達成することはできるのですが、１つの式では両方をかなえることは難しいのではないかと思います。

今回の場合では、１段階目に別の連続したセル範囲に不連続な参照したい範囲を参照させる。
２段階目にその連続したセル範囲を使って式を作る。
というような 2段階に分ける方法であれば可能でした。

例えば、A5 に =A1、B5 に =C1、C5 に =E1、と入力する。
この状態であれば、A1、C1、E1 の不連続の範囲が、A5:C5 に連続しているので、以下のように連続した範囲として指定することが可能になります。
=minifs(A5:C5,">"&K1​) 

希望に沿った回答ではないと思いますが、少しでもお役に立てば幸いです。
他にこちらについてよい方法を知っているという方がいらっしゃいましたらコメントをお待ちしています。

Answer 3

ご助言ありがとうございました。

MINIFS関数ではなく、配列数式を用いて処理するという発想はなかったので参考になりました。

今後、同様の事象が発生した場合には、試してみたいと思います。

Answer 4

ご助言ありがとうございました。

条件指定のところは、演算子のみ""でくくるとは知りませんでした。

今後の参考にさせていただきます。