Excel作為數(shù)據(jù)分析師必備的數(shù)據(jù)工具之一，它的功能是十分強(qiáng)大的液荸。從數(shù)據(jù)界處理數(shù)據(jù)，到游戲界設(shè)計(jì)游戲脱篙，再到藝術(shù)界畫圖娇钱，Excel不斷地刷新著人們對它的認(rèn)知。

注：右圖為日本77歲的藝術(shù)家堀內(nèi)辰男在Excel Autoshape Contest大賽中的獲獎(jiǎng)作品

盡管Excel涉及的領(lǐng)域很廣绊困，但它在它的“本職工作”（表格處理）上尤為出色文搂。我們大都知道Excel在數(shù)據(jù)處理領(lǐng)域十分出色，但很少知道Excel在數(shù)據(jù)自動化監(jiān)控方面也能發(fā)揮巨大作用秤朗。大家平時(shí)使用的數(shù)據(jù)監(jiān)控平臺大都是由專業(yè)的程序員用編程語句編寫的煤蹭，本文將告訴你通過簡單的幾個(gè)公式我們也可以建立數(shù)據(jù)自動化監(jiān)控平臺。部分示例如下取视，我們可以通過選擇任意日期知道某段時(shí)間內(nèi)的銷售情況硝皂。

注：若此處圖片看不太清，可長按圖片保存下來看

上圖用到的函數(shù)主要是countifs作谭、sumifs以及averageifs三個(gè)函數(shù)稽物，接下來將詳細(xì)介紹如何做這個(gè)。源數(shù)據(jù)是取自某電商平臺2011-09/01-2011-09-20日的銷售明細(xì)數(shù)據(jù)折欠。（可在公眾號后臺發(fā)送“銷售數(shù)據(jù)”贝或，獲得此明細(xì)數(shù)據(jù)）

函數(shù)介紹?

1吼过、countifs函數(shù)

函數(shù)定義：用來計(jì)算多個(gè)區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。

函數(shù)用法：countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)

參數(shù)解釋：

criteria_range1為第一個(gè)需要計(jì)算其中滿足某個(gè)條件的單元格數(shù)目的單元格區(qū)域(簡稱條件區(qū)域)傀缩，criteria1為第一個(gè)區(qū)域中將被計(jì)算在內(nèi)的條件(簡稱條件)那先，其形式可以為數(shù)字、表達(dá)式或文本赡艰。例如售淡，條件可以表示為 48、"48"慷垮、">48" 揖闸、 "廣州" 或 A3;

同理，criteria_range2為第二個(gè)條件區(qū)域料身，criteria2為第二個(gè)條件汤纸，依次類推。最終結(jié)果為多個(gè)區(qū)域中滿足所有條件的單元格個(gè)數(shù)芹血。

countifs和countif(range贮泞，criteria)的區(qū)別：countifs是統(tǒng)計(jì)滿足多個(gè)條件的單元格的數(shù)量（包括一個(gè)條件），而countif是統(tǒng)計(jì)滿足一個(gè)條件的單元格的數(shù)量幔烛。一個(gè)條件時(shí)也可以用countifs啃擦，所以為了方便，我個(gè)人偏向于都用countifs函數(shù)饿悬。

案例詳解：

如下圖令蛉，求訂單量公式如下：=COUNTIFS(源數(shù)據(jù)!B:B,"<="&B2,源數(shù)據(jù)!B:B,">="&B1)。

目的是要求開始日期到結(jié)束日期之間的訂單量狡恬。由于訂單號是唯一值珠叔，均只有一條記錄。所以不需要去重弟劲，只需要限制日期條件即可計(jì)算出訂單量祷安。因此限定日期“≥開始日期”且“≤結(jié)束日期”，“&”符號是連接符函卒，源數(shù)據(jù)B列代表日期辆憔。

2、sumifs函數(shù)

函數(shù)定義：使用該函數(shù)可快速對多條件單元格求和

函數(shù)用法：sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

參數(shù)定義：

criteria_range1為計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域报嵌。criteria1為條件1，條件的形式為數(shù)字熊榛、表達(dá)式锚国、單元格引用或者文本，可用來定義將對criteria_range1參數(shù)中的哪些單元格求和玄坦。例如血筑，條件可以表示為32绘沉、">32"、B4豺总、"蘋果"车伞、或"32"。

同理喻喳，criteria_range2為第二個(gè)條件區(qū)域另玖，criteria2為第二個(gè)條件，依次類推表伦。最終結(jié)果為多個(gè)區(qū)域中滿足所有條件的單元格個(gè)數(shù)谦去。

sum_range?是需要求和的實(shí)際單元格。包括數(shù)字或包含數(shù)字的名稱蹦哼、區(qū)域或單元格引用鳄哭。忽略空白值和文本值。

sumifs和sumif(range纲熏，criteria妆丘，sum_range)的區(qū)別：

sumifs是求滿足多個(gè)條件的單元格的和，而sumif是統(tǒng)計(jì)滿足一個(gè)條件的單元格的和局劲。另外勺拣，sumifs和sumif用法上有區(qū)別，sumifs待求和列在第一個(gè)參數(shù)上容握，sumif待求和列在第三個(gè)參數(shù)上宣脉。一個(gè)條件時(shí)也可以用sumifs，所以為了方便剔氏，我個(gè)人偏向于都用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ù)是一個(gè)求平均值函數(shù)嫂沉，主要是用于返回多重條件所有單元格的平均值稽寒。

函數(shù)用法averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

參數(shù)解釋：

criteria_range1為計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。criteria1為條件1趟章，條件的形式為數(shù)字杏糙、表達(dá)式慎王、單元格引用或者文本，可用來定義將對criteria_range1參數(shù)中的哪些單元格求平均值宏侍。例如赖淤，條件可以是數(shù)字10、表達(dá)式">12"谅河、文本"上海發(fā)貨平臺" 或 C2咱旱。

同理，criteria_range2為第二個(gè)條件區(qū)域旧蛾，criteria2為第二個(gè)條件莽龟，依次類推。最終結(jié)果為多個(gè)區(qū)域中滿足所有條件的單元格個(gè)數(shù)锨天。

average_range是需要求平均值的實(shí)際單元格毯盈。包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用病袄。忽略空白值和文本值搂赋。

averageifs和averageif(range, criteria, [average_range])區(qū)別：

averageifs是求滿足多個(gè)條件的單元格的平均值，而averageif是統(tǒng)計(jì)滿足一個(gè)條件的單元格的和益缠。另外脑奠，averageifs和averageif用法上有區(qū)別，averageifs待求平均值列在第一個(gè)參數(shù)上幅慌，averageif待求平均值列在第三個(gè)參數(shù)上宋欺。一個(gè)條件時(shí)也可以用averageifs，所以為了方便胰伍，我個(gè)人偏向于都用averageifs函數(shù)齿诞。

案例詳解：

如下圖，求整體平均客單價(jià)金額公式如下：=AVERAGEIFS(源數(shù)據(jù)!G:G,源數(shù)據(jù)!B:B,"<="&B2,源數(shù)據(jù)!B:B,">="&B1)骂租。

目的是要求開始日期到結(jié)束日期之間的商品平均客單價(jià)祷杈。只需限制日期條件，得出滿足條件的值并求平均值即可渗饮。因此限定日期“≥開始日期”且“≤結(jié)束日期”但汞，“&”符號是連接符，源數(shù)據(jù)B列代表日期互站，源數(shù)據(jù)G列是銷售金額列私蕾。

4、絕對引用胡桃、混合引用以及相對引用

①定義

絕對引用：單元格中的絕對單元格引用(例如 $A$1)總是在指定位置引用單元格是目。如果公式所在單元格的位置改變，絕對引用保持不變标捺。如果多行或多列地復(fù)制公式懊纳，絕對引用將不作調(diào)整。默認(rèn)情況下亡容，新公式使用相對引用嗤疯，需要將它們轉(zhuǎn)換為絕對引用。例如闺兢，如果將單元格B2 中的絕對引用復(fù)制到單元格B3茂缚，則在兩個(gè)單元格中一樣，都是 $A$1屋谭。

混合引用：混合引用具有絕對列和相對行脚囊，或是絕對行和相對列。絕對引用列采用 $A1桐磁、$B1 等形式悔耘。絕對引用行采用 A$1、B$1 等形式我擂。如果公式所在單元格的位置改變衬以，則相對引用改變，而絕對引用不變校摩。如果多行或多列地復(fù)制公式看峻，相對引用自動調(diào)整，而絕對引用不作調(diào)整衙吩。例如互妓，如果將一個(gè)混合引用從 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的混合引用荡澎。為什么要這么用呢奋早？

這個(gè)區(qū)域單元格的目的是為了求產(chǎn)品X在X日的銷售金額疚漆，一個(gè)一個(gè)單元格地輸入公式是很費(fèi)時(shí)低效率的一種方法。這時(shí)用到絕對引用和混合引用之后，只需要在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é)無止境，終身成長除抛。