excel常用公式
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
Match条件查找列、行的位置
Index(应用数据列,取前面列的第几行)
Index(应用数据矩阵,行,列)
If逻辑判断=IF(logical_test,value_if_true,value_if_false)
IF(AND(A2>29,B2="A"),"优秀","") IF(or(A2>29,B2="A"),"优秀","")
SUM(IF((A2:A4="A")*(B2:B4>40),B2:B4)) 省略了第三参数,则返回第三参数值时会返回“FALSE”IF((A2:A4="A")*(B2:B4>40),B2:B4) ,)则返回0
这里(A2:A4="A")和(B2:B4>40)的判断分别返回TRUE或FALSE,其中的星号可以理解为“乘号”,在运算时TRUE相当于1,FALSE相当于0,所以只有两个返回值都是TRUE,乘积才是1;只要有一个FALSE,也就是只要有一个不满足条件,返回结果就是0。而IF第一参数中非0值相当于TRUE,所以可以达到同时满足两个条件统计的目的。
MEDIAN(IF(($A$2:$A$4029=$X2)*($B$2:$B$4029=Y$1),$I$2:$I$4029))
SUM(IF((A2:A4="A")+(B2:B4>40),B2:B4))
把同时满足多条件的乘号改成加号就达到了“或者”的目的。因为只要有一个是TRUE,相加结果就是1,如果两个都是TRUE相加结果就是2。所以达到了判断结果相加,只要结果非0就计算B列数据合计的目的。
MAX(($C:$C=$D1)*($B:$B>0)*$A:$A) 满足前两个条件A列数值最大值
=MAX(IF((A2=A:A)*(P2=P:P)*(R:R<=R2),I:I)) 满足三个条件的最大值
=IF(OR(AI2="重点大客户",AI2="工业销售")*(BB2="z20")*(M2>30),"特价
",IF((AI2="民用销售")*(M2>15)*(BB2="z20"),"特价",IF((BB2="z10")*(M2>15),"特价","流向价")))
match(A,B,0) A检索值B行或列内查找与A相同名称位置信息0 标识=A,1 《A -1>A
=IF(L76<>"",INDEX($C$3:$AA$19,MATCH(D76,$B$3:$B$19,0),MATCH($L76,$C $2:$AA$2,0)),"")
INDEX($B$31:$H$31,MATCH($J33,$B$7:$H$7,0))
INDEX($B$32:$H$32,MATCH($J33,$B$7:$H$7,0))
同时匹配两个:
=IF(H3<>"",INDEX('附表3-客户价格表'!$W$2:$W$994,MATCH(1,('附表'!I3='附表'!$L$2:$L$994)*('附表'!$K$2:$K$994='附表'!H3),0)),"")
=IF(H3<>"",INDEX('附表'!$W$3:$W$1000,MATCH('附表'!H3&'附表'!I3,'附表'!$K$3:$K$1000&'附表'!$L$3:$L$1000,0),),"")
SUMPRODUCT(A2:A8,B2:B8)原始用法两列数据相乘再求和
SUMPRODUCT( (A2:A10="二班")*(B2:B10="数学")*(C2:C10) ),表示满足A列是二班,B列是数学的时候,C列均分满足前面条件1*1*C列对应行的数字,乘积最后再求和
SUMPRODUCT( (A2:A10="二班")*(B2:B10="数学"),C2:C10 ) 与上公式结果一样,但是如果有文本内容可以忽略,上面公式如果有则返回错误
只要前面两个括号内容实现的是计数功能,即第一列二班数学第二列为数学的有几个。((A2:A10="二班"),就是数组中各行的值如果是二班,就是“真”,否则,就是“假”,“真”对应值为1,“假”对应值为0,当有这个条件表达后,A2:A10的值是“0,0,0,1,1,1,0,0,0”。(B2:B10="数学"),就是数组中各行的值如果是数学,就是“真”,否则,就是“假”,“真”对应值为1,“假”对应值为0,值是“0,1,0,0,1,0,0,1,0”。这两个经过变换后的数组乘积之和就是1。
sumif(range,criteria,sum_range)
Sumif(条件区域,求和条件,实际求和区域),第二个求和条件参数在第一个条件区域里。条件如果在条件区域内找不到就不可以,并且如果有文字需用英文状态下引号,实际求和区域省去,自动在条件区域求和。
SUMIF(H3:H93,C98,G3:G93),在H列中等于C98的对G列求和
SUMIF(A:A,"<="&DATE(2015,12,20),B:B)-SUMIF(A:A,"<"&DATE(2015,12,8),B:B) A是时间,B是销售额,中间是满足条件,公式实现8-20日求和
=SUMIF(秭归江南市场部!G2:AK2,"<="&DAY(E2),秭归江南市场部!G25:AK25)
同样实现小于E2日期求和"<="&DAY(E2)技巧使用
countif(range,criteria)range表示要计算其中非空单元格数目的区域,criteria表示统计条件,可以是某一个单元格,或一个数字,或范围条件满足一个加一个VLOOKUP(C3,price!A$2:D$101,4,)
判断某一单元格A1的内容是否在另一列B列"*"&A1&"*"模糊查询
IF(COUNTIF(B$1:B$3,"*"&A1&"*"),"Yes","")
=IF(COUNTIF(承兑例外清单!$D$3:$D$649,"*"&A5&"*"),"1","0")
计算某一区域内某值出现的次数=B2&COUNTIF($B$2:B2,B2).出现如“电视机3”