27 - Excel 的基本公式和重要函数(Excel入门下)

Day 27

学习Excel前需要明确的事:

  • 在学习Excel函数之前,大家要明确一件事,Excel现在涉及到了一些新函数,这些新函数可能只存在于Office365、Office2019及更新版本之中,所以建议大家在学习时安装较新的版本,但是因为电脑系统、电脑环境的原因,大家量力而行。后续函数均有详细版本标识,请仔细辨别。
  • 另外,在 Windows RT 计算机上创建公式和使用 Excel 工作表函数时,您可能会发现其计算结果与基于传统的 x86 体系结构的 PC 上的结果有细微的不同。 这是由于 Windows RT 中使用的处理器的设计和基于 x 86 的计算机中使用的处理器的设计之间存在差异。 这些差异发生在非常高的精度级别中,如第 14个或第 15 个有效位。

配置要求:

  1. windows7系统最高只能安装office2016,安装最低要求:office2016。
  2. 如果电脑安装有office365、office2019、office2021等版本,无须重新安装。
  3. 因各种原因安装有比office2016更低版本,请卸载后安装新版本。
  4. 因电脑系统限制(如windows7系统)请大家自行解决调整。

一、Excel 单元格自定义格式,条件格式

1.更改单元格文本格式

  • 对整个单元格以及单元格中的数据应用格式:一种方式是,更改单元格中文本格式,可以将单元格中文本格式改为数值、货币、时间等Excel定义格式,也可以改为按照一定规则设定的自定义格式。
  • 另一种方式是,将单元格想象成图片框,框中的图片就是数据。 设置单元格的格式可能包括添加边框、添加颜色或阴影,或更改单元格的大小和样式。
    在这里插入图片描述

2.自定义单元格格式

自定义格式语法
1).# 数字占位符:四舍五入;不保存无意义的0
2).0 数字占位符:四舍五入;会保存无意义的0
3).?数字占位符:四舍五入;用空格保存无意义的0
4).,千分位符:使用数字占位符的时候可以设置逗号分割的位数
5).@ 文本占位符:在格式中代表文本数据本身
6). * 重复文本*号后的符号,一直到把单元格填满为止
7).【颜色1】【条件1】格式1:【颜色2】【条件2】格式2:【颜色3】格式3;【颜色4】格式4
在这里插入图片描述
在这里插入图片描述

3.条件格式

  • 条件格式设置

    1. 借助“数据条”直观分析数据。
      在这里插入图片描述
    1. 使用“色阶”创建热图。
      在这里插入图片描述
    1. 借助“图标集”为数据分类。
      在这里插入图片描述
    1. 条件格式的新建、删除、编辑。
      在这里插入图片描述

4.拆分

  • 在我们日常获取数据的时候或许得到的数据比较杂乱,需要对数据进行些拆分处理。
  • 操作:数据 --> 分列

在这里插入图片描述
在这里插入图片描述

二、Excel 的基本公式

1.有关公式、单元格和区域的详细信息

  • Excel 由组成行和列的单个单元格构成。行采用数字编号,列采用字母标明。共有一百多万行和 16,000 多列,可将公式放入其中任何位置。 公式可包含单元格引用、单元格引用范围、运算符和常数。以下均为公式的示例:
    =A1+B1
    =10+20
    =SUM(A1:A10)
  • 你会注意到,上面的第三个示例中使用了 SUM 函数。函数是预先构建的命令,可采用一个值或多个值,以特定方式进行计算并返回结果。例如,SUM 函数采用单元格引用或指定区域,并对它们求和。在此示例中,它使用单元格 A1 到 A10,并对它们求和。Excel 拥有 400 多个函数,你可以在“公式”选项卡上进行浏览。
  • 带有函数的公式以等号开头,随后是函数名称,后接用括号括起来的参数(函数用于计算的值)。
  • 按 Enter 即可确认公式。执行该操作后,公式将进行计算,结果将显示在单元格中。若要查看公式本身,可查看功能区下方的编辑栏,或按 F2 进入“编辑”模式,你将在单元格中看到公式。再次按 Enter 完成公式并计算结果。

在这里插入图片描述

2.给单元格提供内容的方式:

  • 1)直接输入数据 - 输入的是什么,单元格内容就是什么
  • 2)“=单元格地址" - 直接引用单元格内容
  • 3)“=Excel公式” - 计算公式结果来给单元格提供内容(公式中涉及到的数据可以是具体的数据也可以是引用单元格内容)
  • 4)“=函数调用表达式” - 获取函数返回值,来提供单元格内容

3.单元格地址的引用

  • 引用单元格内容的时候,如果引用地址没有锁定,往下拉单元格行号不断加1,往右拉单元格,列号不断加1

  • D1 - 行号和列号都没有锁定

  • $D$1 - 行号和列号都锁定

  • $D1 - 列号锁定,行号不锁定

  • D$1 - 行号锁定,列号不锁定

  • 示例:实现员工工资上调20%
    在这里插入图片描述+ 示例:公式计算本利和,只需输入一个公式,其他复制而得。在这里插入图片描述

单列变多列

在这里插入图片描述

  • 思路:
  • 方法1:找到第一行和原始数据的引用关系,写出引用,然后直接往下拖
    例如本题,要求每一列20行,那么第二列为21第三列为41
    我们就可以得出

=A1 =A21 =A41 =A61 =A81

然后利用下拉填充即可完成。

  • 方法2: 直接通过写出引用地址关系,然后将A变成""
    想到方法一我们再进一步,就可以联想出对应地址关系得出

第一行为:A1 A21
第二行为:A2 A22

再利用等差数列填充,最后进行查找替换,将A换成 =A即可。
在这里插入图片描述

多行转单行

  • 操作步骤:
  1. 通过引用复制原来的数据
  2. 在第一列的最后引用第二列第一个数
  3. 按住第一列最后添加的单元格往右拖,再往下托(拖100行)
  4. 选择性粘贴第一列数据的数值,删除多余数据
    在这里插入图片描述

4.知识点:跨工作表公式计算

  • 在日常处理数据的时候,我们还有可能需要的数据分为了两张或者多张数据,我们可以将数据整理在不同的工作表,在计算时是可以实现夸表计算的。
    在这里插入图片描述

三、Excel 中重要的函数

一、文本函数

  • LEFT():从文本字符串的第一个字符开始返回指定个数的字符
  • LEFTB():基于所指定的字节数返回文本字符串中的第一个或前几个字符
  • RIGHT():根据所指定的字符数返回文本字符串中最后一个或多个字符
  • RIGHTB():根据所指定的字节数返回文本字符串中最后一个或多个字符
  • MID():返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。
  • MIDB():根据您指定的字节数,返回文本字符串中从指定位置开始的特定数目的字符。
  • LEN():返回文本字符串中的字符个数
  • LENB():返回文本字符串中用于代表字符的字节数。
  • FIND():用于从一个文本串中定位另一个文本串的起点并返回字符数
  • FINDB():用于从一个文本串中定位另一个文本串的起点并返回字节数
  • SEARCH():用于从一个文本串中定位另一个文本串的起点并返回字符数
  • SEARCHB():用于从一个文本串中定位另一个文本串的起点并返回字节数
  • REPLACE():将旧字符串的指定位置(字符下标)指定字符数的字符串替换为新字符串
  • REPLACEB():将旧字符串的指定位置(字节下标)指定字节数的字符串替换为新字符串
  • LOWER():将一个文本字符串中的所有大写字母转换为小写字母。
  • UPPER():将文本转换为大写字母。
  • REPT():将文本重复一定次数
  • SUBSTITUTE():在文本字符串中用新文本替换旧文本
  • TRIM():除了单词之间的单个空格之外,移除文本中的所有空格
  • TEXT():自定义单元格格式(可点击超链接)
  • VALUE():将表示数字的文本字符串转换为数字

2.数学函数

ABS(): 返回数字的绝对值
INT(): 向小取整
MOD(): 返回两数相除的余数(结果的符号和余数相同)
RAND(): [0,1)之间的随机数
RANDBETWEEN(): [a,b]之间的随机整数
ROUND(): 将数字四舍五入到指定位数
在这里插入图片描述

SQRT(): 返回算术平方根
SUM(): 将单个值、单元格引用或者区域相加,或者将三者的组合相加
SUMIF(): 对范围中符合指定条件的值求和

在这里插入图片描述

SUMIFS(): 计算满足多条件的全部参数的值

  • EXCEL中的比较运算符:
    < 小于、 >大于、= 等于、>= 大于等于、<= 小于等于、<> 不等于

  • 注意:
    1)条件需要用双引号引起来
    2)求和范围如果和条件范围一样,求和范围可以省略
    3) 如果条件中需要引用单元格内容,将单元格地址放在引号外面并且在单元格底之前加"&"
    4)条件中可以用作为通配符代表任意符号出现任意次数;
    如果想要在条件中表示*本身,而不是通配符,在前加~

在这里插入图片描述

3.统计函数

AVERAGE(): 返回其参数的平均值(求指定范围内容所有数字的和再除以数字的个数)
AVERAGEA(): 返回其参数的平均值,包括数字、文本和逻辑值(求指定范围内容所有数字的和再除以所有数据的个数)
AVERAGEIF(): 返回区域中满足给定条件的所有单元格的平均值
AVERAGEIFS(): 返回满足多个条件的所有单元格的平均值
COUNT(): 计算参数列表中数字的个数
COUNTA(): 计算参数列表中值的个数
COUNTIF(): 计算区域内符合给定条件的单元格的数量

在这里插入图片描述

COUNTIFS(): 计算区域内符合多个条件的单元格的数量

在这里插入图片描述

FREQUENCY(): 以垂直数组的形式返回频率分布(分箱)

在这里插入图片描述

MAX():/MIN(): 返回参数列表中的最大(最小)值
RANK(): 返回一列数字的数字排位(数字排位是相对于列表中其他值的大小)
RANK.AVG(): 返回一列数字的数字排位(数字排位是相对于列表中其他值的大小,如果多个值具有相同的排位,则返回平均排位)
RANK.EQ(): 返回一列数字的数字排位,其大小与列表中其他值相关,如果多个值具有相同的排位,则返回该组值的最高排位

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值