1、IF And
作用：并列执行多条件判断。
【举例】如下图，在C列中设置公式，若A列值小于500且B列值是未到期，则返回”补款“，否则就显示为空。
公式：在C2中输入：
=IF(AND(A2<500,B2="未到期"),"补款","")
解释：两个条件同时成立时，用AND，任一个成立时，用OR函数。
2、Index Match
作用：根据条件进行查询。
【举例2】如下图，依据月份和费用项目，来查找金额。
在E10中输入公式为：
=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))
公式解释：
先使用MATCH函数查找3月在第一行的位置。
=MATCH(B10,$A$2:$A$6,0)
之后再使用MATCH函数，来查找费用项目在A列中的位置。
=MATCH(A10,$B$1:$G$1,0)
最后，使用INDEX依据行数和列数，来提取数值。
=INDEX(区域,行数,列数）
3、Iferror Vlookup
作用：当Vlookup查找不到时，屏蔽错误值。
【举例】如下图，需要按照产品名称，在上表中查询单价，若产品不存在，则显示为空白。
在B9中输入公式：
=IFERROR(VLOOKUP(A8,$A$1:$D$5,3,0),"")
4、Mid Find
作用：依据条件截取字符串。
【举例】在个人信息中，截取出年龄。
在B2中输入公式：
=MID(A2,FIND(" ",A2) 1,9)
注：find函数用于查找分隔符位置，MID则负责截取字符。
5、Left lenB Len
作用：分离汉字、数字和字母。
示例：
=LEFT(A2,LENB(A2)-LEN(A2))
注：带B的函数，是依据字节来计数，一个汉字占2个字节，数字与字母占1个字节。因此使用LENB(A2)-LEN(A2)公式，可倒推出汉字个数，之后用left或mid函数来截取。
6、index Small Row
作用：一对多查找
【例】如下图，需要在F列中，查找“张明城”个人消费记录。
在F2中输入公式：
{=INDEX(C:C,SMALL(IF(B$2:B$10=F$1,ROW($2:$10)),ROW(A1)))}
公式解释：
- IF(B$2:B$10=F$1,ROW($2:$10))：若B列中的姓名与F1的姓名相同，则返回其行号。若不相同，则返回FALSE。
- Row(a1)：返回A1行号1，若向下复制，则会变为 Row(a2)，即返回2，可以使用此公式向下生成序号：1，2，3...之后取符合条件的第1个行号，第2个行号...
- SMALL(): 从符合条件的行号中，从小至大，逐个提取符合条件的行。
- INDEX() :按照所取得的行号，从C列取值。
- { }：数组公式（含有逐一运算的公式）需按ctrl shift enter 来输入大括号（不可手输入大括号哦）。
7、Sum Offset Count
作用：最后N天进行求和。
【举例】在D2单元格中，返回B列的最近7天销量。
在D2中输入公式：
=SUM(OFFSET(B1,COUNTA(B:B)-7,0,7,1))
注：Counta函数负责统计B列非空值的个数，offset则负责生成动态的最后N天区域，SUM函数负责求和。
8、Sumproduct Countif
作用：计算不重复值的个数。
【举例】：统计B列客户数量。
公式如下：
=SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19))
注：Countif函数用于统计各客户出现的次数，Sumprodcut则对1/出现次数进行求和。客户不论出现了多少次，求和结果均是1，求和之后，正好为不重复值的个数。
评论 (0)