Excel公式与函数笔记—第二篇（简单通俗的基础介绍）

Excel公式与函数笔记—第二篇（简单通俗的基础介绍）

SUMIF函数

功能：对指定区域中符合条件的区域求和，即条件求和

三个参数：所有区域（可能）

AVERAGEIF:同理

SUMIFS 函数

功能：多个条件求和

多个参数：所有区域，条件1，条件2，…..，实际求和区域

 

MEDIAN函数

功能：返回一列数或一行数的中位数

参数：一列数

MODE函数

功能：返回一列数的众数

参数：一列数

 

RANK函数

功能：返回一个数在一组数中的排位是多少，即计算名次

三个参数：一是需要排位的值，二是排位区域，三是排序方式：升序或降序

RANK.EQ：同RANK

注意：RANK函数并列的话，同样计名词，如1 2 2 4

RANK.AVG

将并列的选项共同平均，如 1 2.5 2.5 4

 

LARGE:返回一列数中第k大的值

SMALL：返回一列数中第k大的值

两个参数：一个是一列数，二是k的值

OFFSET函数

功能：将某一个区域偏移到另一区域，可动态的通过偏移引用某个值

五个参数：第一个参考起点，第二个偏移行数，第三偏移列数，第四个区域行数，第五个区域的列数

 

INDIRECT函数

功能：返回通过一个文本所指定的引用

两个参数：一个是引用文本，二是引用类型，常省略

 

ROW函数

功能：返回单元格所在行

无参数时返回公式所在行，有时返回参数所在行，可用于序列自动排序等

COLUMN函数

功能：返回单元格所在列，参考ROW

ROWS函数

功能：返回一个区域包含的行

参数：一个区域

COLUMNS函数

功能：返回一个区域包含的列数

这几个函数常常和其他函数嵌套使用

 

ROUND函数

功能：对于指定位数对数字进行四舍五入

两个参数：一个是小数，二是保留的小数位数

应用：常用于解决工资合计中的一分钱误差，在财务工作中经常应用

ROUNDDOWN函数

功能：舍去小数，向下舍入，不进位，如45.37，取45.3

两个参数：一个是小数，二是保留的小数位数

ROUNDUP函数

功能：舍去小数，向上舍入，进位，如45.31，取45.4

两个参数：一个是小数，二是保留的小数位数

 

INT函数

功能：取整函数，向下舍入到最接近的函数，如111.6取111，-112.2，取-123

TRUNK函数：

功能：将数字的小数部分直接截断取整，可保留小数位数，类似ROUNDDOWN函数

两个参数：一个是小数，二是保留的小数位数

ABS函数:

功能：用于返回数字的绝对值。正数和0返回数字本身，负数则返回数字的相反数。

一个参数：函数语法：ABS(number)

 

MOD函数

功能：取余

两个参数：一是被除数，二是除数，返回余数

应用：常和其他函数嵌套使用

 

RAND函数

功能：返回0-1之间的随机数（小数），无参数

应用：抽样调查、产生随机数

RANDBETWEEN函数

功能：返回指定的两个数之间的一个随机整数

两个参数：一个是最小值，一个是最大值，返回的是二者之间的一个整数

注意：参数可是数值型，日期型等

 

CONVERT函数

功能：度量转换函数，换单位

三个参数，数值，原单位，要转换的单位，单位要用双引号

 

TODAY函数

功能：返回当前日期，无参数，自动刷新，ctrl+;也可实现但是不刷新

NOW函数

功能：返回当前时间，自动刷新 ctrl+shift+;也可实现，但是不刷新

YEAR函数

功能：提取日期的年份，一个参数

MONTH函数

功能：提取日期处于的月份，一个参数

DAY函数

功能：提取日期处于第几日，一个参数

HOUR函数

功能：提取时间的小时，一个参数

MINUTE函数

功能：提取时间的分钟，一个参数

SECOND函数

功能：提取时间的秒钟，一个参数

 

DATE函数

功能：返回表示特定日期的连续序列号，即将年月日结合在一起

三个参数：DATE(年,月,日)，返回三者组成的日期

TIME函数

功能：类似DATE函数，返回表示特定时间的连续序列号，即将小时 分钟秒结合在一起

三个参数：DATE(时,分,秒)，返回三者组成的时间

WEEKDAY函数

功能：返回特定日期所对应的星期数

两个参数：语法格式=weekday（特定日期，返回类型）

注意：中外星期天的起始值有差异，根据需要选择返回类型

WORKDAY函数

功能：返回从特定日期开始n个工作日后所对应的日期

三个参数： WORKDAY(开始日期,工作日,节假日)

NETWORKDAYS函数

功能：计算日期间的所有工作日

三个参数：NETWORKDAYS (开始日期,工作日,节假日)

注意：WORKDAY，NETWORKDAYS的周六周日自动扣除，节假日指的时除周六周日之外的法定节假日，可不填。

ENNMONTH函数

功能：返回一个日期当月或n个月后的月末日期

两个参数：ENNMONTH(日期,n)，当n为0时，返回该日期所在月份的月末日期，不为零返回该日期n个月后的月末日期

EDATE函数

功能：返回一个日期n个月后或n个月前的日期

 

DATEIF函数

功能：计算两个日期之间的间隔，可以是天数、月数、或年数

三个参数：DATEDIF(start_date,end_date,unit)

start_date：表示起始日期。

end_date：表示结束日期，end_date参数必须是在 start_date 之后的日期。

unit：要返回的信息类型。

       参数unit的信息类型：

y：计算起始日期和结束日期之间的年数

m：计算起始日期和结束日期之间的月数

d：计算起始日期和结束日期之间的天数

ym：计算起始日期和结束日期之间的月数（日期中的年和日都被忽略）

yd：计算起始日期和结束日期之间的天数（日期中的年被忽略）

md：计算起始日期和结束日期之间的天数（日期中的年和月被忽略）

注意：DATEDIF是隐藏函数，插入函数列表中没有此函数，需要手动输入。

 

LEN函数

功能：返回文本的字符个数，一个参数

LENB函数

功能：返回文本的字节数，一个参数

 

FIND函数

功能：返回待查找字符串在查找字符串中第一次出现的位置

三个参数：=find(find_text,within_text,start_num)

 find-text：查找的字符或数值

within-text：在哪个值或单元格找

start-num：从第几位找起,省略表示第一位查起。

注意：find函数中不能出现通配符，否则错误。而且查找区分大小写，查不到时显示value错误

 

SEARCH函数：查找时不区分大小写，语法同find

 

MID函数

功能：从一个字符串中截取出指定数量的字符

参数：MID(text, start_num, num_chars)

              text被截取的字符

 start_num从左起第几位开始截取（用数字表达）

 num_chars从左起向右截取的长度是多少（用数字表达）

LEFT函数

功能：返回字符串中最左边起指定数量的字符

LEFT函数的语法结构是LEFT(text, num_chars)，

其中text表示我们要截取的那个内容，num_chars表示要截取多少内容。

 

RIGHT函数

功能：从右边取字串，语法结构同LEFT

 

TRIM函数

功能：删除多余的空格

应用：两字名字为了对齐时中间有空格，会影响查找和匹配

注意：用查找替换直接替换空格比较方便

 

CLEAN函数

功能：删除字符串中非打印字符

一个参数

应用：网页粘贴等乱符号很多时

 

CONCATENATE函数

功能：本文合并，类似&符号的功能

多个参数：需要合并的文本

关于在excel表中隔行插入行与隔列插入列的问题

 

PROPER函数：将文字串的首字母转换成大写，其余的字母转换成小写

UPPER函数：将文字串的字母转换成大写

LOWER函数：将文字串的字母转换成小写

 

REPLACE函数

功能：指定位置进行替换，用新字符串替换旧字符串，而且替换的位置和数量都是指定的

四个参数：

=Replace(old_text,start_num,num_chars，new_text)

=replace（要替换的字符串，开始位置，替换个数，新的文本）

注意：第四个参数是文本，要加上引号。

IS函数：

常用的函数是ISERROR函数

功能：判断正确性

三个参数：

iserror(value)，其中value表示要判断的值或者单元格信息，

如果value是错误的，那么iserror显示的结果是TRUE

如果value没有错误，那么iserror显示的结果是FALSE

其中value错误的excel表现形式有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NA等

应用：可和IF函数嵌套使用，处理出现错误时的情况

 

TRANSPOSE函数

功能：和原表相链接的函数转置

参数：一个，被转置的区域

 

数组函数：

功能：快速查询与计算

数组函数计算最终结果是ctrl+shift+enter(以后再详细补充)