EXCEL VBA 宅配便配送料・郵便物の料金計算・集計（テクニック）

2020年2月13日

EXCEL VBA 宅配便配送料・郵便物の料金計算・集計（テクニック）

●はじめに

社内の事務処理やYahooオークションなどで、宅配便を利用して商品や品物等を送る事が有りますが、１件や２件程度ですと宅配便の料金表を見ながら、金額を算出する事は出来ますが、さすがに100件以上の配送先を目で見ながら配送料金を算出するのは、しんどいと思います。そこで、EXCEL VBAを使って住所リスト（配送データ）・サイズ（大きさ）から配送料金をお求めるサンプルプログラムを作成いたしました。あくまでも、サンプルなので、汎用性は、ありませんのでご了承下さい。このような、簡易プログラムでも社内などで上手く利用する事で、業務の効率化を図る事が出来ると思います。それでは、宅配便の配送料算出と郵便物の料金計算を集計する簡易プログラムの作成方法を順番に説明いたします。

宅配便の配送料金算出(配送先・サイズから算出）

●プログラム説明 (サンプル①)

下記のサンプルプログラムは、配送料金を算出するプログラムです。配送データ（住所）から都道府県名とサイズを検索して、該当する配送料金を算出します。

『プログラムの流れ』
①このプログラムは、２つのシートで構成されており、シート「料金表」（上）と「配送データ」（下）があります。
②シート「配送データ」（下）の「C列」に住所と「D列」にサイズが記入されています。この住所の都道府県名とサイズを元にシート「料金表」より配送料金を算出します。
③配送データに記載されているデータ全てに対して配送料金を順番に算出します。

Sub Takuhai01()

    Dim ws01, ws02 As Worksheet
    Dim I, L, lRow As Long
    Dim Chiiki, Chiiki_Hit, Size, Size_hit As Range
    
    Dim MyAddress, Mysize, Ryokin As String
    
       
    Set ws01 = Worksheets("料金表")
    Set ws02 = Worksheets("配送データ")

    Set Chiiki = ws01.Range("C4:N11")  '地域の範囲を設定
    Set Size = ws01.Range("B13:B18")  'サイズの範囲を設定
    
    
    lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row  '配送データA列最終行を取得

    
    For I = 2 To lRow    '配送データの最終行まで繰り返す。
    
        MyAddress = ws02.Cells(I, "C")    '住所データを取得
        MyAddress = Mid(MyAddress, 1, 3)  '住所データの先頭から３文字を取得
        
        Mysize = ws02.Cells(I, "D")  'サイズデータを取得
    
    
        Set Chiiki_Hit = Chiiki.Find(what:=MyAddress, lookat:=xlPart)  '住所から地域（都道府県）を検索する（X軸：列）
        Set Size_hit = Size.Find(what:=Mysize, lookat:=xlWhole)  'サイズデータからサイズ位置を検索（Y軸：行）
                  
          
        If Chiiki_Hit Is Nothing Or Size_hit Is Nothing Then
                Ryokin = "該当なし"    '料金表に地域または、サイズが該当しなければ、”該当なし”を表示する。
            Else
                Ryokin = ws01.Cells(Size_hit.Row, Chiiki_Hit.Column)  '地域（X軸）サイズ（Y軸）から料金を取得
        End If
        
        ws02.Cells(I, "E") = Ryokin  '表から検索された料金を配送料金へ転記
        
    Next I
     
End Sub

Sub Takuhai01()

Dim ws01, ws02 As Worksheet

Dim I, L, lRow As Long

Dim Chiiki, Chiiki_Hit, Size, Size_hit As Range

Dim MyAddress, Mysize, Ryokin As String

Set ws01 = Worksheets("料金表")

Set ws02 = Worksheets("配送データ")

Set Chiiki = ws01.Range("C4:N11") '地域の範囲を設定

Set Size = ws01.Range("B13:B18") 'サイズの範囲を設定

lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row '配送データA列最終行を取得

For I = 2 To lRow '配送データの最終行まで繰り返す。

MyAddress = ws02.Cells(I, "C") '住所データを取得

MyAddress = Mid(MyAddress, 1, 3) '住所データの先頭から３文字を取得

Mysize = ws02.Cells(I, "D") 'サイズデータを取得

Set Chiiki_Hit = Chiiki.Find(what:=MyAddress, lookat:=xlPart) '住所から地域（都道府県）を検索する（X軸：列）

Set Size_hit = Size.Find(what:=Mysize, lookat:=xlWhole) 'サイズデータからサイズ位置を検索（Y軸：行）

If Chiiki_Hit Is Nothing Or Size_hit Is Nothing Then

Ryokin = "該当なし" '料金表に地域または、サイズが該当しなければ、”該当なし”を表示する。

Else

Ryokin = ws01.Cells(Size_hit.Row, Chiiki_Hit.Column) '地域（X軸）サイズ（Y軸）から料金を取得

End If

ws02.Cells(I, "E") = Ryokin '表から検索された料金を配送料金へ転記

Next I

End Sub

●実行前～実行後　※プログラム実行後、シート「配送データ」の住所とサイズを元に配送料金が算出されました。

郵便局の定形外（内）郵便の料金算出(重量・料金から算出）

●プログラム説明 (サンプル②)

下記のサンプルプログラムは、郵便局の定形外（内）郵便の料金を算出するプログラムです。配送データ規格（内・外）と重量を検索して、該当する料金を算出します。

『プログラムの流れ』
①このプログラムは、２つのシートで構成されており、シート「定形外郵便」（上）と「配送データ」（下）があります。
②シート「配送データ」（下）の「D列」に定型内・外と「E列」に重さ（ｇ）が記入されています。このデータを元にシート「定形外郵便」より料金を算出します。
③配送データに記載されているデータ全てに対して料金を順番に算出します。

Sub Teikei01()

    Dim ws01, ws02 As Worksheet
    Dim I, L, lRow, mRow As Long
       
    Set ws01 = Worksheets("定形外郵便")
    Set ws02 = Worksheets("配送データ")

    
    mRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row  '定型外郵便A列の最終行を取得
    lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row  '配送データA列の最終行を取得

    
    For I = 2 To lRow    '配送データの最終行まで繰り返す。
        For L = 4 To mRow '
            If ws02.Cells(I, "D") = ws01.Cells(L, "A") And ws02.Cells(I, "E") = ws01.Cells(L, "B") Then
                    '規格内外と重量に一致するデータを検索する
                    ws02.Cells(I, "F") = ws01.Cells(L, "C") '料金を代入
                    Exit For
            End If
        Next L
        
        If ws02.Cells(I, "F") = "" Then
                ws02.Cells(I, "F") = "該当なし"
        End If
    Next I
     
    MsgBox "定形外郵便計算が完了いたしました。"
     
End Sub