Excel高频考点汇总

工作表和单元格考点汇总

复制工作表：选中需要复制的工作表→按住ctrl键→鼠标左键拖动即可

重命名工作表：选中工作表→鼠标左键双击即可修改名称

更改工作表标签颜色：选中工作表→鼠标右键→单击工作表标签颜色→设置题目要求的颜色

移动工作表：选中工作表→单击鼠标右键→移动或复制工作表→选择相应的位置即可

删除工作表：选中工作表→鼠标右键→删除

跨工作簿移动：打开工作簿1→光标定位在需要移动的工作表→打开工作簿2→选中工作簿2中的工作表→鼠标右键→移动或复制→选择移动至哪一个工作簿→再选择下列选定工作表之前中的某个工作表→点击确定即可

单元格格式：数值型、货币型、文本、自定义等

输入001、002……文本编号：选中需要设置编号的单元格区域→【数字】选项卡设置单元格格式为【文本】→在第一个单元格输入001→下方单元格输入002→选中001、002→鼠标来到右下角→黑色十心箭头往下拖动即可

输入001、002……数值编号：在第一个单元格输入1→下方单元格输入2→选中1、2→鼠标来到右下角→黑色十心箭头往下拖动即可→然后选中输入数字的区域→鼠标右键设置单元格格式→来到数值→保留0位小数→然后找到自定义→将类型处原有内容删除→输入“000”即可

设置日期格式：选中需要设置的日期区域→鼠标右键→设置单元格格式→找到日期→设置即可

设置2019年12月06日 星期五这种日期格式：选中日期区域→鼠标右键→设置单元格格式→找到日期→选中一种有年月日的格式→自定义→来到类型→把“；@”去除→在后面输入一个空格→输入“aaaa”即可

设置单元格框线：选中区域→来到【字体】找到边框→设置相应框线即可

去除网格线：【视图】选项卡→取消勾选网格线

设置小于15的单元格显示为干旱：选中区域→鼠标右键设置单元格格式→自定义→将类型处原有删除→输入“[<15]干旱”→点击确定即可

设置折扣为0单元格显示“-”→大于0单元格显示为百分比→并保留0位小数（如15%）：选中区域→鼠标右键设置单元格格式→自定义→将类型处原有删除→输入“[=0]“-”；[>0]0%”→或者输入“0%；；“-””点击确定即可

条件格式考点汇总

突出显示单元格规则：大于、小于、介于、等于、重复值

项目选取规则：值最大的、最小的10项→这里面的数字可以根据需求进行修改

利用条件格式将成绩中不低于110分的成绩单元格用一种颜色填充：选中数据区域→来到【格式】条件格式→【突出显示单元格规则】→【其他规则】→设置大于或等于的条件→设置值为110→【格式】→【填充】→设置颜色即可

设置一个区域的单元格值大于某个固定值的110%则突出显示单元格格式：【样式】→新建规则→使用公式确定要设置单元格格式的单元格→引用区域的第一个单元格→取消绝对引用→然后输入“>（引用固定值的单元格）并绝对引用乘以110%”→去掉【格式】中设置字体颜色填充色即可

利用条件格式显示成绩的第一名：选中一科成绩的数据区域→【条件格式】→【项目选取规则】→值最大的10项→将10改成1即可→然后设置要求的颜色和填充即可

仅以数据条形式显示单元格格式：选中数据区域→【条件格式】→数据条→选择相应的格式→来到【条件格式】→数据条→其他规则→把仅显示数据条勾选上→点击确定即可

套用表格格式考点汇总

给有合并后居中显示标题的表套用一个合适的表格格式：选中合并后居中以下的内容→【样式】→【套用表格格式】→选择一种样式即可

套用表格格式后的一些基本操作：我们可以在【表格工具】选项卡设置表名称、转化成区域、标题行、汇总行、镶边行、第一列等→进行勾选即可

排序考点汇总

排序依据：里面可以设置数值、单元格颜色、字体颜色

排序次序：升序、降序、自定义序列

对表格内容进行排序：【数据】→【排序】→设置【主要关键字】→设置【排序依据】→设置【次序】→勾选【包含标题】

设置自定义序列：【文件】选项卡→【选项】按钮→【高级】→拖动滚轮条到最低端→【编辑自定义列表】→手动输入序列→每输入一个敲一下回车→点击添加确定即可

根据设置的自定义序列进行排序：【数据】选项卡→【排序】按钮→设置【主要关键字】→设置【排序依据】→设置【次序】→选择【自定义序列】→找到提前设置好的序列→点击确定即可

筛选知识点：

筛选分为：自动筛选、高级筛选

自动筛选中的数字筛选：光标放在数据区域的首行单元格→【数据】选项卡→筛选→点击筛选的下拉框→可以对内容进行数据筛选→数据筛选中找到自己所需要的相关条件即可

自动筛选中的文本筛选：光标放在文本区域的首行单元格→【数据】选项卡→筛选→点击筛选的下拉框→可以对内容进行文本筛选→文本筛选中找到自己所需要的相关条件即可

高级筛选：首先设置好条件区域→将光标定位在结果表中→【数据】选项卡→【高级筛选】按钮→选择【在原有区域显示结果】→【列表区域】用鼠标引用需要进行高级筛选的数据区域→【条件区域】用鼠标引用提前设置好的条件区域→【复制到】选择需要开始存放结果的区域→点击确定即可

定位条件考点汇总

定位通用快捷键：CTRL+G

定位条件：定位批注、常量、公式、空值、……

删除所有公式错误值：全选数据→【开始】选项卡→【查找和选择】下拉框→【定位条件】→【公式】→勾选【错误值】→确定→【delete】删除即可

对数据中所有空单元格输入数字1：全选数据→→【开始】选项卡→【查找和选择】下拉框→【定位条件】→勾选【空值】→确定→鼠标不动→输入1→按ctrl+enter即可

合并计算考点汇总

将4个工作表中的数据以求和方式合并到新工作表中：光标定位在结果表→来到【数据】→【合并计算】→【函数】找到题目要求的函数→此题点求和→引用位置→鼠标引用第一个表的数据→然后点击添加→引用第二个表→点击添加→直到4个表数据均引用完毕→勾选【首行】→【最左列】

注：合并计算不可以处理前2列都是文本的多张表，一般第一列是文本，第二列的是数据即可处理

数据有效性考点汇总

数据有效性主要考核：允许输入条件、输入信息、出错警告等

条件：小数、整数、序列、文本长度、自定义

出错警告的三种样式：停止、警告、信息

给性别列设置数据有效性仅可输入男女并设置相应错误提示：选中需要设置数据有效性的区域→【数据】选项卡→【数据有效性】→【设置】→【允许】选择【序列】→【来源】→输入“男,女”→【出错警告】→样式选择停止→标题输入“信息输入有误”→错误信息输入“性别只能是男或女”→确定即可

给部门输入添加下拉选择的数据有效性：选中需要设置数据有效性的区域→【数据】选项卡→【数据有效性】→【设置】→【允许】选择【序列】→【来源】→手动输入部门然后逗号相隔或引用部门区域→确定即可

模拟分析考点汇总

数据模拟分析有三大重要考点：模拟运算表→方案管理器→方案摘要

模拟运算表：在【模拟运算表】第一个单元格输入公式→选中数据区域→【数据】选项卡→【模拟分析】→【模拟运算表】→引用数据模型中对应的行、列单元格→确定即可

注：如果需要复制公式到单元格中，复制完成以后按ESC键退出编辑状态,再粘贴

方案管理器：切换到【数据】选项卡→【模拟分析】→【方案管理器】→【添加】→输入方案名称→输入引用单元格→点击确定输入数据→确定

创建方案摘要：切换到【数据】选项卡→【模拟分析】→【方案管理器】→【摘要】→在结果单元格区域引用数据模型中包含公式的单元格

分类汇总考点汇总

利用分类汇总功能求出数据类型的和，每组数据不分页：首先对分类字段进行【排序】→【数据】选项卡→【分类汇总】→【分类字段】选择需要求和的类型→【汇总方式】选求和→选择汇总项→勾选相应类型→取消勾选每组数据分页→点击【确定】即可

注：应用了表格格式的数据表应全选→鼠标右键→表格→转化成区域→点击确定；否则【分类汇总】选项卡会是灰色使用不了

注：分类汇总前一定要记得排序、排序、排序

数据透视表考点汇总

插入数据透视表的步骤：选中数据区域→【插入】选项卡→数据透视表→选择放置数据透视表的位置→选择相应的字段放置到行标签、列标签、值汇总方式、报表筛选区域

数据透视表的数据分组主要分为种：第一是对日期（按照月、季度），第二种是对数值（求数值区间中的信息个数）

利用数据透视表向二维表转化为一维表：键盘按ALT+D+P→打开数据透视表向导→添加数据→调整布局

注：设置数据透视表时，题目给了具体字段放在那个位置就按照题目来，这种最简单；遇到没有给具体要求的，只给了样例图的要自己分辨字段所放位置

EXCEL图表考点汇总

图表类型：柱形图、折线图、饼图、组合图表、迷你图

图表重要考核知识点：更改图表类型、切换行列

图表元素包含：图表标题、坐标轴标题、图例、数据标签、模拟运算表、网格线、坐标轴、趋势线

设置数据标签格式：可以设置标签类型、数字、填充、边框颜色、边框样式、阴影、对齐方式等

标签选项：可以设置标签包括哪些内容（系列名称、类别名称、值、百分比、显示引导线）、可以设置标签的位置（居中、数据标签内、外等）、还可以选择分隔符（逗号、分号、句号等）

带数据标记的折线图是考试重点，主要考核数据标记选项的设置：数据标记选项的内置类型和大小、数据标记填充色、标记线颜色、标记线样式

坐标轴的设置：坐标轴最大值、最小值、主要刻度单位、主要刻度线类型、坐标轴标签设置

组合图表：设置组合图表时，注意设置坐标轴系列，放在主或次坐标轴

插入迷你图：选择图表类型→选择数据区域→选择迷你图放置区域

注：Excel图表和Word图表有许多相似之处，所以大家把Word图表学好，这里就简单了

EXCEL页面设置考点汇总

页面的基本设置：页边距、页眉页脚、纸张大小、纸张方向等

将图片设置为页面背景：【页面布局】选项卡→背景→选择图片→点击确定

设置打印标题行：光标定位在数据区域内→【页面布局】选项卡→右下角菜单栏→【工作表】→【打印区域】利用鼠标选中需要打印的数据区域→【顶端标题行】引用表的标题行→点击确定即可

如何将工作表打印在一页上：光标定位在数据区域内→【页面布局】→右下角菜单栏→【页面】→【缩放】→调整为一页宽一页高

EXCEL获取外部数据和删除重复值：从网站获取数据：光标定位到表格的首个单元格→找到网页→选中网页→单击右键→【打开方式】→IE浏览器打开→将光标定位到【网站地址栏】→【复制】→关闭网站→来到EXCEL→【数据】→【自网站】→地址位置【粘贴】刚复制的内容→点击【转到】→选中网站中的表格→勾选→点【导入】→确定即可

注：这种导入方式你也可以直接去网站中将表复制粘贴进来，更方便

注：导入时可以导入前→左下角【属性】→设置相应的功能

从文本获取数据，并且数据中有身份证号码：【数据】选项卡→【自文本】按钮→找到存放的文本→点击【打开】→【文本导入向导】→选择最合适的【文件类型】→选择【分隔符号】→【文件格式】选择简体中文→【下一步】→选择【分隔符号】→【下一步】→选择【数据格式】→选中身份证号码→设置【数据格式】为文本→点击【完成】→点击【确定】

注：身份证号码那一栏必须选择设置为文本，否则会以科学记数法显示

删除重复值：【数据】选项卡→【删除重复项】→勾选需要删除重复项的列→点击【确定】即可

常用公式和计算函数的考点

注意事项：

Ø 书写函数公式时单元格格式一定不能是文本

Ø 函数三要素：等号、函数名、参数

Ø 函数当中的标点一定是英文标点

Ø 最后一个就是要细心，要有耐心

EXCEL工作表中的加减乘除运算：在单元格中输入英文标点的“=”→然后点击需要计算的单元格→使用计算的类型（加减乘除）键盘输入即可→点击另外的单元格→按enter即可计算

五大运算函数：求和函数【SUM】、求平均【AVERAGE】、求最大值【MAX】、求最小值【MIN】、求个数【COUNT】

求和函数【SUM】:单元格中输入”=SUM(计算求和的区域)”

求平均【AVERAGE】：单元格中输入”=AVERAGE(计算平均值的区域)”

求最大值【MAX】：单元格中输入”=MAX(找最大值的区域)”

求最小值【MIN】：单元格中输入”=MIN(找最小值的区域)”

求个数【COUNT】：单元格中输入”=COUNT(计算个数的区域)”

IF函数考点：

IF条件判断函数

IF使用方法：（逻辑判断函数，语句1，语句2）

解析：如果逻辑判断成立，则执行语句1，否则执行语句2

案例：IF(A2>90,”优秀”，“合格“)

案例解析：如果A2单元格的值大于90，则显示优秀，否则显示合格

注：输入中文的时候必须使用引号，文本一定要用双引号引起来，其次所有标点均为英文标点状态下输入的公式

RANK函数和单元格引用：

RANK排名函数

RANK使用方法：（排名对象，排名区域，排名次序）

排名次序：0表示降序，1表示升序，如果排名次序缺失，表示降序

解析：求排名对象在排名区域的排名，排名区域一定要绝对引用；

注：排名函数的排名区域大部分情况下都得绝对引用

单元格引用分为：绝对引用、相对引用、混合引用。比如在单元格B1中：

1.输入=A1，这是相对引用，当被复制到其他单元格时，始终引用与公式所在单元格左边一个单元格（即A1相对于B1的位置）；

2.输入=$A1，行相对列绝对混合引用，横向复制公式不会变化，竖向复制公式时，行号会发生变化，比如复制到C2时，变为=$A2；

3.输入=A$1，行绝对列相对混合引用，竖向复制公式不会变化，横向复制公式时，列标会发生变化，比如复制到C2时，变为=B$1；

4.输入=$A$1，绝对引用，横向竖向复制公式，都不会变化，始终引用该单元格。

注：我们可以把$符看成是一把锁，放在列标前面，就把列标锁住，放在行号前面，就把行号锁住，绝对引用可以选中区域按键盘F4即可快速绝对引用

条件计算函数重要考点：

countif（）与countifs（）单/多条件求个数函数

使用方法：

countif（条件区域，条件），countifs（条件区域1，条件1，条件区域2，条件 2，……）

注：条件可以手动输入，也可以鼠标选择。

sumif（）与sumifs（）单/多条件求和函数

使用方法：

sumif（条件区域，条件，计算求和区域）

sumifs（计算求和区域，条件区域1，条件1，条件区域2，条件2，……）

注意：

Ø 为了避免出现错误，尽量都用sumifs 函数。

Ø 条件书写时要注意格式，譬如没有6月31日

Ø 条件区域大小和计算求和区域大小（行数）要一致

Sumproduct函数重要考点

Sumproduct乘积求和函数

使用方法：Sumproduct ((条件1）*（条件2），计算求和区域）

注：与sumifs用法相似，都可以实现多条件求和，但一般情况下建议大家使用sumifs函数

写条件时一定不要忘记书写括号

VLOOKUP函数重要考点：

Vlookup（）跨表纵向查询填充函数

使用方法：vlookup（查询依据，查询的数据区域，结果所在的列数，精确还是近似）

注：

Ø 查询的数据区域如果没有定义名称一定要进行绝对引用

Ø 查询的数据区域必须以查询依据作为第一列

Lookup函数重要考点

Lookup查询函数

使用方法：Lookup（查询依据，查询的数组，结果对应的数组）

将编号01、02、03在班级列填入一班、二班、三班：

LOOKUP(引用编号列第一个单元格，{“01“，”02“，”03“}，{“一班“，”二班“，”三班“})

注：数组书写一定要用 { }

index+match函数重要考点：

INDEX和MATCH都是查询函数

使用方法：

INDEX函数（数据区域，行号，列号）

MATCH函数（对象，数据区域，匹配类型）

一般是2个函数组合查询：

INDEX函数（数据区域，MATCH函数（对象，数据区域，匹配类型），MATCH函数（对象，数据区域，匹配类型））

注：这2个函数结合可以进行二维查询

日期函数重要考点：

TODAY（）返回系统日期

YEAR（）取年份

MONTH（）取月份

DAY（）取天，返回值为数值型

Datedif（起始时间，终止时间，相隔类型）求两个日期相隔多少个时间单位

WEEKDAY（）返回星期值函数

WEEKDAY（日期，星期开始类型）

注：通常选择类型为2，将周一作为一周的开始。

文本函数函数重要考点

left（对象，n）从左边取n个字符串，right（）从右边取n个字符串

mid（对象，起始位置，n）从指定位置取指定长度的字符串

text（对象，转换形式）

常用数值函数

Int（）求整函数

mod（）求余数

roundup（）往上四舍五入， rounddown往下四舍五入

large（数据区域，N）在指定区域从大到小排第N的数值

small（数据区域，N）在指定区域从小到大排第N的数值

数组函数

什么是数组？

即是有一定个数元素的集合

数组函数就是利用上述所学函数，进行构建，官方对此的解释就是：可以写一个以数组为参数的公式，即数组公式，就能通过这个单一的公式，执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。

注：使用数组函数进行运算时一定要按ctrl+shift+enter,才能进行运算