一个函数公式，摸鱼半小时^_^

大家好啊!今天分享一个函数与Excel处理技巧，用上了可节省30分钟时间用来摸鱼^_^。

在工作中你也许可能大概会遇到如下的表格：

虽然看着舒适，但为进一步的数据处理、汇总带来一定的难度。

能我们需要优化成下面的样式，以便于数据的匹配与汇总。

如何操作呢，咱们可以借助公式与技巧可快速解决。

只需5分钟，包括分析思路、函数的编写以及调试过程。

工具：WPS个人版

将所有的人名汇集到一列，为数据匹配查找做准备。

1、用TEXTJOIN函数以顿号为间隔来合并两列人名：

=TEXTJOIN(“、”,1,B4:B12,D4:D12)

2、再用SUBSTITUTE函数将合并字符串的”、”顿号替换为Char(10)换行符。

3、结束编辑，右键复制，粘贴为值。

4、把单元格内容复制到新的单元格区域。

5、全选单元格中的内容复制，粘贴到Sheet3工作表的A2单元格。

6、在Sheet3工作表的B2单元格键入公式，返回各个姓名对应的部门

=INDEX(Sheet1!$A$1:$A$12,MAX(IFERROR(FIND(A2,Sheet1!$B$1:$D$12)^0*ROW($1:$12),””)))

数组公式 三键回车 ctrl+shift+enter

公式解读：

先看公式中的这部分：

=FIND(A10,Sheet1!$B$1:$D$12)

FIND函数查找A10单元格“小桂子”在sheet1表中的位置,返回一个数组结果：

{#VALUE!,#VALUE!,#VALUE!;#VALUE!;3}

在此基础上，加上一个乘幂计算^0

在查找到数字时，数组结果转换为1，此时数组结果为：

{#VALUE!,#VALUE!,#VALUE!;#VALUE!;1}

用以上结果乘以行号：

= FIND(A10,Sheet1!$B$1:$D$12)^0 *ROW($1:$12)

该部分的结果为：

={……,#VALUE!;#VALUE!;5;……} 

表示符合条件的结果位置在第5行。

接下来使用IFERROR函数将错误值转换为空文本，结果为：

={“”,””,””,””,5}

再使用MAX函数提取出其中的5，这个行号就是该人名对应的第几行（即部门名称）

最后，使用INDEX函数返回sheet1表的第一列第5行，就是该人名对应的部门名称。

8、Sheet3工作表C2单元格使用以下公式，提取成绩为“优秀”或“合格”：

=IF(COUNTIF(Sheet1!$B$4:$B$12,”*”&A2&”*”),”优秀”,”合格”)

温馨提示：

（1）请处理之前 把空格全部替换掉

（2）姓名重名的需要单独处理

（3）姓名重叠的需要单独处理，例如 李思 和 李思思

本次的分享就到这里，下次再会！

示例文件：

点击右上角“前往下载”获取。

END

微信扫一扫加关注，Office自学网官方微信公众号，

专注分享软件安装和视频教程，让你一号在手，办公无忧！