2025年excel考試綜合題及答案一、操作要求請(qǐng)根據(jù)以下業(yè)務(wù)背景及數(shù)據(jù)，在Excel中完成相關(guān)操作（所有操作均在“銷(xiāo)售數(shù)據(jù)”工作表中進(jìn)行，工作簿另存為“2024年銷(xiāo)售數(shù)據(jù)分析.xlsx”）：業(yè)務(wù)背景：某商貿(mào)公司2024年1-12月銷(xiāo)售數(shù)據(jù)存儲(chǔ)于“銷(xiāo)售數(shù)據(jù)”工作表，包含字段：區(qū)域（華北/華東/華南/華西）、產(chǎn)品類(lèi)別（日用品/家電/服飾）、產(chǎn)品名稱(chēng)、銷(xiāo)售日期（格式為“2024-01-01”）、銷(xiāo)售額（單位：元）、成本（單位：元）、銷(xiāo)售人員、客戶類(lèi)型（新客戶/老客戶）。數(shù)據(jù)范圍為A2:J1001（A1:J1為標(biāo)題行）。任務(wù)1：數(shù)據(jù)預(yù)處理（20分）1.將A列“區(qū)域”列寬設(shè)置為10，H列“銷(xiāo)售人員”列寬設(shè)置為12；將所有數(shù)值型數(shù)據(jù)（銷(xiāo)售額、成本）設(shè)置為“會(huì)計(jì)專(zhuān)用”格式，保留2位小數(shù)。2.在K列插入“毛利”列（K1單元格輸入“毛利”），計(jì)算每筆銷(xiāo)售的毛利（毛利=銷(xiāo)售額-成本）。3.在L列插入“銷(xiāo)售周期”列（L1單元格輸入“銷(xiāo)售周期”），計(jì)算每筆銷(xiāo)售從1月1日到銷(xiāo)售日期的天數(shù)（例如：銷(xiāo)售日期為2024-01-15，則銷(xiāo)售周期為14天）。4.在M列插入“季度”列（M1單元格輸入“季度”），根據(jù)銷(xiāo)售日期判斷季度（1-3月為“Q1”，4-6月為“Q2”，7-9月為“Q3”，10-12月為“Q4”）。5.設(shè)置“客戶類(lèi)型”列（I列）數(shù)據(jù)有效性：僅允許輸入“新客戶”或“老客戶”，輸入非法值時(shí)提示“請(qǐng)輸入‘新客戶’或‘老客戶’”。任務(wù)2：函數(shù)與公式應(yīng)用（30分）1.在B1003單元格計(jì)算“日用品”類(lèi)別中，華東區(qū)域、老客戶的總銷(xiāo)售額（要求使用SUMIFS函數(shù)）。2.在B1004單元格計(jì)算“家電”類(lèi)別中，銷(xiāo)售額最高的3筆訂單的銷(xiāo)售額之和（要求使用LARGE函數(shù)）。3.在B1005單元格計(jì)算所有訂單中，銷(xiāo)售周期超過(guò)180天且毛利大于500元的訂單數(shù)量（要求使用COUNTIFS函數(shù)）。4.在B1006單元格顯示“2024年總毛利”文本及具體數(shù)值（例如：“2024年總毛利：123456.78元”），數(shù)值部分為K列毛利總和（要求使用TEXT函數(shù)與SUM函數(shù)嵌套）。5.在B1007單元格查找“產(chǎn)品名稱(chēng)”為“智能電飯煲”的首筆訂單對(duì)應(yīng)的銷(xiāo)售人員姓名（要求使用INDEX+MATCH組合函數(shù)，且MATCH函數(shù)匹配類(lèi)型為精確匹配）。任務(wù)3：圖表制作（20分）根據(jù)A2:M1001區(qū)域中“區(qū)域”“產(chǎn)品類(lèi)別”“銷(xiāo)售額”數(shù)據(jù)，制作如下圖表：1.插入一張簇狀柱形圖，橫坐標(biāo)為“區(qū)域”，圖例項(xiàng)為“產(chǎn)品類(lèi)別”，數(shù)據(jù)系列值為“銷(xiāo)售額”總和。2.設(shè)置圖表標(biāo)題為“2024年各區(qū)域不同產(chǎn)品類(lèi)別銷(xiāo)售額對(duì)比”，字體12號(hào)、加粗；橫坐標(biāo)標(biāo)題為“區(qū)域”，縱坐標(biāo)標(biāo)題為“銷(xiāo)售額（元）”，字體10號(hào)。3.將圖表放置在“圖表分析”工作表（需新建）中，調(diào)整圖表大小至寬度20厘米、高度12厘米。4.為圖表添加數(shù)據(jù)標(biāo)簽，顯示值且保留0位小數(shù)；隱藏圖例。任務(wù)4：數(shù)據(jù)透視表與高級(jí)分析（20分）1.以A2:J1001區(qū)域?yàn)閿?shù)據(jù)源，在“透視分析”工作表（需新建）中創(chuàng)建數(shù)據(jù)透視表。2.行標(biāo)簽設(shè)置為“區(qū)域”+“產(chǎn)品類(lèi)別”（嵌套顯示），列標(biāo)簽設(shè)置為“季度”（M列），值字段為“銷(xiāo)售額”（求和）與“毛利”（求平均）。3.添加“客戶類(lèi)型”切片器，關(guān)聯(lián)該數(shù)據(jù)透視表；添加“銷(xiāo)售人員”篩選器，放置在數(shù)據(jù)透視表上方。4.在數(shù)據(jù)透視表中插入計(jì)算字段“毛利率”（毛利率=毛利/銷(xiāo)售額），保留2位小數(shù)，以百分比格式顯示。任務(wù)5：高級(jí)操作（10分）1.設(shè)置“銷(xiāo)售數(shù)據(jù)”工作表保護(hù)：允許用戶編輯區(qū)域?yàn)椤癒:L”列（毛利、銷(xiāo)售周期），其余區(qū)域禁止編輯，密碼為“123456”（提示：使用“允許用戶編輯區(qū)域”功能）。2.錄制宏“自動(dòng)填充日期”：選中A2:A1001區(qū)域，將文本格式的銷(xiāo)售日期（如“2024年1月1日”）批量轉(zhuǎn)換為“2024-01-01”格式（假設(shè)原始數(shù)據(jù)存在此問(wèn)題），并將宏保存在當(dāng)前工作簿中，快捷鍵設(shè)置為“Ctrl+Shift+D”。答案及操作步驟任務(wù)1答案1.選中A列→右鍵“列寬”→輸入10；選中H列→列寬輸入12。選中F列（銷(xiāo)售額）、G列（成本）→右鍵“設(shè)置單元格格式”→分類(lèi)選擇“會(huì)計(jì)專(zhuān)用”→小數(shù)位數(shù)2→確定。2.在K2單元格輸入公式“=F2-G2”→雙擊填充柄至K1001。3.在L2單元格輸入公式“=D2-DATE(2024,1,1)”→設(shè)置單元格格式為“常規(guī)”（自動(dòng)顯示天數(shù)）→雙擊填充柄至L1001。4.在M2單元格輸入公式“=TEXT(D2,"Q0")”→雙擊填充柄至M1001（或使用IF函數(shù)：=IF(MONTH(D2)<=3,"Q1",IF(MONTH(D2)<=6,"Q2",IF(MONTH(D2)<=9,"Q3","Q4")))）。5.選中I2:I1001→數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)置→允許“序列”→來(lái)源輸入“新客戶,老客戶”（逗號(hào)英文）→出錯(cuò)警告→標(biāo)題“輸入錯(cuò)誤”→錯(cuò)誤信息“請(qǐng)輸入‘新客戶’或‘老客戶’”→確定。任務(wù)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輸入（數(shù)組公式）。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))（假設(shè)產(chǎn)品名稱(chēng)在D列，若實(shí)際列號(hào)不同需調(diào)整）。任務(wù)3答案1.選中A1:C1001（區(qū)域、產(chǎn)品類(lèi)別、銷(xiāo)售額）→插入→圖表→簇狀柱形圖。2.點(diǎn)擊圖表標(biāo)題→輸入“2024年各區(qū)域不同產(chǎn)品類(lèi)別銷(xiāo)售額對(duì)比”→設(shè)置字體12號(hào)加粗；右鍵橫坐標(biāo)→設(shè)置坐標(biāo)軸標(biāo)題→輸入“區(qū)域”；右鍵縱坐標(biāo)→設(shè)置坐標(biāo)軸標(biāo)題→輸入“銷(xiāo)售額（元）”→字體10號(hào)。3.右鍵圖表→移動(dòng)圖表→選擇“圖表分析”工作表→確定；拖動(dòng)圖表邊緣調(diào)整寬度20cm、高度12cm（或在“大小”選項(xiàng)卡中設(shè)置精確數(shù)值）。4.點(diǎn)擊圖表→設(shè)計(jì)→添加圖表元素→數(shù)據(jù)標(biāo)簽→居中；右鍵圖例→刪除。任務(wù)4答案1.選中A1:J1001→插入→數(shù)據(jù)透視表→選擇放置位置為“透視分析”工作表A1單元格→確定。2.字段列表中拖動(dòng)“區(qū)域”“產(chǎn)品類(lèi)別”到行區(qū)域（先拖“區(qū)域”，再拖“產(chǎn)品類(lèi)別”至“區(qū)域”下方實(shí)現(xiàn)嵌套）；拖動(dòng)“季度”（M列）到列區(qū)域；拖動(dòng)“銷(xiāo)售額”到值區(qū)域（默認(rèn)求和），拖動(dòng)“毛利”到值區(qū)域→右鍵毛利值字段→值字段設(shè)置→選擇“平均值”。3.數(shù)據(jù)透視表工具→分析→插入切片器→勾選“客戶類(lèi)型”→確定；在數(shù)據(jù)透視表字段列表中拖動(dòng)“銷(xiāo)售人員”到篩選器區(qū)域→放置在數(shù)據(jù)透視表上方。4.數(shù)據(jù)透視表工具→分析→字段、項(xiàng)目和集→計(jì)算字段→名稱(chēng)“毛利率”→公式輸入“=毛利/銷(xiāo)售額”→確定→右鍵毛利率值字段→設(shè)置單元格格式→百分比→小數(shù)位數(shù)2→確定。任務(wù)5答案1.審閱→允許用戶編輯區(qū)域→新建→標(biāo)題“可編輯區(qū)域”→引用單元格“$K$2:$L$1001”→確定→保護(hù)工作表→輸入密碼“123456”→勾選“允許此工作表的所有用戶進(jìn)行：選定鎖定單元格”→確定（重復(fù)輸入密碼）。2.開(kāi)發(fā)工具→錄制宏→宏名“自動(dòng)填充日期”→快捷鍵“Ctrl+Shift+D”→保存在“當(dāng)前工作簿”→確定