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(條件區域,指定條件)