Excel课程

1Excel在办公中的应用微软(中国)高级培训师张剑悦2Excel应用•数据管理–高效管理–准确、安全管理–专业美化效果•数据运算–公式–函数•数据分析–排序、筛选–分类汇总–数据透视表分析–单变量求解、模拟运算表、规划求解•数据图表化•数据共享与协作3数据管理4多种字段类型的数据表5Excel操作技巧举例6序列填充方法方法1方法2方法3方法47日期在Excel中的应用可更新固定日期可更新日期应用实例计算工龄8填充柄对日期的填充以工作日填充以月填充9自定义单元格类型•格式类型是显示在单元格中外在的表现形式•与Excel数据本身没有必然联系10自定义单元格格式类型应用自定义类型——#（数值）@（文本）11数据有效性•对单元格内容的填写进行限制•数据管理中使数据准确性加强最好的方式–整数–日期–序列–文本长度–自定义12数据有效性应用13对数据表单元格进行保护•对单元格进行保护和隐藏•设置单元格的“锁定”•设置单元格的“隐藏”•对工作表进行保护•对工作表的局部进行保护•无法选择被保护的单元格区域14数据保护举例对数据表单元格进行只读权限的设置15保护单元格和工作表可以添加密码保护可以对保护的工作表进行操作限制哪个单元格是“锁定”的，哪个单元格就会被保护！16对工作表的隐藏利用“VB编辑器”设置显示属性利用“隐藏”命令对工作隐藏17对工作表进行专业效果的美化•表头–竖排表头–斜线表头•工作表背景–背景图片–单元格底纹•框线设置–实线框–虚框设置–三维效果18设置表格美化的经验•不显示网格线•选用您独特的字体,如Tahoma,Verdana(公司另有规定除外)•表头,标题和正文应选用不同的字号和图案以示区别•规范使用数字格式•没有必要把所有的数据都放在一张表内•尽量纵向安排数据,以方便浏览•设置合适的显示比例,以尽量在一屏显示全部数据(Ctrl+鼠标滚动轮)•固定窗格•为每一张工作表命名一个有意义的名称•删除多余的工作表19数据表美化前的效果20数据表美化后的效果设置“竖排表头”、“工作表背景”和“框线”后的数据表21斜线表头的制作22凹凸感三维效果数据表的制作23工作表组•为多个工作表添加相同的数据信息•为多个工作表设置相同的表格格式–组合工作组–撤销工作组–填充工作组•为分表数据在汇总表计算数据和24用工作表组进行数据添加25用三维引用获得数据结果注意相对地址的引用=一月!B726跨表计算—求分表数据在总表的和注意：SUM函数与Shift键的配合27数据运算28Excel中的公式•所有的公式都以等号（=）开始•公式中可使用的运算符有–加（+）、减（-）–乘（*）、除（/）–乘方（^）–括号（()）–运算规则和数学中的一样29公式和函数应用技巧•在公式中尽量使用引用而少输入数值•可用鼠标点击单元格来输入引用，而不必手工输入•适当使用括号或空格以易于阅读和理解•当公式较长时，可用Alt+Enter强行换行，以方便阅读•按F2或双击单元格可进入公式编辑状态•可按F9进行公式的重新计算•既可以复制公式，也可以复制公式的计算值30在Excel的工作表中，选中某些连续或不连续的单元格，那么这些单元格的一些计算（求和、平均值、最大或最小等）会自动显示在状态栏，而不用输入公式计算。如下图：不使用公式就知道计算结果的技巧31单变量求解32常用函数•基本函数•计数相关函数–Count–Counta–Countblank–Countif•Sumproduct–Product–Sum{}数组函数•Rank•Sumif•CONCATENATE•IF•Left•Right•Mid•Vlookup•Hlookup33财务函数和其他函数•PMT•SYD•FREQUENCY•INDEX•MATCH•OFFSET•DSUM•Dcount•Dcounta•TEXT•VALUE•NA()•INDIRECT•MOD•ROW•INT•TRIM•REPT34相对地址引用和绝对地址引用相对地址：结果更改位置后，公式中引用的单元格也同步更改相对的位置绝对地址：结果更改位置后，公式中引用的单元位置保持不变35相对地址和绝对地址引用举例公式=C5/SUM($C$4:$C$7)中C5使用了相对地址，所以在计算其他部门时，可自动进行调整；而求和区域使用了绝对地址“$C$4:$C$7”，所以在计算时无论哪个部门都会与相同的区域和进行相除。36常用函数SUM求和AVERAGE求平均值37计数相关Count函数是计算数值类型单元格的个数Counta函数是计算所有类型非空单元格的个数Countif函数是计算满足条件的单元格个数38计算总价的函数Sumproduct函数的功能是计算多列（多行）数据的乘积和，通常用来计算已知“单价”和“数量”后的“总价”数据值39排名函数40条件求和计算SUMIF函数的作用是当满足一定的条件后，计算指定数值的和41条件函数IF函数的使用规则=IF（条件，条件成立，条件不成立）42IF函数的嵌套函数的嵌套=IF(L25000,20%,IF(L22000,15%,IF(L2500,10%,IF(L20,5%,0))))43文本函数文本函数包括：文本的提取、文本长度计算、去除空格、文本重复、文本查找等多种运算44Vlookup函数利用Vlookup函数查找字母对应的城市：=VLOOKUP(C2,$M$2:$N$28,2,FALSE)45函数综合举例利用身份证号码判断性别、计算出生日期、计算年龄等结果计算性别：=IF(VALUE(RIGHT(G18,3))/2=INT(VALUE(RIGHT(G18,3))/2),女,男)计算出生日期：=IF(LEN(G18)=15,(CONCATENATE(19,MID(G18,7,2),/,MID(G18,9,2),/,MID(G18,11,2))),(CONCATENATE(,MID(G18,7,4),/,MID(G18,11,2),/,MID(G18,13,2))))计算年龄：=(TODAY()-I18)/36546FREQUENCY频率函数应用计算区间段的个数可以使用Frequency函数{=FREQUENCY(D2:D29,G4:G8)}注意最好使用数组函数得到最终的结果47数据库函数•什么是数据库?•数据库是一个内涵比较广泛的单词,在Excel中我们通常指数据列表•Excel中的数据列表通常满足下列条件:•首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为字段•每一个字段都是唯一的•除第一行外，其他的行被称为记录•不包含空白行或列48数据库函数49数据库函数中条件的写法•可以放在数据列表之外的工作表的任意位置,但通常在数据列表的上面或下面•至少包含两行,第一行只包含某些或全部字段名•其他行包括条件•同一行的条件意味”与”•不同行的条件意味”或”•条件中可包含=,,,=,=等比较符•条件中也可包含公式或单元格引用50数据库函数举例数据库函数要在空白单元格中填写要计算的条件51公式错误值的理解52公式审核及追踪引用追踪引用单元格追踪从属单元格取消所有追踪箭头53公式审核及追踪引用举例追踪引用单元格54公式审核及追踪引用举例追踪从属单元格55数据分析56数据分析方法•条件格式–单元格数值条件–公式条件•排序–一般排序–高级排序•筛选–自动筛选–高级筛选•分类汇总–一级分类–多极分类•数据透视表–分类汇总–对分类字段分组–计算字段和计算项–数据透视的筛选功能57单元格数值条件的条件格式应用制作不同分数段颜色变化的条件格式58公式条件的条件格式应用设置隔行底纹效果的公式条件格式59公式条件的条件格式应用设置周末变色效果的公式条件格式60多级高级排序61自动筛选—自定义筛选筛选年龄字段：条件是大于40且小于5062自动筛选—自定义筛选筛选年龄字段：条件是小于40或大于5063自动筛选—自定义筛选筛选民族字段：不是“汉族”（查看少数民族）的所有员工64高级筛选•高级筛选是对数据列表进行操作，数据列表一般符合下列原则：•首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为字段•每一个字段都是唯一的•除第一行外，其他的行被称为记录•不包含空白行或列•具体步骤参见文件Manual_AdvancedFilter.doc•高级筛选的条件一般单独放置在数据列表之外的某个地方，写法如下：•至少包含两行,第一行只包含某些或全部字段名•其他行包括条件•同一行的条件意味”与”•不同行的条件意味”或”•条件中可包含=,,,=,=等比较符•条件中也可包含公式或单元格引用65高级筛选高级筛选要在空白单元格中添加筛选条件同行条件为“与”关系；异行条件为“或”关系筛选条件筛选结果66分类汇总•对文本字段分类•再对其他字段进行汇总–求和–求平均值–求计数•多级分类汇总67分类汇总举例操作方法：先对分类字段进行排序，然后再使用“分类汇总”命令68用数据透视表进行数据分析•对数据表生成数据透视表•对数据字段进行分类•对数据字段进行汇总•对分类字段进行多级分类•制作二维分类表•隐藏汇总行•对多个数据进行汇总•对分类字段进行分组•对数据透视表添加计算字段或计算项69字段数据表70生成数据透视表71数据透视表分析举例查看“各部门”、各个“文化程度”，平均工资的情况二维分类表72数据透视表分析举例查看“各部门”、各个“职务”，平均工资的情况二级分类表73数据透视表分析举例查看“各部门”、各个“职务”，平均工资和平均年龄的情况二级分类表和多计算字段74按时间分组的数据透视表应用75单变量求解76模拟运算表77图表分析78图表的生成79图表的美化图表分析做了个图表80图表分析举例堆积图图表分析计算数据分量的和，分析总量的关系81图表分析举例三维饼图图表分析分析总量份额之间的比例关系82图表分析举例复合饼图图表分析分析总量份额之间的比例关系83图表分析举例折线图图表分析分析数据走势情况84图表分析举例雷达图图表分析分析数据走势和数量大小关系85图表分析举例双轴图表图表分析既查看数据大小，又分析数据走势86其他图表举例四象限图水平线图直方图瀑布图87创建动态图表•Offset•以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。•语法•OFFSET(reference,rows,cols,height,width)•Reference作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用；否则，函数OFFSET返回错误值#VALUE!。88Offset函数•OFFSET(reference,rows,cols,height,width)–Rows相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。–Cols相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。–Height高度，即所要返回的引用区域的行数。Height必须为正数。–Width宽度，即所要返回的引用区域的列数。Width必须为正数。–如果省略height或width，则假设其高度或宽度与reference相同。89宏90什么是宏•定义–宏:一组指令,告诉Excel执行一个或多个操作.•两种创建宏的方法–a.录制–b.在VBE中新建91录制宏•录制宏的步骤–a.启动宏录制器–b.命名宏,指定快捷键,保存位置及描述–c.执行所要录制的操作–d.停止宏录制器•使用宏录制器的优点:–快速–帮助学习•使用宏录制器的缺点:–某些语句不能通过录制而必须编写;常产生多余的代码.92编写宏•编写宏的步骤–启动VBE编辑器–选择:插入-模块–输入Sub,后面加宏的名称和()–输入VBA代码–输入EndSub结束93编写宏—VBE窗口介绍94宏语句•最常用的宏语句–MsgBox“text”–If…Then…Else–DoWhile…Loop–For…Next–ForEach…Next