利用Excel的VBA代碼實(shí)現(xiàn)自動(dòng)化“收集原始數(shù)據(jù)、匯總計(jì)算和報(bào)表”

利用Exce1得VBA代碼實(shí)現(xiàn)自動(dòng)化 “收集原始數(shù)據(jù)、匯總計(jì)算與報(bào)表”聯(lián)系人:楊先生電話：電子郵箱：以房地產(chǎn)銷(xiāo)售數(shù)據(jù)為例.兩個(gè)銷(xiāo)售中心以Excel記錄銷(xiāo)售活動(dòng)，原始數(shù)據(jù)與直接使用公式形成得表格模板如下。黃色標(biāo)題名稱(chēng)為公式項(xiàng)，根據(jù)已知數(shù)據(jù)自動(dòng)計(jì)算。1原始數(shù)據(jù)收集表1.1產(chǎn)品表：所有房屋產(chǎn)品，主房、輔房（儲(chǔ)藏室、車(chē)庫(kù)、車(chē)位笥得基本信息;含義房行=ROW（主房口）-ROW（主房［井標(biāo)題］）,動(dòng)態(tài)得數(shù)據(jù)行號(hào)買(mǎi)受人=IFERROR（INDEX（銷(xiāo)售［買(mǎi)受人］,［售行］）,〃"），當(dāng)前買(mǎi)受人售次銷(xiāo)售項(xiàng)目名稱(chēng)分區(qū)名稱(chēng)分期名稱(chēng)數(shù)字樓號(hào)數(shù)字單兀號(hào)數(shù)字樓層數(shù)字方位編號(hào)預(yù)售面積預(yù)售價(jià)格產(chǎn)權(quán)面積=COUNTIFS（銷(xiāo)售［主房索引］,［主房索引］）,當(dāng)前得銷(xiāo)售次數(shù)，退房、換房不刪除數(shù)據(jù)，所以用售次區(qū)別售行對(duì)應(yīng)得銷(xiāo)售數(shù)據(jù)行。房號(hào)=VALUE（［單］&TEXT（［層］，〃00”）&TEXT（［房］，〃00”）），如1單元1層?xùn)|戶表示為1—0101（數(shù)字得自定義格式）主房索引=INDEX（項(xiàng)目分區(qū)［代碼］,MATCH（［項(xiàng)目］&［分區(qū)］，項(xiàng)目分區(qū)［分區(qū)名稱(chēng)］,0））&［分期］&TEXT（［樓］，”00"）&TEXT（［房號(hào)］，"00000”），用于表間互查數(shù)據(jù)銷(xiāo)售索引-IFERROR（［主房索引］&ABS（:售序］）,""），用于表間互查數(shù)據(jù)總房款已收待收1.2銷(xiāo)售表:每次銷(xiāo)售活動(dòng)得真實(shí)記錄，產(chǎn)品得組合及從產(chǎn)品表查取得基本信息;標(biāo)題名稱(chēng)含義-ROW（主房［］）—ROW（銷(xiāo)售［「標(biāo)題］）分區(qū)名稱(chēng)分期名稱(chēng)手工輸入數(shù)字（自定義格式）

當(dāng)前得銷(xiāo)售次數(shù)，退房、換房不刪除數(shù)據(jù)，所以用售次區(qū)別業(yè)務(wù)姓名置業(yè)顧問(wèn)姓名合同中填寫(xiě)得總金額=ROUND（SUM（［主房款］,［儲(chǔ)款］,［庫(kù)款］,［位款］）,0）,自動(dòng)總款計(jì)算得總金額差異=［總房款］—［總款］主房面積=INDEX（主房［面積］,［房行］）=IFERROR（INDEX（房款［實(shí)收日］，MATCH（［銷(xiāo)售索引］&"認(rèn)購(gòu)日期定金〃,房款［款類(lèi)索引］,0））,〃”），實(shí)交定金日期主房款=ROUND（［實(shí)售價(jià)］大［主房面積］,0）購(gòu)房合同簽署日期合同單價(jià)貸款對(duì)象包含儲(chǔ)藏室（C）、車(chē)庫(kù)（K）等貸款資料合格日貸款合同簽署日=SUMIFS（房款［金額］,房款［銷(xiāo)售索引］,［銷(xiāo)售索引］,房款［實(shí)收日］，〃〉40544”，房款［款類(lèi)］，〃商貸"），商業(yè)貸款商放到賬日=SUMIFS（房款［金額］,房款［銷(xiāo)售索引］,［銷(xiāo)售索引］,房款［實(shí)公放收日］,”〉40544”，房款［款類(lèi)］,〃公貸”），公積金貸款到賬日=SUMIFS（房款［金額］,房款［銷(xiāo)售索引］,［銷(xiāo)售索引］，房款［實(shí)已收收日］,”〉40544〃，房款［款類(lèi)］,〃＜〉找差”），不含找差待收=IF（［售序］〉0,［總房款］—［已收］,0）=MATCH（:主房索引］,主房［主房索引］,0）,對(duì)應(yīng)產(chǎn)品表得行房行號(hào)=INDEX（項(xiàng)目分區(qū)［代碼］,MATCH（房款！$B$1&［分區(qū)］,主房索引項(xiàng)目分區(qū)［分區(qū)名稱(chēng)］，0））&［分期:&TEXT（:房號(hào)］,”0000000〃）銷(xiāo)售索引=［主房索弓U&ABS（［售序］）

因業(yè)務(wù)換房造成本次銷(xiāo)售無(wú)效時(shí)，記錄換成了哪套房子1.3房款表:按合約應(yīng)交、實(shí)交價(jià)款得信息標(biāo)題名稱(chēng)含義款行=ROW(房款［］)-ROW(房款［#標(biāo)題］)買(mǎi)受人=INDEX（銷(xiāo)售［買(mǎi)受人］,［售行］）房行=MATCH（［主房索引］,主房［主房索引］,0）售行=MATCH（［銷(xiāo)售索引］,銷(xiāo)售［銷(xiāo)售索引］,0）售次=INDEX（主房［售次］，［房行］）主房索引=$D$1&［分期］&TEXT(［房號(hào)］,"0000000”)銷(xiāo)售索引=［主房索引］&［售序］款類(lèi)索引=［銷(xiāo)售索引］&［款類(lèi)］2匯總計(jì)算表，使用VBA進(jìn)行原始數(shù)據(jù)合并與統(tǒng)計(jì)指標(biāo)得計(jì)算。2.1日?qǐng)?bào)數(shù)據(jù)指標(biāo)表（其她數(shù)據(jù)只就是原始數(shù)據(jù)合并）標(biāo)題名稱(chēng)含義項(xiàng)目分區(qū)分期范圍狀態(tài)說(shuō)明開(kāi)始日期=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))主房套數(shù)=COUNTIFS（銷(xiāo)售［項(xiàng)目］,［項(xiàng)目］，銷(xiāo)售［分區(qū)］,［分區(qū)］,銷(xiāo)售［分期］,［分期］，IF（［狀態(tài)］=”認(rèn)購(gòu)”，銷(xiāo)售［認(rèn)購(gòu)日］,IF（［狀態(tài)］=”簽約”，銷(xiāo)售［房約日］,銷(xiāo)售［退房日］））,”〉”&［開(kāi)始日期］）主房面積=SUMIFS（銷(xiāo)售［主房面積］,銷(xiāo)售［項(xiàng)目］,［項(xiàng)目］,銷(xiāo)售［分區(qū)］，［分區(qū)］，銷(xiāo)售［分期］，［分期］,IF（［狀態(tài)］=”認(rèn)購(gòu)"，銷(xiāo)售［認(rèn)購(gòu)日］,IF（［狀態(tài)］=”簽約”，銷(xiāo)售［房約日］,銷(xiāo)售［退房日］））,”〉〃&［開(kāi)始日期］）應(yīng)收=IF（［狀態(tài)］=〃退房〃，0,SUMIFS（房款［金額］,房款［款類(lèi)］,”<〉找差"，房款［登錄項(xiàng)目］,［項(xiàng)目］,房款［分區(qū)］,［分區(qū)］,房款［分期］，［分期］，房款［狀態(tài)］,［狀態(tài)］，房款［應(yīng)收日期］,"〉”&［開(kāi)始日期］,房款［應(yīng)收日期］,〃<〃&［截至日期］））+IF（［狀態(tài)］=〃退房”，0,SUMIFS（房款［金額］,房款［款類(lèi)］,"〈>找差”，房款［登錄項(xiàng)目］,［項(xiàng)目］，房款［分區(qū)］,［分區(qū)］,房款［分期］，［分期］，房款［狀態(tài)］,［狀態(tài)］,房款［應(yīng)收日期］,"<〃&［開(kāi)始日期］，房款［實(shí)收日］，””））實(shí)收=SUMIFS（房款［金額］,房款［款類(lèi)］,"〈〉找差”，房款［登錄項(xiàng)目］，［項(xiàng)目］，房款［分區(qū)］,［分區(qū)］,房款［分期］，［分期］，房款［狀態(tài)］,［狀態(tài)］,房款［實(shí)收日］,”〉〃&［開(kāi)始日期］,房款［實(shí)收日］,"<"&［截至日期］）欠收=IF（［狀態(tài)］=〃退房”，0,［應(yīng)收］-［實(shí)收］）2.2VBA代碼PrivateSubWorkbook_Open()ConstYXJUZIUKAsString=”05:00：00"'設(shè)置自動(dòng)運(yùn)行結(jié)束最遲時(shí)刻DimMyWbAsWorkbook'打開(kāi)得工作表(原始數(shù)據(jù)與報(bào)表)DimMySht,ShtJCAsWorksheet'打開(kāi)工作薄得指定工作表與本工作簿得指定工作表DimMyTb,ThisTbAsListObject'打開(kāi)工作薄得指定表格與本工作簿得指定表格DimMyRngAsRangeDimMyNamePath,Vltd(3),Ftww(4) AsStringDimMyRow,MyRows,MyRngR,MyRngC,I,J,AnsAsLongOnErrorResumeNext'出現(xiàn)錯(cuò)誤不提示,直接運(yùn)行下一行代碼Application、ScreenUpdating=Fa1se'關(guān)閉屏幕刷新Application、DisplayAlerts=False'關(guān)閉相應(yīng)與確認(rèn)IfTime〉TimeValue(YXJUZIUK)Then'如果不在凌晨打開(kāi),確認(rèn)就是否運(yùn)行代碼Ans=MsgBox("要進(jìn)行數(shù)據(jù)運(yùn)算嗎？，vbYesNo,"請(qǐng)確認(rèn)就是否進(jìn)行數(shù)據(jù)運(yùn)算")IfAns=vbNoThenExitSubEndIfVltd(0)=”認(rèn)購(gòu)”Vltd(1)="簽約"Vltd(2)=”退房”Ftww(0)=”1本日"Ftww(1)="2本月"Ftww(2)= "3本年”Ftww(3) =”4項(xiàng)目”MyNamePath=”"清除匯總計(jì)算工作簿原有數(shù)據(jù)ForEachMyShtInWorksheetsIfMySht、Name〈＞”基礎(chǔ)"Then'如果不就是基礎(chǔ)表，清除原有數(shù)據(jù)MySht、Rows("2："&MySht、UsedRange、Rows、Count)、DeleteEndIfNextMySht'清除完成'逐個(gè)打開(kāi)讀入原始文件新數(shù)據(jù)SetShtJC=ThisWorkbook、Sheets("基礎(chǔ)”)ForEachMyRngInShtJC、Range("原始數(shù)據(jù)文件［原始數(shù)據(jù)文件］”)Workbooks、OpenMyRng、Value,3,True,, ,,True'只讀方式打開(kāi)原始數(shù)據(jù)文件ShtJC、Cells(MyRng、Row,2)=(MyRng、Va1ue)'記錄原始文件得最終修改時(shí)間MyNamePath=ShtJC、Ce1ls(MyRng、Row,4) &"、收款、xlsx”Workbooks、OpenMyNamePath,3,False, ,,,True'讀寫(xiě)方式打開(kāi)對(duì)賬工作簿W(wǎng)ithWorkbooks("收款、x1sx")、Sheets("房款”)、Rows("2:" &、UsedRange、Rows、Count)、De1eteEndWithThisWorkbook、ActivateForEachMyShtInWorksheetsMyRows=MySht、UsedRange、Rows、CountIfMySht、NameV〉”基礎(chǔ)"AndMySht、Name<>”日?qǐng)?bào)數(shù)據(jù)”ThenIfMySht、Cells(MyRows,1)> " "Then'表格后面無(wú)空行時(shí)添加一行MySht、Range(MySht、Name)、ListObject、ListRows、AddA1waysInsert:=TrueMyRows=MyRows+1EndIf'讀入原始數(shù)據(jù)Workbooks(”銷(xiāo)售數(shù)據(jù)、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'讀入原始數(shù)據(jù)完成EndIfNextMySht'備份原始數(shù)據(jù)MyWordbookName=ShtJC、Ce1Is(MyRng、Row,5)&"銷(xiāo)售數(shù)據(jù)”&Format(Day(Date),”00")&”、xlsm"'設(shè)置備份文件名稱(chēng)MyNamePath=ThisWorkbook、Path&"'備份\"&MyWordbookName'設(shè)置備份文件路徑與名稱(chēng)Ki1lMyNamePathWorkbooks("銷(xiāo)售數(shù)據(jù)、xlsm")、SaveAsMyNamePathWorkbooks(MyWordbookName)、C1oseSavechanges：=Fa1se‘備份完成，關(guān)閉備份得文件NextMyRng'下一個(gè)原始數(shù)據(jù)文件'完成原始數(shù)據(jù)讀入'形成日?qǐng)?bào)數(shù)據(jù)WithShtJC'ThisWorkbook、Sheets("基礎(chǔ)”)ForEachMyRngIn、Range(”分期［分期］”)'遍歷分期數(shù)據(jù)行MyRow=MyRng、RowForI=0To3'范圍(本日、本月、本年、項(xiàng)目)ForJ=0To2'狀態(tài)(0認(rèn)購(gòu)1簽約2退房)SetMySht=ThisWorkbook、Sheets(”日?qǐng)?bào)數(shù)據(jù)")IfMySht、Ce1ls(2,1)〉" "Then'如果不就是空表格就增加一個(gè)新空行MySht、Range("日?qǐng)?bào)數(shù)據(jù)")、ListObject、ListRows、AddAlwaysInsert:=TrueEndIfMyRows=MySht、UsedRange、Rows、Count'記錄表格最后一行以方便后面插入數(shù)據(jù)'把數(shù)據(jù)寫(xiě)入日?qǐng)?bào)數(shù)據(jù)表MySht、Cells(MyRows,1)=、Cells(MyRow,1)'寫(xiě)入項(xiàng)目名稱(chēng)MySht、Cel1s(MyRows,2) =、Cells(MyRow,'寫(xiě)入分區(qū)名稱(chēng)MySht、Ce1ls(MyRows,3)=、Ce1ls(MyRow,'寫(xiě)入分期名稱(chēng)MySht、Cells(MyRows,4)=Ftww(I)'寫(xiě)入范圍MySht、Cells(MyRows,5) =V1td(J)'寫(xiě)入狀態(tài)NextJ'狀態(tài)NextI'范圍NextMyRng'分期'完成日?qǐng)?bào)數(shù)據(jù)'形成新得空表報(bào)文件Kill、Cells(2,1)'刪除原報(bào)表文件、Ce1ls(3,1),.Cells(2,1)'從模板復(fù)制出新文件SetMyWb=Workbooks、0pen(ThisWorkbook、Sheets("基礎(chǔ)”)、Cells(2, 1))'打開(kāi)新文件EndWith'ThisWorkbook、Sheets("基礎(chǔ)”)WithMyWb、Sheets("銷(xiāo)售日?qǐng)?bào)”)、Cel1s(6,2)=Date-1'記錄報(bào)表截至日期、Sheets(”基礎(chǔ)”)、Range(”原始數(shù)據(jù)文件表［最新版本日期］")、Value=ShtJC、Range(”原始數(shù)據(jù)文件［最新版本日期］”)、ValueForEachMyRngInShtJC、Range(”數(shù)據(jù)工作表")IfMyRng、Value="基礎(chǔ)"Then、Sheets(”基礎(chǔ)")>Range(”原始數(shù)據(jù)文件表［最新版本日期］")、Value=_ShtJC、Range(”原始數(shù)據(jù)文件［最新版本日期］")、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'數(shù)據(jù)行，處理其她工作表、RefreshAll'刷新表報(bào)、Save‘保存新報(bào)表、sheeets("日?qǐng)?bào)")、Cel1s(1,8)、SelectApplication、ScreenUpdating=TrueApplication、DisplayAlerts=True'打開(kāi)相響應(yīng)與確認(rèn)OnErrorGoTo0IfTime<TimeVa1ue(YXJUZIUK)Then、CloseSavechanges：=True'退出報(bào)表ThisWorkbook、CloseSavechanges:=True'退出本簿Application、QuitEndIfEndWithEndSub3 表報(bào)，使用數(shù)據(jù)透視獲得所有需要得數(shù)據(jù)成果3.1總指標(biāo)

3.2銷(xiāo)售統(tǒng)計(jì)總表1本日認(rèn)購(gòu)19779,71110,00069,711簽約004,925,416464,9874,460,429退房000002本月認(rèn)購(gòu)19779,71110,00069,711簽約005,404,406464,9874,939,419退房000003本年認(rèn)購(gòu)52463,2343,494,9633,356,856138,107簽約55667,211435,670,499428,291,3527,379,147退房45860—798,59104項(xiàng)目認(rèn)購(gòu)1,534181,8614,073,9633,778,856295,107簽約1,495177,3531,004,922,220995,181,4729,740,748退房571502,173,97703.3項(xiàng)目銷(xiāo)售統(tǒng)計(jì)表項(xiàng)目11本日認(rèn)購(gòu)19779,71110,00069,711簽約002,179,372464,9871,714,385退房000002本月認(rèn)購(gòu)19779,71110,00069,711簽約002,658,362464,9872,193,375退房000003本年認(rèn)購(gòu)35140,6103,454,9633,316,856138,107簽約38044,325227,972,468223,309,3654,663,103退房000300,00004項(xiàng)目認(rèn)購(gòu)950109,3253,733,9633,438,856295,107簽約924106,701557,783,725550,789,0216,994,704退房0001,343,1370項(xiàng)