今天学习了excel的公式和函数，常用的还没学完，先做好笔记，都是比较常用的东西，对未来分析数据很有用，明天继续加油

3 excel的公式和函数

3.1 excel常用公式

3.1.1给单元格提供内容的方式：

a.直接输入数据 - 输入什么是什么，单元格内容就是什么

b.“=单元格地址” - 直接引用单元格内容

c.“=Excel公式” - 计算公式结果类给单元格提供内容（公式中涉及的数据可以是具体数据，也可以是引用单元格内容）

d.“=函数调用表达式” - 获取函数返回值，来提供单元格内容

3.1.2锁定行列(固定单元格地址) - $

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SliHSMeK-1661256782145)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823192348105.png)]

3.1.3单列数据转换成多列

方法1：找到第一行和原始数据的引用关系，写出引用，然后直接往下拖

方法2: 直接通过写出引用地址关系，然后将A变成""

可以用Ctrl+H,进行替换，将A替换成=A

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TmVmZh90-1661256782146)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823192445536.png)]

3.1.4多列数据转为一列数据

操作步骤: 1. 通过引用复制原来的数据

​ 2. 在第一列的最后引用第二列第一个数

​ 3.按住第一列最后添加的单元格往右拖，再往下托（拖100行）

​ 4.选择性粘贴第一列数据的数值，删除多余数据

3.1.5跨工作表公式计算

操作方法：“！”的作用

直接=，然后点击工作表里面需要的位置再加上其它工作表需要的位置，然后回车

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uo8Lz7YE-1661256782146)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823192707730.png)]

3.2 excel常用函数

3.2.1 文本函数

1.LEFT(文本数据，个数=1)

从左向右取指定个数的值

从文本字符串的第一个字符开始返回指定个数的字符

注意：文本数据可以是双引号引起来的字符串也可以是单元格内容

LEFTB(文本数据，个数=2)

从左向右取指定个数的值，语言的一个字就是两个数字

基于所指定的字节数返回文本字符串中的第一个或前几个字符

LEFTB()函数并不适用于所有语言，只有在将 DBCS 语言设置为默认语言时，函数 LEFTB 才会将每个字符按 2 个字节计数。 否则，函数 LEFTB 的行为与 LEFTB 相同，即将每个字符按 1 个字节计数。
支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

下方的RIGHTB()、MIDB()函数同理

2.RIGHT(文本数据，个数=1)

根据所指定的字符数返回文本字符串中最后一个或多个字符

从右向左取，但是也会按顺序取

RIGHTB(文本数据，个数=1)

根据所指定的字节数返回文本字符串中最后一个或多个字符

从右向左取，但是也会按顺序取

3.MID(文本，开始位置，取得数量)

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

取文本中需要的元素

MIDB(文本，开始位置，取得数量)

取文本中需要的元素

4.LEN(文本)

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

LENB(文本)

返回文本字符串中用于代表字符的字节数。

5.FIND(被查对象，文本) - 区分大小

用于从一个文本串中定位另一个文本串的起点并返回字符数

FINDB(被查对象，文本)

6.SEARCH(被查对象，文本) - 不区分大小写

SEARCHB(被查对象，文本)

7.REPLACE(文本，开始位置，数量，替换成什么) - 替换，必须知道位置

将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串

REPLACEB()

8.LOWER(文本)

将一个文本字符串中的所有大写字母转换为小写字母。

9.UPPER(文本)

将文本转换为大写字母。

10.REPT(重复对象，次数)

将文本重复一定次数

11.SUBSTITUTE(文本，需要替换的，新的) 就是resplace

在文本字符串中用新文本替换旧文本

12.TRIM(文本)

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

13.TEXT(文本，自定义格式) - 自定义单元格格式

14.VALUE(文本)

将表示数字的文本字符串转换为数字

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xu5Wryae-1661256782146)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823144824878.png)]

文本函数:		忍一时风平浪静，退一步越想越气。
LEFT()	从文本字符串的第一个字符开始返回指定个数的字符	a
LEFTB()	基于所指定的字节数返回文本字符串中的第一个或前几个字符	忍一
RIGHT()	根据所指定的字符数返回文本字符串中最后一个或多个字符	想越气。
RIGHTB()	根据所指定的字节数返回文本字符串中最后一个或多个字符	气。
MID()	返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。	时风平浪
MIDB()	根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。	一时
LEN()	返回文本字符串中的字符个数	16
LENB()	返回文本字符串中用于代表字符的字节数。	32
FIND()	用于从一个文本串中定位另一个文本串的起点并返回字符数	3
FINDB()	用于从一个文本串中定位另一个文本串的起点并返回字节数
SEARCH()	用于从一个文本串中定位另一个文本串的起点并返回字符数	3
SEARCHB()	用于从一个文本串中定位另一个文本串的起点并返回字节数
REPLACE()	将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串	忍二风平浪静，退一步越想越气。
REPLACEB()	将旧字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串
LOWER()	将一个文本字符串中的所有大写字母转换为小写字母。	m你好abc
UPPER()	将文本转换为大写字母。	MNI你好
REPT()	将文本重复一定次数	忍一时风平浪静，退一步越想越气。忍一时风平浪静，退一步越想越气。
SUBSTITUTE()	在文本字符串中用新文本替换旧文本	忍二时风平浪静，退二步越想越气。
TRIM()	除了单词之间的单个空格之外，移除文本中的所有空格	a bmn c d m 9
TEXT()	自定义单元格格式（可点击超链接）	¥12.35
VALUE()	将表示数字的文本字符串转换为数字	1000

可以利用LEN和LENB结果的差值来计算一个字符串中中文字符的个数

LEFTB()函数并不适用于所有语言，只有在将 DBCS 语言设置为默认语言时，函数 LEFTB 才会将每个字符按 2 个字节计数。 否则，函数 LEFTB 的行为与 LEFTB 相同，即将每个字符按 1 个字节计数。支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

下方的RIGHTB()、MIDB()函数同理

FIND()和SEARCH()区别：FIND()区分大小写，SEARCH()不区分大小写。FIND()的find_text参数不能写通配符，SEARCH()的find_text参数可以写通配符

3.2.2 数学函数

1.ABS(数据) - 绝对值

2.INT(数据) - 向小取整（在相邻的两个整数取小的那个）

3.MOD(数据1，数据2) - 相除的余数

4.RAND() - [0,1)之间的随机数

5.RANDBETWEEN(数据1，数据2) - [a,b]之间的随机整数

6.ROUND(数据，需要保留几位小数) - 保留小数

将数字四舍五入到指定位数

7.SQRT(数据) - 开方

8.SUM(数据地址1：数据地址2) - 和

9.SUMIF(条件范围，条件，求和范围) - 对范围中符合指定条件的值求和

（1）条件范围：与条件相关的范围；

（2）条件：需要判断的条件，如果条件需要引用单元格内容，单元格地址放引号外面，并且在单元格地址前加&；

（3）求和范围：需要求和的范围。

注意：

（1）条件要用双引号引起来

（2）求和范围和条件范围一样可以省略求和范围

（3）条件可以用*作为通配符代表任意符号，如果需要表示*号本身，而不是通配符，就在*前加~号

（4）excel中比较运算符不等于<>

10.SUMIFS(求和范围，条件范围1，条件1，条件范围2，条件2，…)

注意：1）条件需要用双引号引起来

​ 2）求和范围如果和条件范围一样，求和范围可以省略

​ \3) 如果条件中需要引用单元格内容，将单元格地址放在引号外面

​ ，并且在单元格底之前加"&"

​ 4)条件中可以用*作为通配符代表任意符号出现任意次数；

​ 如果想要在条件中表示本身，而不是通配符，在*前加~

EXCEL中的比较运算符：

> 大于

< 小于

= 等于

>= 大于等于

<= 小于等于

<> 不等于

数学函数
ABS()	返回数字的绝对值	23
INT()	向小取整	-3
MOD()	返回两数相除的余数（结果的符号和余数相同）	1
RAND()	[0,1)之间的随机数	0.519199812
RANDBETWEEN()	[a,b]之间的随机整数	26
ROUND()	将数字四舍五入到指定位数	2.342
SQRT()	返回算术平方根	4
SUM()	将单个值、单元格引用或者区域相加，或者将三者的组合相加	111
SUMIF()	对范围中符合指定条件的值求和
SUMIFS()	计算满足多条件的全部参数的值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eFMW97r4-1661256782147)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823193043851.png)]

3.2.3 统计函数

1.AVERAGE() - 平均值，指定范围内所有数字的和除以数字的个数

AVERAGEA() - 求指定范围内所有数字的和，除以所有数据的个数

AVERAGEIF(条件范围，条件，平均范围)

AVERAGEIFS(平均范围，条件范围1，条件1，条件范围2，条件2，…)

2.COUNT() - 统计数字的个数

COUNTA() - 不分类型数据中的个数

COUNTIF(条件范围，条件) - 统计满足条件的个数

COUNTIFS(条件范围，条件)

3.FREQUENCY(需要分的数据，分段的段) - 以垂直数组的形式返回频率分布(分箱)，节点可以取到，分箱后，剩下的会单独求个数

分段的段：单独写出来，60就是<=60

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2GuuvgFq-1661256782147)(C:\Users\yang1\AppData\Roaming\Typora\typora-user-images\image-20220823164355709.png)]

4.MAX()/MIN()

5.RANK(指定的数，数据) - 返回指定序列在里面排第几名，如果有多个相同的返回第一个（可以和之前的兼容）

RANK.AVG(指定的数，数据) - 返回指定序列在里面排第几名，如果有多个相同的返回他们相同排名的平均排名

RANK.EQ(指定的数，数据) - 返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位（跟第一个差不多，兼容性不同，兼容2007后面的）

统计函数:
AVERAGE()	返回其参数的平均值(求指定范围内容所有数字的和再除以数字的个数)	25
AVERAGEA()	返回其参数的平均值，包括数字、文本和逻辑值（求指定范围内容所有数字的和再除以所有数据的个数）	25
AVERAGEIF()	返回区域中满足给定条件的所有单元格的平均值
AVERAGEIFS()	返回满足多个条件的所有单元格的平均值
COUNT()	计算参数列表中数字的个数	5
COUNTA()	计算参数列表中值的个数	8
COUNTIF()	计算区域内符合给定条件的单元格的数量
COUNTIFS()	计算区域内符合多个条件的单元格的数量
FREQUENCY()	以垂直数组的形式返回频率分布(分箱)
MAX()/MIN()	返回参数列表中的最大（最小）值	40
RANK()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小）	1
RANK.AVG()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位）	1
RANK.EQ()	返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位	1

个条件的单元格的数量 | |
| FREQUENCY() | 以垂直数组的形式返回频率分布(分箱) | |
| MAX()/MIN() | 返回参数列表中的最大（最小）值 | 40 |
| RANK() | 返回一列数字的数字排位（数字排位是相对于列表中其他值的大小） | 1 |
| RANK.AVG() | 返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位） | 1 |
| RANK.EQ() | 返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位 | 1 |