EXCEL常用函数和代码.docx

EXCEL常用函数和代码函数名 功能 用途示例 ABS 求出参数的绝对值。 数据计算 AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 条件判断 AVERAGE 求出所有参数的算术平均值。 数据计算 COLUMN 显示所引用单元格的列标号值。 显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。 条件统计 DATE 给出指定数值的日期。 显示日期 DATEDIF 计算返回两个日期参数的差值。 计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。 计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。 条件统计 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。 概率计算 IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。 条件计算 INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。 数据定位 INT 将数值向下取整为最接近的整数。 数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。 逻辑判断 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。 截取数据 LEN 统计文本字符串中字符数目。 字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 匹配位置 MAX 求出一组数中的最大值。 数据计算 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。 字符截取 MIN 求出一组数中的最小值。 数据计算 MOD 求出两数相除的余数。 数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。 日期计算 NOW 给出当前系统日期和时间。 显示日期时间 OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。 逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。 数据排序 RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符。 字符截取 SUBTOTAL 返回列表或数据库中的分类汇总。 分类汇总 SUM 求出一组数值的和。 数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。 条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式 数值文本转换 TODAY 给出系统日期 显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。 文本数值转换 VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值 条件定位 WEEKDAY 给出指定日期的对应的星期数。 星期计算 -方法是有的。用VBA解决。工作表保护密码:123 你可以在代码中自己更改工作表中只要单元格不为空程序就会锁定保护单元格右键单击工作表标签，在右键菜单中选择“查看代码”，然后在出现的工作表代码编辑窗口复制粘贴以下代码：Private Sub Worksheet_Change(ByVal Target As Range)Dim rng As RangeIf Target.Count = 1 ThenUnprotect Password:=123Cells.Locked = FalseSet rng = UsedRangeFor i = 1 To rng.Cells.CountIf rng(i) <> "" Thenrng(i).Locked = TrueEnd IfNextProtect Password:=123EnableSelection = xlUnlockedCellsEnd IfEnd Sub另外说明：如果要修改原有数据是要解除工作表保护后才能修改。 右击工作表标签查看代码粘贴下面的代码即可Private Sub Worksheet_Change(ByVal Target As Range)Application.EnableEvents = FalseFor Each c In Target.CellsWith cIf .Row > 3 And .Row < 301 And .Column < 21 Then Cells(2, 3) = NowEnd WithNextApplication.EnableEvents = TrueEnd Sub 认识 Target 对象变量 使用工作表事件Change或SelectionChange时，会自动带进来一个 Target变数。 Target 代表事件作用的储存格或储存格范围，例如 Worksheet_Change事件: 变更了 A1，Target 就是 A1 储存格。 Worksheet_SelectionChange事件: 选取 A1:C10，Target 就是A1:C10 储存格。 Target 变量是 Range (储存格范围) 对象，你可以在事件的程序中以 Target 对象变量来存取作用储存格的属性，例如以 Target.Address 取得作用储存格地址的字符串。 限制工作表事件作用范围的几种写法 有时我们会希望工作表事件只针对特定的栏/列或储存格范围，所以可在事件一开始就进行判断。 几种写法可视需要择一或搭配运用 - If Target.Address <> "$A$1" Then Exit Sub 限制为A1储存格才起作用 If Target.Row > 1 Then Exit Sub 限制为第1列才起作用 If Target.Column > 1 Then Exit Sub 限制为A栏(第1栏)才起作用 If Target.Count > 1 Then Exit Sub 限制为单一储存格才起作用 If Not Target.Address Like "$CGX$*" Then Exit Sub 限制为C,G,X栏才起作用 If Application.Intersect(Target, A1:C10) Is Nothing Then Exit Sub 限制为A1:C10范围内才起作用当使用并触发 Worksheet_Change，程序中指定储存格的值时又再次触发 Worksheet_Change，如此反复造成循环，程序无法结束，造成假性当机的现象。 虽然 Excel 会有循环次数的限制，还是会停下来，但这终究算是一个不良程序。 有时你奇怪为何一个事件没什么内容却执行很久，就要小心这个问题。 为避免这个情形，程序中我们会在指定储存格值之前加上这一句： Application.EnableEvents = False目的是使以下的动作不再触发 Worksheet_Change 事件。 但切记最后再将它复原，以使 Change 或其它事件能再次作用： Application.EnableEvents = TruePublic n As Boolean, s As ByteSub myColor()If n ThenApplication.OnTime Now + TimeValue("00:00:01"), "myColor"Sheet1.Range("A1").Font.ColorIndex = Second(Time) Mod 57s = s + 1If s = 5 ThenCall Sheet1.CommandButton1_Clicks = 0End IfEnd IfEnd Sub安排宏在指定的时间和间隔运行（OnTime方法）可以使用Application对象的OnTime方法在指定的时间或者在有规律的时间间隔运行某过程。OnTime方法的语法如下： Application.OnTime(EarliestTime,Procedure,LastestTime,Schedule)参数EarliestTime指明希望何时运行由参数Procedure指定的过程，可选的参数LastestTime和Schedule指明过程运行的最迟时间，以及是否安排运行一个新过程或者删除已经存在的过程。当开始调用某过程而Excel正忙时，则需要使用参数LastestTime指定希望调用该过程的时间区间。如果使用Application对象的Wait方法暂停某宏，所有的Excel行为，包括手工交互操作，都将被挂起。OnTime方法的优势在于，当等待运行安排的宏时，允许返回正常的Excel交互操作，包括运行其他的宏。下面的示例指定每隔5分钟运行一次名为YourProc的过程：Application.OnTime EarliestTime:=Now + TimeValue("00:05:00"), Procedure:="YourProc"下面的示例在每天中午运行过程YourProc：Application.OnTime EarliestTime:=TimeValue("12:00:00"), Procedure:="YourProc"下面的示例安排每隔5分钟调用一次AutoSave过程。如果关闭该工作簿，则调用CleanUp过程来执行可能希望的清理以及删除任何额外的调用。Private Sub Workbook_Open()Application.OnTime Now + TimeValue("00:05:00"), "AutoSave"End SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)On Error Resume NextApplication.OnTime Now + TimeValue("00:05:00"), "CleanUp", , FalseEnd Sub注意，Workbook_Open事件和Workbook_BeforeClose事件包含在工作簿代码模块中，而AutoSave过程和CleanUp过程则存在于标准代码模块中。当使用OnTime方法安排在将来的某个时间运行宏时，必须确保Excel一直在内存中运行直至到达安排的时间。但不需要一直打开包含OnTime宏的工作簿。如果需要，Excel将打开该工作簿。通过上述简介，我们已经了解了OnTime方法的基本用法。下面再详细介绍OnTime方法。有时，我们可能需要设计Excel工作簿定期并自动地运行一个过程。例如，可能希望每隔几分钟从数据源中更新数据，此时执行Excel应用程序的OnTime方法指令Excel在给定的时间去运行某过程。通过编写代码使程序自已调用OnTime方法，能使VBA代码定期自动执行。OnTime方法要求指定日期和时间以及要运行的过程作为参数，重要的是要记住具体地告诉Excel什么时候运行这个过程而不是从当前时间开始的偏差。为了取消一个未执行的OnTime过程，必须经过该过程计划要运行的确切的时间，不能够告诉Excel取消下一个计划执行的过程。因此，建议将安排过程开始运行的时间存放在一个公共的（或全局）变量中，该变量作用于所有的代码。然后，能够使用所存储时间的变量去安排运行或取消事件。下面的示例代码在公共的常量中存储了所运行过程的名称和重复执行的时间间隔，当然这不是必需的。Public RunWhen As DoublePublic Const cRunIntervalSeconds = 120 two minutesPublic Const cRunWhat = "The_Sub"为开始这个过程，使用一个名为 StartTimer的子程序。代码如下：Sub StartTimer()RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _schedule:=TrueEnd Sub将比当前时间多两分钟的日期和时间存放在RunWhen变量中，然后调用OnTime方法指令Excel何时运行cRunWhat过程。“The_Sub”是一个字符串变量，Excel将在合适的时间运行该过程。下面是该过程代码示例：Sub The_Sub()这里放置代码StartTimerEnd Sub注意，The_Sub过程的最后一行调用了StartTimer过程，再次重复运行这个过程。并且当下次使用OnTime调用The_Sub过程时，将再次调用StartTimer来重复执行它自已。这就是如何执行周期循环的方法。有时，当关闭工作簿时或者满足某个条件时需要停止定时执行的过程。由于OnTime方法是Application对象的一部分，简单地关闭已创建事件的工作簿不会取消对OnTime的调用。一旦Excel自身保持运行，它将执行OnTime过程，并且在必要时会自动打开该工作簿。为了停止OnTime过程，必须对OnTime方法指定确切的时间，这就是我们将时间作为公共的变量存放在RunWhen中的原因。否则，没办法知道过程计划执行的确切时间。（所计划的时间像OnTime方法中的一把“钥匙”，如果没有它，就没有通往事件的入口）下面是一个名为StopTimer的子过程，它将停止要执行的OnTime过程。Sub StopTimer()On Error Resume NextApplication.OnTime earliesttime:=RunWhen, _procedure:=cRunWhat, schedule:=FalseEnd Sub这个过程使用了和StartTimer过程相同的OnTime语法，将schedule参数设置为False告诉Excel取消该过程的执行。可能希望在Auto_Close宏或Workbook_BeforeClose事件中包括一个对该过程的调用。在StopTimer过程中，使用On Error Resume Next语句忽略当你企图删除一个不存在的过程时可能产生的任何错误。下面的示例演示当在单元格B1中输入一个值后，如果A1单元格中不为空，那么将在10秒后自动清除单元格A1和B1中的内容。示例代码如下：在标准模块中输入如下代码：Su