2024年2月19日发(作者：武孤兰)
EXCEL知识要点
EXCEL 知识要点
DAY 1
1.列宽的调整：双击边线调整到刚好显示数据，选中多个一起调整
会是宽度一样，选中多个双击边线全部刚好显示
+拖拽边线可以移动列的位置
3.首行冻结：视图-冻结窗格-首行冻结，首列同理。有的比较奇葩，
表头不在第一行，用冻结-冻结拆分窗格
4.“填充柄“，一般来说是数字复制，按住Ctrl是序列，时间相反；
时间右键拖拽，可选以XX填充
+: 输入今天的日期
6.名称框不是一个单元格名，可以利用它“叫”出来单元格，输入单
元格名回车，写2:10回车可以选中2-10行,列同理。
7.选中一个区域，输入一个数值，Ctrl+回车批量填充
8.迅速跳到表格的上、下边或某边：双击单元格上、下或某边边线；
DAY 2
+空格实现单个单元格换行；ENTER是确认键
2.日期时间本质上数字，所以出现一些类似乱码值，调整单元格
的格式修改为日期即可
3.单元格中的自定义：三个分号在不填写任何东西的情况下，可以
将正数、负数、0和文本可以将所有信息隐藏；
4.在日期格式中，关于时间，2个m和d表示中文，3个m和d
表示英文字母简称，4个M和D英文字母全称；4个a则可以
表示中文的星期几出来；3个a表示一，周一则可以利用“周aaa”
即可（中文版本office）
只能保持15个有效数字；
6.一旦已经确定单元格为文本方式，再利用单元格更改其他格式，
是不能更改成功。方法：选中所有文本，左上角会有“I”,可以实现转化。
7.文本表格转化EXCEL：利用分列，需要注意分列的原因，中文
的逗号需要选择其他功能；
8.分列工具：可以实现文本和EXCEL的数值快速转化；
DAY 3
一：查找和替换
1.按值查找 eg: 查找和选择-查找和替换-替换-选项 -单元格匹配
2.按格式查找 eg: 查找和选择-查找和替换-替换-格式-填充
3.模糊查找认识通配符
*星号后面全部替换, 是通配符
（是英文的问号）一个问号表示一个字符
若所需替换的数据中有通配符的，替换时可以在通配符前加~（英文）二：定位工具
1.通过名称框定位单元格及区域位置：
eg: 需要选择A9000到B10000 可以在名声框里输入 A9000：
B10000回车（在excel中“：”是到）
常用某个区域的单元格可以在名称框里给它起名字如常用区以后再
名称框中输入常用区回车就可以找到这个区域
2.使用“定位条件”解决问题
查找和选择-转到-定位-定位条件或查找和选择-定位条件
eg:选中所有批注
3.批注右击 - 批注或审阅-批注
更改备注栏形状：插入-形状会出一个绘图工具栏点一下格式在编
辑形状-更改形状-右键- 添加到快速访问工具栏
批注加图片：选中所需加图片的批注 - 点中批注边框-右击-设置批注格式-颜色与线条-颜色-填充效果-图片
4.把所有空单元格填充等于自己上方的单元格：
选中区域-定位条件-空值-= ↑（方向键上）然后按住ctrl敲回车
5. 删图片：查找和选择-定位条件-对象-delete
或查找和选择-选择对象
DAY 4
一、排序
1.简单排序：选择某个单元格（非常不建议选中整列然后进行排序，这样对例如数学列进行排序会使的其他列顺序不变，从而导致整个表格数据错乱）-开始-排序和筛选-升序or降序
2.多条件排序：选择某个单元格-开始-排序和筛选-自定义排序
3.按颜色排序
4.自定义排序：选择某个单元格-开始-排序和筛选-次序-自定义顺序-自定义序列-新序列-添加-确定
5.利用排序插入行：例如工资条先按照表头复制与数据相同的行数，在原表头旁边打上0，按住ctrl，往下拉在复制的表头旁边写上
1.5,
2.5，再往下拉然后排序
二、筛选
1.使用筛选：选择某个单元格-开始-排序和筛选-筛选
2.高级筛选：数据-筛选- 高级
常量筛选条件区域必须带表头
公式筛选条件不可带表头如果两条件在一行里表示“且”不在一行里表
示“或”（表头一定要有）
另：.全选表格快捷键 ctrl加shift，再按向右和向下
每页打印第一行：文件→页面设置→工作表→打印标题→顶端标题行
DAY 5
一、分类汇总工具
1.认识分类汇总：数据-分类汇总
2.使用分类汇总前先排序：数据-排序
3.使用分类汇总合并相同内容的单元格：首先要排序，然后在数据中使用分类汇总，形成新的一列（要想形成新的一列，必须分类字段和汇
总项相同且未表格最左的那列，若不是表格最左的那列，可以先在要
选的列左侧新建一个空列），在新的列中使用定位，定位出所有的空
值然后合并单元格，再复制新一列的格式用格式刷刷到要汇总的那一列，再删除分类汇总即可
二、设置数据有效性
1.使用数据有效性规定可输入的数据大小，长度及内容等。
2.使用数据有效性保护数据不被改动:选中整个表格，选择数据有效性，点自定义，在公式中输入0或者一个不可能实现的公式，即可。同时
也可以在信息中提醒要修改数据的人。
DAY6
数据透视表
1、Excel创建数据透视表：选中任意一个单元格--插入--数据透视表--右击数据透视表选项--经典数据透视表
2、Excel更改数据透视表汇总方式：在左上角“求和项”上双击--值字段设计
3、Excel数据透视表中的组合：根据日期将数据透视表中的日期汇总为季度，点中一个日期单元格，右键选择组合或创建组合，按季汇总
4、Excel汇总多列数据：如果值字段有多个，每个上下重复了。可以拉第一个的名称到第二个的栏目框，就可以左右显示。
5、Excel在透视表中使用计算：数据透视表工具--选项--域、项目和集-计算字段-
eg：名称：利润率公式： =（合同金额-成本）/合同金额设置单
元格格式-数字-百分比
怎样消除错误值：任意位置右键-数据透视表选项-打印-布局格式-勾选对于错误值显示空白
在数据透视表中加入公式计算一个字段，建议在表内加入，单击表中任意单元格，选项的公式中有一个计算字段。
怎样生成数量众多的工作表：建数据透视表-将需要分页显示的项目拉入页字段和列字段-在数据透视表中点选项-选项-小三角形显示报表筛
选页-选定要显示的报表筛选页字段。如果需要删除因此产生的数据透视表的话，按住shift键，利用下方小三角形，选定所有报表，在第一个工作表中，复制空白单元格，覆盖数据表，这样就会清除所有数据透视表。
（另：1、创建数据透视表一列是一个字段
2、日期中有空格无法组合没有空格但文本组合也不可统计组）
DAY7
一、认识Excel公式（公式必须以等号开头,否则无意义；文本需要用英文半角的双引号括住）
1、Excel运算符算数运算符：+ - * / % & ^
比较运算符：= ><>= <= <>（不等于）
2、Excel公式中的比较判断比较运算符的结果：TRUE是1 FALSE
否0
3、Excel运算符优先级
a.负号-
b.百分比%
c.求幂^
d.乘和除*/
e.加和减+-
f.文本链接&
g.比较
4、Excel单元格引用
相对引用：eg: A1
绝对引用：eg: $A$1 (用F4锁定）
混合引用：eg: $A1 A$1
相对引用转变为绝对引用：选中想要绝对引用的值，按下F4，或者在值得每一个字符前面加$,例如：$K$4
二、认识Excel函数
1、如何使用Excel函数
等号开头函数名在中间括号结尾括号中间写参数2、学习以下Excel函数 SUM
其他：批量填充，按住Ctrl敲回车
排名： =rank（H5，$H$5：$H$11）
DAY 8
1.有多个需要判断的条件使用If（s）函数时，可以使用if 函数嵌
套，为避免过多的if 嵌套可以用几个简单的if 函数相加，如果是文字可以用连字符连几个If
2.使用and 或者or 函数时，先写and 或or 然后用括号把两个条件
包起来，中间有逗号隔开
3.如果IF函数嵌套的层次太多（超出四个），就可以用
VLOOKUP函数
r函数，处理运算错误
函数+ISERORR函数合起来使用可以避免表格中出现错误的
格式显示。eg: if(iserror(d35/c35,0,d35/c35)
DAY 9
1、Count函数，共有多少记录的count函数，count只数个数，
=COUNT(F:F)
2、Countif函数语法，Countif(range,criteria) ，在什么地方，数什么东西，数科目划分的笔数
=COUNTIF(E2:E53,"邮寄费")或者=COUNTIF(E5:E56,H11)，
3、Countif函数计算数值区间
带条件的数数，用countif函数
及格数的函数，=COUNTIF(A2:F2,">=60")，多了个双引号
4、银行卡号的计算数量函数=COUNTIF(A2:A3,A2)
这个函数结果为二，因为用countif数数，只能数到字符串的15位，超过了就要用&"*"
5、countif函数计算银行卡号数量二的函数，第一次试用函数，
=COUNTIF(A8:A20,A8&"*")，要对数据区域试用绝对引用
=COUNTIF($A$8:$A$20,A8&"*")结果才对，
6、是否体检的函数，=COUNTIF(G:G,A2)，结合前面的if函数，
=IF(B2=1,"体检","未体检")，两个公示等额结合
=IF(COUNTIF(G:G,A2)=1,"体检","未体检")，在已体检名单中输入未体检的人民，自动将未体检显示为体检，
7、利用开始下的条件格式新建规则，将未体检的学生名单改变格式，选择类型为使用公式改变格式 =COUNTIF(G:G,A2)=0
2、在数据有效性中使用Countif函数，数据验证中的设置自定义，公式=c1>500,数据有效性中问题一的公式，=COUNTIF(C:C,C1)<2，注意要先选中设置区域
DAY 10
函数的第三参数的简写，求和区域和判断区域大小一样
计算多条件的时候可以使用辅助列，实现多个条件连接的
求和计算，在辅助列输入需要进行连接的条件单元格，例如：在
A列插入辅助列，然后=C2&D2.再需要计算的单元格中输
=sumif(A:A,J5&K5,G:G)进行求和
的求和区域在第一个，与sumif颠倒了
DAY 11
1、要求一行是一条完整的记录，一列是一个属性
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) =VLOOKUP(G7,$B$5:$E$10,4,0/1)，
第一在第二个的区域里找这个
第二区域要至少包含eg: 姓名&原始分（从姓名找原始分）,姓名
必须
是区域的最左列，对数据区域的值绝对引用
第三要返回区域里的第几列
第四精确匹配1；模糊匹配大多用0
2、VLOOKUP要求找的是不重复的否则重复的只找到第一个
【跨表引用】数据源!A:B，点完记住立刻输逗号
3、【使用通配符】，注意不是模糊匹配
有些公司用名称的简写显示错误代码改前：VLOOKUP(A2,数据
源!B:E,4,0)
改后：=VLOOKUP(【A2&"*"】,数据源!B:E,4,0)
4、【模糊匹配】找小于等于lookup_value的近似值
5、格式不匹配，不改原表的情况下：
原表是文本格式，lookup_value数字格式，数字转文本格式：【F4&""】原表是数字格式，lookup_value文本格式，文本转数字格式：强制*1，【F12*1】或负负得正，【--F12】
6、既有文本又有数值的函数，通过数值找一次，（如果用数值找到发
生NA错误）再通过文本找一次
【ISNA判断是否发生NA错误】
=IF(ISNA(VLOOKUP(F20,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$ 18:$C$22,3,0),VLOOKUP(F20*1,$A$18:$C$22,3,0))
7、Hlookup函数，为横行的时候用，联系人姓名函数
=HLOOKUP(B14,$1:$3,3,0)
DAY 12
2024年2月19日发(作者：武孤兰)
EXCEL知识要点
EXCEL 知识要点
DAY 1
1.列宽的调整：双击边线调整到刚好显示数据，选中多个一起调整
会是宽度一样，选中多个双击边线全部刚好显示
+拖拽边线可以移动列的位置
3.首行冻结：视图-冻结窗格-首行冻结，首列同理。有的比较奇葩，
表头不在第一行，用冻结-冻结拆分窗格
4.“填充柄“，一般来说是数字复制，按住Ctrl是序列，时间相反；
时间右键拖拽，可选以XX填充
+: 输入今天的日期
6.名称框不是一个单元格名，可以利用它“叫”出来单元格，输入单
元格名回车，写2:10回车可以选中2-10行,列同理。
7.选中一个区域，输入一个数值，Ctrl+回车批量填充
8.迅速跳到表格的上、下边或某边：双击单元格上、下或某边边线；
DAY 2
+空格实现单个单元格换行；ENTER是确认键
2.日期时间本质上数字，所以出现一些类似乱码值，调整单元格
的格式修改为日期即可
3.单元格中的自定义：三个分号在不填写任何东西的情况下，可以
将正数、负数、0和文本可以将所有信息隐藏；
4.在日期格式中，关于时间，2个m和d表示中文，3个m和d
表示英文字母简称，4个M和D英文字母全称；4个a则可以
表示中文的星期几出来；3个a表示一，周一则可以利用“周aaa”
即可（中文版本office）
只能保持15个有效数字；
6.一旦已经确定单元格为文本方式，再利用单元格更改其他格式，
是不能更改成功。方法：选中所有文本，左上角会有“I”,可以实现转化。
7.文本表格转化EXCEL：利用分列，需要注意分列的原因，中文
的逗号需要选择其他功能；
8.分列工具：可以实现文本和EXCEL的数值快速转化；
DAY 3
一：查找和替换
1.按值查找 eg: 查找和选择-查找和替换-替换-选项 -单元格匹配
2.按格式查找 eg: 查找和选择-查找和替换-替换-格式-填充
3.模糊查找认识通配符
*星号后面全部替换, 是通配符
（是英文的问号）一个问号表示一个字符
若所需替换的数据中有通配符的，替换时可以在通配符前加~（英文）二：定位工具
1.通过名称框定位单元格及区域位置：
eg: 需要选择A9000到B10000 可以在名声框里输入 A9000：
B10000回车（在excel中“：”是到）
常用某个区域的单元格可以在名称框里给它起名字如常用区以后再
名称框中输入常用区回车就可以找到这个区域
2.使用“定位条件”解决问题
查找和选择-转到-定位-定位条件或查找和选择-定位条件
eg:选中所有批注
3.批注右击 - 批注或审阅-批注
更改备注栏形状：插入-形状会出一个绘图工具栏点一下格式在编
辑形状-更改形状-右键- 添加到快速访问工具栏
批注加图片：选中所需加图片的批注 - 点中批注边框-右击-设置批注格式-颜色与线条-颜色-填充效果-图片
4.把所有空单元格填充等于自己上方的单元格：
选中区域-定位条件-空值-= ↑（方向键上）然后按住ctrl敲回车
5. 删图片：查找和选择-定位条件-对象-delete
或查找和选择-选择对象
DAY 4
一、排序
1.简单排序：选择某个单元格（非常不建议选中整列然后进行排序，这样对例如数学列进行排序会使的其他列顺序不变，从而导致整个表格数据错乱）-开始-排序和筛选-升序or降序
2.多条件排序：选择某个单元格-开始-排序和筛选-自定义排序
3.按颜色排序
4.自定义排序：选择某个单元格-开始-排序和筛选-次序-自定义顺序-自定义序列-新序列-添加-确定
5.利用排序插入行：例如工资条先按照表头复制与数据相同的行数，在原表头旁边打上0，按住ctrl，往下拉在复制的表头旁边写上
1.5,
2.5，再往下拉然后排序
二、筛选
1.使用筛选：选择某个单元格-开始-排序和筛选-筛选
2.高级筛选：数据-筛选- 高级
常量筛选条件区域必须带表头
公式筛选条件不可带表头如果两条件在一行里表示“且”不在一行里表
示“或”（表头一定要有）
另：.全选表格快捷键 ctrl加shift，再按向右和向下
每页打印第一行：文件→页面设置→工作表→打印标题→顶端标题行
DAY 5
一、分类汇总工具
1.认识分类汇总：数据-分类汇总
2.使用分类汇总前先排序：数据-排序
3.使用分类汇总合并相同内容的单元格：首先要排序，然后在数据中使用分类汇总，形成新的一列（要想形成新的一列，必须分类字段和汇
总项相同且未表格最左的那列，若不是表格最左的那列，可以先在要
选的列左侧新建一个空列），在新的列中使用定位，定位出所有的空
值然后合并单元格，再复制新一列的格式用格式刷刷到要汇总的那一列，再删除分类汇总即可
二、设置数据有效性
1.使用数据有效性规定可输入的数据大小，长度及内容等。
2.使用数据有效性保护数据不被改动:选中整个表格，选择数据有效性，点自定义，在公式中输入0或者一个不可能实现的公式，即可。同时
也可以在信息中提醒要修改数据的人。
DAY6
数据透视表
1、Excel创建数据透视表：选中任意一个单元格--插入--数据透视表--右击数据透视表选项--经典数据透视表
2、Excel更改数据透视表汇总方式：在左上角“求和项”上双击--值字段设计
3、Excel数据透视表中的组合：根据日期将数据透视表中的日期汇总为季度，点中一个日期单元格，右键选择组合或创建组合，按季汇总
4、Excel汇总多列数据：如果值字段有多个，每个上下重复了。可以拉第一个的名称到第二个的栏目框，就可以左右显示。
5、Excel在透视表中使用计算：数据透视表工具--选项--域、项目和集-计算字段-
eg：名称：利润率公式： =（合同金额-成本）/合同金额设置单
元格格式-数字-百分比
怎样消除错误值：任意位置右键-数据透视表选项-打印-布局格式-勾选对于错误值显示空白
在数据透视表中加入公式计算一个字段，建议在表内加入，单击表中任意单元格，选项的公式中有一个计算字段。
怎样生成数量众多的工作表：建数据透视表-将需要分页显示的项目拉入页字段和列字段-在数据透视表中点选项-选项-小三角形显示报表筛
选页-选定要显示的报表筛选页字段。如果需要删除因此产生的数据透视表的话，按住shift键，利用下方小三角形，选定所有报表，在第一个工作表中，复制空白单元格，覆盖数据表，这样就会清除所有数据透视表。
（另：1、创建数据透视表一列是一个字段
2、日期中有空格无法组合没有空格但文本组合也不可统计组）
DAY7
一、认识Excel公式（公式必须以等号开头,否则无意义；文本需要用英文半角的双引号括住）
1、Excel运算符算数运算符：+ - * / % & ^
比较运算符：= ><>= <= <>（不等于）
2、Excel公式中的比较判断比较运算符的结果：TRUE是1 FALSE
否0
3、Excel运算符优先级
a.负号-
b.百分比%
c.求幂^
d.乘和除*/
e.加和减+-
f.文本链接&
g.比较
4、Excel单元格引用
相对引用：eg: A1
绝对引用：eg: $A$1 (用F4锁定）
混合引用：eg: $A1 A$1
相对引用转变为绝对引用：选中想要绝对引用的值，按下F4，或者在值得每一个字符前面加$,例如：$K$4
二、认识Excel函数
1、如何使用Excel函数
等号开头函数名在中间括号结尾括号中间写参数2、学习以下Excel函数 SUM
其他：批量填充，按住Ctrl敲回车
排名： =rank（H5，$H$5：$H$11）
DAY 8
1.有多个需要判断的条件使用If（s）函数时，可以使用if 函数嵌
套，为避免过多的if 嵌套可以用几个简单的if 函数相加，如果是文字可以用连字符连几个If
2.使用and 或者or 函数时，先写and 或or 然后用括号把两个条件
包起来，中间有逗号隔开
3.如果IF函数嵌套的层次太多（超出四个），就可以用
VLOOKUP函数
r函数，处理运算错误
函数+ISERORR函数合起来使用可以避免表格中出现错误的
格式显示。eg: if(iserror(d35/c35,0,d35/c35)
DAY 9
1、Count函数，共有多少记录的count函数，count只数个数，
=COUNT(F:F)
2、Countif函数语法，Countif(range,criteria) ，在什么地方，数什么东西，数科目划分的笔数
=COUNTIF(E2:E53,"邮寄费")或者=COUNTIF(E5:E56,H11)，
3、Countif函数计算数值区间
带条件的数数，用countif函数
及格数的函数，=COUNTIF(A2:F2,">=60")，多了个双引号
4、银行卡号的计算数量函数=COUNTIF(A2:A3,A2)
这个函数结果为二，因为用countif数数，只能数到字符串的15位，超过了就要用&"*"
5、countif函数计算银行卡号数量二的函数，第一次试用函数，
=COUNTIF(A8:A20,A8&"*")，要对数据区域试用绝对引用
=COUNTIF($A$8:$A$20,A8&"*")结果才对，
6、是否体检的函数，=COUNTIF(G:G,A2)，结合前面的if函数，
=IF(B2=1,"体检","未体检")，两个公示等额结合
=IF(COUNTIF(G:G,A2)=1,"体检","未体检")，在已体检名单中输入未体检的人民，自动将未体检显示为体检，
7、利用开始下的条件格式新建规则，将未体检的学生名单改变格式，选择类型为使用公式改变格式 =COUNTIF(G:G,A2)=0
2、在数据有效性中使用Countif函数，数据验证中的设置自定义，公式=c1>500,数据有效性中问题一的公式，=COUNTIF(C:C,C1)<2，注意要先选中设置区域
DAY 10
函数的第三参数的简写，求和区域和判断区域大小一样
计算多条件的时候可以使用辅助列，实现多个条件连接的
求和计算，在辅助列输入需要进行连接的条件单元格，例如：在
A列插入辅助列，然后=C2&D2.再需要计算的单元格中输
=sumif(A:A,J5&K5,G:G)进行求和
的求和区域在第一个，与sumif颠倒了
DAY 11
1、要求一行是一条完整的记录，一列是一个属性
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) =VLOOKUP(G7,$B$5:$E$10,4,0/1)，
第一在第二个的区域里找这个
第二区域要至少包含eg: 姓名&原始分（从姓名找原始分）,姓名
必须
是区域的最左列，对数据区域的值绝对引用
第三要返回区域里的第几列
第四精确匹配1；模糊匹配大多用0
2、VLOOKUP要求找的是不重复的否则重复的只找到第一个
【跨表引用】数据源!A:B，点完记住立刻输逗号
3、【使用通配符】，注意不是模糊匹配
有些公司用名称的简写显示错误代码改前：VLOOKUP(A2,数据
源!B:E,4,0)
改后：=VLOOKUP(【A2&"*"】,数据源!B:E,4,0)
4、【模糊匹配】找小于等于lookup_value的近似值
5、格式不匹配，不改原表的情况下：
原表是文本格式，lookup_value数字格式，数字转文本格式：【F4&""】原表是数字格式，lookup_value文本格式，文本转数字格式：强制*1，【F12*1】或负负得正，【--F12】
6、既有文本又有数值的函数，通过数值找一次，（如果用数值找到发
生NA错误）再通过文本找一次
【ISNA判断是否发生NA错误】
=IF(ISNA(VLOOKUP(F20,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$ 18:$C$22,3,0),VLOOKUP(F20*1,$A$18:$C$22,3,0))
7、Hlookup函数，为横行的时候用，联系人姓名函数
=HLOOKUP(B14,$1:$3,3,0)
DAY 12