2025年excel考试综合题及答案

2025年excel考试综合题及答案一、操作要求请根据以下业务背景及数据，在Excel中完成相关操作（所有操作均在“销售数据”工作表中进行，工作簿另存为“2024年销售数据分析.xlsx”）：业务背景：某商贸公司2024年1-12月销售数据存储于“销售数据”工作表，包含字段：区域（华北/华东/华南/华西）、产品类别（日用品/家电/服饰）、产品名称、销售日期（格式为“2024-01-01”）、销售额（单位：元）、成本（单位：元）、销售人员、客户类型（新客户/老客户）。数据范围为A2:J1001（A1:J1为标题行）。任务1：数据预处理（20分）1.将A列“区域”列宽设置为10，H列“销售人员”列宽设置为12；将所有数值型数据（销售额、成本）设置为“会计专用”格式，保留2位小数。2.在K列插入“毛利”列（K1单元格输入“毛利”），计算每笔销售的毛利（毛利=销售额-成本）。3.在L列插入“销售周期”列（L1单元格输入“销售周期”），计算每笔销售从1月1日到销售日期的天数（例如：销售日期为2024-01-15，则销售周期为14天）。4.在M列插入“季度”列（M1单元格输入“季度”），根据销售日期判断季度（1-3月为“Q1”，4-6月为“Q2”，7-9月为“Q3”，10-12月为“Q4”）。5.设置“客户类型”列（I列）数据有效性：仅允许输入“新客户”或“老客户”，输入非法值时提示“请输入‘新客户’或‘老客户’”。任务2：函数与公式应用（30分）1.在B1003单元格计算“日用品”类别中，华东区域、老客户的总销售额（要求使用SUMIFS函数）。2.在B1004单元格计算“家电”类别中，销售额最高的3笔订单的销售额之和（要求使用LARGE函数）。3.在B1005单元格计算所有订单中，销售周期超过180天且毛利大于500元的订单数量（要求使用COUNTIFS函数）。4.在B1006单元格显示“2024年总毛利”文本及具体数值（例如：“2024年总毛利：123456.78元”），数值部分为K列毛利总和（要求使用TEXT函数与SUM函数嵌套）。5.在B1007单元格查找“产品名称”为“智能电饭煲”的首笔订单对应的销售人员姓名（要求使用INDEX+MATCH组合函数，且MATCH函数匹配类型为精确匹配）。任务3：图表制作（20分）根据A2:M1001区域中“区域”“产品类别”“销售额”数据，制作如下图表：1.插入一张簇状柱形图，横坐标为“区域”，图例项为“产品类别”，数据系列值为“销售额”总和。2.设置图表标题为“2024年各区域不同产品类别销售额对比”，字体12号、加粗；横坐标标题为“区域”，纵坐标标题为“销售额（元）”，字体10号。3.将图表放置在“图表分析”工作表（需新建）中，调整图表大小至宽度20厘米、高度12厘米。4.为图表添加数据标签，显示值且保留0位小数；隐藏图例。任务4：数据透视表与高级分析（20分）1.以A2:J1001区域为数据源，在“透视分析”工作表（需新建）中创建数据透视表。2.行标签设置为“区域”+“产品类别”（嵌套显示），列标签设置为“季度”（M列），值字段为“销售额”（求和）与“毛利”（求平均）。3.添加“客户类型”切片器，关联该数据透视表；添加“销售人员”筛选器，放置在数据透视表上方。4.在数据透视表中插入计算字段“毛利率”（毛利率=毛利/销售额），保留2位小数，以百分比格式显示。任务5：高级操作（10分）1.设置“销售数据”工作表保护：允许用户编辑区域为“K:L”列（毛利、销售周期），其余区域禁止编辑，密码为“123456”（提示：使用“允许用户编辑区域”功能）。2.录制宏“自动填充日期”：选中A2:A1001区域，将文本格式的销售日期（如“2024年1月1日”）批量转换为“2024-01-01”格式（假设原始数据存在此问题），并将宏保存在当前工作簿中，快捷键设置为“Ctrl+Shift+D”。答案及操作步骤任务1答案1.选中A列→右键“列宽”→输入10；选中H列→列宽输入12。选中F列（销售额）、G列（成本）→右键“设置单元格格式”→分类选择“会计专用”→小数位数2→确定。2.在K2单元格输入公式“=F2-G2”→双击填充柄至K1001。3.在L2单元格输入公式“=D2-DATE(2024,1,1)”→设置单元格格式为“常规”（自动显示天数）→双击填充柄至L1001。4.在M2单元格输入公式“=TEXT(D2,"Q0")”→双击填充柄至M1001（或使用IF函数：=IF(MONTH(D2)<=3,"Q1",IF(MONTH(D2)<=6,"Q2",IF(MONTH(D2)<=9,"Q3","Q4")))）。5.选中I2:I1001→数据→数据验证→设置→允许“序列”→来源输入“新客户,老客户”（逗号英文）→出错警告→标题“输入错误”→错误信息“请输入‘新客户’或‘老客户’”→确定。任务2答案1.B1003单元格公式：=SUMIFS(F:F,C:C,"日用品",A:A,"华东",I:I,"老客户")。2.B1004单元格公式：=SUM(LARGE(IF(C:C="家电",F:F),{1,2,3}))→按Ctrl+Shift+Enter输入（数组公式）。3.B1005单元格公式：=COUNTIFS(L:L,">180",K:K,">500")。4.B1006单元格公式：="2024年总毛利："&TEXT(SUM(K:K),"0.00元")。5.B1007单元格公式：=INDEX(H:H,MATCH("智能电饭煲",D:D,0))（假设产品名称在D列，若实际列号不同需调整）。任务3答案1.选中A1:C1001（区域、产品类别、销售额）→插入→图表→簇状柱形图。2.点击图表标题→输入“2024年各区域不同产品类别销售额对比”→设置字体12号加粗；右键横坐标→设置坐标轴标题→输入“区域”；右键纵坐标→设置坐标轴标题→输入“销售额（元）”→字体10号。3.右键图表→移动图表→选择“图表分析”工作表→确定；拖动图表边缘调整宽度20cm、高度12cm（或在“大小”选项卡中设置精确数值）。4.点击图表→设计→添加图表元素→数据标签→居中；右键图例→删除。任务4答案1.选中A1:J1001→插入→数据透视表→选择放置位置为“透视分析”工作表A1单元格→确定。2.字段列表中拖动“区域”“产品类别”到行区域（先拖“区域”，再拖“产品类别”至“区域”下方实现嵌套）；拖动“季度”（M列）到列区域；拖动“销售额”到值区域（默认求和），拖动“毛利”到值区域→右键毛利值字段→值字段设置→选择“平均值”。3.数据透视表工具→分析→插入切片器→勾选“客户类型”→确定；在数据透视表字段列表中拖动“销售人员”到筛选器区域→放置在数据透视表上方。4.数据透视表工具→分析→字段、项目和集→计算字段→名称“毛利率”→公式输入“=毛利/销售额”→确定→右键毛利率值字段→设置单元格格式→百分比→小数位数2→确定。任务5答案1.审阅→允许用户编辑区域→新建→标题“可编辑区域”→引用单元格“$K$2:$L$1001”→确定→保护工作表→输入密码“123456”→勾选“允许此工作表的所有用户进行：选定锁定单元格”→确定（重复输入密码）。2.开发工具→录制宏→宏名“自动填充日期”→快捷键“Ctrl+Shift+D”→保存在“当前工作簿”→确定