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

Застосовується до
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel Web App

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

Скористайтеся розкривним списком нижче або перейдіть до однієї з інших областей:

Виправлення помилки певної функції

Яка функція використовується?

AVERAGE

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях AVERAGE і SUM

CONCATENATE

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції CONCATENATE

COUNTIF, COUNTIFS

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях COUNTIF і COUNTIFS

DATEVALUE

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції DATEVALUE

DAYS

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції DAYS

FIND, FINDB

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях FIND, FINDB, SEARCH і SEARCHB

IF

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції IF

INDEX, MATCH

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях INDEX і MATCH

SEARCH, SEARCHB

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях FIND, FINDB, SEARCH і SEARCHB

SUM

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях AVERAGE і SUM

SUMIF, SUMIFS

Докладні відомості див. в статті Виправлення помилки #VALUE! у функціях SUMIF і SUMIFS

SUMPRODUCT

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції SUMPRODUCT

TIMEVALUE

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції TIMEVALUE

TRANSPOSE

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції TRANSPOSE

VLOOKUP

Докладні відомості див. в статті Виправлення помилки #VALUE! у функції VLOOKUP

* Жодна з перелічених вище

Якщо потрібної функції немає в цьому списку, спробуйте інші способи вирішення, перелічені нижче.

Проблеми з відніманням

Основна операція віднімання

Початківці іноді неправильно вводять в Excel формулу для віднімання. Її можна створити двома способами.

Віднімання одного посилання на клітинку від іншого

Клітинка D2 зі значенням 2000,00 $, клітинка E2 зі значенням 1500,00 $, клітинка F2 з формулою =D2-E2 і результатом 500,00 $ Введіть два значення у двох окремих клітинках. У третій клітинці відніміть посилання на одну клітинку від іншого. У цьому прикладі клітинка D2 містить планову суму, а клітинка E2 – фактичну. F2 містить формулу =D2–E2.

Використання функції SUM із додатними та від’ємними числами

Клітинка D6 зі значенням 2000,00 $, клітинка E6 зі значенням 1500,00 $, клітинка F6 із формулою =SUM(D6;E6) і результатом 500,00 $ Введіть додатне значення в одній клітинці, а від'ємне в іншій. У третій клітинці скористайтеся функцією SUM, щоб додати дві клітинки. У цьому прикладі клітинка D6 містить планову суму, а клітинка E6 – фактичну (від’ємне число). F6 містить формулу =SUM(D6;E6).

Помилка #VALUE! в результаті виконання основної операції віднімання

Якщо використовується програма для Windows, помилка #VALUE! може з’явитися в результаті, навіть якщо це основна операція віднімання. Вирішити проблему, яка її спричинила, можна таким чином:

  1. Спочатку виконайте швидку перевірку. У новій книзі введіть 2 в клітинці A1. Введіть 4 в клітинці B1. Потім у клітинці C1 введіть таку формулу: =B1–A1. Якщо з’явиться помилка #VALUE!, перейдіть до наступного кроку. В іншому разі спробуйте інший спосіб вирішення на цій сторінці.

  2. Відкрийте у Windows панель керування регіональними параметрами.

    • Windows 10Windows 10: Натисніть кнопку "Пуск", введіть "Регіон", а потім виберіть панель керування "Регіон".
    • Windows 8Windows 8: На початковому екрані введіть "Регіон", виберіть "Параметри", а потім виберіть "Регіон".
    • Windows 7. Натисніть кнопку "Пуск", введіть "Регіон", а потім виберіть "Регіон і мова".

  3. На вкладці "Формати" натисніть кнопку "Додаткові параметри".

  4. Знайдіть Роздільник елементів списку. Якщо роздільником елементів списку призначено знак мінус, змініть його на інший. Наприклад, зазвичай для списків використовується кома. Ще один поширений роздільник – крапка з комою. Однак, можливо, у вашому регіоні прийнятніший інший варіант.

  5. Натисніть кнопку OK.

  6. Відкрийте книгу. Якщо клітинка містить помилку #VALUE!, двічі клацніть клітинку, щоб відредагувати.

  7. Якщо там, де має бути мінус для віднімання, стоять коми, замініть їх на знак "мінус".

  8. Натисніть клавішу Enter.

  9. Повторіть ці дії для інших клітинок, які містять помилку.

Віднімання дат

Віднімання одного посилання на клітинку від іншого

Клітинка D10 зі значенням 01.01.2016, клітинка E10 зі значенням 24.04.2016, клітинка F10 із формулою =E10-D10 і результатом 114 Введіть дві дати у двох окремих клітинках. У третій клітинці відніміть посилання на одну клітинку від іншого. У цьому прикладі клітинка D10 містить дату початку, а клітинка E10 – дату завершення. F10 містить формулу =E10–D10.

Використання функції DATEDIF

Клітинка D15 зі значенням 01.01.2016, клітинка E15 зі значенням 24.04.2016, клітинка F15 із формулою =DATEDIF(D15;E15;d) і результатом 114 Введіть дві дати у двох окремих клітинках. У третій клітинці скористайтеся функцією DATEDIF, щоб знайти різницю між датами. Докладні відомості про функцію DATEDIF див. у статті Обчислення різниці між двома датами.

Помилки #VALUE! в результаті віднімання дат, збережених у текстовому форматі

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

Перевірка на наявність пробілів на початку

  1. Двічі клацніть дату, яка використовується у формулі віднімання.
  2. Помістіть курсор на початок і перевірте, чи можна виділити один або кілька пробілів. Ось як виглядає виділений пробіл на початку клітинки: Клітинка з виділеним пробілом перед 01.01.2016
    Якщо в клітинці виявлено цю проблему, перейдіть до наступного кроку. Якщо пробілів немає, перейдіть до наступного розділу, де розповідається про те, як перевірити параметри дати на комп’ютері.
  3. Виділіть стовпець, який містить дату, клацнувши його заголовок.
  4. Виберіть текст даних>у стовпцях.
  5. Виберіть "Далі" двічі.
  6. На кроці 3 з 3 у майстрі в розділі "Формат даних стовпця" виберіть пункт "Дата".
  7. Виберіть формат дати, а потім натисніть кнопку "Готово".
  8. Повторіть ці дії для інших стовпців, щоб виключити наявність пробілів перед датами.

Перевірка параметрів дати на комп’ютері

Excel використовує систему дат, задану на комп’ютері. Якщо в клітинках використовується інша система дат, Excel не розпізнає їхні значення як дати.

Наприклад, припустімо, що на комп’ютері дати відображають у такому вигляді: дд.мм.рррр. Якщо значення в клітинці введено в такому форматі, Excel розпізнає його як дату, тож цю клітинку можна використовувати у формулі віднімання. Однак якщо ввести значення у форматі дд.мм.рр, програма не розпізнає його як дату. Натомість вона інтерпретуватиме його як текст.

Цю проблему можна вирішити двома способами. Ви можете змінити систему дат, яка використовується на комп’ютері, на ту, яку потрібно використовувати в Excel. Інший варіант – створити в Excel новий стовпець і скористатися функцією DATE, щоб створити дату зі значень, які зберігаються як текст. Ось як це можна зробити, якщо система дат на комп’ютері передбачає формат дд.мм.рррр, а дата, задана в текстовому форматі в клітинці A1 має такий вигляд: 31/12/2017.

  1. Створіть таку формулу: =DATE(RIGHT(A1;4);MID(A1;4;2);LEFT(A1;2)).
  2. Результат буде таким: 31.12.2017.
  3. Якщо ви хочете отримати такий формат, як дд.мм.рр, натисніть клавіші CTRL+1 (або зображення піктограми кнопки Command на комп'ютері MAC + 1 на комп'ютері Mac).
  4. Виберіть іншу мову, у якій підтримується формат мм.дд.рр, наприклад англійська (Сполучені Штати). Коли формат застосується, результат матиме вигляд 31/12/2017 , і це буде справжня дата, а не її текстове представлення.

Примітка.

У формулі вище використовуються функції DATE, RIGHT, MID і LEFT. Зверніть увагу: у ній припускається, що дата в текстовому форматі містить два символи для днів, два символи для місяців і чотири символи для року. Можливо, її доведеться змінити відповідно до конкретного формату дати.

Проблеми з пробілами в тексті

Видалення пробілів, які спричиняють помилку #VALUE!

Часто помилка #VALUE! стається тому, що формула посилається на інші клітинки, які містять пробіли або навіть приховані пробіли (які додатково ускладнюють ситуацію). Через це може здаватися, що клітинка пуста, хоча це не так. 

1. Виділення клітинок, на які вказують посилання

Виділено стовпець Знайдіть клітинки, на які посилається формула, і виділіть їх. У багатьох випадках бажано видалити пробіли з усього стовпця, оскільки це дає можливість позбутися відразу кількох зайвих пробілів одночасно. У цьому прикладі якщо натиснути букву E , виділиться весь стовпець.

2. Пошук і заміна

Вкладка > " На вкладці "Основне" натисніть кнопку "Знайти" & кнопку"Замінити>".

3. Заміна пробілів пустим значенням

Пошук поля, яке містить пробіл, і заміна його пустим вмістом У полі "Знайти " введіть один пробіл. Потім видаліть усе з поля Замінити на.

4. Заміна одного значення або всіх значень

Кнопка Якщо ви впевнені, що слід видалити всі пробіли в стовпці, натисніть кнопку "Замінити все". Якщо ви хочете поступово замінювати пробіли на окремі елементи, спочатку натисніть кнопку "Знайти далі ", а потім натисніть кнопку "Замінити ", коли будете впевнені, що простір не потрібен. Коли все буде готово, можливо, помилка #VALUE! зникне. Якщо цього не сталося, перейдіть до наступного кроку.

5. Активування фільтру

Головна > Сортування & фільтрування > фільтра Іноді приховані символи, окрім пробілів, можуть призвести до того, що клітинка здаватиметься пустою, хоча насправді вона не пуста. Це можуть спричинити одинарні лапки в клітинці. Щоб позбутися цих символів у стовпці, увімкніть фільтр, вибравши пункт "Сортування &>> фільтр".

6. Налаштування фільтра

Меню фільтрування зі знятим прапорцем Клацніть стрілку фільтра " та зніміть прапорець "Виділити все". Далі встановіть прапорець поруч із пунктом (Пусті).

7. Установлення всіх безіменних прапорців

Прапорець, установлений коло безіменного поля Установіть усі прапорці, коло яких нічого не написано, як цей.

8. Виділення пустих клітинок і їх видалення

Виділено відфільтровані пусті клітинки Коли в Excel залишаться тільки пусті клітинки, виділіть їх. Потім натисніть клавішу Delete. Програма видалить усі приховані символи в клітинках.

9. Очищення фільтра

Меню фільтра, видалення фільтра з ... Клацніть стрілку фільтра Стрілка фільтра , а потім виберіть пункт Очистити фільтр з... , щоб було видно всі клітинки.

10. Результат

#VALUE!, а замість неї відображається результат формули. Зелений трикутник у клітинці E4 Якщо винуватцем #VALUE стали пробіли! спричинили пробіли, замість неї має з’явитися результат обчислення формули, як показано тут у нашому прикладі. В іншому разі повторіть ці дії для інших клітинок, на які посилається формула. Крім того, можна спробувати інші способи вирішення, запропоновані на цій сторінці.

Примітка.

Зверніть увагу, що в цьому прикладі в клітинці E4 відображається зелений трикутник, а число вирівняно ліворуч. Це означає, що число збережено як текст. Це може призвести до проблем у майбутньому. Якщо ви помітили таку ситуацію, радимо перетворити числа з текстового формату на числовий.

Перевірка на наявність текстових або спеціальних символів

Текст або спеціальні символи в клітинці можуть призвести до помилки #VALUE!. Проте іноді важко зрозуміти, у яких саме клітинках виникли ці проблеми. Вирішення. Розв'язання: Використовуйте функцію ISTEXT , щоб перевірити клітинки. Зверніть увагу, що ISTEXT не виправляє помилку, а просто знаходить клітинки, які можуть її спричиняти.

Приклад із #VALUE!

H4 з формулою =E2+E3+E4+E5 і результатом #VALUE! Тут наведено приклад формули з #VALUE! . Ймовірніше за все, її спричинила клітинка E2. Спеціальний символ відображається як невеликий прямокутник після "00". Крім того, як показано на наступному зображенні, ви можете перевірити текст за допомогою функції ISTEXT в окремому стовпці.

Той же приклад з ISTEXT

Клітинка F2 з формулою =ISTEXT(E2) і результатом TRUE Тут функцію ISTEXT додано в стовпець F. Усі клітинки правильні, окрім тієї, що містить значення TRUE. Це означає, що клітинка E2 містить текст. Щоб вирішити цю проблему, можна видалити її вміст і ввести значення 1865,00. Крім того, можна також скористатися функцією CLEAN, щоб очистити символи або використати функцію REPLACE, щоб замінити спеціальні символи іншими значеннями.

Застосувавши функцію CLEAN або REPLACE, можна скопіювати результат і скористатися > домашнім > вставленням > і вставленням спеціальних значень. Ви можете також перетворити числа з текстового формату на числовий.

Використання функцій замість операцій

Формули з математичними операціями, як-от + і *, можуть не обчислювати клітинки з текстом або пробілами. У такому випадку спробуйте натомість скористатися функцією. Функції часто пропускають текстові значення та обчислюють усе як числа, не допускаючи #VALUE! . Наприклад, замість =A2+B2+C2 введіть =SUM(A2:C2). Або замість =A2*B2 введіть =PRODUCT(A2,B2).

Інші можливі вирішення

Знайдіть джерело помилки

Виділення помилки

Клітинка H4 з формулою =E2+E3+E4+E5 і результатом #VALUE! Спочатку виділіть клітинку з #VALUE! помилку #REF!.

Натисніть кнопку "Формули" > Обчислити формулу

Діалогове вікно Вибір формул>Обчислення формул Обчислення формул> Excel перевіряє кожну частину формули окремо. У нашому випадку формула =E2+E3+E4+E5 не працює через прихований пробіл у клітинці E2. У клітинці E2 пробілу не видно. Однак його можна побачити тут. Він відображається як " ".

Заміна помилки #VALUE! на щось інше

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

Попередження

Функція IFERROR приховує всі помилки, а не лише #VALUE! . Ми не радимо приховувати помилки, оскільки вони часто свідчать про те, що потрібно дещо виправити, а не приховати. Використовуйте цю функцію, лише якщо ви абсолютно певні, що формула працює належним чином.

Клітинка з #VALUE!

Клітинка H4 з формулою =E2+E3+E4+E5 і результатом #VALUE! Тут наведено приклад формули з #VALUE! через прихований пробіл у клітинці E2.

Помилка, прихована за допомогою функції IFERROR

Клітинка H4 з формулою =IFERROR(E2+E3+E4+E5,--) Ось тут до формули додано таку само формулу з IFERROR. Формулу можна прочитати як: "Формула обчислюється, але якщо стається будь-яка помилка, результат замінюється на два дефіси". Зважте, що також можна використати "", щоб не відображалося нічого замість двох дефісів. Крім того, можна підставити власний текст, наприклад: "Помилка підсумку".

На жаль, очевидно, що функція IFERROR не вирішує помилки, а просто приховує їх. Тому слід бути певними, що краще приховати помилку, ніж вирішити її.

Перевірка доступності зв’язків із даними

На певному етапі зв'язок із даними може стати недоступним. Щоб виправити це, відновіть його або спробуйте імпортувати дані, якщо це можливо. Якщо доступу до зв’язку немає, попросіть автора книги створити новий файл для вас. В ідеалі новий файл має містити лише значення без зв'язків. Для цього можна скопіювати всі клітинки та вставити лише їхні значення. Щоб вставити лише значення, вони можуть вибрати "Домашнєвставлення">Вставлення>спеціальних>значень. Усі формули та зв’язки буде видалено, тому зникнуть і помилки #VALUE .

Публікація запитання на форумі спільноти Excel

Якщо ви не знаєте, що робити на цьому етапі, пошукайте схожі запитання на форумі спільноти Excel або опублікуйте власне запитання.

Посилання на форум спільноти Excel Публікація запитання на форумі спільноти Excel

Додаткові відомості

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

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