MODE函式三兄弟,能玩出花的冷門函式
MODE什麼意思?眾數呀!說人話……
一組資料中出現次數最多的數值,可能有多個!
別看不起眼的說明,後面 我們能玩出花來了!
三兄弟的來歷
開啟Excel輸入,MODE提示有三個兄弟!MODE是大哥,兩個是弟弟!
MODE最早出現,只能返回一個出現次數最多的值,即使有多個! 後來微軟說要不咋出一個可以多個的吧!然後就出了
> MODE.MULT函式,
MULT是MULTI的縮寫,表示多個!
同時出了一個和多對應的
>
MODE.SNGL函式
,SNGL差不多是single的意思,也就是僅一個!
既然兩個都有了,於是微軟決定棄用MODE函式,所以我們現在看到MODE函式上面有一個感嘆號,不推薦使用了!
基礎用法對比
我們透過一個簡單的案例,來對比一下他們的區別!
=MODE($B$2:$B$11)=MODE。SNGL($B$2:$B$11)=MODE。MULT(B2:B11)
在出現次數最多的時候,他們的結果是完全一致的!
我們稍微修改一下模擬的分數,讓3也出現3次,這樣出現次數最多的就有3和4了!
出現次數最多的有多個時
MODE.MULT
的效果顯然!同時MODE和MODE。SNGL效果是完全一樣的,偷懶完全還可以使用MODE,只是微軟不推薦了!
如果僅僅是這樣的用法,我們會發現好像用處也不大,所以我們要結合其他函式,讓他能“玩出花”
例項應用
我們通過幾個案例,由淺入深來聊聊MODE系列的用法!
案例01 | 出席會議次數最多的人是誰?
▼對應公式 - 365版本動態陣列
=INDEX($B$2:$B$15,MODE。MULT(MATCH($B$2:$B$15,$B$2:$B$15,)))
▲ 非O365請配合INDEX+SMALL提取
這裡主要藉助於MATCH函式的特性,重複內容返回
首次出現
的位置!
比如呂布,首次出現在我們區域B2:B15的第2行,後面再發現呂布還是返回2(首次發現相對於選取區域的位置)
這樣我們就把文字問題成功轉化成了數值出現次數問題!
案例02 | 銷售達標人數最多的組
▼對應公式
=INDEX($A$2:$A$21,MODE。MULT(IF($D$2:$D$21>$C$2:$C$21,MATCH($A$2:$A$21,$A$2:$A$21))))
公式原理概述:
IF函式判斷實際是否大於目標,如果大於就是滿足條件的,我們執行MATCH部分,MATCH上面已經講過,返回首次滿足條件的位置,這也也是同理,否則就是
返回預設的邏輯FASLE,非數值會被MODE.MULT函式過濾掉
!
最後找到滿足條件最多的組所對應的行號,配合INDEX來提取
案例03 | 提取所有金額並求和
▼公式看上去有點長
=SUM(IFERROR(——MID(TEXT(RIGHT(LEFT(A2,MODE。MULT(ROW($1:$99)-(MID(A2&“元”,ROW($1:$99),1)=“元”))),COLUMN($A:$O)),),2,99),))
公式概述:
1、
MODE.MULT部分
:擷取到元就會返回TRUE=1,否則返回FALSE=0
這樣只要是有的地方,就會-1,返回上一個位置,也就是元的前一位,那麼就會出現2次,一般ROW(1:99)=1,2,3,……99,是不可能重複,現在2次的就算出現次數最多的,所以返回所有出現2次的數值,也就是所有元對應的上一個位置!
2、其他部分我們大概說一下思路,非本次主題!
> 使用LEFT擷取到每次數值的位置!這樣我們需要的數值都在末尾部分了。
> 下面就從末尾擷取也就是用RIGHT右邊擷取,但是每個數值長度不同,如何才能精確找到呢?技巧就是先依次擷取!
> 我們的目標是對應的值,但是長度不定,我們可以先把截取出來的數值全部處理掉,因為裡面還有其他數值。TEXT顯示為空
> 我們要的數值已經出現,規律就是第2位擷取到最後,其他部分擷取肯定包括非數值,不可能轉成數值!
>——轉數值,文字報錯,使用IFEROR轉成0,最後求和即可!
> 最後,SUM求和一下即可!
其實只要你思路清晰,很快就能寫出來,跟公式長度沒有關係!
MODE。MULT函式,我們就玩到這裡!別小看任何一個函式,他們組隊起來,真的有無限可能……
Excel辦公實戰
一起學習Excel,0基礎、進階、高階圖文動畫教程!技巧,函式,技巧,VBA,視覺化圖表,PBI!影片教程、免費模板、精英培訓!VLOOKUP系列教程! 997篇原創內容