返回列表 上一主題 發帖

[發問] 如何計算浮動列數範圍的平均值

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
114  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-5-17 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
1# 跳轉到 »
發表於 2013-1-18 00:23 | 顯示全部帖子
回復 1# jsleee
定義名稱
x=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=21),ROW(y),""))
y=OFFSET(工作表1!$A$1,,,COUNTA(工作表1!$B:$B)+1,)
C1儲存格公式
=IF(A1="","",AVERAGE(OFFSET(B1,,,INDEX(x,COUNTA($A$1:A1)+1))))
向下複製
浮動列數計算.rar (6.45 KB)
學海無涯_不恥下問

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
114  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-5-17 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
2#
發表於 2013-1-18 19:34 | 顯示全部帖子
回復 7# jsleee
範例的做法為配合資料量變動
x名稱的公式改為=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=ROWS(y)),ROW(y),""))

x名稱的用意在傳回每個分類資料量
以檔案資料來說，會傳回陣列{1,4,2,6,3,5}
所以若以A欄為非空格的相對B欄位置，向下擴展的資料作平均就是各分組的平均

以C1而言，A1項目的資料量是x的第2個值=4
所以OFFSET(B1,,,INDEX(x,COUNTA($A$1:A1)+1))
中範圍的列數是INDEX(x,COUNTA($A$1:A1)+1)
就是計算A欄非空格的數量加1來指定x陣列中的序列數
學海無涯_不恥下問

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
114  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-5-17 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
3#
發表於 2013-1-19 23:20 | 顯示全部帖子
回復 9# jsleee
因為資料範圍並非從E1開始
所以ROWS(y)並不會是50
定義名稱x公式改為
=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=ROWS(y)+ROW(y)-1),ROW(y),""))
學海無涯_不恥下問

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
114  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-5-17 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
4#
發表於 2013-1-20 15:39 | 顯示全部帖子
回復 11# jsleee
y公式 =OFFSET(測試!$C$14,,,COUNTA(測試!$E$14:$E$65536)+1,)
列位應該要用絕對參照，否則當名稱使用於公式內時
會因為制改變而改變計算的起始位置
學海無涯_不恥下問

TOP

        靜思自在 : 有智慧才能分辨善惡邪正；有謙虛才能建立美滿人生。
返回列表 上一主題