遇到这样的源数据，千万别用数据透视表！万金油才能帮你解决问题！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

有这样一道练习题，需要将左侧的数据转换为右侧的数据。

第一眼望去，感觉可以使用数据透视表来解决问题的！但仔细看一下，真是吓人一跳啊！A列中的仓库名称输入五花八门，数据透视表根本处理不了想这样的源数据的！

看起来，只有请出坊间传闻的万金油经典公式来帮我们啦！

01

在单元格E2中输入公式“=IFERROR(INDEX($B$2:$B$13,SMALL(IF(ISERROR(FIND($D2,$A$2:$A$13)),9^9,ROW($1:$12)),COLUMN(A1))),"")”，三键回车并向下向右拖曳即可。

思路：

  • 由于仓库的名称比较混乱，不能直接使用。我们需要先处理一下。使用FIND函数在单元格区域$A$2:$A$13中查找数字“1”，即第1个仓库，并使用ISERROR函数来做逻辑判断

  • 如果查找到，就返回对应的行号；如果查找不到，就返回一个极大数9^9

  • 下面该SMALL函数上场了。该函数从小到大依次返回行号

  • 利用INDEX返回入库的数量，并用IFERROR函数做修正处理

02

这是一个万金油公式的变异应用。

在单元格E2中输入公式“=IFERROR(SMALL(IF(LEN($A$2:$A$13)<>LEN(SUBSTITUTE($A$2:$A$13,$D2,"")),$B$2:$B$13,""),COLUMN(A2)),"")”，三键回车并向下向右拖曳即可。

思路：

  • 两个LEN（）部分做比较，若相等，则返回空值；如不相等，则返回单元格区域$B$2:$B$13中对应的数值

  • SUBSTITUTE($A$2:$A$13,$D2,"")部分，利用SUBSTITUTE函数将单元格区域$A$2:$A$13中的数字“1”用空值替换掉。替换前和若字符长度不相等，表明这个仓库就是当前仓库

03

其值这个问题也可以用POWER QUERY来处理，但是也要对A列中的数据预先处理一下。看起来，在数据输入阶段就设定好规范的数据录入准则是非常必要和重要的！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

本期阅读分享赠书书目为：

赠书规则：

  • 本公众号下文章“阅读最多”排名和“分享最多”排名各自第一名的朋友将会获赠一本

  • 截止时间：2021-5-9

我就知道你“在看”

注意！前方有红包挡道！速点阅读原文消灭之

推荐阅读
赞 (0)

相关推荐