標題:
[發問]
如何計算浮動列數範圍的平均值
[打印本頁]
作者:
jsleee
時間:
2013-1-15 21:57
標題:
如何計算浮動列數範圍的平均值
本帖最後由 jsleee 於 2013-1-15 21:59 編輯
請教先進
如果我的工作表如附件所示，
第一欄為項目，第二欄有數值，我想要在第三欄黃色區域
埋入公式計算第二欄的數值的平均
但 每個項目的列數可能每一次都不一樣...
換句話說，目前AA的列數有4列，
但下一次AA可能變成6列或其他的列數
其他項目也是一樣，列數都會浮動
不知道這樣的函數如何寫？還是只能用VBA？
我的困擾是 不知用甚麼函數來機動判斷 所需的範圍列數...
麻煩 先進們指點迷津，謝謝
JS[attach]13955[/attach]
作者:
p212
時間:
2013-1-16 08:54
回復
1#
jsleee
請參考
1、建議A欄與B欄給予欄位名稱，如A欄為項目名稱，B欄為數量。
2、建議在B欄有數據狀況下，A欄儲存格勿空白，即屬於項目名稱為AA的數據，其對應於A欄者應有4個AA。(益處：資料清楚)
3、接執行「插入_樞紐分析表」，採「平均數」呈現即可。爾後原始工作表若有新增列時，只要重整樞紐分析表即可更新分析結果。
作者:
zz5151353
時間:
2013-1-16 10:06
請教先進
如果我的工作表如附件所示，
第一欄為項目，第二欄有數值，我想要在第三欄黃色區域
...
jsleee 發表於 2013-1-15 21:57
請參考那檔案,是那意思嗎?
作者:
p212
時間:
2013-1-16 13:42
本帖最後由 p212 於 2013-1-16 13:43 編輯
回復
3#
zz5151353
發問者(1#樓主)的需求似乎隨時會插入一列新增資料，若依您(3#樓主)的建議，算式可能會出問題。
作者:
jsleee
時間:
2013-1-17 21:49
回復
3#
zz5151353
zz大大的回覆似乎可行...
但有點不解該公式的涵義，不知能否稍微解釋一下
感激不盡
JS
作者:
Hsieh
時間:
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))))
向下複製
[attach]13977[/attach]
作者:
jsleee
時間:
2013-1-18 18:20
回復
6#
Hsieh
請教 超級版主
實在不好意思，因為看不太懂
能否 撥空用 較白話的方式 說明 公式設定
其中 範圍 x 的設定 & "C" 欄的公式 比較無法理解....
或是有甚麼 其他的參考資訊可以參考
感激不盡
JS
作者:
Hsieh
時間:
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陣列中的序列數
作者:
jsleee
時間:
2013-1-19 18:24
回復
8#
Hsieh
非常謝謝 超級版主 耐心的說明，無奈 愚鈍的我還是一知半解
我嘗試套用這樣的作法在附件的 測試 檔案中
但只有幾列的數據是正確的，之後的數據就出問題...
附件中 的 J欄 是我要的結果，
J欄=I欄數值相乘的結果，相乘的依據是根據 C欄的項目
K欄 則是驗算
其中 第14列~30列 結果都還正常，但從 31列開始，數據就出現問題...
能否在勞煩 超級版主 協助確認一下我的問題是出在哪裡？
感激不盡
JS
作者:
Hsieh
時間:
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),""))
作者:
jsleee
時間:
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
作者:
Hsieh
時間:
2013-1-20 15:39
回復
11#
jsleee
y公式 =OFFSET(測試!$C$14,,,COUNTA(測試!$E
$
14:$E$65536)+1,)
列位應該要用絕對參照，否則當名稱使用於公式內時
會因為制改變而改變計算的起始位置
作者:
jsleee
時間:
2013-1-20 16:54
回復
12#
Hsieh
感謝 超級版主 提供的修正
已修正並測試正常，感恩
JS
作者:
zz5151353
時間:
2013-1-21 10:34
公式已設動態了,另外增加一 VBA 速度跟效率都全面提升,請參考附上的檔案
作者:
jsleee
時間:
2013-1-21 11:21
回復
14#
zz5151353
感謝 zz大大的協助，我再好好研究研究
JS
作者:
ML089
時間:
2013-2-3 16:00
j14
=IF(C14="","",PRODUCT(OFFSET(I14,,,IF(ISNA(MATCH("*",C15:C$99,)),COUNT(E14:E$99),MATCH("*",C15:C$99,)))))
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
