教师常用Excel函数应用例解

兴胜办公兴胜办公教师常用Excel函数应用例解1.求和（差）方法（1）：=SUM(A2:A10).即将单元格A2—A10中的值加在一起。〔注〕电脑界面在”公式“中直接有，点击可用。拓展：=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中的一起的平均值。〔注〕电脑界面在”公式“中直接有，点击可用。拓展1.=AVERAGE(A2:A10,C2:C10)

.即将单元格A2—A10以及单元格C2—C10中的值一起的平均值，类推。拓展2.在某列中求指定的群体的平均值，如“班别”在B列，成绩在D列，求“1班”的平均分：=SUMIF($B$4:$B$723,"1班",$D$4:$D$723)/AB4.（＄为锁定符，不受拖动、填充的影响，AB4指学生数的单元格，“”要为英文状态，下同。）4.自动排名=RANK(B2,B:B,0)，即B2的值在B列（B:B）中的排序值，公式中的”0“代表降序排序，”1“代表升序排序。若须在指定区域内排序，在（B:B）中加上锁定符＄，如限定在B2—B10单元格中排序，输入为（$B$2:$B$10).5.判断符合指定条件后记为“合格，不合格”=IF（B2>=90,”合格”，”不合格”）.即B2单元格90分以上显示为“合格”，90分以下显示“不合格”。〔注〕显示文字可改动。,“”要为英文状态。6.求符合指定条件的数目（如合格人数）=COUNTIF(C2:C100,”>=90”),即C2—C100中90分以上有多少数目。拓展：求某群体的合格或不合格数目，如求“1班”不合格人数：=SUM(IF(($B$4:$B$723="1班")*($D$4:$D$723<90),1,0))，即B列为班级，D列为成绩。〔注〕公式写完后，同时按ctil+shift+Enter,意为成为数组进行计算，可用于分数段或年龄段的人数统计（后一栏减前一栏来实现）。7.求指定群体的数目如求：“1班“的人数：=COUNTIF(初二!E:E,"1班")，即“初二！”为初二级的学生表，E为班级栏。也可用于统计其它数目，如”辍学“等。拓展：求”1班“中有多少女生：=SUMPRODUCT((初二!E:E=”1班”)*(初二!G:G="女"))〔注〕”1班“要与班级栏中字符要一致,“”要为英文状态。8.求合格率（优秀率）如求合格率：=SUM($D$4:$D$723>=90),1,0)/AB4.即“$D$4:$D$723”为成绩列，AB4为学生数单元格，求优秀率时把“90”改成“120”等。拓展：求指定群体的合格率（优秀率）：如求“1班”的合格率：=SUM(IF(($B$4:$B$723="1班")*($D$4:$D$723>=90),1,0))/AB4，即B列为班级，D列为成绩，AB4为学生数.〔注〕公式写完后，同时按ctil+shift+Enter,意为成为数组进行计算.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))〔注〕公式写完后，同时按ctil+shift+Enter,意为成为数组进行计算.10.求某群体在全体中前m名数目如求“1班”语文科在全级中前50名人数：=SUM(($B$4:$B$723=“1班”)*($D$4:$D$723>=LARGE($D$4:$D$723,50))即B列为班级，D列为语文成绩，“50”为前50名，根据需要可改动。〔注〕公式写完后，同时按ctil+shift+Enter,意为成为数组进行计算.11.查重与比对方法（1）：=IF(COUNTIF(C:C,C3&"*")>1,"重复","")，即核查C3单元格在C列身份证号中是否有重复？有显示”重复“，没有不显示。方法（2）：同行中比对：在另单元格中输入“=A3=B3”,显示”TRUE“为相同，显示”FALSE“为不同。〔注〕若是文字符也可用“条件格式”中的“重复值”显示不同色来比对。12.查找与提取顺向查找提取（查找值在“线索”列的右侧）：=VLOOKUP(L2,B:G,3,0)，即L2为“线索”值，B:G为查找区域，3为从B开始第几列（查找对象），0代表精确查找，〔注〕L2与B列是同类值，要先清空多余字符，否则识别有误。逆向查找提取（查找值在“线索”列的左侧）：=VLOOKUP(K3,IF({1,0},B:B,A:A),2,0)，即“IF（{1，0}），B：B，A：A）”语法意为B列和A列交换位置。“2”代表从“线索”列开始到查找列的列数，“0”表示精确查找.13.同序先增加一辅助列，输入=MATCH(A2,$D$2:$D$213,0)，即A2值在D2—D213中的位置数，然后把数字列按升序排列即可，显示“#N/A”为没有这个值，也可用于比对。14.身份证的核查和提取信息（1）验证格式是否正确：=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)),"正确！","出错啦！"),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,"正确！","出错啦！")),IF(LEN(C5)=15,"老号，请注意！",IF(LEN(C5)=0,"缺号码","位数不对！")))即针对C5单元格的身份证号进行判断。应用修改时选定公式内容全部，在“查找”中“替换”当前单元格即可。（2）提取出生年月日：=MID(C3,7,8)即身份证号在C列，”7“代表从第7个数字，开始提取，”8“代表顺序提取8位数字。可根据需要修改。（3）提取性别：=IF(MOD(MID(C3,17,1),2),"男","女")，即C3单元格的第17位数是单数为男，是双数为女。（4）计算实时年龄：以C3单元格的人为例：=DATEDIF(TEXT(MID(C3,7,INT(LEN(C3)/2-1)),"#-00-00"),TODAY(),"Y")（5）识别身份证号的籍贯=VLOOKUP(--LEFT(C3,6),H:I,2,0)，即C3为身份证单元格，”6“代表身份证号的前6位数字，”H：I“是附上的”县区的代号表“。15.拆分或合并（1）用Ctrl+E智能填充，先在第一行中填写“示范”后，光标在下一格中时同时按Ctrl+E即可。（2）用连接符“＆”，如在C3输入：=A3＆B3，这样C3中就是A3B3合并的内容。16.统一给某列加上或删除字符（1）添加：新增一辅助列，输入要加的内容，然后用连接符“＆”全并成一列，最后复制粘贴（用文本粘贴）即可，删除辅助列。（2）删除：利用查找中的替换功能，查找出要删除的字符，“替换”栏中为空白即可。〔注〕给同一内容添加相同的字符时也可用这一替换方法.17.常用快捷操作（1）快速全选定：同时按Ctil+Shift+↓,或↑或→或←，实现一步选定，（针对大篇幅时快捷用