#РОЗГОРТАННЯ! помилка – виходить за межі аркуша

Застосовується до

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для iPad Excel Web App Excel для iPhone Excel для планшетів Android Excel для телефонів Android

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

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

Знімок екрана: #SPILL! коли =SORT(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:C,2,FALSE) раніше шукала лише ідентифікатор у клітинці A2. Однак у динамічному масиві Excel формула спричиняє #SPILL! помилку через те, що Excel шукає у цілому стовпці, повертає 1 048 576 результатів і доходить до кінця сітки Excel.

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

Щоб вирішити цю проблему, скористайтесь одним із наведених нижче підходів.

#	Підхід	Формула
1	Посилання лише на потрібні значення пошуку. Цей стиль формул повертає динамічний масив, але не працює з таблицями Excel.	=VLOOKUP(A2:A7,A:C,2,FALSE)
2	Додайте посилання лише на значення в тому самому рядку, а потім скопіюйте формулу вниз. Цей традиційний стиль формул працює в таблицях, але не повертає динамічний масив.	=VLOOKUP(A2,A:C,2,FALSE)
3	Попросіть Excel виконати неявний перетин за допомогою оператора @, а потім скопіюйте формулу вниз. Цей стиль формули працює в таблицях, але не повертає динамічний масив.	=VLOOKUP(@A:A,A:C,2,FALSE)