【Excel如何做】用函数代替数据透视表的功能(一) |
您所在的位置:网站首页 › excel的数据透视表怎么使用公式计算 › 【Excel如何做】用函数代替数据透视表的功能(一) |
背景 在标准模式中,我们推荐大家尽可能地使用数据透视表完成最终的工作。不过那个工作说的是最终的数据报告。 但是,在以前的Excel中,有很多非报告的工作也只能用数据透视表来完成(这是以前最好的工具了)。其实,这么做有点啰嗦,并且不太合适,因为会产生大量中间表。 现在,有了动态数组以及各种新函数,我们完全可以用函数来完成这一部分工作。 例如: 我们有如上图所示源数据,希望做数据透视表如下: 这是一个很简单的数据透视表,我们下面看如何用函数实现。 实现方法首先,我们得到所有的产品(不重复列表),需要使用UNIQUE函数: =UNIQUE(B3:B23)结果如下: 再得到所有的区域列表: =TRANSPOSE(UNIQUE(C3:C23))仍然使用UNIQUE函数获得不重复区域,同时使用TRANSPOSE函数将结果转换为一行: 接下来计算中间的数量合计,因为是求和,所以我们使用SUMIFS函数: =SUMIFS(D3:D23,B3:B23,F3#,C3:C23,G2#)结果如下: “透视表”完成! 在透视表中,我们除了求和外,还可以进行计数,最大最小值等汇总, 我们都可以用下面的函数实现: 计数 => COUNTIFS函数 平均值 => AVERAGEIFS函数 最大值 => MAXIFS函数 最小值 => MINIFS函数 用一个公式实现上面,我们使用了3个公式实现了“基本透视表”。借助LET函数,我们可以只用一个公式就完成这个“透视表”: =LET(products,B3:B23,areas,C3:C23,qty,D3:D23,uni_products,UNIQUE(products),uni_areas,TRANSPOSE(UNIQUE(areas)),result_qty, SUMIFS(qty,products,uni_products,areas,uni_areas),row_title, HSTACK({''},uni_areas),VSTACK(row_title, HSTACK(uni_products, result_qty)))结果如下: 总结先说一下用函数实现数据透视表的好处:不用手工刷新。 当然这里我们实现的只是最基本的透视表,还有很多功能没有完成,比如添加汇总列和汇总行,添加分类汇总,以及实现各种百分比统计等等。实际上,用函数可以实现非常复杂的透视表功能。我们后面将为大家详细介绍。 Excel+Power Query+Power Pivot+Power BI Power Excel 知识库 按照以下方式进入知识库学习Excel函数 底部菜单:知识库->Excel函数自定义函数 底部菜单:知识库->自定义函数 Excel如何做 底部菜单:知识库->Excel如何做面授培训 底部菜单:培训学习->面授培训 也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |