文源：數據不吹牛

這篇文章選擇性匯總了EXCEL的常用且重點的模塊和公式，用作內部員工EXCEL基礎操作培訓，以幫助表格基礎薄弱的朋友快速熟悉常用操作，提升工作效率。現將內容分享，作為數據分析基礎的第一篇。

所有公式均結合實例（本節課以小例子為主），講為輔，練為主，實例數據附在文章最後，基礎紮實童鞋可以直接跳過，其他同鞋可以當做回顧和複習。這，將是後面數據分析的公式(EXCEL）基礎

文章略長，大家可以先馬後看，當然更重要的是實踐。

P1 基礎操作模塊：

1.1、數據透視表：

開篇神器必談透視表，它可以說是EXCEL的重武器了，殺傷力爆表。

百度定義是這樣的：數據透視表（Pivot Table）是一種交互式的表，可以進行某些計算，如求和與計數等。

Emmm，各位看完之後有沒有一個特別清晰的概念呢。反正我是沒有的。

我覺得數據透視表就是一個快速分組，並基於分組個性化計算的神器。

下面我們結合數據來一探究竟：

源數據是2017年7月-12月的銷售數據，每一行代表一筆交易，數據涉及5個關鍵欄位」訂單序列」，「日期」，「省份」，「城市」，銷售額「。如果我們想知道每個月，每個省份銷售額是多少該怎麼辦呢？

在學會透視表之前我會靠著縝密的思維和堅韌的毅力人肉計算。掌握透視表之後我發現當初縝密的思維和堅韌的毅力都是傻逼的表現。

喏，我們先選中所有列，在插入模塊選中「數據透視表」

接著就是選擇數據透視表存放的區域，默認是新工作表，大家在實踐中也可選擇現有工作表的區域。

始的透視表什麼都沒有，大家注意右側的「數據透視表欄位」區域，這裡是控制透視表的核心地帶。

我們的問題是「計算每個月，每個省份的銷售額」，那就是按照「月」和「省份」來進行分組了。

以哪個欄位分組，就將哪個欄位拖到行或者列，像下面這樣：

左側數據透視表結構區域隨著我們的拖動發生了變化，剛才我們把日期拖動到行，把省份移動到列，果然，數據透視表布局和我們操作一模一樣：

等等！我們是想知道每個月的銷售情況，為什麼這裡出現的是「年月日 時分秒」的格式？

那是因為，我們源數據格式是醬紫的，數據透視表分組邏輯是判斷是否唯一，如果唯一則單獨分為一行（或一列），想要把行標籤的日期格式變成月的維度，也HIN簡單。

我們選擇行標籤的單元格，右鍵選擇「創建組」：

點擊創建組之後會出現如下選項卡：

起始時間默認是源數據中最早和最晚時間，這裡不用更改，「步長」就是選擇以什麼時間維度去分組，我們想以月的維度創建分組，所以選擇「月」

這透視表分組，如你所願了，行是月份，列是省份。

分組完了，下面就是個性化計算，我們要計算涉及到的核心欄位是銷售額，在已經分好組的情況下，只需要把銷售額欄位拖到值的位置：

數據透視表隨之改變，大功告成。。。了嗎？

別急，大功只差一步，大家注意，我們剛才把銷售額拖動到值的位置，默認是「計數項」，也就是說，數據透視表現在顯示的每個值，指的是訂單數量，如果要計算銷售額，要再點擊「銷售額」欄位，

進入「值欄位設置」，

這裡的」計算類型「是個性化計算的核心了，選擇」求和「，我們就得到各月各省的銷售額總和，」平均值「就是各月各省銷售額平均值，最大值、最小值依然。（我們最常用的也就是這幾個）

最後才算大功告成：

各省、各月銷售額，一目了然。

1.2、分列：

很多時候，我們拿到的源數據某一列是按一定規律混雜的，而我們需要把它分成多個列，從而有側重的分析。

假如我們從資料庫中導出的數據是這樣的：

省-市混在一起，正常分析我們當然需要將省和市拎出來單獨分析，很簡單，選中源數據所在的列，點擊「數據」選項卡，選擇「分列」

這時候會蹦出分列的邏輯

第一種是按照分隔符號分列，

規性的符號有Tab鍵、分號、逗號，這裡我們分列的依據是中文波折號，所以勾選其他，手動輸入「——」，需要注意，上面源數據里，中文波折號是兩個短線構成，而這裡手動輸入最多只能輸入一條短線：

輸入之後我們會看到源數據列已經被分成3列（系統默認按照單個短線劃分，省—空行—市），正常情況我們是想分成兩列的，只需要勾選「連續分隔符號視為單個處理」即可。

有一種分列邏輯是按照固定長度，適用於規律非常明確的源數據，只需要自己移動分割線的位置，就能實現源數據的自定義分列：

結果就是把省的名稱和」省「字分成單獨兩列。

1.3、刪除重複項：

顧名思義，就是刪掉重複的項，這個項指的是行。

選中數據，點擊「數據」選項卡下的「刪除重複項」

彈出刪除介面：

默認是全選，但一定要慎重，假如我們單勾選A，就是只判斷A列中的值是否重複，若重複則刪去（單選B則刪B），這裡我們選單選A嘗試，

結果反饋：

刪除後的數據：

源數據中，第6行杭州的鋼鐵俠和北京的鋼鐵俠都被刪除了，畢竟鋼鐵俠只有一個。

但是！鋼鐵俠只有一個，並不妨礙我前室友曾自詡「窮版鋼鐵俠」啊，同理，杭州的鋼鐵俠可能和北京的鋼鐵俠並不是一個人。

因此，需要同時判斷姓名和城市，如果都重複才會刪除，只有一個重複則保留。要實現這個邏輯，只需要按照默認勾選，同時選A和B就可以了，結果如下：

OKAY~That is it!

P2 函數模塊：

二、字符串相關：

2.1 LEN、LENB

LEN（字符串）和LENB（字符串）是倆兄弟，他們都是用來衡量目標字符串長度的，但度量維度有所不同。

簡單來說，英文和數字的話，用LEN(TEXT)和LENB(TEXT)得到的數字是一樣的，而漢字，LEN(TEXT)中，一個漢字是1個長度，LENB(TEXT)則是2個。

2.2 TRIM和SUBSTITUDE

上面兩個函數專用於清除空格，只是他們清除的空格位置不同。

TRIM(單元格)清除的是目標單元格前後的空格，

而SUBSTITUDE(單元格）清除了目標所有空格，包括字符串中間的空格。

2.3 CONCATENATE 和 &

常用於連接多個單元格內容：

現在有這幾個單元格

我們想把A8,A9,A10單元格中的內容連接起來，很簡單，用CONCATENATE 或者 & （他們實現的是一樣的連結效果）：

2.4 LEFT,RIGHT

他們語法邏輯是一樣的，拿LEFT來說，他有兩個參數

LEFT(TEXT,NUM)，第一個參數輸入你要提取內容所在的位置（單元格），第二個參數是一個數字，也就是你想要從左邊開始，提取多少位，LEFT（text,3），就是從左邊起，提取3個字符，RIGHT(TEXT,3)是從右邊起，提取3個，下面是一個簡單的例子：

我們想要分別提取A13單元格，左邊3個字符，右邊5個字符：

2.5 FIND,SEARCH,MID

上面LEFT和RIGHT是很粗暴的提取方法，而MID就顯得更加溫婉和靈活了，

有一串這樣的文本，而我們只想要提取其中的數字部分，該怎麼做呢？

很簡單，輸入「=MID(TEXT,4,4)」即可，MID有3個參數，第一個參數依然是目標單元格，第二個參數規定了從第幾個字符開始提取，第三個參數是說提取幾位。上面的公式是說我們從第4個字符開始，提取其後的4位，結果如下：

咳，提取問題加大難度：

假如我們想要提取上面「省-市」單元格中的城市部分，怎麼辦呢？（比如武漢市、杭州市..）

首先你想到了MID函數，很棒！但是MID需要指定從第幾個字符開始，這裡「黑龍江省」和其他省長度不一樣，不能夠硬性指定從第幾個字符開始，也不能強制性指定截取多少位，如果有個靈活查找固定字符出現位置的函數，我們MID就可以用了。這個時候，FIND和SEARCH函數閃亮登場！

FIND（要查找什麼，TEXT，從第幾個開始）和SEARCH函數都有3個參數，第一個參數是我們想要查找的內容，這裡也就是「省」，第二個參數是在哪裡查找，即目標單元格，我們以A28為例，第三個參數是從第幾個開始查找，可以根據需要設置，此處我們設置為1。對應函數和結果如下：

到這一步，MID函數第一個參數（提取誰）有了，第二個參數（從哪裡開始截取）也有了，還差一個截取長度設定。仔細觀察目標函數，發現規律了嗎？沒錯，我們可以再次利用FIND或SEARCH函數，找到「市」出現的位置，用市出現的位置減去省出現的位置，不就是我們要截取的長度了？

注意，MID（TEXT,從哪裡開始，截取幾個），我們剛才拿到「省」的位置，還需要加1，才是正確的開始位置。到此，3個寶石在手（參數），MID函數打了個響指，喏，就是這樣：

FIND和SEARCH函數語法相近，需要注意的是FIND區分大小寫，SEARCH不區分，舉個簡單的栗子：

用FIND查找「D」，會嚴格找到大寫的「D"，而SEARCH不區分大小寫，遇到小寫的"d"就停止了搜索。

現在，你掌握了LEFT,RIGHT,MID,FIND,SEARCH，結合源數據打個響指試試唄：）。

三、日期函數：

3.1 時間函數：YEAR,MONTH,DAY,WEEKDAY,HOUR,MINUTE,SECOND

上面7個公式，其實本質都是一樣的，那就是獲取目標日期的對應模塊。

比如year(時間)得到的就是年份，month(時間)會返回月份，minute(時間)得到具體的分鐘數，second（時間）亦然。至於weekday嘛有點特殊，他有兩個參數：

第一個參數和前面介紹的函數一樣，就是目標時間，後面的參數選項比較多了，大家可以嘗試一下：

咳，同志們，不要被參數所迷惑，我們使用weekday是想知道目標時間是星期幾，這個星期幾我們習慣是從星期一開始算的，默認選擇2就OK。

下面是一個小例子：

上述這些GUYS在實踐中常用於構建輔助列。

3.2 DAYS

DAYS（結束日期，開始日期），輸入結束日期和開始日期，DAYS函數會計算返回兩個日期的相差天數：

四、邏輯與條件判斷：

4.1 AND,OR

AND（參數1，參數2，..)，AND參數個數不限，每個參數是一個判斷，比如(A1>0)，每個判斷回返回一個TRUE（A1確實大於0）或者FALSE（A1小於等於0），如果每一個參數返回的都是TRUE，AND會返回一個TRUE，如果有一個返回FALSE，AND則返回FALSE。

OR用法和AND一樣，不同的是，只有當所有的參數返回FALSE，OR函數才會最終返回FALSE，否則會返回TRUE。

概括來說，

AND是（參數）全為真（TRUE）時才為真（TRUE)

OR是（參數）全為假（FALSE）時才為假(FALSE）。

他們通常結合IF條件判斷函數使用。

4.2 IF

IF(判斷條件，如果為真執行的操作，如果為假執行的操作），

一個簡單例子：IF("數學>90「，」優秀「，」不夠優秀「)，第一個參數會判斷數學是否大於90分，如果大於就返回TRUE，程序會自動執行第二個參數裡面的指令，這裡是顯示」優秀「，否則則執行第三個參數（FALSE）時的指令。

簡單嵌套一下：IF("數學">90,"優秀",IF("數學">80,"良好",IF(」數學">60，「及格」,「不及格"）

別暈，一層一層看，顯示判斷數學是否大於90分，大於就是優秀，否則再判斷是否大於80（小於90的情況下），是則返回「良好",不然繼續判斷是否大於60，大於60是及格，小於就是不及格。

需要注意的是，IF函數可以不斷嵌套。

IF大哥出鏡率很高，我們再來引入一個情景集合AND函數鞏固一下，我們這裡有ABCDE五位男嘉賓，有顏值和身材兩個打分維度，1的話代表公認具備，0的話代表不具備，

打分後的結果是這個樣子：

我們需要判斷每個男嘉賓屬於什麼類型，如果顏值和身材並存（都是1），自然是男神了，如果顏值1身材0，暫且歸為」靠臉吃飯「，如果只有身材沒有顏，就是」肌肉男「，最後，如果什麼都沒有，別灰心，至少還是個好人。

要完成上述打分，IF結合AND可以很輕鬆的搞定：

五、匹配：

匹配函數很多種，只有VLOOKUP最受寵。

VLOOKUP(匹配的參數，想要在哪個區域匹配，返回匹配區域的第多少列，是否精確查找）

函數構成很難懂，絕知此事要躬行：

現在有兩個區域，區域1一個是包含產品ID，銷量，銷售額

區域2一個是供應商表，有ID，最早生成時間，供應商三個欄位，還缺少銷量，銷售額兩個欄位：

我們發現兩個區域的表有一個交集，他們有共同的產品ID，因此，我們可以通過ID作為紐帶，將區域1裡面的銷量、銷售額數據匹配到區域2中。

先做銷量，我們在J2單元格輸入如下公式：

展開解釋，首先我們想要根據G2單元格的ID——SW0001進行匹配，第一個參數就是G2，

第二步，是想根據ID匹配獲取表1區域的銷量欄位，所以在第二個參數位置輸入A:C（選擇A到C列所有數據），選定待匹配的數據列；

第三步，就是輸入我們想要返回的列數（這裡是銷量），從匹配列（ID）數起，ID本身是第一列，銷量是第二列，因此我們再第三個參數輸入2；

最後，就是選擇匹配方式，精確匹配還是近似匹配，絕大部分情況下我們默認精確匹配，因此輸入FALSE或者0。

這樣，根據ID我們就匹配到了對應ID的銷量，銷售額公式只需要改變返回的列數即可：

至此，表2的區域獲取了銷量、銷售額相關數據：

註：這裡兩張表放在一起是為了方便演示，實際中表格一般是獨立的，函數都支持跨表格選擇對應參數。

六、計算統計相關：

6.1 COUNT/COUNTIF

COUNT(區域)函數，是統計目標區域有多少個數值類型的單元格，拿下面數據為例：

=COUNT(A:C)，就是統計A、B、C列所有單元格，有多少個數值類型的,結果顯而易見是20（銷量和銷售額都是數值類型）。

COUNT函數還有一群表兄弟:COUNTA(區域)是統計所有非空單元格個數，COUNTBLANK(區域)統計空白單元格個數，他們不太常用，就不展開贅述。

下面重點講一下COUNTIF()函數。

他可以統計區域內，符合我們設置條件的單元格個數。

COUNTIF(區域，條件)由2個參數構成，第一個是要統計的區域，第二個是條件設置，比如我們想要統計ID為「SW0001」的產品出現了多少次，輸入

即可，還有一個小技巧

上面數據中，"SW0001"在F2單元格，在COUNTIF函數第二個參數直接輸入他所在的位置F2，等同於輸入了「=SW0001"。

6.2 SUM和SUMIF

SUM函數很好懂，常用於對某一區域求和，SUM（區域）就是對該區域內所有數值求和。

SUMIF用法稍微複雜點，SUMIF(匹配列，條件，求和列）

直接上例子

左邊是之前的數據，產品ID存在重複，我們想計算出每個ID的銷量之和（補全右邊銷量區域），以F2為例，直接輸入SUMIF(A:A,F2,B:B)，

第一個參數是被匹配區域的匹配列，簡單來說，你想通過F列的ID，來匹配A列的ID（再獲取A附近的銷量列），那麼A:A就是被匹配區域的匹配列；

第二個參數是條件，以F2為例，參數輸入F2，等同於"=SW0001"，當A列產品ID等於"SW0001「時，條件生效。

第三個參數規定了求和列，是對銷量進行匯總，自然就是B:B。

6.3 MAX/MIN/AVERAGE/MEDIAN/STD

最後這幾個函數用法都很簡單，只需要選定區域，就能計算對應的結果：

MAX 最大值，MIN最小值

AVERAGE 平均值，MEDIAN則是中位數

STD（2016版是STDEV.P）計算的是樣本總體標準差。

以上，雖沒有做到面面俱到，但已經涉及了大部分工作中常用的操作和公式。整理不易，覺得有用的點個收藏，轉發哈~

不知道會不會有人耐著性子看到這個地方，能一口氣看下來的毅力絕對能成大事！

且受我一拜！

註：文中excel素材可後台私信回復「excel」獲得！