帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

1^# 跳轉到 » 倒序看帖

字體大小: tT

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

超級版主 Rank: 8 Rank: 8

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

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

2^#

發表於 2013-2-25 19:08 | 只看該作者

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

學海無涯_不恥下問

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

3^#

發表於 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欄的值最大的

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

4^#

發表於 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))
向下複製

學海無涯_不恥下問

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

5^#

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

感謝版主~

TOP