[發問] 公式效率問題

Bodhidharma

想請教一個問題：
表格有兩個部分，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

回復 1# Bodhidharma
使用合併彙算功能

下載 (202.7 KB)

2013-2-25 19:08

Bodhidharma

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

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

回復 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)還要低的演算法…(話說演算法這門課在上什麼我幾乎已經忘光了)

感謝版主~