В екселі пише значуще що робити. Помилки в Excel: як їх знайти, зрозуміти та виправити. Помилки у формулі Excel відображаються в комірках
При створенні складних формул (та й просто неуважності) у MS Excel помилку зробити досить легко. Зазвичай MS Excel у таких випадках виводить повідомлення про помилки або навіть пропонує «правильний» на його погляд варіант написання формули, проте навіть за наявності довідкової системи спочатку досить складно зрозуміти, чого хоче від нас «дурна програма». У цій статті ми розглянемо всі типи помилок, що виникають у формулах MS Excel, і навчимося їх виправляти та розуміти.
Помилка #ЗНАЧ! (помилка у значенні)
Якби був «топ помилок MS Excel», перше місце належало б помилці #ЗНАЧ!. Як можна здогадатися з назви, виникає вона у разі, коли у формулу чи функцію підставлено неправильне значення. Якщо ви намагаєтеся провести арифметичні операції з текстом, або підставляєте в функцію діапазон осередків, коли потрібно вказати лише одну комірку, результатом обчислень буде помилка #ЗНАЧ!.
Як і говорилося - спроба скласти число і текст ставить MS Excel у глухий кут
Помилка #ПОСИЛКА! (Неправильне посилання на комірку)
Одна з найчастіших помилок під час обчислень. Позначає найпростішу річ — у формулі використовується посилання на комірку, яку ви або не створювали або ненароком видалили. Найчастіше #ПОСИЛАННЯ!виникає коли ви видаляєте «непотрібний» стовпець, деякі осередки якого, як виявляється, брали участь у обчисленнях.
Помилка #СПРАВ/0! (ділення на нуль)
Зі шкільної лави ми пам'ятаємо просте правило: на нуль ділити не можна! Помилка #СПРАВ/0!- Це попередження від MS Excel про те, що це базове правило порушено і ви все-таки намагаєтеся розділити деяке число на нуль. При цьому сам «нуль» не є обов'язковим — будь-яка спроба розділити існуюче число на «порожній» осередок також викличе цю помилку.
Ділити на нуль не можна - порожній осередок сприймається MS Excel як той же нуль
Помилка #Н/Д (значення недоступне)
Помилка #Н/Двиникає в тому випадку, якщо в функції пропущено якийсь аргумент, або одне з значень, що використовуються у формулі, стає недоступно. Побачив #Н/Д — насамперед шукай чогось у твоїх обчисленнях не вистачає.
Застосовую функцію ВПР, знак поділу поставив, а ось вказати до якого осередку він належить - забув
Помилка #ІМ'Я? (Неприпустиме ім'я)
Помилка #ІМ'Я— ознака того, що ви та Excel один одного не зрозуміли. Точніше MS Excel не зрозумів, що ви мали на увазі — ви явно вказуєте на якийсь елемент, а програма його не може знайти. У яких випадках це зазвичай відбувається?
- У функції вказано осередок або діапазон осередків з неіснуючим (найчастіше з неправильно введеним) ім'ям.
Спроба підсумувати неіснуючий діапазон під назвою Стовпець
- Текст усередині функції полягає у лапки. Якщо цього не відбувається (тобто замість = "Вася" ми вводимо = Вася), MS Excel приходить в повне здивування.
Ще одна найпростіша помилка - текст у функціях та формулах вказується в лапках
- У назві функції випадково допущено друкарську помилку.
Помилка #ПУСТО! (порожня безліч)
Помилка #ПУСТОнайчастіше виникає коли у формулі пропущено один із операторів, але може виникати і в тому випадку, коли нам потрібно знайти перетин двох діапазонів осередків, а цього перетину просто не існує.
Все б добре, але забув про другий знак «+»
Помилка #ЧИСЛО! (Неправильне число)
Помилка #КІЛЬКІСТЬ! ms Excel видає у випадках, коли результат математичних обчислень у формулі породжує якийсь зовсім нереальний результат. Результат у вигляді гранично великого чи малого числа, спроба обчислити корінь із негативного числа — це призведе до виникнення помилки #ЧИСЛО!
Обчислити корінь із негативного числа? Вас би не зрозумів не лише Excel
Знаки «решітки» в осередку Excel (#######)
У минулому дуже поширена «помилка» MS Excel пов'язана з раптовим заповненням комірки знаками решітки (#) могла бути викликана тим, що в комірку введено число яке не міститься в ній цілком (але якщо комірка має формат «числовий» або «дата») .
З появою MS Office 2013 помилка практично зійшла нанівець, тому що «розумнілий» Excel став у більшості випадків автоматично збільшувати ширину комірки під число. Якщо ж ви бачите «решітки», найпростіше позбутися їх збільшивши ширину комірки вручну.
Достатньо збільшити ширину стовпця і проблема зникне
Якщо ви знаєте в яких випадках в екселі виникає та чи інша помилка, ви швидше за все відразу зрозумієте, чим вона викликана. Однак програма ще більше спрощує вам роботу і виводить поруч із помилковим значенням спецсимвол у вигляді значка знака оклику в жовтому ромбі. При натисканні на нього вам буде запропоновано список можливих дій з виправлення помилки.
Натисніть значок, щоб отримати допомогу у виправленні помилки
У першому рядку контекстного меню, що з'явилося, ви побачите повну назву помилки, у другому зможете викликати докладну довідку по ній, але найцікавіше приховує в собі третій пункт: « Показати етапи обчислення.«.
«Показати етапи обчислення…» — програму не обдуриш, точно виводить фрагмент формули, де допущена помилка
Натисніть на нього і в вікні, що з'явилося, побачите той самий фрагмент формули де допущена помилка - це особливо зручно, коли «розплутувати» доводиться цілий клубок з громади вкладених один в одного дій.
Помилки в Excel виникають часто. Ви, напевно, помічали дивні значення в осередках замість очікуваного результату, які починалися зі знаку # . Це свідчить, що формула повертає помилку. Щоб позбутися помилки, Ви повинні виправити її причину, а вони можуть бути різними.
Невідповідність дужок, що відкривають і закривають
Найпоширенішим прикладом виникнення помилок у формулах Excel є невідповідність дужок, що відкривають і закривають. Коли користувач вводить формулу, Excel автоматично перевіряє її синтаксис і не дасть закінчити введення, поки в ній є синтаксична помилка.
Наприклад, на малюнку вище ми навмисно пропустили дужку, що закриває, при введенні формули. Якщо натиснути клавішу Enter, Excel видасть таке попередження:
У деяких випадках Excel пропонує варіант виправлення помилки. Ви можете погодитися з Excel, або виправити формулу самостійно. У жодному разі сліпо покладатися на це виправлення в жодному разі не можна. Наприклад, на наступному малюнку Excel запропонував нам неправильне рішення.
Осередок заповнений знаками решітки
Бувають випадки, коли комірка в Excel повністю заповнена знаками ґрат. Це означає один із двох варіантів:
У разі збільшення ширини стовпця не допоможе.
Помилка #СПРАВ/0!
Помилка #СПРАВ/0!виникає, як у Excel відбувається розподіл на нуль. Це може бути як явний поділ на нуль, так і поділ на комірку, яка містить нуль або порожня.
Помилка #Н/Д
Помилка #Н/Двиникає, коли формули чи функції недоступне якесь значення. Наведемо кілька випадків виникнення помилки #Н/Д:
Помилка #ІМ'Я?
Помилка #ІМ'Я?виникає, коли у формулі є ім'я, яке Excel не розуміє.
У цьому прикладі ім'я діапазон не визначено.
Помилка #ПУСТО!
Помилка #ПУСТО!виникає, коли задано перетин двох діапазонів, що не мають спільних точок.
Помилка #ЧИСЛО!
Помилка #КІЛЬКІСТЬ!виникає, коли проблема у формулі пов'язана із значенням.
Не забувайте, що Excel підтримує числові величини -1Е-307 до 1Е+307.
- Ще одним випадком виникнення помилки #КІЛЬКІСТЬ!є вживання функції, яка під час обчислення використовує метод ітерацій і може обчислити результат. Яскравим прикладом таких функцій у Excel є СТАВКАі ВСД.
Помилка #ПОСИЛКА!
Помилка #ЗНАЧ!
Помилка #ЗНАЧ!одна з найпоширеніших помилок, що трапляються в Excel. Вона виникає, коли значення однієї з аргументів формули чи функції містить неприпустимі значення. Найпоширеніші випадки виникнення помилки #ЗНАЧ!.
Після введення або коригування формули, а також за зміни будь-якого значення функції буває, що з'являється помилка формули, а не потрібне значення. Усього табличний редактор розпізнає сім основних типів таких неправильних розрахунків. Як виглядають помилки в Excel та як їх виправити, розберемо нижче.
Нижче ми представимо опис формул, показаних на картинці з детальною інформацією з кожної помилки.
1. #СПРАВА/О!- «Поділ на 0», найчастіше виникає при спробі ділити на нуль. Тобто закладена в комірці формула, виконуючи функцію поділу, натикається на комірку, де з нульовим значенням або стоїть Пусто. Щоб усунути проблему, перевірте всі комірки, що беруть участь у обчисленнях, і виправте всі неприпустимі значення. Друга дія, що призводить до #СПРАВ/О! – це введення невірних значень у деякі функції, такі як =СРЗНАЧ(), якщо при розрахунку в діапазоні значень стоїть 0. Той самий результат спровокують незаповнені осередки, до яких звертається формула, яка вимагає розрахунку конкретних даних.
2. #Н/Д- "немає даних". Так Excel позначає значення, незрозумілі формулі (функції). Ввівши невідповідні цифри у функцію, ви обов'язково спричините цю помилку. При її появі простежте, чи всі вхідні осередки заповнені правильно, а особливо в тих, де світиться такий же напис. Часто зустрічається при використанні
3. #Ім'я? - "Неприпустиме ім'я", показник некоректного імені формули або якоїсь її частини. Проблема зникає, якщо перевірити та підправити всі назви та імена, що супроводжують алгоритм обчислень.
4. #ПУСТО!– «в діапазоні порожнє значення», сигнал про те, що десь у розрахунку прописані області, що не перетинаються, або проставлено пробіл між зазначеними діапазонами. Досить рідкісна помилка. Виглядати помилковий запис може так:
СУМ(G10:G12 I8:J8)
Excel не розпізнає такі команди.
5. #КІЛЬКІСТЬ!– помилку викликає формула, що містить число, яке не відповідає рамкам зазначеного діапазону.
6. #ПОСИЛАННЯ!– попереджає про те, що зникли осередки, пов'язані з цією формулою. Перевірте, найімовірніше були видалені осередки вказані у формулі.
7. #ЗНАЧ!– неправильно вибрано тип аргументу для роботи функції.
8. Бонус, помилка ##### - Ширина осередку недостатня для відображення всього числа
Крім того, Excel видає попередження про неправильну формулу. Програма спробує підказати вам, як саме зробити розстановку пунктуації (наприклад дужок). Якщо запропонований варіант відповідає вашим вимогам, натисніть «Так». Якщо підказка вимагає ручного коригування. Тоді оберіть «Ні» і переставте дужки самі.
Помилки в Excel. Застосування функції ПОМИЛКА () для Excel 2003
Добре допомагає ліквідувати помилки в Excel функція. Діє вона шляхом знаходження помилок у комірки, якщо знаходить помилку у формулі повертає значення ІСТИНА і навпаки. У комбінації з =якщо(), вона дозволить замінити значення якщо знайдено помилку.
Робоча формула: =ЯКЩО(ПОМИЛКА(вираз);помилка;вираз).
ЯКЩО(ЕПОМИЛКА (А1/А2);””;А1/А2)
Пояснення: якщо під час виконання А1/А2 знайдено помилку, буде повернено порожньо («»). Якщо все пройшло коректно (тобто ПОМИЛКА (А1/А2) = БРЕХНЯ), то розраховується А1/А2.
Помилки в Excel.Застосування ЕСЛИПОМИЛКА для Excel 2007 і вище
Одна з причин, чому я швидко перейшов на Excel 2007 була ЯСЛИПОМИЛКА() (найголовніша причина — це )
Функція якщопомилка містить можливості обох функцій - ПОМИЛКА () і ЯКЩО(), але доступна в нових версіях Excel, що дуже зручно
Інструмент активується наступним чином: =ЯКЛИПОМИЛКА(значення; значення при помилці). Замість «значення» ставиться розрахунковий вираз/посилання на комірку, а замість «значення при помилці» — те, що слід повернути при появі неточності, наприклад, якщо при розрахунку А1/А2 видається #СПРАВА/О!то формула буде виглядати так:
ЯСЛИПОМИЛКА(А1/А2;””)
» качайте тут
При помилкових обчисленнях формули відображають кілька типів помилок замість значень. Розглянемо їх у практичних прикладах у процесі роботи формул, які дали помилкові результати обчислень.
Помилки у формулі Excel відображаються в комірках
У цьому уроці буде описано значення помилок формул, які можуть містити комірки. Знаючи значення кожного коду (наприклад: #ЗНАЧ!, #СПРАВА/0!, #ЧИСЛО!, #Н/Д!, #ІМ'Я!, #ПУСТО!, #ПОСИЛКА!) можна легко розібратися, як знайти помилку у формулі та усунути її.
Як прибрати #ДІЛ/0 в Excel
Як видно при розподілі на комірку з порожнім значенням програма сприймає як розподіл на 0. У результаті видає значення: # СПРАВ/0! У цьому вся можна переконатися і з допомогою підказки.
В інших арифметичних обчисленнях (множення, підсумовування, віднімання) порожній осередок також є нульовим значенням.
Результат помилкового обчислення - #КІЛЬКІСТЬ!
Неправильне число: #ЧИСЛО! - Це помилка неможливості виконати обчислення у формулі.
Декілька практичних прикладів:
Помилка: #КІЛЬКІСТЬ! виникає, коли числове значення занадто велике або занадто маленьке. Так само дана помилка може виникнути при спробі отримати корінь із негативного числа. Наприклад, = КОРІНЬ (-25).
У осередку А1 – дуже велика кількість (10^1000). Excel не може працювати з такими великими числами.
У осередку А2 – та сама проблема з великими числами. Здавалося б, 1000 невелике число, але при поверненні його факторіалу виходить занадто велике числове значення, з яким Excel не впорається.
У осередку А3 – квадратний корінь може бути з негативного числа, а програма відобразила цей результат цієї ж помилкою.
Як прибрати НД в Excel
Значення недоступне: #Н/Д! означає, що значення є недоступним для формули:
Записана формула B1: =ПОШУКПОЗ("Максим"; A1:A4) шукає текстовий вміст "Максим" в діапазоні осередків A1:A4. Вміст знайдено у другому осередку A2. Отже, функція повертає результат 2. Друга формула шукає текстовий вміст «Андрій», діапазон A1:A4 не містить таких значень. Тому функція повертає помилку #Н/Д (немає даних).
Помилка #ІМ'Я! в Excel
Відноситися до категорії помилки у написанні функцій. Неприпустиме ім'я: #ІМ'Я! - означає, що Excel не розпізнав тексту написаного у формулі (назва функції = СУМ() йому невідомо, воно написано з помилкою). Це результат помилки синтаксису під час написання імені функції. Наприклад:
Помилка #ПУСТО! в Excel
Порожня множина: #ПОРОЖНЯ! - Це помилки оператора перетину множин. В Excel існує таке поняття як перетин множин. Воно застосовується для швидкого отримання даних великих таблиць за запитом точки перетину вертикального і горизонтального діапазону осередків. Якщо діапазони не перетинаються, програма відображає хибне значення – #ПУСТО! Оператором перетину множин є одиночний пробіл. Їм поділяються вертикальні та горизонтальні діапазони, задані в аргументах функції.
У цьому випадку перетином діапазонів є комірка C3 і функція відображає її значення.
Задані аргументи функції: =СУМ(B4:D4 B2:B3) – не утворюють перетин. Отже, функція дає значення з помилкою - #ПУСТО!
#ПОСИЛАННЯ! – помилка посилань на осередки Excel
У цьому прикладі помилка виникала при неправильному копіюванні формули. У нас є 3 діапазони осередків: A1: A3, B1: B4, C1: C2.
Під першим діапазоном в комірку A4 вводимо формулу, що підсумовує: =СУММ(A1:A3). А далі копіюємо цю формулу під другий діапазон, в комірку B5. Формула, як і раніше, підсумовує лише 3 осередки B2: B4, минаючи значення першої B1.
Коли та сама формула була скопійована під третій діапазон, в комірку C3 функція повернула помилку #ПОСИЛКА! Так як над коміркою C3 може бути тільки 2 комірки, а не 3 (як того вимагала вихідна формула).
Примітка. У цьому випадку найзручніше під кожним діапазоном перед початком введення натиснути комбінацію гарячих клавіш ALT+=. Тоді вставиться функція підсумовування і автоматично визначить кількість комірок, що підсумовують.
Як виправити ЗНАЧ в Excel
#ЗНАЧ! - Помилка в значенні. Якщо ми намагаємося скласти число і слово Excel в результаті ми отримаємо помилку #ЗНАЧ! Цікавий той факт, що якби ми спробували скласти два осередки, в яких значення першої число, а другий - текст за допомогою функції = СУМ (), то помилки не виникне, а текст прийме значення 0 при обчисленні. Наприклад:
Грати в осередку Excel
Ряд ґрат замість значення осередку ###### – це значення не є помилкою. Просто це інформація про те, що ширина стовпця занадто вузька для того, щоб вмістити вміст комірки, що коректно відображається. Потрібно просто розширити стовпець. Наприклад, зробіть подвійне клацання лівою кнопкою мишки на межі заголовків стовпців цієї комірки.
Так решітки (######) замість значення осередків можна побачити за негативної дати. Наприклад, ми намагаємося відібрати від старої дати нову дату. А в результаті обчислення встановлено формат осередків "Дата" (а не "Загальний").
Неправильний формат комірки також може відображати замість значень ряд символів решітки (######).
Доброї доби друзі!
У цій статті ми поговоримо про те, що бувають помилки у формулахExcel, з якими стикаємося, працюючи з електронними таблицями Excel. Я більш ніж впевнений, що помилки бачили всі, а от як правильно їх позбудеться, знають поменше. Все ж таки ці знання важливі, оскільки це застрахує вас від типових помилок або допоможе швидко і без паніки позбудеться або виправити отримані помилки у формулахExcel.
Про помилки в Excel можна говорити багато, але давайте розглянемо найпоширеніші, я розповім, чому і як вони виходять, а також як виправити виниклі помилки у формулахExcelдля правильного відображення даних.
Ну от, що буває помилки у формулахExcel:
- Помилка #####.Це одна з найпоширеніших і найпростіших помилок у формулах Excel. Означає вона лише одне, що ширина стовпця немає достатньої ширини, щоб повноцінно відобразити ваші дані. Лікування цієї проблеми дуже просте, курсор мишки наведіть на межу стовпця, і при затиснутій лівій кнопці збільшіть комірку до тих пір поки дані не почнуть відображатися або подвійним кліком на кордоні стовпця дозволить по найбільш широкому комірці в стовпці.
- Помилка #ІМ'Я?. Ця помилка (#NAME?)виникає у формулах Excel, лише тоді, коли редактор неспроможна розпізнати текст у формулі (наприклад, помилка у назві функції у зв'язку з друкарською помилкою =СУМ(А1:А4). помилки у формулахExcelВам потрібно уважно прочитати її і виправити помилку (А1:А4).
- Помилка #ЗНАЧ!. Ця помилка (#VALUE!)може виникнути у випадку, коли у формулі є аргумент, тип якого не підходить для ваших обчислень. Наприклад, у вашу математичну або формулу затесалося текстове значення = А1 + В1 + С1 де С1 - це текст. Лікування проблеми просто, використовуйте формулу, яка ігнорує комірки, в яких є текст або просто приберіть дане значення з комірки С1.
- Помилка #СПРАВА/0. Як видно з помилки, що виникла у формулі, ви просто помножили свій аргумент на число 0, а це не можна робити виходячи з математичних правил. Для виправлення цієї помилки, ви повинні змінити число, щоб воно не дорівнювало 0 або змінити формулу, наприклад, логічної , що дозволить уникнути помилки. =ЯКЩО(A2=0;””;A1/A2)
- Помилка #ПОСИЛКА!. Це одна з найпоширеніших та заплутаних помилок у функціях Excel. Коли ви бачите цю помилку, це означає, що формула посилається на комірку, якої більше немає. Особливо це проблемно, коли ви працюєте з великими обсягами даних і великою кількістю . Коли ви редагуєте свої таблиці, такі помилки у формулахExcelвас не повинні лякати, їх дуже легко виправити, потрібно лише й усе повернеться на свої місця, ну або при необхідності вручну переписати формулу, виключивши з неї помилковий аргумент.
Сподіваюся, ця стаття про те, які бувають помилки у формулахExcelта їх виправленні, стала вам корисною, і ви дізналися для себе щось нове та цікаве.
До зустрічі у нових статтях!
"Чому так влаштований світ, що у людей, які вміють жити на втіху, ніколи немає грошей, а ті, у кого гроші водяться, уявлення не мають, що означає «пропалювати життя»?
Д.Б. Шоу
