快捷键
全选: ctrl + a
向不同方向扩展一个单元格: shift + 上/下/左/右箭头
向不同方向扩展到最后一个有内容的单元格: ctrl + shift + 上/下/左/右箭头
向不同方向跳到最后一个单元格: ctrl + 上/下/左/右箭头
快速求和:选中指定数据区域以及空出来的下方或者右方空格,然后alt + =
快速填充:选中需要填充内容的区域,直接输入需要填充的内容,然后alt + = ;mac是ctr+shift+回车键
筛选:ctrl + shift +L
锁定切换: F4
查看计算过程:alt+ m+ v
调出公式调试模式:F2
单独查看公式中某一部分的计算结果:在公式模式下,选中要查看的部分,单击F9; ESC退出查看
函数
聚合函数
sum(),max(),min(),average(),count(),sumifs(),countifs()
字符串函数
left()
right()
mid()
len()
查找函数
vlookup()
lookup()
语法:lookup(查找的值,查找的值所在的列,返回值所在列)
说明:当查找的值在查找值所在列有对应的值时,是精确查找;当查找的值所在列无对应的值,是模糊查找,返回与查找值最接近的值对应的返回值,模糊查找查找范围必须是升序排列。可用于区分大小写匹配
举例:区分大小写匹配
lookup(1,0/exact(C1,A1:A4),B1:B4)(C1是查找索引值所在单元格,A1:A4是查找索引值所在区域,B1:B4是查找值所在区域)
索引函数
find()
match()
语法:match(目标值,查找区域,顺序参数)
说明:返回目标值在查找区域内的排列位置。顺序参数为-1表示降序,0表示任意顺序,1表示升序,默认是1。当选择1或者-1时,查找区域的数值也必须是按照升序或者逆序排列,不然会报错
index()
语法:index(array, row_num, [column_num])
说明:返回表或数组中元素的值,由行号和列号索引选择
有个区分大小写的方法,用到index,没太明白,什么时候需要用到数组,什么时候不需要。当返回值是数组时,需要用到数组公式。
=INDEX(B:B,MATCH(1,–EXACT(A:A,D2),0))输入完公式,要按CTRL+SHIFT+ENTER三键结束。(因为–EXACT(A:A,D2)返回的一组值)
offset()
语法:OFFSET(reference,rows,cols,height,width)
说明:reference是基准位置,rows是移动的行数,cols是移动的列数,height是引用的行数,width是引用的列数,height,width可省略,省略默认返回该行/该列
举例:
每隔7天统计一列数据的均值,假设数据在A列
offset($A$1,(row(1:1)-1)*7,0,10)
或者
offset($A$1:$A$7,(row(1:1)-1)*7,0)
row(1:1)返回第一行的所在行数
常见用法举例
1.间隔相同时间计算均值
见offset应用举例
2.统计奇数行数据的和
统计A1:A8中奇数行的和
sumproduct(mod(row(A1:A8),2)=row(A1)*A1:A8)
衍生:选中要查看的单元格格式,同时按住ALT+M+V,可以查看row()的计算过程
常用功能
筛选:数据>筛选
排序:数据>排序
自定义排序:数据>排序>次序>自定义序列