在Excel中创建惊人的关键性能指标数据卡

2018-12-19

在这篇文章中,我们将学习如何在Excel中创建一些超酷的关键性能指标(KPI)数据卡。

这些非常适合注意对仪表板中总销售的单一度量。

在Excel中创建惊人的关键性能指标数据卡

在这篇文章中,我们将获得额外的花哨,并在上一年的百分比变化和背景中增加一些百分比的百分比,并在背景中添加一些百分点。

要建立这些花哨的KPI卡,我们将从Excel技巧的工具箱中拔出枢轴表,枢轴图,GetPivotData,形状和自定义编号格式。

数据设置

AdventureWorks-Order-Data在Excel中创建惊人的密钥性能指标数据卡

在这个例子中,我将使用AdventureWorks数据集,该数据集有一个虚构的自行车商店订购数据。

此数据包含订购日期合计订单我们将使用数据卡。

枢轴表设置

然后,我们需要使用此订单数据创建两个枢轴表。

这可以通过在订单数据中选择一个单元格并进入插入选项卡的色带并选择数据透视表命令。

Pivot-Table-1在Excel中创建惊人的关键性能指标数据卡

对于第一个枢轴表,我们需要添加订购日期现场进入区域和两个实例合计订单现场进入区域。

这应该将订单日期自动分组为年、季度和月。我们只需要年和月分组,因此我们可以从数据透视表中删除季度分组。

日期分组在Excel中创建惊人的关键性能指标数据卡

如果Excel不自动分组我们的订单日期,日期将被列在透视表的行,我们可以右击在他们身上,选择团体命令从菜单。

的第一个实例合计订单在里面我们的枢轴表的区域将是常规的。对于第二个例子,我们将创建百分比差异计算。

Show-Values-As-ulliver usy在Excel中创建惊人的关键性能指标数据卡

我们可以右击第二组的数字合计订单领域,然后选择显示值%区别

Show-ValuesS-基本场和项目在Excel中创建惊人的密钥性能指标数据卡

然后我们可以选择作为我们的基本域和以前的作为我们的基本项目。这将计算每个月与当前年度的百分比差异。

数据透视表2在Excel中创建惊人的关键性能指标数据卡

对于第二个数据透视表,我们将添加Order Date分组到区域,订单日期分组到地区和合计订单现场到区域。

然后可以对两个数据透视表进行年过滤,只显示我们想要比较的两年数据。

Add-Fimtotals-AT-Top-of-Group在Excel中创建惊人的密钥性能指示符数据卡

我们还将在第一个枢轴表中需要小计。我们可以通过选择枢轴表并进入分析选项卡➜小计显示组顶部的所有计

使用GetPivotData提取KPI

现在,我们可以使用GetPivotData公式从我们的枢轴表中提取数字,该数字将成为关键性能指示符。在我们的案件中,这将是2016年的总订单,2016年至2015年的百分比差异。

Generate-getPivotData在Excel中创建惊人的密钥性能指示符数据卡

首先,我们需要打开GetPivotData特性。这是一个(有时令人讨厌的)功能,当试图引用透视表中的单元格时,它会自动创建一个GetPivotData公式。

默认情况下应该启用这,但如果不是,请转到分析选项卡,然后单击旁边的小箭头选项然后选择生成getPivotData.(打开的时候,旁边应该有一个小的勾号)。

在Excel中创建惊人的关键性能指标数据卡

在枢轴表之外的一个小区中,我们可以创建参考枢轴表中的2016小计的公式。这将自动创建GetPivotData公式以引用Pivot表中的值。

2016  - 百分比 - 差异 - 子电池引用在Excel中创建惊人的关键绩效指标数据卡

在透视表之外的另一个单元格中,我们可以创建另一个GetPivotData引用,以引用2016年百分比差异小计。

Format-getPivotData-References在Excel中创建惊人的密钥性能指示符数据卡

我们需要将这两个值格式化为单元格中的格式,它们将如何在我们的数据卡中格式化。

订单小计可以格式化为没有小数位的货币。去吧首页标签并选择货币数量功能区的部分并调整小数位以显示无。

Add-Custom-Format在Excel中创建惊人的密钥性能指示符数据卡

百分比差异小计,我们将添加一个自定义格式向上和向下的箭头符号为正负值。新闻Ctrl.+1打开“格式单元格”对话框。

  1. 去吧数量选项卡。
  2. 选择自定义从列表中。
  3. 添加▲0.0%;▼-0.0%类型场,按下按钮。这将为任何负数显示任何正数和向下箭头显示向上箭头。

创建数据卡

插入-矩形-形状创建惊人的关键性能指标数据卡在Excel

我们需要在Excel工作表中添加4个矩形。一个用于背景,两个用于显示小计,一个用于显示标题标题。

去吧插入选项卡,然后单击形状按钮从所有各种形状中选择。选择矩形形状,然后单击并拖动工作表以绘制形状。

Add-Shape-Fill-and-utline在Excel中创建惊人的密钥性能指示符数据卡

使用4个形状,我们将根据其目的格式化填充和大纲。

  1. 用于背景形状填充形状轮廓可以是纯色。我们稍后将使用黑色和白色的字体颜色,因此我们应该选择填充的颜色,与黑色和白色会产生对比。黑色轮廓和绿色填充将很好地工作。
  2. 我们将选择小计没有填充没有大纲。这样背景将显示。
  3. 对于百分比不同小计,我们也将选择没有填充没有大纲
  4. 对于标题,我们将挑选一个白色形状填充和黑色形状轮廓

如果我们按此顺序创建形状,那么我们稍后不需要调整分层顺序。背景形状将在其他形状下方。

小计和百分比差异小计数据卡

在Excel中创建惊人的关键性能指标数据卡

现在我们可以将小计到我们的矩形,没有填充或概述。

  1. 选择形状。
  2. 在公式栏中键入=然后选择包含GetPivotData引用的单元格引用我们的小计值。
  3. 去吧首页选项卡并增加字体大小和改变字体颜色。
  4. 我们也可以中间中心对齐文本。

我们需要与我们的第二个矩形同样,没有填充或概述和我们的差异差异计数值。

创建数据卡标题标题

我们的最后一个矩形形状将包含我们的数据卡标题标题。我们可以简单地选择形状并开始键入我们的标题。然后我们可以去首页选项卡以增加字体大小,更改字体颜色,中间和中心对齐文本。

安排对齐和组形状

安排 - 和 -  aligal在Excel中创建惊人的关键性能指标数据卡

现在我们已经有了所有的形状,我们可以选择并拖动它们,像上面的图片一样排列它们。

当我们对近似位置满意时,我们可以用对齐工具完美地对齐它们。选择所有的形状(选择一个然后按Ctrl.+一种),然后去绘图工具设计选项卡➜安排部分➜对齐居中对齐

我们也可以把它们组合在一起,这样就不会不小心搞混了。所有选中的形状转到绘图工具设计选项卡➜安排部分➜团体团体

添加界面图表

Add-Pivotchart在Excel中创建惊人的密钥性能指示符数据卡

这就是我们要用到第二个主元表的地方。选择它,然后转到分析标签并按第三章命令。

选择线条图表在Excel中创建惊人的密钥性能指示符数据卡

然后从选项中选择一条线条图。

删除 - 对象 - 从图表中创建Excel中的惊人钥匙性能指标数据卡

我们还要删除所有按钮,轴,网格线,将图例移动到底部,然后删除填充和轮廓。

  1. 右键点击在任何按钮和选择隐藏图表上的所有字段按钮
  2. 选择轴并按删除
  3. 选择网格线并按删除
  4. 将图例移动到底部。选择图表,然后转到Pivotchart工具设计选项卡➜添加图表元素传说底部
  5. 删除任何填充。PivotChart工具格式选项卡➜形状填充没有填充
  6. 删除任何轮廓。PivotChart工具格式选项卡➜形状轮廓没有大纲
  7. 我们还可以将图例文本更改为白色,以便更好地对绿色背景的可见性。选择传奇并转到首页选项卡以更改字体颜色。
  8. 将图表颜色调色板更改为与背景相比良好的颜色调色板。Pivotchart工具设计选项卡➜改变颜色➜从其中一个颜色的调色板挑选。

在数据卡上安排折线图

现在,我们可以把图表放在数据卡上。

选择图表,然后转到PivotChart工具格式选项卡➜对齐抓住形状。这将使您可以轻松地将图表的边缘排列到数据卡背景的边缘。

现在拖动图表边缘,使其与背景对齐。图表将是最顶层的分层对象,因为它是最后创建的。我们希望它刚好在大的总订单数下面。只有选中的图表转到PivotChart工具格式选项卡➜倒退倒退直到它落后于订单总数。

现在我们可以将一切组合在一起。选择每个对象并转到绘图工具格式选项卡➜团体团体

成品数据卡在Excel中创建惊人的关键性能指标数据卡

我们现在有一个富有的数据卡,可以移动并调整为一个对象。

添加切片器以控制数据卡

由于数据卡是基于数据透视表的,我们现在可以添加切片来控制数据卡!

Insert-Slicer在Excel中创建惊人的关键性能指示器数据卡

选择任意一个数据透视表,然后到分析选项卡并选择插入切片机命令。

Select-Slicers在Excel中创建惊人的密钥性能指示符数据卡

我们可以为切片器选择数据中的任何字段。在这个例子中,我们将为我们的产品添加一个切片器。

报告 - 连接在Excel中创建惊人的密钥性能指示符数据卡

我们还需要将新的SliCer连接到另一个枢轴表。右键点击在切片机上并选择报告连接从菜单中,然后在“报告连接”对话框中查看枢轴表。

现在,数据卡将显示2016年在切片机中选择的任何产品集的总订单以及2015年的变化百分比。

结论

有一点工作和创造力,我们可以为我们的Excel仪表板制作一些非常酷的东西。

正确设置数据透视表以提取我们想要显示在数据卡中的指标,这对整个过程至关重要。数据透视表负责总结数据的关键指标。

形状不能直接引用数据透视表中的值,所以有必要使用GetPivotData公式作为中间步骤来引用这些值。

我很想知道你能做出什么样的仪表盘视觉效果。请在评论中告诉我!

关于作者

John Macdougall.

John Macdougall.

John是一个微软MVP和自由职业顾问和专业从事Excel,Power BI,Power自动化,电源应用程序和SharePoint的培训师。您可以在博客或YouTube频道上找到其他有趣的文章。

订阅

广告

相关文章

评论

1条评论

  1. 盖伯瑞尔

    你好,
    非常有趣的文章,我喜欢整个设置。但是,关于您的示例文件,有一点需要注意的是——在该文件中,订单总数没有计算,而是显示了相同的订单数量,而不管订单数量是多少。这使得卡片上的数字不正确(如果你只是想重现结果,这是一个小细节,但我偶然发现了这一点)。
    保持良好的工作,谢谢所有精彩的文章。
    亲切的问候
    加布里埃尔

    回复

提交评论

您的电子邮件地址将不会被公布。必需的地方已做标记*

这个网站使用Akismet来减少垃圾邮件。了解如何处理评论数据

获取最新消息

跟着我们

在社交媒体上关注我们,并与Excel的最新提示保持联系!

把它钉在Pinterest上

分享这