Excel函数课件.ppt

日常EXCEL函数应用 EXCEL操作界面及设置 函数与公式通用技巧 常用函数 Whereisit EXCEL操作界面及设置 在 视图 选项卡中 通过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏 行号列标 工作表标签等 公式 选项卡中 有 公式审核 保护 等常用函数公式 Whereisit 右键单击工具栏 自定义快速访问工具栏 弹出自定义快速访问工具栏选项卡 可以设置自定义访问工具栏 EXCEL操作界面及设置 EXCEL操作界面及设置 函数与公式通用技巧 常用函数 在工作表或工作簿中移动和滚动的快捷键按下完成箭头键向上 下 左或右移动单元格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编辑器 函数与公式通用技巧 实例 函数与公式通用技巧 函数与公式通用技巧 限制 突破 使用定义名称 当参数为单元格区域引用时 可使用一对括号来包含多个单元格区域的引用部分形成合并区域引用作为函数参数 1 提前将单元格格式设置为文本后再输入数字2 在输入数字时用半角单引号开头 公式内容的长度不能超过1024个字符 公式中函数的嵌套不能超过7层 公式中函数的参数不能超过30个 数字计算精度为15位 函数与公式通用技巧 改变工作表的计算精度 在实际工作中 许多用户在计算数字时会设置单元格格式为2为小数的数值 货币格式或者是整数值 但在使用公式进行数据处理的过程中 经常会发现几个单元格之和与合计单元格显示的数据之和不相等 因为excel将数值按照15位计算精度带入计算所致 如图 在excel选项中勾选 将精度设为所显示的精度 可以解决上述情况的发生 但这样也降低了工作薄中所有工作表的计算精度 但不影响其他工作薄 此外 也可以用ROUND函数对数值进行修约后再求和 函数与公式通用技巧 分步查看公式计算结果 方法一 选中包含公式的单元格 单击 公式 选项卡中的 公式求值 按钮 弹出 公式求值 对话框 然后多次单击 求值 按钮 求值 域中将按照公式计算的顺序逐步地显示公式的计算过程 方法二 功能键除了可以重算工作薄外 还可以用于查看公式的运算结果 当一个函数公式比较长时 可以用键对各个部分进行分段计算 以便公式差错和更正 在分段选择时 注意要包含一个完整的运算对象 函数与公式通用技巧 EXCEL操作界面及设置 函数与公式通用技巧 常用函数 函数语法由函数名 括号 参数组成例 求和函数 SUM A1 B2 参数与参数之间用逗号 表示 常用函数 1 条件函数 IF IFERROR 根据对指定的条件计算结果为TRUE或FALSE 返回不同的结果 可以使用IF对数值和公式执行条件检测 MicrosoftExcel还提供了其他一些函数 它们可根据条件来分析数据 例如 如果要计算某单元格区域内某个文本字符串或数字出现的次数 则可使用COUNTIF和COUNTIFS电子表格函数 若要计算基于某区域内一个文本字符串或一个数值的总和 可使用SUMIF和SUMIFS工作表函数 常用函数 ISERROR 括号中为 N A VALUE REF DIV 0 NUM NAME 或 NULL时为TRUEISNA 括号中为 N A时为TRUE常与IF嵌套使用 条件函数 IF IFERROR 常用函数 IFERROR value value if error 如果公式计算出错误则返回您指定的值 否则返回公式结果 使用IFERROR函数来捕获和处理公式中的错误 Value是需要检查是否存在错误的参数 Value if error是公式计算出错误时要返回的值 计算得到的错误类型有 N A VALUE REF DIV 0 NUM NAME 或 NULL 条件函数 IF IFERROR 常用函数 2 逻辑函数 AND OR 所有参数的计算结果为TRUE时 返回TRUE 只要有一个参数的计算结果为FALSE 即返回FALSE 通过AND函数可以检验多个不同的条件 而不仅仅是一个条件 AND logical1 logical2 OR logical1 logical2 在其参数组中 任何一个参数逻辑值为TRUE 即返回TRUE 任何一个参数的逻辑值为FALSE 即返回FALSE 注意 参数的计算结果必须是逻辑值 如TRUE或FALSE 而参数必须是包含逻辑值的数组如果数组或引用参数中包含文本或空白单元格 则这些值将被忽略 如果指定的单元格区域未包含逻辑值 则函数将返回错误值 VALUE 常用函数 逻辑函数AND 条件 括号中条件值均为TRUE 则为TRUE 如果任一个为FALSE 则为FALSEOR 条件 括号中条件值均为FALSE 则为FALSE 如果任一个为TRUE 则为TRUENOT 条件 括号中条件值为FALSE 则为TRUE 如果为TRUE 则为FALSE 常用函数 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个最小值 常用函数 4 统计函数 MAX MIN MAX number1 number2 返回一组值中的最大值 注解参数可以是数字或者是包含数字的名称 数组或引用 逻辑值和直接键入到参数列表中代表数字的文本被计算在内 如果参数为数组或引用 则只使用该数组或引用中的数字 数组或引用中的空白单元格 逻辑值或文本将被忽略 如果参数不包含数字 函数MAX返回0 零 如果参数为错误值或为不能转换为数字的文本 将会导致错误 如果要使计算包括引用中的逻辑值和代表数字的文本 请使用MAXA函数 MIN number1 number2 返回一组值中的最小值 MEDIAN number1 number2 返回给定数值的中值 中值是在一组数值中居于中间的数值 MODE number1 number2 返回在某一数组或数据区域中出现频率最多 众数 的数值 常用函数 5 统计函数 AVERAGE TRIMMEAN array percent 返回数据集的内部平均值 函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点 然后再求平均值 当希望在分析中剔除一部分数据的计算时 可以使用此函数 AVERAGE number1 number2 返回参数的平均值 算术平均值 注释 参数可以是数字或者是包含数字的名称 单元格区域或单元格引用 逻辑值和直接键入到参数列表中代表数字的文本被计算在内 如果区域或单元格引用参数包含文本 逻辑值或空单元格 则这些值将被忽略 但包含零值的单元格将被计算在内 如果参数为错误值或为不能转换为数字的文本 将会导致错误 若要在计算中包含引用中的逻辑值和代表数字的文本 请使用AVERAGEA函数 若要只对符合某些条件的值计算平均值 请使用AVERAGEIF函数或AVERAGEIFS函数 常用函数 AVERAGE 数值1 数值2 ABC110020100022004020003300603000 AVERAGE A1 A3 200 AVERAGE A1 A3 B1 105 统计函数 AVERAGE 常用函数 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 常用函数 求和 SUMSUM 数值1 数值2 A1 6A2 7A3 8 SUM 6 8 14 SUM A1 A3 6 8 14 SUM A1 A3 6 7 8 14 7 数学函数 SUM 常用函数 数学函数 SUM SUM number1 number2 说明直接键入到参数表中的数字 逻辑值及数字的文本表达式将被计算 请参阅下面的示例一和示例二 如果参数是一个数组或引用 则只计算其中的数字 数组或引用中的空白单元格 逻辑值或文本将被忽略 请参阅下面的第三个示例 如果参数为错误值或为不能转换为数字的文本 将会导致错误 常用函数 条件求和 SUMIFSUMIF 范围 条件 要求和范围 ABC110020100022004020003300603000 SUMIF A1 A3 200 B1 B3 100 SUMIF A1 A3 200 C1 C3 5000 8 数学函数 SUMIF 常用函数 9 统计函数 COUNT COUNT value1 value2 注解如果参数为数字 日期或者代表数字的文本 例如 用引号引起的数字 如 1 则将被计算在内 逻辑值和直接键入到参数列表中代表数字的文本被计算在内 如果参数为错误值或不能转换为数字的文本 则不会被计算在内 如果参数为数组或引用 则只计算数组或引用中数字的个数 不会计算数组或引用中的空单元格 逻辑值 文本或错误值 若要计算逻辑值 文本值或错误值的个数 请使用COUNTA函数 若要只计算符合某一条件的数字的个数 请使用COUNTIF函数或COUNTIFS函数 常用函数 求个数 COUNTCOUNT 数值1 数值2 ABC110020100022004020003300603000 COUNT A1 A3 3 COUNT B1 B3 3 COUNTIF range criteria 计算某个区域中满足给定条件的单元格数 例如 COUNTIF A2 A7 20 常用函数 求符合条件个数 COUNTIFCOUNTIF 范围 条件 ABC110020100022004020003300603000 COUNTIF A1 A3 200 2 COUNTIF A1 A3 200 1 10 统计函数 COUNTIF 常用函数 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根据所指定的字符数返回文本字符串中最后一个或多个字符 常用函数 文本函数截取函数LEFTRIGHTMIDLEFT 文本 数值 从左边截取RIGHT 文本 数值 从右边截取MID 文本 开始位 数值 从中间截取 LEFT abcdef 2 ab RIGHT abcdef 2 ef MID abcdef 2 3 bcd 常用函数 12 文本函数 CONCATENATE CONCATENATE text1 text2 将两个或多个文本字符串合并为一个文本字符串 注解您也可使用连接符号 计算运算符代替CONCATENATE函数来连接文本项 例如 A1 B1与 CONCATENATE A1 B1 返回相同的值 常用函数 计算字符长度LENLEN 文本 计算字符串的长度 LEN abcdef 6合并字符函数CONCATENATE或 CONCATENATE 文本1 合并字符串 CONCATENATE a ef aef 常用函数 将数值转化为文本TEXTTEXT 数值 参数 将数值转化为文本 参数一般为0 TEXT 1234 0 1234还可用于补0 常用函数 在字符串中查找特定字符FINDFIND 文本 范围 数值 查找一个字符在另一个字符串中的位置数值表示查找第几个 FIND a abcaef 1 1 FIND a abcaef 2 4 常用函数 比较两个字符是否完全相符EXACTEXACT 文本1 文本2 比较两个字符是否完全相符 是则为TRUE 否则为FALSE EXACT a a TRUE EXACT a ab FALSE EXACT a A FALSE 常用函数 文本函数 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 常用函数 数学函数 RAND RANDBETWEEN RAND 注解若要生成a与b之间的随机实数 请使用 RAND b a a如果要使用函数RAND生成一随机数 并且使之不随单元格计算而改变 可以在编辑栏中输入 RAND 保持编辑状态 然后按F9 将公式永久性地改为随机数 返回大于等于0及小于1的均匀分布随机实数 每次计算工作表时都将返回一个新的随机实数 RANDBETWEEN bottom top 返回位于指定的两个数之间的一个随机整数 每次计算工作表时都将返回一个新的随机整数 Bottom函数RANDBETWEEN将返回的最小整数 Top函数RANDBETWEEN将返回的最大整数 常用函数 统计函数 RANK RANK number ref order Number为需要找到排位的数字 Ref为数字列表数组或对数字列表的引用 Ref中的非数值型参数将被忽略 Order为一数字 指明排位的方式 如果order为0 零 或省略 MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表 如果order不为零 MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表 注解函数RANK对重复数的排位相同 但重复数的存在将影响后续数值的排位 例如 在一列按升序排列的整数中 如果整数10出现两次 其排位为5 则11的排位为7 没有排位为6的数值 返回一个数字在数字列表中的排位 常用函数 排位 RANKRANK 数值 范围 数值 1 升序 0 降序ABC110020100022004020003300603000 RANK A1 A1 A3 1 1 RANK A1 A1 A3 0 3 常用函数 数学函数 ROUND ROUND number num digits ROUND函数可将某个数字四舍五入为指定的位数 说明如果num digits大于0 零 则将数字四舍五入到指定的小数位 如果num digits等于0 则将数字四舍五入到最接近的整数 如果num digits小于0 则在小数点左侧进行四舍五入 若要始终进行向上舍入 远离0 请使用ROUNDUP函数 若要始终进行向下舍入 朝向0 请使用ROUNDDOWN函数 若要将某个数字四舍五入为指定的倍数 例如 四舍五入为最接近的0 5倍 请使用MROUND函数 常用函数 四舍五入函数 ROUNDROUND 数值 数位 ABC1150 4528990 2345 ROUND C1 2 0 23 ROUNDUP C1 2 0 24 ROUNDDOWN A1 1 150 4 常用函数 引用函数 ROW COLUMN 说明 如果省略reference 则假定是对函数所在单元格的引用 如果reference为一个单元格区域 并且函数作为数组输入 则函数将以数组的形式返回reference的行号或列号 如果参数reference为一个单元格区域 并且函数不是以数组公式的形式输入的 则COLUMN函数将返回最上侧的行号或最左侧列的列号 Reference不能引用多个区域 ROW reference ROWS array 返回引用或的行数 COLUMNS array 返回数组或引用的列数 COLUMN reference 常用函数 查找函数 MATCH MATCH lookup value lookup array match type MATCH函数可在单元格区域 区域 工作表上的两个或多个单元格 区域中的单元格可以相邻或不相邻 中搜索指定项 然后返回该项在单元格区域中的相对位置 常用函数 引用函数 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 常用函数 引用函数 INDEX 常用函数 查找函数 OFFSET OFFSET reference rows cols height width 以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 Reference作为偏移量参照系的引用区域 Rows相对于偏移量参照系的左上角单元格 上 下 偏移的行数 行数可为正数或负数 Cols相对于偏移量参照系的左上角单元格 左 右 偏移的列数 列数可为正数或负数 Height高度 即所要返回的引用区域的行数 Height必须为正数 Width宽度 即所要返回的引用区域的列数 Width必须为正数 常用函数 查找函数 VLOOKUP HLOOKUP VLOOKUP lo