EXCEL应用操作（一）函数

你的微光

已于 2023-11-21 12:20:20 修改

阅读量817

点赞数

分类专栏： Excel基础知识文章标签： excel

于 2020-11-24 17:22:04 首次发布

本文链接：https://blog.csdn.net/weixin_45469056/article/details/110074379

版权

Excel基础知识专栏收录该内容

1 篇文章 0 订阅

订阅专栏

EXCEL是学习计算机最常用的软件，今天向大家介绍EXCEL的函数部分，相对其他几个方面是比较难的，但使用也是最为广泛的。
函数实际上是Excel 预定义的一种内置公式。它通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算。函数可用于执行简单或复杂的计算。
在这里插入图片描述

函数难点

在这里插入图片描述

函数思路

在这里插入图片描述

函数工具与技巧

在这里插入图片描述

分一下几个部分介绍：
（一）数字函数
（二）文本函数
（三）统计函数
（四）逻辑函数
（五）日期函数
（六）查找和引用函数
（七）组合函数
首先

（一）数字函数

INT(number)----------------------------------------------取整
MOD(number,被除数)----------------------------------求余数
ROUND(number,小数位）----------------------------四舍五入
ROUNDDOWN(数）-----------------------------------向下取整
roundup（数）------------------------------------------向上舍入
ABS(number)--------------------------------------------取绝对值
SQRT(number)----------------------------------------- 算术平方根
RAND（）---------------------------------------------- -产生随机数
randbetween(底，顶）--------------------------------范围内生成随机数

（二）文本函数

▶ LEN-----------------------------------------------文本长度
▶ MID-----------------------------------------------取子串
▶ LEFT-----------------------------------------------从左取子串
▶ RIGHT-----------------------------------------------从右取子串
▶ TEXT-----------------------------------------------数字转化为文本格式
在这里插入图片描述

▶ REPLACE（文本，开始，替换位数，新的）-替换特定位置处的内容
▶ SUBSTITUTE-----------------------------------替换文本
▶ FIND-----------------------------------------------查找

（三）统计函数

▶MAX-------------------------------------求最大
▶MIN-------------------------------------求最小
▶SUM-------------------------------------求和
▶COUNT-------------------------------------计数
▶COUNTA-------------------------------------数值个数
▶average -------------------------------------求平均
▶countif-------------------------------------条件计数
COUNTIF18用法
在这里插入图片描述

▶sumif-------------------------------------条件求和
在这里插入图片描述

▶averageif------------------------------------条件求平均
▶countifs-------------------------------------多条件计数
▶sumifs-------------------------------------多条件求和
▶averageifs-------------------------------------多条件求平均
▶frequency-------------------------------------求数据分布频率
在这里插入图片描述

（四）逻辑函数

▶IF-------------------判断
▶AND-------------------与（都）
▶OR-------------------或（之一）
▶NOT-------------------非

（五）日期函数

▶ 1、=TODAY()
▶ 取当前的系统日期
▶ 2、=NOW()
▶ 取当前系统日期和时间
▶ 3、= NOW()-TODAY()
▶ 计算当前是几点几分。也可以用=MOD(NOW(),1)计算。
▶ 4、=YEAR(TODAY())
▶ 取当前日期的年份
▶ 5、=MONTH(TODAY())
▶ 取当前日期的月份
▶ 6、=DAY(TODAY())
▶ 计算当前日期是几号。
▶ 7、=WEEKDAY(TODAY(),2)
▶ 计算今天是星期几。第二参数是2，表示将星期一计算为1，这样比较符合国人的
习惯。也可以写成=TEXT(TODAY(),“aaa”)
▶ 8、=EDATE(TODAY(),1)
▶ 计算当前日期之后一个月的日期。
▶ 如果第二参数写成 -1，就是计算当前日期之前1个月的日期。
▶ 9、=EOMONTH(TODAY(),1)
▶ 计算下个月最后一天的日期。
▶ 如果第二参数写成0，是本月最后一天的日期。用=DAY(EOMONTH(TODAY(),0))
计算出当前这个月一共有多少天了。
▶ 10、=EOMONTH(TODAY(),0)-TODAY()
▶ 计算今天到本月底还有多少天。
▶ 11、=IF(COUNT(-“2-29”),“是”,“否”)
▶ 计算今年是不是闰年。
▶ 在Excel中如果输入“月/日”形式的日期，会默认按当前年份处理。如果当前年份中没有2月29日，输入"2-29"就会作为文本处理。如果当前年份没有2月29日，"2-29"前面加上负号，就相当于在文本前加负号，会返回错误值#VALUE!。再用COUNT函数判断-"2-29"是数值还是错误值，如果是错误值，当然就不是闰年了。注意，"2-29"的写法在不同的语言版本中结果可能有差异。

（六）查找和引用函数

▶VLOOKUP----------------------垂直查找
▶HLOOKUP----------------------水平查找
▶OFFSET----------------------计算偏移量
▶MATCH----------------------查找位置
▶INDIRECT----------------------文本字符串指定引用
▶ROW----------------------引用行数据
▶COLUMN----------------------引用列数据
▶CHOOSE----------------------索引列指定多数列表中数据
▶FIND----------------------一个字符串在另一个字符串的起始位置
▶INDEX----------------------查找数据

▶ OFFSET引用函数（动态图表、动态透视表等）

在这里插入图片描述
使用特点
▶ 设置所需要的区域
▶ 若结果为1个单元格,其值显示在公式所在单元格
▶ 若结果为多行多列,使用数组公式方法,其值显示所在区域
▶ 使用在：
▶ 单元格区域
▶ 求和、平均、最大最小的统计区域
▶ 查找函数的范围参数

▶ MATCH引用函数

▶ 功能:在单元格区域中搜索指定项，然后返回该项在单元格区域中的相
对位置。
▶ MATCH(lookup_value, lookup_array, [match_type])
▶ 例如，如果单元格区域 A1:A3 包含值 5、25 和 38，则以下公式：
=MATCH(25,A1:A3,0)
▶ 会返回数字 2，因为值 25 是单元格区域中的第二项。
▶ match_type 可选:
▶ -1:查找大于或等于 lookup_value 的最小值,参数中的值必须按降序排列
▶ 0 : 查找等于 lookup_value 的第一个值
▶ 1: (默认值)查找小于或等于 lookup_value 的最大值,参数中的值必须按升序排列
在这里插入图片描述

▶ INDEX查找函数

▶ INDEX(array,row_num,[column_num])
▶ 功能：返回单元格或数组中的数据或元素值，此元素由行号和列号的索引值来给
定。
▶ Array——单元格区域或数组常量。
▶ Row_num——数组中某行的行号，函数从该行返回数值。如果省略 row_num，
则必须有 column_num。
▶ Column_num——数组中某列的列标，函数从该列返回数值。如果省略
column_num，则必须有 row_num。
在这里插入图片描述

▶ ROW和COLUMN获取行、列号

在这里插入图片描述

▶ INDIRECT函数

▶ INDIRECT(ref_text,A1)
▶ 功能：返回由文本字符串指定的引用。
▶ Ref_text :为对单元格的引用，此单元格可以包含 A1-样式的引用、
R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如
果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值
#REF!。
▶ A1:为一逻辑值，指明包含在单元格 ref_text 中的引用的类型。
▶ 如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
▶ 如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。

▶ （VLOOKUP、HLOOKUP查找函数）

在这里插入图片描述

▶ SUMPRODUCT数组函数

SUMPRODUCT(array1, [array2], [array3], …)
▶ 功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
SUMPRODUCT函数是执行多条件汇总计算的领军人物
▶ 如果是多条件求和，可以这样写：
▶ =SUMPRODUCT(条件一条件二……条件N求和区域)
▶ 如果是多条件计数，只要将最后的求和区域拿掉就可以哦：
▶ =SUMPRODUCT(条件一条件二*……条件N)
▶ 案例
▶ 而OFFSET函数、INDIRECT函数、SUMPRODUCT函数是中级水平验明正身的标杆函数，不会这三个函数难说自己已经是中级水平。

（七）组合函数

什么是数组？
数组是具有某种联系的多个元素的组合。某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。Excel里，班级就相当于工作表，而学生就相当于工作表
的单元格数据。Excel数组，理解是为多个单元格数值的集合。
— —— 主要解决问题：快速处理多行多列数据
在这里插入图片描述
数组优点
无需考虑公式若复制时设置地址引用的相对还是绝对
有时，无需复制公式，就可以直接获取多个结果单元格
批量处理数据，效率高
逻辑关系清晰

3种方法实现商品的销售总金额计算。

方法1:插入辅助列，先求出各商品的销售额，然后再求总和。
方法2:直接输入类似公式“=SUM(B2C2,B3C3,B4*C4)”，效率低，实现费时。尤其有100行数据，一千行号数据呢？
数组：方法3：输入公式：=SUM(B2:B41*C2:C41)，按CTRL+SHIFT+ENTER结束公式输入。
CTRL+SHIFT+ENTER {} 数组计算
ALT+=
sum求和
数组公式用F2可以进入编辑