现在的位置: 首页 > 搜索引擎优化 > 正文

大批量多竞争对手关键词多维度统计分析VBA代码

2014年01月06日 搜索引擎优化 ⁄ 共 3425字 暂无评论 ⁄ 阅读 1,349 次

该数据统计分析自身网站与竞争对手网站大批量关键词排名数据,以第一、前三、五、十等数量为维度,周期性执行。该数据为笨鸟玩VBA的练习代码,数据本身非SEO必要数据,可转移性也不高。若您对Excel VBA自动处理数据有兴趣或疑问,欢迎沟通交流。

Sub 小区词排名多维度统计表()
Sheets(1).Select
Sheets(1).Name = "data"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = "二手房"
Range("A2").Select
ActiveCell.FormulaR1C1 = "365"
Range("B2").Select
ActiveCell.FormulaR1C1 = "sf"
Range("C2").Select
ActiveCell.FormulaR1C1 = "gj"
Range("D2").Select
ActiveCell.FormulaR1C1 = "58"
Range("E2").Select
ActiveCell.FormulaR1C1 = "ajk"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*二手房"",data!R[-1]C[1]:R[-1]C[6],2,0)"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*二手房"",data!R[-1]C:R[-1]C[5],3,0)"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*二手房"",data!R[-1]C[-1]:R[-1]C[4],4,0)"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*二手房"",data!R[-1]C[-2]:R[-1]C[3],5,0)"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*二手房"",data!R[-1]C[-3]:R[-1]C[2],6,0)"
Range("F1").Select
ActiveCell.FormulaR1C1 = "租房"
Range("F2").Select
ActiveCell.FormulaR1C1 = "365"
Range("G2").Select
ActiveCell.FormulaR1C1 = "sf"
Range("H2").Select
ActiveCell.FormulaR1C1 = "gj"
Range("I2").Select
ActiveCell.FormulaR1C1 = "58"
Range("J2").Select
ActiveCell.FormulaR1C1 = "ajk"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*租房"",data!R[-1]C[-4]:R[-1]C[1],2,0)"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*租房"",data!R[-1]C[-5]:R[-1]C,3,0)"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*租房"",data!R[-1]C[-6]:R[-1]C[-1],4,0)"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*租房"",data!R[-1]C[-7]:R[-1]C[-2],5,0)"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""*租房"",data!R[-1]C[-8]:R[-1]C[-3],6,0)"
Range("A3:J3").Select
Dim i As Integer
i = Sheets(1).[B65536].End(xlUp).Row
Selection.AutoFill Destination:=Range("A3:J" & i), Type:=xlFillDefault
Range("A3:J" & i).Select
Sheets.Add After:=Sheets(Sheets.Count)
Range("B2").Select
ActiveCell.FormulaR1C1 = "全部词"
Range("C2").Select
ActiveCell.FormulaR1C1 = "365"
Range("D2").Select
ActiveCell.FormulaR1C1 = "搜房"
Range("E2").Select
ActiveCell.FormulaR1C1 = "赶集"
Range("F2").Select
ActiveCell.FormulaR1C1 = "58"
Range("G2").Select
ActiveCell.FormulaR1C1 = "安居客"
Range("B3").Select
ActiveCell.FormulaR1C1 = "第一"
Range("B4").Select
ActiveCell.FormulaR1C1 = "前三"
Range("B5").Select
ActiveCell.FormulaR1C1 = "前五"
Range("B6").Select
ActiveCell.FormulaR1C1 = "前十"
Range("B7").Select
ActiveCell.FormulaR1C1 = "前三十"
Range("B8").Select
ActiveCell.FormulaR1C1 = "前五十"
Range("B9").Select
ActiveCell.FormulaR1C1 = "一百内"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(data!C,""=1"")"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(data!C,"">0"",data!C,""0"",data!C,""0"",data!C,""0"",data!C,""0"",data!C,""0"",data!C,""0"",Sheet1!C[-2],""0"",Sheet1!C[-2],""0"",Sheet1!C[-2],""0"",Sheet1!C[-2],""0"",Sheet1!C[-2],""0"",Sheet1!C[-2],""0"",Sheet1!C[-4],""0"",Sheet1!C[-4],""0"",Sheet1!C[-4],""0"",Sheet1!C[-4],""0"",Sheet1!C[-4],""0"",Sheet1!C[-4],""<101"")"
Range("J12:J18").Select
Selection.AutoFill Destination:=Range("J12:N18"), Type:=xlFillDefault
Range("J12:N18").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "纯小区"
Range("C2:G2").Select
Selection.Copy
Range("J2").Select
ActiveSheet.Paste
Range("B3:B9").Select
Selection.Copy
Range("I3").Select
ActiveSheet.Paste
Range("J3").Select
ActiveCell.FormulaR1C1 = "=RC[-7]-R[9]C[-7]-R[9]C"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:N3"), Type:=xlFillDefault
Range("J3:N3").Select
Selection.AutoFill Destination:=Range("J3:N9"), Type:=xlFillDefault
Range("J3:N9").Select
End Sub

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?