Excel Data Analysis 简明教程

Key Performance Indicators

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

Key Performance Indicators (KPI) are a set of quantifiable measures that an organization uses to measure its performance over time. KPIs are normally used to evaluate the success of an organization as a whole or department-wise (e.g. sales, finance, etc.). You need to define the KPIs based on the organization objectives and monitor them from time to time to track the progress.

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

There are a number of KPI types to choose from based on your requirements. Examples include −

  1. Income and Expenses

  2. Rate of Return

  3. Average Purchase Value

  4. Customer Lifetime Value

  5. Working Capital

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

Note that KPIs are a form of communication involving the following activities −

  1. Identifying the KPIs based on the organization’s objectives.

  2. Monitoring and reporting the KPIs.

  3. Altering the KPIs as the organization progresses and / or the organization’s goals change.

Identifying the KPIs

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

The first and the most crucial step in KPI analysis is to identify the KPIs that effectively monitor the required trends in the organization. This requires complete understanding of the objectives and requires proper communication channels between the analysts and those who are responsible for fulfilling the objectives.

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

There are a number of KPIs to choose from, but the success in monitoring relies on the right choice of those that are relevant to the objectives. The KPIs differ from organization to organization and from department to department and will be effective only when they lead to improvement in the performance.

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

You can evaluate the relevance of a KPI using the SMART criteria – i.e. the KPI should be *S*pecific, *M*easurable, *A*ttainable, *R*elevant and *T*ime-bound. In other words, the KPI chosen should meet the following criteria −

  1. The KPI reflects your *S*pecific objective.

  2. The KPI enables you to *M*easure progress towards that goal.

  3. The goal for which the KPI is being defined is realistically *A*ttainable.

  4. The goal that the KPI is targeting is *R*elevant to the organization.

  5. You can set a time-frame for achieving the goal so that the KPI reveals how near the goal is as compared to the time that is left.

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

The defined KPIs are to be evaluated from time to time to find their relevance as the time progresses. If required, different KPIs need to be defined and monitored. Only then, your KPI monitoring will be relating to the current organization needs.

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

Based on the analysis needs, you have to choose the relevant KPIs, and examples include the following −

  1. Sales department might use a KPI to measure monthly gross profit against projected gross profit.

  2. Accounting department might measure monthly expenditures against revenue to evaluate costs.

  3. Human resources department might measure quarterly employee turnover.

  4. Business professionals frequently use KPIs that are grouped together in a business scorecard to obtain a quick and accurate historical summary of business success or to identify trends or to identify performance improvement opportunities.

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

The examples used in this chapter are indicative so as to help you in understanding how you can define and monitor KPIs in Excel. The sole discretion of identifying the KPIs lies with you based on the objectives so as to reflect the current scenario as compared to the targets.

KPIs in Excel

  1. You can analyze performance against the set target with PowerPivot. For e.g., a PowerPivot KPI could be used to determine for each year and salesperson how his actual sales compared to his sales target.

  2. You can explore and visualize the same KPI with Power View.

  3. You can also define new KPIs and /or edit them in Power View.

  4. You can produce aesthetic reports with KPIs in Power View.

Defining a KPI in Excel

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

The first step in KPI analysis is to define the identified KPI. This requires defining the three parameters for the KPI as follows −

Base Value

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

A Base Value is defined by a calculated field that resolves to a value. The calculated field represents the current value for the item in that row of the table. E.g., aggregate of sales, profit for a given period, etc.

Target Value/ Goal

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

A Target Value (or Goal) is defined by a calculated field that resolves to a value, or by an absolute value. It is the value against which the current value is evaluated. This could be a fixed number, for example, average number of sick-leave days that is applicable to all the employees, or a calculated field, which results in a different goal for each row, for example, budget of each department in the organization.

Status

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

Status is the indicator of the value. It would be striking if you set it as visual indicator. In Power View in Excel, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator.

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

For example, suppose you want to monitor the Sales targets of the Salespersons in an organization who are selling a product. The objective of the analysis is to identify the best performers who are meeting the target Sales Amount. You can proceed to define the KPI as follows −

  1. Base Value − Current Value of the Sales Amount for each salesperson.

  2. Target Value / Goal − This is fixed for all the salespersons so as to enable comparison between the salespersons. Assume that the Target Sales Amount is 3500. Note that for a different analysis you could vary the target values for the salespersons.

  3. Status − The Status is to be displayed with a graphic to easily determine the status of the Base Value compared to the Target Value.

KPIs in PowerPivot

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

You can define KPIs in PowerPivot as follows −

  1. Start with two tables SalesPerson and Sales. SalesPerson table contains SalesPerson ID and SalesPerson Name.Sales table contains the sales information salesperson-wise and month-wise.

  2. Add the two tables to Data Model.

  3. Create a relationship between the two tables using the field SalesPerson ID.

kpis in powerpivot

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

To set the Base Value, you need a calculated field for Sales Amount.

  1. Add the calculated field in the Sales table for Sales Amount column in the Data Model as follows −

Total Sales:= sum([Sales Amount])

Total Sales:= sum([Sales Amount])

set base value

  1. Click on PivotTable on the Ribbon in the PowerPivot window.

  2. Select New Worksheet in the Create PivotTable dialog box.

pivottable dialog box

  1. Add the field Salesperson to ROWS area in the PivotTable.

  2. Click on the POWERPIVOT tab on the Ribbon.

  3. Click on KPIs in the Calculations group.

  4. Click on New KPI in the dropdown list.

click new kpi

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

Key Performance Indicator (KPI) dialog box appears.

  1. Select Total Sales in the KPI base field (value) box.

  2. Under KPI Status, have the following options − Under Define target value, select Absolute value and type 3500 in the box. Under Define status thresholds, adjust the vertical bars representing the percentages to 40 and 80. Under Select icon style, select the first option.

kpi dialog box

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

Click on the OK button. You can observe the following in the Sales table in PivotTable Fields list −

  1. Total Sales field is a KPI and is depicted by the icon .

  2. The three KPI parameters – Value, Goal and Status appear as fields under Total Sales KPI.

kpi parameter

  1. Select the three KPI parameters – Value, Goal and Status under Total Sales.

  2. The three columns appear in the PowerPivot, with the Status column displaying the icons as per the corresponding value.

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

You can also define the KPI thresholds by values instead of percentages. To modify a defined KPI, proceed as follows −

  1. Click on KPIs in the Calculations group on the Ribbon.

  2. Click on Manage KPIs in the dropdown list.

click manage kpis

管理 KPI 对话框显示。

Manage KPIs dialog box appears.

manage kpis dialog box

  1. Click on the KPI – Total Sales.

  2. Click on the Edit button.

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

Key Performance Indicator (KPI) dialog box appears.

key performance indicator

  1. Under Define status thresholds, adjust the vertical bars to 1500 and 3000.

  2. Retain the rest of the earlier options.

  3. Click on OK.

adjust vertical bars

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

As you can observe, the status icons reflect the changed thresholds.

KPIs in Power View

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

You can create aesthetic reports with KPIs in Power View. You can either use the prior defined KPIs in Data Model or you can add KPIs in Power View.

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

To add or edit a KPI in Power View, proceed as follows −

  1. In the Power View Sheet, click on the PowerPivot tab.

kpis in power view

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

The PowerPivot Ribbon appears, which you had used in the previous section.

  1. Click on KPIs in the Calculation group.

  2. Click on New KPI to add a KPI.

  3. Click on Manage KPIs to edit a KPI.

步骤与上一节相同。

The steps are the same as in the previous section.

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

You can create an aesthetic report of Sales Performance with KPIs in Power View as follows −

  1. Click on DATA tab on the ribbon.

  2. Click on Power View in the Reports group.

Power View 工作表出现。

Power View sheet appears.

  1. Add a Table with the fields – Salesperson, Total Sales and Total Sales Status.

  2. Add a second Table with the fields – Salesperson, Total Sales and Total Sales Goal.

  3. Convert the second Table to 100% Stacked Bar.

  4. Add a third Table with the fields – Salesperson, Region, Total Sales and Total Sales Status.

  5. Convert the third Table to Card. Drag the field Region to Tile By.

  6. Add the Title – Sales Performance.

  7. Change the font.

  8. Increase the Text Size.

  9. Resize Table, 100% Stacked Bar and Card appropriately.

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

Your Sales Performance report is ready −

sales performance

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

As you can observe, in the Power View you could portray the results as follows −

  1. Table with icons for KPI status is similar to the PowerPivot report.

  2. 100% Stacked Bar depicts the percentage achieved with respect to the Goal. You can also notice that it gives a clear comparison of the performance of all.

  3. Card depicts the KPI status of the Salespersons along with the Region they belong to. You can interactively scroll through the Tiles to display results for different Regions that would give scope to assess performance region-wise also.