[其它]EXCEL讲义.ppt

Excel电子表格 一、工作表的概念与编辑 二、排版工作表 * 三、数据处理* 四、图表的使用* 五、公式与函数的使用* 六、常用技巧介绍* 第2页 一、工作表的概念与编辑 EXCEL窗口组成 第3页 工作薄与工作表的概念 工作簿： Excel保存的文件称为工作薄.xls，每个工作薄包含若 干个工作表，默认3个，由工作表标签Sheet1，Sheet2， Sheet3表示 工作表： （电子表格）位于工作薄的中央区，由行号，列号和网 格线构成。单击行号1上A左的方块，显示工作表行号和列 号。当前单元格（活动单元格）单击即可，右下角的黑色 小方块为填充句柄，拖动填充单元格数据。 一、工作表的概念与编辑 第4页 1.选定工作表 若要选中一张，则直接单击该工作表的标签即可。 若要同时选中多张连续的工作表，则应按住Shift键单击第 一个工作表标签和最后一根工作表标签。 若要选中多张不连续的工作表，则应按住Ctrl键单击要选的 工作表的标签 2.插入工作表 鼠标右键单击工作表标签，在弹出的快捷菜单中选择“插入” “工作表”命令 在菜单栏中选择“插入”菜单，然后选择“工作表”菜单命令 一、工作表的概念与编辑 第5页 3.删除工作表 鼠标右键单击要删除的工作表，在弹出的快捷菜单中选择“删除”菜 单命令即可 选中要删除的一个或多个工作表，单击“编辑”菜单中的“删除工作 表”命令，然后确定删除 4.重命名工作表 鼠标右键单击要更名的工作表标签，然后在出现的快捷菜单上选中 “重命名”命令，此时，选中的工作表名反相显示，即可输入新的工 作表名 5.移动、复制工作表 选中要移动或复制的一个或多个工作表，单击“编辑”菜单中的“移 动或复制工作表”命令，在出现的对话框中的“将选定工作表移至工 作簿”下拉列表中选择目标工作簿，若选中“新工作簿”则是创建一 个新工作簿并把选中工作表移动或复制到其中。若是需要复制工作表 ，则应当选中该对话框底部的“建立副本”项 一、工作表的概念与编辑 第6页 6.选定单元格和单元格区域 单击选定单元格（活动单元格）； 选定单元格区域单击起始单元格并拖动鼠标到目的单元 格； 选定不相邻的多个单元格区域选定第一个后，其余的按 Ctrl+单击或拖动； 选整行或整列单击行号或列号； 选整个表单击行列表的交叉处或单击“全选”命令。 选取行或列区域先选定第一个行或列区域，Shift+其他 行或列。 选取不相邻的行或列区域先选定第一个行或列区域， Ctrl+其他行或列区域。 一、工作表的概念与编辑 第7页 7.在单元格内输入数据（可使用填充功能填充相 同数据或有规律的数据）。 输入数字：单元格默认宽度为8个字符，右对齐，按回车键确 认输入。超过的宽度用指数形式表示，11位，保留15位精确 度。为了避免与日期混淆，输入分数时前面输入0加空格1/2 。输入货币或百分比先单击“格式”菜单中的单元格格式并 选定货币或百分比。 输入日期和时间：2001/12/06 17:30 12小时制要区分上午 （AM）下午（PM），输入当前日期Ctrl+；输入当前时间 Ctrl+Shift+： 输入文字：文字是数字、空格和非数字字符的组合。左对齐。 输入到单元格的字符串只要不被系统解释成数字、日期、时 间、公式、逻辑值。全视为文字。 输入数据序列： （相同的序列和有规律的序列） 5个100，10个98111，星期一至星期日，奇数，偶数 一、工作表的概念与编辑 第8页 几种快速输入数据的方法 1.连续区域内数据的输入 选中所要输入数据的区域，在所选中的区域内，如果要 沿着行的方向输入数据，则在每个单元格输入完后按Tab键； 如果要沿着列的方向输入数据，则在每个单元格输入完后按 Enter键。 2.输入相同单元格内容 按住Ctrl键不放，选择要输入相同内容的所有单元格。 松开Ctrl键，输入数据。按下Ctrl+Enter键，则刚才所选的单 元格内都将填充同样的数据。如果选定的单元格中已经有数据 ，将被覆盖。 一、工作表的概念与编辑 第9页 编辑工作表 1.插入单元格 插入单元格：可以在某个单元格上方或左侧插入单元格，先选中该 单元格”插入”菜单“单元格”选项，选择所需的插入方式，单击“确 定” 插入行 A.需要在某行的上方插入一行，则选中该行，选择“插入”菜单中的“ 行”选项即可完成操作 B.需要插入多行，则选中相同的行数，然后选择“插入”菜单中的“行 ”选项即可。 插入列：方法等同于插入行 2.删除单元格 删除单元格：选中要删除的单元格，然后选择“编辑”菜单的“删 除”选项，选择删除单元格的其他单元格的布局方式，然后单击“确 定” 删除行：选中要删除的行，然后选择“编辑”菜单的“删除”选项 删除列：方法等同于删除行 一、工作表的概念与编辑 第10页 编辑单元格 1.编辑单元格的数据-两种编辑单元格数据的方法： 在单元格内编辑:选定要编辑的单元格，按F2键或者双击该单元 格，然后直接输入数据。如果单元格中已有数据，则以新的数 据代替。 在编辑栏内编辑: 选定要编辑的单元格，编辑栏中显示出单元 格中的数据。单击编辑栏后，就可以在编辑栏中单元格中的数 据进行编辑。 2.清除单元格数据-清除单元格的步骤如下： 选定要清除的单元格。 单击“编辑”菜单中的“清除”命令，出现一个子菜单。 从“清除”子菜单中可以选择4种清除方式中的任意一种。 一、工作表的概念与编辑 第11页 3. 单元格内容的删除 选择要删除内容的单元格，选择“编辑”菜单中的“清 除”命令，或在子菜单中选择“全部”、“内容”、“格式” 或“批注”项。按Del键相当于仅删除内容。 4. 单元格的删除、插入 删除单元格后，Excel会将其右侧或下方单元格内容自 动左移或上移。插入单元格后，Excel将把当前单元格的内容 自动右移或下移。 一、工作表的概念与编辑 第12页 5.移动和复制单元格 利用“常用”工具栏上的快捷按钮移动或复制 用菜单命令移动或复制 使用鼠标拖放的方法移动或复制按钮 1、同表内近距离移动：选中需要移动的单元格区域，把鼠标指针 指向选中的单元格区域的边框上，指针变成箭头时，按住鼠标 左键把单元格区域拖动到目标位置松开鼠标即可 2、不同表的远距离移动：选中需要移动的单元格区域，按“剪切 ”组合键（Ctrl+X），然后选中目标区域上角单元格按“粘贴 ”按钮组合键（Ctrl+ V）即完成所需操作 3、同表内近距离复制：操作与“同表近距离移动”相同，不过在 拖动鼠标时按住Ctrl键，即可进行复制操作 4、不同表的远距离复制：选中需要复制的单元格区域，按“复制 ”按钮（或按“Ctrl+C”），然后选中目标区域的左上角单元 格按“粘贴”按钮（或按Ctrl+V）即可完成操作 一、工作表的概念与编辑 第13页 调整行和列的尺寸 设置字体格式 设置数字格式 边框和底纹 调整数据对齐方式 工作表的打印区域设置 工作表的页面设置 二、排版工作表 第14页 调整行和列的尺寸：选中需要改变尺寸的一行（列 ）或者多行（列），然后选择“格式”菜单下的“行”或 “列”的选项，在出现的子菜单中选择“行高”或“列宽” 选项，再在出现的兑换框中输入行的高度值或列的 宽度值，单击“确定”按钮即可。 调整字体格式：首先选中需要改变的文本或文本所 造的单元格区域，单击工具栏中的“格式”，在选用 各种选项来控制文本格式，包括字体、字号、字型 、颜色等。 设置数字格式：首先选中数字，然后单击工具栏中 的“格式”菜单，即可选用相应的数字格式。 二、排版工作表 第15页 边框和底纹：选中需要改变边框和底纹的一行（列 ）或者多行（列），然后选择“格式”菜单下的“单元 格”的选项，在出现的单元格格式的对话框中，单击 “边框”或者“图案”按钮，即可对相关选项进行设置。 调整数据对齐方式：选中需要改变边框和底纹的一 行（列）或者多行（列），然后选择“格式”菜单下 的“单元格”的选项，在出现的单元格格式的对话框 中，单击“对齐”按钮，即可对相关选项进行设置。 （关注跨列居中的使用） 二、排版工作表 第16页 打印设置 打印预览 打印预览的方法有：在“文件”菜单中选择“打印 预览”命令；单击常用工具栏的“打印预览”按钮 ；在“页面设置”对话框中，单击“预览”按钮； 在“打印”对话框中，单击“预览”按钮。 打印 打印文件的方法有多种，直接单击常用工具栏中的 “打印”按钮，这种方法快捷，但缺乏控制。另外 ，单击“文件”菜单中的“打印”命令，在“页面 设置”对话框中单击“打印”按钮。 二、排版工作表 第17页 页面设置 页面设置: 单击“文件”菜单中的“页面设置”命令，打开“ 页面设置”对话框，如图所示 “页面设置”对话框 二、排版工作表 第18页 设置页面 页面设置：选择“页面”选项卡，设置打印方向、打印比例、纸 张大 小、起始页码等。 设置页边距：单击“页边距”选项卡，如图所示的对话框。从中 输入数据到页边的距离及对齐方式等。 设置页边距 二、排版工作表 第19页 添加页眉与页脚 添加页眉与页脚：单击“页眉/页脚”选项卡，如图所示。 在此对话框中，为页面添加页眉和页脚。 添加页眉与页脚 二、排版工作表 第20页 设置工作表 设置工作表：单击“工作表”选项卡，如图所示。在此对话框中 可选择打印区域，标题行，以及是否打印网格线等。 设置工作表 二、排版工作表 第21页 数据处理 数据排序 数据筛选 数据的分类汇总 返回本章目录 三、数据处理 第22页 数据排序 数据排序：在Excel中，数据库实际上就是工作表中 的 一个区域，一列单元格就是一个字段， 每 行中的字段输入项就是一条记录。 三、数据处理 第23页 记录排序 1. 记录排序 (1) 单列内容的排序 三、数据处理 第24页 多列内容的组合排序 (2) 多列内容的组合排序 “排序选项 ”对话框 “排序” 对话框 三、数据处理 第25页 数据筛选 2.利用自动筛选器 自动筛选器的使用 三、数据处理 第26页 数据的分类汇总 分类汇总：对分类字段进行排序，例如需要根据“性别”进 行分类汇总，首先要对“性别”进行排序，将光标放在工作表 的任一单元格，选择“数据”菜单中的“分类汇总”，打开“分类汇 总”对话框，如图所示。 数据库原始数据 “分类汇总”对话框 三、数据处理 第27页 数据的分类汇总 按性别进行分类汇总的结果 三、数据处理 第28页 图表的使用 图表的创建 图表的编辑 返回本章目录 四、图表的使用 第29页 图表的创建 建立图表：Excel提供的“图表向导”工具可以帮助我们顺 利完成建立图表的工作。单击常用工具栏中的“图表向导”按 钮，或者，选择 “插入” 菜单中的“图表”命令，将出现步 骤之1图表类型”对话框，如图所示。 “步骤之一图标类型”对话框 四、图表的使用 第30页 图表的创建 在该对话框中，根据需要选择指定图表的类型，单 击“下一步”按钮，出现步骤之2图表源数据”对 话框，如图所示。 步骤之2图表源数据”对话框 四、图表的使用 第31页 图表的创建 在“数据区域”选项卡中给出了图表的样本，可以改变图表的数 据来源和系列数据产生在行或列。单击“系列”选项卡，可以对 系列进行定义，如图所示。 “图表源数据”对话框中的“系列”选项卡 四、图表的使用 第32页 图表的创建 单击“下一步”按钮，出现步骤之3图表选项”对 话框，在此对话框中单击需要的选项卡，为选定的 图表类型设置或关闭某些标准项。 图步骤之3图表选项”对话框 四、图表的使用 第33页 单元格的数据类型 数据引用 公式的使用 常用基础函数的使用 返回本章目录 五、公式与函数的使用 公式与函数的使用 第34页 单元格的数据类型 数值：工资数、学生成绩、员工工龄、销售额以及 日 期、时间等，数值类型数据主要用于各种数 学 计算 文本：说明性、解释性的数据成为文本类型。比如 ， 汉字、英文等属于文本类型（电话号码、学 号、邮编等） 公式：公式的共同特点是以“=”开头，它可以是 简单 的数学公式，也可以是包含各种Excel函数 的 式子 五、公式与函数的使用 第35页 五、公式与函数的使用 相对地址 在公式复制时将自动调整 调整原则是： 新的行地址=原来的行地址+行偏移量 新的列地址=原来的列地址+列偏移量 例如：将D2单元中的公式 =A2+B2+C2 复制到D4后，D4中的公式 =A4+B4+C4 数据引用 第36页 五、公式与函数的使用 数据引用 绝对地址 在公式复制时不变。 例如：C3单元的公式=$B$1+$B$2 复制到D5中，D5单元的公式=$B$1+$B$2 混合地址 在公式复制时绝对地址不变， 相对地址按规则调整。 第37页 公式： Excel工作表中的公式最多可以有1024个字符组成，公式以 “=”开头，后面由5种元素组成:运算符、单元格引用、数值 或文本、工作表函数和括号 运算符： 算术运算符：用来完成基本的数学运算，算术运算符有+（加 ）、-（减）、*（乘）、/（除）、%（百分比）、（乘方） ，其运算结果为数值型。 文本运算符：文本运算符“&”用来将一个或多个文本连接成 为一个组合文本。例如“Exc”&“el”的结果为“Excel”。 (3) 比较运算符：用来对两个数值进行比较，产生的结果为逻辑 值True（真）或False（假）。比较运算符有=（等于）、（ 大于）、=（大于等于）、 （不等于）等。 (4) 引用运算符：用以对单元格区域进行合并运算。引用运算符 包括区域、联合和交叉。 公式的使用 五、公式与函数的使用 第38页 Excel 中所提的函数其实是一些预定义的公式， 它们使用一些称为参数的特定数值按特定的顺序或 结构进行计算，用户可以直接用它们对某个区域内 的数值进行一系列运算。 Excel 函数一共有11 类分别是数据库函数、日 期与时间函数、工程函数、财务函数、信息函数、 逻辑函数、查询和引用函数、数学和三角函数、统 计函数、文本函数以及用户自定义函数。 五、公式与函数的使用 函数的概念及组成 第39页 IF函数 VLOOKUP函数 ROUND函数(ROUNDDOWN函数, ROUNDUP函数) SUM函数 SUMIF函数 AVERAGE函数 MAX函数 COUNT函数 COUNTIF函数 CONCATENATE函数 MID 或MIDB函数 SEARCH函数 五、公式与函数的使用 常用基础函数的使用 第40页 用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果 ，从而执行数值或公式的条件检测任务。 语法：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函数的使用 第41页 用途：在表格或数值数组的首列查找指定的数值，并由此返回表 格或数组当前行中指定列处的数值。 语法：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函数的使用 第42页 用途：按指定位数四舍五入某个数字。(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函数的使用 第43页 用途：返回某一单元格区域中所有数字之和。 语法：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函数的使用 第44页 用途：根据指定条件对若干单元格、区域或引用求和 。 语法：SUMIF(range，criteria，sum_range) 参数：Range 为用于条件判断的单元格区域， Criteria是由数字、逻辑表达式等组成的判定条件 ，Sum_range 为需要求和的单元格、区域或引用。 实例：某单位统计工资报表中职称为“中级”的员工 工资总额。假设工资总额存放在工作表的F 列，员 工职称存放在工作表B 列。则公式为 “=SUMIF(B1:B1000，“中级“，F1:F1000)”，其中 “B1:B1000”为提供逻辑判断依据的单元格区域，“ 中级“为判断条件，就是仅仅统计B1:B1000 区域中 职称为“中级”的单元格，F1:F1000 为实际求和的 单元格区域。 五、公式与函数的使用 SUMIF函数的使用 第45页 用途：计算所有参数的算术平均值。 语法：AVERAGE(number1，number2，.)。 参数：Number1、number2、.是要计算平均值的1 30个参数。 实例：如果A1：A5 区域命名为分数，其中的数值分别 为100、70、92、47 和82，则公式“=AVERAGE(分数 )”返回78.2。 五、公式与函数的使用 AVERAGE函数的使用 第46页 用途：返回数据集中的最大数值。 语法：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函数的使用 第47页 用途：返回数字参数的个数。它可以统计数组或单元 格区域中含有数字的单元格个数。 语法：COUNT(value1，value2，.)。 参数：Value1，value2，.是包含或引用各种类型数 据的参数(130 个)，其中只有数字类型的数据才 能被统计。 实例：如果A1=90、A2=人数、A3=、A4=54、A5=36 ，则公式“=COUNT(A1:A5)”返回3。 五、公式与函数的使用 COUNT函数的使用 第48页 用途：统计某一区域中符合条件的单元格数目。 语法：COUNTIF(range，criteria) 参数：range 为需要统计的符合条件的单元格数目的 区域；Criteria 为参与计算的单元格条件，其形式 可以为数字、表达式或文本(如36、“160“和“男“等 )。其中数字可以直接写入，表达式和文本必须加引 号。 实例：假设A1:A5 区域内存放的文本分别为女、男、 女、男、女，则公式“=COUNTIF(A1:A5，“女“)”返 回3。 五、公式与函数的使用 COUNTIF函数的使用 第49页 用途：将若干文字串合并到一个文字串中，其功能与 “&“运算符相同。 语法：CONCATENATE(text1，text2，.) 参数：Text1，text2，.为1 到30 个将要合并成单 个文本的文本项，这些文本项可以是文字串、数字 或对单个单元格的引用。 实例：如果A1=98、A2=千米，则公式 “=CONCATENATE(A1，A2)”返回“98 千米”，与公 式“=A1&A2”等价。 五、公式与函数的使用 CONCATENATE函数的使用 第50页 用途：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函数的使用 第51页 用途：返回从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函数的使用 第52页 常用技巧介绍 利用“分页预览视图”进行打印设置 利用“单变量求解”功能进行求解。 利用“高级筛选”功能实现查找重复单元格目的 利用“分列”功能实现单元格的分列 利用“数据透视表”功能实现数据的快速统计 返回本章目录 六、常用技巧介绍 第53页 六、常用技巧介绍 1、利用“分页预览视图”进行打印设置 可以拖动预览边界线以达到排版的目的 预览边界线 第54页 六、常用技巧介绍 2、利用“单变量求解”功能进行求解 单变量求解是解决假定一个公式要取的某一结果 值，其中变量的引用单元格应取值为多少的问题。 EXCEL根据所提供的目标值，将引用单元格的值不断 调整，直至达到所需要求的公式的目标值时，变量的 值才确定。 举个简单的例子来说明单变量求解。例如，一个 职工的年终奖金是全年销售额的0.2%，前三个季度的 销售额已经知道了，该职工想知道第四季度的销售额 为多少时，才能保证年终奖金为10000元。我们可以 建立如图所示的表格。 其中，单元B5中的公式为“=（B1+B2+B3+B4） *0.2%” 第55页 六、常用技巧介绍 用单变量求解的具体操作步骤如下： 1.选定包含想产生特定数值的公式的目标单元格。例如，单击单元格 B6。 2.选择“工具”菜单中的“单变量求解”命令，出现如图所示的“单 变量求解”对话框。此时，“