Excel複数条件の最大・最少・平均・中央値

質問

自分で挑戦しましたが、どうしてもできなかったのでお力を貸してください。

　 A B
　体重（kg)　採血量（ｇ）
1 　 20.0　　　　 2
2　　4.5　　　　　2
3　　15.0　　　　1
4　　10.0　　　　3
5　　39.0　　　　1.4
6　　8.5　　　　　1.7
7　　6.0　　　　　2
8　　23.0　　　　1
9　　38.0　　　　7.5
10 　5.0　　　　　5.4
11　12.0　　　　　5
12　6.0　　　　　1.8

上記の表より
下記の条件で、それぞれの最大・最少・平均・中央値を出すには
どのようにすればよろしいでしょうか。

体重　～5kg
5.1kg～10kg
10.1kg～20kg
20.1kg～30kg
30kg以上

どうぞよろしくお願いします。

回答No.2

keithin · ベストアンサー

5.1kg～10kgを例に
最大
=MAX(IF((5<A2:A13)*(A2:A13<=10),B2:B13))
と記入しコントロールキーとシフトキーを押しながらEnterで入力
最小
=MIN(IF((5<A2:A13)*(A2:A13<=10),B2:B13))
と記入しコントロールキーとシフトキーを押しながらEnterで入力
平均
=AVERAGE(IF((5<A2:A13)*(A2:A13<=10),B2:B13))
と記入しコントロールキーとシフトキーを押しながらEnterで入力
中央値
=MEDIAN(IF((5<A2:A13)*(A2:A13<=10),B2:B13))
と記入しコントロールキーとシフトキーを押しながらEnterで入力

お礼

思わず、これでできるの!?と思ってしまいましたが…
できました！
これから活用させていただきます。
本当にありがとうございました。

回答No.4

kagakusuki

回答番号ANo.3です。
　関数と作業列を使用して、全自動で行う方法です。
　今仮に、元データの表と結果の表が存在しているシートがSheet1であり、Sheet2のA列とB列を作業列として使用するものとします。

　まず、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),RANK(INDEX(Sheet1!$A:$A,ROW()),Sheet1!$A:$A,1)+COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()-1),INDEX(Sheet1!$A:$A,ROW())),"")

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet1!$A:$A),"",INDEX(Sheet1!B:B,MATCH(ROWS($2:2),$A:$A,0)))

　次に、Sheet2のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet1の
D3セルに　　0
F3セルとD4セルに　　5
F4セルとD5セルに　　10
F6セルとD6セルに　　20
F7セルに　　30
と入力して下さい。
　次に、Sheet1のG3セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,">"&$D3)>COUNTIF($A:$A,">"&$F3),MAX(INDEX(Sheet2!$B:$B,COUNTIF($A:$A,"<="&$D3)+ROW(Sheet2!$B$1)+1):INDEX(Sheet2!$B:$B,IF($D3=MAX($D:$D),COUNT($A:$A),COUNTIF($A:$A,"<="&$F3))+ROW(Sheet2!$B$1))),"")

　次に、Sheet1のH3セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,">"&$D3)>COUNTIF($A:$A,">"&$F3),MIN(INDEX(Sheet2!$B:$B,COUNTIF($A:$A,"<="&$D3)+ROW(Sheet2!$B$1)+1):INDEX(Sheet2!$B:$B,IF($D3=MAX($D:$D),COUNT($A:$A),COUNTIF($A:$A,"<="&$F3))+ROW(Sheet2!$B$1))),"")

　次に、Sheet1のI3セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,">"&$D3)>COUNTIF($A:$A,">"&$F3),AVERAGE(INDEX(Sheet2!$B:$B,COUNTIF($A:$A,"<="&$D3)+ROW(Sheet2!$B$1)+1):INDEX(Sheet2!$B:$B,IF($D3=MAX($D:$D),COUNT($A:$A),COUNTIF($A:$A,"<="&$F3))+ROW(Sheet2!$B$1))),"")

　次に、Sheet1のJ3セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,">"&$D3)>COUNTIF($A:$A,">"&$F3),MEDIAN(INDEX(Sheet2!$B:$B,COUNTIF($A:$A,"<="&$D3)+ROW(Sheet2!$B$1)+1):INDEX(Sheet2!$B:$B,IF($D3=MAX($D:$D),COUNT($A:$A),COUNTIF($A:$A,"<="&$F3))+ROW(Sheet2!$B$1))),"")

次に、Sheet1のG3～I3の範囲をコピーして、Sheet1のG4～I7の範囲に貼り付けて下さい。

　以上です。

お礼

一つの質問にこんなにご丁寧に、しかも他のやり方までお答えいただきありがとうございました。
こちらはまだやっていませんが、落ち着いたところでぜひ試したみたいと思います。
関数は奥が深いということが改めて実感しました。

回答No.3

kagakusuki

中央値を求める方法だけ思い付きませんでしたが、関数のみで全自動で行う方法です。

　まず、D3セルに　　0
F3セルとD4セルに　　5
F4セルとD5セルに　　10
F6セルとD6セルに　　20
F7セルに　　30
と入力して下さい。
　次に、G3セルに次の関数を入力して下さい。

=SUMPRODUCT(MAX(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(999,$A:$A))*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))>$D3)*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))<=IF(ISNUMBER($F3),$F3,999)))*1)

　次に、H3セルに次の関数を入力して下さい。

=MAX($B:$B)+1-SUMPRODUCT(MAX((MAX($B:$B)+1-INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(999,$A:$A)))*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))>$D3)*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))<=IF(ISNUMBER($F3),$F3,999)))*1)

　次に、I3セルに次の関数を入力して下さい。

=SUMPRODUCT(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(999,$A:$A))*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))>$D3)*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))<=IF(ISNUMBER($F3),$F3,999)))/SUMPRODUCT((INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))>$D3)*(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(999,$A:$A))<=IF(ISNUMBER($F3),$F3,999))*1)

　次に、G3～I3の範囲をコピーして、G4～I7の範囲に貼り付けて下さい。

　尚、A列の体重の最大値が999kg以上（999kg丁度の場合も含む）ある場合には、各関数中の999の部分を、最大値を確実に上回る値に適時変更するか、或いは

MAX($A:$A)+1

に変更して下さい。

お礼

お答えいただくのに、時間を割いていただいたかと思うと本当に感謝です。
こういったやり方もあるんですね。
少しずつ理解しながら試していたら時間がかかりましたができました！
大変参考になりました。ありがとうございました。

回答No.1

tom04

こんばんは！
外していたらごめんなさい。

「中央値」は何を指すか判らないので、こちらで勝手に　MEDIAN関数で処理してみました。
MEDIAN関数の詳細については検索してみてください。

↓の画像のようにSheet2に表を作成しておきます。
（Sheet2のB列は必要ないのですが、判りやすくするために敢えて表示しています。）

Sheet1のC列を作業列としています。
作業列C2セルに
=IF(COUNTBLANK(A2:B2),"",VLOOKUP(A2,Sheet2!$A$2:$C$6,3,1))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

F2セル（配列数式になります。）は
=MAX(IF($C$1:$C$100=ROW(A1),$B$1:$B$100))
※　この画面からF2セルにコピー＆ペーストする場合はF2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

G2セル（これも配列数式）に
=MIN(IF($C$1:$C$100=ROW(A1),$B$1:$B$100))
としてShift＋Ctrlキー＋Enter

H2セル（配列数式ではありません！そのままコピー＆ペーストだけでOKです）
=SUMIF(C:C,ROW(A1),B:B)/COUNTIF(C:C,ROW(A1))

※　Excel2007以降のバージョンであればAVERAGEIF関数が使用できると思います。

I2セル（配列数式になります）に
=MEDIAN(IF($C$1:$C$100=ROW(A1),$B$1:$B$100))
としてShift＋Ctrl＋Enter

最後にF2～I2セルを範囲指定し、I2セルのフィルハンドルで6行目までオートフィルでコピーすると
画像のような感じになります。

※　「中央値」に関しては自信がありません。

参考になれば良いのですが・・・m（＿ ＿）m

お礼

いち早くお答えいただきありがとうございました。
ご記入いただいた通りやってみたらできました！
大量のデータを処理するので大変助かります。