2025年excel計算機二級試題及答案一、銷售數(shù)據(jù)整理與基本操作打開考生文件夾下的“2024宏遠科技銷售數(shù)據(jù).xlsx”，按以下要求完成操作：1.將“銷售記錄表”工作表的A1:I1單元格區(qū)域合并后居中，設置字體為“微軟雅黑”，字號14，字體顏色“深藍”；為A2:I23單元格區(qū)域添加所有框線，外框線為“粗實線”，內框線為“細實線”。2.將“銷售日期”列（B列）格式設置為“2024年3月14日”類型；“單價”列（F列）和“成本”列（H列）保留2位小數(shù)，“數(shù)量”列（E列）設置為整數(shù)格式。3.調整“產(chǎn)品名稱”列（C列）列寬為20，“客戶姓名”列（I列）列寬為12；將第2行（標題行）行高設置為25，并填充“淺藍”背景色。4.在J列插入“銷售額”列（J1單元格輸入“銷售額”），K列插入“利潤”列（K1單元格輸入“利潤”）；在L列插入“季度”列（L1單元格輸入“季度”），用于根據(jù)“銷售日期”判斷季度（1-3月為Q1，4-6月為Q2，7-9月為Q3，10-12月為Q4）。答案：1.選中A1:I1，點擊“開始”選項卡→“對齊方式”組→“合并后居中”；選中合并后的單元格，在“字體”組設置字體為“微軟雅黑”，字號14，顏色選擇“深藍”；選中A2:I23，點擊“邊框”下拉按鈕→“所有框線”，再次選中該區(qū)域，點擊“邊框”→“粗匣框線”設置外框。2.選中B列→右鍵“設置單元格格式”→“日期”→選擇“2024年3月14日”；選中F列和H列→“設置單元格格式”→“數(shù)值”→小數(shù)位數(shù)2；選中E列→“設置單元格格式”→“數(shù)值”→小數(shù)位數(shù)0。3.雙擊C列列標右側調整列寬為20（或右鍵“列寬”輸入20）；同理調整I列為12；選中第2行→右鍵“行高”輸入25；選中第2行→“填充顏色”→“淺藍”。4.右鍵點擊E列列標→“插入”（插入J列），J1輸入“銷售額”；右鍵F列→“插入”（插入K列），K1輸入“利潤”；右鍵G列→“插入”（插入L列），L1輸入“季度”。二、公式與函數(shù)應用基于“銷售記錄表”完成以下計算：1.計算“銷售額”（J列）：銷售額=數(shù)量×單價（E列×F列）。2.計算“利潤”（K列）：利潤=銷售額-成本×數(shù)量（J列-H列×E列）。3.使用函數(shù)計算“季度”（L列）：根據(jù)“銷售日期”（B列）的月份判斷，要求使用VLOOKUP或IF嵌套實現(xiàn)。4.在B24單元格輸入“統(tǒng)計日期”，C24輸入“2024/9/30”，D24輸入“累計銷售額”，使用SUMIFS函數(shù)計算截至2024年9月30日（含）所有“華東”地區(qū)的銷售額總和，結果存于E24。5.在A25單元格輸入“最大利潤產(chǎn)品”，B25使用LOOKUP函數(shù)查找利潤最大的產(chǎn)品名稱（利潤在K2:K23，產(chǎn)品名稱在C2:C23）。答案：1.在J2輸入公式“=E2F2”，雙擊填充柄向下填充至J23。2.在K2輸入公式“=J2-H2E2”，雙擊填充柄至K23。3.方法一（VLOOKUP）：在L2輸入公式“=VLOOKUP(MONTH(B2),{1,2,3;"Q1","Q1","Q1";4,5,6;"Q2","Q2","Q2";7,8,9;"Q3","Q3","Q3";10,11,12;"Q4","Q4","Q4"},2,TRUE)”；方法二（IF嵌套）：“=IF(MONTH(B2)<=3,"Q1",IF(MONTH(B2)<=6,"Q2",IF(MONTH(B2)<=9,"Q3","Q4")))”，填充至L23。4.在E24輸入公式“=SUMIFS(J2:J23,B2:B23,"<="&C24,D2:D23,"華東")”。5.在B25輸入公式“=LOOKUP(MAX(K2:K23),K2:K23,C2:C23)”。三、數(shù)據(jù)排序與篩選1.將“銷售記錄表”數(shù)據(jù)按“利潤”降序排序，若利潤相同則按“銷售額”降序排序。2.篩選出“地區(qū)”為“華北”或“華南”、“利潤”大于等于800且“數(shù)量”小于15的記錄。3.使用自動篩選，顯示“季度”為“Q3”且“產(chǎn)品名稱”包含“筆記本”的記錄（不區(qū)分大小寫）。4.對篩選結果（完成第3步后）進行復制，粘貼到“篩選結果”工作表（需新建）的A1起始位置，保留源格式。答案：1.選中數(shù)據(jù)區(qū)域A2:I23（含標題行），點擊“數(shù)據(jù)”→“排序”→主要關鍵字“利潤”→降序；點擊“添加條件”→次要關鍵字“銷售額”→降序→確定。2.點擊“數(shù)據(jù)”→“篩選”，點擊“地區(qū)”篩選按鈕→勾選“華北”“華南”；點擊“利潤”篩選→“數(shù)字篩選”→“大于或等于”→輸入800；點擊“數(shù)量”篩選→“數(shù)字篩選”→“小于”→輸入15→確定。3.點擊“季度”篩選→勾選“Q3”；點擊“產(chǎn)品名稱”篩選→“文本篩選”→“包含”→輸入“筆記本”→確定（若不區(qū)分大小寫需確保原數(shù)據(jù)無大寫，或使用通配符）。4.選中篩選后的可見數(shù)據(jù)（A2:I23中顯示的行），按Ctrl+C復制；新建“篩選結果”工作表，點擊A1→Ctrl+V→選擇“保留源格式”粘貼。四、數(shù)據(jù)透視表與圖表1.在“分析表”工作表中，以“銷售記錄表”為數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表，位置為A1。2.數(shù)據(jù)透視表要求：行標簽為“地區(qū)”“產(chǎn)品類別”（注：產(chǎn)品類別需從“產(chǎn)品名稱”列提取，規(guī)則：產(chǎn)品名稱中“筆記本”對應“電腦”，“手機”對應“通訊”，“平板”對應“平板”，其他為“其他”）；值字段為“銷售額”（求和）和“利潤”（求平均值）。3.為數(shù)據(jù)透視表添加切片器，關聯(lián)“季度”字段，切片器樣式為“切片器樣式中等深1”。4.在數(shù)據(jù)透視表右側（F1起始）創(chuàng)建簇狀柱形圖，顯示各地區(qū)各產(chǎn)品類別的平均利潤，圖表標題為“地區(qū)-產(chǎn)品類別平均利潤對比”，設置坐標軸標題（橫軸“地區(qū)-類別”，縱軸“平均利潤（元）”）。答案：1.切換至“分析表”，點擊“插入”→“數(shù)據(jù)透視表”→選擇“銷售記錄表!A1:I23”為數(shù)據(jù)源→選擇放置位置“分析表!A1”→確定。2.首先在“銷售記錄表”M1輸入“產(chǎn)品類別”，M2輸入公式“=IF(ISNUMBER(FIND("筆記本",C2)),"電腦",IF(ISNUMBER(FIND("手機",C2)),"通訊",IF(ISNUMBER(FIND("平板",C2)),"平板","其他")))”，填充至M23；返回數(shù)據(jù)透視表字段列表，將“地區(qū)”“產(chǎn)品類別（M列）”拖入行區(qū)域；將“銷售額”拖入值區(qū)域（默認求和），“利潤”拖入值區(qū)域→右鍵值字段→“值字段設置”→選擇“平均值”。3.點擊數(shù)據(jù)透視表→“分析”→“插入切片器”→勾選“季度”→確定；選中切片器→“切片器工具-選項”→“切片器樣式”→選擇“中等深1”。4.選中數(shù)據(jù)透視表中的“地區(qū)”“產(chǎn)品類別”“利潤”（平均值）字段數(shù)據(jù)→點擊“插入”→“柱形圖”→“簇狀柱形圖”；將圖表拖至F1位置；雙擊圖表標題→輸入“地區(qū)-產(chǎn)品類別平均利潤對比”；雙擊橫軸→“坐標軸選項”→“標題”→“坐標軸下方標題”→輸入“地區(qū)-類別”；雙擊縱軸→輸入“平均利潤（元）”。五、高級功能與綜合應用1.為“銷售記錄表”的“數(shù)量”列（E列）添加數(shù)據(jù)驗證：允許整數(shù)，介于1到100之間，輸入提示信息“請輸入1-100的整數(shù)”，錯誤提示信息“數(shù)量必須在1-100之間”。2.使用條件格式：對“利潤”列（K列）設置色階，綠色（最高值）、黃色（中間值）、紅色（最低值）；對“銷售額”列（J列）中大于20000的單元格添加“星形-8角”圖標集。3.將“銷售日期”列（B列）使用分列功能，按“年-月-日”拆分為“年份”（N列）、“月份”（O列）、“日期”（P列）三列，格式均為數(shù)值型。4.錄制宏“調整格式”，實現(xiàn)以下操作：選中A1:I23→設置字體為“宋體”，字號10；自動調整所有列寬；為A1:I1添加“橙色”背景色。將宏保存在當前工作簿中，快捷鍵設置為Ctrl+Shift+Z。答案：1.選中E2:E23→“數(shù)據(jù)”→“數(shù)據(jù)驗證”→“設置”→允許“整數(shù)”→最小值1→最大值100；“輸入信息”→標題“數(shù)量提示”→輸入“請輸入1-100的整數(shù)”；“出錯警告”→標題“錯誤”→輸入“數(shù)量必須在1-100之間”→確定。2.選中K2:K23→“開始”→“條件格式”→“色階”→選擇“綠-黃-紅”三色階；選中J2:J23→“條件格式”→“圖標集”→“星形-8角”→右鍵圖標集規(guī)則→“編輯規(guī)則”→“類型”選擇“數(shù)字”→“值”輸入20000→確定（確保僅大于20000顯示圖標）。3.選中B2:B23→“數(shù)據(jù)”→“分列”→“分隔符號”→下一步→勾選“其他”→輸入“年”“月”（需分兩次分列：第一次按“年”分列，提取年份和剩余部分；第二次按“月”分列剩余部分為月份和日期）；完成后N列（年份）、O列（月份）、P列（日期）格式設置為數(shù)值（選中列→“設置單元格格式”→“數(shù)值”）。4.點擊“開發(fā)工具”→“錄制宏”→宏名“調整格式”→保存在“當前工作簿”→快捷鍵“Ctrl+Shift+Z”→確定；依次操作：選中A1:I23→“字體”設置為“宋體”，字號10；點擊“開始”→“格式”→“自動調整列寬”；選中A1:I1→“填充顏色”→“橙色”；點擊“開發(fā)工具”→“停止錄制”。六、函數(shù)與圖表的綜合拓展1.在“銷售記錄表”A26輸入“客戶年齡”，B26使用DATEDIF函數(shù)計算“客戶生日”（假設新增列N為客戶生日，格式為“1990/5/1”）對應的當前年齡（截至2024年12月31日），要求忽略未到生日的情況（如1990/12/31出生，2024/12/30時年齡為33歲）。2.創(chuàng)建動態(tài)圖表：在“圖表頁”工作表中，A1輸入“選擇季度”，B1插入下拉列表（數(shù)據(jù)源為L2:L23中的唯一季度值）；基于“銷售記錄表”數(shù)據(jù)，當選擇季度時，圖表自動顯示該季度各地區(qū)的銷售額對比（簇狀柱形圖）。答案：1.在B26輸入公式“=DATEDIF(N2,"2024-12-31","Y")”（若N列為客戶生日，N2為具體日期），該函數(shù)自動計算截至2024年12月31日的整年數(shù)，未到生日時不增加年齡。2.首先提取唯一季度值：在“銷售記錄表”R1輸入“唯一季度”，R2輸入公式“=UNIQUE(L2:L23)”；切換至“圖表頁”，選中B1→“數(shù)據(jù)”→“數(shù)據(jù)驗證”→“序列”→來源“=銷售