功效矩阵气泡图是在专利分析中常用来分析专利技术布局热点和空白点的一种较好的表现形式。关于气泡图的原理和做法笔者很早就曾做过介绍(利用EXCEL绘制专利分析中的技术-功效矩阵气泡图),主要原理就是通过利用辅助坐标定位数据并结合EXCEL中的气泡图,来获取矩阵形式的气泡图,目前也已经可以有很多EXCEL的气泡图的模板,同时各种可视化工具的涌现,绘制一张功效矩阵气泡图也不再是什么难事。
但是采用EXCEL的气泡图的模板来绘制气泡图还是停留在手工层面上,而且绘制出来的气泡图的精细度略显不足。本文将从EXCEL图表编程的视角来揭示一键生成气泡图的奥秘。最终效果是将希望生成气泡图的数据复制到EXCEL中,然后一键点击“创建气泡图”,就能自动绘制气泡图了。下面将一键获取气泡图的核心思想进行介绍,对于有代码恐惧症的读者可以直接跳到文末了解EXCEL文件的获取方式,就不用烧脑费神了。
自动生成辅助定位数据
绘制矩阵气泡图的关键实际上是要获取气泡的定位辅助数据。通常的方法是在EXCEL中单独根据当前的数据形成一个辅助的气泡的定位数据,对于不同的数据阵列,其行数和列数通常会发生变化,对于不同的行数和列数也需要对辅助的定位数据进行修改,比较繁琐。而采用VBA的方法就是要获取绘制气泡图所需要的3个维度的数据,气泡的横坐标位置(X轴定位)、气泡的纵坐标(Y轴定位)以及气泡的大小(SIZE数据)。通过简单的循环语句就可以很方便的生成这3个数据系列,下面是相关的代码片段。
'获取X轴辅助定位数据
For i = 1 To n
If i = 1 Then
X = 1
Else
X = X & "," & i
End If
Next i
X = "={" & X & "}"
'获取Y轴辅助定位数据
For i = 1 To n
If i = 1 Then
Y = k
Else
Y = Y & "," & k
End If
Next i
Y = "={" & Y & "}"
'获取气泡大小值 Size
For i = 1 To n
tmp = MyRange.Cells(i + 1, k + 1)
If i = 1 Then
If Len(tmp) = 0 Then
Size = 0
Else
Size = tmp
End If
Else
If Len(tmp) = 0 Then
Size = Size & "," & 0
Else
Size = Size & "," & tmp
End If
End If
Next i
Size = "={" & Size & "}"
自动添加横轴和纵轴标签
在对X轴和Y轴的辅助定位数据生成之后,已经可以获得矩阵气泡图的雏形,但是面临的一个问题就是需要在横坐标和纵坐标添加文本标签,而在之前通常是采用文本框的形式,手工将各个文本标签加上去,然后摆放整齐,这种方法既费时也不美观。通过添加辅助数据系列的方法自动添加文本标签,即在X轴上和Y轴上分别添加“透明气泡”,然后分别为这些“透明气泡”添加对应的文本标签。而对文本标签的长度和位置再进行一些精细的调整,从而保证气泡图的美观性。下面是添加X轴标签相关代码片段。
'X轴标签辅助数据
Dim X1, Y1, S1 As String
Dim X2, Y2, S2 As String
'生成X轴辅助数据列 Y1恒为0
For i = 1 To n
If i = 1 Then
'设置初始值
X1 = i
Y1 = 0
S1 = 0.5
Else
X1 = X1 & "," & i
Y1 = Y1 & "," & 0
S1 = S1 & "," & 0.5
End If
Next i
'格式化
X1 = "={" & X1 & "}"
Y1 = "={" & Y1 & "}"
S1 = "={" & S1 & "}"
With .SeriesCollection(m + 1)
.Name = "X轴"
.XValues = X1
.Values = Y1
.BubbleSizes = S1
'设置成透明
.Border.LineStyle = xlLineStyleNone
.Interior.ColorIndex = xlColorIndexNone
'去除阴影
.Shadow = False
'增加X轴标签
.HasDataLabels = True
.DataLabels.ShowValue = False
.DataLabels.ShowBubbleSize = True
.DataLabels.Font.Size = 10
.DataLabels.Position = xlLabelPositionBelow
'设置值
Pts = .Points.Count
For i = 1 To Pts
.Points(i).DataLabel.Text = MyRange.Cells(i + 1, 1)
'Debug.Print "X标签:"; MyRange.Cells(i + 1, 1)
Next i
End With
气泡图的整体细节设置
Excel中对于图表的各个图表元素,均可以通过VBA代码来进行控制,这就为对气泡图进行进一步的精细化调整和风格化提供了便利。重点需要自定义的包括X轴标签区域、Y轴标签区域、网格线等内容,尤其是对于网格线的设置,网格线的间距需要与最初设置的定位数据相一致,从而能够使气泡图呈现矩阵化的形式。EXCEL中关于图表元素的设置有专门的帮助文件进行解释,下面也给出了相关的代码片段。
With BubbleCht.Chart
.ApplyLayout 1
.ChartStyle = 26
.HasTitle = False
'.SetElement msoElementDataTableNone
.ClearToMatchStyle
.ChartType = xlBubble
.SetSourceData Source:=Selection, PlotBy:=xlColumns
With .ChartArea
.Border.LineStyle = xlLineStyleNone
.AutoScaleFont = False
End With
'---------------------设置图表区格式------------------------
With .PlotArea
.Width = BubbleCht.Chart.ChartArea.Width - YW
.Height = BubbleCht.Chart.ChartArea.Height - 30
.Top = 1
.Left = YW
' AutoScaleFont = False
End With
.Legend.Select
Selection.Delete
'---------------------设置X坐标轴------------------------
With .Axes(xlCategory)
'坐标轴标签
'.TickLabels.Font.Color = RGB(255, 255, 255)
.MajorUnit = 1
.MinorUnit = 1
.MinimumScale = 0
.MaximumScale = n + 1
.TickLabels.Delete
' AutoScaleFont = False
'坐标轴标题
.HasTitle = False
'网格线
.HasMajorGridlines = True
.MajorGridlines.Border.Color = RGB(0, 0, 0)
.MajorGridlines.Border.LineStyle = xlContinuous
End With
'---------------------设置y坐标轴------------------------
With .Axes(xlValue)
'设置Y轴颜色
.Border.Color = RGB(0, 0, 0)
'坐标轴标签
' .TickLabels.Select
' Selection.Delete
.MajorUnit = 1
.MinorUnit = 1
.MinimumScale = 0
.MaximumScale = m + 1
.TickLabels.Delete
'坐标轴标题
.HasTitle = False
'网格线
.HasMajorGridlines = True
.MajorGridlines.Border.Color = RGB(0, 0, 0)
.MajorGridlines.Border.LineStyle = xlContinuous
End With
End With
以上对一键生成气泡图的核心代码进行了介绍,可见EXCEL中的VBA功能也是非常的强大,也希望借这个气泡图的一键绘制的方法介绍,给各位感兴趣的读者提供一些运用EXCEL自动化绘制图表的思路,更充分的发挥EXCEL的强大威力。最后,笔者也将包含VBA宏功能的EXCEL文件给各位读者进行共享,大家可以在本文附件中进行下载和使用。
EXCEL文件下载地址(推荐使用EXCEL2013版本):
大神,好厉害,以前一直用手工做
呵呵,手工效率太低,还不好看
干货,刚需
牛逼大神,好崇拜你~~~
希望继续分享专利分析的其他知识~~~
会坚持的,多谢支持!
简直找到了知音!在Google上搜索专利数据 编程,然后就看到你的博客,博主写了很多精彩的文章,正在如饥似渴地拜读中,目前刚进入专利分析行业,本人对编程也有浓厚的兴趣,懂点编程方面的知识对工作流程时间可以大大的简化,最近才感悟到在现在的时代里掌握一门编程语言太重要了,编程就像一门工具。 目前本人关注数据可视化领域比如百度出的echart,微软的power,前辈想必在此领域深耕多年,以后常来看看你的博客学习交流!
大神,我想问一下这个必须是excel2013版才有吗?还有这个程序按钮怎么设置,VBA功能在excel哪里找
不同版本VBA代码可能会不太兼容,目前没有对其他版本做过测试。
VBA功能是在宏功能里面的。