Excel VBAでサプライヤー評価報告書を自動生成する方法

この記事では、Excel VBAを使用してサプライヤー評価報告書を生成する方法について詳しく説明します。サプライヤーの評価は、ビジネスでの重要な業務の一つです。適切な評価を行い、報告書を効率的に作成することで、ビジネスパートナーとの関係をより強固にすることができます。具体的なコード例とその解説、応用例を含めています。

基本コードの構築

Sub CreateSupplierEvaluationReport()
    Dim LastRow As Long
    Dim ws As Worksheet

    ' 新しいワークシートを作成
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "サプライヤー評価報告書"

    ' ヘッダーを設定
    ws.Cells(1, 1).Value = "サプライヤー名"
    ws.Cells(1, 2).Value = "評価点"
    ws.Cells(1, 3).Value = "コメント"

    ' データ入力(仮)
    ws.Cells(2, 1).Value = "サプライヤーA"
    ws.Cells(2, 2).Value = "85"
    ws.Cells(2, 3).Value = "良好"

    ' 最終行を取得
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' フォーマット設定
    ws.Range("A1:C" & LastRow).Borders.LineStyle = xlContinuous
    ws.Range("A1:C1").Font.Bold = True
    ws.Range("A1:C" & LastRow).Columns.AutoFit
End Sub

基本コードの解説

1. まず、新しいワークシートを作成し、”サプライヤー評価報告書”という名前を付けます。
2. ヘッダーとして、サプライヤー名、評価点、コメントを設定します。
3. 仮のデータとして、サプライヤーAの情報を入力しています。
4. データの最終行を取得し、セルのフォーマットを設定します。これにより、表の外観がきれいに整います。

応用例

応用例1: 評価点に応じたコメントの自動入力

Sub AutoCommentByScore()
    Dim ws As Worksheet
    Dim i As Long
    Dim LastRow As Long

    Set ws = ThisWorkbook.Sheets("サプライヤー評価報告書")
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To LastRow
        If ws.Cells(i, 2).Value >= 90 Then
            ws.Cells(i, 3).Value = "優秀"
        ElseIf ws.Cells(i, 2).Value >= 70 Then
            ws.Cells(i, 3).Value = "良好"
        Else
            ws.Cells(i, 3).Value = "改善が必要"
        End If
    Next i
End Sub

このコードは、サプライヤーの評価点に応じて、自動でコメントを入力します。90点以上は”優秀”、70点以上は”良好”、それ以下は”改善が必要”と評価します。

応用例2: データのソート

Sub SortByScore()
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ThisWorkbook.Sheets("サプライヤー評価報告書")
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ws.Range("A1:C" & LastRow).Sort Key1:=ws.Range("B2"), Order1:=xlDescending, Header:=xlYes
End Sub

このコードは、評価点の降順にデータをソートします。これにより、最も高い評価を受けたサプライヤーを一覧の上部に表示することができます。

応用例3: 評価点の平均値と標準偏差の計算

Sub CalculateAverageAndStdDev()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim Avg As Double, StdDev As Double

    Set ws = ThisWorkbook.Sheets("サプライヤー評価報告書")
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Avg = Application.WorksheetFunction.Average(ws.Range("B2:B" & LastRow))
    StdDev = Application.WorksheetFunction.StDev(ws.Range("B2:B" & LastRow))

    ws.Cells(LastRow + 2, 1).Value = "平均"
    ws.Cells(LastRow + 2, 2).Value = Avg
    ws.Cells(LastRow + 3, 1).Value = "標準偏差"
    ws.Cells(LastRow + 3, 2).Value = StdDev
End Sub

このコードは、サプライヤーの評価点の平均値と標準偏差を計算し、報告書の最後に追加します。これにより、サプライヤーの評価の傾向やバラつきを一目で確認することができます。

まとめ

Excel VBAを使用して、サプライヤー評価報告書を効率的に作成する方法を学びました。基本的なコードから応用例まで、様々な機能を追加して報告書の質を向上させることができます。この知識を活用して、ビジネスの効率化を図りましょう。