トピック
「Excel」のLET関数は“複雑な数式”を“人が読める式”に変えられる! 「Office 2021」新搭載の謎関数を攻略
数式中で変数を定義できるとこんなに便利
- 提供:
- 日本マイクロソフト株式会社
2021年11月1日 06:55
WordやExcel、PowerPointなどがパッケージされた「Microsoft Office」の最新版「Office 2021」(2021年10月5日発売)。多くの機能が追加された中で、最も強化されたといわれるのが「Excel」です。
今回は、「Excel」の強化点の中で便利だという声とともに、難解だともいわれるLET関数の機能と動作を紹介します。
なお、今「Office 2021」を購入すると、特典として小冊子『できる Office 2021 新機能ガイド』がもらえます(ただし、配布冊子がなくなり次第終了。詳細な配布方法は販売店によって異なります)。LET関数をはじめとする便利になった新機能を使いこなし、より効率よく作業する具体的な手順を詳しく紹介しているので、要チェックです。
▼LET関数は「謎」の関数?
▼基本の動作は「変数の名前と式を繰り返した後で計算」
・LET関数は数式バーを広げて改行を使うのがオススメ
▼IF関数を使った複雑な式を整理、すっきりさせてメンテもしやすく
・複数の条件を使ったIF関数は複雑で見づらい
・セル範囲と数式を変数にまとめると式が一気に見やすくなる
▼複数のハイフンで区切られた文字列から一部を取り出す
・【レベル1】最初のハイフンまでを取り出す
・【レベル10】ハイフンで囲まれた真ん中の数値を取り出す
▼使いどころは「同じ関数と範囲を何度も利用する」式!
「Office 2021」関連の注目記事一覧
- 買い切り版「Office 2021」がついに登場! テレワーク向けの快適機能や、手軽に使える無料素材、最新の「XLOOKUP」関数など大幅強化
- ExcelのVLOOKUP関数にサヨナラを ~圧倒的に便利な「XLOOKUP関数」をOffice 2021で利用しよう!
- 「Excel」のLET関数は“複雑な数式”を“人が読める式”に変えられる! 「Office 2021」新搭載の謎関数を攻略
- Excel 2021では、「並び替えミスで表を破壊」が防げる新関数が!フィルター/ソート/抽出の新関数はこう使おう!
- 最新の「Outlook」は「探す」ストレスなし! 常にメール・予定・機能を賢く検索可能
- パワポはGIFアニメ作成ツールとしても有能! 万能ツール「PowerPoint」を使い倒そう
※本稿の内容はプレビュー版である「Office LTSC Professional Plus 2021 Preview」で動作確認しています。
LET関数は「謎」の関数?
実は、「Microsoft 365」ではすでにLET関数を利用できるのですが、何だかよくわからないという声も聞きます。構文も複雑に見えます。
最短で[名前][式][計算]の3つの引数で数式は成り立ちます。[名前]と[式]をセットで考えると理解しやすいと思います。[式]に[名前]を付けて、その[名前]を使って[計算]に計算式を指定します。最後の[計算]に、処理したい計算式を指定すると考えてください。
なお、[名前]はLET関数の中でのみ適用されます。命名規則は、A1やB1などのセル番地はNG、先頭に数字は使えません。Excelの関数名との重複は問題ありませんが、[名前]の重複はできません。
わざわざLET関数を使わなくてもよさそうですが、何度も同じ計算が必要になる場合は別です。[式]にはセル範囲や関数を指定でき、数式の可読性が向上するほか、簡略化も期待できます。また、[式]は1回のみの計算なので、処理も高速化されます。ここでは、よくある関数の処理をLET関数で書き換えてみます。
基本の動作は「変数の名前と式を繰り返した後で計算」
Microsoftの公式サイトに掲載されている「計算式」を参考に、LET関数の基本的な動作を確認しておきましょう。
入力する数式は「=LET(変数1,1000+100,変数1+A1)」です。引数[名前]は「変数1」としています。[名前]と[式]はセットなので、[式]は「1000+100」です。つまり「1000+100」という式に「変数1」という名前を付けたことになります。
「変数1」の内容は「1000+100」つまり「1100」です。引数[計算]には「変数1+A1」なので、1100+A1の値(11)の結果として「1111」が表示されます。
今度は[名前]と[式]のセットをひとつ増やしてみます。入力する数式は「=LET(変数1,1000+100,変数2,1000+111,変数1+変数2+A1)」です。「変数2」と「1000+111」を追加しました。
IF関数を使った複雑な式を整理、すっきりさせてメンテもしやすく
複数の条件を使ったIF関数は複雑で見づらい
以下の表には、アンケート結果から満足度を「○」と「×」で判定する数式が入力されています。『全体の合計が20以上、平均5以上、最低評価が4以上のすべてを満たした場合」に「○」、満たさなければ「×」』が条件です。
IF関数とAND関数を組み合わせて「=IF(AND(SUM(B2:E2)>=20,AVERAGE(B2:E2)>=5,MIN(B2:E2)>=4),"○","×")」と入力してあります。「集計結果の表示用」としては、ありがちな感じではないでしょうか。
普通に使うならこれで十分ですが、時折課題になるのが「しばらくたって範囲や条件を変えたくなった」ときでしょう。
自分の書いた数式の意味はほぼ忘れていますから、「範囲がなんでこうなっているのか」「3つの範囲設定が同じでいいのか?」「条件はなんでこうなっているのか」など、数式を解析するところから始める羽目になる、なんてことも多いと思います。
そんな時、内容を見やすく、そして修正もしやすくできるのがLET関数です。
セル範囲と数式を変数にまとめると式が一気に見やすくなる
ここでは、3回出現するセル範囲「B2:E2」をLET関数でまとめてみます。SUM関数、AVERAGE関数、MIN関数、AND関数の数式も変数に代入しておきます。入力する数式は以下の通りです。[名前]は日本語表記でも構いません。
=LET(
範囲,B2:E2,
合計,SUM(範囲),
平均,AVERAGE(範囲),
最小,MIN(範囲),
満足度,AND(合計>=20,平均>=5,最小>=4),
IF(満足度,"○","×")
)
どんな処理をしている式なのか、一気にわかりやすくなりました。
このように、変数を「中間結果の入れ物」として使うことで見やすくなりますし、1つのデータを使いまわすので、後から修正する場合も「ここは同じにしないといけないんだっけ……?」といった悩みがなくなります。
例えば、以下は集計範囲を「B2:E2」から「B2:C2」へ変更した例です。修正箇所が1カ所だけで済むようになり、ミスが起きにくくなるのがわかると思います。
=LET(
範囲,B2:C2,
合計,SUM(範囲),
平均,AVERAGE(範囲),
最小,MIN(範囲),
満足度,AND(合計>=20,平均>=5,最小>=4),
IF(満足度,"○","×")
)
複数のハイフンで区切られた文字列から一部を取り出す
文字列から一部を取り出す処理もよくありますね。ハイフンやカンマなどの区切り文字を目安にして、MID関数で取り出すのが一般的ですが、区切り文字が複数ある場合、FIND関数をネストする必要があり、混乱しやすい数式です。
【レベル1】最初のハイフンまでを取り出す
「型番」から「分類」「番号」「オプション」を取り出している上の表のセルB2には「=MID(A2,1,FIND("-",A2)-1)」と入力してあります。セルA2の1文字目からハイフン手前までを取得したいので、ハイフンの位置をFIND関数で探して「-1」します。これをLET関数で書き換えると、以下のようになります。1つめのハイフンを探すFIND関数の数式に「分類の区切り」と名前を付けました。
=LET(
分類の区切り,FIND("-",A2),
MID(A2,1,分類の区切り-1)
)
【レベル10】ハイフンで囲まれた真ん中の数値を取り出す
上の例ではLET関数の恩恵をあまり感じられませんね。では、真ん中のハイフンに挟まれた数値を取得する場合はどうでしょう? 1つめのハイフン+1の位置から、2つめのハイフンの位置-1までを調べる必要があるので、従来の数式はかなり複雑になります。セルC2の数式は「=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-1-FIND("-",A2))」です。
これをLET関数で書き換えると以下のようになります。1つめのハイフンを探す数式は同じ。2つめのハイフンを探すFIND関数は『「分類の区切り」で区切ったハイフンの次の文字』から探すようにして、その結果を「番号の区切り」という変数名に入れています。
=LET(
分類の区切り,FIND("-",A2),
番号の区切り,FIND("-",A2,分類の区切り+1).
MID(A2,分類の区切り+1,番号の区切り-1-分類の区切り)
)
なお、セルD2に入力した数式は以下のように記述しています。MID関数の3つめの引数[文字数]は余裕をもって「10」としていますが、決め打ちで「2」でも構いません。
=LET(
分類の区切り,FIND("-",A2),
番号の区切り, FIND("-",A2,分類の区切り+1),
MID(A2,番号の区切り+1,10)
)
使いどころは「同じ関数を何度も利用する」式!
1つの数式で同じ関数を何度も利用する場合、LET関数は有用でしょう。Microsoftの公式サイト掲載の数式の書き換えなどがいい例です。
=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-",FILTER(A2:D8,A2:A8="Fred"))
=LET(
絞り込み基準,"Fred",
絞り込み済み範囲,FILTER(A2:D8,A2:A8=絞り込み基準),
IF(ISBLANK(絞り込み済み範囲),"-",絞り込み済み範囲)
)
プログラミングに慣れているなら、変数の使えるLET関数は記述しやすいと思いますが、いつ使うのかの判断は難しいかもしれません。既存の関数で記述したほうが簡単なことも多々あります。無理矢理LET関数を使おうとせず、同じセル範囲や数式を何度も記述していると気付いた時に使ってみてください。