一切似乎都很簡單…要改善對銷售或其他衡量指標的預(yù)測，您只需找到領(lǐng)先指標…與關(guān)鍵衡量指標高度相關(guān)但有時差的衡量指標。然后，您可以使用這些領(lǐng)先指標作為預(yù)測的基礎(chǔ)。但是，當您嘗試使其全部工作時，這個簡單的想法可能會成為巨大的挑戰(zhàn)。
Excel中的互相關(guān)，圖表1例如，假設(shè)此圖表中的藍色Data1線顯示您在廣告上花費的錢。并假設(shè)紅色的Data2行顯示了您的銷售額。乍一看，您似乎真的需要更改廣告策略，因為……當您花費更多的廣告費用時，您的銷售額將大大下降，并且，…當您減少廣告支出時，您的銷售額就會大幅增長。如果您想更精確地進行分析，則可以使用Excel的CORREL函數(shù)來了解Data1和Data2的相關(guān)系數(shù)為-.50。也就是說，如下圖所示，您的廣告和銷售價值在很大程度上呈負相關(guān)。但是，這還不是故事的結(jié)局。您花在廣告上的錢可能是幾個月后銷售額的領(lǐng)先指標。該圖顯示了從該角度進行的分析…
與Excel中兩個月時滯的關(guān)聯(lián)在此，該表顯示了與十一個不同時移相關(guān)的相關(guān)性。相關(guān)性最高的版本偏移+2個月。也就是說，已經(jīng)計算了相關(guān)性，其中銷售（Data2）比廣告支出（Data1）提前了兩個月。也就是說，廣告支出可能是兩個月后銷售業(yè)績的良好領(lǐng)先指標。但是，這種解釋并不是唯一的一種解釋，如下圖所示：
互相關(guān)結(jié)果的Excel圖表在這里，我們看到銷售業(yè)績可能是三個月后廣告支出的領(lǐng)先指標。這可能是因為當銷售額上升或下降時，市場營銷部門決定在廣告上花費更多或更少。兩種解釋都得到高度相關(guān)性的支持。但是現(xiàn)實是您必須正確解釋這樣的分析告訴您的內(nèi)容。即使這樣，分析也絕對可以為您提供決策依據(jù)的其他事實。因此，在發(fā)出該警告的情況下，讓我們進行分析。互相關(guān)數(shù)據(jù)示例互相關(guān)工作簿
我的工作簿包含兩個相關(guān)的工作表：數(shù)據(jù)和報表。（您可以在這里下載工作簿。）此圖顯示了數(shù)據(jù)工作表。Date，Data1和Data2列包含顯示的值。DateText列包含一些公式，這些公式返回要在圖表中顯示的文本。這是顯示的單元格的第一個公式：E5： = TEXT（B5，“ mmm”）＆CHAR（13）＆“’”＆TEXT（B5，“ yy”）此公式的CHAR（13）節(jié)返回回車符。Excel不在E列中顯示此字符。但是，當文本顯示在圖表中時，此字符會導(dǎo)致年份文本換行到月份文本下方的第二行。NumRows單元格返回表中的行數(shù)。這樣做是因為它使用了COUNT函數(shù)，該函數(shù)僅計算數(shù)字。我們將在此工作簿中的多個動態(tài)范圍名稱中引用此值。這是該單元格的公式：C1： = COUNT（B：B）要命名此單元格，請選擇范圍B1：C1，然后按Ctrl + Shift + F3或選擇“公式”，“定義的名稱”，“從選擇中創(chuàng)建”以啟動“創(chuàng)建名稱”對話框。確保僅選中左列；然后選擇確定。另外，在“報告”工作表中，設(shè)置此處顯示的兩個單元格，然后使用“創(chuàng)建名稱”對話框?qū)⒚QShift分配給單元格B1。（當我像Shift單元格一樣設(shè)置一個具有設(shè)置的單元格時，通常會給它填充黃色。）
設(shè)置動態(tài)范圍名稱**自動執(zhí)行互相關(guān)計算的關(guān)鍵是設(shè)置動態(tài)范圍名稱，這些名稱將在輸入更多數(shù)據(jù)時擴展，或者根據(jù)Shift值對數(shù)據(jù)進行移位。使用NumRows單元格可以輕松設(shè)置動態(tài)范圍名稱，該名稱會擴展為包括可能添加到上數(shù)據(jù)圖中第25行以下的其他數(shù)據(jù)行。要在下面定義名字，請首先將“日期”名稱的公式復(fù)制到剪貼板。然后選擇“公式”，“定義的名稱”，“定義的名稱”（或按Ctrl + Alt + F3）以啟動“新名稱”對話框。在“新名稱”對話框中，輸入“ 日期” 作為名稱；將復(fù)制的公式粘貼到“引用至編輯”框中；然后選擇確定。對其余的每個名稱重復(fù)此過程。日期數(shù)據(jù)1數(shù)據(jù)2日期文本= OFFSET（數(shù)據(jù)！$ B $ 4,1,0，NumRows，1）= OFFSET（數(shù)據(jù)！$ C $ 4,1,0，NumRows，1）= OFFSET（數(shù)據(jù)！$ D $ 4,1,0，NumRows，1 ）= OFFSET（數(shù)據(jù)！$ E $ 4,1,0，NumRows，1）現(xiàn)在，您需要設(shè)置動態(tài)范圍名稱，以根據(jù)Shift值的符號而變化的方式移動它們引用的數(shù)據(jù)。使用“新名稱”對話框來定義每個名稱。s.Data1Ns.Data1Ps.Data2Ns.Data2Ps.DateText1Ns.DateText1Ps.DateText2Ns.DateText2P
= OFFSET（數(shù)據(jù)1，-Shift，0，NumRows + Shift，1）= OFFSET（數(shù)據(jù)1，，0，NumRows -Shift，1）= OFFSET（數(shù)據(jù)2,0,0，NumRows + Shift，1）= OFFSET（數(shù)據(jù)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”。表示名稱正在 轉(zhuǎn)移您的數(shù)據(jù)；“ N”表示當Shift值為負時使用該名稱；“ P”表示當Shift值為正時使用該名稱。最終的動態(tài)范圍名稱是我們繪制并在計算中使用的名稱。由于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）現(xiàn)在，在定義了動態(tài)名稱的情況下，您可以建立一個數(shù)據(jù)表來計算互相關(guān)。設(shè)置Excel數(shù)據(jù)表**該圖顯示了整個報告區(qū)域。J和K列中的數(shù)據(jù)表計算互相關(guān)值。
與Excel數(shù)據(jù)表的互相關(guān)要設(shè)置數(shù)據(jù)表，首先輸入在J7：J17范圍內(nèi)顯示的移位值。然后在顯示的單元格中輸入此公式：K6： = CORREL（g.Data1，g.Data2）該公式返回所示兩個動態(tài)范圍的相關(guān)系數(shù)。當然，這些范圍會隨時間變化…取決于Shift單元格中的值。接下來，選擇范圍J6：K17，然后選擇“數(shù)據(jù)”，“數(shù)據(jù)工具”，“假設(shè)分析”，“數(shù)據(jù)表”以啟動“數(shù)據(jù)表”對話框。在該對話框的“列輸入單元格”中，輸入…= Shift…這是包含當前值的單元格名稱，該當前值表示數(shù)據(jù)移動的月數(shù)。然后，選擇“確定”后，Excel將J列的每個選定值放入Shift單元格，在K6單元格中計算公式，然后將公式的結(jié)果寫入K列的相鄰單元格中。最后，要將范圍名稱分配給數(shù)據(jù)表以便于參考，請輸入表底部顯示的文本；選擇范圍J7：K18; 按Ctrl + Shift + F3; 在對話框中，確保僅選中“底部行”；然后選擇確定。設(shè)置班次的數(shù)據(jù)驗證列表**Shift單元格使用Excel的“數(shù)據(jù)驗證列表”功能僅允許數(shù)據(jù)表中列出的Shift值。要進行設(shè)置，請首先選擇單元格B1。
現(xiàn)在選擇數(shù)據(jù)，數(shù)據(jù)工具，數(shù)據(jù)驗證，數(shù)據(jù)驗證。在“設(shè)置”選項卡中，在“允許”部分中選擇“列表”。對于Source，輸入…= ShiftVal…，這是您分配給數(shù)據(jù)表中移位值列表的范圍名稱。
