Advanced Excel Charts 简明教程
Advanced Excel Charts - Introduction
您知道图表是传达结果的高效数据可视化手段。除了 Excel 中提供的图表类型之外,一些广泛使用的应用程序图表也很流行。在本教程中,您将了解到这些高级图表以及如何在 Excel 中创建它们。
Types of Advanced Charts
以下是您将在本教程中学习的高级图表 -
-
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 Excel - Waterfall Chart
瀑布图是小企业和大企业中最流行的可视化工具之一,尤其是在金融领域。瀑布图非常适合显示您如何得出净值(例如净收入),方法是分解积极贡献和消极贡献的累积效应。
What is a Waterfall Chart?
瀑布图是一种数据可视化形式,有助于了解按顺序引入的正值或负值所产生的累积效应。典型的瀑布图用于显示初始值如何随着一系列中间值而增加或减少,最终得出最终值。
在瀑布图中,各列使用颜色编码,以便您可以快速区分正数和负数。初始值和最终值列从水平轴开始,而中间值是浮动列。
由于这种外观,瀑布图也被称为桥图、飞砖图或级联图。
Advantages of Waterfall Charts
瀑布图具有以下优点 -
-
Analytical purposes - 特别用于理解或解释实体数量值的逐步过渡,这些值会受到增量或减量因素的影响。
-
Quantitative analysis - 用于从库存分析到业绩分析的定量分析当中。
-
Tracking contracts - 从年初的可合约数量开始,考虑到添入的新合约、取消的合约、完成的合约,最终以年末可合约数量结算。
-
追踪公司在若干年的业绩。
通常,如果存在一个初始值,并且一段时间内该值发生变化(正值和负值),则瀑布图就可用于描绘初始值、按发生顺序排列的正值和负值变化以及最终值。
Preparation of Data
您需要从给定输入数据中准备数据,以便可以将其描绘成瀑布图。
考虑以下数据 -
按照以下步骤准备瀑布图数据 -
-
确保净现金流列位于月份列的左侧(这是因为在创建图表时您不会包含净现金流列)。
-
Add two columns - 对于正现金流和负现金流分别增加和减少。
-
Add a column Start - 图表中首列,包含净现金流中的起始值。
-
Add a column End - 图表中末列,包含净现金流中的最终值。
-
Add a column Float - 支撑中间列。
-
插入公式来计算这些列中的值,如下表所示。
在浮动列中,在开头和末尾插入一行。放一个任意的值 50000。这只是为了在图表左侧和右侧留一些空间。
数据将显示在下表中给出 -
该数据已准备好创建瀑布图。
Creating a Waterfall Chart
您可以通过自定义堆积柱形图来创建一个瀑布图,如下所示 -
Step 1 − 选择单元格 C2:H18(即不包括现金净流量列)。
Step 2 − 插入堆积柱形图。
Step 3 − 右键单击浮动序列。
Step 4 − 在下拉列表中单击格式化数据系列。
Step 5 − 在格式化数据系列窗格中的序列选项中选择无填充为填充。
Step 6 − 右键单击负序列。
Step 7 − 选择填充颜色为红色。
Step 8 − 右键单击正序列。
Step 9 − 选择填充颜色为绿色。
Step 10 − 右键单击开始序列。
Step 11 − 选择填充颜色为灰色。
Step 12 − 右键单击结束序列。
Step 13 − 选择填充颜色为灰色。
Step 14 − 右键单击任意序列。
Step 15 − 在格式化数据系列窗格中的序列选项下选择 10% 的间隙宽度。
Step 16 − 为图表命名。
瀑布图已完成。
Advanced Excel - Band Chart
假设你有一些跨时间段的数据需要用图形表示,并限定每个数据点在一个定义的区间内。例如,你可能需要展示不同区域的产品顾客调查结果。分段图适合此目的。
Advantages of Band Charts
分段图用于以下情况下:
-
在标准定义的条带内监视指标。
-
每个区域的利润百分比(以折线图表示)和范围在 0%-100% 的条带。
-
员工或公司对消费者投诉的绩效测量。
-
监控服务票证 - 响应式服务票证作为折线图,通过时间作为条带。
Preparation of Data
您需要准备可用于从给定输入数据创建条带图的数据。
Step 1 - 考虑以下数据,该数据来自针对两个地区(东部和西部)进行的消费者调查,跨度为 4 月到 3 月的财政年度。
假设要按三个间隔比较这些数据 -
-
Low (<50%).
-
Medium (50% - 80%).
-
High (>80%).
Step 2 - 在上述表中添加三列,如下所示。
如您所见,列 Low 中的值为 50%,表示条带 0% - 50%,而列 Medium 中的值为 30%,表示超出条带 Low 的 Medium 的带宽。类似地,列 High 中的值为 20%,表示超出 Low 的 High 的带宽。
使用此数据创建条带图。
Creating a Band Chart
按照以下步骤创建条带图 -
Step 1 - 选择上述表中的数据。
Step 2 - 插入组合图。
Step 3 - 单击更改图表类型。按如下方式更改数据序列的图表类型 -
-
东部系列和西部系列的带标记的线图。
-
Low、Medium 和 High 系列的堆积柱形图。
您的图表如下所示。
Step 4 - 单击其中一个列。
Step 5 - 在格式数据系列窗格中将间隙宽度更改为 0%。
您将获得条带而不是柱状图。
Step 6 - 让图表更美观 -
-
Add Chart Title.
-
Adjust Vertical Axis range.
-
将条带颜色改为绿-黄-红。
-
向条带添加数据标签。
结果是一个条带图,其边界由条带描绘。调查结果在各条带中呈现。人们可以从图表中快速且清晰地看出调查结果是否令人满意或是否需要引起注意。
条带图已完成。
Advanced Excel - Gantt Chart
甘特图广泛用于项目规划和跟踪。甘特图形象地展示了一个日程,有助于规划、协调和跟踪项目中的特定任务。有一些软件应用程序提供甘特图作为一种规划工作和跟踪相同任务的方法,例如 Microsoft Project。但是,你也可以在 Excel 中轻松创建甘特图。
What is a Gantt Chart?
甘特图是其中包含一系列水平线的图表,这些水平线显示了在特定时间段内完成的工作量与计划在那段时间内完成的工作量之间的关系。水平线描述了任务、任务持续时间和任务层次结构。
亨利·L·甘特,是一位美国工程师和社会科学家,于1917年开发了甘特图作为生产控制工具。
在 Excel 中,你可以使用柱形任务代表图表的堆积柱形图类型自定义一个甘特图。Excel 甘特图通常使用天作为水平轴上的时间单位。
Advantages of Gantt Charts
甘特图在项目管理中经常用于管理项目时间表。
-
它提供了开始和结束特定任务的可视时间线。
-
它将多个任务和时间线容纳到一个图表中。
-
这是一种简单的可视化理解方法,可以显示完成的工作量、剩余工作量以及时间滑移,如果有的话,可以在任何时间。
-
如果甘特图在一个公共场所共享,它将限制状态会议的数量。
-
甘特图促进了按时交付,因为时间线对参与工作的所有人都是可见的。
-
它促进了协作和团队精神,以按时完成项目为共同目标。
-
它提供了项目进度的真实视图,消除了项目结束时的意外情况。
Preparation of Data
按照以下方式将你的数据排列在一个表中 -
-
创建三列 - 任务、开始和持续时间。
-
在“任务”列中,给出项目中任务的名称。
-
在“开始”列中,对于每个任务,放置从项目开始日期开始的天数。
-
在“持续时间”列中,为每项任务放置任务的持续时间(以天为单位)。
Note − 当任务处于层次关系中时,任何任务的开始 - Taskg 开始于上一个任务 + 任务的持续时间。也就是说,一个任务 Taskh 的开始是上一个任务 Taskg 的结束,如果它们处于层次关系中,这意味着 Taskh 依赖于 Taskg。这被称为任务依存关系。
以下是数据 −
Creating a Gantt Chart
Step 1 − 选择数据。
Step 2 − 插入堆积条形图。
Step 3 − 右键单击表示起点序列的条形图。
Step 4 − 单击“填充”图标。从下拉列表中选择“无填充”。
Step 5 − 右键单击纵轴(类别轴)。
Step 6 − 从下拉列表中选择“格式化轴”。
Step 7 − 在“AXIS OPTIONS”选项卡上的“Format Axis”(格式化轴) 窗格中,选中方框: 相反顺序的类别。
您将看到纵轴值相反。此外,水平轴移动到图表顶部。
Step 8 − 通过一些格式化使图表更具吸引力。
-
在图表元素中,取消选择以下项 − 图例。网格线。
-
将水平轴格式化为如下 − 调整范围。5 天间隔的主要刻度线。1 天间隔的次刻度线。
-
对数据序列进行格式化,以使条形图的外观更加令人印象深刻。
-
Give a Chart Title.
您的甘特图已准备就绪。
Advanced Excel - Thermometer Chart
温度计图是明确定义的度量实际值的显示,例如任务状态,与目标值相比。这是您将在下一章节学习到的仪表盘图的线性版本。
您能够通过一个简单的上升温度计图跟踪一段时间针对目标的进展。
Advantages of Thermometer Charts
温度计图可用于追踪任何实际值,与目标值相比较完成百分比。它适用于单个值并且是很好的图表,可以纳入到控制台中,以便对达成的百分比、针对目标销售目标的性能百分比、利润百分比、工作完成百分比、预算使用百分比等进行快速视觉影响。
如果有多个值用于追踪针对目标的实际值,则您可以使用您将在后续章节学习到的子弹图。
Creating a Thermometer Chart
以下是创建温度计图的步骤:
Step 1 − 选择数据。
Step 2 - 插入一个簇状柱形图。
正如您所看到的,右侧的柱状图是目标。
Step 3 - 在图表中单击一栏。
Step 4 - 单击功能区上的“设计”选项卡。
Step 5 - 单击“切换行/栏”按钮。
Step 6 - 在目标栏上右键单击。
Step 7 - 从下拉列表中选择“格式数据系列”。
Step 8 - 在“格式数据系列”窗格中的“系列选项”下,单击“次要坐标轴”。
可以看到,主坐标轴和次坐标轴的范围不同。
Step 9 - 在主坐标轴上右键单击。从下拉列表中选择“格式化坐标轴”。
Step 10 - 在“格式化坐标轴”窗格中,“坐标轴选项”下的“界限”中,键入以下内容:
-
0 for Minimum.
-
1 for Maximum.
为次坐标轴重复上述步骤,将“界限”更改为 0 和 1。
主坐标轴和次坐标轴都将设置为 0% - 100%。
可以看到,目标栏隐藏了实际栏。
Step 11 - 在可见栏(即目标)上右键单击。
Step 12 - 从下拉列表中选择“格式数据系列”。
在“格式数据系列”窗格中,选择以下内容:
-
在“填充”选项下,“无填充”。
-
在“边框”选项下,“实线”。
-
在“颜色”选项下,“蓝色”。
Step 13 - 在“图表元素”中,取消选择以下内容:
-
Axis → Primary Horizontal.
-
Axis → Secondary Vertical.
-
Gridlines.
-
Chart Title.
Step 14 - 右键单击“主垂直轴”。
Step 15 - 从下拉列表中选择“设置格式轴”。
Step 16 - 在“设置格式轴”窗格的“轴选项”下,单击“刻度线”。
Step 17 - 为 主要 类型选择 内部 选项。
Step 18 - 右键单击图表区。
Step 19 - 从下拉列表中选择“设置图表区格式”。
Step 20 - 在“设置图表区格式”窗格中,单击“填充和线条”。选择以下内容:
-
在“填充”选项下,“无填充”。
-
在“边框”选项下,无线条。
Step 21 - 调整图表区域大小,以获取用于图表体温计形状。
获得了体温计图表,其中显示实际值与目标值。
Step 22 - 可以通过一些设置格式来使此体温计图表更具有吸引力。
-
插入一个矩形形状,将其覆盖在图表中的蓝色矩形部分上。
-
在“设置形状格式”选项中,选择以下内容:用于“填充”的“渐变填充”。用于“类型”的“线性”。用于“角度”的“1800”。
-
将渐变停止点设置在 0%、50% 和 100%。
-
对于位于 0% 和 100% 的渐变停止点,选择颜色黑色。
-
对于位于 50% 的渐变停止点,选择颜色白色。
-
在底部插入一个椭圆形。
-
使用与矩形相同的选项设置椭圆形的格式。
-
结果将如下所示:
你的美学温度计图表已准备就绪。它在仪表盘上或作为演示文稿的一部分中效果很好。
Advanced Excel - Gauge Chart
仪表是测量某物数量或大小的设备,例如燃料/雨量/温度表。
仪表的使用场景多种多样 -
-
使用温度计来测量人的体温。
-
使用速度表来测量汽车的速度。
-
使用成绩单来测量学生的成绩。
仪表图表开始用于将绩效与设定目标进行可视化对比。仪表图表基于汽车速度表的概念。对于执行人员而言,这些图表已成为首选,他们能够一目了然地了解值是否在可接受值(绿色)范围内或不可接受值(红色)范围内。
What is a Gauge Chart?
仪表图表(也称为刻度盘图表或速度表图表)使用指针或刻度盘来显示信息,作为刻度盘上的读数。仪表图表显示最小值、最大值以及当前值,显示您距最大值的距离。或者,您可以在最小值和最大值之间设置两个或三个范围,并可视化当前值在哪范围内。
仪表图表如下所示 -
Advantages of Gauge Charts
仪表图表可用于显示相对于一到三个数据范围的值。它们通常用于可视化以下内容 -
-
已完成工作与总工作量的对比。
-
实际销售额与目标销售额的对比。
-
已关闭服务票与收到的服务票总数的对比。
-
利润与设定目标的对比。
Disadvantages of Gauge Charts
尽管大多数执行人员仍然更喜欢仪表图表,但它们确实存在一些缺点。它们 -
-
本质上非常简单,无法描绘背景。
-
常因遗漏关键信息而误导,这在当前大数据可视化需求中是可能的。
-
在使用多张图表时,它们可能会浪费空间。例如,在一个仪表板上显示有关不同汽车的信息。
-
它们对色盲人群不友好。
出于这些原因,由 Stephen Few 引入的子弹图变得非常突出。数据分析师发现子弹图是数据分析的手段。
Creating a Gauge Chart
您可以通过两种方式创建仪表图 −
-
Creating a simple Gauge chart with one value − 这个简单的仪表图基于饼图。
-
Creating a Gauge chart with more number of Ranges − 该仪表图基于甜甜圈图和饼图的组合。
Preparation of Data
考虑以下数据 -
Step 1 − 如下所示创建仪表图数据。
Step 2 − 数据将如下所示 −
您可以观察到以下内容 −
-
C7 包含与 C2 相对应的值。
-
C5 具有 100%,以表示饼图的一半。
-
C6 具有一个值,使 C6 和 C7 为 100%,形成饼图的另一半。
Creating a Simple Gauge Chart
以下是使用一个值创建简单仪表图的步骤 −
Step 1 − 选择数据 – C5:C7。
Step 2 − 插入饼图。
Step 3 − 右键单击图表。
Step 4 − 从下拉列表中选择“数据系列格式”。
Step 5 - 单击“序列选项”。
Step 6 - 在框中键入90 - 第一个切片的角度。
可以观察到,饼形图的上半部分是你将转换为仪表图的部分。
Step 7 - 右键单击底部饼形图切片。
Step 8 - 单击“填充”。选择无填充。
这将使底部饼形图切片不可见。
可以看到,右侧的饼形切片表示利润百分比。
Step 9 - 按照如下方式制作出有吸引力的图表。
-
更改饼形图切片的填充颜色。
-
单击右侧饼形图切片,选择 3D 格式作为顶角,然后选择角度。
-
单击左侧饼形图切片,选择 3D 格式作为顶角,然后选择圆角。
-
单击右侧饼形图切片,在“序列选项”下选择 1% 作为数据点爆炸效果。
-
单击右侧饼形图切片并添加数据标签。
-
调整数据标签的大小和位置。
-
取消选择图表元素中的图例。
-
将图表标题指定为利润百分比并调整其位置。
仪表图已准备妥当。
Preparation of Data
按照下面给出的说明排列数值数据。
此数据将用于甜甜圈图。按照下面给出的说明排列指针数据。
您可以观察到以下内容 −
-
C8 单元格中的值为希望在仪表图中显示的值。
-
C9 单元格中的值为指针大小。为了简化格式设置,可以将其设为 5,之后再将其更改为 1,使其成为一个细指针。
-
C10 单元格中的值计算为 200 - (C8+C9)。这是为了完成饼图。
Creating Gauge Chart with Multiple Ranges
可以使用甜甜圈图创建仪表图,该甜甜圈图显示对应于不同值的各个区域,以及表示指针的饼图。这样的仪表图如下所示 −
Step 1 − 选择值数据并创建甜甜圈图。
Step 2 − 双击甜甜圈图的一半(在上述图表中显示为黄色)。
Step 3 − 右键单击并在填充类别下选择无填充。
Step 4 − 从图表元素中取消选中图表标题和图例。
Step 5 − 右键单击图表并选择“设置数据系列”。
Step 6 − 在“设置数据系列”窗格中的“系列选项”中,在框中输入 271 - 第一个切片的角度。
Step 7 − 在“设置数据系列”窗格中的“系列选项”中,将甜甜圈孔大小更改为 50%。
Step 8 − 更改颜色以使图表具有吸引力。
正如您所看到的,仪表图在值方面是完整的。下一步是使用指针或指针显示状态。
Step 9 − 如下所示使用饼图创建指针。
-
单击甜甜圈图。
-
单击功能区上的设计选项卡。
-
单击数据组中的“选择数据”。
-
将显示“选择数据源”对话框。单击“添加”按钮。
Step 10 − 将显示“编辑系列”对话框。
-
为“系列名称”选择包含名称“指针”的单元格。
-
选择包含“值”、“指针”和“结束”数据值的单元格,即系列值的 C8:C10。单击“确定”。
Step 11 - 在“选择数据源”对话框中单击“确定”。
-
单击功能区上的设计选项卡。
-
在“类型”组中单击“更改图表类型”。
-
将显示“更改图表类型”对话框。在“所有图表”选项卡下选择“组合”。
-
将图表类型选择为以下内容 - 值系列为“环形图”。指针系列为“饼图”。
-
选中指针系列的“次要坐标轴”复选框。单击“确定”。
您的图表如下所示。
Step 12 - 右键单击两个较大的饼图切片。
-
单击“填充”,然后选择“无填充”。右键单击指针饼图切片并选择“设置数据系列格式”。
-
在“系列选项”中为“第一块的角度”键入 270。您的图表如下所示。
Step 13 - 右键单击指针饼图切片。
-
单击“设置数据系列格式”。
-
单击“填充和线条”。
-
对“填充”选择“纯色填充”并选择黑色作为颜色。
-
对“边框”选择“纯色线条”并选择黑色作为颜色。
Step 14 - 在数据中将指针值从 5 更改为 1,以使指针饼图切片成为一条细线。
Step 15 - 添加显示完成百分比的数据标签。
仪表图已准备妥当。
Advanced Excel - Bullet Chart
子弹图的出现是为了克服仪表图的缺点。我们可以将它们称为线性仪表图。子弹图是由斯蒂芬·菲尤引入的。子弹图用于轻松比较类别并节省空间。子弹图的格式是灵活的。
What is a Bullet Chart?
根据斯蒂芬·菲尤的说法,子弹图支持将一个度量与一个或多个相关度量(例如,目标或过去某个时间点的相同度量,例如一年前)进行比较,并将度量与声明其质量状态(例如,好、满意和差)的定义定量范围联系起来。其线性设计不仅使其占用的空间小,而且比仪表图更支持高效阅读。
请考虑以下给出的示例 -
在子弹图中,您将看到以下组件 −
-
表示定性状态的定性波段 −
Band |
Qualitative Value |
<30% |
Poor |
30% - 60% |
Fair |
60% - 80% |
Good |
> 80% |
Excellent |
-
Target Value, say 80%.
-
Actual Value, say 70%.
根据以上的值,子弹图如下所示。
虽然我们在上图中使用了颜色,但 Stephen Few 建议仅使用灰色阴影,以照顾色盲人群。
Advantages of Bullet Charts
子弹图具有以下用途和优点 −
-
子弹图被数据分析师和仪表盘供应商广泛使用。
-
子弹图可用于比较指标的性能。例如,如果您想比较两年的销售额或将总销售额与目标进行比较,您可以使用子弹图。
-
您可以使用子弹图来跟踪低、中和高类别中的缺陷数量。
-
您可以可视化整个财政年度的收入流。
-
您可以可视化整个财政年度的支出。
-
You can track Profit%.
-
您可以可视化客户满意度,也可以用来显示 KPI。
Creating a Bullet Chart
以下是如何创建子弹图的步骤 −
Step 1 − 选择数据并插入一个堆积柱形图。
Step 2 − 单击图表。
Step 3 - 单击功能区上的设计选项卡。
Step 4 − 单击“数据”组中的“切换行/列”按钮。
Step 5 − 更改图表类型。
-
Click Change Chart Type.
-
Click the Combo icon.
-
将目标的图表类型更改为带标记的堆积折线图。
-
勾选“次要坐标轴为目标”,然后点击确定。
Step 6 - 如您所见,主垂直坐标轴和次垂直坐标轴的范围不同。按照下列步骤使其相等。
-
右键单击主垂直坐标轴,然后选择“格式化坐标轴”。
-
在“格式化坐标轴”窗格中,单击“坐标轴选项”选项卡。
-
在“坐标轴选项”中,在“界限”下,键入以下内容 - 最小值为 0.0,最大值为 1.0
-
对次垂直坐标轴重复上述步骤。
Step 7 - 取消选择图表元素中的次垂直坐标轴。
Step 8 - 设计图表
-
Click on the chart.
-
单击功能区上的设计选项卡。
-
Click Change Chart Type.
-
选中“值”系列的次要坐标轴框。
Step 9 - 右键单击“值”的列(上述图表中的蓝色部分)。
Step 10 - 选择“设置资料系列格式”。
Step 11 - 在“设置资料系列格式”窗格中,“系列选项”下,将间隙宽度更改为 500%。
Step 12 - 取消选择图表元素中的次垂直坐标轴。
图表将呈现如下所示 -
Step 13 - 按下列方式设计图表 -
-
Click on the chart.
-
单击图表右上角的“图表样式”。
-
Click the COLOR tab.
-
Select Color 17.
Step 14 - 按照下列方式微调图表。
-
右键单击“值”列。
-
将填充颜色更改为深绿色。
-
Click on the Target.
-
将标记的填充和线条颜色更改为橙色。
-
增加标记的大小。
Step 15 - 优化图表设计。
-
Resize the chart.
-
在图表元素中选择图例。
-
在图表元素中取消选择主水平轴。
-
在图表元素中取消选择网格线。
-
Give a Chart Title.
极值图已完成。
您可以将图表颜色更改为灰色渐变色,使其对色盲人群友好。
Advanced Excel - Funnel Chart
漏斗图用于可视化在数据从一个阶段传递到另一个阶段时渐进的减少。这些阶段中的每个阶段的数据均表示为 100%(整体)的不同部分。与饼图类似,漏斗图也不使用任何坐标轴。
例如,在销售渠道中,将有如下所示的阶段。
潜在客户 → 合格潜在客户 → 提议 → 谈判 → 最终销售。
通常,这些值将逐渐降低。许多潜在客户被识别出来,但其中一部分被验证,甚至更少的一部分获得了提案资格。来谈判的数字更少,最终只有为数不多的交易获胜。这将使这些条形图类似于漏斗。
What is a Funnel Chart?
漏斗图显示了一个从初始状态开始、以最终状态结束的过程,在该过程中可以明显看出流失发生在哪些阶段以及流失的规模如何。如果该图表还与研究数据相结合,即对在销售或订单履行过程的每个步骤中丢失了多少项目的量化测量,那么漏斗图将说明流程中最大的瓶颈在哪里。
与真正的漏斗不同,并不是所有从顶部倒入的东西都会流向底部。该名称仅指图表形状,其目的是为了说明。
漏斗图的另一种变体是以 100%(整体)的不同部分表示每个阶段的数据,以显示变化沿着漏斗发生的速率。
与饼图类似,漏斗图也不使用任何坐标轴。
Advantages of Funnel Charts
漏斗图可用于各种场景,包括以下场景 −
-
允许高管了解销售团队在将销售线索转化为已完成交易方面的有效性。
-
漏斗图可用于显示网站访问者趋势。它可以在顶部显示访问者进入主页的页面点击次数,以及其他区域,例如网站下载或有兴趣购买产品的用户,将依比例越来越小。
-
订单履行漏斗图,顶部显示已发起的订单,底部一直到已交付给满意客户的订单。它显示了过程中还有多少订单以及已取消和退回的订单的百分比。
-
漏斗图的另一种用途是按销售人员显示销售情况。
-
漏斗图还可用于评估招聘流程。
-
漏斗图还可用于分析订单履行流程。
Creating a Funnel Chart
以下是如何创建漏斗图的步骤:
Step 1 - 选择数据,插入堆叠柱状图。
Step 2 - 右键单击移位柱状条(上图中的蓝色),然后将填充色更改为不填充。
该图表如下所示。
Step 3 - 如下所示设计图表。
-
右键单击纵轴。
-
从下拉列表中选择格式化轴。
-
在“格式化轴”窗格中单击“轴选项”。
-
选中此选项 - 按照相反的顺序排列类别。
Step 4 - 如下所示微调图表。
-
取消选择图表元素中的图例。
-
Change the Chart Title.
-
右键单击橙色柱状条。
-
Select Format Data Series.
-
在系列选项中将间距宽度更改为 0%。
Step 5 - 选择图表元素中的数据标签。
销售渠道漏斗图已完成。
Aesthetic Funnel Chart
现在你已经理解了漏斗图的基础知识,可以继续创建以下实际上看起来像漏斗的美观漏斗图−
Step 1 − 从原始数据表开始。
Step 2 − 选择数据并插入 3-D 堆积柱形图。
Step 3 - 如下所示设计图表。
-
在柱形图上右击。
-
Select Format Data Series.
-
在“数据系列格式”窗格中点击“系列选项”。
-
在柱形图形状下选择完整的圆锥。
Step 4 - 如下所示微调图表。
-
在功能区上点击“设计”选项卡。
-
点击“切换行/列”。
-
在垂直坐标轴上右击,然后在下拉列表中选择“格式坐标轴”。
-
在“格式化轴”窗格中单击“轴选项”。
-
选中“反向顺序的值”选项。
Step 5 − 取消选择所有图表元素
Step 6 − 在长条形上右击,然后在下拉列表中选择“格式数据系列”。
Step 7 − 在“系列选项”窗格中点击“系列选项”,并在“系列选项”下为“间隙深度”键入 50%。
Step 8 − 使用以下详细信息对图表进行格式化。
-
在图表元素中选择数据标签。
-
放置并对它们进行格式化。
-
对于底部数据的 Data Label,您可以选择 Call Out 选项。
-
Give a Chart Title.
漏斗形图表已就绪。
Advanced Excel - Waffle Chart
如果您希望将工作进度显示为完成百分比、实际完成目标等,饼状图可以增加您数据可视化的美感。它快速直观地显示了您想要表达的内容。
饼状图也被称为方形饼图或矩阵图。
What is a Waffle Chart?
饼状图是一个 10 × 10 单元格网格,单元格根据条件格式着色。网格表示 1% - 100% 范围内的值,并且单元格将以应用于它们包含的 % 值的条件格式突出显示。例如,如果工作的完成百分比为 85%,则通过将所有包含值 ⇐ 85% 的单元格格式化为特定颜色(如绿色)来表示。
饼状图如下图所示。
Advantages of Waffle Chart
华夫饼图具有以下优点:
-
It is visually interesting.
-
It is very readable.
-
It is discoverable.
-
它不会扭曲数据。
-
它提供超出简单数据可视化的可视交流。
Creating a Waffle Chart Grid
对于华夫饼图,您需要先创建一个 10 × 10 平方单元格网格,以便网格本身为正方形。
Step 1 − 通过调整单元格宽度在 Excel 表上创建一个 10 × 10 方形网格。
Step 2 − 使用 % 值填充单元格,从左下角单元格的 1% 开始,在右上角单元格以 100% 结束。
Step 3 − 减小字体大小,以便所有值可见,但不要更改网格的形状。
这是您将用于华夫饼图的网格。
Creating a Waffle Chart
假设您具有以下数据:
Step 1 − 通过对您创建的网格应用条件格式,创建一个华夫饼图,其中显示东区利润率,如下所示:
-
Select the Grid.
-
单击功能区上的条件格式。
-
从下拉列表中选择新规则。
-
定义规则,用深绿色填充和字体颜色来设置低于 85%(给出利润百分比的单元格引用)的值格式。
Step 2 – 定义另一个规则,用浅绿色填充和字体颜色来设置高于 85%(给出利润百分比的单元格引用)的值格式。
Step 3 – 给图表标题,引用单元格 B3。
如你所见,同时为填充和字体选择相同的颜色使你能够不显示百分比值。
Step 4 – 给图表添加如下标签。
-
在图表中插入一个文本框。
-
在文本框中引用单元格 C3。
Step 5 – 将单元格边框着色为白色。
你的区域东部的华夫饼图表已经就绪。
为如下区域(即北方、南方和西方)创建华夫饼图表:
-
参照前一部分所给出的内容,创建北方、南方和西方的网格。
-
对于每个网格,基于相应的利润百分比值,应用上述条件化设置。
你还可以通过为条件化设置选择不同的颜色,明确地为不同区域创建华夫饼图表。
如你所见,右边的华夫饼图表的颜色选择不同于左边的华夫饼图表。
Advanced Excel Charts - Heat Map
热图通常用于引用于二维阵列中区域的彩色区分,每种颜色都与每个区域共享的不同特征相关。
在 Excel 中,热图可以基于单元格包含的值应用于一系列单元格,方法是使用单元格颜色和/或字体颜色。Excel 条件格式适用于此目的。
What is a Heat Map?
热图是在表格中可视化表示数据,以突出显着的 data points。例如,如果你在过去一年中按月份统计了产品销售数据的,则可以预测哪些月份的产品销售高或低。
热图如下所示。
Advantages of Heat Maps
热图可用于以不同的颜色直观显示不同范围的数据。当你拥有大型数据集并且想要快速可视化数据中的某些特征时,这非常有用。
使用热图旨在 −
-
突出显示值范围中的几个最大值和最小值。
-
通过使用色度来描述值中的趋势。
-
识别空白单元格 - 比如在答题卷或问卷中。
-
突出显示产品的质量范围。
-
突出显示供应链中的数字。
-
Highlight negative values.
-
Highlight zero values.
-
突出显示阈值定义的异常值。
Creating a Heat Map
以下是创建热图的步骤 −
Step 1 − 选择数据。
Step 2 − 在功能区上单击条件格式。单击管理规则并添加规则,如下所示。
前五个值用绿色(填充)着色,后五个值用红色(填充)着色。
Creating Heat Map without Displaying Values
有时,查看者可能只需要信息,而数字可能并不重要。在这种情况下,您可以进行一些格式化,如下所示 −
Step 1 − 选择数据并将字体颜色选择为白色。
正如您所看到的,数字不可见。接下来,您需要突出显示前五个和后五个值,而不显示数字。
Step 2 − 选择数据(当然,是不可见的)。
Step 3 − 应用条件格式,以便前五个值用绿色(填充和字体)着色,而后五个值用红色(填充和字体)着色。
Step 4 − 单击应用按钮。
这种做法可以快速直观地显示全年及所有产品的高销量和低销量。由于您为填充和字体选用了相同的颜色,因此无法查看值。
Advanced Excel - Step Chart
如果您必须以不规则时间间隔显示数据,并且在变更之间保持恒定,那么步长图将很有用。例如,步长图可用于显示商品价格变更、税率变更、利率变更等。
What is a Step Chart?
步长图是一种不使用最短距离连接两个数据点的折线图。相反,它使用垂直和水平线条以系列形式连接数据点,形成阶梯状进行。步长图的垂直部分表示数据及其大小的变化。步长图的水平部分表示数据的恒定。
考虑以下数据 -
正如您所看到的,数据变动发生在不规则的时间间隔内。
步长图如下所示。
正如您所看到的,数据变动发生在不规则的时间间隔内。当数据保持恒定时,它会以水平线显示,直到发生变动为止。发生变化时,其大小由垂直线表示。
如果您使用折线图显示相同的数据,它将如下图所示。
Differences between Line Charts and Step Charts
您可以识别同一数据中的折线图和步长图之间的以下差异 −
-
折线图的重点不在于数据点的趋势,而在于变化的确切时间。步长图显示数据变化的确切时间以及趋势。
-
折线图不能描绘变化的大小,但步长图直观地描绘了变化的大小。
-
折线图无法显示数据值没有变化的持续时间。步长图可以清楚地显示数据值没有变化的持续时间。
-
折线图有时可能在显示两个数据值之间的趋势时具有欺骗性。例如,折线图可以显示两个值之间的变化,但事实并非如此。另一方面,当没有变化时,步长图可以清楚地显示稳定性。
-
折线图可以显示突然增加/减少,尽管变化仅在两次场合发生。步长图只能显示发生的两次变化以及变化实际发生的事件。
Advantages of Step Charts
步长图可用于描绘数据在不规则的时间间隔内具有数据变化的固有特性的任何类型的数据。示例包括以下内容 −
-
Interest rates vs. time.
-
Tax rates vs. income.
-
根据所用单位计算的电费分段。
Preparation of Data
考虑以下数据 -
Step 1 − 选择数据。将数据复制并粘贴到最后一行的下方。
Step 2 − 复制并粘贴右侧的所有数据。数据如下所示。
Step 3 − 删除下表中所示的第二组数据表中以红色高亮显示的单元格。
Step 4 − 在删除时向上移动单元格。第二组数据如下所示。
Step 5 − 复制第二组数据并将它们粘贴到右侧以获得第三组数据。
Step 6 − 选择第三组数据。将其从小到大的值进行排序。
你需要使用这种已排序的第三组数据来创建阶梯图。
Creating a Step Chart
按照以下步骤创建阶梯图 −
Step 1 − 选择第三组数据并插入折线图。
Step 2 − 如下格式化图表-
-
Click on the chart.
-
单击功能区上的设计选项卡。
-
单击“数据”组中的“选择数据”。“选择数据源”对话框将出现。
-
在“图例项(系列)”下选择“系列 1”。
-
Click the Remove button.
-
单击“水平(类别)轴标签”下的“编辑”按钮。单击“确定”。
“轴标签”对话框将出现。
Step 3 − 在“轴标签范围”下选择单元格 F2:F10,然后单击“确定”。
Step 4 − 在“选择数据源”对话框中单击“确定”。你的图表将如下所示。
Step 5 − 您会看到,水平(类别)轴中缺少一些值(年份)。要插入值,请按照以下步骤操作。
-
在水平轴上右键单击。
-
Select Format Axis.
-
在“格式化轴”窗格中单击“轴选项”。
-
在“轴选项”下的“轴类型”下选择“日期轴”。
如你所见,水平(类别)轴现在还包含类别值中缺失的年份。此外,在发生更改之前,该线是水平的。当有更改时,其大小由垂直线的高度表示。
Step 6 - 在图表元素中取消选择图表标题和图例。
你的步骤图已准备就绪。
Advanced Excel - Box and Whisker Chart
盒须图(也称为箱形图)通常用于统计分析。例如,您可以使用盒须图对比实验结果或竞争性考试成绩。
What is a Box and Whisker Chart?
在盒须图中,数值数据将按四分位数划分,并在第一四分位数和第三四分位数之间绘制一个矩形,并沿着第二四分位数绘制一条附加的线以标记中位数。第一四分位数和第三四分位数之外的最小值和最大值将以称作“须”的线表示。须表示高于或低于上下四分位数的变化幅度,且须之外的任何点都将被视为异常值。
盒须图如下所示。
Advantages of Box and Whisker Charts
无论您在哪里,只要需要了解数据分布,就可以使用盒须图。并且数据可以是多样化的,数据可从任何领域获取以进行统计分析。示例包括以下内容-
-
- 特定产品或服务的调查问卷答复,以了解用户的偏好。
-
- 考试成绩,以确定哪些学生在特定科目中需要更多关注。
-
- 竞赛考试的问答模式,以最终确定类别的组合。
-
- 实验室结果,以就新发明的药物得出结论。
-
- 特定线路上的交通模式,以简化途中信号。异常值还有助于确定数据被排除在外的原因。
Preparation of Data
假设您获得了以下数据-
根据以上数据创建第二张表,如下所示-
Step 1 - 使用 Excel 函数 MIN、QUARTILE 和 MAX 计算 2014、2015 和 2016 年的每个系列的以下内容。
-
Minimum Value.
-
First Quartile.
-
Median Value.
-
Third Quartile.
-
Maximum Value.
生成的第二张表将如下所示。
Step 2 - 根据第二张表计算差值的第三张表-
-
保留第一行为最小值,不变。
-
在第二行中,将值计算为第一四分位数 - 最小值。
-
在第三行中,将值计算为中位值 - 第一四分位数。
-
在第四行 – 计算值,作为第三四分位数 - 中位值。
-
在第五行 – 计算值,作为最大值 - 第三四分位数。
您将得到如以下所示的第三个表格。
您将使用这些数据用于箱线图。
Creating a Box and Whisker Chart
以下是创建箱线图的步骤。
Step 1 - 选择在上节中获取的第三个表格中的数据。
Step 2 - 插入一个堆积柱形图。
Step 3 - 单击功能区上的设计选项卡。
Step 4 - 在数据组中单击切换行/列按钮。
您的图表将如下图所示。
Step 5 - 右键单击底部数据系列。单击填充并选择无填充。
底部的 Data 系列将变得不可见。
Step 6 - 在图表元素中取消选择图表标题和图例。
Step 7 - 将水平轴标签更改为 2014、2015 和 2016。
Step 8 - 现在,您的盒子已准备就绪。接下来,您需要创建线须。
-
右键单击上部数据系列。
-
将填充更改为无填充。
-
单击功能区上的设计选项卡。
-
在图表布局组中单击添加图表元素。
-
在下拉列表中单击错误线并选择“标准偏差”。
Step 9 − 创建了顶部线须。接下来,按照如下所示格式化线须(错误线)−
-
右键单击错误线。
-
Select Format Error Bars.
-
在“格式错误线”窗格中的“错误线选项”下,选择以下内容。在“方向”下选择减号。在“末端样式”下选择无盖。在“误差量”下选择百分比,然后输入 100。
Step 10 − 在“格式错误线”窗格中的“错误线选项”下,单击“填充和线条”选项卡。
-
在“线条”下选择实线。
-
选择深蓝色作为颜色。
-
在“宽度”框中输入 1.5。
Step 11 − 为第二根较低的底部系列重复上述步骤。
Step 12 − 接下来,按照如下所示格式化框。
-
右键单击其中一个框系列。
-
Click Fill.
-
选择浅蓝色作为颜色。
-
Click Outline.
-
选择深蓝色作为颜色。
-
Click Weight.
-
Select 1½ pt.
Step 13 − 为其他框系列重复上述步骤。
您的箱线图就制作好了。
Advanced Excel Charts - Histogram
直方图是数值数据分布的图形表示。它广泛应用于统计分析。Karl Pearson 引入了直方图。
在 Excel 中,您可以从作为 Excel 附加项提供的分析工具包创建直方图。但是,在这种情况下,当数据更新时,除非再次通过分析工具包修改直方图,否则直方图将不会反映更改后的数据。
在本章中,您将学习如何从柱形图创建直方图。在这种情况下,当源数据更新时,图表也会刷新。
What is a Histogram?
直方图由一系列矩形表示,这些矩形的长度与连续数值区间中变量出现次数对应。数值区间称为“bin”,而出现次数称为频率。
Bin 通常被指定为连续、不相交的变量区间。Bin 必须是相邻的,并且具有相等的大小。位于 Bin 上的矩形的高度与 Bin 的频率成比例,它表示该 Bin 中案例的数量。因此,水平轴表示 Bin,而垂直轴表示频率。矩形被着色或阴影化。
直方图将如下图所示。
Advantages of Histograms
直方图用于检查数据的潜在分布、异常值、偏度等。例如,直方图可在统计分析中用于以下场景:
-
人口普查,以获取各个年龄群体的数量。
-
人口普查,以获取不同国家的识字水平。
-
关于热带疾病在一个州的不同区域内整个季节的影响的研究。
Advanced Excel - Pareto Chart
帕累托图被广泛应用于决策的统计分析。它体现了帕累托原则,也被称为“80/20法则”。
Pareto Principle (80/20 Rule)
帕累托原则也被称为“80/20法则”。这意味着80%的结果是由20%的原因造成的。例如,80%的缺陷可归因于关键的20%的原因。它也被称为“重要的少数和琐碎的多数”。
维尔弗雷多・帕累托通过调查和观察发现,大多数国家80%的收入流向20%的人口。
Advantages of Pareto Charts
您可以使用帕累托图进行以下分析 −
-
* 分析过程中问题频率的数据。
-
* 确定过程中问题的重大原因。
-
* 确定产品中缺陷的重大领域。
-
* 了解流程管道中的重大瓶颈。
-
* 确定团队或组织面临的最大问题。
-
* 了解员工离职的主要原因。
-
* 确定产生高利润的最重要产品。
-
确定增加公司价值的重要改进。
Preparation of Data
考虑以下数据,其中给出了缺陷原因和相应计数。
Step 1 −按缺陷计数列从大到小对表格进行排序(从最大到最小)。
Step 2 −创建一个累积计数列,如下所示:
这将导致以下表格:
Step 3 −对缺陷计数列求和。
Step 4 −创建一个累积百分比列,如下所示。
Step 5 −将累积百分比列格式化为百分比。
您将使用此表格创建帕累托图。
Creating a Pareto Chart
通过创建帕累托图,您可以得出缺陷的主要原因。在 Excel 中,您可以将帕累托图创建为柱形图和折线图的组合图。
以下是创建帕累托图的步骤:
Step 1 −选择表格中的缺陷原因和缺陷计数列。
Step 2 - 插入一个簇状柱形图。
Step 3 −如您所见,表示原因的列以降序排列。按如下方式设置图表格式。
-
右键单击列,然后单击数据系列格式。
-
单击数据系列格式窗格中的系列选项。
-
更改系列选项下的间隙宽度为 0。
-
右键单击列,然后选择轮廓。
-
选择深色并使粗细醒目。
您的图表将如下图所示。
Step 4 −按如下方式设计图表。
-
Click on the chart.
-
单击功能区上的设计选项卡。
-
单击“数据”组中的“选择数据”。“选择数据源”对话框将出现。
-
Click the Add button.
编辑系列对话框出现。
Step 5 -在单元格上单击 - 系列名称的累积 %。
Step 6 -选择累积 % 列中的数据,以获得系列的值。单击确定。
Step 7 -在选择数据源对话框中单击确定。您的图表将如下所示。
Step 8 -在功能区上单击设计选项卡。
Step 9 -在类型组中单击更改图表类型。
Step 10 -更改图表类型对话框出现。
-
单击所有图表选项卡。
-
Click the Combo button.
-
为缺陷数选择聚集柱形图,为累积 % 选择折线图。
-
选中 Secondary Axis 复选框以使用折线图。单击确定。
正如您所见,80% 的缺陷是由两个原因造成的。
Advanced Excel - Organization Chart
你可以使用组织结构图来说明团队或组织内的报告关系。在 Excel 中,你可以使用具有组织结构图布局的 SmartArt 图形。
What is an Organization Chart?
组织结构图以图形方式表示组织的管理结构,例如部门经理和组织中相应的报告员工。此外,高级经理可以有助手,助手也会在组织结构图中显示。
Excel 中的组织结构图如下所示。
Preparation of Data
以下是准备组织结构图数据的步骤:
Step 1 - 按如下方式整理有关组织不同角色的信息。
Step 2 - 识别层级中的报告关系。
您将使用此信息创建组织结构图。
Creating an Organization Chart
以下是创建组织结构图的步骤:
Step 1 - 单击功能区上的插入选项卡。
Step 2 - 单击插图组中的 SmartArt 图形图标。
Step 3 - Choose a SmartArt Graphic 对话框出现。
Step 4 - 从左窗格中选择层级结构。
Step 5 - 单击组织结构图。
Step 6 - 组织结构图的预览出现。单击确定。
组织结构图模板出现在您的工作表中。
正如您所看到的,您可以在左窗格中输入文本,它会立即出现在右侧的图表中。左窗格中带有附加线条的项目符号的框表示它是图表中的“助理”框。左窗格中带有项目符号的框表示它们是图表中悬挂布局的一部分。
Step 7 - 在文本窗格中输入信息。
Step 8 - 如果有报告关系,则降级。
Step 9 - 单击图表外部。您的组织结构图已准备就绪。