データを扱う時のいろいろな操作 2

■ Excel豆知識44





44-3 ピボットテーブルを利用した365日分のシート作成
これもTasanさんの日記からのご紹介です。


用意するものは、"Sheet1"の一枚だけです。

Sheet1に
 A1:日付
 B1:値
 A2:[CTRL]+[;](今日の日付)
 B2:1
を入力

A1セルを選択してピボットテーブルウィザードで[次へ]、[次へ]、
image04454.png
[図04454]

ウィザード3/3で「既存のワークシート」を指定し、A5セルをクリックして[完了]
image04455.png
[図04455]

行のエリアに"日付"、データのエリアに"値"を配置

日付を右クリック
 =>グループ化と詳細の表示(グループとアウトラインの設定)
  =>グループ化
   =>"日"だけを反転させて[OK]
image04456.png
[図04456]

日付をページのエリアに移動して
image04457.png
[図04457]

"日付"をダブルクリック

表示しないアイテム(アイテムを表示しない)で
 一番上と一番下にある"<西暦/○/○"と">西暦/○/○"を反転させて[OK]
(うるう年でない場合は、2月29日も反転)
image04458.png
[図04458]

ピボットテーブルツールバー
 =>ピボットテーブル
  =>ページの表示
   =>"日付"=>[OK]
image04459.png
[図04459]

これで365日分のシートが出来上がります。
image04460.png
[図04460]

シート見出しを右クリックして、"すべてのシートを選択"
image04461.png
[図04461]

A:B列を選択してコピー
 =>そのまま形式を選択して貼り付け
  =>貼り付けオプションを"値"にして[OK]
   =>そのまま、右クリックして、A:B列を削除
    =>A1セルをクリック
image04462.png
[図04462]

これで作業に使ったピボットテーブル関係の部分はなくなって、まっさらな365日分のシートが
出来上がりました。
image04463.png
[図04463]




Tasanさんが、ピボットテーブルでシートを作成する方法についての解説を書いてくださいました。
【解説】
まず、データですが、XL2002以降では、"日付"のみでグループ化できますが、XL2000では、"値"も入れないと
グループ化できないので"日付"と"値"を入力しています。

次に、日付や時刻に対するグループ化ですが、グループ化する項目を選択することによって元のデータに関係
なく次のアイテムが取り出せます。

 秒:0秒〜59秒
 分:0分〜59分
 時:0時〜23時
 日:1月1日〜12月31日
 月:1月〜12月
 四半期:第1四半期〜第4四半期
 年:入力した日付の西暦年

今回の場合で、シート名に月を表示したい時は、"月"を選択すればいいし、時間を表示したい時は"時"を選択
します。

次に、ページエリアに移動させた後、非表示にするアイテムを選択しています。選択しないと"<西暦/1/1"など
が、そのままシートとして作成されます。「任意の月の日付データの入ったシート」を作成したい場合は、そ
れ以外のアイテムを非表示にする必要があります。

後は、「ページの表示」でシートを作成します。
「ページの表示」は、ページエリアにあるフィールドのアイテム毎にシートを分けて集計する機能ですが、シ
ート名に使えるアイテムであれば、そのアイテムがシート名に使用されます。ですから、日付を期間でグルー
プ化していたり、アイテム名が31文字を超えていたり、":"や"/"が入っていて、「ページの表示」を使うと、
シート名には、アイテム名が反映されません。

シートが完成したら、ピボットテーブルは要らないので、列を指定して削除し、元のシートも削除して日付の
入ったブックが完成します。

ちなみに365日分のシートは、使い勝手が非常に悪いので使わない方がいいですね。(笑)
それと集計目的でデータを整理していくなら、一枚のシートに作成するべきです。だって、分けるのは「ペー
ジの表示」で簡単にできるけど、複数のシートにあるデータを一つにまとめるのは、大変な作業ですからね。

私がこの機能を使うのは、メールで入ってくるウィークデーのデータをまとめて保管しておくときに、当該月
だけの日付が入ったシートを作成するときだけです。

【おまけ】
ピボットテーブルの機能を使ってマンスリーシートを作るマクロ
Sub MonthlySht()
    Dim MyToday As Date
    Dim InputMonth As String
    Dim OldNewBookSheetsCount As Long
    Dim MyData(1, 1) As Variant
    Dim PvtItem As PivotItem
    Dim MySht As Worksheet
    Dim TargetMonth As String

    MyToday = Date
    With Application
        Do
            InputMonth = .InputBox(Prompt:="作成する月を半角で入力してね!", _
            Title:="マンスリーシートの作成", _
            Default:=Month(MyToday), Type:=2)
            If InputMonth = "False" Then Exit Sub
            InputMonth = Val(InputMonth)
        Loop Until InputMonth >= 1 And InputMonth <= 12
        .ScreenUpdating = False
        OldNewBookSheetsCount = .SheetsInNewWorkbook
        .SheetsInNewWorkbook = 1
        .Workbooks.Add
        .SheetsInNewWorkbook = OldNewBookSheetsCount
    End With
    With ActiveWorkbook
        With .Worksheets(1)
            MyData(0, 0) = "日付"
            MyData(0, 1) = "値"
            MyData(1, 0) = MyToday
            MyData(1, 1) = 1
            .Range("A1:B2").Value = MyData
            .PivotTableWizard SourceType:=xlDatabase, _
                SourceData:=.Range("A1").CurrentRegion.Address(External:=True), _
                TableDestination:=.Range("A6"), HasAutoFormat:=False
            With .PivotTables(1)
                .PreserveFormatting = False
                .AddFields RowFields:="日付"
                .PivotFields("値").Orientation = xlDataField
                .RowRange.Range("A1").Group Start:=True, End:=True, _
                    Periods:=Array(False, False, False, True, False, False, False)
                With .PivotFields("日付")
                    .Orientation = xlPageField
                    If InputMonth = "2" Then
                        If IsDate(Year(MyToday) & "/2/29") = False Then
                            .PivotItems("2月29日").Visible = False
                        End If
                    End If
                    TargetMonth = InputMonth & "月*"
                    For Each PvtItem In .PivotItems
                        If Not PvtItem.Caption Like TargetMonth Then
                            PvtItem.Visible = False
                        End If
                    Next PvtItem
                End With
                .ShowPages PageField:="日付"
            End With
        End With
        For Each MySht In .Worksheets
            MySht.Range("A:B").Clear
        Next
        Application.DisplayAlerts = False
        .Sheets(Sheets.Count).Delete
        Application.DisplayAlerts = True
    End With
    Application.ScreenUpdating = True
End Sub



今回はTasanさんにたくさんのことを教えていただきました。
本当にどうもありがとうございました。m(__)m



これもおまけで、ページの表示だけを利用して、名簿から1人1枚のシートを作成してみます。

下図のような名簿があります。
image04464.png
[図04464]

A1セルを選択してピボットテーブルウィザードで[次へ]、[次へ]、

ウィザード3/3で「新規ワークシート」を指定し、[完了]

行のエリアに"氏名"、データのエリアに"氏名"を配置
image04465.png
[図04465]

氏名をページのエリアに移動して
image04466.png
[図04466]

ピボットテーブルツールバー
 =>ピボットテーブル
  =>ページの表示
   =>"氏名"=>[OK]
image04467.png
[図04467]

シート見出しを右クリックして、"すべてのシートを選択"

A:B列を選択してコピー
 =>そのまま形式を選択して貼り付け
  =>貼り付けオプションを"値"にして[OK]
   =>そのまま、右クリックして、A:B列を削除
    =>A1セルをクリック

Sheet1を選択して削除

これで、名簿の1人1枚のシートが出来上がりました。^^
image04468.png
[図04468]


ただ、[図04464]の名簿と[図04465]の名前の並びが違うように、ピボットテーブルでは、データによっては
並びが変わりますので、元のデータと同じ並びにしたい時は、手作業で並べ替えるか、ユーザー設定リスト
を使って並べ替えをするといいと思います。こちらの「ユーザー設定リスト」で登録するやり方を参考にし
てください。

ページTOPへ