Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Вебпрограма Excel Excel для iPad Excel Web App Excel для iPhone Excel для планшетів Android Excel для телефонів Android

Формула, яку ви намагаєтеся перейти, буде виходити за межі діапазону аркуша. Повторіть спробу з меншим діапазоном або масивом.

У наведеному нижче прикладі, щоб перемістити формулу до кнопки «стільниковий F1», буде вирішено помилку, а формула буде належним чином проллється.

Помилки #SPILL! помилка, коли = СОРТУВАННЯ (D:D) у клітинках F2 буде виходити за межі книги. Переміщайте його до стільникової формули F1, і вона працюватиме належним чином.

Поширені причини: "повні посилання на стовпці"

Щоб створити формули VLOOKUP , можна часто неправильно визначити значення аргументу " lookup_value ". Перш ніж використовувати динамічний масив Excel, Excel матиме значення лише в тому самому рядку, що й у формулі, і ігнорувати будь-які інші, оскільки ФУНКЦІЯ VLOOKUP очікувала лише одне значення. Завдяки впровадженню динамічних масивів Excel враховує всі значення, надані lookup_value. Це означає, що якщо аргумент lookup_value має весь стовпець, то програма Excel спробує виконати пошук усіх значень 1 048 576 у стовпці. Після цього вона намагатиметься пролити їх на сітку, і, швидше за все, вдарить по закінченні сітки в результаті чого #SPILL! помилку #REF!.  

Наприклад, якщо помістити в клітинці E2, як у прикладі нижче, формула = VLOOKUP (A:A, A:A, 2, false) раніше тільки буде шукати ідентифікатор у клітинці a2. Однак у динамічному масиві Excel формули призведуть до #SPILL! помилка, оскільки програма Excel піддає весь стовпець, повертають 1 048 576 результати та вдарить по закінченні сітки Excel.

Помилки #SPILL! помилка, пов'язана з = VLOOKUP (A:A, A:A, 2, FALSE) у клітинках E2, тому що результати буде проллється за межі аркуша. Переміщення формули на клітинну E1, і вона працюватиме належним чином.

Щоб вирішити цю проблему, виконайте три прості способи.

#

Підхід

Формула

1

Посилання лише на значення підстановки, які вас цікавлять. Цей стиль формули поверне динамічний масив,але не працює з таблицями Excel

Використовуйте = VLOOKUP (a2: A7, A:C, 2, FALSE), щоб повернути динамічний масив, який не призведе до #SPILL! помилку #REF!.

= VLOOKUP (a2: A7; a:c, 2, false)

2

Посилання лише на значення в тому самому рядку, а потім скопіюйте формулу вниз. Цей традиційний стиль формули працює в таблицях, але не поверне динамічний масив.

Використовуйте традиційну VLOOKUP з одним lookup_value посиланням: = VLOOKUP (a2, A:C, 32, FALSE). Ця формула не поверне динамічний масив, але можна використовувати в таблицях Excel.

= VLOOKUP (a2; a:c; 2; false)

3

Запит про те, що програма Excel виконує неявне перехрестя за допомогою оператора @, а потім скопіюйте формулу вниз. Цей стиль формули працює в таблицях, але не повертає динамічний масив.

Використовуйте оператор @ і скопіюйте його: = VLOOKUP (@A: A, A:C, 2, FALSE). Цей стиль посилання працюватиме в таблицях, але не поверне динамічний масив.

= VLOOKUP (@A: A, a:c, 2, false)

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Додаткові відомості

Функція FILTER

Функція RANDARRAY

Функція SEQUENCE

Функція SORT

Функція SORTBY

Функція UNIQUE

Помилки #SPILL! в Excel

Поведінка розгорнутих і динамічних масивів

Оператор "неявний перетин": @

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.