Поділитися, додати в закладки або роздрукувати статтю
Питання про помилки в Ексель - найпоширеніші, я їх отримую кожен день, ними наповнені тематичні форуми і сервіси відповідей. Дуже легко припуститися помилки у формулі Excel, особливо коли працюєш швидко і з великою кількістю даних. Результат - неправильні розрахунки, незадоволені керівники, збитки ... Як же знайти помилку, яка закралася в Ваших розрахунках? Давайте розбиратися. Єдиного інструменту або алгоритму пошуку помилок немає, тому будемо рухатися від простого до складного.
Тепер на стрічці знайдіть Головна - Число, і в списку виберіть відповідний формат даних. Зробіть це для всіх осередків, в яких формула стала звичайним текстом.
Якщо Ви змінюєте вихідні дані, а формули на аркуші ніяк не хочуть перераховуватися - у Вас відключений автоматичний перерахунок формул.
Щоб це виправити - натисніть на стрічці: Формули - Обчислення - Параметри обчислень - Автоматично. Тепер все буде перераховуватися, як зазвичай.
Пам'ятайте, автоматичний перерахунок міг бути відключений цілеспрямовано. Якщо у Вас на аркуші величезна кількість формул - кожне внесення змін змушує їх перераховуватися. У підсумку, робота з документом переростає в хронічний стан очікування після кожної зміни. В такому випадку, потрібно перевести перерахунок в ручний режим: Формули - Обчислення - Параметри обчислень - Вручну. Тепер вносите всі зміни у вихідні дані, програма буде чекати. Коли всі зміни внесені - тисніть F9, все формули оновлять значення. Або Знову включите автоматичний перерахунок.
Ще одна класична ситуація, коли в результаті розрахунків Ви отримуєте не результат, а осередок, заповнену знаками решітки:
Насправді, це не помилка, так програма говорить Вам, що результат не влазить в клітинку, потрібно просто збільшити її ширину до потрібного розміру.
Іноді трапляється, що після виконання обчислень, результат з'являється не в тому вигляді, який Ви очікуєте. Наприклад, склали два числа, а в результаті отримали дату. Чому так відбувається? Ймовірно, формат даних в осередку раніше був встановлений «дата». Просто змініть його на потрібний, і все буде по-Вашому.
Якщо в формулах використовувалася посилання на документ, який тепер недоступний, при черговому перерахунку програма попросить вказати розташування документа, в якому містяться втрачені дані.
Для початку знайдіть всі зовнішні посилання (як це робиться - я писав в статті про зовнішні посилання і консолідації ). Перевірте на які файли вони посилаються і, при необхідності, розірвіть зв'язку, або вкажіть нові посилання.
Якщо Ви написали формулу, а вона повертає очевидно неправильний результат - розбираємося з логікою формули. Ви могли банально помилитися в дужках, порядку проходження операторів. вивчіть правила написання формул і перевірте чи вірно у Вашій. Якщо вірно - переходимо до наступного кроку.
Якщо Ви використовуєте функції - переконайтеся, що Ви знаєте правила застосування функцій . Кожна з них має свій синтаксис. Перевірте, чи правильно Ви задали параметри для формули, для чого прочитайте довідку по ній. Натисніть F1 і в вікні «Довідка Excel» в пошуку напишіть Вашу функцію, наприклад, « ВПР ». Програма відобразить список доступних матеріалів по цій функції. Як правило, їх вистачає, щоб отримати повне уявлення про роботу Вашої функції. Автори довідки дуже доступно викладають матеріал, наводять приклади використання.
Часто користувачі невірно вказують посилання на осередки в формулах, від того і отримують помилкові результати. Перше, що потрібно зробити для перевірки зовнішніх формул - включити відображення формул в осередках. Для цього виконайте на стрічці Формули - Залежності формул - Показати формули. Тепер в осередках будуть відображатися формули, а не результати розрахунків. Можна пробігтися очима по листу і перевірити чи правильні посилання вказані. Щоб знову показати результати - ще раз виконайте ту ж команду.
Щоб спростити процес - можна включити стрілки посилань. Можна легко визначити на які клітинки посилається формула, натиснувши Формули - Залежності формул - Впливають осередки. На аркуші синіми стрілками буде вказано, на які дані Ви посилаєтеся.
Аналогічно, можна побачити осередки, формули в яких посилаються на задану клітку. Для цього виконуємо: Формули - Залежності формул - Зовсім осередки.
Врахуйте, що в складних таблицях отрисовка стрілок займе багато часу і машинних ресурсів. Щоб прибрати стрілки - клікніть Формули - Залежності формул - Прибрати стрілки.
Як правило, уважна перевірка формул з перерахованими вище інструментами вирішує проблеми помилкового результату. Шукаємо проблему до перемоги!
Іноді після введення формули програма попереджає, що введена циклічна посилання. Розрахунок припиняється. Це означає, що формула посилається на клітинку, яка, в свою чергу, посилається на клітинку, в яку Ви вводите формулу. Виходить, замкнутий цикл обчислень, програма повинна буде обчислювати результат нескінченно довго. Але цього не буде, ви будете попереджені і отримаєте можливість усунути проблему.
Часто, осередки посилаються один на одного побічно, тобто не безпосередньо, а через проміжні формули.
Щоб знайти такі «неправильні» формули, знайдіть на стрічці: Формули - Залежності формул - Перевірка наявності помилок - Циклічні помилки. Це меню відкриває список осередків з «зацикленими» формулами. Клацніть по будь-, щоб встановити в неї курсор і перевірити формулу.
Природно, циклічні посилання усуваються шляхом перевірки та виправлення логіки обчислень. Однак, в деяких випадках, циклічна посилання не буде помилкою. Тобто, цією системою формул все ж потрібно дати прорахуватися до стану, близького до рівноваги, коли зміни практично не відбуваються. Деякі інженерні завдання вимагають цього. На щастя, Excel це допускає. Називається такий підхід «ітеративні обчислення». Щоб їх включити, натисніть Файл - Параметри - Формули, і встановіть галку «Ітеративні обчислення». Там же встановіть:
- Гранична кількість ітерації - максимальна кількість ітерацій (циклів), яке буде проведено до повної зупинки
- Відносна похибка - мінімальна зміна цільових значень за одну ітерацію, при яких перерахунок буде зупинений.
Тобто циклічна формула буде прораховуватимуться до досягнення відносної похибки, але не більше, ніж задано граничним числом ітерацій.
Іноді при обчисленнях випадають помилки, що починаються зі знака "#". Наприклад, «# Н / Д», «# ЧИСЛО!», І т.д. ці помилки я описував раніше , Прочитайте цей пост і постарайтеся осмислити причину появи Вашої помилки. Коли це станеться, Ви легко все виправите.
Якщо ж Вам не вдається знайти помилку в досить складній формулі - клікніть на знак оклику біля осередку і в контекстному меню виберіть «Показати етапи обчислення».
На екрані з'явиться вікно, яке відображає в який з моментів обчислення виявилася невдалою, то він буде підкреслять у формулі. Це вірний спосіб зрозуміти, що саме не так.
Це, мабуть, всі основні способи знайти і виправити помилки в Ексель. Ми розглянули найбільш часто зустрічаються проблеми і способи їх усунення. А ось що робити, коли помилки потрібно передбачити відразу в обчисленнях, я розповім в пості про обхід помилок за допомогою функцій Ексель . Не пошкодуйте на цю статтю 5 хвилин часу, її уважне прочитання дозволить заощадити масу часу в майбутньому.
А я чекаю ваших запитань і коментарів до цього посту!
Поділитися, додати в закладки або роздрукувати статтю
Як же знайти помилку, яка закралася в Ваших розрахунках?Як виправити?
Чому так відбувається?
