关注互联网创业
干货|工具|教程|资源

从瞎忙到高效,这18个Excel技巧谁看谁受益!

今天技巧妹整理了18个高效的Excel操作技巧和大家分享,建议收藏,以备所需。

1、Excel提取指定内容

在一串混合文本中,需要把括号里的内容剔除掉,只提取括号外的内容。

操作:可以用查找替换功来解决问题,选中文本所在单元格区域,按Ctrl+H组合键,打开“查找和替换”对话框,查找内容中输入:(*),点击“全部替换”即可。

Excel提取指定内容

Excel提取指定内容

 

2、Excel快速复制可见单元格

隐藏部分数据后,如何复制粘贴可见单元格中的数据?

操作:选择需要复制的单元格区域,按Alt+;组合键后,复制粘贴即可。

Excel快速复制可见单元格

Excel快速复制可见单元格

 

3、Excel快速隐藏行列

操作:快速隐藏某行,选中指定行区域,按Ctrl+9组合键;如果想隐藏某列,则选择后按Ctrl+0组合键。

Excel快速隐藏行列

Excel快速隐藏行列

 

4、Excel快速隐藏数据

由于某些特殊原因,需要隐藏表格中的部分数据,如何实现?

操作:选中需要隐藏的数据,按Ctrl+1组合键打开设置单元格格式对话框,在自定义类型中输入英文状态下的“;;;”即可。

Excel快速隐藏数据

Excel快速隐藏数据

 

5、跳过空行填充序列号

如下图表格,存在小计行,如何填充连续的序列号?

操作:点击【数据】选项卡下的筛选命令,对D列进行筛选,不显示空白行。选中A2单元格,在编辑栏输入公式:=MAX($A$1:A1)+1,鼠标拉动往下填充公式,最后取消筛选状态即可。

跳过空行填充序列号

跳过空行填充序列号

 

6、Excel始终保持序号连续性

我们对数据进行筛选、隐藏或者删除之后,如何保持序号的连续性呢?

这里需要用到SUBTOTAL函数,输入公式:

=SUBTOTAL(103,B$2:B2)*1,下拉填充。

Excel始终保持序号连续性

Excel始终保持序号连续性

 

7、Excel跳过空行批量填充公式

如下图表格,表格中存在空白行,如果直接双击鼠标往下填充公式,发现无法批量填充,如何解决问题?

操作:选中D2为起始单元格的数据区域,按Ctrl+D组合键即可实现公式的批量向下填充。

 

从瞎忙到高效,这18个Excel技巧谁看谁受益!

 

8、Excel查找最后一个非空单元格数字

如下图表格,如何查找引用各个地区最后一个记录的数据?

输入公式=LOOKUP(9E+307,B15:J15),下拉填充即可。

Excel查找最后一个非空单元格数字

 

9、Excel批量显示所有隐藏的工作表

如何批量把隐藏的所有工作表重新显示出来呢?

操作:如果不想用VBA需要在进行工作表隐藏之前,点击视图——添加自定义视图,输入名称。这样当你隐藏指定工作表后,想要再次显示出所有表格时,就可以点击自定义视图中对应的名称,点击显示即可。

Excel批量显示所有隐藏的工作表

 

10、Excel逆向查找数据

用VLOOKUP函数进行查找时,如果查找值不在首列,会出现错误的查询结果。

结合IF函数来实现正常查找。输入公式:

=VLOOKUP(F2,IF({1,0},B2:B11,A2:A11),2,0)

Excel逆向查找数据

 

说明:利用IF({I,0},区域1,区域2)对查找的数据区域进行重新构建。

 

11、Excel交叉查找引用数据

利用VLOOKUP+MATCH函数组合实现交叉查询,输入公式:=VLOOKUP(A11,$A$1:$F$7,MATCH(B11,$A$1:$F$1,0),0)

Excel交叉查找引用数据

Excel交叉查找引用数据

 

说明:先用MATCH(B11,$A$1:$F$1,0)确定姓名所在列,再用VLOOKUP函数进行查找引用。

 

12、Excel跨表查询引用数据

如果需要跨表或多表查询引用数据,经常用到VLOOKUP+INDIRECT函数组合。输入公式:=IFERROR(VLOOKUP($B2,INDIRECT(C$1&"!B:C"),2,0),"")

Excel跨表查询引用数据

 

说明:这里用VLOOKUP函数和INDIRECT函数结合,对各个明细表B、C两列数据查找区域进行动态引用。公式中还结合了IFERROR函数进行容错处理。

 

13、Excel快速条件求和

如下图表格,如何对指定人员指定月份进行数据求和?

利用SUM函数结合SUMIF函数,输入公式:=SUM(SUMIF(A2:A11,{"李霞霞";"戴梦梦"},D2:D11))

Excel快速条件求和

Excel快速条件求和

 

14、Excel不重复计数

如下图表格,如何统计人员的不重复个数?

可以利用SUM函数结合COUNTIF函数进行计数,输入公式:

=SUM(1/COUNTIF(A2:A14,A2:A14)),按Ctrl+Shift+Enter组合键完成。

Excel不重复计数

说明:

  • COUNTIF(A2:A14,A2:A14):对每个单元格进行统计判断;
  • 1/COUNTIF(A2:A14,A2:A14):删除重复值,假如只有出现一个值,1除以1就等于1,若是出现2个,那么1除以2等于1/2,所有1/2求和也等于1,相当于获取不重复人数。

 

15、Excel多条件查找引用数据

根据多个条件查找引用数据,可以利用OFFSET+MATCH这对函数组合,输入公式:

=OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,))

Excel多条件查找引用数据

Excel多条件查找引用数据

 

说明:先用MATCH函数分别定位出指定月份和产品在A2:A11和B1:E1区域中的位置,作为OFFSET函数的第2和第3个参数,然后以A1为基准位置偏移对应的行数和列数即可。

 

16、Excel一对多查找引用数据

如下图表格,根据销售区域查找引用对应的人员和业绩,如何解决?

 

可以用到INDEX+SMALL+IF这个经典的函数组合,输入公式:

=INDEX(B:B,SMALL(IF($A$1:$A$11=$E$2,ROW($A$1:$A$11),4^8),ROW(A1)))&""

Excel一对多查找引用数据

Excel一对多查找引用数据

 

说明:

  • SMALL函数用来定位所有E2在A列中的位置(从小到大)
  • 4^8这里指的是一个比较大的数,在这个IF函数公式中,如果单元格区域A1:A11的值等于E2,就显示E2在A列中所在的行号,如果不等于就显示一个较大的数
  • 当我们利用SMALL函数得到行号之后,结合INDEX函数一对多查找需要的值
  • 最后的&""是用来进行容错处理。

 

17、Excel计算带单位数据

如下图表格,销售额数据带有单位,如何直接进行合计?

利用SUMPRODUCT函数结合SUBSTITUTE函数,可以用来对带单位的数据进行求和。输入公式:=SUMPRODUCT(SUBSTITUTE(C2:C11,"元","")*1)&"元"

Excel计算带单位数据

Excel计算带单位数据

 

说明:SUBSTITUTE(D2:D10,"元","")先将C列中的“元”全部替换为空值,乘以1将文本转换为数值,再利用SUMPRODUCT函数求和。

 

18、Excel条件判断

IF函数和AND或OR函数结合使用,可以用来进行条件判断并获取对应值。

IF+AND函数,可以获取同时满足多个条件的值。如下图表格,输入公式:

=IF(AND(B2="女",C2>85),"优秀","")

Excel条件判断2-1

Excel条件判断2-1

 

而IF函数和OR函数结合,则是用来获取满足任意一个条件的值。如下图表格,输入公式:=IF(OR(C2>90,D2>90),"优秀","")

Excel条件判断2-2

Excel条件判断2-2

 

历史上的今天

赞(1)
未经允许不得转载:木木资源博 » 从瞎忙到高效,这18个Excel技巧谁看谁受益!
大学生必备,最全互联网精品资源打包下载

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址