Помилка #REF! указує на те, що формула містить неприпустиме посилання на клітинку. Це часто буває, коли формула посилається на клітинки, вміст яких видалено або замінено іншими даними.
Приклад помилки #REF! через видалення стовпця
У наступному прикладі в стовпці E використовується формула =SUM(B2,C2,D2).
Видалення стовпця B, C або D призведе до #REF! помилку #REF!. У цьому випадку ми видалимо стовпець C (продажі за 2007 р.), а формула отримає формулу =SUM(B2;#REF!;C2). Якщо використовуються явні посилання на клітинки, як це пояснено нижче (де посилання на кожну клітинку окремо відокремлено комою) і видалено рядок або стовпець, на який посилається формула, функція Excel не розпізнає їх, тому повертається #REF! помилку #REF!. Це основна причина, чому не рекомендовано використовувати явні посилання на клітинки у функціях.
Вирішення
-
Якщо ви випадково видалили рядки або клітинки, негайно натисніть кнопку "Скасувати" на панелі швидкого доступу (або клавіші Ctrl+Z), щоб відновити їх.
-
Змініть формулу так, щоб вона посилалася на діапазон, а не на окремі клітинки, наприклад =SUM(B2:D2). Тепер можна видалити будь-який стовпець у діапазоні суми, і Excel автоматично виправить формулу. Щоб обчислити суму значень у рядках, також можна використовувати формулу =SUM(B2:B5).
Приклад функції VLOOKUP із неправильними посиланнями на діапазони
У наведеному нижче прикладі формула =VLOOKUP(A8;A2:D5;5;FALSE) поверне #REF! оскільки вона шукає значення, яке потрібно повернути зі стовпця 5, але діапазон посилань – це A:D, що містить лише 4 стовпці.
Вирішення
Змініть діапазон або зменшіть значення стовпця для пошуку так, щоб він попадав у вказаний діапазон. Формула =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,4,4) поверне правильний результат.
Приклад посилання на закриту книгу з використанням функції INDIRECT
У наведеному нижче прикладі функція INDIRECT намагається створити посилання на закриту книгу, що викликає #REF! помилку #REF!.
Вирішення
Відкрийте книгу, на яку посилається посилання. Виникне така сама помилка, якщо ви посилаєтеся на закриту книгу з функцією динамічного масиву.
Проблеми OLE
Якщо ви використали посилання для зв'язування та вбудовування об'єктів (OLE), яке повертає #REF! , а потім запустіть програму, яка викликає посилання.
Примітка. OLE – це технологія обміну інформацією між програмами.
Проблеми З DDE
Якщо ви використовували розділ динамічних даних Exchange (DDE), який повертає #REF! перевірте, чи правильно ви посилаєтеся на потрібний розділ. Якщо ви все ще отримуєте #REF! перегляньте відомості про зовнішній Настройки в Центрі безпеки та конфіденційності, як показано в розділі Блокування та розблокування зовнішнього вмісту Office документах.
Примітка. Динамічні Exchange даних (DDE)– це протокол, установлений для аналізу даних між програмами на Windows Microsoft.
Проблеми з макросами
Якщо макрос вводить на аркуші функцію, яка посилається на клітинку над функцією, а клітинка з функцією міститься в рядку 1, функція поверне #REF! оскільки над рядком 1 немає клітинок. Перевірте, чи є аргумент функції посилання на клітинку або діапазон клітинок, які неприпустимі. Можливо, знадобитися відредагувати макрос у Visual Basic редакторі VBE, щоб врахувати цю ситуацію.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.
