OFFICE_EXCEL笔记

初级基础操作

显示万元格式

首先定义一个10000，转换为数字格式，选择性粘贴除以即可

零值显示

文件(左上角) → 选项(左下角) → 高级 → 此工作表的显示选项 → 在具有零值的单元格中显示为零

隐藏单元格的值

选择要设置的区域，右键选择设置单元格格式，自定义输入;;;

在单元格中输入以0开头的数字

选择要设置的区域，右键选择设置单元格格式，自定义输入所要的位数，不足为会自动补0

按月填充日期

先拉动一格(选择一个单元格，右下角往下拖动)，点击右下角自动填充选项，选择按月填充

合并多个单元格的内容

选择要合并的区域，点击菜单栏开始中的格式，选择内容重排

注：要先把列宽调整为可以容纳合并内容的长度

防止录入重复数据

选择要校验的区域，选择菜单栏中的数据，选择数据校验，校验条件为自定义，输入函数COUNTIF(列:列,列1)=1

公式格式如何转数值

选择复制的内容为公式时，选择性粘贴为数值选项

不显示小数点后的数据

替换法：

ctrl + h 呼出替换窗格，选取 .* 进行替换为空

如何插入多行

要插入的位置，选择整行，按住shift在下线处拉伸，拉多少行就是创建多少行

互换两行内容

选择要交换的行或列，按下shift点击左键进行拖动，直至出现虚线即可

批量自动求和

选择要求和的数据和求和的位置，按下alt+=

查看多个窗口的工作表

选择视图，点击新建窗口

注:可在全部重排选择排列的方式

同时修改多个表相同位置的内容

按下shift选择多个sheet，即可在当前页面进行修改

恢复未保存文件

文件→选项→保存 自动恢复文件位置的路径即为未保存的文件

注：没有到自动保存时间间隔就关闭是保存不了的

给Excel进行加密

文件→信息→保护工作薄→用密码进行加密

关闭所以Excel文件

按住shift关闭其中一个，所有的就全部关闭了

设置下拉菜单

选择需要的区域，数据校验选择序列，勾选来源的数据

删除看不见的换行符

替换法：

ctrl + h 替换alt + 1 + 0 为空

删除空行

选择区域，Ctrl + G ，定位条件为空行，再在开始进行删除

设置不可修改

如果除选中区域外其他都不可修改：

选择可修改的区域，ctrl + 1 调出单元格设置格式，保护→取消锁定→确定，在审阅点击保护工作表

文字跨列居中显示

选择要跨的列，设置单元格格式，在对齐中选择跨列居中

给批注添加图片

点击要设置的单元格，审阅→新建批注，在新建批注窗口右键设置批注格式，颜色与线条的颜色下拉框填充效果添加图片

显示和隐藏所有批注

审阅→显示所有批注

解决数字不能求和

选择要转化的区域，在右上角会出现一个三角形，点击转化为数字

调整最合适的列宽

单列：

选择这一列，双击

多列：

选择要调整的列，在任何一列双击

复制公式

首先确定第一个的公式，然后点击右下角，进行双击

拆分数据列

选择区域，数据→分列，设置分割符号

在每一页打印标题行（打印时）

页面布局→打印标题→工作表→选择打印标题行

快速拆分数字

例如：

123人 在第一行输入123，点击快速填充，即可拆分为一样的

解决身份证后三位变成0或科学计数法

1.在录入前输入英文分号在输入

2.先把单元格设置为文本格式

快捷键

功能	快捷键
√	Alt + 41420
智能拆分	CTRL + E
自动生成下拉列表	alt + ⬇
插入批注	shift + F2
查找	ctrl + F
定位	Ctrl + G
美化表格	Ctrl + T
筛选	Ctrl + Shift + L
全选(可点击表格中的任意一个单元格来全选表格)	Ctrl + A
替换	Ctrl + H
撤销和恢复	Ctrl + Z / Y
求和	Alt + =
快速移动	Ctrl + 方向
快速框选	Ctrl + Shift + 方向键
增加行或列	Ctrl + Shift + +
删除行或列	Ctrl + -
批量填充	Ctrl + Enther
分析	Ctrl + Q
编辑	F2

函数

清洗类

截取 LEFT()

=LEFT(text,[num_chars])

=LEFT(单元格,字符串的长度) 不写字符串长度即默认为1

例如：

=LEFT(A1,2) 

即可截取A1单元格的前两位字符

截取 RIGHT()

=RIGHT(text,[num_chars])

=RIGHT(单元格,字符串的长度) 不写字符串长度即默认为1

例如：

=RIGHT(A1,2) 

即可截取A1单元格的最后两位字符

截取 MID()

=MID(text,start_num,num_chars)

=MID(单元格,起始位置,字符串长度) 不合规会报错

例如：

=MID(A1,2,3)

截取单元格A1从第二个字符开始的后三个内容

返回长度 LEN()

=LEN(text)

=LEN(单元格)

例如：

若A1内容为 “ 我要当数据分析师 ”
=LEN(A1)

获取的即为A1的字符串长度，为8

返回长度 LENB()

=LENB(text) 单字节反1，双字节反2

=LENB(单元格)

例如：

A1 数据分析师 B1 fenxishi

=LENB(A1)

A1则会返回10（每个字节返回2，5*2），B1则会返回8（每个字节返回1）

合并单元格数据 CONCATENATE()

=CONCATENATE(text,text,…)

=CONCATENATE(单元格1，单元格2，…)

例如：

A1 我是 B2 大 C3 帅哥

=CONCATENATE(A1,B2,C3)

返回结果即为"我是大帅哥"

转换成文本 TEXT()

=TEXT(value,format_text)

=TEXT(必须为数值,设置value格式)

例如：

A1 2000 B1 2020/10/15

=TEXT(A1,"0.00")			2000.00
=TEXT(B2,"YYYY年M月D日")	  2020年10月15日

去除前后空格 TRIM()

=TRIM(text)

=TRIM(单元格)

把单元格两边的空格去掉，但并不去除字符之间的空格

例如：

A1 “ 数据分析师 ”

=TRIM(A1) 

返回结果即为数据分析师，仅仅只去除前后的空格

替换字符串指定位置的内容 REPLACE()

=REPLACE(old_text,start_num,num_chars,new_text)

=REPLACE(单元格,起始位置，字符长度，替换内容)

例如:

A1 继续努力呀

=REPLACE(A1,3,2,"奋斗")

输出结果为 继续奋斗呀

替换字符串的指定内容 SUBSTITUTE()

=SUBSTITUTE(text,old_text,new_text,[instance_num])

=SUBSTITUTE(单元格，需要替换的内容，如果有多个则选择第几个【可不选】）

例如：

A1 要努力努力努力呀

=SUBSTITUTE(A1,"努力","奋斗")
=SUBSTITUTE(A1,"努力","奋斗",2)

若不选择第几个则全部替换，输出结果为 要奋斗奋斗奋斗呀

选择后则根据第几个参考值来进行替换，输出结果为 要努力奋斗努力呀

查找在字符中出现的位置 FIND()

=FIND(find_text , within_text , [start_num])

=FIND(要查找的字符串，要在查找的单元格，要开始查询的起点)

是区分大小写的

例如：

A1 ABCDEFG

=FIND("A",A1,1)

输出结果为 1

查找在字符中出现的位置 SEARCH()

=SEARCH(find_text , within_text , [start_num])

=SEARCH(要查找的字符串，要在查找的单元格，要开始查询的起点)

不区分大小写

例如：

A1 abcdefg

=SEARCH("A",A1,1)

输出结果为 1

日期类

返回当前日期 TODAY()

=TODAY()

例如：

比如现在为2002年1月1日

A1 10

=TODAY()

=TODAY() + A1   A1也可以是固定的数值

返回的结果为 2002/1/1

有附加条件即为，2002/1/11

返回特定日期 DATE()

=DATE(year,month,day)

=DATE(数值年，数值月，数值日)

若首年不足四位则会从1900+数值 开始计算，月和日为0则默认从最后一天开始

例如：

A1 2002 B1 1 C1 1

=DATE(2021,5,21)
=DATE(A1,B1,C1)

返回数值为，2021/5/21

返回数值为，2002/1/1

返回某月最后一天 EOMONTH()

=EOMONTH(start_date,months)

=EOMONTH(选中的日期，0) -1为上个月，0为当前月，1为下个月

​ 例如：

A1 2023/10/25

=EOMONTH(A1,0)

返回结果为，2023/10/31

提取日期的年份 YEAR()

=YEAR(serial_number)

=YEAR(选中的日期)

例如：

A1 2021/10/20

=YEAR(A1)

返回结果为，2021

提取时间的月份 MONTH()

=MONTH(serial_number)

=MONTH(选中的时间)

例如：

和提取年份结构相同

提取日期中的天 DAY()

=DAY(serial_number)

=DAY(选中的时间)

例如：

和提取年份结构相同

返回某日期的星期数 WEEKDAY()

=WEEKDAY(serial_number,[return_type])

=WEEKDAY(选中的日期，返回的类型） 1：从日到六 2：从一到日

例如：

A1 2021/5/21

=WEEKDAY(A1)
=WEEKDAY(A1,2)

不填类型则默认为1，输出结果为，6

输出结果为，5

返回指定日期是一年第几个日期的数字 WEEKNUM()

=WEEKNUM(serial_number,[return_type])

=WEEKNUM(选中的日期，返回的类型) 1：从日到六 2：从一到日

例如：

A1 2021/5/21

=WEEKNUM(A1)
=WEEKNUM(A1,2)

不填类型则默认为1，输出结果为，21

输出结果为，21 （因为只差一天，所以还在同一星期）

逻辑类

判断 IF()

=IF(条件表达式，真返回的值，假返回的值)

例如：

A1 10

=IF(A1>5,1,0)

因为A1大于5成立，所以结果为真，输出为，1

判断与 AND()

=AND(条件表达式，条件表达式)

都符合返回true，有其中一个不符合返回false

例如：

A1:5 , B1:9

=AND(A1>10,B1<10)

因为A1>10不符合，所以返回结果为，false

判断或 OR()

=OR(条件表达式，条件表达式)

有其中一个符合返回true，全部不符合返回false

例如：

A1：10 B1：9

=OR(A1>5,B1>10)

因为A1>5符合，所以返回true

判断数值是否有错 ISERROR()

=ISERROR(要测试的内容)

用于测试返回的数值是否有错，有错则返回TRUE，没错则返回FALSE

例如：

A1：3 B1：0

=ISERROR(A1/B1)

因为3/0，0不能作为除数，所以为错，返回true

判断内容的正确与否 IFERROR()

=IFERROR(要测试的内容，如果错则返回的内容)

用来判断某些内容的正确与否，正确则返回正确的结果，错误则返回设定的信息

例如：

A1:100 B1:10 C1:0

=IFERROR(A1/B1,"error")
=IFERROR(A1/C1,"error")

因为A1/B1为正确表达式，返回为，10

A1/C1为错误表达式，0不能作为除数，所以返回error

返回 TRUE()

=TRUE()

返回逻辑值TRUE

例如：

=TRUE()

无特殊表达，返回即为，TRUE

返回 FALSE()

=FALSE()

返回逻辑值FALSE

例如：

同TRUE()一同，无特殊表达

关联匹配类

查找指定内容 VLOOKUP()

=VLOOKUP(要匹配查找的内容某一单元格，去匹配查找的数组区域，返回内容所在的列数，精度)

精度：0为精确匹配，1为近似匹配

例如：

姓名	工资
张三（A1）	1000
李四	2000
王五	3000

姓名	部门
张三（C1）	人事部
李四	销售部
王五	开发部

例如：

要在部门表右边新建一列来查看员工工资

=VLOOKUP(C1,A1:B3,2,0)

将会在右边生成一列根据姓名获取的工资列

姓名	部门
张三（C1）	人事部	1000
李四	销售部	2000
王五	开发部	3000

单元格信息

列索引

=CHAR(COLUMN()+64)

行索引

=ROW()

某一单元格数据

=INDIRECT("序列号")