数据透视表为你搞定80%的工作难题，你却连它的最佳拍档都不知道……
每天一点小技能
职场打怪不得怂
编按：在日常工作中，数据透视表已经成为每个EXCELER最重要工作伙伴之一，在每个EXCEL的工作战场几乎都留下了它战无不胜的身影。可是，当遇到脏数据的时候，透视表却无能为力……所以，小E今天给大家带来的就是数据透视表和与之互补的最强搭档万金油函数，帮你搞定工作难题，不加班！
【前言】
数据透视表有不可承受之重！在EXCEL交流群里，笔者（E图表述）为大家解答关于数据处理类问题的时候，往往都会使用函数或者VBA。这时，笔者就会被大家“友善的提（抨）醒（击）”——有没有更简单的方法？因为大家都习惯使用那种信手拈来的方法，例如数据透视表。
不容置疑，数据透视表是EXCEL中一个很重要的版块，即便现下比较流行的POWERQUERY或者BI，其实也都是在数据透视表上的一个延伸。在合适的时候使用合适的方法，这永远是我们处理任何问题时应该秉承的原则。
【正文】
您好，这是您的开胃菜：
公众号回复：入群，下载练习课件
需求如下：
将A1:B13单元格区域中数据，填写到D1:I6单元格区域中。
这是一个模拟数据透视表的问题，将一维表转为二维表，典型的“数透工作内容”，但是这A列的仓库名称写的也太……太太……了。
（PS：这不是为了模拟而模拟的数据，现实工作中确实就有人把数据录成这样，这是和自己有多大仇，才能把EXCEL用成这样啊！）
这样的数据用数据透视表来做，基本无望了，即使是PQ的清洗数据也是无从下手。那么大家要么就收工将A列内容“清洗干净”，要么就只能烧脑想函数或者VBA解决了。
【万金油函数处理“脏数据”】
在E2单元格输入函数：
{=IFERROR(INDEX($B$2:$B$13,SMALL(IF(ISERROR(FIND($D2,$A$2:$A$13)),9^9,ROW($1:$12)),COLUMN(A1))),"")}
点击E2单元格后，选择选项卡中的“公式求值”，可逐一验证公式内各嵌套函数分别的结果。
函数解析：
STEP1：
使用ISERROR+FIND函数，判断A列内容中是否包含了D2单元格的值。在公式求值对话框中，可得出这部分的结果，如下图横线部分。
在这里要说明一下，ISERROR函数是判断是否报错的函数。如果FIND找到值的时候，ISERROR返回的是FALSE；如果FIND没找到值，是#VALUE时，反而ISERROR返回TRUE。切记不要弄混！
STEP2：
使用IF函数判断，如果返回值为TRUE，就返回9^9（9的9次幂，一个绝对大的数，目的是不会在后面的运算中用到）；返回值为FALSE时，使用ROW函数返回对应的行号。依然用“公式求值”功能，可知结果如下图下划线部分。
STEP3：
再使用SMALL+COLUMN函数，在数列中“逐个”提取第N小的值吧。例如E2单元格，COLUMN函数引用的是A1（A1单元格的列号），返回值是1，那么数列中第1小的值是1。如果函数变动位置，那么列号就会变动，原因是COLUMN函数的相对引用。
STEP4：
使用INDEX函数，结合第三步引出的值，就可以提取B2:B13单元格区域的值了。
STEP5：
使用IFERROR函数将没有引出内容的错误值，容错为“空值”。
STEP6：
最关键的一步，使用CTRL+SHIFT+ENTER组合键，将函数转为数组函数，及此，整个处理过程完毕。
【标准数据用数据透视表】
上面是一个工作上的实例，虽然笔者给出了解决的办法，但是真心不希望大家的工作表也是这样的“脏数据”。
如果是下面的数据，你会如何做呢？
加一列辅助列，同学们就可以使用数据透视表来做了，如下：
STEP1：
在C列做辅助列“入库批次”
在C2单元格输入函数=COUNTIF($A$2:A2,A2)，下拉至C13单元格填充。
STEP2：
选中A1:C13单元格区域，再在工具栏中点击插入——数据透视表。
在弹出的“创建数据透视表”窗口中，“表/区域”的文本框中已经自动添加上了（因为大家此前选中了该单元格区域）。然后，选择“现有工作表”，位置为F1单元格。最后，点击“确定”按钮。
STEP3：
在布局窗口中，按下图拖拽标签。
及此，大家就得到了一个入库批次及库存位置的统计表了，而且还有合计数。
不要只看，动手试一下吧。顺便“点赞、关注、在看、分享”一波走起~~~
扫一扫，在线咨询Excel课程
Excel教程相关推荐