Excel使用技巧集萃.doc

Excel使用技巧集萃目錄 20個(gè)函數(shù)的作用方法21.DATE22.DATEVaLUE33.DAY34.DAYS36045.EDATE56.EOMONTH57.HOUR68.MINUTE69.MONTH710.NETWORKDAYS811.NOW812.SECOND913.TIME914.TIMEVaLUE1015.TODAY1016.WEEKDAY1017.WEEKNUM1118.WORKDAY1119.YEAR1220.YEARFRAC13Excel中用VBA宏自動(dòng)統(tǒng)計(jì)成績(jī)13用格式和函數(shù)實(shí)現(xiàn)Excel表格自動(dòng)隔行著色25編寫高效Excel VBA代碼的最佳實(shí)踐27編寫高效Excel VBA代碼的最佳實(shí)踐34工作簿設(shè)計(jì)34盡量簡(jiǎn)化代碼34強(qiáng)制聲明變量37盡可能使用早期綁定40使用For EachNext循環(huán)41使用WithEnd With語(yǔ)句46盡量減少OLE引用47盡可能少使用“.”，使用對(duì)象變量48盡可能加速對(duì)數(shù)字的運(yùn)算53提高字符串操作的性能53使用Len()檢驗(yàn)空串54有效地使用數(shù)組55使用Excel的內(nèi)置函數(shù)55不要認(rèn)為工作表函數(shù)總是更快的58只要有可能就使用集合索引值60使用完全受限制的對(duì)象引用60結(jié)語(yǔ)6420個(gè)函數(shù)的作用方法1.DATE用途：返回代表特定日期的序列號(hào)。語(yǔ)法：DATE(year，month，day)參數(shù)：year為一到四位，根據(jù)使用的日期系統(tǒng)解釋該參數(shù)。默認(rèn)情況下，Excel for Windows使用1900日期系統(tǒng)，而Excel for Macintosh使用1904日期系統(tǒng)。Month代表每年中月份的數(shù)字。如果所輸入的月份大于12，將從指定年份的一月份執(zhí)行加法運(yùn)算。Day代表在該月份中第幾天的數(shù)字。如果day大于該月份的最大天數(shù)時(shí)，將從指定月份的第一天開始往上累加。注意:Excel按順序的序列號(hào)保存日期，這樣就可以對(duì)其進(jìn)行計(jì)算。如果工作簿使用的是1900日期系統(tǒng)，則Excel會(huì)將1900年1月1日保存為序列號(hào)1。同理，會(huì)將1998年1月1日保存為序列號(hào)35796，因?yàn)樵撊掌诰嚯x1900年1月1日為35795天。實(shí)例：如果采用1900日期系統(tǒng)(Excel默認(rèn))，則公式“=DATE(2001，1，1)”返回36892。2.DATEVaLUE用途：返回date_text所表示的日期的序列號(hào)。該函數(shù)的主要用途是將文字表示的日期轉(zhuǎn)換成一個(gè)序列號(hào)。語(yǔ)法：DATEVaLUE(date_text)參數(shù)：Date_text是用Excel日期格式表示日期的文本。在使用1900日期系統(tǒng)中，date_text必須是1900年1月1日到9999年12月31日之間的一個(gè)日期;而在1904日期系統(tǒng)中，date_text必須是1904年1月1日到9999年12月31日之間的一個(gè)日期。如果date_text超出上述范圍，則函數(shù)DATEVaLUE返回錯(cuò)誤值#value!。如果省略參數(shù)date_text中的年代，則函數(shù)DATEVaLUE使用電腦系統(tǒng)內(nèi)部時(shí)鐘的當(dāng)前年代，且date_text中的時(shí)間信息將被忽略。實(shí)例：公式“=DATEVaLUE(2001/3/5)”返回36955，DATEVaLUE(2-26)返回36948。3.DAY用途：返回用序列號(hào)(整數(shù)1到31)表示的某日期的天數(shù)，用整數(shù)1到31表示。語(yǔ)法：DAY(serial_number)參數(shù)：Serial_number是要查找的天數(shù)日期，它有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如1900日期系統(tǒng)的35825表示的1998年1月30日)，以及其他公式或函數(shù)的結(jié)果(如DATEVaLUE(1998/1/30)。實(shí)例：公式“=DAY(2001/1/27)”返回27，=DAY(35825)返回30，=DAY(DATEVaLUE(2001/1/25)返回25。4.DAYS360用途：按照一年360天的算法(每個(gè)月30天，一年共計(jì)12個(gè)月)，返回兩日期間相差的天數(shù)。語(yǔ)法：DAYS360(start_date，end_date，method)參數(shù)：Start_date和end_date是用于計(jì)算期間天數(shù)的起止日期。如果start_date在end_date之后，則DAYS360將返回一個(gè)負(fù)數(shù)。日期可以有多種輸入方式:帶引號(hào)的文本串(例如:1998/01/30)、序列號(hào)(例如:如果使用1900日期系統(tǒng)，則35825表示1998年1月30日)或其他公式或函數(shù)的結(jié)果(例如，DATEVaLUE(1998/1/30)。Method是一個(gè)邏輯值，它指定了在計(jì)算中是采用歐洲方法還是美國(guó)方法。若為FALSE或忽略，則采用美國(guó)方法(如果起始日期是一個(gè)月的31日，則等于同月的30日。如果終止日期是一個(gè)月的31日，并且起始日期早于30日，則終止日期等于下一個(gè)月的1日，否則，終止日期等于本月的30日)。若為TRUE則采用歐洲方法(無論是起始日期還是終止日期為一個(gè)月的31號(hào)，都將等于本月的30號(hào))。實(shí)例：公式“=DAYS360(1998/2/1，2001/2-1)”返回1080。5.EDATE用途：返回指定日期(start_date)之前或之后指定月份的日期序列號(hào)。語(yǔ)法：EDATE(start_date，months)參數(shù)：Start_date參數(shù)代表開始日期，它有多種輸入方式:帶引號(hào)的文本串(例如:1998/01/30)、序列號(hào)(如35825表示1998年1月30日)或其他公式或函數(shù)的結(jié)果(例如:DATEVaLUE(1998/1/30)。Months為在start_date之前或之后的月份數(shù)，未來日期用正數(shù)表示，過去日期用負(fù)數(shù)表示。實(shí)例：公式“=EDATE(2001/3/5，2)”返回37016即2001年5月5日，=EDATE(2001/3/5，-6)返回36774即2000年9月5日。6.EOMONTH用途：返回start-date之前或之后指定月份中最后一天的序列號(hào)。語(yǔ)法：EOMONTH(start_date，months)參數(shù)：Start_date參數(shù)代表開始日期，它有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如1900日期系統(tǒng)中的35825)或其他公式或函數(shù)的結(jié)果(如DATEVaLUE(1998/1/30)。Month為start_date之前或之后的月份數(shù)，正數(shù)表示未來日期，負(fù)數(shù)表示過去日期。實(shí)例：公式“=EOMONTH(2001/01/01，2)”返回36981即2001年3月31日，=EOMONTH(2001/01/01，-6)返回36738即2000年7月31日。7.HOUR用途：返回時(shí)間值的小時(shí)數(shù)。即介于0(12:00 A.M.)到23(11:00 P.M.)之間的一個(gè)整數(shù)。語(yǔ)法：HOUR(serial_number)參數(shù)：Serial_number表示一個(gè)時(shí)間值，其中包含著要返回的小時(shí)數(shù)。它有多種輸入方式:帶引號(hào)的文本串(如6:45 PM)、十進(jìn)制數(shù)(如0.78125表示6:45PM)或其他公式或函數(shù)的結(jié)果(如TIMEVaLUE(6:45 PM)。實(shí)例：公式“=HOUR(3:30:30 PM)”返回15，=HOUR(0.5)返回12即12:00:00 AM，=HOUR(29747.7)返回16。8.MINUTE用途：返回時(shí)間值中的分鐘，即介于0到59之間的一個(gè)整數(shù)。語(yǔ)法：MINUTE(serial_number)參數(shù)：Serial_number是一個(gè)時(shí)間值，其中包含著要查找的分鐘數(shù)。時(shí)間有多種輸入方式:帶引號(hào)的文本串(如6:45 PM)、十進(jìn)制數(shù)(如0.78125表示6:45 PM)或其他公式或函數(shù)的結(jié)果(如TIMEVaLUE(6:45 PM)。實(shí)例：公式“=MINUTE(15:30:00)”返回30，=MINUTE(0.06)返回26，=MINUTE(TIMEVaLUE(9:45 PM)返回45。9.MONTH用途：返回以序列號(hào)表示的日期中的月份，它是介于1(一月)和12(十二月)之間的整數(shù)。語(yǔ)法：MONTH(serial_number)參數(shù)：Serial_number表示一個(gè)日期值，其中包含著要查找的月份。日期有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如表示1998年1月30日的35825)或其他公式或函數(shù)的結(jié)果(如DATEVaLUE(1998/1/30)等。實(shí)例：公式“=MONTH(2001/02/24)”返回2，=MONTH(35825)返回1，=MONTH(DATEVaLUE(2000/6/30)返回6。10.NETWORKDAYS用途：返回參數(shù)start-data和end-data之間完整的工作日(不包括周末和專門指定的假期)數(shù)值。語(yǔ)法：NETWORKDAYS(start_date，end_date，holidays)參數(shù)：Start_date代表開始日期，End_date代表終止日;Holidays是表示不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，法定假日以及其他非法定假日。此數(shù)據(jù)清單可以是包含日期的單元格區(qū)域，也可以是由代表日期的序列號(hào)所構(gòu)成的數(shù)組常量。函數(shù)中的日期有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如使用1900日期系統(tǒng)的35825)或其他公式或函數(shù)的結(jié)果(如DATEVaLUE(1998/1/30)。注意:該函數(shù)只有加載“分析工具庫(kù)”以后方能使用。11.NOW用途：返回當(dāng)前日期和時(shí)間所對(duì)應(yīng)的序列號(hào)。語(yǔ)法：NOW()參數(shù)：無實(shí)例：如果正在使用的是1900日期系統(tǒng)，而且計(jì)算機(jī)的內(nèi)部時(shí)鐘為2001-1-28 12:53，則公式“=NOW()”返回36919.54。12.SECOND用途：返回時(shí)間值的秒數(shù)(為0至59之間的一個(gè)整數(shù))。語(yǔ)法：SECOND(serial_number)參數(shù)：Serial_number表示一個(gè)時(shí)間值，其中包含要查找的秒數(shù)。關(guān)于時(shí)間的輸入方式見上文的有關(guān)內(nèi)容。實(shí)例：公式“=SECOND(3:30:26 PM)”返回26，=SECOND(0.016)返回2。13.TIME用途：返回某一特定時(shí)間的小數(shù)值，它返回的小數(shù)值從0到0.99999999之間，代表0:00:00(12:00:00 A.M)到23:59:59(11:59:59 P.M)之間的時(shí)間。語(yǔ)法：TIME(hour，minute，second)參數(shù)：Hour是0到23之間的數(shù)，代表小時(shí);Minute是0到59之間的數(shù)，代表分;Second是0到59之間的數(shù)，代表秒。實(shí)例：公式“=TIME(12，10，30)”返回序列號(hào)0.51，等價(jià)于12:10:30 PM。=TIME(9，30，10)返回序列號(hào)0.40，等價(jià)于9:30:10 AM。=TEXT(TIME(23，18，14)，h:mm:ss AM/PM)返回“11:18:14 PM”。14.TIMEVaLUE用途：返回用文本串表示的時(shí)間小數(shù)值。該小數(shù)值為從0到0.999999999的數(shù)值，代表從0:00:00(12:00:00 AM)到23:59:59(11:59:59 PM)之間的時(shí)間。語(yǔ)法：TIMEVaLUE(time_text)參數(shù)：Time_text是一個(gè)用Excel時(shí)間格式表示時(shí)間的文本串(如6:45 PM和18:45等)。實(shí)例：公式“=TIMEVaLUE(3:30 AM)”返回0.145833333，=TIMEVaLUE(2001/1/26 6:35 AM)返回0.274305556。15.TODAY用途：返回系統(tǒng)當(dāng)前日期的序列號(hào)。參數(shù)：無語(yǔ)法：TODAY()實(shí)例：公式“=TODAY()”返回2001-8-28(執(zhí)行公式時(shí)的系統(tǒng)時(shí)間)。16.WEEKDAY用途：返回某日期的星期數(shù)。在默認(rèn)情況下，它的值為1(星期天)到7(星期六)之間的一個(gè)整數(shù)。語(yǔ)法：WEEKDAY(serial_number，return_type)參數(shù)：Serial_number是要返回日期數(shù)的日期，它有多種輸入方式:帶引號(hào)的文本串(如2001/02/26)、序列號(hào)(如35825表示1998年1月30日)或其他公式或函數(shù)的結(jié)果(如DATEVaLUE(2000/1/30)。Return_type為確定返回值類型的數(shù)字，數(shù)字1或省略則1至7代表星期天到數(shù)星期六，數(shù)字2則1至7代表星期一到星期天，數(shù)字3則0至6代表星期一到星期天。實(shí)例：公式“=WEEKDAY(2001/8/28，2)”返回2(星期二)，=WEEKDAY(2003/02/23，3)返回6(星期日)。17.WEEKNUM用途：返回一個(gè)數(shù)字，該數(shù)字代表一年中的第幾周。語(yǔ)法：WEEKNUM(serial_num，return_type)參數(shù)：Serial_num代表一周中的日期。應(yīng)使用DATE函數(shù)輸入日期，或者將日期作為其他公式或函數(shù)的結(jié)果輸入。Return_type為一數(shù)字，確定星期計(jì)算從哪一天開始。默認(rèn)值為1。18.WORKDAY用途：返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和專門指定的假日)的某一日期的值，并扣除周末或假日。語(yǔ)法：WORKDAY(start_date，days，holidays)參數(shù)：Start_date為開始日期;Days為Start_date之前或之后不含周末及節(jié)假日的天數(shù);Days是正值將產(chǎn)生未來日期、負(fù)值產(chǎn)生過去日期;Holidays為可選的數(shù)據(jù)清單，表示需要從工作日歷中排除的日期值(如法定假日或非法定假日)。此清單可以是包含日期的單元格區(qū)域，也可以是由代表日期的序列號(hào)所構(gòu)成的數(shù)組常量。日期有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如1900日期系統(tǒng)時(shí)的35825表示1998年1月30日)或其他公式或函數(shù)的結(jié)果(例如DATEVaLUE(1998/1/30)。19.YEAR用途：返回某日期的年份。其結(jié)果為1900到9999之間的一個(gè)整數(shù)。語(yǔ)法：YEAR(serial_number)參數(shù)：Serial_number是一個(gè)日期值，其中包含要查找的年份。日期有多種輸入方式:帶引號(hào)的文本串(例如1998/01/30)、序列號(hào)(例如，如果使用1900日期系統(tǒng)則35825表示1998年1月30日)或其他公式或函數(shù)的結(jié)果(例如DATEVaLUE(1998/1/30)。實(shí)例：公式“=YEAR(2000/8/6)返回2000”，=YEAR(2003/05/01)返回2003，=YEAR(35825)返回1998。20.YEARFRAC用途：返回start_date和end_date之間的天數(shù)占全年天數(shù)的百分比。語(yǔ)法：YEARFRAC(start_date，end_date，basis)參數(shù)：Start_date表示開始日期，End_date代表結(jié)束日期。函數(shù)中的日期有多種輸入方式:帶引號(hào)的文本串(如1998/01/30)、序列號(hào)(如35829表示1900日期系統(tǒng)中的1998年1月30日)，或其他公式或函數(shù)的結(jié)果(例如DATEVaLUE(1998/1/30)。Basis表示日計(jì)數(shù)基準(zhǔn)類型，其中0或省略為US(NASD)30/360，1實(shí)際天數(shù)/實(shí)際天數(shù)，2實(shí)際天數(shù)/360，3實(shí)際天數(shù)/365，4歐洲30/360。實(shí)例：公式“=YEARFRAC(2001/01/31，2001/06/30，0)”返回0.416666667，YEARFRAC(2001/01/25，2001/09/27)返回0.67222。Excel中用VBA宏自動(dòng)統(tǒng)計(jì)成績(jī)?cè)趯W(xué)校的教學(xué)活動(dòng)中，通過統(tǒng)計(jì)分析學(xué)生的考試成績(jī)，了解教學(xué)情況，為改進(jìn)以后的教學(xué)工作提供依據(jù)，是整個(gè)教學(xué)工作中非常重要一環(huán)。但經(jīng)常按各種不同的指標(biāo)統(tǒng)計(jì)分析成績(jī)，又是一件很枯燥，很繁瑣的事。哪么，能否對(duì)需統(tǒng)計(jì)的各種指標(biāo)，無需人工干預(yù)，讓計(jì)算機(jī)自動(dòng)完成呢？答案是肯定的，筆者通過Excel中自帶的Vba，實(shí)現(xiàn)了這個(gè)設(shè)想。為能給被統(tǒng)計(jì)成績(jī)困擾的朋友提供一些幫助，下面以我校高中三年級(jí)理科成績(jī)統(tǒng)計(jì)為例，介紹其作法，供大家參考。1 設(shè)計(jì)流程,編制程序源代碼：為便于說明做法，同時(shí)代碼簡(jiǎn)短一點(diǎn)，這里以我校高三理科1-4班成績(jī)統(tǒng)計(jì)為例，說明其做法。另外為使編寫代碼效率高一些，這里采用先錄制一部分為宏，再在vba中打開編輯相結(jié)合的辦法編寫。 從外部的“考試成績(jī).XLS”工作簿中將1-4班的成績(jī)拷貝，依次粘貼到“成績(jī)統(tǒng)計(jì)表.XLS”工作簿中的“理科”表中；程序源代碼：Sub 拷外部理科0()ActiveWindow.WindowState = xlMinimizedWindows(考試成績(jī).xls).ActivateActiveWindow.WindowState = xlMaximizedSheets(1班).SelectRange(A1:S60).SelectSelection.CopyActiveWindow.WindowState = xlMinimizedWindows(成績(jī)統(tǒng)計(jì)表.xls).ActivateActiveWindow.WindowState = xlMaximizedSheets(理科).SelectActiveWindow.ScrollRow = 1Range(A1).SelectActiveSheet.Paste拷貝“考試成績(jī)簿”中的1班成績(jī)表中數(shù)據(jù)，粘貼到到成績(jī)統(tǒng)計(jì)表工作簿的理科表A1Range(S22).SelectActiveWindow.LargeScroll Down:=1Range(S44).SelectActiveWindow.LargeScroll Down:=1Range(S66).SelectWindows(考試成績(jī).xls).ActivateActiveWindow.WindowState = xlNormalSheets(2班).SelectRange(A2).SelectActiveWindow.SmallScroll Down:=43Range(A2:S58).SelectApplication.CutCopyMode = FalseSelection.Copy ActiveWindow.WindowState = xlNormalRange(A61).SelectActiveSheet.Paste拷貝考試成績(jī)簿中的2班成績(jī)表中數(shù)據(jù)，粘貼到“成績(jī)統(tǒng)計(jì)”工作簿的理科表A61Windows(考試成績(jī).xls).ActivateActiveWindow.WindowState = xlNormalSheets(3班).SelectRange(A1:S59).SelectApplication.CutCopyMode = FalseSelection.CopyWindows(成績(jī)統(tǒng)計(jì)表.xls).ActivateActiveWindow.WindowState = xlNormalRange(S58).SelectActiveWindow.LargeScroll Down:=1Range(S80).SelectActiveWindow.LargeScroll Down:=1Range(S102).SelectActiveWindow.LargeScroll Down:=1Range(A118).SelectActiveSheet.Paste拷貝成績(jī)簿中的3班成績(jī)表中數(shù)據(jù)，粘貼到成績(jī)統(tǒng)計(jì)表工作簿的理科表A118Rows(118:118).SelectApplication.CutCopyMode = FalseSelection.Delete Shift:=xlUpRange(D122).SelectWindows(考試成績(jī).xls).ActivateActiveWindow.WindowState = xlNormalSheets(4班).SelectRange(A2:S71).SelectSelection.CopyWindows(成績(jī)統(tǒng)計(jì)表.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拷貝四班成績(jī)End Sub 利用Excel的統(tǒng)計(jì)函數(shù)“RANK”將理科表中學(xué)生的各科成績(jī)排名次；目的是讓每位學(xué)生了解自己在全年級(jí)學(xué)生中的排名的位置。程序代碼如下：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列復(fù)制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”分別替換為對(duì)應(yīng)的“”$F$2:$F$246”, ”$H$2:$H$246”,范圍End Sub 將全年級(jí)理科的成績(jī)拷貝，粘貼到新表“降序”表中，并對(duì)各科成績(jī)按降序排序,確定分析中的A,B等優(yōu)秀分?jǐn)?shù)線；程序源代碼如下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列成績(jī)降冪排列Columns(E:E).SelectSelection.Sort Key1:=Range(E1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin將E列成績(jī)降冪排列Columns(G:G).SelectSelection.Sort Key1:=Range(G1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 將G列成績(jī)降冪排列Columns(I:I).SelectSelection.Sort Key1:=Range(I1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 將I列成績(jī)降冪排列Columns(K:K).SelectSelection.Sort Key1:=Range(K1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin 將K列成績(jī)降冪排列Columns(M:M).SelectSelection.Sort Key1:=Range(M1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin將M列成績(jī)降冪排列Columns(O:O).SelectSelection.Sort Key1:=Range(O1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin將O列成績(jī)降冪排列Columns(Q:Q).SelectSelection.Sort Key1:=Range(Q1), Order1:=xlDescending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin將Q列成績(jī)降冪排列End Sub 建立分析表，列出各班統(tǒng)計(jì)的各種參數(shù)指標(biāo)值。在前面的成績(jī)統(tǒng)計(jì)工作簿中建立如下分語(yǔ)文，數(shù)學(xué)，英語(yǔ)，綜合，總分等項(xiàng)目的“成績(jī)分析”表其中的A、B優(yōu)生分?jǐn)?shù)線的確定，類同于高考錄取線的確定。這里將各科前1-80名定為A等，第81-160名定為B等，其具體分?jǐn)?shù)值，用Excel自帶的函數(shù):MIN計(jì)算，如語(yǔ)文的A等優(yōu)秀分?jǐn)?shù)線的公式為：=MIN(理降冪！C2:C200),即從理科降序表的“語(yǔ)文字段”第C列的第2到第200個(gè)紀(jì)錄中，選取其中的最小者作為語(yǔ)文A等最低優(yōu)秀分?jǐn)?shù)線，其余各科分?jǐn)?shù)線的確定，公式基本形同，此處不再詳述。各班達(dá)到各種優(yōu)秀分?jǐn)?shù)線學(xué)生個(gè)數(shù)，利用Excel的統(tǒng)計(jì)函數(shù)COUNTIF計(jì)算，如語(yǔ)文科1班A等優(yōu)生個(gè)數(shù)公式如下：=COUNTIF(理科!$C$2:$C$60,=102)；這里的COUNTIF是條件紀(jì)錄數(shù)值項(xiàng)數(shù)的函數(shù)，其中的“理科!”表示成績(jī)統(tǒng)計(jì)工作簿中的“理科” 表，$C$2:$C$60表示理科1班的語(yǔ)文數(shù)據(jù)范圍，“=102”表示數(shù)據(jù)滿足的條件。求和就直接用Excel的求和函數(shù)計(jì)算，如語(yǔ)文科1班上優(yōu)秀分?jǐn)?shù)線的總?cè)藬?shù)合計(jì)公式為： =SUM(C6:D6) 求平均分和求總分用Excel的求平均函數(shù)與求和函數(shù)計(jì)算，如1班語(yǔ)文總分公式為：=SUM(理科！$C$2:$C$60)，語(yǔ)文平均分公式為：=AVERAGE(理科!C2:C60)。將上述表中的每個(gè)單元格內(nèi)的公式輸入完畢，你的成績(jī)自動(dòng)統(tǒng)計(jì)表就做好了。保存好你的工作成績(jī)統(tǒng)計(jì)薄，只要在每次統(tǒng)計(jì)成績(jī)時(shí)，同時(shí)打開考試成績(jī)?cè)紨?shù)據(jù)表和你的成績(jī)統(tǒng)計(jì)工作簿，按次序1、選定成績(jī)統(tǒng)計(jì)工作簿中的理科表的A1單元格，運(yùn)行宏拷外部理科，從外部將數(shù)據(jù)拷到“理科表”；2、運(yùn)行宏理科名次，排出理科表中每一位同學(xué)的各科成績(jī)的名次；3、選定理科降序表的A1單元格，運(yùn)行宏理降冪，將“理科表”數(shù)據(jù)拷到“理降冪”表中，并將每科成績(jī)按列從高到低的排列；這時(shí)，成績(jī)分析表中的數(shù)據(jù)就會(huì)按表內(nèi)的公式，將各班成績(jī)的各種指標(biāo)一一統(tǒng)計(jì)出來。另外，如果第二次考試各班的人數(shù)有變化，可以通過選中成績(jī)分析中該班所在行，用查找替換的方式變更；第二次考試A,B等優(yōu)秀分?jǐn)?shù)線，也可以通過選中該列，用查找替換的方式改變。如果班級(jí)有增加或減少，可以在Vba中，通過增刪部分代碼來適應(yīng)你的班級(jí)個(gè)數(shù)。用格式和函數(shù)實(shí)現(xiàn)Excel表格自動(dòng)隔行著色我們?cè)贓xcel中瀏覽一個(gè)非常大的工作簿中的數(shù)據(jù)時(shí)，有時(shí)會(huì)出現(xiàn)看錯(cuò)行的現(xiàn)象發(fā)生。如果能隔行填充上不同的顏色，這種現(xiàn)象就應(yīng)該不會(huì)發(fā)生了。我們利用條件格式和函數(shù)來實(shí)現(xiàn)這一需求。1、啟動(dòng)Excel，打開相應(yīng)的工作簿文檔。2、選中數(shù)據(jù)區(qū)域(或用Ctrl+A組合鍵選中整個(gè)工作表)。3、執(zhí)行“格式條件格式”命令，打開“條件格式”對(duì)話框(如圖1)。圖1 條件格式對(duì)話框4、單擊“條件1”下面左側(cè)方框右邊的下拉按鈕，在隨后出現(xiàn)的下拉列表中，選擇“公式”選項(xiàng)，并在右側(cè)的方框中輸入公式：=MOD(ROW(),2)=0。5、單擊其中的“格式”按鈕，打開“單元格格式”對(duì)話框(如圖2)。圖2 單元格格式6、切換到“圖案”標(biāo)簽下，選中一種顏色，確定返回到“條件格式”對(duì)話框，按下“確定”按鈕退出。7、隔行填充不同顏色的效果得以實(shí)現(xiàn)(如圖3)。圖3 實(shí)現(xiàn)的效果編寫高效Excel VBA代碼的最佳實(shí)踐很多Excel VBA文章和圖書都介紹過如何優(yōu)化VBA代碼，使代碼運(yùn)行得更快。下面搜集了一些使Excel VBA代碼運(yùn)行更快的技術(shù)和技巧，基本上都是實(shí)踐經(jīng)驗(yàn)的總結(jié)。如果您還有其它優(yōu)化Excel VBA代碼的方法，可以在本文后留言或給出鏈接，與大家分享。對(duì)于應(yīng)用程序?qū)傩?，在代碼運(yùn)行時(shí)關(guān)閉除必需屬性以外的其它所有屬性在代碼運(yùn)行時(shí)關(guān)閉不需要的Excel功能。其原因是，如果通過VBA更新不同的單元格區(qū)域，或者從不同的單元格區(qū)域復(fù)制 /粘貼來創(chuàng)建匯總表，則不希望Excel浪費(fèi)時(shí)間和資源來重新計(jì)算公式、顯示粘貼進(jìn)度或者重繪網(wǎng)格，尤其在每次單獨(dú)的操作后（更有甚者，如果代碼使用了循環(huán)，則每次單獨(dú)操作后Excel都會(huì)在后臺(tái)運(yùn)行這些功能）。只需要在代碼執(zhí)行結(jié)束時(shí)進(jìn)行一次重新計(jì)算和重繪就足以使工作簿更新。下面的代碼將幫助您提高代碼的執(zhí)行速度。（1）放置在主代碼前的一段代碼，獲取Excel當(dāng)前的屬性狀態(tài)，然后將其關(guān)閉獲得當(dāng)前的Excel設(shè)置狀態(tài),將其放置在代碼的開頭screenUpdateState = Application.ScreenUpdatingstatusBarState = Application.DisplayStatusBarcalcState = Application.CalculationeventsState = Application.EnableEventsdisplayPageBreakState = ActiveSheet.DisplayPageBreaks 注:這是工作表級(jí)的設(shè)置關(guān)閉一些Excel功能使代碼運(yùn)行更快Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = FalseActiveSheet.DisplayPageBreaks = False 注:這是工作表級(jí)的設(shè)置（2）放置在主代碼結(jié)束后的一段代碼，用來將Excel恢復(fù)到代碼運(yùn)行前的設(shè)置代碼運(yùn)行后,恢復(fù)Excel原來的狀態(tài);將下面的代碼放在代碼的末尾Application.ScreenUpdating = screenUpdateStateApplication.DisplayStatusBar = statusBarStateApplication.Calculation = calcStateApplication.EnableEvents = eventsStateActiveSheet.DisplayPageBreaks = displayPageBreaksState 注:這是工作表級(jí)的設(shè)置下面簡(jiǎn)要解釋這些設(shè)置：Application.ScreenUpdating：將該屬性設(shè)置為False，告訴Excel不要重繪屏幕。其優(yōu)點(diǎn)是不需要Excel花費(fèi)資源來繪制屏幕，因而其改變會(huì)更快而不致讓用戶察覺其變化。因?yàn)槿绱祟l繁地繪制屏幕需要大量的資源，所以關(guān)閉繪制屏幕直到代碼執(zhí)行結(jié)束。在代碼結(jié)束前，確保重新開啟了該屬性。Application.DisplayStatusBar：將該屬性設(shè)置為False，告訴Excel停止顯示狀態(tài)欄。例如，如果使用VBA復(fù)制/粘貼單元格，當(dāng)粘貼執(zhí)行時(shí)Excel將在狀態(tài)欄中顯示操作的進(jìn)度。關(guān)閉屏幕更新不會(huì)關(guān)閉狀態(tài)欄顯示，因此，如果需要的話，可以禁用屏幕更新而仍然可以通過狀態(tài)欄給用戶提供反饋。記住，如果將該屬性設(shè)置為False，在代碼結(jié)束前應(yīng)該將其設(shè)置為True。Application.Calculation：該屬性允許編程設(shè)置Excel的計(jì)算模式?！笆止さ摹保▁lCalculationManual）模式意味著Excel等待用戶（或代碼）來觸發(fā)計(jì)算；默認(rèn)為“自動(dòng)的”（xlCalculationAutomatic）模式，意味著由Excel來決定何時(shí)重新計(jì)算工作簿（例如，當(dāng)在工作表中輸入新公式時(shí)）。由于重新計(jì)算工作簿將花費(fèi)時(shí)間且浪費(fèi)資源，因此可能不希望每次改變單元格值時(shí)Excel都觸發(fā)重新計(jì)算。當(dāng)代碼執(zhí)行時(shí)關(guān)閉重新計(jì)算，在代碼結(jié)束前再設(shè)置回重新計(jì)算模式。Application.EnableEvents：將該屬性設(shè)置為False，告訴Excel不要觸發(fā)事件。你可能不希望Excel為每個(gè)正在通過代碼發(fā)生改變的單元格觸發(fā)事件，關(guān)閉事件將加速VBA代碼的執(zhí)行。ActiveSheet.DisplayPageBreaks：當(dāng)在較新版本的Excel中運(yùn)行VBA時(shí)，則可能比在早期版本的Excel中需要更長(zhǎng)的時(shí)間完成。例如，需要幾秒鐘在早期版本的Excel中完成的宏可能需要幾分鐘才能在更高版本的Excel中完成。或者，第二次運(yùn)行一個(gè)宏可能比第一次運(yùn)行需要的時(shí)間更長(zhǎng)。這是由于VBA宏修改了多行或列的屬性，或者必須強(qiáng)制執(zhí)行計(jì)算Excel分頁(yè)符。如果宏設(shè)置了任何PageSetup屬性或者手動(dòng)設(shè)置了PageSetup屬性，接著運(yùn)行較大區(qū)域的行或列屬性設(shè)置時(shí)會(huì)出現(xiàn)這樣的問題。您可以將該屬性設(shè)置為False來提高代碼的運(yùn)行速度。當(dāng)然，在代碼運(yùn)行結(jié)束前，應(yīng)將該屬性恢復(fù)為原設(shè)置。在單個(gè)操作中讀/寫大塊的單元格區(qū)域本技巧用于優(yōu)化在Excel和代碼之間轉(zhuǎn)換數(shù)據(jù)的次數(shù)。使用數(shù)組變量存儲(chǔ)所需要的值并執(zhí)行取值或賦值操作，而不是一次遍歷單個(gè)單元格并獲取或設(shè)置單個(gè)值。例如，下面的代碼在單元格區(qū)域A1：C10000中放置隨機(jī)數(shù)。代碼段一：運(yùn)行速度較慢的代碼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次