Виправлення помилки #REF! помилка

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

Приклад помилки #REF! через видалення стовпця

У наступному прикладі в стовпці E використовується формула =SUM(B2,C2,D2).

Формула з явним посиланням на клітинки, наприклад =SUM(B2;C2;D2), може спричинити #REF! у разі видалення стовпця.

Видалення стовпця B, C або D призведе до #REF! помилку #REF!. У цьому випадку ми видалимо стовпець C (продажі за 2007 р.), а формула отримає формулу =SUM(B2;#REF!;C2). Якщо використовуються явні посилання на клітинки, як це пояснено нижче (де посилання на кожну клітинку окремо відокремлено комою) і видалено рядок або стовпець, на який посилається формула, функція Excel не розпізнає їх, тому повертається #REF! помилку #REF!. Це основна причина, чому не рекомендовано використовувати явні посилання на клітинки у функціях.

Приклад помилки #REF! через видалення стовпця.

Вирішення

  • Якщо ви випадково видалили рядки або клітинки, негайно натисніть кнопку "Скасувати" на панелі швидкого доступу (або клавіші Ctrl+Z), щоб відновити їх.

  • Змініть формулу так, щоб вона посилалася на діапазон, а не на окремі клітинки, наприклад =SUM(B2:D2). Тепер можна видалити будь-який стовпець у діапазоні суми, і Excel автоматично виправить формулу. Щоб обчислити суму значень у рядках, також можна використовувати формулу =SUM(B2:B5).

Приклад функції VLOOKUP із неправильними посиланнями на діапазони

У наведеному нижче прикладі формула =VLOOKUP(A8;A2:D5;5;FALSE) поверне #REF! оскільки вона шукає значення, яке потрібно повернути зі стовпця 5, але діапазон посилань – це A:D, що містить лише 4 стовпці.

Приклад формули VLOOKUP із неправильним діапазоном.  Формула має таку формулу: =VLOOKU(A8;A2:D5;5;FALSE).  П'ятого стовпця в діапазоні VLOOKUP немає, тому 5 спричиняє #REF! помилку #REF!.

Вирішення

Змініть діапазон або зменшіть значення стовпця для пошуку так, щоб він попадав у вказаний діапазон. Формула =VLOOKUP(A8,A2:E5,5,FALSE) працюватиме правильно, так само як і формула =VLOOKUP(A8,A2:D5,4,FALSE).

Приклад функції INDEX із неправильним посиланням на рядок або клітинку

У цьому прикладі формула =INDEX(B2:E5,5,5) повертає #REF! через те, що діапазон INDEX – це 4 рядки та 4 стовпці, але формула просить повернути значення в 5-му рядку та 5-му стовпці.

Приклад формули INDEX із неправильним посиланням на діапазон.  Формула має вигляд =INDEX(B2:E5,5,5), але діапазон містить лише 4 рядки й 4 стовпці.

Вирішення

Змініть посилання на рядки й стовпці так, щоб вони потрапляли в діапазон пошуку функції INDEX. Формула =INDEX(B2:E5,4,4) поверне правильний результат.

Приклад посилання на закриту книгу з використанням функції INDIRECT

У наведеному нижче прикладі функція INDIRECT намагається створити посилання на закриту книгу, що викликає #REF! помилку #REF!.

Приклад помилки #REF! через посилання на закриту книгу з використанням функції INDIRECT.

Вирішення

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

Проблеми OLE

Якщо ви використали посилання для зв'язування та вбудовування об'єктів (OLE), яке повертає #REF! , а потім запустіть програму, яка викликає посилання.

Примітка. OLE – це технологія обміну інформацією між програмами.

Проблеми З DDE

Якщо ви використовували розділ динамічних даних Exchange (DDE), який повертає #REF! перевірте, чи правильно ви посилаєтеся на потрібний розділ. Якщо ви все ще отримуєте #REF! перегляньте відомості про зовнішній Настройки в Центрі безпеки та конфіденційності, як показано в розділі Блокування та розблокування зовнішнього вмісту Office документах.

Примітка. Динамічні Exchange даних (DDE)– це протокол, установлений для аналізу даних між програмами на Windows Microsoft.

Проблеми з макросами

Якщо макрос вводить на аркуші функцію, яка посилається на клітинку над функцією, а клітинка з функцією міститься в рядку 1, функція поверне #REF! оскільки над рядком 1 немає клітинок. Перевірте, чи є аргумент функції посилання на клітинку або діапазон клітинок, які неприпустимі. Можливо, знадобитися відредагувати макрос у Visual Basic редакторі VBE, щоб врахувати цю ситуацію.

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

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

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

Огляд формул в Excel

Способи уникнення недійсних формул

Виявлення помилок у формулах

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

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

Отримуйте нові функції раніше за інших
Приєднатися до оцінювачів Microsoft Office

Ця інформація корисна?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

Дякуємо за відгук!

×