EXCEL_函数_宏_VBA 入门知识

Excel 函数函数 宏宏-VBA-VBA2021/7/11What can you get from here?如何设置EXCEL国际象棋棋盘式底纹什么是VB，VBS，VBA和宏如何对一张表所有红色字体的数据求和Burn-in自动安装脚本究竟有何诀窍2021/7/12CONTENTSEXCEL 函数概述EXCEL 宏-VBA介绍VBS在测试中的应用Q/A2021/7/13EXCEL 函数概述AVERAGE函数函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(number1,number2,)参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），参数不超过30个。应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。2021/7/14EXCEL 函数概述CONCATENATE函数函数 函数名称：CONCATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。使用格式：CONCATENATE(Text1，Text)参数说明：Text1、Text2为需要连接的字符文本或引用的单元格。应用举例：在C14单元格中输入公式：=CONCATENATE(A14,B14,.com)，确认后，即可将A14单元格中字符、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。特别提醒：如果将上述公式改为：=A14&B14&.com，也能达到相同的目的。2021/7/15EXCEL 函数概述COUNTIF函数函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域，Criteria表示指定的条件表达式。应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,=80)，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。特别提醒：允许引用的单元格区域中有空白单元格出现。2021/7/16EXCEL 函数概述DATEDIF函数函数函数名称：DATEDIF 主要功能：计算返回两个日期参数的差值。使用格式：=DATEDIF(date1,date2,“y(m,d)”)参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),y)特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。2021/7/17EXCEL 函数概述IF函数函数函数名称：IF 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：=IF(Logical,Value_if_true,Value_if_false)参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容 应用举例：在C29单元格中输入公式：=IF(C26=18,“符合要求”,“不符合要求”)2021/7/18EXCEL 函数概述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)”特别提醒：其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（男、女）中。2021/7/19EXCEL 函数概述RANK函数函数函数名称：RANK主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：RANK（Number,ref,order）参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。特别提醒：我们让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式（增加了一个“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。2021/7/110EXCEL 函数概述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，不包括隐藏的行。2021/7/111EXCEL 函数概述LEFT函数函数/RIGHT函数函数函数名称：LEFT主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。使用格式：LEFT(text,num_chars)，参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符。特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。2021/7/112EXCEL 函数概述MID函数函数函数名称：MID主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。应用举例：假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中输入公式：=MID(A47,4,3)，确认后即显示出“天极网”的字符。特别提醒：公式中各参数间，要用英文状态下的逗号“,”隔开。2021/7/113EXCEL 函数概述MOD函数函数函数名称：MOD 主要功能：求出两数相除的余数。使用格式：MOD(number,divisor)参数说明：number代表被除数；divisor代表除数。应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。特别提醒：如果divisor参数为零，则显示错误值“#DIV/0!”；MOD函数可以借用函数INT来表示：上述公式可以修改为：=13-4*INT(13/4)。2021/7/114轻松一刻 -excel*在word中分别输入三个“=”，输入三个“”或输入三个“*”或“-”或“#”，回车。有惊喜哦*如果希望设置国际象棋棋盘式底纹(白色自定义色)话，应该可以的，不妨试试哦=MOD(ROW()+COLUMN(),2)0，=MOD(ROW()+COLUMN(),2)=0 2021/7/115ExcelExcel宏宏2021/7/116挑战第一关挑战第一关善用宏简化工作善用宏简化工作2021/7/117什么是宏宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误2021/7/118录制宏录制宏需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务 执行宏的操作执行宏的操作 执行宏的命令执行宏的命令执行宏的命令执行宏的命令 在工具栏上设置宏按钮在工具栏上设置宏按钮在工具栏上设置宏按钮在工具栏上设置宏按钮 使用窗体控件使用窗体控件使用窗体控件使用窗体控件 使用图形对象执行宏使用图形对象执行宏使用图形对象执行宏使用图形对象执行宏 2021/7/119宏的结构Sub主界面主界面()主界面主界面MacroCorenLee记录的宏记录的宏2002-11-5Sheets(主界面主界面).SelectEndSub宏名宏名宏名宏名批注批注批注批注宏程序宏程序宏程序宏程序2021/7/120挑战第二关挑战第二关深入浅出深入浅出VBAVBA2021/7/121VBA及其特点 什么是什么是VBA?VBA（Visual Basic for Applications）是Microsoft Office 系列的内置编程语言，即应用程序中的VB。它“寄生于”Office应用程序，是Office 套装软件的一个重要组件。VBA编程优点编程优点 1程序只起辅助作用，大部分功能用Office的，因此简化了程序设计。2通过宏录制，可以部分地实现程序设计的自动化。3发行方便，只要发行含有VBA代码的文档或模板即可。4.Office界面对于广大计算机应用人员来说比较熟悉，符合一般操作人员的使用习惯，便于软件推广应用。5用VBA编程非常简单。2021/7/122为何要使用 VBA宏缺少伸缩的弹性记录了许多不需要的步骤记录了许多不需要的资料无法使用较深入的功能无法完成重复性的工作无法完成需要条件判断的工作宏宏VBAVBAVisual Basic for Application2021/7/123VBA与Visual Basic的差异Visual Basic以界面为中心的开发方式重点在使用者如何进行操作VBA以文件为中心的开发方式重点在使用者最后看到什么2021/7/124Visual Basic编辑器用来编辑宏程序或制作更高阶的整合性应用程序附属于Office之下，使用较少的系统资源，达到编辑应用程序的目的。2021/7/125什么是工程工程就是模块的集合，包括：用户窗体文件，例如Excel中的工作表以及图表工作表模块类模块ActiveX文件引用的其他工程2021/7/126VBA程序的基本架构对象面向对象的开发方式VBA是以操作Office对象为目的的程序语言属性对象所属的特性或特征方法控制对象的内建动作或功能，以便可以让程序顺利打开事件发生于对象上的事情2021/7/127ExcelExcel对象结构对象结构对象结构对象结构2021/7/1281.Application对象（代表整个Excel应用程序）2.Workbook（工作簿，即是说Excel文件）3.Worksheet（工作表，即工作簿的“页”）4.Range（范围对象，指一个或者多个单元格）Excel对象结构对象结构2021/7/129使用对象的程序语法Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value=“ABCDE”容器容器容器容器属性属性Workbooks.Close集合对象集合对象2021/7/130VBA控制结构分支结构If条件条件1Then语句块语句块1ElseIf条件条件2Then语句块语句块2Else语句块语句块n+1EndIfSelectCase测试变量测试变量Case表达式表达式1语句块语句块1Case表达式表达式2语句块语句块2CaseElse语句块语句块n+1EndSelect2021/7/131VBA控制结构循环结构For变量变量=初值初值To终值终值Step步长步长语句块语句块Next变量变量ForEach对象变量对象变量In对象集对象集语句块语句块Next对象变量对象变量DoWhile条件表达式条件表达式语句块语句块Loop2021/7/132示例：对红色字体的数据求和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 End If Next SUMIFRAD=sumEnd Function2021/7/133创建用户窗体提示使用者选取、输入或浏览数据使用控件文本框输入文字列表框选取内容选择框选取一个或多个功能2021/7/134用户窗体窗口的操作建立用户窗体配置ActiveX控件设置窗口属性配置控件属性及其事件Load与Unload语句加载到内存或从内存中释放用Show和Hide方法控制对象的显示和隐藏2021/7/135VBAVBA帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃2021/7/136轻松一刻 -excel*控制特定单元格输入文本的长度*给自己的函数建立新菜单1.在工具栏空白处右击鼠标，选在工具栏空白处右击鼠标，选“自定义自定义”选项，打开选项，打开“自定义自定义”对话框。对话框。在在“命令命令”标签中，选中标签中，选中“类别类别”下的下的“新菜单新菜单”项，再将项，再将“命令命令”下面的下面的“新菜单新菜单”拖到菜单栏。拖到菜单栏。2.再在再在“类别类别”下面任选一项（如下面任选一项（如“插入插入”选项），在右边选项），在右边“命令命令”下面任下面任选一项（如选一项（如“超链接超链接”选项）选项）单击单击“数据数据”菜单的菜单的“有效性有效性”选项。在选项。在“设置设置”卡片卡片“有效性条件有效性条件”的的“允许允许”下拉菜单中选择下拉菜单中选择“文本长度文本长度”。然后在。然后在“数据数据”下拉菜单中选择下拉菜单中选择“等于等于”，且且“长度长度”为为“4”。2021/7/137终极突破Vbs脚本编程2021/7/138什么是什么是VBSVBS？Vbs是一种Windows脚本，它的全称是:Microsoft Visual Basic Script Editon.(微软公司可视化BASIC脚本版)，VBS是Visual Basic的的一个抽象子集，是系统内置的，用它编写的脚本代码不能编译成二进制文件，直接由Windows系统执行（实际是一个叫做宿主host的解释源代码并执行），高效、易学，但是大部分高级语言能干的事情，它基本上都具备，它可以使各种各样的任务自动化，可以使你从重复琐碎的工作中解脱出来，极大的提高工作效率。2021/7/139应用领域应用领域Windows操作系统 VBScript可以被用来自动地完成重复性的Windows操作系统任务。在Windows操作系统中，VBScript可以在Windows Script Host的范围内运行。Windows操作系统可以自动辨认和执行*.VBS和*.WSF两种文件格式网页浏览器网页服务器2021/7/140Sendkeys函数函数SendkeysSendkeys函函数数 函数名称：Sendkeys主要功能：将一个或多个按键消息发送到活动窗口，就如同在键盘上进行输入一样。使用格式：SendKeys string,wait参数说明：string 必需的。字符串表达式，指定要发送的按键消息。Wait 可选的。特别提醒：对 Sendkeys 来说，加号(+)、插入符()、百分比符号(%)、上划线()及圆括号()都具有特殊意义。为了指定上述任何一个字符，要将它放在大括号()当中。例如，要指定正号，可用+表示。为了指定大括号字符，请使用 及。2021/7/141Sendkeys函数函数为了指定那些与 SHIFT、CTRL 及 ALT 等按键结合的组合键，可在这些按键码的前面放置一个或多个代码，这些代码列举如下：按键 代码 SHIFT +CTRL ALT%特别提醒：为了说明在按下其它按键时应同时按下 SHIFT、CTRL、及 ALT 的任意组合键，请把那些按键的码放在括号当中。例如，为了说明按下 E 与 C 的时候同时按下 SHIFT 键，请使用+(EC)。为了说明在按下 E 的时候同时按下 SHIFT 键，但接着按 C 而不按 SHIFT，则使用+EC。2021/7/142Burn in自动安装程序自动安装程序Dim WshShell,fsSet WshShell=WScript.CreateObject(WScript.Shell)Set fs=wscript.createobject(scripting.filesystemobject)If(fs.FileExists(C:Program FilesBurnInTestbit.exe)=true)ThenMsgbox Please dont reinstallwscript.quitElseIf(fs.FolderExists(C:Program Files(x86)=true)ThenWshShell.run bitpro_x64.exeElseWshShell.Run bitpro.exeEnd If2021/7/143Burn in自动安装程序自动安装程序WScript.Sleep 2000WshShell.AppActivate Setup-BurnInTestWshshell.SendKeys%nWScript.Sleep 1000Wshshell.SendKeys%aWScript.Sleep 1000Wshshell.SendKeys%nWScript.Sleep 1500Wshshell.SendKeys%nWScript.Sleep 1500Wshshell.SendKeys%nWScript.Sleep 1000Wshshell.SendKeys%iWScript.Sleep 20002021/7/144定时关机程序定时关机程序代码开始set ws=CreateObject(wscript.shell)获得这个对象的句柄，用于下面的操作do设置循环，不停监视时间if abs(DateDiff(s,CDate(11:45:00),time)60 then判断时间是不是在要求范围内，即与11点45分接近exit do在时间范围内就退出循环end ifwscript.sleep 1000*10暂停脚本执行以节省系统cpu资源loop循环结束ws.run shutdown-s-t 600 c”系统将在计时结束关闭，请注意保存需要的文件,0调用shutdown命令关机代码结束2021/7/145利用EXCEL常用函数来简化工作了解VB，VBS，VBA和宏是什么关系利用VBA来创建基本函数和宏利用VBS来处理简单重复性步骤what should we get now?2021/7/146Thanks!HWQA-KYLE 2021/7/147 结结束束语语若有不当之处，请指正，谢谢！若有不当之处，请指正，谢谢！