數據分析常用的Excel函數合集（上）

↑ 關注 + 星標 ~ 有趣的不像個技術號

每晚九點，我們準時相約

大家好，我是朱小五

Excel是我們工作中經常使用的一種工具，對於數據分析來說，這也是處理數據最基礎的工具。

本文對數據分析需要用到的函數做了分類，並且有詳細的例子說明。

Excel函數分類：關聯匹配類、清洗處理類、邏輯運算類、計算統計類、時間序列類由於篇幅過長，本篇先分享關聯匹配類和清洗處理類，其餘三個在明日推文第三條繼續分享。

關聯匹配類

經常性的，需要的數據不在同一個excel表或同一個excel表不同sheet中，數據太多，copy麻煩也不準確，如何整合呢？這類函數就是用於多表關聯或者行列比對時的場景，而且表越複雜，用得越多。

包含函數：VLOOKUP、HLOOKUP、INDEX、MATCH、RANK、Row、Column、Offset

1. VLOOKUP

功能：用於查找首列滿足條件的元素

語法：=VLOOKUP（要查找的值，要在其中查找值的區域，區域中包含返回值的列號，精確匹配(0)或近似匹配(1) ）

(1) 單表查找

把選手Tian的戰隊找到之後，接下來把滑鼠放到G8單元格右下角位置，出現十字元號後往下拉，Excel會根據單元格的變化自動填充G9和G10單元格的公式。

(2) 跨多工作表查找

假設我有一個工資表格文件，裡面每個部門有一張表，有4個部門對應的部門工資表和一個需要查詢工資的查詢表，為方便說明這裡的姓名取方便識別的編號，你也可以用真正的姓名。

在查詢表中，要求根據提供的姓名，從銷售~人事4個工作表中查詢該員工的基本工資。

如果，我們知道A1是銷售部的，那麼公式可以寫為：

=VLOOKUP(A2,銷售!A:C,3,0)

如果，我們知道A1可能在銷售或財務表這2個表中，公式可以寫為：

=IFERROR(VLOOKUP(A2,銷售!A:C,3,0),VLOOKUP(A2,財務!A:C,3,0))

意思是，如果在銷售表中查找不到(用IFERROR函數判斷)，則去財務表中再查找。

如果，我們知道A1可能在銷售、財務或服務表中，公式可以再次改為：

=IFERROR(VLOOKUP(A2,銷售!A:C,3,0),IFERROR(VLOOKUP(A2,財務!A:C,3,0),VLOOKUP(A2,服務!A:C,3,0)))

如果，有更多的表，如本例中4個表，那就一層層的套用下去，如果4個表都查不到就設置為"無此人資訊"：

=IFERROR(VLOOKUP(A2,銷售!A:C,3,0),IFERROR(VLOOKUP(A2,財務!A:C,3,0),IFERROR(VLOOKUP(A2,服務!A:C,3,0),IFERROR(VLOOKUP(A2,人事!A:C,3,0),"無此人資訊"))))

2. HLOOKUP

當查找的值位於查找範圍的首行，並且返回的值在查找範圍的第幾行，可以使用 hlookup 函數

語法：=HLOOKUP（要查找的值，查找的範圍，返回的值在查找範圍的第幾行，精確匹配(0)或近似匹配(1) ）

區別：HLOOKUP按行查找，返回的值與需要查找的值在同一列上，VLOOKUP按列查找，返回的值與需要查找的值在同一行上。

3. INDEX

在Excel中，除了VLOOKUP函數常用來查找引用外，INDEX函數和MATCH函數組合也可用來做查找引用工作，這組函數有效彌補了VLOOKUP函數查找目標不在查找範圍數據首列的缺陷。

功能：返回表格或區域中的值

語法：= INDEX(要返回值的單元格區域或數組,所在行,所在列)

4. MATCH

功能：用於返回指定內容在指定區域（某行或者某列）的位置

語法：= MATCH (要查找的值，查找的區域，查找方式)，查找方式0為等於查找值，1為小於查找值，-1為大於查找值

5. RANK

功能：求某一個數值在某一區域內的數值排名

語法：=RANK(參與排名的數值, 排名的數值區域, 排名方式-0是降序-1是升序-默認為0）。

6. Row

功能：返回單元格所在的行

語法：ROW()或ROW(某個單元格)

7. Column

功能：返回單元格所在的列

語法：COLUMN()或COLUMN(某個單元格)

8. Offset

功能：從指定的基準位置按行列偏移量返回指定的引用

語法：＝Offset（指定點，偏移多少行(正數向下，負數向上)，偏移多少列(正數向右，負數向左)，返回多少行，返回多少列）

清洗處理類

數據處理之前，需要對提取的數據進行初步清洗，如清除字元串空格，合併單元格、替換、截取字元串、查找字元串出現的位置等。

  • 清除字元串前後空格：使用Trim
  • 合併單元格：使用concatenate
  • 截取字元串：使用Left/Right/Mid
  • 替換單元格中內容：Replace/Substitute
  • 查找文本在單元格中的位置：Find/Search
  • 獲取字元長度：Len/Lenb
  • 篩選包含某個條件的 內容：IF+OR+COUNTIF
  • 轉換數據類型：VALUE/TEXT

1. Trim

功能：主要用於把單元格內容前後的空格去掉，但並不去除字元之間的空格，如果是想要去掉所有的空格，需要用substitute函數。

語法：=TRIM(單元格)

2. concatenate

語法：=Concatenate(單元格1，單元格2……)

合併單元格中的內容，還有另一種合併方式是&，需要合併的內容過多時，concatenate效率更快。

3. Left

功能：從左截取字元串

語法：=Left(值所在單元格，截取長度)

4. Right

功能：從右截取字元串

語法：= Right (值所在單元格，截取長度)

5. Mid

功能：從中間截取字元串

語法：= Mid(指定字元串，開始位置，截取長度)

Text函數表示將數值轉化為自己想要的文本格式，語法：

=TEXT(value,format_text)

6. Replace

功能：替換掉單元格的字元串

語法：=Replace（指定字元串，哪個位置開始替換，替換幾個字元，替換成什麼）

7. Substitute

和replace接近，不同在於Replace根據位置實現替換，需要提供從第幾位開始替換，替換幾位，替換後的新的文本。

而Substitute根據文本內容替換，需要提供替換的舊文本和新文本，以及替換第幾個舊文本等。因此Replace實現固定位置的文本替換，Substitute實現固定文本替換。

8. Find

功能：查找文本位置

語法：=Find（要查找字元，指定字元串，從第幾個字元開始查起）

9. Search

功能：返回一個指定字元或文本字元串在字元串中第一次出現的位置，從左到右查找

語法：=search（要查找的字元，字元所在的文本，從第幾個字元開始查找）

Find和Search這兩個函數功能幾乎相同，實現查找字元所在的位置，區別在於Find函數精確查找，區分大小寫；Search函數模糊查找，不區分大小寫。

10. Len

功能：返回字元串的字元數

語法：=LEN（字元串)

字元串是指包含數字、字母、符號等的一串字元。

11. Lenb

功能：返回字元串的位元組數

區別在於，len是按字元數計算的、lenb是按位元組數計算的。數字、字母、英文、標點符號（半形狀態下輸入的哦）都是按1計算的，漢字、全形狀態下的標點符號，每個字元按2計算。

綜合應用：

篩選內容：IF+OR+COUNTIF

=IF(OR(COUNTIF(A1,"*"&{"Python","java"}&"*")),A1,"0")

如果含有欄位Python或java中的任何一個則為本身，否則為"0"，* 代表任意內容，之後就可以通過Excel的篩選功能，把B列的"0"篩選掉。

12. VALUE

功能：將所選區域轉為數值類型

13.TEXT

功能：將所選區域轉為文本類型

作者：北風吹沙

來源：部落格園

VirMach 便宜 VPS

QNews

QNews