Чому # REF! відбувається і як виправити # ПОСИЛАННЯ! в Excel

У цій статті ми дізнаємося чому #REF! відбуваються і як виправити #REF!

У excel ми іноді отримуємо #REF! помилка. Ця помилка виникає, коли щось важливе відсутнє в діапазоні, який не може бути ідентифікований у формулі, яку ви вказали для комірки, і розпізнається Excel, показуючи помилку. Згідно з офіційним сайтом Microsoft #REF! — це спосіб сказати Excel, вказаний діапазон неприпустимий у вказаній формулі.

Якщо ви використовуєте посилання на осередок для масиву, якщо воно недійсне, ця помилка пропонує вам перевірити деякі моменти, що обговорюються нижче. Зазвичай це відбувається, коли ви намагаєтеся скопіювати формулу з одного осередку до іншого, не усвідомлюючи посилання: / excel-generals-relative-and-absolute-reference-in-excel [relative reference] `в Excel.

Давайте розберемося, як виникає ця помилка та як її виправити на кількох прикладах. Насамперед уважно перевірте формулу в комірці для кожного символу.

# ПОСИЛАННЯ! Помилка через копіювання та вставки формули з іншого осередку. Основна причина # ПОСИЛАННЯ! помилка - це коли формула, яка використовується в комірці, не знаходить дійсне посилання. Як правило, в Excel є визначений формат для формули, який він приймає лише при дотриманні цього формату.

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

Наприклад, функція MIN знаходить найменше значення масиві, заданому як вхідні. Тут діапазон (A2: A8) передається як посилання на комірку функції MIN у комірці A9. І копія комірки вставляється в комірку D4.

Подивіться на знімок вище і зрозумійте, що помилка сталася щойно.Значення MIN для зазначеного вище масиву формул (A2 та A21).

Як це виправити! Ви можете виправити помилку за результатом. По-перше, вам потрібно запитати себе, що вам потрібно скопіювати значення або формулу. Виконайте вказівки нижче, щоб дізнатися, що нам потрібно скопіювати.

Копіювання значення з одного осередку до іншого

Копіюйте та вставляйте значення з одного осередку до іншого за допомогою опції «Вставити за значеннями». Ця опція просто дозволяє користувачеві просто вставити значення комірки замість формули.

СХОДНІ СТАТТІ

• 30 найпопулярніших питань для співбесід з Microsoft Excel
• Важлива деталь у Microsoft Excel Viewer,
• Як використовувати РАХУНКИ в VBA в Microsoft Excel
• Як конвертувати число в текст, використовуючи текст Excel у Microsoft Excel 2010
• Перетворити Дата та час від GMT (час за Грінвічем) на CST (Central Standard Time)
• Do While Loop в Excel VBA
• Як вибрати перші 5 символів з іншого осередку в Microsoft Excel
• Як Виділіть рядки та стовпці вибраної комірки Використання VBA в Microsoft Excel 2010
• Перетворення дюймів у десятковому на Microsoft Excel
• Виберіть із випадаючого списку та витяг даних із різних листових у Microsoft Excel

Формула Excel: як виправити помилку #REF! помилка -

#REF! помилка виникає, коли посилання недійсне. У багатьох випадках це відбувається тому, що аркуші, рядки або стовпці були видалені або тому, що формула з відносними посиланнями була скопійована в нове місце, де посилання недійсні. Виправлення помилки #REF - це питання редагування формули, щоб знову використати дійсні посилання. Див. Нижче додаткові приклади та інформацію.

Пояснення

Про #REF! помилка

#REF! помилка виникає, коли посилання недійсне.У багатьох випадках це відбувається тому, що аркуші, рядки або стовпці були видалені або тому, що формула з відносними посиланнями була скопійована в нове місце, де посилання недійсні. У наведеному прикладі формула C10 повертає #REF! помилка при копіюванні в комірку E5:

=SUM(C5:C9) // original C10 =SUM(#REF) // when copied to E5

Запобігання помилкам #REF

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

Якщо ви викликаєте #REF! помилка, краще одразу виправити. Наприклад, якщо ви видалите стовпець і # ПОСИЛАННЯ! з'являються помилки, скасуйте цю дію (ви можете використовувати клавіші Control + Z). Коли ви скасуєте дію, #REF! помилки зникнуть. Потім відредагуйте формулу (и), щоб виключити стовпець, який потрібно видалити, при необхідності перемістивши дані. Нарешті видаліть стовпець і переконайтеся, що #REF немає! помилки.

Видалення кількох #REF! помилки

Щоб швидко видалити з листа багато помилок #REF, можна використовувати функцію "Знайти і замінити". Використовуйте клавіші Control + H, щоб відкрити діалогове вікно «Знайти та замінити». Введіть #REF! в області введення пошуку та залиште область введення заміни порожньою:

Потім можна вносити індивідуальні зміни за допомогою «Знайти далі + замінити» або використовувати «Замінити всі» для заміни всіх помилок #REF за один крок.

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

Помилки #REF досить складно виправити, тому що вихідне посилання на комірку зникло назавжди. Якщо ви знаєте, яким має бути посилання, ви можете просто виправити його вручну. Просто відредагуйте формулу та замініть # ПОСИЛАННЯ! з чинним посиланням. Якщо ви не знаєте, яким має бути посилання на комірку, вам, можливо, доведеться детальніше вивчити робочий лист, перш ніж ви зможете виправити формулу.

Примітка: Ви не можете скасувати видалення аркуша Excel. Якщо ви видаляєте вкладку робочого листа і бачите помилки #REF, найкращим варіантом, ймовірно, буде закрити файл і повторно відкрити останню збережену версію. Тому завжди зберігайте книгу (або зберігайте копію) перед видаленням одного або декількох аркушів.

# ПОСИЛАННЯ! помилки з ВВР

Ви можете побачити #REF! помилка з функцією ВПР, коли стовпець вказано неправильно. На екрані нижче ВПР повертає # ПОСИЛАННЯ! тому що в діапазоні таблиці немає шпальти 3, тобто B3: C7:

Якщо для індексу шпальти встановлено правильне значення 2, #REF! помилка усунена, і функція ВПР працює правильно:

Примітка: Ви також можете побачити помилку #REF з функцією INDEX, коли посилання на рядок або стовпець є недійсним.

Захоплення #REF! помилка з ЯСЛИПОМИЛКА

Найчастіше перехопити #REF! помилка у формулі, тому що # ПОСИЛАННЯ! вказує на проблему низького рівня. Однак одна ситуація, коли ПОЛИШНЯ може мати сенс відловити помилку # ПОСИЛАННЯ, - це коли ви створюєте посилання динамічно за допомогою функції ДВССИЛ.