|
一、數(shù)字處理
1、取絕對值函數(shù)
公式：=ABS(數(shù)字)
2、取整函數(shù)
公式：=INT(數(shù)字)
3、四舍五入函數(shù)
公式：=ROUND(數(shù)字,小數(shù)位數(shù))
二、判斷公式
1、如果計算的結(jié)果值錯誤那么顯示為空
公式：=IFERROR(數(shù)字/數(shù)字,)
說明：如果計算的結(jié)果錯誤則顯示為空，否則正常顯示。
2、IF語句的多條件判定及返回值
公式：IF(AND(單元格（邏輯運算符）數(shù)值,指定單元格=返回值1),返回值2,)
說明：所有條件同時成立時用AND,任一個成立用OR函數(shù)。
三、常用的統(tǒng)計公式
1、統(tǒng)計在兩個表格中相同的內(nèi)容
公式:B2=COUNTIF(數(shù)據(jù)源:位置,指定的，目標(biāo)位置)
說明：如果返回值大于0說明在另一個表中存在，0則不存在。
如果，在此示例中所用到的公式為：B2=COUNTIF(Sheet15!A:A,A2)
2、統(tǒng)計不重復(fù)的總數(shù)據(jù)
公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
說明:用COUNTIF函數(shù)統(tǒng)計出源數(shù)據(jù)中每人的出現(xiàn)次數(shù)，并用1除的方式把變成分?jǐn)?shù)，最后再相加。
四、數(shù)據(jù)求和公式
1、隔列求和的應(yīng)用
公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
說明：如果在標(biāo)題行中沒有規(guī)則就可以用第2個公式
2、單條件應(yīng)用之求和
公式：F2=SUMIF(A:A,C:C)
說明：這是SUMIF函數(shù)的最基礎(chǔ)的用法
五、查找與引用公式
1、單條件查找
說明：VLOOKUP是excel中最常用的查找方式
六、字符串處理公式
1、多單元格字符串的合并
說明：Phonetic函數(shù)只能合并字符型數(shù)據(jù)，不能合并數(shù)值
2、截取結(jié)果3位之外的部分
說明：LEN計算總長度,LEFT從左邊截總長度-3個
七、日期計算相關(guān)
1、日期間相隔的年、月、天數(shù)計算
A2是開始日期（2011-12-2），B2是結(jié)束日期(2013-6-11)。計算：
相差多少天的公式為：=datedif(A2,B2,d) 其結(jié)果：557
相差多少月的公式為： =datedif(A2,B2,m) 其結(jié)果：18
相差多少年的公式為： =datedif(A2,B2,Y) 其結(jié)果：1
不考慮年份相隔多少月的公式為：=datedif(A1,B1,Ym) 其結(jié)果：6
不考慮年份相隔多少天的公式為：=datedif(A1,B1,YD) 其結(jié)果：192
不考慮年份月份相隔多少天的公式為：=datedif(A1,B1,MD) 其結(jié)果：9
datedif函數(shù)第3個參數(shù)說明：
Y 時間段中的整年數(shù)。
M 時間段中的整月數(shù)。
D 時間段中的天數(shù)。
MD 日期中天數(shù)的差。忽略月和年。
YM 日期中月數(shù)的差。忽略日和年。
YD 日期中天數(shù)的差。忽略年。
擴(kuò)展資料：
工程函數(shù)
BESSELI返回經(jīng)過修改的貝塞爾函數(shù)IN(X)
BESSELJ 返回貝塞爾函數(shù)JN(X)
BESSELK返回經(jīng)過修改的貝塞爾函數(shù)KN(X)
BESSELY返回貝塞爾函數(shù)YN(X)
XLFCTBIN2DEC、BIN2DEC 將二進(jìn)制數(shù)轉(zhuǎn)換為十進(jìn)制數(shù)
BIN2HEX 將二進(jìn)制數(shù)轉(zhuǎn)換為十六進(jìn)制數(shù)
BIN2OCT將二進(jìn)制數(shù)轉(zhuǎn)換為八進(jìn)制數(shù)
COMPLEX 將實系數(shù)和虛系數(shù)轉(zhuǎn)換為復(fù)數(shù)
CONVERT 將一種度量單位制中的數(shù)字轉(zhuǎn)換為另一種度量單位制
DEC2BIN 將十進(jìn)制數(shù)轉(zhuǎn)換為二進(jìn)制數(shù)
DEC2HEX 將十進(jìn)制數(shù)轉(zhuǎn)換為十六進(jìn)制數(shù)
DEC2OCT 將十進(jìn)制數(shù)轉(zhuǎn)換為八進(jìn)制數(shù)
DELTA 檢測兩個值是否相等
ERF 返回誤差函數(shù)
ERFC 返回余誤差函數(shù)
GESTEP 檢測數(shù)字是否大于某個閾值
HEX2BIN 將十六進(jìn)制數(shù)轉(zhuǎn)換為二進(jìn)制數(shù)
HEX2DEC 將十六進(jìn)制數(shù)轉(zhuǎn)換為十進(jìn)制數(shù)
HEX2OCT 將十六進(jìn)制數(shù)轉(zhuǎn)換為八進(jìn)制數(shù)
IMABS 返回復(fù)數(shù)的絕對值（模）
IMAGINARY 返回復(fù)數(shù)的虛系數(shù)
IMARGUMENT 返回參數(shù)THETA，一個以弧度表示的角
IMCONJUGATE 返回復(fù)數(shù)的共軛復(fù)數(shù)
IMCOS 返回復(fù)數(shù)的余弦
IMDIV 返回兩個復(fù)數(shù)的商
IMEXP 返回復(fù)數(shù)的指數(shù)
IMLN 返回復(fù)數(shù)的自然對數(shù)
IMLOG10 返回復(fù)數(shù)的常用對數(shù)
IMLOG2 返回復(fù)數(shù)的以2為底數(shù)的對數(shù)
IMPOWER 返回復(fù)數(shù)的整數(shù)冪
IMPRODUCT 返回兩個復(fù)數(shù)的乘積
IMREAL 返回復(fù)數(shù)的實系數(shù)
IMSIN 返回復(fù)數(shù)的正弦
IMSQRT 返回復(fù)數(shù)的平方根
IMSUB 返回兩個復(fù)數(shù)的差
IMSUM 返回兩個復(fù)數(shù)的和
OCT2BIN 將八進(jìn)制數(shù)轉(zhuǎn)換為二進(jìn)制數(shù)
OCT2DEC 將八進(jìn)制數(shù)轉(zhuǎn)換為十進(jìn)制數(shù) A, digital processing
1, the absolute value function
Formula: = ABS (digital)
2, the integral function
Formula: INT = (number)
3, rounded function
Formula: = ROUND (Numbers, decimal digits)
Second, the judgment formula
1, if the result of calculation value error display is empty
Formula: = IFERROR (digital/digital)
Note: if the result of the calculation error is displayed is empty, otherwise the normal display.
2, determine IF statements more and return values
Formula: IF (AND (cell (logical operators), specify cell = return value 1), 2, the return value)
Note: all conditions was founded with the AND at the same time, as a form using the OR function.
Three, the commonly used statistical formula
1, statistics on two forms of the same content
Formula: B2 = COUNTIF (data source: location, the specified target location)
Note: if the return value is greater than 0 in another table, zero does not exist.
If, in the example used formula is: B2 = COUNTIF (Sheet15!A: A, A2)
2, not to repeat the total number of statistics according to
Formula: C2 = SUMPRODUCT (1 / COUNTIF (A2: A8, A2: A8))
Description: the source data using COUNTIF function in the occurrences of each, and the way in the 1 turn into fractions, then add.
Fourth, data summation formula
The application of 1, every column sum
Formula: H3 = SUMIF ($A $2: $G $2, $2 H, A3: G3) or = SUMPRODUCT ((MOD (COLUMN (B3: G3), 2) = 0) * B3: G3)
Note: if there are no rules in the header row can use the second formula
2, single application conditions of peace
Formula: F2 = SUMIF (A: A, C, C)
Explanation: this is the most basic usage of SUMIF function
Five, find and formula for reference
1, single condition
Description: VLOOKUP is the most commonly used excel to find way
Six, string handling formula
More than 1, the cell string
Description: Phonetic function can only be merged, character data cannot be merged
2, intercepting results outside of the three parts
Description: LEN calculation, the total length of LEFT from the LEFT - the total length of 3
Seven, date calculations
1, during the years, months, days of computing
A2 is the start date (2011-12-2), B2 is end date (2013-6-11).Calculation:
Formula is: how much day = datedif (A2, B2, d) as a result, 557
Difference formula is: how many month = datedif (A2, B2, m) as a result: 18
How many years difference formula is: = datedif (A2, B2, Y) as a result: 1
Regardless of the years apart, the formula is: how many month = datedif (A1, B1, Ym) as a result: 6
Don't consider the formula is: year how many days apart = datedif (A1, B1, YD) as a result: 192
Regardless of the year in how many days apart, the formula is: = datedif (A1, B1, MD) as a result: 9
Third parameter datedif function description:
Y time period in a year.
M the number of whole month in a period.
D time period the number of days.
Number of days in the date of MD is poor.Ignore the month and year.
YM date in months.Ignore the day and year.
YD date in the days of the poor.Ignore the years.
Extended information:
Engineering function
BESSELI returns the modified Bessel function IN (X)
BESSELJ return Bessel function Jacqueline Nottingham (X)
BESSELK returns the modified Bessel function KN (X)
BESSELY return Bessel function YN (X)
XLFCTBIN2DEC, BIN2DEC () function converts a binary number to a decimal number
BIN2HEX () function converts a binary number to a hexadecimal number
BIN2OCT converts a binary number to octal number
COMPLEX transform coefficient of real and virtual coefficient for plural
CONVERT converts a the number in the metric system of units to another metric system of units
DEC2BIN convert decimal number to a binary number
DEC2HEX converts a decimal number to a hexadecimal number
DEC2OCT () function converts a decimal number octal number
The DELTA test two values are equal
ERF return error function
ERFC return error function
GESTEP test number is greater than a certain threshold
HEX2BIN convert hexadecimal number to a binary number
HEX2DEC convert hexadecimal number to a decimal number
HEX2OCT convert hexadecimal number to an octal number
IMABS returns the absolute value of the plural (die)
IMAGINARY return the plural coefficient
IMARGUMENT return parameter THETA, an Angle in radians
IMCONJUGATE returns the complex conjugate complex
IMCOS returns the cosine of the plural
IMDIV returned two plural
IMEXP returns the index of the plural
IMLN return the natural logarithm of plural
IMLOG10 return the logarithm of plural
IMLOG2 returns the plural with 2 for the base of the logarithm
IMPOWER returns the integer power of plural
Return to the product of two complex IMPRODUCT
IMREAL returns the plural coefficient
IMSIN returns the sine of the plural
IMSQRT returns the square root of the plural
IMSUB returned two plural
Two plural and IMSUM returned
OCT2BIN converts octal number to a binary number
OCT2DEC converts octal number to a decimal number
|
