Common Used Excel Formulas
一、數(shù)字處理
1、取絕對(duì)值
=ABS(數(shù)字)
2、取整
=INT(數(shù)字)
3、四舍五入
=ROUND(數(shù)字,小數(shù)位數(shù))
二、判斷公式
1、把公式產(chǎn)生的錯(cuò)誤值顯示為空
公式：C2
=IFERROR(A2/B2,””)
說明：如果是錯(cuò)誤值則顯示為空，否則正常顯示。
2、IF多條件判斷返回值
公式：C2
=IF(AND(A2<500,B2=”未到期”),”補(bǔ)款”,””)
說明：兩個(gè)條件同時(shí)成立用AND,任一個(gè)成立用OR函數(shù).
三、統(tǒng)計(jì)公式
1、統(tǒng)計(jì)兩個(gè)表格重復(fù)的內(nèi)容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
說明：如果返回值大于0說明在另一個(gè)表中存在，0則不存在。
2、統(tǒng)計(jì)不重復(fù)的總?cè)藬?shù)
公式：C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
說明:用COUNTIF統(tǒng)計(jì)出每人的出現(xiàn)次數(shù)，用1除的方式把出現(xiàn)次數(shù)變成分母，然后相加。
四、求和公式
1、隔列求和
公式：H3
=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
說明：如果標(biāo)題行沒有規(guī)則用第2個(gè)公式
2、單條件求和
公式：F2
=SUMIF(A:A,E2,C:C)
說明：SUMIF函數(shù)的基本用法
3、單條件模糊求和
公式：詳見下圖
說明：如果需要進(jìn)行模糊求和，就需要掌握通配符的使用，其中星號(hào)是表示任意多個(gè)字符，如”*A*”就表示a前和后有任意多個(gè)字符，即包含A。
4、多條件模糊求和
公式：C11
=SUMIFS(C2:C7,A2:A7,A11&”*”,B2:B7,B11)
說明：在sumifs中可以使用通配符*
5、多表相同位置求和
公式：b2
=SUM(Sheet1:Sheet19!B2)
說明：在表中間刪除或添加表后，公式結(jié)果會(huì)自動(dòng)更新。
6、按日期和產(chǎn)品求和
公式：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最擅長(zhǎng)的，基本用法
2、雙向查找公式
公式：
=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值
3、查找最后一條符合條件的記錄。
公式：詳見下圖
說明：0/(條件)可以把不符合條件的變成錯(cuò)誤值，而lookup可以忽略錯(cuò)誤值
4、多條件查找
公式:詳見下圖
說明:公式原理同上一個(gè)公式
5、指定區(qū)域最后一個(gè)非空值查找
公式;詳見下圖
說明：略
6、按數(shù)字區(qū)域間取對(duì)應(yīng)的值
公式：詳見下圖
公式說明：VLOOKUP和LOOKUP函數(shù)都可以按區(qū)間取值，一定要注意，銷售量列的數(shù)字一定要升序排列。
六、字符串處理公式
1、多單元格字符串合并
公式：c2
=PHONETIC(A2:A7)
說明：Phonetic函數(shù)只能對(duì)字符型內(nèi)容合并，數(shù)字不可以。
2、截取除后3位之外的部分
公式：
=LEFT(D1,LEN(D1)-3)
說明：LEN計(jì)算出總長(zhǎng)度,LEFT從左邊截總長(zhǎng)度-3個(gè)
3、截取-前的部分
公式:B2
=Left(A1,FIND(“-“,A1)-1)
說明：用FIND函數(shù)查找位置，用LEFT截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1,” “,REPT(” “,20)),20,20))
說明:公式是利用強(qiáng)插N個(gè)空字符的方式進(jìn)行截取
5、字符串查找
公式：B2
=IF(COUNT(FIND(“河南”,A2))=0,”否”,”是”)
說明: FIND查找成功，返回字符的位置，否則返回錯(cuò)誤值，而COUNT可以統(tǒng)計(jì)出數(shù)字的個(gè)數(shù)，這里可以用來判斷查找是否成功。
6、字符串查找一對(duì)多
公式：B2
=IF(COUNT(FIND({“遼寧”,”黑龍江”,”吉林”},A2))=0,”其他”,”東北”)
說明：設(shè)置FIND第一個(gè)參數(shù)為常量數(shù)組，用COUNT函數(shù)統(tǒng)計(jì)FIND查找結(jié)果
七、日期計(jì)算公式
1、兩日期相隔的年、月、天數(shù)計(jì)算
A1是開始日期(2011-12-1)，B1是結(jié)束日期(2013-6-10)。計(jì)算：
相隔多少天?=datedif(A1,B1,”d”) 結(jié)果：557
相隔多少月? =datedif(A1,B1,”m”) 結(jié)果：18
相隔多少年? =datedif(A1,B1,”Y”) 結(jié)果：1
不考慮年相隔多少月?=datedif(A1,B1,”Ym”) 結(jié)果：6
不考慮年相隔多少天?=datedif(A1,B1,”YD”) 結(jié)果：192
不考慮年月相隔多少天?=datedif(A1,B1,”MD”) 結(jié)果：9
datedif函數(shù)第3個(gè)參數(shù)說明：
“Y” 時(shí)間段中的整年數(shù)。
“M” 時(shí)間段中的整月數(shù)。
“D” 時(shí)間段中的天數(shù)。
“MD” 天數(shù)的差。忽略日期中的月和年。
“YM” 月數(shù)的差。忽略日期中的日和年。
“YD” 天數(shù)的差。忽略日期中的年。
2、扣除周末天數(shù)的工作日天數(shù)
公式：C2
=NETWORKDAYS.INTL(IF(B2
說明：返回兩個(gè)日期之間的所有工作日數(shù)，使用參數(shù)指示哪些天是周末，以及有多少天是周末。周末和任何指定為假期的日期不被視為工作日
三. Matching and Return value crossing different columns
8.1?
=IF( COUNTIF(‘Servers’!A:A, A3)=0, “No”, “Yes”)
Check if A3 value is in worksheet “Servers” column A. If found , show Yes, else, show No
8.2?
=VLOOKUP(A3,’Z:\0 Operation\1 Scan\Servers\Jan 2019\[Scan_Report_Server_Urgent_Vuls.xlsx]APP IP’!A:H,8,)
Check if A3 value found in the file “Z:\0 Operation\1 Scan\Servers\Jan 2019\[Scan_Report_Server_Urgent_Vuls.xlsx” – worksheet “APP IP’ – Column A to H. If found, return same row’s , eighth column’s value.?
四. Excel Formula : Convert a text to NumberGot a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is ‘ ?4 ‘.
My challenge is to convert?whole column to numbers which can be used to do sum or other math calculation.
With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))))
it perfectly resolved this challenge as you can see from following screen shot.
Note: trim will not work with the only space cell. Thanks?reply?from?Hari Krishna.
五. Excel Formula: Search a Column of Strings to Match Another Column
Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.
Column O includes all exported ip addresses. I have to manually search if those column O’s ip addresses are appearing in column B’s text. If yes, which row is it?
Formula is set at Column N.
=MATCH(“*”&(O6)&”*”,B:B,0)
As you can see from cell N6, the number is 7, which means the text in B7 includes O6’s string.
N12’s number is 5, which means the test in B5 includes O12’s string.?
Share this:
- More
Like this:
Like Loading...Related
總結(jié)
以上是生活随笔為你收集整理的Common Used Excel Formulas的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： lolS4赛季打野符文选取
- 下一篇： 魔兽世界怀旧服元素套牌作用解析 元素套牌