Excel电子表格处理.pptx

内容提要内容提要 Excel 2010的基本知识 Excel 2010的基本操作 公式与函数 制作图表 数据管理和分析 页面设置和打印工作表 Excel 2010电子表格处理 4.1 Excel 2010的基本知识 Excel 2010的主要功能概述 建立基本概念 表格标题 数据文字数值 自动填充公式函数 图表图形对象数据清单 排序筛选分类汇总 表格处理表格处理 制作图表制作图表 数据计算功能数据计算功能 打印工作表打印工作表 管理和分析数据管理和分析数据 决策指示决策指示 远程发布数据功能远程发布数据功能 建立基本概念 工作簿与工作表 工作簿（Workbook） 工作簿是通常意义上的Excel文件，扩展名为.xlsx。每一个工作簿 可由一个至255个工作表组成，在默认是由3个。 工作表（Worksheet） 工作表是工作簿的一部分，也称作电子报表。每个工作表有16384 列1048576行。行号从11048576，列号从A、B、AA、 、AZ、AAA至XFD。 单元格 单元格是指工作表中的一个小格子。每个单元格都有自己的坐标， 即单元格地址。单元格地址表示为：列标+行号。 单元格区域 一组相邻单元格构成的矩形块。区域用构成区域的对角单元的坐标 中间加一个冒号分隔的方式来表示。如B2:D6。 建立基本概念 4.2 Excel 2010的基本操作 n 新建工作簿 方法： 单击 “新建”按钮，或选择“文件” “新建” 命令。新建的工作簿 临时文件名为“工作簿X”。 步骤： 新建空白工作簿 在各工作表中输入和编辑数据 完成各种设置和计算 保存 n 打开一个工作簿 与Word中打开文档的方法相似。 n 保存一个工作簿 与Word保存中文档的方法相似。 工作表的操作与编辑 在工作表中输入数据 1. 数据类型 数值型(也称数字型) 通常意义上的数值，默认右对齐。如：-100,000、+$100、 100%等。有定点式和浮点式(科学记数法)两种表示方式， 如-1.234E+11、3.2783E-08。 文本型 任何数字、字母、汉字及其他字符的组合。默认左对齐。 日期/时间型数据 表示日期和时间。 格式为YYYY-MM-DD HH:MM。如： 2008-3-19与 2008-3-19 16:53等。默认右对齐。 逻辑型 只有两个值：False和 True。默认居中对齐。 工作表的操作与编辑 2. 输入数据 在工作表中输入数据主要有 以下4种方法： n 直接输入数据 n 快速输入数据 n 利用“自动填充”功能输入有规律的数据 n 利用命令导入外部数据。 工作表的操作与编辑 利用“自动填充”功能输入数据 使用“自动填充”功能可以填充日期、时间及数字、文本序列。 例如：“星期一， 星期日”；“一月， ， 十二月”；“1， 2， 3， ”等。 u使用菜单命令填充序列 单击“开始”选项卡，选择“编辑”选项组中的“填充”按钮，在下拉列 表中选择“序列”项实现。 u 使用自定义序列 单击“文件”按钮，在弹出的菜单中依次单击“选项”、“高级”、“编 辑自定义列表”，此后弹出“自定义列表”对话框。 u用鼠标动填充柄的方法 左键拖动填充柄可填充等差数列。右键拖动填充柄可填充填充等 差、等比数列。 工作表的操作与编辑 输入数据的几点说明 如果输入非文本型数据显示成全“#”号时，表示列宽不够 输入分数（分式），应在分数前加入0和空格。如“0 2/3” 把输入的数字作为文本处理，可在数字前加单引号。如 123 输入日期与时间，可按YYYY-MM-DD HH:MM 格式输入 单元格中输入当前系统日期按Ctrl+；键，当前系统时间按 Ctrl+Shift+；键 按Alt+Enter组合键，可强制在某处换行 在一个选定区域内输入同一个数据，输入结束后应按 Ctrl+Enter确认 工作表的操作与编辑 设置数据有效性 p在向工作表输入数据，可能 会输入一些不合要求的数据 ，即无效数据。如成绩、年 龄、性别等。 p实现方法：选择“数据”选项 卡中的“数据有效性”命令， 在弹出的“数据有效性”对话 框中设置某些单元格允许输 入的数据类型和范围，设置 数据输入提示信息和输入错 误提示信息等。 编辑工作表 工作表的编辑主要包括工作表中数据的编辑，单元格、 行、列的插入和删除，以及工作表的插入、移动、复制、删 除、重命名等。 “先选定，后操作”也是Excel的工作方式。 u 选定单元格：单击鼠标左键 u 选定一个单元格区域：鼠标拖动法 u 选定不相邻的多个区域：先选第一个，然后按住 再选其它区域。 u 选定行或列：单击行号或列号 u 选定整个工作表：单击“全选按钮”或按CTRL+A键。 u 撤消选定：单击任一单元格 工作表的操作与编辑 n 清除数据 清除针对的是单元格中的数据，单元格本身仍保留在原位置。 n移动或复制数据 使用菜单命令移动或复制数据。 使用拖动法移动或复制数据。 n粘贴选项与选择性粘贴 一个单元格含有内容、格式、公式及批注等多种信息，使用粘贴 选项或选择性粘贴，复制数据时可以全部信息，也可以只复制部分信 息，还可以在复制数据的同时进行算术运算、行列转置等。 使用粘贴选项。 使用选择性粘贴。 工作表的操作与编辑 工作表中数据的编辑 在Excel中，也提供了查找和替换操作。查找和替换的操作 方法与Word中的类似。替换操作的步骤如下： 选择“开始”选项卡，在“编辑”选项组中单击“查找和选择” 下拉按钮，打开下拉列表。 在打开的下拉列表中选择“替换”命令，打开“查找和替换” 对话框。 在“查找和替换”对话框中，分别在“查找内容”、“替换为” 文本框中输入要查找及替换的文本内容，并按要求设置替换格 式、搜索方式、查找范围，是否区分大小写等。 单击“替换”或“全部替换”按钮。 工作表的操作与编辑 查找和替换 单元格、行、列的插入操作，可以通过“开始”选项卡的“单 元格”选项组中的“插入”按钮实现，操作步骤如下： 在工作表中选择要插入单元格、行、列的位置 在“开始”选项卡的“单元格”选项组中单击“插入”按钮旁的 下拉箭头，打开“插入”下拉列表框。 在下拉列表框中选择相应的命令。如果选择“插入单元格” 命令，会打开“插入”对话框。 在该对话框中作出选择。 单元格、行、列的删除操作与插入操作类似。 工作表的操作与编辑 单元格、行、列的插入和删除 右击工作表标签，在出现的快捷菜单中选择相应的命令，可 实现工作表的插入、移动、复制、删除、重命名等操作。 工作表的操作与编辑 工作表的插入、移动、复制、删除、重命名 图4-15 工作表的快捷菜单图4-16 移动或复制工作表对话框 格式化工作表 工作表的格式化主要包括格式化数据、调整工作表的列宽和行高、 设置对齐方式、添加边框和底纹、使用条件格式等。 1. 格式化数据 设置数字格式 Excel 将常用的格式化命令集成在“开始”选项卡的“字体”、“对齐 方式”、“数字”和“样式”等选项组中。另外也可以通过“设置单元格格 式”对话框来设置。打开“设置单元格格式”对话框有以下两种常用方 法： 单击“字体”、“对齐方式”、“数字”选项组右下角的对话框启动 器。 选择要格式化的单元格或单元格区域，右击鼠标，从弹出的快捷 菜单中选择“设置单元格格式” 工作表的操作与编辑 设置数字格式 可用格式字符控制数字显示格式。常用的格式字符有： u # ：预留数字位数，一个#预留一位数字。 如 123.456 采用#.#格式可以显示为：123.46 u 0 ：预留数字位数，一个0预留一位数字。与#不同的是数字 的位数比0的个数少时，不足的位数用0补足。 如 123.456 采用0000.00格式可以显示为：0123.46 123.45 采用#.0000格式可以显示为：123.4500 u , ：千分位分隔符，从个位开始每3位整加一个逗号分隔。 如 12345678采用0,000.00格式可以显示为： 12,345,678.00 u %：将指定数放大100倍后再加上%。 如 123.456 采用#.#%格式可以显示为：12345.6% 工作表的操作与编辑 格式化工作表 设置文本格式 包括字体、字号、字形、对齐方式等。通过“设置单元格格式”对话框中 的对应选项卡来完成。 2. 表格框线的设置 选择相应的区域，然后打开“设置单元格格式”对话框，在“边框”选项卡 中进行设置。 3. 调整工作表的列宽和行高 采用菜单命令 单击“单元格”选项组中的“格式”，可以精确调整列宽和行高。 采用拖放及双击鼠标操作 将鼠标指向要调整的列宽(或行高)的列(或行)号的右（或下）边框线 上，当鼠标指针变成双向箭头时，拖曳鼠标即可。双击行的下边框可得到 最合适的行高。双击列的右边框可得到最合适的列宽。 工作表的操作与编辑 格式化工作表举例 【例4.2】对【例4.1】中的“职工工资表”进行格式化： 设置扣款数列小数位为1，加千位分隔符和人民币符号“￥”； 设置标题行高为25，姓名列宽为10； 将A1：H1单元格区域合并，标题内容水平居中对齐，标题字 体设为华文彩云、20号、加粗； 工作表边框外框为黑色粗线，内框为黑色细线；姓名所在行 背景色为黄色。 选择行高和列宽的相应设置命令。 工作表的操作与编辑 条件格式 Excel提供了多种内置样式。通过“格式”选项组的“条件格式 ”、“套用表格格式”和“单元格样式”等3组命令可以设置相关样 式。其中，条件格式可以使数据在满足不同的条件时，显示不 同的格式。 【例4.3】对【例4.2】中的工作表设置条件格式： 将基本工资大于等于1500的单元格设置成蓝色、加粗、倾 斜、背景色为红色； 基本工资小于500的单元格设置成红色、加双下划线。 工作表的操作与编辑 4.3 公式与函数 公式与函数的应用 n 公式的使用 公式是用运算符将数据、单元格地址、函数等连接在一起 的式子，以等号“=”开头。以下的都是公式： =5*10-20 常数运算 =A6+B1 对单元格A6和B1中的值相加 =SUM(A1:D4) 使用Excel函数，SUM表示求和 n 公式的输入和复制 单元格中的公式，Excel会自动加以运算，并将运算结果 存放在该单元中。公式引用的单元格数据发生变动时，公式所 在单元格的值也会随之改变 。 【例4.4】 打开“职工工资表.xls”，使用公式计算每位职工 的实发工资。 单元格地址及变化原则 公式与函数的应用 u 单元格地址表示方式 相对地址：以列标和行号组成，如A1，B2，C3等。 绝对地址：以列标和行号前加上符号“$”构成，如 $A$1，$B$2等。 混合地址：述两种地址的混合使用方式。 如$A1(绝对 列相对行)，A$1(相对列绝对行)等。 u 公式经操作后地址的变化原则 因插入、删除行、列等引起的坐标变化时，公式中的相对坐 标及绝对坐标都可能作相应的改变。 复制公式，公式中的绝对坐标不变，相对坐标作相应改变。 移动公式，公式中的绝对坐标及相对坐标均不改变。 函数的使用 u函数 函数是Excel中已经定义好的公式。格式如下： 函数名(参数1,参数2,) 其中参数可以是常量、单元格、区域、公式或其他函数。如： SUM(10,A5,C2:C6) u函数按功能分成以下9类 8 数学与三角函数（比如，SUM、 SUMIF） 8 统计函数（比如， AVERAGE、COUNTIF） 8 逻辑函数（比如， IF） 8 财务函数（比如， PMT） 8 日期和时间函数（比如， TODAY） 8 数据库函数（比如， DCOUNT） 8 查找与引用函数（比如， VLOOKUP） 8 文本函数（比如， LEFT） 8 信息函数（比如， CELL） 公式与函数的应用 主要函数介绍 nSUM (参数1,参数2,) 求各参数的和 nAVERAGE(参数1,参数2,) 求各参数的平均值 nMAX (参数1,参数2,) 求各参数的最大值 nMIN (参数1,参数2,) 求各参数的最小值 nCOUNT (参数1,参数2,) 求各参数中数值数据的个数 nCOUNTIF (计数区域,条件) 求区域中满足条件数据的个数 nSUMIF (条件区域,条件,求和区域) 在求和区域中求出满足条件的数值之和 nLEFT( 文本,N ) 求文本左边的 N 个字符 nRIGHT( 文本,N ) 求文本右边的 N 个字符 nMID( 文本,M,N ) 从文本的第 M 个字符开始取 N 个字符 nLEN( 文本 ) 求文本的长度(即字符个数) nAND(逻辑值1, 逻辑值2, ) 所有参数的逻辑值为真时返回 True；只要一个参数的逻辑值为假即返回 False。 主要函数介绍 n OR(逻辑值1, 逻辑值2, ) 所有参数的逻辑值为假时 返回False ;只要一个参数的逻辑值为真即返回True 。 n DATE (年,月,日) 生成日期 n YEAR(日期) 求日期所在的年份 n MONTH (日期 ) 求日期所在的月份 n DAY(日期) 求日期所在的日数字 n NOW( ) 求系统日期和时间，本函数不参数 n TODAY( ) 求系统日期，本函数不参数 n RANK ( N, 区域 , M ) 返回数值N在区域中数组的排位。 M为0或省略时按降序， M不省略且不为0时按升序。 n FREQUENCY ( 统计区域,间距区域 ) 统计出各区间内满足条件的单元格(数据)个数。 n IF( 条件式, 值1, 值2 ) 条件式成立时，取“值1”，否则取“值2”。 n PMT(Rate,Nper,Pv,Fv,Type) 返回固定利率下的投资或贷款 的等额分期存款或还款额。 函数的输入 函数的输入有以下两种方法： (1) 直接输入法。即直接在单元格或 编辑栏内输入，适用于简单的函数。 (2) 插入函数法。单击“编辑栏”上的“ 插入函数”按钮 ，或单击“公式”选项 卡“函数库”选项组中的“插入函数”按 钮，打开“插入函数”对话框，在该对 话框进行操作。 公式与函数的应用 函数应用举例 【例4.5】对下图的职工工资表，要求使用IF函数在区域I3：I15完 成对职工工资高低进行评价。标准如下：实发工资，=，=， ) 和通配符 ( ?代表单个字符, * 代表多个字符 )，但运算符和通配符要用西文 符号。 2. 高级筛选 条件区域的构造举例 要筛选基本工资大于等于 1000，且职务为“工程师”的所有 记录。条件区域见图4-55。 要筛选基本工资大于等于1000 ，或职务为“工程师”的所有记 录。条件区域见图4-56。 要筛选1000基本工资1500 ，且职务为“工程师”的姓“李”职 工的记录。条件区域见图4-57。 基本工资职务 =1000工程师 图4-55 基本工资职务 =1000 工程师 图4-56 基本工资 基本工资职务姓名 =10001000 或财务部基本工资1000或财务部基本工资1000的人数、实发工资总和、平 均值、最大值、最小值，并将结果放入区域K2：K6的各单元中。 操作步骤如下： 建立条件区域：本例使用例4.13已构造的条件区域，即A17：B19。 在区域K2：K6的各单元中依次输入以下公式： 计算人数：=DCOUNTA(A2:H15,1,A17:B19) 计算实发工资总和：=DSUM(A2:H15,8,A17:B19) 计算实发工资平均值：=DAVERAGE(A2:H15,H2,A17:B19) 计算实发工资最大值：=DMAX(A2:H15,H2,A17:B19) 计算实发工资最小值：=DAVERAGE(A2:H15,”实发工资”,A17:B19) 数据库函数使用举例 p 分类汇总是对数据清单按某个字段进行分类(排序)，将字段值相 同的连续记录作为一类，然后再对某个字段进行求和、求平均 值、求最大值、最小值等的运算。 p “分类汇总”操作通过单击“数据”选项卡的“分组显示”选项组中的 “分类汇总”命令实现。 p 分类汇总有两种：简单汇总和嵌套汇总。 1. 简单汇总 简单汇总是对数据清单的一个或多个字段仅做一种方式的汇总。 【例4.15】在【例4.4】的 “职工工资表”中，求各部门基本工资、 奖金和实发工资的平均值。 分类汇总 嵌套汇总是指对同一字段进行多种不同方式的汇总。 分类汇总后，数据会分3级显示： 单击按钮“1”，只显示清单中的列标题和总计结果； 单击按钮“2”，显示各个分类汇总结果和总计结果； 单击按钮“3”，显示全部详细数据。 若要取消分类汇总，单击“分类汇总”对话框中 “全部删除”按 钮。 【例4.16】在【例4.15】的基础上再统计各部门人数。汇总 结果如下图所示。 2. 嵌套汇总 嵌套汇总结果 数据透视表是一种包含汇总数据的交互式表格。 分类汇总适合按一个字段进行分类，对一个或多个字段进行 汇总。如果要对多个字段进行分类并汇总，这就需要利用数 据透视表。 创建数据透视表，通过单击“插入”选项卡的“表格”选项组的“ 数据透视表”下拉按钮，在下拉列表中选择“数据透视表”命令 实现。 1创建数据透视表 【例4.17】在【例4.4】的 “职工工资表”中，统计各部门各 职务的人数。 数据透视表 根据“数据透视表字段列表”对话框修改数据透视表，也可以通过“数据透 视表工具”下的“选项”及“设计”选项卡修改数据透视表。 (1)更改数据透视表布局 要修改透视表结构中行、列、页、数据字段，可通过“数据透视表字段列 表”对话