如何在Excel中创建周期图以绘制季节性销售数据-Excel学习网
2019年10月19日 18:35 发布来源：Excel学习网
如果您有季节性销售或其他绩效指标，则周期图可以比传统图表技术更深入地了解您的绩效。
如果您公司的销售是及时的，那么您可能已经看过如下图的图表：
典型的季节性数据图表。
此Excel图表显示了十一年期间的连续销售趋势。尽管该图表使一般模式显而易见，但在了解细节方面却无济于事。
例如，哪个月份的销售额最高？销售会变得更季节性还是更季节性？平均而言，每年六月我们卖多少钱？哪个月份增长最快？最慢？
图2显示了呈现这种数据的另一种常见方式。我们没有使用上图显示连续的趋势，而是使用Excel每年绘制一个数据系列。
显示季节性数据的另一种典型方法，每年只显示一行。
但是这种方法几乎没有改善。仅显示了几年，我们就可以更轻松地看到一年中的典型趋势。但是，如上所述，经过多年的展示，大量的线条变得毫无意义。
纳奥米·罗宾斯（Naomi Robbins）在她的著作《创建更有效的图》中写道 ，它介绍了一种使用循环图显示相同数据的更好方法：
周期图图表，显示一年中每个月的多年趋势。 水平线显示每个月的平均效果。
在此Excel图表中，我们可以轻松地比较一年中每个月的效果。黑线表示十一年来每年一月，二月等的性能趋势。灰色的水平线显示了这些年份每个月的平均销售额。粗蓝线表示多年来每个月的销售趋势。
该数据表明，6月份的销售增长速度快于其他月份，而11月和12月的销售增长速度却慢。由于我们的高销售月份增长速度更快，因此该图表明销售正变得更加季节性。
显示季节性数据的另一种方法是在星期几：
一周中每一天的销售图表均未显示明显的模式。
最初，我将周线图用于每周数据。但是我决定使用柱形图为每日销售提供更多有用的信息。在这里，我们看到销售在一周中旬趋于下降，但在周末有所上升。但是由于总差异仅占销售额的2％，因此在这种情况下效果并不明显。
另外，由于两组列实际上是相同的，因此我们知道在整个报告期间的前半部分和后半部分之间，这种模式没有太大变化。
如何在Excel中创建循环图
为了避免将本文变成一本简短的书，我将仅解释包含这些图表的原始工作簿中最重要的元素。我最近更新了工作簿以使用Excel表。而且我决定将所有四个图表都包括在 Charley的Swipe File＃74中。
创建一个如图3所示的循环图需要三个步骤。首先，设置工作表及其数据。其次，为数据分配范围名称。最后，创建图表。
在开始之前，您可能会对这个过程有一些疑问：
1.数据从Excel表中的一长列销售开始。我使用INDEX - MATCH来检索图3的数据。
2.除了格式，图1和2中的折线图非常常见。几乎所有有关Excel图表的信息都可以向您展示如何创建类似的图表。
除了这些项目之外，让我们集中讨论如何设置循环图的基本元素，如仪表板和上面的图3所示。
步骤1：设置工作簿和数据
用一个工作表创建一个新的工作簿。将工作表命名为Chart，然后在Excel 2007或更高版本中将工作簿另存为CyclePlot.xlsx。像这样设置工作表的顶部区域：
用于计算循环图的数据。
该表显示了2002年1月至2013年7月的数据。第18行的其余部分包含＃N / A。要消除自己输入数字的工作，您可以在下表中选择单元格，将其复制，然后将其粘贴到Excel中。
1个 2002年 14 21 25 21 26 32 27 20 10 11 5 5
2 2003年 18岁24 28 24 33 37 30 25 13 14 6 6
3 2004年 22 31 36 28 37 43 35 30 13 13 7 7
4 2005年 25 32 38 34 39 48 38 29 14 14 8 8
5 2006年 29 38 47 33 44 57 41 39 16 16 9 8
6 2007年 29 35 49 34 43 57 41 37 20 17 9 10
7 2008年 22 32 37 30 35 44 38 31 16 17 8 7
8 2009年 25 34 41 33 39 47 44 32 17 17 9 8
9 2010年 26 35 46 40 47 61 47 41 20 18岁9 10
10 2011年 29 39 55 38 55 67 53 41 19 20 11 11
11 2012年 38 48 60 49 57 79 62 54 26 26 13 11
12 2013年 40 41 53 42 52 67 54 ＃N / A ＃N / A ＃N / A ＃N / A ＃N / A
确保格式化灰色边框行，如第6和19行所示。并确保边框行为空。
下面单元格地址处的公式包含一个数组公式：
C20：= ROWS（C6：C19）-SUM（IF（ISNA（C6：C19），1,0））-2
要将其作为数组公式输入，请输入如图所示的公式，但是在按Enter之前，按住Ctrl和Shift键。完成后，Excel将以大括号{和}开始和结束公式。之后，将单元格复制到右侧，如图所示。
现在，您可以输入返回平均值和趋势的公式。这样做，将以下部分添加到您的工作表：
销售数据的平均值和趋势。
列A和B具有显示的值。以下是其他数字的代表性公式：
C24：= AVERAGE（OFFSET（C $ 6,1,0，C $ 20））
C25：= C24
通过单元格N24向右复制单元格C24。将单元格C25复制到范围C25：N35。
C40：= TREND（OFFSET（C $ 6,1,0，C $ 20），OFFSET（$ A $ 6,1,0，C $ 20），$ A40）
将单元格C40复制到范围C40：N51。
步骤2：设置范围名称
现在是关键步骤。您将要创建三个范围名称，它们可能与您之前创建的任何Excel范围名称都不相同。您将通过选择十二个“不连续”范围来开始该过程。
首先，单击并从单元格C6到C19拖动，就像通常选择该范围一样。释放鼠标键。
按住Ctrl键。
现在单击并从单元格D6到D19拖动。再次释放鼠标按钮。如果您没有按住Ctrl键，Excel会将您的选择移至第二个范围。但是，Excel现在将第二个范围添加到您的选择中。
按住Ctrl键的同时，在Excel中的表格中进行操作，选择总共12个独立范围，每个月选择一个。在下图中，您可以看到每个选定列之间的一小部分空白。这告诉您这12列中的每列都是单独选择的。也就是说，它们是不连续的。
您可以单独选择此表中的每一列，而不要选择一个大区域。
现在，您需要为此选择指定一个范围名称。为此，请在New Excel（2007版及更高版本）中选择“公式”，“定义的名称”，“定义的名称”。
在对话框中，输入PlotData作为名称，然后选择“确定”。
然后，与对PlotData范围所做的一样，在PlotAvg部分中不连续地选择12列，并将此选择定义为PlotAvg。对PlotTrend部分执行相同的操作，命名选择PlotTrend。在这两种情况下，请确保在数据列的上方和下方都包含灰色边框行。
最后，定义两个普通范围名称。定义DateText以引用范围C1：N1，如下图所示。然后定义DateValue以引用范围C2：N2。
步骤3：建立图表
现在已经准备好基础，您可以创建循环图。第一个数据系列将仅显示图1至4中所示的X轴标签。其他三个数据系列将显示图3中所示的行。
首先，选择范围C1：N2，如下所示。
要绘制的数据的开始。
要在New Excel（Excel 2007）中绘制此数据图表，请选择“插入”，“图表”，“折线”。选择左上方的子类型。
图表对象将在X轴上显示月份，但不显示数据。
您将不需要图表的图例。因此，在图表中选择它，然后按Delete键将其删除。
将单元格引用更改为范围名称通常是个好主意。这有助于记录我们的公式并增加灵活性。要分配名称，请替换此公式中的阴影区域。
= SERIES（，Sheet1！$ C $ 1：$ N $ 1，Sheet1！$ C $ 2：$ N $ 2，1）
...名称显示在这里...
= SERIES（，Sheet1！DateText，Sheet1！DateValue，1）
...这样做之后，Excel会将公式更改为...
= SERIES（，CyclePlot_01.xlsx！DateText，CyclePlot_01.xlsx！DateValue，1）
现在，让我们添加第二个 SERIES函数，它将显示循环图数据。
添加了第二个SERIES函数之后的图表。
您将分两步添加系列。首先在一行中选择任何两个单元格范围的数据，例如C7：D7。复制此范围并将其粘贴到您的图表中。
当您这样做时，您的图表将看起来像这样。
此时，隐藏SERIES 1行是个好主意。为此，您需要选择它。这很困难，因为SERES 1线与X轴边界重叠。
解决此问题的一种简单方法是选择图表区域，然后按一次键盘上的向下箭头。这样做会选择最近创建的SERIES功能。（如果您碰巧选择了图表中的其他对象，请按住向下箭头。最终，SERIES函数将出现在您的公式栏中。）
随着SERIES 1公式显示在您的公式栏中，按Ctrl + 1以显示该系列的“属性”对话框。选择线条颜色选项卡。选择无行。然后选择关闭。
接下来，选择SERIES 2函数，并将对C7：D7的引用替换为对范围PlotData的引用。也就是说，改变...
SERIES（，CyclePlot.xls！DateText，Chart！$ C $ 7：$ D $ 7，2 ）
...至...
SERIES（，CyclePlot.xls！DateText，Chart！PlotData，2）
输入更改后，Excel会将SERIES公式更改为...
= SERIES（，CyclePlot.xlsx！DateText，CyclePlot.xlsx！PlotData，2）
现在，图表开始看起来像图表。
...您的图表将如下所示。
好消息是该图表现在显示了循环图。坏消息是X轴标签已经弄乱了。
出现此问题的原因是，系列1（我们的日期标签）和系列2（我们的循环图）当前共享相同的X轴，但是它们的要求冲突。
要解决此问题，我们需要设置循环图系列以使用辅助轴。
首先，通过单击循环图线之一选择SERIES 2。按Ctrl + 1启动“属性”对话框，然后选择“系列选项”，“辅助轴”，“关闭”。
Excel通过忽略数据中除前12个值之外的所有值来“解决”轴问题，并将这些值与12个月的标签保持同步。
选择“布局”，“轴”，“轴”，“辅助水平轴”，“从左到右轴显示”。然后选择“布局”，“轴”，“轴”，“次要垂直轴”，“无”。
您的图表看起来像上面的图表。
在两个版本的Excel中，如果图表不够宽或字体太大，则X轴标签将旋转到垂直位置。若要更正此问题，请选择X轴，按Ctrl + 1，然后：
在Classic Excel中，在“对齐方式”选项卡中，单击“度数”向上箭头按钮将度数更改为1。然后单击“向下箭头”按钮将度数更改回0。这样做会删除“自动”方向，强制将标签设置为水平。
在New Excel中的“对齐”选项卡中，单击“自定义角度”向上箭头按钮一次，然后单击一次向下箭头。这样做会强制Excel接受0度的自定义角度。
现在，我们必须摆脱图表顶部的标签和刻度线混乱。为此，选择顶部轴，按Ctrl + 1，然后：
在Classic Excel中，在“模式”选项卡中，根据需要为“主要”刻度线类型，“次要”刻度线类型和“刻度线”标签选择“无”。然后选择确定。
在New Excel中，在“轴选项”选项卡中，根据需要在下拉列表框中为“主要”刻度线类型，“次要”刻度线类型和“轴”标签选择“无”。然后选择关闭。
进行这些调整之后，您的图表在顶部应该已经混乱了。
从这里下山都是..
我们需要在图表中再添加两条线。一个将显示每个月的平均值，另一个将显示趋势线。为此，选择循环图线。您将在公式栏中看到以下公式：
= SERIES（，CyclePlot.xls！DateText，CyclePlot.xls！PlotData，2）
在公式栏中选择整个公式，然后按Ctrl + C进行复制。
选择您的图表。这样做将从您的公式栏中删除SERIES公式。按Ctrl + V将复制的系列粘贴到编辑栏。然后修改复制的公式，以使下面的阴影部分如图所示。
= SERIES（，CyclePlot.xls！DateText，CyclePlot.xls！PlotAvg， 3）
最后，按Enter键输入此新公式。
再次选择图表。再次按Ctrl + V再次复制您复制的SERIES公式。修改复制的公式，使其看起来像这样：
= SERIES（，CyclePlot.xls！DateText，CyclePlot.xls！PlotTrend， 4）
顶部的图表来自Excel 2003； 底部的图表来自Excel 2007或更高版本。然后按Enter。
完成后，您应该拥有一张看起来像这里所示的图表的图表。顶部图表来自Classic Excel，底部图表来自New Excel。
在这两种情况下，您可能都希望尝试使用颜色，线条粗细和标记。（最上面的示例包含模糊的粗线，因为Classic Excel自动添加了您需要删除的标记。）
要调整线条，请在图表中选择一条线条，按Ctrl + 1，然后调整将找到的相关设置。
另外，您可能想查看垂直网格线是否可以改善图表。要设置它们，请选择图表，然后：
在Classic Excel中，选择“图表”，“图表选项”，“网格线”。在“类别（X）轴”下，选中“主要网格线”。然后选择确定。
在新Excel中，选择“布局”，“轴”，“网格线”，“主垂直网格线”，“主要网格线”。
要增加或减少图表显示的数据年数，只需在PlotData部分的阴影灰色边框之间插入或删除数据行，然后根据需要在其他两个部分中调整公式的行。
原创文章如转载请注明：转载自Excel学习网 [ http://www.excelxx.com/]
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10725.html
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10725.html
相关教程：
- → excel使用教程,EXCEL的替换查找功能,比你想象中强大1000倍-Excel教程
- → EXCEL财务函数-年金现值函数的应用-Excel学习网
- → excel报表最可怕的5个错误-Excel教程-Excel学习网
- → EXCEL表格中能不能用SUMIFS写出INDEX和MATCH混用的效果-Excel学习网
- → 如何在Excel中创建周期图以绘制季节性销售数据-Eexcel学习网
- → Excel的主要功能是什么-Excel学习网
- → 创建链接到Excel表的可变长度，动态报告-Excel学习网
- → Excel动态图表让数据动起来,老板看完亮瞎眼,还有这种操作?-Excel学习网
- → 运用Excel构建杜邦分析模板进行财务报表分析-Excel学习网
- → EXCEL做的杜邦财务分析模型Excel学习网
经典Excel学习视频及教程推荐：
Excel优秀工具推荐：
小工具推荐：