脏数据源，数据透视表不能搞定，就用万金油函数搞定！
作者：E图表述来源：部落窝教育发布时间：2021-05-08 10:36:08点击：2549
编按：
在日常工作中，数据透视表已经成为每个EXCELER最重要工作伙伴之一，在每个EXCEL的工作战场几乎都留下了它战无不胜的身影。可是，当遇到脏数据的时候，透视表却无能为力，万金油函数成为替代它的最佳选择！所以，小E今天给大家带来的就是万金油函数和数据透视表双重技能，帮你搞定工作中80%的难题，只需1分钟！
【前言】
数据透视表有不可承受之重！在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：
在布局窗口中，按下图拖拽标签。
及此，大家就得到了一个入库批次及库存位置的统计表了，而且还有合计数。
不要只看，一起动手试一下吧！
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
Excel数据透视表入门教程3：透视表排序的4种方法和插入公式
版权申明：
本文作者E图表述；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。