只有萌新才知道的事（公式簡化萌新瑟瑟發抖）

2023-04-22 00:30:03 2

很多人都奇怪，為什麼老師們一個很簡潔的公式寫出來的效果，我要寫一長串?

在函數圈有一句話:」思路決定出路」，很多時候你寫的公式長度取決於你知道的函數數量和參數性質。

以下是一些典型化簡案例，希望對大家有所幫助。

1，填0佔位

把數字控制成固定位數的字符串，新手通常使用TEXT的第二參數添加添加對應個數的0解決，所以總是要在心裡默數0的個數。

大佬從來不數0，而是而是用REPT的第二參數來控制0的個數。

萌新默默路過，藉助BASE函數的第三參數來指定0的個數。

(BASE2參數為指定的進位數，這個用法下數據10進位不變，故2參數固定為10(進位))。

因為能對數字位數處理的函數不是只有文本函數的(注意BASE是2013新增的)。

2，定位最後1個」-」的位置

新手一般不了解數組公式，因此要先用LEN SUBSTITUTE的思路確認」-」的數量後作為SUBSTITUTE的4參數(此時只替換這個」-」)，

把最後這個「-」替換為一個字符串內不存在的特殊字符(這裡用@)，利用FIND查找這個特殊字符的位置，即為結果。

大佬都是熟悉數組的，利用二分法以大欺小原則下的1，0/結構來比較每個字符是否為」-」，結果即最後1個」-」的位置。

論壇的萌新熟悉各種函數的各參數，利用FIND的3參數性質配合能忽略錯誤值的COUNT，計數結果就是所求。

3，日期類計算

求本月最後1個周六的對應日期：

日期類問題是函數問題裡一類相對比較常見的題目，新手通常處理成下個月第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，判斷勝負

新手公式

=IF(--LEFT(B2,FIND("-",B2)-1)>--MID(B2,FIND("-",B2) 1,9),"勝",IF(--LEFT(B2,FIND("-",B2)-1)=--MID(B2,FIND("-",B2) 1,9),"平","負"))

大佬公式

=TEXT(LEFT(B2,FIND("-",B2)-1)-MID(B2,FIND("-",B2) 1,9),"勝;負;平")

萌新公式

=TEXT(IMREAL(IMDIV(B2&"I","1 I")),"勝;負;平")

這類問題新手只會先把兩部分比分，分別提取出來比較或計算差值，用IF分別判斷差值分別為正數，負數或者0來確認結果是勝負還是平局。

大佬會使用TEXT來把2次IF簡化為TEXT的正數位;負數位;0位來獲取對應結果。

至於」萌新」，能只引用1次的絕不引用2次數據源，利用複數結構和IMDIV的特殊算法來簡化計算A B(I)的複數結構的A B的結果(這裡的數據結構B是負值)。

(利用對應參數C DI實現AC BD 為A-B算法，用IMREAL提取複數結構中的實部係數，即為需要的2個數的差值的正負情況。

6，最大值之和

求各學科的最大值之和

新手有多少列，就用多少個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))

(注意數組三鍵，Z1:Z2是任意空白區域，利用資料庫函數性質默認條件為全部)

7，不規範時間格式處理

很多時候數據源是不規範的，譬如我們要用這種小數處理成正確的對應時間格式。

新手通常分別分別提取小時和分鐘部分後用TIME函數構成對應的正確時間(這裡INT和MOD*100已經是簡潔思路了，如果用LEFT FIND會更繁瑣)。

大佬級會直接*100處理成整數後用TEXT進行格式處理，變成正確的時間格式，最後用減負運算變成真數值(時間)。

「萌新」利用財務函數DOLLARDE對小數部分進行進位換算後直接用時間計算方式(1天是24小時)處理成正確時間。

這個函數適合處理這種整數和小數位的進位換算不同的情況。

人外有人，天外有天，函數這條路是沒有終點的，簡化公式不僅是為了鍛鍊提升，也是為了把正確的函數拿來做正確的事情｡

作者：流浪鐵匠