excel的数据库应用.pptx

《excel的数据库应用.pptx》由会员分享，可在线阅读，更多相关《excel的数据库应用.pptx（41页珍藏版）》请在装配图网上搜索。

1、Excel应 用 主 要 内 容vExcel与 数 据 库 使 用 Excel建 立 数 据 库 的 优 缺 点vExcel基 础 知 识vExcel函 数 与 VBA基 础 Excel系 统 介 绍vMicrosoft Excel是 微 软 公 司 的 办 公 软 件Microsoft office的 组 件 之 一 ， 是 由Microsoft为 Windows和 Apple Macintosh操 作 系 统 的 电 脑 而 编 写 和 运 行 的 一 款 表 格 软 件 。v它 具 有 强 大 的 制 作 表 格 、 处 理 数 据 、 分 析 数 据 、创 建 图 表 等 功 能 ，

2、是 当 今 使 用 最 广 泛 的 一 个 办 公自 动 化 软 件 。 用 Excel建 立 数 据 库 的 优 点v简 单 易 学 （ 和 ACCESS的 比 较 ） Excel是 电 子 表 格 处 理 软 件 ， 而 ACCESS是 数 据 库 软 件 。用 EXCEL可 以 很 方 便 地 设 置 、 运 算 、 统 计 表 格 ， 但 数据 的 联 动 性 和 整 体 运 算 能 力 不 及 ACCESS。v可 以 格 式 转 换 Excel格 式 具 有 较 强 的 通 用 性 ， 易 于 导 入 ACCESS、DBF等 数 据 库 中 。 用 Excel建 立 数 据 库 的

3、优 点v具 备 数 据 库 的 基 本 要 求 ： 安 全 性 、 保 密 性 等 自 动 备 份 加 密 Table表 增 加 修 改 删 除 用 Excel建 立 的 数 据 库 的 缺 点v封 装 性 不 好 直 接 操 作 原 始 数 据 不 易 封 装 。v没 有 SQL支 持v有 限 的 安 全 性 只 有 用 户 访 问 权 限 ， 无 法 进 行 角 色 管 理 。v不 支 持 多 用 户 同 时 操 作v数 据 量 （ 行 列 数 有 限 ） Excel基 础 知 识v界 面 快 速 访 问 工 具 栏 “ 文 件 ” 菜 单 标 题 栏 功 能 区 工 作 表 格 区 工

4、作 表 标 签 列 标 行 号 水 平 滚 动 条 垂 直 滚 动 条 状 态 栏 显 示 比 例 显 示 模 式 编 辑 栏 Excel基 础 知 识v基 本 操 作 输 入 数 据 删 除 数 据 更 改 数 据 复 制 数 据 自 动 填 充 数 据 查 找 数 据 替 换 数 据 Excel基 础 知 识v数 据 类 型 数 字 型 日 期 型 文 本 型 逻 辑 型v此 外 ， 一 个 工 作 表 也 可 以 含 有 图 表 、 图 形 、 图 片 、按 钮 和 其 他 对 象 。 这 些 对 象 并 不 包 含 在 单 元 格 中 ，而 是 驻 留 在 工 作 表 的 绘 图 层

5、， 它 是 位 于 每 张 工 作表 顶 部 的 不 可 见 图 层 。 Excel基 础 知 识v数 值 型 在 Excel中 ， 数 值 型 数 据 是 使 用 最 多 ， 也 是 最 为 复 杂 的数 据 类 型 。 数 值 型 数 据 由 数 字 09、 正 号 、 负 号 、 小 数点 、 分 数 号 “ /”、 百 分 号 “ %”、 指 数 符 号 “ E”或 “ e”、货 币 符 号 “ ￥ ” 或 “ $”和 千 位 分 隔 号 “ ， ” 等 组 成 。 如 果 要 输 入 分 数 （ 如 7/8） ， 应 先 输 入 “ 0”和 一 个 空 格 ，然 后 输 入 “ 7/

6、8”。 如 果 不 输 入 “ 0”， Excel会 把 该 数 据当 做 日 期 格 式 处 理 ， 存 储 为 “ 8月 7日 ” 。 Excel基 础 知 识v日 期 型v文 本 型 字 符 型 数 据 是 由 字 母 、 汉 字 或 其 他 字 符 开 头 的 数 据 。例 如 ， 表 格 中 的 标 题 、 名 称 等 。 默 认 情 况 下 ， 字 符 型数 据 沿 单 元 格 左 边 对 齐 。 在 Excel中 ， 每 个 单 元 格 最 多可 包 含 32000个 字 符 。 如 果 数 据 全 部 由 数 字 组 成 ， 例 如 ， 邮 政 编 码 、 学 号 等 ，输 入

7、 时 应 在 数 据 前 输 入 单 引 号 “ ”（ 如 341416） ，Excel就 会 将 其 看 作 是 字 符 型 数 据 ， 将 它 沿 单 格 左 边 对齐 。 Excel基 础 知 识v数 据 保 护 设 置 保 护 工 作 簿 和 工 作 表 可 限 制 对 工 作 簿 和 工 作 表 进 行 访 问 。Excel 2007提 供 了 多 种 方 式 ， 用 来 对 用 户 如 何 查 看 或 改 变 工 作 簿和 工 作 表 中 的 数 据 进 行 限 制 。 利 用 这 些 限 制 ， 可 以 防 止 其 他 人 更改 工 作 表 中 的 部 分 或 全 部 内 容 ，

8、 查 看 隐 藏 的 数 据 行 或 列 ， 查 阅 公式 等 。 利 用 这 些 限 制 ， 还 可 以 防 止 其 他 人 添 加 或 删 除 工 作 簿 中 的工 作 表 ， 或 者 查 看 其 中 的 隐 藏 工 作 表 。 引 用 数 据 与 字 符 串 相 加 函 数 与 示 例 宏 VBA与 示 例Excel 函 数 和 VBA基 础 引 用v单 元 格 引 用 ： =A1v工 作 表 间 引 用 ： =Sheet2!A1v工 作 簿 间 引 用 ：=Mybook.xlssheet2!$A$1v相 对 引 用 &绝 对 引 用 如 果 将 包 含 引 用 的 公 式 向 下 和

9、向 右 各 复 制 两 个 单 元 格 时 ，相 对 引 用 “ A1”将 自 动 更 新 为 “ C3”， 而 绝 对 引 用“ $A$1”将 维 持 不 变 。 按 F4键 进 行 相 对 引 用 与 绝 对 引 用间 的 切 换 。 数 值 相 加 与 字 符 串 相 加v两 个 数 值 相 加 使 用 “ +” 1+2=3v两 个 字 符 串 相 加 使 用 “ &” “A” & “BC” = “ABC”v附 加 ： 公 式 转 换 为 数 值 如 需 将 单 元 格 中 的 公 式 转 换 为 固 定 的 值 ， 拷 贝 相 应 的 数据 后 点 击 鼠 标 右 键 “ 选 择 性

10、粘 贴 ” 为 数 值 。 公 式 运 算 符 的 优 先 级v如 果 公 式 中 同 时 用 到 多 个 运 算 符 ， Excel 2007将 会 依 照 运算 符 的 优 先 级 来 依 次 完 成 运 算 。 如 果 公 式 中 包 含 相 同 优 先级 的 运 算 符 ， 例 如 公 式 中 同 时 包 含 乘 法 和 除 法 运 算 符 ， 则Excel将 从 左 到 右 进 行 计 算 。 Excel 2007中 的 运 算 符 优 先级 如 下 表 所 示 。 其 中 ， 运 算 符 优 先 级 从 上 到 下 依 次 降 低 。 Excel函 数 系 统 提 示 每 一 个

11、函 数 的 功 能 数 据 整 理 常 用 函 数 清 单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_index_num,range_lookup) 函 数v Left函 数 ： =Left(text,Num_chars) 提 取 字 符 串 左 边 的 若 干 个 字 符v Right函 数 ： =Right

12、(text,Num_chars) 提 取 字 符 串 右 边 的 若 干 个 字 符v 举 例 ： 如 提 取 字 符 串 “ 螺 旋 机 升 降 制 动 器 ” 左 边 的 3个 字 符 “ 螺 旋 机 ” ：=Left(“螺 旋 机 升 降 制 动 器 ” ,3) 如 提 取 字 符 串 “ 螺 旋 机 升 降 制 动 器 ” 右 边 的 3个 字 符 “ 制 动 器 ” ：=Right (“螺 旋 机 升 降 制 动 器 ” ,3) v 问 题 ： 如 要 提 取 字 符 串 “ 螺 旋 机 升 降 制 动 器 ” 第 4-5两 个 字 符 “ 升 降 ” ，如 何 定 义 公 式 ？

13、函 数v Len函 数 = Len(text) 计 算 字 符 串 字 符 数v Lenb函 数 =Lenb(text) 计 算 字 符 串 长 度 （ 字 节 数 ） ， 一 个 双 字 节 字 符 如 汉 字 算 2个v 举 例 ： =Len(“化 1”) 结 果 为 2； =Lenb(“化 1”) 结 果 为 3。 什 么 是 宏v宏 是 一 段 定 义 好 的 操 作 ， 它 可 以 是 一段 程 序 代 码 ， 也 可 以 是 一 连 串 的 指 令集 合 。 宏 的 作 用 可 以 使 频 繁 执 行 的 动 作 自 动 化 节 省 时 间 ， 提 高 工 作 效 率 ， 又 能

14、减 少 失 误 需 要 经 常 重 复 执 行 的 某 项 任 务 ， 就 可以 把 执 行 这 些 任 务 的 步 骤 全 部 录 制 在宏 里 ， 把 宏 变 为 可 自 动 执 行 的 任 务 执 行 宏 的 命 令 在 工 具 栏 上 设 置 宏 按 钮 使 用 窗 体 控 件 使 用 图 形 对 象 执 行 宏 如 何 录 制 宏v宏 菜 单 ： 菜 单 栏 视 图 -宏 -录 制 宏 /查 看 宏v录 制 过 程 ： 单 击 录 制 宏 -进 行 操 作 -停 止 录 制v运 行 宏 ： 查 看 宏 -执 行 如 何 为 宏 指 定 按 钮v1.调 出 “ 开 发 工 具 ” 选

15、项 卡 右 键 功 能 区 -自 定 义 功 能 区 -主 选 项 卡 (开 发 工 具 ） 如 何 为 宏 指 定 按 钮v插 入 按 钮 开 发 工 具 -插 入 -按 钮v指 定 宏 指 定 与 按 钮 关 联 的 宏 宏 的 结 构Sub 主 界 面 () 主 界 面 Macro Coren Lee 记 录 的 宏 2002-11-5 Sheets(主 界 面 ).Select End Sub 查 看 宏 代 码 ： 点 击 查 看 宏 -编 辑 Visual Basic编 辑 器v 用 来 编 辑 宏 程 序 或 制 作 更 高 阶 的 整 合 性 应 用 程 序v 附 属 于 Of

16、fice之 下 ， 使 用 较 少 的 系 统 资 源 ， 达 到 编 辑 应 用 程 序 的 目 的 。 为 何 要 使 用 VBA 宏 缺 少 伸 缩 的 弹 性 记 录 了 许 多 不 需 要 的 步 骤 记 录 了 许 多 不 需 要 的 资 料 无 法 使 用 较 深 入 的 功 能 无 法 完 成 重 复 性 的 工 作 无 法 完 成 需 要 条 件 判 断 的 工 作VBA： Visual Basic for Application 认 识 VBAvVBA是 一 个 通 用 程 序 语 言 ， 可 共 享 Microsoft各 种 相 关 的重 要 软 件 。v以 VBA编 写

17、 的 程 序 允 许 用 户 将 其 复 制 到 Visual Basic中加 以 调 试 ， 用 Visual Basic宏 来 控 制 Excel。v当 记 录 宏 不 能 满 足 用 户 需 要 或 用 记 录 宏 无 法 记 录 命 令 时 ，可 以 用 VBA语 言 创 造 宏 来 控 制 工 作 簿 的 各 项 操 作 。v创 建 用 户 对 话 框 、 工 具 按 钮 或 自 定 义 命 令 ， 把 Excel提 供的 多 个 工 具 按 钮 连 接 起 来 ， 作 为 一 个 工 具 按 钮 。v利 用 重 复 语 句 控 制 循 环 宏 内 操 作 ， 利 用 不 同 参 数

18、 执 行 某 个相 关 功 能 模 块 ， 使 宏 指 令 完 成 一 系 列 复 杂 操 作 。 来 连 接 多个 宏 。vVBA提 供 了 许 多 内 部 函 数 ， 还 允 许 用 户 自 定 义 函 数 来 简 化 对 工 作 簿 、 工 作 表 、 图 表 等 复 杂 操 作 。 VBA程 序 的 基 本 架 构v对 象 面 向 对 象 的 开 发 方 式 VBA是 以 操 作 Office对 象 为 目 的 的 程 序 语 言v属 性 对 象 所 属 的 特 性 或 特 征v方 法 控 制 对 象 的 内 建 动 作 或 功 能 ， 以 便 可 以 让 程 序 顺 利 打 开v事

19、件 发 生 于 对 象 上 的 事 情 使 用 对 象 的 程 序 语 法Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value = “ABCDE” 容 器 容 器 属 性集 合 对 象 VBA的 重 要 语 法vSet Object1 = ObjectvObject.Property = ValuevObject.Method (Setting) VBA控 制 结 构v分 支 结 构If 条 件 1 Then 语 句 块 1ElseIf 条 件 2 Then 语 句 块 2Else 语 句 块 n+1End If Sele

20、ct Case 测 试 变 量 Case 表 达 式 1 语 句 块 1Case 表 达 式 2 语 句 块 2Case Else 语 句 块 n+1End Select VBA控 制 结 构v循 环 结 构For 变 量 = 初 值 To 终 值 Step 步 长 语 句 块 Next 变 量 For Each 对 象 变 量 In 对 象 集 语 句 块 Next 对 象 变 量 Do While 条 件 表 达 式 语 句 块 Loop 示 例 1：Sub Macro01() If Range(A1) Empty Then Range(B2) = Range(B2) + 1 Else R

21、ange(B2) = Range(B2) - 1 End If MsgBox (B2 = & Range(B2)End Sub 示 例 2：Sub macro02() 使 用 函 数 3表 i = 5 Do While Range(A & i) Empty If Range(d & i) = Empty Then Range(d & i) = Range(d & i - 1) End If i = i + 1 LoopEnd Sub 示 例 3：Sub macro03() 分 别 使 用 函 数 3表 函 数 2表 i = 1 Do While Cells(1, i) Empty i = i

22、+ 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.Workbooks(1).Worksheets() va = va + w.Name + Chr(13) Next MsgBox (本 工 作 簿 有 以 下 几 张 表 ： & Chr(13) & Chr(13) & va)End Sub 示 例 6：Sub Macro06() For Each w In Application.Workbooks(1).Worksheets() Sheets(w.Name).Select Range(B2) = w.Name NextEnd Sub 谢 谢 ！