excel數(shù)據(jù)處理與分析章節(jié)測試題與答案一、單項選擇題（每題2分，共20分）1.在Excel中，若要統(tǒng)計某列中數(shù)值型數(shù)據(jù)的個數(shù)，應使用的函數(shù)是（）A.COUNTB.COUNTAC.COUNTBLANKD.COUNTIF答案：A解析：COUNT函數(shù)統(tǒng)計數(shù)值型數(shù)據(jù)的個數(shù)；COUNTA統(tǒng)計非空單元格個數(shù)（包括文本）；COUNTBLANK統(tǒng)計空白單元格；COUNTIF按條件統(tǒng)計。2.以下關于數(shù)據(jù)透視表的描述，錯誤的是（）A.數(shù)據(jù)透視表可動態(tài)調整行/列字段B.原始數(shù)據(jù)修改后，需手動刷新數(shù)據(jù)透視表才能更新C.值字段默認匯總方式為“求和”D.不能基于多個不連續(xù)的數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視表答案：D解析：Excel2016及以上版本支持通過“數(shù)據(jù)模型”基于多個不連續(xù)區(qū)域創(chuàng)建數(shù)據(jù)透視表。3.若A1單元格內容為“2023-10-5”，B1單元格輸入公式“=MONTH(A1)”，則返回結果為（）A.10B.5C.2023D.10-5答案：A解析：MONTH函數(shù)提取日期中的月份值，返回1-12的整數(shù)。4.在條件格式中，若要將高于平均值的單元格填充為紅色，應選擇的規(guī)則類型是（）A.突出顯示單元格規(guī)則-大于B.項目選取規(guī)則-值最大的10項C.使用公式確定要設置格式的單元格D.數(shù)據(jù)條答案：A解析：“突出顯示單元格規(guī)則-大于”可直接輸入平均值（或用AVERAGE函數(shù)）作為條件值，系統(tǒng)自動計算當前區(qū)域平均值。5.執(zhí)行“數(shù)據(jù)-排序”操作時，若選中“數(shù)據(jù)包含標題”，則（）A.標題行不會參與排序B.標題行會作為普通數(shù)據(jù)參與排序C.排序后標題行自動調整位置D.無法對標題行進行格式設置答案：A解析：勾選“數(shù)據(jù)包含標題”時，標題行作為字段名標識，不參與數(shù)據(jù)行的排序。6.若要在Excel中實現(xiàn)“當輸入的工號長度不等于6位時提示錯誤”，應使用（）A.數(shù)據(jù)驗證-設置-文本長度B.條件格式-新建規(guī)則-公式C.保護工作表-允許用戶編輯區(qū)域D.審閱-批注答案：A解析：數(shù)據(jù)驗證的“文本長度”規(guī)則可限制輸入內容的字符數(shù)，配合錯誤提示信息實現(xiàn)該功能。7.以下關于VLOOKUP函數(shù)的描述，正確的是（）A.查找值必須位于查找區(qū)域的首列B.第四個參數(shù)為0時表示近似匹配C.可以查找水平方向的數(shù)據(jù)D.若查找區(qū)域包含隱藏列，仍能正確返回結果答案：A解析：VLOOKUP要求查找值在查找區(qū)域的首列；第四個參數(shù)0為精確匹配，1為近似匹配；水平查找需用HLOOKUP；隱藏列會導致VLOOKUP無法訪問數(shù)據(jù)。8.在“數(shù)據(jù)透視表字段”窗格中，灰色顯示的字段表示（）A.該字段已被添加到報表中B.該字段類型不支持匯總（如文本）C.該字段為空白字段D.數(shù)據(jù)透視表已達到最大字段限制答案：B解析：文本型字段（如“產品名稱”）在值區(qū)域默認無法匯總（灰色顯示），需手動選擇“計數(shù)”等匯總方式。9.若要將A列中“2023/10/05”格式的日期批量轉換為“2023年10月05日”，最快捷的方法是（）A.使用TEXT函數(shù)：=TEXT(A1,"yyyy年mm月dd日")B.復制后粘貼為數(shù)值，再設置單元格格式C.數(shù)據(jù)-分列-固定寬度-日期格式D.開始-數(shù)字-自定義-類型輸入“yyyy年mm月dd日”答案：D解析：直接設置單元格格式為自定義“yyyy年mm月dd日”可批量轉換，無需公式或分列操作。10.在Excel中，若要計算“銷售額”列中大于5000且小于10000的數(shù)值個數(shù)，應使用的函數(shù)是（）A.COUNTIFS(銷售額,">5000",銷售額,"<10000")B.COUNTIF(銷售額,">5000")-COUNTIF(銷售額,">=10000")C.SUM(IF((銷售額>5000)*(銷售額<10000),1,0))（數(shù)組公式）D.以上方法均正確答案：D解析：COUNTIFS可直接多條件統(tǒng)計；通過兩個COUNTIF相減可計算區(qū)間值；數(shù)組公式配合SUM和IF也可實現(xiàn)，三種方法均正確。二、判斷題（每題1分，共10分。正確填“√”，錯誤填“×”）1.絕對引用符號“$”只能出現(xiàn)在列標前，不能出現(xiàn)在行號前。（）答案：×解析：“”可2.數(shù)據(jù)透視表的值字段可以設置為“最大值”“最小值”“平均值”等多種匯總方式。（）答案：√解析：值字段右鍵-值字段設置中可選擇求和、計數(shù)、最大值等20余種匯總方式。3.使用“合并后居中”功能合并多個單元格時，只有左上角單元格的內容會被保留。（）答案：√解析：合并單元格會清空其他單元格內容，僅保留左上角數(shù)據(jù)。4.在“數(shù)據(jù)驗證”中設置“整數(shù)”類型時，若允許負數(shù)，需手動勾選“允許負數(shù)”選項。（）答案：×解析：“整數(shù)”類型默認允許負數(shù)，無需額外設置；若要限制正數(shù)，需設置“最小值”為0。5.函數(shù)“=SUMIF(區(qū)域,條件,求和區(qū)域)”中，“條件”可以是文本、數(shù)值或表達式。（）答案：√解析：條件支持多種形式，如">60"、"蘋果"、B1（單元格引用）等。6.對數(shù)據(jù)進行降序排序時，空單元格會被排列在數(shù)據(jù)區(qū)域的最上方。（）答案：×解析：排序時空單元格默認排在最后（升序/降序均如此）。7.圖表中的數(shù)據(jù)系列可以來源于不同的工作表，但必須為同一工作簿。（）答案：√解析：圖表數(shù)據(jù)源可跨工作表引用（如=Sheet2!A1:B10），但不能跨工作簿（需鏈接）。8.使用“快速填充”功能（Ctrl+E）時，需至少輸入1個示例值才能觸發(fā)自動填充。（）答案：√解析：快速填充需要用戶提供至少一個正確示例，系統(tǒng)通過模式識別完成后續(xù)填充。9.在“條件格式”中，使用公式“=A1>100”時，該規(guī)則會應用于當前選中區(qū)域的所有單元格，其中A1會根據(jù)相對位置自動調整。（）答案：√解析：條件格式中的公式默認使用相對引用，應用到B1時會自動變?yōu)椤?B1>100”。10.保護工作表后，所有單元格的編輯權限都會被鎖定，無法進行任何修改。（）答案：×解析：保護工作表時可勾選“允許用戶編輯區(qū)域”，設置特定區(qū)域可編輯（需輸入密碼）。三、操作題（每題10分，共30分）1.現(xiàn)有“員工信息表”（部分數(shù)據(jù)如下），要求：（1）將A1:E1單元格合并并居中，設置字體為“微軟雅黑”，字號14，加粗；（2）在F列計算“年齡”（當前年份為2023年，出生日期格式為“yyyy-mm-dd”）；（3）將“性別”列中“男”填充為藍色底紋，“女”填充為粉色底紋。|A（工號）|B（姓名）|C（部門）|D（出生日期）|E（性別）||----------|----------|----------|---------------|----------||001|張三|銷售部|1990-05-12|男||002|李四|技術部|1995-08-20|女|答案及解析：（1）選中A1:E1→開始-對齊方式-合并后居中→字體選擇“微軟雅黑”，字號14，點擊加粗按鈕；（2）在F2單元格輸入公式：=2023-YEAR(D2)→向下填充至數(shù)據(jù)末尾；（3）選中E列數(shù)據(jù)區(qū)域（E2:E3等）→開始-條件格式-新建規(guī)則-使用公式確定格式→輸入公式：=E2="男"→設置填充顏色為藍色→重復操作，輸入公式：=E2="女"→設置填充顏色為粉色。常見錯誤：合并單元格時未選中連續(xù)區(qū)域；計算年齡時直接用“2023-D2”（需用YEAR函數(shù)提取年份）；條件格式公式未使用相對引用（如固定為E2）。2.某公司“2023年季度銷售表”包含字段：日期（A列）、產品（B列）、地區(qū)（C列）、銷售額（D列）、成本（E列）。要求：（1）在F列計算“毛利”（毛利=銷售額-成本）；（2）按“地區(qū)”和“產品”分類匯總，顯示各地區(qū)各產品的總銷售額；（3）將“日期”列中2023年第二季度（4-6月）的數(shù)據(jù)篩選出來。答案及解析：（1）F2單元格輸入公式：=D2-E2→向下填充；（2）插入數(shù)據(jù)透視表（選中數(shù)據(jù)區(qū)域→插入-數(shù)據(jù)透視表-選擇放置位置）→將“地區(qū)”拖入行區(qū)域，“產品”拖入行區(qū)域（或列區(qū)域），“銷售額”拖入值區(qū)域（默認求和）；（3）點擊“日期”列篩選按鈕→日期篩選-介于→起始日期2023-4-1，結束日期2023-6-30→確定。關鍵步驟：數(shù)據(jù)透視表需正確拖放字段（行區(qū)域放分類字段，值區(qū)域放匯總字段）；日期篩選時注意“介于”包含起始和結束日期。3.現(xiàn)有“客戶訂單表”（數(shù)據(jù)范圍A1:H100），其中H列“交貨狀態(tài)”需根據(jù)G列“交貨日期”判斷：若交貨日期≤訂單日期（B列）+30天，標記為“按時”；否則標記為“延遲”。要求用函數(shù)實現(xiàn)自動標記。答案及解析：在H2單元格輸入公式：=IF(G2<=B2+30,"按時","延遲")向下填充至H100。擴展說明：若“訂單日期”或“交貨日期”為文本格式，需先用DATEVALUE函數(shù)轉換（如=IF(DATEVALUE(G2)<=DATEVALUE(B2)+30,"按時","延遲")）；若存在空值，可添加容錯處理：=IF(ISBLANK(G2),"未交貨",IF(G2<=B2+30,"按時","延遲"))。四、綜合分析題（40分）背景資料：某電商公司2023年1-6月銷售數(shù)據(jù)（部分如下表），包含字段：月份（A列）、商品類別（B列）、商品名稱（C列）、銷售數(shù)量（D列）、單價（E列）、地區(qū)（F列）。|月份|商品類別|商品名稱|銷售數(shù)量|單價|地區(qū)||------|----------|----------|----------|------|--------||1|家電|空調|120|3500|華北||1|家電|冰箱|80|2800|華南||2|3C數(shù)碼|手機|200|4200|華北||...|...|...|...|...|...|任務要求：（1）計算各商品的月銷售額（銷售額=銷售數(shù)量×單價），并添加“銷售額”列（G列）；，（2）分析“家電”類商品在華北地區(qū)的季度銷售趨勢（1-3月為Q1，4-6月為Q2），要求：①創(chuàng)建數(shù)據(jù)透視表，行標簽為“月份”，列標簽為“商品名稱”，值為“銷售額”求和；②基于數(shù)據(jù)透視表生成折線圖，顯示各家電商品在華北地區(qū)的月度銷售額變化；（3）統(tǒng)計上半年各地區(qū)總銷售額TOP3的商品類別，輸出表格包含“地區(qū)”“商品類別”“總銷售額”“排名”字段；（4）若7月份華北地區(qū)空調預計銷售150臺，單價保持3500元，在不修改原數(shù)據(jù)的情況下，預測華北地區(qū)7月空調銷售額對上半年總銷售額的占比（保留2位小數(shù)）。答案及詳細解析：（1）計算月銷售額在G2單元格輸入公式：=D2*E2→向下填充至數(shù)據(jù)末尾。解析：乘法運算直接使用“*”符號，確?！颁N售數(shù)量”和“單價”均為數(shù)值格式（非文本），否則需用VALUE函數(shù)轉換。（2）家電類華北地區(qū)季度銷售趨勢分析①數(shù)據(jù)透視表創(chuàng)建步驟：篩選數(shù)據(jù)：在“商品類別”列篩選“家電”，在“地區(qū)”列篩選“華北”；插入數(shù)據(jù)透視表（選中篩選后的數(shù)據(jù)區(qū)域→插入-數(shù)據(jù)透視表-選擇放置位置如Sheet2!A1）；字段設置：將“月份”拖入行區(qū)域，“商品名稱”拖入列區(qū)域，“銷售額”拖入值區(qū)域（值字段設置為“求和”）；調整布局：右鍵行標簽-組合-起始月份1，結束月份6，步長1（確保顯示所有月份）。②生成折線圖：選中數(shù)據(jù)透視表任意單元格→插入-圖表-折線圖（推薦帶數(shù)據(jù)標記的折線圖）；圖表標題設置為“華北地區(qū)家電類商品月度銷售額趨勢（2023年1-6月）”；坐標軸設置：X軸為“月份”（1-6月），Y軸為“銷售額（元）”，調整刻度間隔；美化：添加數(shù)據(jù)標簽，調整線條顏色區(qū)分不同商品。常見問題解決：若數(shù)據(jù)透視表未顯示所有月份（如2月無數(shù)據(jù)），需在“數(shù)據(jù)透視表選項-顯示-空行”中勾選顯示空行，或通過“組合”功能強制顯示1-6月。（3）各地區(qū)TOP3商品類別統(tǒng)計①數(shù)據(jù)透視表1（計算地區(qū)+類別總銷售額）：插入數(shù)據(jù)透視表（全選原始數(shù)據(jù)）→行區(qū)域拖入“地區(qū)”和“商品類別”（“地區(qū)”在上，“商品類別”在下），值區(qū)域拖入“銷售額”（求和）；右鍵值字段-值顯示方式-按某一字段匯總的百分比（可不選），保持默認“求和”。②按地區(qū)分組排名：在數(shù)據(jù)透視表右側插入輔助列（如H列），標題為“排