エクセル又はOpenOfficeで条件付き最小値を抽出する方法
- エクセル又はOpenOffice.orgで条件付きの最小値を抽出する方法について質問があります。
- 質問者はOpenOffice.orgのCalcを使用しており、条件付き最小値を求める数式を調べて試していますがうまくいきません。
- 最大所要時間は「=SUMPRODUCT(MAX((B4:B100>=0)*A4:A100))」で求められましたが、最小所要時間を求める方法についてアドバイスを求めています。
- ベストアンサー
エクセル又は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関数も考えましたが、条件を割り当てるために不要なセルを作成しなければならないので、避けたいと思いまして。 最小時間が求まれば完成という段階なので、できれば数式で対応したいです。 色々と制約があって申し訳ないのですが、お知恵をお貸しいただけると助かります。 よろしくお願いいたします。
- nekobucho
- お礼率75% (6/8)
- オフィス系ソフト
- 回答数6
- ありがとう数4
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
>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))
その他の回答 (5)
- mt2008
- ベストアンサー率52% (885/1701)
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の調子が悪かったのか、私のやり方がまずかったのかわかりませんが、とにかく最終的にはうまくいきました。 本当に助かりました。 今回皆さんに回答を頂いた事でとても勉強になりました。 ありがとうございました。
- mt2008
- ベストアンサー率52% (885/1701)
今更の補足願いですが、最大値、最小値が複数登場する時はどうするのでしょう? 先に登場した方、後?それとも複数登場は無い?
- MackyNo1
- ベストアンサー率53% (1521/2850)
>Calcは配列数式に制限があり、IFやINDEXが含まれる配列数式は使えないらしく 私の回答に対する返答がないので、Web検索をしてみたところ、CalcでもCtrl+Shift+Pagedownで配列数式にできるようですね。 またIF関数を含む数式は配列数式として認識できないようですね。 私の提示したSMALL関数の数式の中で、もしINDEX関数で配列を範囲に変換する部分が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とエクセルの違いを全て把握しきれていないので、 もしかしたらその違いが原因でうまくいかないのかもしれませんね。 私の知識不足でお手数おかけしてしまって申し訳ありません。 でも今回頂いた回答で、数式の組み立て方や考え方など、とても参考になりました。 ただ今の私のレベルでは理解するまでに少し時間がかかりそうです・・・。すみません。 他にも回答を頂いているので、そちらもあわせてちょっと整理させて下さい。 また疑問がでてきたら質問させていただくかもしれませんが、その時は宜しくお願いいたします。 ありがとうございました。
- MackyNo1
- ベストアンサー率53% (1521/2850)
質問のタイトルがエクセルでもOKのようなので、参考までにエクセルで表示できる例を示します。 >似たような質問はたくさん見つかりましたが、配列数式を使用するものばかりでした。 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関数で配列を範囲に変更できないなら(または配列数式そのものが入力できない仕様なら)、補助列なしには対応できないと思います。
添付図参照 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のセルは不要なのですが、このような方法なら計算も単純化できますね。 今後はそういった点もふまえて表の組み立てを考えて行こうと思います。 今回は、知りたかった事とちょっと違いましたが、今後につながるという意味ではとても参考になりました。 ありがとうございます。
関連するQ&A
- 《エクセル2000》配列数式とMIN関数を使い、最小値を出したいのですが
いつもお世話になっております。 配列数式とMIN関数を使って最小値を出す作業をしていますが、最小値は0.3なのに0.0と返って来てしまいます。 条件は「A列が1で、B列が数字だったら」なのですが、B列には空白も入っています。 B列の空白のセルは、ISNUMBER関数でチェックするとFALSEが返ってくるのですが、もしや空白を0とみなしているのでしょうか。 COUNTIFのMIN版と言いますか、条件つきで最小値を出せる関数があればいいのですが…原因と対策をご存じの方、どうかご教示を。
- ベストアンサー
- オフィス系ソフト
- エクセルに関しての質問です!
図のようにB13にB列の最大値を求めたいのですが、 A列が"■"だった場合の条件付で最大値を求めたいです。 B13にどうのような数式を使えばよろしいでしょうか? よろしくお願いします┏O ※B13=MAX(INDEX((A1:A11="■")*B1:B11,)) という回答を別の質問スレで頂いたのですが、なぜかエラーになってしまいます。 OpenOffice.org3.1を使っていますが、エクセルだといけるのにOpenOfficeだとエラーになってしまうのでしょうか・・・
- 締切済み
- オフィス系ソフト
- 複数条件で最小値を求める
Excel2007です。 複数の条件をつけて最小値を求めたいです。配列数式を使います。 A列に体重、B列に性別、C列に住所だとして 男で東京都と神奈川という条件の中から体重の最小値を求める時、 {=MIN(IF((B1:B100="男")*((C1:C100="東京")+(C1:C100="神奈川")),A1:A100)}でCtrl+Shift+Enterで確定しました。 実際のデー量はもっと多いです。 たぶんきちんと計算されているようなのですが、自信がありません。 正しい数式になっているでしょうか? マクロは使わない方法でお願いします。
- ベストアンサー
- オフィス系ソフト
- 複数条件で最小値を求める
Excel2007です。 複数の条件をつけて最小値を求めたいです。配列数式を使います。 A列に体重、B列に性別、C列に年齢だとして 男で20歳~25歳という条件の中から体重の最小値を求める時、 {=MIN(IF((B1:B100="男")*((C1:C100="20")+(C1:C100="21")+(C1:C100="22")+(C1:C100="23")+(C1:C100="24")+(C1:C100="25"))),A1:A100)} としてみたのですが、うまくいきません。 おそらく、((C1:C100="20")+(C1:C100="21")+(C1:C100="22")+(C1:C100="23")+(C1:C100="24")+(C1:C100="25"))が違うのだと想像はつくのですが、どうすればきちんとでるのかわかりません。 マクロは使わない方法でお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル 条件つきで最小値を求めたいです
A列に200名を超える生徒の名前が入っています。 B列に計算テストの結果が入っています。 計算テストは何度も行われており、A列に何度も同じ生徒の名前が出てきます。A列に入っている生徒の名前は1000を超えます。 A B 田中 42 佐藤 48 鈴木 52 佐藤 56 田中 72 鈴木 60 鈴木 23 などです。この中で鈴木君のとった得点の最小値は23点ですが、それを求める数式を知りたいです。 『rankif』とか『minif』みたいな関数があればいいのですが、ないですよね。ちなみに、私が使用しているパソコンはexcel2000です。 他のシートやC列から右に生徒を判別する関数を入れていけば、私の知っている知識でも可能なのですが、関数を多用すると重くなりますし、避けたいです。また、もともとのデータの入力の仕方を変えたらいいのですが、それはできない状態です。 =MIN(IF(A2:A7="鈴木",B2:B7),false) みたいな感じかなと式を立てたのですが、うまくいきません。上の状態で、セルに23と表示されるには、どうすればいいか教えてください。お願い致します。m(_ _)m
- ベストアンサー
- オフィス系ソフト
- 最大値最小値に色付けする。
エクセル2013で縦に月を横に時間の項目をとりたデータがあります。 対象データを範囲指定し列ごとに最大値、最小値にセルに色付けをする方法は無いでしょうか。 ちなみに行毎に色づけするのは 最大値は=A1=MAX(INDIRECT("A"&ROW(A1)&":F"&ROW(A1))) 最小値=A1=MIN(INDIRECT("A"&ROW(A1)&":F"&ROW(A2))) で出来るのですが、列毎に表示するにはどのような関数を使えば出来るのでしょうか。 また、条件付き書式設定で=b2=max(b2:b13)の数式を入れて隣にコピーするときに絶対値になっており範囲指定しなおす必要があるので表を範囲指定し数式を入れたい。
- ベストアンサー
- Excel(エクセル)
- openoffice calc で、IF関数
openOffice calc の IF関数についての質問です。 A列のA1からA20のセルに(A,B,C,D)が、ランダムにあるとします。 これを、A列のセルの値が”A”ならば、返す値は”あ”、”B”ならば、”い”、同じように「C→う」「D→え」 のように、値に応じた、答えをB列に出させるにはどのような関数と式を使えば良いのでしょうか? 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 最大値、最小値の求め方
たびたびすみません。 A列 B列 あ 1 え 2 い 4 あ 0 い 0 あ 2 か 3 上記の列から"あ"以外の0を除いた最小値を求めたく MIN(IF(OR(A1:A7="あ",B1:B6<>0),"",B1:B6)) で数式配列にしたのですが、 うまくいきません。 単純なことの様な気するのですが、 ご指摘いただけないでしょうか?
- ベストアンサー
- オフィス系ソフト
- Excelで最大値最小値の検出
皆さん こんばんは。 データのExcel配列は下記のようになっています。空白セル(数は不定)を境目に異なるグループに分け、各グループの最大値あるいは最小値を(グループごとに計算すればできますが、データの量が多いので手間かかります)一遍で検出したいですが、何かいい方法はないでしょうか。皆さん教えてください。宜しくお願い致します。 A列 B列 C列 D列 (時間) (データ) (最大値) 〈最小値) 0:00 7.316784186 0:05 7.178492184 0:10 7.031467139 0:15 0:20 0:25 0:30 4.878174647 0:35 3.402687629 0:40 2.051343872 0:45 0.420805671 0:50 0:55 2.175188612 1:00 2.849337126 1:05 3.256652642 1:10 1:15 4.427495186 1:20 1:25 1:30 1:35 6.008051928 1:40 6.773041277 ・ ・ ・ ・ ・ ・ ・ ・
- 締切済み
- FX・外国為替取引
- エクセルで条件に合った最大値を求めるには
A B 7 5 8 6 8 9 7 4 上記のような配列で数値が入力されています。 A列が7の時のB列の最大値(今の場合は5)を求めたいのですが 何かいい関数はないでしょうか? =MAX(IF(A2:A5="7",B2:B5,0)) でやろうとしたのですがうまくいきませんでした。
- ベストアンサー
- その他(業務ソフトウェア)
お礼
早々にご回答いただきありがとうございます。 こちらの数式で無事に解決いたしました。 別シートを作成しての検証作業中に、今までちゃんと計算してくれていたものが急にエラーになったりしてちょっと検証に手間取っておりました。 Calcの調子が悪かったのか、私のやり方がまずかったのかわかりませんが、とにかく最終的にはうまくいきました。 本当に助かりました。 ありがとうございました。
補足
補足ではありませんが、一言追加させていただきます。 皆さんをベストアンサーにしたいのですが、今回はsige1701様の「=SUMPRODUCT(MIN((B1:B100>=0)*1+A1:A100))」という数式が一番わかりやすかったので選ばせていただきました。