Резюме
#REF! помилка виникає, коли посилання недійсне. У багатьох випадках це відбувається тому, що аркуші, рядки або стовпці були видалені або тому, що формула з відносними посиланнями скопійована в нове місце, де посилання недійсні. Виправлення помилки #REF - це питання редагування формули для повторного використання дійсних посилань. Дивіться нижче для отримання додаткових прикладів та інформації.
Пояснення
Про #REF! помилка
#REF! помилка виникає, коли посилання недійсне. У багатьох випадках це відбувається тому, що аркуші, рядки або стовпці були видалені або тому, що формула з відносними посиланнями скопійована в нове місце, де посилання недійсні. У наведеному прикладі формула в C10 повертає #REF! помилка при копіюванні в комірку E5:
=SUM(C5:C9) // original C10 =SUM(#REF) // when copied to E5
Запобігання помилкам #REF
Найкращий спосіб запобігти #REF! помилки полягає у тому, щоб у першу чергу не відбуватися. Перш ніж видаляти стовпці, рядки чи аркуші, переконайтеся, що на них не посилаються формули у книзі. Якщо ви копіюєте та вставляєте формулу в нове розташування, можливо, вам доведеться перетворити деякі посилання на комірки в абсолютні посилання, щоб запобігти змінам під час операції копіювання.
Якщо ви викликаєте #REF! помилку, найкраще виправити негайно. Наприклад, якщо ви видалите стовпець, і #REF! з'являються помилки, скасуйте цю дію (можна скористатися комбінацією клавіш Control + Z). Коли ви скасуєте, #REF! помилки зникнуть. Потім відредагуйте формулу (и), щоб виключити стовпець, який потрібно видалити, переміщуючи дані, якщо потрібно. Нарешті, видаліть стовпець і переконайтеся, що немає #REF! помилки.
Очищення декількох #REF! помилки
Щоб швидко видалити з робочого аркуша багато помилок #REF, можна скористатися функцією "Знайти та замінити". За допомогою комбінації клавіш Control + H відкрийте діалогове вікно пошуку та заміни. Введіть #REF! в області пошуку введення і залиште поле заміни вводу порожнім:
Потім ви можете вносити зміни в кожному конкретному випадку за допомогою Знайти далі + Замінити або використовувати Замінити все, щоб замінити всі помилки #REF одним кроком.
Виправлення помилок #REF
Помилки #REF виправити дещо складно, оскільки вихідне посилання на клітинку назавжди зникло. Якщо ви знаєте, яким має бути посилання, ви можете просто виправити це вручну. Просто відредагуйте формулу та замініть #REF! з дійсним посиланням. Якщо ви не знаєте, яким має бути посилання на клітинку, можливо, доведеться детальніше вивчити аркуш, перш ніж ви зможете відновити формулу.
Примітка. Ви не можете скасувати видалення аркуша в Excel. Якщо ви видалите вкладку робочого аркуша та побачите помилки #REF, найкращим варіантом є закриття файлу та повторне відкриття останньої збереженої версії. З цієї причини завжди зберігайте книгу (або збережіть копію) перед тим, як видалити один або кілька аркушів.
#REF! помилки з VLOOKUP
Ви можете побачити #REF! помилка функції VLOOKUP, коли стовпець вказано неправильно. На екрані нижче VLOOKUP повертає #REF! оскільки в діапазоні таблиці немає стовпця 3, тобто B3: C7:
Коли для індексу стовпця встановлено правильне значення 2, #REF! помилка вирішена і VLOOKUP працює належним чином:
Примітка: Ви також можете побачити помилку #REF із функцією INDEX, коли посилання на рядок або стовпець недійсне.
Захоплення #REF! помилка з IFERROR
У більшості випадків немає сенсу захоплювати #REF! помилка у формулі, оскільки #REF! вказує на проблему низького рівня. Однак одна ситуація, коли IFERROR може мати сенс вловити помилку #REF, це коли ви динамічно будуєте посилання за допомогою функції INDIRECT.
