Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@a_eau_

Excel の LET 関数を試す

Last updated at Posted at 2021-02-14

プログラミングを真面目にやり始めてから Excel 関数 なんか 正直全然追っていませんでしたが、Office365 ではかなり高い頻度で機能が追加されており、中には Excel の可能性を大きく押し広げるような革新的な更新もあります。スピル機能と配列入力の導入とかもインパクトありました。最近 LAMBDA 関数が話題になりましたが、本記事では 2020 年 11 月に正式追加された LET 関数 についてまとめてみます。

文法

Let といえば VBA では変数に値を代入するときに使用するステートメントです（ふつう省略するので知名度は低いですが……）。このたび追加された LET 関数もそれと似た機能を持っていて、セル内数式の中で ローカル変数 を定義して使いまわすことができます。

書き方としては、左辺から変数名と値のペアを列挙していき、最右辺の計算結果が出力される感じです。

LETの基本形
=LET(name1, val1, [name2, val2,...], calculation)

従って本体となるのは最右辺の数式で、それより前の部分はひたすら変数名と値のペアを書いていくことになります。ちょうど普通のプログラミングをしているときに 代入文を書き連ねていく ような感覚で書いていくことができます。

他言語との類推
// たとえば JavaScript のこんな処理は……
const cost = 30000;
const quantity = 4;
const taxRate = 0.08;
const totalCost = cost * quantity;
const totalCostWithTax = totalCost * (1 + taxRate);
console.log(totalCostWithTax);
    
// このようなセル内数式で表現できる
=LET(
    cost, 30000,
    quantity, 4,
    taxRate, 0.08,
    totalCost, cost * quantity,
    totalCostWithTax, totalCost * (1 + taxRate),
    totalCostWithTax
)

この LET を用いると、任意の数式を変数を用いて表現しなおすことができます。たとえば以下の数式の組において、下式は上式を LET で書き換えてみたものです。式自体は長くなっていますが、変数名を設定できる ぶん数式の「意味」を表現しやすくなっています。

=SUM(A1:A25)
  ↓
=LET(begin,A1, end,A25, SUM(begin:end))

=IF(A1>=80,"合格","不合格")
  ↓
=LET(score,A1, criterion,80, IF(score>=criterion, "合格", "不合格"))

変数に自由な名前をつけられる1ので、学校で習った数学の公式みたいなものも抜群の再現度で書けますし、可搬性も高まります。LAMBDA を利用したローカル関数の定義も可能です（参考）。

// 台形の面積
=LET(底辺,A1, 高さ,B1, 底辺*高さ/2)

// 二次方程式の解の公式
=LET(a,A1, b,B1, c,C1, (-b+SQRT(b^2-4*a*c))/(2*a))

// 条件を組み入れれることもできる：
=LET(a,A1, b,B1, c,C1, det,b^2-4*a*c,
     IF(det<0, "実数解なし",
     IF(a=0,   -c/b,
               (-b+SQRT(det))/(2*a))))

// ヘロンの公式
=LET( a,A1, b,B1, c,C1, 
      s,(a+b+c)/2, 
      SQRT(s*(s-a)*(s-b)*(s-c)))

// 連立方程式の解；配列の入出力も可
=LET(matrix,A1:C3, column,D1:D3, 
     MMULT(MINVERSE(matrix),column))

2021/04/18 追記：LET 関数の中で再帰的に LAMBDA を定義する例を掲載していましたが、実際にはうまく動かないことが発覚しましたので、削除しました（経緯）。

LET 関数内で定義された変数は 再代入不可 です。たとえば以下のコードは「LET 関数に同じ名前を 2 回定義することはできません。」と怒られてしまいます。

再代入不可
// 年収1000万円以上だと丁寧なあいさつを返す（男性版）
=LET(
    name, "Taro", income, 20000000,
    name, IF(income>=10000000, CONCAT("Mr. ", name), name),
    greet, CONCAT("Hello, ", name, "!"),
    greet
)
// Expected: "Hello, Mr. Taro!"
// Actual:   「LET 関数に同じ名前を 2 回定義することはできません。」

利点

公式では以下の2つを LET 関数を使用する利点として挙げています。

パフォーマンスの向上　1 つの数式内に同じ式を複数回記述した場合、Excel はその結果を複数回計算します。 LET を使用して式を名前で呼び出し、Excel で 1 回だけ計算させることができます。

快適な閲覧と構成　特定の範囲参照またはセル参照で何を参照したか、何を計算していたか、同じ式をコピー/貼り付けしたかなどを覚えておく必要はもうありません。 変数を宣言して名前を付ける機能を使用して、自分と式のコンシューマーにとってわかりやすいコンテキストを割り当てることができます。

LET 関数の要点は数式内で 変数宣言 ができるという点に尽きます2。上掲のシンプルな例ではあまり恩恵が感じられませんが、一つの値を複数個所で使う ような複雑な数式、入れ子が重なって何行にも渡るような 秘伝の数式 になると、だんだんその効能が実感できるようになります。

たとえばある範囲の平均をとって、その平均を使って条件分岐をする、のような複数のプロセスを経るとき、LET で変数を抽象するとぐっと視認性が高まり、タイポの危険性も下がります。普通のプログラミング言語だと誰でもやってることなんですけど、これができるようになったのが大きい。

卑近な例として、5 教科の平均点を用いて評定をつけることを考えます。従来はまず ワーキングスペースに AVERAGE を算出しておき 、そこを参照して多重入れ子の IF で分岐する手法がありました (1)。多重入れ子については、最近導入された IFS 関数で改善されましたが (2)、それでもすべてを一つの数式に押し込むと冗長で保守性も悪く、その都度 AVERAGE の計算が行なわれるため非効率でもあります (3)。これに LET を組み合わせると、透明なセマンティクスを保ちつつシンプルに書け、パフォーマンスも保てるというわけです (4)。

// (1) ワーキングスペース + IF
=AVERAGE(A1:E1)
=IF(G1>=80,"優", IF(G1>=65,"良" ,IF(G1>=50,"可", "不可")))

// (2) ワーキングスペース + IFS
=AVERAGE(A1:E1)
=IFS(G1>=80,"優", G1>=65,"良", G1>=50,"可", TRUE,"不可")

// (3) IFS ワンライナー
=IFS(AVERAGE(A1:E1)>=80,"優", AVERAGE(A1:E1)>=65,"良", AVERAGE(A1:E1)>=50,"可", TRUE,"不可")

// (4) LET + IFS ワンライナー
=LET(ave,AVERAGE(A1:E1), IFS(ave>=80,"優", ave>=65,"良", ave>=50,"可", TRUE,"不可"))

数万レコードからなるデータを扱う場合、パフォーマンスにも体感できるほどの大きな差異が出るようです。

また RAND() のような生成するたびに異なる乱数を返すものに至っては、式の中で値を使いまわすことができませんでしたが、これも変数に格納することで複数個所から同一の値を参照することができるようになりました。たとえば RAND() で 0 以上 1 以下の乱数を取得して、0.5 以上のときはそのまま表示、0.5 未満のときは切り捨てて 0 と表示したいとします。(1) のように書くと、判定に使われる乱数と表示される乱数が独立に生成されますので、期待した結果が得られません（0.5 未満の数値も表示されてしまう）。ひとつの数式で完結させるには (2) のように LET 関数を使う必要があります。

// (1) 期待した結果が得られない
=IF(RAND()>=0.5, RAND(), 0)

// (2) 適切な書き方
=LET(value, RAND(), IF(value>=0.5, value, 0))

用法

公式でも挙げられている IF や FILTER との併用が人気なようです。つまるところ、3 つ以上の変数をとるような関数に変数を設定して可読性を上げたり、多重入れ子が生まれやすい関数で途中の計算結果を変数に格納して再利用することでパフォーマンスを改善したりするのがメインユースとなりそうです。

例１：FILTER + IF

公式の例。あるデータセットから Fred のレコードを抽出しつつ、値が Null のところはハイフン埋めするというタスクです3。「元の数式」は非常に意味がつかみにくいですが、「LET を使用した数式」は FILTER → ハイフン埋め、という理解しやすい順序で書けているし、なかなかにリーダブルです。

// 元の数式
=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")), "-", FILTER(A2:D8,A2:A8="Fred"))

// LET を使用した数式
=LET(filterCriteria, "Fred", 
     filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
     IF(ISBLANK(filteredRange),"-", filteredRange))

適宜改行・インデントすることにより「プログラミング」感が増して個人的には良い感じです。

さきに定義した変数をあとの変数定義中で使えるのも重要な点ですね。基本的に LET では前から 2 つずつの組で改行することで「式」を連ねていく気分で記述することができます。Excel 関数にはたぶん破壊的メソッドがないので、バグも出にくいのではないでしょうか（適当）。

例２：MAXIFS, MINIFS, SUMIFS

先ほどの IFS もそうですが、可変長引数をとる関数がどんどん増えており、LET との相性がよさそうです。たとえば、とあるコンビニの店舗のうち、東京所在かつ面積 100m2 以下の店舗を抽出して売上の最大値を求めたいとき、以下のようにさらっと書けます。

=LET(sales,B2:B1000, 
     location,C2:C1000, 
     area,D2:D1000,
     MAXIFS(sales, location,"東京", area,"<=100"))

実現したい機能は SQL の以下のような単純構文と同じですが、Excel は DB ではないことと、構文の柔軟性がないぶん関数語彙を増やして対応していることから、やや慣れが必要です。

SELECT MAX(sales)
  FROM stores
 WHERE location = '東京' and area <= 100

例３：デバッグ

これは LET 関数を使う上での小技みたいなものですが、出力計算式をいちど変数に格納してから出力するように書いておくことで、デバッグがやりやすくなります。たとえば複雑な数式を多く含む LET 関数を書いてみたが、思ったような出力が出なかった場合、あるいは検算目的で計算途中の値を覗きたい場合、最終項を書き換えるだけで簡単に変数の値を確認することができます。

// このように出力用の変数 res を噛ませておくと、
=LET(arg1, _some_value_,
     arg2, _some_value_, 
     arg3, _some_complex_formula_,
     arg4, _some_complex_formula_,
     res,  _some_complex_formula_, 
     res)

// たとえば途中の arg3 の値が知りたいときは最終項を書き換えるだけでよい。
=LET(arg1, _some_value_,
     arg2, _some_value_, 
     arg3, _some_complex_formula_,
     arg4, _some_complex_formula_,
     res,  _some_complex_formula_, 
     arg3)

出力に名前を付けられるという点でも好ましく、日ごろからこう書く癖をつけておくといいかもしれませんね。

まとめ

  • LET 関数を使うと、セル内のみ有効で再代入不可能なローカル変数を宣言できる。
  • これにより可読性の大幅な向上、冗長性の排除＝パフォーマンスの向上などが見込まれる。
  • 数式の表現可能性が広がったわけではないが、一般的なプログラミング言語の書き味に少し近づいた。
  • それはそうと、得意先の Excel で LET 関数が使えるようになるのは何年後なのだろうか？

  1. 自由と言っても Excel にも予約語がありますので、一定の制限があります。「変数の名前は、名前マネージャーで使用できる有効な名前と一致します。 たとえば、"a" は有効ですが、"c" は R1C1 形式の参照と競合するため有効ではありません」公式より。もっとも、日本語マルチバイト文字などは問題なく使用可能です。 ↩

  2. LET 関数では、LET 関数内でのみ有効なローカル変数の設定ができます。グローバル変数の設定は Excel の「名前の管理」機能を利用することで可能です。これに加えて例の LAMBDA 関数で 関数定義 ができるようになります。少しずつプログラミング言語らしさが増してきているようですが、どこに何が設定されているのか却って分かりにくいような……。IDE のように宣言までジャンプできたり、参照を検索できたりすれば随分マシなんですけど。 ↩

  3. なお公式日本語版の「LET を使用した数式」はそのままコピペするとエラーを吐きます。"Fred" を囲むダブルクオートの一つ目が全角か何かになっているからです。MS ドキュメントの自動翻訳日本語はまだまだ改善の余地があるなあ……と思います。 ↩

6
4
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
6
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?