2025年計(jì)算機(jī)二級(jí)考試電子表格操作考試題及答案一、基礎(chǔ)數(shù)據(jù)整理打開(kāi)考生文件夾下的“銷(xiāo)售數(shù)據(jù).xlsx”工作簿，其中“原始數(shù)據(jù)”工作表包含某公司2024年第四季度銷(xiāo)售記錄，字段包括：序號(hào)（A列）、銷(xiāo)售日期（B列）、部門(mén)（C列）、產(chǎn)品ID（D列）、產(chǎn)品類(lèi)別（E列）、銷(xiāo)售人員（F列）、銷(xiāo)售額（G列）、成本（H列）。請(qǐng)按以下要求完成操作：1.在B列右側(cè)插入一列（新B列），列標(biāo)題為“月份”，利用函數(shù)提取“銷(xiāo)售日期”中的月份（如“2024-10-05”提取為“10”），結(jié)果為數(shù)值型。2.將A列“序號(hào)”設(shè)置為文本格式（以避免首位0丟失），調(diào)整C列“部門(mén)”列寬為12，G列“銷(xiāo)售額”和H列“成本”列寬為15。3.將A1:I1單元格區(qū)域合并后居中，設(shè)置字體為“微軟雅黑”、字號(hào)14、加粗；其余標(biāo)題行（A2:I2）設(shè)置為淺藍(lán)填充色（主題顏色第5行第1列）、居中對(duì)齊。答案：1.選中B列（原銷(xiāo)售日期列），右鍵選擇“插入”；在B2單元格輸入公式“=MONTH(A2)”（注：插入列后原B列變?yōu)镃列，此處假設(shè)原數(shù)據(jù)從A2開(kāi)始），向下填充至數(shù)據(jù)末尾；選中B列，右鍵“設(shè)置單元格格式”-“數(shù)字”-“數(shù)值”-“小數(shù)位數(shù)0”。2.選中A列，右鍵“設(shè)置單元格格式”-“數(shù)字”-“文本”；選中C列，右鍵“列寬”-輸入“12”；選中G、H列，右鍵“列寬”-輸入“15”。3.選中A1:I1，點(diǎn)擊“開(kāi)始”-“合并后居中”；設(shè)置字體“微軟雅黑”、字號(hào)14、加粗；選中A2:I2，點(diǎn)擊“填充顏色”-選擇主題顏色第5行第1列（淺藍(lán)），點(diǎn)擊“居中”對(duì)齊。二、公式與函數(shù)應(yīng)用1.在I列插入“利潤(rùn)”列（標(biāo)題為“利潤(rùn)”），計(jì)算每筆銷(xiāo)售的利潤(rùn)（利潤(rùn)=銷(xiāo)售額-成本），結(jié)果保留2位小數(shù)。2.在J列插入“利潤(rùn)率”列（標(biāo)題為“利潤(rùn)率”），計(jì)算利潤(rùn)率（利潤(rùn)率=利潤(rùn)/銷(xiāo)售額），結(jié)果以百分比格式顯示（保留2位小數(shù)），若銷(xiāo)售額為0則顯示“無(wú)數(shù)據(jù)”（需處理錯(cuò)誤值）。3.利用VLOOKUP函數(shù)，在K列提供“產(chǎn)品名稱(chēng)”（標(biāo)題為“產(chǎn)品名稱(chēng)”），產(chǎn)品ID與名稱(chēng)對(duì)應(yīng)關(guān)系存儲(chǔ)在“產(chǎn)品對(duì)照表”工作表的A1:B10區(qū)域（A列為產(chǎn)品ID，B列為產(chǎn)品名稱(chēng)）。4.在L列計(jì)算“季度累計(jì)銷(xiāo)售額”，要求按“銷(xiāo)售人員”統(tǒng)計(jì)其10月至12月的累計(jì)銷(xiāo)售額（例如：張三10月所有銷(xiāo)售記錄的銷(xiāo)售額之和，加上11月、12月的銷(xiāo)售額之和）。答案：1.在I2單元格輸入公式“=G2-H2”，向下填充；選中I列，右鍵“設(shè)置單元格格式”-“數(shù)值”-“小數(shù)位數(shù)2”。2.在J2單元格輸入公式“=IF(G2=0,"無(wú)數(shù)據(jù)",I2/G2)”，向下填充；選中J列，右鍵“設(shè)置單元格格式”-“百分比”-“小數(shù)位數(shù)2”。3.在K2單元格輸入公式“=VLOOKUP(D2,產(chǎn)品對(duì)照表!$A$1:$B$10,2,0)”，向下填充（注意鎖定區(qū)域?yàn)榻^對(duì)引用）。4.在L2單元格輸入公式“=SUMIFS(G:G,F:F,F2)”，向下填充（SUMIFS函數(shù)按F列銷(xiāo)售人員篩選，求和G列銷(xiāo)售額）。三、數(shù)據(jù)排序與篩選1.對(duì)“原始數(shù)據(jù)”工作表，按以下優(yōu)先級(jí)排序：主要關(guān)鍵字“部門(mén)”（升序），次要關(guān)鍵字“利潤(rùn)率”（降序），第三關(guān)鍵字“銷(xiāo)售日期”（升序）。2.篩選出“部門(mén)”為“家電部”或“數(shù)碼部”、“月份”為11月且“利潤(rùn)率”高于18%的記錄，隱藏其他行。3.對(duì)篩選結(jié)果中的“銷(xiāo)售額”列添加數(shù)據(jù)條條件格式（顏色為橙色，最小值和最大值類(lèi)型均為“百分比”，值為0和100）。答案：1.選中數(shù)據(jù)區(qū)域（A2:L1001，假設(shè)共1000條記錄），點(diǎn)擊“數(shù)據(jù)”-“排序”；主要關(guān)鍵字選“部門(mén)”、升序；添加條件，次要關(guān)鍵字選“利潤(rùn)率”、降序；添加條件，第三關(guān)鍵字選“銷(xiāo)售日期”、升序，點(diǎn)擊“確定”。2.點(diǎn)擊“數(shù)據(jù)”-“篩選”；點(diǎn)擊“部門(mén)”篩選按鈕，勾選“家電部”和“數(shù)碼部”；點(diǎn)擊“月份”篩選按鈕，勾選“11”；點(diǎn)擊“利潤(rùn)率”篩選按鈕，選擇“數(shù)字篩選”-“大于”-輸入“18%”，點(diǎn)擊“確定”。3.選中篩選后的“銷(xiāo)售額”列（如G3:G500），點(diǎn)擊“開(kāi)始”-“條件格式”-“數(shù)據(jù)條”-“其他規(guī)則”；在“格式樣式”中選擇“數(shù)據(jù)條”，顏色選橙色；最小值類(lèi)型選“百分比”、值0；最大值類(lèi)型選“百分比”、值100，點(diǎn)擊“確定”。四、數(shù)據(jù)透視表與圖表1.在“分析”工作表中，基于“原始數(shù)據(jù)”工作表創(chuàng)建數(shù)據(jù)透視表，要求：行標(biāo)簽為“部門(mén)”和“產(chǎn)品類(lèi)別”，列標(biāo)簽為“月份”，值字段為“銷(xiāo)售額”（求和）和“利潤(rùn)率”（平均值），顯示行總計(jì)和列總計(jì)。2.在數(shù)據(jù)透視表下方插入一張簇狀柱形圖，數(shù)據(jù)源為數(shù)據(jù)透視表中“家電部”各產(chǎn)品類(lèi)別10-12月的銷(xiāo)售額數(shù)據(jù)（需篩選家電部），圖表標(biāo)題為“家電部各產(chǎn)品類(lèi)別月度銷(xiāo)售額對(duì)比”，設(shè)置分類(lèi)軸標(biāo)題為“產(chǎn)品類(lèi)別”，數(shù)值軸標(biāo)題為“銷(xiāo)售額（萬(wàn)元）”，數(shù)據(jù)標(biāo)簽顯示值（保留2位小數(shù)）。答案：1.點(diǎn)擊“插入”-“數(shù)據(jù)透視表”，選擇數(shù)據(jù)源為“原始數(shù)據(jù)!$A$1:$L$1001”，放置位置為“分析!A1”；在“數(shù)據(jù)透視表字段”中，將“部門(mén)”和“產(chǎn)品類(lèi)別”拖至“行”區(qū)域，“月份”拖至“列”區(qū)域；將“銷(xiāo)售額”拖至“值”區(qū)域（默認(rèn)求和），“利潤(rùn)率”拖至“值”區(qū)域并右鍵“值字段設(shè)置”-“平均值”；勾選“數(shù)據(jù)透視表工具-設(shè)計(jì)”-“總計(jì)”-“行和列總計(jì)”。2.點(diǎn)擊數(shù)據(jù)透視表中的“部門(mén)”篩選按鈕，選擇“家電部”；選中篩選后的行標(biāo)簽（產(chǎn)品類(lèi)別）和列標(biāo)簽（10月、11月、12月）及對(duì)應(yīng)銷(xiāo)售額數(shù)據(jù)（如B3:D5），點(diǎn)擊“插入”-“柱形圖”-“簇狀柱形圖”；右鍵圖表標(biāo)題，輸入“家電部各產(chǎn)品類(lèi)別月度銷(xiāo)售額對(duì)比”；右鍵分類(lèi)軸（X軸），選擇“設(shè)置坐標(biāo)軸格式”-“坐標(biāo)軸選項(xiàng)”-“標(biāo)題”-“坐標(biāo)軸下方標(biāo)題”，輸入“產(chǎn)品類(lèi)別”；右鍵數(shù)值軸（Y軸），設(shè)置標(biāo)題為“銷(xiāo)售額（萬(wàn)元）”；右鍵圖表數(shù)據(jù)系列，選擇“添加數(shù)據(jù)標(biāo)簽”，右鍵數(shù)據(jù)標(biāo)簽-“設(shè)置數(shù)據(jù)標(biāo)簽格式”-“數(shù)字”-“數(shù)值”-“小數(shù)位數(shù)2”。五、高級(jí)操作與優(yōu)化1.在“原始數(shù)據(jù)”工作表F列“銷(xiāo)售人員”中，設(shè)置數(shù)據(jù)有效性：允許“序列”，來(lái)源為“張三,李四,王五,趙六”（逗號(hào)分隔），輸入信息為“請(qǐng)從下拉列表中選擇銷(xiāo)售人員”，出錯(cuò)警告標(biāo)題為“錯(cuò)誤”，信息為“必須選擇指定銷(xiāo)售人員”。2.使用INDEX+MATCH組合函數(shù)，在M列“最高銷(xiāo)售額”中，查找每個(gè)銷(xiāo)售人員的最高銷(xiāo)售額（例如：張三的所有銷(xiāo)售記錄中銷(xiāo)售額最大值）。3.設(shè)置“原始數(shù)據(jù)”工作表頁(yè)面布局：紙張大小為A4，上下邊距2厘米，左右邊距1.5厘米，水平居中對(duì)齊；頁(yè)眉內(nèi)容為“2024年第四季度銷(xiāo)售數(shù)據(jù)統(tǒng)計(jì)”（居中），頁(yè)腳左側(cè)插入“制表人：財(cái)務(wù)部”，右側(cè)插入頁(yè)碼（格式為“第X頁(yè)，共Y頁(yè)”）。答案：1.選中F列（F2:F1001），點(diǎn)擊“數(shù)據(jù)”-“數(shù)據(jù)有效性”-“數(shù)據(jù)驗(yàn)證”；“設(shè)置”選項(xiàng)卡中，允許選“序列”，來(lái)源輸入“張三,李四,王五,趙六”；“輸入信息”選項(xiàng)卡中，標(biāo)題輸入“提示”，輸入信息輸入“請(qǐng)從下拉列表中選擇銷(xiāo)售人員”；“出錯(cuò)警告”選項(xiàng)卡中，樣式選“停止”，標(biāo)題輸入“錯(cuò)誤”，信息輸入“必須選擇指定銷(xiāo)售人員”，點(diǎn)擊“確定”。2.在M2單元格輸入公式“=INDEX(G:G,MATCH(F2,F:F,0)+MATCH(MAX(IF(F:F=F2,G:G)),G:G,0)-1)”（注：需按Ctrl+Shift+Enter輸入數(shù)組公式），或更簡(jiǎn)潔的“=MAXIFS(G:G,F:F,F2)”（若Excel版本支持MAXIFS），向下填充。3.點(diǎn)擊“頁(yè)面布局”-“紙張大小”-“A4”；點(diǎn)擊“頁(yè)邊距”-“自定義邊距”，設(shè)置上、下邊距2厘米，左、右邊距1.5厘米；勾選“水平居中”；點(diǎn)擊“頁(yè)眉/頁(yè)腳”-“自定義頁(yè)眉”，中間位置輸入“2024年第四季度銷(xiāo)售數(shù)據(jù)統(tǒng)計(jì)”；點(diǎn)擊“自定義頁(yè)腳”，左側(cè)輸入“制表人：財(cái)務(wù)部”，右側(cè)輸入“&P/&N”（表示第X頁(yè)，共Y頁(yè)）。六