本文 我们在学习工作中难免会遇到一些奇葩表格,比如应该写在一列里面的内容,偏偏被拆成了两列…… 碰到这种奇葩数据,压根没办法正常统计计算,只能先调整成正常的形式。如果你只会复制粘贴,那可得忙半天,甚至熬夜通宵才能搞定。 而如果你掌握一些整理数据的小技巧,那可就不一样了。别人半天的工作量,可能你只需要 10 秒! 今天葫芦就来分享 6 个整理数据的万能公式。 行列互换 需要将数据的行和列对调过来时,可以用 TRANSPOSE 转置函数。 ❶ TRANSPOSE 是数组函数,必须先选中区域,输入公式后按【Ctrl Enter】完成输入才能生效。 ❷ 案例第 10 行中因为源数据没有更多数据,产生了错误值,这些错误值不能单独删除,因为数组区域是一个整体,必须同时修改。 其实,普通的行列互换转置,用选择性粘贴更简单,还可以保留源格式。So,函数公式虽好,可不要贪多哦 ~ 一行转多行 想要把一行数据转换成多行数据怎么办?比如,转换成固定 3 列的多行数据: 这是一个万能的函数公式,你不需要懂太多,只要会改参数就能套用。 =INDEX($A$1:$G$1,1,(ROW(A1)-1)*3 COLUMN(A1)) ❶ 把红色的数据区域换成你的数据区域 ❷ 把 3 换成一行希望填写的数据个数,比如一行 4 个,就换成 4。 一列转多列 这同样是一个万能的公式结构,只需要改变两个参数,就能套用 =INDEX($A$1:$A$7,ROW(A1) (COLUMN(A1)-1)*3,1) ❶ 把红色的数据区域换成你的数据区域 ❷ 把 3 换成一列希望填写的数据个数,比如一列 2 个,就换成 2。 多行转一行 这里用的公式看起来更复杂了,但是要做的事情反而更简单了,只需要将下方公式中 3 个一样的数据区域,统一换成你要转换的数据源,就可以直接套用。 =INDEX($A$1:$B$3,INT((COLUMN(A1)-1)/COLUMNS($A$1:$B$3)) 1,MOD(COLUMN(A1)-1,COLUMNS($A$1:$B$3)) 1) 多列转一列 万能配方: =INDEX($A$1:$B$3,MOD(ROW(A1)-1,ROWS($A$1:$B$3)) 1,INT((ROW(A1)-1)/ROWS($A$1:$B$3)) 1) 交叉转换 下面这个表格中的数据就比较棘手了,所有的人员信息都分成了两列,一列是人员信息类别的名称,一列是具体的信息。怎么把他们转成一个人的信息为一行的数据记录呢? 这是一个函数公式的高级应用: 案例效果中涉及 3 个函数公式,都是非常经典的套路。 ❶ 利用 COUNTIF 函数自动分组 =COUNTIF(数据区域,条件)
❷ 利用LOOKUP 函数实现多条件查找匹配 =LOOKUP(1,0/((条件1)*(条件2)*(条件N)),匹配区域)
❸ 利用 IFERROR 屏蔽错误值 =IFERROR(原公式,出错时返回另一个结果)
|
|