Excelデータの入力不備で計算エラーが発生して家に帰れない!――入力規則よりも簡単なチェックマクロとは働く価値を上げる“VBA/GAS術”(3)(1/2 ページ)

1つのExcelファイルを複数人で編集する場合、想定していたのとは違う形式でデータを入力されてしまい、不備が発生する可能性も少なくない。その問題をExcel VBAで解決するにはどうすればいいのだろうか。

» 2017年09月06日 05時00分 公開
[高橋宣成プランノーツ]

Excel VBAとGAS

 業務で発生するちょっとした面倒くさい業務。特定の操作を繰り返すだけなので、今のままでもいいけど、楽になったらいいなと思うこともあるのではないでしょうか。例えば業務を自動化すると時間が空くため、他の業務に時間を充てたり、残業時間を減らしたりできるかもしれません。

 本連載「働く価値を上げる“VBA/GAS術”」では、業務を効率化する手段として「Microsoft Excel」(マイクロソフトエクセル)で使える「VBA」(Visual Basic for Applications)と「G Suite」の「GAS」(Google Apps Script)の使い方を説明し、よくある業務課題の解決策を紹介します。

Excel VBAで簡単なチェックツールを作る

 Excelで顧客リスト、商品リスト、従業員リスト、備品購入リストなどさまざまなデータリストを取り扱うことも少なくありません。しかし、いざリストを使って作業するとき、次のように困ったことに遭遇することはないでしょうか?

  • 数値を入れるべき欄に「円」や「個」などの単位が入力されているため、合計金額の計算式でエラーになる
  • 日付欄に「2017/8/3〜2017/8/5」や「いつでも」など、日付形式で入っていないためフィルターが使えない

 もし多くの人と1つのExcelファイルを編集する場合、想定していたのとは違う形式でデータを入力される可能性があります。「データの入力規則」機能で、入力の制限をかけることは可能ですが、事前に設定をする必要があります。今回は、タイミング問わずデータを検査できるようにするため、Excel VBAで「入力内容のチェックをするマクロ」を作成していきます。

 Excel VBAの基本については、連載第1回の『さらば残業! 面倒くさいエクセル業務を楽にする「Excel VBA」とは』を参照してください。

備品購入リストの例

 今回、図1のような備品購入リストを用意しました。

図1 Excelシート上の備品購入リスト

 C3セルの「3個」、D6セルの「250円」というデータは、本来は数値のみで入力すべきデータのはずが単位まで入っています。そのため実際にE列に、C列の値とD列の値を乗算して金額を求める数式を入力すると、図2のようにエラーになってしまいます。

図2 Excelで文字列を乗算するとエラーになる

データが数値かどうかをチェックするプロシージャ

 そこで、備品購入リストのC2からD6の範囲で数値ではないデータがあれば、そのセルの背景色を黄色に設定するプロシージャを作りました。

Sub 数値チェック()
Dim i As Long
For i = 2 To 6
    If Not IsNumeric(Sheet1.Cells(i, 3).Value) Then Sheet1.Cells(i, 3).Interior.Color = vbYellow
    If Not IsNumeric(Sheet1.Cells(i, 4).Value) Then Sheet1.Cells(i, 4).Interior.Color = vbYellow
Next i
End Sub

 このプロシージャを実行すると、図3のように数値でないデータのあるセル背景色が黄色に変更されます。

図3 Excelシートのデータを数値チェックする

数値チェックをするプロシージャ

 ではプロシージャ「数値チェック()」の内容について解説します。

Dimステートメントで変数を宣言する

 まず、以下の部分を見ていきましょう。

Dim i As Long

 ここでは「Dim」ステートメント(命令文)というコードを使って「変数の宣言」を行っています。変数は、プログラムの実行状況によって、文字列や数値などさまざまなデータを記憶するものです。変数の宣言を行うことで、宣言以降の処理で変数を使用できる状態になります。Dimステートメントの書式は下記の通りです。

Dim 変数名 As 型

 変数の宣言時に、変数名とともに、「型」を指定する必要があります。型は変数に入れるデータの種類を指定するもので、例えば整数であれば「Long」、文字列であれば「String」、日付であれば「Date」となります。そのため今回のDimステートメントは、「iという名前の変数を整数で用意してね」という命令となるわけです。

繰り返し処理を行う「For〜Next」文

 人の手では面倒な繰り返しの作業でも、プログラムさえすればコンピュータがそれを高速かつ正確に実行します。VBAでは、その繰り返し処理を実現する構文として「For〜Next」文が用意されています。書式は以下の通りです。

For 変数 = 初期値 To 最終値
    '処理
Next 変数

 この構文で使う変数は、「繰り返しの回数をカウントする整数」です。この変数を「イテレータ」と呼びます。イテレータの値は、「初期値」にセットされ、For〜Next間の処理が繰り返されるたびに、1ずつ自動で加算されていきます。イテレータの値が「最終値」に達成すると繰り返し処理を終了します。今回の例では、以下の部分が該当します。

For i = 2 To 6
    '処理
Next i

 イテレータとしてDimステートメントで宣言した変数iを用い、その初期値は2。値が6に達するまで計5回、For〜Next文内の処理を行います。今回は、「とある行のB、C列に入っている値が数値かどうかを判定し、数値でなければセルの色を黄色に変更する」という下記コードの処理を行っていきます。

If Not IsNumeric(Sheet1.Cells(i, 3).Value) Then Sheet1.Cells(i, 3).Interior.Color = vbYellow
If Not IsNumeric(Sheet1.Cells(i, 4).Value) Then Sheet1.Cells(i, 4).Interior.Color = vbYellow
For〜Next文内の処理

 分解すると、下記リストの命令を行っていることになります。

  • 【1】「Sheet1」でワークシートを指定
  • 【2】「Cells」でセルの値を参照
  • 【3】「If Not IsNumeric(……) Then」で2〜6行目のC列、D列についてその値が数値かどうかを判断
  • 【4】「Interior.Color = vbYellow」でセルの色を黄色に変更する

 次は、1つ1つの命令内容を理解するために、使うコードをイミディエイトウィンドウで記しながら、確認してきます。

       1|2 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

AI for エンジニアリング
「サプライチェーン攻撃」対策
1P情シスのための脆弱性管理/対策の現実解
OSSのサプライチェーン管理、取るべきアクションとは
Microsoft & Windows最前線2024
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。