Excel函数名师优质课获奖市赛课一等奖课件

日常EXCEL函数应用第1页EXCEL操作界面及设置函数与公式通用技巧惯用函数第2页Whereisit？EXCEL操作界面及设置在“视图”选项卡中，经过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏、行号列标、工作表标签等。“公式”选项卡中，有“公式审核”、“保护”等惯用函数公式。第3页Whereisit？右键单击工具栏→“自定义快速访问工具栏”→弹出自定义快速访问工具栏选项卡，能够设置自定义访问工具栏。EXCEL操作界面及设置第4页EXCEL操作界面及设置函数与公式通用技巧惯用函数第5页在工作表或工作簿中移动和滚动快捷键

按下

完成箭头键 向上、下、左或右移动单元格CTRL+箭头键

移动到当前数据区域边缘CTRL+HOME 移动到工作表开头CTRL+END 移动到工作表最终一个单元格（右下角）CTRL+PAGEDOWN 移动到工作簿中下一个工作表CTRL+PAGEUP

移动到工作簿中前一个工作表CTRL+F6或CTRL+TAB

移动到下一工作簿或窗口F5 或CTRL+G 显示“定位”对话框SHIFT+F5或CTRL+F 显示“查找”对话框SHIFT+F4

重复上一次“查找”操作（等同于“查找下

一个”）TAB

在保护工作表中非锁定单元格之间移动函数与公式通用技巧第6页选择单元格、行或列快捷键

请按

假如要SHIFT+箭头键

将选定区域扩展一个单元格宽度CTRL+SHIFT+箭头键

选定区域扩展到活动单元格同一行或同 一列最终一个非空白单元格SHIFT+HOME

将选定区域扩展到行首CTRL+SHIFT+HOME

将选定区域扩展到工作表开始CTRL+SHIFT+END

将选定区域扩展到表最终一个使用单

元格CTRL+A 选定整个工作表SHIFT+PAGEDOWN

将选定区域向下扩展一屏SHIFT+PAGEUP 将选定区域向上扩展一屏函数与公式通用技巧第7页插入工作表、图表、宏快捷键

请按

假如要SHIFT+F11或ALT+SHIFT+F1

插入新工作表F11或ALT+F1

创建使用当前区域图表ALT+F8 显示“宏”对话框ALT+F11 显示“VisualBasic编辑器”函数与公式通用技巧第8页符号说明实例优先次序：_(空格)，引用运算符=SUM(A1:B10)区域运算符=SUM(A1:B5A4:D9)交叉运算符，相当于=SUM(A4:B5)=RANK(A1,(A1:A10,C1:C10))联合运算符1+

-*

/算术运算符=5+7-9/3*42&文本运算符=“Excel”&”home”返回”Excelhome”3=

<>>>=<<=比较运算符=(A1=A2)判断A1与A2相等=(B1<>”ABC”)判断B1≠”ABC”=(C1>=5)判断C1≥54括号能够改变运算优先级别，多组括号进行嵌套是，计算次序是由最内层括号逐层向外进行运算。实例函数与公式通用技巧第9页符号说明实例*通配符，表示任何字符=COUNTIF(A:A，“北京*有限企业”)?通配符，表示任何单个字符=COUNTIF(A:A，“北京*有限??企业”)~通配符，用于解除字符通配性=COUNTIF(A:A，”北京~*有限企业”)函数与公式通用技巧第10页限制：突破：

使用定义名称。当参数为单元格区域引用时，可使用一对括号来包含多个单元格区域引用部分形成合并区域引用作为函数参数。1、提前将单元格格式设置为文本后再输入数字2、在输入数字时用半角单引号开头。公式内容长度不能超出1024个字符；公式中函数嵌套不能超出7层；公式中函数参数不能超出30个；数字计算精度为15位。函数与公式通用技巧第11页改变工作表计算精度在实际工作中，许多用户在计算数字时会设置单元格格式为2为小数数值、货币格式或者是整数值。但在使用公式进行数据处理过程中，经常会发觉几个单元格之和与累计单元格显示数据之和不相等。因为excel将数值按照15位计算精度带入计算所致。如图，在excel选项中勾选“将精度设为所显示精度”，能够处理上述情况发生，但这么也降低了工作薄中全部工作表计算精度，但不影响其它工作薄。

另外，也能够用ROUND函数对数值进行修约后再求和。函数与公式通用技巧第12页分步查看公式计算结果方法一：选中包含公式单元格，单击“公式”选项卡中“公式求值”按钮，弹出“公式求值”对话框，然后屡次单击“求值”按钮，“求值”域中将按照公式计算次序逐步地显示公式计算过程。方法二：

<F9>功效键除了能够重算工作薄外，还能够用于查看公式运算结果。

当一个函数公式比较长时，能够用<F9>键对各个部分进行分段计算，方便公式差错和更正。在分段选择时，注意要包含一个完整运算对象。按<F9>功效键切换函数与公式通用技巧第13页EXCEL操作界面及设置函数与公式通用技巧惯用函数第14页

函数语法

由函数名+括号+参数组成例：求和函数:SUM(A1,B2,…)

参数与参数之间用逗号“

,

”表示惯用函数第15页1.条件函数——IF、IFERROR依据对指定条件计算结果为TRUE或FALSE，返回不一样结果。能够使用IF对数值和公式执行条件检测。MicrosoftExcel还提供了其它一些函数，它们可依据条件来分析数据。比如，假如要计算某单元格区域内某个文本字符串或数字出现次数，则可使用COUNTIF和COUNTIFS电子表格函数。若要计算基于某区域内一个文本字符串或一个数值总和，可使用SUMIF和SUMIFS工作表函数。惯用函数第16页ISERROR()

括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或#NULL时为TRUEISNA()

括号中为：#N/A时为TRUE

常与IF嵌套使用条件函数——IF、IFERROR惯用函数第17页①

②=IFERROR(value,value_if_error)假如公式计算犯错误则返回您指定值；不然返回公式结果。使用IFERROR函数来捕捉和处理公式中错误。Value

是需要检验是否存在错误参数。Value_if_error

是公式计算犯错误时要返回值。计算得到错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。条件函数——IF、IFERROR惯用函数第18页2.逻辑函数——AND、OR全部参数计算结果为TRUE时，返回TRUE；只要有一个参数计算结果为FALSE，即返回FALSE。经过AND函数能够检验多个不一样条件，而不但仅是一个条件。=AND(logical1,[logical2],...)=OR(logical1,[logical2],...)在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数逻辑值为FALSE，即返回FALSE。注意：参数计算结果必须是逻辑值（如TRUE或FALSE），而参数必须是包含逻辑值数组假如数组或引用参数中包含文本或空白单元格，则这些值将被忽略。假如指定单元格区域未包含逻辑值，则函数将返回错误值#VALUE!。惯用函数第19页逻辑函数AND(条件)

括号中条件值均为TRUE，则为TRUE；假如任一个为FALSE，则为FALSEOR(条件)

括号中条件值均为FALSE，则为FALSE；假如任一个为TRUE，则为TRUENOT(条件)

括号中条件值为FALSE，则为TRUE；假如为TRUE，则为FALSE惯用函数第20页3.统计函数——LARGE、SMALL=LARGE(array,k)返回数据集中第k个最大值。使用此函数能够依据相对标准来选择数值。Array

为需要从中选择第k个最大值数组或数据区域。K

为返回值在数组或数据单元格区域中位置（从大到小排）。注解假如数组为空，函数LARGE返回错误值#NUM!。假如k≤0或k大于数据点个数，函数LARGE返回错误值#NUM!。假如区域中数据点个数为n，则函数LARGE(array,1)返回最大值，函数LARGE(array,n)返回最小值。=SMALL(array,k)返回数据集中第k个最小值。惯用函数第21页4.统计函数——MAX、MIN=MAX(number1,number2,...)返回一组值中最大值。注解参数能够是数字或者是包含数字名称、数组或引用。逻辑值和直接键入到参数列表中代表数字文本被计算在内。假如参数为数组或引用，则只使用该数组或引用中数字。数组或引用中空白单元格、逻辑值或文本将被忽略。假如参数不包含数字，函数MAX返回0（零）。假如参数为错误值或为不能转换为数字文本，将会造成错误。假如要使计算包含引用中逻辑值和代表数字文本，请使用MAXA函数。=MIN(number1,number2,...)返回一组值中最小值。=MEDIAN(number1,number2,...)返回给定数值中值。中值是在一组数值中居于中间数值。=MODE(number1,number2,...)返回在某一数组或数据区域中出现频率最多（众数）数值。惯用函数第22页5.统计函数——AVERAGE=TRIMMEAN(array,percent)返回数据集内部平均值。函数TRIMMEAN先从数据集头部和尾部除去一定百分比数据点，然后再求平均值。当希望在分析中剔除一部分数据计算时，能够使用此函数。=AVERAGE(number1,[number2],...)返回参数平均值（算术平均值）。注释：参数能够是数字或者是包含数字名称、单元格区域或单元格引用。逻辑值和直接键入到参数列表中代表数字文本被计算在内。假如区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值单元格将被计算在内。假如参数为错误值或为不能转换为数字文本，将会造成错误。若要在计算中包含引用中逻辑值和代表数字文本，请使用AVERAGEA函数。若要只对符合一些条件值计算平均值，请使用AVERAGEIF函数或AVERAGEIFS函数。惯用函数第23页AVERAGE(数值1,数值2,…)ABC11002010002200403300603000

=AVERAGE（A1：A3）=200=AVERAGE（A1：A3，B1）=105统计函数——AVERAGE惯用函数第24页6.统计函数——AVERAGEIF=AVERAGEIF(range,criteria,average_range)注解：忽略区域中包含TRUE或FALSE单元格。假如average_range中单元格为空单元格，AVERAGEIF将忽略它。假如range为空值或文本值，则AVERAGEIF会返回#DIV0!错误值。假如条件中单元格为空单元格，AVERAGEIF就会将其视为0值。假如区域中没有满足条件单元格，则AVERAGEIF会返回#DIV/0!错误值。您能够在条件中使用通配符，即问号(?)和星号(*)。Average_range无须与range大小和形状相同。求平均值实际单元格是经过使用average_range中左上方单元格作为起始单元格，然后加入与range大小和形状相对应单元格确定。range

是要计算平均值一个或多个单元格，其中包含数字或包含数字名称、数组或引用。criteria

是数字、表示式、单元格引用或文本形式条件，用于定义要对哪些单元格计算平均值。比如，条件能够表示为32、"32"、">32"、"苹果"或B4。average_range

是要计算平均值实际单元格集。假如忽略，则使用range。惯用函数第25页求和:SUMSUM(数值1,数值2,…)

A1=6A2=7A3=8=SUM(6,8)=14=SUM(A1,A3)=6+8=14=SUM(A1:A3)=6+7+8=147.数学函数——SUM惯用函数第26页数学函数——SUM=SUM(number1,number2,...)说明直接键入到参数表中数字、逻辑值及数字文本表示式将被计算，请参阅下面示例一和示例二。假如参数是一个数组或引用，则只计算其中数字。数组或引用中空白单元格、逻辑值或文本将被忽略。请参阅下面第三个示例。假如参数为错误值或为不能转换为数字文本，将会造成错误。惯用函数第27页条件求和:SUMIFSUMIF(范围,条件,要求和范围)ABC11002010002200403300603000=SUMIF(A1:A3,“>=200”,B1:B3)=100=SUMIF(A1:A3,“>=200”,C1:C3)=50008.数学函数——SUMIF惯用函数第28页9.统计函数——COUNT=COUNT(value1,[value2],...)注解假如参数为数字、日期或者代表数字文本（比如，用引号引发数字，如"1"），则将被计算在内。逻辑值和直接键入到参数列表中代表数字文本被计算在内。假如参数为错误值或不能转换为数字文本，则不会被计算在内。假如参数为数组或引用，则只计算数组或引用中数字个数。不会计算数组或引用中空单元格、逻辑值、文本或错误值。若要计算逻辑值、文本值或错误值个数，请使用COUNTA函数。若要只计算符合某一条件数字个数，请使用COUNTIF函数或COUNTIFS函数。惯用函数第29页求个数:COUNTCOUNT(数值1,数值2,…)ABC11002010002200403300603000

=COUNT(A1:A3)=3

=COUNT(B1:B3)=3=COUNTIF(range,criteria)计算某个区域中满足给定条件单元格数。比如：=COUNTIF(A2:A7,"<=20")惯用函数第30页求符合条件个数:COUNTIFCOUNTIF(范围,条件)ABC11002010002200403300603000

=COUNTIF(A1:A3,“>=200”）=2=COUNTIF(A1:A3,“>200”

）=110.统计函数——COUNTIF惯用函数第31页11.文本函数——LEFT、MID、RIGHT=LEFT(text,num_chars)=MID(text,start_num,num_chars)=RIGHT(text,num_chars)Text

是包含要提取字符文本字符串。Start_num

是文本中要提取第一个字符位置。文本中第一个字符start_num为1，以这类推。Num_chars

指定希望函数从文本中返回字符个数。依据所指定字符数，LEFT返回文本字符串中第一个字符或前几个字符。MID返回文本字符串中从指定位置开始特定数目标字符，该数目由用户指定。RIGHT依据所指定字符数返回文本字符串中最终一个或多个字符。惯用函数第32页文本函数截取函数LEFTRIGHTMIDLEFT(文本,数值)

从左边截取RIGHT(文本,数值)

从右边截取MID(文本,开始位，数值)

从中间截取

=LEFT（“abcdef”,2）=ab=RIGHT（“abcdef”,2）=ef=MID（“abcdef”,2,3）=bcd惯用函数第33页12.文本函数——CONCATENATE=CONCATENATE(text1,text2,...)将两个或多个文本字符串合并为一个文本字符串。注解您也可使用连接符号(&)计算运算符代替CONCATENATE函数来连接文本项。比如=A1&B1与=CONCATENATE(A1,B1)返回相同值。惯用函数第34页计算字符长度LENLEN(文本)

计算字符串长度

=LEN（“abcdef”）=6合并字符函数CONCATENATE或&CONCATENATE(文本1，…)

合并字符串

=CONCATENATE（“a”,“ef”）=aef惯用函数第35页将数值转化为文本TEXTTEXT(数值,参数)

将数值转化为文本,参数普通为0.

=TEXT（1234,0）=1234还可用于补0惯用函数第36页在字符串中查找特定字符FINDFIND(文本,范围,数值)

查找一个字符在另一个字符串中位置数值表示查找第几个.=FIND（“a”,“abcaef”,1）=1=FIND（“a”,“abcaef”,2）=4惯用函数第37页比较两个字符是否完全相符EXACTEXACT(文本1,文本2)

比较两个字符是否完全相符,是则为TRUE,不然为FALSE.=EXACT（“a”,“a”）=TRUE

=EXACT（“a”,“ab”）=FALSE

=EXACT（“a”,“A”）=FALSE惯用函数第38页文本函数——SUBSTITUTE=SUBSTITUTE(text,old_text,new_text,instance_num)Text

为需要替换其中字符文本，或对含有文本单元格引用。Old_text

为需要替换旧文本。New_text

用于替换old_text文本。Instance_num

为一数值，用来指定以new_text替换第几次出现old_text。假如指定了instance_num，则只有满足要求old_text被替换；不然将用new_text替换Text中出现全部old_text。在文本字符串中用new_text替换old_text。惯用函数第39页数学函数——RAND、RANDBETWEEN=RAND()注解若要生成a与b之间随机实数，请使用：RAND()*(b-a)+a假如要使用函数RAND生成一随机数，而且使之不随单元格计算而改变，能够在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。返回大于等于0及小于1均匀分布随机实数，每次计算工作表时都将返回一个新随机实数。=RANDBETWEEN(bottom,top)返回位于指定两个数之间一个随机整数。每次计算工作表时都将返回一个新随机整数。Bottom

函数RANDBETWEEN将返回最小整数。Top

函数RANDBETWEEN将返回最大整数。惯用函数第40页统计函数——RANK=RANK(number,ref,order)Number

为需要找到排位数字。Ref

为数字列表数组或对数字列表引用。Ref中非数值型参数将被忽略。Order

为一数字，指明排位方式。假如order为0（零）或省略，MicrosoftExcel对数字排位是基于ref为按照降序排列列表。假如order不为零，MicrosoftExcel对数字排位是基于ref为按照升序排列列表。注解函数RANK对重复数排位相同。但重复数存在将影响后续数值排位。比如，在一列按升序排列整数中，假如整数10出现两次，其排位为5，则11排位为7（没有排位为6数值）。返回一个数字在数字列表中排位。惯用函数第41页排位:RANKRANK(数值,范围,数值)

1-升序,0-降序

ABC11002010002200403300603000=RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3惯用函数第42页数学函数——ROUND=ROUND(number,num_digits)ROUND函数可将某个数字四舍五入为指定位数。说明假如num_digits大于0（零），则将数字四舍五入到指定小数位。假如num_digits等于0，则将数字四舍五入到最靠近整数。假如num_digits小于0，则在小数点左侧进行四舍五入。若要一直进行向上舍入（远离0），请使用ROUNDUP函数。若要一直进行向下舍入（朝向0），请使用ROUNDDOWN函数。若要将某个数字四舍五入为指定倍数（比如，四舍五入为最靠近0.5倍），请使用MROUND函数。惯用函数第43页四舍五入函数:ROUNDROUND(数值,数位(+/-))ABC1150.4528990.2345=ROUND(C1,2)=0.23=ROUNDUP(C1,2)=0.24=ROUNDDOWN(A1,1)=150.4惯用函数第44页引用函数——ROW、COLUMN说明：假如省略reference，则假定是对函数所在单元格引用。假如reference为一个单元格区域，而且函数作为数组输入，则函数将以数组形式返回reference行号或列号。假如参数reference为一个单元格区域，而且函数不是以数组公式形式输入，则COLUMN函数将返回最上侧行号或最左侧列列号。Reference不能引用多个区域。=ROW(reference)=ROWS(array)返回引用或行数。=COLUMNS(array)返回数组或引用列数。=COLUMN(reference)惯用函数第45页查找函数——MATCH=MATCH(lookup_value,lookup_array,[match_type])MATCH函数可在单元格区域（区域:工作表上两个或多个单元格。区域中单元格能够相邻或不相邻。）中搜索指定项，然后返回该项在单元格区域中相对位置。Match_type行为1或被省略MATCH函数会查找小于或等于lookup_value最大值。lookup_array参数中值必须按升序排列，比如：...-2,-1,0,1,2,...,A-Z,FALSE,TRUE。0MATCH函数会查找等于lookup_value第一个值。lookup_array参数中值能够按任何次序排列。-1MATCH函数会查找大于或等于lookup_value最小值。lookup_array参数中值必须按降序排列，比如：TRUE,FALSE,Z-A,...2,1,0,-1,-2,...等等。惯用函数第46页引用函数——INDEX数组形式:=INDEX(array,row_num,column_num)引用形式:=INDEX(reference,row_num,column_num,[area_num])Array

为单元格区域或数组常量。Reference

对一个或多个单元格区域引用。假如为引用输入一个不连续区域，必须将其用括号括起来。假如引用中每个区域只包含一行或一列，则对应参数row_num或column_num分别为可选项。Row_num

引用中某行行号，函数从该行返回一个引用。Column_num

引用中某列列标，函数从该列返回一个引用。Area_num

选择引用中一个区域，返回该区域中row_num和column_num交叉区域。选中或输入第一个区域序号为1，第二个为2，以这类推。假如省略area_num，则函数INDEX使用区域1。惯用函数第47页引用函数——INDEX惯用函数第48页查找函数——OFFSET=OFFSET(reference,rows,cols,height,width)以指定引用为参考系，经过给定偏移量得到新引用。返回引用能够为一个单元格或单元格区域。并能够指定返回行数或列数。Reference

作为偏移