利用Excel的VBA代码实现自动化收集原始数据汇总计算和报表.docx

1、利用Excel的VBA代码实现自动化收集原始数据汇总计算和报表利用Exce得VBA代码实现自动化“收集原始数据、汇总计算与报表”联系人:杨先生电话:电子邮箱:以房地产销售数据为例.两个销售中心以Ecel记录销售活动,原始数据与直接使用公式形成得表格模板如下。黄色标题名称为公式项，根据已知数据自动计算。1原始数据收集表1.1产品表：所有房屋产品，主房、辅房（储藏室、车库、车位等)得基本信息；标题名称含义房行ROW(主房)-RW(主房标题），动态得数据行号买受人IFERROR(INDEX（销售买受人,售行）,)，当前买受人销售项目名称分区名称分期名称数字楼号数字单元号数字楼层数字方位编号预售面积预

2、售价格产权面积售次=CUNI(销售主房索引,主房索引)，当前得销售次数,退房、换房不删除数据，所以用售次区别售行对应得销售数据行。房号=VAL(单&TEXT（层，0”)&TXT(房，0”），如1单元1层东户表示为1101（数字得自定义格式）主房索引DE(项目分区代码,MATCH（项目分区，项目分区分区名称,)）分期&ET（楼，”00）&TET（房号，00000”),用于表间互查数据销售索引=IFRRO(主房索引&B(售序）,） ,用于表间互查数据总房款已收待收1.2销售表:每次销售活动得真实记录，产品得组合及从产品表查取得基本信息;标题名称含义售行=ROW（主房）ROW（销售#标题）分区名称分

3、期名称手工输入数字（自定义格式）当前得销售次数，退房、换房不删除数据,所以用售次区别业务姓名置业顾问姓名合同中填写得总金额总款ROUND(UM(主房款,储款,库款,位款)，0)，自动计算得总金额差异=总房款总款主房面积INDX(主房面积,房行)认购日期=IERR(DE(房款实收日，MATCH（销售索引定金,房款款类索引,0）,”）,实交定金日期主房款ROUND（实售价主房面积,0)购房合同签署日期合同单价贷款对象包含储藏室（C）、车库(K)等贷款资料合格日贷款合同签署日商放SMFS（房款金额，房款销售索引，销售索引，房款实收日，54”，房款款类,商贷），商业贷款到账日公放UMIFS（房款金额，

4、房款销售索引,销售索引，房款实收日，”0544”，房款款类,公贷”），公积金贷款到账日已收=SMIS（房款金额，房款销售索引,销售索引，房款实收日,”4544，房款款类,找差”)，不含找差待收=I（售序，总房款已收,）房行=AC（主房索引，主房主房索引，0），对应产品表得行号主房索引NDEX(项目分区代码，(房款！B1&分区,项目分区分区名称，0）&分期&TXT(房号，”00000）销售索引主房索引BS（售序）因业务换房造成本次销售无效时，记录换成了哪套房子1.3房款表:按合约应交、实交价款得信息标题名称含义款行=RO(房款）-RO（房款#标题)买受人=INDE（销售买受人，售行）房行=MAT

5、CH(主房索引,主房主房索引，）售行MCH(销售索引，销售销售索引，0）售次=INDEX（主房售次,房行）主房索引分期&TEXT(房号，000000）销售索引主房索引售序款类索引销售索引&款类2汇总计算表，使用VBA进行原始数据合并与统计指标得计算。2.1日报数据指标表(其她数据只就是原始数据合并）标题名称含义项目分区分期范围状态说明开始日期=HOOE(LET（范围,)，AY(）-2，EOMONTH(TOAY（）1,1)，DATE(YE(TOAY(）)，1，)1,4017）截至日期COOSE(LEFT(范围,）,TY（),EOONTH(TAY(）1，0）+1,DATE（YEAR(TDA()-1

6、）1,1,),DATE（E(TODAY()-1）+20,1,1)主房套数=COUI（销售项目，项目，销售分区，分区,销售分期，分期，IF（状态”认购”，销售认购日,IF（状态”签约”，销售房约日，销售退房日),”&开始日期）主房面积UIS（销售主房面积，销售项目，项目，销售分区，分区，销售分期，分期,F(状态=”认购，销售认购日，IF(状态”签约，销售房约日，销售退房日）)，”开始日期）应收=（状态退房，0,SUMFS（房款金额,房款款类,”找差，房款登录项目，项目，房款分区，分区，房款分期，分期，房款状态,状态，房款应收日期,”开始日期，房款应收日期,找差”，房款登录项目，项目，房款分区,分

7、区，房款分期，分期，房款状态，状态,房款应收日期，&开始日期，房款实收日，”）)实收SIS(房款金额，房款款类,找差”,房款登录项目，项目，房款分区,分区,房款分期，分期，房款状态,状态,房款实收日，”开始日期，房款实收日，&截至日期)欠收=IF（状态=退房”，0，应收-实收）2.2V代码Privat Sub kbok_en（) nst YXJZI As Strig = ”05:00：00 设置自动运行结束最迟时刻 Dim MyWbAs Workbok 打开得工作表（原始数据与报表） DiySht, h As Wkht 打开工作薄得指定工作表与本工作簿得指定工作表 Di yTb, ThisTb

8、 A Listbject 打开工作薄得指定表格与本工作簿得指定表格 Dim MRng As ng Dm MNamePah，Vld（3）,Ftww（4) s rng DimMRw， yRs， MyRg， MyRng, I,J, s As Lng On Erro ResmeNx 出现错误不提示,直接运行下一行代码 Aplican、Sceenpdatig= Fas关闭屏幕刷新 Aplcatin、iyAles = False 关闭相应与确认 If Te TieValue（YXJUZU） hen 如果不在凌晨打开, 确认就是否运行代码 As = sgo（要进行数据运算吗？, vbYesNo，请确认就是否

9、进行数据运算） If Ans= vbN Ten Ext Su End If Vltd（) =”认购 l(1) = 签约 Vltd(2)= 退房” Ftww(0) = ”本日 tww(1） 2本月 Fw（） =3本年” Ftww(）= ”4项目” MyNamePath =” 清除汇总计算工作簿原有数据 Fo Ech Myh n Worheet f MySht、ame 基础 h 如果不就是基础表，清除原有数据 MSht、ows（2： MySht、sedRag、ows、Count)、Dlee Ed If NexySh 清除完成 逐个打开读入原始文件新数据 St htJCTsWorkbok、Sheet

10、s（基础”） Fr h Rng InhC、Rang（原始数据文件原始数据文件”） Wrkboks、OpMyRg、Vale， 3, True， ， ， True只读方式打开原始数据文件 ShtJC、ls（MyRng、Row, 2） = （Myg、Vaue) 记录原始文件得最终修改时间 MyamPath= SC、Cels（MyRng、ow， 4）& 收款、lsx” Wrkbook、pen MNamePat, 3， ale， ， , ru读写方式打开对账工作簿 With Workboks(收款、sx)、Sees（房款”) 、ws(2: 、UsdRa、ows、Cout）、eete EdWth isWo

11、kboo、Acvae For achMySht In Workshet MyRs ySh、URg、Rows、Count IfMySht、ae ”基础 Ad MS、Nae ”Then 表格后面无空行时添加一行 ySht、ne(ySh、Ne）、Lisbect、ListRows、Add AwayInset:=rue yRows MRows + 1 En f 读入原始数据 Workooks（”销售数据、xlsm）、hs（yht、ae）、Rang(MySt、Nae）、op Myht、es（yRws， 1）、PasteSpecil Pse:=xlasVaues, _ Oprato：=xlNe，Skipla

12、ns:Fle,ranspose：=Fae If MyS、m= 房款”The Wrkbooks(收款、xlsx）、Sheets（”房款”）、l（2, 1）、PseSeca Paste:=xlPaalues， Operat:=xNone, SiBank:=als，Trapose：=Fase orbook(”收款、sx”)、Ce Savchae:=re En f 读入原始数据完成 Ed If Net Myht 备份原始数据 MyWordookame ShtJ、Cels（MRng、Row， 5） 销售数据” Forma(ay（Dat）， ”00） &”、lsm 设置备份文件名称 MNamePath =

13、 Thisrkbo、ath 备份 MyordboName 设置备份文件路径与名称 Kil MyNaePath Wrkbos（销售数据、xls)、SaeA MyNmePat Wrkoks（MyWorookName）、Cose Sachngs：=Fase 备份完成,关闭备份得文件 Nex MyRng 下一个原始数据文件 完成原始数据读入 形成日报数据 StJC ThisWrkbok、Sees(基础”) Fo ach yRng In 、Range(”分期分期”) 遍历分期数据行 MRow = MyRng、Row For I = To3 范围(本日、本月、本年、项目) Fo J = 0 状态(认购1签

14、约2退房) tMht = ThisWbook、Shets（”日报数据) If MSh、Cel(2， 1） he如果不就是空表格就增加一个新空行 MySht、Rang（日报数据”)、ListOject、tRow、Add AlaysInert:=Tue n If MRows =Myht、UsedRage、Rws、out记录表格最后一行以方便后面插入数据 把数据写入日报数据表 MySh、Clls（MyRw, 1)= 、Cells（yRow, )写入项目名称 MySt、Cels（MyRow,）= 、Cell（MRow，2) 写入分区名称 MySht、els(MyRo，3） 、Cls(MyRw，) 写入

15、分期名称 MySht、Cells(MyRows， 4) Ft（) 写入范围 MySht、Cells（MyRows,5）= d(J)写入状态 ext J 状态 Next I 范围 Nxt Myg 分期 完成日报数据 形成新得空表报文件 Kll 、Cells(， 1） 删除原报表文件 、Cel(3,1)， 、Cls(2， 1） 从模板复制出新文件 Set yWb=Wook、pen（hisWkok、Shet（基础”)、Cell(，1） 打开新文件 nd Wh ThisWorkbok、Shes（基础） WthMW 、Shees（销售日报）、Cel(6,2) = Dae - 1 记录报表截至日期 、he

16、ets（基础）、Rg（”原始数据文件表最新版本日期)、Valu = _ ShtJC、ang（原始数据文件最新版本日期）、Value For ahMyRng In ShtJC、Rnge（”数据工作表） I MyRn、Value= 基础Tn 、hes(”基础）、Rae（”原始数据文件表最新版本日期”）、al = _ tJC、Rnge（原始数据文件最新版本日期)、alu Else 、Sheets（Myng、Va）、Rae（Mng、aue)、Row、Delte Thikoo、Shet(MRg、Vale）、ang（yRng、Value)、Copy 、Shets（yRng、ale）、el（,）、PasSp

17、ialPate:=xlaseValues， Operation:xlNne, _ kBlank：alse，Tanpose：=Fale I Nxt MRng 数据行，处理其她工作表 、RereshAll刷新表报 、Save保存新报表 、sheets（”日报）、Cels(1,8)、lct pplcion、Scrnpdatn = Tru Appliction、isplaAlerts =Tre 打开相响应与确认 rr GoTo 0 IfTme TimeVaue(YUZIUK） Ten 、losSvchnge：=True 退出报表 ThisWkbok、Close Sanges:=True 退出本簿 pp

18、licaton、i EndI End WihEn Sub3表报，使用数据透视获得所有需要得数据成果3.1总指标 0161/1 3.2销售统计总表1本日认购 97 7，71110, 6，711签约0 4，925，416 464，74,40，429 退房0 0 2本月认购1 97 79，71110,009,11签约0 5,404，4066，87 4，939,19 退房00 0 0 3本年认购524 3,23,494，63,356，856 18,10 签约55667,211 45，670,49 ，91,352 7，39，17 退房 8 0 98，91 0 项目认购1，3418，8 4,73，963

19、,778,56 295，10签约1，45 177,353 1，004，9，220995，1，2 9，74,748退房5 715 0 2，173，77 0 3.3项目销售统计表项目11本日认购1 977,711 0,000 69,711 签约0 0 2，179,372 464，987 ，14,385 退房0 00 2本月认购 9 79,711 0，00 6，711 签约 ,658,62 46，987 2，193,75 退房0 0 0 0 3本年认购51 4，60 ,45，63 ,316，86 38，107签约380 44，325 22，2，468223,309,6 4,66，103 退房0 0

20、0 300,0000 4项目认购950 09，25 3,733,96,43，56 29，17 签约924 106，1 557,783，25 550，79,02 6，99,0 退房0 0 0 1,33，1370 项目23.4分区分期销售统计表项目1A1本日认购0 0 0 签约0068,398 0 684，98 退房0 0 0 本月认购00 0 0 签约0 0 64,39 4， 退房00 0 0 0 3本年认购5 8，8 500,000500,000 0 签约3 ，66 ,51，928 0，253,3 1,8，8 退房00 0 4项目认购51161,13 2，000 52,0 0 签约51 61,4143，51，1 35，,23 1，48,98退房0 0 11,470 B1本日认购1979，7111,000 6，711签约0 01,494，94464,87 1，029，987 退房0 00 0 本月认购 97 9，711 10，000 69,1 签约0 0 ,97，444，87 1，08,9退房0 0 0 03本年认购294 3,64，54，963 ，816，56 138，10签约317 3，660 176，450,5 7,055，835 ，4，5 退房0 0 0 30,00 0 4项目认购49，9113，171，963 2,876，856 295,07 签约