巨大な Excel の数式を実際に理解できるものに変えました

途方もなく複雑な Excel の数式を作成し、それが機能することを誇らしげに発表するのが大好きな人を誰もが知っています。私もかつてはそんな人間でした。私は何十年も Excel の数式を書いてきましたが、かつては不可能だと思われていた問題を 1 つの数式で解決できる満足感がとても気に入りました。問題は、これらの公式を後から読み返したときに、印象がかなり薄れてしまう可能性があることでした。そこで登場したのがLET関数です。

私の長い Excel の数式は、それを読まなければならなくなるまでは正常に機能しました

エニグマの暗号を解いたような気分だった

この Excel テーブルを例として取り上げます。これには 9 つの列が含まれており、そのうちの 4 つは、セル B1 で選択した営業担当者の 100 点満点中のパフォーマンス スコアに反映されます。このスコアは、売上目標の達成度を 50 ポイント、利益率を 30 ポイント、平均顧客評価を 20 ポイントという 3 つの尺度を組み合わせたものです。

セル B2 に入力する数式は次のとおりです。

=MIN(SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1)/MAXIFS(tblSales(Sales Target), tblSales(Salesperson), B1), 1)*50+(SUMIFS(tblSales(Profit), tblSales(Salesperson), B1)/SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1))*30+(AVERAGEIFS(tblSales(Customer Rating), tblSales(Salesperson), B1)/5)*20

この式には実際には何も問題はありません。 Excel はそれを完全に理解しており、期待どおりのスコアを返します。問題は、その一部を変更したり、他の人に説明したり、1 年後に戻ってきたりする必要がある場合、最初に構築するのにかかる時間よりも、分解するのに時間がかかる可能性があることです。 1 つの SUMIFS が収益を計算し、MAXIFS が売上目標を取得し、別の SUMIFS が利益を計算し、AVERAGEIFS が顧客評価を生成することを確認する必要があります。

そこには繰り返しも隠れています。総収益を 2 回計算しています。1 回目は営業担当者が目標をどれだけ達成したかを計算するため、もう 1 回目は利益率を計算するためです。これにより、式が長くなり、変更が必要になった場合に維持するためのロジックが 1 つ増えます。

式を確認するときに本当に確認したいのは、その背後にあるロジック (収益、目標、利益、評価、目標スコア、利益率、評価スコア) です。数式に戻るたびに、関数、参照、括弧の壁をそれらの概念に変換する必要はありません。

LET を使用すると、各計算に名前を付けることができます

数式が突然私の言語を話し始めます

ここで、LET 関数 (Excel for Microsoft 365、Excel 2024 および 2021、Excel for the web で利用可能) が登場します。各中間結果に意味のある名前を付け、後から数式の中でその名前を使用できます。

まず、Excel に名前を使用したいことを伝えます。 revenue 私の最初の計算のために。名前が最初にあり、次にカンマが続き、その値を生成する計算が続きます。

=LET(
revenue, SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1),

次に、販売員の情報を追加します。 target:

=LET(
revenue, SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1),
target, MAXIFS(tblSales(Sales Target), tblSales(Salesperson), B1),

私も同じことをします profit そして平均的な顧客 rating:

=LET(
revenue, SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1),
target, MAXIFS(tblSales(Sales Target), tblSales(Salesperson), B1), 
profit, SUMIFS(tblSales(Profit), tblSales(Salesperson), B1), 
rating, AVERAGEIFS(tblSales(Customer Rating), tblSales(Salesperson), B1),

この時点で、複雑に見える計算の 4 つの部分を、それぞれの結果が何を表すかを正確に示す 4 つの名前に置き換えました。さらに重要なのは、これらの名前を使用して計算の次の部分を構築できるようになったということです。

の targetScore 販売員のものです revenue 彼らの target、100% に制限されます。の profitMargin は profit で割る revenue、そして ratingScore です rating 5 段階評価から変換すると、次のようになります。

=LET(
revenue, SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1),
target, MAXIFS(tblSales(Sales Target), tblSales(Salesperson), B1),
profit, SUMIFS(tblSales(Profit), tblSales(Salesperson), B1),
rating, AVERAGEIFS(tblSales(Customer Rating), tblSales(Salesperson), B1),
targetScore, MIN(revenue/target,1),
profitMargin, profit/revenue,
ratingScore, rating/5,

最後に、それぞれの重み付けを使用してこれら 3 つのスコアを結合します。

=LET(
revenue, SUMIFS(tblSales(Revenue), tblSales(Salesperson), B1),
target, MAXIFS(tblSales(Sales Target), tblSales(Salesperson), B1),
profit, SUMIFS(tblSales(Profit), tblSales(Salesperson), B1),
rating, AVERAGEIFS(tblSales(Customer Rating), tblSales(Salesperson), B1),
targetScore, MIN(revenue/target,1),
profitMargin, profit/revenue,
ratingScore, rating/5,
(targetScore*50)+(profitMargin*30)+(ratingScore*20)
)

基本的な計算は変わっていません。私は今でも、収益と利益の計算に SUMIFS を使用し、営業担当者の売上目標の取得に MAXIFS を、平均顧客評価の計算に AVERAGEIFS を使用しています。結果の意味を反映するために、これらの結果に名前を付けただけです。 revenue、 target、 profit、 そして rating。これらの名前付き変数が数式内に明確にリストされるようになったので、それらをすばやく調べて、計算の構成要素を一目で確認できるようになりました。これらの名前を使用して作成できます targetScore、 profitMargin、 そして ratingScore 最終的なスコア計算にそれらを組み合わせる前に。

また、 revenue は 1 回だけ計算され、両方で再利用されるようになりました。 targetScore そして profitMargin。また、後でこれらの計算の 1 つまたはその重み付けを変更することにした場合でも、元の式で見つけるよりもはるかに簡単に、関連する名前付きコンポーネントを見つけることができます。

これが LET の最も魅力的な部分です。同じ作業を Excel に依頼することは変わりませんが、数式が理解しやすくなり、ロジックがより可視化されます。

ヘルパー式は方程式のどこに当てはまりますか?

それは中間計算に依存します

私は、既存のデータセットに列として追加される場合でも、別のテーブルにまとめられる場合でも、ヘルパー数式の大ファンです。上のスクリーンショットはトレードオフを示しています。ヘルパー テーブルを使用すると、すべての中間計算が見やすくなり、再利用できるようになりますが、最終的に単一の値を生成する計算のために、かなりの量のスプレッドシート構造も追加されます。

これらの中間計算を別の分析、グラフ、ピボットテーブルなど他の場所で使用したい場合は、LET ではなくヘルパー数式を選択します。しかし、その代償として、余分な機械が追加されることになります。計算を追加するたびに、ブック内に別の変動部分が作成され、変動部分が増えるほど、エラーが発生する可能性が高くなります。

したがって、LET とヘルパー式のどちらを使用するかを決定するときは、通常、次の経験則に従います。中間計算がそれ自体で役立つ場合は、それにヘルパー式を与えます。単に 1 つの最終結果をサポートするだけの場合、LET はロジックを明確に保ちながら、それを 1 つの式に含めることができます。

はい、ヘルパー計算を非表示にしたりグループ化することもできますが、その場合、ナビゲートおよび維持するためのスプレッドシート構造の層がさらに追加されます。 LET を使用すると、サポートする計算を数式内に保持し、既存の構造をそのままにして、ほとんどの人が見る必要のない計算を追加することなく最終結果を表示できます。

私の公式はもう謎である必要はありません

LET のような関数のおかげで、当時は自分にしか意味がなかった長くて読めない数式を作成する必要がなくなりました。 Excel に複雑な処理を実行させることはできますが、それらの計算の背後にあるロジックをはるかに理解しやすくすることもできます。さらにコンテキストを追加したい場合は、N() 関数のトリックを使用して、平易な言語のメモを挿入できます。