Board logo

標題: [發問] 公式效率問題 [打印本頁]

作者: Bodhidharma    時間: 2013-2-25 18:26     標題: 公式效率問題

本帖最後由 Bodhidharma 於 2013-2-25 18:27 編輯

想請教一個問題:
表格有兩個部分,ABC欄是異動資料記錄,GH欄中,G是所有東西的名稱
我希望H欄能夠計算G欄名稱的東西,在所有ABC欄中,數量最大的一個,該列的異動性質
(如:aaa在B欄第2,5,7列中有出現,相對應的A欄數量為231,446,111,取列大的446,也就是第5列,傳回該列的異動性質4)
我的寫法如下:
H2={INDEX($C$2:$C$9,MATCH(1,($A$2:$A$9=MAX(IF($B$2:B$9=G2,$A$2:$A$9,"")))*($B$2:$B$9=G2),0))}

運算結果沒問題,但是因為我資料約有5000筆,運算速度非常緩慢
想請教有沒有比較有效率的寫法?

  A              B                   C                     G                     H
數量        名稱        異動性質              名稱             最大數量的異動性質
231        aaa                1                                aaa                4
234        bbb                2                                bbb        2
221        ccc                6                                ccc                6
446        aaa                4                                ddd        2
231        bbb                1                                       
111        aaa                2                                       
111        ccc                3                                       
321        ddd                2
作者: Hsieh    時間: 2013-2-25 19:08

回復 1# Bodhidharma
使用合併彙算功能
[attach]14250[/attach]
作者: Bodhidharma    時間: 2013-2-25 21:07

本帖最後由 Bodhidharma 於 2013-2-25 21:09 編輯

回復 2# Hsieh

唔…好像有一點誤解
1.我要的是"A欄中的值最大",但是回傳C欄的值
2.而不是"回傳C欄最大的值
剛好我的資料以上兩者的情形都一樣
不過如果把C2的值改成9,就會比較清楚
(如:aaa在B欄第2,5,7列中有出現,相對應的A欄數量為231,446,111,取列大的446,也就是第5列,傳回該列的異動性質4)

1.的話aaa那邊正確應該還是4,因為該列是aaa在A欄中值最大的
2.的話就會回傳9,因為9是aaa在C欄的值最大的
作者: Hsieh    時間: 2013-2-25 23:05

回復 3# Bodhidharma
試試看速度是否有快一點
H2陣列公式
=INDEX($C$2:$C$5000,MATCH($G2&MAX(($B$2:$B$5000=$G2)*($A$2:$A$5000)),$B$2:$B$5000&$A$2:$A$5000,0))
向下複製
作者: Bodhidharma    時間: 2013-2-26 00:29

本帖最後由 Bodhidharma 於 2013-2-26 00:31 編輯

回復 4# Hsieh

A式:H2=INDEX($C$2:$C$9,MATCH(1,($A$2:$A$9=MAX(IF($B$2:B$9=G2,$A$2:$A$9,"")))*($B$2:$B$9=G2),0))
B式:H2=INDEX($C$2:$C$9,MATCH($G2&MAX(($B$2:$B$9=$G2)*($A$2:$A$9)),$B$2:$B$9&$A$2:$A$9,0))

簡化一下,差異大概是這樣子:
max函式部分:
A式:max(if(陣列A=目標a,陣列B,"")
B式:max((陣列A=目標a)*(陣列B))
B式比較直觀,而且"*"應該會比"if"有效率,所以B式我覺得比較好
match函式部分:
A式:match(1,(陣列A=目標a)*(陣列B=目標b),0)
B式:match(目標a&目標b,陣列A&陣列B,0)
我覺得A式比較直觀一點(不過那個"1"要轉一個小彎),但是就效率來講,B式應該會好一點(大概會節省計算"陣列A=目標a"和"陣列A=目標a"的時間吧)

改天再改寫看看,我猜大概會省個一半多的時間吧,但是還是很慢(汗)
不過看起來這個東西似乎很難找出比O(n^2)還要低的演算法…(話說演算法這門課在上什麼我幾乎已經忘光了)

感謝版主~




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