[[20141013082929]]　『グラフで任意の列のみ表示する』（浅井）　ページの最後に飛ぶ

『グラフで任意の列のみ表示する』（浅井）

 いつもお世話になっています。今日も宜しくお願いします。

 マクロで表示させたい列を指定してグラフを作成したいのですが、その範囲指定方法が分かりせん。
 　Range("A1:A100,B1:B100,F1:F100・・・")の様に固定でするのであれば容易なのですが・・・
 現在、データ列数は４列が１組で１２組プラス１の合計４９列あります。
 更に増える可能性が有ります。１度に表示する最大数は１０個程です。
   １）表示列の指定→数値で取得
   ２）先頭列と最終行は取得済み　　(１)(２)はＯＫです。

 A-Zの範囲であれば何とか処理出来るのですが、AB100とかBG2000等となると分かりません。

 この様な中でどの様にRangeオブジェクトに組込むのかご教授をお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows8 ＞

 質問内容が具体的にはよく分かっていないのですど、、、

 ＞   １）表示列の指定→数値で取得
 ＞   ２）先頭列と最終行は取得済み　　(１)(２)はＯＫです。
 ＞ A-Zの範囲であれば何とか処理出来るのですが、

 ここまで出来るなら、何とでも対処できるような気がするんですけど・・・

 　　　　　取りあえず、これがどんな形なのか教えてください。(配列になっているのかなぁ？　とか言う意味なんですけど）
 　　　　　　　　　　　　↓
 ＞   １）表示列の指定→数値で取得

（半平太） 2014/10/13(月) 10:54

 質問内容が充分でないかもしれませんので追記します。
 > １）表示列の指定→数値で取得 
これをユーザーフォームのチェックボックスから取得しようと考えています。
もしくは連続セルに事前入力したものを使用します。
ですから、ABCDという文字列ではなく数値(1,2,3,4)で得ます。

 『グラフのデータテーブルの範囲指定の仕方』が分かりません。
    ActiveSheet.ChartObjects("myグラフ").Chart.SetSourceData Source:=Range(strRg)
    でRange(strRg)の変数strRgをどの様に表現するのかです。

 先頭列は『日付』で以降の列は４列1組となっています。データ行を１から100とします。
        A(日付)  BCDE(1)   FGHI(2)  JKLM(3)  NOPQ(4) ・・・
  範囲  A1:A100  B1:B100   F1:F100  J1:J100  N1:N100

 例えばここで『日付』と１組目と３組目の３列分の範囲をどの様に変数strRgへ格納するのかです。
 行と列(Row,Column)は分かるのですが、文字列として "A1:A100,B1:B100,J1:J100" を渡す方法は？
 若しくは、数値で渡す事が出来るのかです。自分なりに試行錯誤しましたが失敗の連続です。
（浅井） 2014/10/13(月) 11:30

 すみません。更に付け加えます。
 『日付』以外は各組の先頭列のみが表示対象です。　何度もすみません。
（浅井） 2014/10/13(月) 11:34

 ＞ 『日付』以外は各組の先頭列のみが表示対象です。

 ＞文字列として "A1:A100,B1:B100,J1:J100" を渡す方法は？

 二つの関係はどうなっているんですか？（具体的にお願いします）

（半平太） 2014/10/13(月) 12:58

 何度も説明不足で申し訳ありません。

 データ内容：日々の商品単価と変動幅やコメント等があり、１商品に対し４列使用
 グラフ表示：日付と指定商品の単価を表示

 テーブルの構成
           -----------------------------  
 [列]   A  |   B     C      D      E   |  FGHI  JKLM  NOPQ　・・・
      日付 | 単価  変動幅 ｺﾒﾝﾄ1  ｺﾒﾝﾄ2 |  
           -----------------------------  
 B-Eが１グループで現在は１０グループ程となっています。
 グラフでは各グループの先頭列[B,F,J,N,…]が系列となります。夫々のセルは商品名となっています。

 Range("A1").CurrentRegion.Columns.Count で対象テーブルの列数が分かり、各組の先頭列も把握できます。
 これを利用すればグループ追加にも対応出来るのではと考えたのです。

 [工程手順] （思惑）
 　１.ﾕｰｻﾞｰﾌｫｰﾑを開いた時に各商品のチェックボックスと追加する。→ グループ先頭位置取得用
 　２.チェックOn を調べ『変数strRg』を生成する。

 この『変数strRg』の生成手順を模索しているのです。何か良い方法は無いものかと・・・
 宜しくお願いします

（浅井） 2014/10/13(月) 14:05

   ＞ 『日付』以外は各組の先頭列のみが表示対象です。　
   ＞文字列として "A1:A100,B1:B100,J1:J100" を渡す方法は？

  ・・と言うことは、上の2文は、同じ内容を云い方を変えて表現しただけであると解釈します。

 ＞ １.ﾕｰｻﾞｰﾌｫｰﾑを開いた時に各商品のチェックボックスと追加する。→ グループ先頭位置取得用

 「１」については、浅井さんに工夫して頂くとして、兎に角、位置番号を配列の形で持つことを前提とする為,
  下のサンプルでは「先頭位置がZシートの1行目に書き込まれている」ものとします。（下図ご参照)

 (実際はチェックボックスなりの利用とかで、浅井さんのやり易い様にしてください)

 <Z>シート
  行  _A_  _B_  _C_  _D_
   1   1    2    6   14

 ＞ ２.チェックOn を調べ『変数strRg』を生成する。

 「２」については、アドレスの文字列を求める他、Rangeオブジェクトを直接取得してもよさそうですよね？
 　　　　　　　　　つまり、Range(strRg)　と云うRangeオブジェクトをここに設定する。
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓
 ＞ActiveSheet.ChartObjects("myグラフ").Chart.SetSourceData Source:=Range(strRg)

 まぁどちらでもそんなに変わりませんが、なんか屋上屋を重ねるような気がしたものでして・・・

 サンプルでは、Rangeオブジェクトを取得するFunctionプロシージャを作ることにします。
 ※あくまで文字列でやりたい場合は、そのAddressプロパティを利用してください。

 指定した Rangeオブジェクト を取得するプロシージャ

 Function RangeRecognitized() As Range
     Dim colNums '列番配列
     Dim rowSt As Long, rowEd As Long, NN As Long
     Dim rngTargeted As Range
     Dim leftCol As Range

     With Worksheets("Z")　’Zシートにある先頭番号群を配列に取り込む
         colNums = .Range("A1", .Range("Z1").End(xlToLeft)).Value
     End With

     rowSt = 1 '仮に1行目からとします　※実際にはそちらで取得した行番号にしてください。
     rowEd = 6 '仮に6行目までとします　※実際にはそちらで取得した行番号にしてください。

     Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)
     Set rngTargeted = leftCol

     For NN = 2 To UBound(colNums, 2)
         Set rngTargeted = Union(rngTargeted, leftCol.Offset(, colNums(1, NN) - 1))
     Next NN

     Set RangeRecognitized = rngTargeted
 End Function

 そして、今あるプロシージャを以下に変更する
 ＞ActiveSheet.ChartObjects("myグラフ").Chart.SetSourceData Source:=Range(strRg)
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓
   ActiveSheet.ChartObjects("myグラフ").Chart.SetSourceData Source:=RangeRecognitized

（半平太） 2014/10/13(月) 15:19

半平太さんへ

 私の質問内容を理解して頂き有難うございます。又、ご丁寧にサンプルを作成して頂き本当に有難うございます。
 私は２年ほど前からこのサイトを利用させて頂き、半平太さんのご指導などを見て来ました。　
 半平太さんを含め指南役の人達の知識力には頭が下がります。
 ８月に６３歳となり仕事も殆どなく、 趣味の範囲でエクセルシートの活用を目指しております。
 独学の為、絶えず問題にぶつかり苦悶しています。

 私にとってご指導頂いたスクリプトを理解するのには２～３時間は必要かと思います。
 実際に行動する前に質問して申し訳ないのですが、１か所理解出来ない所があります。
 ＞Set RangeRecognitized = rngTargeted　とするのは何故でしょうか？
 その前段階の For ～ Next 句の中で使用している " Set rngTargeted = " を " Set RangeRecognitized = "
 とすれば良いのではと思いました。（自分としてはプロシージャーはより簡潔にと心掛けている事によります）

 多分、明日中には結果報告出来るかと思います。（昼間っからの飲酒をしなければ…）
 有難うございました。

（浅井） 2014/10/13(月) 18:03

 私のはあくまで趣味の範囲なので、大規模・分担作業・業務用を想定するエキスパートの人たちのとはかなり差があります　m(__)m
 今回のも、Zシートが無かったら、あっても１行目にデータが無かったら？　とか云う問題は放置しております。

 後で気が付いたのですけど、RowStとRowEdは引数の形でメインから渡さないと使いにくいと思われます。
 全部メインに組み込むように作ればそんな懸念もないですけど・・・・
 (書き始めはそんなつもりでやっていたのですけど。途中で気が変わってFunctionにしてしまいました）

  ＞Set RangeRecognitized = rngTargeted　とするのは何故でしょうか？

 おっしゃる通り、屋上屋を重ねる類かも知れません。ひとの事を言ってられないですね　(^_^;)

 ゴチャゴチャ変動するものはローカル変数で処理して、最終形を渡すようにしています。
 処理の途中でプロシージャ名が出てくるとビックリします。(私は）
 それが私にとっての「簡潔」です。

（半平太） 2014/10/13(月) 19:15

 有難うございます。只今、動作確認中ですが、只今問題（疑問）が発生しております。

   Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)
 上記の中でのcolNums(1, 1)の実態は項目行である為に文字列ではないかと思います。（←エラー発生）
 動作確認の中でこの様な事を言うべきではないと思っていますが・・・

 確認終了後、改めてお伝えします。どうも済みません。

（浅井） 2014/10/13(月) 19:53

 ＞  Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)
 ＞ 上記の中でのcolNums(1, 1)の実態は項目行である為に文字列ではないかと思います。

 あのー、こう云う前提なんですけど。（数値しかありません）
 　　　　　↓
 ＞  下のサンプルでは「先頭位置がZシートの1行目に書き込まれている」ものとします。（下図ご参照)
 ＞ (実際はチェックボックスなりの利用とかで、浅井さんのやり易い様にしてください)
 ＞
 ＞ <Z>シート
 ＞  行  _A_  _B_  _C_  _D_
 ＞   1   1    2    6   14

 そちらのテストではどうやって、{1,2,6,14} の配列 を colNums に取り込んだのでしょうか？

（半平太） 2014/10/13(月) 20:15

 あれー？
 もしかして、全ての単価列が対象なんですか？

 私は、特別に選んだ単価列だけが対象かと思ってしまったのですが・・・

 そうじゃないと、なんのためにこのご説明があるのか分からないです。
　　　　　　　　　　　　　　　↓
 ＞１）表示列の指定→数値で取得 
 ＞これをユーザーフォームのチェックボックスから取得しようと考えています。
 ＞もしくは連続セルに事前入力したものを使用します。
 ＞ですから、ABCDという文字列ではなく数値(1,2,3,4)で得ます。

（半平太） 2014/10/13(月) 20:36

半平太さん

 返答が遅れました。大変失礼しました。私の不足と致したところでした。
 自分で書いておきながら以下の事を亡失していました。

 ＞１）表示列の指定→数値で取得 ・・・これが前提条件でした。

 私の思い道理の結果を得る事が出来ました。有難うございます。

 ■ 所で理解するのが難しい部分が有ります。 ご説明頂ければ幸いです。
    ↓↓↓
 ＞ Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)

 結果、[leftCol.Address]は､[$A$1:$A$6]となるのが分かりました。これが[Rangeオブジェクト]という事も。
 つまり、この事を理解出来ないために作業が滞っていたのです。今も摩訶不思議に思っています。

 今迄の自分の中でこの様な使用はした事が無く、例えばバリアント変数(Ex)に対しSet句を使用せず

   Ex = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)

 という形でしか知らなかったのです。当然､[Ex]は拡張範囲の配列となります。故に勘違いが発生しました。
 厚かましいようですがご教授をお願い出来ないでしょうか。

（浅井） 2014/10/14(火) 13:33

  ■ 所で理解するのが難しい部分が有ります。 ご説明頂ければ幸いです。
     ↓↓↓
  ＞ Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)
  ＞          Ex = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1)

 その２文の違いですね。結構難しい話なんですぅ。

 Setはオブジェクト変数にオブジェクトへの参照を代入するとき使います。
 従って右辺がオブジェクトの類じゃないといけません。

 例えば、下式なんかだと「型不一致」のエラーになります。何故なら右辺はオブジェクトじゃなく値だから。
 Set leftCol = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1).Value

 じゃ、上記Variant型Exの時、左辺・右辺の「型不一致」になりもしなければ、勿論オブジェクトになりもせず
 何故右辺の値配列を格納するのか、ってことになりますよね？

 (難しい話を脇に置くと）
 こう書いてあるのと同じことになっています。（Valueプロパティを追加)
 　↓　　　　　　　　　
 Ex = Cells(rowSt, colNums(1, 1)).Resize(rowEd - rowSt + 1, 1).Value

 いや「同じ」と云うより、もしそう云う結果を望んでいるなら、積極的にそう書くべきなのかも知れません。
 何故って、RangeオブジェクトのプロパティはValueに限った訳でないからです。

 ※ただ、指定するとどんなプロパティでも期待通りの結果を返してくれるかと云うと、
 　そうでもなく(マイクロソフトの仕様によるので）話が厄介になります。

 書かないとValueプロパティと解釈される仕様なら、書かない方が簡潔とみるか、
 積極的に明記した方が紛れがなく簡明なんだとするかは人それぞれです。(後者が多数派です)

 一つ言えることは、表面上は前者であって「Valueプロパティを省略しているんだ」と云う
 認識を持たない人は危ういです。

 Setを付けるのか/付けないのか、それは必然の結果である。
 Valueプロパティを指定するのか/省略するのか、それは簡明を求める選択の結果である。

（半平太） 2014/10/14(火) 15:56

半平太さんへ

ご教授有難うございました。
昨日､質問した後から所用でPCを見ていませんでした。丁度帰宅したばかりの今、拝見させて頂きました。
漠然とですが理解出来ました。今回の利用の仕方だけについては完璧に理解する事は出来ると思います。
然し、これを自分のツールとして活用出来るかは自信が有りませんが、これからも私なりの自己研鑽に励みます。
絶えず問題が噴出するのが私の拙いスクリプトです。これからもお付き合いのほど宜しくお願いします。
（浅井） 2014/10/15(水) 19:00

コメント返信：

[ 一覧(最新更新順) ]

[[20141013082929]] 『グラフで任意の列のみ表示する』（浅井） ページの最後に飛ぶ

[[20141013082929]]　『グラフで任意の列のみ表示する』（浅井）　ページの最後に飛ぶ