Excel。関数内で名前の定義をして可読化UPのLET関数【LET】

Excel。関数内で名前の定義をして可読化UPのLET関数

＜LET関数＞

複雑な数式の場合、数式内に、同じ文字が繰り返しつかわれていることがあります。

例えば、次のような場合です。

C2の数式は、

=IFS(B2:B11>=80,"A",B2:B11>=70,"B",B2:B11>=50,"C",B2:B11>=40,"D",TRUE,"E")

というIFS関数をつかったAからEの判定をする数式を設定しました。

この数式は、スピル機能に対応しているので、オートフィルで数式をコピーする必要はありません。

この数式をみると、B2:B11　という文字列が何度も登場しています。

そこで、このB2:B11を、通常の名前の定義ではなくて、数式内で名前の定義ができるLET関数をつかって修正したいと思います。

この程度の数式ならば、このままIFS関数でもいいですし、通常の名前の定義でもいいと思いますが、MOSの出題範囲ということで、LET関数をつかってみました。

LET関数は、数式にも名前の定義ができますので、通常の名前の定義よりも、数式としては使い勝手がいいかもしれません。

では、LET関数をつかって、置き換えてみます。

LET関数を使った数式は、

=LET(判定,B2:B11,IFS(判定>=80,"A",判定>=70,"B",判定>=50,"C",判定>=40,"D",TRUE,"E"))

数式自体の文字数はあまり変わってはいませんが、B2:B11を「判定」と定義したことで、IFS関数の引数では、可動性が改選されています。

このLET関数の引数を確認しておきましょう。

最初の引数は、名前です。

「判定」という名前を定義しました。

2つ目の引数は、名前値です。

名前で定義した、範囲や数式などを設定します。今回は、B2:B11。

3つ目は、計算または名前2です。

計算式なので、今回はIFS関数の箇所となっております。

LET関数は、Office2021から登場した関数なので、新しい関数ですが、MOS Excel365Expertの出題範囲のため、押さえておくといいかもしれませんね。

また、現場では、とても長い数式を置き換えることで、LET関数が活躍すると思われます。

Excel。LET関数は計算結果を名前に割り当てることができます。【LET】

Excel。LET関数は計算結果を名前に割り当てることができます。

＜関数辞典：LET関数＞

LET関数

読み方： レット　　

分類： 論理 

LET(名前1,名前値1,計算または名前2,[名前値2,…)

計算結果を名前に割り当てます。

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる【Excluding】

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる

＜SUM+MAX+MIN+COUNT関数・LET関数＞

最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。

AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。

C2:C6まで、売上金額が入力されています。

この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。

=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)

長いですが、シンプルな関数しかつかっていません。

だけど、C2:C6という範囲選択が何度も登場してきます。

数式を説明すると、

SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)

SUM関数で、合計を算出します。

その値から、MAX関数で最大値をMIN関数で最小値を減算します。

この値を

COUNT(C2:C6)-2　の算出結果で除算します。

COUNT関数で数値の件数を算出することができます。

「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。

この数式で算出して、何ら問題はありません。

今回は、この数式をLET関数で、算出してみます。

LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。

ただ、どのように使うのか、わかりにくい関数といわれています。

そこで、比較的わかりやすい　範囲選択　を名前に設定してみます。

D9にLET関数をつかって数式をつくります。

=LET(rg,C2:C6,(SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2))

数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。

では、この数式の説明をします。

LET関数の最初の引数は、名前。

プログラムでいうところに変数。あるいは、名前の定義です。

今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。

2つ目の引数は、式。

式となっていますが、数式でもいいし、範囲でも指定できます。

繰り返し設定するところを設定するといいので、「C2:C6」としました。

この名前と式を複数設定することも出来ます。

3つ目は、計算式。

SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2

C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。

このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。

Excel。新しく追加された「LET関数」ってどういう時に使ったらいいの？【New function】

Excel。新しく追加された「LET関数」ってどういう時に使ったらいいの？

＜LET関数＞

Office365に新しく追加された関数に、「LET関数」というのがあります。

ただ、使い勝手がわからないというか、どこで使ったらいいのかイマイチわかりにくい関数なんですね。

Microsoftさんの説明には、「LET 関数は、計算結果に名前を割り当てます。 これにより、中間計算、値、定義名などを数式内に格納できます。」とあります。

きっと、色々な使い方があるのでしょうけど、簡単に言ってしまえば、一度別のセルに計算結果を算出して、その算出結果を使って、回答を求めるような2段階なことはしなくてもいいということなんだと思います。

LET関数がもっている引数はというと、

LET(名前1,名前1の値,計算または名前2…)

です。

とりあえず、動きを確認してみましょう。

次の表を用意しました。

やりたいことは、H列に、C列・E列・G列の合算値が200点以上ならば、「Good!」と表示したいわけです。

早速、H2にLET関数をつかった数式を作っていきます。

=LET(W,C2,L,E2,H,G2,IF(SUM(W,L,H)>=200,"Good!",""))

C2には、Writingの「W」。

E2には、Listeningの「L」。

G2には、Hearingの「H」と名前を設定して、その名前を使って、IF関数。

WLHの合算値が200以上だったら「Good!」と表示する数式です。

オートフィルで数式をコピーしてみましょう。

ご覧のように、200点以上のものに、「Good!」と表示することができました。

普通ならば、C列とE列とG列の合算値を算出する列を用意して、算出された合算値をつかって、IF関数をつかった判定を行うことが多いと思いますが、合算値を算出する作業である、中間計算を合わせて、行っています。

しかし、よく考え見ると、別にIF+SUM関数のネストでも同じように結果を算出することができます。

=IF(SUM(C2,E2,G2)>=200,"Good!","")

まぁ、たしかに、SUM関数の引数が「,（カンマ）」で選択するのは大変面倒なのと、対象件数が増えてしまうと、わかりにくくなる傾向からミスを誘発しかねないことを考えれば、LET関数を使う方がいいのかもしれませんね。

とくに今回のように、途中途中に、日付の列があったりすると、次の数式のように

=IF(SUM(C2:F2)>=200,"Good!","")

とSUM(C2:F2)のように範囲選択してしまうと、日付はシリアル値であるために、合算対象になってしまうので、結果が200を超過してしまうために、すべてが「Good!」と表示されてしまいます。

新しく登場したLET関数。

新しく追加された理由があるはずなので、Excel VBAや、スピル機能との連動や新しく追加された関数などと、ネストしたり組み合わせて使ったりすることで、きっと効果を発揮するのではないかなぁ～思っています。