tamago8610のブログ

日々の勉強を備忘録としてブログを書いています。

VBAでデータ集計を行う方法

※注意 この記事は、投稿者が勉強用に作成している備忘録のようなものです。 メソッドやプロパティ、変数に代入されている値の説明など省略されている箇所があります。

 

要約

下記、フォーマットのように、集計を行いたい月を選択して、取引先別で平均・合計売上金額を割り出したい場合の解説を行います。

 

 

f:id:tamago8610:20210519152334p:plain

(例)合計と平均を求めるフォーマット

月(選択)のセルには、あらかじめ入力規則で月を選択できるようリストボックスを作ってあります。

 

集計ボタンを押せば、担当ごとの売上金額が自動的に入力されるようにし、あわせて合計・平均欄も入力される仕組みです。

合計・平均欄には、それぞれSUM関数、AVERAGE関数を仕込んであります。

 

下記の写真のような100件くらいあるデータから集計を行うということにしましょう。

f:id:tamago8610:20210519152415p:plain

売上の元データ

 

集計を行う流れ

 該当月が選択されているかを確認する

選択されていなければ、入力を促すメッセージを表示。

選択されていれば、次の処理に進む。

取引先データを最後の行まで取得

担当者データを最後の列まで取得

元データから検索するために最後の行まで検索を行う

該当月で、取引先のデータ、担当者が一致すれば、売上金額を取得する

フォーマットに担当者・取引先別の売上金額を記述する

 

検索する該当月の日数を取得する

月によって、一カ月は何日あるか異なります。

また、1年は365日とは限らず、うるう年というものがあるので、毎年2月は28日まであるともいいきれません。

月の最後の日付を取得する場合、DateSerial関数を使うと便利です。

 

【構文】DateSerial(Year, Month, Day)

※引数Yearに年、Monthに月、Dayに日付を入力します。

 

(例)2017年5月1日を取得する

   DateSerial(2017,5,1)

 

日付を引数にそのまま入れるだけですね。

 

(例)2017年5月1日の前日(4月末日)

DateSerial(2017, 5 + 1 , 1) -1  →2017/04/30

 

これで、4月の末日を取得することができます。

用意する変数

※入力する手間を省くために、用意しておいた方がよい変数も含めます。

  •  元データシートを格納する変数 
  • 検索する日付の開始日を格納する変数
  • 検索する日付の終了日を格納する変数
  • 取引先の最終行を格納する変数
  • 担当の最終列を格納する変数
  • 元データの最終行を格納する変数
  • 取引先データを格納する変数
  • 担当者を格納する変数
  • 売上金額を格納する変数
  • ループ変数3つ

入力するコード

該当月が入力されているかどうか調べ、メッセージを表示させる説明は省略。

 

表示させるデータ件数が多ければ、画面のちらつきを防ぐために  Application.ScreenUpdating = False (画面の更新をしない)
Application.EnableEvents = False (イベントを一時的に無効)

を設定しておいた方が良いでしょう。

 

処理が終われば、ScreenUpdating、EnableEventsをTrueに戻すことを忘れないように。

また、再計算を手動にする設定

Application.Calculation = xlCalculationManual 

も記述しておいた方が、処理が早くなります。

 

処理が終われば再計算を自動に戻しましょう。

値をxlCalculationAutomaticにします。

 

Dim sDate1 As Date '月の始めの日付
Dim sDate2 As Date '月の末日
Dim i, k, j As Long 'i = 行を取得、k = 列を取得、j = データの行を取得
Dim maxRow As Long
Dim maxCol As Long
Dim maxData As Long '元データの最後の行
Dim dDate As Date '比較する日付データ
Dim torihiki As String '取引先データ
Dim tanto As String '担当データ
Dim goukei As Long '売上金額
Dim shtDat As Worksheet 'データシート

sDate1 = DateSerial(Range("C3").Value, Range("D3").Value, 1)
sDate2 = DateSerial(Range("C3").Value, Range("D3").Value + 1, 1) - 1
maxRow = Cells(Rows.Count, 2).End(xlUp).Row
maxCol = Range("XFD6").End(xlToLeft).Column
Set shtDat = Worksheets("データ")
maxData = shtDat.Cells(Rows.Count, 1).End(xlUp).Row

f:id:tamago8610:20210520182506p:plain

(例)データ集計を行っているコード

f:id:tamago8610:20210520183304p:plain

繰り返し処理の流れ

①取引先データを格納するために、始点のセルから最終行まで繰り返す。

②担当者の該当列番号を最後まで繰り返す。

③元データシートを最終行まで繰り返す。

 

VBAを使ってExcelからCSVファイルを出力する方法

※注意 この記事は、投稿者が勉強用に作成している備忘録のようなものです。 メソッドやプロパティ、変数に代入されている値の説明など省略されている箇所があります。

 

 

ExcelからCSVファイルを作成する方法に、普通に名前を付けて保存でCSVファイルを作成することはできます。

(※ただし、Excelで表示した際に、数値データの頭の数字「0」が消えてしまいます。メモ帳やテラパッドで開いたときは大丈夫でした。)

ここでは、VBAを使ってExcelからCSVを出力する方法を記述しています。

 

前回と同様に参照設定でADOオブジェクトを使用できるように参照設定をします。

前回の記事:CSVファイルを読み込む - tamago8610のブログ

 

用意する変数

  • ADODB.streamオブジェクトを生成する変数
  • シートの最終行を取得する変数
  • 書き込むCSVファイルのパスを格納する変数
  • Streamに書き込むため、Excelシートから1行ずつデータを格納する変数
  • 行、列のデータを準に取得するための繰り返し変数

 

処理の流れ

①シートの最終行を変数に格納・出力ファイルのパスを変数に格納

②出力するCSVファイルの改行コード、文字コードを設定、Streamを開く

③シートのセルの値を1行ずつカンマ区切りで結合してStreamに追記

④Streamファイルに出力し、Streamを閉じる

 

 

書き込むコード

f:id:tamago8610:20210509191948p:plain

CSVファイルを出力する

出力するCSVファイルの文字コード、改行コードをADODB.streamオブジェクトで設定していきます。

ADOオブジェクトのプロパティの説明等は前回のブログに掲載。

 

WriteTextメソッド

文字列をStreamに書き込みます。

 

【構文】

Streamオブジェクト.WriteText Data [,Options]

 

【引数】

Data … 書き込む文字列データを指定します。

Options …

  adWriteChar  文字列データを改行せずに書き込みます。(既定値)

  adWriteLine 文字列データを改行して書き込みます。

 

(例)adoStr.WriteText strBuf,adWriteLine

変数「adoStr」に、Streamオブジェクト。変数「strBuf」にCSVに書き出すデータを格納。

 

※注意 WriteTextメソッドの引数、Optionsは基本adWriteLineを設定した方が良い。

WriteTextメソッドの引数Optionsを省略すると、既定値のadWriteCharになります。

意図して改行せず1行で文字列データを出力するのでしたら、いいのですが、データ量が多いとすべての文字列データがCSVファイルに収まり切れなくなります。

以下の画像がその例です。

f:id:tamago8610:20210509204928p:plain

Optionsを省略、または既定値にした場合

ファイルに出力処理をするSaveToFileメソッド

Streamオブジェクトからファイルにデータを書き込みます。

 

【構文】

Streamオブジェクト.SaveToFile FileName,SaveOptions

 

【引数】

FileName  出力するファイルをパスで指定。

 

SaveOptions  ファイルがまだ存在しない場合は新しいファイルを作成しますが、ファイルが存在する場合には上書きするかどうかを指定する。

 adSaveCreateNotExist  上書きなし

 adSaveCreateOverWrite  上書きする

 

(例)adoStr.SaveFile path, adSaveCreateOverWrite

 

 

CSVファイルを読み込む

※注意 この記事は、投稿者が勉強用に作成している備忘録のようなものです。

メソッドやプロパティ、変数に代入されている値の説明など省略されている箇所があります。

 

 

 

 

参照設定

文字コードが、UTF-8、その他の文字コードも読み込めれるようにADOオブジェクトを使用して使った方法を扱う。

 

文字コードがあわないCSVファイルをExcelで読み込むと文字化けを起こします。

※正味、CSVファイルをExcelで観覧したいだけならExcel機能の「外部データの取り込み」で取り込んでもいい。

Microsoft ActiveX Objects バージョン番号(最新の選ぶ) Library」にチェックしてOK

 

VBAコードの流れ

 

宣言しておく変数
  • ADODB.streamオブジェクトを生成
  • CSVファイルを開く為のパスを格納する変数
  • 1行ずつ格納するための変数
  • さらに、1行ずつ格納した変数からカンマで区切った配列を代入する変数(Variant型)
  • ループするためのi変数

 

CSVファイルを開くための準備

②ファイルを開いてCSVファイルの最後の行を読み込むまで、1行ずつシートに記述するループ

③ファイルを閉じる

 

f:id:tamago8610:20210506202832p:plain

(例) CSVを読み込み、シートに記述するコード

 

Streamオブジェクトのプロパティ

Charsetプロパティ   文字コードを指定する

UTF-8”、”Shift-JIS"、"Unicode"など (※Unicodeはデフォルト)

 

LineSeparatorプロパティ   …改行コードを指定

[指定できる定数]

  • adCR (改行復帰) カーソルを左端に戻す
  • adLF(行送り) カーソルを下の行に移動する
  • adCRLF(CR + LF) 上記2つをあわせたやつ

読み込むCSVファイルの改行コードを事前に調べておく必要がある。

CSVファイルを開いて、どれになっているかを確認。

f:id:tamago8610:20210506210011p:plain

これだったら定数adLFにする

 

Openメソッド   …Streamオブジェクトを開く(ファイル操作開始)

 

LoadFromFileメソッド   …引数で指定したパスのファイルをStreamに読み込ませる

 

Closeメソッド   …Streamオブジェクトを閉じる(ファイル操作終了)

 

テキストを読み込む抜粋

Do Until adoStr.EOS

 strBuf = adoStr.ReadText(adReadLine)

 ~~~~~~~~~~~~~~~~~~

 '処理内容

Loop

※変数「adoStr」にはADODB.streamオブジェクトが入っているとしています

 

EOSプロパティ  …

 読み取りがStreamの最後(End Of Stream)に到達しているかどうかを表します。

 

ReadTextメソッド   …

 Streamオブジェクトから1行または全文を返します。

【構文】

Streamオブジェクト.ReadText(引数:NumChars)

 

【引数NumChars】

adReadAll 全文を返します。省略した場合、デフォルトはこれ。

adReadLine 1行を返します。

 

配列の要素の最大数を取得してくれるUBound関数

UBound関数で配列のなかに、いくつ要素が格納されているかわかります。

ただし、(0)、(1)、(2)... と0から配列の要素を数えるので、配列に3つの要素が入っていたとしても「2」の数値を返します。

 

そのため、上記コードの例で、

Cells(i,1).Resize(1,UBound(arrBuf) + 1).Value = affBuf

とUBound関数で返される数値に「1」を足しています。

CSVを取り込んだ時の表示形式

CSVファイルを読み込むと、数値や日付データが思い通りに読み込むことが出来なくなる時があります。

具体的には、数値「001」の値を読み込んだあと、数値をダブルクリックでセルを編集モードにし、何も値を変更しないでEnterキーを押しただけで数値の頭にあった「0」が消え「001」が「1」になってしまいます

f:id:tamago8610:20210508215044p:plain

表示形式が変わってしまう

 

それを防ぐため、以下のような記述が必要です。

 

Columns("A").NumberFormatLocal = "@"

 

ただし、日付データの場合は、NumberFormatLocalで表示形式を指定しても、表示形式は確かに変更されますが、逆にセルをダブルクリックし、編集モードにしなければ意図した表示形式で表示されません。

なので、CSVファイルを読み取るとき、日付データは、NumberFormatLocalではなく、Format関数で表示形式を設定しましょう。

 

f:id:tamago8610:20210508222941p:plain

CSVで読み込んだ日付データ

VBAでWordを動かすときによく使うコードまとめ

 ※注意

自分用にまとめた記事なので、かなり省略しています。

 

 

 

Wordと連携する前に理解しておかなければいけないオブジェクトの構造階層

f:id:tamago8610:20210501203441j:plain

Wordのオブジェクト階層

Rangeオブジェクトは、Wordに入力された文字のことを指します。

WordのApplication内に、Documentオブジェクトの文書があり、さらに段落のParagraphs(読み:パラグラフ)オブジェクト、表のTablesオブジェクトがあり、それぞれにRangeオブジェクトが入っています。

Paragraphsオブジェクト、Tablesオブジェクトは複数形のSが付くので、コレクションになります。

 

オブジェクトライブラリを参照設定に追加

まず、Excelで参照設定を行う。

Microsoft Word xx(バージョン番号) Object Libary」にチェックをする。

 

Wordを開いてドキュメントを操作できるように宣言 

f:id:tamago8610:20210502101246p:plain

Word文書を開くための宣言

Wordアプリケーションを格納する変数を宣言して、さらにWordアプリケーションを格納した変数を使って、Word文書を宣言する感じです。

これで、変数「wdDoc」を使ってWordを操作することが可能になりました。

 

□Wordアプリを宣言

Dim wdApp As Word.Application

Set wdApp = New Word.Application

 

□Documentオブジェクトを宣言

Dim wdDoc As  Word.Document

Set WdDoc = wdApp.Documents



Word文書を操作する主なメソッド

Documents.Add     ・・・新規の文書を開く 

Documents.Open     ・・・既存の文書を開く 

(例)wdDoc.Documents.Open(path & "¥sample.docx")

 

InsertBefore     ・・・段落の先頭位置に文字列を記入 

InsertAfter     ・・・段落の終了位置に文字列を記入 

Save     ・・・上書き保存  

Close     ・・・ドキュメントを閉じる

 

(例)ドキュメントを保存する時、一緒に上書き保存をする

WdDoc.Close SaveChanges:= True 

※引数 SaveChanges:= は、上書き保存をするかどうかを決めます。

「SavesChanges:= False」は保存しない。

 

Quit     ・・・アプリケーションを終了する

 

よく使うコード

□文書の0~50文字目を取得

wdDoc.Range(Start:= 0, End:= 50).Text

 

□12段落のすべての文字列を取得

WdDoc.Paragraphs(12).Range.Text

※取得したい文字は段落(Paragraphs)の中の文字(Range)だから忘れずに、Range.Textをつける

 

□12段落の3~10文字目を取得する

wdDoc.Range(wdDoc.Paragraphs(12).Range.Start + 2, _

   wdDoc.Paragraphs(12).Range.Start + 10).Text 

※特定の段落のなかの文字を取り出したい場合は、ドキュメント全体から取り出す方法と書き方が異なるので注意する。

 

 □1段落目の先頭に文字列を挿入する

wdDoc.Paragrahs(1).Range.InsertBefore "おはよう"

 

□表内のセルに文字列を挿入する

wdDoc.Tables(1).Cell(Row:=1,Colum:=1).Range.InsertBefore "メモメモ"

※ドキュメント内に表が2つあり、2つ目の表が対象の場合は、Tables(2)とする。

表は「Cells」ではなく、「Cell」であるという点に注意する。

引数のRowは行。Columは列。

 

例のように、文字を取得する場合は「.Text」が必要ですが、Wordドキュメントに文字を入力する場合は、「.Text」は要りません。

 

□印刷プレビュー

wdDoc.ActiveDocuments.PrintPreview

 

□ドキュメントを印刷する

wdDoc.PrintOut

 

□PDFで出力する

wdDoc.ExportAsFixedFormat OutputFileName:= path & "¥sample.pdf", _

ExportFormat:=wdExportFormatPDF