エクセル MINIFS関数 応用

質問

MINIFS関数で最小値が複数あった場合、別フィールドにある最小値を返す方法を教えてください

よろしくお願いいたします。

回答No.6

bunjii · ベストアンサー

回答No.4に見落としがありました。
12歳のチェックが漏れていますので訂正します。
J2の数式
=MIN(INDEX((B2:B5="1組")*(F2:F5<>"")*G2:G5+((B2:B5<>"1組")+(F2:F5=""))*MAX(G2:G5),0))
　　　　↓
=MIN(INDEX((B2:B5="1組")*(F2:F5<>"")*(E2:E5=12)*G2:G5+((B2:B5<>"1組")+(F2:F5="")+(E2:E5<>12))*MAX(G2:G5),0))

I2の数式
=INDEX(F:F,MAX(INDEX((B2:B5="1組")*(F2:F5<>"")*(G2:G5=J2)*ROW(I2:I5),0)))
　　　　↓
=INDEX(F:F,MAX(INDEX((B2:B5="1組")*(F2:F5<>"")*(G2:G5=J2)*(E2:E5=12)*ROW(I2:I5),0)))

お礼

いろいろ貴重な情報を平易にご教示いただきありがとうございました。
まだ理解できていない部分が多いですが、頂いたアドバイスを元に理解を深めていこうと思います。本当にありがとうございました。

補足

以下について確認させてください。

>J2の数式
・Index関数内の"*"はどのような意味になりますか？配列形式における、配列、行番号、列番号を区切る","の役割でしょうか？
・F2:F5<>""の値が"2"と返される理由は？"0"ではないのでしょうか？
・上記解釈が正の場合、min関数で求められる数値は必ずしも1組で若い子(12歳の子)の中で、1番若い兄弟の年齢を求める式にならないのでは？

L2の数式にも質問があるのですが、一旦ここまで教えていただけませんでしょうか？

宜しくお願い致します。

回答No.10

Nouble

教材になる、サイトを
お探し、とか

私は、此方で
学びました

https://okwave.jp/qa/q8886106.html
のベストアンサーの方が参考情報に挙げられている場所です

尚、
雑誌、紹介ページに
リダイレクト、されたら
其れは、違います

他にも、少し
吟味して、みました

http://home.att.ne.jp/zeta/gen/excel/c01p09.htm

今一、ですが
http://www.clayhouse.jp/array/array01.htm

http://www.waseda.jp/ocw/ComputerScience/17-4003-01IntroductiontoITFall2003/StudyMaterials/root/document/emat.html

尚、此等で
紹介、されている
配列数式は
ほんの、入り口で

本来は
行列と、論地演算と、暗号化復号技術の、
融合的、応用技術です

一つの、値群に
演算に、より
より、多くの
情報を、詰め込み
また
復号を、する

其の、際に
行列や、論理演算、
其の、他を、
伴う

本来は
そういった、もの
です

他の、質問で
回答させて、頂いた
答えも
Small関数、Large関数、
を、用いた
開発、当初も

暗号化複合技術と
配列演算の、要素を
意識し
論理演算を、多用して

1つの、値群に
適合、不適合、
の、情報を
織り込ませ、られた
結果、です

故に、
此等が、解ると
有利、ですね

回答No.9

bunjii

>配列の演算結果が 3;2;0;0 となる部分が分かりません。
>参考になるサイトなどあれば教えていただけませんか？
次の説明が分からないと参考サイトで読んでも分からないですよ。
({1;1;1;0})*({1;1;1;1})*({1;1;0;1})*{3;2;1;1} → {3;2;0;0}
配列値の読み方を理解していないのではないですか？
{1;1;1;0} は1列4行の数値の塊で同じ1列4行の{1;1;1;1}と{1;1;0;1}と{3;2;1;1}を積算しています。4つの配列値（1列4行）を積算する過程は各行毎に積算した結果を1列4行の配列値として返します。
1行目 1*1*1*3 → 3
2行目 1*1*1*2 → 2
3行目 1*1*0*1 → 0
4行目 0*1*1*1 → 0
1行目から4行目までを纏めて表現すると {3;2;0;0} となります。

>除外したい検索結果をここに入れる認識であっていますか？
配列の論理演算の結果は {0;0;1;1} になるはずです。
それに MAX({3;2;1;1}) の結果 3 を掛けると {0;0;3;3} になります。
前項で確認された {3;2;0;0} と {0;0;3;3} を加算すればMIN関数へ渡す {3;2;3;3} が得られます。
MIN({3;2;3;3}) の結果は 2 になるでしょう。

>Ｅ列の数値は変動する予定です。12と数値を入れてしまうと、それより小さい数値が入った際に問題がでませんでしょうか？
E列の値と12歳を比較するのはあなたが言ったことで私の関知することではありません。
特定のセルに比較する年齢を入力するのであれば12の代わりにそのセルを指定すれば良いでしょう。

>ここにかかっている"*"は"＋"の意味？？
前の回答でも言っていますが数式内の数値と数値の間にある*は乗算記号であり加算記号の+とは異なります。
Excelで計算するときの算術記号を理解しないと何もできません。

>今回のようなIndex関数の使い方に関して参考になるようなサイトがあればご教示いただけますと幸いです。
Excel内に組み込まれたヘルプ機能で学習してください。
検索窓で質問すれば適当なサイトに導かれると思います。

回答No.8

msMike

お望みのMINIFSを使ってないし、恐らく誰も([フィルタオプションの設定]に慣れた人でも)提案しない手法だろうという意味での別解です。
私の忘備録としてアップしておくので、興味なければ無視して「お礼」を書く必要もありません。

添付図参照
上段左を Sheet1、下段を Sheet2 とします。

元データ範囲 Sheet1!A1:F8 に名前 dtable を付けておきます(簡単のため)。
なお、汎用性のために貴方のデータを増やしたり改竄したりしているけど、貴方の元データに置き換えて試してみてください。
ただし、Sheet1、Sheet2 の1行目は添付図のマンマで入力してください。
(例えば、Sheet1 のセル D1、F1 が同じ文字列「年齢」なんてのは厳禁です)

セル E5 に次式を入力(貴方が求める「若い兄弟の年齢を返してく」れます)
=MIN(IF((Sheet1!B$2:B$8=E$2)*(Sheet1!D$2:D$8=E$3)*(Sheet1!E$2:E$8<>""),Sheet1!F$2:F$8,""))
【お断り】上式は必ず配列数式として入力のこと

セル F3 に次式を入力（セル F2 は空白のまま←これ必須）
=AND(Sheet1!B2=E$2,Sheet1!D2=E$3,Sheet1!E2<>E$4,Sheet1!F2=E$5)

[フィルタオプションの設定]操作は必ず Sheet2 から、下記の条件で始めます。
抽出先→指定した範囲
リスト範囲→ dtable
検索条件範囲→ $F$2:$F$3
抽出範囲→ $A$1:$B$1

[OK]ボタンを「エイヤッ！」と叩き付けた結果を添付図下段の列Ａ、Ｂに示しています。

お礼

まだうまく再現できていませんが、改めてやってみてまた何かあれば相談させてください。
宜しくお願い致します。

回答No.7

bunjii

>Index関数内の"*"はどのような意味になりますか？
数式内の*は乗算記号です。
(B2:B5="1組")*(F2:F5<>"")*(E2:E5=12)*G2:G5
　　↓　各セル間の演算結果
({True;True;True;False})*({True;True;True;True})*({True;True;False;True})*{3;2;1;1}
　　↓　論理値を数値化すると
({1;1;1;0})*({1;1;1;1})*({1;1;0;1})*{3;2;1;1}
　　↓　数値化された配列の演算結果
{3;2;0;0}
論理演算の結果を数値化する方法として加減乗除を使うことができます。
また、[論理値]*[論理値]はAND([論理値],[論理値])と等価になり、[論理値]+[論理値]はOR([論理値],[論理値])と等価になります。

>F2:F5<>""の値が"2"と返される理由は？"0"ではないのでしょうか？
F2:F5<>""は配列値の論理演算なので{True;True;True;True}が返されます。
今回の例ではすべてTrueなのでチェックの必要が無かったようです。しかし、他の例では必要になるでしょう。
全てのチェック要件を集計すると配列値の{3;2;0;0}ですが、この配列値から最小値を求めると0になってしまいますので0を3以上（兄弟の年齢の最大値）に置き換えるために次の数式で補います。
+((B2:B5<>"1組")+(F2:F5="")+(E2:E5<>12))*MAX(G2:G5)
　　↓　論理値を数値化して表示
+(({0;0;0;1})+({0;0;0;0})+({0;0;1;0}))*3
　　↓　カッコ内の計算結果整理する
+({0;0;1;1})*3
　　↓　前述の{3;2;0;0}を補正するには
{3;2;0;0}+{0;0;3;3} → {3;2;3;3}
=MIN({3;2;3;3}) → 2
つまり、INDEX関数からMIN関数へ渡される配列値は{3;2;3;3}になります。

補足

>INDEX関数の第1引数（範囲）は2つの条件を論理式で前処理し、第2引数の0は第1引数を配列値のままMAX関数へ引き渡すための行番号です。

→Index関数の書式(範囲,行番号,列番号)の内、行番号を０とすることで"範囲"の配列の演算結果のみをMINに引き渡せるということですか？

>論理値を数値化すると
({1;1;1;0})*({1;1;1;1})*({1;1;0;1})*{3;2;1;1}
　　↓　数値化された配列の演算結果
{3;2;0;0}

→配列の演算結果が 3;2;0;0 となる部分が分かりません。
参考になるサイトなどあれば教えていただけませんか？

>+((B2:B5<>"1組")+(F2:F5="")+(E2:E5<>12))
除外したい検索結果をここに入れる認識であっていますか？

>E2:E5<>12
Ｅ列の数値は変動する予定です。12と数値を入れてしまうと、それより小さい数値が入った際に問題がでませんでしょうか？

>*MAX(G2:G5)
ここにかかっている"*"は"＋"の意味？？

質問ばかりですみません。
今回のようなIndex関数の使い方に関して参考になるようなサイトがあればご教示いただけますと幸いです。

宜しくお願い致します。

回答No.5

imogasi

#2です。
＃３の補足のデータを見ました。
初めから質問にこのような例が挙げられていないので。小生は誤解しました。
そのため、＃２は該当しない点があります。
取り下げます。
ーー
しかし補足のデータを見ても質問のデータの書き方が不明確な点があると思います。
（１）「1組に在籍し・・」は1組のデータはない。
「組単位で」という意味か？紛らわしい。
（２）兄弟の名前をB君とか書いているが、下の名前か？
氏名列の方は佐藤Ａ君とか書いているので紛らわしい。
（３）当初質問の「別フィールドにある最小値を返す」
というのであれば、
１組　佐藤Ａ君　　弟　　５才
１組　佐藤Ａ君　　弟　　４才
２組　佐藤Ａ君　　妹　　３才
というようなデータを加えて、（データ例を増やし）示して、その後こういう判定で、答え（結果は）はこうなるようにしたい、ということを文章で、示さないとわかりにくいと思う。
ーー
普通このタイプの例題は
具体的に氏名＝佐藤　で、かつ１組所属（組＝1組）の佐藤さんで、かつ兄弟=弟の年齢の最少という探索が多いが、佐藤さんや１組というのがわからない中で式を考えるのは、式がむつかしくなると思う。
どういう単位（まとまりで）で最少を考えるのか、よくわからなかった。
２０１３まではＭＩＮＩＦＳ関数がないので、配列数式でも使うのかな。
（関数が新設されたので）珍しくエクセルバージョンンを質問に書く必要の質問だ。

補足

エクセル関数の質問の仕方に不慣れだったもので、ご迷惑をおかけしてしまい申し訳御座いませんでした。アドバイス参考にさせていただきます。

回答No.4

bunjii

>模擬データを作成しましたのでご確認ください。
>・1組に在籍している12歳の子で兄弟がいる場合、1番若い兄弟の年齢を返してください。
模擬データがA1:G5の配置されたものとします。
抽出する年齢をJ2へ代入するものとすれば次のような数式で良いでしょう。
=MIN(INDEX((B2:B5="1組")*(F2:F5<>"")*G2:G5+((B2:B5<>"1組")+(F2:F5=""))*MAX(G2:G5),0))
INDEX関数の第1引数（範囲）は2つの条件を論理式で前処理し、第2引数の0は第1引数を配列値のままMAX関数へ引き渡すための行番号です。
INDEX((B2:B5="1組")*(F2:F5<>"")*G2:G5+((B2:B5<>"1組")+(F2:F5=""))*MAX(G2:G5),0) → {3;2;1;3}

>・可能であればその兄弟の名前も返してください。
可能です。
数式を省略化するためにJ2セルへ代入された値を参照します。
抽出する名前をI2セルへ代入するものとすれば次の数式で良いでしょう。
=INDEX(F:F,MAX(INDEX((B2:B5="1組")*(F2:F5<>"")*(G2:G5=J2)*ROW(I2:I5),0)))

私の環境はWindows 10のExcel 2013なのでMINIFS関数を使えません。
従って、MINIFS関数の代わりにINDEX関数とMIN関数（年齢のとき）またはMAX関数（名前のとき）を使いました。
MINIFS関数で同等の処理が可能か否かは検証できませんので、上記の論理が理解できればあなた自身で確認してください。

回答No.3

bunjii

>MINIFS関数で最小値が複数あった場合、別フィールドにある最小値を返す方法を教えてください
MINIFS関数はExcel 2016に追加された組み込み関数のようです。
MINIFS関数では条件に合う最小値が複数であるか否かが検出できません。
この関数の目的は複数の条件下で最小の値を求めているものなので複数が見付かっても問題ないのではないでしょうか？
MAXIFS関数も同じです。
あなたはどのようにして複数であることを知ったのですか？
他の関数で配列中の値を論理演算すれば最小値が複数であることを検出できます。しかし、数式が複雑になり処理速度にも影響します。
具体的な模擬データと条件を提示して頂ければ数式を提示できると思います。
質問の文面だけでは抽象的な表現なので回答しようがありません。

補足

ご返信をいただきありがとうございます。
説明が不足しておりすみませんでした。

模擬データを作成しましたのでご確認ください。

ID クラス 氏 名 年齢 兄弟 年齢
1 1組 佐藤 A君 12 B君 3
2 1組 加藤 C君 12 D君 2
3 1組 田中 D君 13 E君 1
4 2組 鈴木 F君 12 G君 1

・1組に在籍している12歳の子で兄弟がいる場合、1番若い兄弟の年齢を返してください。

・可能であればその兄弟の名前も返してください。

・MINIFSがエクセル2016からとの仕様とのことご教示いただきありがとうございました。

よろしくお願いいたします。

回答No.2

imogasi

エクセル関数を使う場合、結果が１つでないと、普通ではできないということを知ってますか。１関数は、（関数を入れた）１セルに対し、１つのセルの値を返す。しかしニーズはよくあるので、いろいろ、解決のやり方を紹介される。
関数向きのもんだいじゃないんだ。ＶＢＡなら配列にして返す、とか１行ずらして答えを返すとかできるのだ。
ーー
該当の行が複数あり（今回では最小値と同じ行が複数行ある）場合に
その最小値と同じ行をすべて取り上げてリスト（シート上に列挙という意味）したいということかな。当然結果も複数行セルを使う。
下記はＶＬＯＯＫＵＰ関数の例で、作業列を使っていますが、根底は同じようなニーズでしょう。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/fukusu_data.html
https://allabout.co.jp/gm/gc/297817/
など
Ｇｏｏｇｌｅで「エクセル関数　該当のものを複数返す関数」で照会の1つ２つの氏とです。
これらの記事を読んで、本質問では、関数は別になるが、やってみてください。
一種の「関数を使っての、条件による複数の該当行の抜出し問題」になります。
関数式が長く複雑になりますが。
このタイプの質問は,月１件前後質問がでます。
ーー
ＶＢＡも色々欠点はありますが、ＶＢＡで、各行の繰り返し判別法でやれば、何結果を出すセルを指定できるため、どうということはない問題になります。

回答No.1

msMike

例としｔ具体的なデータを埋めた表を示すことをお奨めします。
言葉だけで処理しようとすると間違いや誤解が出そうだから(例:MINIFS/IFMINS)

補足

アドバイスありがとうございます。捕捉させていただきました。