合并单元格人见人爱,可我想说对他我是又爱又恨,爱他的直观好看,恨他给我的数据处理带来一系列麻烦。上周已说过一些合并单元格问题,今日将全部重新整理发布,送给曾经因合并单元格而困扰的您! ▲ 批量合并相同单元格 操作动画如下: ▲ 取消合并单元格并批量填充 方法一、技巧法: 选中A3:A19单元格区域 取消合并单元格 按F5键-定位空值 输入=上一个单元格(如=A3) 按Ctrl Enter键 复制黏贴为值
操作动画如下: 方法二、函数法 输入公式:=LOOKUP(1,0/(A$3:A3<>''),A$3:A3) 或者输入公式:=LOOKUP('座',A$3:A3) 操作动画如下: ▲ 合并单元格填充序号 方法一:LOOKUP 选中H3:H19单元格区域 输入公式:=LOOKUP(9E 307,H$2:H2) 1 按Ctrl Enter键结束,注意单元格的引用方式
方法二:函数COUNTA 选中I3:I19单元格区域 输入公式:=COUNTA(I$2:I2) 按Ctrl Enter键结束,注意单元格的引用方式 解析:计算当前公式单元格之前的非空单元格个数。
方法三、COUNT法 选中J3:J19单元格区域 输入公式:=COUNT(J$2:J2) 1 按Ctrl Enter键结束,注意单元格的引用方式 解析:计算当前公式单元格之前包含数字的单元格个数,在该个数的基础上加1。
方法四、MAX法 选中J3:J19单元格区域 输入公式:=MAX(K$2:K2) 1 按Ctrl Enter键结束,注意单元格的引用方式 解析:查找当前公式单元格之前的最大值,在该最大值的基础上加1。
操作动画如下: ▲ 按合并单元格填充序号 输入公式:=IF(A3<>'',1,H2 1),向下填充。 操作动画如下: ▲ 合并单元格的排序 保持直播课程列合并单元格格式不变,对其进行升序排列 操作动画如下: ▲ 按合并单元格排序 保持直播课程列单元格格式及内容不变,对其销售数量进行升序排列 操作动画如下: ▲ 合并单元格的筛选 比如筛选出函数中级的全部记录 函数中级明明有4条记录,可是筛选出来的却只有一条,这该怎么办呢? 操作动画如下: ▲ 合并单元格表头设置 操作动画如下: ▲ 合并单元格打印技巧 操作动画如下: ▲ 查找合并单元格 按Ctrl F键弹出查找和替换对话框 点击选项→格式→勾选合并单元格→确定 查找全部→按Ctrl A键全选所有查找到的记录 操作动画如下: ▲ 在合并单元格中填充数据 操作动画如下: ▲ 提取合并单元格中的数据 输入公式: =IFERROR(INDEX(A$2:A$18,SMALL(IF(A$2:A$18<>'',ROW($1:$17)),ROW(A1))),'') 按Ctrl Shift Enter三键结束 操作动画如下: ▲ 合并单元格的计数 选中E3:E19单元格区域 输入公式=COUNT(D3:D19)-SUM(E4:E19) 注意:按Ctrl Enter键,完成公式的输入! 操作动画如下: ▲ 合并单元格的求和 选中E3:E19单元格区域 输入公式=SUM(D3:D19)-SUM(E4:E19) 注意:按Ctrl Enter键,完成公式的输入! 操作动画如下: ▲ 合并单元格求平均值、最大值 求平均值: 选中E3:E19单元格区域 输入公式: =AVERAGE(OFFSET(D3,,,IFERROR(MATCH('*',A4:A19,0),COUNTA(D:D)-ROW() 1),1)) 注意:按Ctrl Enter键,完成公式的输入!
操作动画如下: 求最大值: 选中F3:F19单元格区域 输入公式: =MAX(OFFSET(D3,,,IFERROR(MATCH('*',A4:A19,0),COUNTA(D:D)-ROW() 1),1)) 注意:按Ctrl Enter键,完成公式的输入! 操作动画如下: ▲ 合并单元格的引用问题 根据客服引用合并单元格中对应的直播课程 输入公式:=LOOKUP('座',INDIRECT('A3:A'&MATCH(G3,B3:B19,0) 2)) 或者输入公式:=LOOKUP('座',OFFSET(A3,,,MATCH(G3,B3:B19,))) 操作动画如下: ▲ 合并单元格的查询问题 输入公式:=VLOOKUP(LOOKUP('座',A$3:A3),G:H,2,0) 操作动画如下: ▲ 合并单元格一对多查询问题 G3单元格输入直播课程,H列得到对应的所有客服 输入公式: =IFERROR(INDEX(B$3:B$19,SMALL(IF(LOOKUP(ROW($3:$19),IF(A$3:A$19<>'',ROW($3:$19)),A$3:A$19)=G$3,ROW($1:$17),4^8),ROW(A1))),'') 数组公式,按Ctrl Shift Enter三键结束 操作动画如下: ▲ 有合并单元格的汇总问题 输入公式: =SUM((LOOKUP(ROW($3:$19),IF(A$3:A$19<>'',ROW($3:$19)),A$3:A$19)=F3)*D$3:D$19) 按Ctrl Shift Enter三键结束 操作动画如下: 【爱上Excel合伙人】能为读者做什么? 我们【爱上Excel合伙人】微信订阅号平台一直秉承简洁、优雅、高效的为读者分享工作中遇到的每一个Excel问题,不论是Excel技巧、函数、图表、VBA,甚至是有关于Excel的开发,只要你能提出来问题,我们总能给你一个满意的答案! 注:文章素材与操作动画均在合伙人社群,有需要的可以入群下载! 合伙人QQ交流社群 |
|