抛弃那些看似有用,实则鸡肋的Excel技巧吧!
Excel去重,是一个看上去很简单的问题。但是很多人却误解了。常用的删除重复项是实际用处最少的一个功能。因为在现实工作中几乎没有需要仅仅得到不重复列表就完成的工作。多数情况下都是需要后续分析和统计。这时,删除重复项功能就变成了一个很大的障碍,阻止我们建立自动化的数据处理方案。
只有抛弃这里看似有用,实则鸡肋的Excel功能,从Power Excel的视角学习和使用Excel,才能真正提高效率。
在传统上,去重是一个非常简单的问题,只要用删除重复项功能就可以完成:
但是,这个问题却是被过度简化的问题。
你可以回忆一下,在现实的工作场景中,有多少次你需要回答正在销售的产品清单,或者是发生销售的区域或月份?
可以说,很少发生这样的问题。
因为这些问题的答案一定在每个业务人员心里。
很少有人真的需要从数据中得到一份不重复列表。
这些问题之所以被抽象出来,是因为我们要用它们做后续的分析,而在这些真正的场景中,删除重复项就不是合适的解决方案。
简单总结一下,我们需要去重的操作有3个场景:
简单去重
就是仅仅从数据中得到不重复清单就OK了。
单表去重统计
得到不重复清单,然后针对其中的每项汇总统计。比如每种产品的销售收入,市场投入等。
多表分类统计
也是需要不重复清单,然后针对其中的每项汇总统计。不过原始数据在多个表上。
其中只有最不常见的a场景才是删除重复项功能的最适合场景。其他场景,比如b场景,当然也可以先用删除重复项功能获得不重复列表,然后再写公式汇总。不过,这只适合那些一次性的场合,不能建立可重复的自动化方案。
对于b. 单表去重统计场景来说,最合适的是使用公式:
=LET( Title, {"产品","收入","计划"}, UniProduct, UNIQUE(表1[产品]), Sales, SUMIFS(表1[收入], 表1[产品], UniProduct), SalesInPlan, SUMIFS(表1[计划], 表1[产品], UniProduct), VSTACK(Title, HSTACK(UniProduct, Sales, SalesInPlan)))
使用这个公式可以在源数据和结果数据之间建立自动化的方案,随时自动反应源数据的变化:
但是这个公式用到了LET函数和UNIQE函数,所以需要新版本的Excel。如果你的版本不支持,那么可以使用c场景中的方案。
值得一提的是,在b场景中,有时也可以通过数据透视表完成。不过透视表并不是最合适的方案(尽管在这个案例中,透视表可以完成我们的要求)。这是因为有可能需要统计汇总的数据和产品不在同一张表上。
对于c. 多表分类统计来说,最合适的是使用Power Query,只要建立一个简单的查询:
let 源 = Excel.CurrentWorkbook(), 筛选的行 = Table.SelectRows(源, each Text.Contains([Name], "SalesData_")), #"展开的“Content”" = Table.ExpandTableColumn(筛选的行, "Content", {"Year", "Qtr", "Month", "产品", "区域", "收入", "计划"}, {"Year", "Qtr", "Month", "产品", "区域", "收入", "计划"}), 分组的行 = Table.Group(#"展开的“Content”", {"产品"}, {{"收入", each List.Sum([收入]), type number}, {"计划", each List.Sum([计划]), type number}})in 分组的行
就可以自动得到一个汇总后的结果表。关键是,整个操作特别简单,而且方案是自动化的。
正如之前所讲,这个方案同样可以用在b场景中,对Excel版本没有特殊要求。
关于各种方案的介绍请看视频。
实际上,我们熟知的大部分Excel技巧都是这种鸡肋性质的。只有一些高度简化的特定场景中,才能使用它们。真正对我们有用的方法却没有得到应有的关注。这些真正的方法就是Power Query,Power Pivot等这些Excel的新功能,以及Excel的动态数组和新函数。这些内容一起,构成了我们的Power Excel方案。
联系客服