计算机二级Excel考点及详细解题步骤归纳

马上就临近计算机二级考试了，报考了Office但是知识还没复习好？这份计算机二级ms office之Excel知识点及详细解题步骤归纳快收好吧。史上最强，作者尽心整理，干货满满，赶快收藏学起来吧！

Excel

合并计算

将4个工作表的数据以求和方式合并到新工作表中

选取数据点添加，勾选首行和最左列：新建工作表命名为月销售合计，在“数据”卡片“数据工具”中的“合并计算”，“函数”中选择“求和”“引用位置”后面的小矩形，来到第一张工作表中ctrl+A全选数据，敲回车，点“添加”，像这样添加完四张工作表，最后勾选“首行”和“最左列”，最后在缺失文本的A1单元格输入“名称”

多用于第一列是文本，后面都是数据的工作表

真题28

分类汇总

考点

当“设计”选项卡中“分类汇总”显示灰色无法操作时时：单击右键点击“表格”，“转换为区域”

首先按照分类字段排序：在“数据”选项卡中“排序”升序或降序都行

例题

通过分类汇总求出“应付工资合计”“实发工资”的和

先把“部门”排序，再在“分类汇总”中“分类字段”选择部门，“汇总方式”选择“求和”，“选定汇总项”为“实发工资”和“应付工资合计”，不勾选“每组数据不分页”

真题7

定位条件

考点

位置

定位通用快捷键

ctrl+g

“开始”卡片中“编辑”的“查找和选择”的“定位条件”

常考：“公式”中的错误；“空值”

例题

删除表格中的错误值并输入1

选中表格，在“开始”卡片中“编辑”的“定位条件”“公式”“错误”，再按删除 在“定位条件”中选择“空值”输入1，按住ctrl键不放，敲回车

在G3：K336单元格区域中的空单元格中输入0；删除姓名列中所有字母，只留汉字

选中G3:K336数据区域，在“定位条件”中选择“空值” 输入0 按住ctrl键加回车

把姓名列复制粘贴到word中，在“替换”中“查找内容”“更多”“特殊格式”“ 【空格】任意字母”，“替换为”不输入使之为空

真题22

获取外部数据和删除重复值

获取外部数据

自网站

从网站中导入数据

光标放在excel A1单元格，网页打开方式选择IE浏览器，光标放在地址栏复制，再打开Excel，在“数据”卡片中“自网站”粘贴到“地址”点“转到”，往下拉，勾选表格数据 点“导入”点“确定” 或者可以直接复制粘贴

优势：“导入”后的“属性”中“刷新控件”可以刷新数据

真题4

自文本

从文本中导入

点击A1单元格，“数据”卡片中“自文本”选择相应文件，勾选“分隔符号”，“文件原格式”调成“简体中文”“下一步”“tab键”“下一步”在“数据预览”中选择身份证号码，并在“列数据格式”中选择“文本”“确定” 分开学号和姓名：先在c中插入一列，在学号后点击空格键，选中A列，在“数据”卡片“数据工具”中“分列”勾选“固定宽度”“下一步”在“数据预览”中移动标尺分开

真题10

删除重复值

删除订单编号重复的记录

选中订单编号那一列，在“数据”卡片“数据工具”中的“删除重复项”，“列”勾选订单编号

真题13

图表考点

新建折线图，添加标签

左边点击2显示第二等级，选中4个季度的数据“插入”卡片中“折线图”，将折线图剪切粘贴到新建工作表中；“图表工具”中“设计”的“切换行列”；“布局”的“模拟预算表”“显示模拟预算表” 给每类开支最高季度月均支出值添加标签：双击最高点，“数据标签”“上方”

真题11

创建图表，体现对比情况

子主题 1

真题17

页面设置

考点

（页边距、纸张方向、纸张大小、打印区域、页面背景、）打印标题、页眉页脚、缩印

题目

纸张大小方向、页宽高

“页面布局”卡片“页面设置”右下角箭头，“页面”中“方向”中调整纸张方向，“缩放”中调整为1页宽1页高，调整纸张大小； “页边距”中“居中方式”中勾选“水平”

真题21

数据区域设置为打印区域，标题可重复出现；纸张设置为横向，添加页眉页脚

选中表格，“页面布局”卡片中“打印区域”的“设置打印区域”；“打印标题”在“顶端标题行”中选中标题所在行，再“确定” 按住shift选中所有工作表，设置纸张方向为横向，在“页面设置”右下角箭头打开，“页眉页脚”在“自定义页眉页”“中”的框输入内容；在“页脚”选择页码格式 可点击右下的页面视图来检查

真题22

工作表和单元格

工作表

考点

新建表，重命名表，移动复制表，设置工作表颜色，隐藏工作表，删除工作表

对着下面那栏选中工作表单击右键

例题

复制工作表，将副本放置到原表右侧，改变该副本的颜色并重命名

按住ctrl键不放拖动Excel下面那栏的工作表到右边松手，双击重命名；单击右键“工作表标签颜色”

真题11

将工作表“统计数据”插入到另一工作表“比较数据”的右侧（考点为工作表的的移动复制）

单击统计数据工作表右键“移动或复制”，在“工作簿”选择”数据分析文件“，选择“移至最后”

真题4

单元格

考点

单元格的12种数字格式（千位分隔符、货币符号、自定义是难点），单元格的对齐方式（合并居中和跨列居中对齐），单元格的填充和清除

例题

插入序号、修改格式、设置网格线

A3单元格中输入“序号”，为销售记录插入序号，修改数字格式；对标题行区域应用单元格上下框线，对数据区域最后一行应用单元格下框线；去掉网格线

在A3输入“序号”，选中A4至391，在“开始”卡片单元格格式调整为“文本”，再开始输入001、002，下拉自动生成；选中日期列，单击右键“设置单元格格式”,在“分类”中选择“日期”型，类型选择“/” 选中标题区域，在“开始”卡片中“字体”的“边框”中设置上下框线，选中数据最后一行，设置下框线 在“视图”卡片“显示”中取消勾选“网格线”

真题18

设置单元格格式：在日期后添加星期几

连续快速选择【的快捷键是按住ctrl+shift+方向键的下】日期列，单击右键“设置单元格格式”在“分类”选择“自定义”在“类型”选择“yyyy年m月d日：@”去掉“；@”改为aaaa【aaaa：星期几；aaa：星期简写；dddd：英文星期几；ddd：英文星期简写】

真题5

设置数字格式：由1、2...改为数值型001、002...

在序号输入1、2...，选中序号列单击右键设置单元格格式在“分类”选择“数值”，“小数位数”填写0；再选择“自定义”在“类型”输入000【0代表任何数字,用0填补空位】

真题23

修改单元格数字格式，值小于15显示“干旱”

选中区域，单击右键“设置单元格格式”在“分类”“自定义”在“类型”输入条件“[<15]干旱”

举例多条件（最多3个条件）：[<15]干旱;[<100]适中;多雨

真题26

修改格式，折扣为0显示-，大于0显示百分比格式

选中折扣，单击右键“设置单元格格式”“自定义”，在“类型”处输入“ [=0]"-";[>0]0% ”【不是中文的条件要加上“”】 “类型”处还可以这样填写：0%;;"-"【意思是正数显示百分比，没有负数不显示，负数显示-】

真题27

模拟分析

例题

在B7：M27创建模拟运算表，模拟不同的年需求量和单位年储存成本所对应的不同经济订货批量

双击数据模型中经济订货批量的单元格复制粘贴公式到数据区域的首单元格，全选数据区域，在“数据”卡片“数据工具”的“模拟运算表”，在“输入引用行的单元格”中输入数据模型中代表行的单元格“$C$2”在“输入引用列的单元格”中输入数据模型中代表列的单元格“$C$4”

真题25

根据C2:C4作为可变单元格，按照要求创建方案管理器

选中C2到C4，“数据”卡片“模拟分析”中选择“方案管理器”“添加”在“方案名”填入“需求下降”“确定”在“年需求量”填入10000，在“单次订货成本”填入600，在“单位年储存成本”填入35，点“添加”；在“方案名”填入“需求持平”“确定”；在“方案名”填入“需求上升”，在“年需求量”输入20000，在“单次订货成本”输入“450”，在“单位年储存成本”填入“27”

真题25

以C5单元格为结果创建方案摘要，新工作表位于右侧

选中C5单元格，在“数据”卡片中“模拟分析”选择“方案管理器”选择“摘要”，在“结果单元格”选择C5单元格，“确定”；将方案摘要移到右侧

真题25

函数

函数公式

if

考点

逻辑判断条件：成立语句1，否则语句2

英文标点，可将输入法点为英文标点

公式：=if(单元格>=条件,"语句1"，"语句2")

输入公式后按回车,双击右下加号

画图理清关系

例题

考核成绩判断等级\排名

通过考核成绩判断等级

=if(考核成绩单元格E2>=60,"及格","不及格")

通过考核成绩判断排名

从大到小写:=if(考核成绩单元格E2>=90,"优秀",if(考核成绩单元格E2>=80,"良好",if(考核成绩单元格E2>=60,"及格","不及格"))) 或从小到大写:=if(E2<=60,"不及格",if(E2<=80,"及格",if(E2<=90,"良好","优秀")))

求个人所得税

=if(应纳税所得额K2<=1500,K2*0.03-0,if(K2<=4500,K2*0.1-105,if(K2<=9000,K2*0.2-555,if(K2<=35000,K2*0.25-1005,if(K2<=55000,K2*0.3-2755,if(K2<=80000,K2*0.35-5505,K2*0.45-13505))))))

左对齐,自动换行

根据成绩写期末总评

在总评单元格=if(学期成绩F2>=102,"优秀",if(F2>=84,"良好",if(F2>=72,"及格","不及格")))

真题10

根据工龄计算工资

=if(工龄>=30,工龄*50,if(工龄>=10,工龄*30,if(工龄>=1,工龄*20,0)))

真题19

lookup

考点

查询对象，查询的数据区域，对应的结果值

例题

根据档案编号第7、8位自动生成部门（其中7、8代表的是部门）

在部门下的单元格=lookup(mid(A2,7,2),【意思是在A2中间取,从第7个位置开始取,取两位】{"01","02","03","04"},{"销售部","财务部","生产部","人事部"}

子主题 2

学号第3位为专业代码，第4位为班级序号

方法

lookup

班级下单元格=lookup(mid(学号单元格,3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"}

文本连接符

班级下单元格="法律"&numberstring(mid(学号下单元格,4,1),1)&"班"【意思是从第4位开始取，取1个长度，按照第1种形式，其实还有第2种形式，只是大小写数字的区别】

真题8

查询index与match

考点

match

对象,数据区域,匹配类型

求对象在该区域内排第几

index

数据区域,行号,列号

引用区域

例题

求广州市7月降水量

=match(空白处的广州市R3,选中城市列A:A,选择精确匹配0) =match(空白处的7月S2,选中月份行1:1,精确匹配0) =index(选中全部表格数据,match(R3,A:A,0),match(S2,1:1,0) =index(选中全部表格数据,match,(R3,A:A,0),match(S2,1:1,0))

真题25

求出某对象的位置区域(例如求出广州市7月降水数据所在行列):=match(空白处目标,选中月份行,精确匹配)列也是相同操作 查询某位置对应数据(例如求出广州市7月降水数据):=index(选中数据,行,列) 其中行和列用match函数表示

rank

考点

=rank(排名对象、数据区域 按F4、次序)F4可以锁定单元格或者Fn+F4【在行号和列号前加入美元符号】

注意：数据区域需要绝对应用，顺序可忽略

水平方向拖动：列变行不变；垂直方向拖动：行变列不变

例题

根据成绩排名

在排名下的单元格=(考核成绩E2,考核成绩那一列E2:E19按下功能键F4,降序0)其中0为降序,1为升序

九九乘法表

=B1*$A2【打横拖动】在列号前加美元符号让列号不变 =B$1*$A2【打竖拖动】在行号前加美元符号让行号不变

跨表引用求销售额

=第一张表的销量单元格C2*第二张表格的单价单元格C2

引用

相对、绝对、混合引用

sum

考点

sumproduct可拆开运行,sum是求和,product是求乘积【连乘】

写法1 =sumproduct(数组1*数组2) 写法2 =sumproduct(数组1,数组2)

sumproduct((区域=条件)*(区域=条件)*计算求和区域)

例题

利用单价销售利润率求利润

=product(选中单价销量到利润率的单元格B10:D10)，再用=sum(所有产品利润的单元格E:10:E14)

利用单价销量求总销售额

=sumproduct(所有产品单价的单元格B2:B6,所有产品销售的单元格C2:C6)或者中间用*隔开

对应校对码

将身份证的前17位数分别与对应系数相乘,将成绩之和除以11,所得余数与校对码对应

在计算校验码下面的单元格=sumproduct(第1位到第18位D3:T3*校对系数$E$5:$U$5)【可以按f4对校对系数进行绝对应用】

真题30

求女职工工资总和

生产部女职工的工资总和： =sumproduct((部门所在那一列D2:D7=生产部单元格D2)*(性别所在那一列C2:C7=女所在单元格C2)*工资那一列E2:E7）相乘时逻辑真充当1，逻辑假充当2

女职工的工资总和： =sumproduct((性别那一列C2:C7=女单元格C2)*工资那一列E2:E7)

sumproduct((区域=条件)*(区域=条件)*计算求和区域)

求图书在2013年各月销量

方法一：sumproduct

1月下单元格【第一个条件区域是图书名称*第二个条件区域是年份*第三个条件区域是月份*计算求和区域是计算销量本】 =sumproduct((销售订单中全部图书名称那一列=分析表中该本图书名称)*(year(销售订单中日期这一列)=2013)*(month(销售订单中日期这一列)=1)*(销售订单中销量这一列) 剩下的月份粘贴复制公式，只需将1修改为月份

方法二：数据透视表

在销售订单创建数据透视表，“插入”“数据透视表”在新的页面，将图书名称放在行标签，日期放在列标签，销量放在求和项，点击日期单击右键“创建组”起始起始日期是2013/1/1,终止日期是2013/12/31 "确定" "列标签"箭头下拉把多余去掉,只留下月份 把书名排序(把它变成自定义序列):选中销售分析中的全部书名"文件""选项""高级""编辑自定义列表""添加""导入""添加""确定" 再来到数据透视表 行标签箭头下拉 "其他排序选项""升序""其他选项"去掉勾选"每次更新"找到office办公"确定" 最后再把数据透视表的数据粘贴到销售分析中

vlookup

考点

定义

跨表纵向查询填充函数

格式

vlookup(查询对象,查询的数据区域,结果在数据区域中的列数,精确匹配还是近似匹配)

通俗版

vlookup(找啥,上哪里找,告诉我结果在第几列,找一模一样的还是差不多的)

例题

精确匹配,一一对应

订单表:根据图书编号查找图书名称、单价(订单表和编号对照表位于同一表中)

在图书名称下单元格=vlookup(图书编号下的单元格C2,旁边以图书编号为开始的数据区域$J$1:$L$18,图书名称位于数据区域的第2列 2,精确匹配false) true为近似匹配,false为精确匹配或者0默认为精确匹配,1为近似匹配

单价下的单元格=vlookup(图书编号下的单元格C2,数据区域,3,false)

订单表:根据图书编号查找图书名称、单价(不同表)

=vlookup(图书编号下的单元格C2,点一下第二张表 选中中的对照表,图书名称位于第2列,精确查询false) 直接敲回车

单价下的单元格=vlookup(图书编号下的单元格C2,数据区域,3,false) 直接敲回车

近似匹配,区间对应

以成绩区间为标准给成绩评定等级

等级下单元格 =vlookup(查询成绩下的单元格A2,成绩区间等级对应表的数据区域$E$1:$F$5,等级位于第2列 2,近似查询true)

根据等级计算提成

提成下的单元格=vlookup(销售额下的单元格F22,数据引用区域$A$21:$C$32,3,TRUE)*F22 【因为求出的是提成率还需要乘销售额】

求根据年销售额计算客户等级

先根据客户代码求出求和客户年销售额

在客户等级下单元格=sumifs(订单信息表中订单金额那一列G:G,客户代码那一列B:B,原表中客户代码下单元格A2) 意思是对订单信息中客户金额进行求和,条件区域为信息订单中客户代码那一列,满足客户信息表中客户代码单元格A2

嵌套入vlookup函数

在客户等级下单元格=vlookup(sumifs(订单信息表中订单金额那一列G:G,客户代码那一列B:B,原表中客户代码下单元格A2) ,客户等级表中的级别对照表数据区域A1:B11,结果等级位于第2列 2 ,近似匹配true) 意思是用条件求和求出年总销售额【对订单信息中客户金额进行求和,条件区域为信息订单中客户代码那一列,满足客户信息表中客户代码单元格A2】，查询数据区域，结果位于第2列，近似匹配2

日期函数

考点

year（）年、month（）月、day（）天

datedif隐藏函数（求起始日期、终止日期、日期单位）

weekday（日期、星期返回类型）

例题

根据出生日期计算年龄，满一年才计1岁

在年龄下单元格=datedif（出生日期单元格E2,today(),"y"）【意思是起始日期和终止日期中间相隔了几个年】

真题10

若"日期"列为周六和周日,则"是否加班列"显示"是",否则显示"否"

在是否下单元格=if(weekday(日期下单元格A3,2)>5,"是"，"否")

真题5

计算员工截止2015年9月30日的年龄,其中每月按30天、一年按360天计算

在年龄下单元格=int((days360(出生日期单元格H2,"2015-09-30"))/360) 【days360(起始日期,终止日期)】

day360求两个日期相距多少天:按每年360天返回两个日期间相差的天数(每月30天) int:结果求整

真题19

数学函数

考点

lnt（）求整数；mod（）求余； ROUNDUP（）向上四舍五入；rounddown向下四舍五入； large（数据区域，n）；small（数据区域，n）

例题

输入学生的性别，根据出生日期计算年龄（按周岁计算，满1年才计1岁）

身份证号码第17位代表性别（奇男偶女）=if(mod(mid(身份证号码下单元格，17,1),2)=0,"女","男"【先取出身份证号码第17位，再通过mod求余数，最后用if进行判断】

真题10

计算停车费，车型不同价格不同，不满15min按15min计算

收费金额(不满15min按15min计算): 收费金额下单元格=ROUNDUP(停放时间下单元格J2*24*60/15,0)*收费标准下单元格E2)【停放时间本质是几天，乘24小时，乘60分钟，先计算共停放了多少分钟，再除以15计算有几个收费单位,向上四舍五入,保留0位小数,乘收费标准】 拟收费金额(不满15min不收费): =rounddown(J2*24*60/15,0)*E2

插入季度列,根据月份自动生成相应季度

插入新列命名为季度,季度下单元格=roundup(month(A3)/3,0)&"季度";单元格格式调整为"常规"【先对日期求月份,再除以3,0位余数,最后加上季度】

真题11

求1到6月份销售业绩前三名

销售第一名业绩旁的单元格=large(引用1月份下全部单元格，1) 再打横往旁边拉，生成23456月份的； 第二：=(1月下单元格,2)，再往右边拉生成其他月份； 第三：=(1月下单元格,3)，再往右边拉生成其他月份；【1代表第一大，2代表第二大，3代表第三大】

常用计算函数

average求平均

求1到6月平均值

=average(1到6月数据区域H8:M8)

平时30%、期中30%、期末40%按比例计算学期成绩

学期成绩下单元格=平时成绩下单元格C2*0.3+期中成绩下单元格D2*0.3+期末成绩下单元格E2*0.4

真题10

求各项目月平均支出

=average(1月到12月的数据区域B2:B13)

sum求和

求总支出

=sum(需要求和的区域B2:L2)

count求个数

计算区域中数字的个数 =count(数据区域 xx:xx)

计算区域中非空单元格的个数 =counta(数据区域 xx:xx)

max求最大

最高开支

=max(1月到12月数据区域B2:B13)

min求最小

最低开支

=min(1月到12月数据区域B2:B13)

另 : 时间段

求停放时间

停放时间下单元格=出场日期H2-进场日期F2+进场时间I2-出场时间G2； 时间段: 单击右键设置单元格格式，在"分类"中选择自定义,在"类型中选择"h"时"mm"分,在h加入英文[]即"[h]"时"mm"分

真题12

注意

出现数据区域(某个单元格到每个单元格)时需要按F4或加美元符号$锁定

单元格格式不能是文本：在“开始”卡片“数字”中调整

等号开头：等号是运行命令

标点必须是英文标点

数组函数

考点

数组是有一定个数元素的集合 例：{8,10,13,15}

数组间可以进行数字运算，得到的同样是一个数组

需要注意的是数组进行运算时双方元素相等

例题

根据产品单价和销量求总销售额； 求A销售员的总销售额

总销售额下单元格=sum(B2:B12*C2:C12),再ctrl+shift+enter【数组函数不能直接按回车】 [ {=sum(B2:B12*C2:C12)} ] A总销售额：A总销售额下单元格=sum(if（A2:A12="A",B2:B12*C2:C12,FALSE）)最后ctrl+shift+enter【若销售员列单元格内容满足是A的条件显示对应的单价乘销量，不满足就显示false】

求黄石一、二中一、二班的学生人数、总分、最高分

在黄石一中一班学生人数下单元格=count（if((学校列$A:$2:$A$31=黄石一中单元格F3)*(班级列$B:$2:$B:$31=一班单元格C3),成绩列$C$2:$C$31）)【如果满足单元格内容为黄石一中的条件，同时班级列单元格内容为一班】；其他班直接拖拉 复制公式时，先复制公式 再按下x取消，再粘贴到单元格 总分：把count改成sum即可 最高分：把count改成max 最低分：把count改成min 平均分：把count改成average

求滨海市第一二三中学的最高、低分

最高分：=max(if((成绩单中的学校A列=当前按班级汇总表的A2)*(成绩单中的班号B列=当前按班级汇总表的B2),成绩单中的成绩D列)) ctrl+shift+回车 复制粘贴时要按x取消再粘贴

真题16

条件计算函数

考点

countif

单条件：（范围，条件）在某个范围当中符合条件的个数

例：求女职工人数

=countif(性别列C2:C45,"女")

或 =countif(性别列C2:C45,引用性别列中任意为女的单元格C2)

例:工资大于1000的职工人数

=countif(工资列E2:E45,">10000")

countifs

多条件：（数据范围1，条件1，数据范围2，数据2）

例:生产部女职工人数

=countifs(部门列D2:D45,"生产部",性别列C1:C45,"女")

或 =countifs(部门列D2:D45,任意含有生产部的单元格D39,性别列C1:C45,任意含有女的单元格C2)

sumif

单条件求和:(条件区域,条件,计算求和区域)

例:求女职工的工资总和

=sumif(性别列C2:C45,"女",工资列E34:E45)

sumifs

多条件求和:(计算求和区域,条件区域,条件,条件区域,条件)

例:求生产部女职工的工资总和

=sumifs(工资列E2:E45,部门列D2:D45,生产部D2,性别列C2:C45,女C41)

例:求生产部女职工平均工资

=averageifs(工资列E2:E45,部门列D2:D45,生产部D2,性别列C2:C45,女C41)

sumif与sumifs条件书写刚好相反,建议只记sumifs

例题

根据"成绩单"工作表求"按班级汇总"工作表中滨海市一中一班考试学生数

=countifs(成绩单中学校名称列A:A,按班级汇总中一中单元格A2,成绩单中班级列B:B,按班级汇总表中B2单元格)

根据"订单明细"表中求"统计报告"表中所有订单的总销售额; 求ms xxx在12年的总销售额 求隆华书店在11年第三季度(7.1-9.30)的总销售额 求隆华书店在11年每月平均销售额

=sum(订单明细表中小计列);点击"闪电"图标中"撤销计算列"取消下面单元格的自动计算

=sumfis(订单明细表中小计列,订单明细表中图书名称列,订单明细表中任意含有ms xxx单元格E24,订单明细表中日期列,">=2012-1-1",订单明细表中日期列,"<=2012-12-31")

=sumifs(小计列,书店名称列,任意含隆华书店的单元格,日期列,">=2011-7-1",日期列,"<=2011-9-30")

=sumifs(小计列,书店名称列,任意含隆华书店的单元格,日期列,">=2011-1-1",日期列,"<=2011-12-31")/12

文本函数

考点

left（）、right（）

mid（文本对象，起始位置，所取长度）

text（对象，最终形式）

例题

统计每个活动地点所在的省份或直辖市，填写在地区所对应的单元格中

地区下单元格=left（活动地点下单元格C3,3）【3代表取三个单位长度】

真题5

插入年份列，通过F列中年份信息获取年份填到年份列中

插入年份列，=mid(F4,find（”[“，F4）+1,4)【先找到括号位置，复制[到公式中】意思是 对于F4从中间取,中间的括号开始的下1个位置开始取4位 "查找和选择""定位条件""错误" 删除

真题23

根据身份证号码计算出生日期

子主题 1

真题10

排序

考点

数据排序

排序列

主要关键词、次要关键词

排序依据

数值、单元格颜色、字体颜色

排序次序

升序、降序、自定义序列

注意

不需要选中数据区域，把光标放在数据区域内就可以了

勾选“数据包含标题”

例题

“数据”卡片中“排序”

将订单编号列按照数值升序方式排序，并将重复订单编号标记为紫色，排列在顶端

光标放在订单编号的单元格，在“开始”卡片，“排序和筛选”中选择“升序”； 条件格式：选中所有的订单编号（ctrl+shift+↓），在“开始”卡片“条件格式”中选择“突出显示单元格规则”“重复值”，在“设置为”中选“自定义格式”“颜色”中选择紫色，“确定”； 光标放在订单编号，在“开始”卡片“编辑”中“排序和筛选”中“自定义排序”，“添加条件”在“次要关键字”中选择“订单编号”“排序依据”选择“字体颜色”“紫色”，把次要关键字往上移

真题9

依据自定义序列“研发部、物流部...”排序，名称相同则按平均成绩由高到低的顺序排序

光标放在“部门”“开始”卡片中“排序和筛选”的“自定义排序”，在“主要关键词”选部门，“排序依据”选择数值，“次序”选择“自定义序列”在“输入序列”依次输入研发部、物流部...【每个部门占一行，每个部门后加回车键】“添加” “添加条件”在“次要关键词”选择“平均成绩”在“排序依据”选择“数值”在“次序”选择降序

真题22

子主题 3

从A2单元格依次为员工生成工资条，第一行为空白行、第二行为标题行、第三行为信息行

子主题 1

真题19

筛选与高级筛选

自动筛选（可实现交集）

考点

数据筛选

自动筛选: 数字筛选、文本筛选、日期筛选

本质

只显示符合条件的数据，隐藏不符合条件的数据

与条件格式的区别

条件格式：突出显示符合条件的数据

自动筛选快捷键

ctrl+shift+L

例题

高级筛选（可实现并集）

考点

高级筛选：列表区域、条件区域、复制到

例题

在工作表右侧新建工作表“大额订单”，在新表中使用高级筛选筛选出产品A、B、C的记录

单击右击插入新的工作表，移至右侧，双击重命名大额订单； 复制类型、数量、产品ABC及其对应的数量至新表A1至B4区域，分别在产品A、B、C的数量单元格内输入 >1550、>1900、>1500; 光标放在新表中A6单元格，“数据”“高级筛选”，在“列表区域”选中销售记录表中的全部数据，“条件区域”中选中大额订单中的条件区域A1至B4，勾选“将筛选结果复制到其他位置”，在“复制到”选中A6单元格

真题18

注意

要书写条件区域

数据透视表

例题

根据销售订单表创建数据透视表，以A1为数据透视表为起点，命名为2012年书店销售；设置日期列标签，书店名称为行标签，销量本为求和汇总项，显示每季度的销量情况

将光标放在数据源区域，“插入”卡片“表格”中的“数据透视表”，将日期设置为列标签，书店名称设置为行标签，销量本为求和汇总项【数值下箭头“值字段设置”“计算类型”可以求和、求平均、最大小、乘积等】，双击行标签、列标签可以修改名称 季度：把光标放在x年x月x日的日期单元格上，单价右键“创建组”“起始于”填2012/1/1“终止于”填2012/12/31，在“步长”选择“季度”；在列标签箭头下取消勾选“>2012/12/31”

真题9

创建东、西、南、北区工作表，分别统计各类图书累计销售金额，金额设置带千分位、保留两位小数

光标放在数据区域，“插入”卡片“数据透视表”，图书标签设置为行标签、销售额设置为求和项，改名行标签、求和项，所属区域设置为报表筛选； 选中销售额的数据，单击右键“设置单元格格式”，“分类”中选择数值，“小数位数”为2，勾选“使用千位分隔符”； 分以东西南北区分为4个工作表（可以分别以区域复制成4个工作表或者）：“数据透视表工具”“选项”卡片，“选项”中“数据透视表”的“选项”“显示报表筛选页”；删除总数据透视表

真题13

统计每个城市各月降水量以及在全年中的比重，并为其创建单独报告

二维表转换化为一维表：alt、d、p（按完一个再下一个），勾选“多重合并计算数据区域”“下一步”，“在选定区域”里选中数据区域（主要城市降水量内含城市、各月降水量），“下一步”，右边取消勾选“页1”，把列标签里的“列”拖到行标签中；“数据透视表工具”“设计”卡片中“布局”的“分类汇总”中“不显示分类汇总”，“总计”中“对行列禁用”，“报表布局”“以表格形式显示”“重复所有项目标签” 全部数据复制粘贴到新工作表中（只保留数值），修改列、行、求和项为城市、月份、降水量，删除原数据透视表 在“插入”卡片“数据透视表”，月份设置为行标签，降水量拖两次为求和项；把10、11、12月拖到9月下面，修改标题行；光标放在全年占比下的数据，单击右键，“值显示方式”“总计的百分比”； 将“城市”放在“报表筛选”中，“数据透视表工具”“选项”卡片，“选项”“显示报表筛选页”

真题26

数据有效性

考点

规范表格数据的输入

选中补助金额，在“数据”卡片“数据工具”的“数据有效性”，在“允许”内选择相应的内容。序列：可以自己选择内容；文本长度： 出错警告：停止、警告、信息 输入信息：标题、输入信息

例题

检测准考证号（12位）和面试分数(0-100)的有效性,输入错误时给出提示信息，以红色标出错误

ctrl+Shfit+下全选准考证号，“数据”卡片“数据有效性”，“允许”填“文本长度”，“数据”中输入“等于”，“长度”输入12；全选面试分数，“数据有效性”，“允许”填“整数”“数值”填介于，“最小值”填0，“最大值”填100； 分别在准考证号和面试分数的“数据有效性”“出错警告”“错误信息”输入超出范围请重新输入； 条件格式：选中准考证号，“开始”卡片“条件格式”“新建规则”，“选择规则类型”中选择“使用公式确定设置格式的单元格”，在“为符合此公式的值设置格式”填=len(B4)<>12；在“格式”中颜色设置为标准红色；“面试分数”则输入=or(J4<0,J4>100)

真题24

R3、S2建立数据有效性，分别仅允许在该单元格输入A2:A32的城市名称、 B1:M1的月份名称

选中R3，在“数据”中“数据有效性”在“允许”中选择“序列”，“来源”中引用城市名称下A2到A32的单元格 S2则在“来源”中引用B1:M1的单元格

真题26

套用表格格式

考点

三大分类

浅色、中等深浅、深色

有哪些设置、有什么优势

表格样式选项（显示或不显示个别行、列）；美化表格，自动填充公式，自动更新数据透视表数据

容易出现的问题

不能选中合并后居中单元格

无法直接分类汇总

单击右键，“表格”“转化成区域”再开始分类汇总

例题

通过套用表格的形式将销售记录调整为一致的表格格式

选中除合并后居中的单元格的表格区域，在“开始”卡片“样式”“套用表格样式”中选择格式；可以在左上修改表名称

真题1

条件格式

考点

把符合特定条件的数据设置格式突出显示

突出显示单元格格式（大于、小于、介于、等于、包含、发生日期、重复值、其他规则）、项目选取规则（值最大、小n项，高低于平均值）、数据条（不显示数值）、新建规则（使用公式确定要设置格式的单元格）、公式（引用形式、与rank函数结合）

例题

突出显示单元格规则：语数英不低于110的以一色填充，其他科目高于95以另一颜色标出

ctrl+shift+↓连续选中语数英区域，在“开始”卡片“条件格式”“突出显示单元格规则”“其他规则”，“单元格值”设置为“大于或等于”“110”，“格式”设置颜色 选中其他四科区域，“条件格式”“大于”“95”“设置为”设置颜色

真题2

将月单项开支金额大于1000的数据以不同的字体颜色显示；月总支出额大于月均总支出110%的数据以另一颜色显示

选中各项开销数据区域，“条件格式”“大于”“设置为”调整字体颜色； 选中各月总支出区域，“条件格式”“新建规则”“使用公式确定要设置格式的单元格”在“为符合此公式的值设置格式”填入=11月总支出单元格N3>$N$15*1.1 【前面各月总支出不用加美元符号，月均开销是固定值所以要绝对引用加美元符号】 “格式”中设置颜色

真题11

标出各科第一名、总分前10

选中语文列区域，“条件格式”“项目选取规则”“值最大的10项”把10改为1 “设置为”中选“自定义格式”字体颜色设置为红色，加粗；双击格式刷，刷其他科目，再点格式刷；或者 选中各科成绩“条件格式”“新建格式”“使用公式确定要设置格式的单元格”“为符合此公式的值设置格式”中填=rank(语文成绩单元格C3,语文成绩列C$3:C$46)=1，设置“格式” 选中总分区域，“条件格式”“项目选取规则”“值最大的10 项”“设置为”中选“自定义格式”，选中填充效果

真题10

在N2:N32中计算各城市全年的降水量，对其应用实心填充的数据条件格式，不显示数值本身

选中N2:N32“条件格式”“数据条”“实心填充”，再次选中“数据条”“其他规则”“勾选仅显示数据条”

真题26