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

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

Примітка.: Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Докладні відомості див. в статті Виправлення помилки #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).

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

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

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

    • У Windows 10. Натисніть кнопку Пуск, введіть Регіон і клацніть панель керування Регіон.

    • У Windows 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 див. в статті Обчислення різниці між двома датами.

Розширте стовпець дат. Якщо значення вирівняно за правим краєм, вони мають формат дати, а якщо за лівим, вони інтерпретуються як текст. Програма 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. Виберіть іншу мову, у якій підтримується формат мм.дд.рр, наприклад англійська (Сполучені Штати). Коли формат застосується, результат матиме вигляд 12.31.2017, і це буде справжня дата, а не її текстове представлення.

Примітка.: Формула вище написано без дати, праворуч, MIDі функції ліворуч . Зверніть увагу, що написано припущенні, що текстовою датою має два символи днів, дві цифри для місяців і чотири символи на рік. Потрібно настроїти формулу, щоб відповідно до дати.

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

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

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

Виділено стовпець

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

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

Вкладка "Основне > Знайти й виділити > Замінити"

На вкладці Основне натисніть кнопку Знайти й виділити, a потім виберіть Замінити.

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 очищення символи, або використовувати замість функції замінити спеціальні символи з іншими значеннями.

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

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

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

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

Клітинка H4 з формулою =E2+E3+E4+E5 і результатом #VALUE!

Спочатку виберіть клітинку з помилкою #VALUE!.

Виберіть "Формули > Обчислити формулу".

Діалогове вікно "Обчислення формули" з формулою " "+E3+E4+E5

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

Іноді потрібно просто замінити на #VALUE! Помилка щось інше, як власний текст, нуль або пусті клітинки. У цьому випадку функція IFERROR можна додати до формули. IFERROR перевірте, чи немає повідомлення про помилку і якщо так, замініть його інше значення за вашим вибором. Якщо не з'являється повідомлення про помилку, буде обчислено вихідного формулу. IFERROR буде працювати тільки у програмах Excel 2007 або пізнішої версії. Для попередніх версій можна використовувати IF(ISERROR()).

Попередження!: 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

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

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

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

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

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

×