1 つのセル、1 つの数式: 繰り返しの計算を置き換える 5 つの Excel 関数

思い出せるほど長い間、私は Excel の数式を作成し、数百行下にドラッグし、列間で計算をコピーし、一度に 1 セルずつ累計を作成してきました。これは機能しますが、特に単一のセルにある単一の数式で処理できるジョブがある場合は、作業が多すぎることにいつも気づきます。

FILTER、SORTBY、UNIQUE などの Excel 関数を使用したことがある場合は、1 つの数式を入力すると Excel が結果全体を返すというアイデアに慣れているでしょう。以下の 5 つの関数は同様のアプローチを採用していますが、動作が少し異なります。計算を一度定義すると、それを配列、行、列、または一連の値に繰り返し適用できます。

MAP は対応する値に計算を適用します

同じロジックを繰り返し適用する

MAP は 1 つ以上の配列を受け取り、対応する値を LAMBDA 関数に渡し、結果を別の配列として返します。これは、各行で数式を繰り返さずに、一連の値に対して同じ計算を実行する場合に最適です。

構文は次のとおりです。

=MAP(
array1, (array2,...),
LAMBDA(parameter1, (parameter2, ...),
calculation)
)

最初の 1 つ以上の引数は操作する配列であり、LAMBDA は対応する値の各セットに対して Excel が何を行うべきかを定義します。

T_Sales テーブルで、すべてのトランザクションの割引販売額を計算し、結果が 1,000 ドルを超えるかどうかに応じて、それぞれを「高」または「標準」に分類したいと考えています。

そのために、セル G2 に次のように書きます。

=MAP(
T_Sales(Units), T_Sales(Price), T_Sales(Discount),
LAMBDA(u, p, d,
LET(value, u*p*(1-d), IF(value>1000, "High", "Standard")))
)

MAP は 3 つの列を別々の配列として受け取ります。次に、LAMBDA は行ごとに単位、価格、割引を受け取り、割引価格を計算して分類を返します。

はい、計算列で数式を繰り返すことでこれを行うことができますが、MAP を使用すると、マルチステップ ロジックを 1 回定義するだけで済みます。また、計算の 1 つを誤って上書きすることを心配する必要がないことも意味します。

BYROW は LAMBDA に行全体を与えます

一度に 1 行ずつ処理します

BYROW は配列を受け取り、各行を個別に LAMBDA 関数に渡します。 LAMBDA は個々の値を操作するのではなく、行全体を受け取ります。つまり、その行内のすべての値に対して計算やテストを実行できます。

構文は次のとおりです。

=BYROW(
array,
LAMBDA(row,
calculation)
)

MAP との主な違いは、BYROW が複数の配列の個々の値ではなく、行全体を LAMBDA に与えることです。生徒のスコア データセットでは、各生徒が 2 つの条件に基づいて合格するかどうかを決定したいと考えています。1 つは平均スコアが少なくとも 80 点である必要があり、各生徒の個人スコアは 70 未満であってはなりません。

したがって、G2 では次のように入力します。

=BYROW(
T_Scores((Math):(History)),
LAMBDA(row,
IF(AND(AVERAGE(row)>=80, MIN(row)>=70), "Pass", "Review"))
)

BYROW は行ごとに 4 つの被験者のスコアを配列として LAMBDA に渡します。 AVERAGE は学生の総合スコアをチェックし、MIN は最低スコアが 70 以上であることを確認します。その後、IF は各学生に対して「合格」または「レビュー」のいずれかを返します。

ここでも、ヘルパー列を使用して同様の結果を達成できました。違いは、BYROW では行全体を一度に操作できるため、行ごとに個別の数式を維持することなく、複数の値に基づいて 1 つの計算を構築できることです。

BYCOL は、すべての列に 1 つの計算を適用します。

一度に 1 つの列を処理します

BYCOL は BYROW とよく似ていますが、代わりに各列を LAMBDA に渡します。これは、配列内のすべての列に対して独立して同じ計算またはテストを実行する場合に便利です。

関数の構造は次のとおりです。

=BYCOL(
array,
LAMBDA(column,
calculation)
)

今回の目的は、各科目で 80 点以上を獲得した生徒の割合を計算することです。

パーセント数値形式をセル B13:E13 に適用した後、次の数式をセル B13 に入力します。

=BYCOL(
T_Scores((Math):(History)),
LAMBDA(column,
COUNTIF(column, ">=80")/ROWS(column))
)

BYCOL は各被験者のスコアを LAMBDA に渡します。 COUNTIF は 80 以上のスコアの数をカウントしますが、ROWS は生徒の数を提供するため、結果は各科目のパーセンテージを返します。

途中のあらゆるステップを維持してください

Excel で累計を作成するにはいくつかの方法がありますが、最近では SCAN がよく使われます。各ステップの結果を保持しながら配列に計算を順次適用するため、各結果が前の結果に依存する計算に最適です。

基本的な構文は次のとおりです。

=SCAN(
(initial_value), array,
LAMBDA(accumulator, value,
calculation)
)

initial_value 出発点を確立します。 LAMBDA は、これまでに累積された結果と配列内の次の値を受け取り、次の結果を計算します。 T_Sales テーブルで、販売レコードを下に移動しながら、販売されたユニットの累計を計算しようとしています。

そのために、セル G2 に次のように書きます。

=SCAN(
0, T_Sales(Units),
LAMBDA(total, units,
total+units)
)

アキュムレータはゼロから始まります。次に、SCAN は最初のユニット数をそれに加算し、その結果を次の計算に持ち込んで、配列を下位に進みます。結果は、累計合計のこぼれたリスト (3、11、26、48 など) となり、256 で終わります。

次のような数式を繰り返す代わりに、 =SUM($C$2:C2) 列を下に進むか、前の結果に各行を追加すると、SCAN は前の結果を次の計算に引き継ぎ、1 つのセル内の 1 つの式から累計全体を返します。

REDUCE は最終結果のみを保持します

バックグラウンドで値を蓄積する

REDUCE は SCAN と密接に関連していますが、最終的な累積結果のみを返します。配列に計算を繰り返し適用し、結果を 1 つのステップから次のステップに渡します。

仕組みは次のとおりです。

=REDUCE(
(initial_value), array,
LAMBDA(accumulator, value,
calculation)
)

SCAN と同様に、初期値から始まり、アキュムレータと次の値を LAMBDA に渡します。違いは、REDUCE が最終値の処理後にアキュムレータを返すことです。私の目的は、T_Inflation データを使用して、元の価値から始めて各年のインフレ率を前年の結果に適用して、製品の価値が 6 年間でどのように変化するかを計算することです。

セル B2 に必要な数式は次のとおりです。

=REDUCE(
B1, T_Inflation(Rate),
LAMBDA(balance, inflation,
balance*(1+inflation))
)

これは、各年の計算が前の年の結果に依存するため、単純にパーセンテージを加算するよりも REDUCE の方が合理的である状況です。

繰り返しを 1 つの式で処理しましょう

特に、それが仕事を成し遂げることがすでにわかっている場合、何年も使用してきた Excel の方法に頼るのは簡単です。これらの LAMBDA ヘルパー関数は慣れるのに少し時間がかかりますが、数回使用すると、複数の数式を書いたり、ワークシート全体で同じ計算を繰り返したりする手間を省くことができます。代わりに、ロジックを一度定義すれば、単一の式で繰り返しを処理させることができます。