Excel使用技巧集萃.doc

Excel使用技巧集萃目录 20个函数的作用方法21.DATE22.DATEVaLUE33.DAY34.DAYS36045.EDATE56.EOMONTH57.HOUR68.MINUTE69.MONTH710.NETWORKDAYS811.NOW812.SECOND913.TIME914.TIMEVaLUE1015.TODAY1016.WEEKDAY1017.WEEKNUM1118.WORKDAY1119.YEAR1220.YEARFRAC13Excel中用VBA宏自动统计成绩13用格式和函数实现Excel表格自动隔行着色25编写高效Excel VBA代码的最佳实践27编写高效Excel VBA代码的最佳实践34工作簿设计34尽量简化代码34强制声明变量37尽可能使用早期绑定40使用For EachNext循环41使用WithEnd With语句46尽量减少OLE引用47尽可能少使用“.”，使用对象变量48尽可能加速对数字的运算53提高字符串操作的性能53使用Len()检验空串54有效地使用数组55使用Excel的内置函数55不要认为工作表函数总是更快的58只要有可能就使用集合索引值60使用完全受限制的对象引用60结语6420个函数的作用方法1.DATE用途：返回代表特定日期的序列号。语法：DATE(year，month，day)参数：year为一到四位，根据使用的日期系统解释该参数。默认情况下，Excel for Windows使用1900日期系统，而Excel for Macintosh使用1904日期系统。Month代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份执行加法运算。Day代表在该月份中第几天的数字。如果day大于该月份的最大天数时，将从指定月份的第一天开始往上累加。注意:Excel按顺序的序列号保存日期，这样就可以对其进行计算。如果工作簿使用的是1900日期系统，则Excel会将1900年1月1日保存为序列号1。同理，会将1998年1月1日保存为序列号35796，因为该日期距离1900年1月1日为35795天。实例：如果采用1900日期系统(Excel默认)，则公式“=DATE(2001，1，1)”返回36892。2.DATEVaLUE用途：返回date_text所表示的日期的序列号。该函数的主要用途是将文字表示的日期转换成一个序列号。语法：DATEVaLUE(date_text)参数：Date_text是用Excel日期格式表示日期的文本。在使用1900日期系统中，date_text必须是1900年1月1日到9999年12月31日之间的一个日期;而在1904日期系统中，date_text必须是1904年1月1日到9999年12月31日之间的一个日期。如果date_text超出上述范围，则函数DATEVaLUE返回错误值#value!。如果省略参数date_text中的年代，则函数DATEVaLUE使用电脑系统内部时钟的当前年代，且date_text中的时间信息将被忽略。实例：公式“=DATEVaLUE(2001/3/5)”返回36955，DATEVaLUE(2-26)返回36948。3.DAY用途：返回用序列号(整数1到31)表示的某日期的天数，用整数1到31表示。语法：DAY(serial_number)参数：Serial_number是要查找的天数日期，它有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如1900日期系统的35825表示的1998年1月30日)，以及其他公式或函数的结果(如DATEVaLUE(1998/1/30)。实例：公式“=DAY(2001/1/27)”返回27，=DAY(35825)返回30，=DAY(DATEVaLUE(2001/1/25)返回25。4.DAYS360用途：按照一年360天的算法(每个月30天，一年共计12个月)，返回两日期间相差的天数。语法：DAYS360(start_date，end_date，method)参数：Start_date和end_date是用于计算期间天数的起止日期。如果start_date在end_date之后，则DAYS360将返回一个负数。日期可以有多种输入方式:带引号的文本串(例如:1998/01/30)、序列号(例如:如果使用1900日期系统，则35825表示1998年1月30日)或其他公式或函数的结果(例如，DATEVaLUE(1998/1/30)。Method是一个逻辑值，它指定了在计算中是采用欧洲方法还是美国方法。若为FALSE或忽略，则采用美国方法(如果起始日期是一个月的31日，则等于同月的30日。如果终止日期是一个月的31日，并且起始日期早于30日，则终止日期等于下一个月的1日，否则，终止日期等于本月的30日)。若为TRUE则采用欧洲方法(无论是起始日期还是终止日期为一个月的31号，都将等于本月的30号)。实例：公式“=DAYS360(1998/2/1，2001/2-1)”返回1080。5.EDATE用途：返回指定日期(start_date)之前或之后指定月份的日期序列号。语法：EDATE(start_date，months)参数：Start_date参数代表开始日期，它有多种输入方式:带引号的文本串(例如:1998/01/30)、序列号(如35825表示1998年1月30日)或其他公式或函数的结果(例如:DATEVaLUE(1998/1/30)。Months为在start_date之前或之后的月份数，未来日期用正数表示，过去日期用负数表示。实例：公式“=EDATE(2001/3/5，2)”返回37016即2001年5月5日，=EDATE(2001/3/5，-6)返回36774即2000年9月5日。6.EOMONTH用途：返回start-date之前或之后指定月份中最后一天的序列号。语法：EOMONTH(start_date，months)参数：Start_date参数代表开始日期，它有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如1900日期系统中的35825)或其他公式或函数的结果(如DATEVaLUE(1998/1/30)。Month为start_date之前或之后的月份数，正数表示未来日期，负数表示过去日期。实例：公式“=EOMONTH(2001/01/01，2)”返回36981即2001年3月31日，=EOMONTH(2001/01/01，-6)返回36738即2000年7月31日。7.HOUR用途：返回时间值的小时数。即介于0(12:00 A.M.)到23(11:00 P.M.)之间的一个整数。语法：HOUR(serial_number)参数：Serial_number表示一个时间值，其中包含着要返回的小时数。它有多种输入方式:带引号的文本串(如6:45 PM)、十进制数(如0.78125表示6:45PM)或其他公式或函数的结果(如TIMEVaLUE(6:45 PM)。实例：公式“=HOUR(3:30:30 PM)”返回15，=HOUR(0.5)返回12即12:00:00 AM，=HOUR(29747.7)返回16。8.MINUTE用途：返回时间值中的分钟，即介于0到59之间的一个整数。语法：MINUTE(serial_number)参数：Serial_number是一个时间值，其中包含着要查找的分钟数。时间有多种输入方式:带引号的文本串(如6:45 PM)、十进制数(如0.78125表示6:45 PM)或其他公式或函数的结果(如TIMEVaLUE(6:45 PM)。实例：公式“=MINUTE(15:30:00)”返回30，=MINUTE(0.06)返回26，=MINUTE(TIMEVaLUE(9:45 PM)返回45。9.MONTH用途：返回以序列号表示的日期中的月份，它是介于1(一月)和12(十二月)之间的整数。语法：MONTH(serial_number)参数：Serial_number表示一个日期值，其中包含着要查找的月份。日期有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如表示1998年1月30日的35825)或其他公式或函数的结果(如DATEVaLUE(1998/1/30)等。实例：公式“=MONTH(2001/02/24)”返回2，=MONTH(35825)返回1，=MONTH(DATEVaLUE(2000/6/30)返回6。10.NETWORKDAYS用途：返回参数start-data和end-data之间完整的工作日(不包括周末和专门指定的假期)数值。语法：NETWORKDAYS(start_date，end_date，holidays)参数：Start_date代表开始日期，End_date代表终止日;Holidays是表示不在工作日历中的一个或多个日期所构成的可选区域，法定假日以及其他非法定假日。此数据清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。函数中的日期有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如使用1900日期系统的35825)或其他公式或函数的结果(如DATEVaLUE(1998/1/30)。注意:该函数只有加载“分析工具库”以后方能使用。11.NOW用途：返回当前日期和时间所对应的序列号。语法：NOW()参数：无实例：如果正在使用的是1900日期系统，而且计算机的内部时钟为2001-1-28 12:53，则公式“=NOW()”返回36919.54。12.SECOND用途：返回时间值的秒数(为0至59之间的一个整数)。语法：SECOND(serial_number)参数：Serial_number表示一个时间值，其中包含要查找的秒数。关于时间的输入方式见上文的有关内容。实例：公式“=SECOND(3:30:26 PM)”返回26，=SECOND(0.016)返回2。13.TIME用途：返回某一特定时间的小数值，它返回的小数值从0到0.99999999之间，代表0:00:00(12:00:00 A.M)到23:59:59(11:59:59 P.M)之间的时间。语法：TIME(hour，minute，second)参数：Hour是0到23之间的数，代表小时;Minute是0到59之间的数，代表分;Second是0到59之间的数，代表秒。实例：公式“=TIME(12，10，30)”返回序列号0.51，等价于12:10:30 PM。=TIME(9，30，10)返回序列号0.40，等价于9:30:10 AM。=TEXT(TIME(23，18，14)，h:mm:ss AM/PM)返回“11:18:14 PM”。14.TIMEVaLUE用途：返回用文本串表示的时间小数值。该小数值为从0到0.999999999的数值，代表从0:00:00(12:00:00 AM)到23:59:59(11:59:59 PM)之间的时间。语法：TIMEVaLUE(time_text)参数：Time_text是一个用Excel时间格式表示时间的文本串(如6:45 PM和18:45等)。实例：公式“=TIMEVaLUE(3:30 AM)”返回0.145833333，=TIMEVaLUE(2001/1/26 6:35 AM)返回0.274305556。15.TODAY用途：返回系统当前日期的序列号。参数：无语法：TODAY()实例：公式“=TODAY()”返回2001-8-28(执行公式时的系统时间)。16.WEEKDAY用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。语法：WEEKDAY(serial_number，return_type)参数：Serial_number是要返回日期数的日期，它有多种输入方式:带引号的文本串(如2001/02/26)、序列号(如35825表示1998年1月30日)或其他公式或函数的结果(如DATEVaLUE(2000/1/30)。Return_type为确定返回值类型的数字，数字1或省略则1至7代表星期天到数星期六，数字2则1至7代表星期一到星期天，数字3则0至6代表星期一到星期天。实例：公式“=WEEKDAY(2001/8/28，2)”返回2(星期二)，=WEEKDAY(2003/02/23，3)返回6(星期日)。17.WEEKNUM用途：返回一个数字，该数字代表一年中的第几周。语法：WEEKNUM(serial_num，return_type)参数：Serial_num代表一周中的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。Return_type为一数字，确定星期计算从哪一天开始。默认值为1。18.WORKDAY用途：返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和专门指定的假日)的某一日期的值，并扣除周末或假日。语法：WORKDAY(start_date，days，holidays)参数：Start_date为开始日期;Days为Start_date之前或之后不含周末及节假日的天数;Days是正值将产生未来日期、负值产生过去日期;Holidays为可选的数据清单，表示需要从工作日历中排除的日期值(如法定假日或非法定假日)。此清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。日期有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如1900日期系统时的35825表示1998年1月30日)或其他公式或函数的结果(例如DATEVaLUE(1998/1/30)。19.YEAR用途：返回某日期的年份。其结果为1900到9999之间的一个整数。语法：YEAR(serial_number)参数：Serial_number是一个日期值，其中包含要查找的年份。日期有多种输入方式:带引号的文本串(例如1998/01/30)、序列号(例如，如果使用1900日期系统则35825表示1998年1月30日)或其他公式或函数的结果(例如DATEVaLUE(1998/1/30)。实例：公式“=YEAR(2000/8/6)返回2000”，=YEAR(2003/05/01)返回2003，=YEAR(35825)返回1998。20.YEARFRAC用途：返回start_date和end_date之间的天数占全年天数的百分比。语法：YEARFRAC(start_date，end_date，basis)参数：Start_date表示开始日期，End_date代表结束日期。函数中的日期有多种输入方式:带引号的文本串(如1998/01/30)、序列号(如35829表示1900日期系统中的1998年1月30日)，或其他公式或函数的结果(例如DATEVaLUE(1998/1/30)。Basis表示日计数基准类型，其中0或省略为US(NASD)30/360，1实际天数/实际天数，2实际天数/360，3实际天数/365，4欧洲30/360。实例：公式“=YEARFRAC(2001/01/31，2001/06/30，0)”返回0.416666667，YEARFRAC(2001/01/25，2001/09/27)返回0.67222。Excel中用VBA宏自动统计成绩在学校的教学活动中，通过统计分析学生的考试成绩，了解教学情况，为改进以后的教学工作提供依据，是整个教学工作中非常重要一环。但经常按各种不同的指标统计分析成绩，又是一件很枯燥，很繁琐的事。哪么，能否对需统计的各种指标，无需人工干预，让计算机自动完成呢？答案是肯定的，笔者通过Excel中自带的Vba，实现了这个设想。为能给被统计成绩困扰的朋友提供一些帮助，下面以我校高中三年级理科成绩统计为例，介绍其作法，供大家参考。1 设计流程,编制程序源代码：为便于说明做法，同时代码简短一点，这里以我校高三理科1-4班成绩统计为例，说明其做法。另外为使编写代码效率高一些，这里采用先录制一部分为宏，再在vba中打开编辑相结合的办法编写。 从外部的“考试成绩.XLS”工作簿中将1-4班的成绩拷贝，依次粘贴到“成绩统计表.XLS”工作簿中的“理科”表中；程序源代码：Sub 拷外部理科0()ActiveWindow.WindowState = xlMinimizedWindows(考试成绩.xls).ActivateActiveWindow.WindowState = xlMaximizedSheets(1班).SelectRange(A1:S60).SelectSelection.CopyActiveWindow.WindowState = xlMinimizedWindows(成绩统计表.xls).ActivateActiveWindow.WindowState = xlMaximizedSheets(理科).SelectActiveWindow.ScrollRow = 1Range(A1).SelectActiveSheet.Paste拷贝“考试成绩簿”中的1班成绩表中数据，粘贴到到成绩统计表工作簿的理科表A1Range(S22).SelectActiveWindow.LargeScroll Down:=1Range(S44).SelectActiveWindow.LargeScroll Down:=1Range(S66).SelectWindows(考试成绩.xls).ActivateActiveWindow.WindowState = xlNormalSheets(2班).SelectRange(A2).SelectActiveWindow.SmallScroll Down:=43Range(A2:S58).SelectApplication.CutCopyMode = FalseSelection.Copy ActiveWindow.WindowState = xlNormalRange(A61).SelectActiveSheet.Paste拷贝考试成绩簿中的2班成绩表中数据，粘贴到“成绩统计”工作簿的理科表A61Windows(考试成绩.xls).ActivateActiveWindow.WindowState = xlNormalSheets(3班).SelectRange(A1:S59).SelectApplication.CutCopyMode = FalseSelection.CopyWindows(成绩统计表.xls).ActivateActiveWindow.WindowState = xlNormalRange(S58).SelectActiveWindow.LargeScroll Down:=1Range(S80).SelectActiveWindow.LargeScroll Down:=1Range(S102).SelectActiveWindow.LargeScroll Down:=1Range(A118).SelectActiveSheet.Paste拷贝成绩簿中的3班成绩表中数据，粘贴到成绩统计表工作簿的理科表A118Rows(118:118).SelectApplication.CutCopyMode = FalseSelection.Delete Shift:=xlUpRange(D122).SelectWindows(考试成绩.xls).ActivateActiveWindow.WindowState = xlNormalSheets(4班).SelectRange(A2:S71).SelectSelection.CopyWindows(成绩统计表.xls).ActivateActiveWindow.WindowState = xlNormalActiveWindow.LargeScroll Down:=1Range(D144).SelectActiveWindow.LargeScroll Down:=1Range(D166).SelectActiveWindow.LargeScroll Down:=1Range(D188).SelectActiveWindow.LargeScroll Down:=-1Range(A176).Select ActiveSheet.Paste拷贝四班成绩End Sub 利用Excel的统计函数“RANK”将理科表中学生的各科成绩排名次；目的是让每位学生了解自己在全年级学生中的排名的位置。程序代码如下：Sub 理名次()Range(D2).SelectActiveCell.FormulaR1C1 = =RANK(RC-1,R2C3:R246C3,0)Selection.AutoFill Destination:=Range(D2:D246), Type:=xlFillDefaultRange(D2:D246).SelectActiveWindow.LargeScroll Down:=-13ActiveWindow.ScrollRow = 1 在工作表的D2单元格输入公式，并用拖的方式在D2-D246列复制Selection.CopyRange(F2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=$c$2:$c$246, Replacement:=$e$2:$e$246, LookAt _:=xlPart, SearchOrder:=xlByRows, MatchCase:=False拷贝D列粘贴到F列F2Selection.CopyRange(H2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=e, Replacement:=g, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False拷贝D列粘贴到H列H2Selection.CopyRange(J2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=g, Replacement:=i, LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=False拷贝D列粘贴到J列J2Selection.CopyRange(L2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=i, Replacement:=k, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False拷贝D列粘贴到L列L2Selection.CopyRange(N2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=k, Replacement:=m, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseColumns(N:N).ColumnWidth = 5.63Columns(N:N).ColumnWidth = 5Range(N2).SelectColumns(M:M).ColumnWidth = 5.13Range(N2).SelectActiveCell.FormulaR1C1 = =RANm(RC-1,R2C13:R246C13,0)Columns(N:N).SelectSelection.Replace What:=runm, Replacement:=runk, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseRange(N2:N246).SelectSelection.Replace What:=ranm, Replacement:=rank, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseSelection.CopyRange(P2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=m, Replacement:=o, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseSelection.CopyRange(R2).SelectActiveSheet.PasteApplication.CutCopyMode = FalseSelection.Replace What:=o, Replacement:=q, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseRange(G4).Select分别选中F,H,J,.列，用查找替换的方式,将原来的C列范围”$C$2:$C$246”分别替换为对应的“”$F$2:$F$246”, ”$H$2:$H$246”,范围End Sub 将全年级理科的成绩拷贝，粘贴到新表“降序”表中，并对各科成绩按降序排序,确定分析中的A,B等优秀分数线；程序源代码如下Sub 理降幂()Sheets(理科).SelectCells.SelectSelection.CopySheets(理降幂).SelectRange(A1).SelectSelection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _False, Transpose:=FalseColumns(C:C).SelectApplication.CutCopyMode = FalseSelection.Sort Key1:=Range(C1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin将C列成绩降幂排列Columns(E:E).SelectSelection.Sort Key1:=Range(E1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin将E列成绩降幂排列Columns(G:G).SelectSelection.Sort Key1:=Range(G1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 将G列成绩降幂排列Columns(I:I).SelectSelection.Sort Key1:=Range(I1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 将I列成绩降幂排列Columns(K:K).SelectSelection.Sort Key1:=Range(K1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 将K列成绩降幂排列Columns(M:M).SelectSelection.Sort Key1:=Range(M1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin将M列成绩降幂排列Columns(O:O).SelectSelection.Sort Key1:=Range(O1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin将O列成绩降幂排列Columns(Q:Q).SelectSelection.Sort Key1:=Range(Q1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin将Q列成绩降幂排列End Sub 建立分析表，列出各班统计的各种参数指标值。在前面的成绩统计工作簿中建立如下分语文，数学，英语，综合，总分等项目的“成绩分析”表其中的A、B优生分数线的确定，类同于高考录取线的确定。这里将各科前1-80名定为A等，第81-160名定为B等，其具体分数值，用Excel自带的函数:MIN计算，如语文的A等优秀分数线的公式为：=MIN(理降幂！C2:C200),即从理科降序表的“语文字段”第C列的第2到第200个纪录中，选取其中的最小者作为语文A等最低优秀分数线，其余各科分数线的确定，公式基本形同，此处不再详述。各班达到各种优秀分数线学生个数，利用Excel的统计函数COUNTIF计算，如语文科1班A等优生个数公式如下：=COUNTIF(理科!$C$2:$C$60,=102)；这里的COUNTIF是条件纪录数值项数的函数，其中的“理科!”表示成绩统计工作簿中的“理科” 表，$C$2:$C$60表示理科1班的语文数据范围，“=102”表示数据满足的条件。求和就直接用Excel的求和函数计算，如语文科1班上优秀分数线的总人数合计公式为： =SUM(C6:D6) 求平均分和求总分用Excel的求平均函数与求和函数计算，如1班语文总分公式为：=SUM(理科！$C$2:$C$60)，语文平均分公式为：=AVERAGE(理科!C2:C60)。将上述表中的每个单元格内的公式输入完毕，你的成绩自动统计表就做好了。保存好你的工作成绩统计薄，只要在每次统计成绩时，同时打开考试成绩原始数据表和你的成绩统计工作簿，按次序1、选定成绩统计工作簿中的理科表的A1单元格，运行宏拷外部理科，从外部将数据拷到“理科表”；2、运行宏理科名次，排出理科表中每一位同学的各科成绩的名次；3、选定理科降序表的A1单元格，运行宏理降幂，将“理科表”数据拷到“理降幂”表中，并将每科成绩按列从高到低的排列；这时，成绩分析表中的数据就会按表内的公式，将各班成绩的各种指标一一统计出来。另外，如果第二次考试各班的人数有变化，可以通过选中成绩分析中该班所在行，用查找替换的方式变更；第二次考试A,B等优秀分数线，也可以通过选中该列，用查找替换的方式改变。如果班级有增加或减少，可以在Vba中，通过增删部分代码来适应你的班级个数。用格式和函数实现Excel表格自动隔行着色我们在Excel中浏览一个非常大的工作簿中的数据时，有时会出现看错行的现象发生。如果能隔行填充上不同的颜色，这种现象就应该不会发生了。我们利用条件格式和函数来实现这一需求。1、启动Excel，打开相应的工作簿文档。2、选中数据区域(或用Ctrl+A组合键选中整个工作表)。3、执行“格式条件格式”命令，打开“条件格式”对话框(如图1)。图1 条件格式对话框4、单击“条件1”下面左侧方框右边的下拉按钮，在随后出现的下拉列表中，选择“公式”选项，并在右侧的方框中输入公式：=MOD(ROW(),2)=0。5、单击其中的“格式”按钮，打开“单元格格式”对话框(如图2)。图2 单元格格式6、切换到“图案”标签下，选中一种颜色，确定返回到“条件格式”对话框，按下“确定”按钮退出。7、隔行填充不同颜色的效果得以实现(如图3)。图3 实现的效果编写高效Excel VBA代码的最佳实践很多Excel VBA文章和图书都介绍过如何优化VBA代码，使代码运行得更快。下面搜集了一些使Excel VBA代码运行更快的技术和技巧，基本上都是实践经验的总结。如果您还有其它优化Excel VBA代码的方法，可以在本文后留言或给出链接，与大家分享。对于应用程序属性，在代码运行时关闭除必需属性以外的其它所有属性在代码运行时关闭不需要的Excel功能。其原因是，如果通过VBA更新不同的单元格区域，或者从不同的单元格区域复制 /粘贴来创建汇总表，则不希望Excel浪费时间和资源来重新计算公式、显示粘贴进度或者重绘网格，尤其在每次单独的操作后（更有甚者，如果代码使用了循环，则每次单独操作后Excel都会在后台运行这些功能）。只需要在代码执行结束时进行一次重新计算和重绘就足以使工作簿更新。下面的代码将帮助您提高代码的执行速度。（1）放置在主代码前的一段代码，获取Excel当前的属性状态，然后将其关闭获得当前的Excel设置状态,将其放置在代码的开头screenUpdateState = Application.ScreenUpdatingstatusBarState = Application.DisplayStatusBarcalcState = Application.CalculationeventsState = Application.EnableEventsdisplayPageBreakState = ActiveSheet.DisplayPageBreaks 注:这是工作表级的设置关闭一些Excel功能使代码运行更快Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = FalseActiveSheet.DisplayPageBreaks = False 注:这是工作表级的设置（2）放置在主代码结束后的一段代码，用来将Excel恢复到代码运行前的设置代码运行后,恢复Excel原来的状态;将下面的代码放在代码的末尾Application.ScreenUpdating = screenUpdateStateApplication.DisplayStatusBar = statusBarStateApplication.Calculation = calcStateApplication.EnableEvents = eventsStateActiveSheet.DisplayPageBreaks = displayPageBreaksState 注:这是工作表级的设置下面简要解释这些设置：Application.ScreenUpdating：将该属性设置为False，告诉Excel不要重绘屏幕。其优点是不需要Excel花费资源来绘制屏幕，因而其改变会更快而不致让用户察觉其变化。因为如此频繁地绘制屏幕需要大量的资源，所以关闭绘制屏幕直到代码执行结束。在代码结束前，确保重新开启了该属性。Application.DisplayStatusBar：将该属性设置为False，告诉Excel停止显示状态栏。例如，如果使用VBA复制/粘贴单元格，当粘贴执行时Excel将在状态栏中显示操作的进度。关闭屏幕更新不会关闭状态栏显示，因此，如果需要的话，可以禁用屏幕更新而仍然可以通过状态栏给用户提供反馈。记住，如果将该属性设置为False，在代码结束前应该将其设置为True。Application.Calculation：该属性允许编程设置Excel的计算模式。“手工的”（xlCalculationManual）模式意味着Excel等待用户（或代码）来触发计算；默认为“自动的”（xlCalculationAutomatic）模式，意味着由Excel来决定何时重新计算工作簿（例如，当在工作表中输入新公式时）。由于重新计算工作簿将花费时间且浪费资源，因此可能不希望每次改变单元格值时Excel都触发重新计算。当代码执行时关闭重新计算，在代码结束前再设置回重新计算模式。Application.EnableEvents：将该属性设置为False，告诉Excel不要触发事件。你可能不希望Excel为每个正在通过代码发生改变的单元格触发事件，关闭事件将加速VBA代码的执行。ActiveSheet.DisplayPageBreaks：当在较新版本的Excel中运行VBA时，则可能比在早期版本的Excel中需要更长的时间完成。例如，需要几秒钟在早期版本的Excel中完成的宏可能需要几分钟才能在更高版本的Excel中完成。或者，第二次运行一个宏可能比第一次运行需要的时间更长。这是由于VBA宏修改了多行或列的属性，或者必须强制执行计算Excel分页符。如果宏设置了任何PageSetup属性或者手动设置了PageSetup属性，接着运行较大区域的行或列属性设置时会出现这样的问题。您可以将该属性设置为False来提高代码的运行速度。当然，在代码运行结束前，应将该属性恢复为原设置。在单个操作中读/写大块的单元格区域本技巧用于优化在Excel和代码之间转换数据的次数。使用数组变量存储所需要的值并执行取值或赋值操作，而不是一次遍历单个单元格并获取或设置单个值。例如，下面的代码在单元格区域A1：C10000中放置随机数。代码段一：运行速度较慢的代码Sub testSlow()Dim DataRange As RangeDim Irow As LongDim Icol As IntegerDim MyVar As DoubleSet DataRange = Range(A1:C10000) For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow, Icol) 从Excel单元格中读取值30K次If MyVar 0 ThenMyVar = MyVar * MyVar 改变值DataRange(Irow, Icol) = MyVar 将值写入Excel单元格中30000次