office高级应用.ppt

1、Office高级应用,Office高级应用,Excel VBA基础 Word 长篇文档排版技巧 PowerPoint制作 综合应用,EXCEL篇,推荐书籍： 别怕，Excel VBA其实很简单 网站：Excel Home 学习方法: 实践,用户分级,菜鸟,初级用户,中级用户,高级用户,专家,Excel用户水平层级,新手：刚刚开始接触 初级：掌握基本常用功能 中级：熟练使用常用功能+数据透视表+公式 高级：VBA简单编程 专家：EXCEL技能+专业知识+行业经验,重新认识Excel,1、工作薄、工作表、行、列、单元格、区域,2、我们所有的操作都是针对这些对象,3、这些对象本身有自己的属性,Exc

2、el主要功能,数据操作 录入、编辑、格式化等 图表与图形 图表生成、高级图表、交互图表 公式与函数 公式基础、函数、数组公式、自定义函数 数据分析 排序、筛选、列表、数据透视表 宏与VBA 宏、VBA、Excel对象、窗体与控件、类模块,Excel数据操作输入技巧,输入文本 输入分数 日期时间,单引号 Alt+Enter,0 1/2,Ctrl+; Ctrl+Shift,Excel数据操作输入技巧,数据有效性 允许对单元格中所输入的值设置限制 示例 限制数字数值的输入 限制文本条目的长度 限制数值的范围 将单元格条目限制在特定序列中,Excel数据操作输入技巧,使用单元格下拉菜单规范输入 数据有

3、效性 输入序列内容 使用单元格引用创建下拉菜单 定义名称 创建二级下拉菜单,条件格式,根据单元格的当前值来更改单元格的格式 示例 修改格式 隐藏出错值 公式=ISERROR(H19) 出错值的单元格的字体颜色设置为与工作表背景颜色一样,单元格引用,单元格引用范围 单元格引用类型 相对引用 绝对引用 混合引用 F4,Excel公式,普通公式 =A1+B1 =SUM（A1：A10） 数组公式（多重计算） =AVERAGE(B1:B10-A1:A10 命名公式,Excel中的运算符,简单公式：利用其他单元格中的数据进行加、减、乘、除等算术运算的公式，称为“简单公式,2011-9-27,课程编号：XX

4、-XXXX-XX,15,最基础的函数应用,1、在菜单栏中有最基础的函数运用,2、sum、average、count、max、min,3、函数的构成：函数名(参数1,参数2,2011-9-27,课程编号：XX-XXXX-XX,16,日期函数,1、常用：year、month、day、weekday、today,2、其他：datedif、date、days360,3、日期函数的高级应用,2011-9-27,课程编号：XX-XXXX-XX,17,文本函数,1、常用：left、right、mid、concatenate,2、其他：find、replace、proper、char,3、文本函数的高级应用,

5、2011-9-27,课程编号：XX-XXXX-XX,18,统计函数,1、常用：count、counta、countif,2、其他：sumproduct、subtotal 、round,3、统计函数的高级应用,2011-9-27,课程编号：XX-XXXX-XX,19,查找函数,1、常用：lookup、vlookup、index,2、其他：choose、match、hlookup,3、查找函数的高级应用,2011-9-27,课程编号：XX-XXXX-XX,20,逻辑函数,1、常用：if、and、or,2、其他：true、false、not,3、逻辑函数的高级应用,2011-9-27,课程编号：XX

6、-XXXX-XX,21,函数的嵌套,1、用一个函数来做为另一个函数的参数,2、函数嵌套是为了解决复杂的问题,3、函数嵌套没有固定的要求，变化很多,2011-9-27,课程编号：XX-XXXX-XX,22,数组公式,1、用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式,2、可以执行多重计算,它返回的是一组数据结果,3、不要迷恋数组公式,Excel函数,函数的作用 函数的选择与使用 函数的嵌套 =RIGHT(A1, LEN(A1)-3,IF函数,函数语法 IF(条件表达式, 结果1, 结果2,VLOOKUP函数,函数用途 函数语法： VLOOKUP(查找值, 查找区域,

7、返回结果的列号,查找方式) =VLOOKUP(B9,B1:G5,6,0,了解Excel公式的错误值,！ 原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#！错误。 #VALUE! 当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。 #DIV/0! 当公式被零除时，将会产生错误值#DIV/O!。 #NAME? 在公式中使用了Excel不能识别的文本时将产生错误值#NAME,了解Excel公式的错误值,N/A 当在函数或公式中没有可用数值时，将产生错误值#N/A。 #NULL！ 当试图为两个并不相交

8、的区域指定交叉点时将产生错误值#NULL,VBA应用开发与实例,李 辉 微软课程培训讲师,宏与VBA,录制运行宏 VBA基础 EXCEL对象 窗体与控件 类模块,宏 VBA,在应用程序中可以自动运行的一连串的功能指令 能够完成大量重复的操作,Visual Basic for Application 编程语言 基于Excel或其他Office应用程序进行开发,善用宏简化工作,与宏的第一次亲密接触,打开带有宏的工作簿 宏病毒？ 设置Excel的安全性,高】通常适用于用户没有安装防病毒软件的时候 【中】用户可以确保所使用的宏命令是安全的 【低】不对宏作出任何检查,宏的安全性,开始录制宏,操作步骤,完

9、成宏录制,运行宏,录制宏 需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务,执行宏的操作 执行宏的命令 在工具栏上设置宏按钮 使用窗体控件 使用图形对象执行宏,DEMO：制作工资表,宏的结构,Sub 主界面() 主界面 Macro Coren Lee 记录的宏 2002-11-5 Sheets(主界面).Select End Sub,宏名,批注,宏程序,宏的局限性,录制的宏无判断或循环能力. 人机交互能力差,即用户无法进行输入,计算机无法给出提示. 无法显示excel对话框. 无法显示自定义窗体,为何要使用 VBA 宏缺少伸缩的弹性 记录了许多不需

10、要的步骤 记录了许多不需要的资料 无法使用较深入的功能 无法完成重复性的工作 无法完成需要条件判断的工作,宏,VBA,Visual Basic for Application,VBA中最重要的几个概念,对象 Object：工作簿、工作表、单元格、图表等 属性 对象的具体性质、特色 方法 具体的操作、动作、指令 事件 某个能引发具体动作的事件,集合,集合也是对象 许多对象有单复数之分， 例如 Workbook 和 Workbooks； Worksheet 和 Worksheets， 怎么引用对象 Worksheets(“Sheet1”,Excel对象结构,怎么表示对象的属性,对象.属性 Shee

11、t1工作表的A1单元格 Worksheets(“Sheet1”).Range(“A1”,使用对象的程序语法,Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value = “ABCDE,属性,Workbooks.Close,集合对象,Application.Workbooks(“mybook.xls”) Application.Workbooks(“mybook.xls”).Worksheets(“mysheet”) Application.Workbooks(“mybook.xls”).Worksheets(“mysheet”

12、).Range(“A1:D10”) Worksheets(“mysheet”).Range(“A1:D10”,方法,打开工作簿 复制工作表 选中单元格 Set Object1 = Object Object.Property = Value Object.Method (Setting) 对象.方法 Range(“A1).Select,VBA的编程环境,Alt+F11,Sub Hello() MsgBox (Hello, world!) End Sub,Sub RenameWorksheets() RenameWorksheets Macro Sheets(Sheet1).Select She

13、ets(Sheet1).Name = New Name End Sub,Sub RenameWorksheets() Dim i As Integer i = 1 For Each myWorksheet In Worksheets myWorksheet.Name = New Name & i i = i + 1 Next End Sub,VBA基本控制结构,选择结构 循环结构,VBA控制结构分支结构,If 条件1 Then 语句块1 ElseIf 条件2 Then 语句块2 Else 语句块n+1 End If,Select Case 测试变量 Case 表达式1 语句块1 Case 表达

14、式2 语句块2 Case Else 语句块n+1 End Select,VBA控制结构循环结构,For 变量=初值 To 终值 Step 步长 语句块 Next 变量,For Each 对象变量 In 对象集 语句块Next 对象变量,Do While 条件表达式 语句块 Loop,九九乘法表,Sub mysub() Dim i As Integer Dim j As Integer Dim s As String Worksheets(“Sheet1”).Range(“A1”).Value = “九九乘法表 For i = 1 To 9 For j = 1 To 9 s = i & * &

15、j & = & i * j ActiveSheet.Cells(i + 1, j) = s Next j Next i End Sub,示例：在Excel中打印工资表,Sub InsertTitle() Selection.CurrentRegion.Select Cells(Selection.Row, Selection.Column).Select Range(Selection, Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select Do Until ActiveC

16、ell = Selection.Insert Shift:=xlDown Range(Selection, Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select Loop Application.CutCopyMode = False End Sub,示例：对话框与外部应用程序,Private Sub donkey_Click() MsgBox ( I am a donkey ) End Sub,Private Sub Wabbit_Click() Call PlaySou

17、nd(ActiveDocument.Path + wabbit.wav) End Sub,Private Sub turtle_Click() Const Answer = TURTLE Dim Tries As Integer Dim Message, Title, Default0, MyValue As String Tries = 0 Message = What am I? Set prompt. Title = What am I? Set title. Default0 = * Set default for input field. Question: Tries = Trie

18、s + 1 If Tries = 3 Then Default0 = T* End If If Tries = 4 Then Default0 = TUR* End If MyValue = InputBox(Message, Title, Default0) MyValue = UCase(MyValue) Convert to uppercase,示例：保存并关闭系统,Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Dim abc abc = MsgBox(您确认要关闭本系统吗？, vbQuestion + vbYesNo + vbDefaultButton2, 确认) If abc = vbYes Then Sheets(说明).Select Act