Excel VBA スタンダード試験対策4 ファイルの操作、ワークシート関数

前回：ステートメント

前回に引き続き、Excel VBA スタンダードについて、公式テキストで勉強した内容をまとめる。

今回はファイルの操作とワークシート関数について。

本日、公式テキスト付属の模擬問題を解いてみた。

コードを読み解き、空欄の穴埋めをする形式の問題が多い。

どのような形式で出題がされるのかは実際に解いてみなければわからないので、

やはり実際に問題を解くためにも公式テキストは購入した方が良い。

ブックを開く
ブックを保存する
ファイルをコピーする
フォルダを操作する
合計値を求める
特定のデータの数を数える
特定のデータが存在し、同じ行にある数値を合計する
降順で指定した大きさの数値を調べる
昇順で数値が何番目にあるか調べる
表の左端列を検索し、該当する行の指定した列の値を返す
表の右端列を検索し、該当する行の指定した列の値を返す
指定した月の月末の日付を調べる

ブックを開く

ブックを開くにはOpenメソッドを用いる。

Workbooks.Open "C:¥Test¥test.xlsx"

1	Workbooks.Open "C:¥Test¥test.xlsx"

ブックを保存する

ブックを保存するときはSaveAsメソッドを用いる。

ActiveWorkbook.SaveAs "C:¥Test¥test.xlsx"

1	ActiveWorkbook.SaveAs "C:¥Test¥test.xlsx"

ファイルをコピーする

ファイルをコピーする場合はFileCopyステートメントを用いる。

「FileCopy コピー元 , コピー先」のように指定する。

また、コピー先のファイル名の指定時に名前を変更してコピーすることもできる。

コピー先のファイルがすでに存在する場合はそのファイルに上書き保存される。

FileCopy "C:¥Test¥テスト.xlsx", "C:¥Test¥テスト2.xlsx"

1	FileCopy "C:¥Test¥テスト.xlsx", "C:¥Test¥テスト2.xlsx"

フォルダを操作する

フォルダを作成する場合はMkDirステートメントを用いる。

MkDir "C:¥Test¥テスト"

1	MkDir "C:¥Test¥テスト"

合計値を求める

セルに入力されている関数の合計値を求める場合はSUMを用いる。

以下ではSにB1からB3までのセルの数値を合計して代入している。

S = WorksheetFunction.Sum(Range("B1:B3"))

1	S = WorksheetFunction.Sum(Range("B1:B3"))

特定のデータの数を数える

特定のデータの数を数える場合はCountIfを用いる。

ここではSにセルC1からセルC3までの範囲で「テスト」と入力されているセルの数を代入している。

S = WorksheetFunction.CountIf(Range("C1:C3")),"テスト")

1	S = WorksheetFunction.CountIf(Range("C1:C3")),"テスト")

特定のデータが存在し、同じ行にある数値を合計する

ここではセルC1からセルC5までの範囲で「テスト」と入力されているセルがある場合、

D列の数値を合計した数値をSに代入している。

S = WorksheetFunction.SumIf(Range("C1:D5"), "テスト", Range("D1:D5"))

1	S = WorksheetFunction.SumIf(Range("C1:D5"), "テスト", Range("D1:D5"))

降順で指定した大きさの数値を調べる

セルC1からセルC5の範囲で、5番目に大きい数値（一番小さい数値）をSに代入している。

S = WorksheetFunction.Large(Range("C1:C5"),5)

1	S = WorksheetFunction.Large(Range("C1:C5"),5)

昇順で数値が何番目にあるか調べる

セルC1からセルC5の範囲で、3番目に小さい数値をSに代入している。

S = WorksheetFunction.Small(Range("C1:C5"),3)

1	S = WorksheetFunction.Small(Range("C1:C5"),3)

表の左端列を検索し、該当する行の指定した列の値を返す

VLOOKUP関数はVBAでも使うことができる。

セルE1の値がA列に存在するかをセルA1からセルC5の範囲で検索し、存在する場合は3列目（C列）の値をセルD1に代入している。

Range("D1") = WorksheetFunction.VLookup(Range("E1"), Range("A1:C5"),3,False)

1	Range("D1") = WorksheetFunction.VLookup(Range("E1"), Range("A1:C5"),3,False)

表の右端列を検索し、該当する行の指定した列の値を返す

VLOOKUP関数は左端で検索し、その右側にある値しか返すことはできない。

右端を検索し、その左側にある値を返す場合はMATCH関数とINDEX関数を用いる。

MATCH関数は指定した範囲の中で上から何番目に指定した値が存在するかを返す。

また、INDEX関数は指定した範囲の中で、上から順に数えて指定した位置の値を返す。

以下ではセルE1の値が存在するかをセルC1からセルC5までの範囲で検索し、上から何番目にあるかをセルF1に代入している。

続いて、セルF1の値をセルB1からセルB5までの範囲で検索してその値を返している。

Range("F1") = WorksheetFunction.Match(Range("E1"), Range("C1:C5"),0)
Range("F2") = WorksheetFunction.Index(Range("B1:B5",Range("F1")))

1 2	Range("F1") = WorksheetFunction.Match(Range("E1"), Range("C1:C5"),0) Range("F2") = WorksheetFunction.Index(Range("B1:B5",Range("F1")))

指定した月の月末の日付を調べる

EOMONTH関数で指定した月の月末の日付を調べることができる。

セルC1の日付を開始日とし、2ヶ月前の最終日をセルA1に代入している。

2つ目の引数に入れる数値によって前後の月を調べることもできる。

今回は-2と入力しているが、0なら当月、2なら2ヶ月後の月末の日付を調べる。

Range("A1") =  WorksheetFunction.EoMonth(Range("C1"),-2)

1	Range("A1") = WorksheetFunction.EoMonth(Range("C1"),-2)

前の記事：ステートメント

次の記事：Excel VBA スタンダード試験対策5 セルの検索とオートフィルターの操作