一連の値を返す Excel の数式 (配列とも呼ばれる) は、隣接するセルにこれらの値を返します。 この動作はスピルと呼ばれます。
可変サイズの配列を返すことができる数式は、動的配列数式と呼ばれます。 現在、正常にスピルした配列を返す数式は、スピル済み配列数式と呼ぶことができます。
以下は、このような数式を理解し、利用するための注意点です。
スピルとはどういう意味ですか。
注: レガシ配列数式と呼ばれる以前の配列数式は、常に固定サイズの結果を返します。つまり、常に同じ数のセルにスピルします。 このトピックで説明するスピルの動作は、レガシ配列数式には適用されません。
スピルとは、数式によって複数の値が生成され、その値が隣のセルに配置されることです。 たとえば、=SORT(D2:D11,1,-1), で配列を降順に並べ替えると、対応する 10 行の高さの配列を返します。 ただし、左上のセル、この場合は F2 に数式を入力するだけで、自動的にセル F11 までスピルしていきます。
重要な点
-
Enter キーを押して数式を確定すると、Excel は出力範囲のサイズを動的に調整し、その範囲内の各セルに結果を配置します。
-
データの一覧に作用する動的な配列数式を記述する場合、それを Excel テーブルに配置し、構造化された参照を使用してデータを参照すると便利なことがあります。 これは、構造化された参照が、テーブルの行の追加や削除に応じて自動的に調整されるためです。
-
スピル済みの配列数式は、Excel の表自体には対応していないため、表の外のグリッドに配置する必要があります。 表は、独立したデータの行と列を保持するのに最も適しています。
-
スピル済み配列数式を入力すると、スピル領域内の任意のセルを選択した場合に、Excel は範囲を囲む強調表示された境界線を配置します。 領域外のセルを選択すると、境界線は消えます。
-
スピル領域の最初のセルだけが編集可能です。 スピル領域で他のセルを選択すると、数式は数式バーに表示されますが、テキストは「ゴースト化」され、変更できません。 数式を更新する必要がある場合は、配列範囲の左上のセルを選択して、必要に応じて変更し、Enter を押すと、Excel が自動的に残りのスピル領域を更新します。
-
数式の重複 - 出力範囲が何らかの原因でブロックされている場合、配列数式を入力することはできません。 また、このような場合は、ブロックされていることを示す #SPILL! エラーが返されます。 ブロックを取り除くと、数式は正常にスピルされます。 以下の例では、数式の出力範囲がデータを含む別の範囲と重複し、スピルしないことを示す値を含むセルと重複する点線の境界線で表示されます。 ブロック データを削除するか、別の場所にコピーすることで、数式は期待どおりにスピルします。
-
CTRL+SHIFT+ENTER (CSE) を介して入力されたレガシ配列数式は、下位互換の理由で引き続きサポートされていますが、今後は使用しないでください。 希望する場合は、配列範囲の最初のセルを検索して数式のテキストをコピーし、レガシ配列の範囲全体を削除してから、左上のセルに数式を再入力することで、レガシ配列数式を動的配列数式に変換できます。 レガシ配列数式を動的配列式にアップグレードする前に、両者の計算の違いについて認識しておく必要があります。
-
Excel では、ブック間の動的配列のサポートが制限されており、このシナリオは双方のブックが開いているときにのみサポートされます。 元のブックが閉じられている場合、リンク済みの動的配列数式は更新されるとエラー値 #REF! を返します。
補足説明
Excel Tech Communityで、いつでも専門家に質問できます。また、コミュニティでは、サポートを受けられます。
