Excel VBA質問箱 IV

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

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


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

【27010】累計 超超初心者 05/7/26(火) 15:16 質問[未読]
【27013】Re:累計 Jaka 05/7/26(火) 16:07 発言[未読]
【27076】Re:累計 超超初心者 05/7/28(木) 9:35 質問[未読]
【27085】Re:累計 Jaka 05/7/28(木) 16:28 発言[未読]
【27112】Re:累計 MokoMoko 05/7/29(金) 9:56 回答[未読]
【27130】Re:累計 超超初心者 05/7/29(金) 14:13 質問[未読]
【27137】Re:累計 MokoMoko 05/7/29(金) 16:19 回答[未読]
【27185】Re:累計 超超初心者 05/8/1(月) 12:16 質問[未読]
【27204】Re:累計 MokoMoko 05/8/1(月) 17:16 回答[未読]
【27237】Re:累計 超超初心者 05/8/3(水) 10:19 お礼[未読]

【27010】累計
質問  超超初心者 E-MAIL  - 05/7/26(火) 15:16 -

引用なし
パスワード
   いつも大変お世話になります。
ピボットテーブルで月別の部品数の累計を算出しています。

    HDD    メモリ    基板    総計
2003/02    1    0    0    1
2003/04    2    0    0    2
2003/05    3    0    0    3

データがない月は1行挿入し、前月のデータをコピペしたいのです。
2003/03    1    0    0    1
という風にです。
ピポットでは無理なような気がするので、違うシートにデータを
はりつけて処理する予定です。飛んでいる月をどのように
したら検索できるのかはたと困っていまいました。どうか
よろしくお願いします。

【27013】Re:累計
発言  Jaka  - 05/7/26(火) 16:07 -

引用なし
パスワード
   こんにちは。
ピボットテーブル全く解りません。
元となるデータはどうなっていて、ピボットテーブル自体がどうな風になっているのか解りませんが、データを挿入後
ピボットテーブルを右クリック、データの更新で更新できませんか!
レイアウトがわからないので、適当に作って試した結果ですが...。

【27076】Re:累計
質問  超超初心者 E-MAIL  - 05/7/28(木) 9:35 -

引用なし
パスワード
   ▼Jaka さん:
レスありがとうございます。
説明がわかりにくくて申し訳ありません。
累計からグラフを作るときに月が飛んでいると
求めているようなグラフが作れません。
データがない月探し出して
1行挿入し、前月のデータをコピペするというのを
VBAで自動化したいと思っています。

【27085】Re:累計
発言  Jaka  - 05/7/28(木) 16:28 -

引用なし
パスワード
   ピボットテーブルのデータ範囲を変えるのなら、
ピボットテーブルを選択して、
データ → ピボットテーブルリポート を開き
「戻る」ボタンで、新たにデータ範囲を変える事ができました。

これをマクロ記録したのが、こんな感じでした。

Sub Macro6()
  ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R7C5", TableDestination:="R1C1", TableName:="ピボットテーブル3"
End Sub

この部分を解りやすいA1形式でコードを書くとこんな感じになります。
"Sheet1!R1C1:R7C5"
  ↓
"Sheet1! " & Range("A1:E7").Address(, , xlR1C1)

う〜ん。やっぱりピボットテーブルは解らない。
ピボットテーブルのレイアウトが書いてないので、合っているのかどうか解りません。
グラフについては、元のデータで範囲を変えられる見たいですから、マクロ記録で出来ると思います。

【27112】Re:累計
回答  MokoMoko  - 05/7/29(金) 9:56 -

引用なし
パスワード
   ▼超超初心者 さん:
こんにちは・・・

>累計からグラフを作るときに月が飛んでいると
>求めているようなグラフが作れません。

該当月と翌々月の平均などの数値を張り込むことがありますが・・・
兎に角累積には必ず何らかのデータを入れないと、グラフの価値が下がります?
というより、問題が発生します。

>データがない月探し出して
>1行挿入し、前月のデータをコピペするというのを
>VBAで自動化したいと思っています。

この考えも近似値を埋めるということでのアイディアでしょうね。

そこで、トライしました。これで出来ると思います。
ピボットもこれで行けるでしょう。
A列からD列までのデータです、更にE、F等までデータ転記する必要があれば
Offsetで同じように増やしてください。


Sub 抜けの月を加え上の行と同じ数値持ち込み()

Dim r As Range
 For Each r In Range("A3", Range("A65535").End(xlUp))
    r.Select
   If r.Offset(1).Value <> DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value)) Then
    r.Offset(1).EntireRow.Insert
    r.Offset(1).Value = DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value))
      r.Offset(1, 1).Value = r.Offset(, 1).Value
      r.Offset(1, 2).Value = r.Offset(, 2).Value
      r.Offset(1, 3).Value = r.Offset(, 3).Value
       ’必要であれば増やしてください
    End If
   If r.Row = Range("A65536").End(xlUp).Row - 1 Then
   Exit Sub
   End If
 Next

End Sub

【27130】Re:累計
質問  超超初心者 E-MAIL  - 05/7/29(金) 14:13 -

引用なし
パスワード
   ▼MokoMoko さま。
私のつたない文章からやりたいことをわかってくださってありがとうございます。
教えていただいたコードを早速使わせていただきました。
ピボットには行を挿入できませんというエラーが出たので、
ピボットデータを他のところにコピーしてからコードを実行しました。
すると
 HDD    メモリ    基板    総計
2003/02    1    0    0    1
2003/04    2    0    0    2
2003/05    3    0    0    3
2003/06    4    0    0    4
2003/07    5    0    0    5

とデータがあるとき
2003/05と2003/06の間に
2003/6/1
2003/7/1
2003/8/1
 〜
2028/04/01
という風に行が延々と増えて
いったので処理を途中で中断させました。

【27137】Re:累計
回答  MokoMoko  - 05/7/29(金) 16:19 -

引用なし
パスワード
   ▼超超初心者 さん:
こんにちは。今日は夏休みで見させていただいていました。
じっくりと読んでください。

シートの使い方はどのようになっていますか、きっちりと動きますが。
   A  B   C   D   E
1  日付 HDD メモリ 基板 総計
2 2003/02
3

このようなレイアウトでしょうか。
それから、年月の指定の仕方・・・これはシリアルナンバー(1900年1月1日を
1として今日は38562です。この数字を利用していますので、そこの列の
書式の設定を正しくしてください。
A2を選択→右クリック→書式の設定
分類で「ユーザー設定」を選択
種類(T)のところにyyyy/mmとしてください。
A3・・・・A10・・・・全て書式の設定をしてください。
お分かりでしょうか?「書式のコピー/貼り付け」=箒のようなアイコンを使うと便利です。
A2からデータが入っているとしたら、下のコードでいけます。

Sub 抜け月を加え上の行と同じ数値2()  ’2にしました

Dim r As Range
 For Each r In Range("A2", Range("A65535").End(xlUp)) 'ここをA2に変更
    r.Select
    If r.Offset(1).Value <> DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value)) Then
       r.Offset(1).EntireRow.Insert
      r.Offset(1).Value = DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value))
      r.Offset(1, 1).Value = r.Offset(, 1).Value
      r.Offset(1, 2).Value = r.Offset(, 2).Value
      r.Offset(1, 3).Value = r.Offset(, 3).Value
    End If
   If r.Row = Range("A65536").End(xlUp).Row - 1 Then
   Exit Sub
   End If
 Next

End Sub

このままこのコードをコピーして張り込んで実行してください。
張り込んで、このSubにカーソルをあて、F8を押して、エクセルの画面の
変化を見られると問題なく動いていることが分かります。
月や日、時間は裏でシリアル値に変換されて動かすのが基本です。
因みに、A2 の2003年の2月はシリアル値は37653で
A3 に入った3月は37681です。
年月により1ケ月の日数が異なりますので、DateSerial関数を使います。
 
以上です。試してみてください。必ず動きます。

【27185】Re:累計
質問  超超初心者 E-MAIL  - 05/8/1(月) 12:16 -

引用なし
パスワード
   ▼MokoMoko さん:
返信ありがとうございました。教えていただいたとおりに書式を設定したのですが、
ピボットテーブルのデータをコピペしたものではやはりきちんと動かないのです。データを自分で手入力してみたらきちんと動きました。

【27204】Re:累計
回答  MokoMoko  - 05/8/1(月) 17:16 -

引用なし
パスワード
   ▼超超初心者 さん:
何に問題があるのでしょう?
手作業で埋めて作成したデータとマクロで作成したデータと変わりなく、そのままピボットに使えるはずですが?
>、
>ピボットテーブルのデータをコピペしたものではやはりきちんと動かないのです。データを自分で手入力してみたらきちんと動きました。
・・・ここの意味がいまひっつ分かりづらいですが?

もう一度質問を整理しましょう
貴方のしたいことは、最終的には
ピボットで集計なりグラフを作成することでしたね。
   ↓↓
「累計からグラフを作るときに月が飛んでいると
求めているようなグラフが作れません。
データがない月探し出して
1行挿入し、前月のデータをコピペするというのを
VBAで自動化したいと思っています。」(これが貴方の依頼文です)

ピボットはデータが揃ってからの作業です。
その前にピボットができるデータの作成です。

そこで元データ(貴方の書かれたデータ)の月単位のデータが
抜けている場合、抜けている月を挿入して、前月のデータを
抜けているデータとして代用する事ですね。
今回のテーマはこれですね。

(抜けたデータ)
       HDD   メモリ  基板   総計
2003/02    1    0    0    1
2003/04    2    0    0    2
2003/05    3    0    0    3
2003/06    4    0    0    4
2003/07    5    0    0    5

(穴埋めしたデータ)
       HDD   メモリ  基板  総計
2003/02    1    0    0    1
2003/03    1    0    0    1
2003/04    2    0    0    2
2003/05    3    0    0    3
2003/06    4    0    0    4
2003/07    5    0    0    5

具体的には「抜けたデータ」を
「穴埋めしたデータ」を得たいという事ですね。

問題ないでしょうか。このコードでできる筈ですが・・・
もう一度試してみてください。問題なくマクロが動きます。


Sub 抜け月を加え上の行と同じ数値()
Dim r As Range
 For Each r In Range("A2", Range("A65535").End(xlUp))
    r.Select
    If r.Offset(1).Value <> DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value)) Then
       r.Offset(1).EntireRow.Insert
      r.Offset(1).Value = DateSerial(Year(r.Value), Month(r.Value) + 1, Day(r.Value))
      r.Offset(1, 1).Value = r.Offset(, 1).Value
      r.Offset(1, 2).Value = r.Offset(, 2).Value
      r.Offset(1, 3).Value = r.Offset(, 3).Value
      r.Offset(1, 4).Value = r.Offset(, 4).Value
    End If
   If r.Row = Range("A65536").End(xlUp).Row - 1 Then
   Exit Sub
   End If
 Next

End Sub

【27237】Re:累計
お礼  超超初心者 E-MAIL  - 05/8/3(水) 10:19 -

引用なし
パスワード
   ▼MokoMoko さん:
アクセスデータからエクセルでピボットを使って
データを集計しています。それから飛んでいる
月をVBAで連続データにさせたかったのです。
mokomokoさんのデータを少しさわって
無事連続データにすることができました。
つたない説明及に最後までつきあい
いただきこころより感謝します。ありがとうございました。

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