|
今天技巧妹整理了18个高效的Excel操作技巧和大家分享，建议收藏，以备所需。 1、提取指定内容 在一串混合文本中，需要把括号里的内容剔除掉，只提取括号外的内容。 操作：可以用查找替换功来解决问题，选中文本所在单元格区域，按Ctrl+H组合键，打开“查找和替换”对话框，查找内容中输入：(*)，点击“全部替换”即可。 2、快速复制可见单元格 隐藏部分数据后，如何复制粘贴可见单元格中的数据？ 操作：选择需要复制的单元格区域，按Alt+；组合键后，复制粘贴即可。 3、快速隐藏行列 操作：快速隐藏某行，选中指定行区域，按Ctrl+9组合键；如果想隐藏某列，则选择后按Ctrl+0组合键。 4、快速隐藏数据 由于某些特殊原因，需要隐藏表格中的部分数据，如何实现？ 操作：选中需要隐藏的数据，按Ctrl+1组合键打开设置单元格格式对话框，在自定义类型中输入英文状态下的“;;;”即可。 5、跳过空行填充序列号 如下图表格，存在小计行，如何填充连续的序列号？ 操作：点击【数据】选项卡下的筛选命令，对D列进行筛选，不显示空白行。选中A2单元格，在编辑栏输入公式：=MAX($A$1:A1)+1，鼠标拉动往下填充公式，最后取消筛选状态即可。 6、始终保持序号连续性 我们对数据进行筛选、隐藏或者删除之后，如何保持序号的连续性呢？ 这里需要用到SUBTOTAL函数，输入公式： =SUBTOTAL(103,B$2:B2)*1，下拉填充。 7、跳过空行批量填充公式 如下图表格，表格中存在空白行，如果直接双击鼠标往下填充公式，发现无法批量填充，如何解决问题？ 操作：选中D2为起始单元格的数据区域，按Ctrl+D组合键即可实现公式的批量向下填充。 8、查找最后一个非空单元格数字 如下图表格，如何查找引用各个地区最后一个记录的数据？ 输入公式=LOOKUP(9E+307,B15:J15)，下拉填充即可。 9、批量显示所有隐藏的工作表 如何批量把隐藏的所有工作表重新显示出来呢？ 操作：如果不想用VBA，需要在进行工作表隐藏之前，点击视图——添加自定义视图，输入名称。这样当你隐藏指定工作表后，想要再次显示出所有表格时，就可以点击自定义视图中对应的名称，点击显示即可。 10、逆向查找数据 用VLOOKUP函数进行查找时，如果查找值不在首列，会出现错误的查询结果。 结合IF函数来实现正常查找。输入公式： =VLOOKUP(F2,IF(,B2:B11,A2:A11),2,0) 说明：利用IF(,区域1,区域2)对查找的数据区域进行重新构建。 11、交叉查找引用数据 利用VLOOKUP+MATCH函数组合实现交叉查询，输入公式：=VLOOKUP(A11,$A$1:$F$7,MATCH(B11,$A$1:$F$1,0),0) 说明：先用MATCH(B11,$A$1:$F$1,0)确定姓名所在列，再用VLOOKUP函数进行查找引用。 12、跨表查询引用数据 如果需要跨表或多表查询引用数据，经常用到VLOOKUP+INDIRECT函数组合。输入公式：=IFERROR(VLOOKUP($B2,INDIRECT(C$1&'!B:C'),2,0),'') 说明：这里用VLOOKUP函数和INDIRECT函数结合，对各个明细表B、C两列数据查找区域进行动态引用。公式中还结合了IFERROR函数进行容错处理。 13、快速条件求和 如下图表格，如何对指定人员指定月份进行数据求和？ 利用SUM函数结合SUMIF函数，输入公式：=SUM(SUMIF(A2:A11,{'李霞霞';'戴梦梦'},D2:D11)) 14、不重复计数 如下图表格，如何统计人员的不重复个数？ 可以利用SUM函数结合COUNTIF函数进行计数，输入公式： =SUM(1/COUNTIF(A2:A14,A2:A14))，按Ctrl+Shift+Enter组合键完成。 说明： COUNTIF(A2:A14,A2:A14)：对每个单元格进行统计判断； 1/COUNTIF(A2:A14,A2:A14）：删除重复值，假如只有出现一个值，1除以1就等于1，若是出现2个，那么1除以2等于1/2，所有1/2求和也等于1，相当于获取不重复人数。 15、多条件查找引用数据 根据多个条件查找引用数据，可以利用OFFSET+MATCH这对函数组合，输入公式： =OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,)) 说明：先用MATCH函数分别定位出指定月份和产品在A2:A11和B1:E1区域中的位置，作为OFFSET函数的第2和第3个参数，然后以A1为基准位置偏移对应的行数和列数即可。 16、一对多查找引用数据 如下图表格，根据销售区域查找引用对应的人员和业绩，如何解决？ 可以用到INDEX+SMALL+IF这个经典的函数组合，输入公式： =INDEX(B:B,SMALL(IF($A$1:$A$11=$E$2,ROW($A$1:$A$11),4^8),ROW(A1)))&'' 说明： SMALL函数用来定位所有E2在A列中的位置(从小到大) 4^8这里指的是一个比较大的数，在这个IF函数公式中，如果单元格区域A1:A11的值等于E2，就显示E2在A列中所在的行号，如果不等于就显示一个较大的数 当我们利用SMALL函数得到行号之后，结合INDEX函数一对多查找需要的值 最后的&''是用来进行容错处理。 17、计算带单位数据 如下图表格，销售额数据带有单位，如何直接进行合计？ 利用SUMPRODUCT函数结合SUBSTITUTE函数，可以用来对带单位的数据进行求和。输入公式：=SUMPRODUCT(SUBSTITUTE(C2:C11,'元','')*1)&'元' 说明：SUBSTITUTE(D2:D10,'元','')先将C列中的“元”全部替换为空值，乘以1将文本转换为数值，再利用SUMPRODUCT函数求和。 18、条件判断 IF函数和AND或OR函数结合使用，可以用来进行条件判断并获取对应值。 IF+AND函数，可以获取同时满足多个条件的值。如下图表格，输入公式： =IF(AND(B2='女',C2>85),'优秀','') 而IF函数和OR函数结合，则是用来获取满足任意一个条件的值。如下图表格，输入公式：=IF(OR(C2>90,D2>90),'优秀','') |
|
|
