#SPILLを無効にする！エラー（Microsoft Excel）

Janの会社は最近Office365ProPlusにアップグレードしました。それに伴い、＃SPILLが登場しました！彼がLOTを使用しているVLOOKUPを使用するとエラーが発生します。彼は現在、フィルター付きのVLOOKUPを使用できません。 Janは、前のVLOOKUPからの＃N / Aを並べ替えたり削除したりするのは簡単だと知っていますが、このエラーにより、彼は多くの余分な時間と労力を費やしています。彼は、この「機能」をオフにする方法があるかどうか疑問に思います。

非常に近い過去に、Microsoftはワークシートの計算方法を変更しました。

これは大きな変更であり、他の場所で読んだことがあるかもしれません。変更に関するいくつかの優れた情報を提供する1つの記事があります：

記事はかなり長いです。そこに含まれる情報を消化するために、時間を取っておきたいと思うでしょう。ただし、Excelで多数の数式を作成する場合は、作成する必要があります。プログラムを変更すると、最終的にはそれを理解する必要があります。

基本的に、Microsoftは配列関数の概念を廃止し（引き続き機能します）、代わりにVLOOKUPを含むほとんどすべての関数が値の配列を返すことを許可しました。戻り値の配列が使用可能なスペースに収まらない場合は、新しい#SPILLを取得します。エラー。

正直なところ、答えは#SPILLを無効にすることではありません。エラー;本当にそうする方法はありません。答えは、Excelが計算するときに現在何をしているのかを理解し、それに応じて数式を変更することです。

例を見てみましょう。単純な2列のデータセットにアイテムとその価格をリストするワークシートがあるとします。次に、この右側にいくつかのアイテムを入力し、VLOOKUP関数を使用して、それらの各アイテムに関連付けられた価格を返します。このワークブックを古いバージョンのExcel（2019以前）で開くと、すばらしい結果が得られます。 （図1を参照）

図1.Excel2010の単純なVLOOKUP式。

このスクリーンショットはExcel2010システムを使用して撮影されましたが、Excel2016またはExcel2019でも同じように機能します。この例では、セルF2のVLOOKUP数式（セルF2のために数式バーに表示されます）に注意してください。が選択されている）は、F2：F8の範囲にコピーされます。 VLOOKUP関数はテーブルから単一の値を返すため、目的の結果が得られます。

次に、Office 365で提供されているバージョンのExcelで、同じ数式を使用して同じブックを作成するとどうなるかを見てみましょう。この場合、エラーが表示されます。 （図2を参照）

図2.最新バージョンのExcelの同じ単純なVLOOKUP式。

#SPILLに注意してください！エラー。このエラーは、VLOOKUP数式が複数の値を返すことができるようになったために発生します。実際、VLOOKUPの最初のパラメーターでセルの範囲を使用すると、その範囲内の各セルの値が返されるようになりました。したがって、最初のパラメーターに範囲E2：E8を使用すると、VLOOKUPは7つの値を返します。つまり、値の配列を自動的に返します。これらの値をすべて表示できない場合は、＃SPILL！を取得します。エラー。そのため、＃SPILLが表示されます。セルF2：F7のエラー。それらの下には、これらの数式で返されるすべての値が表示されないようにするものがあります。セルF8の下には表示を停止するものがないため、エラーは表示されません。

では、これをどのように修正しますか？実際に修正する方法は3つあります。

この特定の例では、最も簡単な方法は、セルF3：F8のすべてを削除することです。これにより、セルF2の数式を「こぼす」ことができます

その下の残りのセルに正しく。

2番目のアプローチは、セルF2の数式を変更することであるため、次のようになります。

最初のパラメーターの直前で@記号が使用されていることに注意してください。これは、VLOOKUP数式が単一の値のみを返すようにすることをExcelに通知します。数式を調整するもう1つの方法は、セルF2で次のように表示することです。

これで、セルF2からF2：F8の全範囲にいずれかの数式をコピーでき、問題は発生しません。どうして？これらのインスタンスの（再び）VLOOKUPは、値の配列ではなく、単一の値のみを返すためです。

結果として、数式を変更する最善の方法は、（1）

のいずれかです。 VLOOKUPに返すように要求している値の完全な配列の表示を妨げる​​ものがないことを確認するか、（2）最初のパラメーターを変更して単一のセルのみを参照するようにします。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（13750）は、Office365のMicrosoftExcelExcelに適用されます。