EXCEl循环援用怎样查找处理查找领先指标--英雄云拓展知识分享

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

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

与Excel中两个月时滞的关联在此，该表显示了与10一个不同时移相干的相干性。相干性最高的版本偏移+2个月。也就是说，已计算了相干性，其中销售（Data2）比广告支出（Data1）提早了两个月。也就是说，广告支出多是两个月后销售事迹的良好领先指标。但是，这类解释其实不是唯一的一种解释，以下图所示：

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

我的工作簿包括两个相干的工作表：数据和报表。（您可以在这里下载工作簿。）此图显示了数据工作表。Date，Data1和Data2列包括显示的值。DateText列包括一些公式，这些公式返回要在图表中显示的文本。这是显示的单元格的第一个公式：E5： = TEXT（B5，“ mmm”）＆CHAR（13）＆“’”＆TEXT（B5，“ yy”）此公式的CHAR（13）节返回回车符。Excel不在E列中显示此字符。但是，当文本显示在图表中时，此字符会致使年份文本换行到月份文本下方的第2行。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.Data1Ns.Data1Ps.Data2Ns.Data2Ps.DateText1Ns.DateText1Ps.DateText2Ns.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.Data1g.Data2g.DateText1g.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…，这是您分配给数据表中移位值列表的范围名称。