從這個案例來講,它包含了三大内容,一是正常排序,二是條件排序,三是多個關鍵字查找引用。
excel的排序問題,大多數情況下都是通過資料工具欄下的排序功能來操作的。
本文也會在最後通過排序操作動圖來對比公式排序的結果。
但如果在一些特定情境下,需要使用公式來進行資料排序,并設定了指定的排序條件,那麼這樣的情況下,該使用什麼函數來寫這個公式!?
通過最後公式的介紹,也能了解關于其中函數的一些特性群組合應用。
下面作者以某加工企業的執行個體來講解。
如下圖所示,現在需要先對資料表中的線材規格清單進行從大到小的排序,然後再對同一線材規格下的标準線材長度進行從大到小的排序,最後再将對應的線材總支數引用過來。
關于線材規格的排序,屬于正常排序應用,而後續對線材長的排序,則屬于條件排序,即指定線材規格,按照大小進行不重複排序。
從這個案例來講,它包含了三大内容,一是正常排序,二是條件排序,三是多個關鍵字引用。
根據重點,作者将介紹條件排序的公式寫法和含義。
首先,我們還是來看看解題的第一步。
一、對線材規格進行排序
由于是從大到小來排序,是以作者選擇了large函數。
large函數的作用是傳回清單中指定第K大的值。
其文法圖如下所示:
表達式為:=large(單元格區域,第K大)
根據其文法表達,我們可以輸入一個公式:
=LARGE($A$2:$A$38,ROW(A1))
這個公式重點在于第2參數的設定,它嵌套了row函數,而row函數最大的特點是通過下拉填充能夠獲得動态結果。比如row(A1)等于1,下拉到A2單元格,即為row(A2),結果等于2.
是以輸入公式後,下拉填充就能得到從第1大的值一直到最後一個最小的值。
二、對指定線材規格的線材長度進行排序
得到了排序後的線材規格清單,我們需要根據相同的線材規格,來進行線材長度從大到小的排序。
既然還是大小排序的場景,那麼我們再利用large函數來寫一個公式:
=LARGE(IF($A$2:$A$38=E2,$B$2:$B$38),COUNTIF($E$2:E2,E2))
這個公式組合了large、if、countif函數,其中if函數起到了什麼作用呢?countif函數又有什麼特殊效果。
首先if函數是一個邏輯判斷函數,根據公式中的if函數表達式“IF($A$2:$A$38=E2,$B$2:$B$38)”。
它的含義是當A列的資料等于E2到的值,則傳回B列的資料。
而這個地方我們需要看到if函數的一個特性,即當A列資料不等于E2的值的時候,它會傳回一個邏輯值false。
從資料表可以看出,if函數表達式的結果肯定是包含了兩組不同的結果值,一組是false,另一組則是符合條件傳回的資料值。
我們按下快捷鍵來解析if函數表達式的預覽結果:
通過if函數我們得到了一個包含邏輯值和數值的數組區域。
此時我們便可以利用large函數忽略邏輯值的特性,來對這個數組區域内的數值進行大小排序。
那我們就要來看看第2參數countif函數表達式的含義了:COUNTIF($E$2:E2,E2)
這是條件計數函數countif的正常表達,表示清單區域$E$2:E2包含E2的值的單元格個數。
大家注意一個要點,在countif函數的第1參數清單區域中,使用了單元格區域的混合引用,即首個單元格為絕對引用,結尾單元格則是相對引用。
這個寫法,與row函數的作用有點類似,它會通過下拉填充公式時,得到一個動态擴充的區域結果。也就是将,當填充公式到E6單元格時,其表達式中的清單區域則等于“$E$2:E6”。
是以當countif函數的第1參數清單區域發生了變化,其計數結果也會相應變動。
最後,需要注意三鍵運作公式,才能得到正确結果。
我們填充一下公式,看看最後的排序效果:
三、多個關鍵字的查找引用
關于多個關鍵字的查詢引用,則可以利用“vlookup+if{1,0}”的固定套路來寫公式。
完整公式為:=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$38&$B$2:$B$38,$C$2:$C$38),2,0)
這裡作者不再詳述該公式的含義,大家可以參照作者專欄《excel100個常見場景可套公式》中關于該段内容的講解。
最後,我們再來看下使用排序功能,進行自定義條件排序的操作,如下動圖所示:
從動圖可見,公式排序的結果與功能排序的結果是一緻的!
總結一下,今天主要介紹了large函數公式的兩種案例介紹,一個是嵌套row函數的用法,另一個則是large組合if和countif函數的數組公式應用。
我們需要了解當中if函數和countif函數表達式的具體含義,以及在large函數中起到的作用!