计算机等级考试二级excel试题及答案.docxVIP

计算机等级考试二级excel试题及答案

一、基础操作题

现有“2023年某科技公司销售数据表”（以下简称“销售表”），包含字段：区域（文本，如“华北”“华东”）、产品ID（文本，如“P001”“P002”）、产品名称（文本，如“智能手环”“无线耳机”）、销售日期（日期格式，如2023/3/15）、销售数量（数值）、单价（数值，保留2位小数）、销售额（数值，=销售数量×单价）、成本（数值，保留2位小数）、利润（数值，=销售额成本）、客户类型（文本，如“企业客户”“个人客户”）。请按以下要求完成操作：

1.在“销售表”中，将A1:J1单元格区域合并并居中，设置字体为“微软雅黑”，字号14，加粗，背景色为“橙色，个性色2，淡色80%”。

2.将“销售日期”列（D列）格式设置为“yyyy年m月d日”（如2023年3月15日）。

3.调整列宽：A列（区域）列宽10，B列（产品ID）列宽8，C列（产品名称）列宽15，J列（客户类型）列宽12，其余列使用“自动调整列宽”。

4.为数据区域（A2:J1001）添加所有框线，外框线为“粗实线”，内框线为“细实线”。

答案：

1.选中A1:J1，点击“开始”选项卡→“对齐方式”组→“合并后居中”；选中合并后的单元格，在“字体”组设置字体为“微软雅黑”，字号14，加粗；点击“填充颜色”下拉箭头，选择“主题颜色”→“橙色，个性色2，淡色80%”。

2.选中D列（销售日期），右键选择“设置单元格格式”→“日期”→类型选择“yyyy年m月d日”→确定。

3.选中A列，右键“列宽”输入10；B列列宽输入8；C列列宽输入15；J列列宽输入12；选中DI列，右键“列宽”→“自动调整列宽”。

4.选中A2:J1001，点击“开始”选项卡→“字体”组→“边框”下拉箭头→“所有框线”；再次点击“边框”下拉箭头→“粗外侧框线”。

二、公式与函数应用题

1.在“销售表”K列（客户等级）中，根据以下规则计算客户等级：若客户为“企业客户”且销售额≥8000元，标记为“VIP”；若客户为“企业客户”但销售额8000元，或客户为“个人客户”且销售额≥5000元，标记为“普通”；其余标记为“潜在”。

2.在“产品信息表”（另存于同一工作簿，包含字段：产品ID、产品名称、库存数量）中，使用VLOOKUP函数在“销售表”C列（产品名称）右侧插入“库存数量”列（L列），要求精确匹配。

3.计算“销售表”M列（利润率），公式为“利润/销售额”，结果保留2位小数并以百分比形式显示（如15.20%）。

答案：

1.在K2单元格输入公式：

=IF(AND(J2=企业客户,H2=8000),VIP,IF(OR(AND(J2=企业客户,H28000),AND(J2=个人客户,H2=5000)),普通,潜在))

向下填充至K1001。

2.右键点击C列列标→“插入”→在C列插入新列（原C列变为D列，新列为C列）；在C2单元格输入公式：

=VLOOKUP(B2,产品信息表!A:C,3,0)

向下填充至C1001；将C列列标题修改为“库存数量”。

3.在M2单元格输入公式：=I2/H2，按回车后右键单元格→“设置单元格格式”→“百分比”→小数位数2→确定；向下填充至M1001。

三、数据处理与分析题

1.在“销售表”中，使用高级筛选功能，筛选出满足以下条件的记录：区域为“华东”或“华南”，产品名称包含“耳机”（如“无线耳机”“降噪耳机”），且利润2000元。要求将结果复制到以A1003单元格为起始位置的区域。

2.以“销售表”数据为基础，创建数据透视表（放置于“分析表”工作表A1单元格），要求：行标签为“区域”，列标签为“客户类型”，值字段为“销售额”（求和）和“利润”（平均值），并在数据透视表中显示“销售额”占总计的百分比（值显示方式为“父行汇总的百分比”）。

3.使用条件格式，将“销售表”H列（销售额）中高于本列平均值的单元格填充为“浅红填充色深红色文本”，低于平均值的单元格填充为“浅绿填充色深绿色文本”。

答案：

1.步骤：

①在A1002单元格输入“区域”，B1002输入“产品名称”，C1002输入“利润”；

②在A1003输入“华东”，B1003输入“耳机”，C1003输入“2000”；

③在A1004输入“华南”（与A1003同一列，形成“或”条件）；

④选中数据区域A1:J1001，点击“数据”选项卡→“排序和筛选”组→“高级”；

⑤在“高级筛选”对话框中，“列表区域”选择A1:J1001，“条件区域”选择A1002:C1004，“复制到”选择A1003（起始位置）