excel的数据库应用.pptx

Excel应用 主要内容 vExcel与数据库 使用Excel建立数据库的优缺点 vExcel基础知识 vExcel函数与VBA基础 Excel系统介绍 vMicrosoft Excel是微软公司的办公软件 Microsoft office的组件之一，是由 Microsoft为Windows和Apple Macintosh 操作系统的电脑而编写和运行的一款表格软件。 v它具有强大的制作表格、处理数据、分析数据、 创建图表等功能，是当今使用最广泛的一个办公 自动化软件。 用Excel建立数据库的优点 v简单易学（和 ACCESS的比较） Excel是电子表格处理软件，而ACCESS是数据库软 件。用EXCEL可以很方便地设置、运算、统计表格， 但数据的联动性和整体运算能力不及ACCESS。 v可以格式转换 Excel格式具有较强的通用性，易于导入ACCESS、 DBF等数据库中。 用Excel建立数据库的优点 v具备数据库的基本要求：安全性、保密性等 自动备份 加密 Table表 增加 修改 删除 用Excel建立的数据库的缺点 v封装性不好 直接操作原始数据 不易封装。 v没有SQL支持 v有限的安全性 只有用户访问权限，无法进行角色管理。 v不支持多用户同时操作 v数据量（行列数有限） Excel基础知识 v界面 Excel基础知识 v基本操作 输入数据 删除数据 更改数据 复制数据 自动填充数据 查找数据 替换数据 Excel基础知识 v数据类型 数字型 日期型 文本型 逻辑型 v此外，一个工作表也可以含有图表、图形、图 片、按钮和其他对象。这些对象并不包含在单元 格中，而是驻留在工作表的绘图层，它是位于每 张工作表顶部的不可见图层。 Excel基础知识 v数值型 在Excel中，数值型数据是使用最多，也是最为复杂的 数据类型。数值型数据由数字09、正号、负号、小数 点、分数号“/”、百分号“%”、指数符号“E”或“e”、货币 符号“￥”或“$”和千位分隔号“，”等组成。 如果要输入分数（如7/8），应先输入“0”和一个空格， 然后输入“7/8”。如果不输入“0”，Excel会把该数据当做 日期格式处理，存储为“8月7日”。 Excel基础知识 v日期型 v文本型 字符型数据是由字母、汉字或其他字符开头的数据。 例如，表格中的标题、名称等。默认情况下，字符型 数据沿单元格左边对齐。在Excel中，每个单元格最多 可包含32000个字符。 如果数据全部由数字组成，例如，邮政编码、学号等 ，输入时应在数据前输入单引号“”（如341416）， Excel就会将其看作是字符型数据，将它沿单格左边对 齐。 Excel基础知识 v数据保护 设置保护工作簿和工作表可限制对工作簿和工作表进行访问。 Excel 2007提供了多种方式，用来对用户如何查看或改变工作簿 和工作表中的数据进行限制。利用这些限制，可以防止其他人更 改工作表中的部分或全部内容，查看隐藏的数据行或列，查阅公 式等。利用这些限制，还可以防止其他人添加或删除工作簿中的 工作表，或者查看其中的隐藏工作表。 引用 数据与字符串相加 函数与示例 宏 VBA与示例 Excel 函数和VBA基础 引用 v单元格引用：=A1 v工作表间引用：=Sheet2!A1 v工作簿间引用： =Mybook.xlssheet2!$A$1 v相对引用&绝对引用 如果将包含引用的公式向下和向右各复制两个单元格时 ，相对引用“A1”将自动更新为“C3”，而绝对引用“$A$1” 将维持不变。按F4键进行相对引用与绝对引用间的切 换。 数值相加与字符串相加 v两个数值相加使用“+” 1+2=3 v两个字符串相加使用“&” “A” & “BC” = “ABC” v附加：公式转换为数值 如需将单元格中的公式转换为固定的值，拷贝相应的数 据后点击鼠标右键“选择性粘贴”为数值。 公式运算符的优先级 v 如果公式中同时用到多个运算符，Excel 2007将会依照运 算符的优先级来依次完成运算。如果公式中包含相同优先 级的运算符，例如公式中同时包含乘法和除法运算符，则 Excel将从左到右进行计算。Excel 2007中的运算符优先 级如下表所示。其中，运算符优先级从上到下依次降低。 Excel函数 系统提示每一个函数的功能 uu 公式公式 vv 公式公式 1 1 vv 公式公式 2 2 ExcelExcel将函数分为将函数分为9 9大类大类 系统进一步提示函数系统进一步提示函数 中每一个栏位的功能中每一个栏位的功能 数据整理常用函数清单 v =Left(text,Num_chars) v =Right(text,Num_chars) v =Trim(text) v =Len(text) v =Lenb(text) v =If(Logical_test,Value_if_true,Value_if_false ) v =Vlookup(lookup_value,Table_array,Col_ind ex_num,range_lookup) 函数 v Left函数： =Left(text,Num_chars) 提取字符串左边的若干个字符 v Right函数： =Right(text,Num_chars) 提取字符串右边的若干个字符 v 举例： 如提取字符串“螺旋机升降制动器”左边的3个字符“螺旋机”： =Left(“螺旋机升降制动器”,3) 如提取字符串“螺旋机升降制动器”右边的3个字符“制动器”：=Right (“螺旋机升降制动器”,3) v 问题： 如要提取字符串“螺旋机升降制动器”第4-5两个字符 “升降”，如何 定义公式？ 函数 v Len函数 = Len(text) 计算字符串字符数 v Lenb函数 =Lenb(text) 计算字符串长度（字节数），一个双字节字符如汉字算2个 v 举例： =Len(“化1”) 结果为2； =Lenb(“化1”) 结果为3。 什么是宏 v宏是一段定义好的操作，它可以是一 段程序代码，也可以是一连串的指令 集合。 宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误 开始录制宏开始录制宏 操作步骤操作步骤 完成宏录制完成宏录制 运行宏运行宏 录制宏录制宏 需要经常重复执行的某项任务，就可 以把执行这些任务的步骤全部录制在 宏里，把宏变为可自动执行的任务 执行宏的操作执行宏的操作 执行宏的命令 在工具栏上设置宏按钮 使用窗体控件 使用图形对象执行宏 如何录制宏 v宏菜单：菜单栏视图-宏-录制宏/查看宏 v录制过程：单击录制宏-进行操作-停止录制 v运行宏：查看宏-执行 如何为宏指定按钮 v1.调出“开发工具”选项卡 右键功能区-自定义功能区-主选项卡(开发工具） 如何为宏指定按钮 v插入按钮 开发工具-插入-按钮 v指定宏 指定与按钮关联的宏 宏的结构 Sub 主界面() 主界面 Macro Coren Lee 记录的宏 2002-11-5 Sheets(“主界面“).Select End Sub 宏名宏名 批注批注 宏程序宏程序 查看宏代码：点击查看宏-编辑 Visual Basic编辑器 v 用来编辑宏程序或制作更高阶的整合性应用程序 v 附属于Office之下，使用较少的系统资源，达到编辑应用程序的目 的。 为何要使用 VBA 宏缺少伸缩的弹性 记录了许多不需要的步骤 记录了许多不需要的资料 无法使用较深入的功能 无法完成重复性的工作 无法完成需要条件判断的工作 宏宏VBAVBA VBA：Visual Basic for Application 认识VBA v VBA是一个通用程序语言，可共享Microsoft各种相关的 重要软件。 v 以VBA编写的程序允许用户将其复制到Visual Basic中 加以调试，用Visual Basic宏来控制Excel。 v 当记录宏不能满足用户需要或用记录宏无法记录命令时， 可以用VBA语言创造宏来控制工作簿的各项操作。 v 创建用户对话框、工具按钮或自定义命令，把Excel提供的 多个工具按钮连接起来，作为一个工具按钮。 v 利用重复语句控制循环宏内操作，利用不同参数执行某个 相关功能模块，使宏指令完成一系列复杂操作。来连接多 个宏。 v VBA提供了许多内部函数，还允许用户自定义函数来简化 对工作簿、工作表、图表等复杂操作。 VBA程序的基本架构 v对象 面向对象的开发方式 VBA是以操作Office对象为目的的程序语言 v属性 对象所属的特性或特征 v方法 控制对象的内建动作或功能，以便可以让程序顺利打开 v事件 发生于对象上的事情 使用对象的程序语法 Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value = “ABCDE” 容器属性 WorkbooksWorkbooks. .CloseClose 集合对象 VBA的重要语法 vSet Object1 = Object vObject.Property = Value vObject.Method (Setting) VBA控制结构 v分支结构 If 条件1 Then 语句块1 ElseIf 条件2 Then 语句块2 Else 语句块n+1 End If Select Case 测试变量 Case 表达式1 语句块1 Case 表达式2 语句块2 Case Else 语句块n+1 End Select VBA控制结构 v循环结构 For 变量=初值 To 终值 Step 步长 语句块 Next 变量 For Each 对象变量 In 对象集 语句块 Next 对象变量 Do While 条件表达式 语句块 Loop 示例1： Sub Macro01() If Range(“A1“) Empty If Range(“d“ & i) = Empty Then Range(“d“ & i) = Range(“d“ & i - 1) End If i = i + 1 Loop End Sub 示例3： Sub macro03() 分别使用“函数3“表“函数2“表 i = 1 Do While Cells(1, i) Empty i = i + 1 Loop MsgBox (“列数为：“ & i - 1) End Sub 示例4： Sub macro04() 使用“函数2“表 i = ActiveSheet.UsedRange.Rows.Count j = ActiveSheet.UsedRange.Columns.Count MsgBox (ActiveSheet.Name & “表 有 “ & i & “ 行 “ & j & “列。“) End Sub 示例5： Sub Macro05() For Each w In Application.Workbo