Excelの「名前の管理」で作成した関数が他PCでエラーになる問題

技術情報
技術情報

Excelで少し複雑な処理を行う際、「名前の管理」にLAMBDA関数を登録して、ユーザー定義関数のように使うケースはよくあります。
今回その方法を使ったところ、PC環境の違いによってエラーが発生したため、対応内容をまとめておきます。

実現したかったこと

案件で、文字列をバイト単位で扱う必要がありました。
具体的には以下のような扱いです。

  • 半角英数字:1バイト
  • 日本語などの全角文字:2バイト

このルールに従って、「指定したバイト数で文字列を切り出す」処理を行いたいという要件でした。
標準関数だけでの実現は難しいため、LAMBDAを使って関数を定義しました。

名前の管理に定義した関数

FW_BYTES

=LAMBDA(text,n,
  LET(
    chars,MID(text,SEQUENCE(LEN(text)),1),
    arr,LENB(chars),
    cum,SCAN(0,arr,LAMBDA(a,b,a+b)),
    INDEX(cum,MATCH(TRUE,cum>=n,0))
  )
)

FWMID

=LAMBDA(text,start_n,length_n,
  LET(
    chars,MID(text,SEQUENCE(LEN(text)),1),
    widths,LENB(chars),
    cum,SCAN(0,widths,LAMBDA(a,b,a+b)),
    start_pos,MATCH(TRUE,cum>=start_n,0),
    end_pos,MATCH(TRUE,cum>=start_n+length_n-1,0),
    TEXTJOIN("",TRUE,INDEX(chars,SEQUENCE(end_pos-start_pos+1,,start_pos)))
  )
)

処理の流れとしては、

  1. 文字列を1文字ずつに展開
  2. 各文字のバイト数(LENB)を取得
  3. SCANで累積バイト数を計算
  4. 指定バイト位置に対応する文字位置を取得

という内容です。

自分の環境では問題なく動作

上記の関数は、私のPC(Microsoft 365のExcel)では問題なく動作しました。
日本語を含む文字列でも、想定どおりバイト単位で処理できています。

他のPCでエラーが発生

しかし、ファイルを共有して別のPCで開いたところ、以下のような問題が発生しました。

  • #N/A エラーになる
  • 関数自体が正しく評価されない

調査した結果、関数のロジックではなく、Excelの環境差が原因でした。

原因:Excelのバージョン差

今回使用している関数には以下が含まれています。

  • LAMBDA
  • LET
  • SCAN
  • SEQUENCE

これらは比較的新しい関数のため、

  • Microsoft 365(サブスクリプション版) → 使用可能
  • 買い切り版(Office 2019 / 2021など) → 使用不可または制限あり

という違いがあります。
特に SCAN は未対応の環境が多く、これがエラーの直接的な原因になっていました。

対応策:VBAでの実装に変更

環境差の影響を受けない方法として、VBAで同等の処理を実装しました。

< 手順 >

  1. Alt + F11 でVBE(Visual Basic Editor)を開く
  2. 「挿入」→「標準モジュール」を選択
  3. 以下のコードを追加

FW_BYTES(VBA)

Function FW_BYTES(text As String, n As Long) As Long
    Dim i As Long, cum As Long
    For i = 1 To Len(text)
        cum = cum + LenB(StrConv(Mid(text, i, 1), vbFromUnicode))
        If cum >= n Then
            FW_BYTES = i
            Exit Function
        End If
    Next i
    FW_BYTES = 0
End Function

FWMID(VBA)

Function FWMID(text As String, start_n As Long, length_n As Long) As String
    Dim i As Long, cum As Long
    Dim start_pos As Long, end_pos As Long
    Dim textLen As Long
    
    textLen = Len(text)
    start_pos = 0
    end_pos = 0
    
    For i = 1 To textLen
        cum = cum + LenB(StrConv(Mid(text, i, 1), vbFromUnicode))
        If start_pos = 0 And cum >= start_n Then
            start_pos = i
        End If
        If cum >= start_n + length_n - 1 Then
            end_pos = i
            Exit For
        End If
    Next i
    
    If start_pos = 0 Then
        FWMID = ""
    ElseIf end_pos = 0 Then
        end_pos = textLen
        FWMID = Mid(text, start_pos, end_pos - start_pos + 1)
    Else
        FWMID = Mid(text, start_pos, end_pos - start_pos + 1)
    End If
End Function

結果

VBAで実装したことで、

  • Microsoft 365
  • 買い切り版(2019 / 2021 など)

いずれの環境でも問題なく動作するようになりました。

まとめ

今回のポイントは以下のとおりです。

  • LAMBDAは非常に便利だが、環境依存がある
  • 特に新しめの関数(SCANなど)は注意が必要
  • 複数環境で利用する場合はVBAの方が安定することが多い

最近はLAMBDAで多くのことが実現できるようになっていますが、
「どの環境で使われるか」を意識して選択することが重要だと感じました。

同様の問題に遭遇した際の参考になれば幸いです。

タイトルとURLをコピーしました