EXCEL 函数 宏 VBA入门知识

1、 What can you get from here? ? 如何设置EXCEL国际象棋棋盘式底纹 什么是VB，VBS，VBA和宏 如何对一张表所有红色字体的数据求和 Burn-in自动安装脚本究竟有何诀窍 CONTENTS ?EXCEL 函数概述 EXCEL 宏-VBA介绍 VBS在测试中的应用 Q/A EXCEL 函数概述 ? AVERAGE函数函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,) 参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），参数不超过30个。 应用举例：在

2、B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。 EXCEL 函数概述 ? CONCATENATE函数函数 函数名称：CONCATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 使用格式：CONCATENATE(Text1，Text) 参数说明：Text1、Text2为需要连接的字符文本或引用的单元格。 应用举例：在C14单元格中输入公式：=CONCAT

3、ENATE(A14,B14,.com)，确认后，即可将A14单元格中字符、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。 特别提醒：如果将上述公式改为：=A14&B14&.com，也能达到相同的目的。 EXCEL 函数概述 ? COUNTIF函数函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF ( Range, Criteria) 参数说明：Range代表要统计的单元格区域，Criteria表示指定的条件表达式。 应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,=80)

4、，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现。 EXCEL 函数概述 ? DATEDIF 函数函数 函数名称：DATEDIF 主要功能：计算返回两个日期参数的差值。 使用格式：=DATEDIF(date1,date2,“y(m ,d)”) 参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。 应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),y) 特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输

5、入使用，对于计算年龄、工龄等非常有效。 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函数函数

6、 函数名称：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 函数概述 ? RAN

7、K函数函数 函数名称：RANK 主要功能：返回某一数值在一列数值中的相对于其他数值的排位。 使用格式：RANK（Number,ref,order） 参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。 应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。 特别提醒：我们让Number参数采取了相对引用形式，而让ref参数采取了

8、绝对引用形式（增加了一个“$” 符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。 EXCEL 函数概述 ? SUBTOTAL函数函数 函数名称：SUBTOTAL 主要功能：返回列表或数据库中的分类汇总。 使用格式：SUBTOTAL(function_num, ref1, ref2, .) 参数说明：Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用来指定使用什么函数在列表中进行分类汇总计算（如图6）；ref

9、1, 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单元格中

10、保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符。 特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。 EXCEL 函数概述 ? MID函数函数 函数名称：MID 主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。 使用格式：MID(text,start_num,num_chars) 参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。 应用举例：假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中

11、输入公式：=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 * 在

12、word中分别输入三个“=” ，输入三个“”或输入三个“*”或 “-” 或 “#” ，回车。有惊喜哦 * 如果希望设置国际象棋棋盘式底纹(白色自定义色)话，应该可以的，不妨试试哦 =MOD(ROW()+COLUMN(),2)0， =MOD(ROW()+COLUMN(),2)=0 挑战第一关 善用宏简化工作 什么是宏 ? 宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。 宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误 开始录制宏开始录制宏 录制宏录制宏 需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在

13、以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务 操作步骤操作步骤 执行宏的操作执行宏的操作 完成宏录制完成宏录制 ? 执行宏的命令执行宏的命令 ? 在工具栏上设置宏按钮在工具栏上设置宏按钮 ? 使用窗体控件使用窗体控件 ? 使用图形对象执行宏使用图形对象执行宏 运行宏运行宏 宏的结构 宏名宏名 Sub 主界面主界面() 主界面主界面 Macro Coren Lee 记录的宏记录的宏 2002-11-5 Sheets( 主界面主界面).Select End Sub 批注批注 宏程序宏程序 挑战第二关 深入浅出VBA VBA及其特点 什么是什么是VBA

14、 ? ? VBA（Visual Basic for Applications）是Microsoft Office 系列的内置编程语言，即应用程序中的VB。它“寄生于”Office应用程序，是Office 套装软件的一个重要组件。 VBA编程优点编程优点 ? 1程序只起辅助作用，大部分功能用Office的，因此简化了程序设计。 ? 2通过宏录制，可以部分地实现程序设计的自动化。 ? 3发行方便，只要发行含有VBA代码的文档或模板即可。 ? 4. Office界面对于广大计算机应用人员来说比较熟悉，符合一般操作人员的使用习惯，便于软件推广应用。 ? 5用VBA编程非常简单。 Visual Basi

15、c for Application Visual Basic for Application 宏宏 为何要使用 VBA VBA VBA 宏缺少伸缩的弹性 记录了许多不需要的步骤 记录了许多不需要的资料 无法使用较深入的功能 无法完成重复性的工作 无法完成需要条件判断的工作 VBA与Visual Basic的差异 ? Visual Basic以界面为中心的开发方式 重点在使用者如何进行操作 ? VBA以文件为中心的开发方式 重点在使用者最后看到什么 Visual Basic编辑器 ? 用来编辑宏程序或制作更高阶的整合性应用程序 ? 附属于Office之下，使用较少的系统资源，达到编辑应用程序的目

16、的。 什么是工程 ? 工程就是模块的集合，包括： 用户窗体 文件，例如Excel中的工作表以及图表工作表 模块 类模块 ActiveX文件 引用的其他工程 VBA程序的基本架构 ? 对象 面向对象的开发方式 VBA是以操作Office对象为目的的程序语言 ? 属性 对象所属的特性或特征 ? 方法 控制对象的内建动作或功能，以便可以让程序顺利打开 ? 事件 发生于对象上的事情 Excel对象结构对象结构 Application Workbooks Windows Addins Worksheets Charts Cells Range Excel对象结构对象结构 1.Application对象（

17、代表整个Excel应用程序） 2.Workbook（工作簿，即是说Excel文件） 3.Worksheet（ 工作表，即工作簿的“页”） 4.Range（范围对象，指一个或者多个单元格） 使用对象的程序语法 Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value = “ABCDE” 容器容器 Workbooks . Close 容器容器 属性属性 集合对象集合对象 VBA控制结构 ? 分支结构 If 条件条件1 Then 语句块语句块1 ElseIf 条件条件2 Then 语句块语句块2 Else 语句块语句块n+1 End

18、 If Select Case 测试变量测试变量 Case 表达式表达式1 语句块语句块1 Case 表达式表达式2 语句块语句块2 Case Else 语句块语句块n+1 End Select VBA控制结构 ? 循环结构 For 变量变量=初值初值 To 终值终值 Step 步长步长 语句块语句块 Next 变量变量 For Each 对象变量对象变量 In 对象集对象集 语句块语句块 Next 对象变量对象变量 Do While 条件表达式条件表达式 语句块语句块 Loop 示例：对红色字体的数据求和 Function SUMIFRAD (Target As range) Dim sum

19、 As Integer Dim r As range For Each r In Target If r.Font.Color = vbRed Then RGB(255,0,0) sum = sum + r.Value End If Next SUMIFRAD = sum End Function 创建用户窗体 ? 提示使用者选取、输入或浏览数据 ? 使用控件 文本框输入文字 列表框选取内容 选择框选取一个或多个功能 用户窗体窗口的操作 ?建立用户窗体 配置ActiveX控件 设置窗口属性 配置控件属性及其事件 Load与Unload语句 加载到内存或从内存中释放 用Show和Hide方法控制

20、对象的显示和隐藏 VBA帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃 轻松一刻 -excel *控制特定单元格输入文本的长度 单击单击“ 数据数据” 菜单的菜单的“ 有效性有效性” 选项。在选项。在“ 设置设置” 卡片卡片“ 有效性条件有效性条件” 的的“ 允许允许” 下拉菜单中选择下拉菜单中选择“ 文本长度文本长度” 。然后在。然后在“ 数据数据” 下拉菜单中选择下拉菜单中选择“ 等于等于” ， 且且“ 长度长度” 为为“4”。 *给自己的函数建立新菜单 1.在工具栏空白处右击鼠标，选在工具栏空白处右击鼠标，选“ 自定义自定义” 选项，打开选项，打开“ 自定义自定

21、义” 对话框对话框。在。在“ 命令命令” 标签中，选中标签中，选中“ 类别类别” 下的下的“ 新菜单新菜单” 项，再将项，再将“ 命令命令” 下面的下面的“ 新菜单新菜单” 拖到菜单栏。拖到菜单栏。 2.再在再在“ 类别类别” 下面任选一项（如下面任选一项（如“ 插入插入” 选项），在右边选项），在右边“ 命令命令” 下面任下面任选一项（如选一项（如“ 超链接超链接” 选项）选项） 终极突破 什么是什么是VBSVBS？ Vbs是一种Windows脚本，它的全称是:Microsoft Visual Basic Script Editon.( 微软公司可视化BASIC脚本版)， VBS是Visua

22、l Basic的的一个抽象子集，是系统内置的，用它编写的脚本代码不能编译成二进制文件，直接由Windows系统执行（实际是一个叫做宿主host的解释源代码并执行），高效、易学，但是大部分高级语言能干的事情，它基本上都具备，它可以使各种各样的任务自动化，可以使你从重复琐碎的工作中解脱出来，极大的提高工作效率 。 应用领域应用领域 ? Windows操作系统 VBScript可以被用来自动地完成重复性的Windows操作系统任务。在Windows操作系统中，VBScript可以在Windows Script Host 的范围内运行。Windows操作系统可以自动辨认和执行 *.VBS和*.WSF两

23、种文件格式 ? 网页浏览器 ? 网页服务器 Sendkeys函数函数 ? SendkeysSendkeys函数函数 函数名称：Sendkeys 主要功能：将一个或多个按键消息发送到活动窗口，就如同在键盘上进行输入一样。 使用格式：SendKeys string, wait 参数说明：string 必需的。字符串表达式，指定要发送的按键消息。 Wait 可选的。 特别提醒：对 Sendkeys 来说，加号 (+)、插入符 ()、百分比符号 (%)、上划线 () 及圆括号 ( ) 都具有特殊意义。为了指定上述任何一个字符，要将它放在大括号 () 当中。例如，要指定正号，可用 + 表示。为了指定大括

24、号字符，请使用 及 。 Sendkeys函数函数 ? 为了指定那些与 SHIFT、CTRL 及 ALT 等按键结合的组合键，可在这些按键码的前面放置一个或多个代码，这些代码列举如下 ： 按键 代码 SHIFT + CTRL ALT % 特别提醒：为了说明在按下其它按键时应同时按下 SHIFT、CTRL、及 ALT 的任意组合键，请把那些按键的码放在括号当中。例如，为了说明按下 E 与 C 的时候同时按下 SHIFT 键，请使用 +(EC)。为了说明在按下 E 的时候同时按下 SHIFT 键，但接着按 C 而不按 SHIFT，则使用 +EC。 Burn in自动安装程序自动安装程序 Dim WshShell,fs Set WshShell=WScript.CreateObject(WScript.Shell) Set fs=wscript.createobject(scripting.filesystemobject) If(fs.FileExists(C:Program FilesBurnInTestbit.exe)=true) Then Msgbox Please dont reinstall wscript.quit ElseIf (fs.FolderExists(C:Prog