VLOOKUP関数っぽい処理をVBAで書いてみる（料金表の検索）

はじめに

今回は繰り返しと条件分岐の練習として新しい例題をやります。

ホテルの料金表（実在のホテルとは関係ない仮のホテルとします）をもとに、泊まる人数と平日か休日かを入力すると自動で金額を計算する処理をVBAで実行します。

最後に消費税も計算します。税率は10%です。

なお注意なのですが、消費税は金額を合計してから税率をかけるかどうかや、端数処理で小数点以下を切り捨てるか四捨五入するかなどで1円程度の誤差が発生する場合があります。

今回扱った計算方法は消費税計算に対する絶対的な手法ではありませんのでご注意ください。

当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

例題

次のような料金とします。

ここから人数と平日か休日かを入力することで金額を税込みで出力することにします。

エクセル関数で求める場合

VLOOKUP関数を使います。

エクセルに次のように入力します。

G5セルに次のように入力します。

=IF(H2=”平日”,VLOOKUP(G2,C4:E8,2,FALSE),IF(H2=”休日”,VLOOKUP(G2,C4:E8,3,FALSE),”平日か休日を選択してください”))

そしてH5セルに次のように入力します。

=G5*1.1

G5セルに13000、H5セルに14300と出力されたでしょうか？

VLOOKUP関数の解説

VLOOKUP関数は、指定したセルの値が検索したい範囲の最左列に存在した場合、その同じ行にある指定した列番号の値を取得する関数です。

VLOOKUP(検索値, 範囲, 列番号, 検索の型)

このように記述します。

検索値が検索したい値、範囲が検索したい範囲、列番号が検索したい範囲の何列目か、検索の型は今回はFLASEです。

検索の型はTRUEになると検索値に完全一致しなくても似たような値を出力するのですが、今回は厳密に一致させたいのでFALSEとします。

VLOOKUP(G2,C4:E8,2,FALSE)

この関数が意味するのは次のようなことです。

●検索する値はG2の値、すなわち2ということ。

●検索範囲はC4:E8ということ。

●出力するセルは検索値がある行の左から2列目ということ。

●検索するときは完全一致ということ。

結果今回は11000が出力されます。

VLOOKUP(G2,C4:E8,3,FALSE)

上で解説したように処理が行われますが、

●出力するセルは検索値がある行の左から3列目ということ。

ここが違います。

今回は13000が出力されます。

二つのIF関数で条件分岐

=IF(H2=”平日”,VLOOKUP(G2,C4:E8,2,FALSE),IF(H2=”休日”,VLOOKUP(G2,C4:E8,3,FALSE),”平日か休日を選択してください”))

最初のIF関数で平日かどうかを判定します。

IF関数の説明は「VBAで条件分岐」もご覧ください。

条件式の

H2=”平日”

はH2セルが「平日」という文字列かどうかを判定します。

「平日」を「””」で囲んでください。文字列はこの記号で囲む決まりです。

判定の結果条件に合致していれば次の

VLOOKUP(G2,C4:E8,2,FALSE)

が実行されます。

今回は合致しないので合致しなかったときの処理が行われます。

そこには次のように入力されています。

IF(H2=”休日”,VLOOKUP(G2,C4:E8,3,FALSE),”平日か休日を選択してください”)

これを解説すると、

H2が「休日」かどうか判定して、条件に合致していれば

VLOOKUP(G2,C4:E8,3,FALSE)

が実行、そうでないなら

“平日か休日を選択してください”

と出力する、という処理です。

最初のIF関数で平日かどうか判定しているので、条件に合致していないならH2セルは「休日」か「それ以外の何らかの数値や文字」となります。

なので次のIF関数で休日かどうか判定して、休日なら休日の料金を出力、そうでないよくわからない文字なら「平日か休日を選択してください」という出力で注意しています。

別にここは「””」とかでもかまいません。

そのときは何も出力されません。

「””」は空文字といって空白という意味になります。

消費税

H5セルに次のように入力します。

=G5*1.1

14300と出力されたでしょうか？

VBAで求める場合

コード入力欄に次のように記述します。

Sub price()
    Dim number As Integer
    Dim weekday As String
    Dim i As Integer
    Dim withTax As Double
    number = Range("G2").Value
    weekday = Range("H2").Value
    For i = 1 To 5
        If (Cells(i + 3, 3).Value = number) Then
            If (weekday = "平日") Then
                Range("H6").Value = Cells(i + 3, 4).Value * 1.1
            ElseIf (weekday = "休日") Then
                Range("H6").Value = Cells(i + 3, 5).Value * 1.1
            Else
                Range("H6").Value = "平日か休日を入力してください"
            End If
        End If
    Next i
End Sub

少し今までより長くなりましたが順に解説していきます。

変数で下準備

    Dim number As Integer
    Dim weekday As String
    Dim i As Integer
    Dim withTax As Double
    number = Range("G2").Value
    weekday = Range("H2").Value

numberには人数が格納されます。

weekdayには平日か休日かが格納されます。

これまで変数はIntegerだけ使ってきましたが今回は別の型も使います。

ますString。

これは文字列用の型です。

次にDouble。

これは浮動小数点数型と呼んで、小数の計算をするための型です。

Integerは整数の計算しかできませんが、Doubleは小数の計算ができます。

iは繰り返し処理用の変数。

withTaxが税込み金額用の変数です。

繰り返し処理と条件分岐

具体的に値の探索と出力を行うコードは次の部分です。

    For i = 1 To 5
        If (Cells(i + 3, 3).Value = number) Then
            If (weekday = "平日") Then
                Range("H6").Value = Cells(i + 3, 4).Value * 1.1
            ElseIf (weekday = "休日") Then
                Range("H6").Value = Cells(i + 3, 5).Value * 1.1
            Else
                Range("H6").Value = "平日か休日を入力してください"
            End If
        End If
    Next i

まずFor文から考えましょう。

    For i = 1 To 5
    
    Next i

つまり人数が1から5までの5回何らかの処理を実行するということです。

次にIf文を考えましょう。

        If (Cells(i + 3, 3).Value = number) Then

        End If

Cells(i + 3, 3).ValueはC列の人数の数値です。

「=」は条件式を入力する欄、例えば今回のようにIf文の条件式の部分では、「右と左の値が一致しているかどうか」という意味になります。

これが人数が格納されているnumberと一致しているかどうかを判定しています。

つまり最初のIf文では、「人数と同じ数値が入力されている行はどこか」ということを調べています。

二つ目のIf文を考えましょう。

            If (weekday = "平日") Then
                Range("H6").Value = Cells(i + 3, 4).Value * 1.1
            ElseIf (weekday = "休日") Then
                Range("H6").Value = Cells(i + 3, 5).Value * 1.1
            Else
                Range("H6").Value = "平日か休日を入力してください"
            End If

見慣れないElseIfが出てきました。

これは上のIfの条件ではじかれたときElseIfの判定を行って、条件に合致したらElseIfの中身が実行されるという命令文です。

厳密にはElseIfを複数書く場合などで決まりごとがあるのですが、とりあえずIfではじかれたらElseIfに移ると覚えるといいと思います。

つまりIfのところで平日か判定して、はじかれたときだけElseIfの判定が実行されて、休日かどうかが判定されます。

Elseは上のIfとElseIfすべての判定ではじかれたとき実行されます。

つまり平日でも休日でもないとき実行される処理を書きます。

Range(“H6”).Value = Cells(i + 3, 4).Value * 1.1

はCells(i + 3, 4).Valueが平日の料金だったのでその値に消費税10%を加えた値を計算しています。

(Cells(i + 3, 3).Value = number)で条件が合致している時だけ

Range(“H6”).Value = Cells(i + 3, 4).Value * 1.1

が実行されるということに注意してください。

条件に合致したときiはそのまま維持されているのでi + 3が共通していれば、同じ行の値にアクセスできるのです。

Range(“H6”).Value = Cells(i + 3, 5).Value * 1.1

だと休日の料金（5列、すなわちE列）に消費税を加えた額を計算します。

まとめ

今回はVLOOKUP関数っぽい処理をVBAで作ってみました。

For文とIf文を使うと色々な処理が書けます。

少しずつ経験を積んで慣れていくことで使えるようになってくると思います。

まずは細かいことばかり気にせず使ってみるのが大切だと思います。

これからも例題を追加していく予定です。