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. 借助“数据条”直观分析数据。
     
• 2. 使用“色阶”创建热图。
     
• 3. 借助“图标集”为数据分类。
     
• 4. 条件格式的新建、删除、编辑。
     

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()： 返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位