Power Pivot 系列 (2) - 动态数据透视表和透视图

2024-02-05 13:32

本文主要是介绍Power Pivot 系列 (2) - 动态数据透视表和透视图,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本篇接着介绍 Power Pivot,使用上一篇的数据,编制一个透视表 + 透视图,实现数据动态展示,动态效果来自切片器 (slicer)。其实切片器并不是 Power Pivot 而是 Excel 的功能,在 Excel 中表格和数据透视表都能用到切片器。但鉴于切片器的作用,所以也在 Power Pivot 系列中对切片器作一些介绍。。

本篇要实现的最终界面如下:

最左边和最上边使用两个切片器实现动态筛选:按年份和按产品类别。主体部分的左边部分是按品牌统计销售数量和销售金额,右边是按季度统计。上一篇我已经解释了 Power Pivot 多个表建立连接之后数据透视表能在多个表中自由选择字段进行分析,所以按品牌来编制数据透视表已经没有问题,但按年度筛选,按按季度分析,需要先对数据进行进一步加工。

Power Pivot 添加列

进入 Power Pivot 界面,切换到 SalesOrder 表,选中最右边「添加列」任意一个单元格,然后将光标放到公式栏编写公式,这个跟 Excel 工作表界面非常类似:

然后在公式栏中输入:

= YEAR([TxDate])

像下面这样:


然后回车,这样就创建了一个新列,对新列重命名为 TxYear。使用 MONTH() 函数添加另一个计算列 TxMonth:


介绍一下 Power Pivot 字段的语法。Power Pivot 的表 (table) 用单引号包含,比如 'SalesOrder'。如果表名称没有空格,也可以省略单引号;Power Pivot 的字段用中括号包含,比如 [TxDate]。引用其他表的字段需要用完全标识符 (full-qualified identifier),比如 'Products'[CategoryName]。在公式栏或度量值输入的时候,可以输入单引号或中括号,Power Pivot 在输入单引号或中括号后,给出表或字段的智能提示,能帮助公式输入。

但 Power Pivot 并没有直接获取季度的函数,可以用 FORMAT() 函数,根据日期来获得季度值:

TxQuarter = VALUE(FORMAT([TxDate], "Q"))

Format 函数的第二个参数值为 Q 表示返回季度, FORMAT()函数的返回值是文本型,所以再用 VALUE 函数转换为数字。

添加数据透视图

回到 Excel 工作表,通过下面的菜项添加 一个新的数据透视图:

图例选择 Products 表的 BandName,选择 SalesOrder 表的 SalesAmount ,计算类型使用合计


此时得到的数据透视图如下,因为品牌比较多,最初的格式不太美观,需要进行美化。


现在值字段显示为「以下项目的总和:SalesAmount」,我们可以在值字段设置(右键菜单)调出下面的对话框进行修改。将值字段改为:销售总额:

这个是 Excel 数据透视表就有的功能,Power Pivot 中有一个更好的方法:度量值。什么是度量值呢?可以简单的理解为基于字段的计算值,但这个计算值非常灵活,能根据相关的条件(称为上下文)对参与计算的数据进行筛选。

再进入 Power Pivot 界面,下图红线标记的部分就是建立度量值的区域:


选中 SalesAmount 列任意单元格,或者选中整列,然后在【主页】选项卡中点击【自动汇总】下拉框,选择求和项,这样就建好了一个度量值。

这个新添加的度量值,在公示栏显示公式,在 SalesAmount 列下面的度量值区域显示了计算的结果:


在公示栏将度量值的名称改为:销售额:

销售额:=SUM([SalesAmount])

同样的方法添加一个度量值:销售数量,对 Qty 字段进行求和。

销售数量:=SUM([Qty])

回到 Excel 界面,此时数据透视图字段面板中,在 SalesOrder 表下面,多了两个字段,前面有 fx 标识,这就是新添加的两个度量值。


销售额度量值拖到值字段中,这个时候数据透视图的效果与前面将 SalesAmount 拖到值字段类似,但值字段在透视图中也显示为销售额,而不是「以下项目的总和:SalesAmount」。

接下来插入两个切片器:年度和产品类别。选中数据透视图,Excel 自动出现【数据透视图分析】选项卡,点击【插入切片器】菜单:

选择 Products 表的 CategoryName 和 SalesOrder 表的计算字段 TxYear,将这两个字段作为切片器。

可以对切换器的样式、列数和标题等进行修改。我门对两个切片器都改变标题,拖到合适的位置。在选中切片器的时候,Excel 自动出现【切片器】选项卡,在这里设置切片器的样式以及切片器列的数量。


透视图左边 y 轴 数据精确到个位,可以改为以千元显示。方法是选中左边 y 轴,右键,选择设置坐标轴格式菜单,右边出现设置面板。将数字的类别设为「自定义」,将格式代码改为 #,##0, 然后点击添加按钮:

在这里插入图片描述

数据就变成千元显示格式了:

可以用同样的方法添加一个按季度分析的数据透视图,但更简单的做法是从已经添加的数据透视图复制一个,再进行修改。我用复制的方法,很快添加第二个数据透视图如下:


在数据透视图的下面添加两个数据透视表(操作参照上一篇),调整大小和位置,得到最终的输出效果。

将切换器连接到报表

添加的切换器,并不是自动连接到所有数据数据透视表和数据透视图,如果没有关联,选中切片器后,在自动出现的【切片器】选项卡中,点击【连接报表】菜单进行设置。

示例数据

Github - Dynamic Pivot using Slice

这篇关于Power Pivot 系列 (2) - 动态数据透视表和透视图的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/681063

相关文章

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门(一):登录与退出》 《Spring-Security-入门(二):基于数据库验证》 《Spring-Security-入门(三):密码加密》 《Spring-Security-入门(四):自定义-Filter》 《Spring-Security-入门(五):在 Sprin

科研绘图系列:R语言扩展物种堆积图(Extended Stacked Barplot)

介绍 R语言的扩展物种堆积图是一种数据可视化工具,它不仅展示了物种的堆积结果,还整合了不同样本分组之间的差异性分析结果。这种图形表示方法能够直观地比较不同物种在各个分组中的显著性差异,为研究者提供了一种有效的数据解读方式。 加载R包 knitr::opts_chunk$set(warning = F, message = F)library(tidyverse)library(phyl

【生成模型系列(初级)】嵌入(Embedding)方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入(Embedding)方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节:嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”,它将文本中的单词或短语转换成计算机能够理解的数学形式,即向量。 正如翻译机将一种语言

flume系列之:查看flume系统日志、查看统计flume日志类型、查看flume日志

遍历指定目录下多个文件查找指定内容 服务器系统日志会记录flume相关日志 cat /var/log/messages |grep -i oom 查找系统日志中关于flume的指定日志 import osdef search_string_in_files(directory, search_string):count = 0

GPT系列之:GPT-1,GPT-2,GPT-3详细解读

一、GPT1 论文:Improving Language Understanding by Generative Pre-Training 链接:https://cdn.openai.com/research-covers/languageunsupervised/language_understanding_paper.pdf 启发点:生成loss和微调loss同时作用,让下游任务来适应预训

Spark MLlib模型训练—聚类算法 PIC(Power Iteration Clustering)

Spark MLlib模型训练—聚类算法 PIC(Power Iteration Clustering) Power Iteration Clustering (PIC) 是一种基于图的聚类算法,用于在大规模数据集上进行高效的社区检测。PIC 算法的核心思想是通过迭代图的幂运算来发现数据中的潜在簇。该算法适用于处理大规模图数据,特别是在社交网络分析、推荐系统和生物信息学等领域具有广泛应用。Spa

Java基础回顾系列-第七天-高级编程之IO

Java基础回顾系列-第七天-高级编程之IO 文件操作字节流与字符流OutputStream字节输出流FileOutputStream InputStream字节输入流FileInputStream Writer字符输出流FileWriter Reader字符输入流字节流与字符流的区别转换流InputStreamReaderOutputStreamWriter 文件复制 字符编码内存操作流(

Java基础回顾系列-第五天-高级编程之API类库

Java基础回顾系列-第五天-高级编程之API类库 Java基础类库StringBufferStringBuilderStringCharSequence接口AutoCloseable接口RuntimeSystemCleaner对象克隆 数字操作类Math数学计算类Random随机数生成类BigInteger/BigDecimal大数字操作类 日期操作类DateSimpleDateForma

Java基础回顾系列-第三天-Lambda表达式

Java基础回顾系列-第三天-Lambda表达式 Lambda表达式方法引用引用静态方法引用实例化对象的方法引用特定类型的方法引用构造方法 内建函数式接口Function基础接口DoubleToIntFunction 类型转换接口Consumer消费型函数式接口Supplier供给型函数式接口Predicate断言型函数式接口 Stream API 该篇博文需重点了解:内建函数式

Java基础回顾系列-第二天-面向对象编程

面向对象编程 Java类核心开发结构面向对象封装继承多态 抽象类abstract接口interface抽象类与接口的区别深入分析类与对象内存分析 继承extends重写(Override)与重载(Overload)重写(Override)重载(Overload)重写与重载之间的区别总结 this关键字static关键字static变量static方法static代码块 代码块String类特