《Excel学习(函数)》由会员分享,可在线阅读,更多相关《Excel学习(函数)(35页珍藏版)》请在金锄头文库上搜索。
1、1 EXCEL函数 2 函数概述 对照数学上函数的概念y f x1 x2 x3 xn 函数的一般格式 函数名 参数1 参数2 可以是数值 文本 单元格 单元格区域 数组 也可以是函数 整个函数有一个计算结果 函数值 返回值 括号不能省略 例Now 3 直接求和1 SUM函数格式 SUM number1 number2 对number1 number2等这些指定参数进行求和例 求总成绩 某列 某行之和 2 用公式 特别 用数组公式求和 Set1 Set2 Set3 集合运算 1 套 一 求和 4 有条件求和 1 SUMIF函数 对符合指定条件的单元格区域内的数值进行求和格式为 SUMIF ran
2、ge criteria sum range range表示的是判断条件的单元格区域 criteria表示的是指定条件表达式 sum range表示的是需要计算数值所在的单元格区域例题 统计各种商品的采购总量和采购总金额 2 使用函数计算 各用户所订购图书所需支付的金额总数 3 统计每个销售人员的销售总额 6 注 sumif为单个条件 如多个条件一般不能用 5 多条件求和 数据库函数2 Dsum database field criteria criteria 为包含指定条件的单元格区域例题 计算产品为白炽灯 其瓦数大于等于80且小于等于100的数量 5 类似 求平均值Sum Average S
3、umif Dsum Daverage 带多个条件的平均值 5 6 1 COUNT函数格式 COUNT value1 value2 value1 value2 各类型数据或单元格区域用于返回数字参数的个数 即只统计单元格区域中含有数值类型的单元格个数2 COUNTA函数计算参数中非空值的数目 即计算数组或单元格区域中数据项的个数3 COUNTBLANK函数计算某个单元格区域中空白单元格的数目例题 统计吸烟未登记的部门个数 5 二 计数 统计个数 7 有条件的计数 统计 1 COUNTIF函数格式 COUNTIF range criteria 功能 计算区域中满足给定条件的单元格的个数Criter
4、ia确定哪些单元格将被计算在内的条件 单个条件例题 统计在登记的部门中 吸烟的部门个数 5 数学分数位于0到20分的人数 1 统计高级工程师人数 结果填入N4单元格中 8 8 多个条件的计数 数据库函数2 DCOUNT database field criteria 和DCOUNTA database field criteria 数据库函数 多个条件的计数与统计Criteria 条件区域例题 计算 语文 和 数学 成绩都大于或等于85的学生人数 11 9 类似函数1 最值函数 MAX MIN功能 MAX函数 求参数列表中对应数字的最大值MIN函数 求参数列表中对应数字的最小值格式 MAX n
5、umber1 number2 MIN number1 number2 2 多条件的最值函数DMAX database field criteria DMIN database field criteria 注 无Maxif Minif函数例 求 体育 成绩中男生的最高分 11 10 格式 IF logical test value if true value if false logical test 逻辑条件value if true 条件为 真 时的返回值value if false 条件为 假 时的返回值 缺省值为FALSE在IF函数中使用嵌套函数 最多可用嵌套7层例1 当成绩A2 60
6、 85则 B 否则为 C 错误 if 60 60 if A2 60 A2 85 B C 例2 使用函数 判断Sheet2中的年份是否为闰年 3 三 逻辑函数 IF 11 补充 了解 1 逻辑与函数AND logical1 logical2 例 And A1 10 B1 20 2 逻辑或函数OR logical1 logical2 12 文本函数可以处理公式中的文本字符串主要介绍以下几个文本函数 EXACT函数CONCATENATE函数SUBSTITUTE函数REPLACE函数 例11 求新学号 SEARCH函数MID函数 例13 求考试级别 Right Left函数 例8 升级员工代码 四 文
7、本函数 13 功能 用来比较两个文本字符串是否相同 如果两个字符串相同 则返回 TRUE 反之 则返回 FALSE 格式 EXACT text1 text2 text1和text2 两个要比较的文本字符串注意 1 在字符串中如果有多余的空格 会被视为不同2 EXACT函数在判别字符串的时候 会区分英文的大小写 但不考虑格式设置的差异 1 文本函数 EXACT函数 14 功能 将多个字符文本或单元格中的数据连接在一起 显示在一个单元格中格式 CONCATENATE text1 text2 text1 text2 需要连接的字符文本或引用的单元格注意 1 该函数最多可以附带30个参数2 如果其中的
8、参数不是引用的单元格 且为文本格式的 请给参数加上英文状态下的双引号将上述函数改为使用 符连接也能达到相同的效果 2 文本函数 CONCATENATE函数 15 功能 实现替换文本字符串中的某个特定字符串格式 SUBSTITUTE text old text new text instance num text 原始内容或是单元格地址old text 要被替换的字符串new text 替换old text的新字符串如果字符串中含有多组相同的old text时 可以使用参数instance num来指定要被替换的字符串是文本字符串中的第几组 如果没有指定instance num的值 默认的情况下
9、 文本中的每一组old text都会被替换为new text 3 文本函数 SUBSTITUTE函数 16 功能 将某几位的文字以新的字符串替换 其替换功能与SUBSTITUTE函数大致类似格式 REPLACE old text start num num chars new text old text 原始的文本数据start num 设置要从old text的第几个字符位置开始替换num chars 设置共有多少字符要被替换new text 要用来替换的新字符串 4 文本函数 REPLACE函数 17 功能 用来返回指定的字符串在原始字符串中首次出现的位置格式 SEARCH find te
10、xt within text start num find text 要查找的文本字符串within text 要在哪一个字符串查找start num 从within text的第几个字符开始查找 注意 在find text中 可以使用通配符 例如 问号 和星号 其中问号 代表任何一个字符 而星号 可代表任何字符串 如果要查找的字符串就是问号或星号 则必须在这两个符号前加上 符号 5 文本函数 SEARCH函数 18 日期与时间函数可以用来分析或操作公式中与日期和时间有关的值主要以下几个日期与时间函数 DATE函数DAY函数 MONTH YEARTODAY函数 NowTIME函数 五 日期与时
11、间函数 19 功能 计算某一特定日期的系列编号格式 DATE year month day year 指定年份month 每年中月份的数字day 在该月份中第几天的数字说明 已知year month day求日期 1 日期与时间函数 DATE函数 20 功能 返回指定日期所对应的当月中的第几天格式 DAY serial number serial number 指定的日期或数值说明 已知日期 求day DAY函数已知日期 求month MONTH函数已知日期 求year YEAR函数例题 求年龄 第4套 2 日期与时间函数 DAY函数 21 功能 返回当前系统的日期和时间格式 TODAY 日期
12、NOW 日期 时间其语法形式中无参数 若要显示当前系统的日期 可以在当前单元格中直接输入公式TODAY 或NOW 3 日期与时间函数 22 功能 返回某一时间的小时 分钟 秒数格式 HOUR 时间 返回小时 0 23MINUTE 时间 返回分 0 59之间的数SECOND 时间 返回秒 0 59之间的数例题 计算停车费 不到1小时算1小时 超过15分钟加1小时 第9套 4 日期与时间函数 23 在一个工作表中 可以利用查找与引用函数功能按指定的条件对数据进行快速查询 选择和引用 查找与引用函数用于查找 查看 列表或表格中的值主要介绍以下几个查找与引用函数 VLOOKUP函数HLOOKUP函数L
13、OOKUP函数 六 查找与引用函数 24 功能 从一个表格的最左列 垂直方向 中查找含有特定值的字段 再返回同一行中某一指定列中的值格式 VLOOKUP lookup value talbe array col index num range lookup look value 被查找的值 依据哪个值去找 table array 要搜索的数据表格 到哪里找 col index num 找到后返回第几列rang lookup 一个逻辑值 如果为 TRUE 1或省略 则近似查找 如果为 FALSE 或0时 则进行精确查找 如果进行近似查找 则table array第一列的值必须以递增次序排列例题
14、第2套 第12套 1 查找与引用函数 VLOOKUP函数 25 功能 可以用来查询表格的第一行的数据 水平方向按行查找 格式 HLOOKUP lookup value table array row index num range lookup 说明 除了row index num代表要返回的值位于table array中的第几行之外 其它同VLOOKUP例题 第9套 2 查找与引用函数 HLOOKUP函数 26 LOOKUP函数有两种语法形式 向量 较常用 和数组 向量形式的格式 LOOKUP lookup value lookup vector result vector look val
15、ue 要查找的数据lookup vector 一个单行或单列范围 内容可以是文字 数字或逻辑值 但要以递增方式排列 否则不会返回正确的值result vector 一个单行或单列范围 大小应与lookup vector相同查询时 若LOOKUP函数无法找到完全符合的lookup value 则会采用在lookup value中仅次于lookup value的值 3 查找与引用函数 LOOKUP函数 27 财务函数是财务计算和财务分析的专业工具 有了这些函数的存在 可以很快捷方便地解决复杂的财务运算 主要介绍以下几个财务函数 1 PMT函数2 IPMT函数3 FV函数4 PV函数5 SLN函数
16、七 财务函数 28 功能 基于固定利率及等额分期付款方式 返回贷款的每期付款额格式 PMT rate nper pv fv type rate 贷款利率nper 该项贷款的总贷款期限或者总投资期pv 从该项贷款 或投资 开始计算时已经入账的款项 或一系列未来付款当前值的累积和fv 未来值 或在最后一次付款后希望得到的现金余额 如果忽略该值 将自动默认为0type 一个逻辑值 用以指定付款时间是在期初还是在期末 1表示期初 0表示期末 其默认值为0 1 财务函数 PMT函数 29 功能 基于固定利率及等额分期付款方式 返回投资或贷款在某一给定期限内的利息偿还额格式 IPMT rate per nper pv fv rate 各期利率per 用于计算利息数额的期数 介于1 nper之间nper 总投资 或贷款 期 即该项投资 或贷款 的付款期总数pv 从该项投资 或贷款 开始计算时已经入账的款项 或一系列未来付款当前值的累积和fv 未来值 或在最后一次付款后希望得到的现金余额 如果忽略该值 将自动默认为0 2 财务函数 IPMT函数 30 功能 基于固定利率及等额分期付款方式 返回某项投资