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

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

3^#

發表於 2012-7-30 18:18 | 只看該作者

回復 2# Hsieh

感謝超版^^

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

4^#

發表於 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

5^#

發表於 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

6^#

發表於 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:到該數值為範圍重計一次平均值

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

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

7^#

發表於 2012-7-31 15:27 | 只看該作者

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

看不懂你每次平均的範圍有甚麼規則?
就無法得知與你的發問有何關聯?
至於你xldown的公式
應該是陣列公式=INDEX($A$2:$A$171,MATCH(TRUE,$A$2:$A$171="",0)-1,)
而不是=INDEX($A$2:$A$171,MATCH(TURE,$A$2:$A$171="",0)-1,)

學海無涯_不恥下問

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

8^#

發表於 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

9^#

發表於 2012-7-31 15:52 | 只看該作者

回復 8# icestormer

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

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

10^#

發表於 2012-7-31 16:52 | 只看該作者

回復 9# icestormer

公式錯誤是因為必須使用陣列公式才能成功(Ctrl+Shift+Enter)

試試看
先建立一個名稱rng
參照到公式=OFFSET(更新!$C$5,,,MATCH(TRUE,更新!$A$5:$A$65536="",0)-1,)

D1=AVERAGE(rng)
D2=SUMPRODUCT((rng<D1)*rng)/(COUNT(rng)-SUMPRODUCT((rng>=D1)*1))
D3=SUMPRODUCT((rng<D2)*rng)/(COUNT(rng)-SUMPRODUCT((rng>=D2)*1))

EXCEL測試.zip (16 KB)

學海無涯_不恥下問