当前位置: 首页 > Excel > Excel常用函数 > ExcelExcel技巧 > 如何在Excel中创建周期图以绘制季节性销售数据-Excel学习网

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

发布时间:2020年09月27日 08:56:12 来源: 点击量:1353

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

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

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

如何在<a style=Excel中创建周期图以绘制季节性销售数据-Excel学习网" />

典型的季节性数据图表。

此Excel图表显示了十一年期间的连续销售趋势。尽管该图表使一般模式显而易见,但在了解细节方面却无济于事。

例如,哪个月份的销售额最高?销售会变得更季节性还是更季节性?平均而言,每年六月我们卖多少钱?哪个月份增长最快?最慢?

图2显示了呈现这种数据的另一种常见方式。我们没有使用上图显示连续的趋势,而是使用Excel每年绘制一个数据系列。

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

显示季节性数据的另一种典型方法,每年只显示一行。

但是这种方法几乎没有改善。仅显示了几年,我们就可以更轻松地看到一年中的典型趋势。但是,如上所述,经过多年的展示,大量的线条变得毫无意义。

纳奥米·罗宾斯(Naomi Robbins)在她的著作《创建更有效的图》中写道 ,它介绍了一种使用循环图显示相同数据的更好方法:

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

周期图图表,显示一年中每个月的多年趋势。 水平线显示每个月的平均效果。

在此Excel图表中,我们可以轻松地比较一年中每个月的效果。黑线表示十一年来每年一月,二月等的性能趋势。灰色的水平线显示了这些年份每个月的平均销售额。粗蓝线表示多年来每个月的销售趋势。

该数据表明,6月份的销售增长速度快于其他月份,而11月和12月的销售增长速度却慢。由于我们的高销售月份增长速度更快,因此该图表明销售正变得更加季节性。

显示季节性数据的另一种方法是在星期几:

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

一周中每一天的销售图表均未显示明显的模式。

最初,我将周线图用于每周数据。但是我决定使用柱形图为每日销售提供更多有用的信息。在这里,我们看到销售在一周中旬趋于下降,但在周末有所上升。但是由于总差异仅占销售额的2%,因此在这种情况下效果并不明显。

另外,由于两组列实际上是相同的,因此我们知道在整个报告期间的前半部分和后半部分之间,这种模式没有太大变化。

如何在Excel中创建循环图

为了避免将本文变成一本简短的书,我将仅解释包含这些图表的原始工作簿中最重要的元素。我最近更新了工作簿以使用Excel表。而且我决定将所有四个图表都包括在 Charley的Swipe File#74中。

创建一个如图3所示的循环图需要三个步骤。首先,设置工作表及其数据。其次,为数据分配范围名称。最后,创建图表。

在开始之前,您可能会对这个过程有一些疑问:

1.数据从Excel表中的一长列销售开始。我使用INDEX - MATCH来检索图3的数据。

2.除了格式,图1和2中的折线图非常常见。几乎所有有关Excel图表的信息都可以向您展示如何创建类似的图表。

除了这些项目之外,让我们集中讨论如何设置循环图的基本元素,如仪表板和上面的图3所示。

步骤1:设置工作簿和数据

用一个工作表创建一个新的工作簿。将工作表命名为Chart,然后在Excel 2007或更高版本中将工作簿另存为CyclePlot.xlsx。像这样设置工作表的顶部区域:

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

用于计算循环图的数据。

该表显示了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将以大括号{和}开始和结束公式。之后,将单元格复制到右侧,如图所示。

现在,您可以输入返回平均值和趋势的公式。这样做,将以下部分添加到您的工作表:

如何在Excel中创建周期图以绘制季节性销售数据-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列中的每列都是单独选择的。也就是说,它们是不连续的。

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

您可以单独选择此表中的每一列,而不要选择一个大区域。

现在,您需要为此选择指定一个范围名称。为此,请在New Excel(2007版及更高版本)中选择“公式”,“定义的名称”,“定义的名称”。

在对话框中,输入PlotData作为名称,然后选择“确定”。

然后,与对PlotData范围所做的一样,在PlotAvg部分中不连续地选择12列,并将此选择定义为PlotAvg。对PlotTrend部分执行相同的操作,命名选择PlotTrend。在这两种情况下,请确保在数据列的上方和下方都包含灰色边框行。

最后,定义两个普通范围名称。定义DateText以引用范围C1:N1,如下图所示。然后定义DateValue以引用范围C2:N2。

步骤3:建立图表

现在已经准备好基础,您可以创建循环图。第一个数据系列将仅显示图1至4中所示的X轴标签。其他三个数据系列将显示图3中所示的行。

首先,选择范围C1:N2,如下所示。

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

要绘制的数据的开始。

要在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。复制此范围并将其粘贴到您的图表中。

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

当您这样做时,您的图表将看起来像这样。

此时,隐藏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)

现在,图表开始看起来像图表。

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

...您的图表将如下所示。

好消息是该图表现在显示了循环图。坏消息是X轴标签已经弄乱了。

出现此问题的原因是,系列1(我们的日期标签)和系列2(我们的循环图)当前共享相同的X轴,但是它们的要求冲突。

要解决此问题,我们需要设置循环图系列以使用辅助轴。

首先,通过单击循环图线之一选择SERIES 2。按Ctrl + 1启动“属性”对话框,然后选择“系列选项”,“辅助轴”,“关闭”。

Excel通过忽略数据中除前12个值之外的所有值来“解决”轴问题,并将这些值与12个月的标签保持同步。

选择“布局”,“轴”,“轴”,“辅助水平轴”,“从左到右轴显示”。然后选择“布局”,“轴”,“轴”,“次要垂直轴”,“无”。

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

您的图表看起来像上面的图表。

在两个版本的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。

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

在这两种情况下,您可能都希望尝试使用颜色,线条粗细和标记。(最上面的示例包含模糊的粗线,因为Classic Excel自动添加了您需要删除的标记。)

要调整线条,请在图表中选择一条线条,按Ctrl + 1,然后调整将找到的相关设置。

另外,您可能想查看垂直网格线是否可以改善图表。要设置它们,请选择图表,然后:

在Classic Excel中,选择“图表”,“图表选项”,“网格线”。在“类别(X)轴”下,选中“主要网格线”。然后选择确定。

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

在新Excel中,选择“布局”,“轴”,“网格线”,“主垂直网格线”,“主要网格线”。

要增加或减少图表显示的数据年数,只需在PlotData部分的阴影灰色边框之间插入或删除数据行,然后根据需要在其他两个部分中调整公式的行。

分享到: 编辑:wangmin

就业培训申请领取
您的姓名
您的电话
意向课程
点击领取

环球青藤

官方QQ

扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群

绑定手机号

应《中华人民共和国网络安全法》加强实名认证机制要求,同时为更加全面的体验产品服务,烦请您绑定手机号.

预约成功

本直播为付费学员的直播课节

请您购买课程后再预约

环球青藤移动课堂APP 直播、听课。职达未来!

安卓版

下载

iPhone版

下载
环球青藤官方微信服务平台

刷题看课 APP下载

免费直播 一键购课

代报名等人工服务

课程咨询 学员服务 公众号

扫描关注微信公众号

APP

扫描下载APP

返回顶部