excel中最常用的30個函數
本文已影響1.7W人
本文已影響1.7W人
excel中最常用的30個函數,excel在我們的學習和工作中都是比價經常使用的到一種辦公軟件,對於excel可能大部分的人都沒有很多瞭解,以下excel中最常用的30個函數。
excel中最常用的30個函數1
一、數字處理
1、取絕對值
2、取整
3、四捨五入
二、判斷公式
1、把公式產生的錯誤值顯示爲空
2、IF多條件判斷返回值
三、統計公式
1、統計兩個表格重複的內容
2、統計不重複的總人數
四、求和公式
1、隔列求和
2、單條件求和
3、單條件模糊求和
4、多條件模糊求和
5、多表相同位置求和
6、按日期和產品求和
五、查找與引用公式
1、單條件查找公式
2、雙向查找公式
3、查找最後一條符合條件的記錄。
4、多條件查找
5、指定區域最後一個非空值查找
6、按數字區域間取對應的值
六、字符串處理公式
1、多單元格字符串合併
2、截取除後3位之外的部分
3、截取-前的部分
4、截取字符串中任一段的公式
5、字符串查找
6、字符串查找一對多
七、日期計算公式
1、兩日期相隔的年、月、天數計算
2、扣除週末天數的工作日天數
一、數字處理
1、取絕對值
=ABS(數字)
2、取整
=INT(數字)
3、四捨五入
=ROUND(數字,小數位數)
二、判斷公式
1、把公式產生的錯誤值顯示爲空
公式:C2
=IFERROR(A2/B2,"")
說明:如果是錯誤值則顯示爲空,否則正常顯示。
2、IF多條件判斷返回值
公式:C2
=IF(AND(A2<500,B2="未到期"),"補款","")
說明:兩個條件同時成立用AND,任一個成立用OR函數。
三、統計公式
1、統計兩個表格重複的內容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
說明:如果返回值大於0說明在另一個表中存在,0則不存在。
2、統計不重複的總人數
公式:C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
說明:用COUNTIF統計出每人的出現次數,用1除的方式把出現次數變成分母,然後相加。
四、求和公式
1、隔列求和
公式:H3
=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
說明:如果標題行沒有規則用第2個公式
2、單條件求和
公式:F2
=SUMIF(A:A,E2,C:C)
說明:SUMIF函數的基本用法
3、單條件模糊求和
公式:詳見下圖
說明:如果需要進行模糊求和,就需要掌握通配符的使用,其中星號是表示任意多個字符,如"*A*"就表示a前和後有任意多個字符,即包含A。
4、多條件模糊求和
公式:C11
=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
說明:在sumifs中可以使用通配符*
5、多表相同位置求和
公式:b2
=SUM(Sheet1:Sheet19!B2)
說明:在表中間刪除或添加表後,公式結果會自動更新。
6、按日期和產品求和
公式:F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
說明:SUMPRODUCT可以完成多條件求和
五、查找與引用公式
1、單條件查找公式
公式1:C11
=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、多條件查找
公式:詳見下圖
說明:公式原理同上一個公式
5、指定區域最後一個非空值查找
公式;詳見下圖
說明:略
6、按數字區域間取對應的值
公式:詳見下圖
公式說明:VLOOKUP和LOOKUP函數都可以按區間取值,一定要注意,銷售量列的數字一定要升序排列。
六、字符串處理公式
1、多單元格字符串合併
公式:c2
=PHONETIC(A2:A7)
說明:Phonetic函數只能對字符型內容合併,數字不可以。
2、截取除後3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
說明:LEN計算出總長度,LEFT從左邊截總長度-3個
3、截取-前的部分
公式:B2
=Left(A1,FIND("-",A1)-1)
說明:用FIND函數查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
說明:公式是利用強插N個空字符的方式進行截取
5、字符串查找
公式:B2
=IF(COUNT(FIND("河南",A2))=0,"否","是")
說明: FIND查找成功,返回字符的位置,否則返回錯誤值,而COUNT可以統計出數字的個數,這裏可以用來判斷查找是否成功。
6、字符串查找一對多
公式:B2
=IF(COUNT(FIND({"遼寧","黑龍江","吉林"},A2))=0,"其他","東北")
說明:設置FIND第一個參數爲常量數組,用COUNT函數統計FIND查找結果
七、日期計算公式
1、兩日期相隔的年、月、天數計算
A1是開始日期(2011-12-1),B1是結束日期(2013-6-10)。計算:
相隔多少天?=datedif(A1,B1,"d") 結果:557
相隔多少月? =datedif(A1,B1,"m") 結果:18
相隔多少年? =datedif(A1,B1,"Y") 結果:1
不考慮年相隔多少月?=datedif(A1,B1,"Ym") 結果:6
不考慮年相隔多少天?=datedif(A1,B1,"YD") 結果:192
不考慮年月相隔多少天?=datedif(A1,B1,"MD") 結果:9
datedif函數第3個參數說明:
"Y" 時間段中的整年數。
"M" 時間段中的整月數。
"D" 時間段中的天數。
"MD" 天數的差。忽略日期中的月和年。
"YM" 月數的差。忽略日期中的日和年。
"YD" 天數的差。忽略日期中的年。
2、扣除週末天數的工作日天數
公式:C2
=(IF(B2 說明:返回兩個日期之間的所有工作日數,使用參數指示哪些天是週末,以及有多少天是週末。週末和任何指定爲假期的日期不被視爲工作日 excel中最常用的30個函數2 ABS函數能求出相應數字的絕對值,AND函數能返回邏輯值,AVERAGE函數能求出所有參數的算術平均值,COLUMN能顯示所應用單元格的列標號值,CONCATENATE函數將多個字符文本或單元格中的數據連接在一起,顯示在一個單元格中。 COUNTIF函數能統計某個單元格中區域中符合指定條件的單元格數目,DATE函數可給出指定數值的日期,DATEDIF能計算返回兩個日期參數的差值,DAY函數能求出指定日期,DCOUNT函數能返回數據庫。 FREQUENCY能以一列垂直數組返回某個區域中數據的頻率分佈,IF能根據指定條件邏輯判斷真假結果,INDEX能返回列表或數組中的元素值,INT能將數值向下取整爲最接近的整數,ISERROR用於測試函數式返回數值是否有錯。 LEFT是能截取指定數目的字符,LEN能統計文本字符串中字符數目,MATCH能返回在指定方式下與指定數值匹配的數組中元素的相應位置,MAX可求出一組數中的'最大值,MID能截取指定數目的字符。 MIN可求出一組數中的最小值,MOD可求出兩數相除的餘數,MONTH能求出指定日期或引用單元格的日期月份,NOW能給出當前系統日期和時間,OR的功能是返回邏輯值。 RANK能返回數值在一列數值中相對於其他數值的排位,RIGHT可截取指定數目的字符,SUBTOTAL能返回列表數據或數據庫中的分類彙總,SUM用來計算所有參數值的和,SUMIF用於計算符合指定條件單元格區域內的數值和。 excel中最常用的30個函數3 excel函數公式大全 excel函數公式大全之一:AVERAGE 求平均值函數 計算一組數據的平均值數據 選擇一個單元,用來顯示平均值數據,在fx公式輸入欄裏邊輸入:=AVERAGE(D2:D4),其中D2 D4 爲要計算平均的數據範圍。如下圖所示,回車顯示平均值數據。 excel函數公式大全之二:MAX 求最大值函數 計算一組數據的最大值 選擇一個單元,用來顯示最大值數據,在fx公式輸入欄裏邊輸入:=MAX(D2:D4),其中D2 D4 爲要計算平均的數據範圍。如下圖所示,回車顯示最大值數據。 excel函數公式大全之三:MIN 求最小值 計算一組數據的最小值 選擇一個單元,用來計算最小值,在fx公式輸入欄裏邊輸入:=MIN(D2:D4),其中D2 D4 爲要計算平均的數據範圍。如下圖所示,回車顯示平均值數據。 excel函數公式大全之四:IF IF比較函數 對數據進行比較分組 下面來根據每小時的pm25數據,計算pm25等級,就要用到IF函數,在K列中選中K2單元格,點擊公式欄目中的Fx 字樣,打開公式選擇器,選中列表中的IF函數,點擊確定。 excel函數公式大全之五:IF IF比較函數 在logical_test 中輸入比較條件 F2>200,value_if_ture 輸入當f2>200時候顯示的結果,value_if_false 輸入單f2<=200 時候顯示結果。 選中K2單元格,使其變成粗黑色邊框,對中右下方的黑點,單擊下拉,把公式應用到所有的行中
防曬指數50跟30哪個好 防嗮指數怎麼選
Code Blue3好看嗎 Code Blue3劇情介紹
Alexander McQueen 2020春夏系列The Story Shoulder手袋
你會excel表格這個小技巧嗎?
Alexander McQueen新兇器
code blue第三季什麼時候播出 code blue3好看嗎
Offset個人資料簡介 彙編僞指令 函數說明 函數示例
excel表格中有大量空格怎麼去除
excel口紅什麼牌子 excel口紅怎麼樣
日劇code blue第三季講的什麼,code blue3分集劇情介紹
recollect Forget----painrecollect爲題短文
老師用愛情講函數什麼情況 當函數遇上愛情會發生什麼
yeezy350 Yecheil什麼時候發售 yeezy350 Yecheil怎麼樣
ecco bella脣釉怎麼樣?ecco bella脣釉好用嗎
初中三年中數學最難的題,盤點初中數學最難的3個點
cecilmcbee官網地址 cecilmcbee什麼檔次
excel表格製作的教程
excel最常用的八個函數 職場人士必須掌握的8個Excel函數
adidasTERREX X RICO LEE 一“件”穿梭,敢行未來
yeezy350 Cloud White發售時間 yeezy350 Cloud White圖片
Black Fleece在哪裏買 Black Fleece品牌介紹
excel表格的基本快捷鍵
Levercil口紅怎麼樣 Levercil是那個國家的牌子
excel的實用技巧
ALEXANDER MCQUEEN骷髏的魅力
中國新說唱cee是誰 cee個人資料年齡身高微博介紹
becca oceanjeewels系列彩妝有什麼 becca粉紫色高光多少錢
AlexanderMcQueen秀上這種瘋狂珠寶皆來源於ShaunLeane之手
excelsior是什麼牌子 excelsior鞋子尺碼偏大嗎
blanc&eclare中國官網 鄭秀妍blanc&eclare怎麼樣
h&m velvet lip cream 色號belle epoque試色
ecco bella口紅怎麼樣?ecco bella口紅好用嗎
elence 2001是什麼牌子哪裏的 elence 2001洗髮水怎麼樣