10 можливих помилок у функціях Excel та їх рішення

Ось як автоматично виявити ці помилки в Excel

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

Це 10 поширених помилок у функціях Excel

Нижче наведено десять найпоширеніших кодів помилок для можливих проблем та рішень Excel.

1. #NAME?

Виникають помилки при введенні - якщо це так у формулі, замість результату з'являється повідомлення про помилку #NAME? Немає сенсу приховувати помилку за допомогою такої функції, як IFERROR. Ви повинні це виправити. Все, що вам потрібно зробити, це уважно подивитися на назву, яку ви дали формулі. Чи є перекручена буква? Ви забули лист або ввели його двічі? Наприклад, виправте = SUME (A3: A16) на = SUM (A3: A16), і ви отримаєте правильний результат.

Використовуйте майстер формул

Цього типу помилок можна уникнути, скориставшись Помічником формул. Це працює так:

Коли ви починаєте вводити назву формули, відкриється спадне меню з іменами, які відповідають введеним значенням.

Якщо ви введете ім’я та початкову дужку, у тексті наведення буде вказано правильне написання.

Майстер функцій допоможе при вході у функцію. Якщо позначити клітинку формулою та вибрати «Вставити функцію» на вкладці «Формула», Excel викликає майстра. Тут відображаються окремі аргументи - а також чи є помилка.

2. #НУЛЬ!

Це повідомлення про помилку може вказувати на дві речі:

• Ви вказали неправильний оператор діапазону у формулі.
• Ви використовуєте оператор перетину на двох неперекриваються областях.

У першому випадку ви маєте на увазі безперервний діапазон клітинок у формулі - наприклад клітинки В4 -В12. Ви використовуєте двокрапку як оператор діапазону. Наприклад, якщо ви хочете обчислити підсумок, правильна формула = SUM (B4: B12). Якщо ви посилаєтесь на дві області без накладання, крапка з комою є відповідним оператором. Якщо ви хочете обчислити суму з областей B4 до B12 та C9 до C23, ця формула правильна: = SUM (B4: B12; C9: C23).

У другому випадку потрібно працювати з діапазонами клітинок, що перекриваються, але ці два діапазони не перекриваються. Наприклад, ви отримуєте помилку для формули = CELL ("Адреса" (B4: B12 D4: D6)). Між двома областями немає перекриття. Якщо змінити області так, щоб вони перекривалися - наприклад як = CELL ("Адреса" (B4: B12 B5: D5)) - точка перетину обох областей відображається як результат. У цьому випадку це клітина В5.

3. #РЕФЕРЕНТАЦІЯ!

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

Наприклад, у вас є електронна таблиця Excel з рядками 1, 2 і 3 та стовпцями A, B і C. Формула = SUM (A2; B2; C2). Тепер видаліть рядок 2. Замість результату ви побачите значення помилки #REFER, оскільки одне зі значень для обчислення не існує.

Якщо ви випадково видалили частину, яка відсутня для обчислення, ви можете виправити свою помилку безпосередньо за допомогою команди "Скасувати". Якщо підрайон був належним чином видалений, переформулюйте формулу. Якщо ви введете = SUM (A2: C2), вам буде показано правильний результат, оскільки Excel просто видаляє видалений другий стовпець.

4. #ЦІННІСТЬ!

Це повідомлення про помилку може вказувати на багато різних проблем Excel. Одне зі значень у таблиці не відповідає інформації у формулі, тому програма не може виконати обчислення. Це має місце, наприклад, якщо одна з клітинок, що використовується для обчислення, містить слово замість числа.

Оскільки існує багато потенційних джерел помилок для #VALUE! є багато способів виправити це. Наступні рішення можуть допомогти:

• Замість математичних операторів можна використовувати функції. Тому замість = B4 + B5 + B6 введіть функцію = SUM (B4: B6).
• Перевірте відповідні клітинки на наявність спеціальних символів. При необхідності використовуйте для цього функцію ISTTEXT в окремому стовпці. Це показує, в якій клітинці знаходиться помилка.
• Можливо, значення помилки пов'язане з пробілами в клітинці, яка з'являється у функції. Щоб дізнатися, позначте відповідні клітинки. Потім перейдіть до "Пошук і вибір" під "Пуск" і натисніть "Замінити". Введіть пробіл для "Пошук" і нічого для "Замінити на". Тепер ви можете замінити небажані пробіли.
• Приховані символи також можуть призвести до цієї поширеної помилки у функціях Excel. Щоб усунути їх, перейдіть до "Пуск" та "Сортування та фільтрування" у "Фільтрі". Під стрілкою фільтра деактивуйте «Виділити все» та поставте галочку «Очистити клітинки» та всі позиції, де нічого немає. Якщо ви натиснете OK, Excel покаже вам усі нібито порожні клітинки, які містять приховані символи. Виділіть його і натисніть "Видалити". Знову видаліть фільтр.

5. #####

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

6. # DIV / 0!

Наприклад, припустимо, що ви ввели функцію, яку потрібно розділити на кілька значень. Тоді може статися так, що в одній із клітинок є нуль, значення якого потрібно поділити на - або взагалі нічого. У цьому випадку значення помилки # DIV / 0! З'являється, що вказує на те, що ділення на нуль не допускається.

Рішення: Переконайтеся, що у відповідних осередках Excel немає нуля або нічого, або змініть посилання. Крім того, ви можете придушити відображення помилки - а саме тоді, коли ви все ще чекаєте значень, які потрібно вставити у відповідні клітинки. Для цього можна, наприклад, використати формулу Excel IFERROR, яка більш детально представлена нижче.

7. #NV!

З таким значенням помилки Excel вказує, що те, що ви шукали, неможливо знайти. Наприклад, якщо ви призначили значення певним термінам і хочете їх відобразити, але одного з термінів немає, це повідомлення про помилку з’являється замість результату. Це часто робиться разом із формулами LOOKUP, HLOOKUP, MATCH або VLOOKUP. Наприклад, ви можете відображати помилки за допомогою VLOOKUP, оскільки це допомагає вам порівнювати таблиці між собою або узагальнювати їх.

Приклад: Ви створили таблицю з відповідними цінами на гвинти, гачки, штирі, болти, гайки тощо і забули один із умов. Програма не знаходить його. Відповідно, не може бути ціни, лише #NV! вказують. Ви вирішуєте цю проблему, додаючи термін. Крім того, ви можете придушити відображення помилок за допомогою формули IFERROR (див. Нижче).

8. #НОМЕР

Якщо функція містить недійсне числове значення, ви отримаєте помилку #NUMBER. Це може статися, наприклад, коли ви пишете числа типу 1000 з крапкою після одиниці. Введіть цифри без будь -якого форматування. Можливо, ви також десь берете квадратний корінь з від’ємного числа або робите інші обчислення, які не працюють математично?

Інша ситуація, яка може призвести до значення помилки #NUM, - це використання ітераційних функцій, таких як відсотки. Щоб отримати результат у цьому випадку, просто змініть кількість ітерацій, які Excel дозволяє обчислити, тобто повторне використання одного і того ж методу обчислення. Це необхідно при розрахунку відсотків, наприклад, якщо три відсотки додаватимуться знову і знову.

Як це зробити:

1. У розділі "Файл" та "Параметри" виберіть "Формули". Тут у розділі "Параметри розрахунку" встановіть прапорець "Активувати ітераційний розрахунок".

2. У розділі "Максимальна кількість ітерацій" можна вказати кількість обчислень, які має виконувати Excel. Чим вище це число, тим довше буде потрібно для обчислення.

3. У полі "Максимальна зміна" можна вказати, скільки може бути сума зміни між двома результатами рахунків -фактур.

У рідкісних випадках також може статися, що результат формули занадто великий або занадто малий для обчислення в Excel. Для цього, однак, результат повинен бути нижче -1x10 (високий) 307 або вище 1x10 (високий) 307. У цьому випадку вам доведеться відредагувати формули так, щоб результат знаходився в діапазоні, який може обчислити Excel.

9. Кругова довідка

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

Як усунути помилку:

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

2. Якщо ви хочете знайти нерозкриті кругові посилання, перейдіть до "Формули", "Моніторинг формул" та "Кругові посилання". Усі кругові посилання в документі тепер відображаються, і ви можете їх вирішити, як у першому випадку.

10. E +

E + не є типовою помилкою у функціях Excel, але час від часу все ще створює плутанину для користувачів. Якщо ви вводите дуже велике число в одну з клітинок, Excel може скоротити її. Тоді E + вступає в гру. Наприклад, 265000000000000 у комірці Excel стає 2,65E + 14. Це експоненційний формат. Excel використовує його, щоб мати можливість відображати навіть великі числа, щоб вони були повністю видимі в клітинках.

Таким чином можна відображати цифри повністю

Якщо ви хочете, щоб цифри відображалися повністю, необхідно виконати кілька простих кроків:

1. Позначте відповідні позиції у своєму списку.

2. Натисніть Ctrl і 1, щоб мати можливість форматувати клітинки.

3. У діалоговому вікні, що відкриється, виберіть вкладку "Числа" та категорію "Номер".

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

5. Після натискання кнопки OK програма Excel покаже вам номер у таблиці повністю.

Автоматично виявляти типові помилки у функціях Excel

У вас є можливість активувати автоматичну перевірку Excel. Якщо для вас його ще не ввімкнено, достатньо декількох клацань мишею:

У розділі "Файл" перейдіть до "Параметри".

Виберіть "Формули".

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

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

Ці функції визначають помилки у функціях Excel

Ви можете використовувати ці функції для швидкої перевірки типових помилок у функціях Excel.

ПОМИЛКА

Деякі формули можуть бути не повними, як зараз. Ви хочете запобігти відображенню помилок? Для цього підходить функція IFERROR. Він оцінює типи помилок #NV, #VALUE!, #REFERENCE!, #DIV / 0!, #NUMBER!, #NAME? або #NULL! кінець. Він має просту структуру, оскільки містить лише значення та значення_і_помилки. Перше значення перевіряє правильність функції. У разі помилки програма повинна відображати останнє значення результату. Ця функція дуже корисна при тривалому редагуванні таблиці.

Приклад:

Ви вводите = IFERROR (B3 * C3; "Enter values") або = IFERROR (B3 * C3; ""). У першому випадку вам буде нагадано, що вам ще належить зробити (якщо є помилка) з текстом "Додати значення". У другому випадку клітинка, яка зазвичай містить результат, просто залишається порожньою.

Увага: Вводячи текст у формулу, ви завжди повинні поставити його в лапки. Інакше не вийде. Оскільки без лапок Excel розуміє слова як частину формули.

ПОМИЛКА

У зв'язку з функцією IFERROR функція ISERROR також може вказувати на наявні помилки. Таким чином, вони не перешкоджають подальшим розрахункам у списку. Для цього введіть, наприклад: = IF (ISERROR (B3); "будь ласка, перевірте"; B3 / 6). Якщо розрахунок можна провести, ви отримаєте результат. Якщо Excel виявляє помилку під час перевірки B3, у відповідній комірці з’являються слова «будь ласка, перевірте».

Висновок: Виправити помилки у функціях Excel швидко та легко

Будь то зворотна буква, формула, яку неможливо математично обчислити, неправильне посилання чи пробіл, оскільки значення все ще відсутнє - помилки трапляються у повсякденній роботі. Excel це теж знає. Автоматична перевірка вказує на типові помилки функцій Excel і пояснює, що це таке. Існують також функції, які перевіряють або приховують помилки, щоб ви могли перейти до решти електронної таблиці.