[Excel] テーブルと Excel VBA

ワークシート関数を使う時、これまでの「範囲」の変化に対応することができるテーブル機能は Excel 2007 以降の最大の機能拡張、、、なんてことも言われています。

過去にこのブログでもいくつかテーブル機能について紹介しました。

テーブルのすすめシリーズ
http://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

機能、関数で多くのメリットを提供するテーブルですが、Excel VBA の世界ではどうでしょう？

まず、表としての範囲が、テーブルというオブジェクト（ListObject オブジェクト）になるため、最終行＋空白問題（上から End モードで下に行ったとき、空白があったら止まる）、もしくは最終行＋ごみ問題（行の一番下から上に上がっても、途中にごみがあればそこで止まる）は基本的になくなります。

というのも、テーブルではテーブルの行数は End モードを使わずとも、テーブルのプロパティで確認することができるからです。テーブルの行数は ListRows.Count でわかります。

ここで標準的なテーブルを実践ワークシート協会の田中亨が推奨する「黄金テンプレート」で処理するコードを見てみると以下のようになります。

サンプルテーブル

サンプルテーブル

テーブルでも表の処理同様の VBA コードを書くことができます。
名前が田中だったら、個数X単価を合計に代入する、といった処理だと、田中亨の黄金テンプレートで考えると、

範囲はどこか？　

行は 3 から 12。もちろん、最終行の 12 が可変だとしても、End モードで特定します。

条件はなにか？

条件は、名前のセルが "田中" だったら、ですね。

処理は何か？

合計セルに個数X単価を代入する、です。

そうすると VBA のコードは以下のようになりますね。

Sub sample1()
    Dim i As Long
    For i = 3 To Range("B2").End(xlDown).Row
        If Cells(i, 2) = "田中" Then
            Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
        End If
    Next i
End Sub

これをテーブルのオブジェクトを使って、テーブルらしく書いてみると、上記の表形式とは大きく違う点があります。それは「列番号」であり、「行番号」の考え方になります。

テーブル オブジェクトの世界では、ワークシートの「A列」や「D列」は意味がなく、あくまでテーブル内での列順序の番号です。上の例でいえば、名前の列は、B列で列番号2ではなく、あくまで名前フィールドであり、フィールドの番号はテーブル内で「1」になります。

さらに、テーブルというオブジェクトは「ListRowsコレクション」からデータ部分が構成されています。テーブルの行の各1行は ListRow オブジェクトとして扱うことができます。このことより、「何行あるか、わからないけど、For Each を使えば、そのテーブルのすべての行（ListRowsコレクション）を扱うことができる」として処理可能になります。

そのサンプル コードが以下です。

Sub sample2()
    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("TestTable")  ''テーブル名がTestTableだった場合
    Dim R As ListRow
    For Each R In tb.ListRows
        If R.Range(1) = "田中" Then
            R.Range(4) = R.Range(2) * R.Range(3)
        End If
    Next R
End Sub

たぶん、R.Range(1) という書き方ってあまり使っていなかったと思います。

この R.Range(1) という書き方は、以下の書き方と同じものです。

R.Range.Item(1)

R.Range.Cells(1)

おもしろいですね。

VBAセミナースタンダードで田中亨がよく「上級者は2次元配列は使いません。だって、Excel VBA にはワークシートがあるんですから！」と言いますが、上記の表記はその流れです。（正確にはこれは一次元配列ですけど）

For Each でとってくる R は一次元配列（的なもの)です。（要素の型が違うので配列とは言えませんが、便宜上お許しを）この1次元配列は1列または一行で複数セルを選択した「セル範囲」のイメージです。今はテーブルなので、以下のような横方向の1行のセル範囲です。

[[名前] [個数] [単価] [合計] ]

この順序です。ですから、名前は (1) で、個数が (2)、、、となるわけです。

その表記方法が R.Range(1) だったり、R.Range.Item(1) だったり、R.Range.Cells(1) になるわけです。セル範囲の Range オブジェクトを配列のイメージとして扱っているんですね。

繰り返しますが、ポイントは A列、B列といったワークシート上の絶対的な番地では無い！ということです。

上記の例はサンプルなので処理や条件がシンプルなものですが、これが複雑になってくると、Cells(行, 列) でセルを指定するやり方と、R.Range(要素) で処理すべきセルを指定する方法のどちらがいいかはだんだん変わってきます。

その意味から、変数を使って、テーブルの列順序を定義すると、さらに可読性が高まります。（IMEオン・オフの操作があるので日本語変数がいいわけではありませんが、サンプルとして定義します）

Sub sample3()

    Dim tb As ListObject

    Set tb = ActiveSheet.ListObjects("TestTable")

    Dim 名前 As Long, 個数 As Long, 単価 As Long, 合計 As Long

    名前 = tb.ListColumns("名前").Index

    個数 = tb.ListColumns("個数").Index

    単価 = tb.ListColumns("単価").Index

    合計 = tb.ListColumns("合計").Index

    

    Dim R As ListRow

    For Each R In tb.ListRows

        If R.Range(名前) = "田中" Then

            R.Range(合計) = R.Range(個数) * R.Range(単価)

        End If

    Next R

End Sub

たった5～6行の処理のための変数宣言と変数設定で7行は、、、と思いますが、通常はもっと条件や処理が複雑になるので、これだけで「行数が多い、無駄だ」と判断するのは早計だと思います。 For Each から Next R までの処理の「可読性」を見てください。実にわかりやすい表記になっています。

たぶん、テーブル ListObject オブジェクトを VBA で扱うのは、このオブジェクト変数を使ってテーブルを設定し、テーブル内のフィールドの順序を変数として設定していくのが一番良い方法だと思います。

ここでテーブル機能で重要な「構造化参照」が出てきてませんよね？

いろいろ試した結果、この「構造化参照」は VBA を編集するツールである VBE で実装されていないため、インテリセンスのような形で、文字通りの「構造化参照」という機能は実装されていないようです。テーブル名を指定すれば、そのテーブルにあるフィールドが表示される、、、ような機能は VBE には無い、ということです。

では、VBE に期待しないで表記したらどうなるでしょう？

ここでも Excel の Range オブジェクトの実装、、、「すげー」となるわけです（笑

VBE では構造化参照のメリット享受できないので使う理由がないのですが、無理やりやると以下のようなコードを書くことができます。

Sub sample4()

    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序

    Dim R As Range

    For Each R In Range("TestTable").Rows

        If R.Cells(1) = "田中" Then

            R.Cells(4) = R.Cells(2) * R.Cells(3)

        End If

    Next R

End Sub

テーブルを ListObject として宣言しない分だけ行数が短いですが、R.Cells(1) などを番号ではなくフィールド名で、、、となると、宣言や設定のためそれなりに行数が増えます。さらに、構造化参照が VBE でサポートされていないために、複雑、長くなればなるほどめんどうなことが発生してきます。(*1 最後に追記してます）

以上のことより、通常、テーブルを VBA で扱うのであれば、テーブルは ListRow のかたまりであり、テーブルを ListRows コレクションとして処理するのが王道でしょう。

そのときの For Each として１つ１つ処理するのは ListRow オブジェクトであり、その ListRow オブジェクトの扱いは「一次元j配列」的になる、と言えるでしょうね。

今後、データ接続でテーブル形式でデータを取り込むことが多くなります。そのときは、上記の考え方でテーブルをマクロで扱うことも多くなると思います。

(*1) 構造化参照が少なすぎるとおもったので追記。
構造化参照方式で以下のような表記が可能です。

Range("テーブル1[名前]")

上記でテーブル１の名前フィールドのデータ部分の範囲を指し示すことができます。
しかし、この Range(構造化参照) は、あくまで Range による範囲（つまり、named range) のため、ListObject との関連がありません。

そのため、Range("テーブル[名前]").Column は、ワークシート上の列番号を返します。


[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。

Read More

曜日と表示形式とTEXT関数とFormat関数

実践ワークシート協会主催の Excel VBA セミナーでは、講師の田中先生が毎回必ず聞く質問がいくつかありますが、そのうちの一つが「曜日を表す表示形式」に関連する内容です。

Excel VBA セミナー ベーシック
http://officetanaka.net/seminar/seminar1.htm

なぜ VBA セミナーで表示形式？と思われるかもしれませんが、VBA プログラミングにおいて、日付＝シリアル値から曜日を知るためには、曜日を返す専用の関数はなく、表示形式を利用して曜日を判定しなくてはならないからです。その関数が　Format 関数です。

Format関数 （Excel VBA の関数）
http://officetanaka.net/excel/vba/function/Format.htm

この Format 関数はワークシート関数の TEXT 関数とほぼ同じ動きをします。

TEXT関数 （Excel ワークシート関数）
http://officetanaka.net/excel/function/function/text.htm

そして、このFormat関数やTEXT関数を使う上で知っておかなければならない知識が「書式記号」です。

曜日の鉄板ネタとして、書式記号の a を使って、aaa や aaaa で日本語での曜日をシリアル値から表示できます。ddd や dddd を使えば英語での曜日をシリアル値から調べることができます。

曜日を表示する
http://officetanaka.net/excel/function/tips/tips38.htm

曜日の表示で WEEKDAY関数と CHOOSE関数を使った数式を見ることがありますが、TEXT関数が一番簡単だと思います。ただし、aaa といった書式記号を覚えていなくてはなりません。

この書式記号、書式設定のユーザー定義書式でも使います。
セルを選択して Ctrl+1 でセルの書式設定ダイアログボックスが表示され、表示形式で書式記号を使って、セルに入力された表示方法を制御します。

セル内に入力されたデータがシリアル値になっていれば、このように曜日を調べることが可能です。シリアル値はExcelが内部で持っている万年カレンダーのようなもので、曜日のみならず、うるう年も管理しているので、うるう年の計算をしてチェックするような必要はありません。

Office TANAKA サイトも上記のURLページ以外にも多くの表示形式の記事があります。それだけ、表示形式や書式記号は深い、と言えるでしょう。

さて、冒頭に田中先生が表示形式についてかならず質問する、と書きましたが、この表示形式・書式記号の aaa を知っている人が年々「減って」きています。Excel 鉄板ネタの一つなのですが、本当にこれを知っている人が減っているのです。

あらためて、きっちりと伝えるべき技術は定番として伝え続けなければならないんだ、と感じる1件です。

ちなみに Power Query で日付から曜日を判定するには、WEEKDAY関数と似たような動きをする Date.DayOfWeek がヘルプの数式カテゴリから見つかりますが、ワークシート関数同様に TEXT 関数に相当するメソッドを使ったほうがよさそうです。Date.ToText で "ddd" で書式設定して日本語の曜日を表示させることが可能です。

Date.ToText のヘルプ
(Power Query が機械翻訳で電源のクエリになっているのはご愛嬌）

なお、この曜日については SharePoint のリストでも同様の動きと扱いをすることができます。これもチェックですね。

ExcelユーザーのためのSharePointリスト「集計値」列

Read More

シート別にデータを分ける – VBA/マクロとピボットテーブルの活用

実践ワークシート協会の通常業務では、なるべく「元データ」は Office 365 の SharePoint か、SharePoint Access アプリを経由して SQL Azure に入力して、クラウド上でまとめるようにしています。

生のデータがクラウド上にあれば、あとは Excel のデータ接続を使って生データをワークシートに取り込み、自分の欲しい形に加工すればよいわけです。おおよそオートフィルターかピボットテーブルで目的は達成でき、ちょっと複雑（複数テーブルを使う、列を入れ替える、削除するなど）になれば、Power Query や Power Pivot を使います。

(Office 365 SharePoint Online のリストアイテムの取り込みは OData フィードを使うと幸せになれることが多い。くわしくはこちらを参照)

定型の業務であれば、一度つくったピボットテーブルや表を Excel Services の Excel Web Access を使って SharePoint Online のポータルに貼り付けておけば、ローカル(PC)で Excel を開くことなく状況の確認が可能で、協会のメンバーと情報を共有できます。

Excel ユーザーのための Excel Services – Office 365

強いて言えば、協会のメンバーは全員が Excel を使いこなすことができるので、準備したデータを「あ～したい」、「こ～したい」、と思ったら自分で加工できるのが大きな強みでしょう。

このように協会ではピボットテーブルやオートフィルターを活用・多用するので、最新データは「データ更新」ボタンを押してクラウド上のデータを Excel で確認するか、条件が変われば自分でピボットテーブルやオートフィルターを変更するわけですが、一般的にはそうではなく、日次や月次などで「決まった形」でワークシートを用意する、印刷して提出しなければならないケースを聞くことが多いです。（繰り返しになりますが、協会内部の業務ではこのようなケースはありません。）

例えば、支店別の売り上げとか、製品別の売り上げとか、年月での売り上げでまとめたワークシート。[東京支店][大阪支店][名古屋支店] といったワークシートが並んでいたり、[2015年4月][2015年5月][2015年6月] というようなワークシートが並んでいるブックです。

比較・確認する数字などは定例の会議で確認する項目などはほとんど決まっているのでピボットテーブルを駆使した方が最新データへの更新も考えると楽なのに、、、と思うこともしばしばありますが、もうそうやって何年も十何年もその資料を使っていると、なかなか変更するのも大変です。（変更したほうがいいんですけどね）

だったら、その作り方で楽をしよう、というのが今回のテーマです。Excel VBA と ピボットテーブル の話になります。

グループ化と明細の表示を使う

Excel のピボットテーブルには「明細の表示」という機能があります。通常はピボットテーブルによる集計済みの項目の集計元データを、別のワークシートに作成し内容を確認するためのものです。

たとえば、日付項目を「年・月」でグループ化したピボットテーブルを作り、年月の集計値をダブルクリックすると、その年のその月のデータだけが「明細の表示」としてワークシートが追加されます。

クラウドやサーバーからデータを Excel に取り込み、年・月のシートに分割する、というような定型の業務がある場合は、オートフィルターを使ってコピーする方法より、このピボットテーブルと明細の表示を使うほうがあるかに手間がかかりません。そして、この手作業を VBA で自動化すれば、一瞬で年月ワークシートを作成することができます。元データは SharePoint や SQL Azure といったサーバーから取ってきますので、その時点での最新データから年月ワークシートを作成することが可能です。

グループ化は日付だけではなく、支店名や、製品グループなどの文字列データでも有効です。ピボットテーブルのグループ化の機能が強力なのは、あらかじめどんなユニークデータがあるかを知っている必要がないことです。この性質を利用して「重複データの削除」のような使い方も可能です。

重複したデータをチェックする - ピボットテーブルの応用

マクロ / VBA でピボットテーブルを作る

実践ワークシート協会代表理事/Office TANAKA の中の人 田中亨曰く、 マクロ記録で恐ろしい(難解な)結果が出る代表格の１つであるピボットテーブルの作成ですが、実は、非常にシンプルなコードでまとめることができます。このあたりは田中亨著の「Excel VBA 逆引き辞典」にシンプルなサンプルコードが紹介されています。

1回かぎりであれば手作業で十分ですが、毎回毎回同じことを繰り返す、作成するシートの数が多いようであれば、VBA を用意してそれを実行するだけで、ある条件でワークシートを作成することを自動化可能になります。

手順としては大きく4つです。

1. 元の表(テーブル)から条件を設定してピボットキャッシュ、ピボットテーブルを作成する
2. ピボットテーブルから詳細の表示を使って詳細ワークシートを作成
3. 作成したワークシートにあるテーブルを並べ替え
4. 作成したシートの名前を変更

それぞれの手順を見てみましょう。

例として以下のようなテーブルから年月別のワークシートを複数作成します。(サンプルは 10,000件）

1. 元のテーブルから条件を設定してピボットテーブルを作成する

Sub CreatePivotTable()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'Dからピボットキャッシュとテーブルの作成
   With nWS.PivotTables(1)  'ピボットテーブルのフィールドリスト操作を以下で行う
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。マクロ記録で調べるとよい。
End Sub

実行結果は以下のようになります。

ピボットテーブルから年月シートを VBA/マクロで作成する

ピボットテーブルができてしまえば、あとは [詳細の表示] に相当するコードでワークシートを追加していきます。大まかな手順は以下になります。

2. ピボットテーブルから[詳細の表示]を使って、各月のワークシートを作成する

3. 作成したワークシートを日付でソートする

4. シートの名前を年月にする

5. 最後にピボットテーブルのシートを消す

ここは一気にいきます。

上記ピボットテーブルを作成したソースに 2, 3, 4, 5 を実現するコードを加えたものが以下です。

Sub CreateMonthlySheets()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'ピボットキャッシュとnWSシートにピボットテーブルを作成
   With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定。各年月シート作成のためのフィールドで「金額」そのものには意味はない。
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。設定はマクロ記録で調べるとよい。
   Set D = nWS.PivotTables(1).DataBodyRange  'ピボットテーブルの値の範囲（金額集計）を D とする
   Dim Item As Range
   For Each Item In D  '金額集計の範囲の数だけ For Each を回す。個々のセル(Range)は Item に入る
       Item.ShowDetail = True　　'詳細の表示を行う。このとき追加されたシートがアクティブシートになる
       With ActiveSheet.Sort.SortFields　'追加されたシート(アクティブシート）で日付(A2)のソート条件を設定する
            .Clear  'おまじない
            'A2の日付の値で、昇順(xlAscending)、標準(xlSortNormal) でソートする条件を設定する
            .Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       End With
       With ActiveSheet.Sort　'アクティブシートの Sort オブジェクトでソートを実行する
            .SetRange Range("A1").CurrentRegion　'A1を含むデータのかたまり（範囲）を対象とし
            .Header = xlYes　'一番上の行はヘッダーとして
            .Orientation = xlTopToBottom　'上から下に向かって
            .Apply　'ソートを実行
       End With
       'セル A2 の日付から年月の文字列を作成し、シートの名前にする
       ActiveSheet.Name = Format(ActiveSheet.Range("A2"), "yyyy") & "年" & Format(ActiveSheet.Range("A2"), "m") & "月"
    Next Item
    Application.DisplayAlerts = False　'ピボットテーブルのシートを消す際のアラート表示をオフにする
    nWS.Delete　'ピボットテーブルのシートを消す
    Application.DisplayAlerts = True　'アラート表示を元に戻す
End Sub

10,000件程度であれば、数秒で年月別シートの作成が可能です。

最近は Power Query や Power Pivot といった機能の追加・拡張が Excel では多いですが、ワークシートを消す、新規に作る、追加するといった作業は VBA でしかできません。
そういった作業の自動化の要望がある限りは VBA がなくなることはないでしょうね。

ただ、ちょっとだけ「手間」を入れると Power Query の機能だけでも上記のことが可能になります。
機会があれば、Power Query で「年月別のシートを作る」手順をご紹介したいと思います。

Read More