Excelでスピル範囲を使用する方法 - TechRepublic

Excelでスピル範囲を使用する方法 - TechRepublic

画像: PixieMe/Shutterstock

ここ数ヶ月、Excelの新しい動的配列関数についていくつか記事を書いてきました。多くの場合、これらの関数は従来の複雑な式を置き換えることができます。新しい関数はすべての計算を内部で行い、結果の値の範囲を返します。この結果の値の範囲は「スピル範囲」と呼ばれます。これらの動的配列関数を使用するには、「スピル範囲」について理解しておく必要がありますので、このまま読み進めてください。

参照: すべてのユーザーが習得すべき Excel のヒント 83 選 (TechRepublic)

Windows 10 64ビットシステムでMicrosoft 365を使用しています。これらの新しい動的配列関数は、Microsoft 365、Excel for the Web、Androidタブレットおよびスマートフォン向けExcelでのみご利用いただけます。デモファイルはありませんので、必要ありません。

まずは流出範囲を明確に定義することから始めましょう

動的配列関数または式が複数の結果値を生成する場合、それらの値は隣接するセルにスピルオーバーされます。つまり、スピルオーバー範囲とは、動的配列式または関数によって返される値の配列です。重要なのは、これらは結果値に過ぎず、式または関数を含むセルは、それを入力したセルだけであるということです。左上のセルには式または関数が含まれ、スピルオーバー範囲の残りのセルには結果値のみが表示されます。つまり、式または関数を変更する際に変更する必要があるセルは1つだけですが、それに応じてすべての結果値が更新されます。幸いなことに、これは一見複雑に思えるほど複雑ではありません。

参照:Windows 11:インストール、セキュリティなどに関するヒント(無料PDF)(TechRepublic)

スピル範囲内の任意のセルをクリックすると、Excelはスピル範囲全体を青い枠で囲みます。この青い枠線内のすべてが結果値です。図Aは、H5のUNIQUE()動的関数の小さなスピル範囲を示しています。この動的関数は、Region列から一意の値のリストを返します。

=UNIQUE(売上[地域],FALSE)

スクリーンショットでは、境界線が細いため、青色であることが分かりにくいです。現在、スピル範囲はH5:H7です。また、数式バーには構造化されたテーブル参照ではなく、明示的な範囲が表示されています。これは、両方の使用例を示すためです。どちらも正常に動作します。

図A

ソースデータはTableオブジェクトであるため、H5の動的関数はTable内のソースデータを更新すると更新されます。例えば、新しい地域を含む新しいレコードを追加すると、図Bに示すように、H5から始まるリストもそれに応じて更新されます。ソースデータをTableに保存しておらず、元の範囲(この場合はRegion列)外の値を入力した場合、UNIQUE()関数のスピル範囲は更新されません。H5の動的関数を削除すると、スピル範囲は完全に消えます。

図B

これまでのところ、結果値を入れるための十分なスペースがありました。スペースが足りない場合はどうなるでしょうか?

#SPILL! エラーとは何ですか?

前のセクションは簡単でした。エラーも問題もありませんでした。しかし、常にそうとは限りません。図Cはスピル範囲エラー「#SPILL!」を示しています。アンカーセルE5は変更されておらず、関数も全く同じです。ただし、先ほど表示されていた結果値の代わりに、エラーが表示されます。

図C

H7の「test」という値が邪魔になっているとお考えなら、その通りです!このエラーが表示される場合、ほとんどの場合、元の計算を実行するためのセル数が不足していることが問題です。動的関数がすべての結果値を返せない場合は、#SPILL! エラーが返されます。

解決策は簡単です。スピル範囲内の問題のあるデータを削除するか、元の動的関数を、計算結果の値をすべて入力できる十分なスペースがあるセルに移動してください。

問題が明らかな場合は、解決は簡単です。図Cの例のように問題が明らかでなくても、諦めないでください。エラータグで詳細情報を確認してください。エラータグとは、元の式または関数を選択したときに表示される感嘆符のことです(図C )。スマートタグをクリックするだけで、図Dに示すエラーリストが表示されます。

図D

起こりうるエラーを確認しましょう:

  • 「スピル範囲が空白ではありません」はおそらく最も一般的なエラーです。これは図Cの問題です。この例では明らかですが、問題となるセルが画面外にある場合はどうなるでしょうか?問題となる値が見つからない場合は、「ブロックしているセルを選択」を選択してください。すると、結果値をブロックしているセルが表示されます。この時点で、値を削除するか、動的関数を移動するかを決めることができます。
  • 「このエラーに関するヘルプ」では、これらのエラー メッセージの意味の一覧を含むヘルプ ファイルを開きます。
  • 「妨げているセルを選択」は、スピル範囲内で結果の値を遮っているセルを選択します。スピル範囲に結合セルが収まらない場合は、結合セルも選択対象になる場合があります。
  • [計算ステップの表示] では、 Visual Basic エディターで VBA コードをステップ実行する場合と同様に、計算をステップ実行できる [数式の評価] ダイアログが表示されます。
  • 「エラーを無視」を使用すると、スマートタグをクリアしてエラーを無視して作業を続行できます。特別な理由がない限り、このオプションの使用はお勧めしません。
  • 数式バーで編集すると、数式バー内の実際の式または関数にフォーカスが当てられ、編集できるようになります。
  • エラー チェック オプションを選択するとオプション ダイアログが開き、これらのオプションを確認したり、必要に応じて変更したりできるようになります。

おそらく最も一般的なエラーである今回のケースでは、最初の項目「スピル範囲が空白ではありません」が直接的で情報提供に役立ちます。問題のあるセルが見つからない場合は、「ブロックされているセルを選択」を選択してください。他のエラーに遭遇することはあまりないでしょう。

さらに、Tableオブジェクトは動的配列式や関数をサポートしていません。これらの式や関数をTableオブジェクトに入力しないでください。ソースデータはTableに格納できますし、格納するべきですが、関数そのものをTableに入力しないでください。

Excel に組み込まれている動的配列関数は、スピル範囲とそのニーズへの対応方法さえわかっていれば、使いやすく、保守も簡単です。

これらの新しい動的配列関数の詳細については、次の記事を参照してください。

  • Excelスプレッドシートで並べ替えられた一意のリストを作成する方法
  • ExcelでFILTER()動的配列関数を使用する方法
  • Excelで新しいXLOOKUP()動的配列関数を使う方法
  • Microsoft 365 の動的配列関数 SEQUENCE() を使用して Excel で日付のリストを作成する方法
  • ExcelのRANDARRAY()関数を使ってランダムなテキストを生成する方法 – TechRepublic
  • Excelでフィルターやピボットテーブルを使わずに上位または下位のnレコードを返す方法
Tagged: