今日学习来源：微信公众号“ Excel之家ExcelHome”
原文链接：https://mp.weixin.qq.com/s/jrZjO6zlhmeNydoCsu7n8Q
今天学习的是如何批量提取Excel一对多时的“多”，原文主要介绍了Power Pivot、vlookup+辅助列两种方法，补充数组函数方法
【案例】
下图是某公司各部门的部分员工信息
现在要根据这些数据，按部门进行汇总。分别得到各部门的人员详单、人数和平均年龄：
方法1：用“辅助列+vlookup”取人员详单，再用countif、averageif求人数和平均年龄
方法2：用“数组函数”取人员详单，再用countif、averageif求人数和平均年龄
=INDEX($B$3:$B$101,SMALL(IF($A$3:$A$101=$F3,ROW($A$3:$A$101)-ROW($A$2),99),COLUMN(A1)))&""
简单解释下：
1、用index函数从姓名区域中查找满足部门条件的姓名；
需要特别说明的是，此处选取区域时一定要“超过非空数据区域 且 包含small函数中的k”，否则会返回#REF!
2、用if函数判断部门是否满足对应条件，并返回对应数值
如果满足，取该记录在对应区域的行号：ROW($A$3:$A$101)-ROW($A$2)
如果不满足，取一个较大值（超过数据区域的行数）
3、用small函数从1开始，逐个取出if的数值结果
4、函数外用&""限定超出区域的部分返回空
方法3：用power pivot + 透视表
步骤1
单击数据区域任意单元格，在【power pivot】选项卡下单击【添加到数据模型】按钮。
如果你的Excel中没有显示这个选项卡，可以依次单击【文件】→【选项】，打开【Excel选项】对话框。然后按下图所示步骤设置即可。
步骤2
单击数据区域底部的任意空白单元格，在编辑栏输入公式：
人员详单:=CONCATENATEX('表2','表2'[姓名],"，")
CONCATENATEX函数的作用是按照指定的间隔符号来合并多个字符串。
用法是：
=CONCATENATEX(表名,表名[字段名],间隔符号)
单击其他空白单元格，在编辑栏输入公式：
人数:=COUNTA('表2'[姓名])
COUNTA函数的作用是对指定字段中的非空单元格进行计数。
再次单击其他空白单元格，在编辑栏输入公式：
平均年龄:=AVERAGE('表2'[年龄])
AVERAGE函数的作用，是计算指定字段的平均值。
步骤3
插入数据透视表。
在【数据透视表字段列表】中，依次将“部门”拖动到行区域，将“人员详单”、“人数”和“平均年龄”拖动到值区域。
右键单击透视表中的“总计”→删除总计。
最后单击数据透视表，在【设计】选项卡下选择一种内置的样式效果：
网友评论