Excel函数课件

2231EXCEL操作界面及设置函数与公式通用技巧常用函数Whereisit？在“视图”选项卡中，通过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏、行号列标、工作表标签等。“公式”选项卡中，有“公式审核”、“保护”等常用函数公式。Whereisit？右键单击工具栏→“自定义快速访问工具栏”→弹出自定义快速访问工具栏选项卡，可以设置自定义访问工具栏。2231EXCEL操作界面及设置函数与公式通用技巧常用函数在工作表或工作簿中移动和滚动的快捷键按下完成箭头键向上、下、左或右移动单元格CTRL+箭头键移动到当前数据区域的边缘CTRL+HOME移动到工作表的开头CTRL+END移动到工作表的最后一个单元格（右下角）CTRL+PAGEDOWN移动到工作簿中下一个工作表CTRL+PAGEUP移动到工作簿中前一个工作表CTRL+F6或CTRL+TAB移动到下一工作簿或窗口F5或CTRL+G显示“定位”对话框SHIFT+F5或CTRL+F显示“查找”对话框SHIFT+F4重复上一次“查找”操作（等同于“查找下一个”）TAB在保护工作表中的非锁定单元格之间移动选择单元格、行或列的快捷键请按如果要SHIFT+箭头键将选定区域扩展一个单元格宽度CTRL+SHIFT+箭头键选定区域扩展到活动单元格同一行或同一列的最后一个非空白单元格SHIFT+HOME将选定区域扩展到行首CTRL+SHIFT+HOME将选定区域扩展到工作表的开始CTRL+SHIFT+END将选定区域扩展到表的最后一个使用单元格CTRL+A选定整个工作表SHIFT+PAGEDOWN将选定区域向下扩展一屏SHIFT+PAGEUP将选定区域向上扩展一屏插入工作表、图表、宏的快捷键请按如果要SHIFT+F11或ALT+SHIFT+F1插入新工作表F11或ALT+F1创建使用当前区域的图表ALT+F8显示“宏”对话框ALT+F11显示“VisualBasic编辑器”符号说明实例优先顺序：_(空格)，引用运算符=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括号可以改变运算的优先级别，多组括号进行嵌套是，计算顺序是由最内层的括号逐级向外进行运算。符号说明实例*通配符，表示任何字符=COUNTIF(A:A，“北京*有限公司”)?通配符，表示任何单个字符=COUNTIF(A:A，“北京*有限??公司”)~通配符，用于解除字符的通配性=COUNTIF(A:A，”北京~*有限公司”)限制：突破：使用定义名称。当参数为单元格区域引用时，可使用一对括号来包含多个单元格区域的引用部分形成合并区域引用作为函数参数。1、提前将单元格格式设置为文本后再输入数字2、在输入数字时用半角单引号开头。公式内容的长度不能超过1024个字符；公式中函数的嵌套不能超过7层；公式中函数的参数不能超过30个；数字计算精度为15位。在实际工作中，许多用户在计算数字时会设置单元格格式为2为小数的数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元格显示的数据之和不相等。因为excel将数值按照15位计算精度带入计算所致。如图，在excel选项中勾选“将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，但不影响其他工作薄。此外，也可以用ROUND函数对数值进行修约后再求和。方法一：选中包含公式的单元格，单击“公式”选项卡中的“公式求值”按钮，弹出“公式求值”对话框，然后多次单击“求值”按钮，“求值”域中将按照公式计算的顺序逐步地显示公式的计算过程。方法二：F9功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。当一个函数公式比较长时，可以用F9键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。按F9功能键切换2231EXCEL操作界面及设置函数与公式通用技巧常用函数函数语法由函数名+括号+参数组成例：求和函数:SUM(A1,B2,…)参数与参数之间用逗号“,”表示根据对指定的条件计算结果为TRUE或FALSE，返回不同的结果。可以使用IF对数值和公式执行条件检测。MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF和COUNTIFS电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和，可使用SUMIF和SUMIFS工作表函数。ISERROR()括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或#NULL时为TRUEISNA()括号中为：#N/A时为TRUE常与IF嵌套使用①②=IFERROR(value,value_if_error)如果公式计算出错误则返回您指定的值；否则返回公式结果。使用IFERROR函数来捕获和处理公式中的错误。Value是需要检查是否存在错误的参数。Value_if_error是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。通过AND函数可以检验多个不同的条件，而不仅仅是一个条件。=AND(logical1,[logical2],...)=OR(logical1,[logical2],...)在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。注意：参数的计算结果必须是逻辑值（如TRUE或FALSE），而参数必须是包含逻辑值的数组如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域未包含逻辑值，则函数将返回错误值#VALUE!。逻辑函数AND(条件)括号中条件值均为TRUE，则为TRUE；如果任一个为FALSE，则为FALSEOR(条件)括号中条件值均为FALSE，则为FALSE；如果任一个为TRUE，则为TRUENOT(条件)括号中条件值为FALSE，则为TRUE；如果为TRUE，则为FALSE=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个最小值。=MAX(number1,number2,...)返回一组值中的最大值。注解参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数不包含数字，函数MAX返回0（零）。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算包括引用中的逻辑值和代表数字的文本，请使用MAXA函数。=MIN(number1,number2,...)返回一组值中的最小值。=MEDIAN(number1,number2,...)返回给定数值的中值。中值是在一组数值中居于中间的数值。=MODE(number1,number2,...)返回在某一数组或数据区域中出现频率最多（众数）的数值。=TRIMMEAN(array,percent)返回数据集的内部平均值。函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。=AVERAGE(number1,[number2],...)返回参数的平均值（算术平均值）。注释：参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。若要在计算中包含引用中的逻辑值和代表数字的文本，请使用AVERAGEA函数。若要只对符合某些条件的值计算平均值，请使用AVERAGEIF函数或AVERAGEIFS函数。AVERAGE(数值1,数值2,…)ABC110020100022004020003300603000=AVERAGE（A1：A3）=200=AVERAGE（A1：A3，B1）=105=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。求和:SUMSUM(数值1,数值2,…)A1=6A2=7A3=8=SUM(6,8)=14=SUM(A1,A3)=6+8=14=SUM(A1:A3)=6+7+8=14=SUM(number1,number2,...)说明直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅下面的第三个示例。如果参数为错误值或为不能转换为数字的文本，将会导致错误。条件求和:SUMIFSUMIF(范围,条件,要求和范围)ABC110020100022004020003300603000=SUMIF(A1:A3,“=200”,B1:B3)=100=SUMIF(A1:A3,“=200”,C1:C3)=5000=COUNT(value1,[value2],...)注解如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如1），则将被计算在内。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为错误值或不能转换为数字的文本，则不会被计算在内。如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本或错误值。若要计算逻辑值、文本值或错误值的个数，请使用COUNT