如何在Excel中创建周期图以绘制季节性销售数据-Excel学习网
【摘要】如果您有季节性销售或其他绩效指标,则周期图可以比传统图表技术更深入地了解您的绩效。如果您公司的销售是及时的,那么您可能已经看过如下
如果您有季节性销售或其他绩效指标,则周期图可以比传统图表技术更深入地了解您的绩效。
如果您公司的销售是及时的,那么您可能已经看过如下图的图表:
Excel中创建周期图以绘制季节性销售数据-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年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部分的阴影灰色边框之间插入或删除数据行,然后根据需要在其他两个部分中调整公式的行。
就业培训申请领取
环球青藤
官方QQ群扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群
Excel相关文章推荐
|Excel最新文章推荐
- Excel表格的基本操作-Excel学习网
- Excel求和公式常用的几种方式_Excelcn学习网
- 【分享篇】零基础学EXCEL小白到大神-Excel学习网
- 小白学Excel之操作中的常用技巧-Excel学习网
- 【小白必备】这9个Excel技巧你必须会! -Excel学习网
- 启动后使用共同年龄的Excel图表比较相同时间段的效果-Exc
- 绘制变化率以显示隐藏的业务绩效-Excel学习网
- 秒杀vlookup函数,Index+Match函数组合-Excel学习网
- Excel中如何活用INDEX和MATCH函数-INDEX-MATCH上-Excel学习网
- 如何开始使用条件格式-怎样使用Excel 2010条件格式-Excel学习网
刷题看课 APP下载
免费直播 一键购课
代报名等人工服务