エクセル又はOpenOfficeで条件付き最小値を

質問

エクセル又はOpenOffice.orgで条件付きの最小値を抽出する数式

当方OpenOffice.orgのCalc使用です。
関数も勉強し始めたばかりで、まだまだ理解しきれていませんが、宜しくお願いいたします。

下記の条件で最小値を出す数式を色々調べては試しているのですが、中々うまくいかないので質問させて下さい。

　　A(所要時間)　　B
1　 01:00:00　　　 04.5
2　 00:30:00　　　 20.5
3　 02:10:00　　　-10.0
4　 01:40:00　　　 07.5
5　 00:25:00　　　-05.0
6　 00:45:00　　　 13.5
7　 00:15:00　　　-03.5
8　 01:05:00　　　 18.0

上記表からB列の値がプラス（つまり0以上）の中の最大所要時間（この場合01:40:00）と最小所要時間（00:30:00）、
B列の値がマイナス（0以下）の中の最大所要時間（02:10:00）と最小所要時間（00:15:00）をそれぞれ求めたいと思っています。

最大所要時間は「=SUMPRODUCT(MAX((B4:B100>=0)*A4:A100))」で求められましたが、MINではダメでした。
SMALLを使うのかもと思い調べてみましたが、だんだん混乱してしまって・・・

似たような質問はたくさん見つかりましたが、配列数式を使用するものばかりでした。
Calcは配列数式に制限があり、IFやINDEXが含まれる配列数式は使えないらしく、苦労しています。

DMIN関数も考えましたが、条件を割り当てるために不要なセルを作成しなければならないので、避けたいと思いまして。
最小時間が求まれば完成という段階なので、できれば数式で対応したいです。

色々と制約があって申し訳ないのですが、お知恵をお貸しいただけると助かります。
よろしくお願いいたします。

回答No.4

sige1701 · ベストアンサー

>Calcは配列数式に制限があり、IFやINDEXが含まれる配列数式は使えないらしく、苦労しています。
Calcが無いので検証できませんが

「=SUMPRODUCT(MAX((B4:B100>=0)*A4:A100))」で求められるのですよね

=MAX((B4:B100>=0)*A4:A100) という配列数式　または
=MAX(INDEX((B4:B100>=0)*A4:A100,)) ではだめだということですね

SUMPRODUCT関数が使用可能として
以上、以下、未満、超　のく区別が微妙ですが

>B列の値がマイナス（0以下）の中の最小所要時間（00:15:00）
=SUMPRODUCT(MIN((B1:B100>=0)*1+A1:A100))

B列に　未入力や0がある場合
=SUMPRODUCT(MIN(((B1:B100>0)+(B1:B100=""))*1+A1:A100))

お礼

早々にご回答いただきありがとうございます。
こちらの数式で無事に解決いたしました。

別シートを作成しての検証作業中に、今までちゃんと計算してくれていたものが急にエラーになったりしてちょっと検証に手間取っておりました。
Calcの調子が悪かったのか、私のやり方がまずかったのかわかりませんが、とにかく最終的にはうまくいきました。

本当に助かりました。
ありがとうございました。

補足

補足ではありませんが、一言追加させていただきます。
皆さんをベストアンサーにしたいのですが、今回はsige1701様の「=SUMPRODUCT(MIN((B1:B100>=0)*1+A1:A100))」という数式が一番わかりやすかったので選ばせていただきました。

回答No.6

mt2008

ANo.5です。
先ほどの補足願いは、質問を勘違いしておりましたので無視してください。

プラスの最小所要時間　　=SUMPRODUCT(MIN(A1:A100+99999*(B1:B100<=0)))
マイナスの最大所要時間　=SUMPRODUCT(MAX((A1:A100)*(B1:B100<0)))
マイナスの最小所要時間　=SUMPRODUCT(MIN(A1:A100+99999*(B1:B100>0)))

こんな感じでいかがでしょう。
OOo3.1で確認しました。

お礼

早々にご回答いただきありがとうございます。

前の方の回答でもうまく行きましたが、
こちらの数式でも大丈夫でした。

別シートを作成しての検証作業中に、今までちゃんと計算してくれていたものが急にエラーになったりしてちょっと検証に手間取っておりました。
Calcの調子が悪かったのか、私のやり方がまずかったのかわかりませんが、とにかく最終的にはうまくいきました。
本当に助かりました。

今回皆さんに回答を頂いた事でとても勉強になりました。
ありがとうございました。

回答No.5

mt2008

今更の補足願いですが、最大値、最小値が複数登場する時はどうするのでしょう？
先に登場した方、後？それとも複数登場は無い？

回答No.3

MackyNo1

＞Calcは配列数式に制限があり、IFやINDEXが含まれる配列数式は使えないらしく

私の回答に対する返答がないので、Ｗｅｂ検索をしてみたところ、CalcでもCtrl＋Shift＋Pagedownで配列数式にできるようですね。
またＩＦ関数を含む数式は配列数式として認識できないようですね。

私の提示したSMALL関数の数式の中で、もしＩＮＤＥＸ関数で配列を範囲に変換する部分がCalcでは対応できないということなら、この部分を削除した以下のような関数にして、Ctrl＋Shift＋Pagedownのキー操作でうまくいかないか調べてみてください。

=SMALL((B4:B100>=0)*A4:A100,COUNTBLANK(B4:B100)+COUNTIF(B4:B100,"<0")+1)

お礼

早々に回答をいただきありがとうございます。
返事が遅くなり、申し訳ございません。
教えていただいた事を試したり理解するのに時間がかかっていました（汗）

その間に更に色々回答を頂いていたのですね。
本当にありがとうございます。

配列数式についてまだあまり理解しておらず、うまく伝えられずにすみません。
ご指摘の通り、IFやINDEXが含まれると「Ctrl+Shift+Enterの操作ができない」という事です。

最初に教えていただいた「=SMALL(INDEX～」の式では変数が足りない旨のエラーが出てしまいました。
「Ctrl＋Shift＋Pagedown」というのは初めて知りました。
新しく頂いたほうの式で「Ctrl＋Shift＋Pagedown」を試した所うまくいかず、
「Ctrl+Shift+Enter」で{}に囲まれた状態になったのですが、やはりエラーになってしまいました。
私もCalcとエクセルの違いを全て把握しきれていないので、
もしかしたらその違いが原因でうまくいかないのかもしれませんね。

私の知識不足でお手数おかけしてしまって申し訳ありません。
でも今回頂いた回答で、数式の組み立て方や考え方など、とても参考になりました。
ただ今の私のレベルでは理解するまでに少し時間がかかりそうです・・・。すみません。
他にも回答を頂いているので、そちらもあわせてちょっと整理させて下さい。

また疑問がでてきたら質問させていただくかもしれませんが、その時は宜しくお願いいたします。
ありがとうございました。

回答No.2

MackyNo1

質問のタイトルがエクセルでもＯＫのようなので、参考までにエクセルで表示できる例を示します。

＞似たような質問はたくさん見つかりましたが、配列数式を使用するものばかりでした。
Calcは配列数式に制限があり、IFやINDEXが含まれる配列数式は使えないらしく、苦労しています。

基本的に、例示のSUMPRODUCT関数も配列数式です。

エクセルでCtrl＋Shift+Enterで確定してもよいなら以下の式になります。

=MIN(IF((B4:B100<>"")*(B4:B100>=0),A4:A100,100))

ご質問の趣旨が、Ctrl+Shift+Enterの操作ができないということなら以下のような数式になります。

=SMALL(INDEX((B4:B100>=0)*A4:A100,),COUNTBLANK(B4:B100)+COUNTIF(B4:B100,"<0")+1)

上記の数式で表示できないなら、OpenOfficeには具体的にどのような制限があるのか提示してください。
例えば、上記の数式のINDEX関数で配列を範囲に変更できないなら（または配列数式そのものが入力できない仕様なら）、補助列なしには対応できないと思います。

回答No.1

noname#204879

添付図参照

C1:　=IF(B1>=0,A1,"")
C10: =MAX(C1:C8)
C11; =MIN(C1:C8)

D1:　=IF(B1<=0,A1,"")
D10; =MAX(D1:D8)
D11: =MIN(D1:D8)

範囲 C1:D8 が「不要なセル」ならば、この回答は無視してください。

お礼

早々に回答いただきありがとうございます。
ご指摘の通りC1:D8のセルは不要なのですが、このような方法なら計算も単純化できますね。
今後はそういった点もふまえて表の組み立てを考えて行こうと思います。
今回は、知りたかった事とちょっと違いましたが、今後につながるという意味ではとても参考になりました。
ありがとうございます。