淺談COUNTIF函數(shù)在教學(xué)管理中的運(yùn)用

章喜洋

摘 要：EXCEL軟件中的COUNTIF函數(shù)對數(shù)據(jù)統(tǒng)計(jì)、條件運(yùn)算具有較強(qiáng)的功能，同時(shí)對文本與數(shù)據(jù)輸入控制、提高工作效率方面也有較大作用，在職業(yè)技術(shù)學(xué)校的教學(xué)管理中得到較廣泛的運(yùn)用，也是高職高專學(xué)生、專業(yè)技術(shù)類和文職文秘類人員應(yīng)掌握的一項(xiàng)專業(yè)技能。

關(guān)鍵詞：COUNTIF函數(shù) 教學(xué)管理 運(yùn)用 公式設(shè)置

中圖分類號：G421 文獻(xiàn)標(biāo)識碼：A 文章編號：1672-3791（2014）06（a）-0192-02

當(dāng)前Excel軟件在職業(yè)技術(shù)學(xué)校的教學(xué)管理工作中得到廣泛的運(yùn)用，而該軟件中COUNTIF函數(shù)對數(shù)據(jù)統(tǒng)計(jì)、條件運(yùn)算和數(shù)據(jù)輸入控制等方面具有較強(qiáng)的功能，靈活運(yùn)用能大大提高教學(xué)管理工作效率。筆者在教學(xué)管理實(shí)踐中對該函數(shù)運(yùn)用進(jìn)行歸納和總結(jié)，供大家分享和提升Excel函數(shù)的運(yùn)用能力。

1 COUNTIF函數(shù)的語法定義

COUNTIF作為Excel軟件中一個單元格條件計(jì)數(shù)函數(shù)，在實(shí)際工作中發(fā)揮著強(qiáng)大的作用。其函數(shù)語法為“COUNTIF（range，criteria）”，Range為需要計(jì)算滿足條件的單元格數(shù)目的單元格區(qū)域；Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。例如：條件可以表示為28、"28"、"〉28"、"Orange"或D3。

2 COUNTIF函數(shù)在教學(xué)管理中運(yùn)用實(shí)例分析

筆者在職業(yè)技術(shù)教學(xué)管理實(shí)踐中，運(yùn)用COUNTIF函數(shù)在學(xué)員學(xué)籍注冊、學(xué)員成績統(tǒng)計(jì)、獎學(xué)金評定、免學(xué)費(fèi)資金申報(bào)等方面發(fā)揮較強(qiáng)的條件統(tǒng)計(jì)功能，同時(shí)對文本與數(shù)據(jù)輸入控制、提高工作效率方面具有較大的作用。以下通過應(yīng)用實(shí)例的分析和問題解決來體現(xiàn)COUNTIF函數(shù)的功能和作用。

2.1 學(xué)員成績分?jǐn)?shù)段人數(shù)統(tǒng)計(jì)

例1：學(xué)校2012級某班級41名學(xué)生，第一學(xué)期各科考試成績?nèi)鐖D1所示，請運(yùn)用EXCEL函數(shù)統(tǒng)計(jì)學(xué)生總分、各科平均分、成績排名，并對各科平均分按“優(yōu)秀、良好、及格和不及格統(tǒng)計(jì)人數(shù)。

操作方法和步驟：（1）可運(yùn)用SUM（D3：J3）、AVERAGE（D3：J3）、RANK（L3，L3：L43）等函數(shù)公式統(tǒng)計(jì)41名學(xué)生的總分、各科平均分和成績排名；（2）單擊Q4、Q5等單元格，在編輯欄內(nèi)輸入“=COUNTIF （L3：L43，">=85"）”按Enter鍵確認(rèn)、“=SUM（COUNTIF（L3：L43，">="&{75，85}）*{1，-1}）”按Enter鍵確認(rèn)，可以計(jì)算出Q4、Q5單元格內(nèi)數(shù)值，如圖1所示，其它分?jǐn)?shù)段人數(shù)可用此方法計(jì)算。

2.2 利用文本信息統(tǒng)計(jì)人數(shù)

例2：學(xué)校在統(tǒng)計(jì)某學(xué)年度學(xué)生免學(xué)費(fèi)補(bǔ)助資金申請的報(bào)表數(shù)據(jù)時(shí)，需根據(jù)各年級專業(yè)學(xué)生人數(shù)，匯總統(tǒng)計(jì)出學(xué)校各專業(yè)學(xué)生申請免學(xué)費(fèi)補(bǔ)助資金金額。

操作方法和步驟：引用一、二、三年級學(xué)生匯總表中的“班級專業(yè)名稱”標(biāo)題下的“專業(yè)名稱”文本信息，運(yùn)用COUNTIF函數(shù)統(tǒng)計(jì)各專業(yè)學(xué)生人數(shù)。操作步驟：（1）打開工作表（圖2所示）單擊選定J10單元格；（2）點(diǎn)擊菜單“插入”項(xiàng)選擇“函數(shù)”項(xiàng)、再點(diǎn)擊“COUNTIF”項(xiàng)，按“確定”彈出COUNTIF函數(shù)框；（3）點(diǎn)出引用工作頁輸入Rang，Criteria二項(xiàng)數(shù)據(jù)值，如圖4所示；或在編輯欄內(nèi)輸入=COUNTIF（一年級匯總表！O6：O1119，"*數(shù)控加工"），按“確定”J10單元格內(nèi)“213”為該專業(yè)人數(shù)。采用同樣的方法可計(jì)算出各年度其他專業(yè)學(xué)生人數(shù)，如圖2所示。

2.3 運(yùn)用COUNTIF函數(shù)功能控制某列數(shù)據(jù)避免重復(fù)錄入

例3：學(xué)校在統(tǒng)計(jì)某學(xué)年度學(xué)生免學(xué)費(fèi)補(bǔ)助資金申請的報(bào)表數(shù)據(jù)時(shí)，學(xué)生名單信息匯總表中的“身份證號碼”和“全國技工院校電子注冊管理系統(tǒng)學(xué)生編號”兩項(xiàng)的數(shù)據(jù)不能重復(fù)，請?jiān)O(shè)置數(shù)據(jù)避免出現(xiàn)重復(fù)錄入。

操作方法和步驟：用條件格式避免重復(fù)，（1）分別選定F、G列，在F6、G6單元格輸入“+身份證號碼和電子注冊管理系統(tǒng)學(xué)生編號”（用'避免出現(xiàn)科學(xué)計(jì)數(shù)法），點(diǎn)格式"->"條件格式"，分別將條件設(shè)成公“=COUNTIF（$F：$F，$F$6）>1、=COUNTIF（$G：$G，$G$6）>1”，點(diǎn)"格式"->"字體"->"顏色"，選定紅色后點(diǎn)兩次"確定"；（2）再用格式刷將F6、G6單元格的條件格式向下復(fù)制。這樣設(shè)定好后，如果出現(xiàn)數(shù)據(jù)重復(fù)則字體將會變成紅色，如圖3所示。

2.4 運(yùn)用COUNTIF函數(shù)標(biāo)識兩列數(shù)據(jù)不同之處

例4：在EXCEL表格中如何把B列與A列不同之處標(biāo)識出來？

操作方法和步驟：（1）如果是要求A、B兩列的同一行數(shù)據(jù)相比較：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”→“條件格式”，將條件設(shè)為“單元格數(shù)值” “不等于”=B2，點(diǎn)擊“格式”→“字體”→“顏色”，選中紅色，點(diǎn)擊兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制，B列可參照此方法設(shè)置。（2）如果是A列與B列整體比較（即相同數(shù)據(jù)不在同一行）：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”→“條件格式”，將條件設(shè)為“公式”=COUNTIF（$B：$B，$A2）=0，點(diǎn)“格式”→“字體”→“顏色”，選中紅色，點(diǎn)兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制，B列可參照此方法設(shè)置。（3）按以上方法設(shè)置后，AB列均有的數(shù)據(jù)不著色，A列有B列無或者B列有A列無的數(shù)據(jù)標(biāo)記為紅色字體。

3 COUNTIF函數(shù)運(yùn)用的公式設(shè)置歸納總結(jié)

根據(jù)COUNTIF函數(shù)在教學(xué)管理工作中的運(yùn)用，體現(xiàn)出該函數(shù)的功能和作用，為熟練掌握其功能，歸納總結(jié)出該函數(shù)的公式設(shè)置并靈活運(yùn)用。

3.1 求各種類型單元格數(shù)量的函數(shù)公式設(shè)置

（1）求真空單元格數(shù)量設(shè)置：=COUNTIF （data，"="），真空+假空單元格數(shù)量：=COUNTIF（data，""）相當(dāng)COUNTBLANK（）函數(shù)，非真空單元格數(shù)量：=COUNTIF（data，"<>"）相當(dāng)于COUNTA（）函數(shù)。endprint

（2）求文本型單元格數(shù)量設(shè)置：=COUNTIF （data，"*"），假空單元格也是文本型單元格，區(qū)域內(nèi)所有單元格數(shù)量：COUNTIF（data，"<>"""），邏輯值為TRUE的單元格數(shù)量： =COUNTIF（data，TRUE）。

3.2 求大于或小于等于某個值、單元格數(shù)據(jù)的函數(shù)公式設(shè)置

（1）求大于50、等于50、小于等于50單元格的值設(shè)置為：=COUNTIF（data，">50"）、COUNTIF（data，50）、COUNTIF（data，"<=50"）。

（2）求大于等于E5（單元格）、等于E5、小于E5單元格的值設(shè)置為：=COUNTIF （data，">="&$E$5）、COUNTIF（data，$E$5）、COUNTIF（data，"<"&$E$5）。

3.3 等于或包含某N個特定字符的單元格數(shù)量的函數(shù)公式設(shè)置

在EXECL中查找和替換時(shí)，代表任意單個字符，*代表任意多個字符。如果要將工作表中的"？"和"*"替換成其他字符，就只能在查找框中輸入～？～和～*～才能正確替換。

（1）如“兩個字符、兩個字符且第2個是B和包含B”的函數(shù)設(shè)置為：=COUNTIF （data，"？？"）、COUNTIF（data，"？B"）和COUNTIF（data，"*B*"）。

（2）如“包含D3單元格的內(nèi)容、第2字是D3單元格的內(nèi)容、"你好"”的函數(shù)設(shè)置為：=COUNTIF（data，"*"&D3&"*"）、=COUNTIF（data，"？"&D3&"*"）、 =COUNTIF（data，"你好"）。

3.4 兩個條件求數(shù)量的函數(shù)公式設(shè)置

（1）對大于10且小于等于15、大于等于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，">" &{10，15}）*{1，-1}）？、=SUM（COUNTIF（data，">="&{10，15}）*{1，-1}）。

（2）？對大于等于10且小于等于<=15、對大于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，{">=10"，">15"}）*{1，-1}）；=SUM（COUNTIF（data，{">10"，">=15"}）*{1，-1}）。{注：一般多條件計(jì)數(shù)使用SUMPRODUCT函數(shù)，以上方法較少使用，僅供參考}

4 結(jié)語

通過對COUNTIF函數(shù)在教學(xué)管理工作中實(shí)例運(yùn)用的分析、函數(shù)公式設(shè)置的分類和匯總，能體現(xiàn)Excel函數(shù)具有強(qiáng)大統(tǒng)計(jì)運(yùn)算、分析匯總等功能。由掌握“條件統(tǒng)計(jì)函數(shù)”并靈活運(yùn)用而窺豹一斑，因此，可增強(qiáng)學(xué)習(xí)Excel軟件的興趣，掌握該專業(yè)技能，為提高工作效率發(fā)揮更大作用。

參考文獻(xiàn)

[1] 王俊京，杜壘.Excel中If和Countif函數(shù)在教學(xué)中的應(yīng)用[J].產(chǎn)業(yè)與科技論壇. 2011（5）：160，185.

[2] 張敏.基于EXCEL函數(shù)的學(xué)生成績分析與管理[J].農(nóng)業(yè)網(wǎng)絡(luò)信息.2006（5）：157-158.

[3] 張冬梅.巧用countif（ ）函數(shù)錄入新生信息[J].電腦知識與技術(shù)（經(jīng)驗(yàn)技巧），2012（10）：39-40.

[4] 曹秋仙.巧用Excel中的If和Countif函數(shù)[J].福建電腦，2013（5）：153-154.

[5] 鄭帥.Countif函數(shù)在Excel中的絕妙使用[J].科技資訊，2013（15）：11.endprint

（2）求文本型單元格數(shù)量設(shè)置：=COUNTIF （data，"*"），假空單元格也是文本型單元格，區(qū)域內(nèi)所有單元格數(shù)量：COUNTIF（data，"<>"""），邏輯值為TRUE的單元格數(shù)量： =COUNTIF（data，TRUE）。

3.2 求大于或小于等于某個值、單元格數(shù)據(jù)的函數(shù)公式設(shè)置

（1）求大于50、等于50、小于等于50單元格的值設(shè)置為：=COUNTIF（data，">50"）、COUNTIF（data，50）、COUNTIF（data，"<=50"）。

（2）求大于等于E5（單元格）、等于E5、小于E5單元格的值設(shè)置為：=COUNTIF （data，">="&$E$5）、COUNTIF（data，$E$5）、COUNTIF（data，"<"&$E$5）。

3.3 等于或包含某N個特定字符的單元格數(shù)量的函數(shù)公式設(shè)置

在EXECL中查找和替換時(shí)，代表任意單個字符，*代表任意多個字符。如果要將工作表中的"？"和"*"替換成其他字符，就只能在查找框中輸入～？～和～*～才能正確替換。

（1）如“兩個字符、兩個字符且第2個是B和包含B”的函數(shù)設(shè)置為：=COUNTIF （data，"？？"）、COUNTIF（data，"？B"）和COUNTIF（data，"*B*"）。

（2）如“包含D3單元格的內(nèi)容、第2字是D3單元格的內(nèi)容、"你好"”的函數(shù)設(shè)置為：=COUNTIF（data，"*"&D3&"*"）、=COUNTIF（data，"？"&D3&"*"）、 =COUNTIF（data，"你好"）。

3.4 兩個條件求數(shù)量的函數(shù)公式設(shè)置

（1）對大于10且小于等于15、大于等于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，">" &{10，15}）*{1，-1}）？、=SUM（COUNTIF（data，">="&{10，15}）*{1，-1}）。

（2）？對大于等于10且小于等于<=15、對大于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，{">=10"，">15"}）*{1，-1}）；=SUM（COUNTIF（data，{">10"，">=15"}）*{1，-1}）。{注：一般多條件計(jì)數(shù)使用SUMPRODUCT函數(shù)，以上方法較少使用，僅供參考}

4 結(jié)語

通過對COUNTIF函數(shù)在教學(xué)管理工作中實(shí)例運(yùn)用的分析、函數(shù)公式設(shè)置的分類和匯總，能體現(xiàn)Excel函數(shù)具有強(qiáng)大統(tǒng)計(jì)運(yùn)算、分析匯總等功能。由掌握“條件統(tǒng)計(jì)函數(shù)”并靈活運(yùn)用而窺豹一斑，因此，可增強(qiáng)學(xué)習(xí)Excel軟件的興趣，掌握該專業(yè)技能，為提高工作效率發(fā)揮更大作用。

參考文獻(xiàn)

[1] 王俊京，杜壘.Excel中If和Countif函數(shù)在教學(xué)中的應(yīng)用[J].產(chǎn)業(yè)與科技論壇. 2011（5）：160，185.

[2] 張敏.基于EXCEL函數(shù)的學(xué)生成績分析與管理[J].農(nóng)業(yè)網(wǎng)絡(luò)信息.2006（5）：157-158.

[3] 張冬梅.巧用countif（ ）函數(shù)錄入新生信息[J].電腦知識與技術(shù)（經(jīng)驗(yàn)技巧），2012（10）：39-40.

[4] 曹秋仙.巧用Excel中的If和Countif函數(shù)[J].福建電腦，2013（5）：153-154.

[5] 鄭帥.Countif函數(shù)在Excel中的絕妙使用[J].科技資訊，2013（15）：11.endprint

（2）求文本型單元格數(shù)量設(shè)置：=COUNTIF （data，"*"），假空單元格也是文本型單元格，區(qū)域內(nèi)所有單元格數(shù)量：COUNTIF（data，"<>"""），邏輯值為TRUE的單元格數(shù)量： =COUNTIF（data，TRUE）。

3.2 求大于或小于等于某個值、單元格數(shù)據(jù)的函數(shù)公式設(shè)置

（1）求大于50、等于50、小于等于50單元格的值設(shè)置為：=COUNTIF（data，">50"）、COUNTIF（data，50）、COUNTIF（data，"<=50"）。

（2）求大于等于E5（單元格）、等于E5、小于E5單元格的值設(shè)置為：=COUNTIF （data，">="&$E$5）、COUNTIF（data，$E$5）、COUNTIF（data，"<"&$E$5）。

3.3 等于或包含某N個特定字符的單元格數(shù)量的函數(shù)公式設(shè)置

在EXECL中查找和替換時(shí)，代表任意單個字符，*代表任意多個字符。如果要將工作表中的"？"和"*"替換成其他字符，就只能在查找框中輸入～？～和～*～才能正確替換。

（1）如“兩個字符、兩個字符且第2個是B和包含B”的函數(shù)設(shè)置為：=COUNTIF （data，"？？"）、COUNTIF（data，"？B"）和COUNTIF（data，"*B*"）。

（2）如“包含D3單元格的內(nèi)容、第2字是D3單元格的內(nèi)容、"你好"”的函數(shù)設(shè)置為：=COUNTIF（data，"*"&D3&"*"）、=COUNTIF（data，"？"&D3&"*"）、 =COUNTIF（data，"你好"）。

3.4 兩個條件求數(shù)量的函數(shù)公式設(shè)置

（1）對大于10且小于等于15、大于等于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，">" &{10，15}）*{1，-1}）？、=SUM（COUNTIF（data，">="&{10，15}）*{1，-1}）。

（2）？對大于等于10且小于等于<=15、對大于10且小于15的函數(shù)設(shè)置為：=SUM（COUNTIF（data，{">=10"，">15"}）*{1，-1}）；=SUM（COUNTIF（data，{">10"，">=15"}）*{1，-1}）。{注：一般多條件計(jì)數(shù)使用SUMPRODUCT函數(shù)，以上方法較少使用，僅供參考}

4 結(jié)語

通過對COUNTIF函數(shù)在教學(xué)管理工作中實(shí)例運(yùn)用的分析、函數(shù)公式設(shè)置的分類和匯總，能體現(xiàn)Excel函數(shù)具有強(qiáng)大統(tǒng)計(jì)運(yùn)算、分析匯總等功能。由掌握“條件統(tǒng)計(jì)函數(shù)”并靈活運(yùn)用而窺豹一斑，因此，可增強(qiáng)學(xué)習(xí)Excel軟件的興趣，掌握該專業(yè)技能，為提高工作效率發(fā)揮更大作用。

參考文獻(xiàn)

[1] 王俊京，杜壘.Excel中If和Countif函數(shù)在教學(xué)中的應(yīng)用[J].產(chǎn)業(yè)與科技論壇. 2011（5）：160，185.

[2] 張敏.基于EXCEL函數(shù)的學(xué)生成績分析與管理[J].農(nóng)業(yè)網(wǎng)絡(luò)信息.2006（5）：157-158.

[3] 張冬梅.巧用countif（ ）函數(shù)錄入新生信息[J].電腦知識與技術(shù)（經(jīng)驗(yàn)技巧），2012（10）：39-40.

[4] 曹秋仙.巧用Excel中的If和Countif函數(shù)[J].福建電腦，2013（5）：153-154.

[5] 鄭帥.Countif函數(shù)在Excel中的絕妙使用[J].科技資訊，2013（15）：11.endprint