Excelの動的配列数式は、データ処理の効率を大幅に向上させる革新的な新機能です。Office 2021以降で使用が可能です。動的配列数式は一つの数式から複数の結果を自動的に隣接するセルに展開します。展開した計算結果を「スピル配列」とよびます。「スピル(spill)」は「こぼす、あふれさせる」というイメージです。
動的配列数式とスピル配列のイメージ
例えば以下は通常の数式です。結果は1つのセルにしか表示されません。
=B2*C2
以下は動的配列数式の例です。四則計算で「:」を使って範囲、配列参照をさせると結果が複数のセルへ展開(スピル)し、「スピル配列」を生成します。例では7セル同士で計算をさせているため、7セル分のスピル配列が作成されます。
=B2:B8*C2:C8
セルD2へ動的配列数式を入力した例。スピル配列がD2:D8へ生成された。
この記事では、動的配列数式を用いてスピル配列を生成する基本的な手順とその性質、効用について、四則計算を例にして解説します。
スピル配列の作成手順
ここでは関数を用いずに、四則計算のみでスピル配列を生成する方法を紹介します。スピル配列を生成するスピル配列関数についてはリンク先の別記事を参照してください。
作成手順
- 基本の数式の入力:
- まず、計算を開始する先頭のセルに基本の数式を入力します。「:」を使って単一セルではなく配列を指定すれば、その分だけのスピル配列が生成されます。単一セルを指定した場合は絶対参照として扱われます。
- 例えば、
=B2:B8*C2:C8*F1のように、複数の範囲を掛け合わせた数式を使用します。この例では配列7セル×配列7セル×単一セルです。スピル配列は7セル作成されます。 - セルの参照はドラッグで可能です。
- 数式の確定:
- 数式を入力したら、Enterキーを押して確定します。特別なキー操作（例：CSE）は必要ありません。
- 自動スピル:
- 数式が確定されると、計算結果は自動的に隣接する空いているセルに「スピル」され、結果が展開されます。
サンプル図
=B2:B8*C2:C8*F1
確定前
確定後
式の効果
今回は
配列(A)7セル×配列(B)7セル×単一セル(C)
という計算式を設定しました。配列が7セル同士なので7セル分のスピル配列ができました。
配列(A)1セル目 × 配列(B)1セル目 × 単一セル(C)
配列(A)2セル目 × 配列(B)2セル目 × 単一セル(C)
配列(A)3セル目 × 配列(B)3セル目 × 単一セル(C)
配列(A)4セル目 × 配列(B)4セル目 × 単一セル(C)
配列(A)5セル目 × 配列(B)5セル目 × 単一セル(C)
配列(A)6セル目 × 配列(B)6セル目 × 単一セル(C)
配列(A)7セル目 × 配列(B)7セル目 × 単一セル(C)
スピル配列の性質
- エラー処理: スピル範囲の生成予定範囲内にデータが既に存在する場合、スピルエラー（
#スピル!）が表示されます。
D5にデータがあるため、スピル配列の生成が中断している
- 動的な範囲調整: 数式が参照する範囲が変更されると、スピルされる配列のサイズも自動的に調整されます。次のセクションで解説します。
参照セルの拡張：動的な範囲調整
動的配列数式では、参照するセル範囲が拡張されることに自動的に対応します。これは、以下のような要素において特に有用です。
- 名前付き範囲:
- 名前付き範囲を参照する動的配列数式の場合、名前付き範囲のサイズが変更されると、配列数式の計算範囲も自動的に更新されます。
- データの追加や削除に柔軟に対応できるため、データ管理が容易になります。
各列に名前を付けた。名前の対象範囲を拡張すればスピル配列も拡張される。
- スピル配列:
- 他の動的配列数式の結果（スピル配列）を参照する場合、その配列のサイズが変わると参照する数式の結果も自動的に更新されます。
- スピル配列全体の参照は「スピル範囲演算子(#)」を用います。詳しくはリンク先を参照してください。
- これにより、依存関係のあるデータの変更がスムーズに行われます。
D2から生成されたスピル配列の全体を参照するには「D2#」と指定する。
削除と移動と編集
動的配列数式の扱いにおいて、削除、移動、編集は以下のように行います。
- 削除:
- 数式が入力された「先頭セル」を選択し、Deleteキーを押すことで、スピルされた全ての結果を含めて数式を削除できます。先頭セル以外ではDeleteキーの効果がありません。
- 移動:
- 数式が入力された「先頭セル」を選択し、切り取り・貼り付けを使って移動します。
- スピルされた範囲も一緒に移動され、新しい位置に応じて自動的に調整されます。
- 編集:
- 数式が入力された「先頭セル」を選択し、必要な変更を加えます。
- 編集後、Enterキーを押すと、変更がスピルされた範囲全体に反映されます。
まとめ
動的配列数式を使用することで、データ分析やレポート作成の作業が大幅に簡素化されます。一つの数式で複数のセルにまたがる計算を行い、その結果を自動的に展開する機能は、Excelでの作業効率を飛躍的に高めるものです。
コメント