全國計算機一級等級考試中Excel函數的重難點分析

摘要：隨著信息時代的展開和計算機的遍布，Excel在各行各業中的使用越來越普遍。Excel以超強處理數據能力和豐富的繪圖表能力，受到各行業人員的重視和青睞，掌握Excel，可以讓你的工作事半功倍。因此數據處理成了中等職業學校學生必備的一項技能。計算機一級等級證書更是納入廣東省職業院校高考證書，其中Excel電子表格操作題在計算機一級等級考試中所占分值20分，是所有操作題中最難的。所以想要考證結果優秀，掌握該部分內容至關重要。該部分主要考查函數和公式的應用，條件格式，表格樣式等，其中函數是重點。

關鍵詞：計算機等級考試;一級MS office;Excel函數和公式

中圖分類號：TP317? ? ? 文獻標識碼：A

文章編號：1009-3044（2022）07-0128-02

1 2021年全國計算機一級考試分析

在2020年12月發布的考試時間及相關安排中，發布了考試環境更新的通知，一級計算機基礎的應用軟件升級到MS office版（中文專業版）。

2021年3月27日，全國計算機等級考試如期舉行，某中職學校信息技術部高一學生參加了本次考試。信息技術部20級共4個班，學生人數共計186人，無人缺考。其中175人順利通過本次考試，通過率為94%，保持高水平，36人考試成績達到90分以上，優秀率達19%。

1.1 考試難度分析

根據考試組織方公布數據顯示：本次考試得分率最低的模塊是：Excel電子模塊。新環境下，電子表格模塊新增了多個條件的平均值和求和函數，多套題目涉及多重IF函數和VLOOKUP函數，很多學生無從下手，導致后面的圖表和篩選等操作都沒法繼續做。有68%的學生電子表格部分得分為0，電子表格模塊難度總體上升。

1.2 提高考試優秀率分析

考證的另外兩個模塊，Word和PPT雖然題量大，知識點多，但是只要熟悉軟件界面便可得分。Excel卻要求學生必須理解透徹函數，是一個學以致用的過程。同時題目常分兩部分，第二部分的數據處理必須在第一部分完成基礎上完成，所以第一部分的函數至關重要。Excel雖然難度大，但是反而容易得分，因為知識點相對集中分值大。所以想要等級考試結果優秀，學習好Excel函數是關鍵。

新題庫新內容，為了提高考證優秀率，本文將對考證中的操作題Excel中常見函數進行詳細介紹。

2 Excel重點函數分析

2.1 多重IF函數

通過分析題庫內容，得知條件格式和多重IF函數作為考點出現的頻率最高，多重IF函數最多的時候涉及5個條件，所以難度較大，需要加強訓練，除了題庫中涉及的6道題，還需要另外添加練習題目，幫助學生理解多重IF的解題思路，同時建議將題庫中涉及的6道多重IF題目做成微課，時長10分鐘，讓學生有針對性地鞏固學習。學生只有多練習，才能掌握本類型題目的解題技巧，才能從容作答。下面以無憂模擬題庫中第10套題為例，對本函數進行講解。

本題中績效評分有5個等級，需要用4重IF函數，即4個IF函數[1]。跟剝洋蔥的方法相似，先從最小值開始判斷，在F3單元格寫入=IF（C3>=60，"2000"，"800"），第一個IF函數用于判斷績效評分是否大于等于60，其中第一個參數是判斷條件，第二個參數是條件正確返回的值，即2000，第三個參數是條件不正確返回的值，即800。第二個IF函數用于判斷績效評分是否大于等于70，第一個參數是c3>=70，第二個參數是當表達式正確時返回值，即4000，復制第一個IF函數作為第三個參數，即c3<70時的返回值。即在F3單元格寫入=IF（C3>=70，4000，IF（C3>=60，2000，800））。第三個IF函數用于判斷績效評分是否大于等于80，當表達式正確時返回6000，作為第二個參數，第三個參數是績效評分小于80，即第二個IF函數。用同樣的方法推導第三個IF函數用于判斷績效評分是否大于等于90，F3單元格最終結果是=IF（C3>=90，8000，IF（C3>=80，6000，IF（C3>=70，4000，IF（C3>=60，2000，800））））。通過上面的例子可知，多重IF函數的關鍵是第一個IF函數，通常從最小值開始比較，每一重IF函數的參數都是由上一重函數組成。

2.2 條件函數

另外一個出現頻率較高的函數是sumif、countif和averageif[2]。這三個是帶條件的求和、計數、求平均可放在一起對比學習，前提是掌握sum、count和average這三個基礎函數。在基礎函數之上添加條件，條件在第2個參數，對應是填寫Criteria選項。在新題庫中添加countifs和averageifs兩個函數，是countif和averageif的進階要求，條件從原來的一個變成了多個，一個原則是條件對應在Criteria選項，條件所在列在本條件的上面選項即Criteria_Range，需要用功能鍵F4固定。條件和條件所在列都是成對呈現。Sum_range和average_range是對數據求和或求平均所在的列，對應一列數據。

如圖1的K3單元格，求一組，二組和三組人數，可用countif函數[3]。光標放在k3單元格，在countif函數的第一個參數寫$B$2：$B$100，第二個參數是條件，即J3，其中第一個參數是條件所在的“組別”列，即B列。技巧是條件所在列在條件上方。且條件所在列需要用功能鍵F4固定。

如圖1的L3單元格，求各組獎金平均值，可用averageif函數。光標放在L3單元格，在averageif函數的第一個參數range寫$B$2：$B$100，即條件所在的組別列;第二個參數Criteria是條件，即J3;第三個參數Average_range填$F$2：$F$100，即獎金列，同樣需要用功能鍵F4固定。

如圖1的K9單元格，求各組工資等級A的人數，條件有2個，分別為小組和等級，可用countifs函數[4]。光標放在K9單元格，在countifs函數的第一個參數Criteria_range1寫$B$2：$B$100;第二個參數Criteria1是條件1，即J9;第三個參數Criteria_range2是條件2所在列工資等級列，即$H$2：$H$100;第四個參數Criteria2，是條件2，即“A”。

通過上面的countif、averageif和countifs三個函數的對比，可以得出條件所在列在條件上方，掌握這三個函數的關鍵是把條件找出來，問題就迎刃而解。因此可知，averageifs和countifs函數一樣，只是averageifs多了第5個參數，求數據的平均值。

2.3 VLOOKUP函數

另外一個重點考查函數是VLOOKUP[5]，10道模擬題中出現5次，并且這個函數比較難理解，分為精確查找和模糊查找，因此在本文將重點介紹該函數。VLOOKUP是查找選用函數，主要是在某個區域查找某個值，并把該值所在行中某個數據返回。此函數有四個參數：lookup_value是查找值，是一個數;table_array是查找區域，至少包含兩列數據，需要用功能鍵F4固定;第三個Col_index_num是引用列，是一個數，即引用列在查找區域第N列;Range_lookup是0或者1，在計算機一級等級考試中只考查精確查找，所以是填1。本函數重點是把握第二個參數，是查找區域，至少包含查找列和引用列。因為所有的查找值都是在該查找區域的第一列查找，所以查找列在該查找區域中的第一列且需要用功能鍵F4進行固定。下面以無憂模擬題庫中第8套題為例，對本函數進行講解[5]。

如圖2，根據“產品單品價格表”填寫“某公司員工銷售情況表”中的“產品單價”列。通過分析得知，本題是通過產品名稱找產品單價，因此查找值為“某公司員工銷售情況表”中B3單元格，即第一參數為B3;第二個參數為查找范圍：$G$3：$H$7，即在“產品單品價格表”中的第一列“產品名稱”列找查找值，找到了返回對應的產品單價，因此第二個參數需要包括產品名稱和產品單價兩列;第三個參數是返回列在查找區域第N列，在本實例中產品單價在查找區域$G$3：$H$7是第二列，因此第三個參數填寫2;第4個參數是1，精確查找，只有當“某公司員工銷售情況表”的產品名稱和“產品單品價格表”中的產品名稱一樣，才能把單價返回“某公司員工銷售情況表”C列的產品單價。

VLOOKUP有在原來表查找和選用，還有跨表選用。本實例是原表查找引用?？绫響弥恍枰薷牡诙€參數，在查找區域前添加“工作表名！”。

VLOOKUP函數經常會出現錯誤值N/A，N/A意思是 Not Applicable（不適用，即值不可用），下面提供幾種導致N/A錯誤和對應的解決方法[6]。

1） 查找值在查找區域不存在

當查找區域中沒有查找值，即找不到查找值，就會提示N/A錯誤。

2） 查找區域引用有誤

按照VLOOKUP的查找規則，雖然查找區域內容可以多列，但是查找值只會在查找區域的第一列進行查找，所以當查找區域的首列沒有查找值，也會出現N/A錯誤[1]。

3） 查找區域沒有使用絕對引用

根據VLOOKUP的查找規則，查找區域必須是固定區域，需要用功能鍵F4固定，即使用絕對引用。沒有固定會導致后面的數據在不完整的查找區域內容查詢，最終也會導致N/A錯誤。

4） 查找值有空格，或是兩者單元格格式不一樣

VLOOKUP要求查找值跟查找區域內的數據必須一模一樣，不能有空格，更不能兩者格式不一樣，否則表面看起來很相似，但是還是會出現N/A錯誤[7]。

當查找值和查找區域都是規范的，但是因為查找區域中沒有查找值，出現了N/A錯誤，頁面顯得不美觀，可以用IFNA（）來解決，嘗試將錯誤值#N/A改為空白。IFNA函數作用是如果表達式解析為N/A，則返回指定的值，否則返回表達式的結果。

第一個參數是表達式，第二個參數是當第一個參數表達式的結果是N/A，則返回指定的值，這里可以設置為空白，即一對英文雙引號里面是一個空格。

3 結束語

總之，隨著信息技術的不斷更新發展，對學生的信息技術素養要求越來越高。計算機一級考證考查范圍越來越廣，難度也在逐步上升。要求授課老師不斷豐富教學手段，利用無憂模擬考證軟件精準輔導，提高學生考試通過率和優秀率。

參考文獻：

[1] 鄭忠秀.淺談“全國計算機一級等級考試”中Excel函數的應用技巧[J].飲食科學，2017（24）：176.

[2] 李慧毅.淺析全國計算機等級考試（一級MS Office）中的函數應用[J].現代職業教育，2020（38）：200-201.

[3] 吳巧平.“項目學習”在昆明實驗中學初中信息技術教學中的實證研究——以《初中數據統計》為例[D].昆明：云南師范大學，2013.

[4] 劉中旭.淺談全國計算機等級考試一級計算機基礎及MS Office中常見函數的應用[J].商情，2017（33）：169.

[5] 余溢多.全國計算機等級考試一級MS Office上機學習指導[J].計算機光盤軟件與應用，2013，16（2）：133-134.

[6] 龍錦萍，邵菲.淺析如何提高全國計算機等級考試一級MS Office的過關率[J].好家長，2017（54）：225.

[7] 殷文俊.淺談Excel中的幾個常用函數——以安徽考區計算機一級考試為例[J].考試周刊，2016（A5）：6-7.

【通聯編輯：謝媛媛】

收稿日期：2021-11-28

作者簡介：陳少燕（1989—），女，廣東潮州人，中學一級，本科，研究方向為計算機應用。