数据校验工具培训

Excel函数 宏 VBA与ERP项目数据整理 许军2011年9月 课程表 引用命名区域数据与字符串相加函数与示例宏VBA与示例PM模块数据查错程序与演示 引用 单元格引用 A1工作表间引用 Sheet2 A1工作簿间引用 Mybook xls sheet2 A 1相对引用 绝对引用如果将包含引用的公式向下和向右各复制两个单元格时 相对引用 A1 将自动更新为 C3 而绝对引用 A 1 将维持不变 按F4键进行相对引用与绝对引用间的切换 命名区域 Excel中的命名区域 指的是给某个单元格或者已选定的多个单元格区域 取一个名字 换句话说就是 给选中的单元格 或者多个选中的单元格 取一个名字 为什么要给单元格取名称呢 当然 都是为了方便使用 引用 这在数据处理过程中 会相当的方便 数值相加与字符串相加 两个数值相加使用 1 2 3两个字符串相加使用 A BC ABC 公式转换为数值如需将单元格中的公式转换为固定的值 拷贝相应的数据后点击鼠标右键 选择性粘贴 为数值 Excel函数 系统提示每一个函数的功能 公式公式1公式2 Excel将函数分为9大类 数据整理常用函数清单 Left text Num chars Right text Num chars Trim text Len text Lenb text If Logical test Value if true Value if false Vlookup lookup value Table array Col index num range lookup 函数1 Left函数 Left text Num chars 提取字符串左边的若干个字符Right函数 Right text Num chars 提取字符串右边的若干个字符举例 如提取字符串 螺旋机升降制动器 左边的3个字符 螺旋机 Left 螺旋机升降制动器 3 如提取字符串 螺旋机升降制动器 右边的3个字符 制动器 Right 螺旋机升降制动器 3 问题 如要提取字符串 螺旋机升降制动器 第4 5两个字符 升降 如何定义公式 解答 Rigth Left 螺旋机升降制动器 5 2 注 函数之间可以互相套用 函数2 Trim函数 Trim text 删除字符串中多余的空格 如两个字符之间存在一至多个空格 将保留一个空格举例 如对字符串 字符串 进行Trim Trim 字符串 结果为 字符串 问题 如果删除字符串中所有的空格 如何操作 处理方法 使用Excel的查找替换功能 将空格替换为空 函数3 Len函数 Len text 计算字符串字符数Lenb函数 Lenb text 计算字符串长度 字节数 一个双字节字符如汉字算2个举例 Len 化1 结果为2 Lenb 化1 结果为3 函数4 If函数 If Logical test Value if true Value if false 判断Logical test的真假 如为真 结果为Value if true 如为假 结果为Value if falseIf函数相当有用 举例 If 1 0 A B 结果为 A 练习题 参见Excel练习文件 函数5 Vlookup函数 Vlookup lookup value Table array Col index num range lookup 两个表之间的关联关系 使用Vlookup进行查找 数据整理很重要的一个函数 必须要掌握的一个函数 参见Excel练习文件 什么是宏 宏是一段定义好的操作 它可以是一段程序代码 也可以是一连串的指令集合 宏的作用可以使频繁执行的动作自动化节省时间 提高工作效率 又能减少失误 开始录制宏 操作步骤 完成宏录制 运行宏 录制宏需要经常重复执行的某项任务 就可以把执行这些任务的步骤全部录制在宏里 把宏变为可自动执行的任务 执行宏的操作执行宏的命令在工具栏上设置宏按钮使用窗体控件使用图形对象执行宏 宏功能在excel中的位置 宏的结构 Sub主界面 主界面Macro CorenLee记录的宏2002 11 5 Sheets 主界面 SelectEndSub 宏名 批注 宏程序 VisualBasic编辑器 用来编辑宏程序或制作更高阶的整合性应用程序附属于Office之下 使用较少的系统资源 达到编辑应用程序的目的 宏的复用 为何要使用VBA宏缺少伸缩的弹性记录了许多不需要的步骤记录了许多不需要的资料无法使用较深入的功能无法完成重复性的工作无法完成需要条件判断的工作 宏 VBA VisualBasicforApplication 认识VBA VBA是一个通用程序语言 可共享Microsoft各种相关的重要软件 以VBA编写的程序允许用户将其复制到VisualBasic中加以调试 用VisualBasic宏来控制Excel 当记录宏不能满足用户需要或用记录宏无法记录命令时 可以用VBA语言创造宏来控制工作簿的各项操作 创建用户对话框 工具按钮或自定义命令 把Excel提供的多个工具按钮连接起来 作为一个工具按钮 利用重复语句控制循环宏内操作 利用不同参数执行某个相关功能模块 使宏指令完成一系列复杂操作 来连接多个宏 VBA提供了许多内部函数 还允许用户自定义函数来简化对工作簿 工作表 图表等复杂操作 功能位置数据技术检查要点 1 编号不允许重复2 名称不允许重复3 内容不允许超过限定字节数4 变电间隔功能位置名称最后两个字为 间隔 5 变电间隔功能位置必须能找到上层 变电站功能位置 6 配电功能位置名称中不允许出现 支线 7 输电功能位置仅有两个8 必填项检查9 自动清理非法字符 不可视 不可打印的 10 自动清理内容的前后空格11 自动把0 欧 换成0 零 12 kv自动替换成kV13 单元格内容数据类型判断 日期 数值 14 单元格日期格式必须为YYYYMMDD15 下拉单元格中内容不在下拉列表中16 功能位置规则的检查 比如第四层第一位 第二位的判断等 设备资产数据技术检查要点 1 原资卡号码不允许重复2 设备名称不允许重复3 内容不允许超过限定字节数4 必填项检查5 自动清理非法字符 不可视 不可打印的 6 自动清理内容的前后空格7 单元格内容数据类型判断 日期 数值 8 单元格日期格式必须为YYYYMMDD9 下拉单元格中内容不在下拉列表中10 数据内容要一致 比如供电公司 功能位置 成本中心要匹配 11 累计折旧合计必须等于以前年度累计折旧 本年累计折旧12 根据 双倍余额递减法资产分类表 如果属于该表中的分类折旧方法却不为双倍余额递