TOP
お役立ち情報
コラム
【図解付き】SUM関数をスピルさせる方法とは？《LAMBDA関数の基本と便利技》

【図解付き】SUM関数をスピルさせる方法とは？《LAMBDA関数の基本と便利技》

更新日：2024.09.03

Microsoft365とExcel2021（※）に新機能として実装された「スピル」をご存じでしょうか？

スピルは、"数式を入力したセルだけでなく、隣接するセルにも結果が表示される"というこれまでのExcelの常識を覆す革新的な機能です。

当記事では、スピルを使用する上での注意点と、LAMBDA関数について紹介していきます。

監修：みずき（コクー社員）

EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい！と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています！

スピルが使えない関数

Excelでは、ほとんどの関数がスピル機能をサポートしていますが、一部の関数はスピル機能と互換性がありません。

以下に、スピルできない主な関数を挙げます

範囲に対して計算をする関数

下記の関数は、特定の範囲や複数の数値を入力として取り、単一の結果を出力するため、配列としてのスピル機能には対応していません。

SUM

数値の合計を計算します。

AVERAGE

数値の平均を求めます。

COUNT

数値を含むセルの数を数えます。

MAX

与えられた数値の中で最大の値を見つけます。

MIN

与えられた数値の中で最小の値を見つけます。

論理関数

複数の論理条件を評価して単一の真偽値（TRUEまたはFALSE）を返します。これらの関数も、その性質上、複数のセルに結果をスピルすることはありません。

AND

与えられたすべての条件をすべて満たしていればTRUEを返します。

OR

与えられた条件のいずれか1つでも満たしていればTRUEを返します。

AND・OR関数について詳しくはこちら

文字列関数

下記の関数は、データのフォーマットやレポート作成時に、情報を整理して一つのセルに表示する際に非常に便利です。TEXTJOINは特に区切り文字を自由に設定できる点が特徴で、CONCATはよりシンプルな連結を行います。

どちらも結果は単一のセルに表示されるため、スピル機能とは互換性がありません。

TEXTJOIN

複数の範囲や文字列を一つの文字列に結合し、指定した区切り文字を各要素の間に挿入します。

CONCAT

複数の範囲や文字列を一つの文字列に連結しますが、区切り文字は挿入しません。

今回は通常の方法ではスピルできない関数のうちSUM関数についてスピルさせる方法を紹介します。

LAMBDA関数とは

LAMBDA関数は、Excelで自分だけの関数を作成するための関数です。

これにより、複雑な計算や繰り返し使う計算を一度だけ定義し、何度でも再利用することができます。

LAMBDA関数の基本構文は下記の通りです。

LAMBDA関数

= LAMBDA( ([parameter1, parameter2, …,] calculation)

parameter：
セル参照、文字列、数値などの関数に渡す値。

calculation：
関数の結果として実行して返す数式。最後の引数である必要性があり、結果を返す必要があります。
この引数は必須です。

LAMBDA関数の基本例

例1： 2つの数値を足す関数を作る

LAMBDA関数の作成

=LAMBDA(a, b, a + b)

この関数では、aとbという2つの引数を取り、それらを足した結果を返します。

LAMBDA関数の使用

実際はExcelのセルに次のように入力して使います。

=LAMBDA(a, b, a + b)(3, 4)

これにより、aに3、bに4が引数として渡され、結果として7が返されます。

Lamda_01

スピル機能とLAMBDA関数は、Excelの効率的な使用に役立ちます。スピルで隣接するセルに結果を表示し、LAMBDA関数でカスタム関数を作成できることで、作業を効率化できます。

SUM関数をスピルさせる

SUM関数は通常スピル機能をサポートしていません。

しかし、LAMBDA関数とBYROW、BYCOL関数を組み合わせることで、SUM関数をスピル可能にすることができます。この記事では、その方法について詳しく説明します。

BYROW関数とは

BYROW関数は、指定した範囲の各行に対して指定したLAMBDA関数を適用するための関数です。

BYROW関数の基本構文は下記の通りです。

BYROW関数

=BYROW(array, lambda_function)

array: 対象となるセル範囲。
lambda_function: 各行に適用するLAMBDA関数。

BYCOL関数とは

BYCOL関数は、指定した範囲の各列に対して指定したLAMBDA関数を適用するための関数です。

BYCOL関数の基本構文は下記の通りです。

BYCOL関数

=BYCOL(array, lambda_function)

array: 対象となるセル範囲。
lambda_function: 各行に適用するLAMBDA関数。

実際の使い方

LAMBDA関数とBYROW関数を組み合わせて、行ごとの合計を計算する方法を見てみましょう。

以下の表のF列に、各行の合計をもとめる関数を入力します。数式は各行に入力するのではなく、【F4】セルに入力し結果をスピルさせます。

Lamda_02

【F4】セルに「=BYROW(D4:E15,LAMBDA(x,SUM(x)))」と入力し確定します。

この式は、D4:E15 の範囲内の各行に対して、LAMBDA 関数が呼び出され、その行のセルの値の合計が計算されます。

BYROW 関数は、指定された範囲内の各行に対して、指定された LAMBDA 関数を適用し、結果を返します。

そのため結果がスピルされて表示されます。

Lamda_03

次にLAMBDA関数とBYCOL関数を組み合わせて、列ごとの合計を計算する方法を見てみましょう。

以下の表の16行目に、各列の合計をもとめる関数を入力します。数式は各列に入力するのではなく、【D16】セルに入力し結果をスピルさせます。

Lamda_04

【D16】セルに「=BYCOL(D4:F15,LAMBDA(x,SUM(x)))」と入力し確定します。

この式は、D~F列の範囲内の各列に対して、LAMBDA 関数が呼び出され、その列のセルの値の合計が計算されます。

BYCOL 関数は、指定された範囲内の各列に対して、指定された LAMBDA 関数を適用し、結果を返します。

そのため結果がスピルされて表示されます。

LAMBDA関数とBYROW、BYCOL関数を組み合わせることで、通常はスピル機能をサポートしていないSUM関数の他にも、冒頭で紹介したAVERAGE関数、MAX関数についても同じ考え方で、スピル可能にすることができます。これにより、行ごとや列ごとの合計、平均、最大値を簡単に計算し、結果を自動的に拡張することができます。

この方法を使うことで、Excelのデータ処理がさらに効率化され、業務の生産性が向上します。ぜひ、実際に試してみて、自分の業務に役立ててください。