日々是好日

プログラミングについてのあれこれ、ムダ知識など

【LAMBDA関数】有効桁数で処理する関数を作る【LET関数】

VBA Excel プログラミング GS Spereadsheet

どうも、けーしぃです。数年ぶりの更新です。

仕事柄、有効桁数を指定して処理する作業が多いのですが、エクセルでは桁数を自動で判別して処理する関数は用意されていません。

RoundUp, RoundDown, Round関数は桁数をユーザが指定する必要があるので、1,000円単位と10,000円単位の数字が混ざっていると、それぞれのセルで桁数を確認しないとなりません。

そこで、Microsoft 365、Excel 2021以降で導入されたLAMBDA関数とLET関数で、桁数を自動で判別し、有効桁数をユーザから指定すればいい感じに処理してくれる関数を自作しました。GoogleSpreadsheetでも使えます。

また、LAMBDA関数、LET関数が導入される以前はVBAでユーザ定義関数を作っていましたので、併せて掲載しておきます。

なお、ここで作成した関数は0以上の正の数でしか正しく適用できません。

負の数では切り捨て、切り上げ処理を逆にするなど、少し改良が必要です。

欲しい関数

先にどんな形で一撃処理したいのか考えます。

ユーザの指定が必要な項目は以下のとおりです。

  • 対象の数値を指定する ... v
  • 処理する桁数を指定する ... keta
  • 指定桁の次の桁の処理を指定する ... treat
    • 切り捨て -1, 四捨五入 0, 切り上げ 1の３つの値のみ取る

これらを引数としたYukoKetaという関数を作っていきます。

=YukoKeta(v, keta, treat)

たとえば、0.005555を有効桁数３桁、４桁目を四捨五入する場合は次のようになります。

=YukoKeta(0.005555, 3, 0) '有効数字３桁、４桁目を四捨五入(treat = 0)
'出力 0.00556

有効桁数の判別

まずは、対象の数字が何桁なのかをLOG10関数とCEILING.MATH関数で判別します。

LOG10関数で対象の数字が10の何乗に近いのか判別し、CEILING.MATH関数（天井関数）で最も近い整数に切り上げます。

CEILING.MATH(LOG10(ABS(v)))

対象の数値をそれぞれの関数で処理した結果は以下のとおりです。

ここで得られた天井関数の数字を用いてRound系関数の桁数を計算します。

'5555555を３桁で四捨五入処理したい場合
=ROUND(5555555, -4)
'出力5560000

この-4は、指定桁数－天井関数の出力で計算できます。

=ROUND(5555555, 3-7 = -4)

LAMBDA、LETで組み立てる

上記の関数でYukoKeta関数を組み立てていきます。

先に完成形を示します。改行を消してセルに張り付ければ使用できます。

=LAMBDA(v,keta,treat,
    LET(c,CEILING.MATH(LOG10(ABS(v))),k,keta-c,
        SWITCH(treat,-1,ROUNDDOWN(v,k),0,ROUND(v,k),1,ROUNDUP(v,k)))
    )
 (A1,3,0)

'(A1,3,0)より前の部分を YukoKeta という名前で定義しておく（「名前の定義」機能）
'すると、どのセルでもYukoKeta関数が使えるようになる
=YukoKeta(A1,3,0)

LAMBDA関数とLET関数の組み合わせはめちゃくちゃ強力で、最終出力を得るための途中計算をワンライナーに組み込めるようになります。

LAMBDA関数の部分

LAMBDA関数は、無名の関数を作ることができます。（何を言っている）

=LAMBDA(v,keta,treat,LET(...))(A1,3,0)
'A1の手前までが無名の関数
'無名とは、SUM関数やABS関数といったような名前がついていないという意味

プログラミングをやったことがないとなんとも理解しがたいものですが、ごめんなさい進めます。

まずLET関数の中身を無視します。

このv, keta, treatに対応するのが末尾のA1, 3, 0です。

このLAMBDA関数は、３つの引数（ひきすう, v, keta, treatの３つ）を取る関数として定義されています。

LET関数の部分

上記の３つの引数をLET関数に引き渡し、桁数の計算などをしていきます。

'c : 対象の数値の桁数の計算結果を持つ
'k : 指定された桁数－c の計算結果を持つ
LET(  c,  CEILING.MATH(LOG10(ABS(v))),  k,  keta-c,
    SWITCH(  treat,  -1,  ROUNDDOWN(v,k),  0,  ROUND(v,k),  1,  ROUNDUP(v,k)))
)

ここもなかなか異様な見た目をしていますが、c = CEILING.MATH(LOG10(ABS(v)))、k = keta-cとして考えてください。

c, kは計算結果を一時的に保持しておく箱のようなもので、LET関数の５つめの引数であるSWITCH関数に引き渡すための数値を保持しています。

SWITCH関数は、１つめの引数であるtreat（A1, 3, 0 ののうち、0が入る）の値に応じて処理を分岐させる関数ですが、-1, 0, 1の３つの処理を行っています。

今回はゼロが入力されているため、ROUND(v,k)の結果が出力される形となります。

もし-1, 0, 1以外であれば、エラー値として#N/Aが出力されます。

名前の定義で登録する

上記完成形をYukoKetaという名前で登録します。

スクショはGoogle Spreadsheetですが、エクセルでも「名前の定義」で同じように登録できます。

これで、=YukoKeta(A1, 3, 0)というような形で関数呼び出しが可能になります。

VBAで組み立てる

VBAで関数を定義する場合は下記のとおりです。標準モジュールを作成して貼り付ければ完了です（急にぶん投げ）。

なお、マクロ有効化ファイルとするのが必要なので、エクセルファイルの保存時に.xlsx⇒.xlsmに拡張子を変更して保存してください。

'value: 処理する値
'keta : 有効桁数の指定
'treat: 1=次の位切上、0=次の位四捨五入、-1=次の位切捨
'正の値の切上げは＋∞方向への丸め、切下げは－∞方向への丸め
'負の値の切上げは－∞方向への丸め、切下げは＋∞方向への丸め
Public Function YukoKeta(ByVal value As Double, ByVal keta As Integer, ByVal treat As Integer) As Double

  If value = 0 Then
    YukoKeta = 0
    Exit Function
  ElseIf keta < 1 Or Abs(treat) >= 2 Then
    YukoKeta = Error(5)
    Exit Function
  End If

  Dim ceil As Long

  '先頭の数字がどこにあるか
  ceil = -Application.WorksheetFunction.Ceiling_Math(Application.WorksheetFunction.Log10(Abs(value)))
  
  If treat = 1 Then
    '指定桁の次を切上げ
    YukoKeta = Application.WorksheetFunction.RoundUp(value, keta + ceil)
  ElseIf treat = 0 Then
    '指定桁の次を四捨五入
    YukoKeta = Application.WorksheetFunction.Round(value, keta + ceil)
  Else
    '指定桁の次を切下げ
    YukoKeta = Application.WorksheetFunction.RoundDown(value, keta + ceil)
  End If
  
End Function

まとめ

LAMBDA関数とLET関数はプログラミングの機能が関数として切り出されたような存在で、それゆえ使い方が難しいですがめちゃくちゃ強力な関数です。

それぞれ単体で解説されている記事は数多ありますが、組み合わせるとこんなことができるという記事があまりなかったので書いてみました。

プログラミングを勉強されたことがある方は、VBA無しでいろいろな関数を作ることができるかと思います。