Microsoft365とExcel2021（※）に新機能として実装された「スピル」をご存じでしょうか？
スピルは、"数式を入力したセルだけでなく、隣接するセルにも結果が表示される"というこれまでのExcelの常識を覆す革新的な機能です。
当記事では、スピルを使用する上での注意点と、LAMBDA関数について紹介していきます。
Excelでは、ほとんどの関数がスピル機能をサポートしていますが、一部の関数はスピル機能と互換性がありません。
以下に、スピルできない主な関数を挙げます
下記の関数は、特定の範囲や複数の数値を入力として取り、単一の結果を出力するため、配列としてのスピル機能には対応していません。
数値の合計を計算します。
数値の平均を求めます。
数値を含むセルの数を数えます。
与えられた数値の中で最大の値を見つけます。
与えられた数値の中で最小の値を見つけます。
複数の論理条件を評価して単一の真偽値（TRUEまたはFALSE）を返します。これらの関数も、その性質上、複数のセルに結果をスピルすることはありません。
与えられたすべての条件をすべて満たしていればTRUEを返します。
与えられた条件のいずれか1つでも満たしていればTRUEを返します。
AND・OR関数について詳しくはこちら
下記の関数は、データのフォーマットやレポート作成時に、情報を整理して一つのセルに表示する際に非常に便利です。TEXTJOINは特に区切り文字を自由に設定できる点が特徴で、CONCATはよりシンプルな連結を行います。
どちらも結果は単一のセルに表示されるため、スピル機能とは互換性がありません。
複数の範囲や文字列を一つの文字列に結合し、指定した区切り文字を各要素の間に挿入します。
複数の範囲や文字列を一つの文字列に連結しますが、区切り文字は挿入しません。
今回は通常の方法ではスピルできない関数のうちSUM関数についてスピルさせる方法を紹介します。
LAMBDA関数は、Excelで自分だけの関数を作成するための関数です。
これにより、複雑な計算や繰り返し使う計算を一度だけ定義し、何度でも再利用することができます。
LAMBDA関数の基本構文は下記の通りです。
= LAMBDA( ([parameter1, parameter2, …,] calculation)
例1： 2つの数値を足す関数を作る
=LAMBDA(a, b, a + b)
この関数では、aとbという2つの引数を取り、それらを足した結果を返します。
実際はExcelのセルに次のように入力して使います。
=LAMBDA(a, b, a + b)(3, 4)
これにより、aに3、bに4が引数として渡され、結果として7が返されます。
スピル機能とLAMBDA関数は、Excelの効率的な使用に役立ちます。スピルで隣接するセルに結果を表示し、LAMBDA関数でカスタム関数を作成できることで、作業を効率化できます。
SUM関数は通常スピル機能をサポートしていません。
しかし、LAMBDA関数とBYROW、BYCOL関数を組み合わせることで、SUM関数をスピル可能にすることができます。この記事では、その方法について詳しく説明します。
BYROW関数は、指定した範囲の各行に対して指定したLAMBDA関数を適用するための関数です。
BYROW関数の基本構文は下記の通りです。
=BYROW(array, lambda_function)
BYCOL関数は、指定した範囲の各列に対して指定したLAMBDA関数を適用するための関数です。
BYCOL関数の基本構文は下記の通りです。
=BYCOL(array, lambda_function)
LAMBDA関数とBYROW関数を組み合わせて、行ごとの合計を計算する方法を見てみましょう。
以下の表のF列に、各行の合計をもとめる関数を入力します。数式は各行に入力するのではなく、【F4】セルに入力し結果をスピルさせます。
【F4】セルに「=BYROW(D4:E15,LAMBDA(x,SUM(x)))」と入力し確定します。
この式は、D4:E15 の範囲内の各行に対して、LAMBDA 関数が呼び出され、その行のセルの値の合計が計算されます。
BYROW 関数は、指定された範囲内の各行に対して、指定された LAMBDA 関数を適用し、結果を返します。
そのため結果がスピルされて表示されます。
次にLAMBDA関数とBYCOL関数を組み合わせて、列ごとの合計を計算する方法を見てみましょう。
以下の表の16行目に、各列の合計をもとめる関数を入力します。数式は各列に入力するのではなく、【D16】セルに入力し結果をスピルさせます。
【D16】セルに「=BYCOL(D4:F15,LAMBDA(x,SUM(x)))」と入力し確定します。
この式は、D~F列の範囲内の各列に対して、LAMBDA 関数が呼び出され、その列のセルの値の合計が計算されます。
BYCOL 関数は、指定された範囲内の各列に対して、指定された LAMBDA 関数を適用し、結果を返します。
そのため結果がスピルされて表示されます。
LAMBDA関数とBYROW、BYCOL関数を組み合わせることで、通常はスピル機能をサポートしていないSUM関数の他にも、冒頭で紹介したAVERAGE関数、MAX関数についても同じ考え方で、スピル可能にすることができます。これにより、行ごとや列ごとの合計、平均、最大値を簡単に計算し、結果を自動的に拡張することができます。
この方法を使うことで、Excelのデータ処理がさらに効率化され、業務の生産性が向上します。ぜひ、実際に試してみて、自分の業務に役立ててください。
コクーのExcel研修では、さまざまなテクニックを習得し、データをより効果的に扱う研修も実施しています。
実務経験者が講師のため、現場ですぐに使うことができる実践的なカリキュラムのご提供が可能です。
まずは詳細だけでも聞いてみたい、業務効率化したいけど何をしたらいいのか分からなくて困っている、といった内容など、いつでもお気軽にご相談ください。