При создании сложных формул (да и просто невнимательности) в MS Excel ошибку совершить довольно легко. Обычно MS Excel в таких случаях выводит сообщения об ошибках или даже предлагает «правильный» на его взгляд вариант написания формулы, однако даже при наличии справочной системы, поначалу довольно сложно понять, чего же хочет от нас «глупая программа». В этой статье мы рассмотрим все типы ошибок возникающих в формулах MS Excel, и научимся их исправлять и понимать.
Ошибка #ЗНАЧ! (ошибка в значении)
Если бы был «топ ошибок MS Excel», первое место в нем принадлежало бы ошибке #ЗНАЧ!. Как можно догадаться из названия, возникает она в том случае, когда в формулу или функцию подставлено неправильное значение. Если вы пытаетесь провести арифметические операции с текстом, или подставляете в функцию диапазон ячеек, когда требуется указать всего одну ячейку, результатом вычислений будет ошибка #ЗНАЧ!.
Не работает автосумма в Excel. Как исправить?
Как и говорилось — попытка сложить число и текст ставит MS Excel в тупик
Ошибка #ССЫЛКА! (неправильная ссылка на ячейку)
Одна из самых частых ошибок при вычислениях. Обозначает самую простейшую вещь — в формуле используется ссылка на ячейку которую вы или не создавали или ненароком удалили. Чаще всего #ССЫЛКА! возникает когда вы удаляете «ненужный» столбец, некоторые ячейки которого, как оказывается, участвовали в вычислениях.
Ошибка #ДЕЛ/0! (деление на ноль)
Со школьной скамьи мы помним простое правило: на ноль делить нельзя! Ошибка #ДЕЛ/0! — это предупреждение от MS Excel о том, что это базовое правило нарушено и вы все-таки пытаетесь разделить некое число на ноль. При этом сам «ноль» не обязателен — любая попытка разделить существующее число на «пустую» ячейку также вызовет эту ошибку.
Делить на ноль нельзя — пустая ячейка воспринимается MS Excel как тот же ноль
Ошибка #Н/Д (значение недоступно)
Ошибка #Н/Д возникает в том случае, если в функции пропущен какой-то аргумент, или одно из используемых в формуле значений становится недоступно. Увидел #Н/Д — первым делом ищи чего в твоих вычислениях не хватает.
Применяю функцию ВПР, знак разделения поставил, а вот указать к какой ячейке он относится — забыл
Ошибка #ИМЯ? (недопустимое имя)
Ошибка #ИМЯ — признак того, что вы и Excel друг друга не поняли. Вернее MS Excel не понял что вы имели ввиду — вы явно указываете на какой-то элемент, а программа его не может найти. В каких случаях это обычно происходит?
- В функции указана ячейка или диапазон ячеек с несуществующим (чаще всего с неправильно введенным) именем.
Избавляемся от ошибки #ДЕЛ/0! в Excel
Попытка суммировать несуществующий диапазон с названием Столбец
- Текст внутри функции заключается в кавычки. Если этого не происходит (то есть вместо =»Вася» мы вводим =Вася), MS Excel приходит в полное недоумение.
Ещё одна простейшая ошибка — текст в функциях и формулах указывается в кавычках
- В названии функции случайно допущена опечатка.
Ошибка #ПУСТО! (пустое множество)
Ошибка #ПУСТО чаще всего возникает когда в формуле пропущен один из операторов, но может возникать и в том случае, когда нам требуется найти пересечение двух диапазонов ячеек, а этого пересечения просто не существует.
Все бы хорошо, но забыл про второй знак «+»
Ошибка #ЧИСЛО! (неправильное число)
Ошибку #ЧИСЛО! ms Excel выдает в тех случаях, когда результат математических вычислений в формуле порождает какой-то совершенно нереальный результат. Результат в виде предельно большого или малого числа, попытка вычислить корень из отрицательного числа — все это приведет к возникновению ошибки #ЧИСЛО!
Вычислить корень из отрицательного числа? Вас бы не понял не только Excel
Знаки «решетки» в ячейке Excel (#######)
В прошлом весьма распространенная «ошибка» MS Excel связанная с внезапным заполнением ячейки знаками решетки (#) могла быть вызвана тем, что в ячейку введено число которое не помещается в ней целиком (но только если ячейка имеет формат «числовой» или «дата»).
С появлением MS Office 2013 ошибка практически сошла на нет, так как «поумневший» Excel стал в большинстве случаев автоматически увеличивать ширину ячейки под число. Если же вы видите «решетки», проще всего избавиться от них увеличив ширину ячейки вручную.
Достаточно увеличить ширину столбца и проблема исчезнет
Исправление ошибок в MS Excel
Если вы знаете в каких случаях в экселе возникает та или иная ошибка, вы скорее всего сразу поймете чем она вызвана. Однако программа ещё более упрощает вам работу и выводит рядом с ошибочным значением спецсимвол в виде значка восклицательного знака в желтом ромбе. При нажатии на него, вам будет предложен список возможных действий по исправлению ошибки.
Нажмите на значок, чтобы получить помощь в исправлении ошибки
В первой строке появившегося контекстного меню вы увидите полное название ошибки, во второй сможете вызвать подробную справку по ней, но самое интересное скрывает в себе третий пункт: «Показать этапы вычисления…«.
«Показать этапы вычисления…» — программу не обманешь, точно выводит фрагмент формулы где допущена ошибка
Нажмите на него и в появившемся окне увидите тот самый фрагмент формулы где допущена ошибка — это особенно удобно, когда «распутывать» приходится целый клубок из громады вложенных друг в друга действий.
Источник: bussoft.ru
При делении на ноль программа ms excel выдает
В Excel при использовании формулы деления #DIV/0! ошибка появится, если делитель пуст или равен нулю, что может повлиять на точность последующих вычислений, как показано на снимке экрана ниже. Здесь представлены некоторые способы избежать ошибки #DIV/0! Ошибки в Экселе.
Исправить только ошибки деления на ноль
Исправить только #DIV/0! Ошибки, вы можете использовать функцию ЕСЛИ.
1. Выберите ячейку, в которую вы хотите поместить частное, затем нажмите Формула > Вставить функцию.
2. В диалоговом окне «Вставить функцию» выберите IF из Выберите функцию раздел. Нажмите OK.
3. в Аргументы функций диалоговое окно, выполните следующие действия:
In Логический_текст tрасширение, тип B2 = 0 (здесь B2 — это ссылка на ячейку первого делителя, измените ее на свою реальную потребность);
In Значение_если_истина текстовое поле, оставьте его пустым;
In Значение_если_ложь текстовое поле, тип A2 / B2 (где A2 — это ссылка на ячейку первого делимого, а B2 — это ссылка на ячейку первого делителя, измените их в соответствии с вашими реальными потребностями).
Нажмите OK.
4. Получено первое частное. Затем перетащите маркер автозаполнения вниз, чтобы получить все результаты. Вы можете видеть, что будут нули, если соответствующие делители пусты или равны нулю. И последующий расчет тоже рассчитывается правильно.
Примечание. Вы можете напрямую использовать функцию ЕСЛИ в ячейке, не применяя ее, включив функцию «Вставить функцию».
Исправить все ошибки
Если вы хотите исправить все ошибки в диапазоне ячеек, вы можете выбрать один из следующих способов.
Если вы работаете в Excel 2007 и более поздних версиях, вам может помочь функция ЕСЛИОШИБКА.
Выберите ячейку, которая помещает первое частное, затем используйте функцию ЕСЛИОШИБКА, как это;
// здесь A2 — это ссылка на ячейку первого делимого, а B2 — это ссылка на ячейку первого делителя, измените их в соответствии с вашими реальными потребностями, 0 — это возвращаемое значение, если A2/B2 возвращает какую-либо ошибку.
Нажмите Enter чтобы получить первый результат, затем перетащите автоматический маркер вниз, чтобы заполнить другие ячейки этой формулой.
Если вы используете версии Excel до Excel 2007, вы можете комбинировать функции ЕСЛИ и ЕОШИБКА для достижения этой цели.
// здесь A2 — это ссылка на ячейку первого делимого, а B2 — это ссылка на ячейку первого делителя, измените их в соответствии с вашими реальными потребностями, 0 — это возвращаемое значение, если A2/B2 возвращает какую-либо ошибку.
Если у вас есть Kutools for Excel установленный в Excel, Мастер условий ошибки Функция может быстро преобразовывать все типы ошибок в пробелы, указанное значение или значение выбранной ячейки.
1. Выберите диапазон, в который вы хотите преобразовать ошибки, затем нажмите Кутулс > Больше > Мастер условий ошибки.
2. в Мастер условий ошибки диалога, выберите Любое значение ошибки из Типы ошибок раскрывающемся списке, затем выберите нужный вариант в Отображение ошибки раздел. Нажмите Ok.
Теперь ошибки были преобразованы в указанные вами значения.
Работы С Нами Мастер условий ошибки, вы можете выбрать тип ошибки, которую вы хотите сделать в Типы ошибок .
Дополнительные сведения о Мастере условий ошибки см. здесь.
Для всех функций о Kutools for Excel, Пожалуйста, посетите здесь.
Бесплатно 30 -дневная пробная версия, пожалуйста скачать сейчас.
Прочие операции (статьи)
Как исправить/заблокировать цвет фона на листе
Иногда, когда вы делитесь листом с другими для редактирования, вам может потребоваться заблокировать цвет фона в диапазоне ячеек и запретить их изменение.
Как исправить сломанные ссылки
Иногда в рабочей книге вы можете создавать ссылки на другие рабочие книги для ссылки на некоторую соответствующую информацию. Но когда исходная книга переименовывается или перемещается в другое место, эти дополнительные ссылки не работают.
Excel: как создать или вставить закладку
Вы когда-нибудь представляли себе создание или вставку закладки для быстрого перехода к определенному диапазону данных, когда на листе или в книге много данных?
Как применить затенение к нечетным или четным (альтернативным) строкам / столбцам в Excel?
При разработке рабочего листа многие люди склонны применять затенение к нечетным или четным (альтернативным) строкам или столбцам, чтобы сделать рабочий лист более наглядным. В этой статье будут показаны два метода применения затенения к нечетным или четным строкам / столбцам в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .
Вкладка Office — предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Источник: ru.extendoffice.com
Как игнорировать #DIV/0! При использовании деления в Excel
В Excel, если вы попытаетесь разделить значение на ноль, вы получите #DIV/0! как результат.
Вы можете использовать следующий базовый синтаксис для выполнения деления, игнорируя #DIV/0! ценности:
=IFERROR( A2 / B2 , «»)
В этой конкретной формуле мы пытаемся разделить значение в ячейке A2 на значение в ячейке B2.Если B2 оказывается нулем, мы просто возвращаем в результате пробел.
В следующих примерах показано, как использовать этот синтаксис на практике.
Пример: игнорировать #DIV/0 при использовании деления в Excel
Предположим, мы пытаемся разделить значения в столбце A на значения в столбце B в этой конкретной электронной таблице Excel:
Обратите внимание, что для каждой ячейки в столбце C, где мы пытаемся разделить на ноль, мы получаем #DIV/0! как результат.
Чтобы игнорировать эту ошибку, мы можем ввести следующую формулу в ячейку C2 :
=IFERROR( A2 / B2 , «»)
Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце C:
Теперь для каждой ячейки в столбце C, где мы пытаемся разделить на ноль, мы просто получаем в результате пустое значение.
Обратите внимание, что мы могли бы также использовать следующую формулу, чтобы вернуть в результате ноль:
=IFERROR( A2 / B2 , 0)
На следующем снимке экрана показано, как использовать эту формулу на практике:
Теперь для каждой ячейки в столбце C, где мы пытаемся делить на ноль, мы просто получаем в результате ноль.
Или мы могли бы даже вернуть собственное сообщение при попытке деления на ноль:
=IFERROR( A2 / B2 , «Tried to divide by zero»)
На следующем снимке экрана показано, как использовать эту формулу на практике:
Теперь для каждой ячейки в столбце C, где мы пытаемся разделить на ноль, мы просто получаем в результате «Попытка деления на ноль».
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Источник: www.codecamp.ru