EXCEL-函数-宏-VBA-入门知识.ppt

1、,Excel 函数,宏-VBA,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单元格中输入公式：=CO

3、NCATENATE(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函数 函数名称：SUMIF 主要功能：计算符合

6、指定条件的单元格区域内的数值和。 使用格式：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 主要功能：返回某一数值在一列数

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

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

9、在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

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

11、间，要用英文状态下的逗号“,”隔开。,EXCEL 函数概述,MOD函数 函数名称：MOD 主要功能：求出两数相除的余数。 使用格式：MOD(number,divisor) 参数说明：number代表被除数；divisor代表除数。 应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。 特别提醒：如果divisor参数为零，则显示错误值“#DIV/0!”；MOD函数可以借用函数INT来表示：上述公式可以修改为：=13-4*INT(13/4)。,轻松一刻 -excel,* 在word中分别输入三个“=”，输入三个“”或输入三个“*”或 “-”或 “#”，回车。有惊喜哦 * 如果希望设

12、置国际象棋棋盘式底纹(白色自定义色)话，应该可以的，不妨试试哦,小窍门！,=MOD(ROW()+COLUMN(),2)0， =MOD(ROW()+COLUMN(),2)=0,Excel宏,VBA,挑战第一关,善用宏简化工作,什么是宏,宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。 宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误,开始录制宏,操作步骤,完成宏录制,运行宏,录制宏 需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务,执行宏的操作 执行宏的命令 在工具栏上设置宏按钮 使用窗体控件 使用图形对

13、象执行宏,宏的结构,Sub 主界面() 主界面 Macro Coren Lee 记录的宏 2002-11-5 Sheets(主界面).Select End Sub,宏名,批注,宏程序,挑战第二关,深入浅出VBA,VBA及其特点,什么是VBA ? VBA（Visual Basic for Applications）是Microsoft Office 系列的内置编程语言，即应用程序中的VB。它“寄生于”Office应用程序，是Office 套装软件的一个重要组件。 VBA编程优点 1程序只起辅助作用，大部分功能用Office的，因此简化了程序设计。 2通过宏录制，可以部分地实现程序设计的自动化。

14、3发行方便，只要发行含有VBA代码的文档或模板即可。 4. Office界面对于广大计算机应用人员来说比较熟悉，符合一般操作人员的使用习惯，便于软件推广应用。 5用VBA编程非常简单。,为何要使用 VBA 宏缺少伸缩的弹性 记录了许多不需要的步骤 记录了许多不需要的资料 无法使用较深入的功能 无法完成重复性的工作 无法完成需要条件判断的工作,宏,VBA,Visual Basic for Application,VBA与Visual Basic的差异,Visual Basic以界面为中心的开发方式 重点在使用者如何进行操作 VBA以文件为中心的开发方式 重点在使用者最后看到什么,Visual B

15、asic编辑器,用来编辑宏程序或制作更高阶的整合性应用程序 附属于Office之下，使用较少的系统资源，达到编辑应用程序的目的。,什么是工程,工程就是模块的集合，包括： 用户窗体 文件，例如Excel中的工作表以及图表工作表 模块 类模块 ActiveX文件 引用的其他工程,VBA程序的基本架构,对象 面向对象的开发方式 VBA是以操作Office对象为目的的程序语言 属性 对象所属的特性或特征 方法 控制对象的内建动作或功能，以便可以让程序顺利打开 事件 发生于对象上的事情,Excel对象结构,1.Application对象（代表整个Excel应用程序） 2.Workbook（工作簿，即是说

16、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 End If,Select Case 测试变量 Case 表达式1 语句块1 Case 表达式2 语句块2 Cas

17、e Else 语句块n+1 End Select,VBA控制结构,循环结构,For 变量=初值 To 终值 Step 步长 语句块 Next 变量,For Each 对象变量 In 对象集 语句块Next 对象变量,Do While 条件表达式 语句块 Loop,示例：对红色字体的数据求和,Function SUMIFRAD (Target As range) Dim sum 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 E

18、nd If Next SUMIFRAD = sum End Function,创建用户窗体,提示使用者选取、输入或浏览数据 使用控件 文本框输入文字 列表框选取内容 选择框选取一个或多个功能,用户窗体窗口的操作,建立用户窗体 配置ActiveX控件 设置窗口属性 配置控件属性及其事件 Load与Unload语句 加载到内存或从内存中释放 用Show和Hide方法控制对象的显示和隐藏,DEMO,VBA帮助你完成从必然王国到自由王国的飞跃,轻松一刻 -excel,*控制特定单元格输入文本的长度 *给自己的函数建立新菜单,小窍门！,1.在工具栏空白处右击鼠标，选“自定义”选项，打开“自定义”对话框。

19、在“命令”标签中，选中“类别”下的“新菜单”项，再将“命令”下面的“新菜单”拖到菜单栏。 2.再在“类别”下面任选一项（如“插入”选项），在右边“命令”下面任选一项（如“超链接”选项）,单击“数据”菜单的“有效性”选项。在“设置”卡片“有效性条件”的“允许”下拉菜单中选择“文本长度”。然后在“数据”下拉菜单中选择“等于”，且“长度”为“4”。,终极突破,Vbs脚本编程,什么是VBS？,Vbs是一种Windows脚本，它的全称是:Microsoft Visual Basic Script Editon.(微软公司可视化BASIC脚本版)， VBS是Visual Basic的的一个抽象子集，是系统

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

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

22、LT 等按键结合的组合键，可在这些按键码的前面放置一个或多个代码，这些代码列举如下 ： 按键 代码 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:Program Files (x86)=true) Then WshShell.run bitpro_x64.exe