2025年excel試題庫及答案一、基礎(chǔ)操作題（共5題）1.打開“2025年銷售數(shù)據(jù).xlsx”，在Sheet1中完成以下操作：（1）將A1:E1單元格區(qū)域合并并居中，設(shè)置字體為“微軟雅黑”、12號(hào)、加粗；（2）將B列（“產(chǎn)品名稱”列）列寬調(diào)整為15，行高調(diào)整為20；（3）為F列（“備注”列）添加數(shù)據(jù)驗(yàn)證，允許輸入“已發(fā)貨”“未發(fā)貨”“運(yùn)輸中”三種狀態(tài)，出錯(cuò)提示信息為“請(qǐng)輸入指定狀態(tài)”。答案：（1）選中A1:E1，點(diǎn)擊“開始”選項(xiàng)卡-“對(duì)齊方式”組-“合并后居中”按鈕；選中合并后的單元格，在“字體”組中設(shè)置字體為“微軟雅黑”、字號(hào)12、加粗；（2）選中B列列標(biāo)（B列字母），右鍵選擇“列寬”，輸入15后確定；選中B列所有行（或選中任意B列單元格），右鍵選擇“行高”，輸入20后確定；（3）選中F列（或F2:F1000），點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“數(shù)據(jù)工具”組-“數(shù)據(jù)驗(yàn)證”；在“設(shè)置”選項(xiàng)卡中，“允許”選擇“序列”，“來源”輸入“已發(fā)貨,未發(fā)貨,運(yùn)輸中”（逗號(hào)為英文狀態(tài)）；切換到“出錯(cuò)警告”選項(xiàng)卡，輸入標(biāo)題“錯(cuò)誤”，信息“請(qǐng)輸入指定狀態(tài)”，點(diǎn)擊確定。2.在Sheet2中，現(xiàn)有A列（“訂單號(hào)”）數(shù)據(jù)為文本格式，部分訂單號(hào)前缺少“D-”前綴（如“20250101”需改為“D-20250101”）。要求批量為A列所有單元格添加“D-”前綴，且保持原數(shù)據(jù)長(zhǎng)度不變（即僅在無“D-”前綴的單元格前添加）。答案：在B1單元格輸入公式：=IF(LEFT(A1,2)="D-",A1,"D-"&A1)，按回車后下拉填充至數(shù)據(jù)末尾；選中B列數(shù)據(jù)，右鍵選擇“復(fù)制”，再右鍵點(diǎn)擊A1單元格選擇“選擇性粘貼”-“數(shù)值”，刪除B列。3.在Sheet3中，C列（“日期”）數(shù)據(jù)格式混亂，包含“2025/3/15”“2025-04-20”“2025年5月25日”三種格式。要求將C列統(tǒng)一轉(zhuǎn)換為“2025-03-15”（YYYY-MM-DD）的標(biāo)準(zhǔn)日期格式。答案：選中C列，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“數(shù)據(jù)工具”組-“分列”；在“文本分列向?qū)?步驟1”中選擇“分隔符號(hào)”，點(diǎn)擊下一步；步驟2中取消所有分隔符號(hào)勾選，點(diǎn)擊下一步；步驟3中選擇“列數(shù)據(jù)格式”為“日期”，類型選擇“YMD”，點(diǎn)擊完成；完成后右鍵C列，選擇“設(shè)置單元格格式”-“日期”，類型選擇“2001-3-14”（即YYYY-MM-DD），點(diǎn)擊確定。4.在Sheet4中，需要將G列（“單價(jià)”）和H列（“數(shù)量”）相乘得到I列（“金額”），但部分H列單元格存在“無數(shù)據(jù)”文本（非空），要求I列計(jì)算時(shí)忽略“無數(shù)據(jù)”單元格（即若H列為“無數(shù)據(jù)”，則I列為空；否則計(jì)算GH）。答案：在I1單元格輸入公式：=IF(H1="無數(shù)據(jù)","",G1H1)，按回車后下拉填充至數(shù)據(jù)末尾。5.在工作簿中新建3個(gè)工作表，命名為“一季度”“二季度”“三季度”，并將“一季度”工作表標(biāo)簽顏色設(shè)置為紅色，“二季度”設(shè)置為綠色，“三季度”設(shè)置為藍(lán)色。答案：右鍵點(diǎn)擊任意工作表標(biāo)簽，選擇“插入”-“工作表”，重復(fù)操作創(chuàng)建3個(gè)新表；右鍵新表標(biāo)簽，選擇“重命名”，依次輸入“一季度”“二季度”“三季度”；右鍵“一季度”標(biāo)簽，選擇“工作表標(biāo)簽顏色”-紅色；同理設(shè)置“二季度”為綠色、“三季度”為藍(lán)色。二、函數(shù)與公式題（共8題）6.在“銷售總表”中，A列為“地區(qū)”（包含“華東”“華北”“華南”），B列為“銷售額”（數(shù)值），要求計(jì)算“華東”地區(qū)的總銷售額。答案：在C1單元格輸入公式：=SUMIF(A:A,"華東",B:B)，按回車后顯示結(jié)果。7.在“客戶信息表”中，A列為“客戶ID”（如“KH001”），B列為“注冊(cè)日期”（日期格式），C列為“是否會(huì)員”（“是”或“否”），要求統(tǒng)計(jì)2025年1月1日至2025年6月30日期間注冊(cè)且為會(huì)員的客戶數(shù)量。答案：在D1單元格輸入公式：=COUNTIFS(B:B,">=2025-1-1",B:B,"<=2025-6-30",C:C,"是")，按回車后顯示結(jié)果。8.在“產(chǎn)品表”中，A列為“產(chǎn)品ID”（如“P001”），B列為“產(chǎn)品名稱”，C列為“成本價(jià)”，D列為“售價(jià)”，要求在E列計(jì)算利潤(rùn)率（利潤(rùn)率=（售價(jià)-成本價(jià)）/成本價(jià)，保留2位小數(shù)，負(fù)數(shù)表示虧損）。答案：在E1單元格輸入公式：=ROUND((D1-C1)/C1,2)，按回車后下拉填充；右鍵E列，選擇“設(shè)置單元格格式”-“數(shù)值”，小數(shù)位數(shù)設(shè)為2，點(diǎn)擊確定。9.在“訂單表”中，A列為“訂單日期”（日期格式），B列為“訂單狀態(tài)”（“已完成”“未完成”“取消”），要求在C列提取訂單日期的月份（如“2025-03-15”提取為“3月”）。答案：在C1單元格輸入公式：=TEXT(A1,"m月")，按回車后下拉填充。10.在“庫存表”中，A列為“商品名稱”，B列為“安全庫存”（數(shù)值），C列為“當(dāng)前庫存”（數(shù)值），要求在D列標(biāo)注庫存狀態(tài)：若當(dāng)前庫存≥安全庫存，標(biāo)注“充足”；若當(dāng)前庫存≥安全庫存80%且＜安全庫存，標(biāo)注“預(yù)警”；否則標(biāo)注“短缺”。答案：在D1單元格輸入公式：=IF(C1>=B1,"充足",IF(C1>=B10.8,"預(yù)警","短缺"))，按回車后下拉填充。11.在“員工表”中，A列為“姓名”，B列為“入職日期”（日期格式），要求在C列計(jì)算截至2025年12月31日的司齡（以“X年Y個(gè)月”格式顯示，如“2年5個(gè)月”）。答案：在C1單元格輸入公式：=DATEDIF(B1,"2025-12-31","y")&"年"&DATEDIF(B1,"2025-12-31","ym")&"個(gè)月"，按回車后下拉填充。12.在“供應(yīng)商表”中，A列為“供應(yīng)商編號(hào)”（如“GS001”），B列為“聯(lián)系人”，C列為“聯(lián)系電話”（部分為“1381234”格式，部分為完整號(hào)碼），要求在D列提取完整聯(lián)系電話（即若C列包含“”，則替換為“1234”；否則保留原號(hào)碼）。答案：在D1單元格輸入公式：=IF(ISNUMBER(FIND("",C1)),SUBSTITUTE(C1,"","1234"),C1)，按回車后下拉填充。13.在“數(shù)據(jù)核對(duì)表”中，Sheet1的A列為“商品ID”（唯一），B列為“系統(tǒng)數(shù)量”；Sheet2的A列為“商品ID”，B列為“實(shí)際數(shù)量”。要求在Sheet1的C列標(biāo)注“一致”或“不一致”（若系統(tǒng)數(shù)量=實(shí)際數(shù)量則“一致”，否則“不一致”）。答案：在Sheet1的C1單元格輸入公式：=IF(B1=VLOOKUP(A1,Sheet2!A:B,2,0),"一致","不一致")，按回車后下拉填充（需確保Sheet2的商品ID無重復(fù)）。三、數(shù)據(jù)處理與分析題（共6題）14.在“銷售明細(xì)表”中，數(shù)據(jù)區(qū)域?yàn)锳1:E100（A列地區(qū)，B列產(chǎn)品，C列月份，D列銷售額，E列客戶），要求按以下規(guī)則排序：首先按“地區(qū)”升序（“華北”“華東”“華南”），其次按“產(chǎn)品”降序（“手機(jī)”“電腦”“平板”），最后按“銷售額”降序。答案：選中數(shù)據(jù)區(qū)域A1:E100（或任意單元格），點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“排序和篩選”組-“排序”；在“排序”對(duì)話框中，“主要關(guān)鍵字”選擇“地區(qū)”，“排序依據(jù)”選擇“數(shù)值”，“次序”選擇“自定義序列”，輸入“華北,華東,華南”（逗號(hào)英文），點(diǎn)擊確定；點(diǎn)擊“添加條件”，“次要關(guān)鍵字”選擇“產(chǎn)品”，“次序”選擇“降序”，并在“自定義序列”中輸入“手機(jī),電腦,平板”（降序需調(diào)整順序?yàn)椤笆謾C(jī),電腦,平板”或直接按字母降序，根據(jù)實(shí)際文本調(diào)整）；再次添加條件，“第三關(guān)鍵字”選擇“銷售額”，“次序”選擇“降序”，點(diǎn)擊確定。15.在“客戶訂單表”中，數(shù)據(jù)包含“客戶ID”“訂單日期”“訂單金額”“支付方式”（“支付寶”“微信”“銀行卡”），要求使用自動(dòng)篩選功能，篩選出2025年第二季度（4-6月）通過“支付寶”支付且訂單金額≥5000元的記錄。答案：點(diǎn)擊數(shù)據(jù)區(qū)域任意單元格，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“排序和篩選”組-“篩選”；點(diǎn)擊“訂單日期”列篩選按鈕，選擇“日期篩選”-“介于”，輸入起始日期“2025-4-1”和結(jié)束日期“2025-6-30”；點(diǎn)擊“支付方式”列篩選按鈕，勾選“支付寶”；點(diǎn)擊“訂單金額”列篩選按鈕，選擇“數(shù)字篩選”-“大于或等于”，輸入5000，點(diǎn)擊確定。16.在“庫存匯總表”中，已按“倉庫”（A列）排序，數(shù)據(jù)包含“倉庫”“商品”“庫存數(shù)量”，要求對(duì)各倉庫的庫存數(shù)量進(jìn)行分類匯總，顯示每個(gè)倉庫的平均庫存數(shù)量。答案：確保數(shù)據(jù)已按“倉庫”列排序（點(diǎn)擊“數(shù)據(jù)”-“排序”，關(guān)鍵字選“倉庫”，升序）；點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“分級(jí)顯示”組-“分類匯總”；在“分類字段”選擇“倉庫”，“匯總方式”選擇“平均值”，“選定匯總項(xiàng)”勾選“庫存數(shù)量”，取消勾選“替換當(dāng)前分類匯總”，點(diǎn)擊確定。17.在“年度銷售數(shù)據(jù)”中，包含“地區(qū)”“產(chǎn)品”“季度”“銷售額”字段，要求創(chuàng)建數(shù)據(jù)透視表，顯示各地區(qū)各產(chǎn)品在四個(gè)季度的總銷售額，且以“地區(qū)”為行標(biāo)簽，“產(chǎn)品”為列標(biāo)簽，“季度”為篩選器。答案：選中數(shù)據(jù)區(qū)域，點(diǎn)擊“插入”選項(xiàng)卡-“表格”組-“數(shù)據(jù)透視表”，選擇放置位置（如Sheet2!A1）；在“數(shù)據(jù)透視表字段”任務(wù)窗格中，將“地區(qū)”拖至“行”區(qū)域，“產(chǎn)品”拖至“列”區(qū)域，“季度”拖至“篩選器”區(qū)域，“銷售額”拖至“值”區(qū)域（確保值字段設(shè)置為“求和”）。18.在“異常數(shù)據(jù)記錄表”中，F(xiàn)列（“錯(cuò)誤代碼”）包含“E001”“E002”“無”三種值，要求使用條件格式，將“E001”標(biāo)注為紅色填充、白色字體，“E002”標(biāo)注為黃色填充、黑色字體，“無”不標(biāo)注。答案：選中F列數(shù)據(jù)區(qū)域（F2:F100），點(diǎn)擊“開始”選項(xiàng)卡-“樣式”組-“條件格式”-“新建規(guī)則”；選擇“使用公式確定要設(shè)置格式的單元格”，輸入公式：=F2="E001"，點(diǎn)擊“格式”，設(shè)置填充紅色、字體白色；點(diǎn)擊“確定”返回，再次點(diǎn)擊“條件格式”-“新建規(guī)則”，輸入公式：=F2="E002"，設(shè)置填充黃色、字體黑色，點(diǎn)擊確定。19.在“銷售趨勢(shì)圖”中，A列為“月份”（1-12月），B列為“銷售額”（萬元），C列為“同比增長(zhǎng)率”（%），要求制作組合圖表：銷售額用簇狀柱形圖顯示，增長(zhǎng)率用折線圖顯示，共享同一X軸，圖表標(biāo)題為“2025年銷售趨勢(shì)圖”。答案：選中A1:C12數(shù)據(jù)區(qū)域，點(diǎn)擊“插入”選項(xiàng)卡-“圖表”組-“組合圖”-“創(chuàng)建自定義組合圖”；在“圖表類型”中，“銷售額”選擇“簇狀柱形圖”，“同比增長(zhǎng)率”選擇“折線圖”；勾選“同比增長(zhǎng)率”的“次坐標(biāo)軸”（若需區(qū)分）；點(diǎn)擊“確定”，添加圖表標(biāo)題“2025年銷售趨勢(shì)圖”，調(diào)整坐標(biāo)軸標(biāo)簽和圖例位置。四、高級(jí)功能題（共4題）20.利用PowerQuery合并“一季度銷售.xlsx”“二季度銷售.xlsx”“三季度銷售.xlsx”三個(gè)工作簿中的Sheet1數(shù)據(jù)（結(jié)構(gòu)相同，均包含“日期”“產(chǎn)品”“銷售額”），要求合并后刪除“日期”列中的空值，并將“產(chǎn)品”列中的“手機(jī)”統(tǒng)一替換為“智能手機(jī)”。答案：打開任意Excel文件，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡-“獲取和轉(zhuǎn)換數(shù)據(jù)”組-“從文件”-“從工作簿”，選擇任意一個(gè)季度文件，點(diǎn)擊“加載到”旁邊的下拉箭頭-“轉(zhuǎn)換數(shù)據(jù)”；在PowerQuery編輯器中，點(diǎn)擊“主頁”選項(xiàng)卡-“合并查詢”-“合并文件”，選擇另外兩個(gè)季度文件，點(diǎn)擊“確定”；合并后，點(diǎn)擊“日期”列標(biāo)題，選擇“刪除行”-“刪除空行”；點(diǎn)擊“產(chǎn)品”列標(biāo)題，選擇“替換值”，“查找值”輸入“手機(jī)”，“替換值”輸入“智能手機(jī)”，點(diǎn)擊“確定”；最后點(diǎn)擊“主頁”-“關(guān)閉并上載”到當(dāng)前工作簿。21.錄制一個(gè)宏，名為“調(diào)整格式”，功能為：選中當(dāng)前工作表的A列，設(shè)置列寬為18，行高為22，字體為“宋體”10號(hào)，居中對(duì)齊。答案：點(diǎn)擊“開發(fā)工具”選項(xiàng)卡-“代碼”組-“錄制宏”，輸入宏名“調(diào)整格式”，點(diǎn)擊“確定”；手動(dòng)操作：選中A列，右鍵“列寬”輸入18，右鍵“行高”輸入22；在“開始”選項(xiàng)卡設(shè)置字體為“宋體”、字號(hào)10、居中對(duì)齊；點(diǎn)擊“開發(fā)工具”-“停止錄制”。22.在“動(dòng)態(tài)報(bào)表”中，A1單元格為下拉菜單（內(nèi)容為“華東”“華北”“華南”），要求B列（“篩選后銷售額”）根據(jù)A1的選擇自動(dòng)顯示對(duì)應(yīng)地區(qū)的銷售額（源數(shù)據(jù)在Sheet2的A:B列，A列為地區(qū)，B列為銷售額）。答案：首先為A1添加數(shù)據(jù)驗(yàn)證（同第1題步驟3，來源為“華東,華北,華南”）；在B1單元格輸入公式：=IF(A1="","",SUMIF(Sheet2!A:A,A1,Sheet2!B:B))，按回車后下拉填充（若需顯示明細(xì)，可使用FILTER函數(shù)：=FILTER(Sheet2!B:B,Sheet2!A:A=A1)）。23.利用VBA編寫一個(gè)子程序，功能為：遍歷當(dāng)前工作表A列（A2:A100），若單元格內(nèi)容包含“異常”二字，則將該行背景色設(shè)置為