Помилка #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! помилка, оскільки діапазон ІНДЕКСУ становить 4 рядки на 4 стовпці, але формула має відповідь на запитання про те, що в п'ятому рядку та 5-й стовпець.
Вирішення
Змініть посилання на рядки й стовпці так, щоб вони потрапляли в діапазон пошуку функції INDEX. Формула =INDEX(B2:E5,4,4) поверне правильний результат.
Приклад посилання на закриту книгу з використанням функції INDIRECT
У наведеному нижче прикладі функція "НЕПРЯМА" намагається посилатися на книгу, яка не закриється, і спричиняє #REF! помилку #REF!.
Вирішення
Відкрийте книгу, на яку посилається ця книга. Ви зустрінете таку саму помилку, якщо ви посилаються на закриту книгу за допомогою динамічної функції масиву.
Проблеми з OLE
Якщо ви використовували посилання для зв'язування та вбудовування об'єктів (OLE), які повертаються #REF! повідомлення про помилку, а потім запустіть програму, за допомогою якої ви телефонуєте.
Примітка. OLE – це технологія обміну інформацією між програмами.
Проблеми з DDE
Якщо ви використовували тему динамічного обміну даними (DDE), який повертається #REF! помилка, спочатку установіть прапорець, щоб переконатися, що ви посилаєтеся на коректну тему. Якщо ви все ще отримуєте #REF! помилка, перевірте Настройки центру безпеки та конфіденційності для зовнішнього вмісту, як зазначено в блокуєте або розблокуйте зовнішній вміст у документах Office.
Примітка: динамічний обмін даними (DDE)– це створений протокол для обміну даними між програмами Microsoft Windows.
Проблеми з макросами
Якщо макрос вводить функцію на аркуші, що посилається на одну з наведених вище функцій, а в області, яка містить функцію, міститься в рядку 1, функція повернеться #REF! тому що немає клітинок над рядком 1. Перевірте функцію, щоб дізнатися, чи аргумент посилається на клітинку або діапазон клітинок, які неприпустимі. Це може вимагати редагування макросу в редакторі Visual Basic (VBE), щоб прийняти цю ситуацію в обліковий запис.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.
