会社でデータ整形と完了％出力をVBAで作ったので紹介と解説します…☺️

こんにちわ〜ららぽてすらです🎵

仕事で都道府県とたくさんの完了方法のあるデータから一定の完了方法で終了しているデータ分が合計から完了件数の合計を都道府県毎に完了パーセンテージ出してとのことだったのでVBAで作ってみました。

データ整形用VBA ソースコード

Sub DisplayOneInColumnB()

Dim ws As Worksheet

Dim lastRow As Long

Dim i As Long

' アクティブなシートを設定

Set ws = ThisWorkbook.ActiveSheet

' A列の最後の行を取得

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' 2行目から最後の行までループ

For i = 2 To lastRow

' セルの値を取得

cellValue = ws.Cells(i, "A").Value

' 値が指定された条件に一致しない場合、B列に1を表示

If cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" Then

ws.Cells(i, "B").Value = 1

Else

ws.Cells(i, "B").Value = ""

End If

Next i

End Sub

Sub DisplayOneInColumnB(): DisplayOneInColumnBという名前のサブプロシージャを定義しています。
Dim ws As Worksheet: シートオブジェクトを参照するための変数wsを宣言しています。
Dim lastRow As Long: 最後の行を特定するための変数lastRowを宣言しています。
Dim i As Long: ループカウンタとして使用する変数iを宣言しています。
Set ws = ThisWorkbook.ActiveSheet: アクティブなシート（現在表示しているシート）を変数wsに代入しています。
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: A列の最後の行を取得し、変数lastRowに格納しています。
For i = 2 To lastRow: 2行目から最後の行までループ処理を行っています。
cellValue = ws.Cells(i, "A").Value: セルの値を取得し、変数cellValueに代入しています。
If cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" And cellValue <> "〇〇" Then: セルの値が指定された条件に一致しない場合に、次の処理を実行します。
ws.Cells(i, "B").Value = 1: B列に1を表示します。
Else: 値が指定された条件に一致する場合に、次の処理を実行します。
ws.Cells(i, "B").Value = "": B列に空白を表示します。
End If: If文の終わりを示しています。
Next i: For文の終わりを示しており、次の行に移動します。
End Sub: サブプロシージャの終わりを示しています。

このプログラムは、A列のデータを評価し、指定された条件に一致しない場合にB列に1を表示することで、特定の条件を満たさないデータをフィルタリングするのに役立ちます🎵

整形後のデータに対して都道府県・合計件数・完了％表示VBA

Sub CalculateCompletionData()

Dim ws As Worksheet

Dim lastRow As Long

Dim prefecture As Variant

Dim prefectures As Variant

Dim totalCount As Long

Dim completedCount As Long

Dim completionPercentage As Double

Dim outputRow As Long

Set ws = ThisWorkbook.Worksheets("Sheet4")

lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

prefectures = Array("北海道", "青森県", "岩手県", "宮城県", "秋田県", "山形県", "福島県", _

"茨城県", "栃木県", "群馬県", "埼玉県", "千葉県", "東京都", "神奈川県", _

"新潟県", "富山県", "石川県", "福井県", "山梨県", "長野県", _

"岐阜県", "静岡県", "愛知県", "三重県", _

"滋賀県", "京都府", "大阪府", "兵庫県", "奈良県", "和歌山県", _

"鳥取県", "島根県", "岡山県", "広島県", "山口県", _

"徳島県", "香川県", "愛媛県", "高知県", _

"福岡県", "佐賀県", "長崎県", "熊本県", "大分県", "宮崎県", "鹿児島県", "沖縄県")

outputRow = 2 ' 出力開始行を指定 (例: 2)

ws.Cells(outputRow - 1, 4).Value = "都道府県"

ws.Cells(outputRow - 1, 5).Value = "合計件数"

ws.Cells(outputRow - 1, 6).Value = "1の累計"

ws.Cells(outputRow - 1, 7).Value = "完了パーセント"

For Each prefecture In prefectures

totalCount = Application.WorksheetFunction.CountIf(ws.Range("A1:A" & lastRow), prefecture)

completedCount = Application.WorksheetFunction.CountIfs(ws.Range("A1:A" & lastRow), prefecture, ws.Range("B1:B" & lastRow), 1)

If totalCount > 0 Then

completionPercentage = completedCount / totalCount * 100

Else

completionPercentage = 0

End If

ws.Cells(outputRow, 4).Value = prefecture

ws.Cells(outputRow, 5).Value = totalCount

ws.Cells(outputRow, 6).Value = completedCount

ws.Cells(outputRow, 7).Value = completionPercentage

outputRow = outputRow + 1

Next prefecture

End Sub

Sub CalculateCompletionData(): CalculateCompletionDataという名前のサブプロシージャを定義しています。
Dim ws As Worksheet: シートオブジェクトを参照するための変数wsを宣言しています。
Dim lastRow As Long: 最後の行を特定するための変数lastRowを宣言しています。
Dim prefecture As Variant: 各都道府県を格納するための変数prefectureを宣言しています。
Dim prefectures As Variant: 都道府県リストを格納するための配列prefecturesを宣言しています。
Dim totalCount As Long: 各都道府県の合計件数を格納するための変数totalCountを宣言しています。
Dim completedCount As Long: 各都道府県の完了件数を格納するための変数completedCountを宣言しています。
Dim completionPercentage As Double: 各都道府県の完了率を格納するための変数completionPercentageを宣言しています。
Dim outputRow As Long: 出力開始行を指定するための変数outputRowを宣言しています。
Set ws = ThisWorkbook.Worksheets("Sheet4"): ワークブック内の"Sheet4"をwsに設定しています。
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row: A列の最後の行を取得し、変数lastRowに格納しています。
prefectures = Array(...): 都道府県名を含む配列を作成しています。
ws.Cells(outputRow - 1, 4).Value = "都道府県" ...: 出力テーブルのヘッダーを設定しています。
For Each prefecture In prefectures: 配列内の各都道府県に対してループを実行しています。
totalCount = Application.WorksheetFunction.CountIf(ws.Range("A1:A" & lastRow), prefecture): 各都道府県の合計件数を計算しています。
completedCount = Application.WorksheetFunction.CountIfs(ws.Range("A1:A" & lastRow), prefecture, ws.Range("B1:B" & lastRow), 1): 各都道府県の完了件数を計算しています。
If totalCount > 0 Then ... Else ... End If: 合計件数が0より大きい場合は、完了率を計算し、それ以外の場合は、完了率を0とします。
ws.Cells(outputRow, 4).Value = prefecture: 現在の都道府県名を出力します。
ws.Cells(outputRow, 5).Value = totalCount: 現在の都道府県の合計件数を出力します。
ws.Cells(outputRow, 6).Value = completedCount: 現在の都道府県の完了件数を出力します。
ws.Cells(outputRow, 7).Value = completionPercentage: 現在の都道府県の完了率を出力します。
outputRow = outputRow + 1: 出力行を次の行に移動します。
Next prefecture: 都道府県配列の次の要素に進むためのループの終わりを示します。
End Sub: サブプロシージャの終わりを示します。