Excel综合技巧免费培训精粹II.doc

Excel综合技巧免费培训精粹II第1课(2010-07-27 18:16:58) 转载标签： 杂谈分类： excel 本节课程由linpansheng老师授课，主要有如下内容：查找与替换查找快捷键：ctrl+f替换快捷键：ctrl+h替换选项：格式；区分大小写；单元格匹配；区分全/半角；范围：工作表、工作簿；搜索：按行、按列；查找范围：公式、值、批注高级查找（使用通配符）l ？（问号）任何单个字符；说明：表示要查找的内容与问号所在的位置相同的单一字符。示例：abc?可查找abc1,abcd等，此列中的1和d均用?代替。l *（星号）任何字符说明：表示要查找的内容与问号所在的位置相同的多个字符。示例：abc*可查找abc123,abcexb等，例中的123和exb均用*代替。中文汉字也是一样的算做一个字符l （波形符）后跟？、*或者查找问号、星号或波形符。说明：查找一个问号、星号或波形符号，可在后跟?、*或示例：abc?可查找abc?其中，中文汉字也被当做一个字符可以进行查找和替换。定位定位单元格区域快捷键： ctrl+G或F5定位条件：批注；常量；公式：数字、文本、逻辑值、错误；空值；当前区域；对象；行内容差异单元格；列内容差异单元格；引用单元格；从属单元格：直属、所有级别；最后一个单元格；可见单元格；条件格式；数据有效性：全部、相同本节课的主要内容都是比较常见的，就是有些技巧，尤其是通配符的使用是以前我所不知道的。期待下次课程。感谢excelhome的免费培训课程。详情请看/Excel综合技巧免费培训精粹II第2课(2010-08-03 15:59:40) 转载标签： 杂谈分类： excel 第二课筛选、分类汇总和单元格格式设置夜空星辰的课件做的很简洁，开始的时候还没觉得怎么样，后来发现一课内居然有这么多的内容真是太丰富了。课件开头还有上课作业的正确解答，惊喜啊。本节课程主要内容就是筛选、分类汇总和单元格格式设置。类似内容比较经常用到。1、 自动筛选当需要批量查看符合条件的记录时，自动筛选功能就十分好用及常用，它是以连续区域的首行为条件区域。方法：数据筛选自动筛选筛选出来的记录还可以再次筛选。自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛选结果，点击“数据筛选自动筛选”菜单命令即可。另外，执行数据自动筛选时，如果“自定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中“或”选项。在自动筛选后得到的单元格，如果要复制到别的地方，需要在选中内容后使用：F5定位条件可见单元格确定，然后复制，这样才能得到所需要的内容。2、 高级筛选高级筛选可以筛选含有指定关键字的记录，并且将结果显示在两个表中进行数据比对或其他情况。方法：数据筛选高级筛选；在弹出的“高级筛选” 对话框，选择筛选方式中的“将筛选结果复制到其他位置”单选按钮。将“列表区域”设置为指定的数据区域，“条件区域”设置为筛选选定内容的条件，“复制到”设置为任意空白单元格，单击“确定”按钮，系统便自动将符合条件的记录筛选出来，并复制到指定的单元格区域。在设定筛选条件的时候，excel的通配符也是可以在这里使用的。3、 分类汇总分类汇总是对一个数据列表根据用户指定字段进行分类合计、计数、平均等统计。方法：数据分类汇总选择“分类字段、汇总方式、选定汇总项、替换当前分类汇总、每组数据分页、汇总结果显示在数据下方”确定分类汇总的前提是要先排序。如果不排序，产生的结果将是错误的。单字段和多字段的汇总的操作区别就在于是否勾选替换当前 分类汇总。每组数据分页是让汇总的结果每级数据分页。复制汇总后的结果，与自动筛选的结果复制手段相同。4、 单元格选项卡和自定义格式跨列居中这个方法是个亮点，以前很少运用。可以避免由于合并单元格而产生的种种错误。常用自定义数字格式的代码有：G/通用格式不设置任何格式，按原始输入的数值显示#数字占位符，只显示有效数字，不显示无意义的零值0数字占位符，当数字比代码的数量少时，显示无意义的0？数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数. 小数点%百分数，千位分隔符E科学计数符号显示格式里的下一个字符*重复下一个字符来填充列宽_留出与下一个字符等宽的空格“文本”显示双引号里面的文本文本占位符，如果只使用单个，作用是引用原始文本颜色颜色代码例：将数据直接变成万为单位，保留1位与4位数代码为：#!.0,万元#!.0000万元总结下，本次课程的内容都是在日常常用功能的提高，很容易理解跟应用。期待下次课程。Excel Home免费在线培训中心：Excel Home论坛：/Excel综合技巧免费培训精粹II第3课(2010-08-10 15:07:47) 转载标签： 杂谈分类： excel 讲师：函数菜鸟 昵称：木兰概要：1、 自定义格式的代码格式快捷键ctrl+1分类选项：常规、数值、货币、会计专用、日期、百分比、分数、科学技术、文本、特殊“自定义数字格式”允许用户创建符合一定规则的数字格式，自定义格式并不会改变数值或文本本身，只改变数据的显示方式。默认分类中的格式代码都可以在自定义中体现。先选择格式类型后再进入自定义就可以看到当前格式的代码。自定义格式的代码格式 结构1： 未指定条件时的结构：以“分号”分隔组成相应的区段，每个区段代码作用于特定的对象。完整结构为四区段。 4个区段：作用于正数；作用于负数；作用于0值；作用于文本 3个区段：作用于正数；作用于负数；作用于0值 2个区段：作用于0和正数；作用于负数 1个区段：作用于所有数据 有分号而省略代码的，将被“隐藏”。例：￥#,#0;红色￥-#,#0;蓝色G/通用格式; 该代码文本类型的字符将不在单元格中显示，但是在编辑栏中会显示出来。 ￥#,#0; 该代码只显示正数，其余0值、负值、文本都被隐藏 隐藏全部值使用代码： ; 结构2： 根据实际需要自定义条件时的结构。完整结构为四区段。 4个区段：大于条件值；小于条件值；等于条件值；作用于文本 3个区段：作用于满足条件值1；作用于满足条件值2；作用于其他情况 2个区段：作用于满足条件值1；作用于满足条件值2 1个区段：代码作用于满足指定的唯一条件的数据例：洋红80G/通用格式;蓝色80的数字洋红色显示;80G/通用格式 该代码只显示80的数字，其余数字都被隐藏（文本显示洋红色）根据条件书写不同，条件暗中不包含的部分如果没有指定格式显示，将被“隐藏”，在没有指定文本格式显示的条件下（没有第4区段），文本显示方式受第一区段的影响。*excel2003中文版能识别的颜色名只有8种：黑色、红色、白色、蓝色、蓝绿色、绿色、黄色和洋红。如果使用英文版，需要把颜色字符换为英语单词。Excel2003也能识别按颜色x指定的颜色，其中x是1至56之间的数字，代表56种颜色。2、 数字及文本格式代码介绍代码符号 符号的含义和作用G/通用格式 按原始输入显示，不设置任何格式，同“常规”格式 # 数字占位符，只显示有效数字不显示无意义0值（“.#”表示小数点后显示1位，多余1位的将被四舍五入） 0 数字占位符，当数字比代码数量少时，显示无意义的0。 ？ 数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数。 . 小数点 , 千位分隔符 % 以百分数显示 * 重复下一个字符来填充列宽_ 下划线，留出与下一个字符等宽的空格（利用这种格式可以实现“对齐”） ! 强制显示下一个文本字符，可用于分号（；）点好（.）问号（？）等特殊符号的显示。 文本占位符，如果只使用单个，作用是引用原始文本，如果使用多个，则可以重复文本。 “文本” 显示双引号里面的文本（半角引号）。如果输入的时候不在代码处写”，确定后系统会自动给文本添加上引号。 / 显示后面的字符（与”功能相近，使用后系统自动用”替换/） 颜色 显示相应的颜色。如前所述 颜色n 显示以数值n代表的调色板的颜色，n在156之间 条件值 设置条件使用，一般由比较运算符和数值构成 DBnum1 转换数值为中文小写数字 DBnum2 转换数值为中文大写数字 DBnum3 全角阿拉伯数字与小写中文单位的结合，如“356”显示为“百十” E 科学记数的符号3、日期时间格式代码m:使用没有前导0的数字显示月份； Mm:使用有前导0的数字显示月份； mmm，使用英文缩写显示月份； mmmm，使用英文全称来显示月份； Mmmmm:显示月份的英文首字母； d、dd：显示日期有无前导的0； ddd、dddd：英文缩写、英文全称表示星期几； aaaa、aaa：使用中文显示星期几、中文显示星期几，无星期二字； yy：两位数表示年份； yyyy：四位数表示年份； h、hh：无/有前导0来显示小时； m、mm：无/有前导0来显示分钟； s、ss：无/有前导0来显示秒钟； ：显示超出进制的时间； AM/PM：使用12小时制显示小时； 上午/下午：使用12小时制显示小时。总结下，本次课程的内容很丰富，非常丰富。有些技巧以前从来不知道。技巧啊，这些能快速提高工作效率的技巧希望以后能有更多Excel Home免费在线培训中心：Excel Home论坛：/俺最近在excelhome报名免费在线学习VBA。/index.php把上课的笔记发这里啦。VBA培训第一课1. VBA发展史。1.1. VBA发展史l 60年代中期出现了BASIC编程语言l 1991年微软发布了Visual basic for windowsl 1993年，VBA首次应用于商业软件Microsoft excel 5/Project 4 （excel 5之前的版本中，只能使用XLM宏 MS Excel 4.0宏）；在工作表标签上单击右键，插入，MS Excel 4.0 宏表Macro1。宏表内容类似公式，都以“=”开头。宏表的功能最终应为VBA所取代。l VBA可以集成到其他商用软件中：AutoCAD，Wordperfect 等。1.2. 什么是VBA?1.2.1. VBA全称是Visual Basic for Application1.2.2. VBA是Microsoft Visual Basic 的应用程序版本。1.2.3. VBA使Office形成了独立的编程环境。1.3. VBA与Visual Basic2. VBA用途与作品展示2.1. VBA应用场景l 自动执行重复的操作l 进行复杂的数据分析对比l 生成漂亮的复杂的报表和图表l 个性化用户界面l Office组件的协同工作l Excel二次开发例子：把不同的银行分别到不同的工作表里。用VBA就可以把原始数据拆分了。非常简单例子：另一个excel工具运行后，出现了新的工具栏。最后的效果跟刚才一样。使用代码可以实现很多功能。还可以用来娱乐，做成象棋等等。3. Visual Basic编辑器3.1. 什么是Visual Basic编辑器？l Visual Basic编辑器（Editor）通常简称为VBEl VBE的用途：输入和修改代码；运行和调试代码l 如何打开VBE窗口？excel菜单：工具宏Visual Basic编辑器；快捷键3.2. VBE窗口三大组成部分：菜单、工具栏、功能窗口3.3. VBE功能窗口介绍l 如何显示相关功能窗口VBE的“视图”菜单中选中相关功能窗口。l 工程资源管理器快捷键；VBE中的“资源浏览器”，类似于目录树结构。（单击“”可以将目录折叠）l 代码窗口（快捷键F7），单击工具选项编辑器自动选项选中。自动完成与提示，自动缩进格式。为代码添加注释是一个好的习惯，以“”为开始。VBA编程工具1. VBA窗口鼠标滚屏工具-FreeWheel v2.4l 官方主页 /SiliconValley/2060/freewheel.htmll 下载链接/SiliconValley/2060/freewheel.zipl 安装演示2. 代码美化工具-SmartIndent v 3.5 （用来给代码改为缩进格式）l 官方主页 http:/www.oaltd.co.uk/Indenter/IndentFrm.htmll 下载链接http:/www.oaltd.co.uk/DLCount/DLCount.asp?file=IndenterVBA.exel 安装演示小结：第一课让我对VBA有了初步的认识，对它的作用有了大概了解，在当了几个excel VBA开发出来的游戏之后，太好玩了，这让我对VBA有了更多兴趣，期待下一课赶紧到来！excelVBA第二课(2010-06-14 17:06:51) 转载标签： 杂谈分类： excel VBA培训第二课最简单的VBA程序（宏） 1. 宏安全级。1.1. 什么是“宏安全级”l “宏”可能包含病毒，“宏安全级”是excel自带的宏保护功能。l 用户可以设置不同的宏保护级别来实现是否允许在excel文件里运行VBA程序。l 只有启用宏才能运行excel里的VBA程序。例：打开的文件时候有警告信息“由于安全级被设为高，并且没有附加数字签名的可信任证书，因此这些宏被禁用。若要运行这些宏，请将安全级别设置为更低级别（不建议使用），或请求作者使用证书颁发机构所颁发的证书对宏进行数字签名”如果点击文件里面的程序也会提示如上信息。打开宏安全性对话框通常有三种方法：(1) 选择“工具”“宏”“安全性”对话框(2) “工具”“选项”“安全性”“宏安全性”(3) 在工具栏区域单击右键选择Visual Basic，单击Visual Basic工具栏上的“安全性”选项“安全性”按钮。宏安全性对话框有四个选项：非常高；高；中；低。如果希望自己编写的代码或者录制的宏可以运行，那么可以把安全级设置为“中”或“低”级别。如果设置为中级别，当用户再一次打开一个含有VBA代码的文件时，excel会弹出一个询问的对话框，问启动宏还是禁用宏。如果选择“低”级别，那么就不在具备宏保护的功能。推荐是“中”级。修改过宏安全性之后要重新启动excel才能生效。2. 录制宏2.1. 什么是宏？l “宏”使用VBA代码记录下来的一系列操作步骤的组合。可以使用“录制宏”功能，把操作步骤像录制下来；l 执行这个宏，可以把宏记录下来的操作依次执行一遍；l 可以使用“宏”简单完成需要重复执行多次的相同操作。宏可以简单的帮助我们完成需要需要重复执行多次的相同操作，节约我们的操作时间简化我们的工作量。例子：录制突出显示的宏。将选中单元格格式设置指定格式。打开录制新宏对话框有两种方式：(1) 单击工具宏录制新宏(2) Visual Basic工具栏录制宏按钮然后填写宏名。保存在选项有3个选项：个人宏工作簿（在这台计算机里的每个excel文件都可以使用。保存在一个固定的文件夹里。第一次使用这个选项excel会自动生成这个文件夹）；新工作簿（新建一个工作簿来存放宏）；当前工作簿（保存在正在使用的工作簿）。还可以填写快捷方式。填写完之后单击确定。在开始录制宏之前要考虑下需要让宏做那些操作，如果没有好的计划，会录下多余的操作步骤。录制完成之后，可以单击停止录制工具栏当中的停止录制按钮，或者使用Visual Basic工具栏中的停止录制按钮，还可以用工具宏停止录制。3. 执行录制宏3.1. 怎样执行“宏”l 直接执行l 快捷键执行l 窗体控件（图片、图形）执行l 常用工具栏执行例：通过直接执行宏的方式虽然很方便，但是并不是很快捷。给宏指定相应的快捷键在宏录制好之后也是可以修改的。打开宏对话框，选中需要修改的宏名，在宏选项中重新指定快捷键，不能使用excel以有的快捷键，如果相同，原来的快捷键就会失效。如果指定大写字母，会自动鞋为Ctrl+Shift+指定字母。 虽然使用快捷键可以让使用宏的速度变快，但是如果宏很多，不方便记忆。如果别人使用这个工作簿，也要记忆很多快捷键，那么就不太方便。可以在工作表中增加窗体控件中的按钮，把宏指定给按钮，然后通过单击按钮来执行宏。 在常用工具栏任何位置单击右键，选择窗体，打开窗体工具栏，单击窗体中的按钮，鼠标变成十字，在希望放按钮的位置点击左键，拖动适当位置画出一个矩形，代表按钮大小。放开鼠标左键，弹出指定宏对话框，选择宏名称，确定。这样就把按钮指定好了。鼠标左键可以选中并修改按钮名称。鼠标左键还可以拖动位置进行修改。需要需要重新修改的时候，鼠标右键点击按钮就可以进行相应修改，之后单击按钮外的任意位置结束修改。 还可以把宏指定给图片、图形、艺术字等。指定给图片的方法：首先在插入图片来自文件指定图片。调整图片大小位置，右键单击图片，选择指定宏，在指定宏对话框里选择需要指定的宏名称。使用时，当鼠标移动到图片上，鼠标变成手指形状就可以点击。如果不希望在工作表上添加控件，还一种方法就是在常用工具栏中添加自定义工具按钮，通过单击自定义工具按钮来执行宏。例：首先在工具栏区域单击右键选择自定义，自定义对话框里单击命令选项卡，在类别下选择宏，在命令列表中选择自定义按钮，把按钮拖动到工具栏希望位置中松开左键，在按钮上单击右键，选择指定宏，打开指定宏对话框，选择宏名称，单击确定。还可以单击右键给按钮定义名称，或者更改按钮图像形状。3.2. VBE中单元格的引用方式l 绝对引用：运行VBA程序时，代码操作的单元格区域不会随着当前活动单元格的改变而改变；l 相对引用：运行VBA程序时，代码操作的单元格区域会随着当前活动单元格的改变而改变。跟函数公式的引用方式一样。停止录制宏窗体上的绝对引用相对引用按钮，按钮平的时候是绝对引用。绝对引用后，不管先选的是什么单元格，执行宏的时候都是最初设定的那些单元格。 单击Visual Basic常用工具栏中的Visual Basic编辑器，进入VBE窗口，在左边的工程资源管理器中找到模块，双击模块1，在代码窗口中看到刚才操作录制下来的代码。小结：第二课让我对宏有了初步的认识，对它的录制、使用有了大概了解，看到视频中的例子之后，我认识到宏可以提高重复操作的效率，节约时间，期待能尽早自己编写代码！ VBA培训第三课从宏开始学习VBA(2010-06-22 08:34:50) 转载标签： 杂谈分类： excel VBA培训第三课从宏开始学习VBA1. 什么是宏。l 宏可以用录制操作命令集的方式对excel中的大多数操作进行录制，并且可以将这些录制内容（即代码）进行回放和编辑。2. 实例演示宏录制，执行（回放）的过程l Alt+f8打开宏对话框；l Alt+f11在excel窗口与VBE编辑器窗口切换；l 在编辑器中，F5是运行宏，效果与运行子过程/用户窗体按钮等效。l 录制“宏”过程中会产生很多无用代码，删除也不影响宏的执行。宏录制的时候不会增加判断和循环，并且为了提高宏运行的速度所以要对录制的宏进行修改完善。录制的宏有时候也会有错误。3. 介绍一个批量打印文档的宏l 这个宏可以在2003下使用，2007下不能直接使用l 可以执行在选定目录下所有excel文件的自动打印小结：第三课对我来说无比痛苦。课程上讲解的命令似乎看明白了，等写作业的时候完全不知道该怎么办。于是赶紧在周末休息的时候跑到书店买了VBA的书。终于可以方便的查询到需要用到的命令代码了。还是纸质书比电子版的方便。对于代码缺乏认识，是我目前的缺陷。努力看书弥补这放面的不足吧。记录下，已经了解意思的一些代码： .Strikethrough = False 文字中间有无一条水平删除线 .Superscript = False 设为上标字符 .Subscript = False 设为加下标字符 .OutlineFont = False 空心字体 .Shadow = False 带阴影字体或者对象有阴影 .Underline = xlNone 设置下划线类型 .ColorIndex = xlAutomatic 单元格的字体颜色这些是什么意思已经都能了解了，其他还需要不断努力学习VBA第四课sub过程与程序流程控制语句(2010-06-29 14:34:38) 转载标签： 杂谈分类： excel VBA培训第四课 sub过程与程序流程控制语句/index.php俺在excelhome参加的在线学习课程VBA终于到第四课，也就是Excel VBA免费培训初级班的最后一课了 1. sub过程1.1. 一个过程就是一组完成所需操作的VBA代码的组合。VBA的过程主要包括“sub过程”和“function过程”两种。 Sub过程不可以返回值，function过程可以返回值。1.2. 声明sub过程 声明sub过程Public|PrivateStaticSub过程名称(参数1，参数2)语句块Exit Sub语句块End Sub Public 可选的。用于声明对所有模块中的所有其它过程都可以使用的过程。Private 可选的。用于声明只能在包含该声明的模块中使用的过程。Sub 可选的（但 Sub 或 Function 二者需选其一）。表示该过程没有返回值。Function 可选的（但 Sub 或Function 二者需选其一）。表示该过程会返回一个可用于表达式的值。1.3. 从过程执行另一个过程。 输入过程名称以及参数，参数用逗号隔开 过程名参数1，参数2,.在宏对话框里不显示私有过程的宏名称。在语句的前面加上“”表示注释语句，并不执行。 在过程名称以及参数前使用Call关键字，参数用括号括起来，并用逗号隔开。Call过程名(参数1，参数2，)Call关键字可写可不写，为了清楚建议写。 利用Application对象的Run方法。Application.Run表示过程名的字符串（或字符串变量）参数1，参数2，2. 判断与循环语句。 If语句If 逻辑表达式 Then 语句块1Else 语句块2End If备注：F8可以单步执行。程序流程图可以让思路更清晰明确。 If 逻辑表达式1 Then 语句块1 ElseIf 逻辑表达式2 Then 语句块2 ElseIf 逻辑表达式3 Then 语句块3 Else 语句块n End IfIf语句通常只用在二元判定，虽然可以镶嵌。 Select Case语句Select Case测试表达式 Case表达式列表1 语句块1 Case表达式列表2 语句块2 Case表达式列表3 语句块3 Case表达式列表n 语句块n Case Else 语句块n+1End Select备注：测试表达式必须为数值表达式或字符串表达式；表达式列表可以是用逗号分开的表达式，也可以使用To或Is关键字，如：Case 1,2,3,4 Case 1 to 4 Case Is数据透视表和数据透视图=完成，这就完成了一张数据透视表，剩下就是根据需要把各个项目拉来拉去。数据表的优点，可以根据任何数据源的变动，函数表格重新计算速度非常慢，而且不能在原有表格中体现，需要重新加入列，重新拉公式。在数据透视表中，只要点击刷新数据就可以了。 最大最快速的优势。用数据透视表来修改文件，拖动字段就可以了，快速方便。可以根据不同的分析要求进行变换。根据这些方法创建了我自己的数据透视表。虽然还不太知道该怎么利用这样的表格进行分析，不过相信继续学习肯定能有更多的提高。至少，在处理数据的速度上快多了。勉励一下俺自己，明天就要开始第二课了，加油数据透视表第二课小结(2010-03-16 14:53:05) 转载标签： 杂谈分类： excel 数据透视表 第二课 小结课程内容1、 透视表的基础知识：英语pivot table；最早lotus公司提出，微软excel5具备功能，excel97加强了功能；excel2000有了数据透视图。2、 特点：运算速度快。布局灵活。3、 谋定而后动a) 数据源结构对数据表的影响。一维数据源跟二维数据源举例分析生成的透视表的不同。一维数据源产生的数据透视表的列字段是由品种字段的数据项组成；二维数据源的数据透视表的列字段，是由数据源表里每一个列作为计算项所生成的；一位数据源的数据透视表可以进行选择和再次选择，同时会出现列的合计；二维数据源的数据透视表的列字段是数据源每一列的求和项，在这种情况下，求和项可以进行选择但不可以进行复选，丢失的选项需要重新将列字段拖入数