EXCEL_函数_宏_VBA 入门知识.ppt

Excel函数 宏 VBA Whatcanyougetfromhere 如何设置EXCEL国际象棋棋盘式底纹什么是VB VBS VBA和宏如何对一张表所有红色字体的数据求和Burn in自动安装脚本究竟有何诀窍 CONTENTS EXCEL函数概述EXCEL宏 VBA介绍VBS在测试中的应用Q A EXCEL函数概述 AVERAGE函数函数名称 AVERAGE主要功能 求出所有参数的算术平均值 使用格式 AVERAGE number1 number2 参数说明 number1 number2 需要求平均值的数值或引用单元格 区域 参数不超过30个 应用举例 在B8单元格中输入公式 AVERAGE B7 D7 F7 H7 7 8 确认后 即可求出B7至D7区域 F7至H7区域中的数值和7 8的平均值 特别提醒 如果引用区域中包含 0 值单元格 则计算在内 如果引用区域中包含空白或字符单元格 则不计算在内 EXCEL函数概述 CONCATENATE函数函数名称 CONCATENATE主要功能 将多个字符文本或单元格中的数据连接在一起 显示在一个单元格中 使用格式 CONCATENATE Text1 Text 参数说明 Text1 Text2 为需要连接的字符文本或引用的单元格 应用举例 在C14单元格中输入公式 CONCATENATE A14 B14 com 确认后 即可将A14单元格中字符 B14单元格中的字符和 com连接成一个整体 显示在C14单元格中 特别提醒 如果将上述公式改为 A14 B14 com 也能达到相同的目的 EXCEL函数概述 COUNTIF函数函数名称 COUNTIF主要功能 统计某个单元格区域中符合指定条件的单元格数目 使用格式 COUNTIF Range Criteria 参数说明 Range代表要统计的单元格区域 Criteria表示指定的条件表达式 应用举例 在C17单元格中输入公式 COUNTIF B1 B13 80 确认后 即可统计出B1至B13单元格区域中 数值大于等于80的单元格数目 特别提醒 允许引用的单元格区域中有空白单元格出现 EXCEL函数概述 DATEDIF函数函数名称 DATEDIF主要功能 计算返回两个日期参数的差值 使用格式 DATEDIF date1 date2 y m d 参数说明 date1代表前面一个日期 date2代表后面一个日期 y m d 要求返回两个日期相差的年 月 天 数 应用举例 在C23单元格中输入公式 DATEDIF A23 TODAY y 特别提醒 这是Excel中的一个隐藏函数 在函数向导中是找不到的 可以直接输入使用 对于计算年龄 工龄等非常有效 EXCEL函数概述 IF函数函数名称 IF主要功能 根据对指定条件的逻辑判断的真假结果 返回相对应的内容 使用格式 IF Logical Value if true Value if false 参数说明 Logical代表逻辑判断表达式 Value if true表示当判断条件为逻辑 真 TRUE 时的显示内容 Value if false表示当判断条件为逻辑 假 FALSE 时的显示内容应用举例 在C29单元格中输入公式 IF C26 18 符合要求 不符合要求 EXCEL函数概述 SUMIF函数函数名称 SUMIF主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range代表条件判断的单元格区域 Criteria为指定条件表达式 Sum Range代表需要计算的数值所在的单元格区域 应用举例 在D64单元格中输入公式 SUMIF C2 C63 男 D2 D63 确认后即可求出 男 生的语文成绩和 SUM LARGE D2 D63 1 2 3 4 5 特别提醒 其中 男 和 女 由于是文本型的 需要放在英文状态下的双引号 男 女 中 EXCEL函数概述 RANK函数函数名称 RANK主要功能 返回某一数值在一列数值中的相对于其他数值的排位 使用格式 RANK Number ref order 参数说明 Number代表需要排序的数值 ref代表排序数值所处的单元格区域 order代表排序方式参数 如果为 0 或者忽略 则按降序排名 即数值越大 排名结果数值越小 如果为非 0 值 则按升序排名 即数值越大 排名结果数值越大 应用举例 如在C2单元格中输入公式 RANK B2 B 2 B 31 0 确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果 特别提醒 我们让Number参数采取了相对引用形式 而让ref参数采取了绝对引用形式 增加了一个 符号 这样设置后 选中C2单元格 将鼠标移至该单元格右下角 成细十字线状时 通常称之为 填充柄 按住左键向下拖拉 即可将上述公式快速复制到C列下面的单元格中 完成其他同学语文成绩的排名统计 EXCEL函数概述 SUBTOTAL函数函数名称 SUBTOTAL主要功能 返回列表或数据库中的分类汇总 使用格式 SUBTOTAL function num ref1 ref2 参数说明 Function num为1到11 包含隐藏值 或101到111 忽略隐藏值 之间的数字 用来指定使用什么函数在列表中进行分类汇总计算 如图6 ref1 ref2 代表要进行分类汇总区域或引用 不超过29个 应用举例 在B64和C64单元格中分别输入公式 SUBTOTAL 3 C2 C63 和 SUBTOTAL103 C2 C63 并且将61行隐藏起来 确认后 前者显示为62 包括隐藏的行 后者显示为61 不包括隐藏的行 EXCEL函数概述 LEFT函数 RIGHT函数函数名称 LEFT主要功能 从一个文本字符串的第一个字符开始 截取指定数目的字符 使用格式 LEFT text num chars 参数说明 text代表要截字符的字符串 num chars代表给定的截取数目 应用举例 假定A38单元格中保存了 我喜欢天极网 的字符串 我们在C38单元格中输入公式 LEFT A38 3 确认后即显示出 我喜欢 的字符 特别提醒 此函数名的英文意思为 左 即从左边截取 Excel很多函数都取其英文的意思 EXCEL函数概述 MID函数函数名称 MID主要功能 从一个文本字符串的指定位置开始 截取指定数目的字符 使用格式 MID text start num num chars 参数说明 text代表一个文本字符串 start num表示指定的起始位置 num chars表示要截取的数目 应用举例 假定A47单元格中保存了 我喜欢天极网 的字符串 我们在C47单元格中输入公式 MID A47 4 3 确认后即显示出 天极网 的字符 特别提醒 公式中各参数间 要用英文状态下的逗号 隔开 EXCEL函数概述 MOD函数函数名称 MOD主要功能 求出两数相除的余数 使用格式 MOD number divisor 参数说明 number代表被除数 divisor代表除数 应用举例 输入公式 MOD 13 4 确认后显示出结果 1 特别提醒 如果divisor参数为零 则显示错误值 DIV 0 MOD函数可以借用函数INT来表示 上述公式可以修改为 13 4 INT 13 4 轻松一刻 excel 在word中分别输入三个 输入三个 或输入三个 或 或 回车 有惊喜哦 如果希望设置国际象棋棋盘式底纹 白色 自定义色 话 应该可以的 不妨试试哦 小窍门 MOD ROW COLUMN 2 0 MOD ROW COLUMN 2 0 Excel宏 VBA 挑战第一关 善用宏简化工作 什么是宏 宏是一段定义好的操作 它可以是一段程序代码 也可以是一连串的指令集合 宏的作用可以使频繁执行的动作自动化节省时间 提高工作效率 又能减少失误 开始录制宏 操作步骤 完成宏录制 运行宏 录制宏需要经常重复执行的某项任务 就可以把执行这些任务的步骤全部录制在宏里 把宏变为可自动执行的任务 执行宏的操作执行宏的命令在工具栏上设置宏按钮使用窗体控件使用图形对象执行宏 宏的结构 Sub主界面 主界面Macro CorenLee记录的宏2002 11 5 Sheets 主界面 SelectEndSub 宏名 批注 宏程序 挑战第二关 深入浅出VBA VBA及其特点 什么是VBA VBA VisualBasicforApplications 是MicrosoftOffice系列的内置编程语言 即应用程序中的VB 它 寄生于 Office应用程序 是Office套装软件的一个重要组件 VBA编程优点1 程序只起辅助作用 大部分功能用Office的 因此简化了程序设计 2 通过宏录制 可以部分地实现程序设计的自动化 3 发行方便 只要发行含有VBA代码的文档或模板即可 4 Office界面对于广大计算机应用人员来说比较熟悉 符合一般操作人员的使用习惯 便于软件推广应用 5 用VBA编程非常简单 为何要使用VBA宏缺少伸缩的弹性记录了许多不需要的步骤记录了许多不需要的资料无法使用较深入的功能无法完成重复性的工作无法完成需要条件判断的工作 宏 VBA VisualBasicforApplication VBA与VisualBasic的差异 VisualBasic以界面为中心的开发方式重点在使用者如何进行操作VBA以文件为中心的开发方式重点在使用者最后看到什么 VisualBasic编辑器 用来编辑宏程序或制作更高阶的整合性应用程序附属于Office之下 使用较少的系统资源 达到编辑应用程序的目的 什么是工程 工程就是模块的集合 包括 用户窗体文件 例如Excel中的工作表以及图表工作表模块类模块ActiveX文件引用的其他工程 VBA程序的基本架构 对象面向对象的开发方式VBA是以操作Office对象为目的的程序语言属性对象所属的特性或特征方法控制对象的内建动作或功能 以便可以让程序顺利打开事件发生于对象上的事情 Excel对象结构 1 Application对象 代表整个Excel应用程序 2 Workbook 工作簿 即是说Excel文件 3 Worksheet 工作表 即工作簿的 页 4 Range 范围对象 指一个或者多个单元格 Excel对象结构 使用对象的程序语法 Workbooks Mybook xls Worksheets Sheet1 Range B5 Value ABCDE 属性 Workbooks Close 集合对象 VBA控制结构 分支结构 If 条件1 Then 语句块1 ElseIf 条件2 Then 语句块2 Else 语句块n 1 EndIf SelectCase 测试变量 Case 表达式1 语句块1 Case 表达式2 语句块2 CaseElse 语句块n 1 EndSelect VBA控制结构 循环结构 For 变量 初值 To 终值 Step 步长 语句块 Next 变量 ForEach 对象变量 In 对象集 语句块 Next 对象变量 DoWhile 条件表达式 语句块 Loop 示例 对红色字体的数据求和 FunctionSUMIFRAD TargetAsrange DimsumAsIntegerDimrAsrangeForEachrInTargetIfr Font Color vbRedThen RGB 255 0 0 sum sum r ValueEndIfNextSUMIFRAD sumEndFunction 创建用户窗体 提示使用者选取 输入或浏览数据使用控件文本框输入文字列表框选取内容选择框选取一个或多个功能 用户窗体窗口的操作 建立用户窗体配置ActiveX控件设置窗口属性配置控件属性及其事件Load与Unload语句加载到内存或从内存中释放用Show和Hide方法控制对象的显示和隐藏 DEMO VBA帮助你完成从必然王国到自由王国的飞跃 轻松一刻 excel 控制特定单元格输入文本的长度 给自己的函数建立新菜单 小窍门 1 在工具栏空白处右击鼠标 选 自定义 选项 打开 自定义 对话框 在 命令 标签中 选中 类别 下的 新菜单 项 再将 命令 下面的 新菜单 拖到菜单栏 2 再在 类别 下面任选一项 如 插入 选项 在右边 命令 下面任选一项 如 超链接 选项 单击 数据 菜单的 有效性 选项 在 设置 卡片 有效性条件 的 允许 下拉菜单中选择 文本长度 然后在 数据 下拉菜单中选择 等于 且 长度 为 4 终极突破 Vbs脚本编程 什么是VBS Vbs是一种Windows脚本 它的全称是 MicrosoftVisualBasicScriptEditon 微软公司可视化BASIC脚本版 VBS是VisualBasic的的一个抽象子集 是系统内置的 用它编写的脚本代码不能编译成二进制文件 直接由Windows系统执行 实际是一个叫做宿主host的解释源代码并执行 高效 易学 但是大部分高级语言能干的事情 它基本上都具备 它可以使各种各样的任务自动化 可以使你从重复琐碎的工作中解脱出来 极大的提高工作效率 应用领域 Windows操作系统VBScript可以被用来自动地完成重复性的Windows操作系统任务 在Windows操作系统中 VBScript可以在WindowsScriptHost的范围内运行 Windows操作系统可以自动辨认和执行 VBS和 WSF两种文件格式网页浏览器网页服务器 Sendkeys函数 Sendkeys函数函数名称 Sendkeys主要功能 将一个或多个按键消息发送到活动窗口 就如同在键盘上进行输入一样 使用格式 SendKeysstring wait 参数说明 string必需的 字符串表达式 指定要发送的按键消息 Wait可选的 特别提醒 对Sendkeys来说 加号 插入符 百分比符号 上划线 及圆括号 都具有特殊意义 为了指定上述任何一个字符 要将它放在大括号 当中 例如 要指定正号 可用 表示 为了指定大括号字符 请使用 及 Sendkeys函数 为了指定那些与SHIFT CTRL及ALT等按键结合的组合键 可在这些按键码的前面放置一个或多个代码 这些代码列举如下 按键代码SHIFT CTRL ALT 特别提醒 为了说明在按下其它按键时应同时按下SHIFT CTRL 及ALT的任意组合键 请把那些按键的码放在括号当中 例如 为了说明按下E与C的时候同时按下SHIFT键 请使用 EC 为了说明在按下E的时候同时按下SHIFT键 但接着按C而不按SHIFT 则使用 EC Burnin自动安装程序 DimWshShell fsSetWshShell WScript CreateObject WScript Shell Setfs wscript createobject scripting filesystemobject If fs FileExists C ProgramFiles BurnInTest bit exe true ThenMsgbox Pleasedon treinstall wscript quitElseIf fs FolderExists C ProgramFiles x86 true ThenWshShell run bitpro x64 exe ElseWs