Excel讲义(新)

Office之ExcelOffice软件之Excel的中级应用分享知识就是分享力量分享经验就是分享财富思维开发价值行动演练成效————人力资源部（升级版）Office之Excel2目录概述函数公式统计图表制作邮件合并实例操作Office之Excel3概述•Office办公软件中的Word及ExcelWord–主要用来进行文本的输入、编辑、排版、打印等工作Excel–是一个用于建立与使用电子报表的实用程序，也是一种数据库软件，用于按表格的形式来应用数据记录。Office之Excel4认识工作簿、工作表、单元格工作簿-是运算和存储数据的文件，每一个工作簿由一些工作表组成，用户可根据需要添加更多的工作表。-当启动Excel2000时，系统会自动创建一个名为Book1的工作簿窗口。工作表-通常称为电子表格。每一个工作表由256列和65536行组成。单元格-工作表的行列相交形成单元格，它是构成表格的基本单元。每一个单元格的名称由列号和行号共同组成Office之Excel5Word、Excel快捷键的应用Ctrl键组合:1.Ctrl+A=页面内容全选2.Ctrl+S=内容保存3.Ctrl+D=复制上格内容4.Ctrl+F=查找5.Ctrl+G=定位6.Ctrl+H=替换Office之Excel67.Ctrl+K=插入超级链接8.Ctrl+Z=撤销上一步操作9.Ctrl+X=剪切10.Ctrl+C=复制11.Ctrl+V=粘贴12.Ctrl+B=加粗13.Ctrl+N=新建文档14.Ctrl+R=复制左格内容15.Ctrl+U=加下划线Office之Excel716.Ctrl+I=斜体17.Ctrl+O=打开文档18.Ctrl+P=打印19.Ctrl+home=返回到页面第一格20.Ctrl+end=返回到页面最后一格21.Ctrl+PgUp/PgDn=在工作簿中自由切换工作表22.Alt+Tab=打开的文档间进行切换23.Ctrl+Shift+pgup=选择工作簿中的多个工作表24.Ctrl+Shift=选择输入法Office之Excel8•什么是函数Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM函数对单元格或单元格区域进行加法运算。•Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数第一章函数公式Office之Excel9函数公式运算符•函数公式运算符之一1.算术运算符算术运算符名称用途示例+加号加3+3-减号“减”以及表示负数。3–1*星号乘3*3/斜杠除3/3%百分号百分比20%^脱字符乘方3^2（与3*3相同）Office之Excel10•函数公式运算符之二2.比较运算符比较运算符名称用途示例=等号等于A1=B1大于号大于A1B1小于号小于A1B1=大于等于号大于等于A1=B1=小于等于号小于等于A1=B1不等于不等于A1B1Office之Excel11•函数公式运算符之三3.文本运算符这实际上是一个文字串联符——&，用于加入或连接一个或更多字符串来产生一大段文本。如North&wind，结果将是Northwind。Office之Excel12•函数公式运算符之四4.引用运算符引用运算符名称用途示例:冒号区域运算符，对两个引用之间，包括两个引用在内的所有单元格进行引用。B5:B15,逗号联合操作符将多个引用合并为一个引用。SUM(B5:B15,D5:D15)Office之Excel13•if公式的多重运用；并列条件与选择排除条件•if函数与其他公式的合并运用：if与and或or•计算年及月公式从两个日期中计算月数•统计单元格内字符的个数：len•从给定的一段文本字符左起取n个字符：left•从给定的一段文本字符右起取n个字符：right•取给定两个数相除的整数：=int(被除数/除数)•取给定两个数相除的余数=mod(被除数,除数)•从给定的一段文本字符中间m位取n个字符:mid常用函数公式的应用Office之Excel14•Count用于求单元格个数的统计函数•Countif用于求符合某一条件的单元格个数的统计函数•Frequency求区域中数据的频率分布•数据库函数的用法Office之Excel15一、if公式判断一个条件是否满足，如果满足返回一个值，如果不满足，返回另一个值。公式写法举例：=if(A1=“条件值”,“结果”，“另一结果”)Office之Excel16二、and和or与if的配合应用And：两个及以上并列条件公式写法：=if(and(A1=“甲”,B1=“乙”…）,“此结果”,“彼结果”）Or：两个或两个以上选择条件公式写法：=if(or(A1=“甲”,B1=“乙”,…),“此结果”,“彼结果”)Office之Excel17三、计算年月日公式年=year月=month日=day从两个日期中计算出月数A:2006/3/1B:2000/12/31=(year(A)-year(B))*12+month(A)-month(B)Office之Excel18四、自动更换年、月、日公式如：从昨天2006/3/2更换至今天2006/3/3=year(today())&”年”&month(today())&”月”&day(today())”日”注：此公式的日期随着当天的实际日期而变更Office之Excel19五、len公式统计文本字符串中字符的个数公式写法：=len(A1)Office之Excel20六、left和right公式Left：从给定的一段文本字符左起取n个字符公式写法：=left(A1,n)Right：从给定的一段文本字符右起取n个字符公式写法：=right(A1,n)Office之Excel21七、int公式取给定两个数相除的整数公式写法：=int(被除数/除数)Office之Excel22八、mod公式取给定两个数相除的余数公式写法：=mod(被除数,除数)Office之Excel23九、mid公式从给定的一段文本字符中间m位取n个字符：公式写法：=mid(A1，m，n)Office之Excel24十、count公式(counta)计算包含数字的单元格的个数：公式写法：=count(A1，A2，A3…)Office之Excel25十一、countif公式计算某个区域中满足给定条件的单元格数目：公式写法：=countif(A1:A10,”条件”)Office之Excel26十二、Frequency公式以一列垂直数组返回某个区域中数据的频率分布：公式写法：{=frequency(A1:A10,A12:A15)}备注:示例中的公式必须以数组公式的形式输入。请选中从公式单元格开始的单元格区域A12:A15。按F2，再按Ctrl+Shift+Enter。如果公式未以数组公式的形式输入，则返回的结果为第一个值。Office之Excel27AB1分数分段点2797038579478895856507818959881097公式说明（结果）=frequency(A2:A10,B2:B5)分数小于等于70的个数(1)=frequency(A2:A10,B2:B5)成绩介于71-79之间的个数(2)=frequency(A2:A10,B2:B5)成绩介于80-89之间的个数(4)=frequency(A2:A10,B2:B5)成绩大于等于90的个数(2)Office之Excel28十三、数据库函数公式的应用Office之Excel291、例：某果园的果树的高度、使用年数、产量与利润的统计数据表如图所示，希望求出（1）有多少种苹果树的树高在10~16英尺之间（2）苹果树与梨树的最大利润值是多少（3）高度大于10英尺的苹果树的最小利润是多少（4）苹果树的总利润（5）高度大于10英尺的苹果树的平均产量（6）果园中所有树种的平均使用年数Office之Excel302、求解步骤（1）创建空白工作簿或工作表，将数据录入Excel中（2）由于第一问需要求解有多少种苹果树的树高在10~16英尺之间，因此建立查询条件高度在10与16之间（3）利用函数DCOUNT求解满足条件的单元格数来计算高度在10~16英尺的苹果树的种类。公式为：DCOUNT(A4:E10,“高度”,A1:F2)，求出有一种苹果树满足条件（其他以此类推）Office之Excel31十四、其他常用函数Max,最大值=max(A1:B10)Min,最小值=min(A1:B10)Average,平均值=average(A1:B10)Round,按指定位数取整Sumif,在某种条件下求和=sumif(A1:A10,”条件值”,B1:B10）Text,返回到文本文件=text(A1,“条件值”)Rank返回一个数字在数字列表中的排位=rank(A1,A1:B10,0)Large返回到组据组中第N个最大值=large(A1:B10,N)Trim清除字符串中多余的空格Value,将一个代表数值的文本字符串转换成数值=Value(text)索引公式=offset(book2!$A$1,(match(A1,book2$A$1:$A$n,)-1),N)Office之Excel32第二章统计•筛选：自动筛选和高级筛选•排序：普通排序法和自定义排序法•分类汇总：普通汇总和多重条件下数据汇总•数据透视表：一般透视和多重条件下透视•分列运用Office之Excel33一、筛选自动筛选：单一条件下的筛选Office之Excel34高级筛选：自定义或多重条件的筛选可根据需求进行条件设定，筛选出所要的结果Office之Excel35二、排序-----自定义排序法（第一步，打开工具菜单中的选项）Office之Excel36（第二步，设定序列）Office之Excel37（第三步，返回到数据菜单中选择排序并选定主要关键字）Office之Excel38（第四步，单击排序弹出菜单中的“选项”）Office之Excel39（第五步，选择排序选项中自定义好的序列并确定）Office之Excel40（第六步，点击排序菜单中的“确定”完成排序）Office之Excel41三、分类汇总多重条件下的分类汇总（1、选定多重条件排序）Office之Excel42（2、汇总第一重）Office之Excel43（3、在第一重汇总基础上汇总第二重）Office之Excel44四、数据透视表多重条件下数据透视（1、选择数据透视表）Office之Excel45（2、选择数据源区域）Office之Excel46（3、透视结果布局）Office之Excel47（4、确定选项，得出结果）Office之Excel48五、分列将一段文本按分隔符分列出来（1、选择分列，判定数据具有分隔符）Office之Excel49（2、设定分隔符号，如“,”、“-”、“/”等）Office之Excel50（3、选择列数据格式）Office之Excel51（4、完成分列）Office之Excel52六、Excel多个活页公式设置及链接实践中,我们常常需要在一个Excel簿中建立多个活页记录一些数据,并相互之间进行计算,例如:工作日志和月报的统计.下表是XX公司每日的行政用品入货清单,工作人员需要统计每天的数量及费用,并于月底统计整月的数量及费用.该如何处理呢?Office之Excel53Office之Excel54•第一步建立初始数据档案要点：建立原始档案时，尽量保持项目的完整及全面性，为防止在日后中有项目增加，可在原始档案保留一定空格栏备用。Office之Excel55•第二步设定每日及总表框架Office之Excel56•第三步在月报总表内写入公式Office之Ex