|
大家整理了，數(shù)據(jù)分析入門常用的EXCEL在數(shù)據(jù)分析行業(yè)，EXCEL是最基礎的、入門級的，也是最常用，最容易上手的工具了。想要學習數(shù)據(jù)分析的小伙伴可以選擇先從EXCEL入手，下面小編就給函數(shù)，希望對各位小伙伴有所幫助。 Excel函數(shù)分類：關聯(lián)匹配類、清洗處理類、邏輯運算類、計算統(tǒng)計類、時間序列類 本篇先對關聯(lián)匹配類和清洗處理類函數(shù)進行解說，其余三個在接下來的文章中繼續(xù)分享。 關聯(lián)匹配類 在數(shù)據(jù)分析中，數(shù)據(jù)的查找、對比等非常常見，這就需要用到關聯(lián)匹配類函數(shù)，本文將介紹Excel數(shù)據(jù)分析中常用的關聯(lián)匹配類函數(shù)，如：VLOOKUP、HLOOKUP、INDEX、MATCH、RANK、Row、Column、Offset 1. VLOOKUP 功能：用于查找首列滿足條件的元素 語法：=VLOOKUP（要查找的值，要在其中查找值的區(qū)域，區(qū)域中包含返回值的列號，精確匹配(0)或近似匹配(1) ） (1) 單表查找
在G8中VLOOKUP(F8,$B$2:$6$6,2,0)，這樣把選手Tian的戰(zhàn)隊找到之后，接下來自動填充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函數(shù)判斷)，則去財務表中再查找。 如果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函數(shù)。 hlookup函數(shù)vlookup函數(shù)查找的方式 hlookup函數(shù)——按行查找。 vlookup函數(shù)——按列查找。 語法：=HLOOKUP（要查找的值，查找的范圍，返回的值在查找范圍的第幾行，精確匹配(0)或近似匹配(1) ） 區(qū)別：HLOOKUP按行查找，返回的值與需要查找的值在同一列上，VLOOKUP按列查找，返回的值與需要查找的值在同一行上。 可以看出，hlookup函數(shù)是橫向查找，按行查找。 當查找的值(高數(shù))位于查找范圍(A1:E6)的首行，返回的值在查找范圍(A1:E6)的第幾行，這第幾行由姓名所在行決定，例如張曹在第二行，可利用公式=row(G2)實現(xiàn)。 輸入公式=HLOOKUP($H$1,$A$1:$E$6,ROW(G2),FALSE)。 公式解釋 在數(shù)據(jù)表第一行中查找的值——H1高數(shù)。 查找的范圍——A1:E6。 返回的值在查找范圍的第幾行——使用row函數(shù)確。 模糊匹配/精確匹配——精確匹配。
3. INDEX index+match是一個比較經典的組合，類似vlookup，但是比vlookup更加靈活，比如數(shù)據(jù)列進行剪切移位時，不受影響，還能向前查找列內容。 使用方法為 =INDEX(明細!A:A,MATCH(A2,明細!B:B,0))。 功能：返回表格或區(qū)域中的值 語法：= INDEX(要返回值的單元格區(qū)域或數(shù)組,所在行,所在列)
4. MATCH 含義：返回目標值在查找區(qū)域中的位置。 語法： =match(lookup_value, lookup_array, match_type) =Match（目標值，查找區(qū)域，0/1/-1）
5. RANK 含義：求某一個數(shù)值在某一區(qū)域內一組數(shù)值中的排名。 語法： =rank(number,ref,[order]) number ——參與排名的數(shù)值 ref ——排名的數(shù)值區(qū)域 order——有1和0兩種。0——從大到小排名（降序），1——從小到大排名（升序）。 0默認不用輸入，得到的就是從大到小的排名。
6. Row 含義：返回所選擇的某一個單元格的行數(shù)。 語法： =row(reference) 如果省略reference，則默認返回row函數(shù)所在單元格的行數(shù)。
7. Column 功能：返回單元格所在的列 語法： =column（reference) 如果省略reference，則默認返回函數(shù)column所在單元格的列數(shù)。
8. Offset 功能：以指定的（單元格或相連單元格區(qū)域的引用）為參照系，通過給定偏移量得到新的引用。返回的引用可以是一個單元格也可以是一個區(qū)域（可以指定行列數(shù)）。 語法： =offsetreference,rows,cols,height,width) =Offset（參照單元格，行偏移量，列偏移量，返回幾行，返回幾列）
清洗處理類 數(shù)據(jù)處理之前，需要對提取的數(shù)據(jù)進行初步清洗，如清除字符串空格，合并單元格、替換、截取字符串、查找字符串出現(xiàn)的位置等，以下將做簡要介紹： 清除字符串空格：使用Trim/Ltrim/Rtrim 合并單元格：使用concatenate 截取字符串：使用Left/Right/Mid 替換單元格中內容：Replace/Substitute 查找文本在單元格中的位置：Find/Search 1. Trim 語法：TRIM(text)
2. concatenate 含義：把多個字符文本或數(shù)值連接在一起，實現(xiàn)合并的功能。 語法： =concatenate(text1, [text2], ...） Text1，Text2可以是文本或者數(shù)值 最多為 255 項，但是項與項之間必須用逗號隔開。 合并單元格中的內容，還有另一種合并方式是&，需要合并的內容過多時，concatenate效率更快。
3. Left 含義：用來對單元格內容進行截取。從左邊第一個字符開始截取，截取指定的長度。 語法： =left(text,num_chars) text代表用來截取的單元格內容。 num_chars代表從左開始截取的字符數(shù)。
4. Right 功能：從字符串右邊截取n個字符 語法： =Right(string, length) =right（字符串，截取個數(shù)）
5. Mid 功能：可以從從任意位置截取字符串。 語法：=mid(字符串，開始截取位置，截取字符個數(shù)
Text函數(shù)表示將數(shù)值轉化為自己想要的文本格式，語法： =TEXT(value,format_text) 6. Replace 功能：替換掉單元格的字符串 語法：=Replace（指定字符串，哪個位置開始替換，替換幾個字符，替換成什么）
7. Substitute 和replace接近，不同在于Replace根據(jù)位置實現(xiàn)替換，需要提供從第幾位開始替換，替換幾位，替換后的新的文本。 而Substitute根據(jù)文本內容替換，需要提供替換的舊文本和新文本，以及替換第幾個舊文本等。因此Replace實現(xiàn)固定位置的文本替換，Substitute實現(xiàn)固定文本替換。
8. Find 功能：查找文本位置 語法：=Find（要查找字符，指定字符串，從第幾個字符開始查起）
9. Search 功能：返回一個指定字符或文本字符串在字符串中第一次出現(xiàn)的位置，從左到右查找 語法：=search（要查找的字符，字符所在的文本，從第幾個字符開始查找） Find和Search這兩個函數(shù)功能幾乎相同，實現(xiàn)查找字符所在的位置，區(qū)別在于Find函數(shù)精確查找，區(qū)分大小寫；Search函數(shù)模糊查找，不區(qū)分大小寫。
10. Len 功能：返回字符串的字符數(shù) 語法：=LEN（字符串) 字符串是指包含數(shù)字、字母、符號等的一串字符。
11. Lenb 功能：返回字符串的字節(jié)數(shù) 區(qū)別在于，len是按字符數(shù)計算的、lenb是按字節(jié)數(shù)計算的。數(shù)字、字母、英文、標點符號（半角狀態(tài)下輸入的哦）都是按1計算的，漢字、全角狀態(tài)下的標點符號，每個字符按2計算。
綜合應用：
篩選內容：IF+OR+COUNTIF =IF(OR(COUNTIF(A1,"*"&{"Python","java"}&"*")),A1,"0") 如果含有字段Python或java中的任何一個則為本身，否則為"0"，* 代表任意內容，之后就可以通過Excel的篩選功能，把B列的"0"篩選掉。
12. VALUE 功能：將所選區(qū)域轉為數(shù)值類型 13.TEXT 功能：將所選區(qū)域轉為文本類型
|
|
|
