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

帖子: 151
主題: 25
精華: 0
積分: 194
點名: 1
作業系統: WINDOWS 10
軟體版本: Office 2010 或 2021
閱讀權限: 20
性別: 男
註冊時間: 2011-9-6
最後登錄: 2024-11-8

15^#

發表於 2013-1-21 11:21 | 只看該作者

回復 14# zz5151353

感謝 zz大大的協助，我再好好研究研究

JS

版主 Rank: 7 Rank: 7 Rank: 7

zz5151353

帖子: 206
主題: 3
精華: 0
積分: 218
點名: 0
作業系統: WIN10
軟體版本: OFFICE 365
閱讀權限: 100
性別: 男
來自: 台灣台北
註冊時間: 2010-5-15
最後登錄: 2023-6-18

14^#

發表於 2013-1-21 10:34 | 只看該作者

公式已設動態了,另外增加一 VBA 速度跟效率都全面提升,請參考附上的檔案

浮動列數計算.rar (9.28 KB)

300 字節以內
不支持自定義 Discuz! 代碼

帖子: 151
主題: 25
精華: 0
積分: 194
點名: 1
作業系統: WINDOWS 10
軟體版本: Office 2010 或 2021
閱讀權限: 20
性別: 男
註冊時間: 2011-9-6
最後登錄: 2024-11-8

13^#

發表於 2013-1-20 16:54 | 只看該作者

回復 12# Hsieh

感謝超級版主提供的修正
已修正並測試正常，感恩

JS

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

12^#

發表於 2013-1-20 15:39 | 只看該作者

回復 11# jsleee
y公式 =OFFSET(測試!$C$14,,,COUNTA(測試!$E$14:$E$65536)+1,)
列位應該要用絕對參照，否則當名稱使用於公式內時
會因為制改變而改變計算的起始位置

學海無涯_不恥下問

帖子: 151
主題: 25
精華: 0
積分: 194
點名: 1
作業系統: WINDOWS 10
軟體版本: Office 2010 或 2021
閱讀權限: 20
性別: 男
註冊時間: 2011-9-6
最後登錄: 2024-11-8

11^#

發表於 2013-1-20 10:43 | 只看該作者

回復 10# Hsieh

謝謝超級版主的指點
我試著依照超級版主的指示進行 x定義範圍公式的修改，結果還是有問題
接著我發現只要將 y的定義範圍公式修改，搭配超級版主所提供的 x 修改，結果就OK
修改前 y公式 =OFFSET(測試!$C$14,,,COUNTA(測試!$E14:$E$65536)+1,)
修改後 y公式 =OFFSET(測試!$C$14,,,COUNTA(測試!$E14:$E$65536)+ROW(測試!E14),)

因為對公式並沒有完全了解透測，所以也不太知其所以然
附上修改後的測試結果正常檔案，如果有任何補充說明，請不吝提出，謝謝

JS

測試2.zip (30.11 KB)

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

10^#

發表於 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),""))

學海無涯_不恥下問

帖子: 151
主題: 25
精華: 0
積分: 194
點名: 1
作業系統: WINDOWS 10
軟體版本: Office 2010 或 2021
閱讀權限: 20
性別: 男
註冊時間: 2011-9-6
最後登錄: 2024-11-8

9^#

發表於 2013-1-19 18:24 | 只看該作者

回復 8# Hsieh

非常謝謝超級版主耐心的說明，無奈愚鈍的我還是一知半解
我嘗試套用這樣的作法在附件的測試檔案中
但只有幾列的數據是正確的，之後的數據就出問題...

附件中的 J欄是我要的結果，
J欄=I欄數值相乘的結果，相乘的依據是根據 C欄的項目
K欄則是驗算
其中第14列~30列結果都還正常，但從 31列開始，數據就出現問題...

能否在勞煩超級版主協助確認一下我的問題是出在哪裡？
感激不盡

JS

測試.zip (20.38 KB)

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

8^#

發表於 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陣列中的序列數

學海無涯_不恥下問