excel函數大全完整版
本文已影響2.3W人
本文已影響2.3W人
excel函數大全完整版,相信大家都想快速地提高自己的Excel水平,那麼最直接有效的的方法就是掌握一些小技巧。就是可以拿來即用,下面是excel函數大全完整版。
excel函數大全完整版1
一、數字處理
1、取絕對值 =ABS(數字)
2、取整 =INT(數字)
3、四捨五入 =ROUND(數字,小數位數)
二、判斷公式
1、把公式產生的錯誤值顯示爲空
公式:=IFERROR(A2/B2,"")
說明:如果是錯誤值則顯示爲空,否則正常顯示。
2、IF多條件判斷返回值
公式:=IF(AND(A2<500,B2=" 未到期"),"補款","")
說明:兩個條件同時成立用AND,任一個成立用OR函數。
三、統計公式
1、統計兩個表格重複的內容
公式:=COUNTIF(Sheet15!A:A,A2)
說明:如果返回值大於0說明在另一個表中存在,0則不存在。
2、統計不重複的總人數
公式:=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
說明:用COUNTIF統計出每人的出現次數,用1除的方式把出現次數變成分母,然後相加。
四、求和公式
1、隔列求和
公式:=SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
說明:如果標題行沒有規則用第2個公式。
2、單條件求和
公式:=SUMIF(A:A,E2,C:C)
說明:SUMIF函數的基本用法
3、多條件模糊求和
公式:=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
說明:在sumifs中可以使用通配符*
5、多表相同位置求和
公式:=SUM(Sheet1:Sheet19!B2)
說明:在表中間刪除或添加表後,公式結果會自動更新。
6、按日期和產品求和
公式:=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
說明:SUMPRODUCT可以完成多條件求和
五、查找與引用公式
1、單條件查找公式
公式:=VLOOKUP(B11,B3:F7,4,FALSE)
說明:查找是VLOOKUP最擅長的,基本用法
2、雙向查找公式
公式:=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
說明:利用MATCH函數查找位置,用INDEX函數取值
3、查找最後一條符合條件的記錄。
公式:
說明:0/(條件)可以把不符合條件的變成錯誤值,而lookup可以忽略錯誤值
4、按數字區域間取對應的值
公式:
公式說明:VLOOKUP 和LOOKUP函數都可以按區間取值,一定要注意,銷售量列的數字一定要升序排列。
六、字符串處理公式
1、多單元格字符串合併
公式:=PHONETIC(A2:A7)
說明:Phonetic函數只能對字符型內容合併,數字不可以。
2、截取左部分
公式:=LEFT(D1,LEN(D1)-3)
說明:LEN計算出總長度,LEFT從左邊截總長度-3個
3、截取-前的部分
公式:=Left(A1,FIND("-",A1)-1)
說明:用FIND函數查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))
說明:公式是利用強插N個空字符的方式進行截取
5、字符串查找
公式:=IF(COUNT(FIND("河南",A2))=0,"否","是")
說明:用來判斷查找是否成功。
6、字符串查找一對多
公式:=IF(COUNT(FIND({"遼寧","黑龍江","吉林"},A2))=0,"其他","東北")
說明:設置FIND第一個參數爲常量數組,用COUNT函數統計FIND查找結果
excel函數大全完整版2
一、Excel工作表函數:求和類。
(一)Sum。
功能:計算指定的單元格區域中所有數值的和。
語法結構:=Sum(值1,值2……值N)。
目的:計算總“月薪”。
方法:
在目標單元格中輸入公式:=SUM(1*G3:G12),並用Ctrl+Shift+Enter填充。
解讀:
如果直接用Sum函數進行求和,結果爲0,究其原因就在於“月薪”爲文本型的數值,如果不想調整數據類型,可以給每個參數乘以1將其強制轉換爲數值類型,然後用Sum函數進行求和。
(二)Sumif。
功能:對滿足條件的單元格求和,即單條件求和。
語法結構:=Sumif(條件範圍,條件,[求和範圍]),當“條件範圍”和“求和範圍”相同時,可以省略“求和範圍”。
目的:根據“性別”計算總“月薪”。
方法:
在目標單元格中輸入公式:=SUMIF(D3:D12,J3,G3:G12)。
解讀:
由於“條件範圍”和“求和範圍”不相同,所以不能省略參數“求和範圍”。
(三)Sumifs。
功能:對一組給定條件指定的單元格求和。
語法結構:=Sumifs(求和範圍,條件1範圍,條件1,條件2範圍,條件2……)
目的:根據“性別”統計相應“學歷”下的總“月薪”。
方法:
在目標單元格中輸入公式:=SUMIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
解讀:
參數“條件範圍”和“條件”必須成對出現,否則公式無法正確執行!
二、Excel工作表函數:判斷類。
(一)If。
功能:判斷是否滿足某個條件,如果滿足返回一個值,如果不滿足則返回另一個值。
語法結構:=If(判斷條件,條件爲真時的`返回值,條件爲假時的返回值)。
目的:判斷“月薪”的檔次,如果≥4000,則返回“高”,如果≥3000,則返回“中”,否則返回“低”。
方法:
在目標單元格中輸入公式:=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。
解讀:
If函數除了單獨判斷外,還可以嵌套使用,但多級嵌套時,需要理清邏輯關係,否則容易出錯!
(二)Ifs。
功能:判斷是否滿足一個或多個條件並返回與第一個TRUE條件對應的值。
語法結構:=Ifs(條件1,返回值1,條件2,返回值2……)。
目的:判斷“月薪”的檔次,如果≥4000,則返回“高”,如果≥3000,則返回“中”,否則返回“低”。
方法:
在目標單元格中輸入公式:=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。
解讀:
參數中的“條件”和“返回值”必須成對出現,但該函數僅能應用於16及以上版本的Excel中,在WPS高版本中也可以使用哦!
三、Excel工作表函數:查找類。
(一)Vlookup。
功能:搜索表區域首列滿足條件的元素,確定待檢索單元格在區域中的的序號,再進一步返回選定單元格的值。
語法結構:=Vlookup(查詢值,數據範圍,返回值列數,查詢模式),查詢模式分爲精準查詢和模糊查詢。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
在目標單元格中輸入公式:=VLOOKUP(J3,B3:G12,6,0)。
解讀:
參數“返回值列數”要根據“數據範圍”來確定,是返回值所在的的相對列數。
(二)Lookup。
功能:從單行或單列或單數組中查找一個值。
Lookup函數具有兩種語法結構:向量形式和數組形式。
1、向量形式。
功能:從單行或單列中查找查找指定的值,返回第二個單行或單列中相同位置的值。
語法結構:=Lookup(查找值,查找值所在範圍,[返回值所在範圍]),當“查找值所在範圍”和“返回值所在範圍”相同時,可以省略“返回值所在範圍”。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
1、以“員工姓名”爲主要關鍵字進行升序排序。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:B12,G3:G12)。
解讀:
在使用Lookup函數查詢數據時,首次要以“查詢值”爲主要關鍵字進行升序排序,否則無法得到正確的結果。
2、數組形式。
功能:從指定的範圍第一列或第一行中查詢指定的值,返回指定範圍中最後一列或最後一行對應位置上的值。
語法:=Lookup(查找值,查詢範圍)。
重點解讀:
從“功能”中可以看出,Lookup函數的數組形式,查找值必須在查詢範圍的第一列或第一行中,返回的值必須是查詢範圍的最後一列或最後一行對應的值。即:查找值和返回值在查詢範圍的“兩端”。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
1、以“員工姓名”爲主要關鍵字進行升序排序。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:G12)。
解讀:
查詢值必須在數據範圍的第一列,返回值必須在數據範圍得最後一列。
excel函數大全完整版3
1、IF函數條件判斷
IF函數是最常用的判斷類函數之一,能完成非此即彼的判斷。
如下圖,考覈得分的標準爲9分,要判斷B列的考覈成績是否合格。
=IF(B4>=9,”合格”,”不合格”)
IF,相當於普通話的“如果”,常規用法是:
IF(判斷的條件,符合條件時的結果,不符合條件時的結果)
2、多條件判斷
如果部門爲生產、崗位爲主操 有高溫補助。在D列使用公式:
=IF(AND(B2=”生產”,C2=”主操”),”有”,”無”)
AND函數對兩個條件判斷,如果同時符合,IF函數返回“有”,否則爲無。
3、條件求和
使用SUMIF函數計算一班的總成績:
=SUMIF(D2:D5,F2,C2:C5)
SUMIF用法是:
=SUMIF(條件區域,指定的求和條件,求和的區域)
用通俗的話描述就是:
如果D2:D5區域的班級等於F2單元格的“一班”,就對C2:C5單元格對應的區域求和。
4、多條件求和
要統計部門爲生產,並且崗位爲主操的補助總額。
公式爲:
=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)
SUMIFS用法是:
=SUMIFS(求和的區域,條件區域1,指定的求和條件1,條件區域2,指定的求和條件2,……)
5、條件計數
如下圖,要統計指定店鋪的業務筆數。也就是統計B列中有多少個指定的店鋪名稱。
=COUNTIF(B2:B12,E3)
COUNTIF函數統計條件區域中,符合指定條件的單元格個數。常規用法爲:
=COUNTIF(條件區域,指定條件)
抖音中笨李白的是什麼歌 believer完整版歌詞
小時代歌曲大全盤點 《我好想你》完整版MV首播
Alex大叔年6月天蠍座運勢完整版
yeezy350 Cloud White Reflective反光版發售價及發售時間
excel如何從一個表格中提取想要的數據
Alex大叔年6月射手座運勢完整版
全國奇葩派出所名稱大全(完整版)
coccinelle是什麼牌子?coccinelle哪個國家的?coccinelle什麼檔次?
excelsior是什麼牌子 excelsior鞋子尺碼偏大嗎
excel提取多個表格數據
任嘉倫新歌Dreamers Never Sleep歌詞含義 完整版歌詞介紹
excel身份證格式怎麼調整
2015維多利亞的祕密內衣秀完整版
吳亦凡freestyle表情包高清無水印完整版打包
大牌明星演譯AlexanderMcQueen全新升級升級手袋系列產品
Alex大叔年6月雙子座運勢完整版
excel按條件提取數據
excel口紅什麼牌子 excel口紅怎麼樣
excel某一列提取部分數字
抖音come back home early是什麼歌 抖音come back home early歌詞完整版
excel表格怎麼批量提取數據
excel供應鏈經營數據分析
excel表格的基本操作快捷鍵大全
蘋果電腦excel表格快捷鍵大全
excel函數公式大全 excel函數常用公式
excel表格整體格式調整
Alex大叔年6月雙魚座運勢完整版
excel如何提取數字部分內容
怎樣在excel表格中提取我想要的數據
大明風華全部歌曲大全 完整版歌詞及演唱者介紹
xxxtentacion抖音很火的歌叫什麼 jocelyn flores歌詞
excel從多張表中提取數據
Alex大叔年6月水瓶座運勢完整版