エクセル SCAN 関数:セルの範囲をループして LAMBDA で処理した過程の配列を作成する

はじめに

エクセルの SCAN 関数の使い方を紹介します。

SCAN 関数はセルの範囲をループして LAMBDA 関数で処理した過程の配列を作成します。「REDUCE 関数」の計算している過程をそのまま配列にします。

=SCAN(0,A1:A3,LAMBDA(total,x,total+x)) のようにして、合計を求める過程を配列にできます。

=SCAN(0,A1:A3,LAMBDA(total,x,IF(条件,total+x,total))) のようにして、条件に一致するときは合計を求め、それ以外のときは直前の合計を取得できます。

MAP 関数」から、複数条件の入力や複数のセルの範囲を参照できます。

ループした結果を取得するには「REDUCE 関数」を使用します。
ループする方法は「セルや文字列をループする」をご覧ください。
この関数は 365 で使用できます。
  • 目次
    • SCAN 関数の引数
    • 使い方
    • 使い方、条件を指定
    • 解説

SCAN 関数の引数

SCAN(初期値, 範囲, LAMBDA)
範囲の値をLAMBDAで処理して初期値に加えた値を配列の要素にして取得します。

引数「初期値」合計の初期値を指定します。最初の引数「LAMBDA」の第 1 引数に渡します。
引数「範囲」セルの範囲や配列を指定します。
引数「LAMBDA」LAMBDA 関数を指定します。LAMBDA 関数の第 1 引数に引数「初期値」の値が渡されます。第 2 引数に引数「範囲」の各値が順番に渡されます。それを計算した結果を次の LAMBDA 関数の第 1 引数に渡します。

LAMBDA 関数

LAMBDA 関数」の引数は次のようになっています。

LAMBDA(合計,値,計算)

引数「合計」に SCAN 関数の引数「初期値」の値が渡されます。

引数「値」に SCAN 関数の引数「範囲」の値が 1 つずつ順番に渡されます。

引数「計算」の結果が次に呼び出す LAMBDA 関数の引数「合計」に渡されます。それぞれの値が SCAN 関数が返す配列の要素になります。

使い方

SCAN 関数の使い方を紹介します。

結果は配列になり複数のセルに表示されます。これはスピルという機能によるものです。詳しくは「スピル、動的配列数式の使い方」をご覧ください。

残額を表示する

100 からセルの範囲「B4」~「B7」を引いた残額を表示します。

=SCAN(100,B4:B7,LAMBDA(total,x,total-x))

引数「LAMBDA」の第 2 引数に「B4:B7」のセルを順番に渡します。引数「LAMBDA」の結果が、次の LAMBDA の第 1 引数に渡されます。次のように処理されます。

100-B4
90-B5
70-B6
40-B7
1

文字を結合する

セルの範囲「B4」~「B7」の文字列を結合します。

=SCAN("",B4:B7,LAMBDA(total,x,total&x))

引数「LAMBDA」は次のように処理されます。

""&B4
"A"&B5
"AB"&B6
"ABC"&B7
2

最大値を表示する

セルの範囲「B4」~「B7」の最大値を表示します。「MAX 関数」を使用して最大値を取得できます。

=SCAN(0,B4:B7,LAMBDA(total,x,MAX(total,x)))

引数「LAMBDA」は次のように処理されます。

MAX(0,B4)
MAX(10,B5)
MAX(30,B6)
MAX(30,B7)
3

使い方、条件を指定

SCAN 関数の条件を指定する使い方を紹介します。

100 以上の値だけ合計する

値が 100 以上のときに合計します。それ以外はそれまでの合計を表示します。

=SCAN(0,B4:B7,
LAMBDA(total,x,
IF(x>=100,total+x,total)))
4

数値のセルの数をカウントする

セルの値が数値の数をカウントします。それ以外はそれまでの件数を表示します。「ISNUMBER 関数」を使用して数値か判定できます。

=SCAN(0,B4:B7,
LAMBDA(total,x,
IF(ISNUMBER(x),total+1,total)))
5

名前がエクセルの値を合計する

名前が エクセル の値を合計します。それ以外はそれまでの合計を表示します。

=SCAN(0,
MAP(B4:B7,C4:C7,LAMBDA(b,c,IF(b="エクセル",c,FALSE))),
LAMBDA(total,x,total+x))

SCAN 関数では複数のセルの範囲を参照できないので、「MAP 関数」に条件を入力し、それを引数「範囲」に指定します。条件に一致するときは値を返し、それ以外のときは FALSE や 0 を返します。「MAP 関数」は次の結果を返します。

{FALSE; 20; FALSE; 200}

FALSE を足し算しても合計は変わらないので、結果的に条件に一致する合計を求められます。

7

複数条件

上記の「名前がエクセルの値を合計する」のように、複数のセルの範囲を条件にするには「MAP 関数」に複数条件を入力します。

解説

REDUCE 関数」で計算している過程をそのまま配列にして取得します。

引数「初期値」には、合計の初期値を指定します。最初の引数「LAMBDA」の第 1 引数に渡されます。

引数「LAMBDA」の第 2 引数に引数「範囲」の値を順番に渡します。

引数「LAMBDA」で計算した結果が次の引数「LAMBDA」の第 1 引数に渡され、結果として返す配列の要素になります。

引数「LAMBDA」の引数の数が 2 つでないときはエラー #VALUE! になります。

引数「LAMBDA」が「LAMBDA 関数」でないときはエラー #VALUE! になります。

複数のセルの範囲

複数のセルの範囲の合計を求めるには「MAP 関数」を使用して、複数のセルの範囲をまとめます。それを SCAN 関数で合計を求めます。

=SCAN(0,
MAP(B4:B7,C4:C7,LAMBDA(b,c,b+c)),
LAMBDA(total,x,total+x))

複数のセルの範囲に条件を付けて合計を求めるには「MAP 関数」を使用して、複数のセルの範囲をまとめます。条件に一致しないセルの値は FALSE や 0 にします。それを SCAN 関数で合計を求めます。上記の「名前がエクセルの値を合計する」が使用例です。

=SCAN(0,
MAP(B4:B7,C4:C7,LAMBDA(b,c,IF(b="エクセル",c,FALSE))),
LAMBDA(total,x,total+x))