excel函式大全完整版
本文已影響2.32W人
本文已影響2.32W人
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(條件區域,指定條件)
全國奇葩派出所名稱大全(完整版)
excel某一列提取部分數字
excel按條件提取資料
coccinelle是什麼牌子?coccinelle哪個國家的?coccinelle什麼檔次?
excel從多張表中提取資料
excel提取多個表格資料
excel函式公式大全 excel函式常用公式
excel口紅什麼牌子 excel口紅怎麼樣
excel供應鏈經營資料分析
excel如何提取數字部分內容
大明風華全部歌曲大全 完整版歌詞及演唱者介紹
excel表格怎麼批量提取資料
xxxtentacion抖音很火的歌叫什麼 jocelyn flores歌詞
yeezy350 Cloud White Reflective反光版發售價及發售時間
excel身份證格式怎麼調整
excel表格整體格式調整
抖音come back home early是什麼歌 抖音come back home early歌詞完整版
Alex大叔年6月雙魚座運勢完整版
任嘉倫新歌Dreamers Never Sleep歌詞含義 完整版歌詞介紹
抖音中笨李白的是什麼歌 believer完整版歌詞
excelsior是什麼牌子 excelsior鞋子尺碼偏大嗎
怎樣在excel表格中提取我想要的資料
Alex大叔年6月水瓶座運勢完整版
蘋果電腦excel表格快捷鍵大全
2015維多利亞的祕密內衣秀完整版
吳亦凡freestyle表情包高清無水印完整版打包
excel如何從一個表格中提取想要的資料
小時代歌曲大全盤點 《我好想你》完整版MV首播
大牌明星演譯AlexanderMcQueen全新升級升級手袋系列產品
Alex大叔年6月射手座運勢完整版
Alex大叔年6月雙子座運勢完整版
excel表格的基本操作快捷鍵大全
Alex大叔年6月天蠍座運勢完整版