office教程office教程

office办公学习网
office办公学习网(www.168361.com)是自己整理的一些文章,用于自己学习office使用,如果侵犯了您的版权,请联系我们删除。网站包含office/word/wps等使用技巧大全全集,office百科全书

EXCEl循环引用怎么查找处理查找领先指标-Excel学习网

 

      一切似乎都很简单...   要改善对销售或其他衡量指标的预测,您只需找到领先指标...与关键衡量指标高度相关但有时差的衡量指标。然后,您可以使用这些领先指标作为预测的基础。   但是,当您尝试使其全部工作时,这个简单的想法可能会成为巨大的挑战。  

Excel中的互相关,图表1例如,假设此图表中的蓝色Data1线显示您在广告上花费的钱。并假设红色的Data2行显示了您的销售额。  乍一看,您似乎真的需要更改广告策略,因为...  ...当您花费更多的广告费用时,您的销售额将大大下降,并且,  ...当您减少广告支出时,您的销售额就会大幅增长。

Excel中的互相关,图表1例如,假设此图表中的蓝色Data1线显示您在广告上花费的钱。并假设红色的Data2行显示了您的销售额。   乍一看,您似乎真的需要更改广告策略,因为...   ...当您花费更多的广告费用时,您的销售额将大大下降,并且,   ...当您减少广告支出时,您的销售额就会大幅增长。   如果您想更精确地进行分析,则可以使用Excel的CORREL函数来了解Data1和Data2的相关系数为-.50。也就是说,如下图所示,您的广告和销售价值在很大程度上呈负相关。   但是,这还不是故事的结局。   您花在广告上的钱可能是几个月后销售额的领先指标。该图显示了从该角度进行的分析...  

EXCEl循环引用怎么查找处理查找领先指标-Excel学习网

与Excel中两个月时滞的关联   在此,该表显示了与十一个不同时移相关的相关性。相关性最高的版本偏移+2个月。也就是说,已经计算了相关性,其中销售(Data2)比广告支出(Data1)提前了两个月。   也就是说,广告支出可能是两个月后销售业绩的良好领先指标。   但是,这种解释并不是唯一的一种解释,如下图所示:  

互相关结果的Excel图表   在这里,我们看到销售业绩可能是三个月后广告支出的领先指标。这可能是因为当销售额上升或下降时,市场营销部门决定在广告上花费更多或更少。   两种解释都得到高度相关性的支持。但是现实是您必须正确解释这样的分析告诉您的内容。   即使这样,分析也绝对可以为您提供决策依据的其他事实。因此,在发出该警告的情况下,让我们进行分析。   互相关数据示例互相关工作簿

  我的工作簿包含两个相关的工作表:数据和报表。   (您可以在这里下载工作簿。)   此图显示了数据工作表。   Date,Data1和Data2列包含显示的值。   DateText列包含一些公式,这些公式返回要在图表中显示的文本。这是显示的单元格的第一个公式:   E5:    = TEXT(B5,; mmm;)&CHAR(13)&;';&TEXT(B5,; yy;)   此公式的CHAR(13)节返回回车符。Excel不在E列中显示此字符。但是,当文本显示在图表中时,此字符会导致年份文本换行到月份文本下方的第二行。   NumRows单元格返回表中的行数。这样做是因为它使用了COUNT函数,该函数仅计算数字。我们将在此工作簿中的多个动态范围名称中引用此值。   这是该单元格的公式:   C1:    = COUNT(B:B)   要命名此单元格,请选择范围B1:C1,然后按Ctrl + Shift + F3或选择;公式;,;定义的名称;,;从选择中创建;以启动;创建名称;对话框。确保仅选中左列;然后选择确定。   另外,在;报告;工作表中,设置此处显示的两个单元格,然后使用;创建名称;对话框将名称Shift分配给单元格B1。(当我像Shift单元格一样设置一个具有设置的单元格时,通常会给它填充黄色。)

  设置动态范围名称**   自动执行互相关计算的关键是设置动态范围名称,这些名称将在输入更多数据时扩展,或者根据Shift值对数据进行移位。   使用NumRows单元格可以轻松设置动态范围名称,该名称会扩展为包括可能添加到上数据图中第25行以下的其他数据行。   要在下面定义名字,请首先将;日期;名称的公式复制到剪贴板。然后选择;公式;,;定义的名称;,;定义的名称;(或按Ctrl + Alt + F3)以启动;新名称;对话框。在;新名称;对话框中,输入; 日期; 作为名称;将复制的公式粘贴到;引用至编辑;框中;然后选择确定。   对其余的每个名称重复此过程。   日期数据 1数据 2  日期文本 = OFFSET(数据!$ B $ 4,1,0,NumRows,1) = OFFSET(数据!$ C $ 4,1,0,NumRows,1) = OFFSET(数据!$ D $ 4,1,0,NumRows,1 ) = OFFSET(数据!$ E $ 4,1,0,NumRows,1)   现在,您需要设置动态范围名称,以根据Shift值的符号而变化的方式移动它们引用的数据。使用;新名称;对话框来定义每个名称。   s.Data1N  s.Data1P  s.Data2N  s.Data2P  s.DateText1N  s.DateText1P  s.DateText2N  s.DateText2P
= OFFSET(数据1,-Shift,0,NumRows + Shift,1) = OFFSET(数据1,,0,NumRows -Shift,1) = OFFSET(数据2,0,0,NumRows + Shift,1) = OFFSET(数据2 ,Shift,0,NumRows-Shift,1) = OFFSET(DateText,-Shift,0,NumRows + Shift,1) = OFFSET(DateText,0,0,NumRows-Shift,1) = OFFSET(DateText,0,0 ,NumRows + Shift,1) = OFFSET(DateText,Shift,0,NumRows-Shift,1)   在这些范围名称中,; s;。表示名称正在 转移您的数据;; N;表示当Shift值为负时使用该名称;; P;表示当Shift值为正时使用该名称。   最终的动态范围名称是我们绘制并在计算中使用的名称。由于Excel错误在绘制以; c;(我们希望用于;图表;)的范围名称时存在问题,因此我们以; g;(用于; graph;)开始。   g.Data1  g.Data2  g.DateText1  g.DateText2
= IF(Shift <0,s.Data1N,s.Data1P) = IF(Shift <0,s.Data2N,s.Data2P) = IF(Shift <0,s.DateText1N,s.DateText1P) = IF(Shift < 0,s.DateText2N,s.DateText2P)   现在,在定义了动态名称的情况下,您可以建立一个数据表来计算互相关。   设置Excel数据表** 该图显示了整个报告区域。J和K列中的数据表计算互相关值。  

与Excel数据表的互相关   要设置数据表,首先输入在J7:J17范围内显示的移位值。然后在显示的单元格中输入此公式:   K6:    = CORREL(g.Data1,g.Data2)   该公式返回所示两个动态范围的相关系数。当然,这些范围会随时间变化...取决于Shift单元格中的值。   接下来,选择范围J6:K17,然后选择;数据;,;数据工具;,;假设分析;,;数据表;以启动;数据表;对话框。在该对话框的;列输入单元格;中,输入...   = Shift   ...这是包含当前值的单元格名称,该当前值表示数据移动的月数。   然后,选择;确定;后,Excel将J列的每个选定值放入Shift单元格,在K6单元格中计算公式,然后将公式的结果写入K列的相邻单元格中。   最后,要将范围名称分配给数据表以便于参考,请输入表底部显示的文本;选择范围J7:K18; 按Ctrl + Shift + F3; 在对话框中,确保仅选中;底部行;;然后选择确定。   设置班次的数据验证列表** Shift单元格使用Excel的;数据验证列表;功能仅允许数据表中列出的Shift值。要进行设置,请首先选择单元格B1。

  现在选择数据,数据工具,数据验证,数据验证。在;设置;选项卡中,在;允许;部分中选择;列表;。对于Source,输入...  = ShiftVal ...,这是您分配给数据表中移位值列表的范围名称。

本文链接:http://168361.com/excel/1095.html

版权声明:内容均来源于互联网 如有侵权联系删除

最后编辑于:2016-10-15作者: excel教程

让excel自动调整单元格行高和宽
Excel2010中自定义格式看到的结果如何显示为真实值