Advanced Excel Charts 简明教程

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

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

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 −

  1. Monitoring a metric within standard defined bands.

  2. Profit % for each of the regions (represented by Line chart) and bands with defined intervals in the range 0% - 100%.

  3. Performance measurements of an employee or company responses to client’s complaints.

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

consider data

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

Suppose you want to compare this data across three intervals −

  1. Low (<50%).

  2. Medium (50% - 80%).

  3. High (>80%).

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

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

add columns

如您所见，列 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 −

  1. Line with Markers for East series and West series.

  2. Stacked Column for the series - Low, Medium and High.

change chart types

您的图表如下所示。

Your chart looks as shown below.

chart

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.

click column

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

You will get Bands instead of Columns.

change gap width

Step 6 - 让图表更美观 -

Step 6 − Make the chart appealing −

  1. Add Chart Title.

  2. Adjust Vertical Axis range.

  3. Change the colors of the bands to Green-Yellow-Red.

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

band chart

条带图已完成。

Your Band chart is ready.