| 利用Excel一键获取功效矩阵气泡图的奥秘 – STARBLOG | 您所在的位置：网站首页 › excel气泡图横轴数不对 › 利用Excel一键获取功效矩阵气泡图的奥秘 – STARBLOG |
利用Excel一键获取功效矩阵气泡图的奥秘 – STARBLOG
|
文章目录 1 自动生成辅助定位数据2 自动添加横轴和纵轴标签3 气泡图的整体细节设置3.1 相关功效矩阵气泡图是在专利分析中常用来分析专利技术布局热点和空白点的一种较好的表现形式。关于气泡图的原理和做法笔者很早就曾做过介绍（利用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版本）： 一键生成气泡图 相关 |
【本文地址】