|
一、数字处理
1、取绝对值函数
公式：=ABS(数字)
2、取整函数
公式：=INT(数字)
3、四舍五入函数
公式：=ROUND(数字,小数位数)
二、判断公式
1、如果计算的结果值错误那么显示为空
公式：=IFERROR(数字/数字,)
说明：如果计算的结果错误则显示为空，否则正常显示。
2、IF语句的多条件判定及返回值
公式：IF(AND(单元格（逻辑运算符）数值,指定单元格=返回值1),返回值2,)
说明：所有条件同时成立时用AND,任一个成立用OR函数。
三、常用的统计公式
1、统计在两个表格中相同的内容
公式:B2=COUNTIF(数据源:位置,指定的，目标位置)
说明：如果返回值大于0说明在另一个表中存在，0则不存在。
如果，在此示例中所用到的公式为：B2=COUNTIF(Sheet15!A:A,A2)
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,C:C)
说明：这是SUMIF函数的最基础的用法
五、查找与引用公式
1、单条件查找
说明：VLOOKUP是excel中最常用的查找方式
六、字符串处理公式
1、多单元格字符串的合并
说明：Phonetic函数只能合并字符型数据，不能合并数值
2、截取结果3位之外的部分
说明：LEN计算总长度,LEFT从左边截总长度-3个
七、日期计算相关
1、日期间相隔的年、月、天数计算
A2是开始日期（2011-12-2），B2是结束日期(2013-6-11)。计算：
相差多少天的公式为：=datedif(A2,B2,d) 其结果：557
相差多少月的公式为： =datedif(A2,B2,m) 其结果：18
相差多少年的公式为： =datedif(A2,B2,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 日期中天数的差。忽略年。
扩展资料：
工程函数
BESSELI返回经过修改的贝塞尔函数IN(X)
BESSELJ 返回贝塞尔函数JN(X)
BESSELK返回经过修改的贝塞尔函数KN(X)
BESSELY返回贝塞尔函数YN(X)
XLFCTBIN2DEC、BIN2DEC 将二进制数转换为十进制数
BIN2HEX 将二进制数转换为十六进制数
BIN2OCT将二进制数转换为八进制数
COMPLEX 将实系数和虚系数转换为复数
CONVERT 将一种度量单位制中的数字转换为另一种度量单位制
DEC2BIN 将十进制数转换为二进制数
DEC2HEX 将十进制数转换为十六进制数
DEC2OCT 将十进制数转换为八进制数
DELTA 检测两个值是否相等
ERF 返回误差函数
ERFC 返回余误差函数
GESTEP 检测数字是否大于某个阈值
HEX2BIN 将十六进制数转换为二进制数
HEX2DEC 将十六进制数转换为十进制数
HEX2OCT 将十六进制数转换为八进制数
IMABS 返回复数的绝对值（模）
IMAGINARY 返回复数的虚系数
IMARGUMENT 返回参数THETA，一个以弧度表示的角
IMCONJUGATE 返回复数的共轭复数
IMCOS 返回复数的余弦
IMDIV 返回两个复数的商
IMEXP 返回复数的指数
IMLN 返回复数的自然对数
IMLOG10 返回复数的常用对数
IMLOG2 返回复数的以2为底数的对数
IMPOWER 返回复数的整数幂
IMPRODUCT 返回两个复数的乘积
IMREAL 返回复数的实系数
IMSIN 返回复数的正弦
IMSQRT 返回复数的平方根
IMSUB 返回两个复数的差
IMSUM 返回两个复数的和
OCT2BIN 将八进制数转换为二进制数
OCT2DEC 将八进制数转换为十进制数 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
|
