今天的主題是計(jì)算最大值。確切地說，我們需要找出計(jì)算最大值最有效的方式。

寫這個(gè)內(nèi)容的直接原因是最近看一位朋友的Excel文件。這個(gè)文件有點(diǎn)慢，仔細(xì)看發(fā)現(xiàn)瓶頸在一個(gè)計(jì)算最大值的列上。結(jié)合前一段時(shí)間接觸到的其他一些案例，看到很多人對(duì)一些新函數(shù)的使用方法有些值得商榷的地方，就打算一起介紹一下。

問題很簡(jiǎn)單，

首先我們有這樣一個(gè)參數(shù)表，記錄了所有的產(chǎn)品信息，包括每個(gè)產(chǎn)品的名稱，類別，單位成本和單價(jià)。

然后，我們要在某個(gè)地方做如下計(jì)算：

計(jì)算每個(gè)列出的類別的最高成本。

顯然，在產(chǎn)品表中，每一個(gè)類別有多種產(chǎn)品，所以也就有多個(gè)單位成本。這里只要其中的最高的那個(gè)。

這個(gè)表設(shè)計(jì)的數(shù)據(jù)并不多，產(chǎn)品表只有1600多行，要計(jì)算的結(jié)果也只有2000多行。但是就有性能的問題。如果再有其他的公式，性能問題就很突出了。

這位朋友的文件中使用的公式實(shí)際上非常巧妙：

=MAX((Product!$B$2:$B$1691=B6)*(Product!$C$2:$C$1691))

這個(gè)公式是首先對(duì)產(chǎn)品表的Catetory列進(jìn)行判斷，是否等于當(dāng)前的產(chǎn)品類別，得到一列TRUE/FALSE值，然后將這個(gè)結(jié)果與產(chǎn)品表的單位成本列相乘，這樣，只有那些等于當(dāng)前產(chǎn)品類別的行對(duì)應(yīng)的單位成本保留下來，其余的都變成了0。

然后再求最大值。

用我之前演示過的工具測(cè)試一下，這一列的計(jì)算時(shí)間超過0.3秒，

這是一個(gè)很可觀的時(shí)間了?？紤]到這個(gè)計(jì)算只是總體計(jì)算中的一個(gè)很小的環(huán)節(jié)，跟其他公式疊加后，造成性能問題一點(diǎn)也不奇怪。

這個(gè)問題的常規(guī)解決方法其實(shí)應(yīng)該用MAXIFS函數(shù)，

=MAXIFS(Product!$C$2:$C$1691,Product!$B$2:$B$1691,Index!B6)

就是根據(jù)條件求最大值嘛，很簡(jiǎn)單（估計(jì)那位朋友應(yīng)該是被其他公式帶偏了思路，忘了這個(gè)公式了）。

用這種方法，計(jì)算速度得到一些提升，

有效果，但是不明顯。

這里要強(qiáng)調(diào)一下，在很多實(shí)際場(chǎng)景中，這兩種方法的速度差別要比這種測(cè)試環(huán)境種好很多。

繼續(xù)找新方法。

仔細(xì)看這個(gè)問題，其實(shí)是個(gè)查找問題：

“查找類別等于當(dāng)前類別的產(chǎn)品的最高單位成本”。

既然是個(gè)查找問題，就可以用VLOOKU函數(shù)。

但是，同一個(gè)類別有多條記錄，VLOOKUP返回找到的第一條記錄。

所以，我們應(yīng)該對(duì)產(chǎn)品表排序，按單位成本降序排序即可，保證找到的第一條一定是最高成本。

公式很簡(jiǎn)單，

=VLOOKUP(B6,Product!$B$2:$C$1691,2,0)

這個(gè)公式的效率很高，

當(dāng)然，我們這列使用了精確匹配，如果使用近似匹配，效率還會(huì)有很多提升。不過需要按類別和單位成本兩列進(jìn)行（升序）排序。（這里就不演示了，關(guān)于近似匹配提升效率的方法，具體請(qǐng)參見這里）

這個(gè)問題本身基本解決了。接下來是擴(kuò)展的內(nèi)容。

有時(shí)候，參數(shù)表不能排序（比如，有另外更重要的計(jì)算中需要參數(shù)表的特定順序），上面的VLOOKUP方法就行不通了。

幸虧Excel推出了SORT函數(shù)。

于是，很多人就會(huì)使用下面的公式，

=VLOOKUP(B6, SORT(Product!$B$2:$C$1691, {1,2},{1,-1}),2,0)

其中，SORT函數(shù)對(duì)Product表排序，按照類別和成本列，一個(gè)升序一個(gè)降序。結(jié)果數(shù)組作為數(shù)據(jù)源，使用VLOOKUP進(jìn)行查找。

這個(gè)公式在效果上與我們前面介紹的最后一種VLOOKUP方法一樣。

但是在計(jì)算效率上，這個(gè)公式表現(xiàn)很差，

這么簡(jiǎn)單的計(jì)算竟然需要2秒多。

甚至連近似匹配都挽救不了，

=VLOOKUP(B6, SORT(Product!$B$2:$C$1691, {1,2},{1,1}),2,1)

這個(gè)公式與上一個(gè)公式類似，只不過改用近似匹配。當(dāng)然排序也必須都是升序。

看一看它的計(jì)算時(shí)間，

甚至比精確匹配還差！

我們說過，LET函數(shù)可以提高計(jì)算速度，于是將上面的公式改寫下面的LET公式，

=LET(    prod, Product!$B$2:$C$1691,    sortedProd, SORT(prod, {1,2}, {1,-1}),    VLOOKUP(B6, sortedProd, 2, 0))

這個(gè)公式計(jì)算速度也沒有絲毫改進(jìn)，

這里一定要注意，有很多新函數(shù)都是在進(jìn)行循環(huán)迭代，比如SORT函數(shù)，比如FILTER函數(shù)，還有那些循環(huán)函數(shù)。

一次性的使用基本問題不大，但是在需要反復(fù)使用的公式種嵌套使用，很可能就會(huì)造成嚴(yán)重的性能問題。

是不是沒辦法了呢？

有，結(jié)合動(dòng)態(tài)數(shù)組就可以改進(jìn)。

比如下面的公式，

=LET(    prod, Product!$B$2:$C$1691,    sortedProd, SORT(prod, {1,2}, {1,-1}),    VLOOKUP(B6:B2821, sortedProd, 2, 0))

這個(gè)公式看上去跟前一個(gè)公式基本一樣，只是VLOOKUP的查找條件從B6變成了B6:B2821，從一個(gè)單元格變成了一個(gè)區(qū)域。

性能提升是巨大的，

原因很簡(jiǎn)單，耗時(shí)的排序才做從前一個(gè)公式種的2800多次，變成了只執(zhí)行1次。