Когда вы вводите или редактируете формулу, а также когда меняется одно из входных значений функции, Excel может показать одну из ошибок вместо значения формулы. В программе предусмотрено семь типов ошибок. Давайте рассмотрим их описание и способы устранения.
Если при вводе формулы вы случайно неправильно расставили скобки, Excel выведет на экран предупреждающее сообщение — см. рис. 1. В этом сообщении вы увидите предположение Excel о том, как их необходимо расставить. В случае если вы подтверждаете такую расстановку, нажмите Да . Но зачастую требуется собственное вмешательство. Для этот нажмите Нет и исправьте скобки самостоятельно.
Перехватить любые ошибки и обработать их можно с помощью функции ЕОШИБКА() . Данная функция возвращает истину или ложь в зависимости от того, появляется ли ошибка при вычислении ее аргумента. Общая формула для перехвата выглядит так: =ЕСЛИ(ЕОШИБКА(выражение);ошибка; выражение) .
Функция если вернет ошибку (например, сообщение), если при расчете появляется ошибка. Например, рассмотрим следующую формулу: =ЕСЛИ(ЕОШИБКА(А1/А2);""; А1/А2) . При возникновении ошибки (деление на 0) формула возвращает пустую строку. Если же ошибки не возникает, возвращается само выражение А1/А2 .
Существует другая, более удобная функция ЕСЛИОШИБКА() , которая совмещает две предыдущие функции ЕСЛИ() и ЕОШИБКА() : ЕСЛИОШИБКА(значение;значение при ошибке) , где: значение — выражение для расчета, значение при ошибке — возвращаемый результат в случае ошибки. Для нашего примера это будет выглядеть так: =ЕСЛИОШИБКА(А1/А2;"") .
Доброго времени суток друзья!
В этой статье мы поговорим, о том какие бывают ошибки в формулах Excel , с которыми мы сталкиваемся, работая с электронными таблицами Excel. Я более чем уверен, что ошибки видели все, а вот как правильно от них избавится, знают поменьше. Всё же эти знания важны, так как это застрахует вас от типичных ошибок или поможет быстро и без паники избавится или исправить полученные ошибки в формулах Excel .
Об ошибках в Excel можно говорить много, но давайте рассмотрим самые распространенные, я расскажу, почему и как они получаются, а также как исправить возникшие ошибки в формулах Excel , для правильного отображения данных.
Ну вот, собственно какие бывает ошибки в формулах Excel :
Надеюсь, эта статья о том, какие бывают ошибки в формулах Excel и их исправлении, стала вам полезной, и вы узнали для себя что-то новое и интересное.
До встречи в новых статьях!
"Почему так устроен мир, что у людей, которые умеют жить в свое удовольствие, никогда нет денег, а те, у кого деньги водятся, понятия не имеют, что значит «прожигать жизнь»?
Д.Б. Шоу
При создании сложных формул (да и просто невнимательности) в MS Excel ошибку совершить довольно легко. Обычно MS Excel в таких случаях выводит сообщения об ошибках или даже предлагает «правильный» на его взгляд вариант написания формулы, однако даже при наличии справочной системы, поначалу довольно сложно понять, чего же хочет от нас «глупая программа». В этой статье мы рассмотрим все типы ошибок возникающих в формулах MS Excel, и научимся их исправлять и понимать.
Если бы был «топ ошибок MS Excel», первое место в нем принадлежало бы ошибке #ЗНАЧ! . Как можно догадаться из названия, возникает она в том случае, когда в формулу или функцию подставлено неправильное значение. Если вы пытаетесь провести арифметические операции с текстом, или подставляете в функцию диапазон ячеек, когда требуется указать всего одну ячейку, результатом вычислений будет ошибка #ЗНАЧ!.
Как и говорилось — попытка сложить число и текст ставит MS Excel в тупик
Одна из самых частых ошибок при вычислениях. Обозначает самую простейшую вещь — в формуле используется ссылка на ячейку которую вы или не создавали или ненароком удалили. Чаще всего #ССЫЛКА! возникает когда вы удаляете «ненужный» столбец, некоторые ячейки которого, как оказывается, участвовали в вычислениях.
Со школьной скамьи мы помним простое правило: на ноль делить нельзя! Ошибка #ДЕЛ/0! — это предупреждение от MS Excel о том, что это базовое правило нарушено и вы все-таки пытаетесь разделить некое число на ноль. При этом сам «ноль» не обязателен — любая попытка разделить существующее число на «пустую» ячейку также вызовет эту ошибку.
Делить на ноль нельзя — пустая ячейка воспринимается MS Excel как тот же ноль
Ошибка #Н/Д возникает в том случае, если в функции пропущен какой-то аргумент, или одно из используемых в формуле значений становится недоступно. Увидел #Н/Д — первым делом ищи чего в твоих вычислениях не хватает.
Применяю функцию ВПР, знак разделения поставил, а вот указать к какой ячейке он относится — забыл
Ошибка #ИМЯ — признак того, что вы и Excel друг друга не поняли. Вернее MS Excel не понял что вы имели ввиду — вы явно указываете на какой-то элемент, а программа его не может найти. В каких случаях это обычно происходит?
Попытка суммировать несуществующий диапазон с названием Столбец
Ещё одна простейшая ошибка — текст в функциях и формулах указывается в кавычках
Ошибка #ПУСТО чаще всего возникает когда в формуле пропущен один из операторов, но может возникать и в том случае, когда нам требуется найти пересечение двух диапазонов ячеек, а этого пересечения просто не существует.
Все бы хорошо, но забыл про второй знак «+»
Ошибку #ЧИСЛО! ms Excel выдает в тех случаях, когда результат математических вычислений в формуле порождает какой-то совершенно нереальный результат. Результат в виде предельно большого или малого числа, попытка вычислить корень из отрицательного числа — все это приведет к возникновению ошибки #ЧИСЛО!
Вычислить корень из отрицательного числа? Вас бы не понял не только Excel
В прошлом весьма распространенная «ошибка» MS Excel связанная с внезапным заполнением ячейки знаками решетки (#) могла быть вызвана тем, что в ячейку введено число которое не помещается в ней целиком (но только если ячейка имеет формат «числовой» или «дата»).
С появлением MS Office 2013 ошибка практически сошла на нет, так как «поумневший» Excel стал в большинстве случаев автоматически увеличивать ширину ячейки под число. Если же вы видите «решетки», проще всего избавиться от них увеличив ширину ячейки вручную.
Достаточно увеличить ширину столбца и проблема исчезнет
Если вы знаете в каких случаях в экселе возникает та или иная ошибка, вы скорее всего сразу поймете чем она вызвана. Однако программа ещё более упрощает вам работу и выводит рядом с ошибочным значением спецсимвол в виде значка восклицательного знака в желтом ромбе. При нажатии на него, вам будет предложен список возможных действий по исправлению ошибки.
Нажмите на значок, чтобы получить помощь в исправлении ошибки
В первой строке появившегося контекстного меню вы увидите полное название ошибки, во второй сможете вызвать подробную справку по ней, но самое интересное скрывает в себе третий пункт: «Показать этапы вычисления… «.
«Показать этапы вычисления…» — программу не обманешь, точно выводит фрагмент формулы где допущена ошибка
Нажмите на него и в появившемся окне увидите тот самый фрагмент формулы где допущена ошибка — это особенно удобно, когда «распутывать» приходится целый клубок из громады вложенных друг в друга действий.
Вопросы про ошибки в Эксель – самые распространенные, я их получаю каждый день, ими наполнены тематические форумы и сервисы ответов. Очень легко допустить ошибку в формуле Excel, особенно когда работаешь быстро и с большим количеством данных. Результат – неправильные расчеты, недовольные руководители, убытки… Как же найти ошибку, которая закралась в Ваших расчетах? Давайте разбираться. Единого инструмента или алгоритма поиска ошибок нет, поэтому будем двигаться от простого к сложному.
Если Вы написали формулу в ячейке, нажали Enter, а вместо результата в ней отображается сама формула – значит выбран текстовый формат значения для данной ячейки. Как исправить? Сначала изучите, и выберите тот, который нужен Вам, но не текстовый, в этом формате вычисления не производятся.
Теперь на ленте найдите Главная – Число , и в раскрывающемся списке выберите подходящий формат данных . Сделайте это для всех ячеек, в которых формула стала обычным текстом.
Если Вы изменяете исходные данные, а формулы на листе никак не хотят пересчитываться – у Вас отключен автоматический пересчет формул.
Чтобы это исправить – нажмите на ленте: Формулы – Вычисления – Параметры вычислений – Автоматически . Теперь все будет пересчитываться, как обычно.
Помните, автоматический пересчет мог быть отключен целенаправленно. Если у Вас на листе огромное количество формул – каждое внесение изменений заставляет их пересчитываться. В итоге, работа с документом перерастает в хроническое состояние ожидания после каждого изменения. В таком случае, нужно перевести пересчет в ручной режим: Формулы – Вычисления – Параметры вычислений – Вручную . Теперь вносите все изменения в исходные данные, программа будет ждать. Когда все изменения внесены – жмите F9 , все формулы обновят значения. Или cнова включите автоматический пересчет.
Еще одна классическая ситуация, когда в результате расчетов Вы получаете не результат, а ячейку, заполненную знаками решетки:
На самом деле, это не ошибка, так программа говорит Вам, что результат не влазит в ячейку, нужно просто до нужного размера.
Иногда случается, что после выполнение вычислений, результат предстает не в том виде, который Вы ожидаете. Например, сложили два числа, а в результате получили дату. Почему так происходит? Вероятно, формат данных в ячейке ранее был установлен «дата». Просто измените его на нужный, и все будет по-Вашему.
Если Вы написали формулу, а она возвращает очевидно неправильный результат – разбираемся с логикой формулы. Вы могли банально ошибиться в скобках, порядке следования операторов. Изучите и проверьте все ли верно в Вашей. Если верно – переходим к следующему шагу.
Если Вы используете функции – убедитесь, что Вы знаете . Каждая из них имеет свой синтаксис. Проверьте, правильно ли Вы задали параметры для формулы, для чего прочтите справку по ней. Нажмите F1 и в окне «Справка Excel» в поиске напишите Вашу функцию, например, « ». Программа отобразит список доступных материалов по этой функции. Как правило, их хватает, чтобы получить полное представление о работе Вашей функции. Авторы справки очень доступно излагают материал, приводят примеры использования.
Часто пользователи неверно указывают ссылки на ячейки в формулах, от того и получают ошибочные результаты. Первое, что нужно сделать для проверки внешних формул – включить отображение формул в ячейках. Для этого выполните на ленте Формулы – Зависимости формул – Показать формулы . Теперь в ячейках будут отображаться формулы, а не результаты расчетов. Можно пробежаться глазами по листу и проверить правильные ли ссылки указаны. Чтобы снова показать результаты – еще раз выполните ту же команду.
Чтобы упростить процесс – можно включить стрелки ссылок. Можно легко определить на какие ячейки ссылается формула, нажав Формулы – Зависимости формул – Влияющие ячейки . На листе синими стрелками будет указано, на какие данные Вы ссылаетесь.
Аналогично, можно увидеть ячейки, формулы в которых ссылаются на заданную клетку. Для этого выполняем: Формулы – Зависимости формул – Зависимые ячейки .
Учтите, что в сложных таблицах отрисовка стрелок займет много времени и машинных ресурсов. Чтобы убрать стрелки – кликните Формулы – Зависимости формул – Убрать стрелки .
Как правило, внимательная проверка формул с перечисленными выше инструментами решает проблемы ошибочного результата. Ищем проблему до победы!
Иногда после введения формулы программа предупреждает, что введена циклическая ссылка. Расчет прекращается. Это значит, что формула ссылается на ячейку, которая, в свою очередь, ссылается на ячейку, в которую Вы вводите формулу. Получается, замкнутый цикл вычислений, программа должна будет вычислять результат бесконечно долго. Но этого не будет, вы будете предупреждены и получите возможность устранить проблему.
Часто, ячейки ссылаются друг на друга косвенно, т.е. не напрямую, а через промежуточные формулы.
Чтобы найти такие «неправильные» формулы, найдите на ленте: Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ошибки . Это меню открывает список ячеек с «зацикленными» формулами. Кликните по любой, чтобы установить в нее курсор и проверить формулу.
Естественно, циклические ссылки устраняются путем проверки и исправления логики вычислений. Однако, в некоторых случаях, циклическая ссылка не будет ошибкой. То есть, этой системе формул все же нужно дать просчитаться до состояния, близкого к равновесию, когда изменения практически не происходят. Некоторые инженерные задачи требуют этого. К счастью, Excel это допускает. Называется такой подход «итеративные вычисления». Чтобы их включить, нажмите Файл – Параметры – Формулы ,и установите галку «Итеративные вычисления». Там же установите:
То есть циклическая формула будет просчитываться до достижения относительной погрешности, но не больше, чем задано предельным числом итераций.
Иногда при вычислениях выпадают ошибки, начинающиеся со знака «#». Например, «#Н/Д», «#ЧИСЛО!», и т.д. эти ошибки я , прочтите этот пост и постарайтесь осмыслить причину появления Вашей ошибки. Когда это произойдет, Вы легко все исправите.
Если же Вам не удается найти ошибку в достаточно сложной формуле – кликните на восклицательный знак возле ячейки и в контекстном меню выберите «Показать этапы вычисления».
На экране появится окно, отображающее в какой из моментов вычисления возникает ошибка, он будет подчеркнут в формуле. Это верный способ понять, что именно не так.
Это, пожалуй, все основные способы найти и исправить ошибки в Эксель. Мы рассмотрели наиболее часто встречающиеся проблемы и способы их устранения. А вот что делать, когда ошибки нужно предусмотреть сразу в вычислениях, я расскажу в посте про . Не пожалейте на эту статью 5 минут времени, ее внимательное прочтение позволит сэкономить массу времени в будущем.
А я жду ваших вопросов и комментариев к этому посту!
Дата: 24 декабря 2015 Категория:Ошибки в Экселе – непременный спутник всех, кто . Когда выражение в ячейке рассчитать невозможно, программа отображает в ячейке сообщение об ошибке. Оно начинается со знака «#», после которого записывается имя ошибки. Этого не нужно пугаться, если вы ориентируетесь в функциях Эксель и умеете следовать простейшей логике математических операций – легко найдёте и исправите ошибку.
Если ячейка полностью заполнена знаками решётки (#), это вовсе не ошибка. В ячейке недостаточно места для отображения результата. Увеличьте размеры ячейки или уменьшите шрифт, чтобы результат мог отобразиться.
Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:
| Ошибка | Описание |
| #ДЕЛ/0! | Ошибка возникает при попытке деление на ноль |
| #ИМЯ? | Программа не может распознать введенное имя. Например, вы ошиблись при написании имени функции, или не заключили текстовую строку в кавычки |
| #Н/Д | Данные недоступны. Например, не нашла ни одного значения |
| #ПУСТО! | Вы запросили пересечение диапазонов, которые не пересекаются |
| #ЧИСЛО! | Проблема в одном из числовых значений, которые используются в формуле. Например, Вы пытаетесь извлечь квадратный корень из отрицательного числа. В классической математике эта операция лишена смысла |
| #ССЫЛКА! | Формула содержит ссылку, которой нет. Например, вы удалили ячейку, на которую она ссылается |
| #ЗНАЧ! | Формула содержит недопустимые компоненты. Часто такая ошибка возникает при нарушении синтаксиса формул |
Когда в ячейке формула с ошибкой, возле нее появляется маркер. Нажав на него, вы можете почитать справку по этой ошибке. А еще можно посмотреть этапы вычисления. Выберите этот пункт, и программа покажет окно, где место ошибки будет подчёркнуто. Это лучший способ определить место, где возникает ошибка.
Кроме того, можно обойтись без исправления таких ошибок, а просто их . Но это должно быть уместно. Ошибки нужно обходить, только если нельзя исправить. В противном случае, результаты расчетов могут быть искажены.
Отслеживание ошибки через этапы вычисления
Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1 , а в А2 написать =A1 , возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.
Иногда случается сложное «зацикливание», когда циклическая ссылка образуется с несколькими промежуточными формулами.
Чтобы отследить такие ошибки, выполните Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки . В выпадающем списке программа выводит адреса ячеек, создающих бесконечный цикл. Остаётся только исправить формулы в этих клетках.
Отслеживание циклических ссылок
В Excel можно попытаться рассчитать результат зацикленных формул. Для этого установите галочку Файл – Параметры – Формулы – Включить итеративные вычисления . В этом же блоке можно задать максимальное количество итераций (просчётов) для нахождения баланса и допустимую погрешность. В большинстве случаев этого делать не нужно, поэтому я рекомендую эту галку не устанавливать. Тем не менее, когда Вы знаете, что зацикленные формулы верны и их расчет приведет к устойчивому результату — почему бы это не сделать?
Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в ! Готов ответить на ваши вопросы – пишите в комментариях.
В следующей статье я расскажу . Стоит ли говорить, что функции Эксель — «наше всё»?
Думаю, нет. Потому, переходите и читайте, это будет первый шаг в мир сложных формул с правильными результатами!
