帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2015-9-15 22:35 | 只看該作者

不連續儲存格依比例計算平均

TOP 50、30、20的計算結果目前部分是人工計算後手動輸入公式內
TOP 50%、30%、20%目前想到的方法與TOP 50、30、20相同，都需要人工計算後輸入
由於每次的資料量可能不相同
請問該怎麼修改公式成可以自動判斷後計算出相同的結果?
想整理成如下的結果，附檔有raw data
先謝謝幫忙的各位

            >80 <80
最大    85.40    31.08
最小       81.88  1.27
平均       83.16  3.78
TOP 50 82.51    1.44
TOP 30    82.39  1.39
TOP 20    82.31  1.37
TOP 50%
TOP 30%
TOP 20%

Book2.zip (8.14 KB)

樓主熱帖

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2848
主題: 10
精華: 0
積分: 2903
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2026-7-25

2^#

發表於 2015-9-16 13:08 | 只看該作者

TOP 50　不是〔由大至小前５０〕？
=AVERAGE(LARGE(sort!E$2:AO$99,ROW($1:$50)))　陣列

原公式：=AVERAGE(LARGE(sort!E$2:AO$99,ROW($199:$248)))
是〔>80〕數值的〔由小而大前５０〕

〔不連續儲存格〕，只能用 OFFSET 或 INDIRECT，但很多函數不吃〔聯集〕，
手動按 CTRL〔跳選〕各欄位再〔定義名稱〕較可行，但動態彈性小，
名稱：xArea
=sort!$E:$E,sort!$I:$I,sort!$M:$M,sort!$Q:$Q,sort!$U:$U,sort!$Y:$Y,sort!$AC:$AC,sort!$AG:$AG,sort!$AK:$AK,sort!$AO:$AO

前５０大平均：=AVERAGE(LARGE(xArea,ROW($1:$50)))　陣列

TOP

vvcvc

中學生 Rank: 2

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

3^#

發表於 2015-9-16 13:48 | 只看該作者

其實那些數字是時間，所以希望時間愈少愈好
才會造成TOP 50要先算出總數後前推
在看了版主的回復後突然有個想法
如果設計2個動態選框的話
一個選80以上、一個選80以下
這樣子一來直接使用版主的公式寫法
把large改成small即可得到我要的效果了
不知道可不可行，等等來試試看

TOP

hcm19522

碩士班 Rank: 5 Rank: 5

帖子: 1796
主題: 184
精華: 0
積分: 1985
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 100
性別: 男
註冊時間: 2015-9-11
最後登錄: 2026-9-24

熱烈參與勳章

4^#

發表於 2015-9-16 14:03 | 只看該作者

http://blog.xuite.net/hcm19522/twblog/343417217

TOP

vvcvc

中學生 Rank: 2

帖子: 144
主題: 33
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: Office 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-9-26
最後登錄: 2023-6-23

5^#

發表於 2015-9-25 16:44 | 只看該作者

自己整理後試用hcm19522的方法
但算出來的東西都很奇怪
我寫的公式是{=AVERAGE(IF(排序P<=SMALL(排序P,CHOOSE(ROW(A1),50,30,20)),排序P))}
不知道錯在那裡

整理數據的方式有點笨(原本想用動態選取位置，不過動態還是不能跳選)
幾乎每次都要核對一次公式

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2848
主題: 10
精華: 0
積分: 2903
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2026-7-25

6^#

發表於 2015-9-25 18:19 | 只看該作者

本帖最後由准提部林於 2015-9-25 18:22 編輯

=SMALL({1,2,2,3,3,3,6,8,9},4)　第４小值為〔３〕
但陣列中＜＝３的有６個，

所以公式的平均是６個數字的平均{1,2,2,3,3,3}，
=AVERAGE(IF({1,2,2,3,3,3,6,8,9}<=SMALL({1,2,2,3,3,3,6,8,9},4),{1,2,2,3,3,3,6,8,9}))

而不是前４小值的平圴{1,2,2,3}！
=AVERAGE(SMALL({1,2,2,3,3,3,6,8,9},{1,2,3,4}))
　
　
另．不連續儲存格較難下公式～～

TOP