Day2 数据分析 Excel-基础函数【零基础】

Day2 数据分析 Excel-基础函数

Excel常用公式

1. Excel基础公式

1.1 数学运算

在Excel中进行加+、减-、乘*、除/运算，无需使用内置函数，直接使用即可

在Excel中所有公式皆以=开头

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

2.1 直接输入数据——输入的是什么，显示的就是什么

2.2 =单元格地址——直接引用单元格的内容

a. 单元格地址的引用

引用单元格地址的时候没有锁定的时候，如果引用地址没有锁定，往下拉单元格行号增加，往右拉列号增加；如果要锁定，只需要在需要锁定的行号或者列号前面加上符号$

示例	解释
D1	行列都没锁定
$D1	列号锁定
D$1	行号锁定
$D$1	行列都锁定

b. 单元格地址的引用之一列变多列

方法1：找到第一行和原始数据的引用关系，写出第一行地址，然后直接往下拖
方法2：直接写出引用地址关系然后通过替换将地址变成引用

c. 单元格地址引用之多列变一列

1.通过引用复制原来的数据。
2.在第一列的最后引用第二列第一个数。
3.在第一列最后添加的单元格往右拖，再往下拖，拖到数据完成为止。
4.选择性粘贴第一列的数据的数值，删除多余数值。

d. 合并数据之跨表引用

'表名'!引用的表中单元格地址

2.3 =Excel公式——计算公式结果给单元格提供内容

2.4 =函数调用表达式——获取函数返回值来给单元格提供内容

3、Excel中的通配符

• ?（问号）：表示单个字符，用于匹配数字;

• *（星号）：表示任意数量的字符，用于匹配字符;

• ~（波浪符）：将问号和星号转换为普通字符，而不是作为通配符使用;

❀tips: 在EXCEL页眉页脚中希望出现1个“&”，则需要输入2个"&"

4、Excel中的字符串连接

="abc" & "123"  -->   "abc123"
=A1&A2			-->   (将A1中内容和A2中的内容连接成一个字符串)
=A1&"abc"		-->	  (将A1中的内容和字符串"abc"连接成一个字符串)

Excel常用函数

1、文本函数

	A	B	C	D
1	函数	释义	示例	忍一时风平浪静，退一步越想越气。
2	LEFT()	从文本字符串的第一个字符开始返回指定个数的字符	=LEFT(D1，8)	忍一时风平浪静，
3	LEFTB()	基于所指定的字节数返回文本字符串中的第一个或前几个字符	=LEFT(D1,4)	忍一
4	RIGHT()	根据所指定的字符数返回文本字符串中最后一个或多个字符	=RIGHT(D1,4)	想越气。
5	RIGHTB()	根据所指定的字节数返回文本字符串中最后一个或多个字符	=RIGHT(D1,4)	气。
6	MID()	返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。	=MID(D1,3,4)	时风平浪
7	MIDB()	根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。	=MID(D1,3,4)	一时
8	LEN()	返回文本字符串中的字符个数	=LEN(D1)	16
9	LENB()	返回文本字符串中用于代表字符的字节数。	=LENB(D1)	32
10	FIND()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=FIND(“a”,“xyabc”)	3
11	FINDB()	用于从一个文本串中定位另一个文本串的起点并返回字节数
12	SEARCH()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=SEARCH(“Ab”,“xyabc”)	3
13	SEARCHB()	用于从一个文本串中定位另一个文本串的起点并返回字节数
14	REPLACE()	将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串	=REPLACE(H1,2,1,“二”)	忍二时风平浪静，退一步越想越气。
15	REPLACEB()	将旧字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串
16	LOWER()	将一个文本字符串中的所有大写字母转换为小写字母。	=LOWER(“m你好ABC”)	m你好abc
17	UPPER()	将文本转换为大写字母。	=UPPER(“mni你好”)	MNI你好
18	REPT()	将文本重复一定次数	=REPT(“你好”,2)	你好你好
19	SUBSTITUTE()	在文本字符串中用新文本替换旧文本	=SUBSTITUTE(“三一四”,“一”,“二”)	三二四
20	TRIM()	除了单词之间的单个空格之外，移除文本中的所有空格
21	TEXT()	自定义单元格格式，超链接	=TEXT(12.345678,“￥#.00”)	￥12.35
22	VALUE()	将表示数字的文本字符串转换为数字	=VALUE(“2011/3/4”)	40606

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

​ 2、FIND()和SEARCH()区别：FIND()区分大小写，SEARCH()不区分大小写。

​ FIND()的find_text参数不能写通配符，SEARCH()的find_text参数可以写通配符

2、数学函数

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

SUMIF&SUMIFS

❀SUMIF(条件范围,条件, 求和范围)

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

❀SUMIFS(求和范围,条件范围1,条件1, 条件范围2,条件2,…)

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

3、统计函数

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

4、日期函数

DATE()	根据年、月、日三个数值返回其表示的日期	=DATE(2022,3,2)	2022/3/2
DATEVALUE()	返回由文本字符串表示的日期的十进制数字
TIME()	根据时、分、秒三个数值返回其表示的时间的十进制格式	=TIME(2,30,23)	2:30 AM
TIMEVALUE()	返回由文本字符串表示的时间的十进制数字
DAY()	返回序列数表示在某月的天数	2022/8/1	1
MONTH()	返回序列数表示在某年的月份	2022/8/23	8
YEAR()	返回序列数表示年份	2022/8/23	2022
SECOND()	返回时间值的秒数	2:34:12	12
MINUTE()	返回时间值的分钟数	2:34:13	34
HOUR()	返回时间值的小时数	2:34:14	2
TODAY()	返回当前日期	=TODAY()	2022/8/24
NOW()	返回当前日期和时间	=NOW()	2022/8/24 20:14
EDATE()	返回与某个日期相隔N个月的日期的序列数
EOMONTH()	返回与指定日期相隔N个月份数的月份的最后一天
DATEDIF()	计算两个日期之间的天数、月数、年数

DATADIF

5、逻辑函数

函数	释义	示例	结果
AND()	用于测试所有条件是否均为TRUE	=AND(5>4,3<5)	TRUE
OR()	用于测试是否有为TRUE的条件
NOT()	将TRUE或者False的结果取反
TRUE()	返回TRUE
FALSE()	返回FALSE
IF()	判断单个条件是否为真	=IF(MOD(22,2)=0,“偶数”,“奇数”)	偶数
IFS()	检查是否满足一个或多个条件，且返回第一个符合条件的值	=IFS(K8>=90,“优”,K8>=70,“良好”,K8<70,“一般”)	一般
IFERROR()	使用 IFERROR 函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。	=IFERROR(FIND(“k”,“abc”),“找不到”)	找不到
SWITCH()	根据值列表计算一个值，并返回与第一个匹配值对应的结果，如果不匹配，则可能返回可选默认值。	=SWITCH(INT(K8/10),9,“优”,8,“良好”,7,“中等”,“及格”)	及格

IF()&IFS()

if(条件，条件成立时的结果，条件不成立时的结果)
IFS(条件，结果1，条件2，结果2，......)
注意：后面的条件是在前面条件不成立时才判断
SWITCH(运算表达式，值1，结果1，值2，结果2，......)
计算运算表达式，如果是值1，最后的结果是结果1，如果是值2，最后的结果是结果2.....

6、查找与引用函数

1、COLUMN()

返回与列号对应的数字；不给参数时返回所在单元格的列数

2、ROW()

返回与行号对应的数字；不给参数时返回所在单元格的行数

3、SORT()

对某个区域或数组的内容进行排序

sort(排序序列，排序依据对应的列号，排序方式)
如果没有给出排序依据对应的列号，默认按照选中数据第一列的大小排序
排序方式：1表示升序（默认），-1 表示降序

4、SORTBY()

对某个区域按照某列数据进行排序（默认升序）

SORTBY(排序序列，第一个排序标准对应的列号，排序方式1，第二个排序标准对应的列号，排序方式2，......)
排序标准是通过提供比较对象的一列数据来确定

5、UNIQUE()

对某范围数据进行去重

UNIQUE(去重对象,行列去重方式，去重方式)
行列去重方式：如果去重对象是一列数据--按行去重FALSE
			如果去重对象是一行数据--按列去重TRUE
去重方式：如果是FALSE(默认)--返回原数据去掉重复数据后的结果
        如果是TRUE--返回原数据中没有重复项的数据

6、GETPIVOTDATA()

返回数据透视表中的可见数据

7、LOOKUP()

在一行或者一列中查找某个值并从另一行或者列中找到同位置的值

LOOKUP(查找对象，查找范围，查找结果范围)，查找对象所在列在查找对象前面的数据必须是有序的

8、VLOOKUP()按行查找表格或区域内容

VLOOKUP(查找对象,同时包含查找对象和查找结果列，查找结果列在前面给出的列中的序号，查找方式) 
查找方式:TRUE--近视匹配，查找对象所在列本身有序的时候使用
	    FALSE--精确匹配，查找对象所在列本身无序的时候使用
如果查找结果的列在查找对象列的前面则无法查找

9、HLOOKUP()在表格的首行或数值数组中搜索值，然后返回表格或数组中所在列的指定行中的值

10、XLOOKUP()按行查找表格或区域内容

XLOOKUP(查找对象，查找对象所在列，查找结果列)