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

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

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

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

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

Введіть додатне значення в одній клітинці, а від’ємне в іншій. У третій клітинці скористайтеся функцією 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. Повторіть ці дії для інших клітинок, які містять помилку.

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

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

1. Двічі клацніть дату, яка використовується у формулі віднімання.

2. Помістіть курсор на початок і перевірте, чи можна виділити один або кілька пробілів. Ось як виглядає виділений пробіл на початку клітинки:

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

3. Виділіть стовпець із датою, вибравши його заголовок стовпця.

4. Виберіть дані > текст за стовпцями.

5. Двічі натисніть кнопку Далі .

6. На кроці 3 із 3 майстра в розділі Формат даних стовпця виберіть дата.

7. Виберіть формат дати, а потім натисніть кнопку Готово.

8. Повторіть ці дії для інших стовпців, щоб виключити наявність пробілів перед датами.

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

Excel використовує систему дат, задану на комп’ютері. Якщо в клітинку введено не ту саму систему дат, програма 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 (або +1 на комп’ютері Mac).

4. Виберіть іншу мову, у якій підтримується формат мм.дд.рр, наприклад англійська (Сполучені Штати). Після застосування формату результат буде 12.31.2017 і це справжня дата, а не текстова дата.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Виберіть стрілку фільтра , а потім виберіть очистити фільтр від... для відображення всіх клітинок.

10. Результат

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

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

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

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

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

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

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

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

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

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

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

Тут наведено приклад формули з помилкою #VALUE! через прихований пробіл у клітинці E2.

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

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

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

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

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

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