○第63回目 図形編(その1) どこまで利用価値があるのが作れるか
まずはニーズの洗い出しです。
図形を使って何かやった例を思い起こしてください。ワードも含めて考えると、資料のある一部に、ふき出し等で注記を加えるときに使用するということが多いのではないでしょうか。(図形で近くの地図を書くとかいうかなり趣味的なものは除きます)
他にも、
フローチャートを作る。
業務の流れを図式化する。
あるものの構造を図式化する。(一例として組織図がイメージしやすい)
パソコンの月刊誌二誌を見て気がついてもの。
案内地図、組織図、作業手順、大まかなフローチャート、家系図、説明書きの見出し、必要な手続きの流れ、機能(三つか四つ)の全体像、説明書き(かなりの複数行)、図形に画像をはめ込みイメージアップ、講評、図の重ね合わせで複雑な表現、
図形として使うか、コメントを強調するために使うか、に二分されます。
後者の中でも、単純なものもあれば、図を重ねて複雑な表現をしているのがありました。
マクロですので基本のうち70％ぐらいで十分ですが、後は面白いといったものも対象にしたいですね。
当面考えないもの(複雑なものは当然考えません)。
色、重ね合わせの図の順番、大きさ、線の太さ。
【取り上げる機能】
※ある種の図形のみとする(面取り長方形、噴出し、爆発)
1.図形の新規作成。図中にコメントを入れる。
2.図形の追加(図形の種類として線も対象)
3.図形の削除
4.コメントの修正
5.コネクタで結ぶ
6.図形の移動(複雑。複数、座標を与える)
7.図形の移動(単純。一つの図形のみ、カーソルの位置に移動)
※上記例のうち、"案内地図"、"家系図"、"図形に画像をはめ込みイメージアップ"は対象外としています。
※作成はエクセル2007のものとなります。
2007年11月28日水曜日
2007年11月24日土曜日
第62回目 これまでのマクロの全体像
○第62回目 これまでのマクロの全体像
前回、マクロのかなりの部分を一覧表的に整理しました。
いい機会ですので、ほかのも含めての全体像を整理してみましょう。
------------------------------------------------------
事務屋のマクロで紹介したマクロ一覧表(第62回現在)
------------------------------------------------------
単 独；【コピー関係】
1.計算式のみコピー(シートの指定は不可)
2.数値のみコピー(シートの指定は不可)
メイン；【コピー関係】
サブ；
1.計算式のみコピー(シートの指定も可)
2.数値のみコピー(シートの指定も可)
3.シート内で複数回のコピー(コピー先が複数に渡って規則正しい場合)
4.シート外の複数回コピー(コピー先が複数シートに渡って場所が同じ場合)
メイン；【セルの結合関係】
サブ；
1.セルの結合
2.セルの結合の解除
メイン；【罫線作成】
サブ；
1.全部引く
2.五飛びに横罫線を引く(飛びは指定可)
3.細線の横罫線を引く
4.全部消す
5.罫線ダイアログの表示
メイン；【印刷作成】
サブ；
1.一気にシート印刷(ダイアログの表示を省略)
2.指定範囲の印刷(設定した改行マークをまたいで指定するとページが分かれる点を、改善すべき)
3.ページ指定の印刷(シート内。カラープリンタの設定も可能)
4.複数シートの印刷(現在のシートから複数シート設定。カラープリンタの設定も可能))
5.自分好みのヘッダ・フッタのセット
6.印刷ダイアログの表示
メイン；【ジャンプ関係】
サブ；
1.シート内にジャンプ(セルを指定)
2.シート外にジャンプ(セルも指定可)
メイン；【計算式文字化関係】
サブ；
1.計算式を文字に(頭に特殊文字列を付加)
2.文字を計算式に(頭の特殊文字列を削除)
3.計算式を文字にしてデータのみの貼り付け
メイン；【小技マクロ】
サブ；
1.連番入力(範囲内に連番を作成)
2.複数文字列の置き換え
3.シート名の取得(ファイル内の全シート名を取得)
4.数式セル保護
5.端にヌル式セット
6.端にsum関数セット
7.指定範囲の座標を貼り付け(範囲を指定したA1形式の座標を貼り付ける)
8.列記号の生み出し(連続する列記号を作成)
9.セルの階段状挿入
メイン；【小技^2】
サブ；
1.指定範囲の数値のみを100倍
2.指定範囲の数値のみを1/100
3.指定範囲の数値のみをクリア
4.指定範囲の特定文字列を含むセルのクリア
メイン；【グラフ作成】
サブ；
1.グラフ新規作成(一括作成)
2.グラフからデータ系列の取得(表示も含む)
3.グラフの追加(ワークシートからのデータ系列の取得を含む)
4.グラフデータ範囲の修正(ワークシートからのデータ系列の取得を含む)
5.グラフタイトル等の修正(ワークシートからのタイトル等の取得を含む)
メイン；【ファイルの仕様書作成(専用のワークシートを作成して実行)】
サブ；
1.シート名の取得とリンクの貼付け
2.シート名の横にコメントを貼る
3.アウトライン用セルの挿入
4.各セルの内容を結合(列単位で複数行のセル内容を結合する)
5.各シートにコメントを貼付け
メイン；【チェック表の作成】
サブ；
1.一つ前の行からの増減率・増減値のチェック表を作る
2.二つの表の増減率･増減値のチェック表を作る
3.詳細な表の概略版を作る(ここに整理するのはどうかな…)
メイン；【複雑系計算式の作成】
サブ；
1.複雑_複雑系計算式の作成(バージョンアップ版、作成中)
2.複雑系計算式の作成
【Function】
1.数値からそれに対応する列記号を生み出す
2.数値のみを文字化する
【フリーソフトからのもの】
列幅拡大
列幅縮小
行高さアップ
行高さダウン
セル書式数字増やす･減らす(小数点以下の表示桁数の調整)
左中右寄せ
上中下寄せ
フォントアップ･ダウン
前回、マクロのかなりの部分を一覧表的に整理しました。
いい機会ですので、ほかのも含めての全体像を整理してみましょう。
------------------------------------------------------
事務屋のマクロで紹介したマクロ一覧表(第62回現在)
------------------------------------------------------
単 独；【コピー関係】
1.計算式のみコピー(シートの指定は不可)
2.数値のみコピー(シートの指定は不可)
メイン；【コピー関係】
サブ；
1.計算式のみコピー(シートの指定も可)
2.数値のみコピー(シートの指定も可)
3.シート内で複数回のコピー(コピー先が複数に渡って規則正しい場合)
4.シート外の複数回コピー(コピー先が複数シートに渡って場所が同じ場合)
メイン；【セルの結合関係】
サブ；
1.セルの結合
2.セルの結合の解除
メイン；【罫線作成】
サブ；
1.全部引く
2.五飛びに横罫線を引く(飛びは指定可)
3.細線の横罫線を引く
4.全部消す
5.罫線ダイアログの表示
メイン；【印刷作成】
サブ；
1.一気にシート印刷(ダイアログの表示を省略)
2.指定範囲の印刷(設定した改行マークをまたいで指定するとページが分かれる点を、改善すべき)
3.ページ指定の印刷(シート内。カラープリンタの設定も可能)
4.複数シートの印刷(現在のシートから複数シート設定。カラープリンタの設定も可能))
5.自分好みのヘッダ・フッタのセット
6.印刷ダイアログの表示
メイン；【ジャンプ関係】
サブ；
1.シート内にジャンプ(セルを指定)
2.シート外にジャンプ(セルも指定可)
メイン；【計算式文字化関係】
サブ；
1.計算式を文字に(頭に特殊文字列を付加)
2.文字を計算式に(頭の特殊文字列を削除)
3.計算式を文字にしてデータのみの貼り付け
メイン；【小技マクロ】
サブ；
1.連番入力(範囲内に連番を作成)
2.複数文字列の置き換え
3.シート名の取得(ファイル内の全シート名を取得)
4.数式セル保護
5.端にヌル式セット
6.端にsum関数セット
7.指定範囲の座標を貼り付け(範囲を指定したA1形式の座標を貼り付ける)
8.列記号の生み出し(連続する列記号を作成)
9.セルの階段状挿入
メイン；【小技^2】
サブ；
1.指定範囲の数値のみを100倍
2.指定範囲の数値のみを1/100
3.指定範囲の数値のみをクリア
4.指定範囲の特定文字列を含むセルのクリア
メイン；【グラフ作成】
サブ；
1.グラフ新規作成(一括作成)
2.グラフからデータ系列の取得(表示も含む)
3.グラフの追加(ワークシートからのデータ系列の取得を含む)
4.グラフデータ範囲の修正(ワークシートからのデータ系列の取得を含む)
5.グラフタイトル等の修正(ワークシートからのタイトル等の取得を含む)
メイン；【ファイルの仕様書作成(専用のワークシートを作成して実行)】
サブ；
1.シート名の取得とリンクの貼付け
2.シート名の横にコメントを貼る
3.アウトライン用セルの挿入
4.各セルの内容を結合(列単位で複数行のセル内容を結合する)
5.各シートにコメントを貼付け
メイン；【チェック表の作成】
サブ；
1.一つ前の行からの増減率・増減値のチェック表を作る
2.二つの表の増減率･増減値のチェック表を作る
3.詳細な表の概略版を作る(ここに整理するのはどうかな…)
メイン；【複雑系計算式の作成】
サブ；
1.複雑_複雑系計算式の作成(バージョンアップ版、作成中)
2.複雑系計算式の作成
【Function】
1.数値からそれに対応する列記号を生み出す
2.数値のみを文字化する
【フリーソフトからのもの】
列幅拡大
列幅縮小
行高さアップ
行高さダウン
セル書式数字増やす･減らす(小数点以下の表示桁数の調整)
左中右寄せ
上中下寄せ
フォントアップ･ダウン
2007年11月22日木曜日
第61回目 グラフ関係の図式的な整理、あわせてちょっとした小物編の整理
○第61回目 グラフ関係の図式的な整理、あわせてちょっとした小物編の整理
グラフ関係のマクロの整理をしましょう。
グラフ管理表は次のようになっています。
グラフ関係のマクロはサブルーティン化されて次のようになっています。
メイン；グラフ作成
サブ；
1.グラフ新規作成(一括作成)
2.グラフからデータ系列の取得(表示も含む)
3.グラフの追加(ワークシートからのデータ系列の取得から)
4.グラフデータ範囲の修正(ワークシートからのデータ系列の取得から)
5.グラフタイトル等の修正(ワークシートからのタイトル等の取得から)
となっています。
このシステムは、2を中心に動きます。これによりワークシート上に、既存のグラフのデータ系列が表示されるからです。これを加工したあと、グラフに反映させるのは、4.となります。
また、このデータ系列をもとにして、新たなデータセットを作って、グラフに追加するのは、3.となります。
グラフを管理するワークシートを作るとスムーズにいきます。2.のデータ系列の取得･表示も、貼りつけるセルを指定(シートの移動と同じコマンドを使う)するようにしましたので、任意性も出てきました。
グラフの順番を変えるのも、データ系列をソートして、その結果を4.でグラフに返してやれば、意図した順番になります。(他のグラフでの表示とあわせるためなど)
作業してみて、どこのセルを指定したらいいのかの指示があるといいと思って、そのセルを色付けしました。また、見出しも作っていますので、わかりやすくなっています。
管理表は、一括新規作成用のエリア、タイトル修正用のエリア、データ系列のエリア(20系列ほど確保)となっています。また、表札名(凡例に表示されるもの)も、データ系列にあわせて表示することにしました。
どんどんわかりやすくなっています。
後は図形(オートシェイプ)の研究をして、コメントを追加することでしょうか。位置の問題があるので、大体の場所を指定するができることを考えています。
同様に"ちょっとした小物編"で取り上げたものを整理しました。
なお、最後の小技関係は取り上げていないものも含めています。
メイン；印刷作成
サブ；
1.一気にシート印刷(ダイアログの表示を省略)
＝⇒ほとんど使用価値なし。Ctrl＋ｐ、Enterのほうが早い。
2.指定範囲の印刷(設定した改行マークをまたいで指定するとページが分かれる点を、改善すべき)
3.ページ指定の印刷(シート内。カラープリンタの設定も可能)
4.複数シートの印刷(現在のシートから複数シート設定。カラープリンタの設定も可能))
5.自分好みのヘッダ・フッタのセット
6.印刷ダイアログの表示
メイン；罫線作成
サブ；
1.全部引く
2.五飛びに横罫線を引く(飛びは指定可)
3.細線の横罫線を引く
4.全部消す
5.罫線ダイアログの表示
メイン；ジャンプ関係
サブ；
1.シート内にジャンプ(セルを指定)
2.シート外にジャンプ(セルも指定可)
単 独；コピー関係
1.計算式のみコピー(シートの指定は不可)
2.数値のみコピー(シートの指定は不可)
メイン；コピー関係
サブ；
1.計算式のみコピー(シートの指定も可)
2.数値のみコピー(シートの指定も可)
3.シート内で複数回のコピー(コピー先が複数に渡って規則正しい場合)
4.シート外の複数回コピー(コピー先が複数シートに渡って場所が同じ場合)
メイン；計算式文字化関係
サブ；
1.計算式を文字に(頭に特殊文字列を付加)
2.文字を計算式に(頭の特殊文字列を削除)
3.計算式を文字にしてデータのみの貼り付け
メイン；セルの結合関係
サブ；
1.セルの結合
2.セルの結合の解除
メイン；小技マクロ
サブ；
1.連番入力(範囲内に連番を作成)
2.複数文字列の置き換え
3.シート名の取得(ファイル内の全シート名を取得)
4.数式セル保護
5.端にヌル式セット
6.端にsum関数セット
7.指定範囲の座標を貼り付け(範囲を指定したA1形式の座標を貼り付ける)
8.列記号の生み出し(連続する列記号を作成)
9.セルの階段状挿入
メイン；小技^2
サブ；
1.指定範囲の数値のみを100倍
2.指定範囲の数値のみを1/100
3.指定範囲の数値のみをクリア
4.指定範囲の特定文字列を含むセルのクリア
グラフ関係のマクロの整理をしましょう。
グラフ管理表は次のようになっています。
グラフ関係のマクロはサブルーティン化されて次のようになっています。
メイン；グラフ作成
サブ；
1.グラフ新規作成(一括作成)
2.グラフからデータ系列の取得(表示も含む)
3.グラフの追加(ワークシートからのデータ系列の取得から)
4.グラフデータ範囲の修正(ワークシートからのデータ系列の取得から)
5.グラフタイトル等の修正(ワークシートからのタイトル等の取得から)
となっています。
このシステムは、2を中心に動きます。これによりワークシート上に、既存のグラフのデータ系列が表示されるからです。これを加工したあと、グラフに反映させるのは、4.となります。
また、このデータ系列をもとにして、新たなデータセットを作って、グラフに追加するのは、3.となります。
グラフを管理するワークシートを作るとスムーズにいきます。2.のデータ系列の取得･表示も、貼りつけるセルを指定(シートの移動と同じコマンドを使う)するようにしましたので、任意性も出てきました。
グラフの順番を変えるのも、データ系列をソートして、その結果を4.でグラフに返してやれば、意図した順番になります。(他のグラフでの表示とあわせるためなど)
作業してみて、どこのセルを指定したらいいのかの指示があるといいと思って、そのセルを色付けしました。また、見出しも作っていますので、わかりやすくなっています。
管理表は、一括新規作成用のエリア、タイトル修正用のエリア、データ系列のエリア(20系列ほど確保)となっています。また、表札名(凡例に表示されるもの)も、データ系列にあわせて表示することにしました。
どんどんわかりやすくなっています。
後は図形(オートシェイプ)の研究をして、コメントを追加することでしょうか。位置の問題があるので、大体の場所を指定するができることを考えています。
同様に"ちょっとした小物編"で取り上げたものを整理しました。
なお、最後の小技関係は取り上げていないものも含めています。
メイン；印刷作成
サブ；
1.一気にシート印刷(ダイアログの表示を省略)
＝⇒ほとんど使用価値なし。Ctrl＋ｐ、Enterのほうが早い。
2.指定範囲の印刷(設定した改行マークをまたいで指定するとページが分かれる点を、改善すべき)
3.ページ指定の印刷(シート内。カラープリンタの設定も可能)
4.複数シートの印刷(現在のシートから複数シート設定。カラープリンタの設定も可能))
5.自分好みのヘッダ・フッタのセット
6.印刷ダイアログの表示
メイン；罫線作成
サブ；
1.全部引く
2.五飛びに横罫線を引く(飛びは指定可)
3.細線の横罫線を引く
4.全部消す
5.罫線ダイアログの表示
メイン；ジャンプ関係
サブ；
1.シート内にジャンプ(セルを指定)
2.シート外にジャンプ(セルも指定可)
単 独；コピー関係
1.計算式のみコピー(シートの指定は不可)
2.数値のみコピー(シートの指定は不可)
メイン；コピー関係
サブ；
1.計算式のみコピー(シートの指定も可)
2.数値のみコピー(シートの指定も可)
3.シート内で複数回のコピー(コピー先が複数に渡って規則正しい場合)
4.シート外の複数回コピー(コピー先が複数シートに渡って場所が同じ場合)
メイン；計算式文字化関係
サブ；
1.計算式を文字に(頭に特殊文字列を付加)
2.文字を計算式に(頭の特殊文字列を削除)
3.計算式を文字にしてデータのみの貼り付け
メイン；セルの結合関係
サブ；
1.セルの結合
2.セルの結合の解除
メイン；小技マクロ
サブ；
1.連番入力(範囲内に連番を作成)
2.複数文字列の置き換え
3.シート名の取得(ファイル内の全シート名を取得)
4.数式セル保護
5.端にヌル式セット
6.端にsum関数セット
7.指定範囲の座標を貼り付け(範囲を指定したA1形式の座標を貼り付ける)
8.列記号の生み出し(連続する列記号を作成)
9.セルの階段状挿入
メイン；小技^2
サブ；
1.指定範囲の数値のみを100倍
2.指定範囲の数値のみを1/100
3.指定範囲の数値のみをクリア
4.指定範囲の特定文字列を含むセルのクリア
2007年11月17日土曜日
第60回目 グラフ関係(その6;グラフのタイトル等を修正する及びまとめ)
○第60回目 グラフ関係(その6;グラフのタイトル等を修正する及びまとめ)
ここで修正の対象となるのは、タイトル、最小値、最大値、目盛りの間隔、目盛りの表示桁数(これだけは文字で与えてください)です。
考え方も簡単になります。すべてが既存のものの利用となります。
流れは、
0.ワークシートでタイトル等を入力する
1.ワークシートのタイトル等を取得する
2.グラフシートへの移動
3.タイトル等の修正
1.での最初にセットするカーソルの位置は、タイトルが記入されているセルです。そのセルから下に最小値等を記入しておいてください。
セル座標の取得からセルの値の取得ですので、今まで何回もやってきたものです。
2.シートの移動も何回か出てきました。直前にも出てきています。
3.タイトル等の修正は、タイトル等を保管している特殊な場所に、取得した値を入れればいいのです。
その部分は、
'タイトルの修正
ActiveChart.ChartTitle.Text = g_title
'目盛等の修正
ActiveChart.Axes(xlValue).MinimumScale = y_min
ActiveChart.Axes(xlValue).MaximumScale = y_max
ActiveChart.Axes(xlValue).MajorUnit = y_memori
ActiveChart.Axes(xlValue).TickLabels.NumberFormatLocal = y_keta
となります。(第41回"緊急講座グラフ編"の後半のところからの抜き出しです)
ということで、何の苦もなくできてしまいました。
グラフの線を太くするのは、
ActiveChart.SeriesCollection(i).Select
With Selection.Border
.ColorIndex = 57 'この記述は不要です
.Weight = xlMedium '←太くなります。普通はxlthin
.LineStyle = xlContinuous 'この記述は不要です
End With
線だけであれば、
ActiveChart.SeriesCollection(i).Border.Weight = xlMedium
だけでいいことになります。
ケーススタディ別にやり方をまとめてみますと、
1.グラフを作らなくては…。＝⇒第41回の新規一括作成
2.タイトルとか目盛りが気に入らないので直したい＝⇒第60回(この回)
3.これはこれでいいが、新しいグラフ(データ系列)を追加したい＝⇒第58回により、グラフのデータ系列の情報をとり、これをまねて新しいデータ系列をつくる。第57回により、グラフを追加する
4.ちょっと範囲が違ったよ、1年追加しなければならなくなった。＝⇒第58回により、グラフのデータ系列の情報をとり、これを修正して正しいデータ系列をつくる。第59回により、グラフを修正する。
5.ほかのファイルからグラフシートを張り付けたが、データ系列の参照先は前のままだ、これを直したい。＝⇒同上
6.グラフ(のデータ系列)を一つ削除したい＝⇒手作業で削除。グラフ上データ系列番号がわからないので、手作業で行なうのが無難です。
7.縦軸の標識(例えば"金額"とか)、横軸の標識説明(例えば、"年度"とか)を付けたい＝⇒操作の記録でマクロをとり、自分でマクロを組んでください。(タイトルレベルのものなので簡単です)
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "たいとるタイトル"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "年度"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "金額"
End With
8.10個グラフを追加したいのだが、データ系列のパラメータを作るのが面倒＝⇒その通りです。例えば既存で4個グラフがあったとします。そのグラフのデータ系列の情報の取得は必ず行なうはずです。
それを前提に。
既存のデータ系列の情報を取得します。
既存のデータ系列の情報を下に3セット(10個以上にするため)コピーします。4行、あき行、15行となります。
既存のデータ系列が入った隣の2列に、修正前と後の列を入力します。
(新しいデータ系列は、ほとんどの場合、既存のものの列を置き換えるだけでいいと思われます)
既存のもの…データ系列としてH,I,J,K列を使用。
新しいもの…データ系列としてS,T,U,W列を使用したい。
H⇒S、I⇒T、J⇒U、K⇒Wに置き換える。
4行単位で、複数個の置換えを行います。これはマクロです。
(既存のデータ系列での列が四種類しかないため、一気にはできません)
3回繰り返すと、お望みのデータ系列のパラメータができます。
この"複数個置換えマクロ"は、すでに、"第54回目 ちょっとした小物、その他編"で紹介しておいたような気がしたのですが…、
紹介していませんでした。どこかで紹介していなかったか…
ありました、モドキがありました。第20回の後半に本題のマクロをフォローするマクロとして、複数個置換えマクロがありました。
今回の場合は、置換え前後の文字列は、セルに入力されていますので、範囲を取得して、置換え前後の文字列をセットすればいいと思います。
セル範囲を取得して、セルの内容を変数にセットする、よく使われる機能です。
これを第20回のマクロの頭につければいいでしょう。
修正するのは表札とデータ値の座標ですので、1行で3箇所、4行で12箇所。マクロの方がはるかに早いと思います。
何を書いていたのか忘れてしまいました。
グラフ編のケーススタディでした。
9.グラフにコメントを入れてくれ＝⇒現状では手作業となりますが、分析してみる価値がありますね。
10.精査したら数字が変わったって…。新しいファイルが送られてきた。これでグラフを作り直してくれってことだ＝⇒既存のファイルからグラフシートをコピーします。あとは5.と同じです。
11.複合グラフ(目盛りが二つあるもの)はできないか＝⇒業務上ニーズがあったら考えます。
12.折れ線グラフ以外のグラフも作るんだが…＝⇒折れ線グラフでまずつくって、適当なところで手作業でグラフの種類を変換する。100%の対応ではないですが…。
13.0から始まらないようなグラフがあるんだが＝⇒最小値の設定の仕方で対応できます。
14.例えば五年ごとに縦線が引けないか＝⇒エクセルの機能そのものにないようです。一定の間隔での縦線はそれほどの必要はないと思いますが、特定のところに線を引くというニーズはあるかもしれません。その場合は図形ということになりますので、9.のコメントと同じとなります。
15.階段型グラフが出来ないか＝⇒昔(5年前)そのことを仲間にいったところ、そんなことは出来ませんということでした。モドキでも出来ないのかといったところ、かなりの剣幕で出来ないの一点張りでした。少し考えたらアイデアが出たのでそれをいったら沈黙に包まれました。それは折れ線グラフを工夫して使ったものでした。
しかしながら、その当時でもできる方法はあったようです。ピボットグラフでできるのです。ということで、ピボットグラフには関心があるので、ピボットグラフを研究したいと思います。
16.データ系列のソートできないか＝⇒折れ線グラフで何のメリットがあるのでしょうか。不明です。なんかしらで順番が変わってしまった複数のグラフのデータ系列をそろえるというニーズはありますね。確かに。そのときは、グラフデータ系列の取得し、ワークシート上で順番を変えて(統一して)、それでグラフを修正するというのではどうでしょうか。既存の機能で大丈夫です。ソートの仕方はソートキーを追加すれば、既存のエクセル機能で簡単です。
17.グラフを使って何か図がかけないか＝⇒趣旨が意味不明です。階段状のグラフも同じようなことが含まれているのかもしれません。マクロでデータ系列の数値を動かして、グラフを変化させるなども含まれると見ます。中期的な研究課題ですね。面白いところがあるかもしれないという感じがしますが。
18.グラフシートでグラフを越えた書類が出来ないか＝⇒かなりマニアックになってきました。頭がどうなっているのかと聞きたい感じがします。確かに今まではグラフにコメントをつける程度でしたが、文書の中にグラフがある、それじゃワークシートにグラフを張り付ければそうなってしまう。意味不明ながら何かがあるかもしれません。頭にしまっておきます。
19.グラフ管理用のワークシートができないか＝⇒できると思います。一ファイルに多くのグラフが存在すると、管理のしがいがあるでしょうね。
20.グラフのデータ系列の取得で、表札は座標だけでなく、内容も表示すべきではないか＝⇒表札があれば、わかりやすくなりますので、19.とあわせて取り入れてみます。
ここで修正の対象となるのは、タイトル、最小値、最大値、目盛りの間隔、目盛りの表示桁数(これだけは文字で与えてください)です。
考え方も簡単になります。すべてが既存のものの利用となります。
流れは、
0.ワークシートでタイトル等を入力する
1.ワークシートのタイトル等を取得する
2.グラフシートへの移動
3.タイトル等の修正
1.での最初にセットするカーソルの位置は、タイトルが記入されているセルです。そのセルから下に最小値等を記入しておいてください。
セル座標の取得からセルの値の取得ですので、今まで何回もやってきたものです。
2.シートの移動も何回か出てきました。直前にも出てきています。
3.タイトル等の修正は、タイトル等を保管している特殊な場所に、取得した値を入れればいいのです。
その部分は、
'タイトルの修正
ActiveChart.ChartTitle.Text = g_title
'目盛等の修正
ActiveChart.Axes(xlValue).MinimumScale = y_min
ActiveChart.Axes(xlValue).MaximumScale = y_max
ActiveChart.Axes(xlValue).MajorUnit = y_memori
ActiveChart.Axes(xlValue).TickLabels.NumberFormatLocal = y_keta
となります。(第41回"緊急講座グラフ編"の後半のところからの抜き出しです)
ということで、何の苦もなくできてしまいました。
グラフの線を太くするのは、
ActiveChart.SeriesCollection(i).Select
With Selection.Border
.ColorIndex = 57 'この記述は不要です
.Weight = xlMedium '←太くなります。普通はxlthin
.LineStyle = xlContinuous 'この記述は不要です
End With
線だけであれば、
ActiveChart.SeriesCollection(i).Border.Weight = xlMedium
だけでいいことになります。
ケーススタディ別にやり方をまとめてみますと、
1.グラフを作らなくては…。＝⇒第41回の新規一括作成
2.タイトルとか目盛りが気に入らないので直したい＝⇒第60回(この回)
3.これはこれでいいが、新しいグラフ(データ系列)を追加したい＝⇒第58回により、グラフのデータ系列の情報をとり、これをまねて新しいデータ系列をつくる。第57回により、グラフを追加する
4.ちょっと範囲が違ったよ、1年追加しなければならなくなった。＝⇒第58回により、グラフのデータ系列の情報をとり、これを修正して正しいデータ系列をつくる。第59回により、グラフを修正する。
5.ほかのファイルからグラフシートを張り付けたが、データ系列の参照先は前のままだ、これを直したい。＝⇒同上
6.グラフ(のデータ系列)を一つ削除したい＝⇒手作業で削除。グラフ上データ系列番号がわからないので、手作業で行なうのが無難です。
7.縦軸の標識(例えば"金額"とか)、横軸の標識説明(例えば、"年度"とか)を付けたい＝⇒操作の記録でマクロをとり、自分でマクロを組んでください。(タイトルレベルのものなので簡単です)
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "たいとるタイトル"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "年度"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "金額"
End With
8.10個グラフを追加したいのだが、データ系列のパラメータを作るのが面倒＝⇒その通りです。例えば既存で4個グラフがあったとします。そのグラフのデータ系列の情報の取得は必ず行なうはずです。
それを前提に。
既存のデータ系列の情報を取得します。
既存のデータ系列の情報を下に3セット(10個以上にするため)コピーします。4行、あき行、15行となります。
既存のデータ系列が入った隣の2列に、修正前と後の列を入力します。
(新しいデータ系列は、ほとんどの場合、既存のものの列を置き換えるだけでいいと思われます)
既存のもの…データ系列としてH,I,J,K列を使用。
新しいもの…データ系列としてS,T,U,W列を使用したい。
H⇒S、I⇒T、J⇒U、K⇒Wに置き換える。
4行単位で、複数個の置換えを行います。これはマクロです。
(既存のデータ系列での列が四種類しかないため、一気にはできません)
3回繰り返すと、お望みのデータ系列のパラメータができます。
この"複数個置換えマクロ"は、すでに、"第54回目 ちょっとした小物、その他編"で紹介しておいたような気がしたのですが…、
紹介していませんでした。どこかで紹介していなかったか…
ありました、モドキがありました。第20回の後半に本題のマクロをフォローするマクロとして、複数個置換えマクロがありました。
今回の場合は、置換え前後の文字列は、セルに入力されていますので、範囲を取得して、置換え前後の文字列をセットすればいいと思います。
セル範囲を取得して、セルの内容を変数にセットする、よく使われる機能です。
これを第20回のマクロの頭につければいいでしょう。
修正するのは表札とデータ値の座標ですので、1行で3箇所、4行で12箇所。マクロの方がはるかに早いと思います。
何を書いていたのか忘れてしまいました。
グラフ編のケーススタディでした。
9.グラフにコメントを入れてくれ＝⇒現状では手作業となりますが、分析してみる価値がありますね。
10.精査したら数字が変わったって…。新しいファイルが送られてきた。これでグラフを作り直してくれってことだ＝⇒既存のファイルからグラフシートをコピーします。あとは5.と同じです。
11.複合グラフ(目盛りが二つあるもの)はできないか＝⇒業務上ニーズがあったら考えます。
12.折れ線グラフ以外のグラフも作るんだが…＝⇒折れ線グラフでまずつくって、適当なところで手作業でグラフの種類を変換する。100%の対応ではないですが…。
13.0から始まらないようなグラフがあるんだが＝⇒最小値の設定の仕方で対応できます。
14.例えば五年ごとに縦線が引けないか＝⇒エクセルの機能そのものにないようです。一定の間隔での縦線はそれほどの必要はないと思いますが、特定のところに線を引くというニーズはあるかもしれません。その場合は図形ということになりますので、9.のコメントと同じとなります。
15.階段型グラフが出来ないか＝⇒昔(5年前)そのことを仲間にいったところ、そんなことは出来ませんということでした。モドキでも出来ないのかといったところ、かなりの剣幕で出来ないの一点張りでした。少し考えたらアイデアが出たのでそれをいったら沈黙に包まれました。それは折れ線グラフを工夫して使ったものでした。
しかしながら、その当時でもできる方法はあったようです。ピボットグラフでできるのです。ということで、ピボットグラフには関心があるので、ピボットグラフを研究したいと思います。
16.データ系列のソートできないか＝⇒折れ線グラフで何のメリットがあるのでしょうか。不明です。なんかしらで順番が変わってしまった複数のグラフのデータ系列をそろえるというニーズはありますね。確かに。そのときは、グラフデータ系列の取得し、ワークシート上で順番を変えて(統一して)、それでグラフを修正するというのではどうでしょうか。既存の機能で大丈夫です。ソートの仕方はソートキーを追加すれば、既存のエクセル機能で簡単です。
17.グラフを使って何か図がかけないか＝⇒趣旨が意味不明です。階段状のグラフも同じようなことが含まれているのかもしれません。マクロでデータ系列の数値を動かして、グラフを変化させるなども含まれると見ます。中期的な研究課題ですね。面白いところがあるかもしれないという感じがしますが。
18.グラフシートでグラフを越えた書類が出来ないか＝⇒かなりマニアックになってきました。頭がどうなっているのかと聞きたい感じがします。確かに今まではグラフにコメントをつける程度でしたが、文書の中にグラフがある、それじゃワークシートにグラフを張り付ければそうなってしまう。意味不明ながら何かがあるかもしれません。頭にしまっておきます。
19.グラフ管理用のワークシートができないか＝⇒できると思います。一ファイルに多くのグラフが存在すると、管理のしがいがあるでしょうね。
20.グラフのデータ系列の取得で、表札は座標だけでなく、内容も表示すべきではないか＝⇒表札があれば、わかりやすくなりますので、19.とあわせて取り入れてみます。
2007年11月14日水曜日
第59回目 グラフ関係(その5;グラフのデータ系列を変更する)
○第59回目 グラフ関係(その5;グラフのデータ系列を変更する)
データ系列の変更は簡単です。
これまでやってきたことからのちょっとした改良です。
実際の処理はこうなります。
第57回のマクロで、既存のグラフのデータ系列をワークシートにとります。
そこで必要な修正をします。
そして、データ系列の範囲を指定してから始まります。データ系列はすべて指定してください。
グラフは番号によって区分されています。その番号のデータ系列を新しいものに置き換えてやればいいことになります。
グラフの追加と同じなのですが、追加の場合は、既存のグラフ番号の次からはじめますが、修正ですので、1からはじめ、既存の番号のところまで行います。
第57回で。グラフを追加するメインの処理がありましたが、
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(g_cnt + i).ChartType = xlLine
ActiveChart.SeriesCollection(g_cnt + i).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(g_cnt + i).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(g_cnt + i).Values = "=Sheet2!R29C5:R48C5"
このうち、後半の三行を生かしてもらい、( )内を"i"だけに。、内容を取得した変数にします。
そして、2007ように一文にします
ActiveChart.SeriesCollection.item(i).Formula = _
"=SERIES(" & x_sheet(i) & "!" & x_name(i) & "," & _
x_sheet(i) & "!" & x_midashi(i) & "," & _
x_sheet(i) & "!" & XX(i) & "," & Str(g_cnt + i) & ")"
(注)エクセル2007では、座標はA1方式で大丈夫です。
ほとんど、グラフの追加と同じになります。
また、考え方を変えて、グラフ(のデータ系列)を全部削除してから、追加でもいいと思います。
削除は、マクロの記録でとると、次のようになります
ActiveChart.SeriesCollection(3).Select
Selection.Delete
一般化して、
ActiveChart.SeriesCollection(i).Delete
でいいでしょう。
データ系列の変更は簡単です。
これまでやってきたことからのちょっとした改良です。
実際の処理はこうなります。
第57回のマクロで、既存のグラフのデータ系列をワークシートにとります。
そこで必要な修正をします。
そして、データ系列の範囲を指定してから始まります。データ系列はすべて指定してください。
グラフは番号によって区分されています。その番号のデータ系列を新しいものに置き換えてやればいいことになります。
グラフの追加と同じなのですが、追加の場合は、既存のグラフ番号の次からはじめますが、修正ですので、1からはじめ、既存の番号のところまで行います。
第57回で。グラフを追加するメインの処理がありましたが、
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(g_cnt + i).ChartType = xlLine
ActiveChart.SeriesCollection(g_cnt + i).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(g_cnt + i).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(g_cnt + i).Values = "=Sheet2!R29C5:R48C5"
このうち、後半の三行を生かしてもらい、( )内を"i"だけに。、内容を取得した変数にします。
そして、2007ように一文にします
ActiveChart.SeriesCollection.item(i).Formula = _
"=SERIES(" & x_sheet(i) & "!" & x_name(i) & "," & _
x_sheet(i) & "!" & x_midashi(i) & "," & _
x_sheet(i) & "!" & XX(i) & "," & Str(g_cnt + i) & ")"
(注)エクセル2007では、座標はA1方式で大丈夫です。
ほとんど、グラフの追加と同じになります。
また、考え方を変えて、グラフ(のデータ系列)を全部削除してから、追加でもいいと思います。
削除は、マクロの記録でとると、次のようになります
ActiveChart.SeriesCollection(3).Select
Selection.Delete
一般化して、
ActiveChart.SeriesCollection(i).Delete
でいいでしょう。
2007年11月10日土曜日
第58回目 グラフ関係(その4;グラフのデータ系列を取る)
○第58回目 グラフ関係(その4;グラフのデータ系列を取る)
今回は、グラフからデータ系列の情報を取り出しましょう。
データ系列の情報は4つで、シート名、表札の座標、横軸の座標、データ値の座標のことです。
これはそれほど難しくないのですが、コマンドが何を使うのかがわかりません。
いろいろ調べてみた結果、
ActiveChart.SeriesCollection.item(i).Formula
を使うことがわかりました。
Dim d_graph(30)
With ActiveChart.SeriesCollection
xx_suu = .Count 'グラフの数です
For i = 1 To xx_suu
d_graph(i) = .item(i).Formula
MsgBox "系列" & i & "のソースデータ範囲は ⇒" _
& vbCrLf & d_graph(i) '確認用
Next i
End With
を動かして、データ系列の情報がどうなっているかみてください。
=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)
となっているので、これを分解し、データ系列の4項目を求めます。
ttt = "=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)"
として、分解してみます。
p1=instr(ttt,"(")
p9=instr(ttt,"!")
x_sheet(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1) '使った部分は取り除いていきます
p1=instr(ttt,",")
x_name(i)=left(ttt,p1-1)
ttt=mid(ttt,p1+1)
p1=instr(ttt,"!")
p9=instr(ttt,",")
x_midashi(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1)
p1=instr(ttt,"!")
p9=instr(ttt,",")
XX(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1)
p1=instr(ttt,")")
g_no(i)=left(ttt,p1-1)
これをfor toでxx_suu繰り返し情報をとります。
次にこの情報をワークシートに書き込みます。
シート移動の機能でシートを最寄のワークシートに移してください。
そのシートの特別な場所に書き込みます。グラフ管理表が作成されているので、その場所に書き込みました。F5から書き込みます。
for i=1 to xx_suu
cells(lr004+i,lc005) = "=" & x_sheet(i) & "!" & x_name(i) '計算式グラフの表札
cells(lr004+i,lc005+1)=x_sheet(i)
cells(lr004+i,lc005+2)=x_name(i)
cells(lr004+i,lc005+3)=x_midashi(i)
cells(lr004+i,lc005+4)=XX(i)
next i
このデータ系列の情報を活用して、新しいデータ系列の情報を作ってみてください。それを前回のマクロでグラフに追加してみてください。
今回は、グラフからデータ系列の情報を取り出しましょう。
データ系列の情報は4つで、シート名、表札の座標、横軸の座標、データ値の座標のことです。
これはそれほど難しくないのですが、コマンドが何を使うのかがわかりません。
いろいろ調べてみた結果、
ActiveChart.SeriesCollection.item(i).Formula
を使うことがわかりました。
Dim d_graph(30)
With ActiveChart.SeriesCollection
xx_suu = .Count 'グラフの数です
For i = 1 To xx_suu
d_graph(i) = .item(i).Formula
MsgBox "系列" & i & "のソースデータ範囲は ⇒" _
& vbCrLf & d_graph(i) '確認用
Next i
End With
を動かして、データ系列の情報がどうなっているかみてください。
=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)
となっているので、これを分解し、データ系列の4項目を求めます。
ttt = "=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)"
として、分解してみます。
p1=instr(ttt,"(")
p9=instr(ttt,"!")
x_sheet(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1) '使った部分は取り除いていきます
p1=instr(ttt,",")
x_name(i)=left(ttt,p1-1)
ttt=mid(ttt,p1+1)
p1=instr(ttt,"!")
p9=instr(ttt,",")
x_midashi(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1)
p1=instr(ttt,"!")
p9=instr(ttt,",")
XX(i)=mid(ttt,p1+1,p9-p1-1)
ttt=mid(ttt,p9+1)
p1=instr(ttt,")")
g_no(i)=left(ttt,p1-1)
これをfor toでxx_suu繰り返し情報をとります。
次にこの情報をワークシートに書き込みます。
シート移動の機能でシートを最寄のワークシートに移してください。
そのシートの特別な場所に書き込みます。グラフ管理表が作成されているので、その場所に書き込みました。F5から書き込みます。
for i=1 to xx_suu
cells(lr004+i,lc005) = "=" & x_sheet(i) & "!" & x_name(i) '計算式グラフの表札
cells(lr004+i,lc005+1)=x_sheet(i)
cells(lr004+i,lc005+2)=x_name(i)
cells(lr004+i,lc005+3)=x_midashi(i)
cells(lr004+i,lc005+4)=XX(i)
next i
このデータ系列の情報を活用して、新しいデータ系列の情報を作ってみてください。それを前回のマクロでグラフに追加してみてください。
2007年11月7日水曜日
第57回目 グラフ関係(その3；グラフの追加)
○第57回目 グラフ関係(その3；グラフの追加)
エクセル2003でグラフの操作を行なって、マクロの記録を取ってみました。
【グラフを追加する場合】
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R29C5:R48C5"
前提としてはすでに一本のグラフがあり、二本目を追加した場合です。"(2)"はそういうことを表します。
このイコールの後に、もとにしたい座標を変数で与えればいいことになります。困ったことは座標の表示が、RC方式になっていることです。
面白い関数がありました。
"B10:B15"を与えると、"R10C2:R15C2"に変換してくれるのです。
Sub A1_R1C1_7()
inputFormula = "B10:B15"
outff = Application.ConvertFormula(Formula:=inputFormula, _
fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1,ToAbsolute:=xlAbsolute)
MsgBox outff
End Sub
これを使えばいいですね。
これを前提に組み立ててみましょう。
パラメータとしては、シート名、表札の座標、横軸の座標、データ値の座標となります。この4項目が1セットとなります。必要なだけ作ってください。
これをセルに設定します。セットが変わると次の行となります。
パラメータの範囲を設定してマクロを動かします。
1.パラメータの取得(追加するグラフ数も求めておきます)
2.グラフシートへの移動
3.既存のグラフ数の取得(g_cnt = ActiveChart.SeriesCollection.Count)
4.座標を加工しグラフの追加
変数として、
x_sheet(30), x_name(30), x_midashi(30), XX(30)
⇒シート名、表札・横軸・データ値の座標
xx_suu
⇒追加するグラフ数
を設定します。
【補足説明】
1.について
よく出てくる"セル範囲の取得"の機能です。そのうちの行数が追加グラフの数となります。パラメータを配列変数に入れるのは順番さえ間違わなければ簡単です。（3つの場合の範囲の例示です）
2.について
シートの移動を使います。次の基本機能を参照してください。グラフシートとワークシートは近くにおいてください。デフォルト値ではグラフシートはワークシートの次となっています。
ii_max = ActiveWorkbook.sheets.Count
ii0 = ActiveSheet.Index
'シートの移動(シートは相対位置で指定します)
sh_cnt = InputBox("新しいシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=1, xpos:=2000, ypos:=3000)
Sheets(ii0 + sh_cnt).Select
3.について
なし
4.について
追加するグラフの数だけ繰り返してください。
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(g_cnt + i).ChartType = xlLine
ActiveChart.SeriesCollection(g_cnt + i).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(g_cnt + i).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(g_cnt + i).Values = "=Sheet2!R29C5:R48C5"
例えば、"=Sheet2!R28C5"の作り方は、
x_name(i) = Application.ConvertFormula(Formula:=x_name(i), _
fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1,ToAbsolute:=xlAbsolute)
として
"=" & x_sheet(i) & "!" & x_name(i)
とすればいいのです。
エクセル2007でも以上のことで大丈夫だと思うのですが、表札の座標ではなく値が入ってしまうため、後半の三行を次のようにしています。
'=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)の形式を再現
ActiveChart.SeriesCollection.item(g_cnt + i).Formula = _
"=SERIES(" & x_sheet(i) & "!" & x_name(i) & "," & _
x_sheet(i) & "!" & x_midashi(i) & "," & _
x_sheet(i) & "!" & XX(i) & "," & Str(g_cnt + i) & ")"
(注)エクセル2007では、座標はA1方式で大丈夫です。
エクセル2003でグラフの操作を行なって、マクロの記録を取ってみました。
【グラフを追加する場合】
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R29C5:R48C5"
前提としてはすでに一本のグラフがあり、二本目を追加した場合です。"(2)"はそういうことを表します。
このイコールの後に、もとにしたい座標を変数で与えればいいことになります。困ったことは座標の表示が、RC方式になっていることです。
面白い関数がありました。
"B10:B15"を与えると、"R10C2:R15C2"に変換してくれるのです。
Sub A1_R1C1_7()
inputFormula = "B10:B15"
outff = Application.ConvertFormula(Formula:=inputFormula, _
fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1,ToAbsolute:=xlAbsolute)
MsgBox outff
End Sub
これを使えばいいですね。
これを前提に組み立ててみましょう。
パラメータとしては、シート名、表札の座標、横軸の座標、データ値の座標となります。この4項目が1セットとなります。必要なだけ作ってください。
これをセルに設定します。セットが変わると次の行となります。
パラメータの範囲を設定してマクロを動かします。
1.パラメータの取得(追加するグラフ数も求めておきます)
2.グラフシートへの移動
3.既存のグラフ数の取得(g_cnt = ActiveChart.SeriesCollection.Count)
4.座標を加工しグラフの追加
変数として、
x_sheet(30), x_name(30), x_midashi(30), XX(30)
⇒シート名、表札・横軸・データ値の座標
xx_suu
⇒追加するグラフ数
を設定します。
【補足説明】
1.について
よく出てくる"セル範囲の取得"の機能です。そのうちの行数が追加グラフの数となります。パラメータを配列変数に入れるのは順番さえ間違わなければ簡単です。（3つの場合の範囲の例示です）
2.について
シートの移動を使います。次の基本機能を参照してください。グラフシートとワークシートは近くにおいてください。デフォルト値ではグラフシートはワークシートの次となっています。
ii_max = ActiveWorkbook.sheets.Count
ii0 = ActiveSheet.Index
'シートの移動(シートは相対位置で指定します)
sh_cnt = InputBox("新しいシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=1, xpos:=2000, ypos:=3000)
Sheets(ii0 + sh_cnt).Select
3.について
なし
4.について
追加するグラフの数だけ繰り返してください。
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(g_cnt + i).ChartType = xlLine
ActiveChart.SeriesCollection(g_cnt + i).Name = "=Sheet2!R28C5"
ActiveChart.SeriesCollection(g_cnt + i).XValues = "=Sheet2!R29C3:R48C3"
ActiveChart.SeriesCollection(g_cnt + i).Values = "=Sheet2!R29C5:R48C5"
例えば、"=Sheet2!R28C5"の作り方は、
x_name(i) = Application.ConvertFormula(Formula:=x_name(i), _
fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1,ToAbsolute:=xlAbsolute)
として
"=" & x_sheet(i) & "!" & x_name(i)
とすればいいのです。
エクセル2007でも以上のことで大丈夫だと思うのですが、表札の座標ではなく値が入ってしまうため、後半の三行を次のようにしています。
'=SERIES(y=ax2+bx+c!$B$1,y=ax2+bx+c!$A$2:$A$98,y=ax2+bx+c!$B$2:$B$98,1)の形式を再現
ActiveChart.SeriesCollection.item(g_cnt + i).Formula = _
"=SERIES(" & x_sheet(i) & "!" & x_name(i) & "," & _
x_sheet(i) & "!" & x_midashi(i) & "," & _
x_sheet(i) & "!" & XX(i) & "," & Str(g_cnt + i) & ")"
(注)エクセル2007では、座標はA1方式で大丈夫です。
2007年11月3日土曜日
第56回目 グラフ関係(その2)
○第56回目 グラフ関係(その2)
とうとうグラフです。グラフはめんどくさいです。あれこれと細かに設定しなければ出来ないので、筆者などは二の足を踏んでしまいます。
グラフ関係でマクロ操作により7割ぐらいカバーできるものが出来ないのか、という思いを強く持ちます。私どものところは時系列的な変化を取ることが多いので、主に折れ線グラフとなります。
それをベースに何とかマクロでできないものだろうか…。
という問題意識が、ある時ほぼ解決されました、というより、その糸口が見えてきました。
第41回を"その1"としますので、今回は"その2"となります。
グラフ作成のパラメータをつくりそれを元にグラフを作る･更新するという機能をとりあげます。
さて、基本的な機能として何が必要でしょうか。その分析で60%完了です。
1.グラフに新しい系列のデータを追加する。
2.グラフのデータ系列を違う系列に変更する。
※データ系列とは、データの表札(凡例に出てくるもの)、横軸の数値、データ値となります。
3.グラフの属性(タイトル、目盛りなど)を簡単に修正する。
4.グラフの削除(削除はクリックしdeleteキーで可能なのでマクロでは面白くないかもしれません)
こんな例がありました。すでにあるグラフが出来ています(データ系列の参照は同一ファイル)。違う前提条件で求められたデータ系列が別ファイルにあります。ファイル内の体系は既存のファイルと同じです。このデータ系列で同じグラフを作りたい(データ系列を置き換えたい)。既存のグラフと比べるとデータ系列のファイル名のみが違うだけです。
(グラフ込みのファイルをコピーして、それを元に違う前提条件で計算しなおせば、改めてグラフを作る必要がないという意見があると思いますが、作者が違う時は、データ系列があるそれぞれのファイルにグラフを挿入する必要性が出てきます。そういったケースを想定しました)
これを次のようにやっていました。
1.グラフシートを新しいファイルにコピーする。(この段階で、データ系列以外は、条件を満たします。データ系列は元のファイルで参照されているもののままです)
2.グラフを一つ一つクリックして、グラフのデータ系列指定のタブを出し、データ系列の表札、横軸数値、データ値の指定座標から、ファイル名を削除する(ファイル名を削除すると当該ファイルのシート名、座標からリンクされる)。
2の操作が大変で、グラフ作成に慣れない者にとっては苦痛でした。画面を凝視する必要があり眼がしょぼしょぼになります。通常のワークシートであれば、置換えがあるので簡単に修正できます。その頭があると、操作が更に苦痛となってきます。
もっと簡単な操作でできないのか…。
この場合は、その後更に別の二つデータ系列を追加しなくてはならず、結構疲労が残りました。
これを次のようにしようというものです。
1.はそのままです。
2.を分解し、まず、現状のデータ系列の情報を取得し、ワークシートに書き出します。情報とは前にも述べたとおり、表札と横軸とデータ値の三つ座標情報です。それぞれにファイル名がついていますので、それを削除します。一括の置換えで一気に出来ます。
3.次に、修正した情報によりグラフを直すという、操作です。(機能的にはそれぞれの座標を修正することも可能とします)。
これにより、マウスによる操作から、ワークシートレベルでの操作になり、スムーズに進みます。
新しい系列のグラフの追加は、先ほどの情報(パラメータ)をもとに、新たらしい系列の情報を作ります(コピーし修正)。そして、それを元にグラフを追加する、ということで終了です。
その他の機能として、"3.グラフの属性(タイトル、目盛りなど)を簡単に修正する"がりますが、対象は、タイトル、横軸目盛の最小値、最大値、目盛間隔、目盛の表示桁数です。
グラフの削除のマクロ化は気が乗りません。
今回は能書きだけです。
【グラフ管理表の例】
※グラフ関係のマクロでのコマンドは解説が少ないので苦労します。エクセル2007では、マクロの記録を動かしても、たいした内容しか記録されないために、かなりの試行錯誤となります。
とうとうグラフです。グラフはめんどくさいです。あれこれと細かに設定しなければ出来ないので、筆者などは二の足を踏んでしまいます。
グラフ関係でマクロ操作により7割ぐらいカバーできるものが出来ないのか、という思いを強く持ちます。私どものところは時系列的な変化を取ることが多いので、主に折れ線グラフとなります。
それをベースに何とかマクロでできないものだろうか…。
という問題意識が、ある時ほぼ解決されました、というより、その糸口が見えてきました。
第41回を"その1"としますので、今回は"その2"となります。
グラフ作成のパラメータをつくりそれを元にグラフを作る･更新するという機能をとりあげます。
さて、基本的な機能として何が必要でしょうか。その分析で60%完了です。
1.グラフに新しい系列のデータを追加する。
2.グラフのデータ系列を違う系列に変更する。
※データ系列とは、データの表札(凡例に出てくるもの)、横軸の数値、データ値となります。
3.グラフの属性(タイトル、目盛りなど)を簡単に修正する。
4.グラフの削除(削除はクリックしdeleteキーで可能なのでマクロでは面白くないかもしれません)
こんな例がありました。すでにあるグラフが出来ています(データ系列の参照は同一ファイル)。違う前提条件で求められたデータ系列が別ファイルにあります。ファイル内の体系は既存のファイルと同じです。このデータ系列で同じグラフを作りたい(データ系列を置き換えたい)。既存のグラフと比べるとデータ系列のファイル名のみが違うだけです。
(グラフ込みのファイルをコピーして、それを元に違う前提条件で計算しなおせば、改めてグラフを作る必要がないという意見があると思いますが、作者が違う時は、データ系列があるそれぞれのファイルにグラフを挿入する必要性が出てきます。そういったケースを想定しました)
これを次のようにやっていました。
1.グラフシートを新しいファイルにコピーする。(この段階で、データ系列以外は、条件を満たします。データ系列は元のファイルで参照されているもののままです)
2.グラフを一つ一つクリックして、グラフのデータ系列指定のタブを出し、データ系列の表札、横軸数値、データ値の指定座標から、ファイル名を削除する(ファイル名を削除すると当該ファイルのシート名、座標からリンクされる)。
2の操作が大変で、グラフ作成に慣れない者にとっては苦痛でした。画面を凝視する必要があり眼がしょぼしょぼになります。通常のワークシートであれば、置換えがあるので簡単に修正できます。その頭があると、操作が更に苦痛となってきます。
もっと簡単な操作でできないのか…。
この場合は、その後更に別の二つデータ系列を追加しなくてはならず、結構疲労が残りました。
これを次のようにしようというものです。
1.はそのままです。
2.を分解し、まず、現状のデータ系列の情報を取得し、ワークシートに書き出します。情報とは前にも述べたとおり、表札と横軸とデータ値の三つ座標情報です。それぞれにファイル名がついていますので、それを削除します。一括の置換えで一気に出来ます。
3.次に、修正した情報によりグラフを直すという、操作です。(機能的にはそれぞれの座標を修正することも可能とします)。
これにより、マウスによる操作から、ワークシートレベルでの操作になり、スムーズに進みます。
新しい系列のグラフの追加は、先ほどの情報(パラメータ)をもとに、新たらしい系列の情報を作ります(コピーし修正)。そして、それを元にグラフを追加する、ということで終了です。
その他の機能として、"3.グラフの属性(タイトル、目盛りなど)を簡単に修正する"がりますが、対象は、タイトル、横軸目盛の最小値、最大値、目盛間隔、目盛の表示桁数です。
グラフの削除のマクロ化は気が乗りません。
今回は能書きだけです。
【グラフ管理表の例】
※グラフ関係のマクロでのコマンドは解説が少ないので苦労します。エクセル2007では、マクロの記録を動かしても、たいした内容しか記録されないために、かなりの試行錯誤となります。
登録:
コメント (Atom)
