EXCEL 常用公式

INDIRECT

说明：单元格公式引用

例：=INDIRECT("B"&ROW(A11))

 

MATCH

说明：返回在数组中匹配的数据的行索引

参数1：查找值

参数2：查找区域

例：MATCH(Sheet1!$A$1,Sheet3!$A$1:$A$4)

 

OFFSET

说明：以引用为参照，通过偏移量返回一个新的引用

参数1：参照值

参数2：偏移行数（上下）

参数3：偏移列数（左右）

参数4：高度范围（数组）

参数5：宽度范围（数组）

例：=OFFSET(A1,3,0)

 

INDEX

说明：以行列索引，返回单元格引用

参数1：数据区域

参数2：行索引

参数3：列索引

例：=INDEX(数据!A:H,3,1)

 

WEEKDAY

说明：返回日期为周几

参数1：Info_type，指定所需要的单元格信息的类型

参数2：Reference（1表示从周日开始，2表示从周一开始）

例：=CELL("address",A1) / =CELL("contents", A1) 

 

CELL

说明：返回某一引用区域的左上角单元格的格式、位置或内容等信息

参数1：日期值

参数2：返回类型（1表示从周日开始，2表示从周一开始）

例：=WEEKDAY(A1,2)

 

填充数据到取消的合并单元格

1）复制合并单元格，选择数值粘贴到新列

2）选中新列要填充的区域

3）CTRL+G，定位空值

4）公式 =上一单元格，CTRL+ENTER

 

INDEX + MATCH

说明：正向逆向的查找

例：=INDEX(板材数据!A:H,MATCH(D2,板材数据!A:A),3)

 

LOOKUP

1）查找最后一条符合条件的记录 / 逆向查询

例：=LOOKUP(1,0/(条件区域=条件),查询区域)

 

2）查询符合多个条件的记录

例：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)

 

函数分列

说明：将数据以分隔符进行分列

例：=TRIM(MID(SUBSTITUTE($C3,"*",REPT(" ",99)),COLUMN(A3)*99-98,99))

 

批量插入空行

1）插入序号辅助列，序号数等于数据数量

2）插入几行空行数量等于复制次数

3）选中序号按扩展选区排序

 

图片动态匹配

1）定义函数名称（如：x1，x2，x3...）

2）照相机设置单元格函数

3）=INDEX(数据!$B:$B,MATCH(Dashboard!$A$2,数据!$A:$A))

 

提取列唯一值

说明：提取列唯一值，重复值按序号排列

例：=COUNTIF(INDIRECT("A$2:A"&ROW(),ROW()),INDIRECT("A"&ROW()))

 

获取表名

说明：获取 SHEET名称

例：=MID(CELL("filename",INDIRECT(ADDRESS(ROW(),COLUMN()))),FIND("]",CELL("filename",INDIRECT(ADDRESS(ROW(),COLUMN()))))+1,99)

 

获取路径

说明：获取文件路径

=MID(CELL("filename"),1,SEARCH("[",CELL("filename"))-1)

 

查找重复数据

说明：查找列中重复的数据

例：=IF(COUNTIF(A$2:A2,A2)>1,"√",)

 

DGET

说明：是从列表或数据库的列中提取符合指定条件的单个值

参数1：数据区域（包含表头）

参数2：字段（列号 / 列名）

参数3：查询条件（WHERE FILEDNAME=VALUE）

例：=DGET(A1:E7,5,A9:B10)

 

PROPER

说明：将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写

例：=PROPER(A2)

 

EVALUATE

运算文本中的公式

例：=EVALUATE(A2)

 

CLEAN

删除文本中不能打印的字符

例：=CLEAN(A2)

 

快捷操作

F9，公式步入，CTRL+Z，退出编辑

 

条件格式（聚光灯效果）

=OR(CELL("row")=ROW(), CELL("col")=COLUMN())

 

查找引用外部数据的单元格，

1. CTRL+F查询“[”或“]”，查找范围工作簿

2. 公式标签--编辑链接

 

获取字符串中的中文字符

=LEFT(A2,LENB(A2)-LEN(A2))

 

左补位0填充

=REPT(0,2-LEN(A1))&A1

 

获取字符串中的数字

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},CONCATENATE(A2,"0123456789"))),2*LEN(A2)-LENB(A2))