【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え

photo credit: Waste separation via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについてお伝えしています。

全体としては

取引先マスタの取引先の分だけ新規ブックを作成しそこに請求書ひな形シートをコピーする
コピーしたシートの名称を「請求書ひな形」から「請求書」に変更する
各社の「請求書」シートに「請求データ」から該当のデータを抜き出して転記する
各社の「請求書」シートに「取引先マスタ」から必要な項目を転記する
各社の「請求書」シートの不要な行を非表示にするなど表示を調整
各社の「請求書」シートをPDF形式で保存する

という流れになりますが、前回は6番目であるPDF出力やファイル名の指定保存などについてお伝えしました。

429 Too Many Requests

今回から、番号でいうと3番目以降を進めていきますが、脱初心者向けということで、計算回数が少なめのクールな方法を模索してみたいと思いますよ。

まずは、計算回数が少なめにするための方針について明確にしつつ、取引先ごとのデータを上手に抽出できるように「請求データ」のデータについて、CurrentRegionプロパティでデータ範囲を塊で取得して、それをSortメソッドで並び替えをする方法についてお伝えしていきます。

では、行ってみましょう。

より計算回数の少ない条件判断とコピー＆ペースト
1. 一行ずつ判断して一行ずつ転記をする場合
2. 並び替えをしておいて該当範囲で転記をする場合
指定範囲の並べ替え
まとめ
連載目次：【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集

より計算回数の少ない条件判断とコピー＆ペースト

請求についてのデータは

納品日｜取引先名｜品目｜単価｜数量｜価格

で構成されているとします。

各月ごと、取引先ごとに請求書を作っていくことになりますので、納品日と取引先名の二つの列の値を条件に、品目から数量までの三つの列を実際にひな形に転記をしていけば良いということになります。

（価格は単価と数量で計算できますから、実際のデータとしては不要ですね。）

一行ずつ判断して一行ずつ転記をする場合

プログラムの方針としては

取引先マスタの各取引先について請求書を作る
各取引先について2行目から一行ずつデータを舐めていく
目的の納品月かつ目的の取引先名のデータであれば請求書に一行ずつ転記していく

という、一行ずつ判断して一行ずつ転記する方法というのが最も考えやすいかも知れません。

たとえば、取引先数が2で、請求データ数が9であれば

その場合の条件判断の回数と、転記の回数はそれぞれ

– 判断の回数：取引先数×請求データ数＝18
– 転記の回数：請求データ数＝9

となります。

並び替えをしておいて該当範囲で転記をする場合

それでも良いのですが、より少ない処理で実現することができそうなので考えてみましょう。

作戦としては

請求データを日付と取引先名で並べ替えをしておく
2行目から一行ずつデータを舐めていく
新たな納品月と取引先名の組み合わせが登場したら都度請求書を作る
目的の納品月かつ目的の取引先のデータの範囲をまるっと請求書に転記

という流れです。

これで、前述の方法と同様に判断の回数と、転記の回数を計算してみると

– 判断の回数：請求データ数=9
– 転記の回数：納品月と取引先名の組み合わせの数=2

となります。

手作業でやる場合を思い出してほしいのですが、一行ずつコピーをするよりも、範囲でコピーをしたほうが作業量は少なくなりますよね？

プログラムで実行する場合も同じことが言えまして、コピー＆ペーストの回数は範囲の大きさに関わらず回数が少ないほうが処理が速くなります。

この例ではデータ数が10行もないですからいいですが、これが1000行、10000行とか、そして取引先が数十社とかになってくるとその計算数は大きく変わってきます。

ということで、脱初心者を目指して後者の方法でプログラムを組んでいきたいと思います。

指定範囲の並べ替え

では、請求データの並べ替えを進めていきましょう。

CurrentRegionでデータの塊の範囲を取得する

並べ替えをする場合には、まず並べ替えの範囲を取得する必要があります。

データがある最終行を「Worksheetオブジェクト.Cells(Rows.Count, 列数).End(xlUp).Row」などで調べた上で範囲を直接指定する方法でも良いのですが

【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す

今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

エクセルVBAにはデータがある範囲を勝手に判定して取得するCurrentRegionという便利なプロパティがありますので、それを活用します。

Rangeオブジェクト.CurrentRegion

とすることで、そのRangeオブジェクトを含むデータの塊の範囲を取得することができます。

エクセルでいうと Ctrl + Shift + * のショートカットキーを使った場合と同じですね。便利です。

したがって今回の場合は例えば

Range(“A1”).CurrentRegion

とすれば、A1からF10の範囲が取得できるというわけですね。

Sortメソッドで範囲を並べ替える

範囲を並べ替えるときはSortメソッドを使います。

Rangeオブジェクト.Sort Key1:=1番目に優先されるキー,Key2:=2番目に優先されるキー,Header:=先頭行の指定

Key1、Key2には並べ替えのキーをRangeオブジェクトで指定します。今回はB列の取引先名、A列の納品日という優先順位で並び替えをしてみますので、それぞれRange(“B2”)、Range(“A2”)などと指定すればOKです。

なお並び替えのキーはKey3まで設定できます。

Headerは範囲の先頭行を見出しとして扱うかどうかについてを指定します。

– xlYes：先頭行を見出しとして、先頭行以外を並び替え対象とする
– xlNo：先頭行も含めて、範囲全体を並び替え対象とする（既定値）

今回は、CurrentRegionで取得した範囲の先頭行は見出しですから、xlYesを指定します。

Sortメソッドには、並び順を昇順または降順を指定する、数字と文字列を区別する、など設定できるオプションが他にもありますので、用途に合わせて調べてみて下さいね。

Excelの醍醐味！明日からできるデータの並べ替え、検索、置換、抽出

プログラミング初心者を対象にExcelマクロ／VBAを使った業務自動化プログラムの作り方を一から解説します。今回は、データ操作のメソッド、Sort、Find、FindNext、Replace、AutoFilter、AdvancedFilte...

範囲を取得して並び替えをするプログラム

CurrentRegionプロパティで範囲を取得し、Sortメソッドで並び替えを行うプログラムはこちらになります。

Sub wsDataSort()

With ActiveWorkbook.Worksheets("請求データ")

    '請求データをA列納品日、B列取引先で並び替え（見出しは範囲に含めない）
    .Range("A1").CurrentRegion.Sort key1:=.Range("B2"), key2:=.Range("A2"), Header:=xlYes

End With

End Sub

Withで対象シートオブジェクト「ActiveWorkbook.Worksheets(“請求データ”)」を省略するように書いています。

並び替えの対象はA1セルを含むデータの塊ですから、Rangeオブジェクト「.Range(“A1”).CurrentRegion」が並び替えの対象となっています。

セルB2、A2の順で並び替えの優先度を指定、見出しは並び替え対象範囲としてはは含めないという形で並び替えを実行しています。

実行結果としては