《EXCEL讲义》PPT课件.ppt

Excel电子表格 一 工作表的概念与编辑二 排版工作表 三 数据处理 四 图表的使用 五 公式与函数的使用 六 常用技巧介绍 一 工作表的概念与编辑 EXCEL窗口组成 工作薄与工作表的概念工作簿 Excel保存的文件称为工作薄 xls 每个工作薄包含若干个工作表 默认3个 由工作表标签Sheet1 Sheet2 Sheet3表示工作表 电子表格 位于工作薄的中央区 由行号 列号和网格线构成 单击行号1上A左的方块 显示工作表行号和列号 当前单元格 活动单元格 单击即可 右下角的黑色小方块为填充句柄 拖动填充单元格数据 一 工作表的概念与编辑 1 选定工作表 若要选中一张 则直接单击该工作表的标签即可 若要同时选中多张连续的工作表 则应按住Shift键单击第一个工作表标签和最后一根工作表标签 若要选中多张不连续的工作表 则应按住Ctrl键单击要选的工作表的标签2 插入工作表 鼠标右键单击工作表标签 在弹出的快捷菜单中选择 插入 工作表 命令 在菜单栏中选择 插入 菜单 然后选择 工作表 菜单命令 一 工作表的概念与编辑 3 删除工作表 鼠标右键单击要删除的工作表 在弹出的快捷菜单中选择 删除 菜单命令即可 选中要删除的一个或多个工作表 单击 编辑 菜单中的 删除工作表 命令 然后确定删除4 重命名工作表鼠标右键单击要更名的工作表标签 然后在出现的快捷菜单上选中 重命名 命令 此时 选中的工作表名反相显示 即可输入新的工作表名5 移动 复制工作表选中要移动或复制的一个或多个工作表 单击 编辑 菜单中的 移动或复制工作表 命令 在出现的对话框中的 将选定工作表移至工作簿 下拉列表中选择目标工作簿 若选中 新工作簿 则是创建一个新工作簿并把选中工作表移动或复制到其中 若是需要复制工作表 则应当选中该对话框底部的 建立副本 项 一 工作表的概念与编辑 6 选定单元格和单元格区域单击 选定单元格 活动单元格 选定单元格区域 单击起始单元格并拖动鼠标到目的单元格 选定不相邻的多个单元格区域 选定第一个后 其余的按Ctrl 单击或拖动 选整行或整列 单击行号或列号 选整个表 单击行列表的交叉处或单击 全选 命令 选取行或列区域 先选定第一个行或列区域 Shift 其他行或列 选取不相邻的行或列区域 先选定第一个行或列区域 Ctrl 其他行或列区域 一 工作表的概念与编辑 7 在单元格内输入数据 可使用填充功能填充相同数据或有规律的数据 输入数字 单元格默认宽度为8个字符 右对齐 按回车键确认输入 超过的宽度用指数形式表示 11位 保留15位精确度 为了避免与日期混淆 输入分数时前面输入0加空格1 2 输入货币或百分比先单击 格式 菜单中的单元格格式并选定货币或百分比 输入日期和时间 2001 12 0617 3012小时制要区分上午 AM 下午 PM 输入当前日期Ctrl 输入当前时间Ctrl Shift 输入文字 文字是数字 空格和非数字字符的组合 左对齐 输入到单元格的字符串只要不被系统解释成数字 日期 时间 公式 逻辑值 全视为文字 输入数据序列 相同的序列和有规律的序列 5个100 10个98111 星期一至星期日 奇数 偶数 一 工作表的概念与编辑 几种快速输入数据的方法 1 连续区域内数据的输入选中所要输入数据的区域 在所选中的区域内 如果要沿着行的方向输入数据 则在每个单元格输入完后按Tab键 如果要沿着列的方向输入数据 则在每个单元格输入完后按Enter键 2 输入相同单元格内容按住Ctrl键不放 选择要输入相同内容的所有单元格 松开Ctrl键 输入数据 按下Ctrl Enter键 则刚才所选的单元格内都将填充同样的数据 如果选定的单元格中已经有数据 将被覆盖 一 工作表的概念与编辑 编辑工作表 1 插入单元格 插入单元格 可以在某个单元格上方或左侧插入单元格 先选中该单元格 插入 菜单 单元格 选项 选择所需的插入方式 单击 确定 插入行A 需要在某行的上方插入一行 则选中该行 选择 插入 菜单中的 行 选项即可完成操作B 需要插入多行 则选中相同的行数 然后选择 插入 菜单中的 行 选项即可 插入列 方法等同于插入行2 删除单元格 删除单元格 选中要删除的单元格 然后选择 编辑 菜单的 删除 选项 选择删除单元格的其他单元格的布局方式 然后单击 确定 删除行 选中要删除的行 然后选择 编辑 菜单的 删除 选项 删除列 方法等同于删除行 一 工作表的概念与编辑 编辑单元格 1 编辑单元格的数据 两种编辑单元格数据的方法 在单元格内编辑 选定要编辑的单元格 按F2键或者双击该单元格 然后直接输入数据 如果单元格中已有数据 则以新的数据代替 在编辑栏内编辑 选定要编辑的单元格 编辑栏中显示出单元格中的数据 单击编辑栏后 就可以在编辑栏中单元格中的数据进行编辑 2 清除单元格数据 清除单元格的步骤如下 选定要清除的单元格 单击 编辑 菜单中的 清除 命令 出现一个子菜单 从 清除 子菜单中可以选择4种清除方式中的任意一种 一 工作表的概念与编辑 3 单元格内容的删除选择要删除内容的单元格 选择 编辑 菜单中的 清除 命令 或在子菜单中选择 全部 内容 格式 或 批注 项 按Del键相当于仅删除内容 4 单元格的删除 插入删除单元格后 Excel会将其右侧或下方单元格内容自动左移或上移 插入单元格后 Excel将把当前单元格的内容自动右移或下移 一 工作表的概念与编辑 5 移动和复制单元格 利用 常用 工具栏上的快捷按钮移动或复制 用菜单命令移动或复制 使用鼠标拖放的方法移动或复制按钮1 同表内近距离移动 选中需要移动的单元格区域 把鼠标指针指向选中的单元格区域的边框上 指针变成箭头时 按住鼠标左键把单元格区域拖动到目标位置松开鼠标即可2 不同表的远距离移动 选中需要移动的单元格区域 按 剪切 组合键 Ctrl X 然后选中目标区域上角单元格按 粘贴 按钮组合键 Ctrl V 即完成所需操作3 同表内近距离复制 操作与 同表近距离移动 相同 不过在拖动鼠标时按住Ctrl键 即可进行复制操作4 不同表的远距离复制 选中需要复制的单元格区域 按 复制 按钮 或按 Ctrl C 然后选中目标区域的左上角单元格按 粘贴 按钮 或按Ctrl V 即可完成操作 一 工作表的概念与编辑 调整行和列的尺寸设置字体格式设置数字格式边框和底纹调整数据对齐方式工作表的打印区域设置工作表的页面设置 二 排版工作表 调整行和列的尺寸 选中需要改变尺寸的一行 列 或者多行 列 然后选择 格式 菜单下的 行 或 列 的选项 在出现的子菜单中选择 行高 或 列宽 选项 再在出现的兑换框中输入行的高度值或列的宽度值 单击 确定 按钮即可 调整字体格式 首先选中需要改变的文本或文本所造的单元格区域 单击工具栏中的 格式 在选用各种选项来控制文本格式 包括字体 字号 字型 颜色等 设置数字格式 首先选中数字 然后单击工具栏中的 格式 菜单 即可选用相应的数字格式 二 排版工作表 边框和底纹 选中需要改变边框和底纹的一行 列 或者多行 列 然后选择 格式 菜单下的 单元格 的选项 在出现的单元格格式的对话框中 单击 边框 或者 图案 按钮 即可对相关选项进行设置 调整数据对齐方式 选中需要改变边框和底纹的一行 列 或者多行 列 然后选择 格式 菜单下的 单元格 的选项 在出现的单元格格式的对话框中 单击 对齐 按钮 即可对相关选项进行设置 关注跨列居中的使用 二 排版工作表 打印设置 打印预览打印预览的方法有 在 文件 菜单中选择 打印预览 命令 单击常用工具栏的 打印预览 按钮 在 页面设置 对话框中 单击 预览 按钮 在 打印 对话框中 单击 预览 按钮 打印打印文件的方法有多种 直接单击常用工具栏中的 打印 按钮 这种方法快捷 但缺乏控制 另外 单击 文件 菜单中的 打印 命令 在 页面设置 对话框中单击 打印 按钮 二 排版工作表 页面设置 页面设置 单击 文件 菜单中的 页面设置 命令 打开 页面设置 对话框 如图所示 页面设置 对话框 二 排版工作表 设置页面 页面设置 选择 页面 选项卡 设置打印方向 打印比例 纸张大小 起始页码等 设置页边距 单击 页边距 选项卡 如图所示的对话框 从中输入数据到页边的距离及对齐方式等 设置页边距 二 排版工作表 添加页眉与页脚 添加页眉与页脚 单击 页眉 页脚 选项卡 如图所示 在此对话框中 为页面添加页眉和页脚 添加页眉与页脚 二 排版工作表 设置工作表 设置工作表 单击 工作表 选项卡 如图所示 在此对话框中可选择打印区域 标题行 以及是否打印网格线等 设置工作表 二 排版工作表 数据处理 数据排序数据筛选数据的分类汇总 返回本章目录 三 数据处理 数据排序 数据排序 在Excel中 数据库实际上就是工作表中的一个区域 一列单元格就是一个字段 每行中的字段输入项就是一条记录 三 数据处理 记录排序 1 记录排序 1 单列内容的排序 三 数据处理 多列内容的组合排序 2 多列内容的组合排序 排序选项 对话框 排序 对话框 三 数据处理 数据筛选 2 利用自动筛选器 自动筛选器的使用 三 数据处理 数据的分类汇总 分类汇总 对分类字段进行排序 例如需要根据 性别 进行分类汇总 首先要对 性别 进行排序 将光标放在工作表的任一单元格 选择 数据 菜单中的 分类汇总 打开 分类汇总 对话框 如图所示 数据库原始数据 分类汇总 对话框 三 数据处理 数据的分类汇总 按性别进行分类汇总的结果 三 数据处理 图表的使用 图表的创建图表的编辑 返回本章目录 四 图表的使用 图表的创建 建立图表 Excel提供的 图表向导 工具可以帮助我们顺利完成建立图表的工作 单击常用工具栏中的 图表向导 按钮 或者 选择 插入 菜单中的 图表 命令 将出现步骤之1图表类型 对话框 如图所示 步骤之一 图标类型 对话框 四 图表的使用 图表的创建 在该对话框中 根据需要选择指定图表的类型 单击 下一步 按钮 出现步骤之2 图表源数据 对话框 如图所示 步骤之2 图表源数据 对话框 四 图表的使用 图表的创建 在 数据区域 选项卡中给出了图表的样本 可以改变图表的数据来源和系列数据产生在行或列 单击 系列 选项卡 可以对系列进行定义 如图所示 图表源数据 对话框中的 系列 选项卡 四 图表的使用 图表的创建 单击 下一步 按钮 出现步骤之3 图表选项 对话框 在此对话框中单击需要的选项卡 为选定的图表类型设置或关闭某些标准项 图步骤之3 图表选项 对话框 四 图表的使用 单元格的数据类型数据引用公式的使用常用基础函数的使用 返回本章目录 五 公式与函数的使用 公式与函数的使用 单元格的数据类型 数值 工资数 学生成绩 员工工龄 销售额以及日期 时间等 数值类型数据主要用于各种数学计算文本 说明性 解释性的数据成为文本类型 比如 汉字 英文等属于文本类型 电话号码 学号 邮编等 公式 公式的共同特点是以 开头 它可以是简单的数学公式 也可以是包含各种Excel函数的式子 五 公式与函数的使用 五 公式与函数的使用 相对地址在公式复制时将自动调整调整原则是 新的行地址 原来的行地址 行偏移量新的列地址 原来的列地址 列偏移量例如 将D2单元中的公式 A2 B2 C2复制到D4后 D4中的公式 A4 B4 C4 数据引用 五 公式与函数的使用 数据引用 绝对地址在公式复制时不变 例如 C3单元的公式 B 1 B 2复制到D5中 D5单元的公式 B 1 B 2混合地址在公式复制时绝对地址不变 相对地址按规则调整 公式 Excel工作表中的公式最多可以有1024个字符组成 公式以 开头 后面由5种元素组成 运算符 单元格引用 数值或文本 工作表函数和括号运算符 算术运算符 用来完成基本的数学运算 算术运算符有 加 减 乘 除 百分比 乘方 其运算结果为数值型 文本运算符 文本运算符 用来将一个或多个文本连接成为一个组合文本 例如 Exc el 的结果为 Excel 3 比较运算符 用来对两个数值进行比较 产生的结果为逻辑值True 真 或False 假 比较运算符有 等于 大于 大于等于 不等于 等 4 引用运算符 用以对单元格区域进行合并运算 引用运算符包括区域 联合和交叉 公式的使用 五 公式与函数的使用 Excel中所提的函数其实是一些预定义的公式 它们使用一些称为参数的特定数值按特定的顺序或结构进行计算 用户可以直接用它们对某个区域内的数值进行一系列运算 Excel函数一共有11类分别是数据库函数 日期与时间函数 工程函数 财务函数 信息函数 逻辑函数 查询和引用函数 数学和三角函数 统计函数 文本函数以及用户自定义函数 五 公式与函数的使用 函数的概念及组成 IF函数VLOOKUP函数ROUND函数 ROUNDDOWN函数 ROUNDUP函数 SUM函数SUMIF函数AVERAGE函数MAX函数COUNT函数COUNTIF函数CONCATENATE函数MID或MIDB函数SEARCH函数 五 公式与函数的使用 常用基础函数的使用 用途 执行逻辑判断 它可以根据逻辑表达式的真假 返回不同的结果 从而执行数值或公式的条件检测任务 语法 IF logical test value if true value if false 参数 Logical test计算结果为TRUE或FALSE的任何数值或表达式 Value if true是Logical test为TRUE时函数的返回值 如果logical test为TRUE并且省略了value if true 则返回TRUE 而且Value if true可以是一个表达式 Value if false是Logical test为FALSE时函数的返回值 如果logical test为FALSE并且省略value if false 则返回FALSE Value if false也可以是一个表达式 实例 公式 IF C2 85 A IF C2 70 B IF C2 60 C IF C2 85成立 则D2单元格被赋值 A 如果第一个逻辑判断表达式C2 85不成立 则计算第二个IF语句 IF C2 70 以此类推直至计算结束 该函数广泛用于需要进行逻辑判断的场合 五 公式与函数的使用 IF函数的使用 用途 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 语法 VLOOKUP lookup value table array col index num range lookup 参数 Lookup value为需要在数据表第一列中查找的数值 它可以是数值 引用或文字串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 Col index num为table array中待返回的匹配值的列序号 Col index num为1时 返回table array第一列中的数值 col index num为2 返回table array第二列中的数值 以此类推 Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 实例 如果A1 23 A2 45 A3 50 A4 65 则公式 VLOOKUP 50 A1 A4 1 TRUE 返回50 五 公式与函数的使用 VLOOKUP函数的使用 用途 按指定位数四舍五入某个数字 ROUNDDOWN为往下舍 ROUNDUP为往上进 语法 ROUND number num digits 参数 Number是需要四舍五入的数字 Num digits为指定的位数 Number按此位数进行处理 注意 如果num digits大于0 则四舍五入到指定的小数位 如果num digits等于0 则四舍五入到最接近的整数 如果num digits小于0 则在小数点左侧按指定位数四舍五实例 如果A1 65 25 则公式 ROUND A1 1 返回65 3 ROUND 82 149 2 返回82 15 ROUND 21 5 1 返回20 五 公式与函数的使用 ROUND函数的使用 用途 返回某一单元格区域中所有数字之和 语法 SUM number1 number2 参数 Number1 number2 为1到30个需要求和的数值 包括逻辑值及文本表达式 区域或引用 注意 参数表中的数字 逻辑值及数字的文本表达式可以参与计算 其中逻辑值被转换为1 文本被转换为数字 如果参数为数组或引用 只有其中的数字将被计算 数组或引用中的空白单元格 逻辑值 文本或错误值将被忽略 实例 如果A1 1 A2 2 A3 3 则公式 SUM A1 A3 返回6 SUM 3 2 TRUE 返回6 因为 3 被转换成数字3 而逻辑值TRUE被转换成数字1 五 公式与函数的使用 SUM函数的使用 用途 根据指定条件对若干单元格 区域或引用求和 语法 SUMIF range criteria sum range 参数 Range为用于条件判断的单元格区域 Criteria是由数字 逻辑表达式等组成的判定条件 Sum range为需要求和的单元格 区域或引用 实例 某单位统计工资报表中职称为 中级 的员工工资总额 假设工资总额存放在工作表的F列 员工职称存放在工作表B列 则公式为 SUMIF B1 B1000 中级 F1 F1000 其中 B1 B1000 为提供逻辑判断依据的单元格区域 中级 为判断条件 就是仅仅统计B1 B1000区域中职称为 中级 的单元格 F1 F1000为实际求和的单元格区域 五 公式与函数的使用 SUMIF函数的使用 用途 计算所有参数的算术平均值 语法 AVERAGE number1 number2 参数 Number1 number2 是要计算平均值的1 30个参数 实例 如果A1 A5区域命名为分数 其中的数值分别为100 70 92 47和82 则公式 AVERAGE 分数 返回78 2 五 公式与函数的使用 AVERAGE函数的使用 用途 返回数据集中的最大数值 语法 MAX number1 number2 参数 Number1 number2 是需要找出最大数值的1至30个数值 实例 如果A1 71 A2 83 A3 76 A4 49 A5 92 A6 88 A7 96 则公式 MAX A1 A7 返回96 五 公式与函数的使用 MAX函数的使用 用途 返回数字参数的个数 它可以统计数组或单元格区域中含有数字的单元格个数 语法 COUNT value1 value2 参数 Value1 value2 是包含或引用各种类型数据的参数 1 30个 其中只有数字类型的数据才能被统计 实例 如果A1 90 A2 人数 A3 A4 54 A5 36 则公式 COUNT A1 A5 返回3 五 公式与函数的使用 COUNT函数的使用 用途 统计某一区域中符合条件的单元格数目 语法 COUNTIF range criteria 参数 range为需要统计的符合条件的单元格数目的区域 Criteria为参与计算的单元格条件 其形式可以为数字 表达式或文本 如36 160 和 男 等 其中数字可以直接写入 表达式和文本必须加引号 实例 假设A1 A5区域内存放的文本分别为女 男 女 男 女 则公式 COUNTIF A1 A5 女 返回3 五 公式与函数的使用 COUNTIF函数的使用 用途 将若干文字串合并到一个文字串中 其功能与 运算符相同 语法 CONCATENATE text1 text2 参数 Text1 text2 为1到30个将要合并成单个文本的文本项 这些文本项可以是文字串 数字或对单个单元格的引用 实例 如果A1 98 A2 千米 则公式 CONCATENATE A1 A2 返回 98千米 与公式 A1 A2 等价 五 公式与函数的使用 CONCATENATE函数的使用 用途 MID返回文本串中从指定位置开始的特定数目的字符 该数目由用户指定 MIDB返回文本串中从指定位置开始的特定数目的字符 该数目由用户指定 MIDB函数可以用于双字节字符 语法 MID text start num num chars 或MIDB text start num num bytes 参数 Text是包含要提取字符的文本串 Start num是文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 Num bytes指定希望MIDB从文本中按字节返回字符的个数 实例 如果a1 电子计算机 则公式 MID A1 3 2 返回 计算 MIDB A1 3 2 返回 子 五 公式与函数的使用 MID或MIDB函数的使用 用途 返回从start num开始首次找到特定字符或文本串的位置编号 其中SEARCH以字符数为单位 SEARCHB以字节数为单位 语法 SEARCH find text within text start num SEARCHB find text within text start num 参数 Find text是要查找的文本 可以使用通配符 包括问号 和星号 其中问号可匹配任意的单个字符 星号可匹配任意的连续字符 如果要查找实际的问号或星号 应当在该字符前键入波浪线 Within text是要在其中查找find text的文本 Start num是within text中开始查找的字符的编号 如果忽略start num 则假定其为1 实例 如果A1 学习的革命 则公式 SEARCH 的 A1 返回3 SEARCHB 的 A1 返回5 五 公式与函数的使用 SEARCH或SEARCHB函数的使用 常用技巧介绍 利用 分页预览视图 进行打印设置利用 单变量求解 功能进行求解 利用 高级筛选 功能实现查找重复单元格目的利用 分列 功能实现单元格的分列利用 数据透视表 功能实现数据的快速统计 返回本章目录 六 常用技巧介绍 六 常用技巧介绍 1 利用 分页预览视图 进行打印设置 可以拖动预览边界线以达到排版的目的 预览边界线 六 常用技巧介绍 2 利用 单变量求解 功能进行求解 单变量求解是解决假定一个公式要取的某一结果值 其中变量的引用单元格应取值为多少的问题 EXCEL根据所提供的目标值 将引用单元格的值不断调整 直至达到所需要求的公式的目标值时 变量的值才确定 举个简单的例子来说明单变量求解 例如 一个职工的年终奖金是全年销售额的0 2 前三个季度的销售额已经知道了 该职工想知道第四季度的销售额为多少时 才能保证年终奖金为10000元 我们可以建立如图所示的表格 其中 单元B5中的公式为 B1 B2 B3 B4 0 2 六 常用技巧介绍 用单变量求解的具体操作步骤如下 1 选定包含想产生特定数值的公式的目标单元格 例如 单击单元格B6 2 选择 工具 菜单中的 单变量求解 命令 出现如图所示的 单变量