Spillスピルとは隣接する範囲に結果が、こぼれる Excel2021 Microsoft 365

Spillとは隣接する範囲に結果が、こぼれる Excel2021 Microsoft 365

Spillとは2019年からOffice365のExcelに実装された革新的な機能です。
現在は、Excel2021,Microsoft365で使えます。実装される以前は、数式を入力したセルにしか結果を表示できませんでした。 複数の結果を表示するためには、結果の数の分だけ数式をセルに入力する必要がありました。

Spillとは隣接する範囲に結果が、こぼれる 関数 05回

スピルって要するに何?

スピル(spill)は、数式を入力したセルから結果があふれてこぼれ、隣接したセルにも自動的に出力されるというものです。
スピルは動的配列数式とも呼ばれます。
スピルは結果の分だけ出力されますから、元になるデータ、入力された数式が変われば、スピルする範囲、行や列の大きさが変わります。
(サンプルファイルは、こちらから サンプルデータ


スピルの機能つきで追加された関数

スピル機能のある関数

関数名 説明
FILTER FILTER 関数は指定したセルの範囲や配列から条件に一致する値に絞り込みます。
SORT SORT 関数は、範囲または配列の内容を並べ替えます。
SORTBY SORTBY 関数は、範囲または配列の内容を、複数の範囲を基準として並べ替えたうえでスピル配列として並べ替えます。
UNIQUE UNIQUE 関数は、覧表または範囲内から重複データを削除した一意(ユニーク)な値の一覧を返します。
RANDARRAY RANDARRAY 関数は、0から1までのランダムな数値の乱数を生成できる関数です。スピルに対応しています。
SEQUENCE SEQUENCE 関数は、1、2、3、4など、配列内の連続した数値(連番)の一覧表を作成する関数です。
XLOOKUP XLOOKUP 関数は、VLOOKUP関数の機能をカバーする新関数です。範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。
縦横に検索することができます。スピルに対応しています。
XMATCH XMATCH 関数は、配列またはセル範囲内から一致する場所(位置)を探します。

Unique関数でスピルする

  1. スピルの例をUnique関数とXLOOKUP関数で見てみましょう。

    NAMAE、タイトル01
  2. まず、最初にUnique関数の例です。ここではE3セルに商品名を入力したいと思っています。

    NAMAE、タイトル02
  3. E3セルに=B3:B8と入力してEnterエンターキーを押します。入力したのは、E3セルだけです。

    NAMAE、タイトル03
  4. すると、E3:E8セルに商品名が表示されました。これがスピルです。

    NAMAE、タイトル04
  5. E3セル以外は、実際には数式が入力されていませんが、スピルしたことを表すために、薄いグレーの数式が表示されています。

    NAMAE、タイトル05

数式をFomulatext関数で確認すると

  1. 数式が実際に入力されていないことは、Fomulatext関数で確認することができます。

    NAMAE、タイトル06
  2. 数式をコピーしましたが、E3セル以外には数式が入力されていませんでした。

    NAMAE、タイトル07
  3. 今度は対象範囲から、Unique関数で重複しない一意(ユニーク)な値を取り出し、リスト化してみます。
    数式は=UNIQUE(B3:B14)、E3セルにだけ入力します。

    NAMAE、タイトル08
  4. E列に商品名のリストができました。

    NAMAE、タイトル09
  5. E3セル以外は数式がグレーアウトしています。

    NAMAE、タイトル10

スピルを含むという条件#

  1. F列に売り上げ個数の各合計をだしたいのですが、その前に、データ部分をテーブルにしましょう。

    Spillとは隣接する範囲に結果が、こぼれる、タイトル01
  2. F3セルに数式を入力します。となりのE3と同じならば合計するというSUMIFS関数を使います。
    合計対象範囲は、テーブルのC列部分です。
    範囲を指定すると、テーブル1[売上]と名前が入ります。

    Spillとは隣接する範囲に結果が、こぼれる、タイトル02
  3. そして条件範囲は隣のB列で範囲を指定すると、テーブル1[商品名]と名前が入ります。
    =SUMIFS(テーブル1[売上],テーブル1[商品名],条件)です。

    Spillとは隣接する範囲に結果が、こぼれる、タイトル03
  4. 条件はE3セルなので、そのままE3と指定してやりますが、ここではスピルを含むという条件をつけてやります。
    E3#

    Spillとは隣接する範囲に結果が、こぼれる、タイトル04
  5. F3セルにSUMIFSの数式を入力し、Enterすると、結果がF3以外のセルにもこぼれてあふれ出しました。各商品の売上個数が集計されました。

    Spillとは隣接する範囲に結果が、こぼれる、タイトル05
  6. 他のセルには、グレーアウトしたゴーストの数式が表示されています。

    Spillとは隣接する範囲に結果が、こぼれる、タイトル06