《利用VBA实现Excel电子表格自动分页统计.doc》由会员分享，可在线阅读，更多相关《利用VBA实现Excel电子表格自动分页统计.doc（19页珍藏版）》请在三一文库上搜索。
1、利用VBA实现Excel电子表格（工资报表）自动分页统计作者：王志华摘要：利用VBA编程，在Excel中启用宏命令，实现工资报表自动分页统计，方便工资统计和查找报表错误，减少统计误差，提高工作效率。关键词：VBA Excel工资报表 分页统计一、问题的提出：随着Excel制作的电子报表越来越多，应用越来越广泛，常常遇到对 其项目进行分页统计的问题，尤其是在工资报表系统中，在手动对工资项 目进行分页统计时，如果出现人员增加、减少或人员调动产生的变换位置 的情况，就得对动辄几十、上百页的报表重新对每页手动设置公式进行分 页合计，再最后汇总，给报表的制作带来极大不便，增加了很大的工作量， 降低了工作
2、效率。如果利用 Microsoft Visual Basic Project 即VBA编辑 宏命令，对报表进行自动分页、每页自动合计和最后总合计，将大大地减 少工作强度，提高统计工作的效率和准确性。二、 问题的解决（功能的实现）：1、打开Excel电子表格应用软件。2、点击Excel窗口菜单，从下拉菜单中点击取消隐藏菜单项，弹出对 话框如图：取淸漲邂区取消隐箴工作薄FERSOffAL 确定取消PERSONAL3、然后点击确定，Excel自动切换到 Microsoft Excel编辑中，如图所示:4、点击Excel菜单栏里的工具菜单，点面下来菜单里的宏选项里的录制宏命令弹出录制新宏对话框，如图所
3、示:5、点击保存在的下来箭头，选择个人宏工作簿，然后点击确认，开始 录制事先编辑好的自动分页汇总和删除分页统计的宏6、按Alt+F8键，弹出启动宏命令对话框，如图所示:7、点击新建宏命令对话框中的编辑按钮，弹出如下对话框:&在模块中输入或编辑、调试事先编辑好的宏命令，如图所示:i Vicxpsvft Visanl Hnsic 一 PEEtSOMAL. ZLS -槻块 1 (ft©)立件瞬©很图电）插入口）榕式辺凋试®运行®工具辽）外接程厚窗口帮朋工趕-TVlFrpj«irt曲用） ,CJ+ AFrGj ect (Bookl) VBAPr
4、ojcct (Pf eSOIAJ-5| Hicrosoft Ek cel 对雾題弘只1 舀 ThLsIorkbook-词福块巔倶块I>XIJi丄吆躍余 '毡v 行鸽'列£3Dili ij hP hh, t1kp rr4 dr. 1毎 csr lltft Irit 治 工nt Din rrr Aw StrinfDim rCurrvntCill Al Bwf? '每一页之分页小计所奉单兀格Din HhtSubm 虹 RuiX '小计区jOLT和云荷屢决厂覆矗転宇母序按分类序IFublie Sub页井页汇息0CelliU, lD.SfclfeclQn.
5、 Error Rezune N-exlt = 2Doi = InputB("U为10不能超辺一页的甫動* * %晋輸入斑页拟PTE口的行數二Id) If i < 0 Or i = Then血曲欣每页行数必须犬于応-)ElseExi t DoEnJ I£La ftp翻Ini Ci)h = Inpil覘:：厂起如疔数默认牺","话辅人起期疔ST, S）T2）疇的徨b甲E列顶端的单元不llefl =工理说亦讥起赔列敷，蛰宵2列二”诘轩认起贻列 X】gM = Inpi祐瞅押锁列毅鞋认為制救第0列："诺输1 = Ran軒"竭5536”En
6、d fcrlVp）. Xov -'Fmt RcwCowit 二 1 i曹 Selctim Revrw.Count恥 Whil« 1 >= «._ .Bowi 6< + l)Txii；«rt Shi ft ：=xlDoBn " 1E当前工作表中Rowi. Gt + 1 itrfftA壑行For 亡olwrttwmrl = llft To SeleliiXh. Cluams. Count lri曲l備丁"送择的磚iJLLEugt (Cells 0 + 1, 1). CtllsGc + 1“ 11*f t 1).M皑駅'合
7、并单5E牯Cells. Gt # 1, 1)-丹本贡合计疔Cells (a lf coluancouTLt). Formula - rf=Slffl ©.* > CStr (i) + JC： E"lJC)With. Acti veSheet. Hauge(Cells(x + lr 1 )f Cells (x + 1Sfilteciion. Columns. Count) Borde .L l ne - xlB order LineWeight = xlMednin ' slThm 第线UThidS线.C«l«rlndex 二 3Eid li
8、th9、具体自动分页汇总和、删除分页汇总宏命令如下:Dim i, h, hh, t, l, x, rr, dr, tt. Is, cs, lleft, Iright As In tegerDim rrr As Stri ngDim rCurre ntCell As Range '每一页之分页小计所在单元格Dim r1stSubCell As Ra nge '小计区域第一个单元格Public Sub自动分页汇总()Cells(1, 1).SelectOn Error Resume Nextt = 2Doi = InputBox(" 默认为10，不能超过一页的范围！ ！&
9、quot;,"请输入每页拟打印的行数",10)If i <= 0 Or i = "" ThenMsgBox ("每页行数必须大于1 ！")ElseExit DoEnd IfLoopi = In t（i）h = In putBox（"起始行数，默认为 5 ","请输入起始行数",5）x = i + hlleft = InputBox（"起始列数，默认为 2列","请输入起始列",2）lright = InputBox("最终列数，默认为倒数第
10、 0 列", " 请输入最终列 ", 0)l = Range("A65536").End(xlUp).Row ' 本示例选定包含单元格 B4 的区域中 B 列顶端的单元格。 Range("B4").End(xlUp).Select'For RowCount = 1 To Selection.Rows.Count '循环选择的每一行。Do While l >= xRows(x + 1).Insert Shift:=xlDown ' 在当前工作表中 Rows(x + 1) 行插入空隔行For
11、 columncount = lleft To Selection.Columns.Count - lright '循环选择的每一列。Range(Cells(x + 1, 1), Cells(x + 1, lleft - 1).Merge '合并单元格Cells(x + 1, 1) = "本页合计 "Cells(x + 1, columncount).Formula = "=SUM(R-" + CStr(i) + "C:R-1C)"With ActiveSheet.Range(Cells(x + 1, 1), Cell
12、s(x + 1, Selection.Columns.Count).Borders ' 边框设置.Line = xlBorderLine.Weight = xlMedium 'xlThin细线 'xlThick 粗线.ColorIndex = 3End WithWith ActiveSheet.Range(Cells(x + 1, 1), Cells(x + 1, Selection.Columns.Count).Font '字体设置'.Size = 14 .Bold = True'.Italic = True .ColorIndex = 3En
13、d WithWith ActiveSheet.Range(Cells(x + 1, 1), Cells(x + 1, Selection.Columns.Count).Interior ' 设置单元格底色'.ColorIndex = 8 '为青色End WithNext columncountActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(x + 2) ' 在当前工作表中 Rows(x + 2) 行插入分隔符x = (i + 1) * t x = x + h - 1 t = t + 1 l = l
14、 + 1Looprr = l Mod (i + 1)Rows(l + 1).Insert Shift:=xlDownSelect Case rrCase h + 1 To ihh = 2rr = rr - hrrr = CStr(rr)For columncount = lleft To Selection.Columns.Count - lright '循环选择的每一列Range(Cells(l + 1, 1), Cells(l + 1, lleft - 1).Merge '合并单元格Cells(l + 1, 1) = "本页合计 "Cells(l + 1
15、, columncount).Formula = "=SUM(R-" + CStr(rrr) + "C:R-1C)"With ActiveSheet.Range(Cells(l + 1, 1), Cells(l + 1, Selection.Columns.Count).Borders ' 边框设置.Line = xlBorderLine.Weight = xlMedium 'xlThin细线 'xlThick 粗线.ColorIndex = 3End With字体With ActiveSheet.Range(Cells(l +
16、1, 1), Cells(l + 1, Selection.Columns.Count).Font ' 设置'.Size = 14.Bold = True'.Italic = True.ColorIndex = 3End WithWith ActiveSheet.Range(Cells(x + 1, 1), Cells(x + 1, Selection.Columns.Count).Interior ' 设置单元格底色'.ColorIndex = 8 '为青色End WithNext columncountCase hhh = 1Case 0 T
17、o h - 1hh = 2rr = rr + i - h + 1rrr = CStr(rr)For columncount = lleft To Selection.Columns.Count - lright '循环选择的每一列Range(Cells(l + 1, 1), Cells(l + 1, lleft - 1).Merge '合并单元格Cells(l + 1, 1) = "本页合计 "Cells(l + 1, columncount).Formula = "=SUM(R-" + CStr(rrr) + "C:R-1C)
18、"With ActiveSheet.Range(Cells(l + 1, 1), Cells(l + 1, Selection.Columns.Count).Borders ' 边框设置.Line = xlBorderLine.Weight = xlMedium 'xlThin细线 'xlThick 粗线.ColorIndex = 3End With字体With ActiveSheet.Range(Cells(l + 1, 1), Cells(l + 1, Selection.Columns.Count).Font ' 设置'.Size = 1
19、4.Bold = True'.Italic = True.ColorIndex = 3End WithWith ActiveSheet.Range(Cells(x + 1, 1), Cells(x + 1, Selection.Columns.Count).Interior ' 设置单元格底色' .ColorIndex = 8 ' 为青色End WithNext columncountEnd SelectRows(l + hh).Insert Shift:=xlDownFor columncount = lleft To Selection.Columns.Co
20、unt - lright '循环选择的每一列Range(Cells(l + hh, 1), Cells(l + hh, lleft - 1).Merge ' 合并单元格 Cells(l + hh, 1) = "总合计 "Cells(l + hh, columncount).Formula = "=SUM(R-" + CStr(l - h + 1) + "C:R-1C)/2"With ActiveSheet.Range(Cells(l + hh, 1), Cells(l + hh, Selection.Columns.C
21、ount).Borders ' 边框设置.Line = xlBorderLine.Weight = xlMedium 'xlThin细线 'xlThick 粗线.ColorIndex = 3 '3红色、 4 绿色End WithWith ActiveSheet.Range(Cells(l + hh, 1), Cells(l + hh, Selection.Columns.Count).Font ' 体设置'.Size = 14.Bold = True'.Italic = True.ColorIndex = 3End WithWith Ac
22、tiveSheet.Range(Cells(l + hh, 1), Cells(l + hh, Selection.Columns.Count).Interior' 设置单元格底色.ColorIndex = 8 '为青色End WithNext columncountRange(Cells(1, 1), Cells(l + 1, 2).Locked = TrueActiveSheet.ProtectCells(1, 1).SelectEnd SubPublic Sub删除分页汇总()On Error Resume NextActiveSheet.U nprotectCells.
23、Locked = FalseActiveSheet.ResetAIIPageBreakslastli ne = a65536.E nd(xlUp).RowSet r1stSubCell = Range("Ah")'本例名单从 Ah单元格开始For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown)For i = lastl ine To h Step -1If Range("A" & i)="本页合计 "Or Range("A&quo
24、t; & i)="总合计 “ Then Range(i & ":" &i).E ntireRow.DeleteNext iNext rCurre ntCellEnd Sub10、关闭宏编辑模板，退到Excel电子表格应用软件中。11、在Excel菜单栏框内点击右键，弹出对话框如图：保护 边框 窗体 公式审核监视窗口 控件工具箱 列表任算窗格审阅数据透视表團表图片 退岀设计模式 外部数据 文本到语音 艺术字 哥号栏自定文11、在弹出一个对话框点击自定义，弹出一个新对话框如图:自定兗 关氐 112、在自定义对话框中选择命令标签，并点击新菜单选项
25、，将新菜单拖入Excel菜单栏中新建菜单，并改名为我的菜单13、然后再在自定义对话框中选择命令标签，并点击宏选项，将自定义菜单项拖入Excel菜单栏中我的菜单下，新建弹出式菜单，并改名为自动分页统计和删除分页统计如图:14、然后点击指定宏，弹出对话框如图: 肅定 指定宏宏名FEBSB用LL 3ILS!目动分更汇总FERSCINAL.：匕1删除分页忻庶PERSONAL. XLS!自动分更汇总所有打开的工作簿说明15、在弹出的对话框中分别为我的菜单中的自动分页汇总和删除分页 汇总指定相应的宏命令16、然后点击Excel菜单栏中的窗口菜单中的隐藏命令，把MicrosoftExcel - PERSON
26、A 页面隐藏。17、到此为止，用VBA实现Excel电子表格的自动分页汇总就编辑完毕。三、举例验证效果（功能的验证）：1、调入任意一张Excel工资表，如图所示工筍表序号工資明细表性别工件单位基丰工资标准工资补肋扣三金实发工演签名备注1赵一男生产科U111111'222孙二男生产科12121212'24李三男安全科13131313264周四女安全科14141414'2S j5郑五舅财务科1E151515'3D 16王彳男财务科1616uu'327码七男人事科17171717F 34mA男人事科1S18IBISr 309褚九女企彗科19101919F 38
27、10卫+男企管科20202020r 4011豐一男空产科11il1111F 2212孙二男生产科12121212F 2413男安全科13131313F 2614阖四女安全科14141414F 2815郑五男财务科1515151&r 3016男财务科1Sieie16f 3217人事科171717nr 3418陆八舅人事科IS18IS18f 3619褚九女企管科19191919F 3820卫+企管科2C202020402、点击Excel菜单栏中我的菜单，在弹出的下来菜单中，点击自动分页汇总，弹出如下对话框:3、输入需要每页打印的行数，默认输入为10行，点击确定弹出如下对话框:请錨入起始行数
28、起始行数，默认为3I确走取消4、输入需要统计工资表的起始行数，即表头行数，默认为3行，点击确定弹出如下对话框：5、输入需要统计工资表的起始列数，默认为 5列，点击确定弹出如下 对话框：6、输入需要统计工资表的最终列数，默认为倒数第2列，点击确定, 即完成自动分页汇总，结果下对话框:工资表序号工资明细表性别工柞单傥基本工贤标准工资补助扣三金究发工资签名备注1程一舅生产科111122恥二男生产科222243李二舅安全科333364周四女安全科444435郑五男财务科55551G6王六男财务科63e6127冯七男人事科7777148陈A男人事科888816g褚丸女企管科99S31810卫十男企管科1
29、010101020本页合计55555555HO11赵一男生产科111111112212孙二男生产科121212122413男安全科1313132514周四安全科141414142315郑五男财努科15151515301S王貢男财务科16161163217冯七男人事科17171717341S陈八男入事科1E1316133519褚九女企管科191915193320卫十男企管科2020202040本页含计15515515515S310总合计|155151551C531017、如果删除分页统计，则点击 Excel菜单栏中我的菜单，在弹出的下 拉菜单中，点击删除分页汇总菜单，则删除原来统计项，恢复为原来
30、的表 格内容，如下图所示：工筍表序号工资明细表性别工作单位基本工资标淮工资补助扣三釜实笈工賢签名备注1赵一男生产科1111li11'222孙二男生产科12121212r 243李二男安全科15131313r 264周四女安全科14141414'235郑五男财务科15151515尸306王六男财裁10 a-1«16'327冯七男人事科17171717F 348陆八男人事科16IS13"369褚九文企管科19191919'3S10卫+男企管科20202020F 4011赵男生产科U1111'2212孙二男生产科12121212r2413李
31、三男安全科13.1：3：1313F 2614周四女安全科14141414'2815郑五男财务科15151515L3016王六男财务科16161615'3217冯七男人事科17171717，3418男人事科18181813'3610褚九文企管科19191919r 3820男企管科2020202040&可以重新进行任意行的分页统计，例如非整页统计结果如下所示:二L瓷表序号工贅明细表姓塔性别工作单位基本工诜标准工资补助扣三金实发工资签名备注i赵一男生产科U1111n222孙二'男生产科12121212243李二男安全科13131313264周四女安全科1414
32、14142E5郑五1男财务科15151515306王六男财务科1610ie10327码七'男人車科17171717348男人事科1813IE1836g褚九女企管科101919193fi10卫十男企官科2020202040本页台计1551B615545631011男生产科111111112212孙二1男生产科121212122413李三男安全科131313132614周四女安全科14円14112815郑五男财务科1515151530本页合计65価6565130总合计Z202202202204409、功能演示完毕，功能得到认定此宏命令能将其他 Excel 电子报表进行类似的按要求分页、每页自动 合计和最后总合计，避免了手动完成这部分工作的繁琐，降低了工作强度， 提高了工作效率，如果是长达几十、上百页的报表，更能表现其效果了。