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

利用Exce1得VBA代码实现自动化 “收集原始数据、汇总计算与报表”联系人:杨先生电话：电子邮箱：以房地产销售数据为例.两个销售中心以Excel记录销售活动，原始数据与直接使用公式形成得表格模板如下。黄色标题名称为公式项，根据已知数据自动计算。1原始数据收集表1.1产品表：所有房屋产品，主房、辅房（储藏室、车库、车位笥得基本信息;含义房行=ROW（主房口）-ROW（主房［井标题］）,动态得数据行号买受人=IFERROR（INDEX（销售［买受人］,［售行］）,〃"），当前买受人售次销售项目名称分区名称分期名称数字楼号数字单兀号数字楼层数字方位编号预售面积预售价格产权面积=COUNTIFS（销售［主房索引］,［主房索引］）,当前得销售次数，退房、换房不删除数据，所以用售次区别售行对应得销售数据行。房号=VALUE（［单］&TEXT（［层］，〃00”）&TEXT（［房］，〃00”）），如1单元1层东户表示为1—0101（数字得自定义格式）主房索引=INDEX（项目分区［代码］,MATCH（［项目］&［分区］，项目分区［分区名称］,0））&［分期］&TEXT（［楼］，”00"）&TEXT（［房号］，"00000”），用于表间互查数据销售索引-IFERROR（［主房索引］&ABS（:售序］）,""），用于表间互查数据总房款已收待收1.2销售表:每次销售活动得真实记录，产品得组合及从产品表查取得基本信息;标题名称含义-ROW（主房［］）—ROW（销售［「标题］）分区名称分期名称手工输入数字（自定义格式）

当前得销售次数，退房、换房不删除数据，所以用售次区别业务姓名置业顾问姓名合同中填写得总金额=ROUND（SUM（［主房款］,［储款］,［库款］,［位款］）,0）,自动总款计算得总金额差异=［总房款］—［总款］主房面积=INDEX（主房［面积］,［房行］）=IFERROR（INDEX（房款［实收日］，MATCH（［销售索引］&"认购日期定金〃,房款［款类索引］,0））,〃”），实交定金日期主房款=ROUND（［实售价］大［主房面积］,0）购房合同签署日期合同单价贷款对象包含储藏室（C）、车库（K）等贷款资料合格日贷款合同签署日=SUMIFS（房款［金额］,房款［销售索引］,［销售索引］,房款［实收日］，〃〉40544”，房款［款类］，〃商贷"），商业贷款商放到账日=SUMIFS（房款［金额］,房款［销售索引］,［销售索引］,房款［实公放收日］,”〉40544”，房款［款类］,〃公贷”），公积金贷款到账日=SUMIFS（房款［金额］,房款［销售索引］,［销售索引］，房款［实已收收日］,”〉40544〃，房款［款类］,〃＜〉找差”），不含找差待收=IF（［售序］〉0,［总房款］—［已收］,0）=MATCH（:主房索引］,主房［主房索引］,0）,对应产品表得行房行号=INDEX（项目分区［代码］,MATCH（房款！$B$1&［分区］,主房索引项目分区［分区名称］，0））&［分期:&TEXT（:房号］,”0000000〃）销售索引=［主房索弓U&ABS（［售序］）

因业务换房造成本次销售无效时，记录换成了哪套房子1.3房款表:按合约应交、实交价款得信息标题名称含义款行=ROW(房款［］)-ROW(房款［#标题］)买受人=INDEX（销售［买受人］,［售行］）房行=MATCH（［主房索引］,主房［主房索引］,0）售行=MATCH（［销售索引］,销售［销售索引］,0）售次=INDEX（主房［售次］，［房行］）主房索引=$D$1&［分期］&TEXT(［房号］,"0000000”)销售索引=［主房索引］&［售序］款类索引=［销售索引］&［款类］2汇总计算表，使用VBA进行原始数据合并与统计指标得计算。2.1日报数据指标表（其她数据只就是原始数据合并）标题名称含义项目分区分期范围状态说明开始日期=CHOOSE(LEFT(［范围］,1),TODAY()-2,EOMONTH(TODAY()—1,—1),DATE(YEAR(TODAY()—1),1,1)—1,40179)截至日期=CHOOSE(LEFT(［范围］,1),TODAY(),EOMONTH(TODAY()—1,0)+1,DATE(YEAR(TODAY()-1)+1,1,1),DATE(YEAR(TODAY()-1)+20,1,1))主房套数=COUNTIFS（销售［项目］,［项目］，销售［分区］,［分区］,销售［分期］,［分期］，IF（［状态］=”认购”，销售［认购日］,IF（［状态］=”签约”，销售［房约日］,销售［退房日］））,”〉”&［开始日期］）主房面积=SUMIFS（销售［主房面积］,销售［项目］,［项目］,销售［分区］，［分区］，销售［分期］，［分期］,IF（［状态］=”认购"，销售［认购日］,IF（［状态］=”签约”，销售［房约日］,销售［退房日］））,”〉〃&［开始日期］）应收=IF（［状态］=〃退房〃，0,SUMIFS（房款［金额］,房款［款类］,”<〉找差"，房款［登录项目］,［项目］,房款［分区］,［分区］,房款［分期］，［分期］，房款［状态］,［状态］，房款［应收日期］,"〉”&［开始日期］,房款［应收日期］,〃<〃&［截至日期］））+IF（［状态］=〃退房”，0,SUMIFS（房款［金额］,房款［款类］,"〈>找差”，房款［登录项目］,［项目］，房款［分区］,［分区］,房款［分期］，［分期］，房款［状态］,［状态］,房款［应收日期］,"<〃&［开始日期］，房款［实收日］，””））实收=SUMIFS（房款［金额］,房款［款类］,"〈〉找差”，房款［登录项目］，［项目］，房款［分区］,［分区］,房款［分期］，［分期］，房款［状态］,［状态］,房款［实收日］,”〉〃&［开始日期］,房款［实收日］,"<"&［截至日期］）欠收=IF（［状态］=〃退房”，0,［应收］-［实收］）2.2VBA代码PrivateSubWorkbook_Open()ConstYXJUZIUKAsString=”05:00：00"'设置自动运行结束最迟时刻DimMyWbAsWorkbook'打开得工作表(原始数据与报表)DimMySht,ShtJCAsWorksheet'打开工作薄得指定工作表与本工作簿得指定工作表DimMyTb,ThisTbAsListObject'打开工作薄得指定表格与本工作簿得指定表格DimMyRngAsRangeDimMyNamePath,Vltd(3),Ftww(4) AsStringDimMyRow,MyRows,MyRngR,MyRngC,I,J,AnsAsLongOnErrorResumeNext'出现错误不提示,直接运行下一行代码Application、ScreenUpdating=Fa1se'关闭屏幕刷新Application、DisplayAlerts=False'关闭相应与确认IfTime〉TimeValue(YXJUZIUK)Then'如果不在凌晨打开,确认就是否运行代码Ans=MsgBox("要进行数据运算吗？，vbYesNo,"请确认就是否进行数据运算")IfAns=vbNoThenExitSubEndIfVltd(0)=”认购”Vltd(1)="签约"Vltd(2)=”退房”Ftww(0)=”1本日"Ftww(1)="2本月"Ftww(2)= "3本年”Ftww(3) =”4项目”MyNamePath=”"清除汇总计算工作簿原有数据ForEachMyShtInWorksheetsIfMySht、Name〈＞”基础"Then'如果不就是基础表，清除原有数据MySht、Rows("2："&MySht、UsedRange、Rows、Count)、DeleteEndIfNextMySht'清除完成'逐个打开读入原始文件新数据SetShtJC=ThisWorkbook、Sheets("基础”)ForEachMyRngInShtJC、Range("原始数据文件［原始数据文件］”)Workbooks、OpenMyRng、Value,3,True,, ,,True'只读方式打开原始数据文件ShtJC、Cells(MyRng、Row,2)=(MyRng、Va1ue)'记录原始文件得最终修改时间MyNamePath=ShtJC、Ce1ls(MyRng、Row,4) &"、收款、xlsx”Workbooks、OpenMyNamePath,3,False, ,,,True'读写方式打开对账工作簿WithWorkbooks("收款、x1sx")、Sheets("房款”)、Rows("2:" &、UsedRange、Rows、Count)、De1eteEndWithThisWorkbook、ActivateForEachMyShtInWorksheetsMyRows=MySht、UsedRange、Rows、CountIfMySht、NameV〉”基础"AndMySht、Name<>”日报数据”ThenIfMySht、Cells(MyRows,1)> " "Then'表格后面无空行时添加一行MySht、Range(MySht、Name)、ListObject、ListRows、AddA1waysInsert:=TrueMyRows=MyRows+1EndIf'读入原始数据Workbooks(”销售数据、xlsm")、Sheets(MySht、Name)、Range(MySht、Name)、CopyMySht、Ce11s(MyRows,1)、PasteSpecialPaste:=xlPasteVa1ues,_Operation：=xlNone,SkipBlanks:=False,Transpose：=Fa1seIfMySht、Name="房款” ThenWorkbooks("收款、xlsx")、Sheets(”房款”)、Ce1ls(2,1)、PasteSpecia1Paste:=xlPasteValues,—Operation:=x1None,SkipB1anks:=False，Transpose：=Fa1seWorkbooks(”收款、x1sx”)、C1oseSavechanges:=TrueEndIf'读入原始数据完成EndIfNextMySht'备份原始数据MyWordbookName=ShtJC、Ce1Is(MyRng、Row,5)&"销售数据”&Format(Day(Date),”00")&”、xlsm"'设置备份文件名称MyNamePath=ThisWorkbook、Path&"'备份\"&MyWordbookName'设置备份文件路径与名称Ki1lMyNamePathWorkbooks("销售数据、xlsm")、SaveAsMyNamePathWorkbooks(MyWordbookName)、C1oseSavechanges：=Fa1se‘备份完成，关闭备份得文件NextMyRng'下一个原始数据文件'完成原始数据读入'形成日报数据WithShtJC'ThisWorkbook、Sheets("基础”)ForEachMyRngIn、Range(”分期［分期］”)'遍历分期数据行MyRow=MyRng、RowForI=0To3'范围(本日、本月、本年、项目)ForJ=0To2'状态(0认购1签约2退房)SetMySht=ThisWorkbook、Sheets(”日报数据")IfMySht、Ce1ls(2,1)〉" "Then'如果不就是空表格就增加一个新空行MySht、Range("日报数据")、ListObject、ListRows、AddAlwaysInsert:=TrueEndIfMyRows=MySht、UsedRange、Rows、Count'记录表格最后一行以方便后面插入数据'把数据写入日报数据表MySht、Cells(MyRows,1)=、Cells(MyRow,1)'写入项目名称MySht、Cel1s(MyRows,2) =、Cells(MyRow,'写入分区名称MySht、Ce1ls(MyRows,3)=、Ce1ls(MyRow,'写入分期名称MySht、Cells(MyRows,4)=Ftww(I)'写入范围MySht、Cells(MyRows,5) =V1td(J)'写入状态NextJ'状态NextI'范围NextMyRng'分期'完成日报数据'形成新得空表报文件Kill、Cells(2,1)'删除原报表文件、Ce1ls(3,1),.Cells(2,1)'从模板复制出新文件SetMyWb=Workbooks、0pen(ThisWorkbook、Sheets("基础”)、Cells(2, 1))'打开新文件EndWith'ThisWorkbook、Sheets("基础”)WithMyWb、Sheets("销售日报”)、Cel1s(6,2)=Date-1'记录报表截至日期、Sheets(”基础”)、Range(”原始数据文件表［最新版本日期］")、Value=ShtJC、Range(”原始数据文件［最新版本日期］”)、ValueForEachMyRngInShtJC、Range(”数据工作表")IfMyRng、Value="基础"Then、Sheets(”基础")>Range(”原始数据文件表［最新版本日期］")、Value=_ShtJC、Range(”原始数据文件［最新版本日期］")、ValueElse’、Sheets(MyRng、Value)、Range(MyRng、Va1ue)、Rows、DeleteThisWorkbook、Sheets(MyRng、Value)、Range(MyRng、Value)、Copy、Sheets(MyRng、Value)、Cel1s(2, 1)、PasteSpecialPaste:=xlPasteValues,Operation:=xlNone,_SkipBlanks：=False, Transpose：=FalseEndIfNextMyRng'数据行，处理其她工作表、RefreshAll'刷新表报、Save‘保存新报表、sheeets("日报")、Cel1s(1,8)、SelectApplication、ScreenUpdating=TrueApplication、DisplayAlerts=True'打开相响应与确认OnErrorGoTo0IfTime<TimeVa1ue(YXJUZIUK)Then、CloseSavechanges：=True'退出报表ThisWorkbook、CloseSavechanges:=True'退出本簿Application、QuitEndIfEndWithEndSub3 表报，使用数据透视获得所有需要得数据成果3.1总指标

3.2销售统计总表1本日认购19779,71110,00069,711签约004,925,416464,9874,460,429退房000002本月认购19779,71110,00069,711签约005,404,406464,9874,939,419退房000003本年认购52463,2343,494,9633,356,856138,107签约55667,211435,670,499428,291,3527,379,147退房45860—798,59104项目认购1,534181,8614,073,9633,778,856295,107签约1,495177,3531,004,922,220995,181,4729,740,748退房571502,173,97703.3项目销售统计表项目11本日认购19779,71110,00069,711签约002,179,372464,9871,714,385退房000002本月认购19779,71110,00069,711签约002,658,362464,9872,193,375退房000003本年认购35140,6103,454,9633,316,856138,107签约38044,325227,972,468223,309,3654,663,103退房000300,00004项目认购950109,3253,733,9633,438,856295,107签约924106,701557,783,725550,789,0216,994,704退房0001,343,1370项