Excel电子表格

1、Excel电子表格作者:日期:2个人收集整理，勿做商业用途第三部分 Excel 2010电子表格一、引言Excel同Word 样是世界著名的 Ofice系列之一。它强大的功能主要体现在能够制作出各种电子表格，同时可引用公式对表格中的数据进行复杂的运算，并将结果用各种统计图表的形式表现出来。因引，Excel被广泛用于各大管理公司和财务领域。通过本章节学习需要掌握的知识：1、Excel制表基础2、工作簿与多工作表操作3、Excel公式和函数4、Excel数据分析与处理5、Excel与其他程序的协同与共享、Excel 2010 入门1、Excel工作界面活动单工作表0 4 |8 弋 10 1112

2、:母14無细编B JT 5J-、v、w、v1班”在A1单元格，”张三”B1文本运算符：&可以将一个或多个文本连接为一个组合文本值。例如：”音乐学单元格，可写成：=A1 &B1,结果为：音乐学 1班张三2、引用运算符冒号（区域运算符）：即对两个引用之间，包括两个引用在内的所有单元格进行引用，如A3:A7逗号（联合引用运算符）：将多个引用合并为一个引用，如SUM（B5:B10,D5:D10）空格是交叉运算符：产生对同时隶属于两个引用的单元格区域的引用。如SUM（A1:B3 B1:C3），即对B2至B3单元格引用。3、单元格引用1）相对引用复制公式时地址跟着发生变化，表示方法：列坐标行坐标例B6,A

3、4,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注：如果行不变，行前加 $，如果列不变，列前加 $规律

4、：加上了绝对地址符“ $”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号 的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。六、函数Excel提供的函数包括财务、日期与时间、数学、统计、文本等一系列函数1、数学与三角函数1) Abs()：返回指定实数的绝对值Eg:假定A1单元格中为4 A2单元格中为了 -4=Abs(A1)，结果为 4=Abs(A2),结果为 42) Fact ()：返回某一个数的阶乘Eg:假定A3单元格中为5=Fact(A3)，结果为 1203) I nt ():取指定数的整数，小数全部舍去Eg:假定A4单元格中为3.1A5单元格中为3.

5、9=lnt (A4),结果为 3=lnt ( A5)，结果为 34) Mod():返回两数相除的余数Eg:假定A6单元格中为9，A7单元格中为2=Mod(A6，A7),结果为 15) Round ():对指定数进行四舍五入格式：=Rou nd(需要四舍五入的具体数值，四舍五入后要保留的小数位数)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

6、： C9, E9),结果为 117) Sumif ()：对满足条件的单元格求和格式：=Sumif(条件所在区域，条件，求和实际区域)Eg:分别求男女语文成绩ABCDEF1姓名性别语二数学女生语文成绩总分男生语文成绩总分2张三女1010100803李青青男20304刘雨女60205张强男40406孙小男20507周艳青立3030注意：E2单元格中的公式为：=SUMIF(B2:B7,B2,C2:C7)F3 单元格中的公式为：=SUMIF(B2:B7,B3,C2:C7)其中条件值可任意引用，如E2单元格中的条件引用的是B2,也可以引用B4、B7,结果是一致的8) Sumifs ()：对满足条件的单元

7、格求和格式：=Sumifs(求和单元格区域，条件1区域,条件1,条件2区域,条件2,条件3区域,条件3)注意：sumifs ()函数是SUMIF ()函数的扩展，SUMIF()只能计算一个条件的，SUMIFS()可使用多个条件Eg:汇总销售额在100200之间的销售额(此题涉及到了两个条件)C2臺 I（E2:E10, E2 汨10,B2 :現03200*）ABCDE 11销售辅汇总硝售额在10020心之间的销崖额(含100和如0)2刘络1007103200A JI B | C |DE |1客户月份销售颔汇总要求汇总结果2A1100容户A的销售额7003B2200客户A的1月份销售额3004C2

8、200害户A的1月份和3月份销售额5005D4200客户A和C的销售额11006A1200客户炳胚的1月份销售额合计3007C2200容户A和客户C的1月份、3月份、4月份销售颔合计7008A3200客户A、C的1月份、3月份、4月份的销售额合计9009A4200|1 110B1200汇总要求 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(C

9、2: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()函数可以多个条件求和，也可以多个条件计数多条件计数格式：= SUMPRODUC条件1)*(条件2)*(条件 N)功能：统计同时满足条件1、条件2到条件N的

10、记录的个数。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、条

11、件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 （字符串所在单元格，取字符串的

12、长度）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 ()：从单行或单列或数组中查找一个值，条件是向后兼容注：此函数需要用到数组，用数组则需要用花

13、括号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(年，月，日

14、)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:A WEEKD

15、AY (Cl6)C| DE2015/3/2652015/3/2762015/3/2872015/3/2912015/3/3022015/3/3132015/4/14盂 lEEKDAYtClG, 2)CDE2015/3/2642015/3/2752015/3/2862015/3/2972015/3/3012015/3/3122015/4/135) Year ():返回指定日期的年份Eg:假定A1单元格中有日期：2014-9-25则：=Year(A1),返回的结果为：2014求年龄 =Year(Now( )-Year(出生年月曰)5、逻辑函数1) Not ():非，取相反值2) And():与，当

16、所有结果都为真，最终的结果才会返回真3) Or ():或，只要有一个为真，最终的结果就会返回真4) If ():常用于判断条件格式：=lf(条件表达式，值1,值2)注：如果条件表达式为True时，那么结果取值1;如果条件表达式为False时，则结果取值2)Eg：C2A ilF (F丽id万恳格木及格JABCDE卩G1姓名语文数学英语物理平均分成绩等级 及格12张三786645636334李青青1088152534. 5不及格刘雨5487364555. 5不及格5张强2265992151. 75不及格67孙小5275108S5& 25不及格周艳青1498453247, 25不及格8张明明3296

17、353361. 5及格9王青8589365566. 25及格10赵宇6557652553不及格113254983S55不及格Eg:C2=帀(陀=盹优秀J IF(陀AM良好J IF(F2-S0J格*不及格J)AL ECDEFGHIL姓容语文数学英语物理平均分成绩等级2张三7366456363ESZ3李看音108152534.54刘雨54S7364555.5不及格5张强2265992151. 75不及格6孙小8675988386. 75良好7周艳青1498453247. 25不及格8张明明3296S533&L5矗格9王青S5S9989692优秀10赵宇6557652553不及格1132549336

18、55不屍格5) true ()：逻辑真，等价于1，false ()逻辑假，等价于06、其他函数1) Count ()：统计指定区域的个数Eg:A2到A8单元格中均有值则=Count (A2: A8)，返回的结果为：72) Countblank ():求空单元格个数Eg: =Countblank(引用区域)3) Counta :求非空单元格个数Eg:=Counta(引用区域)4)AVERAGE求平均值F2-A =AVERAGE(B2:E2)ABcDE1姓名语文数学英语物理平均分2张三786645631633李青青1088152534. 54划雨5487364555. 55张强2265992151

19、. 756孙小8675988886. 757周艳青1498453247. 258张明明3296853361. 55)MAX():返回一组数值中的最大值=Max(C2:F1)6) MIN()：返回一组数值中的最小值=Mi n (C2:F1)7) COUNTIF():在单元格 X内查找Y出现的次数格式：=Cou ntif(引用的区域，查找的值 )H2=COUNTIF (BSeBU.BII)AB(:DEFGH1姓名语文数学英语物理平均分成绩等级语文为酣分的有几个2张三7S66456363及搭313李青青108S152534.5不及恪4刘雨5467364555. S不更格5张强22G5992151.

20、75不及格6孙小907598SS7( 75良好11498453247. 25不及格Q张明明3296S5336E 5及格9王音9089989693. 25优秀10赵宇6557652553不及格H 1cn匸片COis枚8)RAN():返回某数字在一列数字中相对于其他数值的大小排位=Rank(X，Y)(其中X是一个数值，Y是一个绝对引用区域)E2A =F.ANK(?2JF$2:JF|11)ABCDEFGH1语文数学英语物理平均分成绩等级排名汝2三青9089989693. 25傾|1 -13孙小9075988887. 75良妹2_ T7 -LH注：在书写函数时出错值信息：1、#DIV/O :被零除了2

21、、#NAME公式中出现了不能识别的文本3、连续多个“ #”：单元格内所含的日期、日期比单元格宽或者单元格的时间日期公式产生了负值4、#NULL!:为两个相交叉的区域指定了交叉点5、NUM：公式或函数中的数值有问题6、REF单元格引用无效7、#N/A :在函数或公式中缺少可用数值8 #VALUE使用错误的参数或运算对象类型七、图表1、图表的常用类型1）柱形图和条形图：一般反应不同对象在同一时期某些属性的比较2）折线图：一般反应同一对象在不同时期某些属性的比较；3）饼图：一般反应部分与整体某些属性的比较；2、创建图表1）插入图表：选择数据和字段一“插入”功能区一图表一图表类型一确定2）图表的组成：图表区、绘图区、分类轴、图例等3）图片部分的格式更改：在图表对应位置上右击一选择该对象需要修改的值一更改一确定3、创建透视图