Board logo

標題: [發問] EXCEL的公式 不知應怎寫 [打印本頁]
作者: icestormer    時間: 2012-7-30 14:18     標題: EXCEL的公式 不知應怎寫

請問一下 不知 EXCEL如果想使用像是ＶＢＡ中的END(xldown) 功能的　公式應要怎寫呢？？
作者: Hsieh    時間: 2012-7-30 15:08

回復 1# icestormer


    [attach]11946[/attach]
作者: icestormer    時間: 2012-7-30 18:18

回復 2# Hsieh


  感謝超版^^
作者: icestormer    時間: 2012-7-31 14:22

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

回復 3# icestormer


   超版你好 那個由下向上找的 有成功了但 由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了[attach]11957[/attach]
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
EXCEL裡的表有主要有二個但資料長度是會變動的

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

不知我想這樣做EXCEL 是否可以做到?? 再次麻煩了
作者: icestormer    時間: 2012-7-31 14:33

回復 3# icestormer

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

   超版你好 那個由下向上找的 有成功了但 由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了[attach]11958[/attach]
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
EXCEL裡的表有主要有二個但資料長度是會變動的

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

不知我想這樣做EXCEL 是否可以做到?? 再次麻煩了
作者: icestormer    時間: 2012-7-31 14:52     標題: EXCEL的幾公式實在弄不出來

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

   超版你好 那個由下向上找的 有成功了但 由上向下都一直出錯,沒法成功呢
我弄了幾天(在問之前也有試著弄看看)實在是弄不出來..
我主要是想要做這個東西我附上EXCEL檔好了[attach]11959[/attach]
因我想不出有什麼公式或法子可以一次就算出真實的平均價位,只好透過前二次平均值的來排除一些不合理的
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    時間: 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,)
作者: icestormer    時間: 2012-7-31 15:46

回復 7# Hsieh


    [attach]11960[/attach]  用這個附件 說一下好了 之前有點問題..

一開始 先從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    時間: 2012-7-31 15:52

回復 8# icestormer


   
那個公式我用複製並貼上去EXCEL 上但郤顯示(#N/A) 不知是那出錯了??
作者: Hsieh    時間: 2012-7-31 16:52

回復 9# icestormer

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

試試看
先建立一個名稱rng
參照到公式=OFFSET(更新!$C$5,,,MATCH(TRUE,更新!$A$5:$A$65536="",0)-1,)
[attach]11961[/attach]
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))   
[attach]11962[/attach]
作者: icestormer    時間: 2012-7-31 20:39

回復 10# Hsieh


   感謝你花時間教我

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

先建立一個名稱rng

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

回復 11# icestormer

[attach]11965[/attach]
作者: icestormer    時間: 2012-7-31 21:56

回復 10# Hsieh


   真感謝你的幫忙 我明天研究一下^^
作者: smouse0220    時間: 2012-7-31 22:39

如果你要寫VBA
你可以用這樣的寫法

1.>先指定一個計算由下往上 或是 由上往下的變數
Dim X As Integer

2.>計算你要計算的了那一列(Ex:A) - 由下往上
X = Worksheets(1).Range("A65536").End(xlUp).Row

3.>計算你要計算的了那一列(Ex:A) - 由上往下
X = Worksheets(1).Range("A1").End(xlDown).Row
作者: icestormer    時間: 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計算會方便多了
因..像這種表需要設置上百種物品的(一個物品有二表)
作者: Hsieh    時間: 2012-8-1 20:09

回復 15# icestormer
試試看
[attach]11984[/attach]
作者: icestormer    時間: 2012-8-1 20:36

回復 16# Hsieh


   可以了 好神喔 超版 ! 真是太感謝了



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)