Якщо ви деякий час працювали з формулами в Excel, я впевнений, що ви вже зустріли #REF! Помилка (помилка посилання).
Оскільки це досить поширене явище, я подумав написати навчальний посібник, щоб вирішити проблему #REF! помилка.
У цьому посібнику з Excel я розгляну, що таке #REF! помилка, що її викликає і як її виправити.
Що таке #REF! Помилка в Excel?
Помилка посилання - це те, що ви отримуєте, коли ваша формула не знає, на яку клітинку/діапазон посилатися, або коли посилання недійсне.
Найбільш поширеною причиною помилки посилання є те, що у вас є формула, яка посилається на клітинку/рядок/стовпець, і ви видаляєте цю клітинку/рядок/стовпець.
Тепер, оскільки формула (яка посилалася на діапазон до її видалення) не знає, куди вказувати, це попереднє посилання у формулі змінюється на #REF !.
Це, у свою чергу. формула повертає #REF! помилка результату формули.
Приклад #REF! Помилка
Дозвольте мені показати вам простий приклад, коли ми в кінцевому підсумку отримуємо помилку посилання, а пізніше я розповім, як її знайти та виправити.
Нижче наведено набір даних, куди я хочу додати чотири комірки, і я використовую просту арифметику за допомогою оператора додавання.
Все йде нормально!
Тепер, якщо я видалю один зі стовпців, формула не матиме посилання на видалену клітинку, тому вона дасть мені #REF! помилка (як показано нижче)
У наведеному вище прикладі, оскільки я видалив четвертий рядок (у якому була клітинка зі значенням 3), формула не знає, на що посилатися, і повертає помилку посилання.
Помилки посилання є досить поширеними, і ви можете швидко перевірити, перш ніж використовувати набір даних у обчисленні (це може мати помилку #REF!).
Тож дозвольте мені показати вам два способи швидкого пошуку комірок, які мають помилку посилання та деякі можливі виправлення.
Пов’язаний підручник: Видалити рядки на основі значення клітинки (або умови) в Excel
Знайдіть #REF! Помилка використання функції "Знайти та замінити"
Припустимо, у вас є набір даних, як показано нижче, де у вас є кілька помилок посилання.
Нижче наведено кроки для пошуку та вибору всіх клітинок, які мають "помилки посилання":
- Виберіть весь набір даних, де потрібно перевірити
- Утримуйте клавішу Control і натисніть клавішу F (або Command + F, якщо ви використовуєте Mac). Відкриється діалогове вікно «Знайти та замінити»
- У полі "Знайти що" введіть #REF!
- Натисніть кнопку «Знайти все». Буде знайдено всі клітинки з #REF! помилка
- Утримуйте клавішу Control і натисніть клавішу A (або Command + A, якщо ви використовуєте Mac). Буде вибрано всі клітинки з помилкою посилання.
- Закрийте діалогове вікно Знайти та замінити.
Вищеописані кроки знайдуть, а потім виберуть усі клітинки з #REF! помилку і виберіть ці.
Вибравши всі ці клітинки з помилкою, ви можете вибрати, що з ними робити.
Давайте розглянемо деякі з можливих способів виправлення помилки посилання.
Способи виправлення #REF! помилка
Після того як усі клітинки з помилкою посилання вибрано, ви можете вибрати будь -яке з наведеного нижче:
- Видалити помилку: Просто натисніть клавішу видалення, і вона видалить ці помилки, і замість них у вас будуть порожні клітинки
- Замінити значенням або текстом: Ви можете замінити помилку на 0 або тире або будь -який інший значущий текст. Для цього просто введіть те, на що ви хочете замінити помилку, утримуйте клавішу Control, а потім натисніть клавішу Enter. Це введе текст, який ви ввели у всі виділені клітинки.
- Виділіть ці клітинки за допомогою параметра кольору клітинки на вкладці Головна на стрічці
Примітка: Під час використання методу "Знайти та замінити" він знайде лише клітинки, які мають #REF! помилку, оскільки це те, що ми шукали у полі "Знайти що". Якщо є інші помилки (наприклад, помилка #NA або #DIV!), Вони не будуть знайдені (якщо ви не повторите ті самі кроки і для цих помилок).
Якщо ви хочете знайти всі помилки (включаючи помилку #REF!), Скористайтесь описаним нижче методом.
Знайдіть помилки за допомогою переходу до спеціальної опції
Ще один спосіб швидко знайти #REF! помилки які є результатом формули за допомогою спеціального методу вставки.
Позитивна сторона цього методу полягає в тому, що він знайде та виділить усі клітинки, які мають будь -який тип помилки (включаючи помилки посилання). Але недоліком цього методу є те, що він лише знайде та виділить клітинки, де помилка через формулу. Якщо помилка є текстом, цей метод не зможе її знайти.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете знайти та виділити всі клітинки, у яких є помилки.
Нижче наведено кроки, за допомогою яких можна перейти до спеціальних опцій, щоб знайти та вибрати всі помилки:
- Виберіть весь набір даних, де потрібно перевірити наявність помилок
- Натисніть вкладку Домашня сторінка
- У групі Редагування натисніть опцію «Знайти та вибрати».
- У параметрах, що з’являються, натисніть опцію «Перейти до спеціального»
- У діалоговому вікні Перейти до спеціального натисніть на опцію Формули
- У параметрах у формулах зніміть прапорець поруч з усіма пунктами Помилка.
- Натисніть OK
Вищезазначені кроки миттєво виділять усі комірки, які мають формули, що повертають помилку.
Вибравши ці клітинки, ви можете видалити їх, виділити або замінити на 0, тире чи інший змістовний текст.
Примітка: Одним обмеженням цього методу є те, що він буде знаходити та вибирати лише ті клітинки, де помилка є результатом формули. Якщо у вас є дамп даних, де помилки знаходяться всередині клітинок як саме значення, а не як результат формули, цей метод не виділить ці клітинки.Отже, це два швидкі методи, які можна використовувати для пошуку та виправлення помилки #REF (помилка посилання) у Excel.
Сподіваюся, цей підручник був вам корисним.
