原标题：班主任必看: 搞懂这 31 个 excel 函数公式, 你也可以秒变制表高手!
在大多数用户中，Sum 是求和的，Vlookup 是查找的，IF 是判断的 …. 可有些 Excel 函数却太不安份守已，做出很多离谱的事，颠覆了我们的三观。
1、Sumproduct 函数完成交叉查找
交叉查找有很多种方法，唯独用 Sumproduct 函数的公式太让人意外，也最简单。
【例】根据姓名和月份，要求从下图上表中查找对应的销量
C14 公式
=SUMPRODUCT((A2:A11=A14)*(B1:G1=B14)*B2:G11)
2、Vlookup 函数隔行求和
Vlookup 本是最最常用的查找函数，这次却出现在隔行求和的公式里。
【例】在 M2 单元格设置公式，隔 1 列求和
数组公式：大括号是按 ctrl+shift+enter 后自动产生的，非手工输入
{=SUM(VLOOKUP(A2,A2:K2,ROW(1:6)*2-1,0))}
指定列求和
指定对 2,3,5,6,8 列求和
公式：
{=SUM(VLOOKUP(A2,A2:K2,{2,3,5,6,8,9},0))}
注：{2,3,5,6,8,9} 把要求和的列数放在大括号内，用逗号分隔。
3、Min 函数判断取值
Min 函数不是最小值函数吗怎么抢 IF 饭碗了。
【例】在 D 列设置公式计算提成，要求：如果 C 列数字小于 2000 则按实际发放，如果大于 2000 则按 2000 发放。
=Min(C4,2000)
如果是限制最小值为 200，则公式可以改：
=MAX(C4,200)
4、Lookup 函数多条件判断
作为查找函数之王，lookup 抢别人饭碗也是经常的事。这次把 IF 函数逼到绝路了。
【例】根据下图上表的提成范围，判断后返回对应的提成比率
=LOOKUP(B10,B$2:C6)
一、数字处理
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字, 小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式：C2=IFERROR(A2/B2,””)
说明：如果是错误值则显示为空，否则正常显示。
2、IF 多条件判断返回值公式：C2=IF(AND(A2<500,B2=” 未到期 ”),” 补款 ”,””)
说明：两个条件同时成立用 AND, 任一个成立用 OR 函数。
三、统计公式
1、统计两个表格重复的内容
公式:B2=COUNTIF(Sheet15!A:A,A2)
说明：如果返回值大于 0 说明在另一个表中存在，0 则不存在。
2、统计不重复的总人数
公式：C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明: 用 COUNTIF 统计出每人的出现次数，用 1 除的方式把出现次数变成分母，然后相加。
四、求和公式
1、隔列求和公式：H3
=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明：如果标题行没有规则用第 2 个公式
2、单条件求和
公式：F2
=SUMIF(A:A,E2,C:C)
说明：SUMIF 函数的基本用法
3、单条件模糊求和
公式：详见下图说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如 ”*A*” 就表示 a 前和后有任意多个字符，即包含 A。
4、多条件模糊求和公式：C11=SUMIFS(C2:C7,A2:A7,A11&”*”,B2:B7,B11)
说明：在 sumifs 中可以使用通配符 *
5、多表相同位置求和
公式：b2
=SUM(Sheet1:Sheet19!B2)
说明：在表中间删除或添加表后，公式结果会自动更新。
6、按日期和产品求和
公式：F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明：SUMPRODUCT 可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式 1：C11
=VLOOKUP(B11,B3:F7,4,FALSE)
说明：查找是 VLOOKUP 最擅长的，基本用法
2、双向查找公式
公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
说明：利用 MATCH 函数查找位置，用 INDEX 函数取值
3、查找最后一条符合条件的记录。
公式：详见下图
说明：0/(条件) 可以把不符合条件的变成错误值，而 lookup 可以忽略错误值
4、多条件查找
公式: 详见下图
说明: 公式原理同上一个公式
5、指定区域最后一个非空值查找
公式; 详见下图
说明：略
6、按数字区域间取对应的值
公式：详见下图
公式说明：VLOOKUP 和 LOOKUP 函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式：c2
=PHONETIC(A2:A7)
说明：Phonetic 函数只能对字符型内容合并，数字不可以。
2、截取除后 3 位之外的部分
公式：
=LEFT(D1,LEN(D1)-3)
说明：LEN 计算出总长度,LEFT 从左边截总长度 - 3 个
3、截取 - 前的部分
公式:B2
=Left(A1,FIND(“-“,A1)-1)
说明：用 FIND 函数查找位置，用 LEFT 截取。
4、截取字符串中任一段的公式公式:B1=TRIM(MID(SUBSTITUTE($A1,” “,REPT(” “,20)),20,20))
说明: 公式是利用强插 N 个空字符的方式进行截取
5、字符串查找
公式：B2=IF(COUNT(FIND(“ 河南 ”,A2))=0,” 否 ”,” 是 ”)
说明: FIND 查找成功，返回字符的位置，否则返回错误值，而 COUNT 可以统计出数字的个数，这里可以用来判断查找是否成功。
6、字符串查找一对多公式：B2=IF(COUNT(FIND({“ 辽宁 ”,” 黑龙江 ”,” 吉林 ”},A2))=0,” 其他 ”,” 东北 ”)
说明：设置 FIND 第一个参数为常量数组，用 COUNT 函数统计 FIND 查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1 是开始日期（2011-12-1），B1 是结束日期 (2013-6-10)。计算：相隔多少天？=datedif(A1,B1,”d”) 结果：557
相隔多少月? =datedif(A1,B1,”m”) 结果：18
相隔多少年? =datedif(A1,B1,”Y”) 结果：1
不考虑年相隔多少月？=datedif(A1,B1,”Ym”) 结果：6
不考虑年相隔多少天？=datedif(A1,B1,”YD”) 结果：192
不考虑年月相隔多少天？=datedif(A1,B1,”MD”) 结果：9
datedif 函数第 3 个参数说明：
“Y” 时间段中的整年数。
“M” 时间段中的整月数。
“D” 时间段中的天数。
“MD” 天数的差。忽略日期中的月和年。
“YM” 月数的差。忽略日期中的日和年。
“YD” 天数的差。忽略日期中的年。
2、扣除周末天数的工作日天数
公式：C2
=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日
除了格式的转换，相信老师们在使用 excel 过程中，还经常会需要用到 excel 的制作查询系统功能，但是对于没有专业的 excel 制表技能的老师们来说，excel 制作查询系统对于他们无外乎是一项较难的工作；其实老师们不必担心，通过易查分就可以生成一个功能强大的查询系统；老师们只需要会基础的 excel 制表技能，就可以自己生成一个在线自助查询的系统；
操作步骤很简单，基本上 3 分钟就可以搞定，还不需要任何技术和服务器，只要把做好的表格导入易查分，就可以生成；
除此之外，易查分还广泛适用于各类学校、培训机构、老师、班主任，可以用于周考、月考、期中期末考试、录取查询、分班查询、作业查询、家长通知书、填写资料、核对信息、报名登记，还可以用于工资查询、水电物业查询等等，百度 易查分，就可以进入使用，完全免费！老师们都去试试吧！
好了，今天的内容就和大家聊到这里，如果对于 excel 和易查分系统的操作有任何问题，可以在留言区提出，我会在第一时间为大家解答！ 返回搜狐，查看更多
责任编辑：
原文链接：https://www.sohu.com/a/408689064_120554784?_trans_=000019_wzwza
