EXCEL在企业管理中的高级应用

讲师:李志刚MSN:[e-mail]:[e-mail]企业管理软件(ERP)与EXCEL1、Excel散2、ERP思想3、ERPEXCEL结合培训前言一．让Excel与众不同的技巧二．各类公式和函数的使用三．制作让领导满意的图表四．宏和自定义函数五．其他实用工具及技巧内容EXCEL操作图表分析VBA函数一．让Excel与众不同的技巧1.高效（工具栏、快捷键）2.安全（单元格、工作表和工作簿保护大全）3.便捷（引用、链接、超级链接和高级打印格式）4.美观（专业、规范）内容第一步：打开自定义开关（三种方法）1.右键菜单或工具栏–自定义2.工具-自定义3.视图-工具栏-自定义一(1).高效优化自己的工具栏和菜单一(1).高效优化自己的工具栏和菜单第二步：选择命令--类别，通过拖动可实现：1.在现有的工具栏内添加按钮2.在现有的工具栏内删除按钮3.在现有的菜单内添加命令4.在现有的菜单内删除命令5.创建自己的工具栏6.创建自己的菜单一(1).高效优化自己的工具栏和菜单练习：把下列工具按钮拖入工具栏内一(1).高效熟练使用常用键盘快捷键Excel常用键盘快捷键：1.Alt+Enter在同一单元格中新增一行2.F4重复上一个操作3.Alt+F11显示VB编辑器4.Ctrl+空格选中整列5.Shift+空格选中整行6.Ctrl+C将所选项复制到剪贴板7.Ctrl+V从剪贴板中复制内容到选中单元格8.Ctrl+Z快速撤消操作9.Ctrl+Y快速恢复操作详细键盘快捷键见文件：Excel键盘快捷键大全一(2).安全单元格保护1.锁定单元格和隐藏单元格公式的步骤：第一步：设置单元格格式（格式-单元格-保护）参见文件案例--保护.xls一(2).安全单元格保护1.锁定单元格和隐藏单元格公式的步骤：第二步：保护工作表（工具-保护-保护工作表）一(2).安全单元格保护2.隐藏整行或整列的步骤：（方法一）①选中某行或某列②单击鼠标右键③选择：隐藏一(2).安全单元格保护3.隐藏整行或整列的步骤：（方法二：分级显示）①选中需要隐藏几行或几列②数据-组及分级显示-组合一(2).安全单元格保护3.隐藏整行或整列的步骤：（方法二：分级显示）单击或可在显示和隐藏之间进行切换一(2).安全单元格保护4.隐藏部分单元格内容的技巧（将文字颜色设为和背景色一样）一(2).安全单元格保护5.隐藏部分单元格内容的技巧（将单元格式修改为自定义；；；）一(2).安全工作表保护1.隐藏工作表步骤：（方法一）格式-工作表-隐藏一(2).安全工作表保护2.隐藏工作表步骤：（方法二）①切换到VB编辑器(Alt+F11)②选中需隐藏的工作表③将Visible属性改为２一(2).安全工作簿保护1.设置工作簿密码的步骤：(Excel2000)①文件-另存为-工具-常规选项②设置打开权限密码或修改权限密码一(2).安全工作簿保护2.设置工作簿密码的步骤:(Excel2003)①工具-选项-安全性②设置打开权限密码或修改权限密码一(3).便捷引用、链接和超级链接引用同一工作表中其他单元格，或同一工作簿中其他工作表中的单元格称为“引用”。引用其他工作簿中的单元格称“外部引用”，或“链接”。超链接则是完全不同的概念。它类似于Web浏览器中超链接：单击一个超链接就会打开链接的目标，而不会把任何值传送到链接的目标。几乎所有的公式都有单元格或区域引用.有三种类型的引用:-相对引用:复制公式时,引用单元格的行或列会改变-绝对引用:复制公式时,引用单元格的行和列都不会改变-混合引用:行或列中,有一个是相对引用,另一个是绝对引用在单元格引用的列字母或行号前加入美元符号$便可创建绝对引用或混合引用,可用F4键在四种引用类型中循环选择.选用适当的引用类型可以提高输入/拷贝公式的效率.引用一(3).便捷一个简单的例子(相对引用):一(3).便捷参见文件案例--引用.xls复杂一点的例子(混合引用):一(3).便捷参见文件案例--引用.xls练习：在D4单元格中输入公式，然后拷贝到D4:I13区域中所有的单元格一(3).便捷参见文件案例--引用.xls一(3).便捷链接当打开含有链接的Excel文件时，通常Excel会提示您需不需要更新链接，最好选择不更新。当确实需要更新链接的内容时，可手动更新：编辑-〉链接（如下页所示）我们甚至可以允许链接的文件更名一(3).便捷链接参见文件案例--链接.xls一(3).便捷超级链接在Excel中可以建立下列超链接：-链接到Web站点-链接到其他已有文档-链接到一个还不存在的文档（Excel将在您单击超链接时创建文档）-链接到本文档其他的地方-链接到一个电子邮件地址（单击超链接时，Excel会启动默认的邮件程序）一(3).便捷引用、链接和超级链接链接引用超链接参见文件案例--链接.xls打印格式的设定1.尽量把内容打印在一页内2.如果内容较多需分页打印,则应为每页设置好行列标题3.打印的文件应有公司的Logo,文件名,页码,日期等4.如果需要,可打印行号列标5.用黑白打印机打印时,最好选用单色打印6.一个文件的多张表可同时打印7.文件路径一(3).便捷可以考虑:1.不显示网格线2.选用您独特的字体,如Tahoma,Verdana(公司另有规定除外)3.表头,标题和正文应选用不同的字号和图案以示区别4.规范使用数字格式5.没有必要把所有的数据都放在一张表内6.尽量纵向安排数据,以方便浏览7.设置合适的显示比例,以尽量在一屏显示全部数据(Ctrl+鼠标滚动轮)8.固定窗格9.为每一张工作表命名一个有意义的名称10.删除多余的工作表一(4).美观参见文件案例--美观内容二．各类公式和函数的使用1.公式的定义及使用公式的基本技巧2.不使用公式就知道某些计算结果的技巧3.计算复杂公式中某些中间结果的技巧4.如何定义名称和在公式中使用名称5.几个常用函数的定义及实例6.数据库函数7.六种公式错误值的理解及解决定义1.所有的公式都以等号（=）开始2.公式中可使用的运算符有：加（+）、减（-）、乘（*）、除（/）、乘方（^)、括号（()），运算规则和数学中的一样二(1).公式的定义及基本技巧基本技巧1.在公式中尽量使用引用而少输入数值2.可用鼠标点击单元格来输入引用，而不必手工输入3.适当使用括号或空格以易于阅读和理解4.当公式较长时，可用Alt+Enter强行换行，以方便阅读5.按F2或双击单元格可进入公式编辑状态6.可按F9进行公式的重新计算7.既可以复制公式，也可以复制公式的计算值二(1).公式的定义及基本技巧在Excel的工作表中，选中某些连续或不连续的单元格，那么这些单元格的一些计算（求和、平均值、最大或最小等）会自动显示在状态栏，而不用输入公式计算。如下图：二(2).不使用公式就知道计算结果的技巧如果只想知道某个复杂公式中某部分的计算结果，可选中该部分然后按F9，该部分公式就会显示为计算值。如下图：二(3).计算复杂公式中某些中间结果的技巧参见文件案例--薪酬.xls注意：该部分应该是完整的能计算出结果的公式检查完后可按ESC键退出，而不能按回车键，否则选中的部分就会被计算结果代替二(3).计算复杂公式中某些中间结果的技巧参见文件案例--薪酬.xls在Excel2003中可用公式求值工具一步步测算每一部分的计算结果。如下图：二(3).计算复杂公式中某些中间结果的技巧二(3).计算复杂公式中某些中间结果的技巧参见文件案例--函数应用.xls二(3).计算复杂公式中某些中间结果的技巧参见文件案例--函数应用.xls一个例子二(4).定义和使用名称1/4参见文件案例--名称.xls定义名称Ctrl+F3二(4).定义和使用名称2/4在公式中插入名称F3二(4).定义和使用名称3/4创建名称列表F3二(4).定义和使用名称4/4IFANDCOUNTACOUNTIFSUMIFSUMPRODUCTVLOOKUPHLOOKUPROUND二(5).熟练使用常用函数1/9参见文件案例--函数.xlsIF二(5).熟练使用常用函数2/9参见文件案例--函数.xlsAND二(5).熟练使用常用函数3/9参见文件案例--函数.xlsCOUNTA二(5).熟练使用常用函数4/9参见文件案例--函数.xlsCOUNTIF二(5).熟练使用常用函数5/9参见文件案例--函数.xls/练习--统计不及格人数.xlsSUMIF二(5).熟练使用常用函数6/9参见文件案例--函数.xlsSUMPRODUCT二(5).熟练使用常用函数7/9参见文件案例--函数.xlsVLOOKUP二(5).熟练使用常用函数8/9参见文件案例--函数.xls//所得税模型.xlsROUND二(5).熟练使用常用函数9/9参见文件案例--函数.xls什么是数据库?数据库是一个内涵比较广泛的单词,在Excel中我们通常指数据列表Excel中的数据列表通常满足下列条件:-首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为字段-每一个字段都是唯一的-除第一行外，其他的行被称为记录-不包含空白行或列-见下页示例二(6).数据库及其函数1/4二(6).数据库及其函数2/4参见文件案例--函数.xls数据库函数二(6).数据库及其函数3/4参见文件案例--函数.xls数据库函数中条件的写法:-可以放在数据列表之外的工作表的任意位置,但通常在数据列表的上面或下面-至少包含两行,第一行只包含某些或全部字段名-其他行包括条件-同一行的条件意味”与”-不同行的条件意味”或”-条件中可包含=,,,=,=等比较符-条件中也可包含公式或单元格引用二(6).数据库及其函数4/4参见文件案例--函数.xls六种公式错误值:二(7).公式错误值的理解参见文件案例--错误值.xls三．制作让领导满意的图表1.创建图表快捷键和创建图表标准四步曲2.识别、修改及美化图表的八要素3.如何在图表中使用次坐标轴4.如何在图表中插入对象5.如何创建动态图表6.复合饼图等其他图形的运用内容F11三(1).创建图表快捷键参见文件案例--图表.xls三(1).创建图表标准四步曲3.设置图表选项2.指定数据源1.选择图表类型4.指定图表放置位置三(2).图表的八要素图表标题网格线数据系列背景区域图例坐标轴数据表数据标志识别图表的八要素修改图表的八要素图表标题:命名,设置图案,字体和对齐方式坐标轴:显示或隐藏,设置图案,刻度,字体,数字格式和对齐方式网格线:显示或隐藏,设置图案,刻度图例:显示或隐藏,设置图案,字体和位置数据标志:显示或隐藏,设置图案,字体,数字格式和对齐方式数据表:显示或隐藏,设置图案,字体背景区域:设置图案数据系列:设置图案,次序,重叠比例,分类间距和系列线三(2).图表的八要素三(3).在图表中使用次坐标轴参见文件案例--图表.xls(1)选中某一数据系列,右键进入数据系列格式-坐标轴-次坐标轴三(3).在图表中使用次坐标轴参见文件案例--图表.xls(2)选中该数据系列,右键进入图表类型,选择另一种图型三(3).在图表中使用次坐标轴三(3).在图表中使用次坐标轴三(4).在图表中插入对象窗体按扭三(5).创建动态图表窗体按钮：练习三(5).创建动态图表参见文件练习--动态图表.xls三(5).创建动态图表-函数COLUMN-函数CELL-函数ADDRESS-函数INDIRECT-图表函数SERIES-函数OFFSET参见文件案例--动态图表.xls三(5).创建动态图表Column返回给定引用的列标。语法COLUMN(reference)Reference为需要得到其列标的单元格。如果省略reference，则假定为是对函数COLUMN所在单元格的引用。三(5).创建动态图表Cell返回某一引用区域的左上角单元格的格式、位置或内容等信息。语法CELL(info_type,reference)Info_type为一个文本值，指定所需要的单元格信息的类型。Info_type