《excel学习教程》PPT课件.ppt

基础篇 第1到第5章应用篇 第6到第10章分析篇 第11到第16章 MicrosoftExcel从入门到精通 第六章创建和使用公式 第一节公式的基本概念公式是指对工作表数据进行运算的算式 利用公式可以进行数学运算 可以比较工作表数据并返回布尔值等公式通常由以下五种元素组成 运算符 单元格引用 文字 数字 字符串等 工作表函数 括号公式以等号 开始 等号后面是要进行计算的操作数 运算符 函数等公式按照运算符特定顺序从左至右进行运算 括号 可改变运算顺序 先运算括号内 再运算括号外 应用篇 第二节公式中使用的运算符算术运算符 加 减 乘 除 百分号 乘方比较操作符 大于等于 不等于文本串联符 文字串联符 把两个字符串首尾连接引用操作符 冒号 引用两个地址间的所有单元格 如B5 C6 包含B5 B6 C5 C6四个单元格 逗号 引用逗号前后的两个单元格或单元格区域 如A1 B2 B4 C5 包含A1 A2 B1 B2和B4 B5 C4 C5 公式中符号运算优先次序 即 引用 负号 百分号 乘方 乘 除 加 减 文本连接符 比较运算符第三节输入公式直接在单元格中输入公式单击要输入公式单元格输入等号 输入公式内容 回车确认使用 公式选项板 输入公式单击编辑栏上 编辑公式 按钮 直接输入公式 或者再单击所需函数 出现 公式选项板 公式中单元格的输入选重要输入公式单元格 输入等号 用鼠标单击要引用单元格 输入单元格地址 输入运算符继续上述操作 若输入完毕 按回车键或单击编辑栏上 确认 按钮结束 也可按Esc或编辑栏上 取消 按钮取消输入第四节编辑公式编辑公式就向编辑其他单元格一样双击单元格可在单元格内部直接编辑公式选择要编辑的单元格 再单击 编辑栏 编辑公式单击 编辑栏 中的 编辑公式 按钮 再使用 公式选项板 编辑公式第五节单元格和区域引用一个引用地址代表工作表上的一个或一组单元格 引用地址告诉Excel在哪些单元格中查找公式中要用的数值通过使用引用地址 可以在一个公式中使用工作表不同部分的数据 也可以在几个公式公式用同一单元格数值 同时还可以引用同一工作簿不同工作表的数据 或者引用其他工作簿的数据相对引用在输入和创建公式的过程中 若非特别指明 通常采用单元格的相对位置相对地址是当公式复制到新位置或用一个公式增加一个新的范围时公式中单元格地址会改变在复制包含相对引得公式时 Excel会自动调整复制公式中的引用 以引用相对于当前公式位置的其他单元格 如 A1 B5 A2 C3等 绝对引用一般情况下 复制单元格地址时使用使用相对地址 但我们如果不希望复制公式时系统自动调整引用 就要使用绝对地址引用绝对地址引用是指在复制公式或把公式填入到新位置时 公式的固定单元格地址不变Excel中通过在行号列号前增加 符号表示绝对引用 如 A 4 B 7等 混合地址引用相对地址和绝对地址可以混合使用如 A1 A 8等三维地址引用所谓三维地址引用是指 在同一工作簿中引用不同工作表中单元格或单元格区域中的数据 三维地址引用的一般格式为 工作表名称 单元格地址如 Sheet3 B3 C4即Sheet3工作表中B3单元格的值和当前工作表中C4单元格的值相加注意避免循环引用如果公式引用自己所在的单元格 不论是直接还是间接 都称为循环引用如 A1单元格的公式为 A1 A2 A3当系统计算此类公式时 必须使用前一次的结果来进行下一次的计算 一般来说Excel将在100次计算后或相邻两次计算值变化小于0 001时 停止迭代计算过程 可在菜单 工具 选项 重新计算 中修改 第六节移动和复制公式 移动和复制单元格的操作同移动复制单元格操作移动复制公式会产生单元格地址的变化可使用 剪切 复制 粘贴 也可使用鼠标拖拽 还可使用填充柄移动复制公式对相对引用的影响Excel会自动调整位于新粘贴单元格区域内的所有的相对地址引用和混合引用的相关部分移动复制单元格对绝对引用的影响移动复制公式对绝对引用和混合引用中的相关部分没有影响 引用地址保持不变第七节在工作表中使用名称可用行列标题引用该行或该列数据可用 列标题行标题 引用该列该行单元格数据 可使用已定义的名称引用相应的数据第八节公式与常见错误 第七章工作表函数的使用 函数是一些预定义的公式 它们使用一些称为参数的特定数值按特定的顺序或结构进行计算 如 SUM函数用来求和 AVERAGE函数用来求平均第一节了解工作表函数在Excel中使用函数 必须先将它们输入到公式中若函数的参数也是函数 称为函数的嵌套一个公式最多可以嵌套7层函数的参数的开始和结束用 括起 必须成对出现 前后不能有空格函数的结构以函数名称开始 然后是左圆括号 以逗号分开的参数和右圆括号 应用篇 函数结构如 例如 求平均 AVERAGE A1 A5 例如 求最大值 MAX A1 B10 函数语法有的函数带有必选参数和可选参数如 CELL info type reference 加粗的是必选参数 常规是可选参数必选参数一定要有 可选参数可省略正确使用 CELL format B12 CELL format 但CELL 是错误的 因为没有必选参数有的函数参数后跟省略号 如 SUM number1 number2 带省略号表示用户可使用该数据类型的多个参数正确的使用 SUM 26 31 SUM A1 B4 A1 B2 等有的函数不带参数如 NOW 虽然这些函数没有参数 但使用时一定要带 常用参数类型number 数字text 文本reference 引用value 任意单值结果logical 逻辑值函数中的逗号函数中使用逗号分隔不同参数 不可多键入逗号如果用逗号预留了一个参数位置而未输入该参数 系统将用默认值代替该参数 但必选参数除外 例如 求平均AVERAGE 1 2 3 4 5 结果为3 若输入为AVERAGE 1 2 3 4 5 则结果为 0 0 1 2 3 4 5 7 2 14 因为用逗号预留的参数用缺省值0补上参与了计算第二节在公式中输入函数两种函数输入方法手工输入使用粘贴函数手工输入函数选定单元格在编辑栏单击 编辑公式 按钮单击 函数 下拉列表框 选择函数 如果函数不在下拉列表中 单击列表中 其他函数 项在粘贴函数对话框中选择函数并输入相应参数 粘贴函数选定单元格菜单 插入 函数 或单击工具栏上 粘贴函数 按钮 出现对话框选择函数的种类和具体函数后 确定 填入函数所需的参数 利用 范围 按钮可以方便快捷输入引用区域 第三节数学和三角函数SUM函数格式 SUM number1 number2 参数为1 30个例 SUM 4 6 值为10例 SUM 3 6 FALSE 值为9 因为文本 3 被自动 转换为数字3 逻辑值FALSE被转换为数字0例 若F10单元格内容为文本 3 则 SUM F10 6 的值为6 因为对非数值型的值的引用不能被转换成数值参与计算如果参数为引用 只有其中的数字被计算 引用中的文本 逻辑值 错误值将被忽略如果参数错误或不能被转换成数字的文本 将导致错误SUMIF函数格式 SUMIF RANGE criteria sum RANGE RANGE 用于条件判断的单元格区域criteria 为确定哪些单元格将被求和的条件sum RANGE 为需要求和的实际单元格 只有当RANGE中相应单元格满足条件 才对sum RANGE 中的单元格求和 若省略sum RANGE 则直接对RANGE中的单元格求和如 SUM A1 A5 1000 B1 B5 INT函数格式 INT number 对number取整如 INT 12 4 的值是12如 INT 12 4 的值是 13RAND函数格式 RAND 返回大于等于0小于1的随机数如 RAND 100 生成1个0 100的数如 INT RAND 100 生成1个0 100的整数 ROUND函数格式 ROUND number num digits 根据number digits指定的位数为number四舍五入如 ROUND 3 52 1 值是3 5如 ROUND 4 276 2 值是 4 28如 ROUND 46 5 1 值是50PI函数格式 PI 返回圆周率3 1415926 精确到小数点后15位如 SIN PI 2 值是1SIN函数格式 SIN number 返回给定弧度的正弦值 SQRT函数格式 SQRT number 返回number的平方根ABS函数格式 ABS number 返回number的绝对值TRUNC函数格式 TRUNC number num digits 根据num digits指定的位数对number截取小数部分num digits默认值为0如 TRUNC 9 3 值为 9LOG函数格式 LOG number base 返回以base为底number的对数 缺省的base位10如 LOG 16 2 值是4COUNTIF函数格式 COUNTIF RANGE criteria 计算RANGE指定区域内满足criteria条件的单元格数目如 A3 A6内容分别为 apples oranges peaches apples 则 COUNTIF A3 A6 apples 值为2如 B3 B6内容分别为32 54 75 86 则 COUNTIF B3 B6 55 值为2第四节文本函数CHAR函数格式 CHAR number 返回一个ASCII码为number 取值1 255 的字符如 CHAR 65 值为 A LEFT函数和LEFTB函数格式 LEFT text num chars 格式 LEFTB text num bytes 主要应用于中文字符返回text文本串中前chars个字符 缺省的num chars为1 若其大于text总长度 返回整个字符串如 LEFT Sale Price 4 值是 Sale 如 A1单元格内容为 Soft 则 LEFT A1 值是 S 如 LEFT 计算机 2 值是 计算 如 LEFTB 计算机 4 值是 计算 LEN函数和LENB函数格式 LEN text 格式 LENB text 主要应用于中文字符返回text字符串的长度 空格也作为字符参与计算如 LEN 值是0如 LEN 值是1如 LEN 计算机 值是3如 LENB 计算机 值是6MID函数和MIDB函数格式 MID text start num 格式 MIDB text start num num bytes 返回指定字符串text中从start num开始长度为num chars或num bytes的子字符串如果start num 返回 空文本如果start num LEN text 但start num num chars LEN text 则返回从start num到字符串末尾的子字符串如 MID Fluidfirm 7 15 值是 firm 如 MID 1234 5 5 值是 空文本如 MID 计算机 1 2 值是 计算 如 MIDB 计算机 1 2 值是 计 REPLACE函数和REPLACEB函数格式 REPLACE old text start num num chars new text 格式 REPLACEB old text start num num bytes new text 在old text中从start num开始用new text替换num char或num bytes个字符如 REPLACE 123456 1 3 值是 456 如 REPLACE 计算机 1 2 值是 机 如 REPLACEB 计算机 1 2 值是 算机 SEARCH函数和SEARCHB函数格式 SEARCH find text start num 格式 SEARCHB find text within text start num 在within text字符串中从处开始查找find text 缺省start num为1 在find text中可以使用通配符 和 SEARCH函数和SEARCHB函数查找指定字符串时不区分大小写如 SEARCH e Statements 6 值是7如 SEARCHB 建 福建省 值是3如 SEARCH 建 福建省 值是2 如 若B10单元格内容 margin A10单元格内容 ProfitMargin 则 SEARCH B 10 A 10 值是8如 上例 REPLACE A 10 SEARCH B 10 A 10 6 Amount 值是 ProfitAmount FIND函数和FINDB函数格式 FIND find text within text start num 格式 FINDB find text within text start num 在within text字符串中从处开始查找find text 缺省start num为1在find text中不可以使用通配符 和 查找指定字符串时区分大小写如 FIND M MiriamMc 值是1如 FIND m MiriamMc 值是6如 FIND M MiriamMc 3 值是8 如 FIND 建 福建省 值是3如 FINDB 建 福建省 值是2UPPER函数和LOWER函数格式 UPPER text 格式 LOWER text 将字符串转化成大写 小写形式如 UPPER Total 值是 TOTAL 如 LOWER Total 值是 total EXACT函数格式 EXACT text1 text2 比较第一个字符串text1和第二个字符串text2是否完全相同EXACT区别大小写 但是忽略格式的不同 如 EXACT James James 值是TRUE如 EXACT Diane diane 值是FALSE第五节逻辑函数IF函数格式 IF logical test value if true value if false logical test是值为逻辑值TRUE或FALSE的逻辑表达式 若条件成立 返回value if true的值 否则返回value if false的值 value if false可省略如 A10单元格中存放当前预算值 如果A10值大于100 显示 超出预算 否则显示 预算内 IF A10 100 预算内 超出预算 如 IF A10 100 SUM B5 B10 IF函数可以嵌套 如果针对 average 的值 在90 100 为 A 80 89 为 B 70 79 为 C 60 69 为 D 60分以下 为 FAIL 可使用嵌套的IF函数 IF average 89 A IF average 79 B IF average 69 C IF average 59 D FAIL AND函数格式 AND logical1 logical2 所有的参数均为真 返回逻辑值 TRUE 否则返回 FALSE 如 AND 1 4 1 2 6 8 值是FALSEOR函数格式 OR logical1 logical2 所有的参数只要有一个为真 返回逻辑值 TRUE 否则返回 FALSE 如 OR 1 4 1 2 6 8 值是TRUENOT函数格式 NOT logical 如果logical为真 返回FALSE 否则 返回TRUE如 NOT 1 3 2 值是TRUE第六节时间日期函数Excel将日期存储为一系列连续的序列数 时间被存储成小数 因为时间可以看作是天的一部分 它们被看作数值后可进行计算 如计算两个日期相差得天数等 默认情况下 1900年1月1日被看作系统的第一天 序列数是1 9999年12月31日是系统最后一天 如 2001年1月21日的序列数是36912 TODAY函数格式 TODAY 返回当前的系统日期如 假设今天是2001年1月21日 则 TODAY 的值是1 21 2000DATE函数格式 DATE year month day 返回year年 month月 day日的序列号如 DATE 2001 1 21 值是36912如果year在0 1899间 系统会自动在此数值上加1900 当作年份进行计算 如果year在1900 9999间 系统直接将此数值当作年份计算如 DATE 100 1 2 等于 DATE 1900 100 1 2 值是36527 WEEKDAY函数格式 WEEKDAY serial number return type 返回serial number代表的日期是星期几 return type为确定返回值类型的数字 可省略 默认是1如 WEEKDAY 2001 1 21 值是1 表示星期日如 WEEKDAY 35981 007 值是1 表示星期日TIME函数格式 TIME hour minute second 返回hour小时 minute分钟 second秒对应的小数值hour取值1 23 minute和second取值0 59如 TIME 12 0 0 值是0 5第七节财务函数财务函数可以进行一般的财务计算 例如确定贷款的支付额 投资的未来值或净现值 以及债券或股票的价值常用的参数 未来值 fv 在所有付款发生后的投资或贷款价值期间数 nper 投资的总支付期间数付款 pmt 对意向投资或贷款的定期支付数额现值 pv 投资初的投资或贷款价值利率 rate 投资或贷款的利率或贴现率类型 type 付款期间内进行支付的间隔 SLN函数格式 SLN cost salvage life 返回一项资产每期的直线折旧费如 一辆价值 30000的卡车 折旧期限10年 资产残值 10000 则每年折旧额为 SLN 30000 10000 10 为 2000RATE函数格式 RATE nper pmt pv type guess 返回年金的各期利率如 金额为 8000的4年期贷款 月支付额为 200 则该笔贷款的利率为 RATE 48 200 8000 为0 77 第八节统计函数AVERAGE函数 格式 AVERAGE number1 number2 返回若干个参数的算术平均值如 AVERAGE 1 2 3 值是2如 A1 A4单元格值为2 3 4 5 则 AVERAGE A1 A4 6 值是4COUNT函数格式 COUNT value1 value2 返回参数个数 计数时 将把数字 空值 逻辑值 日期或以文字代表的数计算在内 错误值和无法转换成数字的文字被忽略如 B1 B4单元格内容是12 8 99 空 10 DIV 0 则 COUNT B1 B4 值是1MAX函数和MIN函数格式 MAX number1 number2 格式 MIN number1 number2 返回参数中最大 小值如果A1 A5中依次包含数值10 7 3 27和2 那么 MIN A1 A5 等于2 MIN A1 A5 0 等于0 MAX A1 A5 等于27第九节函数嵌套在某些情况下 我们可能将某函数作为另一函数的参数使用 以增加公式使用的灵活性 并且加快数据处理的速度当前套函数作为参数使用时 它返回的类型必须与参数使用的数值类型相同公式中最多可以包含7级嵌套如 IF AVERAGE B3 B7 SUM C2 C4 SUM C2 C4 0 第八章图表制作 图表具有较好的视觉效果 可方便用户察看数据的差异 图案和预测趋势第一节初步认识图表用图表表现数据可以在工作表上创建图表 或将图表作为工作表的嵌入对象使用图表的基本元素图表是与生成它的工作表数据相连接的与图表有关的工作表数据变化时 图表也会自动更新将鼠标停留在图表项上 系统会自动显示相关信息 应用篇 选定图表项的方法图表设置中 一般都是先选中某图表项 再单击右键选择快捷菜单或在 格式 菜单中执行命令1 单击所需图表项2 单击图表 然后在 图表 工具栏中单击 图表对象 下拉箭头 视图 工具栏 图表 显示图表工具栏 使用鼠标移动 调整图表项大小单击选中要移动或调整大小的图表项如要移动 鼠标指向后拖拽鼠标到目的位置如要调整大小 鼠标指向尺寸柄变成双向箭头时拖拽鼠标第二节创建图表利用图表向导创建图表选定待显示于图表中的数据所在单元格 可以连续也可不连续 也可以不选 单击工具栏上 图表向导 按钮或菜单 插入 图表 根据向导提示 依次选择或输入图表类型 图表数据源 可在此时选择数据区域或数据系列 图表选项和图表位置 第三节修改图表类型常见图表类型 柱形图 显示一段时间内的数据变化或说明项目之间的比较结果 条形图 显示各个项目之间的比较情况 折线图 显示相同间隔内数据的预测趋势 饼图 显示构成数据系列的项目相对于项目总和的比例大小 散点图 面积图 圆环图 雷达图 曲面图 气泡图 股价图 圆柱图 圆锥图 棱锥图修改图表类型单击选中图表菜单 图表 图表类型 或鼠标右键单击图表区快捷菜单中选择 图表类型 第四节修改图表数据更改图表数据区域单击图表菜单 图表 数据源 或在图表区域单击鼠标右键 快捷菜单中选择 数据源 选择 数据区域 卡片后 选择数据系列产生在行 列的方式 也可重新选择图表产生数据区域选择 系列 卡片后 可以增加或删除图表产生的系列向图表中添加数据通过复制和粘贴向图表中添加数据选择要添加的数据的单元格菜单 编辑 复制 或工具栏上 复制 按钮 单击图表如果要让Excel自动将数据粘贴到图表 菜单 编辑 粘贴 或工具栏上 粘贴 按钮 如果要指定数据在图表中的绘制方式 菜单 编辑 选择性粘贴 使用颜色编码区域向嵌入式图表添加 修改数据选定图表区域拖动工作表中紫色 蓝色 绿色的选择柄进行数据的添加或修改通过拖动向嵌入式图表中添加数据选定包含添加数据的单元格 工作表中必须相连 鼠标指向选定区域的边界将选定区域拖动到希望更新的嵌入土表中第五节更改图表选项包括 标题 坐标轴 网格线 图例 数据标志 修改图表选项单击图表 菜单 图表 图表选项 或在图表区域单击鼠标右键 快捷菜单中选择 图表选项 依次选择 标题 卡片 坐标轴 卡片 网格线 和 数据标志 卡片 进行相应的设置第六节改变图表位置图表可以嵌入工作表 也可单独放在一个工作表修改图表位置单击图表 菜单 图表 位置 或在图表区域单击鼠标右键 快捷菜单中选择 位置 选择图表位置第七节设置图表格式数值轴 分类轴 数值轴主要网格线 绘图区 图表区数值轴标题 分类轴标题 图表标题图例可设置的格式 线条 边框 颜色 纹理 过渡填充等数字格式 轴刻度 刻度线等字形 字体 字号 旋转 颜色等设置方法单击选中对象 菜单 格式 单击选中对象 单击鼠标右键在快捷菜单中选择相应格式第八节更改三维图表修改三维图表的深度和宽度 三维图表中单击要修改数据系列菜单 格式 数据系列 再选择 选项 卡片修改三维图表的三维视图格式单击选定三维图表菜单 图表 设置三维视图格式 弹出的对话框中选择上下仰角 左右转角 透视系数和三维图表的高度第一节在工作表中插入图片Excel中涉及到的图片有 剪贴画 来自文件的图片 艺术字 自选图形 组织结构图在工作表中插入图片 第九章图形和数据地图 应用篇 菜单 插入 图片 剪贴画 从剪辑库中挑选合适剪贴画来自文件的图片 选择要插入文件所在的磁盘 文件夹和文件名 插入图片自选图形 当鼠标变成 时拖动鼠标画出图形 也可以使用绘图工具栏 艺术字 先选择艺术字样式 然后输入文字组织结构图 分别根据题是输入经理 经理助理 左同事 右同事等文字效果菜单 插入 图片 艺术字 绘图 工具栏上选择横排 竖排文本框绘制自选图形后单击鼠标右键 快捷菜单中选择 添加文本 第二节建立数据地图创建地图工作表中输入相关数据 并选定包含地理数据的单元格区域 如果只显示地图不显示数据 可不选定单元格区域 单击工具栏上 数据地图 按钮或菜单 插入 地图 鼠标变成 拖动鼠标选择合适地图大小