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

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

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

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

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

Якщо у формулі використовуються явні посилання на клітинки, наприклад =SUM(B2,C2,D2), то видалення стовпця може викликати помилку #REF!.

Якщо видалити стовпець B, C або D, виникне помилка #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 із неправильним діапазоном.  Формула має вигляд =VLOOKU(A8,A2:D5,5,FALSE).  П’ятого стовпця в діапазоні VLOOKUP немає, тому значення 5 викликає помилку.

Вирішення

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

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

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

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

Вирішення

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

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

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

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

Вирішення

Відкрийте книгу, на яку вказує посилання.

Проблеми з OLE й DDE

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

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

Якщо ви використовуєте розділ DDE, який повертає помилку #REF!, переконайтеся, що ви посилаєтеся на правильний розділ.

Примітка DDE – це протокол, прийнятий для обміну даними між програмами на основі Microsoft Windows.

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

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

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

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

Див. також

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

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

Виправлення поширених помилок у формулах за допомогою функції перевірки помилок

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

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×