教師常用Excel函數(shù)應(yīng)用例解

興勝辦公興勝辦公教師常用Excel函數(shù)應(yīng)用例解1.求和（差）方法（1）：=SUM(A2:A10).即將單元格A2—A10中的值加在一起。〔注〕電腦界面在”公式“中直接有，點(diǎn)擊可用。拓展：=SUM(A2:A10,C2:C10)

.即將單元格A2—A10以及單元格C2—C10中的值加在一起的和，類推。方法（2）：也可直接輸入“=A1+A2-B3+C4.....”,回車Enter即可。2.求乘除乘法：輸入：=A2?B2除法：輸入：=A2/B2，并用：=A2?B2/C2，類推。3.求平均值=AVERAGE(A2:A10).即將單元格A2—A10中的一起的平均值?！沧ⅰ畴娔X界面在”公式“中直接有，點(diǎn)擊可用。拓展1.=AVERAGE(A2:A10,C2:C10)

.即將單元格A2—A10以及單元格C2—C10中的值一起的平均值，類推。拓展2.在某列中求指定的群體的平均值，如“班別”在B列，成績(jī)?cè)贒列，求“1班”的平均分：=SUMIF($B$4:$B$723,"1班",$D$4:$D$723)/AB4.（＄為鎖定符，不受拖動(dòng)、填充的影響，AB4指學(xué)生數(shù)的單元格，“”要為英文狀態(tài)，下同。）4.自動(dòng)排名=RANK(B2,B:B,0)，即B2的值在B列（B:B）中的排序值，公式中的”0“代表降序排序，”1“代表升序排序。若須在指定區(qū)域內(nèi)排序，在（B:B）中加上鎖定符＄，如限定在B2—B10單元格中排序，輸入為（$B$2:$B$10).5.判斷符合指定條件后記為“合格，不合格”=IF（B2>=90,”合格”，”不合格”）.即B2單元格90分以上顯示為“合格”，90分以下顯示“不合格”。〔注〕顯示文字可改動(dòng)。,“”要為英文狀態(tài)。6.求符合指定條件的數(shù)目（如合格人數(shù)）=COUNTIF(C2:C100,”>=90”),即C2—C100中90分以上有多少數(shù)目。拓展：求某群體的合格或不合格數(shù)目，如求“1班”不合格人數(shù)：=SUM(IF(($B$4:$B$723="1班")*($D$4:$D$723<90),1,0))，即B列為班級(jí)，D列為成績(jī)。〔注〕公式寫完后，同時(shí)按ctil+shift+Enter,意為成為數(shù)組進(jìn)行計(jì)算，可用于分?jǐn)?shù)段或年齡段的人數(shù)統(tǒng)計(jì)（后一欄減前一欄來實(shí)現(xiàn)）。7.求指定群體的數(shù)目如求：“1班“的人數(shù)：=COUNTIF(初二!E:E,"1班")，即“初二！”為初二級(jí)的學(xué)生表，E為班級(jí)欄。也可用于統(tǒng)計(jì)其它數(shù)目，如”輟學(xué)“等。拓展：求”1班“中有多少女生：=SUMPRODUCT((初二!E:E=”1班”)*(初二!G:G="女"))〔注〕”1班“要與班級(jí)欄中字符要一致,“”要為英文狀態(tài)。8.求合格率（優(yōu)秀率）如求合格率：=SUM($D$4:$D$723>=90),1,0)/AB4.即“$D$4:$D$723”為成績(jī)列，AB4為學(xué)生數(shù)單元格，求優(yōu)秀率時(shí)把“90”改成“120”等。拓展：求指定群體的合格率（優(yōu)秀率）：如求“1班”的合格率：=SUM(IF(($B$4:$B$723="1班")*($D$4:$D$723>=90),1,0))/AB4，即B列為班級(jí)，D列為成績(jī)，AB4為學(xué)生數(shù).〔注〕公式寫完后，同時(shí)按ctil+shift+Enter,意為成為數(shù)組進(jìn)行計(jì)算.9.求最大值或最小值最大值：=MAX(D2:D100).即D2—D100中最大值最小值：=MIN(D2:D100).即D2—D100中最大值拓展：求某群體的最大值或最小值，如求“1班”的最大值或最小值：最大值：=MAX(IF($B$4:$B$723="1班",$D$4:$D$723))最小值：=MIN(IF($B$4:$B$723="1班",$D$4:$D$723))〔注〕公式寫完后，同時(shí)按ctil+shift+Enter,意為成為數(shù)組進(jìn)行計(jì)算.10.求某群體在全體中前m名數(shù)目如求“1班”語文科在全級(jí)中前50名人數(shù)：=SUM(($B$4:$B$723=“1班”)*($D$4:$D$723>=LARGE($D$4:$D$723,50))即B列為班級(jí)，D列為語文成績(jī)，“50”為前50名，根據(jù)需要可改動(dòng)?！沧ⅰ彻綄懲旰?，同時(shí)按ctil+shift+Enter,意為成為數(shù)組進(jìn)行計(jì)算.11.查重與比對(duì)方法（1）：=IF(COUNTIF(C:C,C3&"*")>1,"重復(fù)","")，即核查C3單元格在C列身份證號(hào)中是否有重復(fù)？有顯示”重復(fù)“，沒有不顯示。方法（2）：同行中比對(duì)：在另單元格中輸入“=A3=B3”,顯示”TRUE“為相同，顯示”FALSE“為不同?！沧ⅰ橙羰俏淖址部捎谩皸l件格式”中的“重復(fù)值”顯示不同色來比對(duì)。12.查找與提取順向查找提?。ú檎抑翟凇熬€索”列的右側(cè)）：=VLOOKUP(L2,B:G,3,0)，即L2為“線索”值，B:G為查找區(qū)域，3為從B開始第幾列（查找對(duì)象），0代表精確查找，〔注〕L2與B列是同類值，要先清空多余字符，否則識(shí)別有誤。逆向查找提取（查找值在“線索”列的左側(cè)）：=VLOOKUP(K3,IF({1,0},B:B,A:A),2,0)，即“IF（{1，0}），B：B，A：A）”語法意為B列和A列交換位置。“2”代表從“線索”列開始到查找列的列數(shù)，“0”表示精確查找.13.同序先增加一輔助列，輸入=MATCH(A2,$D$2:$D$213,0)，即A2值在D2—D213中的位置數(shù)，然后把數(shù)字列按升序排列即可，顯示“#N/A”為沒有這個(gè)值，也可用于比對(duì)。14.身份證的核查和提取信息（1）驗(yàn)證格式是否正確：=IF(LEN(C5)=18,IF(RIGHT(C5,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(C5,18))*7+LEFT(RIGHT(C5,17))*9+LEFT(RIGHT(C5,16))*10+LEFT(RIGHT(C5,15))*5+LEFT(RIGHT(C5,14))*8+LEFT(RIGHT(C5,13))*4+LEFT(RIGHT(C5,12))*2+LEFT(RIGHT(C5,11))*1+LEFT(RIGHT(C5,10))*6+LEFT(RIGHT(C5,9))*3+LEFT(RIGHT(C5,8))*7+LEFT(RIGHT(C5,7))*9+LEFT(RIGHT(C5,6))*10+LEFT(RIGHT(C5,5))*5+LEFT(RIGHT(C5,4))*8+LEFT(RIGHT(C5,3))*4+LEFT(RIGHT(C5,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(C5,1)),"正確！","出錯(cuò)啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(C5,18))*7+LEFT(RIGHT(C5,17))*9+LEFT(RIGHT(C5,16))*10+LEFT(RIGHT(C5,15))*5+LEFT(RIGHT(C5,14))*8+LEFT(RIGHT(C5,13))*4+LEFT(RIGHT(C5,12))*2+LEFT(RIGHT(C5,11))*1+LEFT(RIGHT(C5,10))*6+LEFT(RIGHT(C5,9))*3+LEFT(RIGHT(C5,8))*7+LEFT(RIGHT(C5,7))*9+LEFT(RIGHT(C5,6))*10+LEFT(RIGHT(C5,5))*5+LEFT(RIGHT(C5,4))*8+LEFT(RIGHT(C5,3))*4+LEFT(RIGHT(C5,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(C5,1))*1,"正確！","出錯(cuò)啦！")),IF(LEN(C5)=15,"老號(hào)，請(qǐng)注意！",IF(LEN(C5)=0,"缺號(hào)碼","位數(shù)不對(duì)！")))即針對(duì)C5單元格的身份證號(hào)進(jìn)行判斷。應(yīng)用修改時(shí)選定公式內(nèi)容全部，在“查找”中“替換”當(dāng)前單元格即可。（2）提取出生年月日：=MID(C3,7,8)即身份證號(hào)在C列，”7“代表從第7個(gè)數(shù)字，開始提取，”8“代表順序提取8位數(shù)字?？筛鶕?jù)需要修改。（3）提取性別：=IF(MOD(MID(C3,17,1),2),"男","女")，即C3單元格的第17位數(shù)是單數(shù)為男，是雙數(shù)為女。（4）計(jì)算實(shí)時(shí)年齡：以C3單元格的人為例：=DATEDIF(TEXT(MID(C3,7,INT(LEN(C3)/2-1)),"#-00-00"),TODAY(),"Y")（5）識(shí)別身份證號(hào)的籍貫=VLOOKUP(--LEFT(C3,6),H:I,2,0)，即C3為身份證單元格，”6“代表身份證號(hào)的前6位數(shù)字，”H：I“是附上的”縣區(qū)的代號(hào)表“。15.拆分或合并（1）用Ctrl+E智能填充，先在第一行中填寫“示范”后，光標(biāo)在下一格中時(shí)同時(shí)按Ctrl+E即可。（2）用連接符“＆”，如在C3輸入：=A3＆B3，這樣C3中就是A3B3合并的內(nèi)容。16.統(tǒng)一給某列加上或刪除字符（1）添加：新增一輔助列，輸入要加的內(nèi)容，然后用連接符“＆”全并成一列，最后復(fù)制粘貼（用文本粘貼）即可，刪除輔助列。（2）刪除：利用查找中的替換功能，查找出要?jiǎng)h除的字符，“替換”欄中為空白即可?！沧ⅰ辰o同一內(nèi)容添加相同的字符時(shí)也可用這一替換方法.17.常用快捷操作（1）快速全選定：同時(shí)按Ctil+Shift+↓,或↑或→或←，實(shí)現(xiàn)一步選定，（針對(duì)大篇幅時(shí)快捷用