如何在Excel中创建周期图以绘制季节性销售数据-Excel学习网

Question

如果您有季节性销售或其他绩效指标，则周期图可以比传统图表技术更深入地了解您的绩效。如果您公司的销售是及时的，那么您可能已经看过如下

Answer 1

如果您有季节性销售或其他绩效指标，则周期图可以比传统图表技术更深入地了解您的绩效。

如果您公司的销售是及时的，那么您可能已经看过如下图的图表：

典型的季节性数据图表。

此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年1421252126322720101155

22003年18岁24282433373025131466

32004å¹´2231362837433530131377

42005å¹´2532383439483829141488

52006å¹´2938473344574139161698

62007å¹´29354934435741372017910

72008å¹´2232373035443831161787

82009å¹´2534413339474432171798

92010年26354640476147412018岁910

102011å¹´293955385567534119201111

112012å¹´384860495779625426261311

122013年40415342526754＃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部分的阴影灰色边框之间插入或删除数据行，然后根据需要在其他两个部分中调整公式的行。