Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3662 / 13644 ツリー ←次へ | 前へ→

【60791】データの位置を変えたい たかと 09/3/15(日) 0:15 質問[未読]
【60792】Re:データの位置を変えたい 横入り 09/3/15(日) 7:04 発言[未読]
【60793】Re:データの位置を変えたい 横入り 09/3/15(日) 8:57 発言[未読]
【60797】Re:データの位置を変えたい たかと 09/3/15(日) 23:23 質問[未読]
【60798】Re:データの位置を変えたい 横入り 09/3/16(月) 0:05 発言[未読]
【60803】Re:データの位置を変えたい たかと 09/3/16(月) 11:29 質問[未読]
【60804】Re:データの位置を変えたい ss 09/3/16(月) 12:21 回答[未読]
【60820】Re:データの位置を変えたい 横入り 09/3/16(月) 21:23 発言[未読]
【60846】Re:データの位置を変えたい たかと 09/3/17(火) 22:54 質問[未読]
【60847】Re:データの位置を変えたい 横入り 09/3/17(火) 23:12 発言[未読]
【60848】Re:データの位置を変えたい たかと 09/3/17(火) 23:40 質問[未読]
【60849】Re:データの位置を変えたい 横入り 09/3/17(火) 23:50 発言[未読]
【60867】Re:データの位置を変えたい たかと 09/3/18(水) 23:32 お礼[未読]

【60791】データの位置を変えたい
質問  たかと  - 09/3/15(日) 0:15 -

引用なし
パスワード
   すみませんが、教えてください。


データベースから以下のように数値を取り出しました。
日付 場所 件数
3/1  a   3
3/1  b   2
3/2  a   4
3/1  c   5
3/2  b   2
3/2  c   7
3/3  a   6
3/3  c   7

これを、
縦軸は件数
横軸は日付
a,b,cの件数を日ごとに積み上げたグラフ
にしたいのですが、この形でできないようなので、データを、
  a b c
3/1 3 2 5
3/2 4 2 7
3/3 6 0 7

といったように配置を換えて、グラフにしやすいデータ配置に置き換えたいのですが、
どのようにしたら自動で置き換わりますか?
ピボットテーブルも一つの方法だと思いますが、
いい方法があれば教えていただけないでしょうか?

すみませんがよろしくお願いします。

【60792】Re:データの位置を変えたい
発言  横入り  - 09/3/15(日) 7:04 -

引用なし
パスワード
   同じ日付、同じ場所のデータが複数件あるならなおさら
ピボットテーブルが簡単じゃないですか?
それを自動記録して少し修正を加えればいいのです。
ピボットテーブルが「いい方法」ではない理由は何ですか?
あなたが使ったことがないからですか?

【60793】Re:データの位置を変えたい
発言  横入り  - 09/3/15(日) 8:57 -

引用なし
パスワード
   ・行、列でそれぞれ出てくる項目を重複を排して整列して列挙
・各行ごとに以下の処理を繰り返し
 ・Match関数を使って、日付、場所がそれぞれ何番目かを知る
 ・配列の適切な要素に件数を書き込む
 ・(同一の日付、場所のデータの場合は加算していく必要)
・配列をシートに書き出す
ということを実行すればいいですが、
そのための仕組みピボットテーブルを使うか、
自分でコードを書くかの違いです。

【60797】Re:データの位置を変えたい
質問  たかと  - 09/3/15(日) 23:23 -

引用なし
パスワード
   ▼横入り さん:

回答ありがとうございました。
もう少し質問させてください。

ピボットテーブルをVBAで記述することをしたことがなく、
よくわからなかったせいもあり、ピボット以外に方法があるのでしたら、
ご教授いただきたく投稿しました。
ピボットテーブルを実際に記録してみたのですが、次の点教えていただけないでしょうか?
もとのデータが情報が日ごとに増えていくので、行数などが増えた場合に、
範囲の自動取得させたいのですが…


  Range("A1:C13").Select
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "当月データ!R1C1:R13C13").CreatePivotTable TableDestination:="", TableName:= _
    "ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  ActiveSheet.Cells(3, 1).Select
  Range("G5").Select
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("日付")
    .Orientation = xlRowField
    .Position = 1
  End With
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("場所")
    .Orientation = xlColumnField
    .Position = 1
  End With
  ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
    "ピボットテーブル1").PivotFields("台数"), "合計 / 台数", xlSum


おそらくはじめ3行ほどに範囲の情報が記述されていると思うのですが、
どのように記述すればいいのかわかりません。
ネットなどいろいろ調べてみましたが、ちょっとわかりません。
本当に初心者質問で申し訳ありませんが、
ご教授お願いいたします。
  

【60798】Re:データの位置を変えたい
発言  横入り  - 09/3/16(月) 0:05 -

引用なし
パスワード
   SourceData:="当月データ!R1C1:R13C13"
がPivotTableの対象範囲を指定している箇所です。
13列にも広がったのですか?

仮にA列からC列で、データが入力されているところまでとすると、
例えば
  Dim myRange As Range
  Set myRange = Range("A1", Range("C1").End(xlDown))
などとして、引数のところを  
  SourceData:=myRange.Address
または
  SourceData:=myRange.Address(External:=True)
などとすればいいでしょう。
部分的にしか見ていませんので、実際に動かして確認してください。

【60803】Re:データの位置を変えたい
質問  たかと  - 09/3/16(月) 11:29 -

引用なし
パスワード
   ▼横入り さん:
回答ありがとうございます。
すみませんがもう少し伺いたいのですが・・・

回答いただいた件で実行してみたら範囲取得はできました。
ありがとうございます。
現在やりたいことが、
データをピボットテーブルで集計し、
その集計した結果をグラフにする、という作業を毎日自動更新で
できるようにしたいのですが・・・
2回目以降マクロを実行したとき、当たり前だと思いますが、
「そのピボットテーブルは既に存在します」などといったエラーが出ます。

マクロを実行するたびに集計・グラフが更新されるには、
何か命令を付け足す必要がありますか?
何でも質問して本当にすみません・・・

【60804】Re:データの位置を変えたい
回答  ss  - 09/3/16(月) 12:21 -

引用なし
パスワード
   >その集計した結果をグラフにする、という作業を毎日自動更新で
>できるようにしたいのですが・・・
1回目のピボットテーブル作成時に可変範囲になる様な名前を作成しておき
参照範囲はその名前を利用する(手作業で作成してください)
後は
Sheet1.PivotTables(1).RefreshTable
等で更新すれば良いのでは?
ちなみに可変範囲を作成する方法は、「ピボットテーブル 参照範囲 可変」
でネット検索してください。

【60820】Re:データの位置を変えたい
発言  横入り  - 09/3/16(月) 21:23 -

引用なし
パスワード
   一応書いてみました。
ついでに、ピボットグラフを利用するとグラフもOKです。

Sub test()
  Dim myRange As Range
  Dim e
  Dim pt As PivotTable
  
  Set myRange = Range("A1", Range("C1").End(xlDown))
  
  On Error Resume Next
  Set pt = ActiveSheet.PivotTables("ピボットテーブル1")
  e = Err.Number
  On Error GoTo 0
  
  If e <> 0 Then
    Set pt = ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlDatabase, _
      SourceData:=myRange).CreatePivotTable _
       (TableDestination:=ActiveSheet.Cells(1, 5), _
       TableName:="ピボットテーブル1", _
       DefaultVersion:=xlPivotTableVersion10)

    With pt.PivotFields("日付")
      .Orientation = xlRowField
      .Position = 1
    End With
    With pt.PivotFields("場所")
      .Orientation = xlColumnField
      .Position = 1
    End With
    pt.AddDataField pt.PivotFields("台数"), "合計 / 台数", xlSum
    
  Else
    pt.SourceData = myRange.Address
    pt.RefreshTable
  End If
  
  'ピボットグラフの作成
  Range("E1").Select
  
  Charts.Add
  ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E1")
  ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
  ActiveChart.HasPivotFields = False
End Sub

【60846】Re:データの位置を変えたい
質問  たかと  - 09/3/17(火) 22:54 -

引用なし
パスワード
   ▼SS さん 横入り さん:
ご回答本当にありがとうございました。
更新の方法も、ピボットテーブルからピボットグラフへの流れも、
どちらも有効な方法でした。
何もわからない初心者にご教授頂きありがとうございます。
あと、可変範囲の作成も調べて実行しました。
SSさん、ありがとうございます。


長々とすみませんが、横入りさんへ質問してよいですか?

1.ご教授いただいた中で、

>  On Error Resume Next
>  Set pt = ActiveSheet.PivotTables("ピボットテーブル1")
>  e = Err.Number
>  On Error GoTo 0
>  
>  If e <> 0 Then
>    Set pt = ActiveWorkbook.PivotCaches.Add( _
>      SourceType:=xlDatabase, _
この部分がどのような処理をしているのかいまいちわからなかったのですが、
どのような処理なのでしょうか・・・
ちなみに、下のようにIF条件を外れた場合もわからないのです。
すみませんご教授ください。

>  Else
>    pt.SourceData = myRange.Address
>    pt.RefreshTable
>  End If
>  


あともう一つ・・
これは自分のピボットグラフに関する知識が足りないのですが・・・

>  'ピボットグラフの作成
>  Range("E1").Select
>  
>  Charts.Add
>  ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E1")
>  ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
>  ActiveChart.HasPivotFields = False
>End Sub

ご教授いただいたピボットグラフですが、通常にピボットテーブルから
ピボットグラフの流れで作られるグラフと違いますか?
というのは、自分にとっての加工のしやすさから、ご教授いただいたグラフ、
(たとえば、普通にエクセルに数値をいれツールバーの挿入→グラフから
出すようなグラフ。教えていただいたのがそのようなグラフに見えたので)
のようにしたいのですが、
どうしてもピボットテーブルからグラフを選ぶと新しいページにグラフが作られ、
必要ない項目(たとえば上部のグラフの題名など)の欄を削除したいのに
削除できなかったりして編集しづらいので・・
VBAと大幅にそれてしまい申し訳ありませんが、よければご教授ください。
(もし質問の意図がわからなかったりしたら回答は結構です)
よろしくお願いします。

【60847】Re:データの位置を変えたい
発言  横入り  - 09/3/17(火) 23:12 -

引用なし
パスワード
   >  On Error Resume Next
>  Set pt = ActiveSheet.PivotTables("ピボットテーブル1")
>  e = Err.Number
>  On Error GoTo 0
>  
>  If e <> 0 Then
>    Set pt = ActiveWorkbook.PivotCaches.Add( _
>      SourceType:=xlDatabase, _
>この部分がどのような処理をしているのかいまいちわからなかったのですが、
>どのような処理なのでしょうか・・・

Set pt = ActiveSheet.PivotTables("ピボットテーブル1")
を実行すると、
・"ピボットテーブル1"が未作成ならば、エラーになって
 e = Err.Number には、0 ではないエラーコードが入ります。
・"ピボットテーブル1"が作成済みなら、エラーにはならず e = 0
(on error 云々はエラーになってもそこで止まらないための工夫です。)

後続の処理では eの内容により、未作成か作成を判定し、それぞれに
あった処理を実行します。

例えば、
>  Else
>    pt.SourceData = myRange.Address
>    pt.RefreshTable
>  End If
これは、"ピボットテーブル1"が作成済みのケースの処理です。
対象範囲を設定し直して、テーブルを再作成しています。
ssさんのご指摘どおりです。

グラフについては、両方を比較してみてください。
あなたの判断でどちらでもお使いください。

【60848】Re:データの位置を変えたい
質問  たかと  - 09/3/17(火) 23:40 -

引用なし
パスワード
   ▼横入り さん:
内容の説明、本当にありがとうございます。
非常に参考になりました。

すみません・・・
もう一つ質問させていただいてよろしいですか?
ピボットグラフも自動更新したいとき、
このときもピボットテーブルと同じように、
IF〜ELSEのような条件文が必要なのでしょうか?
ご教授いただいた内容で実行してみたら、
マクロが実行されるたびに、グラフがどんどん作成されていくため、
自分なりに考えましたが、わかりませんでした。
何から何まで申し訳ありませんがご教授いただきたく思います。

【60849】Re:データの位置を変えたい
発言  横入り  - 09/3/17(火) 23:50 -

引用なし
パスワード
   グラフを書く処理を
If e <> 0 Then のほうの最後に移動すればいいでしょう。

すでにテーブルがある場合、ピボットテーブルをRefreshすれば、自動的にグラフも
書き変わるはずです。
 

【60867】Re:データの位置を変えたい
お礼  たかと  - 09/3/18(水) 23:32 -

引用なし
パスワード
   ▼横入り さん:
ありがとうございました。
時間かかりましたが解決しました。

みなさま本当にありがとうございます。
また、質問することあると思いますが、よろしくお願いいたします。

3662 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free