数据透视表函数公式（数据透视表技巧）【百科全说】

今天跟大家分享一位学员在工作中遇到的求助案例，她需要将相同编号对应的人名合并到一个单元格内,也就是说她想要将下图【表1】转换成【表2】的样式。好家伙，这个表有16000多行，要是手工做，那不得累出肩周炎、下肢静脉曲张等毛病。首先，我们先来看下她要的效果：

【表1】原表↓

【表2】需要的效果，见下图↓

1、函数解法

首先，我们先来说说函数解：（注意在此题中如使用函数解，A列必须要先排序，将同样编号的排到一起这是关键。后面我会介绍另外一种透视表的解法，什么，你说透视表做不到，今天我就让你瞧一瞧，^-^）

1.首先在C列加一辅助列，标记上逗号

2.在D2输入公式：

=IF(A2=A3,"",PHONETIC(INDIRECT("b"&MATCH(A2,$A$1:A565,0)):C2))并将其下拉填充至末尾。

3.筛选D列不为空的即获得需要的效果。

效果如下图所示：↓

我们来简单说下上面公式的含义：

MATCH(A2,$A$1:A565,0)是指找各个编码第一次出现的起始位置，MATCH函数是EXCEL中主要的查找函数之一，当MATCH函数第三参数为0表示精确查找，该函数通常有以下几方面用途：

(1)确定列表中某个值的位置；

(2)对某个输入值进行检验，确定这个值是否存在某个列表中；

(3)判断某列表中是否存在重复数据；

(4)定位某一列表中最后一个非空单元格的位置。

技巧：查找文本值时，函数MATCH不区分大小写字母。

INDIRECT("b"&MATCH(A2,$A$1:A565,0)):C2)这段是将MATCH找到的起始位置与C列当前行所在的单元格做为终点进行连接形成单元格区域，随着下拉它会动态变化，这是相对引用的特点，然后利用INDIRECT间接引用得到该区域内的值。

PHONETIC(INDIRECT("b"&MATCH(A2,$A$1:A565,0)):C2)这段是将上一步所获得的值进行拼装，将其连接到一起。

=IF(A2=A3,"",PHONETIC(INDIRECT("b"&MATCH(A2,$A$1:A565,0)):C2))最后做个判断是否显示，如果A列下一行与本行内容相同，那么就不显示，否则将它显示出来。

不错，这里用到了函数的嵌套，可能有些小伙伴会觉得函数嵌套出身高贵，难以亲近，妹子你等着，哥给你介绍另外一种不嵌套的。

2、据透视表解法

如果你在用excel2016，不防试下数据透视表中CONCATENATEX函数，具体操作方法如下：

1.光标放在A1:B37区域中的任一单元格，按CTRL T快速插入【表】，表名为【表1】

2.在【PowerPivot】选项卡下将此表【添加到数据模型】。

（如果表格中没有这个选项，可以在“开发工具”中，点击“COM加载项”-勾选“
MicrosoftPowerPivotforExcel”，如下图所示。）

3.【PowerPivot】选项卡-【度量值】-新建度量值。

4.弹出的对话框中输入公式，如下图所示：

该公式的用途为文本合并，第一参数与第二参数为必需，第三参数可选，=CONCATENATEX('表1','表1'[人名],",")这个函数的白话意思就是要联接哪张表里的哪个字段下的内容，用哪个分隔符连接。CONCATENATEX第三参数为分隔符。我这里用的是用逗号分隔，如果你要用顿号分隔，改成这样即可：CONCATENATEX('表1','表1'[人名],"、")。

5.光标放在A1:B37区域中任一单元格在插入数据透视表，勾选【将此数据添加到数据模型】

6.将编码拉到行字段，度量值1拉到值字段，如下图所示：

7.选中透视表，右键删除总计，字段名改成你要的即可。

用方法二的好处在于它可以动态扩展且可以不用预先排序。OK，咱们今天就分享到这，祝各位小伙伴天天好心情………….我是爱踢汪，您的关注是我坚持到现在的唯一动力，有了您的支持与鼓励，我才有信心一直坚持下去，继续奉上更多内容。衷心期待您能点一下上面红色关注按钮，关注我一下。万分感谢！

数据透视表函数公式（数据透视表技巧）

相关推荐