【原】一組Excel函數(shù)公式，加班黨的“春天”來了

Excel教程平臺 2023-03-08 發(fā)布于四川

展開全文

推薦公式1

現(xiàn)在有兩個月的銷售流水，如圖所示。

需要統(tǒng)計出兩個月都有銷售的人數(shù)，就可以使用公式=COUNT(MATCH(A2:A7,E2:E7,0))

這是一個數(shù)組公式，先由MATCH函數(shù)對A列每一個姓名在E列去做匹配，重復的姓名得到一個數(shù)字，不重復的則得到錯誤值。

再由COUNT統(tǒng)計出數(shù)字的個數(shù)，也就是兩個月重復的姓名。

推薦公式2

根據(jù)員工的入職日期和需要實習的月數(shù)計算出轉正日期。

公式為：=EDATE(B2,C2)

EDATE函數(shù)只需要兩個參數(shù)，第一參數(shù)為開始日期，第二參數(shù)為與開始日期間隔的月數(shù)，例如公式=EDATE(B2,C2)中，開始日期為2023-1-9，間隔的月數(shù)為3，結果表示開始日期之后3個月所對應的日期，即2023/4/9。

推薦公式3

公司按照不同的銷售額制定了傭金比例，現(xiàn)在需要根據(jù)實際銷售業(yè)績計算出應得傭金。

公式為=LOOKUP(B2,{0;1;3;5;8;10},$F$2:$F$7)*B2

在使用LOOKUP進行區(qū)間查找時需要注意兩個要點，一定是使用的區(qū)間下限，并且下限按照從小到大的順序排列。公式中的{0;1;3;5;8;10}就是各銷售額區(qū)間的下限值。

推薦公式4

要把分布在4列的人員姓名合并到一列，可以使用公式=OFFSET($A$1,MOD(ROW(A5),5)+1,INT(ROW(A5)/5)-1)

這個公式的關鍵是構造出OFFSET的第二、三參數(shù)。

行偏移的規(guī)律：數(shù)據(jù)源一共只有5行，當一列數(shù)據(jù)引用完成后就要從第二列繼續(xù)重復1到5的變化，因此需要使用循環(huán)數(shù)列的構造方法，用公式=MOD(ROW(A5),5)+1得到1到5的循環(huán)數(shù)。

列偏移的規(guī)律：五個數(shù)據(jù)位于同一列，當一列引用完成后就變?yōu)榈诙?，因此列位置是五個一組的重復數(shù)列，使用公式=INT(ROW(A5)/5)-1可以得到所需結果。

推薦公式5

按不同的規(guī)格統(tǒng)計各種材質的包裝盒數(shù)量。

公式為=SUMPRODUCT(($B$2:$B$7=E2)*$C$2:$C$7)

看到這個問題可能有的同學會說，也可以用SUMIF啊。

不妨自己驗證一下SUMIF的結果是否正確，把你的驗證結果回復到評論區(qū)。

推薦公式6

根據(jù)選舉結果在所有候選人名單中備注出是否已入選，公式為=IF(COUNTIF($D$2:$D$7,A2),"是","")

公式中的COUNTIF($D$2:$D$7,A2)可以得到0和1兩種結果，結果為0的就是未入選人員，在用COUNTIF進行數(shù)據(jù)核對的時候，搞清楚數(shù)據(jù)區(qū)域和條件是哪個很重要，在本例中，要判斷A列的姓名是否存在于D列，因此第一參數(shù)使用$D$2:$D$7，要進行統(tǒng)計的姓名是A2。

然后再嵌套IF將結果為1的顯示為“是”即可。

推薦公式7

要統(tǒng)計出所有人員最后一次的銷量合計，可以使用公式=SUMIF(B3:F7,"",B2)

在這個公式中，條件區(qū)域是B3:F7，從銷售數(shù)據(jù)的第二行開始到最后一個銷售數(shù)據(jù)下面一行結束，條件值為兩個雙引號表示空值，求和區(qū)域縮寫為第一個銷售數(shù)據(jù)所在的單元格B2，完整的求和區(qū)域是B2:F6。公式表示對條件區(qū)域中每個空白單元格上方單元格中的數(shù)字進行匯總，這種用法要求每列不能出現(xiàn)空單元格，如果沒銷售也需要用0填充，否則會造成結果錯誤。

推薦公式8

分別統(tǒng)計出各區(qū)域每個月的收入支出合計

收入合計公式為：=SUMIF($B$2:$K$2,"收入",$B3:$K3)

支出合計公式為：=SUMIF($B$2:$K$2,"支出",$B3:$K3)