日々のコンピュータ情報の集積と整理

Dr.ウーパのコンピュータ備忘録

2014年4月22日火曜日

Excel:VBAマクロを使わないで、グラフに使用するデータを手軽に切り替える方法(4) - 外部のExcelファイルのデータを使ってグラフを表示する

記事「Excel:VBAマクロを使わないで、グラフに使用するデータを手軽に切り替える方法(3) - 3系列以下を選択して表示する」では、VBAマクロを使わないで、グラフに使用する3系列以下のデータを手軽に切り替える方法を紹介しました。

今回は外部のExcelファイルのデータをグラフに表示できるようにしてみましょう。

※本サンプルではINDIRECT関数を使用して、外部のExcelのシートの値を読み取っています。
  INDIRECT関数の使用により、読み取る対象の Excel ブックは開いた状態でなければなりません。(*1)
  (INDIRECT関数で閉じた状態のExcelブックのセルの値を読み取ると #REF! になります。)


画面

ブック1 - グラフ用シート

excel_sample_graph_change_list_extfile_control
「管理」シート

ブック2 - データシート1

excel_sample_graph_change_list_extfile_data1
「excel_sample_graph_change_list_extfile_data1.xlsx」ブック - 「1日」シート

ブック3 - データシート2

excel_sample_graph_change_list_extfile_data2
「excel_sample_graph_change_list_extfile_data2.xlsx」ブック - 「2日」シート


表示するグラフの切り替え操作

表示するグラフの切り替えを行うには、グラフが表示されている「管理」シートのグラフ化するシートを選択するためのセルを選択します。

excel_sample_graph_change_list_extfile_control
初期状態



すると、表示できるグラフのシート名がリストとして表示されるので、別のシート([excel_sample_graph_change_list_extfile_data2.xlsx]2日)を選択します。

excel_sample_graph_change_list_extfile_control_exp1
リストから [excel_sample_graph_change_list_extfile_data2.xlsx]2日 を選択。



すぐに元々表示されていた「[excel_sample_graph_change_list_extfile_data1.xlsx]1日」シートのデータから、「[excel_sample_graph_change_list_extfile_data2.xlsx]2日」シートのデータに切り替わります。

excel_sample_graph_change_list_extfile_control_exp2
グラフが「[excel_sample_graph_change_list_extfile_data2.xlsx]2日」シートのデータに切り替わる


このように非常に手軽に表示するデータの切り替えを行うことができます。


表示するグラフの切り替えの仕組み

「管理」シートを次のエリアに分けて説明します。

excel_sample_graph_change_list_extfile_control_area
「管理」シート エリア分け

(1) グラフの表示用データ エリア

ここに記載されているデータを基にグラフを作成しています。
このデータは次の数式により、セルD2で選択されたシートのデータを表示するようになっています。


例)セルA2の場合:(他のセルはオートセル機能によって自動でセルの参照が更新されています。)

セルA2の数式:
=INDIRECT(ADDRESS(ROW(A2),COLUMN(A2),1,1,$D$2&$E$2),TRUE)
ADDRESS 関数で、現在の行・列にある別のシート($D$2&$E$2 でシート名を作成)の参照文字列を作り出しています。
その参照文字列をINDIRECT関数に渡し、その参照文字列が指し示すセルの内容を取得しています。


(2) グラフ化するシート選択 エリア

セルD2にはデータの入力規則を設定し、その下にあるデータ範囲のセルの内容をリストとして表示し、そこから値を選択できるようになっています。

excel_sample_graph_change_list_extfile_control_exp3
データの入力規則を設定し、リストから値を選べるようにする


以上の設定で、ユーザが選んだシートのグラフを動的に表示できるようになっています。
VBAマクロなどを使用せず、Excel の標準的な機能で動的なグラフの変更を実現できるため、非常に手軽です。


サンプルファイルのダウンロード

excel_sample_graph_change_list_extfile_ver1_0_0_public.xlsx (12.5 KB (12,876 バイト))
excel_sample_graph_change_list_extfile_data1.xlsx (9.20 KB (9,431 バイト))
excel_sample_graph_change_list_extfile_data2.xlsx (9.44 KB (9,667 バイト))


まとめ

外部のExcelのデータを選択して、グラフのデータを動的に切り替える方法をご紹介しました。
今回ご紹介した Excel のテクニックを用いれば、一つのグラフで外部の複数のデータを処理できるようになるため、作業の効率化につながると思います。


*1:
Excel で INDIRECT 関数を使用して参照を作成する方法




関連記事

関連記事を読み込み中...

同じラベルの記事を読み込み中...
Related Posts Plugin for WordPress, Blogger...