用Excel 中的方差分析制作更好的预算与实质图表-外链论坛

fny5jt9 发表于 2024-10-1 16:28:34

用Excel 中的方差分析制作更好的预算与实质图表

<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/be5ee778fd324c7ca835b7d79abc2f2e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=VjP0nQmGbr0eb4CTHq7bFjc8aWU%3D" style="width: 50%; margin-bottom: 20px;"></div>
无论是平常生活还是工作，咱们几乎无时无刻不在心里将实质结果与计划进行比较。虽然咱们能够谈论数字和百分比，但图表讲述故事的方式是无与伦比的。然而，方差图一般很棘手，由于您需要报告三件事：计划数字实质数字差异，即计划与实质数字之间的差异，可能是有利的，亦可能是有害的。将三个元素放在一块基本不是问题，问题是以最有道理的方式传达故事，这归结为咱们呈现故事的方式。哪些有兴趣认识什么是方差分析的人请阅读这篇文案，由于它用简单的语言描述了这个概念：什么是方差分析？
目录准备表格！完成基本知识打开表格！完成图表校正 X 轴自定义数据标签又名动态数据标签
以下是咱们今天要学习的很酷的营销方差图：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d8e07fed97f343a6b1e31707869b4169~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=6W5j3JqhlKySY6kXeXriDRhSNaU%3D" style="width: 50%; margin-bottom: 20px;"></div>
剧透警告！以下是咱们将在本教程中学习的有些内容：制作要求图表或多色条形图来区分有利和有害的方差。认识主轴和副轴的工作原理调节图表比例运用符号自定义数字格式Excel 图表中的自定义数据标签或我应该说动态数据标签当然还有其他有些事情，例如运用 NA() 函数。那样让咱们起始吧！
下载此Excel 教程工作簿来练习本教程中教授的概念。
链接: https://pan.baidu.com/s/1Bm9J3c0kWvc30dyCVq7Ajw?pwd=qkey 提取码: qkey 复制这段内容后打开百度网盘手机App，操作更方便哦

<h1 style="color: black; text-align: left; margin-bottom: 10px;">准备表格！完成基本知识</h1>
过程 1：下载并打开工作簿。它已然包括一个简单的营销数据以及实质和计划的数字。
过程 2：转到单元格 A22 并输入标题 “实质 < 计划”。 在本专栏中，咱们将认识实质结果是不是小于计划结果。因此咱们要寻找的是每一个月的简单 TRUE 或 FALSE。将此公式放入单元格 A23 中，并将填充柄向下拖动到单元格 A34，以便咱们得到所有月份的结果：
=B2<C2
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/1e6630b1cbca43238cb6edd1d3cfafe4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=Tr%2F%2FVHlrWC7jTNvLWcy2RyMxaGE%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程 3：转到单元格 B22，输入标题“方差”。在单元格 B23 中输入以下公式并将填充柄拖动到单元格 B34：
=ABS(B2-C2)
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cbc0da2e39284a888904732a8afd784c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=LrLQeA4cftOtUXzqsScbbYYxV4E%3D" style="width: 50%; margin-bottom: 20px;"></div>
运用绝对值而不是简单值的原由是为了简化图表过程，由于在图表中处理负数和正数可能会变得有点乏味以呈现想要的方式。
第 4 步：从此刻起始，事情变得非常有趣， “实质 < 计划” 列将在其中发挥重要作用。转到单元格 C22 并创建标题占位符。 此列将帮忙咱们获取实质或计划数字，以较低者为准。将此公式放入单元格 C23 中，而后将其复制到单元格 C34 中：
=IF(A23,B2,C2)
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/993072e2e9914ae0a352a4ff55539146~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=I18kGnh5H0ZiaNFL0K5WZ%2FEXma0%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程5：正如我之前所说，咱们需要以区别的颜色呈现有利和有害的结果，因此呢咱们需要两个区别的数据集，即一个用于绿色（有利方差），一个用于红色（有害方差）。
此刻，这儿的概念是方差能够是正数或负数，但因为咱们在两个单独的数据集中报告它们，因此呢倘若咱们有有利的方差，那样咱们将在绿色栏中表示数字，而在红色栏中无数字。一样，倘若方差有害，则结果将被拉入红色列，但不会拉入绿色列。
转到单元格 D22 并将标题设为 红色 ，并在单元格 E22 中将标题设为绿色。在单元格 D23 至 D34 中输入以下公式：
=IF(A23,B23,NA())
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8d9fefa071f440de88abdc190f531aa7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=80HzIYYYJPfiETlaS9TykVR%2F8No%3D" style="width: 50%; margin-bottom: 20px;"></div>
转到单元格 E23 并将此公式写入单元格 E34：
=IF(A23,NA(),B23)
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c6d476ecb8d34625af04c19ab22fb91f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=R3A%2FgW0AfNlbPPXUZSr0%2BEsL6rs%3D" style="width: 50%; margin-bottom: 20px;"></div>
到日前为止，咱们制作图表所需的核心项目已然就位，但为了以更优雅的方式进行收尾工作，咱们还需要三列。倘若咱们想在图表上报告，两列将帮忙咱们报告累积数字，第三列将帮忙咱们供给自定义数据标签，使图表更易于理解。
第 6 步：咱们将继续运用与方差相同的概念，将数字分成两列。运用相同的概念，咱们将需要添加名叫作为Over和Under 的列。 两列都将表示实质数字，但倘若实质数字大于计划，则它将表示在“上方”列中，倘若少于计划，则它将表示在“下方”列中。
转到单元格 F22 并指定标题“Over” ，而后在单元格 F23 中输入此公式并将填充柄向下拖动到 F34：
=IF(A23,NA(),B2)
该公式检测实质值是不是少于计划值，而后不表示任何内容，否则获取单元格 B2 中的值。
转到单元格 G22 并在单元格G23下方 和单元格 G23 中输入标题，输入此公式并将其向下拖动到单元格 G34：
=IF(A23,B2,NA())
该公式的功效相反，由于倘若实质数字少于计划，则它会获取值，而后在 B2 中表示该值，否则不表示任何内容。以下动画表示了第 6 步：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/0140643994234c49aeb59352a0a86d02~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=GZDGlbZkFuEoaUKUHGMOiti%2FjoY%3D" style="width: 50%; margin-bottom: 20px;"></div>
第 7 步：此刻进入最后一栏，这将帮忙咱们持有自定义数据标签。转到单元格 H22 并安置标题数据标签，而后将此公式放入单元格 H23 中：
=B2-C2
将填充柄向下拖动到单元格 H34 以获取其他月份的值。选取单元格 H23:H34 中的数字，而后按 Ctrl+1 调用单元格格式对话框。保证数字选项卡处在活动状态（默认状况下）。单击左侧列表中的自定义。此刻咱们需要输入有些符号，例如倘若值为正则指向向上的箭头，倘若值为负则指向向下的箭头。要将此记录下来，请根据下列过程操作：按住键盘上的 Alt 键和小键盘上的键 3 和 0，而后释放 Alt 键。这将插进一个向上的箭头。而后输入 0，而后输入冒号 ;。这完成为了正数的格式。按住 Alt 键，而后按数字键盘上的 3 和 1，而后松开 Alt 键。这将插进向下的箭头。紧接着输入 0，而后输入冒号。这般就完成为了负数的格式。针对零数字，请输入：“-”，倘若结果为零，则会表示破折号。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d52ef6d1f8c946daa8bd68cd78265347~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=9wWK1i2YkGEVEtr5ImM8aNPuSMM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">打开表格！完成图表</h1>
第 1 步：选取带有数字的占位符、红色、绿色、上方和下方列。单击功能区中的插进选项卡，单击图表组中的列按钮，而后选取 2D 下的第1个图表类型。您将得到如下所示的图表：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6bb2a513f5cd49d5bc9c991c6f2a3c10~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=Z1XheCMHKBzWewhxSGA4iU7TL9o%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程 2：单击图表中的某处以激活（选取它）它，并根据以下过程清除有些咱们不需要的东西：在图表工具功能区选项下，单击布局，而后单击网格线 > 重点水平网格线 > 选取无。单击图例 > 选取无<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/a31bcb9140964faebd93b35b98af2dc3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=B8V3QnkoBO%2BF5GRZWqICkPShj2M%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程 3：右键单击图表上的空白区域，而后单击选取数据。单击添加按钮。在系列名叫作框仍处在活动状态的状况下，单击单元格 C2。将光标移动到系列值输入字段。删除其中的所有内容，而后选取“计划”列中的值。单击“确定”。这将在图表中添加另一个变量。
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/12acf7452cc445cc9d262d94f9ff0199~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=T1YvkCFbgMH%2FON0FPjyzRjRRa4E%3D" style="width: 50%; margin-bottom: 20px;"></div>
第 4 步：是时候进行重大更改，不仅包含外观更改，还包含每一个系列在图表上绘制的方式。因此要非常重视每一项的处理：
计划系列
位置：单击图表中的任意位置以激活它和图表工具。单击图表工具下的格式选项卡，而后从当前选取组中选取计划系列。查看公式栏并查看末尾的数字。更改它 1。在我的例子中，它是 6。
演示：仍选取计划系列，并在格式选项卡下单击当前选取组下的格式选取按钮。将打开格式数据系列框。将重叠百分比设置为 0%，间隙宽度设置为 40%。
颜色：倘若您想更改计划系列的颜色，此刻是时候了，由于它仍然处在选中状态。我经过更改形状填充下拉菜单的颜色将颜色更改为灰色。以下动画表示了所有过程。占位符系列
位置：从选取下拉列表中选取占位符系列。查看编辑栏中的最后一位数字，将其更改为 2，使其作为图表上的第二个元素。幸运的是我的第二名。
演示：仍选取系列时，单击格式选取按钮。在框中的绘图系列下，选取辅助轴单选按钮。将重叠设置为 100%，间隙宽度设置为 150%。单击“确定”。
颜色：因为这个系列只是为了定位绿色和红色元素，因此呢咱们不需要它被着色。因此呢，我经过从形状填充下拉列表中选取不填充来使其透明。红色系列
位置：必须是3
演示：选取系列后点击格式选择按钮。只需将轴更改为辅助轴，而后将重叠调节为 100%，间隙宽度调节为 150%
颜色：检测其颜色是不是正确。幸运的是我的是淡红色的。不完全是红色但可行。绿色系列
位置：应该是 4。
呈现：与红色系列相同，即将其绘制在辅助轴上，重叠为 100%，间隙为 150%。
颜色：采用绿色或任何您爱好的颜色以表示有利的变化。
图表类型：这是有趣的一点，由于您会看到全部图表立即成形。当绿色系列仍然处在活动/选定状态时，单击设计选项卡 > 单击更改图表类型按钮 > 选取堆积柱形图，这是条形图类别中的第二个。在系列上
定位它 5.
图表类型：咱们需要更改该系列的图表类型。仍然选取该系列，单击图表工具下的设计选项卡，而后单击更改图表类型按钮，而后选取折线类别下的折线图按钮。
颜色：因为咱们不需要表示它，因此呢单击形状轮廓下拉列表并选取无轮廓。下系列
将其定位在 6。
图表类型：进行与上系列相同的更改。颜色：再次将其更改为无轮廓以隐匿它。经过这六个过程后，图表应如下所示：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/732a6fc5fd834399ac96a5458a6c5d6a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=k5jK7%2BU6x5kOD75jDIiWd7eCQl8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">校正 X 轴</h1>
日前咱们的 x 轴表示的是数字而不是月份。要更正它，请单击图表空白区域内的任意位置，而后单击菜单中的“选取数据”。保证在左侧列表中选取计划，而后单击右侧列表上方的编辑按钮。在 A 列中选取月份名叫作，而后单击“确定”关闭对话框。咱们快完成为了。
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/56efc3e0a5bc4820a0dddecf54973c1e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=hUa92CJCtVe4zVpkyYr64nuztWA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">自定义数据标签又名动态数据标签</h1>
我所说的自定义数据标签指的是 Excel 应该从特定单元格中提取数据以表示为数据标签而不是序列号。这般，标签就更加灵活且易于运用，由于它们取决于特定单元格的内容，因此呢亦是动态的。
第 1 步：选取图表并转到“格式”选项卡，而后从选取下拉列表中选取“Over Series”。选取系列后，单击布局选项卡，而后单击数据标签并选取上方。这将在绿色条上方表示以上系列的数字。
过程 2：此刻，经过转到“格式”选项卡，而后从图表选取组下拉列表中选取“下”系列。选取系列后，转到布局，而后单击数据标签，而后选取下方以报告系列下的数字。
过程3：左键单击第1个数据标签，它将选取全部系列中的所有数据标签。再次单击左键，它将仅选取第1个数据标签。选取后，单击编辑栏内并按键盘上的 = 键，而后借助鼠标选取单元格 H23。按回车键，此刻您将看到方差数字，它不仅报告方差，况且借助箭头您能够认识它是有利还是有害。对所有其他数据标签重复此过程。
以下动画向您展示了怎样执行以下过程：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/eb487a52629c4b38a94c69da29bdff0d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=OiaI6BRvgPEks0KSG%2FB59al%2FWko%3D" style="width: 50%; margin-bottom: 20px;"></div>
最后摆脱重点和次要垂直轴以进一步清理视图并准确获取所需内容
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c27ae1384b8b4adc8bd6ee0e367cbb52~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727974131&x-signature=wjiQ8N039PHGIG09Th%2BO7Zx5R%2BA%3D" style="width: 50%; margin-bottom: 20px;"></div>
我留给您的最后一项作业是表示计划系列的数据标签。启用它们将使观看者更容易理解计划的数字和差异效应。只需选取计划系列并启用数据标签就可表示内部基本。便是这般！任务完成！

7wu1wm0 发表于 2024-10-4 17:34:40

我深受你的启发，你的话语是我前进的动力。

1fy07h 发表于 2024-10-26 00:08:17

期待与你深入交流，共探知识的无穷魅力。

4lqedz 发表于 2024-11-7 06:29:48

你的话语如春风拂面，温暖了我的心房，真的很感谢。

j8typz 发表于 3 天前

楼主继续加油啊！外链论坛加油！

页: [1]

外链论坛's Archiver

用Excel 中的方差分析制作更好的预算与实质图表