利用Excel一鍵獲取功效矩陣氣泡圖的奧秘

02-04

功效矩陣氣泡圖是在專利分析中常用來分析專利技術布局熱點和空白點的一種較好的表現形式。關於氣泡圖的原理筆者很早也曾做過介紹（參見:利用EXCEL繪製專利分析中的技術-功效矩陣氣泡圖），主要原理就是通過利用輔助坐標定位數據並結合EXCEL中的氣泡圖，來獲取矩陣形式的氣泡圖。

目前也已經可以有很多EXCEL的氣泡圖的模板，同時各種可視化工具的湧現，繪製出一張功效矩陣氣泡圖也不再是什麼難事。但是採用EXCEL的氣泡圖的模板來繪製氣泡圖還是停留在手工層面上，而且繪製出來的氣泡圖的精細度略顯不足。本文將從EXCEL圖表編程的視角來揭示一鍵生成氣泡圖的奧秘。

通過這個小工具，只需要將希望生成氣泡圖的數據複製到EXCEL中，然後一鍵點擊「創建氣泡圖」，就能自動繪製氣泡圖了。下面將一鍵獲取氣泡圖的核心思想進行介紹，對於有代碼恐懼症的讀者可以直接跳到文末了解EXCEL文件的獲取方式，就不用燒腦費神了。

● ● ●

生成輔助定位數據

繪製矩陣氣泡圖的關鍵實際上是要獲取氣泡的定位輔助數據。通常的方法是在EXCEL中單獨根據當前的數據形成一個輔助的氣泡的定位數據，對於不同的數據陣列，其行數和列數通常會發生變化，對於不同的行數和列數也需要對輔助的定位數據進行修改，比較繁瑣。

而採用VBA的方法就是要獲取繪製氣泡圖所需要的3個維度的數據，氣泡的橫坐標位置（X軸定位）、氣泡的縱坐標（Y軸定位）以及氣泡的大小（SIZE數據）。通過簡單的循環語句就可以很方便的生成這3個數據系列，下面是相關的代碼片段。

獲取X軸輔助定位數據n For i = 1 To nn If i = 1 Thenn X = 1n Elsen X = X & "," & in End Ifn Next in X = "={" & X & "}"n 獲取Y軸輔助定位數據n For i = 1 To nn If i = 1 Thenn Y = kn Elsen Y = Y & "," & kn End Ifn Next in Y = "={" & Y & "}"n 獲取氣泡大小值 Sizen For i = 1 To nn tmp = MyRange.Cells(i + 1, k + 1)n If i = 1 Thenn If Len(tmp) = 0 Thenn Size = 0n Elsen Size = tmpn End Ifn Elsen If Len(tmp) = 0 Thenn Size = Size & "," & 0n Elsen Size = Size & "," & tmpn End Ifn End Ifn Next in Size = "={" & Size & "}"n

● ● ●

自動添加橫軸和縱軸標籤

在對X軸和Y軸的輔助定位數據生成之後，已經可以獲得矩陣氣泡圖的雛形，但是面臨的一個問題就是需要在橫坐標和縱坐標添加文本標籤，而在之前通常是採用文本框的形式，手工將各個文本標籤加上去，然後擺放整齊，這種方法既費時也不美觀。

通過添加輔助數據系列的方法自動添加文本標籤，即在X軸上和Y軸上分別添加「透明氣泡」，然後分別為這些「透明氣泡」添加對應的文本標籤。而對文本標籤的長度和位置再進行一些精細的調整，從而保證氣泡圖的美觀性。下面是添加X軸標籤相關代碼片段。

X軸標籤輔助數據n Dim X1, Y1, S1 As Stringn Dim X2, Y2, S2 As Stringn 生成X軸輔助數據列 Y1恆為0n For i = 1 To nn If i = 1 Thenn 設置初始值n X1 = in Y1 = 0n S1 = 0.5n Elsen X1 = X1 & "," & in Y1 = Y1 & "," & 0n S1 = S1 & "," & 0.5n End Ifn Next in n 格式化n X1 = "={" & X1 & "}"n Y1 = "={" & Y1 & "}"n S1 = "={" & S1 & "}"n n With .SeriesCollection(m + 1)n .Name = "X軸"n .XValues = X1n .Values = Y1n .BubbleSizes = S1n n 設置成透明n .Border.LineStyle = xlLineStyleNonen .Interior.ColorIndex = xlColorIndexNonen n 去除陰影n .Shadow = Falsen 增加X軸標籤n .HasDataLabels = Truen .DataLabels.ShowValue = Falsen .DataLabels.ShowBubbleSize = Truen .DataLabels.Font.Size = 10n .DataLabels.Position = xlLabelPositionBelown n 設置值n Pts = .Points.Countn For i = 1 To Ptsn .Points(i).DataLabel.Text = MyRange.Cells(i + 1, 1)n Debug.Print "X標籤："; MyRange.Cells(i + 1, 1)n Next in End Withn

● ● ●

氣泡圖整體細節設置

Excel中對於圖表的各個圖表元素，均可以通過VBA代碼來進行控制，這就為對氣泡圖進行進一步的精細化調整和風格化提供了便利。重點需要自定義的包括X軸標籤區域、Y軸標籤區域、網格線等內容，尤其是對於網格線的設置，網格線的間距需要與最初設置的定位數據相一致，從而能夠使氣泡圖呈現矩陣化的形式。

EXCEL中對於圖表元素的設置有專門的幫助文件進行詳細的解釋，在使用過程中只需要對照相應的屬性值進行設置即可。下面也給出了本工具中相關的代碼片段示例，供大家參考。

With BubbleCht.Chartn .ApplyLayout 1n .ChartStyle = 26n .HasTitle = Falsen .SetElement msoElementDataTableNonen .ClearToMatchStylen .ChartType = xlBubblen .SetSourceData Source:=Selection, PlotBy:=xlColumnsn With .ChartArean .Border.LineStyle = xlLineStyleNonen .AutoScaleFont = Falsen End Withn ---------------------設置圖表區格式------------------------n With .PlotArean .Width = BubbleCht.Chart.ChartArea.Width - YWn .Height = BubbleCht.Chart.ChartArea.Height - 30n .Top = 1n .Left = YWn AutoScaleFont = Falsen End Withn .Legend.Selectn Selection.Deleten n ---------------------設置X坐標軸------------------------n With .Axes(xlCategory)n 坐標軸標籤n .TickLabels.Font.Color = RGB(255, 255, 255)n .MajorUnit = 1n .MinorUnit = 1n .MinimumScale = 0n .MaximumScale = n + 1n .TickLabels.Deleten AutoScaleFont = Falsen 坐標軸標題n .HasTitle = Falsen 網格線n .HasMajorGridlines = Truen .MajorGridlines.Border.Color = RGB(0, 0, 0)n .MajorGridlines.Border.LineStyle = xlContinuous n End Withn n ---------------------設置y坐標軸------------------------n With .Axes(xlValue)n 設置Y軸顏色n .Border.Color = RGB(0, 0, 0)n 坐標軸標籤n .TickLabels.Selectn Selection.Deleten .MajorUnit = 1n .MinorUnit = 1n .MinimumScale = 0n .MaximumScale = m + 1n .TickLabels.Deleten 坐標軸標題n .HasTitle = Falsen 網格線n .HasMajorGridlines = Truen .MajorGridlines.Border.Color = RGB(0, 0, 0)n .MajorGridlines.Border.LineStyle = xlContinuous n End Withn End Withn

以上對一鍵生成氣泡圖的核心代碼進行了介紹，可見EXCEL中的VBA功能也是非常的強大，也希望借這個氣泡圖的一鍵繪製的方法介紹，給各位感興趣的讀者提供一些運用EXCEL自動化繪製圖表的思路，更充分的發揮EXCEL的強大威力。

最後，筆者也將包含VBA宏功能的EXCEL文件給各位讀者進行共享，大家可以通過點擊博客原文鏈接在文章末尾獲取文件下載鏈接，推薦使用EXCEL2013版來進行使用，如在使用過程中出現問題也歡迎留言反饋。

（歡迎關注微信公眾號：專利檢索分析）