365买球官网入口-365买球网app下载

365买球官网入口-365买球网app下载

“大数化小”运用函数的问题解决技巧

2019/8/31 10:52:55

经常遇到这样的朋友:掌握了足够多的函数,但是遇到复杂问题,没办法一步到位时就感到为难了,觉得无从下手。尤其是数据量比较大、基础的手动操作费时费力的时候更是让人无法自拔。

在这里,我分享一个“大数化小”,把原始的“大数据”看作“小(1)数据”,构建解决方案,然后反推至“大数据”调整,最后进行验证,逐步解决的思路。


案例数据:

这次选用的案例是如下这样一组模拟数据:

image.png

该数据由两列组成:第一列是企业名称,第二列是其对应的关键词。该数据不符合数据库的一般结构,因为缺少索引(Index)。需求就是对这个数据进行转换,每个单位规整为一条记录(一行),在企业名称字段后依次列出该企业的所有关键词。借以实现的数组嵌套,函数公式为:{=TRANSPOSE(OFFSET(关键词!$B$1,MATCH(A2,关键词! A:A,0)-1,0, COUNTIF(关键词!A:A,Sheet2!A2),1))}
乍一看,公式这么复杂,那么是如何构思出来的呢?

解题思路:

1、从中拎出来一个企业的数据来模拟操作:

image.png

单个企业数据的手动操作步骤:①选中该单位的全部关键词所在区域B2:B9,②直接复制,③点选C2单元格,④然后采用选择性粘贴进行转置

GIF082702.gif

Ok,单个企业的操作就完成了。该企业的关键词就被转成一行,放置在了C2:J2的区域里。

接下来如何将第1步的操作推广至全部数据呢?

2、找出必须的手动操作步骤,匹配能将之实现的函数:

从步骤1的操作可以明确两个必须的操作:一个是转置;另一个容易被忽略,那就是转置的基础:选定单元格区域。

对应的两个函数是:转置函数(Transpose)和选区函数(Offset),我个人称之为:主体函数。

3、以必须的函数为框架配置辅助条件:

首先,构建一个数据库,将企业名称字段作为唯一的索引。这一步手动操作更便捷,可以对企业名称列使用数据透视表,也可以复制企业名称列到新的sheet,然后删除重复项实现。

其次,找出数据量从1到n时,主体函数中发生变化的参数(动态参数):Transpose函数的参数(Array区域)虽然发生了变化,但这个变化是通过Offset函数来实现的,所以这里我们只考虑Offset函数就可以了。

Offset函数有5个参数,动态参数有2个:区域的起始行号(Rows)和区域的高度(Height)。Offset函数语法如下:

image.png

最后,把动态参数也通过其他函数来获得。动态参数1:区域的起始行号Rows,即为原始数据里该企业名称首次出现的行数,用Match函数精确匹配即可得到。动态参数2:区域的高度(Height),即为该企业名称出现的次数,用Countif函数可以得到。

4、把所有辅助条件汇总,编制一个嵌套函数公式:

由于嵌套函数的结果为一个Array,所以需要用到数组函数来键入(Ctrl + Shift + Enter),这样就得到了文章开头的数组嵌套函数公式。

{=TRANSPOSE(OFFSET(关键词!$B$1,MATCH(A2,关键词! A:A,0)-1,0, COUNTIF(关键词!A:A,Sheet2!A2),1))}


GIF0828.gif

5、最后一步也是最重要的一步:数据量从n到1的验证。抽出任一单位的关键词进行验证。


小结

当我们遇到量比较大的数据处理时,应首先尝试从单个数据处理中找到基础的解决方案,再放置到全数据的场景中,根据数据量从1n的变化找出动态参数进行修正,逐步补足所需条件,最终实现高效的目标。这种比较常规的构思方式,不知道大家get到没有?希望对大家的Excel学习和工作有所帮助!