Excel

Excel是Office办公软件中的组件之一。

图1 Excel 图2 Office

一、基础

1.1 了解Excel与使用

• Excel专长于对表格中的数据进行计算和统计管理，通常用于财务或其他数据管理的表格制作。同时Excel还有很好的可视化能力，可用于制作各种行业报告。

• Excel表格文件后缀名：.xls和.xlsx

• 为什么.csv不是？
  csv是最通用的一种文件格式，csv文件本质是文本文件，默认使用逗号作为分隔符号，很多
  软件都兼容csv文件。

工作簿 就是我们常说的表格文件。
工作表 就是我们下方切换的N个活动页，创建一个工作簿的时候会自动的创建一个工作表。
单元格 就是我们现在能够看到的每一个方格。

保护功能

1.保护工作簿： 防止其他用户对工作簿的结构进行更改，如移动、删除或者添加工作表。
2.保护工作表： 通过限制其他用户的编辑能力来防止他们进行不需要的更改。
3.保护单元格： 防止数据被篡改。

操作方法

1.保护单元格：选中单元格单元格 --> 开始 --> 格式 --> 锁定单元格、保护工作表
2.保护工作表：在指定工作表右键 --> 保护工作表
3.保护工作簿：审阅 --> 保护工作簿

1.2 输入

• 填充柄

选中单元格，鼠标放置到单元格右下角后右键向下拖拽，选择对应选项。

                

• 快速填充

从数据列后方相邻单元格输入内容，选中单元格，鼠标放置到单元格右下角后右键向下拖拽，选择对应选项。

>     

• 自定义列表填充

自定义列表位置： 文件 --> 选项 --> 高级 --> 编辑自定义列表

• 多个不连续单元格同数据

Ctrl+鼠标左键选中单元格，填入数据，Ctrl+Enter补全
Shift+F8，鼠标点击单元格，输入数据，Ctrl+Enter补全

• 多个不连续单元格不同数据

选中下方数据，Ctrl + G，选中空值，对空值进行单元格引用，Ctrl + Enter

• 文本记忆输入

1. Alt + 向下箭头
2. 直接输入内容，根据提示选择回车。

1.3 数据验证

数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。

• 数据有效性

选中单元格 --> 数据 --> 数据验证 --> 数据验证 --> 设置 --> 允许：序列 --> 来源

• 圈释无效数据

方法：

1.选择B32：D50, 设置数据验证1-10的范围；
2.圈释无效数据。

1.4 一些便捷操作

a. 查找、替换

查找与选择 --> 替换 --> 单元格匹配

b. 快速选择数据

Ctrl + Shift + 方向键

c. 冻结单元格

操作：视图 --> 冻结窗格
注意：如果要冻结前N行，要选到第N+1行再冻结。冻结列同理

d. 定位条件

操作：开始 --> 查找和选择 --> 定位条件（Ctrl + G或者F5）
要求：使用定位条件选择区域中空单元格，填入100，按组合键Ctrl+Enter填充

e. 拆分

操作：数据 --> 分列

f. 选择性粘贴

操作步骤：

1.输入需要增加的比例。
2.将需要变化的数据提前复制一份。
3.复制比例，选中数据，选择正确的选项即可。

g. 导入数据测试页

导入csv文件时，一定要将格式设置为 utf-8

1.5 Excel数据类型

• 错误值

#VALUE!    键入公式的方式错误。或者引用的单元格错误。
#DIV/0!      这个错误值是说数字被0除了，因为0不能当被除数
#NAME?    在公式中是因为公式名称中出现拼写错误
#N/A!      公式找不到引用的值
#REF!       公式引用无效单元格时将显示 #REF! 错误。 当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。
#NUM!      公式或函数中包含无效数值时
######      如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####
#NULL!           如果在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集，将显示此错误。

• 查看Excel单元格数据类型：TYPE()

数字           1
文本           2
逻辑值        4
误差值      16
数组          64
复合数据 128

1.6 单元格格式

这就是单元格的格式

对整个单元格以及单元格中的数据应用格式：

一种方式是，更改单元格中文本格式，可以将单元格中文本格式改为数值、货币、时间等Excel定义格式，也可以改为按照一定规则设定的自定义格式。

另一种方式是，将单元格想象成图片框，框中的图片就是数据。 设置单元格的格式可能包括添加边框、添加颜色或阴影，或更改单元格的大小和样式。

自定义格式语法：

#	数字占位符；四舍五入；不保留无意义的0			
0	数字占位符；四舍五入；会保留无意义的0			
?	数字占位符；四舍五入；会用空格来代替数据中无意义的0			
.	使用数字占位符的时候，设置都好分割的位数			
				
@	文本展位符；在格式中代表文本数据本省			
*	重复*后面一个符号，直到把单元格填满为止			
			
[颜色1][条件1]格式1;[颜色2][条件2]格式2;……				

1.7 条件格式

条件格式设置：选中单元格 --> 开始 --> 条件格式

1. 借助“数据条”直观分析数据。
2. 使用“色阶”创建热图。
3. 借助“图标集”为数据分类。
4. 条件格式的新建、删除、编辑。

二、进阶

2.1 地址、数学运算

D1		-	行号和列号都没有锁定
$D$1	-	行号和列号都锁定
$D1		-	列号锁定，行号不锁定
D$1		-	行号锁定，列号不锁定

F4可以切换锁定方式
注意：引用单元格内容的时候，如果引用地址没有锁定，往下拉单元格行号不断加1，往右拉单元格，列号不断加1

你可以在 Excel 中进行加、减、乘、除运算，无需使用任何内置函数。只需使用部分基本运算符：+、-、*、/。所有公式均以等号 (=) 开头。

也可以运算其他表中的数据：

2.2 函数

文本函数

函数	说明	举例
LEFT()	从文本字符串的第一个字符开始返回指定个数的字符	=LEFT(“abc”)
RIGHT()	根据所指定的字符数返回文本字符串中最后一个或多个字符	=RIGHT(H1,4)
MID()	返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。	=MID(H1,3,4)
LEN()	返回文本字符串中的字符个数	=LEN(H1)
FIND()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=FIND(“a”,“xyabc”)
SEARCH()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=SEARCH(“Ab”,“xyabc”)
REPLACE()	将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串	=REPLACE(H1,2,2,“二”)
LOWER()	将一个文本字符串中的所有大写字母转换为小写字母。	=LOWER(“m你好ABC”)
UPPER()	将文本转换为大写字母。	=UPPER(“mni你好”)
REPT()	将文本重复一定次数	=REPT(H1,2)
SUBSTITUTE()	在文本字符串中用新文本替换旧文本	=SUBSTITUTE(H1,“一”,“二”)
TRIM()	除了单词之间的单个空格之外，移除文本中的所有空格	=TRIM(I20)
TEXT()	自定义单元格格式（可点击超链接）	=TEXT(I21,“￥#.00”)
VALUE()	将表示数字的文本字符串转换为数字	=VALUE(“$1,000”)

数学函数

函数	说明	举例
ABS()	返回数字的绝对值	=ABS(-23)
INT()	向小取整	=INT(-2.9)
MOD()	返回两数相除的余数（结果的符号和余数相同）	=MOD(10,3)
RAND()	[0,1)之间的随机数	=RAND()
RANDBETWEEN()	[a,b]之间的随机整数	=RANDBETWEEN(0,100)
ROUND()	将数字四舍五入到指定位数	=ROUND(2.34183,3)
SQRT()	返回算术平方根	=SQRT(16)
SUM()	将单个值、单元格引用或者区域相加，或者将三者的组合相加	=SUM(10,20,30,40,5,6)
SUMIF()	对范围中符合指定条件的值求和	=SUMIF(E16:E19,“>”&G16,F16:F19)
SUMIFS()	计算满足多条件的全部参数的值	=SUMIFS(E42:E49,G42:G49,“卢宁”,F42:F49,“<>香蕉”)

统计函数

函数	说明	举例
AVERAGE()	返回其参数的平均值(求指定范围内容所有数字的和再除以数字的个数)	=AVERAGE(F1:F4)
AVERAGEA()	返回其参数的平均值，包括数字、文本和逻辑值（求指定范围内容所有数字的和再除以所有数据的个数）	=AVERAGEA(F1:F4)
AVERAGEIF()	返回区域中满足给定条件的所有单元格的平均值	=AVERAGEIF(A19:A22,“>250000”,B19:B22)
AVERAGEIFS()	返回满足多个条件的所有单元格的平均值	=AVERAGEIFS(B19:B22,B19:B22,“<23000”,A19:A22,“>5000”)
COUNT()	计算参数列表中数字的个数	=COUNT(F1:F8)
COUNTA()	计算参数列表中值的个数	=COUNTA(F1:F9)
COUNTIF()	计算区域内符合给定条件的单元格的数量	=COUNTIF(A29:A34,“<5”）
COUNTIFS()	计算区域内符合多个条件的单元格的数量	=COUNTIFS(A29:A34,“<5”,B29:B34,“<2011/5/3”)
FREQUENCY()	以垂直数组的形式返回频率分布(分箱)	=FREQUENCY(C41:C53,G41:G44)
MAX()/MIN()	返回参数列表中的最大（最小）值	=MAX(F1:F4)
RANK()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小）	=RANK(90,F11:I11)
RANK.AVG()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位）	=RANK.AVG(90,F13:J13)
RANK.EQ()	返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位	=RANK.EQ(90,F13:J13)

日期函数

函数	说明	举例
DATE()	根据年、月、日三个数值返回其表示的日期	=DATE(2011,4,5)
DATEVALUE()	返回由文本字符串表示的日期的十进制数字	=DATEVALUE(“2022年3月4日”)
TIME()	根据时、分、秒三个数值返回其表示的时间的十进制格式	=TIME(10,30,52)
TIMEVALUE()	返回由文本字符串表示的时间的十进制数字	=TIMEVALUE(“12:00:00”)
DAY()	返回序列数表示的某月的天数	=DAY(“2011/4/5”)
MONTH()	返回序列数表示的某年的月份	=MONTH(“2022/10/5”)
YEAR()	返回序列数表示的年份	=YEAR(“2022/10/8”)
SECOND()	返回时间值的秒数	=SECOND(234.564)
MINUTE()	返回时间值的分钟数	=MINUTE(J9)
HOUR()	返回时间值的小时数	=HOUR(J9)
TODAY()	返回当前日期	=TODAY()
NOW()	返回当前日期和时间	=NOW()
EDATE()	返回与某个日期相隔N个月的日期的序列数	=EDATE(J13,10)
EOMONTH()	返回与指定日期相隔N个月份数的月份的最后一天	=EOMONTH(J13,1)
DATEDIF()	计算两个日期之间的天数、月数、年数	=DATEDIF(I16,J16,“MD”)

DATEDIF()的参数	说明
Y	相差年数
M	相差总月数
D	相差总天数
YM	一年内相差月数
YD	一年内相差天数
MD	一月内相差天数

逻辑函数

函数	说明	举例
AND()	用于测试所有条件是否均为TRUE	=AND(J2>=60,K2>=60)
OR()	用于测试是否有为TRUE的条件	=OR(J2>=60,K2>=60)
NOT()	将TRUE或者False的结果取反	=NOT(J2>=60)
TRUE()	返回TRUE	=TRUE()
FALSE()	返回FALSE	=FALSE()
IF()	判断单个条件是否为真 If(条件,条件成立对应的结果,条件不成立对应的结果)	=IF(J2>=60,“及格”,“不及格”)
IFS()	检查是否满足一个或多个条件，且返回第一个符合条件的值. IFS(条件1,结果1,条件2,结果2,条件3,结果3,…)注意：后面的条件是在前面的条件不成立的时候判断的	=IFS(J2>=85,“优秀”,J2>=70,“良好”,J2>=60,“一般”,J2<60,“不及格”)
IFERROR()	使用 IFERROR 函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。	=IFERROR(FIND(“a”,“bbc”),“未找到”)
SWITCH()	根据值列表计算一个值，并返回与第一个匹配值对应的结果，如果不匹配，则可能返回可选默认值。 SWITCH(运算表达式,值1,结果1,值2,结果2,值3,结果3,…). 计算运算表达式，计算结果如果是值1，最后的结果就是结果1，如果计算结果是值2，最后结果就是结果2,…	=SWITCH(INT(J10/10),9,“A”,8,“B”,7,“C”,6,“D”,“E”)

查找与引用函数

函数	说明	举例
COLUMN()	返回与列号对应的数字	=COLUMN()
ROW()	返回与行号对应的数字	=ROW()
（仅适配于2021版）SORT()	对某个区域或数组的内容进行排序 SORT(排序序列,排序依据对应的列号,排序方式) 1）排序序列:需要排序的对象 2）排序依据对应的列号:排序时比较大小对象在排序序列中的位置，如果不辅助，默认按照选中数据的第一列数据的大小排序 3）排序方式:1表示升序(默认), -1表示降序	=SORT(E16:F29,2)
（仅适配于2021版）SORTBY()	对某个区域按照某列数据进行排序（默认升序） SORTBY(排序序列,第一排序标准,排序方式,第二排序标准,排序方式,第三排序标准,排序方式3,…) 注意：排序标准是提供比较对象对应的一列数据	=SORTBY(E16:F29,F16:F29)
（仅适配于2021版）UNIQUE()	对某范围数据进行去重 UNIQUE(去重对象,行列去重方式,去重方式) 去重对象 - 提供需要去重的一行或者一列数 行列去重方式 - 去重对象是一列数据按行去重(FALSE-默认)、去重对象是一行数据按列去重(TRUE) 去重方式 - FALSE(默认),返回原数据去掉重复数据后的结果；TRUE，直接返回原数据中没有没有重复项的数据	=UNIQUE(F16:F29,TRUE)
GETPIVOTDATA()	返回数据透视表中的可见数据
LOOKUP()	在一行或者一列中查找某个值并从另一行或者列中找到同位置的值 LOOKUP(查找对象,查找对象在原数据中所在的列,查找结果所在的列) 注意：使用LOOKU做数据查询的时候必须保证查询数据所在的列中，查找对象前面的数据必须是有序的(升序或者降序)	=LOOKUP(“张3”,A3:A7,B3:B7)
VLOOKUP()	按行查找表格或区域内容 VLOOKUP(查找对象,同时包含查找对象和结果的序列,结果在前面给的序列中所在的列号,查找方式) 查找方式: TRUE - 近似匹配，在查找对象所在的列本身有序的时候使用 FALSE - 精确匹配，在查找对象所在的列本身无序的时候使用	=VLOOKUP(“张3”,A45:B49,2,FALSE)
HLOOKUP()	在表格的首行或数值数组中搜索值，然后返回表格或数组中所在列的指定行中的值	=HLOOKUP(“张3”,B115:F116,2,TRUE)
（仅适配于2021版）XLOOKUP()	按行查找表格或区域内容 XLOOKUP(查找对象,查找数据所在的列,查找结果所在的列) XLOOKUP(查找对象,查找数据所在的列,查找结果所在的列, 查找失败返回的默认值)	=XLOOKUP(“张8”,A95:A99,B95:B99,“找不到”)

信息函数

函数	说明
TYPE()	返回数据的数据类型