|
Excel是我們工作中經(jīng)常使用的一種工具，對于數(shù)據(jù)分析來說，這也是處理數(shù)據(jù)最基礎(chǔ)的工具。很多傳統(tǒng)行業(yè)的數(shù)據(jù)分析師甚至只要掌握Excel和SQL即可。 對于初學(xué)者而言，有時(shí)候并不需要急于苦學(xué)R語言等專業(yè)工具（當(dāng)然，學(xué)會了就是加分項(xiàng)），因?yàn)镋xcel涵蓋的功能足夠多，也有很多統(tǒng)計(jì)、分析、可視化的插件等，只不過我們平時(shí)處理數(shù)據(jù)的時(shí)候?qū)τ谠S多函數(shù)都不知道怎么用！ 對于Excel的進(jìn)階學(xué)習(xí)，主要分為兩塊——一個(gè)是數(shù)據(jù)分析常用的Excel函數(shù)，另一個(gè)是用Excel做一個(gè)簡單完整的分析。  關(guān)于函數(shù)：Excel的函數(shù)實(shí)際上就是一些復(fù)雜的計(jì)算公式，函數(shù)把復(fù)雜的計(jì)算步驟交由程序處理，只要按照函數(shù)格式錄入相關(guān)參數(shù)，就可以得出結(jié)果。如，求一個(gè)區(qū)域(A1:C100)的和，可以直接用SUM(A1:C100)的形式。 并且，對于函數(shù)，不用死記硬背，只需要知道應(yīng)該選取什么類別的函數(shù)，以及需要哪些參數(shù)怎么用就行了！比如選取字段，用Left／Right／Mid函數(shù)......其他細(xì)節(jié)神馬的就交給萬能的百度吧！
 經(jīng)常性的，需要的數(shù)據(jù)不在同一個(gè)Excel表或同一個(gè)Excel表不同sheet中，數(shù)據(jù)太多，copy起來麻煩還容易出錯(cuò)，如何整合呢？ 下面這些函數(shù)就是用于多表關(guān)聯(lián)或者行列比對時(shí)的場景，而且表格越復(fù)雜，用起來越爽！ 功能：用于查找首列滿足條件的元素。 語法：=VLOOKUP（要查找的值，要在其中查找值的區(qū)域，區(qū)域中包含返回值的列號，精確匹配或近似匹配 – 指定為 0/FALSE 或 1/TRUE）。 （舉例：查詢姓名是F5單元格中的員工是什么職務(wù)）
大家經(jīng)常使用VLOOKUP函數(shù)，但它的孿生弟弟HLOOKUP函數(shù)也不能忽視，對于橫向查詢，HLOOKUP函數(shù)也是利器。 功能：搜索表的頂行或值的數(shù)組中的值，并在表格或數(shù)組中指定的行的同一列中返回一個(gè)值。 語法：=VLOOKUP（要查找的值，要在其中查找值的區(qū)域，區(qū)域中包含返回值的行號，精確匹配或近似匹配 – 指定為 0/FALSE 或 1/TRUE）。 區(qū)別：函數(shù)HLOOKUP和VLOOKUP都是用來在表格中查找數(shù)據(jù)，但是，HLOOKUP返回的值與需要查找的值在同一列上，而VLOOKUP返回的值與需要查找的值在同一行上。 我們先來講解下基礎(chǔ)用法：
根據(jù)姓名，查找獎(jiǎng)金：=HLOOKUP(B5,C1:K2,2,FALSE) 第一個(gè)參數(shù)為查找值，第二參數(shù)C1:K2，為查詢區(qū)域，因?yàn)椴樵兘Y(jié)果在第2行，所以第三參數(shù)為2，最后一個(gè)參數(shù)false為精確匹配。 功能：返回表格或區(qū)域中的值或引用該值。 語法：= INDEX(要返回值的單元格區(qū)域或數(shù)組,所在行,所在列)
Match函數(shù)是一個(gè)輔助函數(shù)，大多數(shù)的時(shí)候是與其他函數(shù)結(jié)合進(jìn)行統(tǒng)計(jì)一些復(fù)雜的問題。最常用的搭檔就是INDEX函數(shù)，這個(gè)我們以后再介紹。 功能：用于返回指定內(nèi)容在指定區(qū)域（某行或者某列）的位置。 語法：= MATCH (要返回值的單元格區(qū)域或數(shù)組，查找的區(qū)域，查找方式) 接下來我們來學(xué)習(xí)他的基本用法： 要求：找出指定的成績在成績單里對應(yīng)的名次！ 解答：F2單元格，在編輯欄，輸入Match函數(shù)：=MATCH（）； 第1個(gè)參數(shù)：單擊E2單元格，就是我們要查找的成績； 第2個(gè)參數(shù)：選中C2：C8單元格區(qū)域； 第3個(gè)參數(shù)：輸入數(shù)字0，代表精確匹配； 即可查出：完全匹配的成績，對應(yīng)的名次：第7名；
Match函數(shù)公式：=MATCH(E2,C2:C8,0)； 如果將第三參數(shù)的0改為1就是模糊匹配了，繼續(xù)看下面： 輸入成績表中【沒有的成績】，Match函數(shù)：同樣可以查出對應(yīng)的排名！使用Match函數(shù)：模糊匹配模式即可！
Rank函數(shù)的用法：RANK(number, ref, [order]) 第一個(gè)參數(shù)：必選參數(shù)，需要排名的數(shù)字； 第二個(gè)參數(shù)：必選參數(shù)，排名數(shù)字的范圍； 第三個(gè)參數(shù)：可選參數(shù)，排序方式； 功能：求某一個(gè)數(shù)值在某一區(qū)域內(nèi)一組數(shù)值中的排名。 語法：=RANK(參與排名的數(shù)值, 排名的數(shù)值區(qū)域, 排名方式-0是降序-1是升序-默認(rèn)為0）。 示例1：使用Rank函數(shù)進(jìn)行從大到小降序排名，如下圖。 輸入Rank函數(shù)：=RANK(B2,$B$2：$B$9)；下拉
【說明】第一參數(shù)：B2，代表需要排序的單元格；第二參數(shù)：$B$2：$B$9，代表排序區(qū)域； 公式中：$B$2：$B$9，為什么要加美元符呢？$符用于鎖定：單元格區(qū)域，否則下拉公式后，會變成B3：B10，導(dǎo)致排序結(jié)果錯(cuò)誤！ 示例2：使用Rank函數(shù)進(jìn)行從大到小降序排名，如下圖。 Rank函數(shù)排名：默認(rèn)是降序排序！如果想要升序排名，只需要輸入Rank函數(shù)，第三個(gè)參數(shù)：1，就可以了！ Rank函數(shù)：=RANK(B2,$B$2:$B$9,1)；
注：Rank函數(shù)的第三個(gè)參數(shù)，是隱藏參數(shù)！如果想降序排序，這個(gè)參數(shù)可以省略！ 示例3：孿生函數(shù) Rank.EQ函數(shù)并列排名 有兩個(gè)員工銷量相同，在所難免的！那我們要如何排名呢？別擔(dān)心！可以使用：Rank.EQ函數(shù)來：并列排名！ 我們只需要將：Rank函數(shù)，替換成Rank.EQ函數(shù)即可！ 函數(shù)公式：=RANK.EQ(B2,$B$2:$B$9)； 【呂布】和【貂蟬】的銷量相同，所以并列第6名！
Rank.EQ函數(shù)與Rank函數(shù)，用法完全相同！但是Rank.EQ函數(shù)可以計(jì)算出：并列排名！ 這兩個(gè)函數(shù)對于我們中國式排名是不合適用的，因?yàn)槌霈F(xiàn)并列排名后的下一位就跳過去了，比如，兩個(gè)第6名，下一位就是第8名了。 功能：返回單元格所在的行的行號。 Row函數(shù)表達(dá)式：ROW([Reference]) 說明：Row函數(shù)用于返回引用單元格的行號。Reference 為對單元格或單元格區(qū)域的引用，可以省略；如果省略，默認(rèn)返回 Row 所在行的行號；如果 Reference 為對一個(gè)單元格區(qū)域的垂直引用（如 A1:A6），將以數(shù)組形式返回所有引用單元格的行號，按 F9 可以看到；Reference 不能一次引用多個(gè)區(qū)域。 示例1：
示例2：自動更新序號 制表時(shí)需要手動輸入序號？刪除行后，又要費(fèi)心更新序號？別擔(dān)心！我們可以利用ROW函數(shù)實(shí)現(xiàn)刪除行后仍能自動更新序號。操作如下： 用鼠標(biāo)選中【序號】列，在編輯欄，輸入ROW函數(shù)：=ROW()-1；并按回車鍵確認(rèn)；即可自動填充序號； 當(dāng)我們刪除：第4行后，序號列也會自動更新，不需要我們手動修改！
解析：【ROW函數(shù)】返回A2單元格的行數(shù)：2，再用2減1（第一行是標(biāo)題），就可以返回序號：1； Column函數(shù)，是最簡單的Excel函數(shù)！功能是返回單元格所在的列的序號。比如A列即1，B列即2，C列即3。 示例： Column函數(shù)，作用是返回：任意單元格的【列號】；
用鼠標(biāo)雙擊：B9單元格，并輸入函數(shù)：=COLUMN（B1）；然后拖動至：D9單元格，即可返回：對應(yīng)單元格的列號； 功能：offset函數(shù)以指定的引用為參照系，通過給定的偏移量得到新的引用。返回的引用可以為一個(gè)單元格或者單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。 語法：=OFFSET（基準(zhǔn)位置，向下或上偏移幾行，向右或左偏移幾列，引用區(qū)域的高度，引用區(qū)域的寬度）
示例：
公式=OFFSET(B1,3,2) 分析:第一參數(shù)B1，是定位。向下移動3行，就是王五；再向右移動2列，就是王五的數(shù)學(xué)成績89. 這只是最基本的用法，OFFSET函數(shù)更多的是結(jié)合其他函數(shù)進(jìn)行統(tǒng)計(jì)運(yùn)用。 以上是43個(gè)常用函數(shù)分類的第一類，接下來將陸續(xù)介紹其他類別。本系列的函數(shù)介紹，希望能夠幫助到大家！  |
|
|
