excel表格函數(shù)及部分實用公式
excel函數(shù)非常強大，適當?shù)膶W習可提升工作效率和個人競爭力。整理個大概，用于學習
一、關(guān)聯(lián)匹配類
1、VLOOKUP：用于查找首列滿足條件的元素
相當于=VLOOKUP（找什么，在哪找，第幾列，精確找還是大概找一找）
①查找的值?
姓名查找工資?=VLOOKUP（查找值，數(shù)據(jù)表，列序數(shù)，[匹配條件]）
查找不到返回空值?=IFERROR(VLOOKUP(G2,B:E,4,0),"")
②查找區(qū)域或數(shù)組
根據(jù)部門和姓名查工資?=VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0)
③返回值的在列數(shù)
④精確or匹配查找
2、HLOOKUP：搜索表的頂行或值的數(shù)組中的值，并在表格或數(shù)組中指定的行的同一列中返回一個值
在A1到D3的區(qū)域中查找“手機”，并返回找到的行中的第三列的值，F(xiàn)ALSE為精確匹配的值
=HLOOKUP("手機", A1:D3, 3, FALSE)
3、INDEX：返回表格或區(qū)域中的值或引用該值
INDEX(數(shù)組或區(qū)域, 行號, 列號)
INDEX(array, row_num, [column_num])
4、MATCH：用于返回指定內(nèi)容在指定區(qū)域（某行或者某列）的位置
MATCH(lookup_value,lookup_array, [match_type])
5、RANK：求某一個數(shù)值在某一區(qū)域內(nèi)一組數(shù)值中的排名
RANK(排誰，在那個區(qū)域里排，降序/升序)，其中0表示降序，1表示升序
=RANK(C2,$C$2:$C$10,0)
6、ROW：返回單元格所在的行
7、Column：返回單元格所在的列
8、Offset：返回對單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用
offset是一個偏移函數(shù)，它以一個區(qū)域為原點進行偏移得到一個新的偏移區(qū)域
=OFFSET(reference, rows, cols, [height], [width])
二、清洗處理類
1、Trim：清除掉字符串兩邊的空格
2、Rtrim：清除單元格右邊的空格
3、Ltrim：清除單元格左邊的空格
4、Concatenate：合并單元格中的內(nèi)容
5、Left：從左截取字符串
①分離單位與數(shù)字，B2為單位（kg/元）：=LEFT(B2,LEN(B2)-4)
②除后3位數(shù)之外的部分：=LEFT(B2,LEN(B2)-3)
6、Right：從右截取字符串
7、Mid：從中間截取字符串
8、Replace：替換掉單元格的字符串
9、Substitute：和replace接近，實現(xiàn)固定文本替換
10、Find：查找文本位置
11、Search：返回一個指定字符或文本字符串在字符串中第一次出現(xiàn)的位置 ,從左到右查找
12、Len：返回文本字符串中的字符個數(shù)
13、Lenb：返回文本字符串中用于代表字符的字節(jié)數(shù)
三、邏輯運算類
1、IF：=IF(條件, true時返回值, false返回值)
①判斷月薪：=IF(B3>=6000,"高薪","低薪")
②判斷多個條件：=IFS(B3<6000,"員工",B3<10000,"經(jīng)理",B3>=10000,"老板")
③身份證號碼自動生成性別：=IF(MOD(MID(B2,17,1),2),"男","女")
2、AND：邏輯判斷，相當于“并”
大于90分，AND函數(shù)返回邏輯值為真TRUE，顯示結(jié)果為合格。反之
=IF(AND(C2>90,C3>90,C4>90),"合格","不合格")
3、OR：邏輯判斷，相當于“或”
大于90分，OR函數(shù)返回邏輯值為真TRUE，顯示結(jié)果為合格。反之
=IF(OR(C2>90,C3>90,C4>90),"合格","不合格")
四、計算統(tǒng)計類
1、MIN：找到某區(qū)域中的最小值
2、MAX：找到某區(qū)域中的最大值
3、AVERAGE：計算某區(qū)域中的平均值
一個區(qū)域里面的數(shù)字機選平均值
=AVERAGE(A1:A4)
4、COUNT：計算含有數(shù)字的單元格的個數(shù)
統(tǒng)計姓吳且銷售額1萬以上的人數(shù)
=COUNTIFS(B2:B14,"吳*",D2:D14,">=10000")
5、COUNTIF：計算某個區(qū)域中滿足給定條件的單元格數(shù)目
①求文本型單元格個數(shù)：=COUNTIF($A$2:$A$6,"*")
②求非空單元格個數(shù)：=COUNTIF($A$2:$A$6,"<>")
③求空單元格個數(shù)：=COUNTIF($A$2:$A$6,"")
6、COUNTIFS：統(tǒng)計一組給定條件所指定的單元格數(shù)
COUNTIF(區(qū)域，判斷條件)
7、SUM：計算單元格區(qū)域中所有數(shù)值的和
求總和：=SUM（B2：B9）
8、SUMIF：求滿足條件的單元格和
單條件求和：=SUMIF（F：F，I2，G：G）
9、SUMIFS：對一組滿足條件指定的單元格求和
多條件求和：=SUMIFS(G2:G9,D2:D9,I3,G2:G9,">"&K3)
10、SUMPRODUCT：返回相應(yīng)的數(shù)組或區(qū)域乘積的和
SUMPRODUCT（array1,array2,array3, ...）
11、Stdev：統(tǒng)計型函數(shù)，求標準差
STDEV.S(number1,[number2],...)
12、Substotal：匯總型函數(shù)，將平均值、計數(shù)、最大最小、相乘、標準差、求和、方差等參數(shù)化
SUBSTITUTE(text, old_text, new_text, [instance_num])
13、Int：取整函數(shù)，向下取整
14、Round：取整函數(shù)，按小數(shù)位取數(shù)
五、時間序列類
1、TODAY：返回當前日期的序列號，動態(tài)函數(shù)
實用。自動返回今日日期?？勺远x顯示單元格格式
=TODAY（）
2、NOW：返回當前日期和時間的序列號，動態(tài)函數(shù)
實用。自動返回當前的日期與時間?？勺远x顯示單元格格式
=NOW()
3、YEAR：返回對應(yīng)于某個日期的年份
身份證號碼自動生成年齡：=YEAR(TODAY())-MID(B2,7,4)
通過進入公司的日期與指定年份來計算工齡：=YEAR($E$2)-YEAR(B2)
4、MONTH：返回日期（以序列數(shù)表示）中的月份
5、DAY：返回以序列數(shù)表示的某日期的天數(shù)
6、WEEKDAY：返回對應(yīng)于某個日期的一周中的第幾天
7、Datedif：計算兩個日期之間相隔的天數(shù)、月數(shù)或年數(shù)
①身份證號碼自動生成年齡：=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")
②出生年月計算年齡：=DATEDIF(B2,TODAY(),"y")
8、WORKDAY：計算一項工作需要做N天后完成的結(jié)束日期等
預(yù)算完工日期?=WORKDAY（開始日期，天數(shù)，[假期]）
六、其他
1、LOWER：將指定字符串中的字母轉(zhuǎn)換為小寫
英文大寫轉(zhuǎn)小寫：=LOWER(B2)
2、TEXT：通過格式代碼對數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式（功能巨多）
①金額轉(zhuǎn)中文大寫（財務(wù)專用）：=TEXT(B2,"[dbnum2]")
②身份證號碼自動生成出生日期：=TEXT(MID(B2,7,8),"0-00-00")
3、SUBSTITUTE：將查找到的某個字符，替換成別的字符
①加密指定內(nèi)容（隱藏手機號中間四位）：=SUBSTITUTE(B3,MID(B3,4,4),"****")
②字符串計數(shù)：=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1
③帶單位求和：=SUMPRODUCT(SUBSTITUTE(D2:D10,"元","")*1)&"元"
④數(shù)據(jù)分列：=TRIM(MID(SUBSTITUTE($C3,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))
Excel常用快捷鍵
關(guān)閉工作簿 Ctrl+W 、打開工作簿 Ctrl+O 、保存工作簿 Ctrl+S?
復(fù)制 Ctrl+C 、粘貼 Ctrl+V、撤消 Ctrl+Z 、剪切 Ctrl+X?
刪除單元格內(nèi)容 Delete 鍵 、加粗 Ctrl+B 、隱藏選定的行 Ctrl+9?
