Excel电子表格处理ppt课件.ppt

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