Advanced Excel Charts 简明教程

Advanced Excel Charts - Quick Guide

Advanced Excel Charts - Introduction

您知道图表是传达结果的高效数据可视化手段。除了 Excel 中提供的图表类型之外，一些广泛使用的应用程序图表也很流行。在本教程中，您将了解到这些高级图表以及如何在 Excel 中创建它们。

You are aware that charts are the efficient data visualization means to convey the results. In addition to the chart types that are available in Excel, some widely used application charts are popular. In this tutorial, you will learn about these advanced charts and how you can create them in Excel.

Types of Advanced Charts

以下是您将在本教程中学习的高级图表 -

Following are the advanced charts that you will learn in this tutorial −

Waterfall Chart
Band Chart
Gantt Chart
Thermometer Chart
Gauge Chart
Bullet Chart
Funnel Chart
Waffle Chart
Heat Map
Step Chart
Box and Whisker Chart
Histogram
Pareto Chart
Organization Chart

Advanced Charts in Brief

我们将简要了解所有高级图表。

We will see all the advanced charts briefly.

Waterfall Chart

瀑布图是一种数据可视化形式，有助于理解按顺序引入的正值或负值的累积效应。

A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values.

Band Chart

带状图是一种线条图，添加了阴影区域以显示已定义数据范围的上边界和下边界。

A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges.

Gantt Chart

甘特图是一种图表，其中使用一系列描绘任务、任务持续时间和任务层次结构的水平线来计划和跟踪项目。

A Gantt chart is a chart in which a series of horizontal lines depicting tasks, task duration and task hierarchy are used planning and tracking projects.

Thermometer Chart

温度计图表跟踪单项任务，例如工作的完成，表示与目标相比的当前状态。它显示已完成的任务的百分比，将目标视为 100%。

A Thermometer chart keeps track of a single task, for e.g. completion of work, representing the current status as compared to a Target. It displays the percentage of the task completed, taking Target as 100%.

Gauge Chart

仪表图，也称为刻度盘图或速度计图，使用指针或指针在刻度盘上显示信息。

Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial.

Bullet Chart

子弹图支持通过线性设计将度量与一个或多个相关度量进行比较。

Bullet charts support the comparison of a measure to one or more related measures with a linear design.

Funnel Chart

漏斗形图表用于可视化数据在过程中从一个阶段传递到另一个阶段时逐步减少的情况。

Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another in a process.

Waffle Chart

华夫饼图是一个 10 × 10 的单元格网格，单元格根据条件格式着色以描绘百分比值，例如 % 工作完成。

Waffle chart is a 10 × 10 cell grid with the cells colored as per conditional formatting to portray a percent value such % work complete.

Heat Map

热力图是表中数据的可视化表示，用于突出显示重要数据点。

A Heat Map is a visual representation of data in a Table to highlight the data points of significance.

Step Chart

阶梯图是一种线条图，它使用垂直线和水平线连接一系列中的数据点，形成阶梯状的进展。

A Step chart is a Line chart that uses vertical and horizontal lines to connect the data points in a series, forming a step-like progression.

Box and Whisker Chart

箱须图，也称为箱线图，通常用于统计分析。在箱须图中，数字数据被分成四分位数，并在第一四分位数和第三四分位数之间画一个方框，并在第二四分位数上画一条附加线以标记中位数。第 1 四分位数和第 3 四分位数之外的最小值和最大值用线表示，称为须线。须线表示上限和下限四分位数之外的可变性，须线之外的任何点都视为异常值。

Box and Whisker charts, also referred to as Box Plots are commonly used in statistical analysis. In a Box and Whisker chart, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. The minimums and maximums outside the first and third quartiles are depicted with lines, which are called whiskers. Whiskers indicate variability outside the upper and lower quartiles, and any point outside the whiskers is considered as an outlier.

Histogram

直方图是数字数据分布的图形表示，广泛用于统计分析中。直方图由矩形表示，矩形的长度对应于变量在连续数字区间中出现的次数。

A Histogram is a graphical representation of the distribution of numerical data and is widely used in Statistical Analysis. A Histogram is represented by rectangles with lengths corresponding to the number of occurrences of a variable in successive numerical intervals.

Pareto Chart

帕累托图广泛用于统计分析中，用于决策制定。它表示帕累托原则，也称为 20/80 法则，该原则指出 80% 的结果是 20% 的原因造成的。

Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called 80/20 Rule, which states that 80% of the results are due to 20% of the causes.

Organization Chart

组织结构图以图形方式表示组织的管理结构。

An Organization chart graphically represents the management structure of an organization.

虽然其中一些图表包含在 Excel 2016 中，但 Excel 2013 和更早版本不包含此类图表。在本教程中，您将学习如何使用 Excel 中的内置图表类型创建这些图表。

Though some of these charts are included in Excel 2016, Excel 2013 and earlier versions do not have them as built-in charts. In this tutorial, you will learn how to create these charts from the built-in chart types in Excel.

Methodology for Creating Advanced Excel Charts

对于上面提到的每种高级图表，您都将学习如何使用以下步骤在 Excel 中创建它们 -

For each of the advanced charts mentioned above, you will learn how to create them in Excel with the following steps −

Prepare data for the chart − Your input data might have to be put in a format that can be used to create the chart at hand. Hence, for each of the charts you will learn how to prepare the data.
Create the chart − You will learn step by step how you can arrive at the chart, with illustrations.

Advanced Excel - Waterfall Chart

瀑布图是小企业和大企业中最流行的可视化工具之一，尤其是在金融领域。瀑布图非常适合显示您如何得出净值（例如净收入），方法是分解积极贡献和消极贡献的累积效应。

Waterfall chart is one of the most popular visualization tools used in small and large businesses, especially in Finance. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions.

What is a Waterfall Chart?

瀑布图是一种数据可视化形式，有助于了解按顺序引入的正值或负值所产生的累积效应。典型的瀑布图用于显示初始值如何随着一系列中间值而增加或减少，最终得出最终值。

A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. A typical Waterfall chart is used to show how an initial value is increased and decreased by a series of intermediate values, leading to a final value.

在瀑布图中，各列使用颜色编码，以便您可以快速区分正数和负数。初始值和最终值列从水平轴开始，而中间值是浮动列。

In a Waterfall chart, the columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns.

由于这种外观，瀑布图也被称为桥图、飞砖图或级联图。

Because of this look, Waterfall charts are also called Bridge charts, Flying Bricks charts or Cascade charts.

Advantages of Waterfall Charts

瀑布图具有以下优点 -

A Waterfall chart has the following advantages −

Analytical purposes − Used especially for understanding or explaining, the gradual transition in the quantitative value of an entity, which is subjected to increment or decrement.
Quantitative analysis − Used in quantitative analysis ranging from inventory analysis to performance analysis.
Tracking contracts − Starting with the number of contracts at hand at the beginning of the year, taking into account − The new contracts that are added The contracts that got cancelled The contracts that are finished, and Finally ending with the number of contracts at hand at the end of the year.
Tracking performance of company over a given number of years.

通常，如果存在一个初始值，并且一段时间内该值发生变化（正值和负值），则瀑布图就可用于描绘初始值、按发生顺序排列的正值和负值变化以及最终值。

In general, if you have an initial value, and changes (positive and negative) occur to that value over a period of time, then Waterfall chart can be used to depict the initial value, positive and negative changes in their order of occurrence and the final value.

Preparation of Data

您需要从给定输入数据中准备数据，以便可以将其描绘成瀑布图。

You need to prepare the data from the given input data, so that it can be portrayed as a Waterfall chart.

考虑以下数据 -

Consider the following data −

按照以下步骤准备瀑布图数据 -

Prepare the data for the Waterfall chart as follows −

Ensure the column Net Cash Flow is to the left of the Months Column (This is because you will not include Net Cash Flow column while creating the chart).
Add two columns − Increase and Decrease for positive and negative cash flows respectively.
Add a column Start − the first column in the chart with the start value in the Net Cash Flow.
Add a column End − the last column in the chart with the end value in the Net Cash Flow.
Add a column Float − that supports the intermediate columns.
Insert formulas to compute the values in these columns as given in the table below.

在浮动列中，在开头和末尾插入一行。放一个任意的值 50000。这只是为了在图表左侧和右侧留一些空间。

In the Float column, insert a row in the beginning and at the end. Place an arbitrary value 50000. This is just to have some space to the left and right sides of the chart.

数据将显示在下表中给出 -

The data will look as given in the following table −

该数据已准备好创建瀑布图。

The data is ready to create a Waterfall chart.

Creating a Waterfall Chart

您可以通过自定义堆积柱形图来创建一个瀑布图，如下所示 -

You can create a Waterfall chart customizing Stacked Column chart as follows −

Step 1 − 选择单元格 C2:H18（即不包括现金净流量列）。

Step 1 − Select the cells C2:H18 (i.e. excluding the Net Cash Flow column).

Step 2 − 插入堆积柱形图。

Step 2 − Insert Stacked Column chart.

Step 3 − 右键单击浮动序列。

Step 3 − Right click on the Float series.

Step 4 − 在下拉列表中单击格式化数据系列。

Step 4 − Click Format Data Series in the dropdown list.

Step 5 − 在格式化数据系列窗格中的序列选项中选择无填充为填充。

Step 5 − Select No fill for FILL in the SERIES OPTIONS in the Format Data Series pane.

Step 6 − 右键单击负序列。

Step 6 − Right click on the Negative series.

Step 7 − 选择填充颜色为红色。

Step 7 − Select Fill color as red.

Step 8 − 右键单击正序列。

Step 8 − Right click on the Positive series.

Step 9 − 选择填充颜色为绿色。

Step 9 − Select Fill color as green.

Step 10 − 右键单击开始序列。

Step 10 − Right click on the Start series.

Step 11 − 选择填充颜色为灰色。

Step 11 − Select Fill color as gray.

Step 12 − 右键单击结束序列。

Step 12 − Right click on the End series.

Step 13 − 选择填充颜色为灰色。

Step 13 − Select Fill color as gray.

Step 14 − 右键单击任意序列。

Step 14 − Right click on any of the series.

Step 15 − 在格式化数据系列窗格中的序列选项下选择 10％的间隙宽度。

Step 15 − Select Gap Width as 10% under SERIES OPTIONS in the Format Data Series pane.

Step 16 − 为图表命名。

Step 16 − Give a name to the chart.

瀑布图已完成。

Your Waterfall chart is ready.

Advanced Excel - Band Chart

假设你有一些跨时间段的数据需要用图形表示，并限定每个数据点在一个定义的区间内。例如，你可能需要展示不同区域的产品顾客调查结果。分段图适合此目的。

Suppose you have data across a time period to represent graphically, confiding each data point to a defined interval. For example, you might have to present customer survey results of a product from different regions. Band chart is suitable for this purpose.

What is Band Chart?

分段图是一种直线图，添加了阴影区域来显示定义数据范围的上限和下限。阴影区域就是分段。

A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges. The shaded areas are the Bands.

分段图也称为范围图、高低线图或走廊图。

Band chart is also referred to as Range chart, High-Low Line chart or Corridor chart.

Advantages of Band Charts

分段图用于以下情况下：

Band chart is used in the following scenarios −

Monitoring a metric within standard defined bands.
Profit % for each of the regions (represented by Line chart) and bands with defined intervals in the range 0% - 100%.
Performance measurements of an employee or company responses to client’s complaints.
Monitoring Service Tickets- Responded service tickets as line and the throughput time as bands.

Preparation of Data

您需要准备可用于从给定输入数据创建条带图的数据。

You need to prepare the data that can be used to create a Band chart from the given input data.

Step 1 - 考虑以下数据，该数据来自针对两个地区（东部和西部）进行的消费者调查，跨度为 4 月到 3 月的财政年度。

Step 1 − Consider the following data that you have from the customer survey for two regions – East and West across the financial year April - March.

假设要按三个间隔比较这些数据 -

Suppose you want to compare this data across three intervals −

Low (<50%).
Medium (50% - 80%).
High (>80%).

Step 2 - 在上述表中添加三列，如下所示。

Step 2 − Add three columns to the above table as shown below.

如您所见，列 Low 中的值为 50%，表示条带 0% - 50%，而列 Medium 中的值为 30%，表示超出条带 Low 的 Medium 的带宽。类似地，列 High 中的值为 20%，表示超出 Low 的 High 的带宽。

As you can observe, the values in the column Low are 50%, denoting the band 0% - 50% and the values in the column Medium are 30%, denoting the bandwidth of Medium above the band Low. Similarly the values in the column High are 20%, denoting the band width of High above the band Low.

使用此数据创建条带图。

Use this data to create a Band chart.

Creating a Band Chart

按照以下步骤创建条带图 -

Follow the steps given below to create a Band chart −

Step 1 - 选择上述表中的数据。

Step 1 − Select the data in the above table.

Step 2 - 插入组合图。

Step 2 − Insert a Combo chart.

Step 3 - 单击更改图表类型。按如下方式更改数据序列的图表类型 -

Step 3 − Click on Change Chart Type. Change the chart types for the data series as follows −

Line with Markers for East series and West series.
Stacked Column for the series - Low, Medium and High.

您的图表如下所示。

Your chart looks as shown below.

Step 4 - 单击其中一个列。

Step 4 − Click one of the Columns.

Step 5 - 在格式数据系列窗格中将间隙宽度更改为 0%。

Step 5 − Change Gap Width to 0% in the Format Data Series pane.

您将获得条带而不是柱状图。

You will get Bands instead of Columns.

Step 6 - 让图表更美观 -

Step 6 − Make the chart appealing −

Add Chart Title.
Adjust Vertical Axis range.
Change the colors of the bands to Green-Yellow-Red.
Add Data Labels to bands.

结果是一个条带图，其边界由条带描绘。调查结果在各条带中呈现。人们可以从图表中快速且清晰地看出调查结果是否令人满意或是否需要引起注意。

The result is a Band chart with defined boundaries depicted by bands. The survey results are represented across the bands. One can quickly and clearly make out from the chart whether the survey results are satisfactory or they need attention.

条带图已完成。

Your Band chart is ready.

Advanced Excel - Gantt Chart

甘特图广泛用于项目规划和跟踪。甘特图形象地展示了一个日程，有助于规划、协调和跟踪项目中的特定任务。有一些软件应用程序提供甘特图作为一种规划工作和跟踪相同任务的方法，例如 Microsoft Project。但是，你也可以在 Excel 中轻松创建甘特图。

Gantt charts are widely in use for project planning and tracking. A Gantt chart provides a graphical illustration of a schedule that helps to plan, coordinate, and track specific tasks in a project. There are software applications that provide Gantt chart as a means of planning work and tracking the same such as Microsoft Project. However, you can create a Gantt chart easily in Excel also.

What is a Gantt Chart?

甘特图是其中包含一系列水平线的图表，这些水平线显示了在特定时间段内完成的工作量与计划在那段时间内完成的工作量之间的关系。水平线描述了任务、任务持续时间和任务层次结构。

A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time with relation to the amount of work planned for those periods. The horizontal lines depict tasks, task duration and task hierarchy.

亨利·L·甘特，是一位美国工程师和社会科学家，于1917年开发了甘特图作为生产控制工具。

Henry L. Gantt, an American engineer and social scientist, developed gantt chart as a production control tool in 1917.

在 Excel 中，你可以使用柱形任务代表图表的堆积柱形图类型自定义一个甘特图。Excel 甘特图通常使用天作为水平轴上的时间单位。

In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type with the Bars representing tasks. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis.

Advantages of Gantt Charts

甘特图在项目管理中经常用于管理项目时间表。

Gantt chart is frequently used in project management to manage project schedule.

It provides visual timeline for starting and finishing specific tasks.
It accommodates multiple tasks and timelines into a single chart.
It is an easy way to understand visualization that shows the amount of work done, the remaining work, and schedule slippages, if any at any point of time.
If the Gantt chart is shared at a common place, it limits the number of status meetings.
Gantt chart promotes on-time deliveries, as the timeline is visible to everyone who is involved in the work.
It promotes collaboration and team spirit with project completion on-time as a common goal.
It provides a realistic view of the project progress and eliminates project end surprises.

Preparation of Data

按照以下方式将你的数据排列在一个表中 -

Arrange your data in a table in the following way −

Create three columns – Task, Start and Duration.
In the Task column, give the names of the Tasks in the project.
In the Start column, for each Task, place the number of days from the Start Date of the project.
In the Duration column, for each Task, place the duration of the Task in days.

Note − 当任务处于层次关系中时，任何任务的开始 - Taskg 开始于上一个任务 + 任务的持续时间。也就是说，一个任务 Taskh 的开始是上一个任务 Taskg 的结束，如果它们处于层次关系中，这意味着 Taskh 依赖于 Taskg。这被称为任务依存关系。

Note − When the Tasks are in a hierarchy, Start of any Task – Taskg is Start of previous Task + it’s Duration. That is, Start of a Task Taskh is the End of the previous Task, Taskg if they are in a hierarchy, meaning that Taskh is dependent on Taskg. This is referred to as Task Dependency.

以下是数据 −

Following is the data −

Creating a Gantt Chart

Step 1 − 选择数据。

Step 1 − Select the data.

Step 2 − 插入堆积条形图。

Step 2 − Insert a Stacked Bar chart.

Step 3 − 右键单击表示起点序列的条形图。

Step 3 − Right click on a bar representing Start series.

Step 4 − 单击“填充”图标。从下拉列表中选择“无填充”。

Step 4 − Click the Fill icon. Select No Fill from the dropdown list.

Step 5 − 右键单击纵轴（类别轴）。

Step 5 − Right click on the Vertical Axis (Categories Axis).

Step 6 − 从下拉列表中选择“格式化轴”。

Step 6 − Select Format Axis from the dropdown list.

Step 7 − 在“AXIS OPTIONS”选项卡上的“Format Axis”(格式化轴) 窗格中，选中方框: 相反顺序的类别。

Step 7 − On the AXIS OPTIONS tab, in the Format Axis pane, check the box - Categories in reverse order.

您将看到纵轴值相反。此外，水平轴移动到图表顶部。

You will see that the Vertical Axis values are reversed. Moreover, the Horizontal Axis shifts to the top of the chart.

Step 8 − 通过一些格式化使图表更具吸引力。

Step 8 − Make the chart appealing with some formatting.

In Chart Elements, deselect the following − Legend.Gridlines.
Format the Horizontal Axis as follows − Adjust the range.Major Tick Marks at 5 day intervals.Minor Tick Marks at 1 day intervals.
Format Data Series to make the Bars look impressive.
Give a Chart Title.

您的甘特图已准备就绪。

Your Gantt chart is ready.

Advanced Excel - Thermometer Chart

温度计图是明确定义的度量实际值的显示，例如任务状态，与目标值相比。这是您将在下一章节学习到的仪表盘图的线性版本。

Thermometer chart is a visualization of the actual value of well-defined measure, for example, task status as compared to a target value. This is a linear version of Gauge chart that you will learn in the next chapter.

您能够通过一个简单的上升温度计图跟踪一段时间针对目标的进展。

You can track your progress against the target over a period of time with a simple rising Thermometer chart.

What is a Thermometer Chart?

温度计图追踪单个任务，例如工作进度，表示与目标相比的当前状态。它显示任务完成百分比，取目标为 100%。

A Thermometer chart keeps track of a single task, for example, completion of work, representing the current status as compared to the target. It displays the percentage of the task completed, taking target as 100%.

温度计图如下所示。

A Thermometer chart looks as shown below.

Advantages of Thermometer Charts

温度计图可用于追踪任何实际值，与目标值相比较完成百分比。它适用于单个值并且是很好的图表，可以纳入到控制台中，以便对达成的百分比、针对目标销售目标的性能百分比、利润百分比、工作完成百分比、预算使用百分比等进行快速视觉影响。

Thermometer chart can be used to track any actual value as compared to the target value as percentage completed. It works with a single value and is an appealing chart that can be included in dashboards for a quick visual impact on % achieved, % performance against the target sales target, % profit, % work completion, % budget utilized, etc.

如果有多个值用于追踪针对目标的实际值，则您可以使用您将在后续章节学习到的子弹图。

If you have multiple values to track the actuals against the targets, you can use Bullet chart that you will learn in a later chapter.

Preparation of Data

以下列方式准备数据：

Prepare the data in the following way −

Calculate the Actual as a percentage of the actual value as compared to the target value.
Target should always be 100%.
Place your data in a table as given below.

Creating a Thermometer Chart

以下是创建温度计图的步骤：

Following are the steps to create a Thermometer chart −

Step 1 − 选择数据。

Step 1 − Select the data.

Step 2 - 插入一个簇状柱形图。

Step 2 − Insert a Clustered Column chart.

正如您所看到的，右侧的柱状图是目标。

As you can see, the right Column is Target.

Step 3 - 在图表中单击一栏。

Step 3 − Click on a Column in the chart.

Step 4 - 单击功能区上的“设计”选项卡。

Step 4 − Click the DESIGN tab on the Ribbon.

Step 5 - 单击“切换行/栏”按钮。

Step 5 − Click the Switch Row/ Column button.

Step 6 - 在目标栏上右键单击。

Step 6 − Right click on the Target Column.

Step 7 - 从下拉列表中选择“格式数据系列”。

Step 7 − Select Format Data Series from the dropdown list.

Step 8 - 在“格式数据系列”窗格中的“系列选项”下，单击“次要坐标轴”。

Step 8 − Click on Secondary Axis under SERIES OPTIONS in the Format Data Series pane.

可以看到，主坐标轴和次坐标轴的范围不同。

As you can see, the Primary Axis and the Secondary Axis have different ranges.

Step 9 - 在主坐标轴上右键单击。从下拉列表中选择“格式化坐标轴”。

Step 9 − Right click on the Primary Axis. Select Format Axis from the dropdown list.

Step 10 - 在“格式化坐标轴”窗格中，“坐标轴选项”下的“界限”中，键入以下内容：

Step 10 − Type the following in Bounds under AXIS OPTIONS in the Format Axis pane −

0 for Minimum.
1 for Maximum.

为次坐标轴重复上述步骤，将“界限”更改为 0 和 1。

Repeat the steps given above for the Secondary Axis to change the Bounds to 0 and 1.

主坐标轴和次坐标轴都将设置为 0% - 100%。

Both the Primary Axis and Secondary Axis will be set to 0% - 100%.

可以看到，目标栏隐藏了实际栏。

As you can observe, the Target Column hides the Actual Column.

Step 11 - 在可见栏（即目标）上右键单击。

Step 11 − Right click on the visible Column, i.e. Target.

Step 12 - 从下拉列表中选择“格式数据系列”。

Step 12 − Select Format Data Series from the dropdown list.

在“格式数据系列”窗格中，选择以下内容：

In the Format Data Series pane, select the following −

No fill under the FILL option.
Solid line under the BORDER option.
Blue under the Color option.

Step 13 - 在“图表元素”中，取消选择以下内容：

Step 13 − In Chart Elements, deselect the following −

Axis → Primary Horizontal.
Axis → Secondary Vertical.
Gridlines.
Chart Title.

Step 14 - 右键单击“主垂直轴”。

Step 14 − Right click on the Primary Vertical Axis.

Step 15 - 从下拉列表中选择“设置格式轴”。

Step 15 − Select Format Axis from the dropdown list.

Step 16 - 在“设置格式轴”窗格的“轴选项”下，单击“刻度线”。

Step 16 − Click TICK MARKS under the AXIS OPTIONS in the Format Axis pane.

Step 17 - 为主要类型选择内部选项。

Step 17 − Select the option Inside for Major type.

Step 18 - 右键单击图表区。

Step 18 − Right click on the Chart Area.

Step 19 - 从下拉列表中选择“设置图表区格式”。

Step 19 − Select Format Plot Area from the dropdown list.

Step 20 - 在“设置图表区格式”窗格中，单击“填充和线条”。选择以下内容：

Step 20 − Click Fill & Line in the Format Plot Area pane. Select the following −

No fill under the FILL option.
No line under the BORDER option.

Step 21 - 调整图表区域大小，以获取用于图表体温计形状。

Step 21 − Resize the Chart Area to get the Thermometer shape for the chart.

获得了体温计图表，其中显示实际值与目标值。

You got your Thermometer chart, with the Actual Value as against Target Value being shown.

Step 22 - 可以通过一些设置格式来使此体温计图表更具有吸引力。

Step 22 − You can make this Thermometer chart more appealing with some formatting.

Insert a Rectangle shape superimposing the blue rectangular part in the chart.
In the Format Shape options, select the following − Gradient fill for FILL.Linear for Type.1800 for Angle.
Set the Gradient stops at 0%, 50% and 100%.
For the Gradient stops at 0% and 100%, choose the color black.
For the Gradient stop at 50%, choose the color white.

Insert an oval shape at the bottom.
Format the oval shape with the same options as of rectangle.
The result will be as shown below −

你的美学温度计图表已准备就绪。它在仪表盘上或作为演示文稿的一部分中效果很好。

Your aesthetic Thermometer chart is ready. This will look good on a dashboard or as a part of a presentation.

Advanced Excel - Gauge Chart

仪表是测量某物数量或大小的设备，例如燃料/雨量/温度表。

A Gauge is a device for measuring the amount or size of something, for example, fuel/rain/temperature gauge.

仪表的使用场景多种多样 -

There are various scenarios where a Gauge is utilized −

To Gauge the temperature of a person, a thermometer is used.
To Gauge the speed of an automotive, a speedometer is used.
To Gauge the performance of a student, a mark sheet is used.

仪表图表开始用于将绩效与设定目标进行可视化对比。仪表图表基于汽车速度表的概念。对于执行人员而言，这些图表已成为首选，他们能够一目了然地了解值是否在可接受值（绿色）范围内或不可接受值（红色）范围内。

Gauge charts came into usage to visualize the performance as against a set goal. The Gauge charts are based on the concept of speedometer of the automobiles. These have become the most preferred charts by the executives, to know at a glance whether values are falling within an acceptable value (green) or the outside acceptable value (red).

What is a Gauge Chart?

仪表图表（也称为刻度盘图表或速度表图表）使用指针或刻度盘来显示信息，作为刻度盘上的读数。仪表图表显示最小值、最大值以及当前值，显示您距最大值的距离。或者，您可以在最小值和最大值之间设置两个或三个范围，并可视化当前值在哪范围内。

Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. A Gauge Chart shows the minimum, the maximum and the current value depicting how far from the maximum you are. Alternatively, you can have two or three ranges between the minimum and maximum values and visualize in which range the current value is falling.

仪表图表如下所示 -

A Gauge chart looks as shown below −

Advantages of Gauge Charts

仪表图表可用于显示相对于一到三个数据范围的值。它们通常用于可视化以下内容 -

Gauge charts can be used to display a value relative to one to three data ranges. They are commonly used to visualize the following −

Work completed as against total work.
Sales compared to a target.
Service tickets closed as against total service tickets received.
Profit compared to the set goal.

Disadvantages of Gauge Charts

尽管大多数执行人员仍然更喜欢仪表图表，但它们确实存在一些缺点。它们 -

Though the Gauge charts are still the preferred ones by most of the executives, there are certain drawbacks with them. They are −

Very simple in nature and cannot portray the context.
Often mislead by omitting key information, which is possible in the current Big Data visualization needs.
They waste space in case multiple charts are to be used. For example, to display information regarding different cars on a single dashboard.
They are not color-blind friendly.

出于这些原因，由 Stephen Few 引入的子弹图变得非常突出。数据分析师发现子弹图是数据分析的手段。

For these reasons Bullet charts, introduced by Stephen Few are becoming prominent. The data analysts find Bullet charts to be the means for data analysis.

Creating a Gauge Chart

您可以通过两种方式创建仪表图 −

You can create Gauge charts in two ways −

Creating a simple Gauge chart with one value − This simple Gauge chart is based on a Pie chart.
Creating a Gauge chart with more number of Ranges − This Gauge chart is based on the combination of a Doughnut chart and a Pie chart.

Simple Gauge Chart with One Value

我们将学习如何准备数据并使用单值创建简单的仪表图。

We will learn how to prepare the data and create a simple Gauge chart with single value.

Preparation of Data

考虑以下数据 -

Consider the following data −

Step 1 − 如下所示创建仪表图数据。

Step 1 − Create data for Gauge chart as shown below.

Step 2 − 数据将如下所示 −

Step 2 − The data will look as follows −

您可以观察到以下内容 −

You can observe the following −

C7 contains the value corresponding to C2.
C5 has 100% to represent half of the Pie chart.
C6 has a value to make C6 and C7 to be 100% that makes second half of the Pie chart.

Creating a Simple Gauge Chart

以下是使用一个值创建简单仪表图的步骤 −

Following are the steps to create a simple Gauge chart with one value −

Step 1 − 选择数据 – C5:C7。

Step 1 − Select the data – C5:C7.

Step 2 − 插入饼图。

Step 2 − Insert a Pie chart.

Step 3 − 右键单击图表。

Step 3 − Right click on the chart.

Step 4 − 从下拉列表中选择“数据系列格式”。

Step 4 − Select Format Data Series from the dropdown list.

Step 5 - 单击“序列选项”。

Step 5 − Click SERIES OPTIONS.

Step 6 - 在框中键入90 - 第一个切片的角度。

Step 6 − Type 90 in the box – Angle of first slice.

可以观察到，饼形图的上半部分是你将转换为仪表图的部分。

As you can observe, the upper half of the Pie chart is what you will convert to a Gauge chart.

Step 7 - 右键单击底部饼形图切片。

Step 7 − Right click on the bottom Pie slice.

Step 8 - 单击“填充”。选择无填充。

Step 8 − Click on Fill. Select No Fill.

这将使底部饼形图切片不可见。

This will make the bottom Pie slice invisible.

可以看到，右侧的饼形切片表示利润百分比。

You can see that the Pie slice on the right represents the Profit %.

Step 9 - 按照如下方式制作出有吸引力的图表。

Step 9 − Make the chart appealing as follows.

Change the Fill colors of the Pie slices.
Click on the right Pie slice, select 3-D FORMAT as Top bevel, and choose Angle.
Click on the left Pie slice, select 3-D FORMAT as Top bevel, and choose Divot.
Click on the right Pie slice, select 1% as Point Explosion under SERIES OPTIONS.
Click on the right Pie slice and add Data Label.
Size and Position the Data Label.
Deselect Legend in Chart Elements.
Give the chart Title as Profit % and Position it.

仪表图已准备妥当。

Your Gauge chart is ready.

Gauge Chart with Multiple Ranges

现在让我们看看如何制作具有更多范围的仪表图。

Now let us see how to make a gauge chart with more ranges.

Preparation of Data

按照下面给出的说明排列数值数据。

Arrange the data for values as given below.

此数据将用于甜甜圈图。按照下面给出的说明排列指针数据。

This data will be used for Doughnut chart. Arrange the data for Pointer as given below.

您可以观察到以下内容 −

You can observe the following −

The value in the cell C8 is the value you want display in the Gauge chart.
The value in the cell C9 is the Pointer size. You can take it as 5 for brevity in formatting and later change to 1, to make it a thin pointer.
The value in the cell C10 is calculated as 200 – (C8+C9). This is to complete the Pie chart.

Creating Gauge Chart with Multiple Ranges

可以使用甜甜圈图创建仪表图，该甜甜圈图显示对应于不同值的各个区域，以及表示指针的饼图。这样的仪表图如下所示 −

You can create the Gauge chart with a Doughnut chart showing different regions corresponding to different Values and a Pie chart denoting the pointer. Such a Gauge chart looks as follows −

Step 1 − 选择值数据并创建甜甜圈图。

Step 1 − Select the values data and create a Doughnut chart.

Step 2 − 双击甜甜圈图的一半（在上述图表中显示为黄色）。

Step 2 − Double click on the half portion of the Doughnut chart (shown in yellow color in the above chart).

Step 3 − 右键单击并在填充类别下选择无填充。

Step 3 − Right click and under the Fill category, select No Fill.

Step 4 − 从图表元素中取消选中图表标题和图例。

Step 4 − Deselect Chart Title and Legend from Chart Elements.

Step 5 − 右键单击图表并选择“设置数据系列”。

Step 5 − Right click on the chart and select Format Data Series.

Step 6 − 在“设置数据系列”窗格中的“系列选项”中，在框中输入 271 - 第一个切片的角度。

Step 6 − Type 271 in the box – Angle of first slice in the SERIES OPTIONS in the Format Data Series pane.

Step 7 − 在“设置数据系列”窗格中的“系列选项”中，将甜甜圈孔大小更改为 50%。

Step 7 − Change the Doughnut Hole Size to 50% in the SERIES OPTIONS in the Format Data Series pane.

Step 8 − 更改颜色以使图表具有吸引力。

Step 8 − Change the colors to make the chart appealing.

正如您所看到的，仪表图在值方面是完整的。下一步是使用指针或指针显示状态。

As you can observe, the Gauge chart is complete in terms of values. The next step is to have a pointer or needle to show the status.

Step 9 − 如下所示使用饼图创建指针。

Step 9 − Create the pointer with a Pie chart as follows.

Click on the Doughnut chart.
Click the DESIGN tab on the Ribbon.
Click Select Data in the Data group.
Select Data Source dialog box appears. Click the Add button.

Step 10 − 将显示“编辑系列”对话框。

Step 10 − The Edit Series dialog box appears.

Select the cell containing the name Pointer for Series name.
Select the cells containing data for Value, Pointer and End, i.e. C8:C10 for Series values. Click OK.

Step 11 - 在“选择数据源”对话框中单击“确定”。

Step 11 − Click OK in the Select Data Source dialog box.

Click the DESIGN tab on the Ribbon.
Click Change Chart Type in the Type group.
Change Chart Type dialog box appears. Select Combo under the tab All Charts.
Select the chart types as following − Doughnut for Value series. Pie for Pointer series.
Check the box Secondary Axis for the Pointer series. Click OK.

您的图表如下所示。

Your chart looks as shown below.

Step 12 - 右键单击两个较大的饼图切片。

Step 12 − Right click on each of the two bigger Pie slices.

Click on Fill and then select No Fill. Right click on the Pointer Pie slice and select Format Data Series.
Type 270 for Angle of first slice in the SERIES OPTIONS. Your chart looks as shown below.

Step 13 - 右键单击指针饼图切片。

Step 13 − Right click on the Pointer Pie slice.

Click on Format Data Series.
Click on Fill & Line.
Select Solid Fill for Fill and select the color as black.
Select Solid Line for Border and select the color as black.

Step 14 - 在数据中将指针值从 5 更改为 1，以使指针饼图切片成为一条细线。

Step 14 − Change the Pointer value from 5 to 1 in the data to make the Pointer Pie slice a thin line.

Step 15 - 添加显示完成百分比的数据标签。

Step 15 − Add a Data Label that depicts % complete.

仪表图已准备妥当。

Your Gauge chart is ready.

Advanced Excel - Bullet Chart

子弹图的出现是为了克服仪表图的缺点。我们可以将它们称为线性仪表图。子弹图是由斯蒂芬·菲尤引入的。子弹图用于轻松比较类别并节省空间。子弹图的格式是灵活的。

Bullet charts came into existence to overcome the drawbacks of Gauge charts. We can refer to them as Liner Gauge charts. Bullet charts were introduced by Stephen Few. A Bullet chart is used to compare categories easily and saves on space. The format of the Bullet chart is flexible.

What is a Bullet Chart?

根据斯蒂芬·菲尤的说法，子弹图支持将一个度量与一个或多个相关度量（例如，目标或过去某个时间点的相同度量，例如一年前）进行比较，并将度量与声明其质量状态（例如，好、满意和差）的定义定量范围联系起来。其线性设计不仅使其占用的空间小，而且比仪表图更支持高效阅读。

According to Stephen Few, Bullet charts support the comparison of a measure to one or more related measures (for example, a target or the same measure at some point in the past, such as a year ago) and relate the measure to defined quantitative ranges that declare its qualitative state (for example, good, satisfactory and poor). Its linear design not only gives it a small footprint, but also supports more efficient reading than the Gauge charts.

请考虑以下给出的示例 -

Consider an example given below −

在子弹图中，您将看到以下组件 −

In a Bullet chart, you will have the following components −

The qualitative bands representing the qualitative states −

Band

Qualitative Value

<30%

Poor

30% - 60%

Fair

60% - 80%

Good

> 80%

Excellent

Target Value, say 80%.
Actual Value, say 70%.

根据以上的值，子弹图如下所示。

With the above values, the Bullet chart looks as shown below.

虽然我们在上图中使用了颜色，但 Stephen Few 建议仅使用灰色阴影，以照顾色盲人群。

Though we used colors in the above chart, Stephen Few suggests the usage of only Gray shades in the interest of color-blind people.

Advantages of Bullet Charts

子弹图具有以下用途和优点 −

Bullet charts have the following uses and advantages −

Bullet Charts are widely used by data analysts and dashboard vendors.
Bullet charts can be used to compare the performance of a metric. For example, if you want to compare the sales of two years or to compare the total sales to a target, you can use bullet charts.
You can use Bullet chart to track the number of defects in Low, Medium and High categories.
You can visualize the Revenue flow across the Fiscal year.
You can visualize the expenses across the Fiscal year.
You can track Profit%.
You can visualize customer satisfaction and can be used to display KPIs also.

Preparation of Data

如下所示排列数据。

Arrange the data as given below.

正如您所观察到的，定性值列于“绩效”列中。波段由“值”列表示。

As you can observe, the qualitative values are given in the column – Performance. The Bands are represented by the column – Value.

Creating a Bullet Chart

以下是如何创建子弹图的步骤 −

Following are the steps to create a Bullet chart −

Step 1 − 选择数据并插入一个堆积柱形图。

Step 1 − Select the data and insert a Stacked Column chart.

Step 2 − 单击图表。

Step 2 − Click on the chart.

Step 3 - 单击功能区上的设计选项卡。

Step 3 − Click the DESIGN tab on the Ribbon.

Step 4 − 单击“数据”组中的“切换行/列”按钮。

Step 4 − Click Switch Row/ Column button in the Data group.

Step 5 − 更改图表类型。

Step 5 − Change the chart type.

Click Change Chart Type.
Click the Combo icon.
Change the chart type for Target to Stacked Line with Markers.
Check the box – Secondary Axis for Target and click OK.

Step 6 - 如您所见，主垂直坐标轴和次垂直坐标轴的范围不同。按照下列步骤使其相等。

Step 6 − As you can see, the Primary and the Secondary Vertical Axis have different ranges. Make them equal as follows.

Right click on Primary Vertical Axis and select Format Axis.
Click on the AXIS OPTIONS tab in the Format Axis pane.
In AXIS OPIONS, under Bounds, type the following − 0.0 for Minimum1.0 for Maximum
Repeat the above steps for Secondary Vertical Axis.

Step 7 - 取消选择图表元素中的次垂直坐标轴。

Step 7 − Deselect Secondary Vertical Axis in the Chart Elements.

Step 8 - 设计图表

Step 8 − Design the chart

Click on the chart.
Click the DESIGN tab on the Ribbon.
Click Change Chart Type.
Check the Secondary Axis box for the Value series.

Step 9 - 右键单击“值”的列（上述图表中的蓝色部分）。

Step 9 − Right click on the column for Value (blue color in the above chart).

Step 10 - 选择“设置资料系列格式”。

Step 10 − Select Format Data Series.

Step 11 - 在“设置资料系列格式”窗格中，“系列选项”下，将间隙宽度更改为 500%。

Step 11 − Change Gap Width to 500% under SERIES OPTIONS in Format Data Series pane.

Step 12 - 取消选择图表元素中的次垂直坐标轴。

Step 12 − Deselect Secondary Vertical Axis in the Chart Elements.

图表将呈现如下所示 -

The chart will look as follows −

Step 13 - 按下列方式设计图表 -

Step 13 − Design the chart as follows −

Click on the chart.
Click Chart Styles at the right corner of the chart.
Click the COLOR tab.
Select Color 17.

Step 14 - 按照下列方式微调图表。

Step 14 − Fine tune the chart as follows.

Right click on the Value column.
Change the Fill color to dark green.
Click on the Target.
Change the Fill and Line color of Marker to orange.
Increase the size of the Marker.

Step 15 - 优化图表设计。

Step 15 − Fine-tune the chart design.

Resize the chart.
Select Legend in Chart Elements.
Deselect Primary Horizontal Axis in Chart Elements.
Deselect Gridlines in Chart Elements.
Give a Chart Title.

极值图已完成。

Your Bullet chart is ready.

您可以将图表颜色更改为灰色渐变色，使其对色盲人群友好。

You can change the color of the chart to gray gradient scale to make it colorblind friendly.

Bullet Chart in Reverse Contexts

假设要在极值图中显示发现的缺陷数。在这种情况下，缺陷越少意味着质量越高。您可以按以下方式定义缺陷类别 −

Suppose you want to display the number of defects found in a Bullet chart. In this case, lesser defects mean greater quality. You can define defect categories as follows −

Step 1 - 然后，您可以为缺陷数量定义一个极限，并通过一个值来表示发现的缺陷数量。将值和极限添加到上面的表格。

Step 1 − You can then define a Limit for number of defects and represent the number of defects found by a Value. Add Value and Limit to the above table.

Step 2 - 选择数据。

Step 2 − Select the data.

Step 3 - 如前一节所述创建极值图。

Step 3 − Create a Bullet chart as you have learnt in the previous section.

如您所见，为了正确解读上下文，已经更改了范围。

As you can see, the ranges are changed to correctly interpret the context.

Advanced Excel - Funnel Chart

漏斗图用于可视化在数据从一个阶段传递到另一个阶段时渐进的减少。这些阶段中的每个阶段的数据均表示为 100%（整体）的不同部分。与饼图类似，漏斗图也不使用任何坐标轴。

Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another. Data in each of these phases is represented as different portions of 100% (the whole). Like the Pie chart, the Funnel chart does not use any axes either.

例如，在销售渠道中，将有如下所示的阶段。

For example, in a sales pipeline, there will be stages as shown below.

潜在客户 → 合格潜在客户 → 提议 → 谈判 → 最终销售。

Prospects → Qualified Prospects → Proposals → Negotiations → Final Sales.

通常，这些值将逐渐降低。许多潜在客户被识别出来，但其中一部分被验证，甚至更少的一部分获得了提案资格。来谈判的数字更少，最终只有为数不多的交易获胜。这将使这些条形图类似于漏斗。

Typically, the values decrease gradually. Many prospects are identified, but a part of them are validated and even lesser qualify for Proposals. A still lesser number come for negotiations and in the end, there is only a handful of deals that are won. This will make the bars resemble a funnel.

What is a Funnel Chart?

漏斗图显示了一个从初始状态开始、以最终状态结束的过程，在该过程中可以明显看出流失发生在哪些阶段以及流失的规模如何。如果该图表还与研究数据相结合，即对在销售或订单履行过程的每个步骤中丢失了多少项目的量化测量，那么漏斗图将说明流程中最大的瓶颈在哪里。

The Funnel chart shows a process that starts at the initial state and ends with a final state, where it is noticeable in what stages the fall out happens and by what magnitude. If the chart is also combined with research data, meaning quantified measurements of just how many items are lost at each step of the sales or order fulfillment process, then the Funnel chart illustrates where the biggest bottlenecks are in the process.

与真正的漏斗不同，并不是所有从顶部倒入的东西都会流向底部。该名称仅指图表形状，其目的是为了说明。

Unlike a real funnel, not everything that is poured in at the top flows through to the bottom. The name only refers to the shape of the chart, the purpose of which is illustrative.

漏斗图的另一种变体是以 100%（整体）的不同部分表示每个阶段的数据，以显示变化沿着漏斗发生的速率。

Another variant of Funnel chart is where the data in each of these phases is represented as different portions of 100% (the whole), to show at what rate the changes occur along the Funnel.

与饼图类似，漏斗图也不使用任何坐标轴。

Like the Pie chart, the Funnel chart does not use any axes either.

Advantages of Funnel Charts

漏斗图可用于各种场景，包括以下场景 −

Funnel chart can be used in various scenarios, including the following −

To allow executives to see how effective the sales team is in turning a sales lead into a closed deal.
A Funnel chart can be used to display Web site visitor trends. It can display visitor page hits to the home page at the top, and the other areas, for e.g. the web site downloads or the people interested in buying the product will be proportionally smaller.
Order fulfillment funnel chart with the initiated orders on top and down to the bottom the orders delivered to satisfied customers. It shows how many there are still in the process and the percentage cancelled and returned.
Another use of Funnel chart is to display sales by each salesperson.
Funnel chart can also be used to evaluate Recruitment process.
Funnel chart can also be used to analyze the order fulfillment process.

Preparation of Data

将数据值放在表格中。

Place the data values in a table.

Step 1 − 如下所示在表格中插入一列。

Step 1 − Insert a column in the table as shown below.

你会得到以下数据。你将使用此表格创建漏斗图。

You will get the following data. You will use this table to create the Funnel chart.

Creating a Funnel Chart

以下是如何创建漏斗图的步骤：

Following are the steps to create the Funnel chart −

Step 1 - 选择数据，插入堆叠柱状图。

Step 1 − Select the data and insert a Stacked Bar chart.

Step 2 - 右键单击移位柱状条（上图中的蓝色），然后将填充色更改为不填充。

Step 2 − Right click on the Shift Bars (blue color in the above chart) and change Fill color to No Fill.

该图表如下所示。

The chart looks as shown below.

Step 3 - 如下所示设计图表。

Step 3 − Design the chart as follows.

Right click on the Vertical Axis.
Select Format Axis from the dropdown list.
Click AXIS OPTIONS in the Format Axis pane.
Check the box – Categories in reverse order.

Step 4 - 如下所示微调图表。

Step 4 − Fine tune the chart as follows.

Deselect Legend in Chart Elements.
Change the Chart Title.
Right click on the orange Bars.
Select Format Data Series.
Change the Gap Width to 0% in the SERIES OPTIONS.

Step 5 - 选择图表元素中的数据标签。

Step 5 − Select Data Labels in Chart Elements.

销售渠道漏斗图已完成。

Your Sales Pipeline Funnel chart is ready.

Formatting a Funnel Chart

Step 6 - 如下所示，使图表更具吸引力：

Step 6 − Make the chart more appealing as follows

Click on the Chart Elements.
Deselect Horizontal Axis.
Deselect Gridlines.
Right click on the Bars.
Select Format Data Series from the dropdown list.
Click on Fill & Line under SERIES OPTIONS in the Format Data Series pane.
Click on Gradient fill under the Fill option.
Set Gradient stops.

Step 7 − 在边框下点击实线。选择黑色。

Step 7 − Click on Solid line under BORDER. Select Color as black.

你的格式化漏斗图已经完成。

Your formatted Funnel chart is ready.

Aesthetic Funnel Chart

现在你已经理解了漏斗图的基础知识，可以继续创建以下实际上看起来像漏斗的美观漏斗图−

Now that you understood the fundamentals of Funnel chart, you can proceed to create an aesthetic Funnel chart that actually looks like a Funnel as follows −

Step 1 − 从原始数据表开始。

Step 1 − Start with the original table of data.

Step 2 − 选择数据并插入 3-D 堆积柱形图。

Step 2 − Select the data and insert a 3-D Stacked Column chart.

Step 3 - 如下所示设计图表。

Step 3 − Design the chart as follows.

Right click on the Columns.
Select Format Data Series.
Click on SERIES OPTIONS in the Format Data Series pane.
Select Full Cone under Column shape.

Step 4 - 如下所示微调图表。

Step 4 − Fine tune the chart as follows.

Click the Design tab on the Ribbon.
Click Switch Row / Column.
Right click on the Vertical Axis and select Format Axis from the dropdown list.
Click AXIS OPTIONS in the Format Axis pane.
Check the box – Values in reverse order.

Step 5 − 取消选择所有图表元素

Step 5 − Deselect all the Chart Elements

Step 6 − 在长条形上右击，然后在下拉列表中选择“格式数据系列”。

Step 6 − Right click on the Bars and select Format Data Series from the dropdown list.

Step 7 − 在“系列选项”窗格中点击“系列选项”，并在“系列选项”下为“间隙深度”键入 50%。

Step 7 − Click on SERIES OPTIONS in the Format Data Series pane and type 50% for Gap Depth under SERIES OPTIONS.

Step 8 − 使用以下详细信息对图表进行格式化。

Step 8 − Format your chart with details as follows.

Select Data Labels in Chart Elements.
Place them and format them.
You can choose Call Out option for the Data Label of the bottom part.
Give a Chart Title.

漏斗形图表已就绪。

Your Funnel chart is ready.

Advanced Excel - Waffle Chart

如果您希望将工作进度显示为完成百分比、实际完成目标等，饼状图可以增加您数据可视化的美感。它快速直观地显示了您想要表达的内容。

Waffle chart adds beauty to your data visualization, if you want to display work progress as percentage of completion, goal achieved vs Target, etc. It gives a quick visual cue of what you want to portray.

饼状图也被称为方形饼图或矩阵图。

Waffle chart is also known as Square Pie chart or Matrix chart.

What is a Waffle Chart?

饼状图是一个 10 × 10 单元格网格，单元格根据条件格式着色。网格表示 1% - 100% 范围内的值，并且单元格将以应用于它们包含的 % 值的条件格式突出显示。例如，如果工作的完成百分比为 85%，则通过将所有包含值 ⇐ 85% 的单元格格式化为特定颜色（如绿色）来表示。

Waffle chart is a 10 × 10 cell grid with the cells colored as per conditional formatting. The grid represents values in the range 1% - 100% and the cells will be highlighted with the conditional formatting applied to the % values they contain. For example, if the percentage of completion of work is 85%, it is portrayed by formatting all the cells that contain values ⇐ 85% with a specific color, say green.

饼状图如下图所示。

Waffle chart looks as shown below.

Advantages of Waffle Chart

华夫饼图具有以下优点：

Waffle chart has the following advantages −

It is visually interesting.
It is very readable.
It is discoverable.
It does not distort the data.
It provides visual communication beyond simple data visualization.

Uses of Waffle Chart

华夫饼图用于和为 100% 的完全平坦数据。将变量的百分比加亮显示，通过突出显示的单元格数量给出描述。它可用于各种用途，包括以下用途：

The Waffle chart is used for completely flat data that adds up to 100%. The percentage of a variable is highlighted to give the depiction by the number of cells that are highlighted. It can be used for various purposes, including the following −

To display the percentage of work that is complete.
To display the percentage of progress that is made.
To depict the expenses incurred as against the budget.
To display Profit %.
To portray the actual value achieved as against the set target, say in sales.
To visualize the company progress as against the goals that are set.
To display the pass percentage in an exam in a college / city/ state.

Creating a Waffle Chart Grid

对于华夫饼图，您需要先创建一个 10 × 10 平方单元格网格，以便网格本身为正方形。

For the Waffle Chart, you need to first create the 10 × 10 Grid of square cells such that the Grid itself will be a square.

Step 1 − 通过调整单元格宽度在 Excel 表上创建一个 10 × 10 方形网格。

Step 1 − Create a 10 × 10 square grid on an Excel sheet by adjusting the cell widths.

Step 2 − 使用 % 值填充单元格，从左下角单元格的 1% 开始，在右上角单元格以 100% 结束。

Step 2 − Fill the cells with % values, starting with 1% in the left-bottom cell and ending with 100% in the right-top cell.

Step 3 − 减小字体大小，以便所有值可见，但不要更改网格的形状。

Step 3 − Decrease the font size such that all the values are visible but do not change the shape of the grid.

这是您将用于华夫饼图的网格。

This is the grid that you will use for the Waffle chart.

Creating a Waffle Chart

假设您具有以下数据：

Suppose you have the following data −

Step 1 − 通过对您创建的网格应用条件格式，创建一个华夫饼图，其中显示东区利润率，如下所示：

Step 1 − Create a Waffle chart that displays the Profit% for the Region East by applying Conditional Formatting to the Grid you have created as follows −

Select the Grid.
Click Conditional Formatting on the Ribbon.
Select New Rule from the drop down list.
Define the Rule to format values ⇐ 85 % (give the cell reference of the Profit %) with fill color and font color as dark green.

Step 2 – 定义另一个规则，用浅绿色填充和字体颜色来设置高于 85%（给出利润百分比的单元格引用）的值格式。

Step 2 − Define another rule to format values > 85 % (give the cell reference of the Profit %) with fill color and font color as light green.

Step 3 – 给图表标题，引用单元格 B3。

Step 3 − Give the Chart Title by giving reference to the cell B3.

如你所见，同时为填充和字体选择相同的颜色使你能够不显示百分比值。

As you can see, choosing the same color for both Fill and Font enable you not to display the %values.

Step 4 – 给图表添加如下标签。

Step 4 − Give a Label to the chart as follows.

Insert a Text box in the chart.
Give the reference to the cell C3 in the Text box.

Step 5 – 将单元格边框着色为白色。

Step 5 − Color the cell borders white.

你的区域东部的华夫饼图表已经就绪。

Your Waffle chart for the Region East is ready.

为如下区域（即北方、南方和西方）创建华夫饼图表：

Create Waffle charts for the Regions, i.e. North, South and West as follows −

Create the Grids for North, South and West as given in the previous section.
For each Grid, apply conditional formatting as given above based on the corresponding Profit % value.

你还可以通过为条件化设置选择不同的颜色，明确地为不同区域创建华夫饼图表。

You can also make Waffle charts for different regions distinctly, by choosing a variation in the colors for Conditional Formatting.

如你所见，右边的华夫饼图表的颜色选择不同于左边的华夫饼图表。

As you can see, the colors chosen for the Waffle charts on the right are varying from the colors chosen for the Waffle charts on the left.

Advanced Excel Charts - Heat Map

热图通常用于引用于二维阵列中区域的彩色区分，每种颜色都与每个区域共享的不同特征相关。

Heat Map is normally used to refer to the colored distinction of areas in a two dimensional array, with each color associated with a different characteristic shared by each area.

在 Excel 中，热图可以基于单元格包含的值应用于一系列单元格，方法是使用单元格颜色和/或字体颜色。Excel 条件格式适用于此目的。

In Excel, Heat Map can be applied to a range of cells based on the values that they contain by using cell colors and/or font colors. Excel Conditional Formatting comes handy for this purpose.

What is a Heat Map?

热图是在表格中可视化表示数据，以突出显着的 data points。例如，如果你在过去一年中按月份统计了产品销售数据的，则可以预测哪些月份的产品销售高或低。

A Heat Map is a visual representation of data in a table to highlight the data points of significance. For example, if you have month wise data on sale of products over the last one year, you can project in which months a product has high or low sales.

热图如下所示。

A Heat Map looks as shown below.

Advantages of Heat Maps

热图可用于以不同的颜色直观显示不同范围的数据。当你拥有大型数据集并且想要快速可视化数据中的某些特征时，这非常有用。

Heat Map can be used to visually display the different ranges of data with distinct colors. This is very useful when you have large data sets and you want to quickly visualize certain traits in the data.

使用热图旨在 −

Heat maps are used to −

Highlight the top few and the bottom few of a range of values.
Portray a trend in the values by using color shades.
Identify blank cells – say in an answer sheet or a questionnaire.
Highlight the quality ranges of the products.
Highlight the numbers in supply chain.
Highlight negative values.
Highlight zero values.
Highlight outliers defined by thresholds.

Preparation of Data

将数据安排在表格中。

Arrange the data in a table.

正如您所看到的，这些数据是每个产品逐月从 4 月到 3 月的财政年度数据。您可以创建一个热图来快速识别在哪些月份的销售额较高或较低。

As you can see, the data is for a fiscal year, April – March, month-wise for each product. You can create a Heat Map to quickly identify during what months the sales were high or low.

Creating a Heat Map

以下是创建热图的步骤 −

Following are the steps to create a Heat Map −

Step 1 − 选择数据。

Step 1 − Select the data.

Step 2 − 在功能区上单击条件格式。单击管理规则并添加规则，如下所示。

Step 2 − Click Conditional Formatting on the Ribbon. Click Manage Rules and add rules as shown below.

前五个值用绿色（填充）着色，后五个值用红色（填充）着色。

The top five values are colored with green (fill) and the bottom five values are colored with red (fill).

Creating Heat Map without Displaying Values

有时，查看者可能只需要信息，而数字可能并不重要。在这种情况下，您可以进行一些格式化，如下所示 −

At times, the viewers might be just be interested in the information and the numbers might not be necessary. In such a case, you can do a bit of formatting as follows −

Step 1 − 选择数据并将字体颜色选择为白色。

Step 1 − Select the data and select the font color as white.

正如您所看到的，数字不可见。接下来，您需要突出显示前五个和后五个值，而不显示数字。

As you can see, the numbers are not visible. Next, you need to highlight the top five and bottom five values without displaying the numbers.

Step 2 − 选择数据（当然，是不可见的）。

Step 2 − Select the data (which is not visible, of course).

Step 3 − 应用条件格式，以便前五个值用绿色（填充和字体）着色，而后五个值用红色（填充和字体）着色。

Step 3 − Apply Conditional Formatting such that the top five values are colored with green (both fill and font) and the bottom five values are colored with red (both fill and font).

Step 4 − 单击应用按钮。

Step 4 − Click the Apply button.

这种做法可以快速直观地显示全年及所有产品的高销量和低销量。由于您为填充和字体选用了相同的颜色，因此无法查看值。

This gives a quick visualization of high and low sales across the year and across the products. As you have chosen the same color for both fill and font, the values are not visible.

Advanced Excel - Step Chart

如果您必须以不规则时间间隔显示数据，并且在变更之间保持恒定，那么步长图将很有用。例如，步长图可用于显示商品价格变更、税率变更、利率变更等。

Step chart is useful if you have to display the data that changes at irregular intervals and remains constant between the changes. For example, Step chart can be used to show the price changes of commodities, changes in tax rates, changes in interest rates, etc.

What is a Step Chart?

步长图是一种不使用最短距离连接两个数据点的折线图。相反，它使用垂直和水平线条以系列形式连接数据点，形成阶梯状进行。步长图的垂直部分表示数据及其大小的变化。步长图的水平部分表示数据的恒定。

A Step chart is a Line chart that does not use the shortest distance to connect two data points. Instead, it uses vertical and horizontal lines to connect the data points in a series forming a step-like progression. The vertical parts of a Step chart denote changes in the data and their magnitude. The horizontal parts of a Step chart denote the constancy of the data.

考虑以下数据 -

Consider the following data −

正如您所看到的，数据变动发生在不规则的时间间隔内。

As you can observe, the data changes are occurring at irregular intervals.

步长图如下所示。

A Step chart looks as shown below.

正如您所看到的，数据变动发生在不规则的时间间隔内。当数据保持恒定时，它会以水平线显示，直到发生变动为止。发生变化时，其大小由垂直线表示。

As you can see, the data changes are occurring at irregular intervals. When the data remains constant, it is depicted by a horizontal Line, till a change occurs. When a change occurs, its magnitude is depicted by a vertical Line.

如果您使用折线图显示相同的数据，它将如下图所示。

If you had displayed the same data with a Line chart, it would be like as shown below.

Differences between Line Charts and Step Charts

您可以识别同一数据中的折线图和步长图之间的以下差异 −

You can identify the following differences between a Line chart and a Step chart for the same data −

The focus of the Line chart is on the trend of the data points and not the exact time of the change. A Step chart shows the exact time of the change in the data along with the trend.

A Line chart cannot depict the magnitude of the change but a Step chart visually depicts the magnitude of the change.

Line chart cannot show the duration for which there is no change in a data value. A Step chart can clearly show the duration for which there is no change in a data value.

A Line chart can sometimes be deceptive in displaying the trend between two data values. For example, Line chart can show a change between two values, while it is not the case. On the other hand, a step chart can clearly display the steadiness when there are no changes.

A Line chart can display a sudden increase/decrease, though the changes occur only on two occasions. A Step chart can display only the two occurred changes and when the changes actually happened.

Advantages of Step Charts

步长图可用于描绘数据在不规则的时间间隔内具有数据变化的固有特性的任何类型的数据。示例包括以下内容 −

Step charts are useful to portray any type of data that has an innate nature of data changes at irregular intervals of time. Examples include the following −

Interest rates vs. time.
Tax rates vs. income.
Electricity charges slabs based on the Units utilized.

Preparation of Data

考虑以下数据 -

Consider the following data −

Step 1 − 选择数据。将数据复制并粘贴到最后一行的下方。

Step 1 − Select the data. Copy and paste the data below the last row of the data.

Step 2 − 复制并粘贴右侧的所有数据。数据如下所示。

Step 2 − Copy and paste the entire data on the right side of the data. The data looks as given below.

Step 3 − 删除下表中所示的第二组数据表中以红色高亮显示的单元格。

Step 3 − Delete the cells highlighted in red that are depicted in the table of second set of data given below.

Step 4 − 在删除时向上移动单元格。第二组数据如下所示。

Step 4 − Shift the cells up while deleting. The second set of data looks as given below.

Step 5 − 复制第二组数据并将它们粘贴到右侧以获得第三组数据。

Step 5 − Copy the second set of data and paste it to the right side of it to get the third set of data.

Step 6 − 选择第三组数据。将其从小到大的值进行排序。

Step 6 − Select the third set of data. Sort it from the smallest to the largest values.

你需要使用这种已排序的第三组数据来创建阶梯图。

You need to use this sorted third set of data to create the Step chart.

Creating a Step Chart

按照以下步骤创建阶梯图 −

Follow the steps given below to create a step chart −

Step 1 − 选择第三组数据并插入折线图。

Step 1 − Select the third set of data and insert a Line chart.

Step 2 − 如下格式化图表-

Step 2 − Format the chart as follows −

Click on the chart.
Click the DESIGN tab on the Ribbon.
Click Select Data in the Data group. The Select Data Source dialog box appears.
Select Series1 under Legend Entries (Series).
Click the Remove button.
Click the Edit button under Horizontal (Category) Axis Labels. Click OK.

“轴标签”对话框将出现。

The Axis Labels dialog box appears.

Step 3 − 在“轴标签范围”下选择单元格 F2:F10，然后单击“确定”。

Step 3 − Select the cells F2:F10 under the Axis labels range and click OK.

Step 4 − 在“选择数据源”对话框中单击“确定”。你的图表将如下所示。

Step 4 − Click OK in the Select Data Source dialog box. Your chart will look as shown below.

Step 5 − 您会看到，水平（类别）轴中缺少一些值（年份）。要插入值，请按照以下步骤操作。

Step 5 − As you can observe, some values (Years) in the Horizontal (Category) Axis are missing. To insert the values, follow the steps given below.

Right click on the Horizontal Axis.
Select Format Axis.
Click AXIS OPTIONS in the Format Axis pane.
Select Date Axis under Axis Type in AXIS OPTIONS.

如你所见，水平（类别）轴现在还包含类别值中缺失的年份。此外，在发生更改之前，该线是水平的。当有更改时，其大小由垂直线的高度表示。

As you can see, the Horizontal (Category) Axis now contains even the missing Years in the Category values. Further, until a change occurs, the line is horizontal. When there is a change, its magnitude is depicted by the height of the vertical line.

Step 6 - 在图表元素中取消选择图表标题和图例。

Step 6 − Deselect the Chart Title and Legend in Chart Elements.

你的步骤图已准备就绪。

Your Step chart is ready.

Advanced Excel - Box and Whisker Chart

盒须图（也称为箱形图）通常用于统计分析。例如，您可以使用盒须图对比实验结果或竞争性考试成绩。

Box and Whisker charts, also referred to as Box Plots are commonly used in statistical analysis. For example, you can use a Box and Whisker chart to compare experimental results or competitive exam results.

What is a Box and Whisker Chart?

在盒须图中，数值数据将按四分位数划分，并在第一四分位数和第三四分位数之间绘制一个矩形，并沿着第二四分位数绘制一条附加的线以标记中位数。第一四分位数和第三四分位数之外的最小值和最大值将以称作“须”的线表示。须表示高于或低于上下四分位数的变化幅度，且须之外的任何点都将被视为异常值。

In a Box and Whisker chart, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. The minimums and maximums outside the first and third quartiles are depicted with lines, which are called whiskers. Whiskers indicate variability outside the upper and lower quartiles, and any point outside the whiskers is considered as an outlier.

盒须图如下所示。

A Box and Whisker chart looks as shown below.

Advantages of Box and Whisker Charts

无论您在哪里，只要需要了解数据分布，就可以使用盒须图。并且数据可以是多样化的，数据可从任何领域获取以进行统计分析。示例包括以下内容-

You can use Box and Whisker chart wherever to understand the distribution of data. And the data can be diverse that is drawn from any field for statistical analysis. Examples include the following −

Survey responses on a particular product or service to understand the user’s preferences.
Examination results to identify which students need more attention in a particular subject.
Question-Answer patterns for a competitive examination to finalize the combination of categories.
Laboratory results to draw conclusions on a new drug that is invented.
Traffic patterns on a particular route to streamline the signals that are enroute. The outliers also help in identifying the reasons for the data to get outcast.

Preparation of Data

假设您获得了以下数据-

Suppose you are given the following data −

根据以上数据创建第二张表，如下所示-

Create a second table from the above table as follows −

Step 1 - 使用 Excel 函数 MIN、QUARTILE 和 MAX 计算 2014、2015 和 2016 年的每个系列的以下内容。

Step 1 − Compute the following for each of the series – 2014, 2015 and 2016 using Excel Functions MIN, QUARTILE and MAX.

Minimum Value.
First Quartile.
Median Value.
Third Quartile.
Maximum Value.

生成的第二张表将如下所示。

The resulting second table will be as given below.

Step 2 - 根据第二张表计算差值的第三张表-

Step 2 − Create a third table from the second table, computing the differences −

Retain the first row – Minimum Value as it is.
In the second row – compute values as First Quartile - Minimum Value.
In the third row – compute values as Median Value - First Quartile.
In the fourth row – compute values as Third Quartile - Median Value.
In the fifth row – compute values as Maximum Value - Third Quartile.

您将得到如以下所示的第三个表格。

You will get the third table as shown below.

您将使用这些数据用于箱线图。

You will use this data for the Box and Whisker chart.

Creating a Box and Whisker Chart

以下是创建箱线图的步骤。

Following are the steps to create a Box and Whisker chart.

Step 1 - 选择在上节中获取的第三个表格中的数据。

Step 1 − Select the data obtained as the third table in the previous section.

Step 2 - 插入一个堆积柱形图。

Step 2 − Insert a Stacked Column chart.

Step 3 - 单击功能区上的设计选项卡。

Step 3 − Click the DESIGN tab on the Ribbon.

Step 4 - 在数据组中单击切换行/列按钮。

Step 4 − Click Switch Row / Column button in the Data group.

您的图表将如下图所示。

Your chart will be as shown below.

Step 5 - 右键单击底部数据系列。单击填充并选择无填充。

Step 5 − Right click on the bottom Data Series. Click Fill and select No Fill.

底部的 Data 系列将变得不可见。

The bottom Data series becomes invisible.

Step 6 - 在图表元素中取消选择图表标题和图例。

Step 6 − Deselect Chart Title and Legend in Chart Elements.

Step 7 - 将水平轴标签更改为 2014、2015 和 2016。

Step 7 − Change the Horizontal Axis Labels to 2014, 2015 and 2016.

Step 8 - 现在，您的盒子已准备就绪。接下来，您需要创建线须。

Step 8 − Now, your Boxes are ready. Next, you have to create the Whiskers.

Right click on the Top Data Series.
Change Fill to No Fill.
Click the DESIGN tab on the Ribbon.
Click Add Chart Element in the Chart layouts group.
Click Error Bars in the dropdown list and select Standard Deviation.

Step 9 − 创建了顶部线须。接下来，按照如下所示格式化线须（错误线）−

Step 9 − You got the top Whiskers. Next, format Whiskers (Error Bars) as follows −

Right click on the Error Bars.
Select Format Error Bars.
Select the following under ERROR BAR OPTIONS in the Format Error Bars pane. Select Minus under Direction. Select No Cap under End Style. Select Percentage under Error Amount and type 100.

Step 10 − 在“格式错误线”窗格中的“错误线选项”下，单击“填充和线条”选项卡。

Step 10 − Click the Fill & Line tab under ERROR BAR OPTIONS in the Format Error Bars pane.

Select Solid line under LINE.
Select the color as dark blue.
Type 1.5 in the Width box.

Step 11 − 为第二根较低的底部系列重复上述步骤。

Step 11 − Repeat the above given steps for the second lower bottom Series.

Step 12 − 接下来，按照如下所示格式化框。

Step 12 − Next, format the boxes as follows.

Right click on one of the Box series.
Click Fill.
Choose color as light blue.
Click Outline.
Choose the color as dark blue.
Click Weight.
Select 1½ pt.

Step 13 − 为其他框系列重复上述步骤。

Step 13 − Repeat the steps given above for the other Box series.

您的箱线图就制作好了。

Your Box and Whisker chart is ready.

Advanced Excel Charts - Histogram

直方图是数值数据分布的图形表示。它广泛应用于统计分析。Karl Pearson 引入了直方图。

A Histogram is a graphical representation of the distribution of numerical data. It is widely used in Statistical Analysis. Karl Pearson introduced histogram.

在 Excel 中，您可以从作为 Excel 附加项提供的分析工具包创建直方图。但是，在这种情况下，当数据更新时，除非再次通过分析工具包修改直方图，否则直方图将不会反映更改后的数据。

In Excel, you can create a Histogram from the Analysis ToolPak that comes as an add-in with Excel. However, in such a case, when the data is updated, Histogram will not reflect the changed data unless it is modified through Analysis ToolPak again.

在本章中，您将学习如何从柱形图创建直方图。在这种情况下，当源数据更新时，图表也会刷新。

In this chapter, you will learn how to create a Histogram from a Column chart. In this case, when the source data is updated the chart also gets refreshed.

What is a Histogram?

直方图由一系列矩形表示，这些矩形的长度与连续数值区间中变量出现次数对应。数值区间称为“bin”，而出现次数称为频率。

A Histogram is represented by rectangles with lengths corresponding to the number of occurrences of a variable in successive numerical intervals. The numerical intervals are called bins and the number of occurrences is called frequency.

Bin 通常被指定为连续、不相交的变量区间。Bin 必须是相邻的，并且具有相等的大小。位于 Bin 上的矩形的高度与 Bin 的频率成比例，它表示该 Bin 中案例的数量。因此，水平轴表示 Bin，而垂直轴表示频率。矩形被着色或阴影化。

The bins are usually specified as consecutive, non-overlapping intervals of the variable. The bins must be adjacent and are of equal size. A rectangle over a bin with height proportional to the frequency of the bin depicts the number of cases in that bin. Thus, the horizontal axis represents the bins whereas the vertical axis represents the frequency. The rectangles are colored or shaded.

直方图将如下图所示。

A Histogram will be as shown below.

Advantages of Histograms

直方图用于检查数据的潜在分布、异常值、偏度等。例如，直方图可在统计分析中用于以下场景：

Histogram is used to inspect the data for its underlying distribution, outliers, skewness, etc. For example, Histogram can be used in statistical analysis in the following scenarios −

A census of a country to obtain the people of various age groups.
A survey focused on the demography of a country to obtain the literacy levels.
A study on the effect of tropical diseases during a season across different regions in a state.

Preparation of Data

考虑以下给定的数据。

Consider the data given below.

根据以上数据创建 Bin 并计算每个 Bin 中的值的数目，如下所示：

Create bins and calculate the number of values in each bin from the above data as shown below −

Bin 中值的数目称为该 Bin 的频率。

The number of values in a bin is referred to as the frequency of that bin.

此表称为“频率表”，我们将使用它来创建直方图。

This table is called a Frequency table and we will use it to create the Histogram.

Creating a Histogram

以下是创建直方图的步骤。

Following are the steps to create a Histogram.

Step 1 - 选择频率表中的数据。

Step 1 − Select the data in the Frequency table.

Step 2 - 插入一个簇状柱形图。

Step 2 − Insert a Clustered Column chart.

Step 3 - 右键单击列，然后从下拉列表中选择“格式化数据系列”。

Step 3 − Right click on the Columns and select Format Data Series from the dropdown list.

Step 4 - 单击“系列选项”，然后在“系列选项”下将间隙宽度更改为 0。

Step 4 − Click SERIES OPTIONS and change the Gap Width to 0 under SERIES OPTIONS.

Step 5 - 如下所示格式化图表。

Step 5 − Format the chart as follows.

Click on Fill & Line.
Click on Solid Line under Border.
Select black for color.
Type 1.5 for Width.

Step 6 - 调整图表大小。

Step 6 − Adjust the size of the chart.

您的直方图已完成。您会注意到，每列的长度都对应于特定 Bin 的频率。

Your Histogram is ready. As you can observe, the length of each column corresponds to the frequency of that particular bin.

Advanced Excel - Pareto Chart

帕累托图被广泛应用于决策的统计分析。它体现了帕累托原则，也被称为“80/20法则”。

Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called the 80/20 Rule.

Pareto Principle (80/20 Rule)

帕累托原则也被称为“80/20法则”。这意味着80%的结果是由20%的原因造成的。例如，80%的缺陷可归因于关键的20%的原因。它也被称为“重要的少数和琐碎的多数”。

Pareto principle, also called the 80/20 Rule means that 80% of the results are due to 20% of the causes. For example, 80% of the defects can be attributed to the key 20% of the causes. It is also termed as vital few and trivial many.

维尔弗雷多・帕累托通过调查和观察发现，大多数国家80%的收入流向20%的人口。

Vilfredo Pareto conducted surveys and observed that 80% of income in most of the countries went to 20% of the population.

Examples of Pareto Principle (80/20 Rule)

帕累托原则或“80/20法则”可应用于多种情境 −

The Pareto principle or the 80/20 Rule can be applied to various scenarios −

80% of customer complaints arise from 20% of your supplies.
80% of schedule delays result from 20% of the key causes.
80% of a company profit can be attributed to 20% of its products.
80% of a company revenues are produced by 20% of the employees.
80% of the system problems are caused by 20% of causes of defects.

What is a Pareto Chart?

帕累托图是柱状图和折线图的组合。帕累托图按频率的降序排列显示“柱”，线则描绘类别的累积总数。

A Pareto chart is a combination of a Column chart and a Line chart. The Pareto chart shows the Columns in descending order of the Frequencies and the Line depicts the cumulative totals of Categories.

帕累托图如下所示 −

A Pareto chart will be as shown below −

Advantages of Pareto Charts

您可以使用帕累托图进行以下分析 −

You can use a Pareto chart for the following −

To analyze data about the frequency of problems in a process.
To identify the significant causes for problems in a process.
To identify the significant areas of defects in a product.
To understand the significant bottlenecks in a process pipeline.
To identify the largest issues being faced by a team or an organization.
To know the top few reasons for employee attrition.
To identify the topmost products that result in high profit.
To decide on the significant improvements that increase the value of a company.

Preparation of Data

考虑以下数据，其中给出了缺陷原因和相应计数。

Consider the following data, where the defect causes and the respective counts are given.

Step 1 −按缺陷计数列从大到小对表格进行排序（从最大到最小）。

Step 1 − Sort the table by the column - Defect Count in descending order (Largest to Smallest).

Step 2 −创建一个累积计数列，如下所示：

Step 2 − Create a column Cumulative Count as given below −

这将导致以下表格：

This would result in the following table −

Step 3 −对缺陷计数列求和。

Step 3 − Sum the column Defect Count.

Step 4 −创建一个累积百分比列，如下所示。

Step 4 − Create a column Cumulative % as given below.

Step 5 −将累积百分比列格式化为百分比。

Step 5 − Format the column Cumulative % as Percentage.

您将使用此表格创建帕累托图。

You will use this table to create a Pareto chart.

Creating a Pareto Chart

通过创建帕累托图，您可以得出缺陷的主要原因。在 Excel 中，您可以将帕累托图创建为柱形图和折线图的组合图。

By creating a Pareto chart, you can conclude what are the key causes for the defects. In Excel, you can create a Pareto chart as a combo chart of Column chart and Line chart.

以下是创建帕累托图的步骤：

Following are the steps to create Pareto chart −

Step 1 −选择表格中的缺陷原因和缺陷计数列。

Step 1 − Select the columns Defect Causes and Defect Count in the table.

Step 2 - 插入一个簇状柱形图。

Step 2 − Insert a Clustered Column chart.

Step 3 −如您所见，表示原因的列以降序排列。按如下方式设置图表格式。

Step 3 − As you can see, the columns representing causes are in descending order. Format the chart as follows.

Right click on the Columns and click on Format Data Series.
Click SERIES OPTIONS in the Format Data Series pane.
Change the Gap Width to 0 under SERIES OPTIONS.
Right click on the Columns and select Outline.
Select a dark color and a Weight to make the border conspicuous.

您的图表将如下图所示。

Your chart will be as shown below.

Step 4 −按如下方式设计图表。

Step 4 − Design the chart as follows.

Click on the chart.
Click the DESIGN tab on the Ribbon.
Click Select Data in the Data group. The Select Data Source dialog box appears.
Click the Add button.

编辑系列对话框出现。

The Edit Series dialog box appears.

Step 5 -在单元格上单击 - 系列名称的累积 %。

Step 5 − Click on the cell – Cumulative % for Series name.

Step 6 -选择累积 % 列中的数据，以获得系列的值。单击确定。

Step 6 − Select the data in Cumulative % column for Series values. Click OK.

Step 7 -在选择数据源对话框中单击确定。您的图表将如下所示。

Step 7 − Click OK in the Select Data Source dialog box. Your chart will be as shown below.

Step 8 -在功能区上单击设计选项卡。

Step 8 − Click the DESIGN tab on the Ribbon.

Step 9 -在类型组中单击更改图表类型。

Step 9 − Click Change Chart Type in the Type group.

Step 10 -更改图表类型对话框出现。

Step 10 − Change Chart Type dialog box appears.

Click the All Charts tab.
Click the Combo button.
Select Clustered Column for Defect Count and Line for Cumulative %.
Check the box – Secondary Axis for Line chart. Click OK.

正如您所见，80% 的缺陷是由两个原因造成的。

As you can observe, 80% of the defects are due to two causes.

Advanced Excel - Organization Chart

你可以使用组织结构图来说明团队或组织内的报告关系。在 Excel 中，你可以使用具有组织结构图布局的 SmartArt 图形。

You can illustrate the reporting relationships in your team or organization using an organization chart. In Excel, you can use a SmartArt graphic that uses an organization chart layout.

What is an Organization Chart?

组织结构图以图形方式表示组织的管理结构，例如部门经理和组织中相应的报告员工。此外，高级经理可以有助手，助手也会在组织结构图中显示。

An Organization chart graphically represents the management structure of an organization, such as department managers and the corresponding reporting employees within the organization. Further, there can be assistants for the top managers and they are also depicted in the Organization chart.

Excel 中的组织结构图如下所示。

An Organization chart in Excel will be as shown below.

Preparation of Data

以下是准备组织结构图数据的步骤：

Following are steps to prepare the data for an Organization chart −

Step 1 - 按如下方式整理有关组织不同角色的信息。

Step 1 − Collate the information about the different roles in the organization as given below.

Step 2 - 识别层级中的报告关系。

Step 2 − Identify the reporting relationships in the hierarchy.

您将使用此信息创建组织结构图。

You will use this information to create the Organization chart.

Creating an Organization Chart

以下是创建组织结构图的步骤：

Following are the steps to create the Organization chart.

Step 1 - 单击功能区上的插入选项卡。

Step 1 − Click the INSERT tab on the Ribbon.

Step 2 - 单击插图组中的 SmartArt 图形图标。

Step 2 − Click the SmartArt Graphic icon in the Illustrations group.

Step 3 - Choose a SmartArt Graphic 对话框出现。

Step 3 − Choose a SmartArt Graphic dialog box appears.

Step 4 - 从左窗格中选择层级结构。

Step 4 − Select Hierarchy from the left pane.

Step 5 - 单击组织结构图。

Step 5 − Click on an Organization Chart.

Step 6 - 组织结构图的预览出现。单击确定。

Step 6 − A preview of the Organization Chart appears. Click OK.

组织结构图模板出现在您的工作表中。

The Organization chart template appears in your worksheet.

正如您所看到的，您可以在左窗格中输入文本，它会立即出现在右侧的图表中。左窗格中带有附加线条的项目符号的框表示它是图表中的“助理”框。左窗格中带有项目符号的框表示它们是图表中悬挂布局的一部分。

As you can observe, you can enter the text in the left pane and it appears immediately on the chart on the right. The box that has a bullet with line attached in the left pane indicates that it is Assistant box in the chart. The boxes with bullets in the left pane indicate they are part of hanging layout in the chart.

Step 7 - 在文本窗格中输入信息。

Step 7 − Enter the information in the Text pane.

Step 8 - 如果有报告关系，则降级。

Step 8 − Demote if there is reporting relationship.

Step 9 - 单击图表外部。您的组织结构图已准备就绪。

Step 9 − Click outside the chart. Your Organization chart is ready.

Formatting the Organization Chart

您可以设置组织结构图的格式以使其具有设计感。按照以下步骤操作：

You can format the Organization chart to give it a designer look. Follow the steps given below −

Click on the chart.
Click the DESIGN tab on the Ribbon.
Select Polished from the SmartArt Styles group.
Change the colors of the boxes.
Change the font color.
Adjust the size of the boxes.
Adjust the widths of the lines.

您的组织结构图已准备就绪。

Your Organization chart is ready.