当你遇到图中案例这样的工作情况,需要将一列有规律的数据变成两列展示,你会怎么来实现呢?
现在,我们来讲解2个解决方法。
一、用颜色筛选法
首先,我们将所有电话的单元格用颜色填充。
由于全部是隔行为电话,因此可以用条件格式里的公式来选取这些单元格。
选中B列,点击“菜单栏-开始-条件格式-新建规则”,在公示栏里输入公式
=(mod(row(a1),2)=1)
mod函数是求余数,即将所有B列单元格的行数(B1单元格行号为1、B2单元格行号为2……)除以2,余数为1的单元格填充黄色,也就是所有奇数行填充黄色。
设置完之后的效果如图,包括B1单元格也由蓝色填充变为了黄色填充。
有了颜色填充之后,我们再用筛选功能里的颜色筛选就可以了。
点击“菜单栏-开始-排序和筛选-筛选”,B1单元格就多了筛选按钮。
点击筛选按钮,选择“按颜色筛选”,里面有“黄色填充、无填充”两个选项,先选择无填充,就可以将姓名先筛选出来。
筛选结果出来后,复制这些姓名内容,在D1单元格点击右键,选择粘贴选项里的第2个,也就是有123图标的,代表着只粘贴值,去掉所有格式。
这样,姓名首先被筛选复制出来了。
同理,我们再去筛选黄色填充,复制到E列,电话也被筛选复制出来了。
这个时候,会发现数据少了一些,不完整?这是正常的。因为筛选的时候有些行被隐藏了,不用担心。
点击筛选按钮,选择“从姓名电话中清除筛选”,去除所有筛选效果。
这个时候,D列、E列的数据就全部完整展示出来了。
接下来,点击“菜单栏-开始-条件格式-清除规则-清除整个工作表规则”,将B列恢复到初始状态。
最终,再对D列、E列调整下格式,所有数据就变成了两列展示效果。
二、用函数公式法
第一种方法操作的步骤比较多,要想一步到位,就用函数公式最方便。
选中D列区域(D2开始,和B列一样长度,或者超出B列长度,不能少于B列长度),在编辑栏里输入数组公式:
=IFERROR(INDEX(B:B,SMALL(IF(MOD(ROW(),2)=0,ROW()),ROW(A1:A50))),””)
公式第1部分
IF(MOD(ROW(),2)=0,ROW())
表示用B列单元格的行号除以2,如果余数为0(即刚好整除,也就是所有偶数行),返回行号;如果余数不为0(即所有奇数行),返回默认错误值。
公式第2部分
SMALL(公式第1部分,ROW(A1:A50))
表示用small函数将刚才返回的行号全部排到前面来,错误值放到后面去。后面的row(a1:a50)代表总共对50个数进行取值,这个50一定要大于B列所选单元格的数量,否则数据显示不完整。
公式第3部分
INDEX(B:B,公式第2部分)
表示用index函数在B列,将这样行号对应单元格的值取出来,也就是取出所有偶数行里的内容,即所有姓名。
公式第4部分
=IFERROR(公式第3部分,””)
表示用ifferror函数将错误值部分全部显示为空格。
由于是数组公式,要用Ctrl Shift Enter三键确认,确认后D列就直接显示全部的姓名了。
有了D列的姓名,再用offset函数进行获取对应的电话即可。
E2单元格公式为
=IFERROR(OFFSET($B$1,MATCH(D2,B:B,0),0),””)
先用match函数在B列里找到姓名对应的位置,然后用offset函数去取出对应的电话单元格,再用iferror函数过滤掉错误值。
将公式下拉填充即可。
另外,如果大家掌握了INDIRECT函数,会发现用INDIRECT函数最简单,直接一个公式下拉就可以了。
D2单元格公式为
=IF(INDIRECT(“b”&ROW(B1)*2)=0,””,INDIRECT(“b”&ROW(B1)*2))
公式第1部分
INDIRECT(“b”&ROW(B1)*2)
用indirect函数直接取B列所有偶数行的内容。
公式第2部分
=IF(公式第1部分=0,””,公式第1部分)
用if做个判断,如果结果为0,就显示空单元格;结果不为0,就正常显示结果。这是因为多余的单元格为显示0,我们用if函数来过滤掉这些0的值。
然后下拉填充所有D列。
同理,E2单元格公式为:
=IF(INDIRECT(“b”&ROW(B1)*2 1)=0,””,INDIRECT(“b”&ROW(B1)*2 1))
只要在公式里面 1,就变为姓名下面一行了,也就是对应的电话。
另外,过滤掉excel单元格0的显示,还可以直接系统设置里禁用。
点击“菜单栏-文件-选项-高级”,“此工作表的工作选项”里,默认是勾选了“在具有零值的单元格中显示零”,去掉这个勾选,刚才就不需要用if函数来判断0进行过滤了。
总结:这个案例还有其他的方法也可以实现,我们这里主要是给大家提供思路,拓展excel综合应用的办法。
暂无评论内容