说明:SUMPRODUCT可以完成多条件求和
按日期和产品求和
五、查找与引用公式17、单条件查找
公式:C11=VLOOKUP(B11,B3:F7,4,FALSE)
说明:查找是VLOOKUP最擅长的,基本用法
单条件查找
18、双向查找
公式:=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
说明:利用MATCH函数查找位置,用INDEX函数取值
双向查找
19、查找最后一个符合条件记录
公式:详见下图
说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值
查找最后一个符合条件记录
20、多条件查找
公式:详见下图
说明:公式原理同上一个公式
多条件查找
21、指定非空区域最后一个值查找
公式:详见下图
说明:略
指定非空区域最后一个值查找
22、区间取值
公式:详见下图
说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列 。
区间取值
六、字符串处理公式【32个Excel函数公式大全 表格函数基础知识大全】23、多单元格字符合并
公式:c2=PHONETIC(A2:A7)
说明:Phonetic函数只能对字符型内容合并,数字不可以 。
多单元格字符合并
24、截取除后3位之外的部分
公式:=LEFT(D1,LEN(D1)-3)
说明:LEN计算出总长度,LEFT从左边截总长度-3个
截取除后3位之外的部分
25、截取 - 之前的部分
公式:B2=Left(A1,FIND("-",A1)-1)
说明:用FIND函数查找位置,用LEFT截取 。
截取 - 之前的部分
26、截取字符串中任一段
公式:B1=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
说明:公式是利用强插N个空字符的方式进行截取
截取字符串中任一段
27、字符串查找
公式:B2=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明:FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功 。
28、字符串查找一对多
公式:B2=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
七、日期计算公式29、两日期间隔的年、月、日计算
A1是开始日期(2011-12-1),B1是结束日期(2013-6-10) 。
计算:
- 相隔多少天?=datedif(A1,B1,"d") 结果:557
- 相隔多少月? =datedif(A1,B1,"m") 结果:18
- 相隔多少年? =datedif(A1,B1,"Y") 结果:1
推荐阅读
- Excel自动生成目录技巧 Exce生成目录怎么设置
- excel怎么按照数字排序
- 电脑excel截图快捷键 表格怎么截图按什么键
- 快速输入平方和立方的方法 立方米符号m3怎么打出来excel
- 一文详细了解mid函数的使用方法 mid函数怎么用详细步骤
- 表格怎么添加绿色三角
- excel中插入表格操作步骤 excel怎么插入表格
- excel下拉选择项设置方法 表格怎么添加下拉选项
- excel查找对比常用技巧 excel怎么查找
- excel中rank函数的详细使用方法 excel排名公式怎么用