2025年全國(guó)計(jì)算機(jī)等級(jí)考試二級(jí)練習(xí)題測(cè)試(答案)三、電子表格題（一）基礎(chǔ)數(shù)據(jù)整理與格式設(shè)置打開(kāi)考生文件夾下的“銷售數(shù)據(jù).xlsx”工作簿，其中包含“原始數(shù)據(jù)”“產(chǎn)品信息”“區(qū)域目標(biāo)”3張工作表。要求完成以下操作：1.在“原始數(shù)據(jù)”工作表的A列前插入一列，列標(biāo)題為“訂單ID”，并按“D列（客戶ID）+E列（日期，格式為YYYYMMDD）+流水號(hào)”規(guī)則提供唯一訂單ID。其中流水號(hào)為當(dāng)日同客戶的第N筆訂單，例如客戶ID為C001的客戶20240305有3筆訂單，則訂單ID分別為C00120240305001、C00120240305002、C00120240305003。操作步驟：（1）選中A列，右鍵選擇“插入”，在A1單元格輸入“訂單ID”；（2）在B2單元格輸入公式：=D2&TEXT(E2,"YYYYMMDD")&TEXT(COUNTIFS(D$2:D2,D2,E$2:E2,E2),"000")；（3）雙擊B2單元格填充柄向下填充至數(shù)據(jù)末尾；（4）選中B列，右鍵選擇“復(fù)制”，再右鍵選擇“選擇性粘貼數(shù)值”，移除公式依賴。2.將“原始數(shù)據(jù)”工作表的“日期”列（E列）格式設(shè)置為“2024年3月5日”（自定義格式：yyyy"年"m"月"d"日"），“銷售額”列（H列）設(shè)置為帶千位分隔符的數(shù)值格式，保留2位小數(shù)；將“利潤(rùn)”列（I列）設(shè)置為百分比格式，保留1位小數(shù)（如5.2%）。操作步驟：（1）選中E列，右鍵選擇“設(shè)置單元格格式”，在“數(shù)字”選項(xiàng)卡中選擇“自定義”，類型輸入“yyyy"年"m"月"d"日"”，點(diǎn)擊“確定”；（2）選中H列，右鍵選擇“設(shè)置單元格格式”，“分類”選擇“數(shù)值”，勾選“使用千位分隔符”，小數(shù)位數(shù)設(shè)為2，點(diǎn)擊“確定”；（3）選中I列，同樣進(jìn)入“設(shè)置單元格格式”，“分類”選擇“百分比”，小數(shù)位數(shù)設(shè)為1，點(diǎn)擊“確定”。（二）函數(shù)應(yīng)用與數(shù)據(jù)計(jì)算1.在“原始數(shù)據(jù)”工作表J列插入“產(chǎn)品類別”列，根據(jù)“產(chǎn)品ID”（G列）從“產(chǎn)品信息”工作表中匹配獲取對(duì)應(yīng)類別（“產(chǎn)品信息”工作表A列為產(chǎn)品ID，B列為類別）。操作步驟：（1）在J1單元格輸入“產(chǎn)品類別”；（2）在J2單元格輸入公式：=VLOOKUP(G2,產(chǎn)品信息!A:B,2,0)；（3）向下填充至數(shù)據(jù)末尾，檢查是否存在N/A錯(cuò)誤（若有，需核對(duì)“產(chǎn)品信息”工作表是否包含所有產(chǎn)品ID）。2.在“原始數(shù)據(jù)”工作表K列插入“是否達(dá)標(biāo)”列，若該訂單“銷售額”（H列）≥對(duì)應(yīng)區(qū)域當(dāng)月目標(biāo)（“區(qū)域目標(biāo)”工作表A列為區(qū)域，B列為月份，C列為目標(biāo)銷售額），則標(biāo)記“達(dá)標(biāo)”，否則“未達(dá)標(biāo)”。其中“區(qū)域”對(duì)應(yīng)“原始數(shù)據(jù)”C列，“月份”為E列的月份（如3月）。操作步驟：（1）在K1單元格輸入“是否達(dá)標(biāo)”；（2）在K2單元格輸入公式：=IF(H2>=VLOOKUP(C2&TEXT(E2,"m月"),區(qū)域目標(biāo)!A:C,3,0),"達(dá)標(biāo)","未達(dá)標(biāo)")；（3）公式說(shuō)明：通過(guò)TEXT函數(shù)將日期轉(zhuǎn)為“m月”格式（如3月），與區(qū)域（C列）拼接成查找值（如“華北3月”），在“區(qū)域目標(biāo)”工作表中A列需預(yù)先將區(qū)域與月份拼接（如A2=“華北”&B2），或調(diào)整VLOOKUP的查找范圍為區(qū)域目標(biāo)!A:C，并確保查找值與A列內(nèi)容格式一致；（4）向下填充公式，驗(yàn)證邏輯（例如華北區(qū)3月目標(biāo)為5000元，某訂單銷售額為5500元?jiǎng)t顯示“達(dá)標(biāo)”）。3.在“原始數(shù)據(jù)”工作表L列插入“銷售額排名”列，按“產(chǎn)品類別”（J列）分組，計(jì)算該訂單銷售額在同類別中的降序排名（相同銷售額排名相同，后續(xù)排名不跳號(hào)）。操作步驟：（1）在L1單元格輸入“銷售額排名”；（2）在L2單元格輸入公式：=RANK.EQ(H2,IF(J$2:J$1000=J2,H$2:H$1000),0)；（3）按下Ctrl+Shift+Enter組合鍵輸入數(shù)組公式（Excel2019及以上版本可直接輸入，舊版本需確認(rèn)）；（4）向下填充公式，檢查排名是否按類別分組（如“電子產(chǎn)品”類別內(nèi)的銷售額單獨(dú)排名）。（三）數(shù)據(jù)排序、篩選與高級(jí)操作1.對(duì)“原始數(shù)據(jù)”工作表數(shù)據(jù)按以下規(guī)則排序：主要關(guān)鍵字為“區(qū)域”（C列）升序，次要關(guān)鍵字為“產(chǎn)品類別”（J列）升序，第三關(guān)鍵字為“銷售額”（H列）降序。操作步驟：（1）選中數(shù)據(jù)區(qū)域（A1:L1000，假設(shè)數(shù)據(jù)至1000行）；（2）點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡“排序”按鈕，打開(kāi)“排序”對(duì)話框；（3）“主要關(guān)鍵字”選擇“區(qū)域”，排序依據(jù)“數(shù)值”，次序“升序”；（4）點(diǎn)擊“添加條件”，“次要關(guān)鍵字”選擇“產(chǎn)品類別”，次序“升序”；（5）再次“添加條件”，“第三關(guān)鍵字”選擇“銷售額”，次序“降序”；（6）勾選“數(shù)據(jù)包含標(biāo)題”，點(diǎn)擊“確定”完成排序。2.在“原始數(shù)據(jù)”工作表中篩選出“區(qū)域”為“華東”或“華南”、“產(chǎn)品類別”為“家電”或“日用品”、“銷售額”≥8000元且“利潤(rùn)”≥6.0%的訂單，要求使用“高級(jí)篩選”功能，條件區(qū)域放置在A13:L15（自行設(shè)計(jì)條件區(qū)域）。操作步驟：（1）在A13單元格輸入“區(qū)域”，B13輸入“產(chǎn)品類別”，C13輸入“銷售額”，D13輸入“利潤(rùn)”；（2）在A14輸入“華東”，B14輸入“家電”，C14輸入“>=8000”，D14輸入“>=6%”；（3）在A15輸入“華南”，B15輸入“日用品”，C15輸入“>=8000”，D15輸入“>=6%”；（4）點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡“高級(jí)”按鈕，打開(kāi)“高級(jí)篩選”對(duì)話框；（5）“列表區(qū)域”選擇原始數(shù)據(jù)區(qū)域（A1:L1000），“條件區(qū)域”選擇A13:D15；（6）選擇“在原有區(qū)域顯示篩選結(jié)果”或“將篩選結(jié)果復(fù)制到其他位置”（本題默認(rèn)原有區(qū)域），點(diǎn)擊“確定”。（四）條件格式與圖表制作1.在“原始數(shù)據(jù)”工作表中，對(duì)“利潤(rùn)”列（I列）應(yīng)用條件格式：若利潤(rùn)≥8.0%，填充色為綠色（標(biāo)準(zhǔn)色）；若利潤(rùn)≤4.0%，填充色為紅色（標(biāo)準(zhǔn)色）；其他情況無(wú)格式。操作步驟：（1）選中I2:I1000（數(shù)據(jù)區(qū)域）；（2）點(diǎn)擊“開(kāi)始”選項(xiàng)卡“條件格式”“新建規(guī)則”；（3）選擇“使用公式確定要設(shè)置格式的單元格”；（4）在“為符合此公式的值設(shè)置格式”中輸入：=I2>=8%，點(diǎn)擊“格式”，填充選擇綠色，點(diǎn)擊“確定”；（5）再次點(diǎn)擊“條件格式”“新建規(guī)則”，輸入公式：=I2<=4%，格式設(shè)置為紅色填充，點(diǎn)擊“確定”；（6）調(diào)整條件順序（通過(guò)“管理規(guī)則”將“>=8%”規(guī)則置于“<=4%”之上，避免覆蓋）。2.在“分析”工作表（新建）中，基于“原始數(shù)據(jù)”工作表數(shù)據(jù)創(chuàng)建組合圖表：分類軸為“月份”（提取E列月份，如1月、2月…12月）；柱形圖系列為“各月總銷售額”（H列求和）；折線圖系列為“各月平均利潤(rùn)”（I列求平均）；圖表標(biāo)題為“2024年各月銷售與利潤(rùn)趨勢(shì)圖”；數(shù)據(jù)標(biāo)簽顯示柱形圖的具體銷售額（保留0位小數(shù)）。操作步驟：（1）在“分析”工作表A1輸入“月份”，B1輸入“總銷售額”，C1輸入“平均利潤(rùn)”；（2）在A2輸入“1月”，向下填充至A13為“12月”；（3）在B2輸入公式：=SUMIF(原始數(shù)據(jù)!E:E,">=202411",原始數(shù)據(jù)!H:H)SUMIF(原始數(shù)據(jù)!E:E,">=202421",原始數(shù)據(jù)!H:H)（逐月計(jì)算，或使用SUMIFS按月份篩選）；（4）在C2輸入公式：=AVERAGEIF(原始數(shù)據(jù)!E:E,">=202411",原始數(shù)據(jù)!I:I)AVERAGEIF(原始數(shù)據(jù)!E:E,">=202421",原始數(shù)據(jù)!I:I)；（5）選中A1:C13數(shù)據(jù)區(qū)域，點(diǎn)擊“插入”選項(xiàng)卡“組合圖表”“自定義組合圖”；（6）設(shè)置“總銷售額”為“簇狀柱形圖”，“平均利潤(rùn)”為“折線圖”，勾選“次坐標(biāo)軸”（可選，根據(jù)數(shù)據(jù)范圍調(diào)整）；（7）添加圖表標(biāo)題“2024年各月銷售與利潤(rùn)趨勢(shì)圖”；（8）右鍵柱形圖系列，選擇“添加數(shù)據(jù)標(biāo)簽”，設(shè)置標(biāo)簽格式為“值”，小數(shù)位數(shù)0；（9）調(diào)整坐標(biāo)軸刻度、圖例位置，美化圖表。（五）數(shù)據(jù)透視表與數(shù)據(jù)透視圖1.在“分析”工作表中創(chuàng)建數(shù)據(jù)透視表，放置于A16單元格，要求：行標(biāo)簽：區(qū)域、產(chǎn)品類別；列標(biāo)簽：季度（將“日期”字段分組為13月=Q1，46月=Q2，79月=Q3，1012月=Q4）；值字段：銷售額（求和）、利潤(rùn)（平均值，顯示為百分比，保留1位小數(shù)）；按“銷售額”降序排列行標(biāo)簽。操作步驟：（1）點(diǎn)擊“插入”選項(xiàng)卡“數(shù)據(jù)透視表”，選擇“原始數(shù)據(jù)”為數(shù)據(jù)源，放置位置選擇“分析!A16”；（2）在“數(shù)據(jù)透視表字段”任務(wù)窗格中，將“區(qū)域”“產(chǎn)品類別”拖至“行”區(qū)域（“區(qū)域”在上，“產(chǎn)品類別”在下）；（3）將“日期”拖至“列”區(qū)域，右鍵“日期”字段“組合”，起始于2024/1/1，結(jié)束于2024/12/31，步長(zhǎng)選擇“季度”，點(diǎn)擊“確定”（列標(biāo)簽顯示為Q1、Q2等）；（4）將“銷售額”拖至“值”區(qū)域（默認(rèn)求和），將“利潤(rùn)”拖至“值”區(qū)域，右鍵“利潤(rùn)”值字段“值字段設(shè)置”，選擇“平均值”，點(diǎn)擊“數(shù)字格式”設(shè)置為百分比（1位小數(shù)）；（5）右鍵行標(biāo)簽區(qū)域任意單元格“排序”“其他排序選項(xiàng)”，主要關(guān)鍵字選擇“求和項(xiàng):銷售額”，降序排列；（6）調(diào)整列寬、行高，設(shè)置數(shù)據(jù)區(qū)域邊框。2.基于上述數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖，類型為“簇狀柱形圖”，顯示各區(qū)域、產(chǎn)品類別在不同季度的銷售額對(duì)比，圖表放置于“分析”工作表F16位置。操作步驟：（1）選中數(shù)據(jù)透視表任意單元格，點(diǎn)擊“分析”選項(xiàng)卡“數(shù)據(jù)透視圖”；（2）選擇“簇狀柱形圖”，點(diǎn)擊“確定”；（3）拖動(dòng)圖表至F16位置，調(diào)整大??；（4）刪除冗余圖例（若有），設(shè)置圖表標(biāo)題為“區(qū)域產(chǎn)品類別季度銷售額對(duì)比”；（5）右鍵坐標(biāo)軸調(diào)整刻度，確保數(shù)據(jù)顯示清晰。（六）工作表保護(hù)與打印設(shè)置1.保護(hù)“原始數(shù)據(jù)”工作表，允許用戶編輯“備注”列（M列，需自行插入），其他列不可編輯，密碼設(shè)置為“123456”（區(qū)分大小寫）。操作步驟：（1）在“原始數(shù)據(jù)”工作表M1輸入“備注”；（2）點(diǎn)擊“審閱”選項(xiàng)卡“允許用戶編輯區(qū)域”；（3）點(diǎn)擊“新建”，“區(qū)域名稱”設(shè)為“備注列”，“引用單元格”選擇M1:M1000，點(diǎn)擊“確定”；（4）點(diǎn)擊“保護(hù)工作表”，勾選“允許此工作表的所有用戶進(jìn)行：選定鎖定單元格”“選定未鎖定單元格”；（5）輸入密碼“123456”，再次確認(rèn)密碼，點(diǎn)擊“確定”；（6）驗(yàn)證：嘗試修改其他列（如A列）應(yīng)提示“單元格或圖表項(xiàng)受保護(hù)，無(wú)法修改”，修改M列可正常輸入。2.設(shè)置“分析”工作表打印