小夥伴們，大家好啊!
Excel中到底有多少個函數？這個問題可能99%的人都不知道（俺也不知道），今天作者可謂是費了九牛二虎之力特意從微軟Office官網查詢了一下，按照官網目前更新的資料一共有505個（嗯，就是這麼多，一個不多一個不少），我們90%的人能用到的函數也就幾十個，本期教程給大家整理了40個我們最常用的函數公式的應用技巧，可謂是幹貨滿滿，應付我們日常的工作是沒有問題的，大家收藏備用吧！
▶1.SUM函數：對數值、單元格引用或是區域相加求和
文法：SUM(number1,[number2],...)
- number1（必需）：要相加的第一個數，該參數可以是數字，單元格引用或單元格區域。
- number2（可選）：要相加的第二個數，可以按照這種方式最多指定 255 個數字。
示例：
- 求1，2，3的和 =SUM(1,2,3)
- 對一行資料（A2:B2）求和 =SUM(A2:B2)
- 對一列資料(A2:A11)求和 =SUM(A2:A11)
- 對多個區域(A2:A11,B2:B11)進行求和 =SUM(A2:A11,B2:B11)
▶2.SUMIF函數：對符合指定條件的值求和
文法：SUMIF(range, criteria, [sum_range])
- range（必須）：條件判斷的單元格區域
- criteria（必須）：條件表達式。 可包含通配符字元 - 問号（？）以比對任意單個字元，星号（*）以比對任意字元序列。 如果要查找實際的問号或星号，請在該字元前鍵入波形符（~）。例如，可以表示為 32、“>32”、B5、“3？”、“蘋果*”、“*~？”
- sum_range（可選）：求和區域，要和條件區域單元格數量一緻。 如果條件區域和求和區域相同，則可以省略
示例：
- 對銷售額大于5000的求和 =SUMIF(C2:C11,">5000")
- 求包含有“西”字的商品銷售額的和 =SUMIF(A2:A11,"*西*",C2:C11)
- 求水果的銷售額 =SUMIF(B2:B11,"水果",C2:C11)
▶3.SUMIFS函數：對滿足多個條件的值求和
文法：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range（必需）：要求和的單元格區域。
- criteria_range1（必需）：第一個條件區域
- criteria1（必需）：第一個條件表達式
- criteria_range2, criteria2（可選）：附加的區域及其關聯條件。 最多可以輸入 127 個區域/條件對。
示例：
- 求類型為水果且銷售額大5000的總和 =SUMIFS(C2:C11,B2:B11,"水果",C2:C11,">5000")
- 求商品名稱包含有“西”字類型為水果且商品銷售額大于5000的總和 =SUMIFS(C2:C11,A2:A11,"*西*",B2:B11,"蔬菜",C2:C11,">5000")
▶4. SUMPRODUCT 函數：傳回相應的數組或區域乘積的和
文法：SUMPRODUCT (array1， [array2]， [array3]， ...)
- array1（必需）：需要進行相乘并求和的第一個數組區域
- [array2]， [array3],... （可選）： 2 到 255 個數組區域，其相應元素需要進行相乘并求和
示例：
- 求所有商品的銷售額，數量×單價然後再相加 =SUMPRODUCT(C2:C11,D2:D11)
- 求水果的銷售額之和，類型為水果的商品數量×單價再相加 =SUMPRODUCT((B2:B11="水果")*C2:C11*D2:D11)
▶5.IF函數：判斷是否滿足條件，滿足傳回一個值,不滿足則傳回另一個值
文法：IF(Logical test, [Value_ if _true],[Value_ if false])
- logical test（必需）：判斷條件，是任何能被計算為TRUE或FALSE的數值或表達式。
- value_ if _true（可選）：是條件成立時的傳回值。如果忽略，則傳回TRUE。
- value_ if false（可選）：是當條件不成立時的傳回值。如果忽略，則傳回FALSE
示例：
- 判斷預算金額是否大于5萬 =IF(A2>50000,"是","否")
- 判斷是否超出預算 =IF(B3-A3>0,"超出預算","未超預算")
▶6.IFS函數：檢查是否滿足一個或多個條件，且傳回符合第一個滿足條件的值
文法：IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],[logical_test3,value_if_true3],…)
- logical_test1（必需）： 計算結果為 TRUE 或 FALSE 的條件。。
- value_if_true1（必需）：當 logical_test1 的計算結果為 TRUE 時要傳回結果。 可以為空。
- logical_test2…logical_test127（可選）：計算結果為 TRUE 或 FALSE 的條件。
- value_if_true2…value_if_true127（可選）：當 logical_testN 的計算結果為 TRUE 時要傳回結果。每個value_if_trueN 對應于一個條件 logical_testN。可以為空。
示例：
- 判斷學生成績，小于60分不及格，60-70及格，71-80良好，80以上優秀 =IFS(B2>=80,"優秀",B2>=70,"良好",B2>=60,"及格",TRUE,"不及格")
▶7.COUNT函數：計算包含數字的單元格個數
文法：COUNT(value1, [value2], ...)
- value1（必需）：要計算其中數字的個數的第一項、單元格引用或區域。
- value2, ...（可選）：要計算其中數字的個數的其他項、單元格引用或區域，最多可包含 255 個。
示例：
- 判斷(A2:A8)數字的個數 =COUNT(A2:A8)
- 判斷(A2:A8,G2:G8)數字的個數 =COUNT(A2:A8,G2:G8)
- 注意：在EXCEL中日期也會當作數字
▶8.COUNTIF函數：用于統計滿足某個條件的單元格的數量
文法：COUNTIF(range, criteria)
- range（必需）：要進行計數的單元格區域
- criteria（必需）：滿足計數的條件表達式。例如，可以使用 32 之類數字，“>32”之類比較，B4 之類單元格，或“蘋果”之類字元。
示例：
- 統計商品有多少種水果 =COUNTIF(B2:B11,"水果")
- 統計商品有多少種水果(引用單元格的方式) =COUNTIF(B2:B11,B2)
- 統計銷售數量大于30的商品個數 =COUNTIF(C2:C11,">30")
- 統計商品名稱中包含”西“字的商品個數 =COUNTIF(A2:A11,"*西*")
▶9.COUNTIFS函數：統計滿足多個條件的單元格的數量
文法：COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
- criteria_range1（必需）：在其中計算關聯條件的第一個區域。
- criteria1（必需）：條件的形式為數字、表達式、單元格引用或文本，它定義了要計數的單元格範圍。 例如，條件可以表示為 32、">32"、B4、"apples"或 "32"。
- criteria_range2,criteria2, ...（可選）：附加的區域及其關聯條件。最多允許 127 個區域/條件對。
示例：
- 統計商品有多少種水果 =COUNTIFS(B2:B11,"水果")
- 統計商品名稱中有包含"西"字的水果種類 =COUNTIFS(B2:B11,"水果",A2:A11,"*西*")
- 統計銷售數量在30-50之間商品個數 =COUNTIFS(C2:C11,">=30",C2:C11,"<=50")
▶10. COUNTA函數：計算範圍中不為空的單元格的個數
文法：COUNTA(value1, [value2], ...)
- value1（必需）：表示要計數的值的第一個單元格區域。
- value2, ...（可選）：表示要計數的值的其他單元格區域，最多可包含 255 個單元格區域。
示例：
- 判斷(A2:A8)非空單元格的個數 =COUNTA(A2:A8)
- 判斷(A2:A8,G2:G8)非空單元格的個數 =COUNTA(A2:A8,G2:G8)
▶11. COUNTBLANK 函數：計算單元格區域中的空單元格數
文法：COUNTBLANK (range)
- range（必需）：需要計算其中空白單元格個數的區域。
示例：
- 判斷(A2:A8)空單元格的個數 =COUNTBLANK(A2:A8)
▶12. VLOOKUP 函數：在表格或區域中按行查找内容。
文法：VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value（必需）：要查找的内容
- Table_array（必需）：要查找的位置
- col_index_num（必需）：傳回的值所在的列号
- range_lookup（可選）：查找比對模式，1/TRUE 精确比對，0/FALSE 近似比對
示例：
- 查找洋芋的數量 =VLOOKUP("洋芋",A1:C11,3,FALSE)
- 查找第一個包含“西”字的商品名稱 =VLOOKUP("*西*",A1:C11,1,FALSE)
▶13. INDEX 函數：傳回表格或區域中的值。
文法：INDEX(array, row_num, [column_num])
- array（必需）：單元格區域或數組常量
- row_num（必需）：傳回值所在的行号
- column_num（可選）：傳回值所在的列号，如果所選的區域隻有1列可以省略
示例：
- 查找右表中第5行第2列的資料 =INDEX(A1:B11,5,2)
- 查找A列第5行的資料 =INDEX(A:A,5)
▶14. MATCH 函數：傳回該查找值在此區域中的相對位置
文法：MATCH(lookup_value, lookup_array, [match_type])
- lookup_value（必需）：要在區域中比對的值
- lookup_array（必需）：要搜尋的單元格區域
- match_type（可選）：查找值比對方式。為1或省略查找小于或等于查找值的最大值，單元格區域中的值必須以升序排序；為0時查找完全等于 lookup_value 的第一個值；為-1時查找大于或等于 lookup_value 的最小值。 單元格區域中的值必須按降序排列
示例：
- 查找小紅在A列中所在的行号 =MATCH("小紅",A:A,0)
- 查找小紅的成績（配合INDEX函數實作） =INDEX(B:B,MATCH("小紅",A:A,0))
▶15. ROW 函數：傳回單元格所在的行号
文法：ROW([reference])
- reference（可選）：需要得到其行号的單元格或單元格區域，如果省略，則傳回公式所在單元格的行号
示例：
- 傳回公式所在單元格的行号 =ROW()
- 傳回A1單元格所在的行号 =ROW(A1)
▶16. COLUMN函數：傳回單元格所在的列号
文法：COLUMN([reference])
- reference（可選）：需要得到其列号的單元格或單元格區域，如果省略，則傳回公式所在單元格的列号
示例：
- 傳回公式所在單元格所在的列号（公式在C1中） =COLUMN()
- 傳回B1單元格所在的列号 =COLUMN(B1)
▶17. FIND 函數：查找一個文本字元串内在另一個文本字元串中出現的位置
文法：FIND(find_text,within_text,[start_num])
- find_text（必需）：要查找的文本
- within_text（必需）：是包含要查找文本的文本
- start_num（可選）：指定從哪裡開始查找。如果省略則從第1位開始查找
示例：
- 查找字母“C”在字元串中的位置 =FIND("C",A2)
- 查找字串“函數”在字元串中的位置 =FIND("函數",A2)
- 查找字母“E”在字元串中的位置,從第3位開始找 =FIND("E",A2,3)
▶18. LEFT 函數：傳回從字元串左側算起指定數量的字元
文法：LEFT(text, [num_chars])
- text（必需）：包含要提取字元的文本字元串
- num_chars（可選）：訓示要傳回的字元數
示例：
- 截取A2單元格字元串前5位 =LEFT(A2,5)
- 先用FIND函數找出“-”所在的位置，然後在配合LEFT函數截取 =LEFT(A2,FIND("-",A2)-1)
▶19. RIGHT 函數：根據指定的字元數，傳回文本字元串的最後一些字元
文法：RIGHT(text,[num_chars])
- text（必需）：包含要提取字元的文本字元串
- num_chars（可選）：訓示要傳回的字元數
示例：
- 截取A2單元格字元串後4位 =RIGHT(A2,4)
- 用計算字元串總長度減去“-”号所在的位置，擷取後面要取得字元的長度，然後用RIGHT函數擷取 =RIGHT(A2,LEN(A2)-FIND("-",A2))
▶20. MID 函數：傳回從字元串左側算起指定數量的字元
文法：MID(text,start_num,num_chars)
- text（必需）：是包含要提取字元的文本字元串
- start_num（必需）：是要在文本中提取的第一個字元的位置。文本中第一個字元的 start_num 為 1，以此類推
- num_chars（必需）：指定希望從文本中傳回字元的個數
示例：
擷取字元串從第3位開始2個字元 =MID(A2,3,2)
▶21. SUBSTITUTE 函數：在文本字元串中用新字元串替換舊字元串
文法：SUBSTITUTE(text, old_text, new_text, [instance_num])
- text（必需）：包含需要替換的字元串的文本
- old_text（必需）： 需要替換的字元串
- new_text（必需）：用于替換的字元串
- Instance_num（可選）：指定要将第幾個 old_text 替換為 new_text。 如果指定了，則隻有滿足要求的 old_text 被替換。 否則，文本中出現的所有 old_text 都會被替換。
示例：
- 将字元串中“-”替換為空 =SUBSTITUTE(A2,"-","")
- 将字元串中“E”全部替換為“6” =SUBSTITUTE(A2,"E","6")
- 将字元串中第1個“E”替換為“6” =SUBSTITUTE(A2,"E","6",1)
▶22. REPT 函數：将文本重複一定次數
文法：REPT(text, number_times)
- text（必需）：需要重複顯示的文本
- number_times（必需）：重複次數
示例：
- 将A2單元個資料重複2遍 =REPT(A2,2)
- 将實心五角星重複3遍，空心五角星重複2遍 =REPT("★",3)&REPT("☆",2)
▶23. TEXT 函數：通過格式代碼向數字應用格式，更改數字的顯示方式
文法：TEXT(value,format_text)
- value（必需）：要格式化的數字
- format_text（必需）：格式代碼
示例：
- 将小數轉換為貨币并保留2位小數 =TEXT(A2,"￥#,##0.00")
- 将日期轉換為年月日的形式 =TEXT(A3,"yyyy年mm月dd日")
- 将日期轉換為星期 =TEXT(A4,"aaaa")
- 将資料固定6為長度，不足在前面補0 =TEXT(A5,"000000")
- 轉換成百分比 =TEXT(A6,"0%")
- 擷取日期當中的時間 =TEXT(A7,"h時mm分")
▶24. TEXTJOIN 函數：将多個區域和/或字元串的文本連接配接起來
文法：TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter（必需）：分隔符
- ignore_empty（必需）：是否忽略空白單元格，如果為TRUE，則忽略
- text1（必需）：要聯接的文本字元串或單元格區域
- text2，... （可選）：要聯接的其他文本項。 文本項最多可以包含 252 個文本參數 text1。 每個參數可以是一個文本字元串或單元格區域
示例：
- 将A2:B2的資料連接配接，并且用“/”分隔 =TEXTJOIN("/",TRUE,A2:B2)
- 将A2:B2的資料連接配接，不使用分隔符 =TEXTJOIN("",TRUE,A2:B2)
- 将A2:A4的資料連接配接，并且用逗号分隔 =TEXTJOIN(",",TRUE,A2:A4)
- 将B2:B4的資料連接配接，并且用"-"分隔 =TEXTJOIN("-",TRUE,B2:B4)
▶25. DAY 函數：傳回日期的天數,天數是介于 1 到 31 之間的整數
文法：DAY(serial_number)
- serial_number（必需）：要查找的日期
示例：
- 傳回天數（單元格引用） =DAY(A2)
- 傳回天數（值） =DAY("2023/7/5")
▶26. MONTH 函數：傳回日期中的月份,月份是介于1-12之間的整數
文法：MONTH(serial_number)
- serial_number（必需）：要查找的日期
示例：
- 傳回月份（單元格引用） =MONTH(A2)
- 傳回月份（值） =MONTH("2023/7/5")
- 傳回日期月份對應的季度，用月份除以3，然後向上取整，再通過TEXT函數格式化文本顯示 =TEXT(ROUNDUP(MONTH(A2)/3,0),"第0季度")
▶27. YEAR 函數：傳回日期的年份，傳回值為1900 - 9999之間的整數
文法：YEAR(serial_number)
- serial_number（必需）：要查找的日期
示例：
- 傳回天數（單元格引用） =YEAR(A2)
- 傳回天數（值） =YEAR("2023/7/5")
▶28. TODAY 函數：傳回目前日期
文法：TODAY()
示例：
- 傳回目前日期 =TODAY()
- 傳回當月份（和MONTH函數結合使用） =MONTH(TODAY())
▶29. NOW 函數：傳回目前日期和時間
文法：NOW()
示例：
- 傳回目前日期時間 =NOW()
- 傳回目前時間（和TEXT函數結合使用） =TEXT(NOW(),"h:mm:ss")
▶30. WEEKDAY 函數：傳回對應于某個日期的一周中的第幾天, 預設周日是第1天
文法：WEEKDAY(serial_number,[return_type])
- serial_number（必需）：查找的日期
- return_type（可選）：按照下面哪種規則傳回數字（一般用2）。1 或省略 數字 1（星期日）到 7（星期六）；2 數字 1（星期一）到 7（星期日）；3 數字 0（星期一）到 6（星期日）
示例：
- 傳回A2單元格中的日期是本周第幾天 =WEEKDAY(A2,2)
- 傳回目前日期是本周第幾天 =WEEKDAY(TODAY(),2)
- 傳回2023/8/3是本周第幾天 =WEEKDAY("2023/8/3",2)
▶31. EDATE 函數：傳回一個日期相隔多少月後的新日期
文法：EDATE(start_date, months)
- start_date（必需）：開始日期
- months（必需）：開始日期之前或之後的月份數。為正值将生成未來日期；為負值将生成過去日期。
示例：
- 傳回後1個月的日期 =EDATE(A2,1)
- 傳回前1個月的日期 =EDATE(A2,-1)
- 傳回後2個月的日期 =EDATE(A2,2)
▶32. DATEDIF 函數：計算兩個日期之間相隔的天數、月數或年數（隐藏函數）
文法：DATEDIF(start_date,end_date,unit)
- start_date（必需）：開始日期
- end_date（必需）：結束日期
- unit（必需）：要傳回的資訊類型。"Y" 傳回年數；"M" 傳回月數；"D" 傳回天數；"MD" 傳回天數，忽略日期中的月份和年份；"YM" 傳回月數，忽略日期中的天和年份；"YD" 傳回天數，忽略日期中的年份
示例：
- 計算2個日期相隔的天數 =DATEDIF(A2,B2,"D")
- 計算2個日期相隔的月數 =DATEDIF(A2,B2,"M")
- 計算2個日期相隔的年數 =DATEDIF(A2,B2,"Y")
▶33. RANDBETWEEN 函數：傳回位于兩個指定數之間的一個随機整數
文法：RANDBETWEEN(bottom, top)
- bottom（必需）：傳回的最小整數
- top（必需）：傳回的最大整數
示例：
- 生成一個1到100的随機整數 =RANDBETWEEN(1,100)
- 生成一個-10到10的随機整數 =RANDBETWEEN(-10,10)
▶34. MAX 函數：傳回一組值中的最大值
文法：MAX(number1, [number2], ...)
- number1, number2, ...（必需）：number1 是必需的，後續數字是可選的。 要從中查找最大值的 1 到 255 個數字
示例：
- 傳回A2:A11中的最大值 =MAX(A2:A11)
- 傳回A2:A11 和數值 90 之中的最大值 =MAX(A2:A11,90)
▶35. MIN 函數：傳回一組值中的最小值
文法：MIN (number1, [number2], ...)
- number1, number2, ... （必需）：number1 是可選的，後續數字是可選的。要從中查找最小值的 1 到 255 個數字。
示例：
- 傳回A2:A11中的最小值 =MIN(A2:A11)
- 傳回A2:A11 和數值 10 之中的最小值 =MIN(A2:A11,10)
▶36. ROUND 函數：将數字四舍五入到指定的位數
文法：ROUND(number, num_digits)
- number（必需）： 要四舍五入的數字
- num_digits（必需）：要進行四舍五入運算的位數
示例：
- 四舍五入保留2位小數 =ROUND(A2,2)
- 四舍五入保留1位小數 =ROUND(A3,1)
- 四舍五入不保留小數 =ROUND(A4,0)
- 四舍五入為最接近的 10 的倍數 =ROUND(A5,-1)
- 四舍五入為最接近的 100 的倍數 =ROUND(A6,-2)
▶37. INT 函數：将數字向下舍入到最接近的整數
文法：INT( number )
- number（必需）： 需要進行向下舍入取整的數字
示例：
- 向下舍入到最接近的整數 =INT(A2)
- 向下舍入到最接近的整數,負數會朝着遠離 0 的方向将數字舍入 =INT(A3)
- 傳回單元格 A4 中的小數部分 =A4-INT(A4)
▶38. ABS 函數：傳回數字的絕對值
文法：ABS(number)
- number（必需）： 需要計算其絕對值的數值
示例：
- 傳回A2的絕對值 =ABS(A2)
- 傳回A3的絕對值 =ABS(A3)
▶39. MOD 函數：傳回兩數相除的餘數，結果的符号與除數相同
文法：MOD(number, divisor)
- number（必需）： 被除數
- divisor（必需）：除數
示例：
- 傳回A2/3的餘數 =MOD(A2,3)
- 判斷A3奇偶數 =IF(MOD(A3,2),"奇數","偶數")
▶40. AVERAGE 函數：傳回參數的平均值
文法：AVERAGE(number1, [number2], ...)
- number1（必需）：要計算平均值的第一個數字、單元格引用或單元格區域
- number2, ...（可選）：要計算平均值的其他數字、單元格引用或單元格區域，最多可包含 255 個
示例：
- 單元格區域 A2 到 A11 中數字的平均值 =AVERAGE(A2:A11)
- 單元格區域 A2 到 A11 中數字與數字 100 的平均值 =AVERAGE(A2:A11,100)
以上是整理出來的40個常用excel函數，有疏漏的地方還請各位看官老爺指正，也希望對大家有所幫助。