Excel函數教學:MAX、LARGE、LOOKUP查找最大值

Excel分析資料常需要求報表明細中的最大值。本文以學生成績單為例,介紹如何使用MAX函數計算最大值,LARGE函數求前三名分數,最後補充進階LOOKUP函數的用法。

一、班級學生成績

範例是簡化的三年級三個班級部分學生的成績明細。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 1

二、MAX函數最大值

想要求這次段考最高分,第一個想到的是MAX函數:「=MAX(C:C)」,這裡的「C:C」代表C欄,也就是Excel此工作表所有金C欄的儲存格資料,因此得到學生成績中最高的分數,答案是「94」。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 3

三、LARGE函數最大值

求最大值除了使用MAX函數,還有另外一個LARGE函數可以達到相同作用。設計函數公式:「=LARGE(C:C,1)」。可以看到第一個參數是C欄,後面多了第一個參數,這裡的「1」表示要求第一大,也就是最大值,計算結果仍然是「94」。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 5

四、LARGE函數用法

通常成績優秀不會只看最大值第一名,第一名到第三名都是名列前茅,而剛才LARGE函數已經知道它的第2個參數是1的話,取第1名,當然照樣造句把參數設定為「2」或「3」,便可以得到第2名跟第3名的成績。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 7

五、LOOKUP函數極大值

前面步驟介紹的MAX及LARGE算是Excel常用基本基本的函數,不過Excel還有一些較為進階和刁鑽的函數,例如這裡看到的LOOKUP函數:「=LOOKUP(9^9,C:C)」。它有兩個參數,第1個參數看起來比較特別一點,「9^9」是九的九次方,在Excel公式中通常會用它代表一個極大數,配合LOOKUP函數模糊查找特性,再配合第二個參數是C欄,因此計算結果為最後一筆資料,也就是「84」。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 9

六、LOOKUP最後一筆

上個步驟提到LOOKUP算是比較刁鑽的函數,它的刁鑽之一在於先前的MAX或LARGE函數參數的個數是固定,MAX有一個參數,LARGE有兩個參數,不過LOOKUP的參數個數則是可變化的。上個步驟LOOKUP只用到兩個參數,在這裡用到了三個參數:「=LOOKUP(MAX(C:C),C:C,B:B)」。其中第1個參數「MAX(C:C)」有點類似像上個步驟「9^9」的作用,會在範圍內取得最大值,第2個參數「C:C」便是C欄,第三個參數「B:B」則是B欄,注意到這裡C欄和B欄是兩相對應的範圍,這是LOOKUP正確計算必須的參數設定,因為相同範圍才能一一對應的查找及傳回。公式計算結果同樣會取得最後一筆資料,只不過會取得C欄最後一筆相對應B欄的資料,B欄是學生姓名,所以是最後一筆資料的學生:「黃X以」。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 11

七、LOOKUP進階用法

上個步驟用LOOKUP函數得到最後一筆資料,但是比較多的情況是也想要知道第1名到底是誰,這個也可以使用LOOKUP函數:「=LOOKUP(MAX(C:C),C2:C11,B2:B11)」,只要把上個步驟本來是一整個B欄及C欄改成是固定範圍的「C2:11」及「B2:B11」,便可以得到第1名同學的姓名。

在這裡也許有點困惑,可以參考開發者微軟相關這個函數的說明,LOOKUP有兩種方式,「C:C,B:B」視為延伸向量,於是傳回最後一項內容,「C2:C11,B2:B11」視為固定陣列,於是傳回範圍內相對應值。學習Excel並非在設計VBA程式,因此只要大致瞭解函數功能,知道在什麼情況下要使用哪種方式,其實就可以應用在自己的實務工作上了。

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 13

Excel LOOKUP函數家族

本篇文章最後有提到可以參考微軟的說明文件,仔細去看的話,原來LOOKUP是微軟早期的函數,當時比較沒有固定規律相對較為自由,所以它的參數用法限制也較少。也許是因為實務上很需要它的功能,又容易造成使用上的困擾,因此微軟後來又開發了VLOOKUP及HLOOKUP,還有目前最新的XLOOKUP,成為LOOKUP函數家族。不過雖然LOOKUP很老了,就跟白貓黑貓會抓老鼠就是好貓同樣道理,Excel函數的應用沒有早晚的問題,可以用,知道怎麼用就對了。zanzan.tw © 2022

加強學習:贊贊小屋Excel函數文章

Excel函數教學:MAX、LARGE、LOOKUP查找最大值 15
加入Line社群,口袋裡的Excel小教室!