【Excel】LET関数で数式の繰り返しを回避して短縮する方法

Excelである表のデータを参照する際、１つのセルの数式で同じ計算や参照を何度も繰り返すことがよくあります。これにより、数式が冗長で読みづらくなることがあります。この記事では、LET関数を使用して、数式内で変数を定義し、同じ計算や参照を繰り返さずに済む方法を解説します。

表データを参照する例
LET関数を使わない方法
LET関数を使う方法

表データを参照する例

今回想定する表データというのは以下のような表です。

例えば別のシートで、以下のように商品名と販売年を指定して販売数量を引用したいとします。実務ではこういった、別シートから数値を引用するのはよくあることだと思います。

この時最も簡単な方法はXLOOKUP関数を以下のように使う方法です。見やすさのために途中で改行していますが、もちろん改行がなくとも問題ありません。

改行なしだと以下のようになります。

=XLOOKUP(B2,'2001年以降のプロダクトライン種別年間販売数量データ'!A:A,INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE,,MATCH(C1,'2001年以降のプロダクトライン種別年間販売数量データ'!B2#,0)))

XLOOKUP関数については以下を参照ください。

さてここで、元のテーブルに注目すると、以下のようにデータがない場合に”0”と時もあれば空欄になっていることもあり、更に”-”となっていることもあります。

上のXLOOKUP関数は、こういった場合のためのエラー処理をしていません。

例えばあなたが上司から、次の条件を満たす場合に0とし、それ以外の場合に検索結果を返すように修正を求められたらどうしますか？

検索結果が空白
検索結果が0
検索結果が “-“

今回はこういったケースを想定します。

LET関数を使わない方法

まずは、LET関数を使わずに目的の作業を実現するための数式を見てみましょう。この数式では、XLOOKUP関数を3回繰り返して使用しています。非常に冗長で読みにくいですね。

=IF(OR(
    XLOOKUP(B2,'2001年以降のプロダクトライン種別年間販売数量データ'!A:A,INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE,,MATCH(C1,'2001年以降のプロダクトライン種別年間販売数量データ'!B2#,0)))="",
    XLOOKUP(B2,'2001年以降のプロダクトライン種別年間販売数量データ'!A:A,INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE,,MATCH(C1,'2001年以降のプロダクトライン種別年間販売数量データ'!B2#,0)))=0,
    XLOOKUP(B2,'2001年以降のプロダクトライン種別年間販売数量データ'!A:A,INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE,,MATCH(C1,'2001年以降のプロダクトライン種別年間販売数量データ'!B2#,0)))="-"
), 0, 
    XLOOKUP(B2,'2001年以降のプロダクトライン種別年間販売数量データ'!A:A,INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE,,MATCH(C1,'2001年以降のプロダクトライン種別年間販売数量データ'!B2#,0)))
)

特に非効率なのが、その結果が空白、0、または “-” であれば0を返し、の部分ですね。言葉で説明するのは簡単ですが、数式にするとずいぶん長文になります。

LET関数を使う方法

こういったケースで使えるのがLET関数です。LET関数を使用することで、同じ計算を繰り返す必要がなくなり、数式が簡潔で読みやすくなります。LET関数の基本的な使い方は以下です。

=LET(名前1, 値1, 名前2, 値2, ..., 計算式)

名前: 定義する変数の名前。
値: 変数に代入する値や計算式。
計算式: 最終的に実行する計算式。

このLET関数を使って元の数式を短縮する方法を以下に示します。

=LET(
    fc, XLOOKUP(B2, '2001年以降のプロダクトライン種別年間販売数量データ'!A:A, INDEX('2001年以降のプロダクトライン種別年間販売数量データ'!B:AE, , MATCH(C1, '2001年以降のプロダクトライン種別年間販売数量データ'!B2#, 0))),
    IF(OR(fc="", fc=0, fc="-"), 0, fc)
)

ずいぶん短縮されましたね。XLOOKUP関数の長い部分を関数(Fucntion)を示す”fc”という変数に格納することで、全体を簡略化しています。また簡略化だけではなく、IF文の条件分岐が非常に読みやすくなったと思います。これがLET関数です。

LET関数については、数式の内容をわかりやすくするという観点を含めて以下の記事も過去に掲載しています。こちらも参照ください。