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

icestormer

請問一下 不知 EXCEL如果想使用像是ＶＢＡ中的END(xldown) 功能的　公式應要怎寫呢？？

Hsieh

回復 1# icestormer


   

下載 (79.93 KB)

2012-7-30 15:08

icestormer

回復 2# Hsieh


  感謝超版^^

icestormer

回復 3# icestormer


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

下載次數: 2

2012-7-31 14:16

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

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

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

icestormer

回復 3# icestormer

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

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

下載次數: 2

2012-7-31 14:29

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

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

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

icestormer

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

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

下載次數: 1

2012-7-31 14:52

因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
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

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

icestormer

回復 7# Hsieh


    EXCEL測試.rar (13.75 KB)

下載次數: 1

2012-7-31 15:35

  用這個附件 說一下好了 之前有點問題..

一開始 先從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,?又要怎寫法呢??

icestormer

回復 8# icestormer


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

Hsieh

回復 9# icestormer

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

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

下載 (24.09 KB)

2012-7-31 16:49

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)

下載次數: 4

2012-7-31 16:52