1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@uemak42(Uncle K)

Excelの新関数で1年遊んでいたら円周率2000桁計算できた話

Posted at

初めに

Qiitaの読者の皆さんはあまりExcelに興味がないかと思います(偏見です)。
Qiitaで「Excel」と検索すると、

  • 脱Excel（活Excel）
  • Excel VBA
  • Excel ショートカット（便利技）

などがヒットしますが、あまり読まれていないみたい。
そんな非Excelユーザーの方々に、「最近のExcelはVBAを使わなくてもこのくらいのことできますよ」と言いたくて記事を書きました。
とはいえ、最初に断りを入れますが、

  • 業務には役にたちません
  • 会社でこんなことしてたら怒られます
  • Pythonつかえるならそっちの方が早い
  • だけど、このぐらいできるようになると業務で時短できるかも？

といった内容になっています。
暇つぶしにご覧ください。

コード

とりあえず、次のコードをご覧ください。

=LET(
    _00, "配列数は10,000,000進数",
    _dgt, 7,
    
    _01, "文字列数の絶対値",
    BAbs, LAMBDA(x, REGEXREPLACE(x, "[^0-9]", "")),
    
    _02, "文字列数を配列数に変換",
    B2A, LAMBDA(x,
        LET(
            _x, BAbs(x),
            _l, LEN(_x),
            _m, CEILING.MATH(_l, _dgt),
            _y, REPT("0", _m - _l) & _x,
            _c, _m / _dgt,
            MID(_y, SORT(SEQUENCE(_c, , , _dgt), 1, -1), _dgt) * 1
        )
    ),
    _03, "配列数を文字列数に変換",
    A2B, LAMBDA(x,
        LET(
            _r, SORTBY(x, SEQUENCE(COUNTA(x)), -1),
            _y, CONCAT(TEXT(_r, REPT("0", _dgt))),
            REGEXREPLACE(_y, "^0+(?!$)", "")
        )
    ),
    
    _10, "配列数繰上処理",
    AFx, LAMBDA(x,
        LET(
            _unit, 10 ^ _dgt,
            _a, SCAN(0, x, LAMBDA(p, q, INT((p + q) / _unit))),
            _b, A2B(VSTACK(x, 0) - VSTACK(_unit * _a, 0) + VSTACK(0, _a)),
            B2A(REGEXREPLACE(_b, "^0+(?!$)", ""))
        )
    ),
    
    _11, "配列数加算",
    AAd, LAMBDA(x, y,
        LET(
            _l, MAX(COUNTA(x), COUNTA(y)),
            AFx(EXPAND(x, _l, , 0) + EXPAND(y, _l, , 0))
        )
    ),
    
    _12, "配列数乗算",
    AMl, LAMBDA(x, y,
        LET(
            _l, COUNTA(x) + COUNTA(y),
            _a, x * TRANSPOSE(y),
            _b, EXPAND(_a, , _l, 0),
            _c, TOCOL(_b),
            _d, WRAPCOLS(_c, _l - 1, 0),
            _z, BYROW(_d, SUM),
            AFx(_z)
        )
    ),
    
    _13, "配列数比較。x>y:1, x=y:0, x<y:-1",
    ACp, LAMBDA(x, y,
        LET(
            _l, MAX(COUNTA(x), COUNTA(y)),
            _z, EXPAND(x, _l, , 0) - EXPAND(y, _l, , 0),
            _p, IFERROR(XMATCH(TRUE, _z > 0, 0, -1), -1),
            _n, IFERROR(XMATCH(TRUE, _z < 0, 0, -1), -1),
            SIGN(_p - _n)
        )
    ),
    
    _14, "配列数除算",
    ADv, LAMBDA(x, y,
        LET(
            F, LAMBDA(F, y, a, l,
                LET(
                    _b, VSTACK(SEQUENCE(l, , 0, 0), AAd(a, a)),
                    _z, DROP(AAd(_b, -1 * AMl(y, AMl(a, a))), l),
                    IF(ACp(a, _z) = 0, _z, F(F, y, _z, l))
                )
            ),
            _l, COUNTA(x),
            _a, LET(
                _l, _l - COUNTA(y),
                _i, INT(10 ^ _dgt / SUM(TAKE(y, -1))),
                _s, _i & REPT(REPT("0", _dgt), _l),
                B2A(_s)
            ),
            _z, DROP(AMl(x, F(F, y, _a, _l)), _l),
            IF(ACp(y, AAd(x, -1 * AMl(y, _z))) = 1, _z, AAd(_z, 1))
        )
    ),
    
    _21, "文字列数正負判定",
    BSn, LAMBDA(x, IF(LEFT(x, 1) = "n", -1, 1)),
    
    _22, "文字列数比較。x>y : 1, x=y : 0, x<y : -1",
    BCp, LAMBDA(x, y,
        LET(
            _x, BAbs(x),
            _y, BAbs(y),
            _d, LEN(_x) - LEN(_y),
            IF(_d = 0, IF(_x = _y, 0, IF(_x > _y, 1, -1)), SIGN(_d))
        )
    ),
    
    _23, "文字列数加算",
    BAd, LAMBDA(x, y,
        LET(
            _cc, BCp(x, y),
            _cx, IF(_cc >= 0, x, y),
            _cy, IF(_cc >= 0, y, x),
            _sx, BSn(_cx),
            _sy, BSn(_cy),
            _ax, B2A(_cx),
            _ay, B2A(_cy) * (_sx * _sy),
            _az, AAd(_ax, _ay),
            IF(_sx > 0, "p", "n") & A2B(_az)
        )
    ),
    
    _24, "文字列数減算",
    BSb, LAMBDA(x, y,
        LET(
            _cc, BCp(x, y),
            _cx, IF(_cc >= 0, x, y),
            _cy, IF(_cc >= 0, y, x),
            _sx, BSn(_cx),
            _sy, BSn(_cy),
            _ax, B2A(_cx),
            _ay, B2A(_cy),
            _az, AAd(_ax, -1 * _ay),
            IF(_sx * _cc >= 0, "p", "n") & A2B(_az)
        )
    ),
    
    _25, "文字列数乗算",
    BMl, LAMBDA(x, y,
        LET(
            _sx, BSn(x),
            _sy, BSn(y),
            _ax, B2A(x),
            _ay, B2A(y),
            _az, AMl(_ax, _ay),
            IF(_sx * _sy >= 0, "p", "n") & A2B(_az)
        )
    ),
    
    _26, "文字列数除算",
    BDv, LAMBDA(x, y,
        LET(
            _sx, BSn(x),
            _sy, BSn(y),
            _cc, BCp(x, y),
            _az, IF(_cc <= 0, --(_cc = 0), ADv(B2A(x), B2A(y))),
            IF(_sx > 0, "p", "n") & A2B(_az)
        )
    ),
    
    _30, "整数平方根。length:小数点以下の桁数",
    IntSqrt, LAMBDA(x, [length],
        LET(
            _len, IF(ISOMITTED(length), 0, length),
            _cells, ROUNDUP(_len / _dgt, 0) + 1,
            _i, INT(SQRT(x)),
            _a, B2A(_i),
            _b, 1,
            LM, LAMBDA(LM, x, a, b, cells,
                LET(
                    _aa, AMl(a, a),
                    _bb, AMl(b, b),
                    _na, AAd(_aa, AFx(x * _bb)),
                    _nb, AMl(a, AFx(2 * b)),
                    IF(
                        COUNTA(_nb) < cells,
                        LM(LM, x, _na, _nb, cells),
                        ADv(VSTACK(SEQUENCE(cells, , 0, 0), _na), _nb)
                    )
                )
            ),
            _v, LM(LM, x, _a, _b, _cells),
            _z, A2B(DROP(_v, 1)),
            _l, LEN(_i),
            LEFT(_z, _l) & "." & MID(_z, _l + 1, _len)
        )
    ),
    
    _40, "円周率",
    PiChudnovsky, LAMBDA([length],
        LET(
            _0, "項数指定",
            _len, IF(ISOMITTED(length), 15, length),
            _a, 0,
            _b, INT(_len / 14) + 1,
            _00, "マジックナンバー",
            _mn1, B2A("10939058860032000"),
            _mn2, B2A("13591409"),
            _mn3, B2A("545140134"),
            _1, "binary splitting",
            _BS, LAMBDA(F, a, b,
                IF(
                    BSb(b, a) = "p1",
                    LET(
                        _1, "最小単位なら",
                        _k, B2A(a),
                        _p, IF(
                            ACp(_k, 0) = 0,
                            B2A(1),
                            LET(
                                _k, B2A(_k),
                                _2k, AFx(2 * _k),
                                _6k, AFx(3 * _2k),
                                _6k5, AAd(_6k, -5),
                                _2k1, AAd(_2k, -1),
                                _6k1, AAd(_6k, -1),
                                AMl(AMl(_6k5, _2k1), _6k1)
                            )
                        ),
                        _q, IF(
                            ACp(_k, 0) = 0,
                            B2A(1),
                            AMl(AMl(_k, _k), AMl(_k, _mn1))
                        ),
                        _t, AMl(_p, AAd(_mn2, AMl(_mn3, _k))),
                        _sign, IF(ISODD(RIGHT(A2B(_k), 1) * 1), "n", "p"),
                        VSTACK(A2B(_p), A2B(_q), _sign & A2B(_t))
                    ),
                    LET(
                        _1, "2分木で再帰",
                        _m, BDv(BAd(a, b), 2),
                        _l1, F(F, a, _m),
                        _l2, F(F, _m, b),
                        _p1, INDEX(_l1, 1, 1),
                        _q1, INDEX(_l1, 2, 1),
                        _t1, INDEX(_l1, 3, 1),
                        _p2, INDEX(_l2, 1, 1),
                        _q2, INDEX(_l2, 2, 1),
                        _t2, INDEX(_l2, 3, 1),
                        _2, "出力",
                        VSTACK(
                            BMl(_p1, _p2),
                            BMl(_q1, _q2),
                            BAd(BMl(_t1, _q2), BMl(_t2, _p1))
                        )
                    )
                )
            ),
            _2, "QTを出力",
            _pqt, _BS(_BS, _a, _b),
            _q, INDEX(_pqt, 2, 1),
            _t, INDEX(_pqt, 3, 1),
            _3, "π = (√10005*426880*Q)/T",
            _s, BMl(IntSqrt(10005, _len), 426880),
            _z, BDv(BMl(_q, _s), _t),
            MID(_z, 2, 1) & "." & MID(_z, 3, _len)
        )
    ),
    
    PiChudnovsky(2000)
)

これは最新のExcel関数を駆使して書いた「円周率を小数点以下2000位まで表示するコード」です。
「Excel Labs」というアドインを使って書いているため、インデントも入っていますが、実際にはスペースも無く繋がっているワンライナーなコードです。
インデント用のスペースを無視して文字数カウントしても4000文字オーバーという本末転倒なコードとなっています。

解説

「高々、円周率2000桁表示するのになんでこんなことになっているんだ？」と、「Pythonなら30行ぐらいで書けるよ！」と皆さんお思いかとおもいます。
その通りです。わざわざExcelでやることありません。これは「Excelの標準関数縛りのワンライナー」というお遊びです。
いわゆるパズル問題です。
頭の体操だと思って見てください。

使用関数一覧

使用している関数を整理してみました。

関数名 機能 使用数 バージョン
BYROW 行ごとに処理 1 Microsoft365
DROP 配列の一部削除 3 Microsoft365
EXPAND 配列サイズ拡張 5 Microsoft365
ISOMITTED LAMBDA専用 2 Microsoft365
LAMBDA ユーザー定義関数 20 Microsoft365
LET 名前付き式を定義 21 Microsoft365
REGEXREPLACE 正規表現による置換 3 Microsoft365
SCAN 配列の累積処理 1 Microsoft365
SEQUENCE 動的な数列生成 4 Microsoft365
SORT 動的配列関数 2 Microsoft365
SORTBY 条件付き並べ替え 1 Microsoft365
TAKE 配列の一部抽出 1 Microsoft365
TOCOL 配列を1列に変換 1 Microsoft365
VSTACK 垂直方向に結合 7 Microsoft365
WRAPCOLS 列方向に折り返し 1 Microsoft365
XMATCH MATCHの強化版 2 Microsoft365
CONCAT CONCATENATEの後継 1 Excel2016
CEILING.MATH CEILINGの改良版 1 Excel2013
IFERROR エラー処理 2 Excel2007
ISODD 奇数判定 1 Excel2007
SIGN 符号を返す 2 Excel2007
SQRT 平方根 1 Excel2007
COUNTA 非空セル数 10 Excel2003
IF 条件分岐 24 Excel2003
INDEX 配列から値を返す 8 Excel2003
INT 整数部分 4 Excel2003
LEFT 左端から抽出 2 Excel2003
LEN 文字数を返す 4 Excel2003
MAX 最大値 2 Excel2003
MID 中央から抽出 4 Excel2003
REPT 文字列の繰り返し 4 Excel2003
RIGHT 右端から抽出 1 Excel2003
ROUNDUP 切り上げ 1 Excel2003
SUM 合計 2 Excel2003
TRANSPOSE 行列の転置 1 Excel2003

最多使用はIF関数。極力使用を避けていたつもりなのですが、分岐処理でどうしても使ってしまいます。しょうがないですね。
２番目がLET関数で３番目がLAMBDA関数。新関数群の中核なのでこれは順当でしょうか。
意外だったのはTRANSPOSE関数。この表のバージョン情報はCopilotに調べてもらったのですが、こいつ古参関数だったんですね。スピル系と相性ばっちりで重宝しております。

さて、全てを説明していると長くなりすぎるので、重要な２関数だけ説明したいと思います。

LAMBDA関数

やはり最初にこの関数を説明する必要があるでしょう。
オリジナル関数を作るための関数です。
他の言語のことは詳しく知りませんが、Excelでは次の様になっています。

構文
=LAMBDA([parameter1, parameter2, …,] calculation)

使用例1 華氏温度を摂氏に変換する
=LAMBDA(temp, (5/9) * (Temp-32))

使用例2 直角三角形の斜辺の長さを返す
=LAMBDA(a, b, SQRT((a^2+b^2)))

実際に使用する際は、引数を渡します。

=LAMBDA(temp, (5/9) * (temp-32))(140)

引数で渡された「140」が「temp」に入り、「(5/9)*(140-32)=>60」が返り値となります。

=LAMBDA(a, b, SQRT((a^2+b^2)))(3,4)

第１引数「3」が「a」に、第２引数「4」が「b」にはいります。
「SQRT(3^2+4^2)=>5」が返り値です。括弧が多い理由はわかりません。

この例だけだと、わざわざLAMBDA関数を利用する必要性を感じませんが、「名前定義」と合わせることでいろいろ複雑な計算ができるようになります。

LET関数

次に「LET関数」です。
簡単に説明すると「スコープ内名前定義ができる関数」です。
いろいろなプログラムで見られる関数ですので、なんとなく知っているかと思いますが、Excelでは次の様になっています。

構文
= LET (name1、name_value1、calculation_or_name2、[name_value2、calculation_or_name3...])

使用例1
= LET (x, 5, SUM(x, 1))

この使用例は、Microsoftサポートからの引用なのですが、ひどいですね…。
何の意味があるのかさっぱりです。
一応説明すると、まず「5」に「x」という名前をつけます。
つぎに、「SUM(x,1)」を計算します。つまり「SUM(5,1)=>6」ですね。これが返り値です。
なんのためにこんな面倒くさいことしているんだ？と思ってしまいます。

もう一つの使用例も載せます。

使用例2
=LET(filterCriteria,"Fred", filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-", filteredRange))

今度は、複雑すぎてさっぱりです。改行してみてみましょう。

=LET(
    filterCriteria,"Fred", 
    filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), 
    IF(ISBLANK(filteredRange),"-", filteredRange)
)

まず、「"Fred"」という文字列に「filterCriteria」という名前を付けています。
次に、「A2:D8」のテーブルの左端「A2:A8」で「filterCriteria」と一致するデータ、つまり「"Fred"」と一致するデータを抜き出し、「filteredRange」という名前をつけます。
最後にcalculationとしてIF関数がきて、「filteredRange」が空のときは「"-"」を返し、そうでなければそのまま返します。
なんとも意味のないコードです。

=FILTER(A2:D8,A2:A8="Fred","-")

と書けば１行で済むところを…。
というわけで、多少意味のあるサンプルを載せたいと思います。

=LAMBDA(x, y,
    LET(
        _l, MAX(COUNTA(x), COUNTA(y)),
        EXPAND(x, _l, , 0) + EXPAND(y, _l, , 0)
    )
)(A1:A10,B1:B5)

これは、配列の各行を足し算する式です。
配列同士は普通に「+」演算子で足し算できるのですが、配列の長さが違う場合、足りない行の値が「#N/A」になってしまいます。そこで、最初にMAX関数でより長い配列を確認し、EXPAND関数で足りていない部分を0で埋める処理をしてから足し合わせています。
上記の場合、COUNTA(x)=>10、COUNTA(y)=>5なので、_l=10となります。
EXPAND(x,10,,0)は長さ10の配列を長さ10にする。つまりなにもしません。
EXPAND(y,10,,0)は長さ5の配列を長さ10に延長し、新しいセルには0を入れます。
これで#N/A無しで足し算ができる寸法です。
このように２か所以上で同じ値を使いまわす際にLET関数は威力を発揮します。

そして、LET関数の説明として欠かせないのが「関数の名前定義」でしょう。

=LET(
    _00, "配列数は10,000,000進数",
    _dgt, 7,
	
    _01, "文字列数の絶対値",
    BAbs, LAMBDA(x, REGEXREPLACE(x, "[^0-9]", "")),
	
    _02, "文字列数を配列数に変換",
    B2A, LAMBDA(x,
        LET(
            _x, BAbs(x),
            _l, LEN(_x),
            _m, CEILING.MATH(_l, _dgt),
            _y, REPT("0", _m - _l) & _x,
            _c, _m / _dgt,
            MID(_y, SORT(SEQUENCE(_c, , , _dgt), 1, -1), _dgt) * 1
        )
    ),

これは、円周率算出式の最初の部分です。
LET関数内にLAMBDA関数があります。

BAbs, LAMBDA(x, REGEXREPLACE(x, "[^0-9]", "")),

の意味は、右側のLAMBDA式に「BAbs」という「名前」を付けているのです。
これは、

_x, BAbs(x),

のようにLET関数内で使用することができます。
これがLET関数の最大のメリットだと言えます。

そして、もう一つ欠かせないのが、「再帰処理」です。
Excelには今のところ、While文やBreak文に相当する処理系はありません。
For文にあたる関数としてREDUCE関数がありますが、処理数を減らすためにどうしても途中でループを抜けたくなるものです。
そこでLET関数内で再帰処理する関数を作ります。
作り方はYコンビネータとかいくつかあるようですが、わたしには理解できなかったので、一番シンプルな関数ポインタっぽいものを使っています。

    _14, "配列数除算",
    ADv, LAMBDA(x, y,
        LET(
            F, LAMBDA(F, y, a, l,
                LET(
                    _b, VSTACK(SEQUENCE(l, , 0, 0), AAd(a, a)),
                    _z, DROP(AAd(_b, -1 * AMl(y, AMl(a, a))), l),
                    IF(ACp(a, _z) = 0, _z, F(F, y, _z, l))
                )
            ),
            _l, COUNTA(x),
            _a, LET(
                _l, _l - COUNTA(y),
                _i, INT(10 ^ _dgt / SUM(TAKE(y, -1))),
                _s, _i & REPT(REPT("0", _dgt), _l),
                B2A(_s)
            ),
            _z, DROP(AMl(x, F(F, y, _a, _l)), _l),
            IF(ACp(y, AAd(x, -1 * AMl(y, _z))) = 1, _z, AAd(_z, 1))
        )
    ),

これは、配列数の割り算処理のところですが、LET関数内に「F」という名前の関数を作っています。
このF関数は第１引数として「F」を取っており、さらに最後のIF関数内で「F(F, y, _z, l)」と、自分自身を呼び出しています。
そして、

_z, DROP(AMl(x, F(F, y, _a, _l)), _l),

と、この再帰ループする関数で値を求めています。
一見なにをしているのかわからないかと思いますが、とりあえずここは、「Excelでも再帰処理ができるんだな」ぐらいに思っていてください。

もう１つ、地味に便利なのが「コメント」です。
Excelの式にはコメントアウトの機能はありません。
もちろんLET関数にもありません。
ですが、上記コードには、

_14, "配列数除算",

の様にコメントらしき行があります。
この正体は、ただの「名前定義」です。
つまり、「"配列数除算"」という文字列に、「_14」という名前を付けているのです。
いろいろ疑問もあるかと思いますが、これも「こんなことできるのね」ぐらいに思っていただければ幸いです。

さいごに

この関数ができるまでの経緯はnoteの記事で書いています。
興味のある方はこちらの記事をご覧ください。

最後までお読みいただき、ありがとうございました。

1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?