Excel 办公技能测试题及答案.docxVIP

Excel办公技能测试题及答案

一、基础操作题（每题10分，共30分）

现有一份“员工信息表”，A列是员工姓名（存在重复值），B列是部门，C列是入职日期。请写出两种快速删除A列重复姓名所在行的方法（保留首次出现的记录）。

若需将“入职日期”列（格式为“2023/05/12”）批量改为“2023年05月12日”的格式，且要求该列仍为日期类型（而非文本），请简述操作步骤。

如何快速将“员工信息表”中所有低于3000元的薪资（D列）单元格填充为红色背景，高于5000元的填充为绿色背景？

二、函数应用题（每题15分，共45分）

已知“销售数据表”中，A列是产品名称，B列是销售数量，C列是单价（部分单元格为空，代表赠品，单价按0计算）。请写出计算每笔销售金额（D列）的函数公式（销售金额=销售数量×单价），要求自动处理C列空值。

现有“考勤表”，A列是员工姓名，B列至AF列是5月份每日考勤记录（“√”代表出勤，“×”代表缺勤，“调”代表调休）。请写出统计每位员工5月份出勤天数的函数公式（仅“√”计为出勤）。

某公司有“客户信息表”（Sheet1）和“订单表”（Sheet2），Sheet1中A列是客户ID，B列是客户姓名；Sheet2中A列是订单号，B列是客户ID，C列是订单金额。请写出在Sheet2的D列自动显示对应客户姓名的函数公式，要求若客户ID在Sheet1中不存在，显示“未知客户”。

三、数据处理题（25分）

现有“月度销售数据”表，包含列：地区（A列）、产品类别（B列）、销售额（C列）、销售日期（D列）。请完成以下操作，简述关键步骤：

按“地区”和“产品类别”分组，计算每组的总销售额；

筛选出2023年第二季度（4-6月）销售额大于10万元的记录；

制作一个包含各地区总销售额的柱状图，要求图表标题为“2023年各地区总销售额”，X轴为地区名称，Y轴为销售额（单位：万元）。

参考答案

一、基础操作题

方法一：选中A列→数据选项卡→删除重复项→仅勾选“姓名”列→确定（保留首次出现记录）；

方法二：选中A列→条件格式→突出显示单元格规则→重复值→标记重复项→筛选出标记的重复行→删除。

选中C列（入职日期列）→右键→设置单元格格式→日期→选择“2023年03月14日”类型→确定（此时仍为日期类型，可参与日期计算）。

选中D列（薪资列）→开始选项卡→条件格式→新建规则→使用公式确定要设置格式的单元格：

公式1：=D13000→格式→填充→红色→确定；

公式2：=D15000→格式→填充→绿色→确定。

二、函数应用题

D列公式：=B2*IF(C2=,0,C2)（下拉填充，IF函数判断C列为空时按0计算，否则按实际单价相乘）。

出勤天数公式：=COUNTIF(B2:AF2,√)（COUNTIF函数统计B列至AF列中“√”的个数）。

D列公式：=IFERROR(VLOOKUP(B2,Sheet1!A:B,2,FALSE),未知客户)（VLOOKUP匹配客户ID对应的姓名，IFERROR处理匹配失败的情况，显示“未知客户”）。

三、数据处理题

分组计算总销售额：选中数据区域（A1:Dn）→插入选项卡→数据透视表→行字段选“地区”和“产品类别”，值字段选“销售额”（值汇总方式设为“求和”）→确定。

筛选第二季度销售额10万记录：

选中D列（销售日期）→数据选项卡→筛选→点击日期列筛选按钮→日期筛选→期间→2023年第二季度；

选中C列（销售额）→筛选→数字筛选→大于→输入100000→确定。

制作柱状图：

选中数据透视表中“地区”列和“总销售额”列→插入选项卡→柱形图→选择“簇状柱形图”；

点击图表标题，修改为“2023年各地区总销售额”；

右键点击Y轴→设置坐标轴格式→坐标轴选项→单位→主要单位设为“10000”（即1万元）→确定。