目录
拖拽时函数中列号/行号,锁定内容用$符号
EXCEL单元格输入公式,其他单元格内容类似时,采用拖拽时列号字母、数字递增问题:
在指定固定不变的内容前添加 $ 英文符号
如:=SUMIF($O$21:$O$30,O21,$P$21:$P$30)
o列为查询条件区域:O21:O30
具体查询条件值取值位置/具体指定的值:O21
=SUMIF(O21:O30,O21,P21:P30)
P列为对应求和列区域:P21:P30
使用$符号会锁定内容
行拖拽、列拖拽
=VLOOKUP($A13,项目明细!$1:$1048576,COLUMN(项目明细!L13),0)
行拖拽时: A$13 固定列号,单元格行号-不固定 递增 A13\B13\C13 …
列拖拽: $A13 A列固定不变,单元格列号 13会改变-递增 A13\A14\A15…
COUNTA
统计一个区域内非空单元格的数量。可以是文本/数值
=COUNTA(A1:A10)
COUNTIF
=COUNTIF(项目明细!I210:I218,项目明细!I210)
参数:
1、项目明细!I210:I218 查询条件所在区域
2、具体指定的查询条件值
结果:满足查询条件的数据量(数据条数)
COUNT
只能对数值列统计数据量,只能统计年龄列,不能对姓名列统计有多少条记录数,并且年龄不能为空,为空将不统计在内。
LEN
统计一个单元格中字符数量
=LEN(A1)
TRIM
去除单元格中空格
lookup
VLOOKUP 列查找
=VLOOKUP($A13,项目明细!$1:$1048576,COLUMN(项目明细!L13),0)
根据指定值进行统计,指定的值不能存在重复项
=VLOOKUP(A4,项目明细!1:1048576,2,0)
在”项目明细“sheet页查找A4单元格存储的值,对应
HLOOKUP 行查找
LOOKUP 查找
SUMIFS
=SUMIFS(F56:F59,D56:D59,“=测试”,E56:E59,“=男”)
要汇总的列区域: F56:F59
分组条件:
条件1区域和值:D56:D59,“=测试”
条件2区域和值:E56:E59,“=男”
根据测试列分组,和性别列分组,查询数值列数据和
SUMIF
SUMIFS格式:
SUMIFS(汇总区域,查询条件1区域,具体指定的查询条件1值,查询条件2区域,查询条件2值)
=SUMIFS(汇总模板!H4:H58,汇总模板!E4:E58,C148,汇总模板!F4:F58,D148)
取值模板:
汇总文件:
两个SUMIF求和
格式:sumif(查询条件区域,具体指定的查询条件值,汇总区域)
=SUMIF(汇总模板!E4:E58,A23,汇总模板!I4:I58)+SUMIF(汇总模板!E4:E58,A23,汇总模板!J4:J58)
IF
SWITCH 多条件判断
=SWITCH(Y6,0,“”,INT(Y6),“GGD”,MOD(Y6,1),“JP”,“JP/GGD”)
Y6值包括:空,JP,GGD,JP/GGD
现有N条数据记录(N<10000),判断:
1、为JP或(JP和空格)时,展示为JP
2、为GGD或(GGD和空格)时,展示为GGD
3、为JP+(GGD或 JP/GGD)、JP/GGD+空时,展示为JP/GGD
4、全为空时,展示为空
方法:设置JP=0.0001 ,GGD=100,JP/GGD=100.0001,空=0
指定条件数据求和后判断和的值为小数,全为JP
INT 取整数
取整
MOD 取余数
MOD(Y6,1) 为:Y6单元格的值与1取余数。即取小数部分值
IF 判断
IF(测试条件表达式,真值,假值)
IF(A1=“0.001”,“JP”,“GGD”)
AND
AND(C2>=81,C2<=91)
OR
AND(C2>=81,C2<=91)