エクセルのちょっといい話（６）

今日からマクロの改定作業に取り掛かるつもりだったのですが、昨日、風呂に浸かりながら、ふと、「チャート表示の参照先をINDIRECT関数で指定すれば、チャートの更新が容易になるのではないか」と思い立ち、朝から確認作業を行いました。

結論から申し上げますと、チャートの参照先としてINDIRECT関数は使えません。しかし、いろいろと調べてみると、可変長に設定した名前付きセル範囲を、チャートの参照先に指定可能なことが分かりました。

もっとも、そのような手法があることは、5月14日のコラム（5月15日追記箇所）でご紹介した「EXCEL HACKS」を読んで、漠然と分かってはいたのですが、その時はあまり気に留めていませんでした。
現行システムでその手法を利用するためには、非常に多くの名前付きセル範囲を設定する必要があり、処理速度的に不利な方向になるのではないかと思い込んでいました。

しかし、とにかく試してみようと、システムの手直しを行い、最適化演算を実行したところ、チャートの参照先に名前付きセル範囲を指定した方が、処理速度が向上することが分かりました。
それは特に、エクセル2003で顕著であり、私の環境において、約5%の高速化が図れました。ただ、エクセル2007においては、処理速度の向上はごくわずかに留まりました。

従来システムでは2008年8月末までのデータ範囲を、あらかじめ確保しているのですが、新しい方法では、直近のデータまで入力しておけば良くなります。
高速化の理由としては、その分、演算量が減少したためではないかと考えています。

この方法を用いると、あらたにデータを追加（具体的には最下行を直下の行にコピー）するだけで、チャート表示範囲が自動的に更新されます。
今まで行っていたような、将来の日付分を非表示にして、表示の乱れを防ぐ必要はなくなります。

具体的な方法については、上述の書籍で詳しく解説されていますが、エクセルのヘルプからも詳細な解説にアクセスすることができます。
エクセル2003の場合、右肩にある質問窓に、SERIES と入力すると、「Excelで名前付け範囲を使用して動的なグラフを作成する」という記事が検索されます。

この記事は、「Microsoft Office Excel 2003 Inside Out」という書籍から引用されたもので、記述内容は「EXCEL HACKS」の内容とほぼ同じです。
私が解説するよりは、はるかに分かりやすいと思いますので、興味のある方はこれらの記事や書籍をご覧ください。

ちなみに、名前付きセル範囲を設定する際、当初は名前にセル範囲の列番号（記号）を用いようとしたのですが、それだと、うまく設定できない場合がありました。
また、セル範囲を設定できても、チャートから参照できなくなる場合があります。

結局、セル範囲の名前に、日本語（具体的には「列」）から始まる名前を指定したところ、無事に設定できました。
しかし、例えば「列A」、「列A2」という2つの似通った名前を設定したところ、一方の名前をチャートから参照することができませんでした。

その場合、「列A2」を「直近A」と変更したところ、チャートからの参照が可能となりました。結局、何が良くて何がだめなのか、はっきりしたことは分からないのですが、英数字から始まる名前は避けた方が良さそうです。

ちなみに、前述の記事や書籍では、名前付きセル範囲を次例のように1行目から指定するようにしていますが、先頭の数行を全体の集計用に使っている場合は、データ行の先頭行を指定する必要があります。

　　=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A))

これを、例えば次のように変更するわけです。

　　=OFFSET(Sheet1!$A$18,0,0,COUNTA($A$18:$A$65536))

具体的には、実際にセル範囲を確認しながら設定する必要があります。

以上のように、可変長の名前付きセル範囲を用いれば、動的なグラフ表示を行うことが可能となります。また、直近範囲のチャート表示を行うこともできるようになります。
ＫＦシステムクリエイターの改定システムでは、チャート表示を上述の動的な表示に変更することにいたしました。これにより、煩雑なチャート更新作業が軽減されるものと思います。

タグ：エクセル チャート表示 動的グラフ 可変長セル範囲 名前付きセル範囲 チャート更新