今回は、ExcelのSCAN関数を使って、累計の計算や途中経過の一覧を1つの数式で求める方法を紹介します。
売上の累計や在庫数の推移を出すとき、これまでは1行目に数式を入れて下の行へコピーする方法が一般的でした。この方法でも計算はできますが、行を追加したときに数式のコピーが漏れたり、途中の数式だけが書き換わっていたりと、管理の手間がかかります。SCAN関数を使うと、範囲全体の途中経過を1つの数式でまとめて返せるため、数式の管理がシンプルになります。
この記事は、Microsoftのサポート情報で対象とされているExcel for Microsoft 365（Windows版・Mac版）とExcel 2024（Windows版・Mac版）を前提にしています。それより前の買い切り版（Excel 2021以前）では、SCAN関数を使うことはできません。
SCAN関数の基本
SCAN関数は、配列（セル範囲）の値を先頭から1つずつ取り出し、LAMBDA関数で指定した計算を順番に適用していく関数です。そのつど計算した中間の結果をすべて配列として返すのが特徴です。
構文と引数
構文は次のとおりです。
=SCAN([初期値], 配列, LAMBDA(アキュムレーター, 値, 計算式))
- 初期値：計算の出発点になる値です。累計なら0、文字列をつなぐなら””（空の文字列）を指定します。
- 配列：順番に処理したいセル範囲や配列です。
- LAMBDA：2つの引数を受け取り、1つの計算式を返すLAMBDA関数です。1つ目の引数（アキュムレーター）には直前までの計算結果が、2つ目の引数には配列から取り出した現在の値が入ります。
LAMBDAの中の引数名は自由に決められます。この記事では、直前までの結果を「a」、現在の値を「v」と書きます。引数の数が正しくないなど、LAMBDAの指定に誤りがある場合は、#VALUE!エラーになります。
計算の流れをイメージする
SCAN関数の動きは、次のような繰り返しと考えると分かりやすくなります。
- アキュムレーターに初期値が入ります。
- 配列の1つ目の値を取り出し、LAMBDAの計算式で計算します。
- その結果を1つ目の答えとして記録し、次の計算のアキュムレーターに引き継ぎます。
- 配列の最後の値まで、同じ処理を繰り返します。
記録された答えは、元の配列と同じ形の配列として返され、数式を入れたセルから下（または右）へ自動的に広がって表示されます。この広がりはスピルと呼ばれます。
SCAN関数で累計を求める
もっとも基本的な使い方が、売上などの累計です。B2:B13に月ごとの売上が入力されているとします。C2に次の数式を入力します。
=SCAN(0, B2:B13, LAMBDA(a, v, a+v))
初期値0から始めて、各月の売上を順に足し込み、その時点の合計を1行ずつ返します。C2に1つ数式を入れるだけで、C2:C13に12か月分の累計が表示されます。
従来の方法と比べると、違いは次のとおりです。
- 従来の方法：C2に=SUM($B$2:B2)のような数式を入れ、C13までコピーする。行ごとに数式が存在する。
- SCAN関数：C2の1か所だけに数式があり、残りのセルはスピルによる表示になる。
数式が1か所にまとまっているため、計算方法を変えたいときもC2だけを修正すれば全体に反映されます。途中の行だけ別の数式に書き換わってしまう、といった事故も起こりにくくなります。
累計以外の便利な使い方
SCAN関数は、LAMBDAの計算式を変えることで、足し算以外の途中経過も求められます。ここでは実務で使いやすい例を紹介します。
在庫数の推移を求める
期首の在庫数がE1に、日ごとの入庫数がB2:B31、出庫数がC2:C31に入力されているとします。日ごとの在庫数の推移は、次の数式で求められます。
=SCAN(E1, B2:B31-C2:C31, LAMBDA(a, v, a+v))
ポイントは2つあります。
- 初期値に期首の在庫数のセルを指定しているため、そこから増減を積み上げていけます。
- 配列の引数に「入庫－出庫」の計算をそのまま書くことで、補助列を作らずに日ごとの増減を渡しています。
空白の行で累計をリセットする
グループごとに累計を出したい場合、グループの区切りに空白行を入れておけば、そこで累計を0に戻せます。
=SCAN(0, B2:B20, LAMBDA(a, v, IF(v=””, 0, a+v)))
現在の値が空白のときは0を返し、それ以外は直前の結果に足し込みます。区切りのたびに累計がやり直されるため、週ごとや担当者ごとの小計の推移を確認するときに使えます。
その時点までの最大値を求める
「これまでの最高記録」のように、その時点までの最大値を並べたい場合は、足し算の代わりにMAX関数を使います。
=SCAN(B2, B2:B13, LAMBDA(a, v, MAX(a, v)))
初期値を0にすると、すべての値がマイナスの場合に正しい結果になりません。初期値に配列の先頭のセルを指定すると、値の正負にかかわらず期待どおりに動きます。最小値を求めたい場合は、MAXをMINに置き換えます。
文字列を順番につなげる
SCAN関数は数値だけでなく文字列も扱えます。A2:A6に入力された文字を、上から順につなげた途中経過を表示するには、初期値に””を指定します。
=SCAN(“”, A2:A6, LAMBDA(a, v, a&v))
区切り文字を入れたい場合は、=SCAN(“”, A2:A6, LAMBDA(a, v, IF(a=””, v, a&”・”&v)))のように、最初の1つ目だけ区切り文字を付けない工夫をすると、先頭に余計な記号が付きません。
SCAN関数を使うときのコツと注意点
スピルする先のセルを空けておく
SCAN関数の結果は、元の配列と同じ数のセルに広がって表示されます。広がる先に別のデータが入力されていると、#SPILL!エラーになります。数式を入れる列は、結果が表示される範囲まで空けておきましょう。また、テーブルの中ではスピルする数式を使えないため、SCAN関数はテーブルの外のセルに入力します。
文字列が混ざるとエラーになることがある
数値の範囲に「未定」などの文字列が混ざっていると、足し算の時点で#VALUE!エラーになり、以降の結果にもエラーが続きます。文字列を0として扱ってよい場合は、=SCAN(0, B2:B13, LAMBDA(a, v, a+N(v)))のように、N関数で数値に変換してから計算すると、エラーを避けられます。
最後の値だけが必要なら
途中経過ではなく、最終的な結果だけが必要な場合は、同じ仕組みで最終結果だけを返すREDUCE関数が向いています。SCAN関数の結果から最後の値だけを取り出したいときは、TAKE関数と組み合わせて=TAKE(SCAN(0, B2:B13, LAMBDA(a, v, a+v)), -1)のように書くこともできます。
対象範囲の指定を工夫する
データが毎月増えていく表では、範囲を少し広めに指定しておくと、行を追加したときに数式を直す手間が減ります。ただし、空白のセルも計算の対象になるため、空白部分にも累計が表示されます。表示を見やすくしたい場合は、=IF(B2:B100=””, “”, SCAN(0, B2:B100, LAMBDA(a, v, a+v)))のように、IF関数で「元のセルが空白なら空白を表示する」形に包むと、データのない行は空欄のままになります。
使えない環境と共有するときは
SCAN関数に対応していないバージョンのExcelでファイルを開くと、数式が正しく計算されません。社外の人や別の部署とファイルをやり取りする場合は、相手の環境を確認するか、値として貼り付けたシートを別に用意しておくと安心です。
まとめ
SCAN関数を使うと、累計の計算や在庫数の推移など、途中経過の一覧を1つの数式で求められます。ポイントを整理します。
- 構文は=SCAN([初期値], 配列, LAMBDA(a, v, 計算式))で、中間の結果をすべて配列として返す。
- 累計は初期値0と「a+v」の組み合わせで、1つのセルに数式を入れるだけで求められる。
- 初期値や計算式を変えれば、在庫数の推移、区切りごとの累計、その時点までの最大値、文字列の連結にも応用できる。
- スピル先を空けておくこと、文字列が混ざる場合はN関数で変換することを意識する。
- 対象はExcel for Microsoft 365とExcel 2024で、それより前のバージョンでは使えない。
まずは、いま下の行へコピーして使っている累計の数式を1つ選び、SCAN関数に置き換えてみてください。