全國計算機(jī)一級等級考試中Excel函數(shù)的重難點(diǎn)分析

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

關(guān)鍵詞：計算機(jī)等級考試;一級MS office;Excel函數(shù)和公式

中圖分類號：TP317? ? ? 文獻(xiàn)標(biāo)識碼：A

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

1 2021年全國計算機(jī)一級考試分析

在2020年12月發(fā)布的考試時間及相關(guān)安排中，發(fā)布了考試環(huán)境更新的通知，一級計算機(jī)基礎(chǔ)的應(yīng)用軟件升級到MS office版（中文專業(yè)版）。

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

1.1 考試難度分析

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

1.2 提高考試優(yōu)秀率分析

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

新題庫新內(nèi)容，為了提高考證優(yōu)秀率，本文將對考證中的操作題Excel中常見函數(shù)進(jìn)行詳細(xì)介紹。

2 Excel重點(diǎn)函數(shù)分析

2.1 多重IF函數(shù)

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

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

2.2 條件函數(shù)

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

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

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

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

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

2.3 VLOOKUP函數(shù)

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

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

VLOOKUP有在原來表查找和選用，還有跨表選用。本實例是原表查找引用。跨表應(yīng)用只需要修改第二個參數(shù)，在查找區(qū)域前添加“工作表名！”。

VLOOKUP函數(shù)經(jīng)常會出現(xiàn)錯誤值N/A，N/A意思是 Not Applicable（不適用，即值不可用），下面提供幾種導(dǎo)致N/A錯誤和對應(yīng)的解決方法[6]。

1） 查找值在查找區(qū)域不存在

當(dāng)查找區(qū)域中沒有查找值，即找不到查找值，就會提示N/A錯誤。

2） 查找區(qū)域引用有誤

按照VLOOKUP的查找規(guī)則，雖然查找區(qū)域內(nèi)容可以多列，但是查找值只會在查找區(qū)域的第一列進(jìn)行查找，所以當(dāng)查找區(qū)域的首列沒有查找值，也會出現(xiàn)N/A錯誤[1]。

3） 查找區(qū)域沒有使用絕對引用

根據(jù)VLOOKUP的查找規(guī)則，查找區(qū)域必須是固定區(qū)域，需要用功能鍵F4固定，即使用絕對引用。沒有固定會導(dǎo)致后面的數(shù)據(jù)在不完整的查找區(qū)域內(nèi)容查詢，最終也會導(dǎo)致N/A錯誤。

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

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

當(dāng)查找值和查找區(qū)域都是規(guī)范的，但是因為查找區(qū)域中沒有查找值，出現(xiàn)了N/A錯誤，頁面顯得不美觀，可以用IFNA（）來解決，嘗試將錯誤值#N/A改為空白。IFNA函數(shù)作用是如果表達(dá)式解析為N/A，則返回指定的值，否則返回表達(dá)式的結(jié)果。

第一個參數(shù)是表達(dá)式，第二個參數(shù)是當(dāng)?shù)谝粋€參數(shù)表達(dá)式的結(jié)果是N/A，則返回指定的值，這里可以設(shè)置為空白，即一對英文雙引號里面是一個空格。

3 結(jié)束語

總之，隨著信息技術(shù)的不斷更新發(fā)展，對學(xué)生的信息技術(shù)素養(yǎng)要求越來越高。計算機(jī)一級考證考查范圍越來越廣，難度也在逐步上升。要求授課老師不斷豐富教學(xué)手段，利用無憂模擬考證軟件精準(zhǔn)輔導(dǎo)，提高學(xué)生考試通過率和優(yōu)秀率。

參考文獻(xiàn)：

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

[2] 李慧毅.淺析全國計算機(jī)等級考試（一級MS Office）中的函數(shù)應(yīng)用[J].現(xiàn)代職業(yè)教育，2020（38）：200-201.

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

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

[5] 余溢多.全國計算機(jī)等級考試一級MS Office上機(jī)學(xué)習(xí)指導(dǎo)[J].計算機(jī)光盤軟件與應(yīng)用，2013，16（2）：133-134.

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

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

【通聯(lián)編輯：謝媛媛】

收稿日期：2021-11-28

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