一切似乎都很簡單…要改善對銷售或其他衡量指標的預測，您只需找到領先指標…與關鍵衡量指標高度相關但有時差的衡量指標。然后，您可以使用這些領先指標作為預測的基礎。但是，當您嘗試使其全部工作時，這個簡單的想法可能會成為巨大的挑戰。
Excel中的互相關，圖表1例如，假設此圖表中的藍色Data1線顯示您在廣告上花費的錢。并假設紅色的Data2行顯示了您的銷售額。乍一看，您似乎真的需要更改廣告策略，因為……當您花費更多的廣告費用時，您的銷售額將大大下降，并且，…當您減少廣告支出時，您的銷售額就會大幅增長。如果您想更精確地進行分析，則可以使用Excel的CORREL函數來了解Data1和Data2的相關系數為-.50。也就是說，如下圖所示，您的廣告和銷售價值在很大程度上呈負相關。但是，這還不是故事的結局。您花在廣告上的錢可能是幾個月后銷售額的領先指標。該圖顯示了從該角度進行的分析…
與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.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…，這是您分配給數據表中移位值列表的范圍名稱。
