首页 > 代码库 > EXCEL常用函数公式

EXCEL常用函数公式



1    EXCEL基本操作

1      基本概念:工作簿,工作表,行,列,单元格

2      数据录入

       批量录入相同数据(ctrl+enter)

       ***号码输入(设置单元格属性为“文本”或者前面加 ’ )

       分数输入(直接输1/3EXCEL默认为1月3日,应该写成0空格 1/3)

       单元格里换行(alt+enter)

3      查找 :模糊查找、值查找

4      冻结窗口

5      筛选:一般筛选,空行筛选

6      行列转置

7      插入对象:对象格式的设置

8      数据剔重和分列

9      下拉菜单制作

10  格式刷使用

 

2    EXCEL通用运算符

分类

运算符号

运算符含义

示例

算术运算

+(加号)

2+3=5

-(减号)

3-1=2

*(星号

3*2=6

/(斜杠)

6/2=3

%(百分号)

百分号

50%

^(脱字号)

乘方

4^3=43=64

文本

&

可以将文本连接起来

"zhang"&"YL"

比较

=(等号)

相等

B1=C1,若B1中单元格内的值确实与C1中的值相等,则产生逻辑真值TRUE,若不相等,则产生逻辑假值FALSE

<(小于号)

小于

B1<C1

>(大于号)

大于

B1>C1,若B1中数值为6,C1中数值为4,则条件成立产生逻辑真值TRUE,否则产生逻辑假值FALSE

>=(大于等于号)

大于等于

B1>=C1

<>(不等号)

不等于

B1<>C1

<=(小于等于号)

小于等于

B1<=C1

引用运算符

:

区域运算符,产生对包括在丙相引用之间的所有单元格的引用

(A5:A15)

,

联合运算符,将多个引用合并为一个引用

SUM(A5:A15,C5:C15)

(空格)

交叉运算符,产生对两个引用构有的单元格的引用

(B7:D7 C6:C8)

 

 

3    EXCEL函数

       单元格引用方式

相对应用  A1、B1

绝对引用  $A$5、$C$3

混合引用  $A5、C$3

4    函数的常见错误

错误值类型

含义

#####

当使用了负的日期或负的时间时,出现错误

#VALUE!

当使用错误的参数或运算对象类型时,出现错误

#DIV/O!

当公式被零(0)除时,出现错误

#NAME?

在公式中使用了Excel不能识别的文本时,出现错误

#N/A

当在函数或公式中没有可用数值时,出现错误

#REF!

当单元格引用无效时,出现错误

#NUM!

当公式或函数中某个数字有问题时,出现错误

#NULL!

两个并不相交的区域指定交叉点时,出现错误

 

 

 

5    文本类函数和公式

       &,concatenate:将文本连接起来

 

       FIND(find_text,   within_text,   start_num)   Search()

查找其他文本字符串 (within_text)内的文本字符串 (find_text),并从within_text 的首字符开始返回find_text 的起始位置编号

 

       MID(text,  start_num,  num_chars)

返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

       TRIM(text)

除了单词之间的单个空格外,清除文本中所有的空格

 

       LEN(text)

返回文本字符串中的字符个数。

 

       TEXT(value,  format_text)

将数值转换为以指定数字格式表示的文本。

 

       LEFT(text,  num_chars)

根据所指定的字符数,返回文本字符串中第一个字符或前几个字符。

 

       RIGHT(text,    num_chars)

根据指定的字符数,返回文本字符串的最后一些字符。

 

       Upper,Lower,Proper

 

 

6    统计类函数和公式-count

       COUNT(value1,value2,...)

计算包含数字的单元格以及参数列表中数字的个数。使用 COUNT 可以获取区域或数字数组中数字字段的输入项的个数。

 

       COUNTA(value1,value2,...)

计算参数列表中非空值的单元格数量。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格数量。

 

       COUNTBLANK(range)

计算指定单元格区域中空白单元格的个数。

 

       COUNTIF(range,criteria)

计算区域中满足给定条件的单元格的个数。

       COUNTIFS(range1,criteria1,   range2,criteria2,...)

–      计算区域中满足多个条件的单元格个数。

 

7    统计类函数和公式-avg

       AVERAGE(number1,number2,...)

返回参数的平均值(算术平均值)。

 

       AVERAGEA(value1,value2,...)   --注意不要使用

计算参数列表中值的平均值(算术平均值)。 除了数字,计算中还包括文本和逻辑值,如 TRUE 和FALSE。

 

       AVERAGEIF(range,  criteria,    average_range)

返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)。

 

       AVERAGEIFS(average_range,    criteria_range1,criteria1,   criteria_range2,criteria2…)

返回满足多重条件的所有单元格的平均值(算术平均值)。

 

8    统计类函数和公式-sum

       SUM(number1,number2,...)

计算单元格区域中所有数值的和。

 

       SUMIF(range,   criteria,  sum_range)

根据指定条件对若干单元格求和。

 

       SUMIFS(sum_range, criteria_range1,criteria1,criteria_range2,criteria2…)

对区域中满足多个条件的单元格求和。     

 

       SUMPRODUCT(array1,array2,array3,...) -注意:不要使用

将给定数组中的对应元素相乘,并返回这些乘积之和。

例子:=SUMPRODUCT(A1:B3,C1:D3)     将两个数组中的所有元素相乘,然后将乘积相加 — 即 3*2 + 4*7 + 8*6 +6*7 + 1*5 + 9*3。(156)

 

 

9    搜索类函数和公式

       VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

搜索表格第一列中的值,然后返回表格指定列中相同行上的值。当比较值位于所需查找的数据的左边一列时

 

       HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

返回符合特定值特定顺序的项在数组中的相对位置。如果需要获取某个项在区域中的位置而不是项本身时,请使用MATCH 函数,而不是某个LOOKUP 函数。

 

       MATCH(lookup_value,lookup_array,match_type)

返回符合特定值特定顺序的项在数组中的相对位置。如果需要获取某个项在区域中的位置而不是项本身时,请使用MATCH 函数,而不是某个LOOKUP 函数。

 

       INDEX(reference,row_num,column_num,area_num)

返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成,可以选择某一选定区域。

 

 

 

10       判断类函数和公式

       IF(logical_test,  value_if_true,  value_if_false)

如果您指定的条件计算为 TRUE 返回一个值,并且如果计算为 FALSE 返回为另外一个值。

 

       IFERROR()

       ISERROR(value) 

引用任何错误值(  #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!),则返回逻辑值 TRUE;否则,返回 FALSE。

 

       ISERR(value)

引用错误值(#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)

 

       ISNA(value)

引用错误值(  #N/A),则返回逻辑值 TRUE;否则返回 FALSE。

 

       ISBLANK(value) ,如果是空单元格,则返回逻辑值TRUE;否则返回FALSE。

 

       ISTEXT(value) ,如果是字符,则返回逻辑值TRUE;否则返回FALSE。

 

       ISNUMBER(value) ,如果是数值,则返回逻辑值TRUE;否则返回FALSE。

 

 

 

11       通配符

以下通配符可作为筛选以及查找和替换内容时的比较条件

请使用

若要查找

?(问号)

任何单个字符

例如,sm?th 查找“smith”和“smyth”

*(星号)

任何字符数

例如,*east 查找“Northeast”和“Southeast”

 

 

12       排序类函数和公式

       RANK(number,ref,order)

返回一个数字在数字列表中的排位。其大小与数字列表中的他值相关。

 

       RANK.EQ(number,ref,order)

返回一个数字在数字列表中的排位。其大小与列表中的其他值相关。如果多个值具有相同的排位,则返回该组数值的最高排位。(如果要对列表进行排序,则数字排位可作为其位置。)

 

       RANK.AVG(number,ref,order)

返回一个数字在数字列表中的排位。其大小相对于列表中的其他值;如果有一个以上的值排位相同,则返回平均排位。

 

       SMALL(array,k)

返回数据集中第k 个最小值。使用此函数可以返回数据集中特定位置上的数值。

 

       LARGE(array,k)

返回数据集中第k 个最大值。使用此函数可以根据相对标准来选择数值。例如,可以使用LARGE 返回第一名、第二名或第三名的得分。

 

13       数值处理类函数和公式

       MAX(number1,number2,...)

返回一组值中的最大值。


       MIN(number1,number2,...)

 返回一组值中的最小值。


       MAXA(value1,value2,...)

返回参数列表中的最大值。除了数字,计算中还包括文本和逻辑值,如TRUE 和FALSE。


       MINA(value1,value2,...)

返回参数列表中的最小值。 除了数字,计算中还包括文本和逻辑值,如 TRUE 和FALSE。


       ROUND(number,num_digits)

可将某个数字四舍五入为指定的位数。


       ROUNDDOWN(number,num_digits)

靠近零值,向下舍入数字。


       ROUNDUP(number,num_digits)

远离 0(零)值,向上舍入数字。


       INT(number) 

将数字向下舍入到最接近的整数。


       TRUNC(number,num_digits)

将数字的小数部分截去,返回整数。

 


EXCEL常用函数公式