超高速に大量の Excel のグラフを描画する方法の検討

突然ですが仕事で大量のグラフを描画する必要があります。まぁこんな感じのヤツですよ。ログ解析とかもろもろの統計データを可視化するっていうよくあるやつ。当然ながらいちいちグラフ作成を手作業でヤルのは面倒なので Excel VBA で自動化されてる。

img01.jpg

今も昔もこれからもずーっとある作業です。でも困ったことが1つあります。 Excel のグラフ描画って結構書処理が遅いんです。パソコンに処理待ちさせられることが凄く納得いきません。なんとかせねば。

前々からグラフ描画が遅いのは気がついていたのですが社内の VBA のメンテナーが誰もいなくなってしまったので仕方なく解析してみることにしました。VB Editor を起ち上げること自体が過去に10回程度という超素人です。正直何がなんだかわかってません。

- スポンサーリンク -

プログラムを解析してみてわかったことは、Excel のグラフ描画においては、ChartObjects.Chart の各種設定(書式設定とか)が猛烈に遅いということです。

例えば次のコードは円グラフを描画するコードです。こんだけだと比較的高速に処理される。

Dim grp As ChartObject
Set GrpObj = ActiveSheet.ChartObjects.Add(100, 100, 200, 200)
With GrpObj.Chart
    .ChartType = xl3DPie
    .SetSourceData Source:=Sheets("test").Range("A2:A6,C2:C6"), PlotBy:=xlColumns
    .SeriesCollection(1).ApplyDataLabels AutoText:=True
End With

で、これに描画した後に、こんな具合のコードで書式設定とかを行うと猛烈に遅くなる。

With GrpObj.Chart
    .HasLegend = True
    With .SeriesCollection(1).DataLabels
        .NumberFormatLocal = "0.00%"
        With .Font
            .Name = "MS Pゴシック"
            .FontStyle = "標準"
            .Size = 8
        End With
    End With
    With .PlotArea
        .Interior.ColorIndex = xlNone
        With .Border
            .Weight = xlThin
            .LineStyle = xlNone
        End With
    End With
    With .Legend.Font
        .Name = "MS Pゴシック"
        .FontStyle = "標準"
        .Size = 8
    End With
End With


で素人ながら頭をひねってみました。書式設定済みの ChartObject を Copy & Paste してデータの範囲と表示位置を変更してあげれば、すげぇー高速化されるんじゃないかと。

本業の仕事は自宅では極力しないと決め手はいるのですが、こうして高速化の方法をブログのネタにするつもりでプログラムあれこれしてベンチとったりしてみました。冒頭にあるような Excel データにグラフを50個描画するっていうベンチマークプログラムを3つのアルゴリズムで記述して速度を計測してみました。

アルゴリズム1: 単純に ActiveSheet.ChartObjects.Add で50個のグラフを描画
アルゴリズム2: 1つめのグラフを描画し ActiveSheet.ChartObjects(i).Copy → ActiveSheet.Paste
アルゴリズム3: 1つめのグラフを描画し ActiveSheet.ChartObjects(i).Chart.ChartArea.Copy → ActiveSheet.Paste
※詳細は最後に記述するリンクからソースをダウンロードして VB Editor で中をみてねってことで。

結果は以下の通りになりました。アルゴリズム3で Copy & Paste すれば単純描画に比べて10倍高速化できることが検証できました。

img02-2.jpgimg04-2.jpgimg03-2.jpg

初めに検証したときはアルゴリズム2でかなり高速化されると踏んでいたのですが、ChartObjects そのものの Copy & Paste は激烈遅いことが判明しました。まぁ〜高速化できなくていいかぁ・・・と思っていたのですが、マクロの記録でグラフの Copy & Paste のコードをだしてみたら ChartObjects.Chart.ChartArea.Copy なるものがあることがわかったのでアルゴリズム3を検証してみたらヒットという次第でした。

まぁ素人なので全然わかってないのですが

    Dim grp As ChartObject
    ActiveSheet.ChartObjects(1).Chart.ChartArea.Copy
    ActiveSheet.Paste

ってコードでも

    Dim grp As ChartObject
    ActiveSheet.ChartObjects(1).Copy
    ActiveSheet.Paste

ってコードのように ActiveSheet.ChartObjects(2) として ChartObjects のコピーオブジェクトが生成されるということらしい。ってか Copy されたオブジェクトって上記2つは同じもの?違うモノ?まぁよくわからんが同じにみえる。どうにも Chart.ChartArea 部分が配列として追加されるんじゃないの?と Perler な僕にはよく理解できません。

img05.jpgimg06.jpg

取りあえずベンチマークとして作成した Excel VBA はこちら。 マクロのセキュリティを中にして実行でお試し頂けます。

- スポンサーリンク -

関連する記事&スポンサーリンク