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

jsleee

請教先進
    如果我的工作表如附件所示，
    第一欄為項目，第二欄有數值，我想要在第三欄黃色區域
    埋入公式計算第二欄的數值的平均
    但 每個項目的列數可能每一次都不一樣...
    換句話說，目前AA的列數有4列，
    但下一次AA可能變成6列或其他的列數
    其他項目也是一樣，列數都會浮動
    不知道這樣的函數如何寫？還是只能用VBA？
    我的困擾是 不知用甚麼函數來機動判斷 所需的範圍列數...
    麻煩 先進們指點迷津，謝謝

JS

下載 (21.29 KB)

2013-1-15 21:58

p212

回復 1# jsleee
請參考
1、建議A欄與B欄給予欄位名稱，如A欄為項目名稱，B欄為數量。
2、建議在B欄有數據狀況下，A欄儲存格勿空白，即屬於項目名稱為AA的數據，其對應於A欄者應有4個AA。(益處：資料清楚)
3、接執行「插入_樞紐分析表」，採「平均數」呈現即可。爾後原始工作表若有新增列時，只要重整樞紐分析表即可更新分析結果。

zz5151353

請教先進
    如果我的工作表如附件所示，
    第一欄為項目，第二欄有數值，我想要在第三欄黃色區域
   ...
jsleee 發表於 2013-1-15 21:57

請參考那檔案,是那意思嗎?

p212

回復 3# zz5151353
發問者(1#樓主)的需求似乎隨時會插入一列新增資料，若依您(3#樓主)的建議，算式可能會出問題。

jsleee

回復 3# zz5151353

zz大大的回覆似乎可行...
但有點不解該公式的涵義，不知能否稍微解釋一下
感激不盡

JS

Hsieh

回復 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)

下載次數: 10

2013-1-18 00:22

jsleee

回復 6# Hsieh

請教 超級版主
實在不好意思，因為看不太懂
能否 撥空用 較白話的方式 說明 公式設定
其中 範圍 x 的設定 & "C" 欄的公式 比較無法理解....
或是有甚麼 其他的參考資訊可以參考
感激不盡

JS

Hsieh

回復 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

回復 8# Hsieh

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

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

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

JS

Hsieh

回復 9# jsleee
因為資料範圍並非從E1開始
所以ROWS(y)並不會是50
定義名稱x公式改為
=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=ROWS(y)+ROW(y)-1),ROW(y),""))