Advanced Excel Charts 简明教程

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 −

  1. Waterfall Chart

  2. Band Chart

  3. Gantt Chart

  4. Thermometer Chart

  5. Gauge Chart

  6. Bullet Chart

  7. Funnel Chart

  8. Waffle Chart

  9. Heat Map

  10. Step Chart

  11. Box and Whisker Chart

  12. Histogram

  13. Pareto Chart

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

waterfall chart

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.

band chart

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.

gantt chart

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

thermometer chart

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.

gauge chart

Bullet Chart

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

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

bullet chart

Funnel Chart

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

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

funnel chart

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.

waffle chart

Heat Map

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

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

heat map

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.

step chart

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.

box and whisker chart

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.

histogram

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.

pareto chart

Organization Chart

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

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

organization chart

虽然其中一些图表包含在 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 −

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

  2. Create the chart − You will learn step by step how you can arrive at the chart, with illustrations.