February 1, 2016

Excelのグラフに表示するデータやタイトルを動的に変更したい

エクセルのグラフは、通常、セル範囲を指定してその範囲のデータを描画させるわけですが、ユーザーの操作に対応して変更可能なグラフを作成することもできます。VBAを使用する必要はなく、ワークシート関数だけで作成できてしまいます。

まず、下準備としてデータ範囲の名前を設定しましょう。プロットしたいデータの名前がA~Dまであるとして、その名前のリストを用意し、そのセル範囲の名前を、例えばlist_seriesとします。

次に、プロットしたいデータを選択させる仕組みを作ります。フォーム部品を使っても良いのですが、最もお手軽な方法は入力規則を使う方法でしょう。

そして、選択されたデータを参照するdynamic rangeを作成します。ここでは、MATCH関数でlist_seriesの何番目か、を取り出し、OFFSET関数を使って対応するデータ範囲を取り出し、graph_dataと名付けました。

なお、このdynamic rangeを以下のようにひと工夫すると入力されたデータちょうどの大きさのセル範囲を指定できます(但し、データは空白セルなく、ずっと詰まって並んでいる、こと)。

ここで、適当なグラフを作成し、動的に変更したいデータ系列を選択し、以下のようにワークブック参照を付けた状態でデータ範囲をdynamic_range.xlsx!graph_dataと設定します。
(なお、理由は不明ですが、セル範囲に定義された名前のみ、をgraph_dataのように指定することは出来ません。参照された名前は存在しない、といったエラーとなります。ワークシートあるいはワークブック参照を付けて入力する必要があります。)

この状態で、先ほど設定したデータ選択セルを変更すると、動的にグラフのデータが変更されます。

さらに、グラフのタイトルボックスを選択し、数式バーにセルを指定すると、セルの内容がグラフタイトルに表示されます。
(なお、理由は不明ですが、タイトルバーにはセル参照しか指定できず、式やワークシート関数、定義された名前を使うことは出来ません。使いたい場合は、一度適当なセルで計算を行い、そのセルを参照するという美しくない解決法になるようです。)

発展形としては、折れ線グラフではグラフの横軸方向についても動的にしてやり、適当な範囲を選んでプロットする、といったことをしてやれば、時系列のデータを表示するには便利になるでしょう。ついでに、フォーム部品のスクロールバーを用いれば、かなり自由にデータをプロットできる動的なグラフになります。

参考ファイル(dynamic_chart.xlsx)

No comments :

Post a Comment