Excel作為數(shù)據(jù)分析師必備的數(shù)據(jù)工具之一，它的功能是十分強(qiáng)大的。從數(shù)據(jù)界處理數(shù)據(jù)，到游戲界設(shè)計游戲，再到藝術(shù)界畫圖，Excel不斷地刷新著人們對它的認(rèn)知。
注：右圖為日本77歲的藝術(shù)家堀內(nèi)辰男在Excel Autoshape Contest大賽中的獲獎作品
盡管Excel涉及的領(lǐng)域很廣，但它在它的“本職工作”（表格處理）上尤為出色。我們大都知道Excel在數(shù)據(jù)處理領(lǐng)域十分出色，但很少知道Excel在數(shù)據(jù)自動化監(jiān)控方面也能發(fā)揮巨大作用。大家平時使用的數(shù)據(jù)監(jiān)控平臺大都是由專業(yè)的程序員用編程語句編寫的，本文將告訴你通過簡單的幾個公式我們也可以建立數(shù)據(jù)自動化監(jiān)控平臺。部分示例如下，我們可以通過選擇任意日期知道某段時間內(nèi)的銷售情況。
注：若此處圖片看不太清，可長按圖片保存下來看
上圖用到的函數(shù)主要是countifs、sumifs以及averageifs三個函數(shù)，接下來將詳細(xì)介紹如何做這個。源數(shù)據(jù)是取自某電商平臺2011-09/01-2011-09-20日的銷售明細(xì)數(shù)據(jù)。（可在公眾號后臺發(fā)送“銷售數(shù)據(jù)”，獲得此明細(xì)數(shù)據(jù)）
函數(shù)介紹?
1、countifs函數(shù)
函數(shù)定義：用來計算多個區(qū)域中滿足給定條件的單元格的個數(shù)。
函數(shù)用法：countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
參數(shù)解釋：
criteria_range1為第一個需要計算其中滿足某個條件的單元格數(shù)目的單元格區(qū)域(簡稱條件區(qū)域)，criteria1為第一個區(qū)域中將被計算在內(nèi)的條件(簡稱條件)，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 48、"48"、">48" 、 "廣州" 或 A3;
同理，criteria_range2為第二個條件區(qū)域，criteria2為第二個條件，依次類推。最終結(jié)果為多個區(qū)域中滿足所有條件的單元格個數(shù)。
countifs和countif(range，criteria)的區(qū)別：countifs是統(tǒng)計滿足多個條件的單元格的數(shù)量（包括一個條件），而countif是統(tǒng)計滿足一個條件的單元格的數(shù)量。一個條件時也可以用countifs，所以為了方便，我個人偏向于都用countifs函數(shù)。
案例詳解：
如下圖，求訂單量公式如下：=COUNTIFS(源數(shù)據(jù)!B:B,"<="&B2,源數(shù)據(jù)!B:B,">="&B1)。
目的是要求開始日期到結(jié)束日期之間的訂單量。由于訂單號是唯一值，均只有一條記錄。所以不需要去重，只需要限制日期條件即可計算出訂單量。因此限定日期“≥開始日期”且“≤結(jié)束日期”，“&”符號是連接符，源數(shù)據(jù)B列代表日期。
2、sumifs函數(shù)
函數(shù)定義：使用該函數(shù)可快速對多條件單元格求和
函數(shù)用法：sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
參數(shù)定義：
criteria_range1為計算關(guān)聯(lián)條件的第一個區(qū)域。criteria1為條件1，條件的形式為數(shù)字、表達(dá)式、單元格引用或者文本，可用來定義將對criteria_range1參數(shù)中的哪些單元格求和。例如，條件可以表示為32、">32"、B4、"蘋果"、或"32"。
同理，criteria_range2為第二個條件區(qū)域，criteria2為第二個條件，依次類推。最終結(jié)果為多個區(qū)域中滿足所有條件的單元格個數(shù)。
sum_range?是需要求和的實際單元格。包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用。忽略空白值和文本值。
sumifs和sumif(range，criteria，sum_range)的區(qū)別：
sumifs是求滿足多個條件的單元格的和，而sumif是統(tǒng)計滿足一個條件的單元格的和。另外，sumifs和sumif用法上有區(qū)別，sumifs待求和列在第一個參數(shù)上，sumif待求和列在第三個參數(shù)上。一個條件時也可以用sumifs，所以為了方便，我個人偏向于都用sumifs函數(shù)。
案例詳解：
如下圖，求銷售數(shù)量公式如下：=SUMIFS(源數(shù)據(jù)!F:F,源數(shù)據(jù)!B:B,"<="&B2,源數(shù)據(jù)!B:B,">="&B1)。
目的是要求開始日期到結(jié)束日期之間的商品銷售數(shù)量。只需限制日期條件，得出滿足條件的值并求和即可。因此限定日期“≥開始日期”且“≤結(jié)束日期”，“&”符號是連接符，源數(shù)據(jù)B列代表日期，源數(shù)據(jù)F列是銷售數(shù)量列。
3、averageifs
函數(shù)定義：averageifs函數(shù)是一個求平均值函數(shù)，主要是用于返回多重條件所有單元格的平均值。
函數(shù)用法averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
參數(shù)解釋：
criteria_range1為計算關(guān)聯(lián)條件的第一個區(qū)域。criteria1為條件1，條件的形式為數(shù)字、表達(dá)式、單元格引用或者文本，可用來定義將對criteria_range1參數(shù)中的哪些單元格求平均值。例如，條件可以是數(shù)字10、表達(dá)式">12"、文本"上海發(fā)貨平臺" 或 C2。
同理，criteria_range2為第二個條件區(qū)域，criteria2為第二個條件，依次類推。最終結(jié)果為多個區(qū)域中滿足所有條件的單元格個數(shù)。
average_range是需要求平均值的實際單元格。包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用。忽略空白值和文本值。
averageifs和averageif(range, criteria, [average_range])區(qū)別：
averageifs是求滿足多個條件的單元格的平均值，而averageif是統(tǒng)計滿足一個條件的單元格的和。另外，averageifs和averageif用法上有區(qū)別，averageifs待求平均值列在第一個參數(shù)上，averageif待求平均值列在第三個參數(shù)上。一個條件時也可以用averageifs，所以為了方便，我個人偏向于都用averageifs函數(shù)。
案例詳解：
如下圖，求整體平均客單價金額公式如下：=AVERAGEIFS(源數(shù)據(jù)!G:G,源數(shù)據(jù)!B:B,"<="&B2,源數(shù)據(jù)!B:B,">="&B1)。
目的是要求開始日期到結(jié)束日期之間的商品平均客單價。只需限制日期條件，得出滿足條件的值并求平均值即可。因此限定日期“≥開始日期”且“≤結(jié)束日期”，“&”符號是連接符，源數(shù)據(jù)B列代表日期，源數(shù)據(jù)G列是銷售金額列。
4、絕對引用、混合引用以及相對引用
①定義
絕對引用：單元格中的絕對單元格引用(例如 $A$1)總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對引用保持不變。如果多行或多列地復(fù)制公式，絕對引用將不作調(diào)整。默認(rèn)情況下，新公式使用相對引用，需要將它們轉(zhuǎn)換為絕對引用。例如，如果將單元格B2 中的絕對引用復(fù)制到單元格B3，則在兩個單元格中一樣，都是 $A$1。
混合引用：混合引用具有絕對列和相對行，或是絕對行和相對列。絕對引用列采用 $A1、$B1 等形式。絕對引用行采用 A$1、B$1 等形式。如果公式所在單元格的位置改變，則相對引用改變，而絕對引用不變。如果多行或多列地復(fù)制公式，相對引用自動調(diào)整，而絕對引用不作調(diào)整。例如，如果將一個混合引用從 A2 復(fù)制到 B3，它將從 =A$1 調(diào)整到 =B$1。
相對引用：公式中的相對單元格引用(例如 A1)是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復(fù)制公式，引用會自動調(diào)整。默認(rèn)情況下，新公式使用相對引用。例如，如果將單元格?B2 中的相對引用復(fù)制到單元格?B3，將自動從 =A1 調(diào)整到 =A2。
②案例詳解：
絕對引用和混合引用
產(chǎn)品A在2011-09-01日的銷售金額公式為：=SUMIFS(源數(shù)據(jù)!$G:$G,源數(shù)據(jù)!$B:$B,"="&H$14,源數(shù)據(jù)!$D:$D,$F15)。公式中的：“源數(shù)據(jù)!$G:$G”、“源數(shù)據(jù)!$B:$B”，“源數(shù)據(jù)!$D:$D”是對源數(shù)據(jù)中G列、B列以及D列絕對引用，“H$6”和“$F15”是對單元格H6和F15的混合引用。為什么要這么用呢？
這個區(qū)域單元格的目的是為了求產(chǎn)品X在X日的銷售金額，一個一個單元格地輸入公式是很費時低效率的一種方法。這時用到絕對引用和混合引用之后，只需要在H15輸入公式后，拉動H15單元格右下角的十字填滿目標(biāo)區(qū)域即可。在這里，絕對引用在公式中相當(dāng)于固定列或者固定單元格了。混合引用在公式中相當(dāng)于固定單元格“橫移動豎移不動”和“豎移動橫移不動”。
H15單元格的目的是為了求產(chǎn)品A在2011-09-01日的銷售金額。只需限制：【源數(shù)據(jù)!$B:$B,"="&H14】（也可【源數(shù)據(jù)!$B:$B,H14】），【源數(shù)據(jù)!$D:$D,F15】。（源數(shù)據(jù)B列是日期，源數(shù)據(jù)D列是產(chǎn)品）
豎向的：H16單元格則為：【源數(shù)據(jù)!$B:$B,"="&H14】，【源數(shù)據(jù)!$D:$D,F16】；H17單元格則為：【源數(shù)據(jù)!$B:$B,"="&H14】，【源數(shù)據(jù)!$D:$D,F17】... ...；
橫向的：I15單元格則為：【源數(shù)據(jù)!$B:$B,"="&I14】，【源數(shù)據(jù)!$D:$D,F15】；J15單元格則為：【源數(shù)據(jù)!$B:$B,"="&J14】，【源數(shù)據(jù)!$D:$D,F15】... ...；
也就是我們產(chǎn)品類型是列變動，日期是行變動。最終H15單元格公式為：=SUMIFS(源數(shù)據(jù)!$G:$G,源數(shù)據(jù)!$B:$B,"="&H$14,源數(shù)據(jù)!$D:$D,$F15)。“H$14”代表固定單元格“14”不動，“H”動，即“橫移動豎移不動”，“$F15”代表固定單元格“F”不動，“15”動，即“豎移動橫移不動”。（絕對引用、混合引用以及相對引用的快捷鍵是:Fn+F4，電腦型號不一樣情況不一樣，有的電腦直接F4即可）
絕對引用和相對引用
產(chǎn)品A的銷售數(shù)量公式為：=SUMIFS(源數(shù)據(jù)!$F:$F,源數(shù)據(jù)!$B:$B,"<="&$B$2,源數(shù)據(jù)!$B:$B,">="&$B$1,源數(shù)據(jù)!$D:$D,數(shù)據(jù)看板!A15)。公式中的：“源數(shù)據(jù)!$F:$F”、“源數(shù)據(jù)!$B:$B”，“源數(shù)據(jù)!$D:$D”是對源數(shù)據(jù)中G列、B列以及D列絕對引用，“數(shù)據(jù)看板!A15”是對單元格H6和F15的相對引用引用。復(fù)制B15單元格粘貼到B16單元格，公式就會變成：=SUMIFS(源數(shù)據(jù)!$F:$F,源數(shù)據(jù)!$B:$B,"<="&$B$2,源數(shù)據(jù)!$B:$B,">="&$B$1,源數(shù)據(jù)!$D:$D,數(shù)據(jù)看板!A15)；復(fù)制B15單元格粘貼到C17單元格，公式就會變成：=SUMIFS(源數(shù)據(jù)!$F:$F,源數(shù)據(jù)!$B:$B,"<="&$B$2,源數(shù)據(jù)!$B:$B,">="&$B$1,源數(shù)據(jù)!$D:$D,數(shù)據(jù)看板!B16)。單元格隨著相對位置的相對變化，這就是相對引用。
總結(jié)建議? ? ? ???
建立數(shù)據(jù)自動化監(jiān)控平臺，首先先將需要監(jiān)控的指標(biāo)體系列出來（下面有建立數(shù)據(jù)指標(biāo)體系的具體文章輔助閱讀）；接著將這些指標(biāo)歸納排版列入Excel表中，通過上述公式整合運(yùn)算完成；最后添加輔助列表以及圖表，幫助更直觀的了解現(xiàn)況和發(fā)現(xiàn)問題。
歡迎前往關(guān)注數(shù)據(jù)寶典公眾號，更多數(shù)據(jù)分析知識分享，以及案例總結(jié)分享~~
在數(shù)據(jù)分析道路上，學(xué)無止境，終身成長。
