【excel】常用的函数整理

这份内容是之前在一个公众号（也忘记是哪个了）的文章看到的，然后自己整理保存下来，现在分享出来。

• Round函数、int函数：四舍五入与取整
  =Round（数值，保留的小数位数）
  =int（数值）

需求：对A1的小数四舍五入保留2位小数位
=Round(A1,2)

需求：对A1的小数取整
=INT(A1)

• Sumif和Countif函数：按条件求和、计数
  =sumif（判断区域，条件，求和区域）
  =countif（判断区域，条件）

需求：在F2统计A产品的总金额
=sumif(A:A,E2,C:C)

需求：在F2统计A产品的数量
=countif(A:A,E2)

• Sumifs和Countifs函数：多条件求和、计数
  =Sumifs（求和区域，判断区域1，条件1，判断区域2，条件2… …）
  =Countifs（判断区域1，条件1，判断区域2，条件2… …）

需求：统计郑州所有电视机的销量之和
=sumifs(C2:C7,A2:A7,A11&“*”,B2:B7,B11)

• averageifs函数：多条件统计平均值
  =averageifs（统计区域，条件区域1，条件1，条件区域2，条件2 … …）

需求：统计账务部大专文凭的平均工资
=averageifs(D:D,B:B,“财务”,C:C,“大专”)

• Datedif函数：日期的间隔计算
  间隔年数=Datedif（开始日期，结束日期，“y”）
  间隔月份数=Datedif（开始日期，结束日期，“m”）
  间隔天数=Datedif（开始日期，结束日期，“d”）

需求：B列为入职日期， 要求计算入职多少月
=Datedif(B4,today(),“m”)

• 最值计算函数
  最大值=max（区域）
  最小值=min（区域）
  第n大值=large（区域，n）
  第n小值=small（区域，n）

• iferror函数：如果公式结果异常，结果单元格返回指定值；如果无异常，结果单元格返回公式计算结果值。
  =iferror（公式，错误返回值）

需求：计算完成率
=iferror(A5/B5,“”)

• frequency函数：统计某区间的频数
  =frequency（区域，区间条件）

需求：统计年龄在30-40岁之间的员工个数
=frequency(D2:D8,{40,29})

• sumproduct函数：和积（参数中，各数组进行求和后相乘）
  =sumproduct（数组1，数组2 … …）

需求：统计不重复总人数
（用countif 统计每人的出现次数，用1除的方式把出现次数变成分母，然后相加）
=sumproduct(1/countif(A2:A8, A2:A8))

• phonetic函数：对指定区域的字符型内容合并，数字不可合并
  =phonetic( A2:A7 )

• Left、Right和Mid函数：字符串的截取
  =left（字符串，左边截取位数）
  =right（字符串，右边截取位数）
  =mid（字符串，第几位开始截，载取的字符数）

=left(“abcde”,2)
ab

=right(“abcde”,2)
de

=mid(“abcde”,2,3)
bcd

• substitute函数：对指定字符进行替换（第几个旧内容可省略，省略时默认所有旧内容都替换）
  =substitute(内容区域，旧内容，新内容，[第几个旧内容])

需求：将手机号码的中间4位换成星号
=substitute(B2,mid(B2,4,4),“****”,1)

• index+match函数：match通过条件查找到的结果，作为条件放到index中进行进一步查找
  =index（查找区域2，match(查找的值1，查找区域1，0））

需求：根据产品名称，查找编号
=index(A2:A7,match(A10,B2:B6,0))

• Vlookup函数：数据查找、表格核对、表格合并
  =vlookup（查找的值，查找区域，返回值所在列数，精确或模糊查找）

需求：根据姓名查找职位
=vlookup(B9,B1:B6,4,0)

• if函数：根据条件进行判断
  =if（条件，成立返回值，不成立返回值）
  =if（and（条件1，条件2），同时成立返回值，其他情况返回值）
  =if（or（条件1，条件2），其一成立返回值，都不成立返回值）

需求：当A列值小于500且B列值显示未到期，在C列显示补款，否则显示空白。
=IF(AND(A2<500,B2=“未到期”),“补款”,“”)