Помилка #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(A8;A2:D5;5;FALSE) поверне #REF! тому що шукає значення, яке повертається зі стовпця 5, але діапазон посилань – A:D, який містить лише 4 стовпці.
Вирішення
Відрегулюйте діапазон, щоб він був більшим або зменшував значення підстановки стовпців відповідно до діапазону посилань. Формула =VLOOKUP(A8,A2:E5,5,FALSE) працюватиме правильно, так само як і формула =VLOOKUP(A8,A2:D5,4,FALSE).
У цьому прикладі формула =INDEX(B2:E5,5;5) повертає #REF! оскільки діапазон INDEX становить 4 рядки на 4 стовпці, але формула просить повернути те, що міститься в 5-му рядку та 5-му стовпці.
Вирішення
Змініть посилання на рядки й стовпці так, щоб вони потрапляли в діапазон пошуку функції INDEX. Формула =INDEX(B2:E5,4,4) поверне правильний результат.
У наведеному нижче прикладі функція INDIRECT намагається створити посилання на закриту книгу, що спричиняє #REF! помилку #REF!.
Вирішення
Відкрийте книгу, на яка посилається посилання. Така ж помилка виникає, якщо створити посилання на закриту книгу за допомогою функції динамічного масиву.
Структуровані посилання на імена таблиць і стовпців у зв'язаних книгах не підтримуються.
Обчислювані посилання на зв'язані книги не підтримуються.
Переміщення або видалення клітинок спричинило неприпустиме посилання на клітинку або функція повертає помилку посилання.
Якщо використовується зв'язування та вбудовування об'єктів (OLE), яке повертає #REF! а потім запустіть програму, яка викликає посилання.
Примітка. OLE – це технологія обміну інформацією між програмами.
Якщо використовується розділ динамічного обміну даними (DDE), який повертає #REF! спочатку переконайтеся, що ви посилаєтеся на правильну тему. Якщо ви все ще отримуєте #REF! перевірте параметри Центру безпеки та конфіденційності на наявність зовнішнього вмісту, як описано в статті Блокування або розблокування зовнішнього вмісту в документах Microsoft 365.
Примітка.Динамічний обмін даними (DDE)– це усталений протокол для обміну даними між програмами на основі Microsoft Windows.
Проблеми з макросами
Якщо макрос введе на аркуш функцію, яка посилається на клітинку над функцією, а клітинка з функцією міститься в рядку 1, функція поверне #REF! оскільки над рядком 1 немає клітинок. Перевірте, чи аргумент посилається на неприпустиму клітинку або діапазон клітинок. Для цього може знадобитися відредагувати макрос у Редактор Visual Basic (VBE), щоб врахувати цю ситуацію.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
