EXCEL培训教材 (1).xls

EXCELEXCEL在在工工作作中中的的应应用用 目目录录 第一章 round sum vlookup 三个函数应用 第二章 与函数的组合妙用 第三章 len find left right mid函数应用 第四章 选择性粘贴 的奇妙应用 第五章 定位 的应用 第六章 经典函数 sumif 的应用 第七章 if and or函数的应用 第八章 查找 筛选 的应用 第九章 count countif函数的应用 第十章 图表制作 第十一章 制作协调美观的表格 第十二章 数据透析表应用 说明 每两个星期会发布一次EXCEL的应用说明 请大家在规定的时间内将练习做好并发给财务部周建伟 说明 每两个星期会发布一次EXCEL的应用说明 请大家在规定的时间内将练习做好并发给财务部周建伟 第第一一章章 roundround sumsum vlookupvlookup 三三个个函函数数应应用用 函数是EXCEL里最强大的功能之一 EXCEL中自带的函数有几百个之多 但很多使用EXCEL多年的人对函数却很陌生 其实我们只要掌握十到二十个常用函数的应用 对我们的工作会带来很大的帮助 或许本来需要一天才能完成的工作 使 用函数 五分钟就能搞定 现在就让我们开始走进EXCEL的函数世界 一一 round round 函函数数 一一 函函数数介介绍绍 该函数的作用是四舍五入 二二 函函数数应应用用举举例例 例例一一 商商品品名名称称单单位位单单价价销销售售量量销销售售金金额额销销售售金金额额1 1 0102打孔机只5 244315 73215 73 0103打孔机只5 244315 73215 73 0104打孔机只5 244315 73215 73 合计47 19647 20 1 做报表时 往往会要求保留两位小数 有些人会使用右键单击 设置单元格格式 数字 数值 然后将小数位调成2位 如黄色单元格中的效果 看上去是15 73 保留了两位小数 其实不然 大家看他的合计数 三个15 73加起来 应该是47 19 但他却等于47 20 2 使用 设置单元格格式 并不能真正保留两位小数 要想真下四舍五入 必须使用round函数 见蓝色单元格中的公式 三三 函函数数使使用用格格式式说说明明 100 333330 3 3 ROUND B26 C26 1 格格式式说说明明 逗号后面的 1 是round函数的参数 代表保留几位小数 1是保留1位 2是保留2位 0是指保留整数 二二 sum sum 函函数数 一一 函函数数介介绍绍 该函数的作用是求和 二二 函函数数应应用用举举例例 例例一一 销销售售明明细细表表 月份北京销售天津销售 1月100100 2月200200 3月140140 4月130130 5月120120 6月1010 7月9090 8月130130 9月120120 10月1010 11月2020 12月3030 合计110011002200 在本例中 要比12个月的销售求合 有人会用 号 一直从1月加到12月 我曾看到过有人用几十个 号进行求合 让人眼花缭乱 使用sum函数求合很便捷 请看蓝色单元格中的公式 C39 C50 指的是一个区域 我们也可以跨列选中要加的区域 如黄色单元格的C39 D50区域 三三 vlookup vlookup 函函数数 一一 函函数数介介绍绍 查找函数 这是EXCEL中最实用 最经典的函数之一 学会了这个函数 对你的工作有很大很大的帮助 二二 函函数数应应用用举举例例 例例一一 表1 表2 商品档案表销销售售明明细细表表 商品名称单价商品名称单价 0101打孔机5 750103打孔机4 09 0102打孔机5 30104打孔机6 75 0103打孔机4 090103打孔机4 09 0104打孔机6 750104打孔机6 75 0105打孔机21 560105打孔机21 56 假设表1是商品档案表 有商品名称和单价 表2是销售明细表 有商品名称和销售数量 现在需要统计销售金额 统计的关键是填上销售明细表中各商品的单价 如果手工从表1中查找并输入 是件很麻烦的事 而用vlookup函数 可以一次性自动查找 非常的方便 公式见 单价 列 三三 函函数数使使用用格格式式说说明明 表1 商商品品名名称称原原价价销销售售价价成成本本价价 0101打孔机1008060 0102打孔机1409050 0103打孔机1308555 商品名称价格 0102打孔机90 VLOOKUP B90 B 83 D 85 3 0 该函数公式应用见黄色单元格 第一个参数B90 表示要查找的内容在B90单元格 第二个参数 b 83 D 85表示查找的数据源区域 第三个参数3表示要查找的内容从B列到D列 经过B C D三列 如果要查 成本价 则是4 第四个参数0 表示精确匹配 通俗的讲就是告诉函数一定要查B90单元格中一模一样商品的价格 如果不输0 则查出来的结果可能会错 本本章章提提示示 大家可能看到vlookup函数输入时 指定的单元格有 符号 这个美元符号是绝对引用的意思 在例1 例4中 请仔细观察黄色单元格中的公式 然后分别横拉和竖拉例1 例4中的黄色单元格 看复制出来的公式有什么不同 例1 得力集团 得力集团 例2 得力集团 得力集团 例3 得力集团 得力集团 例4 得力集团 得力集团 销销售售金金额额2 2 15 73 15 73 15 73 47 19 函数是EXCEL里最强大的功能之一 EXCEL中自带的函数有几百个之多 但很多使用EXCEL多年的人对函数却很陌生 其实我们只要掌握十到二十个常用函数的应用 对我们的工作会带来很大的帮助 或许本来需要一天才能完成的工作 使 1 做报表时 往往会要求保留两位小数 有些人会使用右键单击 设置单元格格式 数字 数值 然后将小数位调成2位 如黄色单元格中的效果 看上去是15 73 保留了两位小数 其实不然 大家看他的合计数 三个15 73加起来 应该是47 19 2 使用 设置单元格格式 并不能真正保留两位小数 要想真下四舍五入 必须使用round函数 见蓝色单元格中的公式 逗号后面的 1 是round函数的参数 代表保留几位小数 1是保留1位 2是保留2位 0是指保留整数 数量金额 1977 71 在本例中 要比12个月的销售求合 有人会用 号 一直从1月加到12月 我曾看到过有人用几十个 号进行求合 让人眼花缭乱 C39 C50 指的是一个区域 我们也可以跨列选中要加的区域 如黄色单元格的C39 D50区域 查找函数 这是EXCEL中最实用 最经典的函数之一 学会了这个函数 对你的工作有很大很大的帮助 销销售售明明细细表表 24162 2185 89 1067 5 34733 04 假设表1是商品档案表 有商品名称和单价 表2是销售明细表 有商品名称和销售数量 现在需要统计销售金额 统计的关键是填上销售明细表中各商品的单价 如果手工从表1中查找并输入 是件很麻烦的事 而用vlookup函数 该函数公式应用见黄色单元格 第一个参数B90 表示要查找的内容在B90单元格 第二个参数 b 83 D 85表示查找的数据源区域 第三个参数3表示要查找的内容从B列到D列 经过B C D三列 如果要查 成本价 则是4 第四个参数0 表示精确匹配 通俗的讲就是告诉函数一定要查B90单元格中一模一样商品的价格 如果不输0 则查出来的结果可能会错 大家可能看到vlookup函数输入时 指定的单元格有 符号 这个美元符号是绝对引用的意思 在例1 例4中 请仔细观察黄色单元格中的公式 然后分别横拉和竖拉例1 例4中的黄色单元格 看复制出来的公式有什么不同 第第二二章章 与与函函数数的的组组合合妙妙用用 等特殊字符在Excel中是很有用处的 不知道大家有没有注意到他们的存在 这一章介绍特殊字符的功能 以及他们与函数组合妙用 一一 字字符符 一一 字字符符介介绍绍 这是在英文状态下输入的引号 在excel中 这个字符代表空字符 同时 在公式中需要输入字符时 需要输在 中 二二 字字符符应应用用举举例例 例例一一 NAME 在C14单元格中输入等号 然后直销输入 得力公司 四个字 出现错误值 得力公司在C15单元格中输入等号 然后将 得力公司 四个字输入 中 不会出现错误值 NAME C16单元格中会出现错误值 是因为没有输在英文状态的引号中 而是在中文状态的引号中 二二 字字符符 一一 字字符符介介绍绍 这是一个合并符号 能将不同单元格之间的内容合并在一起 该字符在数字键 7 上 需按 shift 键才能切换输出 二二 字字符符应应用用举举例例 例例一一 货货号号商商品品名名称称颜颜色色合合并并后后的的格格式式 500110页资料册蓝5001 10页资料册 蓝 500110页资料册绿5001 10页资料册 绿 500220页资料册绿5002 20页资料册 绿 500220页资料册蓝5002 20页资料册 蓝 在例1中 货号 商品名称 颜色 是分三列显示的 而分销中的商品名称显示格式往往是合并的 如显示为 5001 10页资料册 蓝 的格式 在使用vlookup等函数时 往往需要两个表中的内容是一样的 为此 就必须将例一中零散的将名称合并成分销中的格式 合并的公式详见灰色单元格 三三 字字符符 一一 字字符符介介绍绍 这是一个通配字符 该字符在数字键 8 上 需按 shift 键才能切换输出 二二 字字符符应应用用举举例例 例例一一 表表一一表二 客客户户应应收收账账款款客客户户 山东大唐公司 促销100新星文化 上海新星文化传媒 个案200中鑫 中国人民银行300大唐公司 北京中鑫公司 植根400 四四 hlookup hlookup 函函数数 一一 函函数数介介绍绍 这是一个横向查找函数 和vlookup函数很相似 二二 函函数数应应用用举举例例 例例一一 表表一一 客户山东大唐公司上海新星文化中国人民银行北京中鑫公司 植根 由于表二中的客户名称都 都是简称 前后加上 号后 后 表示只要中间有一 一部分字符相等即可 销售额100200300400 应收账款10 00020 00030 00040 000 表表二二 客户应收账款销售额 上海新星文化20 000200 中国人民银行30 000300 山东大唐公司10 000100 北京中鑫公司 植根40 000400 hlookup函数是横向查找 找 应收账款是在第三 三行 所以参数是3 销 销售额是第二行 所以 以参数是2 5001 10页资料册 蓝 等特殊字符在Excel中是很有用处的 不知道大家有没有注意到他们的存在 这一章介绍特殊字符的功能 这是在英文状态下输入的引号 在excel中 这个字符代表空字符 同时 在公式中需要输入字符时 需要输在 中 在C14单元格中输入等号 然后直销输入 得力公司 四个字 出现错误值 在C15单元格中输入等号 然后将 得力公司 四个字输入 中 不会出现错误值 C16单元格中会出现错误值 是因为没有输在英文状态的引号中 而是在中文状态的引号中 这是一个合并符号 能将不同单元格之间的内容合并在一起 该字符在数字键 7 上 需按 shift 键才能切换输出 在例1中 货号 商品名称 颜色 是分三列显示的 而分销中的商品名称显示格式往往是合并的 应应收收账账款款应应收收账账款款 200 N A 400 N A 100 N A 第一行 如显示为 5001 10页资料册 蓝 的格式 在使用vlookup等函数时 往往需要两个表中的内容是一样的 为此 就必须将例一中零散的将名称合并成分销中的格式 合并的公式详见灰色单元格 由于表二中的客户名称都 都是简称 前后加上 号后 后 表示只要中间有一 一部分字符相等即可 第二行 第三行 hlookup函数是横向查找 找 应收账款是在第三 三行 所以参数是3 销 销售额是第二行 所以 以参数是2 第第三三章章 lenlen findfind leftleft rightright midmid函函数数应应用用 len find left right mid都是和文本处理有关的函数 很多人对这几个函数比较陌生 但它们却是有很大作用 这一章里 我们挨个来看它们的本事 一一 len len 函函数数 一一 函函数数介介绍绍 计算单元格中有多少个字符 二二 函函数数应应用用举举例例 例例一一 名名称称公公式式 得力公司4 123公式 0 a1 2 显显示示提提示示 是是 二二 find find 函函数数 一一 函函数数介介绍绍 查找某个字符或字段在单元格中的位置 二二 函函数数应应用用举举例例 例例一一 名名称称查查找找字字符符公公式式 我们是优秀的得力财务员工优4 5757 文件夹 蓝色 6 5757 文件夹 蓝色 5 三三 left left 函函数数 一一 函函数数介介绍绍 取值函数 从单元格的左边开始 取出指定数量的字符 二二 函函数数应应用用举举例例 例例一一 名名称称取取数数字字数数公公式式 5757 文件夹 蓝色 45757 得力集团有限公司得力 四四 right right 函函数数 一一 函函数数介介绍绍 取值函数 从单元格的右边开始 取出指定数量的字符 二二 函函数数应应用用举举例例 例例一一 名名称称取取数数字字数数公公式式 5757 文件夹 蓝色 4 蓝色 得力集团有限公司公司 五五 mid mid 函函数数 一一 函函数数介介绍绍 取值函数 从单元格的中间开始 取出指定数量的字符 二二 函函数数应应用用举举例例 例例一一 名名称称公公式式 5757 文件夹 蓝色 文件夹 六六 取取数数函函数数的的组组合合应应用用 一一 函函数数应应用用举举例例 例例一一 名名称称公公式式 550斤苹果3550 6320斤大米46320 48斤金龙鱼食用调和油248 要求 在D76 D78中输入公式 取出商品的货号 公式必须是一样的 如第三个取出来的值应该是 35432 例例二二 名名称称公公式式 5757文件夹 蓝色蓝色 758订书机 白白 35432胶带 浅蓝色浅蓝色 要求 在D85 D87中输入公式 取出商品的颜色 公式必须是一样的 如第三个取出来的值应该是 浅蓝色 例例三三 名名称称公公式式 5757 文件夹 蓝色 文件夹 758 订书机 白 订书机 35432 胶带 浅蓝色 胶带 要求 在D94 D96中输入公式 取出商品的名称 公式必须是一样的 如第三个取出来的值应该是 胶带 因为C14单元格是空的 所以显示0 C15单元格有字母 只是字体颜色是白 的 看不出来 C16单元格里有两个空 格 所以显示2 说明 优字在B30单元格中从左往右是第4个 为什么看上都是三个空 空单元格 但公式计算 算出来却分别是0 1 2 呢 len find left right mid都是和文本处理有关的函数 很多人对这几个函数比较陌生 但它们却是有很大作用 文件夹三个字在B31单元格从左往右是 第6个 注意直接输入查找字符时 要 放在 中 当查找空格时 要把空格输入 中 不能直接输入 说明 取出B43单元格前4个字符 注意 可 以将其他单元格的值作为参数 如C43 取出B44单元格前两个字符 注意 可 以直接将参数2输在公式里 说明 取出B55单元格后4个字符 注意 可 以将其他单元格的值作为参数 如C55 取出B56单元格后两个字符 注意 可 以直接将参数2输在公式里 说明 取出B67单元格的第6个字符开始取 取3个字符 说明 分析规律后 发现需要取的数字后面都 有个 斤 字 所以 把 斤 字前面 的字段取出即可 说明 分析规律后 发现需要取的颜色前面都 有个 所以 从右边开始取 取 的数刚好是单元格所有字符数减去到 的位置数 要求 在D76 D78中输入公式 取出商品的货号 公式必须是一样的 如第三个取出来的值应该是 35432 要求 在D85 D87中输入公式 取出商品的颜色 公式必须是一样的 如第三个取出来的值应该是 浅蓝色 说明 分析规律后 发现需要取的名称前面都 有个 名称后面都有一个 所有用mid函数 从 加1的位置 开始取 取 之前 之后的 字符 要求 在D94 D96中输入公式 取出商品的名称 公式必须是一样的 如第三个取出来的值应该是 胶带 第第四四章章 选选择择性性粘粘贴贴 的的奇奇妙妙应应用用 选择性粘贴 想必大家都很熟悉 不过 选择性粘贴 中却有很多奇妙的应 在这里和大家共亨一下 一一 选选择择性性粘粘贴贴 介介绍绍 当复制一个单元格或区域 再右键单击 选择 选择性粘贴 时 会弹出如图2所示的 选择性粘贴 对话框 这一章 主要介绍 公式 数值 格式 批注 运算 转置 等六项最常用功能 图图1 1 图图2 2 二二 各各选选项项功功能能介介绍绍 一一 公公式式 介介绍绍 只将被复制单元格的公式复制过来 其他格式都不复制 例一 商商品品名名称称数数量量单单价价金金额额 0101 打孔机10010 001 000 00 表1 商商品品名名称称数数量量单单价价金金额额 将E31单元格直接复制 制到E35单元格 会把 E35单元格的公式 格 格式设置等全部复制 制到E35单元格 破坏 坏了E35单元格的整体 将E31单元格 选择 择性粘贴 公式 粘贴到E38单元格 格 仅复制了公式 式 没有破坏E38单 元格的原格式 0010 订书钉105 0050 00 表2 商商品品名名称称数数量量单单价价金金额额 0010 订书钉105 0050 00 二二 数数值值 介介绍绍 只将被复制单元格的数值复制过来 其他格式都不复制 例二 商商品品名名称称数数量量单单价价销销售售额额 0101 打孔机10330 00 0102 打孔机20360 00 0103 打孔机30260 00 0104 打孔机40280 00 0105 打孔机50150 00 0106 打孔机604240 00 表1 商商品品名名称称销销售售额额 0101 打孔机 VALUE 0102 打孔机 VALUE 0103 打孔机 VALUE 0104 打孔机 VALUE 0105 打孔机 VALUE 0106 打孔机 VALUE 表2 商商品品名名称称销销售售额额 0101 打孔机30 00 0102 打孔机60 00 0103 打孔机60 00 将E48 E53区域的 的内容直接复制 制到表1 复制出 出来的结果是错 将E48 E53区域的 的内容通过 选 择性粘贴 数 值 复制到表2 复制出来的结果 果是正确的 将E31单元格直接复制 制到E35单元格 会把 E35单元格的公式 格 格式设置等全部复制 制到E35单元格 破坏 坏了E35单元格的整体 将E31单元格 选择 择性粘贴 公式 粘贴到E38单元格 格 仅复制了公式 式 没有破坏E38单 元格的原格式 0104 打孔机80 00 0105 打孔机50 00 0106 打孔机240 00 三三 格格式式 介介绍绍 只将被复制单元格的格式复制过来 其他如公式 数值等都不会被复制 例三 商商品品名名称称数数量量单单价价金金额额 0101 打孔机10010 00 U US S 1 1 0 00 00 0 0 00 0 表1 商品名称数量单价金额 青菜10220 四四 批批注注 介介绍绍 只将被复制单元格的批注复制过来 其他如公式 数值等都不会被复制 例四 商商品品名名称称数数量量单单价价销销售售额额 0101 打孔机10330 00 0102 打孔机20360 00 0103 打孔机30260 00 0104 打孔机40280 00 0105 打孔机50150 00 0106 打孔机604240 00 将E80区域的内 内容通过 选择 择性粘贴 格 式 复制到表1的 E84单元格 并 并观察效果 将B94单元格内 内有批注 通过 选择性粘贴 批注 可以将该 该批注复制到其 其他单元格 大 大家可以试一下 将E48 E53区域的 的内容通过 选 择性粘贴 数 值 复制到表2 复制出来的结果 果是正确的 五五 运运算算 介介绍绍 对被复制单元格进行相应的计算 格式 内容等都不复制 例例五五 假设 该表的销售额要求用 万元 为单位显示 但你在做表时 却是以元为单位的 此时 你想一次性把销售额从元换算为 万元 请按如下步骤操作 1 在任意空单元格中输入10000 如在G114单元格 2 复制G114单元格 3 选择销售额区域 E112 E117 右键单击 选 选择性粘贴 除 点 确定 再看一下效果 商商品品名名称称数数量量单单价价 元元 销销售售额额 万万元元 0101 打孔机10000330 000 00 0102 打孔机20000360 000 00 0103 打孔机3000002600 000 00 0104 打孔机4000002800 000 00 0105 打孔机50000150 000 00 0106 打孔机600004240 000 00 例例六六 客客户户应应收收账账款款 北京大学1000 清华大学2000 上海交大1000 浙江大学2000 复旦大学1000 中国人大2000 合计0 显显示示提提示示否否 C128单元格输入 SUM 合计公式 但计算出来的 的结果却是0 这 是什么原因呢 六六 转转置置 介介绍绍 对被复制区域按顺时针方向转90度进行复制 例例七七 季季度度 客客户户 1 1季季度度2 2季季度度3 3季季度度4 4季季度度 北京大学100100100100 清华大学200200200200 上海交大300300300300 浙江大学400604007 复旦大学5050040500 中国人大600600600600 合计1650176016401707 表表1 1 季季度度销销售售额额 1季度 2季度 3季度 4季度 显显示示提提示示否否 请思考一下 怎 怎样将例七中的 的每季度的合计 计销售额 一次 次性复制到表1中 这是一个非常非常有用 用的对话框 大家可以 以仔细琢磨一下对话框 框中的18项功能 选择性粘贴 想必大家都很熟悉 不过 选择性粘贴 中却有很多奇妙的应 在这里和大家共亨一下 当复制一个单元格或区域 再右键单击 选择 选择性粘贴 时 会弹出如图2所示的 选择性粘贴 对话框 这一章 主要介绍 公式 数值 格式 批注 运算 转置 等六项最常用功能 将E31单元格直接复制 制到E35单元格 会把 E35单元格的公式 格 格式设置等全部复制 制到E35单元格 破坏 坏了E35单元格的整体 将E31单元格 选择 择性粘贴 公式 粘贴到E38单元格 格 仅复制了公式 式 没有破坏E38单 元格的原格式 将E31单元格直接复制 制到E35单元格 会把 E35单元格的公式 格 格式设置等全部复制 制到E35单元格 破坏 坏了E35单元格的整体 将E31单元格 选择 择性粘贴 公式 粘贴到E38单元格 格 仅复制了公式 式 没有破坏E38单 元格的原格式 将E80区域的内 内容通过 选择 择性粘贴 格 式 复制到表1的 E84单元格 并 并观察效果 将B94单元格内 内有批注 通过 选择性粘贴 批注 可以将该 该批注复制到其 其他单元格 大 大家可以试一下 10000 假设 该表的销售额要求用 万元 为单位显示 但你在做表时 却是以元为单位的 此时 你想一次性把销售额从元换算为 万元 请按如下步骤操作 1 在任意空单元格中输入10000 如在G114单元格 2 复制G114单元格 3 选择销售额区域 E112 E117 第第五五章章 定定位位 的的应应用用 EXCEL的 定位 功能可能很多人都不太了解 希望通过该章简单的介绍 在以后的工作中让 定位 功能为你服务 一一 一一个个有有趣趣的的小小测测试试 一个关于 定位 的小测试 小小测测试试 二二 定定位位的的应应用用 图图1 1 可以根据 定位条件 对话框 设置需要定位的条件 来选择符合条件的单元格 现在实例方式 列举 定位 的应用 例例一一 商品名称1号2号3号小计4号 A40404012040 B2020206020 C3030309040 小计909090270450 E101010303 F22265 小计1212123660 按F5 键 在弹出的对 对话框选择 定位条 条件 则弹出如图1 所示的 定位条件 对 显显示示提提示示否否 例例二二 例例三三 商品名称商品系列 A订书机 订书机系列 B订书机 C订书机 D订书机 F订书机 A订书针 三针一钉系列B订书钉 C订书钉 A笔记本 笔记本系列 B笔记本 怎样能一次性选择这 这些 框 只要在定位条件里 里选 对象 即可 怎样才能将例三中的 的商品系列那一列转 转换表1中的商品系 系列样式呢 C笔记本 D笔记本 显显示示提提示示否否 例例四四 137540891187285211571286 812524609624990921758 3623582075342878806679 782238206370506462779086 9495219232266607471190 87772776705375618213511 884593945364948418243636 418531861927190669727418 944090197577726035402027 34982 显显示示提提示示否否 E93单元格是由那些 些单元格计算出来的 的 有什么方法能快 快速把这些单元格同 笔记本系列 假如这是你做的一个 表 这个表公式是固定 不变的 而白色单元格 是需要你输入数值的 怎样才能一次性将手工 输入的数值删除 同时 又能保留公式不被删除 呢 EXCEL的 定位 功能可能很多人都不太了解 希望通过该章简单的介绍 在以后的工作中让 定位 功能为你服务 可以根据 定位条件 对话框 设置需要定位的条件 来选择符合条件的单元格 现在实例方式 列举 定位 的应用 按F5 键 在弹出的对 对话框选择 定位条 条件 则弹出如图1 所示的 定位条件 对 表1 商品名称商品系列 A订书机订书机系列 B订书机订书机系列 C订书机订书机系列 D订书机订书机系列 F订书机订书机系列 A订书针三针一钉系列 B订书钉三针一钉系列 C订书钉三针一钉系列 A笔记本笔记本系列 B笔记本笔记本系列 怎样能一次性选择这 这些 框 只要在定位条件里 里选 对象 即可 C笔记本笔记本系列 D笔记本笔记本系列 9316 5008 9383 8371 6312 6284 5398 3056 3976 E93单元格是由那些 些单元格计算出来的 的 有什么方法能快 快速把这些单元格同 第第五五章章 定定位位 的的应应用用 oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo ooooooooooooooooooo000000000000000000ooooooooooooooooooooo oooooooooooooooo000000000000000000000000oooooooooooooooooo oooooooooooooo0000000000000000000000000000oooooooooooooooo ooooooooooooo000000000000000000000000000000ooooooooooooooo ooooooooooooo000000000000000000000000000000ooooooooooooooo oooooooooooo00000000000000000000000000000000oooooooooooooo ooooooooooo0000000000000000000000000000000000ooooooooooooo ooooooooooo000000000o0o0000000000o0o000000000ooooooooooooo oooooooooo0000000oooooooooo000ooooooooo0000000oooooooooooo oooooooooo000000oooooooooooooooooooooooo000000oooooooooooo oooooooooo00000oooooooooooooooooooooooooo00000oooooooooooo oooooooooo0000oooooooooooooooooooooooooooo0000oooooooooooo oooooooooo000oooooooooooooooooooooooooooooo000oooooooooooo ooooooooooo00oooooooooooooooooooooooooooooo00ooooooooooooo ooooooooooo00oooooo000000oooooo000000oooooo00ooooooooooooo oooooooooooo0oooo0ooooooo0oooo0ooooooo0oooo0oooooooooooooo oooooooooooo0ooo0oooooooooooooooooooooo0oooooooooooooooooo oooooooooooooooooooooooooo0ooooooooooooooooooooooooooooooo ooooooooooo0ooooooo00000oo0ooooo000000oooooo0ooooooooooooo oooooooooo0o0oooo0oo00oo0o0oooo0oo00oo0oooo0o0oooooooooooo oooooooooo0ooooooooooooooo0oooooooooooooooooo0oooooooooooo oooooooooo0ooooooooooooooo0oooooooooooooooooo0oooooooooooo oooooooooo0oo0oooooooooooo0ooooooooooooooo0oo0oooooooooooo oooooooooo0oo0oooooooooooo0ooooooooooooooo0oo0oooooooooooo oooooooooo0ooooooooooooooo0oooooooooooooooooo0oooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooo0oooooooooooooo0oooo0oooooooooooooo0oooooooooooo ooooooooooooo0oooooooooo0oooooo0oooooooooo0ooooooooooooooo ooooooooooo0o0oooooooooo0o0oo0o0oooooooooo0o0ooooooooooooo ooooooooooooooooooooooooo0o00o0ooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooo0oooooooooooooooooooooooooooooo0oooooooooooooo oooooooooooooo0oooooooooooooooooooooooooo0oooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo ooooooooooooooo0ooooooooooo00ooooooooooo0ooooooooooooooooo ooooooooooooooooooooooo0000000000ooooooooooooooooooooooooo oooooooooooooooooooooooooo0000oooooooooooooooooooooooooooo ooooooooooooooooo0oooooooooooooooooooo0ooooooooooooooooooo oooooooooooooooooo0oooooooooooooooooo0oooooooooooooooooooo oooooooooooooooooo00oooooooooooooooo00oooooooooooooooooooo ooooooooooooooooo0ooo0oooooooooooo0ooo0ooooooooooooooooooo oooooooooooooooo0oooooo0oooooooo0oooooo0oooooooooooooooooo ooooooooooooooo0oooooooooo0000oooooooooo0ooooooooooooooooo oooooooooooooo0oooooooooooooooooooooooooo0oooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo 请按F5键 在弹出的 的对话框中选 定位 位条件 常量 然 然后只选 数字 一个 个选项 其他不选 点 确定 然后将 将单元格颜色涂成黑 返返回回课课件件 请按F5键 在弹出的 的对话框中选 定位 位条件 常量 然 然后只选 数字 一个 个选项 其他不选 点 确定 然后将 将单元格颜色涂成黑 第第六六章章 经经典典函函数数 sumif sumif 的的应应用用 sumif 是有条件求和函数 那是相当有用的一个函数 花五分钟学会这个函数 对工作很有帮助 一一 sumifsumif 函函数数 一一 函函数数介介绍绍 有条件求和 二二 函函数数应应用用举举例例 例例一一 表表1 1 日日期期商商品品名名称称销销售售金金额额商商品品称称 1号5383 D型二孔夹 混合 5735383 D型二孔夹 混合 1号0222 订书机10 混合 9220222 订书机10 混合 1号0302 订书机12 混合 8510302 订书机12 混合 1号7112 固体胶15g 无色 9657112 固体胶15g 无色 1号0101 打孔机 混合 1130101 打孔机 混合 2号0222 订书机10 混合 727合计 3号0302 订书机12 混合 651 4号7112 固体胶15g 无色 257公公式式说说明明 5号0302 订书机12 混合 48难以言传 只可意会 请仔细看公式的输入样式 6号7112 固体胶15g 无色 356 7号0101 打孔机 混合 824 8号0222 订书机10 混合 867 9号0302 订书机12 混合 171 10号7112 固体胶15g 无色 159 11号0101 打孔机 混合 907 12号0222 订书机10 混合 370 13号0302 订书机12 混合 32 14号7112 固体胶15g 无色 364 15号0302 订书机12 混合 143 合计9 300 例例二二 表表1 1 客客户户名名称称应应收收账账款款条条件件 上海新世纪 促销6 000应收账款 6000元 北京电视台 个案35 0006000元 应收账款 3000元 CCTV2 590应收账款 应收账款 3000元 这个条件进行汇总时 合计57 890 例例三三 日日期期商商品品名名称称销销售售金金额额 1号5383 D型二孔夹 混合 573 1号0222 订书机10 混合 922 1号0302 订书机12 混合 851 1号7112 固体胶15g 无色 9651 844 1号0101 打孔机 混合 113 2号0222 订书机10 混合 727 3号0302 订书机12 混合 651 4号7112 固体胶15g 无色 257 5号0302 订书机12 混合 48 6号7112 固体胶15g 无色 356 7号0101 打孔机 混合 824 8号0222 订书机10 混合 867 9号0302 订书机12 混合 171 根据例三的 销售明 明细表 在E59单 元格中输入公式 统 统计出 0101 打孔机 机 混合 的合计 10号7112 固体胶15g 无色 159 11号0101 打孔机 混合 907 12号0222 订书机10 混合 370 13号0302 订书机12 混合 32 14号7112 固体胶15g 无色 364 15号0302 订书机12 混合 143 合计9 300 合合计计销销售售金金额额 573 2 886 1 896 2 101 1 844 9 300 根据例一的 销售明 明细表 在表1的 销售汇总表 里求出 出每个单品的合计销 销售金额 根据条件对 例二中的应 收账款进行 汇总 sumif 是有条件求和函数 那是相当有用的一个函数 花五分钟学会这个函数 对工作很有帮助 难以言传 只可意会 请仔细看公式的输入样式 应应收收账账款款 35 000 17 600 5 290 57 890 6000元 应收账款 3000元 这个条件进行汇总时 根据例三的 销售明 明细表 在E59单 元格中输入公式 统 统计出 0101 打孔机 机 混合 的合计 根据条件对 例二中的应 收账款进行 汇总 第第七七章章 ifif andand oror函函数数的的应应用用 了解了if and or三个函数 你会有一种 表随我动 惬意感觉 一一 if if 函函数数 一一 函函数数介介绍绍 这是一个判断函数 有了它 你就可以和EXCEL进行对话 使EXCEL具备 逻辑思维 能力 二二 函函数数应应用用举举例例 例例一一 重重点点客客户户销销售售额额 1 500 客客户户名名称称销销售售额额是是否否重重点点客客户户 上海新世纪 促销1 000 北京电视台 个案2 000重点客户 CCTV1 500重点客户 洛克希德 马丁公司200 波音公司2 400重点客户 南昌花旗有限公司 植根中国900 青岛啤酒公司 促销850 公公式式说说明明 IF C14 C 12 重点客户 if函数有一个条件 两个参数 该公式的意思是 如果C14单元格的值大于等于C12单元格 则显示 重点客户 两字 否则显示空白 注意两个引号 表示显示空白 例例二二 商商品品名名称称单单位位账账面面数数盘盘点点数数 5383 D型二孔夹 混合 只100100 0222 订书机10 混合 只200203 在 是否重点客户 列输 输入公式 当该客户 户的销售额大于或等 等于C12单元格设定的 的销售额时 则显示 重点客户 四字 否则 0302 订书机12 混合 件150145 7112 固体胶15g 无色 支240255 0101 打孔机 混合 只310299 0223 订书机10 混合 只890890 0303 订书机12 混合 只9899 公公式式说说明明 if 函数可以多个组合在一起使用 在EXCEL2003以前的版本 一个公式中最多可以使用7个if函数 例例三三 应应税税所所得得额额税税率率速速算算扣扣除除数数 5005 0 大于500小于等于200010 25 大于2000小于等于500015 125 大于5000小于等于2000020 375 表表1 1 工工资资表表 姓姓名名应应发发工工资资个个人人所所得得税税 张三丰2 40020 杨过3 10085 小龙女1 800 庄聚贤4 500250 程灵素5 200355 钟灵儿14 0002 025 张无忌1 600 郭靖8 000825 输入公式 自 动计算出个人 所得税 例例四四 客客户户名名称称余余额额方方向向余余额额 上海新世纪 促销借10 000 北京电视台 个案借1 500 CCTV贷2 300 洛克希德 马丁公司贷4 500 波音公司借2 340 南昌花旗有限公司 植根中国借4 560 青岛啤酒公司 促销借4 320 大众集团贷12 220 通用公司借42 110 民生银行借20 000 凤凰股份有限公司贷1 500 ifif 函函数数的的用用法法还还有有很很多多 这这里里只只起起到到抛抛砖砖引引玉玉的的作作用用 留留待待大大家家以以后后慢慢慢慢琢琢磨磨 二二 and and 函函数数 一一 函函数数介介绍绍 这是一个逻辑条件函数 类似于并且的意思 往往与if函数一起使用 二二 函函数数应应用用举举例例 例例一一 客客户户名名称称应应收收账账款款是是否否超超账账龄龄是是否否重重点点管管理理 上海新世纪 促销10 000是重点管理 北京电视台 个案1 500 CCTV2 300是 洛克希德 马丁公司4 500是 波音公司2 340 南昌花旗有限公司 植根中国4 560 青岛啤酒公司 促销5 900是重点管理 大众集团12 220 通用公司42 110 民生银行20 000是重点管理 凤凰股份有限公司1 500 三三 or or 函函数数 一一 函函数数介介绍绍 这也是一个逻辑条件函数 类似于或者的意思 往往与if函数一起使用 二二 函函数数应应用用举举例例 客客户户名名称称销销售售额额是是否否特特批批返返利利金金额额 上海新世纪97 000特批9 700 北京电视台90 0004 500 CCTV150 00015 000 洛克希德 马丁公司310 00031 000 波音公司270 00027 000 南昌花旗有限公司60 0003 000 青岛啤酒公司94 000特批9 400 大众集团65 0003 250 通用公司520 00052 000 民生银行25 0001 250 凤凰股份有限公司310 00031 000 盈盈亏亏 盘盈3只 在 盈亏 列输入公式 式 使其根据盘点结 结果显示 盘盈X 或 盘亏X 的格式 这是一个判断函数 有了它 你就可以和EXCEL进行对话 使EXCEL具备 逻辑思维 能力 if函数有一个条件 两个参数 该公式的意思是 如果C14单元格的值大于等于C12单元格 则显示 重点客户 两字 否则显示空白 在 是否重点客户 列输 输入公式 当该客户 户的销售额大于或等 等于C12单元格设定的 的销售额时 则显示 重点客户 四字 否则 盘亏5件 盘盈15支 盘亏11只 盘盈1只 借方表示客户欠我 们的钱 贷方表示 我们欠客户的钱 现在要求转换成借 方用正数 贷方用 负数表示 if 函数可以多个组合在一起使用 在EXCEL2003以前的版本 一个公式中最多可以使用7个if函数 输入公式 自 动计算出个人 所得税 在 盈亏 列输入公式 式 使其根据盘点结 结果显示 盘盈X 或 盘亏X 的格式 余余额额 10 000 1 500 2 300 4 500 2 340 4 560 4 320 12 220 42 110 20 000 1 500 在 是否重点管理 列输入公式 如 如果应收账款大 大于5000 并且 且 已超账龄的 的客户 则显示 重点管理 四字 否则显示空白 借方表示客户欠我 们的钱 贷方表示 我们欠客户的钱 现在要求转换成借 方用正数 贷方用 负数表示 要求 销售额在10 万元以上的 或 或者是特批的客 客户 按10 计算 算返利 否则按5 计算返利 在 是否重点管理 列输入公式 如 如果应收账款大 大于