计算机二级MS之Excel

声明：跟着大猫和小黑学习随便记下一些笔记供大家参考，二级考试之前将持续更新，希望大家二级都能轻轻松松过啦，过了二级的大神也可以在评论区留言给点建议，感谢大家！！

---

考题难点（大猫）

第13套

修改货币符号（数字对话框）

修改时间格式（自定义格式[h]小时m分钟）

[h]表示时间段，h表示时间点，时间段允许超过24小时，时间点不允许

出现###表示太紧了显示不完整可调节列宽

条件格式----突出单元格规则

创建数据透视表

点击字段列表可打开这个窗口
然后按题目要求修改其求和项

第15套

报表布局

默认是以压缩形式显示，两个位于行的标签被压缩到一列

使用以表格形式显示,可以是原本的班号移到一列并显示所有行的值

分类汇总（不需要分类汇总时可关闭）

值汇总依据

计数：可统计班级或者学校的人数，统计学号有多少个，也就是人数有多少个

选择性粘贴-----转置

若转置后得分率消失，则将选择性粘贴中的“全部”改成“值和数字格式”

选择所有列，双击列可以自动调整列宽

第16套

使用千位分隔符

记录行（表示满足条件的话就要将整行标出）

取消与隐藏筛选标记

要使用第一种方法，第二种只是隐藏了筛选标记并没有取消

条件格式-----新建规则----公式

透视表----设置颜色（右键工作表）

透视表----排序

第18套

保留001格式（输入前先设置为文本模式）

修改日期格式

取消网格线

不合并单元格使文字居中（跨列居中）

隐藏行（右键该行，点隐藏）

字段和记录

透视表----创建组（组合），右键

透视表----数据透视图----显示公式与R平方值

高级筛选（先创建筛选区域，再使用高级筛选选项）

列表区域为被筛选的那些数据

第19套

修改工作表标签颜色（右键）

导入文件

点击下一步然后按题目要求选择分隔符号按逗号

点击下一步选择身份证，并改成文本格式

分列

先创建一空白列用于分列之后存放新的一列


光标单击工号和姓名之间建立分列线

最后点击下一步，点击完成就可以了，标题手动修改就行

创建表格（套用表格格式）

制作工资条（排序，编号辅助）

首先知道空行，标题行，数据行的个数，比如这题，第一个空行在第1行，标题行在第2行，数据行在第3行，那么空行就应该都在1,4，7,10这样的等差数列行中，标题行和数据行也同理，那么就可以依次创建68个空行，并且等差数列给予编号，标题行和数据行也同理，最后对所有编号进行排序，排完序后删除编号，那么就可以得到整个工资条了

只选择有内容的单元格（定位条件）（定位到常量）

只选择无内容的空单元格（定位到空值）

多个单元格插入相同的文本或数字的方法

选中多个单元格输入文本或数字后按Ctrl+回车

打印一个页面宽，水平居中（页面设置对话框）

第20套

数据验证（有下拉箭头考察的是数据验证）

隐藏和显示工作表（右键）

排序

if和&的联合考题

发现饼图不符合要求（切换行列）

饼图分离程度

点击一个扇形然后右键，点击设置数据系列格式

第21套

套用表格格式不能选中合并单元格

日期格式m与mm区别

透视表-----合并单元格

右键透视表任意单元格，右键点击数据透视表选项，点击合并且“居中排列带列标签的单元格”

图表-----柱形图-----选择数据

1.先删除三个系列

2.添加系列

3.修改系列名称并删除默认系列值

4.将系列值修改为系列名称对应的数值



5.编辑水平轴标签

图表-----柱形图-----修改纵轴

先选中纵轴




主要单位就是两个刻度值之间的大小

刻度线有内外之分

第22套

表格转换为区域

快速填充（Ctrl+E）（mid函数也能做）

设置数据格式000（表示把数字始终显示为三位数状态，不足三位则补零）

透视表------创建组（右键单元格）

若透视表数据没有人数作为行标签时，则可以使用姓名作为值标签表示人数

散点图-----趋势线----显示公式和R平方

设置打印区域，打印时标题行重复

由于内容很长所以正常打印出第二页时标题行不会打印，所以需设置重复打印

点击打印标题->光标定在顶端标题行->点击需要打印的标题行

设置页眉页脚（无单元格的和有单元格的工作表需分开设置）

以上两个分别为无和有单元格
我们可以先选中两个工作表，会显示工作组

根据题目在页面设置对话框里设置自定义页眉和已存在的页脚



最后重复上述步骤给无单元格工作表设置页眉页脚

第23套

新建批注（审阅）+设置批注格式（右键）

设置窗口视图，滚动时总是可见（冻结窗格）

冻结的永远是选中单元格的左侧和上方所有单元格，所以该题需要冻结F4单元格,保证无论如何滑动滚轮，使页面如何改变，其周边的单元格永远可见

透视表----列标签----取消全选----使表中只有想要的列

透视表----折叠横标签（右键）

透视表------自定义排序（不能导入单元格时）（文件–选项）

透视表—字段—筛选—为每一类生成一张工作表

第24套

工作表----复制工作表

勾选建立副本才是复制，否则就是移动

工作表----设置背景图片

小三角----单元格可能有错误

错误一般都是这个数字是文本格式

数据验证------输入错误时给出提示信息

准考证

面试分数

公式------定义名称

先全选，再公式->根据所选内容创建


定义完成之后可以在这里看名称

删除重复项

条件格式—新建规则----公式-----单元格非空和位于偶数行（函数and和iseven）

图表-----以独立方式嵌入新工作表

右击图表

图表----图表标题与单元格标题内容一致

单击图表标题，在编辑栏里面输入=

然后找到对应单元格，引用该单元格，再回车

第25套

单元格内容分两行显示（单元格内换行：Alt+回车然后再回车）

数据—模拟分析–模拟运算表

图中选中的左上角单元格需填入一个公式，代表了模拟运算表剩下所有空格的计算公式

行在运算表里表示年需求量，就填写年需求量对应数值的单元格，列表示单位年储存成本，同理也填写对应数值单元格

可变单元格（数据–模拟分析–方案管理器–添加–显示）

点击添加，剩下两个方案同理,最终显示方案持平

单元格定义名称

结果单元格（数据–模拟分析–方案管理器–摘要）

选C5

自动生成一个工作表“方案摘要”

第26套

格式----使小于某个值的单元格显示文本

条件格式—数据条—其他规则—勾选只显示数据条

图表—迷你图—柱形图

数据验证—序列

透视表—将行变成列

最后透视表的效果图发现年份是一列，原表格是一行，所以要将年份变成一列

先将年份一个个勾选到值

再将数值移动到行

点击设计，点报表布局，点以表格形式显示


点报表布局，点重复所有项目标签


然后复制需要的部分，粘贴时选择只保留值

删除原透视表，使用查找替换修改改表格

根据复制后的表格创建新透视表

透视表—隐藏标签

索引（文件—信息—属性–自定义）

第27套

命名单元格（选中后左上角名称框修改，输入完之后要回车）

可以在名称管理器查看定义的名称

数据—格式（不同条件显示不同格式）

简写是这样的，在自定义格式里面；起分隔作用，左边分号的左边代表值为正数的格式，所以是0%，表示百分数格式，两个分号之间是值为负数的格式，不存在所以什么也不写，最右边就是值为0代表的格式，为-，表示用-填充

以上两种写法都可以

清除单元格不可见的空格和字符（函数顺序不能颠倒）

透视表----隐藏折叠按钮

第28套

合并工作表数据–合并计算（除了第一行和第一列都是数字时可使用）

点击第一个工作表然后Ctrl+A全选表格，再点击添加，其余工作表也是一样，然后勾选首行和最左列（这是存在文字的地方），最后点击确定

筛选—使用筛选来删除指定单元格所在行

点击筛选，取消全选并勾选0，

选中目标行右击，然后点击删除行

点击月销售的筛选按钮，点击“从月销售中删除筛选”

导入文件，要求编号保持原格式（数据–现有链接）

选中该列改成文本格式，才不会改变编号001这种格式

第29套

隔行底纹就是镶边行

若要在单元格填充与相邻单元格相同内容

先定位到该列的空单元格，然后输入=，再按向上的方向键，最后按下Ctrl和回车，注意：全程都不要使用鼠标

条件规则—优先级

点击条件规则，点击管理规则，然后点击上移，优先级变高

样式—自定义单元格样式

图表—堆积柱形图—系列线

移到图表到新的图表工作表

页面布局—自定义页眉页脚—代表工作表名称的是“数据表名称”

页面布局—自定义页眉页脚—页脚的设置（页码of总页数）

第34套

修改不规范日期（查找替换）

Excel可识别日期

将.用替换换成-或者/

最后再修改时间，这里要把语言先修改成语言英语

将字符和编号连接可使用&

数据验证+定义名称

第五题只需要把绝对引用改成相对引用就可以了

第第四题则麻烦一点
题目动态变化的意思是，B8单元格已经可以使用下拉框选择四个产品类别中的的一个了，但是选择不同产品类别时，对应数据金额也要发生变化

第一步：先选中C2到F6，点击公式，点击根据所选内容创建，用首行创建名称，这样每一列金额对应的名称就是其首行标题名称

第二步：定义一个新的名称，叫各类别销售汇总，然后修改引用位置，使用公式indirect,最后点击确定就OK了

修改单元格文字为竖排文字

图表–簇状柱形图–根据单元格变化动态变化

修改B8单元格可以使改图表发生变化

条件格式—随单元格变化动态变化

超链接–要修改到的单元格

第36套

保护工作簿（点不了取消隐藏）

复制工作表（Ctrl+拖动）

排序若导致公式有问题，则应当选中单元格再手动排序

若直接点击升序导致公式错误

图表–簇状柱形图–系列间隙宽度（分类间距）

图表–另存为模版（右键图表）

图表----修改图表属性

先选中图表（或者右击图表，点击图表区域格式），然后在右边窗格中取消锁定

第38套

超链接—显示文字

函数填写超链接

保护工作表—锁定与取消锁定

由于保护工作表只针对锁定的单元格，而工作表的所有单元格单元格都是默认锁定的，也就是说当你单击保护工作表时，会将全部单元格都进行保护，所以我们先要取消所有单元格的锁定，然后只对要进行保护的单元格进行锁定，再进行保护就可以了

先选中所有单元格

取消锁定

选中要保护单元格并锁定

然后锁定工作表

使保护且看不见公式（对齐方式对话框点击隐藏，再来进行保护）

这样就看不见公式了

工作表—取消工作表标签(文件–选项–高级)

活动工作表就是当前选中且正在操作的工作表

取消显示工作表标签

工作表标签消失了

第39套

以显示精度参与运算（文件–选项–高级）

图表–三维饼图–让其更扁

三维格式的顶部棱台和底部棱台设置为角度，数值给大一点

图表—数据标签—使显示于两行（分隔符设置为分行符）

第40套

coiuntifs+筛选找到非重复项

计算年龄（datedif）

单元格格式—数据长度的判断

99999999表示11位数，因为该题题中不是8位数就是11位数，8位数大小极限就是8个9，“;”表示否则的意思，所以这句话的意思就是数值大于99999999就填入***********，否则就填入********

透视表—插入切片器+自定义序列（文件–选项–高级）

先要编辑自定义列表，才能够使用切片设置其里面的“排序时使用自定义列表”



排序时使用自定义列表

设置为升序，总而言之最后要达到题目的排序效果

清除筛选，防止误触后与题目要求不同

第41套（这套很难）

数据—现有链接（导入网页中的表格）

数据–连接（取消连接关系）

将单元格的值缩小10000倍(选择性粘贴)

先随便找个空单元格输入10000，然后Ctrl+C复制该单元格

然后选中等待缩小的单元格，然后点击选择性粘贴


选择其中的除法运算，然后点击确定

添加数据模型

首先要添加这样一个Power Pivot选项卡

先点击数据，点击管理数据模型，点击启用，然后关掉弹窗，就会出现那个选项卡了



然后选中该区域点击添加到数据模型


勾选我的表具有标题行，点击确定

若出现这样的提醒，则是因为没有为该区域套用表格格式，那我们可以先随便套用一个表格格式


这个直接点击是即可，表示删除链接，可能是刚刚没删干净

再来添加数据模型，将下面的表一改成2010年


最后关掉数据模型即可

创建查询和查询编辑器窗口

第一题：新建查询


若出现这种情况记得找监考老师哦

选中第一个工作表后，数据还有毛病，不要点加载，点编辑（也叫转换数据）


光标定在任意一行，点击将第一行用作标题，这样第一行就变成标题行了



然后修改查询名字为2000年就做完了

第2题：拆分列（按分隔符）
考试版本低没有这种显示，所以只能使用其他方式分列

考试是这种

首先点击关闭并上载至

点击创建连接，点击加载

然后从考生文件夹打开另外一个工作簿（打开之前一定要上载，不然没有打印工作簿，就打不开）

使用替换（两个空格替换为一个空格）将中间的空格变成一个空格

先复制其中的一个符号（因为看起来像空格，实际上可能不是空格），将其替换成空格，然后再将每两个空格换成一个空格，再使用空格来进行分列（分列要在查询编辑窗口进行，不能在该工作簿），将两个空格替换成一个空格时，要反复点击替换只有替换不了位置



最后保存并关掉该工作簿
来到查询编辑器窗口，右键点击编辑，在这里分列（先要刷新预览，才能分列）



按空格分列

删除多余一列，和最后几行



最后关闭并上载

第5，6题：将查询表加载到新工作表之中，并添加到数据模型之中
右键该查询表，点击加载到

点击加载到表，勾选将此数据添加到数据模型，点击加载

加载完之后修改改工作表名称

再Power Pivotx选项卡的管理可看到程序表有没加载到数据模型之中，发现两个年份都有就做完了

数据模型表

第1题：建立关系（对公共部分连接，该题公共部分是地区）
点击关系图视图

直接对地区进行拖动连接就好了

数据模型----透视表—度量值

新建度量值

度量值名称为人口增长数


先刷新透视表，再打开字段列表，将人数增长移到值里面去



同理，然后再搞人口增长率的度量值

自定义序列（文件-选项-高级）

题目是那个网站表格，也就是第六次普查数据这个工作表的表格，然后导入其序列

第42套

美式排名和中式排名（rank.eq或者rank,总分去重+countifs）

美式：

中式需要先总分去重：
现将总分复制到旁边列，然后删除其中重复的值,最后有多少个分数比一个人的总分大，这个人就排多少名


计算多少人分数比本人高，这个L2是一个单元格不能够放在“”里面，否则就变成了一个文本，而且L2前面要加上一个&

数据—分类汇总

先要全选所有数据的单元格，点击分类汇总

分类字段选班级，汇总方式是平均值，汇总项是各科成绩，勾选每组数据分页

分类汇总+图表组合图（隐藏图表用不到的数据）

点一下第二个级别隐藏每个人的成绩

图表—切换行列

横轴和图例的要求与题目要求不一样

如果创建组合图时，题目要求的横轴值与图例和要创建的组合图中显示不符，那么先创建一个图表，修改好数据之后再修改图表类型为组合图就行

第43套

以图片形式粘贴，随数据源变化而变化

先复制该表格，然后粘贴，点击链接的图片

公式合集

绝对引用，混合引用，相对引用（F4）

相对引用：就是不锁定行和列，如果sheet变了，那么原本的A2内容就变成当前sheet的行和列内容
绝对引用：就是锁定行和列，比如$ C2，sheet虽然变了,但是还是使用原来sheet的行列内容
混合引用：看$加在什么前面就是锁定什么，比如 $C2，就是绝对引用C列，C$2就是绝对引用第二行
锁定：行列锁定之后公式中就只会该单元格的值，否则公式中行列值会发生变化
比如：在C2中输入公式= $A$2* $B$2 ，公式向下复制到C3时公式还是= $A$2* $B$2，但是若为= $A2* $B2，再复制到C3公式就是 $A3* $B3，行没有锁定所以会根据位置变化
引用当前填入公式单元格所在的工作表的其余单元格，不需要该工作表的名称
总结：不使用什么就锁定什么，如果你只需要该行的值或者列的值，那么不锁定就行了

复制公式，先选中公式然后Ctrl+C,然后点击Esc键退出选中公式，最后找到对应地方Ctrl+V就行了

快捷键

第一个是选中该单元格选定方向之后的所有单元格
第二个是选中该单元格选定方向之后的一个，一排或者一行单元格
第三个是快速跳转到该单元格选定方向的最后一个单元格

若公式写完后回车不能还是显示公式，可能是因为单元格格式是文本形式，可以将单元格格式修改为常规处理

vlookup(第13套)

使用方法:vlookup(查找车型，查找区域，查找目标在区域中表示第二列，0表示精确匹配)
查找区域要用F4绝对引用

ROUNDUP和ROUNDDOWN(第13套)

在Excel里面时间存储的常规格式是以天为单位
使用方法：up是向上取整数，down是向下取整数，=ROUNDUP(准备进位的数，保留小数的位数)，=ROUNDDOWN(准备退位的数，保留小数的位数)

sumif和sumifs（第15套）

sumif用于单条件求和，sumifs用于多条件求和
sumifs(要求和的区域，条件判断区域1，条件1，条件判断区域2，条件2，…)条件判断和求和区域一般不会变化，就需要绝对引用，表示锁定范围
sumif(条件判断区域，条件，要求和的区域)


表示求和区域为“按班级汇总里面的J2到J33”且绝对引用，条件判断区域为“按班级汇总里面的A2到A33,也就是四个中学”且绝对引用，条件为A2也就是第一中学，最后的结果就是第一中学的物理总分

rank.eq和rank（第16套）

省略第三项就是默认为降序排列

&为文本连接符，这样显示的就是“第*名”

RANK 函数

• 语法：RANK(number,ref,[order])
  参数解释：
  number：指要进行排名的数字。
  ref：是包含一组数字的单元格区域。
  order：这是个可选参数，用来指定排名的方式。若为 0 或者省略，代表降序排名；若为非零值，则是升序排名。

• 示例：假设在 A 列有一组数据（A1:A5）为 5, 3, 8, 3, 6，若要对 A2 单元格中的数字 3 进行降序排名，可在其他单元格输入公式
  =RANK(A2,$ A$ 1:$ A$ 5,0)。这里使用绝对引用 $ A$ 1:$A$5 是为了在复制公式时引用区域不会改变。

RANK.EQ 函数

• 语法：RANK.EQ(number,ref,[order])
  参数解释：和 RANK 函数的参数含义相同。
  与 RANK 函数的区别：在处理相同数值的排名时，RANK.EQ 函数会给相同数值赋予相同的排名，并且跳过后续排名中的相应名次。例如，若有两个数字并列第 2 名，下一个数字的排名将是第 4 名。

• 示例：同样对于 A 列的数据（A1:A5）5, 3, 8, 3, 6，若要对 A2 单元格中的数字 3 进行降序排名，在其他单元格输入公式 =RANK.EQ(A2,$A$1: $A$5,0) 即可。
  示例操作步骤
  打开 Excel 工作表，在 A 列输入数据 5, 3, 8, 3, 6。
  在 B2 单元格输入公式 =RANK.EQ(A2, $A $1: $A$5,0)，然后按下回车键，就能得到 A2 单元格数字的排名。
  把鼠标指针移到 B2 单元格右下角，当指针变为黑色十字时，按住鼠标左键向下拖动，即可将公式应用到其他单元格，从而得到整列数据的排名。

match(第18套)

match(查找的东西，查找区域，精确匹配还是近似匹配)，选中一行就会返回列，选中一列就会返回一行，在这里返回值是第3列，查找的是C4也就是产品A,范围是G3到K3

weekday（第18套）

weekday（转换区域，规则），规则可以为1,2,3，为2时最常用，是星期几就返回几

• 默认返回值（星期日到星期六）：公式 =WEEKDAY(“2024-3-10”,1) 或 =WEEKDAY(“2024-3-10”) ，返回 7 ，表示星期六。因为默认 return_type 为 1 ，返回值范围是从 1 （星期日）到 7 （星期六）。
• 以星期一为起始（星期一到星期日）：公式 =WEEKDAY(“2024-3-11”,2) ，返回 1 ，表示星期一。此时 return_type 为 2 ，返回值范围是从 1 （星期一）到 7 （星期日）。
• 从0开始计数（星期一到星期日）：公式 =WEEKDAY(“2024-3-11”,3) ，返回 0 ，表示星期一。当 return_type 为 3 时，返回值范围是从 0 （星期一）到 6 （星期日）。

if（第19套）

IF 函数用于根据条件判断返回不同的结果，语法为 IF(logical_test, [value_if_true], [value_if_false])

• 基本用法：例如 =IF(A1>10,“大于10”,“小于等于10”) ，如果 A1 单元格中的值大于 10 ，则返回字符串 “大于10” ，否则返回 “小于等于10”
• 多层嵌套：可以通过嵌套 IF 函数进行更复杂的条件判断。如 =IF(A1<0,“负数”,IF(A1=0,“零”,“正数”)) ，先判断 A1 是否小于 0 ，如果是则返回 “负数” ；否则继续判断是否等于 0 ，等于 0 返回 “零” ，否则返回 “正数”

mid(第19套)

MID 函数用于从文本字符串中提取指定数量的字符，语法为 MID(text, start_num, num_chars)

• 基本用法：例如 =MID(“Hello World”,7,5) ，表示从字符串 “Hello World” 的第 7 个字符开始，提取 5 个字符，结果为 “World”
• 结合其他函数使用：经常与 LEN 等函数结合使用。如 =MID(A1,1,LEN(A1)-1) ，假设 A1 单元格内容为 “ABCDE” ，该公式会从 A1 单元格字符串的第 1 个字符开始提取，提取长度为字符串总长度减 1 ，即提取出 “ABCD”

实际应用中，这if与mid函数常组合使用。比如 =IF(MID(A1,1,3)=“ABC”,“符合条件”,“不符合条件”) ，用于判断 A1 单元格中字符串的前 3 个字符是否为 “ABC” ，并根据判断结果返回相应的值。

mid的返回结果是文本，若要将返回结果与某一个值进行比较，则需要统一格式，都为数字或者都为文本

isodd(第19套)

在Excel中， ISODD 函数用于判断一个数是否为奇数。其语法为 ISODD(number) ， number 为必需参数，是要检验的数值。以下是具体使用方法：

• 基本用法：例如 =ISODD(5) ，会返回 TRUE ，因为 5 是奇数。而 =ISODD(6) 则返回 FALSE ，因为 6 是偶数
• 引用单元格数据：若 A1 单元格中存储数字 7 ，在其他单元格输入 =ISODD(A1) ，会返回 TRUE
• 与其他函数嵌套使用：可以和 IF 函数等嵌套。如 =IF(ISODD(A1),“奇数”,“偶数”) ，根据 A1 单元格中的数值判断并返回相应的文本。
  ISODD 函数只能判断常规数值，如果参数是文本或无法转换为数值的内容，函数将返回 #VALUE! 错误值

可和if搭配使用

常用条件格式中，选中所有单元格然后用这个公式，来使奇数行染色

text（第19套）

在Excel中， TEXT 函数用于将数值转换为按指定数字格式表示的文本。其语法为 TEXT(value, format_text) ，其中 value 为必需参数，是要转换的数值； format_text 也是必需参数，用于指定所需的文本格式。以下是一些常见的用法：
基本用法

• 将数字转换为特定格式的文本，如 =TEXT(123.45,“0.00”) ，结果为 123.45 ，将数字 123.45 按照保留两位小数的格式转换为文本
  日期和时间格式转换
• 把日期数字转换为指定的日期格式，例如 =TEXT(A1,“yyyy年mm月dd日”) ，假设 A1 单元格存储的是日期的序列值，通过此公式可将其转换为如 2024年03月13日 的文本格式
• 对时间进行格式转换， =TEXT(B1,“hh:mm:ss”) ，若 B1 单元格是时间的序列值，会将其转换为 小时:分钟:秒 的文本格式，如 12:30:00 。
  结合其他函数使用
• 与 VLOOKUP 等函数结合，在查找引用时对返回值进行格式转换。例如在一个包含日期和销售额的表格中，使用 =VLOOKUP(“2024-03-10”,A:B,2,0) 查找销售额后，若要将销售额格式化为货币格式，可再嵌套 TEXT 函数，即 =TEXT(VLOOKUP(“2024-03-10”,A:B,2,0),“￥0.00”)
  TEXT 函数转换后的结果是文本类型，若需要进行数值计算，可能需要使用 VALUE 等函数将其转换回数值
  

int(向下取整)（第19套）

用于将数字向下舍入到最接近的整数。语法为 INT(number) ， number 为要舍入的数值。比如， =INT(3.8) 会返回3， =INT(-2.3) 会返回-3。如果是日期，也会将其序列值向下取整，例如 =INT(“2024/1/1.5”) ，会把日期的小数部分舍去，返回对应的日期2024年1月1日，因为日期在Excel中是以序列值存储的，这里的小数部分表示时间

yearfrac（第19套）

用于计算两个日期之间的天数占全年天数的比例，可用于计算利息期间或折旧期间等。语法为 YEARFRAC(start_date, end_date,[basis]) 。其中， start_date 和 end_date 为必选参数，是要计算的起始日期和结束日期； basis 为可选参数，指定用于计算的日计数基准，默认值为0或省略，表示按实际天数/实际天数计算。例如， =YEARFRAC(“2024/1/1”,“2024/6/30”) 会返回0.5，即从2024年1月1日到2024年6月30日经过了半年

countifs(第20套)

COUNTIFS函数是Excel中用于统计满足多个条件的单元格数量的函数。下面通过一个例子来介绍它的使用方法

假设你有一个班级学生的成绩表，在A列是学生姓名，B列是学生的语文成绩，C列是学生的数学成绩。现在你想统计语文成绩大于80分且数学成绩大于90分的学生人数，就可以使用COUNTIFS函数

具体公式为： =COUNTIFS(B:B,“>80”,C:C,“>90”) 。在这里， B:B 表示要统计的第一个条件所在的区域，即语文成绩列； “>80” 是第一个条件，即语文成绩大于80分； C:C 是第二个条件所在的区域，即数学成绩列； “>90” 是第二个条件，即数学成绩大于90分

你还可以根据实际需求添加更多条件。比如，再加上一个条件，统计性别为“男”的学生中满足前面成绩条件的人数，假设性别在D列，公式就可以写成： =COUNTIFS(B:B,“>80”,C:C,“>90”,D:D,“男”)

总之，COUNTIFS函数的语法是 COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2,…]) ，其中 criteria_range 是要检查的区域， criteria 是对应的条件，你可以根据需要添加多个区域和条件来进行更复杂的统计

year，month，day（第21套）

YEAR函数
用于提取日期中的年份。语法为 YEAR(serial_number) ， serial_number 为要提取年份的日期值。例如， =YEAR(“2024/5/10”) 将返回 2024

MONTH函数
用于提取日期中的月份。语法为 MONTH(serial_number) ， serial_number 为要提取月份的日期值。例如， =MONTH(“2024/5/10”) 将返回 5

DAY函数
用于提取日期中的日。语法为 DAY(serial_number) ， serial_number 为要提取日的日期值。例如， =DAY(“2024/5/10”) 将返回 10

average，min，max（第22套）

• AVERAGE(number1,[number2,…]) ，其中 number1 为必须参数，[ number2,… ]为可选参数，最多可包含255个可选参数。这些参数可以是数字，或者是包含数字的名称、单元格区域或单元格引用

• 计算同列或同行数据平均值
  例如，要计算A1到A10单元格区域中数字的平均值，在其他单元格中输入 =AVERAGE(A1:A10) ，然后按下回车键即可得到平均值

• 计算不连续区域的平均值
  如果要计算A1到A10以及B1到B10这两个不连续区域中数字的平均值，可以输入 =AVERAGE(A1:A10,B1:B10)

• 忽略文本值和错误值
  当数据区域中包含文本值或错误值时，AVERAGE函数会自动忽略它们，只计算数字值的平均值。例如，A1到A5单元格中分别为1、2、3、“a”、#VALUE!，那么 =AVERAGE(A1:A5) 将只计算1、2、3的平均值，结果为2

• MIN函数

• 基本语法： MIN(number1,[number2,…]) 。 number1 为必须参数， [number2,…] 为可选参数，最多可包含255个可选参数，这些参数可以是数字，或者是包含数字的名称、单元格区域或单元格引用

• 示例：要找出A1到A10单元格区域中的最小值，在其他单元格中输入 =MIN(A1:A10) ，然后按下回车键即可。若要找出多个不连续区域中的最小值，如 A1:A10 和 B1:B10 ，则输入 =MIN(A1:A10,B1:B10)

• MAX函数

• 基本语法： MAX(number1,[number2,…]) 。参数规则与MIN函数相同

• 示例：若要获取C1到C20单元格区域中的最大值，在其他单元格中输入 =MAX(C1:C20) ，按回车键就能得到结果。若要在多个区域中取最大值，如 D1:D10 、 E1:E10 和 F1:F10 ，则使用公式 =MAX(D1:D10,E1:E10,F1:F10)

iferror(第23套)

• 语法： IFERROR(value, value_if_error)
• 功能：用于判断公式计算是否出现错误。如果 value 的计算结果为错误值，就返回 value_if_error 指定的值；如果计算结果正确，则返回 value 的结果。比如 =IFERROR(1/0,“除数不能为0”) ，因为1除以0会出现错误，所以该公式返回“除数不能为0”
• 实际应用场景：在数据处理中，当引用的数据可能存在错误或不完整时，使用 IFERROR 可以避免错误值显示在表格中，使表格看起来更整洁，数据更易读。比如在进行VLOOKUP查找时，如果查找不到对应值可能会返回错误值，可使用 IFERROR 进行处理，如 =IFERROR(VLOOKUP(A1,B:C,2,0),“无匹配数据”)

find（第23套）

• 语法： FIND(find_text, within_text, [start_num])
• 功能：用于在一个文本字符串 within_text 中查找另一个文本字符串 find_text ，并返回 find_text 在 within_text 中首次出现的位置。start_num 为可选参数，指定开始查找的位置，若省略则从第一个字符开始查找。例如 =FIND(“e”,“excel”) 会返回2，即“e”在“excel”中的位置是第2个字符
• 实际应用场景：在文本处理中，可以用于提取特定字符或判断文本中是否包含某个字符串等。比如要判断A列单元格中的文本是否包含“abc”，可使用公式 =IF(FIND(“abc”,A1)>0,“包含”,“不包含”)
• 和mid搭配使用可起到，提取一个单元格里固定的文字或符号后面的一段文字
  

value（第24套）

• 基本用法：若A1单元格中输入“123”（文本格式），在B1单元格输入公式 =VALUE(A1) ，则B1单元格将把A1中的文本“123”转换为数值123
• 与其他函数结合使用：在计算中，可先使用 VALUE 函数将文本型数字转换为数值，再参与其他运算。例如，A2单元格为“5”（文本格式），A3单元格为“3”（文本格式），在B2单元格输入公式 =VALUE(A2)+VALUE(A3) ，结果为8

注意事项

• 若文本字符串不能转换为有效数值， VALUE 函数将返回错误值 #VALUE!
• 对于一些包含非数字字符的文本， VALUE 函数会尝试从左到右识别数字部分进行转换，遇到非数字字符则停止。如 VALUE(“12a”) 结果为12
  

len(第24套)

在Excel中， LEN 函数用于返回文本字符串中的字符数。以下是其具体用法：

• 语法
  LEN(text) ， text 为要计算字符数的文本字符串，可以是直接输入的文本，也可以是单元格引用

-示例

• 计算固定文本的字符数：在A1单元格输入“Hello, World!”，在B1单元格输入公式 =LEN(A1) ，将返回13，即该文本字符串的字符数，包括空格和标点符号
• 计算单元格区域中文本的字符数：若A2:A5单元格区域分别存储了不同的文本内容，在B2单元格输入公式 =LEN(A2) ，然后向下拖动填充柄至B5单元格，可分别计算出A2到A5单元格中每个文本字符串的字符数

注意事项

• LEN 函数会将全角字符和半角字符都作为一个字符来计算
• 如果 text 参数是一个数值， LEN 函数会将其转换为文本后再计算字符数。例如， LEN(123) 的结果为3

<>表示不等号（第24套）

or(第24套)

• 语法： OR(logical1,[logical2,…]) 。 logical1 为必须参数，[ logical2,… ]为可选参数，最多可包含255个条件
• 功能及示例：用于判断多个条件中是否至少有一个条件成立。比如 =OR(A1>10,B1<5) ，若A1单元格的值大于10或者B1单元格的值小于5，结果就为 TRUE ，否则为 FALSE
• 注意事项：参数可以是逻辑值、逻辑表达式或包含逻辑值的单元格引用。若所有参数值为 FALSE ，函数返回 FALSE ，否则返回 TRUE
  

mod(第24套)

• 语法： MOD(number,divisor) 。 number 为被除数， divisor 为除数
• 功能及示例：用于返回两数相除的余数。例如 =MOD(10,3) ，返回值为1，即10除以3的余数。若要判断A1单元格中的数是否为偶数，可使用公式 =IF(MOD(A1,2)=0,“偶数”,“奇数”)
• 注意事项：若 divisor 为0，函数返回错误值 #DIV/0! 。 number 和 divisor 参数可以是数字，或者是包含数字的单元格引用

round(第24套)

• 函数语法
  ROUND(number, num_digits)
  number ：必需，要四舍五入的数字
  num_digits ：必需，指定的位数，按此位数对 number 参数进行四舍五入。如果 num_digits 大于0，则四舍五入到指定的小数位；如果 num_digits 等于0，则四舍五入到最接近的整数；如果 num_digits 小于0，则在小数点左侧进行四舍五入

• 示例
  ROUND(3.14159, 2) ：将3.14159四舍五入到小数点后2位，结果为3.14
  ROUND(5.678, 0) ：将5.678四舍五入到最接近的整数，结果为6
  ROUND(123.456, -2) ：将123.456在小数点左侧2位进行四舍五入，结果为100

and（第24套）

• 函数语法： AND(logical1,[logical2,…]) 。 logical1 为必须参数，[logical2,…]为可选参数，最多可包含255个条件。这些参数可以是逻辑值（TRUE或FALSE）、逻辑表达式或包含逻辑值的单元格引用
• 函数功能：当所有参数的计算结果都为 TRUE 时，返回 TRUE ；只要有一个参数的计算结果为 FALSE ，就返回 FALSE
• 示例： AND(2>1, 3<5) ，两个条件都为真，所以返回 TRUE

iseven(第24套)

• 函数语法： ISEVEN(number) 。 number 为必须参数，是要检验的数值
• 函数功能：用于判断一个数是否为偶数。如果 number 为偶数，返回 TRUE ；如果 number 为奇数，返回 FALSE
• 示例： ISEVEN(4) ，返回 TRUE ； ISEVEN(3) ，返回 FALSE

row(第24套)

• 函数语法
  ROW([reference])
  reference ：为可选参数。如果省略该参数，函数将返回包含该函数的单元格的行号；如果指定了单元格或单元格区域作为参数，则返回该引用中第一个单元格的行号

• 示例
  ROW() ：若在A1单元格中输入此公式，将返回1，因为A1单元格位于第1行
  ROW(B5) ：返回5，即B5单元格所在的行号
  ROW(A1:B10) ：返回1，因为它取的是指定区域A1:B10中第一个单元格A1的行号

• ROW函数常与其他函数（如INDEX、OFFSET等）结合使用，用于根据行号进行数据的查找、引用或偏移等操作

数组公式（第24套）（Ctrl+Shift+Enter）

数组公式是一种在Excel中能够同时对一组数据进行计算或操作的特殊公式。

普通公式通常只对单个单元格或单元格区域中的一个值进行计算，而数组公式可以对多个值同时进行计算，并返回一个或多个结果。它可以在一个公式中完成多个步骤的计算，就好像有一个“隐形”的循环在自动处理数组中的每个元素。

使用数组公式时，需要按 Ctrl + Shift + Enter 组合键来输入，而不是像普通公式那样直接按回车键。这样Excel就知道你输入的是数组公式，会按照数组公式的规则来进行计算。

例如，要计算两个数组对应元素相乘后的总和，可以使用数组公式 {=SUM(A1:A5*B1:B5)} ，这里的花括号 {} 是Excel识别数组公式的标志，不是手动输入的。它会先将A1到A5单元格中的每个值分别与B1到B5单元格中对应的每个值相乘，然后再将这些乘积相加，得到最终结果。

这里意思就是将if()的运算结果，也就是笔试分数存在数组当中，然后用min读取数组中所有的值，使用min找到这些值中的最小值

minifs和maxifs(第24套补充)

MINIFS函数用于在多个条件下查找最小值。以下是其使用方法：

函数语法
MINIFS(min_range,criteria_range1,criteria1,[criteria_range2,criteria2,…])

• min_range ：必需参数，要从中找出最小值的实际单元格区域。
• criteria_range1 ：必需参数，第一个用于条件计算的单元格区域。
• criteria1 ：必需参数，根据 criteria_range1 测试的值，以确定最小值，格式为数字、表达式或文本。
• criteria_range2,criteria2,… ：可选参数，附加的条件区域及其关联条件，最多可输入126个区域/条件对。

示例

• 假设A列是部门，B列是销售额，要找出“销售部”的最低销售额，公式为 =MINIFS(B1:B100,A1:A100,“销售部”) 。
• 若要找出“销售部”且销售额大于5000的最低销售额，公式为 =MINIFS(B1:B100,A1:A100,“销售部”,B1:B100,“>5000”) 。

MINIFS为一样使用方法

index(第26套)

• 语法格式
  INDEX 函数有两种主要的语法形式：
• 数组形式
  INDEX(array, row_num, [column_num])
  array：这是必需参数，指的是单元格区域或者数组常量。
  row_num：也是必需参数，代表要返回值所在的行号。
  column_num：为可选参数，表示要返回值所在的列号。若省略该参数，INDEX 函数会默认使用整个行。
• 引用形式
  INDEX(reference, row_num, [column_num], [area_num])
  reference：必需参数，是一个或多个单元格区域的引用。若引用多个区域，需要用逗号分隔。
  row_num：必需参数，代表要返回值所在的行号。
  column_num：可选参数，表示要返回值所在的列号。若省略该参数，INDEX 函数会默认使用整个行。
  area_num：可选参数，用于指定要从中返回值的引用区域。若引用了多个区域，area_num 可以用来选择具体的区域，其取值从 1 开始。若省略该参数，INDEX 函数会默认使用第一个区域。
• 实际示例
• 数组形式示例
  假设在 A1:C3 区域有如下数据：

	A	B	C
1	10	20	30
2	40	50	60
3	70	80	90

若要获取第 2 行第 3 列的值，可在其他单元格输入公式 =INDEX(A1:C3, 2, 3)，回车后就会返回 60。
引用形式示例
假设你有两个区域，分别是 A1:C3 和 E1:G3，数据如下：

	A	B	C		E	F	G
1	10	20	30		100	200	300
2	40	50	60		400	500	600
3	70	80	90		700	800	900

若要获取第二个区域（E1:G3）中第 3 行第 2 列的值，可在其他单元格输入公式 =INDEX((A1:C3,E1:G3), 3, 2, 2)，回车后会返回 800。
与其他函数组合使用
INDEX 函数常和 MATCH 函数搭配使用，以实现根据条件查找对应的值。例如，在 A 列是姓名，B 列是对应的成绩，若要查找姓名为 “张三” 的成绩，可使用如下公式：
plaintext
=INDEX(B:B, MATCH(“张三”, A:A, 0))

这里 MATCH 函数会返回 “张三” 所在的行号，INDEX 函数则根据这个行号从 B 列中获取对应的成绩。
通过上述介绍，你可以掌握 INDEX 函数在 Excel 中的基本用法，根据实际需求灵活运用它来处理数据。

clean,trim（第27套）

• CLEAN 函数

• 功能：用于从文本中移除不可打印字符，通常在从其他应用程序导入数据时，可确保数据的可读性和可用性。

• 语法：CLEAN(text)，text为必需参数，表示需要从中移除不可打印字符的文本，可以是单元格引用，也可以是直接输入的文本字符串。

• 示例：若单元格 A2 中的文本为=CHAR(9)&“Monthly report”&CHAR(10)，包含不可打印字符CHAR(9)和CHAR(10)，在其他单元格中输入=CLEAN(A2)，则会返回没有不可打印字符的文本 “Monthly report”。
  批量清理：若有一个包含各种文本条目的数据集，如在范围 F2:F100 中，可使用公式=CLEAN(F2:F100)，将 CLEAN 函数应用于该范围内的每个单元格，以确保所有文本数据都没有不可打印字符。

• TRIM 函数

• 功能：可将文本字符串前后的空格以及中间多余的空格去除，只保留单词之间的单个空格，使文本数据更加整洁规范。

• 语法：TRIM(text)，text为需要删除其中空格的文本，可以是直接输入的文本常量，也可以是对包含文本的单元格的引用。

• 示例：假设单元格 A1 中有文本 “hello world”，在 B1 单元格中输入公式 “=TRIM(A1)”，按下回车键后，B1 单元格中的值就变成了 “hello world”。
  批量处理：如果 A 列中有多行数据都有前后空格的问题，可先在 B1 单元格中输入公式 “=TRIM(A1)”，按下回车键确认后，再选择 B1 单元格，将鼠标移至右下角出现填充柄时，双击填充柄或拖动至需要处理的最后一行，这样 A 列数据的前后空格就都被批量去除了。

• 两函数结合使用（函数嵌套顺序不能颠倒）
  通常会将 CLEAN 函数与 TRIM 函数结合使用，以实现更全面的数据清理。例如公式=TRIM(CLEAN(A2))，先使用 CLEAN 函数移除不可打印字符，再使用 TRIM 函数移除多余空格，确保文本既没有多余空格，也没有不可打印字符。

vlookup模糊查找(第27套)

若使用要求对一个范围使用模糊查找，求一个值在一个范围里对应的内容，需要借助辅助表格，先把该范围的每一段的最小值标出来
比如说查找一个年龄位于哪一个年龄段

要看具体判断最小值，这里就不是1500而是1501

int（第29套）

• 基本用法：直接输入数值作为参数。例如，在单元格中输入 =INT(8.9) ，按下回车键后，将返回 8
• 引用单元格：参数可以是单元格引用。如在A1单元格中输入 7.6 ，在B1单元格中输入 =INT(A1) ，则B1单元格会显示 7
• 与其他函数结合使用：可与其他函数嵌套。例如，要计算A1到A10单元格区域中数值的平均值并取整，可以使用公式 =INT(AVERAGE(A1:A10))

index与match的联合使用（vlookup无法使用时）(第34套)

由于vlookup使用时，查找的数据必须在查找区域的第一列，如果不在第一列就只能使用index和match以实现根据条件查找对应的值，来代替vlookup



想要在D列填入地区对应的地区代码，第一个图中可看出vlookup无法使用
本题想要查找到地区对应的代码并填入，所需的值是地区代码，查找内容是地区，所以先使用match找到地区并返回地区所在行的值，然后使用index(所需的值，地区所在行)，由于第一个参数是一列，那么就会在一个列中寻找对应行的单元格内容

与vlookup找到所需值的方法相似，只不过index要靠match才能找到所需值的相关联的单元格，而vlookup可以直接先找到相关联单元格，再输入所需值在相关联单元格的右边第几列，就能找到所需值

indirect(第34套)

• INDIRECT 函数用于返回由文本字符串指定的引用。以下是其基本语法和使用方法：

• 语法
  INDIRECT(ref_text,[a1])
  ref_text ：必需，为对单元格的引用，此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。
  a1 ：可选，为一逻辑值，指定 ref_text 参数所指定的引用的类型。如果 a1 为 TRUE 或省略，则 ref_text 被解释为A1样式的引用；如果 a1 为 FALSE ，则 ref_text 被解释为R1C1样式的引用。

• 示例
  根据单元格内容获取对应单元格的值：例如，A1单元格中输入“B2”，B2单元格的值为10。在C1单元格中输入公式 =INDIRECT(A1) ，则C1单元格将返回B2单元格的值10。
  动态引用工作表中的数据：若要在多个工作表中根据工作表名称动态引用数据。假设在Sheet1的A1单元格中输入工作表名称“Sheet2”，Sheet2的A1单元格中有数据5。在Sheet1的B1单元格中输入公式 =INDIRECT(A1&“!A1”) ，则Sheet1的B1单元格将返回Sheet2中A1单元格的值5。
  创建动态数据区域：可以结合其他函数创建动态的数据区域。如要根据指定的行数和列数来获取一个动态的数据区域。假设A1单元格表示行数，B1单元格表示列数，在C1单元格中输入公式 =OFFSET(INDIRECT(“A1”),0,0,A1,B1) ，该公式会以A1单元格为起始点，根据A1和B1单元格中的值来确定数据区域的大小。

• 使用 INDIRECT 函数时，需确保引用的文本字符串是有效的单元格引用或名称，否则函数将返回错误值。

hyperlink(第38套)

• 在Excel中， HYPERLINK 函数用于创建超链接，其语法为 HYPERLINK(link_location,[friendly_name]) 。以下是其常见的使用方法：

• 链接到其他工作表
  如果要链接到当前工作簿中的其他工作表，公式可以写为 =HYPERLINK(“#Sheet1!A1”,“单击跳转”) ，表示跳转到Sheet1工作表的A1单元格，且单元格中显示“单击跳转”。若要链接到其他工作簿，则需在第一参数中加上文件的存放位置，如 =HYPERLINK(“D:\一起走过的旧时光\失恋33天（2022 - 2023）.xlsx#Sheet2!A10”,“单击跳转”) 。

• 建立工作表目录
  若A列是工作表名称，在B列输入公式 =HYPERLINK(“#”&A2&“!A2”,A2) ，可建立能够单击跳转的工作表目录。如果跳转后需要选中A2:A5多个单元格，公式为 =HYPERLINK(“#”&A2&“!A2:A5”,A2) 。

• 链接到Word文档中的指定位置
  首先在Word中插入书签，然后在Excel中输入公式，如 =HYPERLINK(“D:\一起走过的旧时光\已经远去的青春和未曾远去的她.docx#忆往昔”,“跳转”) ，单击链接将打开Word文档并定位到书签“忆往昔”的位置。

datedif(第40套)