很多小夥伴提出過這樣的疑問:為什麽高手們一個很簡潔的公式寫出來的效果,我要寫一長串? 在函式圈有一句話:」思路決定出路 」,很多時候你寫的公式長度取決於你知道的函式數量和參數性質。
以下是一些典型化簡案例,希望對大家有所幫助。
1,填0占位
新手公式:
=TEXT(A2,"000000000000")
大佬公式:
=TEXT(B2,REPT(0,12))
萌新公式:
=BASE(A2,10,12)
把數位控制成固定位數的字串,新手通常使用TEXT的第二參數添加添加對應個數的0解決,所以總是要在心裏默數0的個數。
大佬從來不數0,而是而是用REPT的 第二 參數來控制0的個數。
萌新默默路過,借助BASE函式的 第三 參數來指定0的個數。
BASE2參數為指定的進制數,這個用法下數據10進制不變,故2參數固定為10(進制)。
2,定位最後1個」-」的位置
新手公式:
=FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",))))
大佬公式: 陣列三鍵
=MATCH(1,0/(MID(A2,ROW($1:$99),1)="-"))
萌新公式: 陣列三鍵
=COUNT(FIND("-",A2,ROW($1:$99)))
新手一般不了解陣列公式,因此要先用LEN+SUBSTITUTE的思路確認」-」的數量後作為SUBSTITUTE的4參數(此時只替換這個」-」),
把最後這個「-」替換為一個字串內不存在的特殊字元(這裏用@),利用FIND尋找這個特殊字元的位置,即為結果。
大佬都是熟悉陣列的,利用二分法以大欺小原則下的1,0/結構來比較每個字元是否為」-」,結果即最後1個」-」的位置。
論壇的萌新熟悉各種函式的各參數,利用FIND的3參數性質配合能忽略錯誤值的COUNT,計數結果就是所求。
3,日期類計算
求本月最後1個周六的對應日期:
新手公式:
=EDATE(DATE(A2,B2,1),1)-WEEKDAY(EDATE(DATE(A2,B2,1),1),1)
大佬公式:
=DATE(A2,B2+1,)-MOD(DATE(A2,B2+1,)-7,7)
萌新公式:
=FLOOR(DATE(A2,B2+1,),7)
日期類問題是函式問題裏一類相對比較常見的題目, 新手通常處理成下個月第1天後用WEEKDAY的差值(註意此時這函式2參數)返回上一個周六。
大佬在日期函式基礎上可以用MOD來簡化公式。
而萌新在解決日期類問題時,經常直接把函式題變成數學題來大幅簡化公式。
4,日期類計算Ⅱ
計算兩個日期之間,周一/周三/周五的總天數:
新手公式:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=1))
+SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=3))
+SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5))
這類題純新手很難在不使用輔助列的前提下實作,會百度的小夥伴可能會用第1個公式的思路分別求2個日期間周一/周三/周五的分別天數再求和。
大佬公式:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,3,5}))
大佬的陣列公式都是很熟練的,可以把3段簡化為1段,利用二維陣列簡化公式。
萌新公式:
=NETWORKDAYS.INTL(A2,B2,"0101011")
萌新總是能找到正確的函式做正確的事情,利用NETWORKDAYS.INTL的3參數利用1和0的7位數序列來指定控制計算周幾。
(周末字串值的長度為七個字元,並且字串中的每個字元表示一周中的一天(從星期一開始)。 1 表示非工作日,0 表示工作日。 在字串中僅允許使用字元 1 和 0。 使用 1111111 將始終返回0。
例如,0000011 結果為星期六和星期日是周末。因此某些不以周六和周日為指定休息日的計算,用NETWORKDAYS.INTL和WORKDAY.INTL這兩個函式會簡化很多。
5, 最大值之和
求各學科的最大值之和
新手有多少列,就用多少個MAX。
=MAX(B2:B6)+MAX(C2:C6)+MAX(D2:D6)+MAX(E2:E6)+MAX(F2:F6)+MAX(G2:G6)+MAX(H2:H6)+MAX(I2:I6)
大佬都是熟悉多維參照的,利用SUBTOTAL和OFFSET的多維參照效果進行求和
=SUM(SUBTOTAL(4,OFFSET(A2:A6,,COLUMN(A:H))))
(註意陣列三鍵)
而對於萌新而言,能用於多維參照的函式不僅僅是SUBTOTAL和SUMIF這些,資料庫函式也是可以的 (註意陣列三鍵) :
=SUM(DMAX(A1:I6,COLUMN(B:I),Z1:Z2))
當然,如果你使用的是Excel 365或者最新的WPS表格,還可以使用以下公式:
=SUM(BYCOL(B2:I6,LAMBDA(x,MAX(x))))
BYCOL與LAMBDA結合,提取出每一列的最大值,最後使用SUM函式求和。
6,不規範時間格式處理
很多時候資料來源是不規範的,譬如我們要用這種小數處理成正確的對應時間格式。
新手通常分別分別提取小時和分鐘部份後用TIME函式構成對應的正確時間:
=TIME(INT(A2),MOD(A2,1)*100,0)
大佬級會直接*100處理成整數後用TEXT進行格式處理,變成正確的時間格式,最後用減負運算變成時間序列值:
=--TEXT(A2*100,"0!:00")
萌新利用財務函式DOLLARDE對小數部份進行進制換算後直接用時間計算方式(1天是24小時)處理成正確時間:
=DOLLARDE(A2,6)/24
這個函式適合處理這種整數和小數位的進制換算不同的情況。
人外有人天外有天,函式這條路是沒有終點的,簡化公式不僅是為了鍛煉提升,也是為了把正確的函式拿來做正確的事情。
好了,今天的內容就是這些,祝大家一天好心情。
作者:流浪鐵匠