プロが教えるわが家の防犯対策術!

集計方法について教えてください。
[前提]
約20,000件のデータがあり、購入世代・購入時間・購入品数を組み合わせた区分別の利益率を分析しております。

それぞれのデータには、購入世代・購入時間・購入数を区分したもの(下記)と、その区分にあてはまる顧客の利益率がのっています。

データの明細
購入世代 購入時間 購入品数 利益率  区分
1 20~35(歳)  14:20   8    7.2%   B03(関数で自動判別させている)

区分表
A01 20歳未満/購入6:30~8:59/購入品数3点未満
A02 20歳未満/購入9:00~11:29/購入品数3点未満
A03
・・・・
A20
B01
B02
B03
・・・・
と、A01~A20、B01~B20・・・と、D20まで80の紐づけ区分をもっています。
20,000件のデータにおける、それぞれの区分にあてはまる利益率のうち、上位5%、下位5%を抜く値とする平均値を導き出すには、どのような関数が必要ですか?教えてください。
trimを使うと、一定のデータ区間内は算出できますが区分けした後に異常な値を抜く公式に苦労しています。もう少しやり方を変えればできるのかもしれないのですが、よろしくお願いします。

A 回答 (2件)

関数を考えるよりは、作業列を作って、TRIMMEANで計算出来るように作業列にデータを引っ張ってくるという方式が現実的かもしれません。


一つの関数式を使いたいのであれば、関数を組み合わせることになると思います。配列数式を入力するという方法を組み合わせると、似たことができるかもしれません。
https://support.office.com/ja-jp/article/%E9%85% …
https://support.office.com/ja-jp/article/TRIMMEA …

L3=H3-INT(H3*0.05)*2
M3=TRIMMEAN(IF($E$3:$E$22800=G3,$D$3:$D$22800,""),0.1)
この数式を入力して、最後に Ctrl+Shift+Enterで、確定入力すると、式の前後に{}がつきます。
N3=LARGE(IF($E$3:$E$22800=G3,$D$3:$D$22800,""),1+INT(H3*0.05))
これも数式を入力して、最後に Ctrl+Shift+Enterで、確定入力
「Excelでの集計について(適切な関数)」の回答画像2
    • good
    • 1
この回答へのお礼

ご丁寧にありがとうございます。
若干関数が重くなり再計算に時間がかかってしまいましたが、大変わかりやすかったです。
助かりました。

お礼日時:2017/02/21 20:39

作業用のセルを用いれば簡単なのではないでしょうか?


要は、区分の一致するデータのみで、上下5%にあたるデータは除外して平均を取りたい。
ということですよね?
異常な値を抜くというのは、上下5%に当たるデータを除外することですよね?

作業用列もしくはシートに、A01~D20の80列を用いて表示する列を分割し、
それぞれの列においてTRIMMEANを使えば良いと思われます。

例えば作業用シートのA1~CB1にA01~D20を入力し、
元のデータがデータというシートのA2~E20001にあるとすれば、
A2=IF(データ!$E2=A$1,データ!$D2,"")
(データシートのE列と作業用シートの1行目が一致すればデータシートのD列を、一致しなければ空白を表示)
としてCB20001までコピーします。
A20002=TRIMMMEAN(A2:A20001,0.1)
(A2~A20001のデータを上下5%ずつ排除して平均した値が出ます。 ただし排除するデータの合計は偶数(上下同じ数ずつ)です)
としてCB20002までコピーします。
A20002~CB20002の値がそれぞれ求めたい平均値となっているはずです。
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!