Advanced Excel Charts 简明教程

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 −

  1. To Gauge the temperature of a person, a thermometer is used.

  2. To Gauge the speed of an automotive, a speedometer is used.

  3. 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 −

gauge chart

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 −

  1. Work completed as against total work.

  2. Sales compared to a target.

  3. Service tickets closed as against total service tickets received.

  4. 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 −

  1. Very simple in nature and cannot portray the context.

  2. Often mislead by omitting key information, which is possible in the current Big Data visualization needs.

  3. They waste space in case multiple charts are to be used. For example, to display information regarding different cars on a single dashboard.

  4. 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 −

  1. Creating a simple Gauge chart with one value − This simple Gauge chart is based on a Pie chart.

  2. 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 −

preparing data for gauge chart

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

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

create gauge chart data

Step 2 − 数据将如下所示 −

Step 2 − The data will look as follows −

gauge chart data

您可以观察到以下内容 −

You can observe the following −

  1. C7 contains the value corresponding to C2.

  2. C5 has 100% to represent half of the Pie chart.

  3. 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.

insert 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.

type nighty

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

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.

click fill

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

This will make the bottom Pie slice invisible.

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.

  1. Change the Fill colors of the Pie slices.

  2. Click on the right Pie slice, select 3-D FORMAT as Top bevel, and choose Angle.

  3. Click on the left Pie slice, select 3-D FORMAT as Top bevel, and choose Divot.

  4. Click on the right Pie slice, select 1% as Point Explosion under SERIES OPTIONS.

  5. Click on the right Pie slice and add Data Label.

  6. Size and Position the Data Label.

  7. Deselect Legend in Chart Elements.

  8. Give the chart Title as Profit % and Position it.

make chart

仪表图已准备妥当。

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.

arrange data

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

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

doughnut chart

您可以观察到以下内容 −

You can observe the following −

  1. The value in the cell C8 is the value you want display in the Gauge chart.

  2. 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.

  3. 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 −

gauge chart

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

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

create 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.

deselect chart title

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.

type twoseventyone

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.

change colors

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

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.

  1. Click on the Doughnut chart.

  2. Click the DESIGN tab on the Ribbon.

  3. Click Select Data in the Data group.

  4. Select Data Source dialog box appears. Click the Add button.

create pointer

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

Step 10 − The Edit Series dialog box appears.

  1. Select the cell containing the name Pointer for Series name.

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

edit series

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

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

  1. Click the DESIGN tab on the Ribbon.

  2. Click Change Chart Type in the Type group.

  3. Change Chart Type dialog box appears. Select Combo under the tab All Charts.

  4. Select the chart types as following − Doughnut for Value series. Pie for Pointer series.

  5. Check the box Secondary Axis for the Pointer series. Click OK.

check box secondary axis

您的图表如下所示。

Your chart looks as shown below.

chart below

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

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

  1. Click on Fill and then select No Fill. Right click on the Pointer Pie slice and select Format Data Series.

  2. Type 270 for Angle of first slice in the SERIES OPTIONS. Your chart looks as shown below.

type twoseventy

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

Step 13 − Right click on the Pointer Pie slice.

  1. Click on Format Data Series.

  2. Click on Fill & Line.

  3. Select Solid Fill for Fill and select the color as black.

  4. Select Solid Line for Border and select the color as black.

select solid line

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.

change pointer value

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

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

gauge chart

仪表图已准备妥当。

Your Gauge chart is ready.