• Excel
  • 業務効率化
  • 新関数

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

更新日:2024.09.03

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

目次

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のデータ処理がさらに効率化され、業務の生産性が向上します。ぜひ、実際に試してみて、自分の業務に役立ててください。

「Excel研修」のアウトソーシングはコクーにお任せください

コクーのExcel研修では、さまざまなテクニックを習得し、データをより効果的に扱う研修も実施しています。

実務経験者が講師のため、現場ですぐに使うことができる実践的なカリキュラムのご提供が可能です。

まずは詳細だけでも聞いてみたい、業務効率化したいけど何をしたらいいのか分からなくて困っている、といった内容など、いつでもお気軽にご相談ください。

Excel女子研修資料

Excel研修サービス案内

実際に現場で活躍するEXCEL女子が考えたカリキュラムをもとに、学習するExcel研修のご紹介です。

資料ダウンロード
各種事務の効率化・DX化サポート『EXCEL女子』

各種事務の効率化・DX化サポート
『EXCEL女子』

Excelの関数やピポットテーブルを駆使し、ワンランク上の事務サポートを提供するサービスのご案内資料です。
資料ダウンロード
料金表

料金表

  • ワンランク上の事務DX人材派遣サービス「EXCEL女子」
  • VBAを活用した業務効率化サービス「VBA女子」
  • RPAを活用した自動化・効率化サービス「RPA女子」
  • 研修サービス ほか
資料ダウンロード
PAGE TOP