Excel电子表格.docx

第三部分Excel 2010电子表格一、引言Excel同Word一样是世界著名的Office系列之一。它强大的功能主要体现在能够制作出各种电子表格，同时可引用公式对表格中的数据进行复杂的运算，并将结果用各种统计图表的形式表现出来。因引，Excel被广泛用于各大管理公司和财务领域。通过本章节学习需要掌握的知识：1、Excel制表基础2、工作簿与多工作表操作3、Excel公式和函数4、Excel数据分析与处理5、Excel与其他程序的协同与共享二、Excel 2010入门1、Excel工作界面活动单元格工作表标签行号名称框列标编辑栏2、工作薄是工作表的集合体，一个工作薄就由一张或多张工作表构成的；（在默认情况下工作薄是由3张工作表组成，其名为工作薄1，一个工作薄就是一个Excel文件，扩展名为.xlsx）注：Excel 2003的扩展名为.xls,2010的为xlsx,高版本的Excel可以兼容低版本的所有功能，低版本的不能打开高版本的内容3、工作表是单元格的集合，一张工作表最多可以由1048576行*16384列个单元格构成 （行号：1-1048576列标：A-XFD列）4、单元格Excel中的最小单位（活动单元格：被激活的单元格），标识方法：列标+行号1）选定单元格：用鼠标单击/用方向键移动选取/用tab移动选取。2）选择连续的单元格区域：从第一个拖动到最后一个单元格 。3）选择间隔单元格区域：ctrl+拖动不相邻的单元格。4）选整行整列：单击行号与列标。5）全选：ctrl+A/单击列标与行号交叉的全选按钮。5、名称框是显示活动单元格的地址6、编辑栏显示或编辑单元格内容二、管理工作表1、插入工作表1）“开始”功能区单元格插入插入工作表2）右击标签插入工作表3）标签右侧单击插入工作表图标（shift+F11）2、删除工作表1）“开始”功能区单元格删除删除工作表 2）右击标签删除3、选择工作表1）单个选择：单击标签2）多个连续选择：选择第一个后按shift键单击最后一个3）多个间隔选择：按住ctrl键单击所需要的工作表4）全选：右击标签全部选定工作表4、移动或复制工作表1）右击标签移动或复制指定移动的位置/建立副本确定（复制工作表也可按住Ctrl+左键拖动）2）“开始”功能区单元格格式移动或复制工作表5、重命名工作表1）右击标签重命名输入新名称确定2）双击标签输入新名称确定3）“开始”功能区单元格格式重命名工作表6、工作表标签颜色设置1）右击工作表标签颜色2）“开始”功能区单元格格式工作表标签颜色7、工作表行高/列宽/隐藏/冻结1)手工调整行高列宽方法一：光标放在行号列线上拖动，多选几行可平均调整多行多列方法二：在行号之间的线上双击可根据内容调整2）精确调整行高与列宽行高列宽：定位单元格“开始”功能区单元格格式行高/列宽/自动调整/最适合行高列宽。3）隐藏行/列/工作表光标定位要隐藏的行列/工作表中“开始”功能区单元格可见性隐藏和取消隐藏行/列/单元格4）“视图”功能区窗口冻结窗格冻结首行/首列/拆分单元格8、保护工作表将光标定位于要保护工作表的任意位置“开始”功能区单元格格式保护工作表输入密码确定9、加密工作薄“文件”选项卡 信息保护文档用密码进行加密输入密码确定保存一次。三、在单元格输入内容1、输入文本汉字、字母及数字的一些组合(文本靠左对齐)2、输入数字阿拉伯数字与$、%的组合（数字靠左对齐）注：当数字超过单元格宽度时会以科学计数法显示，解决的方法是调整单元格宽度，若以#显示，也需调整列宽。3、文本与数字之间的相互转换数字转换为文本： 方法一：前导加单引号：123456方法二：前导加等号，并用双引号括起来：=“038011”方法三：先将单元格格式设置为“文本”型，再直接输入（选中单元格右击设置单元格格式数字选项卡文本确定）。4、输入日期年月日之间用“/”或“-”号隔开，ctrl+；可以提取系统日期。5、输入时间时分秒中间用冒号隔开，ctrl+shift+; ：提取系统时间。6、分数先输入一个0及一个空格，再输入分数。7、逻辑值逻辑真（true）和逻辑假(false)。四、数据的快速填充当一组相邻数据满足某种数学关系或具有某种对应关系时，便可使用“填充”方法输入数据。1、使用填充柄在首单元格中输入常数或公式，横向或纵向拖动填充柄。适用于以下几种情况：1）数字格式时，直接拖动，复制数据；按住Ctrl键拖动，生成步长为1的等差序列。2）日期格式时，直接拖动，生成“日”步长为1的等差序列；按住Ctrl键拖动，复制数据。3）直接拖动公式，自动填充与首单元格相应的公式。4）对于步长不是1的序列，也可以使用填充柄自动填充。方法是：先在相邻两个单元格中输入数据，然后选定并拖动其填充柄。2、使用填充序列在首单元格输入数据选定首单元格及填充区域开始编辑 填充系列3、自定义序列文件选项高级常规编辑自定义列表添加确定（可以定义没有规律的序列）4、任意填充单元格选取单元格区域输入数据按ctrl+回车(可以填充连续或不连续的单元格区域)。5、将单元格行/列内容相互转换选中要转换析内容复制定位光标右击粘贴选项转置。五、利用公式统计计算1、公式的输入及应用公式必须以“=”开头，可由常数、单元格引用、运算符、函数、括号等组成。 公式中的运算符：算术运算符：+、-、* 、/、%等关系运算符：=、文本运算符： 可以将一个或多个文本连接为一个组合文本值。例如：”音乐学1班”在A1单元格,”张三”在B1单元格，可写成：=A1&B1，结果为：音乐学1班张三2、引用运算符冒号(区域运算符)：即对两个引用之间，包括两个引用在内的所有单元格进行引用 ，如 A3:A7逗号(联合引用运算符)：将多个引用合并为一个引用，如 SUM(B5:B10,D5:D10)空格是交叉运算符：产生对同时隶属于两个引用的单元格区域的引用。如SUM(A1:B3 B1:C3)，即对B2至B3单元格引用。3、单元格引用1）相对引用复制公式时地址跟着发生变化，表示方法：列坐标行坐标 例 B6,A4,C5:F8例：C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2 当将公式复制到D1单元格时变为：=B1+C1当将公式复制到A2或B2时单元格时变为 #REF!，即无效引用2）绝对引用复制公式时地址不会跟着发生变化表示方法：$列坐标$行坐标例 $B$6,$A$4,$C$5:$F$8 例：C1单元格有公式：=$A$1+$B$1 当将公式复制到C2或任意单元格时仍为：=$A$1+$B$1 （按下F4即可绝对引用3）混合引用复制公式时地址的部分内容跟着发生变化表示方法：列坐标$行坐标 例 B$6 $列坐标行坐标 例 $B6注：如果行不变，行前加$，如果列不变，列前加$规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。六、函数Excel提供的函数包括财务、日期与时间、数学、统计、文本等一系列函数1、数学与三角函数1）Abs（）：返回指定实数的绝对值Eg: 假定A1单元格中为4A2单元格中为了-4=Abs(A1)，结果为4=Abs(A2),结果为42）Fact（）：返回某一个数的阶乘Eg:假定A3单元格中为5=Fact(A3)，结果为1203）Int（）：取指定数的整数，小数全部舍去Eg:假定A4单元格中为3.1A5单元格中为3.9=Int（A4），结果为3=Int（A5），结果为34）Mod（）：返回两数相除的余数Eg:假定A6单元格中为9，A7单元格中为2 =Mod(A6，A7),结果为15）Round（）：对指定数进行四舍五入格式：=Round(需要四舍五入的具体数值，四舍五入后要保留的小数位数)Eg:假定A8单元格中为123.456=Round（A8,2），结果为123.46=Round（A8,1），结果为123.56）SUM（）：对指定单元格求和 格式：=Sum（求和的单元格） Eg：假定A9到E9的值分别为：1、2、3、4、5 =Sum（A9:E9）,结果为15=Sum（A9：C9，E9），结果为117）Sumif（）：对满足条件的单元格求和格式：=Sumif(条件所在区域，条件，求和实际区域)Eg: 分别求男女语文成绩注意：E2单元格中的公式为：=SUMIF(B2:B7,B2,C2:C7) F3单元格中的公式为：=SUMIF(B2:B7,B3,C2:C7)其中条件值可任意引用，如E2单元格中的条件引用的是B2，也可以引用B4、B7，结果是一致的8）Sumifs（）：对满足条件的单元格求和 格式：=Sumifs(求和单元格区域，条件1区域,条件1,条件2区域,条件2,条件3区域,条件3) 注意：sumifs（）函数是SUMIF（）函数的扩展，SUMIF()只能计算一个条件的，SUMIFS()可使用多个条件 Eg:汇总销售额在100200之间的销售额(此题涉及到了两个条件) 汇总要求E2公式：=SUMIFS(C2:C10,A2:A10,A2)汇总要求E3公式：=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)汇总要求E4公式：=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,1,3)汇总要求E5公式：=SUM(SUMIFS(C2:C10,A2:A10,A,C)汇总要求E6公式：=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,B2)汇总要求E7公式：=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,1;3;4)汇总要求E8公式：=SUM(SUMIFS(C2:C10,A2:A10,A,B,C,B2:B10,1;3;4)9）Sumproduct（）：返回相应的数组或区域乘积的和注：Sumproduct()函数可以多个条件求和，也可以多个条件计数多条件计数格式：SUMPRODUCT(条件1)*(条件2)*(条件N)功能：统计同时满足条件1、条件2到条件N的记录的个数。Eg：统计性别为男且学历为本科学生的人数注：F2中的公式为：=SUMPRODUCT(B2:B7=男)*(C2:C7=本科)F3中的公式为：=SUMPRODUCT(B2:B7=男)*1,(C2:C7=本科)*1) F1和F2中的公式是等价的其中true代表近似匹配，等价于1，false代表精确匹配，等价于0Eg：汇总一班籍贯为安康男性人数=SUMPRODUCT(B2:B13=男)*1,(D2:D13=一班)*1,(E2:E13=安康)*1) 多条件求和格式：SUMPRODUCT(条件1)*(条件2) *(条件N)*求和的实际区域) 作用： 找出同时满足条件1、条件2、条件N的记录，然后对指定的区域求和Eg：统计性别为男性且学历为本科学生的数学总和（假设C列为成绩）=SUMPRODUCT(A2:A10=男)*(B2:B10=本科)*C2:C10) 2、文本函数1）=Left（）：从左边取指定字符串的值，默认从左边的第一位开始取起格式：Left(字符串所在单元格，取字符串的长度)Eg：假定A1单元格有ABCDEFG则：Left(A1,3)，取出的结果为：ABCEg:假定A2单元格中有文博职校则：Left(A2,2),取出的结果为：文博2）Right（）：从右边取指定字符串的值，默认从右边的第一位开始取起格式：Right（字符串所在单元格，取字符串的长度)Eg：A1单元格有ABCDEFG则：Right(A1,3)，取出的结果为：EF3）Mid（）：从中间取指定字符串的值，取的值由起始位置和长度决定 格式：Mid(字符串，起始位置，长度)Eg：A1单元格有ABCDEFG则：Mid(A1,3,2)，取出的结果为：CDEg:A2单元格中有：文博电脑学校则：Mid(A2,3,2)，取出的结果为：电脑4）Len（）：求指定单元格内容的长度Eg:A3单元格中有：美丽的安康！则：Len(A3),返回的结果为：63、查找与引用函数1）Lookup（）：从单行或单列或数组中查找一个值，条件是向后兼容注：此函数需要用到数组，用数组则需要用花括号 Eg：取出学号的第3位与第4位做为班级，如03，则为3班=LOOKUP(MID(A2,3,2),01,02,03,1班,2班,3班) 2）Vlookup（）：是按列查找，最终返回该列所需查询列序所对应的值=Vlookup(两个表格中相等的字段,查找范围,返回值的列数,精确Or 模糊查找) 0或False是精确查找，1或True是模糊查找例：从学生信息中返回姓名到学生成绩的姓名列中，要求学号相等（注：跨工作表时表格与单元格之间用!隔开）=VLOOKUP(A2,学生信息!$A$2:$B$13,2,FALSE)4、日期和时间函数1）Date（）:返回括号里的年月日格式：Date(年，月，日)Eg: =Date(2015，3，9)，返回的结果为：2015/3/92）Now（）：返回系统现在的日期与时间Eg：Now(),返回的结果为：2015/3/29 16：153）Today（）：返回今天的日期Eg：Today（），返回2015/3/294）Weekday（）：返回1至7的整数，代表一周的第几天格式：Weekday(计算的单元格,返回值的表示):返回值的表示：1：代表星期日返回1，星期一返回2，以此类推（缺省或1都代表一周的第一天是星期日)2：代表星期一返回1，星期二返回2，以此类推3：代表星期一返回0，星期二返回1，以此类推Eg:5）Year（）：返回指定日期的年份Eg:假定A1单元格中有日期：2014-9-25则：Year(A1),返回的结果为：2014求年龄 =Year(Now( )-Year(出生年月日) 5、逻辑函数1）Not （）:非，取相反值2）And（）:与，当所有结果都为真，最终的结果才会返回真3）Or（）：或，只要有一个为真，最终的结果就会返回真4）If（）：常用于判断条件格式：=If(条件表达式，值1，值2) 注：如果条件表达式为True时，那么结果取值1；如果条件表达式为False时，则结果取值2） Eg：Eg:5）true（）：逻辑真，等价于1，false（）逻辑假，等价于06、其他函数1）Count（）：统计指定区域的个数Eg:A2到A8单元格中均有值则=Count（A2：A8），返回的结果为：72）Countblank（）:求空单元格个数 Eg: =Countblank(引用区域)3）Counta：求非空单元格个数Eg:=Counta(引用区域)4）AVERAGE：求平均值 5）MAX（）：返回一组数值中的最大值 =Max(C2:F1)6）MIN（）：返回一组数值中的最小值 =Min (C2:F1)7）COUNTIF（）：在单元格X内查找Y出现的次数 格式：=Countif(引用的区域，查找的值) 8）RANK（）：返回某数字在一列数字中相对于其他数值的大小排位=Rank(X，Y) (其中X是一个数值，Y是一个绝对引用区域)注：在书写函数时出错值信息：1、#DIV/0：被零除了2、#NAME：公式中出现了不能识别的文本3、连续多个“#”：单元格内所含的日期、日期比单元格宽或者单元格的时间日期公式产生了负值4、#NULL!：为两个相交叉的区域指定了交叉点5、NUM!：公式或函数中的数值有问题6、REF！单元格引用无效7、#N/A：在函数或公式中缺少可用数值8、#VALUE：使用错误的参数或运算对象类型七、图表1、图表的常用类型1）柱形图和条形图：一般反应不同对象在同一时期某些属性的比较2）折线图：一般反应同一对象在不同时期某些属性的比较；3）饼图：一般反应部分与整体某些属性的比较；2、创建图表1）插入图表：选择数据和字段“插入”功能区图表图表类型确定2）图表的组成：图表区、绘