Помилка #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
Якщо використовується розділ динамічного обміну даними (DDE), який повертає #REF! спочатку переконайтеся, що ви посилаєтеся на правильну тему. Якщо ви все ще отримуєте #REF! перевірте параметри Центру безпеки та конфіденційності на наявність зовнішнього вмісту, як описано в статті Блокування або розблокування зовнішнього вмісту в документах Microsoft 365.
Примітка.Динамічний обмін даними (DDE) – це усталений протокол для обміну даними між програмами на основі Microsoft Windows.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.