Excel高级应用

1、 Excel 高级应用 学 号 :120616124姓 名 : 孙 艳一,用宏录制器进行宏编码宏编码: Sub 跨列居中() 跨列居中 MacroRange("A1").Select With Selection.Font .Name = "Tahoma" .Size = 22 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Theme

2、Color = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A1").Select ActiveCell.FormulaR1C1 = "120616班级学员信息登记表" With ActiveCell.Characters(Start:=1, Length:=6).Font .Name = "Tahoma" .FontStyle = "常规" .Size = 22 .Strikethrough =

3、 False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With With ActiveCell.Characters(Start:=7, Length:=9).Font .Name = "宋体" .FontStyle = &quo

4、t;常规" .Size = 22 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A2").Select ActiveCell.FormulaR1C1 = &

5、quot;学号" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .Tin

6、tAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("B2").Select ActiveCell.FormulaR1C1 = "姓名" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .

7、OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("C2").Select ActiveCell.FormulaR1C1 = "性别" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋体&qu

8、ot; .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D2").Select Activ

9、eCell.FormulaR1C1 = "民族" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xl

10、ThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("E2").Select ActiveCell.FormulaR1C1 = "出生年月" With ActiveCell.Characters(Start:=1, Length:=4).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = Fal

11、se .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A3").Select ActiveCell.FormulaR1C1 = "101" Range("B3").Select ActiveCell.FormulaR

12、1C1 = "孙艳" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLigh

13、t1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("C3").Select ActiveCell.FormulaR1C1 = "女" With ActiveCell.Characters(Start:=1, Length:=1).Font .Name = "宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = F

14、alse .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D3").Select ActiveCell.FormulaR1C1 = "汉" With ActiveCell.Characters(Start:=1, Length:=1).Font .Name = "

15、宋体" .FontStyle = "常规" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("E3").Select

16、ActiveCell.FormulaR1C1 = "3/5/1992" Range("A1:F1").Select Range("F1").Activate With Selection .HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .Readin

17、gOrder = xlContext .MergeCells = False End With Range("F3").SelectEnd Sub二,请写出代码完成身份证号码中生日信息提取宏编码:Sub Macro1()' Macro1 Macro Range("C2").Select ActiveCell.FormulaR1C1 = "=MID(RC-1,11,2)&""-""&MID(RC-1,13,2)" Range("C3").Select A

18、ctiveCell.FormulaR1C1 = "" Range("C2").Select Selection.AutoFill Destination:=Range("C2:C3"), Type:=xlFillDefault Range("C2:C3").SelectEnd Sub三,选做题 2,编写倒计时牌(UI)<html> <head> <title>网页特效|L<BODY> <!-将以下代码加入HTML的<Body&

19、gt;</Body>之间-><SCRIPT language=JavaScript1.2> function setcountdown(theyear,themonth,theday) yr=theyear;mo=themonth;da=theday  setcountdown(2010,6,7) var occasion="2010高考时间" var message_on_occasion="盼望已久的时刻终于来到了!"&#

20、160;var countdownwidth='380px' var countdownheight='40px' var countdownbgcolor='tan' var opentags='<font face="宋体"><small>' var closetags='</small></font>' var montha

21、rray=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") var crosscount='' function start_countdown() if (

22、document.layers) document.countdownnsmain.visibility="show" else if (document.all|document.getElementById) crosscount=document.getElementById&&!document.all?document.getElementById("countdownie") : countdownie countdown() if

23、60;(document.all|document.getElementById) document.write('<span id="countdownie" style="width:'+countdownwidth+' background-color:'+countdownbgcolor+'"></span>')window.onload=start_countdownfunction countdown() var

24、 today=new Date() var todayy=today.getYear() if (todayy < 1000) todayy+=1900 var todaym=today.getMonth() var todayd=today.getDate() var todayh=today.getHours() var todaymin=today.getMinutes() var todays

25、ec=today.getSeconds() var todaystring=montharraytodaym+" "+todayd+", "+todayy+" "+todayh+":"+todaymin+":"+todaysec futurestring=montharraymo-1+" "+da+", "+yr dd=Date.parse(futurestring)

26、-Date.parse(todaystring) dday=Math.floor(dd/(60*60*1000*24)*1) dhour=Math.floor(dd%(60*60*1000*24)/(60*60*1000)*1) dmin=Math.floor(dd%(60*60*1000*24)%(60*60*1000)/(60*1000)*1) dsec=Math.floor(dd%(60*60*1000*24)%(60*60*1000)%(60*1000)/1000*1) if(dday<=0&&dhour<=

27、0&&dmin<=0&&dsec<=1&&todayd=da) if (document.layers) document.countdownnsmain.document.countdownnssub.document.write(opentags+message_on_occasion+closetags) document.countdownnsmain.document.countdownnssub.document.close()  else if

28、0;(document.all|document.getElementById) crosscount.innerHTML=opentags+message_on_occasion+closetags return  else if (dday<=-1) if (document.layers) document.countdownnsmain.document.countdownnssub.document.write(opentags+"时间已经过了!"+closetags)

29、 document.countdownnsmain.document.countdownnssub.document.close()  else if (document.all|document.getElementById) crosscount.innerHTML=opentags+"Occasion already passed! "+closetagsreturn  else if (document.layers) docu

30、ment.countdownnsmain.document.countdownnssub.document.write(opentags+dday+ " days, "+dhour+" hours, "+dmin+" minutes, and "+dsec+" seconds left until "+occasion+closetags)document.countdownnsmain.docu

31、ment.countdownnssub.document.close()  else if (document.all|document.getElementById) crosscount.innerHTML=opentags+"还有 "+dday+ " 天, "+dhour+" 小时, "+dmin+" 分, "+dsec+" 秒 就是 &qu

32、ot;+occasion+closetags  setTimeout("countdown()",1000)  </SCRIPT> <ILAYER id=countdownnsmain visibility="hide" bgColor="&countdownbgcolor;" height="&countdownheight;" width="&countdown

33、width;"><LAYER id=countdownnssub height="&countdownheight;" width="&countdownwidth;" top="0" left="0"></LAYER></ILAYER> </body>If Command1.Caption = "倒计时" ThenT

34、imer1.Enabled = TrueCommand1.Caption = "停止"Command2.Enabled = FalseElseTimer1.Enabled = FalseCommand1.Caption = "倒计时"Command2.Caption = "重设倒计时"Command2.Enabled = TrueEnd IfEnd SubPrivate

35、0;Sub Command2_Click()If Command2.Caption = "重设倒计时" ThenCall againCommand1.Enabled = TrueEnd IfEnd SubPrivate Sub Command3_Click()a = MsgBox("你确定要退出嗎")EndEnd SubPrivate Sub Form_Load()ClsText1.Te

36、xt = ""a = MsgBox("以下是一個倒計時程序,請按以下要求輸入你要倒计的时间")h = Val(InputBox("请输入倒计时的,小時数,若无填0", "小時/输入", 0)m = Val(InputBox("请输入倒计时的,分钟数,若无填0", "小時/输入", 0)s = Val(InputBox("请输入倒

37、计时的,秒数,若无填0", "小時/输入", 0)Label1.Caption = "你输入的时间是:" & Format(h, "00") & ":" & Format(m, "00") & ":" & Format(s, "00")&#

38、160;& "如果沒有错误请点击倒计时按纽"Text1.Visible = FalseEnd SubPrivate Sub Timer1_Timer()If s > 0 Thens = s - 1End IfIf s < 1 And m > 0 Thenm = m - 1s = 60End IfIf m < 1 And s = 0 And h > 0 Thenh = h - 1m = 60End IfIf h < 0 Thenh = 0End IfIf m <&