[發問] EXCEL的公式 不知應怎寫

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

2^#

發表於 2012-7-30 18:18 | 顯示全部帖子

回復 2# Hsieh

感謝超版^^

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

3^#

發表於 2012-7-31 14:22 | 顯示全部帖子

本帖最後由 icestormer 於 2012-7-31 14:25 編輯

回復 3# icestormer

超版你好那個由下向上找的有成功了但由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了

EXCEL測試.rar (17.86 KB)
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
EXCEL裡的表有主要有二個但資料長度是會變動的

D1=AVERAGE(C188:C330) 先用這公式算第一次的平均值
D2=AVERAGE(C188:C286)再根據上頭的[71.66] ,將C188:C330之中選擇比71.66還高的數值算平均值(這部份好像得用上找到符合的數值後傳回該位址,的樣子)
D3=最後再依第二次平均的值[79.14] 從C188:C248 算出第三次平均

不知我想這樣做EXCEL 是否可以做到?? 再次麻煩了

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

4^#

發表於 2012-7-31 14:33 | 顯示全部帖子

回復 3# icestormer

上一文有點問題請以這文為準謝

超版你好那個由下向上找的有成功了但由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了

EXCEL測試.rar (17.86 KB)
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
EXCEL裡的表有主要有二個但資料長度是會變動的

D1=AVERAGE(C186:C279) 先用這公式算第一次的平均值
D2=AVERAGE(C188:C286)再根據上頭的[6.89] ,將C186:C235之中選擇比6.89還高的數值算平均值(這部份好像得用上找到符合的數值後傳回該位址,的樣子)
D3=最後再依第二次平均的值[6.81] 從C186:C108 算出第三次平均

不知我想這樣做EXCEL 是否可以做到?? 再次麻煩了

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

5^#

發表於 2012-7-31 14:52 | 顯示全部帖子

EXCEL的幾公式實在弄不出來

對不起不知怎一回事一直說我不能修改.. 又被我PO的亂了我就重PO一個

超版你好那個由下向上找的有成功了但由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了

EXCEL測試.rar (13.75 KB)
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
EXCEL裡的表有主要有二個但資料長度是會變動的

D1=AVERAGE(C5:C170) 先用這公式算第一次的平均值(這裡是60,360,54)
D2=根據上頭的[60,360,54] ,將C5:C170之中選擇比60,360.54還低的數值 ,算平均值(這部份好像得用上找到符合的數值後傳回該位址,的樣子)
D3=最後再依第二次平均的值[120.3] 從C5:C170找到比120.3還低的值算出第三次平均

依上頭的需求第一步得能自動算出由上至下的資料有幾筆(之後用在範圍之用)
再來則是連續二次按平均值在固定範圍內(C5:C170)中找到比平均值低的數值由C5:到該數值為範圍重計一次平均值

天呀看起來好麻煩還是超版有更簡單好用的方法可以一步算出來呢?
(因有的價格很高但數量很低如果不把它排除,價格將會嚴重失真)

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

6^#

發表於 2012-7-31 15:46 | 顯示全部帖子

回復 7# Hsieh

EXCEL測試.rar (13.75 KB) 用這個附件說一下好了之前有點問題..

一開始先從C5:C170 這是目前第一個表的資料長度(這需要先從C5向下找到最後一筆(C170)並傳回該位址($C$170) 會得出 60,360.54
再來再利用那個60,360.54去比對範圍 C5:C170之間有那一個數值比它高的(表中是C170格比60,360.54高),再把要算平均值的範圍從C5:170>>C5:169 得出 120.30
再重復一次把 120.30 再去比對範圍C5:C170之間有那一個數值比它高的(表中是C158格比120.3還高),算平均值的範圍從C5:C170>>>C5:C158 最後得出65.15

1:要能自動按資料表C5:算至最後一筆的平均值
2:要能比對出比平均值高的數值向上一格的範圍,依C5到那一格為範圍算出平均值.

我自己有試者用MATCH 是可以顯示符合的資料在範圍中的第幾格但沒法顯示位址(例:C20) 這是不是要配合ADDRESS,?又要怎寫法呢??

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

7^#

發表於 2012-7-31 15:52 | 顯示全部帖子

回復 8# icestormer

那個公式我用複製並貼上去EXCEL 上但郤顯示(#N/A) 不知是那出錯了??

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

8^#

發表於 2012-7-31 20:39 | 顯示全部帖子

回復 10# Hsieh

感謝你花時間教我

看起來可行了但我試著想要修改郤不得其門而入

先建立一個名稱rng

參照到公式=OFFSET(更新!$C$5,,,MATCH(TRUE,更新!$A$5:$A$65536="",0)-1,) <<<這東西要怎建立呢??? 之前完全沒看過呢可以教一下如何打開或建立嗎? 謝了^^

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

9^#

發表於 2012-7-31 21:56 | 顯示全部帖子

回復 10# Hsieh

真感謝你的幫忙我明天研究一下^^

帖子: 127
主題: 28
精華: 0
積分: 160
點名: 0
作業系統: W7
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-11-1
最後登錄: 2017-5-20

10^#

發表於 2012-8-1 19:36 | 顯示全部帖子

回復 icestormer
Hsieh 發表於 2012-7-31 20:52

超版你好昨天你教的已學會怎改了(雖然還不太懂他的運作原理..)

在上一個EXCEL檔中下方有另一個表也是跟同樣的分二次計算平均值後算出比較合理的平均值

不同的是:

1:由大至小
2:每一個price 之間必然會出現空白格

如果手動計算(拉範圍)
第一次平均值:5.25 [=AVERAGE(C386:C574)]
第二次平均值: 5.93 [=AVERAGE(C386:C532)]
最終平均值: 6.11 [=AVERAGE(C386:C460)]

如果是這樣要怎改呢?(我試了一整天..弄不出來)
另外想問一個問題如果我要設置像這種的計算(使用陣列)有數百個表是否就要定義RNG1~RNGXXXX 個才行呢??

TO:smouse0220
謝了不過我覺得最好能直接用EXCEL計算會方便多了
因..像這種表需要設置上百種物品的(一個物品有二表)