資源簡介

基礎鞏固9（Excel表格處理）
考點梳理
1.Excel表格界面
2.電子表格軟件Excel的主要功能是存儲和處理數據。根據應用需求，可通過計算、排序 、篩選、圖表等方法對數據進行分析。Excel文件格式（擴展名）為xlsx。
Excel常用快捷工具圖標含義：
3.公式的編輯：必須等號“=”開頭，乘號為*，除號為/，括號（）。乘號不能省略，不能用中括號[]。比如公式“=（（A2+B2）/2）-5”，不能寫成“=[（A2+B2）/2]-5”。如果單元格格式被設置成“文本”，輸入公式會無效（被當做文本字符）。
4.Excel常用的函數有 Sum 、 Average 、Max、Min等。多個不連續區域中間用逗號分隔，比如“=Sum(B2:D2,F2)”
5.編輯的公式引用的空白單元格，空白單元格當做0處理。比如公式“=A2/B2”，如果B2為空白單元格，此時除數為0，會出現“#DIV/O!”錯誤提示；而函數計算時會自動忽略非數字類型單元格和空白單元格。
如下圖，在E2輸入公式“= Average(B2:D2)”計算結果為33。
6.選擇性粘貼
①粘貼（公式和格式）②粘貼值（計算結果）③粘貼公式④行列置換⑤粘貼格式⑥粘貼鏈接
7.Excel常見錯誤
8.Excel相對引用與絕對應用
9.數據排序：將對應數據區域安照相關條件進行排序，注意主要關鍵字和次要關鍵字。
10.數據篩選：分自動篩選和高級篩選。篩選功能是將滿足一定條件的部分數據保留下來，不滿足條件的數據隱藏起來。注意相關關鍵詞：不足、超過、超出、不夠以及10個最大的值的使用。
11.數據分類匯總：對數據分析研究的方法，可使數據按照不同的類別進行求和、求平均值及個數等。
12.圖表的創建及應用：數據區域、數據分析及圖表編輯和修改。常見圖表類型：柱形圖、折線圖、條形圖、餅圖、散點圖、面積圖等。
13.圖表和數據區域中內容
改變圖表區域中某個單元格內容，圖表對應內容也會自動更新。圖表數據區域內的數據排序后，圖表中也會跟著變化。篩選后，不符合篩選條件的信息圖表上不會顯示。圖表數據區域外插入整行或整列，圖表不會變化。
【注意】
公式必須以等號開頭；
（2）使用自動填充的操作要注意是否需要使用絕對引用；
（3）排序區域必須是單一連續區域，不能選擇合并單元格
（4）數據篩選時不同列之間的條件沒有關聯，也不分先后順序，最終結構為各列條件篩選后的交集；
（5）圖表數據源要注意根據圖例確定是否包含標題區域，且最終的圖表數據源應該是一個左右、上下對稱的區域。如A2:B7,E2:F7，避免出現形如A3:B7,E2:F7的答案。
二、常見錯題
例1.小俞用Excel軟件處理查詢得到的“近幾年全國居民人均收入支出統計”數據，如圖a所示：
圖a
請回答下列問題：
（1）參照J3單元格的公式計算J7單元格的值，并用自動填充功能完成區域J8:J14的計算，則J7單元格中的公式為 。
（2）若要對類別為“支出”的數據按“2020年”進行降序排序，則排序的數據區域是 。
（3）根據圖a中數據制作的圖表如圖b所示，以下說法正確的是： （多選，填字母）。
圖b
A. 近幾年，衣、食、住三項支出中，居住消費支出的增長是最緩慢的
B. 選中A1:J1單元格，執行“合并后居中”操作后，該單元格名稱為A1
C. 若在圖a所示工作表中，以“類別”為支出、“2020年”為最大3項進行篩選，則篩選出的記錄有3條
D. 若在圖a所示工作表的C列前插入一列，并輸入2013年的相關數據，J列的數值不會發生改變
（注：全部選對的得2 分，選對但不全的得1 分，不選或有選錯的得0 分）
【答案】
=I7/SUM(I$7:I$14)*100 或 =I7/SUM($I$7:$I$14)*100 （2）A7:J14 或 A7:I14 （3）BD
【解析】（1）本題考查單元格絕對應用和單元格計算。從編輯欄可以看出是計算2020收入占各指標占比，從I7：I14開始計算支出占比，因此需要對支出和進行絕對應用，=I7/SUM($I$7:$I$14)*100，由于按列填充，因此I列可
以不加$。（2）本題考查數據排序范圍選擇。在選擇排序范圍是，范圍盡可能的大，但是要去掉不需要的區間和合并單元格的部分。本題針對支出部分數據進行排序，因此只要選擇支出部分數據區域A7:J14。（3）A項：從圖表可以分析得到食品煙酒消費支出增長緩慢； B項：合并單元格后的名稱是合并區域的第一個單元名稱，所以對的； C項：先以“類別”為支出以“類別”為支出、“2020年”為最大3項進行篩選，則篩選出的記錄有0條，C項是錯誤；D項：在c列前插入一列，C列后面的列好會自動變化，公式也是發生變化，不影響計算結果。
例2.收集某中學兩個班級體質健康測試數據，并對部分項目用Excel軟件進行數據處理，如圖a所示。
圖a
請回答下列問題：
（1）區域G3：G82的數據是通過公式計算得到的，在G3單元格中輸入公式 ，再使用自動填 充功能完成區域G4:G82的計算。（提示：體重指數=體重/身高2，單位：千克/米2）。
（2）對區域A2:G82進行相關數據分析，下列說法正確的是 （多選，填字母）。
A.更新C列身高數據，G列體重指數的值會隨之改變
B.為了將女生身高最高的數據顯示在當前工作表第3行，可選擇身高為主要關鍵字、性別為次要關
鍵字均按降序排序
C.為了顯示男生1000/800米得分大于85的數據，可先對“性別”進行篩選，選擇的值為“男”，再
對“1000/800米得分”進行篩選，條件為大于85
D.為了顯示女生肺活量得分最高的3位學生數據，可先對“性別”進行篩選，選擇的值為“女”，再
對“肺活量得分”進行篩選，選擇“10個最大的值”并顯示最大的3項
（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
（3）對圖a中的數據進行相關統計，結果放在新工作表中，如圖b所示。根據圖b中的數據制作的圖表
如圖c所示。創建該圖表的數據區域是 。
圖b 圖c
【答案】
= D3/(C3 * C3) * 10000 或 =D3/C3^2 * 10000或等價表達式 （2）AC （3）A2:A6,D2:E6
【解析】
直接根據提示信息書寫公式，書寫方式比較多，等價表達式均可。特別注意單位換算（厘米、米）。
（2）A.G列數據由包含C列單元格的公式計算得到，故更新C列數據，G列值會隨之改變。B.身高為主要關鍵字降序，則身高最高的同學（不管男女）會被排在最前面，只有身高相同才會按次要關鍵字性別降序。主次要關鍵字交換，才能實現題目要求。C.多種篩選條件，分步對每個條件進行單獨篩選，各自篩選結果的交集，即是篩選最終結果。D.分析同C。需要注意的是，后續篩選仍然是對整列而非上一篩選結果進行的。該選項的操作結果是“既是女生、肺活量又排在全體前三的”，而非“女生中肺活量排名前三的”。如果肺活量得分最高的3項全是男生，則該操作篩選結果為0條數據。
（3）該題比較常規，根據圖表橫坐標和圖例即可寫出數據區域。特別注意每列單元格數量要一致，不要漏掉A2。
三、精選練習
1.小吳收集了近三年全國工業產能利用率的數據，并用 Excel 軟件進行數據處理，如第1題圖a所示。
第1題圖a
（1）如第1題圖a所示，區域C5:C18的數據由C4單元格公式自動填充得到，且結果正確。為了計算2019和2020年主要行業的產能利用率排名，小吳將C4:C18區域復制到E4:E18和G4:G18區域，但發現計算結果有誤，則C4單元格的公式應修改為 ，并重新進行自動填充和復制操作。
（2）根據第1題圖a中數據制作的圖表如第1題圖b所示，則創建該圖表的數據區域為A3:B5, 。
第1題圖b
為了獲得名稱包含“制造”的主要行業中，2018年“產能利用率(%)”最大的5項數據，并顯示在操作結果的最前面（緊鄰第3行），一種可行的操作是：選擇數據區域A3:G18后，對該區域 （選填2個序號，按操作順序）。
①按“主要行業”進行文本篩選，條件為：包含“制造”②按“主要行業”進行文本篩選，條件為：等于“*制造”
③按2018年的“產能利用率(%)” 進行篩選，選擇“10個最大的值”并顯示最大的5項
④以2018年的“排名”為關鍵字進行降序排序 ⑤以2018年的“產能利用率(%)”為關鍵字進行降序排序
（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
2.用 Excel軟件對“浙江省新高考體藝類招生計劃表”的相關數據進行處理，如第2題圖a所示。
第2題圖a
請回答下列問題：
（1）區域M3:O4的數據是通過公式計算得到的，在M3單元格（計算2020年藝術類計劃人數占總計的百分比）中輸入公式 ，先自動填充至M4，再使用自動填充功能完成區域N3:O4的計算（提示：各類占比=該類合計人數/總計）。
（2）根據第2題圖a中數據制作的圖表如第2題圖b所示，則創建該圖表的數據區域是 。
第2題圖b
（3）對第13題圖a所示工作表中的數據進行操作處理，下列說法正確的有 （多選，填字母）。
A.設置了M3單元格的格式為“數值”、“兩位小數”
B.在正確完成M3:O4計算后，若不小心將B列除，則原M3:04單元格中的數據保持不變
C.對表中藝術類第二批各類統考數據（數據區域為A6:J13）按“列H”為主要關鍵字降序排序，則圖b所示的圖表也會隨之發生變化
D.若要篩選出藝術類第二批的數據，可以選擇A2:J3區域，對“批次名稱”設置篩選條件為“包含：第二批”進行篩選（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
3.某校一個班學生成績的統計數據，用Excel軟件進行數據處理，如第3題圖a所示。請回答下列問題：
第3題圖a
請回答下列問題：
(1)第3題圖a中 D50:J50 單元格計算男同學的各科平均分，E50:J50 的值是通過D50 單元格自動填充功能得到，可知D50單元格公式為___________。
(提示: AVERAGEIF函數返回滿足條件的單元格的平均值。例如:=AVERAGEIF(C3:C48,"女",F3:F48)，返回所有女生的英語平均分）
(2)根據第3題圖a中的數據，制作了一張部分學生總分圖表，如第13題圖b所示，則制作該圖表的數據區域是___________________。
第3題圖b
(3)下列有關對圖表和數據表操作描述正確的是________（多選）。
A.刪除第13題圖a表中“學號”所在的A列，不影響第13圖b所示圖表效果
B.I49單元格中是全班地理平均分，可以用 =AVERAGE（I3:I48）公式計算得到
C.要對全班成績按總分排序，可以僅選擇K1:K48區域進行排序
D.要篩選出全班總分最高的女同學，可以先以“性別”等于“女”篩選，再以“總分”最大的1項篩選得到
4.小林老師收集了全國各地區近5年普通高中畢業生數的數據，并使用Excel軟件對數據進行處理，如圖a所示。
圖a
請回答下列問題：
（1）區域I3:I7的數據來自區域B7:F7，首先選擇區域B7:F7復制，再選擇I3單元格單擊右鍵，出現如圖b所示選項，要實現以上效果，應選擇粘貼選項的序號是 。
圖b 圖c
（2）區域J3:J6的數據是通過公式計算得到的，在J3單元格中輸入公式，并使用自動填充功能完成區域J4:J6的計算，則J3單元格中的公式是 。（提示：增長率=（當年畢業生數 - 去年畢業生數）/ 去年畢業生數）
（3）利用SUM函數在第34行計算每年全國普通高中的畢業生總數，進行了某種排序操作后，創建了如圖c所示圖表。結合圖a和圖c，下列說法正確的有 （多選，填字母）。
A.由圖c可知畢業生總數最多的是2015年，最少的是2017年
B.若不小心刪除了一個省的畢業生人數，圖表中對應的數據條將變低
C.選擇區域A2:F33，以“2019年”為關鍵字進行降序排序，圖表會發生改變
D.選擇區域A2:F33，按“地區”篩選值包含“自治區”，不會影響圖表內容
E.創建圖表前的排序操作可能是選擇B2:F34區域，以“行34”為關鍵字按行進行排序
（注：全部選對的得 2 分，選對但不全的得 1 分，不選或有選錯的得 0 分）
5.小張收集了某校圖書館借閱排行TOP300的相關數據，并用Excel軟件進行處理，如圖a所示：
圖a
（1）為計算圖a中的“流通次數”和“預約次數”，小張已利用公式正確計算了“流通次數”，他希望通過修改K3單元格的公式，利用自動填充得到“預約次數”L3:L7區域中正確的數據，則單元格K3中的公式應修改為 。（提示:SUMIF函數用于求滿足條件的單元格之和，例如=SUMIF(E3:E302,J3,G3:G302)用于求單元格區域E3:E302中與單元格J3中值相等的對應單元格區域G3:G302中的值之和。）
（2）根據第13題圖a中數據制作了圖表如圖b所示，則創建圖表的數據區域 。
圖b
（3）根據第13題圖a所示，下列說法正確是 （多選題）
A.若表格已排序，則主要關鍵字是“流通人次”，次要關鍵字是“預約人次”
B.若以“類型”為文學，以“流通人次”最大的5項進行篩選，得到的記錄數可能大于等于5條
C.因誤操作將“價格”所在列刪除，則“流通次數”和“預約次數”的數據將不能正確計算
D.若要找出文學類流通人次大于10次的書籍，則可以利用“類型”為文學，“流通人次”大于10進行篩選
（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
6．用 Excel軟件對“某公司銷售業績”的相關數據進行處理，如圖a所示。其中貨物編號的規則：型號（第1、2個字符）+ “-”（第3個字符）+年份（4~7個字符）+月份（第8、9個字符）+7位貨品編號。
圖b
圖a 圖c
請回答如下問題：
區域C3:G14的數據是通過公式計算得到的，在C3單元格中輸入公式 ，先自動填充至G3，再使用自動填充功能完成區域C4:G18的計算（提示：銷售金額= 目標數量*單價）
若要篩選出2020年10月所有型號的銷售數據，則第13題圖b中加框處應輸入 。
對第13題圖a所示工作表中的數據進行操作處理，下列說法正確的有 （多選，填字母）。
A.正確完成（1）的計算后，不小心刪除了A列，則區域C3:G18的數據不會發生變化。
B.因為有合并單元格，無法對任何數據進行排序操作
C.要完成（2）的操作，還可以設置篩選條件為“包含：202010”
D.要完成第13題圖c所示圖表，選擇的數據區域B2:G2, B19:G19
（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
7.某校一次考試的成績數據用Excel軟件進行處理，如圖所示。
圖a
請回答下列問題：
（1）區城M3:M172的數據是通過公式計算得到的，在M3單元格中輸入公式 ，再使用自動填充功能對區域M4:M172進行填充。
（2）為了實現1班的總分從高到低排序，下列對區域A2:M172的操作可行的是 （多選，填字母）。
A.先按“班級”為關鍵字升序排序，再按“總分”為關鍵字降序排序
B.以“班級”為主要關鍵字升序、“總分”為次要關鍵字降序進行排序
C.以“總分”為主要關鍵字降序、 “班級”為次要關鍵字升序進行排序
D.先按“班級”篩選。選擇值為“1班”，再按“總分”進行降序排序
（注：全部選對的得2分，選時但不全的得1分，不選或有選錯的得0分）
（3）若表格數據已按班級升序排序，要找出2班總分最高分，可在指定單元格用公式“=max( )”實現。
8.使用Excel軟件處理某超市進銷存數據，如圖a所示。
圖a
請回答下列問題：
（1）區域H3:H357的數據通過公式計算得到：在H3單元格中輸入公式 ，再使用自動填充功能 完成區域H4:H357的計算。（提示：利潤 =（商品售價-商品進價）× 銷量）
（2）圖a中“糧油類”商品編碼數據“101717”誤輸入為“10177”，“糧油類”商品按“商品編碼”升序 排序后，發現“10177”位于“101720”之后，其原因是B355、B356單元格中的數據是 類型。
圖b 圖c
（3）完成圖a中的各類商品總利潤計算之后，制作了如圖b所示的圖表。下列說法正確的有 (多選，填字母)。
A.總利潤最高的商品是水果類
B.對區域E3:G357數據格式設置為“保留1位小數”，“利潤”列數據保持不變
C.在圖a所示工作表中按“商品名稱”篩選所有花生油，圖c中①處可選擇“包含”
D.對蔬菜類以“銷量”為關鍵字進行升序排序，則選擇的排序數據區域為A2:H130
(注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分)
9.小俞用Excel軟件處理查詢得到的“近幾年全國居民人均收入支出統計”數據，如第9題圖a所示：
第9題圖a
請回答下列問題：
（1）參照J3單元格的公式計算J7單元格的值，并用自動填充功能完成區域J8:J14的計算，則J7單元格中的公式為 。
（2）若要對類別為“支出”的數據按“2020年”進行降序排序，則排序的數據區域是 。
（3）根據圖a中數據制作的圖表如圖b所示，以下說法正確的是： （多選，填字母）。
第9題圖b
A. 近幾年，衣、食、住三項支出中，居住消費支出的增長是最緩慢的
B. 選中A1:J1單元格，執行“合并后居中”操作后，該單元格名稱為A1
C. 若在圖a所示工作表中，以“類別”為支出、“2020年”為最大3項進行篩選，則篩選出的記錄有3條
D. 若在圖a所示工作表的C列前插入一列，并輸入2013年的相關數據，J列的數值不會發生改變
（注：全部選對的得2 分，選對但不全的得1 分，不選或有選錯的得0 分）
10.某中學用Excel軟件對高一部分學生體測數據進行統計分析，如第10題圖a所示：
第10題圖a
（1）第10題圖a中，為計算各班級各項目平均分，首先在H4單元格輸入公式,再向下并向右填充完成各班各項目平均分的計算，發現結果不正確，公式應修改為 。
（提示：AVERAGEIFS函數用于對一定區域中滿足指定條件的單元格求平均值。以第10題圖a中的數據為例，=AVERAGEIFS(C3:C88,A3:A88,G4)表示計算A3:A88區域中班級為1901的跳繩平均分）
（2）若要根據立定跳遠得分對整個年級進行降序排序，應選擇的排序區域為 。
第10題圖b
（3）小李根據第13題圖a制作了1901班仰臥起坐得分對比如13題圖b所示。下列說法正確的是 （多選,填字母）。
A.創建該圖表的區域為：B2:B9，E2:E9
B.若要篩選出1902班跳繩得分最高的三個同學，可以先對“班級”按“1902”進行篩選，再對“跳繩得分”按“10個最大的值”中最大3項進行篩選
C.若在C列前插入一列，圖表不會發生變化
D.若修改C、D、E列數值格式保留1位小數，則圖表會發生變化
（注：全部選對的得2分，選對但不全的得1分，不選或有選錯的得0分）
11.為了響應全民健身計劃,某高中對學生進行了體質健康測評,并用Excel軟件進行數據處理，如第13題圖a所示。
圖a
請回答下列問題：
區域M2:P15 的數據是通過公式計算得到的，在M2單元格中輸入公式后，再使用自動填充功能完成該區域的計算，則N3單元格中的公式是“=SUMPRODUCT((______________________)*($I$3:$I$553=N$1))”。
（提示：M2單元格輸入公式=SUMPRODUCT((A1:A100=”2”)*(I1:I100=”優秀”))，表示同時滿足A1:A100是2班和I1:I100是優秀這兩個條件的情況有幾個，也就是進行條件計數。）
（2）根據第13題圖a中的數據制作的圖表如第13題圖b所示。創建該圖表的數據區域是_______________ 圖b
（3）對圖a工作表進行相關數據分析，下列說法正確的是_________（多選，填字母）。
A.以“總分等級”為依據進行升序排序，選擇的區域是A3：I553
B.選擇“A3：I553”按照“總分等級”升序排序后，圖 b 的圖表不會發生改變
C.現需要選出總分等級為“不及格”的學生，可選擇區域A3：I553，以“總分等級”為“不及格”進行篩選并保留結果
D.為了顯示男生立定跳遠得分最高的3位學生數據，可先對“性別”為“男”進行篩選，再對“立定跳遠”進行篩選，選擇最大的3項
12.某品牌銷售部員工收集了2021年第一季度各銷售網點銷售數據，并使用Excel軟件處理數據，如圖a所示。請回答下列問題：
第2題圖a
（1）區域G3:G16的數據是在G3的單元格中輸入公式，然后利用自動填充功能得到的，則G3單元格中
的公式是 。（提示：2021年第二季度比2020年同期增長率=（2021年第二季度銷售額－2020年第二季度銷售額）/ 2020年第二季度銷售額*100）
（2）為了更加直觀地分析2021年第二季度東北地區銷售情況走勢，建立如圖b所示的圖表。則生成該圖表的數據區域是 。
第2題圖b
（3）結合圖a，下列說法正確的是 （多選，填字母）
A.對列F進行篩選，篩選條件設置為“最大3項”，圖b中的圖表不會發生變化
B.若要選出華中地區5月銷售冠軍，應選B7:G10為排序區域，并以“五月銷售額”為關鍵字
C.圖a中標題單元格是選中A1:G1設置“合并后居中”得到
D.若將C3: F16域的數據設置為0位小數，會影響G列數據的值
E.若不慎將F列單元格數據清除，則G列單元格中將顯示#DIV/0!
（注：全部選對的得2分，選對但不全對的得1分，不選或有選錯的得0分）
答案
1.【答案】（1）=RANK(B4, B$4:B$18) （2）D3:D5,F3:F5 （3）①⑤ 或 ⑤① 或1 5 或 5 1
2.【答案】（1）= H14 / H$16 （2）L2:L4,N2:O4 （3）BD
3.【答案】（1）=AVERAGEIF($C3:$C48,"男",D3:D48) （2）B39:B48,K39:K48 （3）AB
4.【答案】（1）④ （2）=（I3-I4）/I4 （3）ABE
5.【答案】（1）=SUMIF($E$3:$E$302,$J3,G$3:G$302) （2）J4:K7 （3）BD
6.【答案】（1）=$B3*C$19 或 =C$19*$B3 （2） 202010 或 T -202010 或 T 202010 （3）AD
7.【答案】（1） =sum(C3:L3) （2） BD （3） M31:M60
8.【答案】（1）=(E3-F3)*G3 （2）文本 （3）BC
9.【答案】（1）=I7/SUM(I$7:I$14)*100 或 =I7/SUM($I$7:$I$14)*100 （2）A7:J14 或 A7:I14（3）BD
10.【答案】（1）=AVERAGEIFS(C$3:C$88,$A$3:$A$88,$G4) （2）A2:E88 或 A3:E88 （3）AC
11.【答案】（1）$A$3:$A$553=$K3 （2）M1:P1,M16:P16 （3）AB
12.【答案】（1）=(SUM(C3:E3)－F3)/F3* 100 （2）B2:E2,B11:E13 （3）BCE

展開更多......

收起↑