Excel Data Analysis 简明教程

Key Performance Indicators

关键绩效指标 (KPI) 是一组可量化的衡量标准，组织使用它们来衡量其随着时间的绩效。KPI 通常用于评估整个组织或部门（例如销售、财务等）的成功。你需要根据组织目标定义 KPI，并时常监控它们以跟踪进度。

根据你的要求，有许多类型的 KPI 可供选择。示例包括−

  1. Income and Expenses

  2. Rate of Return

  3. Average Purchase Value

  4. Customer Lifetime Value

  5. Working Capital

请注意，KPI 是一种涉及以下活动的交流形式−

  1. 根据组织目标确定 KPI。

  2. 监控和报告 KPI。

  3. 随着组织发展和/或组织目标的变化而更改 KPI。

Identifying the KPIs

在 KPI 分析中，第一步也是最重要的一步是确定能有效监控组织中所需趋势的 KPI。这需要对目标有完全的了解，并要求分析师与负责实现目标的人员之间有适当的沟通渠道。

有很多 KPI 可供选择，但监控的成功取决于选择与目标相关的那些 KPI。KPI 因组织而异，也因部门而异，并且只有当它们能提升绩效时才有效。

你可以使用 SMART 标准来评估 KPI 的相关性，即 KPI 应该是*明确的 (S)、可衡量的 (M)、可实现的 (A)、相关的 ® 和有时间限制 (T)*。换句话说，所选择的 KPI 应满足以下标准−

  1. KPI 反映了你的*明确*目标。

  2. KPI 使您能够*M*easure 朝着该目标的进展。

  3. KPI 所定义的目标在实际中是可*A*ttainable。

  4. KPI 所针对的目标与组织*R*elevant。

  5. 您可以设置实现目标的时间范围，以便 KPI 显示相对于剩余时间目标的进度。

需要定时评估已定义的 KPI，以随着时间的推移了解其相关性。如果需要，需要定义和监控不同的 KPI。只有这样，您的 KPI 监控才会与当前组织需求相关。

根据分析需要，您必须选择相关的 KPI，以下是一些示例：

  1. 销售部门可以利用 KPI 将月度毛利与预期毛利进行比较。

  2. 会计部门可以衡量月度支出相对于收入来评估成本。

  3. 人力资源部门可以衡量每季度的员工流失率。

  4. 业务专业人士经常将 KPI 组合在业务记分卡中，以快速准确地获取业务成功的历史摘要，或识别趋势或识别绩效改进机会。

本章中使用的示例具有指示性，旨在帮助您了解如何在 Excel 中定义和监控 KPI。根据目标识别 KPI 的唯一决定权在于您，以便与目标相比反映当前情况。

KPIs in Excel

  1. 您可以使用 PowerPivot 分析相对于设置目标的绩效。例如，PowerPivot KPI 可用于确定每年的销售人员实际销售与其销售目标相比如何。

  2. 您可以使用 Power View 探索和可视化相同的 KPI。

  3. 您还可以在 Power View 中定义新的 KPI 和/或编辑它们。

  4. 您可以在 Power View 中生成带有 KPI 的美观报告。

Defining a KPI in Excel

KPI 分析的第一步是定义已识别的 KPI。这需要定义 KPI 的三个参数，如下所示：

Base Value

基本值由计算出的字段定义，该字段解析为一个值。该计算字段表示表中该行中项目的当前值。例如，销售汇总、某一段时间的利润等。

Target Value/ Goal

目标值（或目标）由计算出的字段定义，该字段解析为一个值，或一个绝对值。它是用于评估当前值的数值。这可以是一个固定数字，例如适用于所有员工的平均病假天数，或一个计算字段，即为每一行产生不同的目标，例如，组织中每个部门的预算。

Status

状态是值的一个指标。如果您将其设置为可视化指标，它将会非常引人注目。在 Excel 的 Power View 中，您可以编辑 KPI，选择要使用的指标和触发每个指标的值。

例如，假设您希望监控销售某一产品的组织中销售人员的销售目标。分析目的是识别达到销售目标金额的最佳表现者。您可以继续按如下方式定义 KPI：

  1. Base Value − 每个销售人员的销售金额当前值。

  2. Target Value / Goal − 这是为所有销售员确定的，以便能够对销售员进行比较。假设目标销售额为 3500。注意，对于不同的分析，你可以改变销售员的目标值。

  3. Status − 状态将通过图形来显示，以便轻松地确定与目标值相比的基础值的状况。

KPIs in PowerPivot

你可以在 PowerPivot 中定义 KPI，如下所示 −

  1. 从两个表 SalesPerson 和 Sales 开始。SalesPerson 表中包含 SalesPerson ID 和 SalesPerson Name。Sales 表包含销售信息和销售人员每月情况。

  2. 将两个表添加到数据模型。

  3. 使用字段 SalesPerson ID 在两表之间创建关系。

kpis in powerpivot

要设置基础值，你需要一个销售额计算字段。

  1. 在数据模型中，向销售额列的 sales 表中添加计算字段，如下所示：

Total Sales:= sum([Sales Amount])

set base value

  1. 在 PowerPivot 窗口的 Ribbon 上，单击数据透视表。

  2. 在创建数据透视表对话框中，选择新建工作表。

pivottable dialog box

  1. 将字段 Salesperson 添加到数据透视表的行区域。

  2. 点击 Ribbon 上的 POWERPIVOT 选项卡。

  3. 在计算组中，单击 KPI。

  4. 在下拉列表中，单击新建 KPI。

click new kpi

关键绩效指标 (KPI) 对话框会出现。

  1. 在 KPI 基本字段（值）框中，选择总销售额。

  2. 在 KPI 状态下，有以下选项 − 在定义目标值中，选择绝对值并在框中输入 3500。 在定义状态阈值中，将表示百分比的垂直条调整为 40 和 80。 在选择图标样式中，选择第一个选项。

kpi dialog box

单击确定按钮。你可以在数据透视表字段列表中的 Sales 表中观察到以下内容 −

  1. 总销售额字段是一个 KPI，并通过图标进行了描述。

  2. 这三个 KPI 参数——值、目标和状态显示为总销售 KPI 下的字段。

kpi parameter

  1. 在总销售下选择这三个 KPI 参数——值、目标和状态。

  2. 这三列显示在 PowerPivot 中，状态列根据相应的值显示图标。

此外，你还可以通过值而不是百分比定义 KPI 阈值。要修改已定义的 KPI，请按以下步骤操作：

  1. 单击功能区中计算组中的 KPI。

  2. 单击下拉列表中的管理 KPI。

click manage kpis

管理 KPI 对话框显示。

manage kpis dialog box

  1. 单击 KPI - 总销售。

  2. 单击编辑按钮。

关键绩效指标 (KPI) 对话框会出现。

key performance indicator

  1. 在定义状态阈值下方，调整垂直条至 1500 和 3000。

  2. 保留之前选项中的其他选项。

  3. Click on OK.

adjust vertical bars

你会注意到状态图标反映了更改的阈值。

KPIs in Power View

可以在 Power View 中创建包含 KPI 的美观报告。可以使用以前在数据模型中定义的 KPI，也可以在 Power View 中添加 KPI。

要在 Power View 中添加或编辑 KPI，请执行以下步骤：

  1. 在 Power View 工作表中，单击 PowerPivot 选项卡。

kpis in power view

PowerPivot 功能区将显示，已在上一部分中使用过该功能区。

  1. 单击计算组中的 KPI。

  2. 单击新建 KPI 以添加 KPI。

  3. 单击管理 KPI 以编辑 KPI。

步骤与上一节相同。

您可以按照如下步骤在 Power View 中使用 KPI 创建销售绩效美学报告 −

  1. 单击功能区上的“数据”选项卡。

  2. 单击“报表”组中的“Power View”。

Power View 工作表出现。

  1. 添加包含字段“销售人员”、“总销售额”和“总销售状态”的表。

  2. 添加包含字段“销售人员”、“总销售额”和“总销售目标”的第二个表。

  3. 将第二个表转换为 100% 堆栈条形图。

  4. 添加包含字段“销售人员”、“地区”、“总销售额”和“总销售状态”的第三个表。

  5. 将第三个表转换为卡片。将字段“地区”拖动到“按平铺排列”。

  6. 添加标题“销售绩效”。

  7. Change the font.

  8. Increase the Text Size.

  9. 适当缩放表、100% 堆栈条形图和卡片的大小。

您的销售绩效报告已准备就绪 −

sales performance

正如您所观察到的，在 Power View 中，您可以按如下方式描绘结果 −

  1. 带有 KPI 状态图标的表类似于 PowerPivot 报表。

  2. 100% 堆栈条形图描绘了相对于目标达到的百分比。您还可以注意到它清晰地比较了所有人的绩效。

  3. 卡片描绘了销售人员的 KPI 状态以及他们所属的地区。您可以交互式地滚动平铺以显示不同地区的业绩，这也可以评估区域绩效。