【VBAリファレンス】Excel VBAによる連続数値のグルーピングと記号連結の完全攻略ガイド

概要

Excelの実務において、膨大なデータリストから「連続する数値」を抽出し、それらを特定の記号（ハイフンやカンマなど）で連結して表示したいという要望は非常に一般的です。例えば、シフト表の勤務日、在庫管理のシリアルナンバー、あるいはプロジェクトの工程番号など、数値が飛び石になっているデータから「1-3, 5, 8-10」のように表現したいケースです。これを標準関数だけで実現しようとすると、配列数式や複雑なIF関数のネストが必要となり、メンテナンス性が著しく低下します。本稿では、VBAを用いてこの処理を高速かつ柔軟に実行するプロフェッショナルな手法を解説します。

詳細解説

連続する数値をグルーピングするアルゴリズムの核心は「ソート」と「差異の検知」にあります。データが昇順に並んでいることが前提となりますが、まずは入力された数値配列を数値として正しくソートする必要があります。

その上で、隣接する数値の差をチェックします。「次の数値 – 現在の数値 = 1」であれば、それは連続しているとみなします。この条件が崩れた瞬間に、それまで蓄積していた連続グループの文字列を確定させ、次のグループを開始するロジックを組みます。

ここで重要なのは、文字列操作の効率化です。数万行のデータに対してループ内で文字列結合（&演算子）を繰り返すとメモリ負荷が大きくなります。VBAにおいて効率的に文字列を構築するには、Collectionオブジェクトや配列を駆使し、最後にJoin関数で結合する方法が最も高速です。

サンプルコード

以下に、選択範囲内の数値を読み込み、連続する数値をハイフンでまとめ、カンマで連結して出力する汎用性の高いプロシージャを提示します。

Option Explicit

' 選択範囲内の数値を連続範囲として文字列に変換する関数
Function GetFormattedSequence(rng As Range) As String
    Dim arr As Variant, i As Long, j As Long
    Dim tempVal As Long, prevVal As Long
    Dim startVal As Long, endVal As Long
    Dim col As Collection
    Set col = New Collection
    
    ' 値を配列に格納
    arr = rng.Value
    
    ' 簡易的な挿入ソート（データが小さい場合）または配列ソートを実行
    ' ここでは既にソート済みであることを前提とするか、別途ソートルーチンを呼ぶ
    ' 今回はシンプルに連続判定ロジックに集中する
    
    If UBound(arr, 1) = 0 Then Exit Function
    
    startVal = arr(1, 1)
    prevVal = arr(1, 1)
    
    For i = 2 To UBound(arr, 1)
        tempVal = arr(i, 1)
        If tempVal = prevVal + 1 Then
            ' 連続している場合は終了値を更新
            endVal = tempVal
        Else
            ' 連続が途切れたら文字列を生成
            If startVal = prevVal Then
                col.Add CStr(startVal)
            Else
                col.Add startVal & "-" & prevVal
            End If
            startVal = tempVal
            endVal = tempVal
        End If
        prevVal = tempVal
    Next i
    
    ' 最後のグループを追加
    If startVal = prevVal Then
        col.Add CStr(startVal)
    Else
        col.Add startVal & "-" & prevVal
    End If
    
    ' コレクションの内容をカンマで連結
    Dim res As String, item As Variant
    For Each item In col
        res = res & item & ", "
    Next item
    
    If Len(res) > 2 Then GetFormattedSequence = Left(res, Len(res) - 2)
End Function

Sub OutputSequence()
    Dim targetRng As Range
    Set targetRng = Selection
    MsgBox GetFormattedSequence(targetRng)
End Sub

実務アドバイス

実務における最大の落とし穴は「データの重複」と「並び順」です。上記のコードは昇順に並んでいることを前提としていますが、実際の業務データには重複が含まれることが多々あります。その場合、処理の直前に「Dictionaryオブジェクト」を使用して重複を排除し、かつキーをソートする前処理を加えるのが定石です。

また、エラーハンドリングについても留意が必要です。数値以外の文字列が含まれている場合、コードは型不一致エラーを起こします。IsNumeric関数によるフィルタリングをループの先頭で必ず行い、不正なデータが混入しても処理が停止しない堅牢な設計を心がけてください。

さらに大規模なデータセットを扱う場合は、セルへの直接アクセス（Cells(i, j).Value）を避けてください。一度配列に格納してメモリ上で処理し、結果を一度にセルに書き出す手法をとることで、処理速度は劇的に向上します。特に数万行の計算を伴う場合、この手法をとるか否かで処理時間が数分単位で変わることもあります。

まとめ

連続数値のグルーピングは、単純なようでいて、データの正規化とアルゴリズムの工夫が試される非常に奥深い課題です。関数だけで解決しようとして「計算式が長すぎてデバッグ不能」という状況に陥る前に、ぜひ今回紹介したVBAでのアプローチを導入してください。

VBAを活用する最大のメリットは、一度作成したコードをモジュールとして保存し、次回以降は引数を渡すだけで瞬時に再利用できる点にあります。今回解説したロジックをテンプレート化し、貴方のツールボックスに加えておくことで、日々のルーチンワークの質とスピードは確実に一段上のレベルへと引き上げられるはずです。自動化は単なる作業の削減ではなく、正確性を担保し、人間がより創造的な業務に集中するための投資であることを忘れないでください。