Excel电子表格.ppt

Excel电子表格讲义 张祖林 访问和交作业地址： FTP:/2 用户名：1101 Excel 内容目录 一、Excel概述 二、创建和编辑工作表 三、格式化工作表 四、公式和函数的使用 五、创建图表 六、数据管理和分析 七、工作薄管理及打印工作表 一、Excel概述 Excel是Windows环境下的电子表格软件， 是Office成员之一。它具有强有力的数据库 管理功能、丰富的宏命令和函数，强有力的 决策支持工具。 1、简介 （1）分析能力 Excel除了可以做一些一般的计算工作外，还有若干 个函数，用来做财务、数学、字符串等操作以及各种工 程上的分析和计算。 （2）操作简便 Windows平台下软件的共同特点。 （3）图表功能 在Excel中，系统大约有100种不同格式的图表可供选用 。用户可以通过图表指南一步步的引导，得到所需的图 表。 （4）数据库管理能力 在Excel中提供了的数据库管理功能使Excel具备了能组 织和管理大量数据的能力，因而使它的用途更广泛。 2、特点 5）宏语言能力 使用VB进行宏命令的开发。 6、样式功能 在Excel中，用户利用各种文字格式化的工具和制 图能力制作出精美的报表，并可以将之存储成样式 ，用于以后的读取。 7、对象的连接与嵌入（OLE）功能 用户利用OLE可以将其它软件制作的图插入到工作 表中。 8、连接和合并功能 Excel利用此功能可以很容易地将多个工作表连接 起来，并进行汇总。 3、Excel界面及基本操作 编辑栏工具栏菜单栏地址栏 （名称栏） （1）编辑栏 给用户提供活动单元格的信息。 在编辑栏中用户可以输入和编辑公式。 由名字栏和公式栏组成。 取消按钮 确认按钮 函数指南按钮 名称栏公式栏 （2）工作薄（BOOK）窗口 “全选”按钮 活动单元格 行号 列标 工作表导航按钮 当前工作表 工作表标签 （3）工作表（SHEET） 一个工作薄最多可由255个工作表组成。工 作薄窗口中默认有3个工作表，只有一个为当前 工作表。 工作表是一个由行和列组成的表格。行号和 列号分别用字母和数字区别。行号为165536， 列标采用字母AIV。因此每张表为256列65536 行。每个单元格都有固定的行号和列标。 系统默认的工作表的标签名称为“Sheet1” 、 “Sheet2”等，用户可以根据需要自己重新 命 名工作表。 （3）工作表（SHEET） 一、工作表删除、插入和重命名 1、选取工作表 2、插入（删除）工作表 选标签，右击插入空工作表。 选标签，右击删除工作表；或选标签，编辑 删除工作表。 3、重命名工作表 双击标签 二、复制或移动工作表 1、使用菜单：右击标签选择复制（移动） 2、使用鼠标：复制按CTRL，移动不需要。 （4）选定操作 选取单元格：单击该单元格。 编辑单元格：双击该单元格。 选取整行：单击行号、SHIFT+SPACE 选取整列：单击列标签 选取整个工作表：单击“全选”按钮、 CTRL+A、CTRL+SHIFT+SPACE 选取连续的区域： 拖动鼠标；或单击起始单元格后按下 Shift单击最后一个单元格。 选取不连续的区域： 按下Ctrl键，然后单击所要选取的 单元格即可。 二、 创建、打开和保存文件 创建空白工作簿 方法一方法一: : 启动 Excel- “Book1”。 方法二：方法二：“文件”/“新建”/“常用”“工作簿”。 方法三：方法三： 、Ctrl+N。 (2)创建基于模板的工作簿 “文件”/“新建”/“电子表格模板”标签中的模板 。 1、工作薄的新建 2、保存工作簿 注意:.选择存放位置 .输入文件名 .确定文件类型 (2)(2)为已有工作簿保存副本为已有工作簿保存副本 “文件”/“另存为” (1) (1) 新工作簿的保存新工作簿的保存 “文件”/“保存”。 按钮。 Ctrl+s。 作用:便于查找操作。 “文件”/“属性”/“摘要信息” (3) (3) 保存摘要信息保存摘要信息 3、工作薄的打开 在在ExcelExcel窗口中窗口中: : “文件”/“打开” 按钮 组合键Ctrl+o 在在“我的电脑我的电脑”或或“资源管理器资源管理器”中中: : 双击 图标对应的文件。 “文件”菜单/“另存为” /“选项” （2 2）实现方法）实现方法: : （1 1）保护方法有两种）保护方法有两种: : 打开口令 修改口令 设置 4、保护工作簿文件 三、编辑工作表 （1）字符的输入 在一个单元格中最多可放32000个字符。 输入多行字符：按Alt+回车键； 默认单 元格宽度为8个字符宽度。 输入由数字组成的字符串时应在数字前 加单引号。如：08362512112 字符的默认对齐方式为左对齐。当字符 宽度超过单元格宽度，如果右边单元格无 数据则延伸到后面的单元格，如果右边单 元格有数据则截去超宽的字符。 1、数据输入 三、编辑工作表 （2）数字的输入 数字太大用科学计数法。公式栏中显示输入数字， 最多显示15位。 可输入数值：0、9、+、-、()、/、*、$、%、e 等。 输入负数：前加负号或将数值放在圆括号里。 输入分数：先输入一个0及空格，再输入分数。 分数：0 5/9 数值数值: :右对齐。 空格 日期格式通常为：年月日、月日。 如: 2005/5/1 、 200551、51、 5/1 时间格式通常为：“时：分：秒”； 上 午加AM，下午加PM。如20:23 AM。如果省 略默认为上午 插入当前日期：CTRL+； 插入当前时间：CTRL+：或CTRL+SHIFT+； 空格 （3）日期和时间的输入 数字的填写 n在该数字的填写行（列）的前两个单元 格内输入开始的2个数字； n选择这两个单元格； n用鼠标按住第二个单元格的填充柄，将 其拖曳到结束单元格，松开鼠标 （4）输入的自动化 自定义序列的自动填写 n在“工具”菜单中， 选择“选项”，单击“自 定义序列”标签； n确定输入序列； n在单元格中输入所选序 列的任一项，并选择该单元格； n用鼠标左键拖曳所选单元格右下角的填充柄 到结束处 （4）输入的自动化 （5）从下拉列表选择（同一列） 在单元格中右击选择“从下拉列 表选择” （6）有效数据 选区域，“数据”“有效数据”“设置 ”“允许”设置限制条件。 2、编辑工作表 2、编辑工作表 （1）单元格内容的移动、复制和删除 用鼠标拖曳的方法移动和复制单元格的内 容 选择要移动、复制的单元格 移动：将光标移到单元格边框的下侧或 右侧，出现箭头状光标时，用鼠标拖曳到 单元格的新位置即可 复制：在按住Ctrl键的同时，按上述方 法用鼠标拖曳单元格到新的位置 2、编辑工作表 n用剪切和粘贴的方法移动和复制单元格 的内容 n选择要移动、复制的单元格 n移动：可以单击工具栏中的剪切按钮 复制：可以单击工具栏中的复制按钮 n单击目标单元格 n单击工具栏中的粘贴按钮 n按Enter 键即可 2、编辑工作表 n单元格内容的删除 n选择所要删除内容的单元格 n在“编辑”菜单中选择“清除”项，在 子菜单中选择相应项 n或按Del键直接删除单元格内容 2、编辑工作表 （2）单元格的删除 选择所要删除的单元格 在“编辑”菜单中选择“删除”项，打开删除对话 框 做相应选择后按“确定”钮完成操作 （3）单元格的插入 n选择所要插入单元格的位置 n在“插入”菜单中选择“单元格”项，在出现的“插入” 对话框中选择插入后当前单元格移动的方向 n按“确定”按钮即可 2、编辑工作表 （4）行、列的删除和插入 行、列的删除 n单击所要删除的行、列的标头 n选择“编辑”/“删除”项即可 行、列的插入 n单击所要插入行、列所在任一单元格 n选择“插入”/“行”或“列”项即可 2、编辑工作表 （5）编辑批注 插入批注 插入批注输入批注确定 删除批注 编辑清除批注 （6）选择性粘贴 编辑选择性粘贴（或右击） （7）查找与替换 编辑查找与替换 3、常见错误提示 “#” 单元格宽度不够。 “#Value!” 引用公式时有错误的输入类型 。 “#NUM!” 数字、参数有错误。 “#REF ” 单元格引用无效。 “#NAME!” 使用Excel不能识别的名称 “NULL!” 指定了两个不相交区域的交叉点 4、窗口的冻结、分割与缩放 （1）冻结窗口 窗口冻结拆分窗口 （2）分割窗口 窗口拆分窗口 （3）缩放窗口 水平移动时，左边窗口冻结 垂直移动时，上面窗口冻结 常用工具栏中的缩放控制 三、格式化工作表 1、改变行高、列宽 Excel默认行高为14.25，列宽为8.38。 当输入的文字或数字超过行高或列宽时会出 现错误信息（如“#”）。 方法： 用鼠标直接拖动列标或行号的间隔。 格式 行行高 列列宽 2、数字显示格式的设定 格式单元格选定单元格 货币样式 格式工具栏按钮 百分比样式千位分隔样式增加小数位数 减少小数位数 如何设置不显示“0”？ 工具 选项 3、表格边框的设置 （1）取消网格线 工具选项 （2）添加表格边框 选定格式单元格边框 4、对齐工具的使用 5、字体、底纹及文字方向 6、自动套用表格格式 选定格式自动套用格式 7、条件格式 选定对象格式条件格式 8、格式复制 格式刷：只复制对象的格式。 选定复制编辑选择性粘贴 四、公式和函数的使用 1、数据的自动求和 选定工具栏自动求和按钮 以“=”开始，由常数、单元格引用、函数 和运算符组成。 即：“= ” + “表达式” 算术运算符：+、-、*、/、 比较运算符：=、=、 字符运算符：&（连接） 2、公式 3、单元格的引用 相对引用：列标和行号的直接组合。如B5 绝对引用：在列标和行号前加上“$”。如$B$5 混合引用：如：$B5或B$5 同一工作表中单元格的引用 不同工作表中单元格的引用 不同工作簿中单元格的引用 在复制公式时，单元格的绝对引用是不变的。 4、函数 Excel提供了300多个函数，包括数学与三角 函数、文字函数、逻辑函数、日期与时间函数、 统计函数、查找与引用函数、数据库函数和信息 函数。另外，用户还可以自定义函数。 （1）“：”和“，”操作符 “：”操作符 操作对象为以“：”前后的两个单元格为角的矩 形区域。 “，”操作符 操作对象为“，”前后的两个单元格。 常用函数Sum 返回某一单元格区域中所有数字之和。 格式：SUM(参数1,参数2, .) 说明： 1、计算机 1 到 30 个需要求和的参数。 2、如果参数为数组或引用，只有其中的数字将被计 算。数组或引用中的空白单元格、逻辑值、文本或 错误值将被忽略。如果参数为错误值或为不能转换 成数字的文本，将会导致错误。 例： =SUM(3, 2)将 3 和 2 相加 =SUM(“5“, 15, TRUE)将5、15 和 1 相加，因为文本值 被转换为数字，逻辑值 TRUE 被转换成数字 1 SUMIF求满足条件的和 语法：SUMIF(range,criteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数 字、表达式或文本 Sum_range 是需要求和的实际单元格。 说明： 只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格 才求和。如果忽略了 sum_range，则对区域中的单元格求和。 例： =SUMIF(A2:A5,“160“,B2:B5) 说明：求A2:A5区域内满足条件“160”的单元格， 在B2:B5区域的和。 常用函数Average： 求平均值函数（算术平均值）返回参数的 平均值。 格式：AVERAGE(参数1,参数2,.) 计算需要计算平均值的 1 到 30 个参数。 说明： 1、参数可以是数字，或者是包含数字的名 称、数组或引用。 2、如果数组或引用参数包含文本、逻辑值 或空白单元格，则这些值将被忽略；但包 含零值的单元格将计算在内。 常用函数Count 统计参数的个数 返回包含数字以及包含参数列表中的数字的单元格的个数 。利用函数 COUNT 可以计算单元格区域或数字数组中 数字字段的输入项个数。 格式：COUNT(参数1,参数2,.) 计算包含或引用各种类型数据的参数（1 到 30个），但只 有数字类型的数据才被计算。 说明： 函数 COUNT 在计数时，将把数字、日期、或以文本代表 的数字计算在内；但是错误值或其他无法转换成数字的 文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的 数字；数组或引用中的空白单元格、逻辑值、文字或错 误值都将被忽略。如果要统计逻辑值、文字或错误值， 请使用函数 COUNTA。 COUNTIF统计满足条件的个数 语法：COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元 格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在 内的条件，其形式可以为数字、表达式 或文本。 例：=COUNTIF(B2:B5,“55“) 说明：统计B2:B5区域内”55”的个数。 常用函数If 执行真假值判断，根据逻辑计算的真假值，返回不同结 果。 格式：IF(表达式,返回值1，返回值2) 表达式： 表示计算结果为 TRUE 或 FALSE 的任意值或 表达式 返回值1：表达式为 TRUE 时返回的值。 返回值2：表达式为 FALSE 时返回的值。 说明 函数 IF 可以嵌套七层， 如：If(B3C3,”Good”,”Bad”) 即如果B3单元格的值大于C3单元格的值， 则返回“Good”，否则返回“Bad”。 =IF(A2B2,“超出预算”,“OK”)判断第 2 行 是否超出预算 例2： =IF(A289,“A“,IF(A279,“B“, IF(A269,“C“,IF(A259,“D“,“F“) 为第一个成绩指定一个字母等级 常用函数Round 返回某个数字按指定位数取整后的数字。 格式：ROUND(参数,数值) 参数： 需要进行四舍五入的数字。 数值：指定的位数，按此位数进行四舍五入。 说明 如果数值大于 0，则四舍五入到指定的小数位。 如果数值等于 0，则四舍五入到最接近的整数。 如果数值小于 0，则在小数点左侧进行四舍五入。 =ROUND(2.15, 1)将 2.15 四舍五入到一个小数位 (2.2) =ROUND(2.149, 1)将 2.149 四舍五入到一个小数位 (2.1) =ROUND(-1.475, 2)将 -1.475 四舍五入到两小数位 (- 1.48) =ROUND(21.5, -1)将 21.5 四舍五入到小数点左侧一位 (20） 常用函数CEILING 将参数向上舍入为最接近的指定数字的倍数。 例如，如购买的商品价格为 $4.42，可以用公 式 =CEILING(4.42,0.1) 将价格向上舍入为以“ 角”表示。 格式： CEILING ( 参数, 数值 ) =CEILING(4.42,0.1)=4.5 =CEILING(4.12,0.1)=4.2 =CEILING(4.12,0.5)=4.5 =CEILING(4.62,0.5)=5.0 后两例在工资计算中为“4收5，5进1” 常用函数-LEFT 所指定的字符数返回文本字符串中的第一个或前几个 字符。 LEFT(文本,数值) 文本：是包含要提取字符的文本字符串。 数值：指定要由 LEFT 所提取的字符数。 数值必须大于或等于 0。如果数值大于文本长度，则 LEFT 返回所有文本。如果省略数值，则假定其为 1 。 =LEFT(A2,4) A2中的前四个字符 =LEFT(A3) A3中的第一个字符 常用函数-MID MID 返回文本字符串中从指定位置开始的特定 数目的字符，该数目由用户指定。 格式:MID(text,N1,N2) Text 包含要提取字符的文本字符串(或单元格) 。 N1 是文本中要提取的第一个字符的位置。 N2 指定希望 MID 从文本中返回字符的个数。 示例 (MID) =MID(“ ABCDEF“,1,3) 等于“ ABC ” 常用函数-RANK 排序 1、有名次相同，缺名次 RANK(b2,$b$2:$b$20,order) 说明： Order 为数字，指明排位的方式。 0升序，1降序 2、有相同名次，不缺名次（先出现的排前） RANK(b2,$b$2:$b$20,order)+countif($b$2:b2,b2)-1 3、最优排名 =SUM(IF($B$2:$B$574)全部条件为假，返回FALSE =OR( 1=0,21,34)有一个条件为真，返回 TRUE FIND用于查找文本字符串内的文本字符串 语法:FIND(find_text,within_text,start_num) Find_text 是要查找的文本。 Within_text 是包含要查找文本的文本或单元格。 Start_num 指定开始进行查找的字符。within_text 中的首 字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。 FIND 区分大小写并且不允许使用通配符 例：比较 =FIND(“国”,“中国政府国家对国家“,1) =FIND(“国”,“中国政府国家对国家“,2) =FIND(“国”,“中国政府国家对国家“,6) 常用函数-NOW 返回当前日期和时间所对应的序列号。如果 在输入函数前，单元格的格式为“常规”，则 结果将设为日期格式。 语法:NOW( ) 3.例：NOW()返回当前日期时间，在工作表设 置该函数可以减少输入当前日期。 如：今天是2006-06-06 NOW()就显示2006-06-06 常用函数-DATE DATE(年,月,日) 年：可以为一到四位数字。Microsoft Excel 将根据所使用的日 期系统来解释 year 参数。 月： 代表每年中月份的数字。如果所输入的月份大于 12，将 从指定年份的一月份开始往上加算。 日： 代表在该月份中第几天的数字。如果 day 大于该月份的 最大天数，则将从指定月份的第一天开始往上累加。例如， DATE(2008,1,35) 返回代表 2008 年 2 月 4 日的序列号。 函数 DATE 在年、月、日为变量的公式中非常有用。 示例 =DATE(A2,B2,C2)返回上述日期的序列号，使用 1900 日期 系统（2008-1-1 或 39448） 其他几个日期函数 1、YEAR() 2、MONTH() 3、DAY() DATEDIF函数 格式：DATEDIF(起始日期,结束日期,返回) 返回值: “Y“ 时间段中的整年数。 “M“ 时间段中的整月数。 “D“ 时间段中的天数。 “MD” 开始与结束日期中天数的差。忽略日期中的月和年。 “YM“开始与结束日期中月数的差。忽略日期中的日和年。 “YD“开始与结束日期中天数的差。忽略日期中的年。 年龄的计算 虚年的计算 =YEAR(TODAY()-YEAR(C3) 实足年龄的计算 =DATEDIF(C3,TODAY(),“y“) TODAY()也可以是指定日期 五、创建图表 1、图表的概念 图表使数据更直观、形象。 （1）图表类型 （2）数据源：作图的数据区域。 （3）图表选项：图表中的各个对象。 图表区、图形区、分类轴、图例、背景等。 （4）图表位置：嵌入式图、独立图表。 选中数据源插入图表 2、创建图表 3、图表的编辑 （1）图表的缩放、移动、复制和删除 （2）图表数据的删除、增加和修改 选中某数据系列编辑清除系列 嵌入式图表数据添加：将数据直接拖进图表 独立图表数据添加：图表 添加数据 （3）改变图表类型 Excel提供了9类二维（平面）图和6类三维 （立体）图，共15类102种统计图。 选定图表图表图表类型 或使用图表工具栏 （4）添加标题、数据标志、网格线、图例等 选定图表，在插入菜单中寻找各项。 （5）三维图的调整 选定图表图表设置三维视图格式 六、数据管理和分析 1、数据列表的创建 在EXCEL中数据库是作为数据列表来看待的 。 每一行信息称之为一条记录，每条记录由若干个 数据项（字段）组成，每一列代表一个字段。最 多可有25列。 数据列表的第一行信息是字段的名称。 注意点： v每列所包含的数据应是同类型的； v列表中不要有空行或空列； v列表与其他数据间最好空一个空行或空列； v每个列表最好占一个工作表。 2、数据列表的编辑 输入数据 编辑记录 删除记录 查找记录 数据记录单 F一般排序 F多关键字排序 F自定义排序 数据排序 3、数据的排序 4、数据的筛选 筛选功能是指只显示出符合给定筛选条件的 某一值或符合一组条件的行。 数据筛选 自动筛选