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文字ずつに展開
- 各文字のバイト数(LENB)を取得
- SCANで累積バイト数を計算
- 指定バイト位置に対応する文字位置を取得
という内容です。
自分の環境では問題なく動作
上記の関数は、私のPC(Microsoft 365のExcel)では問題なく動作しました。
日本語を含む文字列でも、想定どおりバイト単位で処理できています。
他のPCでエラーが発生
しかし、ファイルを共有して別のPCで開いたところ、以下のような問題が発生しました。
- #N/A エラーになる
- 関数自体が正しく評価されない
調査した結果、関数のロジックではなく、Excelの環境差が原因でした。
原因:Excelのバージョン差
今回使用している関数には以下が含まれています。
- LAMBDA
- LET
- SCAN
- SEQUENCE
これらは比較的新しい関数のため、
- Microsoft 365(サブスクリプション版) → 使用可能
- 買い切り版(Office 2019 / 2021など) → 使用不可または制限あり
という違いがあります。
特に SCAN は未対応の環境が多く、これがエラーの直接的な原因になっていました。
対応策:VBAでの実装に変更
環境差の影響を受けない方法として、VBAで同等の処理を実装しました。
< 手順 >
- Alt + F11 でVBE(Visual Basic Editor)を開く
- 「挿入」→「標準モジュール」を選択
- 以下のコードを追加
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で多くのことが実現できるようになっていますが、
「どの環境で使われるか」を意識して選択することが重要だと感じました。
同様の問題に遭遇した際の参考になれば幸いです。