|
编按：哈喽，大家好！遇到不规范的excel表格该怎么办呢？下文分享了7条函数公式，可以快速把不规范的表格变规范，也可以反过来把规范的表格变得符合领导阅读习惯的不那么规范的表格。不规范、规范，左右互搏。赶紧来看看吧！ 【前言】 来了，高手又要出招了，难道这次就是江湖传闻的大招“左右互博”之术？ 作者E图表述在以前的文章中写过一篇关于使用EXCEL对于数据源要求的文章——《函数技巧千千万，如何制表才关键！》，但是现实工作中，还是有很多的同学把表建设的不规范。不规范又能怎么样呢，反正没有人可怜你统计过程有多复杂，最后你要给出结果就对了，那么我们今天就以三种典型“不规范案例”的左右互转，来介绍几个“难且必会的”函数操作。 【正文】 ●左右互博第一式 案例如下： 左博右（不包含空格的数据）
解法： 在F2单元格输入函数： =INDEX($A$2:$C$4,INT(ROW(A3)/3),MOD(ROW(A3),3)+1) 函数解析： INT(ROW(A3)/3)：因为我们此时的函数是写在F2单元格的，此时ROW(A3)=3，3/3=1，再用INT函数取整，还是1； MOD(ROW(A3),3)+1：还是因为我们此时的函数是写在F2单元格，ROW(A3)=3，MOD函数求3/3的余数再加1，就等于1； 之所以作者E图表述一直强调“函数是写在F2单元格”，是因为如果我们在下拉填充函数之后，就是下表的对应值：
再使用INDEX函数分别按照对应区域$A$2:$C$4的行列号，引出值即可。 右博左（不包含空格的数据）
解法： 在D2单元格输入函数： =INDEX($A$2:$A$10,COLUMN(A1)+3*ROW(A1)-3) 函数解析： 和左博右“异曲同工”的效果，都是利用了INDEX函数和数学思维，只不过上例INDEX的数据区域是二维的，而本例是一维的，只需要一个维度即可，可是数学思维的运算更加的复杂。 ●左右互博第二式 案例如下： 左博右（包含空格的数据）
解法： 在G2单元格输入函数： {=INDIRECT(TEXT(SMALL(IF($A$2:$D$6<>"",ROW($2:$6)*1000+COLUMN(A:D),9^9),ROW(A1)),"R0c000"),0)} 函数解析： 相信很多同学对于这个函数，理解起来比较吃力，不过仔细看，这其实就是典型的“万金油”数组函数的用法。 第一步：如果A2:D6区域的值不等于空值，则返回单元格的行号乘以1000，再加上列号，否则返回9^9（一个足够大的数值），使用“公式求值”功能我们看到返回如下数列；
第二步：通过G2单元格下拉填充函数后，ROW（A1）函数返回的内容就是1，2，3，4，5……，再通过SMALL函数返回对应大小的值：2001，2002，2003，2004，3001，3002，3004，4001，……； 第三步：使用TEXT函数，将这组数列转换成R0C000的格式，就形成了类似R1C1单元格引用的格式R2C001、R2C002、……、R4C001、……； 第四步：使用INDIRECT函数将这些R1C1格式单元格文本，形成引用，就得到了我们的结果。这里有必要说一下，INDIRECT函数大家应该不陌生，但是一定要记得，此函数是由两个参数构成的，第2个参数，就是对单元格地址格式的限定，如下图所示：
我们函数中使用了INDIRECT（…，0）的结构，（0是FALSE，1是TRUE，我们以前有讲过）就是对R1C1单元格引用的参数。 还是左博右（包含空格的数据）
高手过招就要出其不备，还是左博右，没想到吧！哈哈哈哈哈…… 但是出招的方式变了，上例我们按照逐行引出内容，现在我们要逐列引出内容。 解法： 在G2单元格输入函数： {=IFERROR(INDIRECT(TEXT(MOD(SMALL(IF(A$2:D$6<>"",COLUMN(A:D)*(10^6+1)+ROW($2:$6)*100),ROW(A1)),10^6),"r0c00"),0),"")} 函数解析： 其实和上例的思路是一样的，依然利用了“经典的万金油”函数。 第一步：使用IF函数，判断当A2:D6单元格区域中的单元格不为空的时候，列号*10^6再加1再加行号乘100，得到下面的数列；
就是这么巧妙，我们拿出其中一个数值来分析，后面的函数原理就迎刃而解了。 例如：30000203，如果我们去掉30000，那么就是203，按照上例的思路，我们可以理解为2代表“第2行”，3代表“第3列”。之所以我们使用10^6，是为了让这个数字2到第1位数值之间有足够多的0，便于数据区域更大的时候依然可以使用。 第二步：通过G2单元格下拉填充函数后，ROW（A1）函数返回的内容就是1，2，3，4，5……，原理同上一个案例； 但是这个函数之所以能够按列索引，也是这步起到的作用，将二维的数列，按照数值的从小到大排列，得到了1000201，1000301，1000401，2000202，2000302，2000402，2000502，2000602，……，这样的数列。 第三步：用MOD（…，10^6）函数去掉代表行号位之前的数据，如图所示：
因为“公式求值”中显示不出MOD函数的数组效果，同学们可以“脑补”画面，形成了201、202、203、204、…、304、…这样的数列。 第四步：使用TEXT函数将值转换成R0C00的表达格式； 第五步：使用INDIRECT（…，0）函数引出每一个单元格的值，完工。 第六步：IFFEROR函数的容错功能。 话外音：第二式中都是左表变右表，一定有小伙伴会问“如何从右表变成左表呢？”，其实我们案例中的数据源是不能转变的，是因为本文中的案例没有可以参考的“分列依据”。但是作者E图表述绝不能让大家失望，后期一定会安排一期“专门的一维表转二维表的N种方法”，统一教给大家，敬请期待。 ●左右互博第三式 案例如下： 左博右
同学们你的表中是否也有这样的操作环境呢？是不是也曾为这样的数据统计而头疼，下面作者：E图表述带你破解这个难题。 解法： 在F2单元格输入函数： {=TRIM(MID(SUBSTITUTE(PHONETIC(B$2:B$7),"、",REPT(" ",199)),ROW(A1)*199-198,199))} 在E2单元格输入函数： =VLOOKUP("*"&F2&"*",IF({1,0},$B$2:$B$6,$A$2:$A$6),2,0) 函数解析： 我们先看F列单元格的函数。 第一步：PHONETIC函数，将B2:B7单元格区域的内容，“连成一串”！
注意：B2:B7单元格区域中，每一个单元格中的每一个元素都需要有一个同样的符号间隔，我们的案例中使用的（、）号，如果实际数据中的符号不统一，或者每个单元格结尾没有（、）号，可以使用替换功能、&连词功能，调整成案例中的格式。 第二步：使用SUBSTITUTE函数，将（、）号替换成199个空格。
第三步：使用MID函数“截取”这个超长的字符串，这个函数中最巧妙的就是ROW(A1)*199-198部分，又是“数学问题大于EXCEL问题”的思路，随着ROW函数的下拉，每次都从第n个199减198的位置作为截取点，截取199个字符。这样就把PHONETIC形成的大串字符“截成一段一段的”，而且每一段中都会包含一个字符元素。 第四步：再通过TRIM函数，去除截取好的字符串中的空格。及此完成F列的操作。 E列中的函数【=VLOOKUP("*"&F2&"*",IF({1,0},$B$2:$B$6,$A$2:$A$6),2,0)】就好理解了，使用了VLOOKUP函数反向查询的功能，再配合通配符的使用，我们这里就不浪费篇幅讲解了。不熟悉的同学，可以查看一下往期教程《VLOOKUP&LOOKUP双雄战（四）：在横向和逆向查询上的血拼！》。 右博左
左手画圆，右手画方。上例反转，看作者E图表述继续破解。（太巧妙了，必学思路） 解法： 首先复制A列，将其粘贴到E列，点击工具栏中“删除重复项”功能按钮，得到E列内容：
在F2单元格输入函数： {=MID(SUBSTITUTE(PHONETIC(OFFSET(A$1,MATCH(E2,A$2:A$24,0),,COUNTIF(A$2:A$24,E2),2)),E2,"、"),2,99)} 函数解析： 第一步：使用MATCH函数，找到E列值在A列区域中第一次出现的序号；使用COUNTIF函数确定A列中有几个E列对应的值。用“平面设计”这个字段来看，在A2:A24区域，第一次出现的位置是1，一共有3个值；
第二步：使用OFFSET函数，以A1单元格为基础单元格，向下偏移1个位置，向右不偏移，扩展出一个3行2列的新区域。用“平面设计”这个字段来看就是A2:B4区域；
第三步：使用PHONETIC函数，将OFFSET函数形成的区域引用，连接成一个字符串；
第四步：使用SUBSTITUTE函数，将E列对应的值替换成（、）号。以“平面设计”字段为例，就是将上图PHONETIC函数形成的字符串中所有的“平面设计”字符串替换成（、）号；
第五步：因为第一个是（、）号，所以我们使用MID函数，从第2位开始截取之后的99个字符。如果我们的元素内容较多，可以调大99这个值。这里也利用了MID函数的一个“BUG”，当数据不足99位时，会自动截取到最后一位，不报错！ 【编后语】 “左右互博”讲完了，基本上涵盖了所有清洗不规范数据的EXCEL函数思路。注意，我说的是思路，而不是解法。大家学习一定要记得，“知其然，更要知其所以然！”这样才能真的学到知识，否则看再多的教程也没有用，换个样子一样不会。学习更多技巧，请收藏关注部落窝教育excel图文教程。 |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
