Excelをマスターする：スプレッドシートの達人になるための3つの関数

Excelには数千もの関数がありますが、ほとんどのユーザーはSUMやAVERAGEといった基本的な関数しか使いません。これらの関数は単純な作業には十分ですが、より複雑なシナリオをはるかに少ない労力で処理できる関数が3つあります。SEQUENCE、LET、LAMBDA関数はそれほど頻繁に使用されるわけではありませんが、面倒な回避策や、管理が困難な長い数式を必要とする特定の問題を解決します。

これらの関数を使用することで、複数の補助列を作成したり、数十のセルに数式をコピーしたりする必要がなくなり、自動的に更新される動的なスタンドアロンソリューションを構築できます。連続データの生成、複雑な計算の管理、再利用可能なカスタム関数の作成など、これらの関数は特に役立ちます。 作業を大幅に節約できるExcel関数.

4. SEQUENCE機能: データを自動生成

動的な数字と日付のシーケンスを作成する

SEQUENCE関数は、各値を手動で入力することなく、シリアル番号の配列を作成します。従業員ID、請求書番号、日付範囲のリストなど、必要なデータの種類を問わず、この関数はシームレスに処理します。

式は単純かつ明快です。

=SEQUENCE(行、[列]、[開始]、[ステップ])

パラメータを分析してみましょう:

• 行： 縦方向に必要な数字の数を指定します。
• 列： 水平方向の広がりを制御します - 1 列の場合は空白のままにします。
• 開始： 開始番号を指定します。デフォルト値は 1 です。
• ステップ： 数値間の増分を指定します。デフォルト値も 1 です。

売上データセットの場合、SEQUENCE関数は参照番号を生成するのに便利です。例えば、次の数式は1から32までの番号を生成します。

=シーケンス(32)

同様に、1001 から開始する必要がある場合は、次を使用できます。

=シーケンス(32, 1, 1001)

この関数は日付の連続にも役立ちます。次の数式は、1月XNUMX日から始まるXNUMXの連続した日付を生成します。これは、月次レポートやプロジェクトスケジュールに日付を手動で入力するよりも便利です。

=シーケンス(12, 1, 日付(2025, 1, 1), 1)

SEQUENCE 関数と NUMBERS 関数を組み合わせて、営業日のみを作成することもできます。 Excelのその他の日付より高度なスケジュール シナリオの場合は、WORKDAY などを使用します。

3. LET 関数を使用すると、複雑な数式を保守しやすくなります。

繰り返しの計算を排除し、読みやすさを向上させます。

LETは数式内の値に名前を付けます。これにより、繰り返し計算がなくなり、計算結果が読みやすくなります。同じ式を何度も入力する代わりに、一度定義して名前で参照することができます。

文の構造は次のパターンに従います。

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

名前と値のペアを追加することで、複数の変数を定義できます。計算では最終的に、これらの名前付き変数を使用して結果が生成されます。

売上データセットが与えられており、営業担当者のコミッションとボーナスを計算するとします。LET関数を使用しない場合、次のように記述します。

=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)

B2*0.05の手数料計算はXNUMX回表示されます。LETを使用すると、さらに明確になります。

=LET(手数料, G2*0.05, IF(手数料>500, 手数料*1.1, 手数料))

計算方法は同じですが、「手数料」は最初に一度だけ設定されます。手数料率の変更は1か所だけで済みます。

複雑な利益率分析には、LETがより有効です。次の例では、各構成要素を明確に定義しています。

=LET(収益, G2, コスト, L2, マージン, (収益-コスト)/収益, IF(マージン>0.3, "高", IF(マージン>0.15, "中", "低")))

この計算式は利益率をパーセンテージで計算し、それを高（30%以上）、中（15～30%）、低（15%未満）に分類します。各要素には明確な名前が付けられているため、ロジックが簡単に理解できます。

この方法により、式の複雑さが半分に減ります。 後でスプレッドシートを修正したり変更したりするのが簡単になります。

2. LAMBDA 関数は再利用可能なカスタム関数を作成します。

定期的なビジネスロジック用のカスタム関数を作成する

LAMBDA関数を使用すると、ワークブック全体で繰り返し使用できるカスタム関数を作成できます。数式をあちこちにコピーする代わりに、入力値を受け取り計算結果を返す単一の関数を作成できます。

式は次のとおりです。

=LAMBDA(パラメータ1, [パラメータ2, ...], 計算)

パラメータはプレースホルダーとして機能します。関数を呼び出す際に、これらのプレースホルダーを置き換える実際の値を渡します。計算ではこれらのパラメータを使用して出力が生成されます。

加重パフォーマンススコアを頻繁に計算するとします。次のようなLAMBDA関数を作成できます。

=LAMBDA(売上高, ノルマ, 重量, (売上高/ノルマ)*重量)

実際の売上、売上ノルマ、そして重み係数の3つの入力値を受け取る再利用可能な関数を作成します。この関数は、売上をノルマで割り、重みを掛けることで、重み付けされたパフォーマンススコアを返します。Excelの名前の管理を使って、この関数に「PerformanceScore」という名前を付けてください。

これで、ワークブック内のどこからでもこの関数を呼び出すことができます。

=パフォーマンススコア(B2, C2, 0.7)

この関数は、提供された販売額、シェア、重み付け係数を使用してパフォーマンス スコアを計算します。

地域を分析するには、収益に基づいて地域をランク付けする関数を作成します。

=LAMBDA(収益, IF(収益>100000, "高", IF(収益>50000, "中", "低")))

この関数は、収益を100,000つのレベルに分類します。50,000万ドルを超える場合は「高」、100,000万ドルから50,000万ドルの場合は「中」、XNUMX万ドル未満の場合は「低」です。この関数に「Revenue」という名前を付け、次のようにすべてのワークシートで使用できます。

=収益(J2)

LAMBDA関数は他の関数でも機能し、 人間の言語で数式を書くことができます あいまいなセル参照の代わりに説明的な名前を使用します。

1. これらの機能を組み合わせて強力なソリューションを作成します。

包括的なビジネス分析ツールの構築

SEQUENCE、LET、LAMBDAを組み合わせて使用​​すると、複数の補助列や複雑な配列数式が必要となる問題を解決できます。この組み合わせにより、動的で保守性の高いソリューションが実現します。

売上データを使った売上予測ツールの構築を考えてみましょう。次の数式は、単一の開始売上額に対して12か月間の売上予測を計算します。まず、LET関数を使って2つの主要な変数を定義します。セルGXNUMXの値を基準売上値として取得します。

=LET(ベース売上, G2, 成長率, L2, ProjectMonthly, LAMBDA(月, ベース売上 * (1 + 成長率)^月), ProjectMonthly(SEQUENCE(12)))

次に、L2から月間成長率0.04（4%）を取得します。この値を変更することで、様々なシナリオをモデル化できます。次に、ProjectMonthlyという小さな再利用可能な関数を定義します。この関数は、ベースライン売上高と成長率に基づいて、特定の月の予測売上高を計算します。

さらに、ProjectMonthly関数を呼び出し、SEQUENCE(12)を渡します。これにより1から12までの数値の配列が生成され、LAMBDA関数は自動的にこの配列内の各数値に対して計算を適用します。

目標達成度に基づいて報酬を計算する便利な報酬計算機をご紹介します。

=LAMBDA(売上高, 目標, LET(比率, 売上高/目標, IF(比率>=1.2, 売上高*0.08, IF(比率>=1, 売上高*0.05, 0))))

最初は小さく始めて、徐々に複雑さを増していきます。

これらの関数は、慎重に組み合わせることで最大限の効果を発揮します。まずはシンプルなアプリケーションから始めましょう。SEQUENCE関数でテストデータを作成し、LET関数で重複計算を整理し、LAMBDA関数で頻繁に使用するビジネスルールを作成します。それぞれの関数を個別に使いこなせるようになれば、自然とそれらを組み合わせてより洗練されたソリューションを構築できるようになるでしょう。

学習曲線は急峻ではありませんが、その効果は絶大です。スプレッドシートの信頼性が向上し、監査が容易になり、ビジネス要件の変化に合わせた修正も容易になります。だからこそ、これらの3つの機能は、データを日常的に扱うすべての人にとって特に価値のあるものとなるのです。