Содержание
- Как исправить ошибку #ПЕРЕНОС! в Excel
- Как исправить ошибку #ЗНАЧ! в Excel
- Как исправить ошибку #ПУСТО! в Excel
- Как исправить ошибку #ИМЯ? в Excel
Как исправить ошибку #ПЕРЕНОС! в Excel
Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.
Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.
Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.
У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.
- Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
- Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.
Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.
Диапазон для переноса содержит одну или более ячеек с значениями
Решение: очистить диапазон для переноса.
.gif)
Диапазон для переноса находится внутри таблицы
Решение 1: преобразовать таблицу в диапазон данных.
Для этого выполните следующие шаги:
- нажмите на любую ячейку в таблице,
- в меню в верхней части окна выберите «Конструктор»,
- выберите команду «Преобразовать в диапазон».
Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.
Решение 2: переместить формулу за границы таблицы.
Диапазон для переноса содержит объединенные ячейки
Решение: разделить ячейки внутри диапазона.
.gif)
Как исправить ошибку #ЗНАЧ! в Excel
Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:
- что-то не так с ячейкой (ячейками), на которую ссылается формула,
- что-то не так с самой формулой.
Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.
Математическая формула ссылается на текст
Формулы с математическими операторами могут вычислять только числа. Если одна или несколько ячеек, на которые ссылается формула, содержит текст, формула будет возвращать ошибку.
Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.
Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.
.png.700x416_q95.jpg)
Скриншот: Zapier.com
Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.
Одна или более ячеек содержат пробелы
В этих случаях ячейка выглядит пустой.
Решение: найти и заменить пробелы. Вот как это сделать:
- выделите диапазон ячеек, к которому обращается формула;
- нажмите на иконку с биноклем, нажмите «Найти и выделить»;
- в окне «Найти и заменить» выберите «Заменить»;
- в поле «Найти» вставьте пробелы;
- поле «Заменить на» оставьте пустым;
- нажмите «Заменить все».
Как исправить ошибку #ПУСТО! в Excel
Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.
Ячейка, на которую ссылается формула, удалена
Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.
Решение 1: Если ячейки удалены случайно, отмените это действие.
Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.
Формула содержит относительные ссылки
Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.
Каждый раз, когда формула с относительными ссылками переносится в другую ячейку, ссылки автоматически меняются. И если установить связь невозможно, происходит ошибка.
Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.
Решение: обновите формулу, чтобы включить в нее абсолютные значения. Это позволит созранить исходные ссылки, даже если формула будет перенесена. Для этого вставьте символ $ перед каждой буквой, обозначающей столбец, и номером ряда.
Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).
Как исправить ошибку #ИМЯ? в Excel
Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.
Название формулы содержит опечатку
Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.
Неверное название формулы
Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.
Решение: найти правильное имя формулы и обновить ее.
Вот как это сделать:
- выберите в меню раздел «Формулы»;
- нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
- выберите и вставьте нужную функцию;
- в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).
Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).
Источник.
Фото на обложке: Aihr.com
Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!
Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки. Возможно, понадобится попробовать одно или несколько решений, чтобы устранить конкретную ошибку.
Исправление ошибок определенных функций
Ваша функция не отображается в этом списке? Попробуйте другие решения, перечисленные ниже.
Проблемы с вычитанием
Если вы раньше не работали в Excel, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:
Вычтите одну ссылку на ячейку из другой
Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.
Или используйте функцию СУММ с положительными и отрицательными числами
Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).
Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.
-
Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
-
В Windows откройте панель управления «Региональные стандарты».
-
Windows 10. Встроке поиска введите регион и выберите панель управления Региональные стандарты.
-
Windows 8. На начальном экране введите регион, щелкните Параметры и выберите пункт Региональные стандарты.
-
Windows 7. Нажмите кнопку Пуск, введите регион и выберите пункт Язык и региональные стандарты.
-
-
На вкладке Форматы нажмите кнопку Дополнительные параметры.
-
Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
-
Нажмите кнопку ОК.
-
Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
-
Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».
-
Нажмите клавишу ВВОД.
-
Повторите эти действия для других ячеек, в которых возникает ошибка.
Вычтите одну ссылку на ячейку из другой
Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.
Или используйте функцию РАЗНДАТ
Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.
Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.
Проверка наличия начальных пробелов
-
Дважды щелкните дату, которая используется в формуле вычитания.
-
Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки:
Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.
-
Выделите столбец, содержащий дату, щелкнув его заголовок.
-
Выберите Данные > Текст по столбцам.
-
Дважды нажмите кнопку Далее.
-
На шаге 3 из 3 в мастере в разделе Формат данных столбца установите переключатель дата.
-
Выберите формат даты и нажмите кнопку Готово.
-
Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.
Проверка параметров даты на компьютере
Excel полагается на систему дат вашего компьютера. Если дата в ячейке введена в другой системе дат, Excel не распознает ее как настоящую дату.
Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если ввести дату в формате мм.дд.гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.
Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.
-
Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
-
Результат будет 31.12.2017.
-
Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или
+ 1 на Mac). -
Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.
+ 1 на Mac).Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что формула записана с учетом того, что в текстовой дате используется два символа для дней, два символа для месяцев и четыре символа для года. Возможно, вам понадобится откорректировать формулу под свою запись даты.
Проблемы с пробелами и текстом
Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.
1. Выберите ячейки, на которые указывают ссылки
Найдите ячейки, на которые ссылается формула, и выделите их. Во многих случаях рекомендуется удалить пробелы для всего столбца, так как можно заменить несколько пробелов одновременно. В этом примере, если щелкнуть E, выделится весь столбец.
2. Найдите и замените
На вкладке Главная щелкните Найти и выделить > Заменить.
3. Удалите пробелы
В поле Найти введите один пробел. Затем в поле Заменить удалите все, что там может быть.
4. Замените одно или все вхождения
Если вы уверены, что следует удалить все пробелы в столбце, нажмите кнопку Заменить все. Если вы хотите просмотреть и удалить пробелы по отдельности, можно сначала нажать кнопку Найти далее, а затем — Заменить, когда вы будете уверены, что пробел не нужен. После этого ошибка #ЗНАЧ! должна исчезнуть. Если нет — перейдите к следующему шагу.
5. Включите фильтр
Иногда из-за скрытых символов (а не просто пробелов) ячейка может выглядеть пустой, хотя на самом деле таковой не является. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.
6. Установите фильтр
Щелкните стрелку фильтра 
и снимите флажок Выделить все. Затем установите флажок Пустые.
7. Установите все флажки без названия
Установите все флажки, напротив которых ничего не указано, как на этом рисунке.
8. Выделите пустые ячейки и удалите их
Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Все скрытые символы в ячейках будут удалены.
9. Очистите фильтр
Щелкните стрелку фильтра и выберите команду Удалить фильтр из… для отображения всех ячеек.
10. Результат
Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.
Примечание: В этом примере обратите внимание, что ячейка E4 содержит зеленый треугольник, а число выравнивается по левому краю. Это значит, что число имеет текстовый формат. Это может вызвать проблемы в дальнейшем. Если вы заметили эту проблему, рекомендуем преобразовать числа из текстового формата в числовой.
Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: используйте функцию ЕТЕКСТ для проверки ячеек. Обратите внимание, что функция ЕТЕКСТ не устраняет ошибку, она просто находит ячейки, которые могут ее вызывать.
Пример с ошибкой #ЗНАЧ!
Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Здесь есть специальный знак, который выглядит как небольшой прямоугольник «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.
Этот же пример с функцией ЕТЕКСТ
Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.
Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.
Формулам с математическими операторами (такими как + и *) не всегда удается вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто пропускают текстовые значения и определяют все значения как числовые, избегая ошибки #ЗНАЧ! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).
Другие решения
Выберите ошибку
Сначала выделите ячейку с ошибкой #ЗНАЧ! .
Щелкните «Формулы» > «Вычислить формулу»
Щелкните Формулы > Вычислить формулу > Вычислить. Excel обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.
Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Она проверяет, есть ли ошибка, и если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула. Функция ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).
Предупреждение: Функция ЕСЛИОШИБКА скроет все ошибки, а не только ошибку #ЗНАЧ! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Используйте эту функцию, только если вы абсолютно уверены, что формула работает правильно.
Ячейка с ошибкой #ЗНАЧ!
Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.
Ошибка, скрытая функцией ЕСЛИОШИБКА
А вот эта же формула с добавленной функцией ЕСЛИОШИБКА. Ее можно прочитать как «Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами.» Помните, что также можно использовать «», чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: «Ошибка суммирования».
К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.
В какой-то момент подключения к данным могут стать недоступны. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале в нем будут только значения (без подключений). Для этого можно скопировать все ячейки и вставить только значения, щелкнув Главная > Вставить > Специальная вставка > Значения. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
In Excel it’s possible to locate formulas that have references to empty cells.
For example
A1 = 1
B1 = Empty
C1 = .5
D1 = A1 + B1 + C1
D1 will correctly calculate the value to be 1.5
However, error checking will identify that there is an error in the formula D1 and it can draw an arrow to this cell.
I need to alert the user if they have a reference to an empty cell.
When I try to record myself using the feature I get this
With Application.ErrorCheckingOptions
.EvaluateToError = False
.TextDate = False
.NumberAsText = False
.InconsistentFormula = False
.OmittedCells = False
.UnlockedFormulaCells = False
.ListDataValidation = False
.InconsistentTableFormula = False
End With
ActiveSheet.ClearArrows
The options for only checking for empty cells are setup correctly but the actual execution of the «Trace» function is completely ignored. Is there any way to cause this to happen automatically and then check the result of the test?
The function is «Formulas Tab» «Error Checking» «Formulas referring to empty cells» «Trace Empty Cell»
asked Jul 2, 2012 at 19:01
1
Here is a VBA method to check Formulas referring to empty cells. This will not only tell you which formula it is but also which cell is empty.
To test this, in Sheet1, Cell A1 put this formula
=F1+G1+H1
Keep H1 Empty and fill F1 and G1
In Cell A5, put this formula
=A1*D5
Keep the Cell D5 empty.
Now paste this code in a module.
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim RngFormulas As Range, fCell As Range, _
DPrcd As Range, aCell As Range
Set ws = Sheets("Sheet1")
With ws
On Error Resume Next
Set RngFormulas = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not RngFormulas Is Nothing Then
For Each fCell In RngFormulas
On Error Resume Next
Set DPrcd = fCell.DirectPrecedents
On Error GoTo 0
If Not DPrcd Is Nothing Then
For Each aCell In DPrcd
If IsEmpty(aCell.Value) Then
Debug.Print aCell.Address & " in " & _
fCell.Formula & " is empty"
End If
Next
End If
Next
End If
End With
End Sub
When you run the macro, you will see the output in the immediate window.
SNAPSHOT
answered Jul 2, 2012 at 19:54
Siddharth RoutSiddharth Rout
146k17 gold badges206 silver badges250 bronze badges
try
=IF(COUNTA(A1:A3),»ok»,»error») to find blanks in an array, and if that passes, then you can do whatever else needs to be done.
answered Jul 2, 2012 at 19:54
I wanted to run Formulas > Error Checking on every page of my large spreadsheet, but I also could not find any VBA code to execute it.
The best I came up with was to just select errors using Goto > Special, which needs error handling to ignore the error that occurs when no errors are found (and it does not work for #N/A constants).
Dim r As Range
On Error Resume Next
Set r = ActiveCell.SpecialCells(xlCellTypeFormulas, xlErrors)
If Err.Number <> 0 And Err.Number <> 1004 Then Stop
On Error GoTo 0
If Not r Is Nothing Then
If r.Count > 0 Then
r.Select
End If
End If
If an error other than 1004 «No cells were found.» occurs, I have not catered for it more than breaking the macro.
answered Oct 18, 2012 at 7:03
Mark HurdMark Hurd
10.6k10 gold badges68 silver badges100 bronze badges
In Excel it’s possible to locate formulas that have references to empty cells.
For example
A1 = 1
B1 = Empty
C1 = .5
D1 = A1 + B1 + C1
D1 will correctly calculate the value to be 1.5
However, error checking will identify that there is an error in the formula D1 and it can draw an arrow to this cell.
I need to alert the user if they have a reference to an empty cell.
When I try to record myself using the feature I get this
With Application.ErrorCheckingOptions
.EvaluateToError = False
.TextDate = False
.NumberAsText = False
.InconsistentFormula = False
.OmittedCells = False
.UnlockedFormulaCells = False
.ListDataValidation = False
.InconsistentTableFormula = False
End With
ActiveSheet.ClearArrows
The options for only checking for empty cells are setup correctly but the actual execution of the «Trace» function is completely ignored. Is there any way to cause this to happen automatically and then check the result of the test?
The function is «Formulas Tab» «Error Checking» «Formulas referring to empty cells» «Trace Empty Cell»
asked Jul 2, 2012 at 19:01
1
Here is a VBA method to check Formulas referring to empty cells. This will not only tell you which formula it is but also which cell is empty.
To test this, in Sheet1, Cell A1 put this formula
=F1+G1+H1
Keep H1 Empty and fill F1 and G1
In Cell A5, put this formula
=A1*D5
Keep the Cell D5 empty.
Now paste this code in a module.
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim RngFormulas As Range, fCell As Range, _
DPrcd As Range, aCell As Range
Set ws = Sheets("Sheet1")
With ws
On Error Resume Next
Set RngFormulas = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not RngFormulas Is Nothing Then
For Each fCell In RngFormulas
On Error Resume Next
Set DPrcd = fCell.DirectPrecedents
On Error GoTo 0
If Not DPrcd Is Nothing Then
For Each aCell In DPrcd
If IsEmpty(aCell.Value) Then
Debug.Print aCell.Address & " in " & _
fCell.Formula & " is empty"
End If
Next
End If
Next
End If
End With
End Sub
When you run the macro, you will see the output in the immediate window.
SNAPSHOT
answered Jul 2, 2012 at 19:54
Siddharth RoutSiddharth Rout
146k17 gold badges206 silver badges250 bronze badges
try
=IF(COUNTA(A1:A3),»ok»,»error») to find blanks in an array, and if that passes, then you can do whatever else needs to be done.
answered Jul 2, 2012 at 19:54
I wanted to run Formulas > Error Checking on every page of my large spreadsheet, but I also could not find any VBA code to execute it.
The best I came up with was to just select errors using Goto > Special, which needs error handling to ignore the error that occurs when no errors are found (and it does not work for #N/A constants).
Dim r As Range
On Error Resume Next
Set r = ActiveCell.SpecialCells(xlCellTypeFormulas, xlErrors)
If Err.Number <> 0 And Err.Number <> 1004 Then Stop
On Error GoTo 0
If Not r Is Nothing Then
If r.Count > 0 Then
r.Select
End If
End If
If an error other than 1004 «No cells were found.» occurs, I have not catered for it more than breaking the macro.
answered Oct 18, 2012 at 7:03
Mark HurdMark Hurd
10.6k10 gold badges68 silver badges100 bronze badges
Привет, друзья. Ошибки в Экселе часто пугают новичков, ведь их исправление не всегда понятно и очевидно. Сегодня поговорим об ошибке, #ДЕЛ/0!, которая иногда появляется там, где её совсем не ждёшь.
Эта ошибка возникает, когда ваша формула пытается произвести деление на ноль. Да, в высшей математике такая операция вернёт «бесконечность», а в большинстве языков программирования – Infinity. Но в Экселе это вызовет ошибку, которая «поломает» все связанные расчёты. Давайте рассмотрим основные причины описанного.
Формула ссылается на пустую ячейку, или нулевое значение
Это простейший случай, который очень легко отследить.
В примере на картинке мы видим, что делитель – пустое значение (или ноль). Это могло случиться из-за того, что:
- Эта ячейка еще не заполнена, но это будет сделано позднее
- В ячейке правильное значение, равное нулю
Первое, что нужно сделать – проверить, все ли данные правильно внесены в таблицу, ввести или исправить некорректные величины.
Если все данные корректны, ошибку можно обойти с помощью формул, но об этом ниже в статье.
Формула среднего значения без подходящих аргументов
Когда вы пользуетесь функциями расчёта среднего значения СРЗНАЧ, СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, программа суммирует элементы и делит сумму на их количество. При том, если подходящих элементов для суммирования в диапазоне нет, их сумма и количество будет равны нулю, функция вернет #ДЕЛ/0!
В примере выше я попытался посчитать средний показатель Сидорова, но такого человека нет в списке, функция вернула злосчастную ошибку.
Чтобы это исправить, нужно либо проверить список работников на ошибки и добавить Сидорова, либо применять формулы обхода.
Формула ссылается на ячейку, в которой содержится ошибка
Если ваша формула ссылается на ячейку, в которой ошибка #ДЕЛ/0!, она тоже вернёт эту ошибку. Смотрите, на примере:
В ячейке F12 – функция суммирования, там вообще нет никакого деления, но есть ошибка. Внутри диапазона суммирования, в ячейке F5, программа не смогла вычислить среднее значение, что и вызвало «цепное» распространение ошибки. В этом случае, нужно проверить внутренние формулы на предмет корректности, исправить и повторить расчёт.
Обход деления на ноль с помощью функции ЕСЛИ
Сейчас расскажу, как с помощью функций устранить ошибки и сохранить корректность расчёта. Но перед их применением следует убедиться, что ошибки нельзя исправить другими способами.
Применяя функцию ЕСЛИ, можно проверить значение делителя. Вот так:
=ЕСЛИ(делитель=0; 0; делимое/делитель)
Формула проконтролирует значение делителя. Если он нулевой – вернёт ноль. Если нет – отношение делимого к делителю:
Видим, в примере формула отследила возможную ошибку и вывела вместо нее ноль.
Перехват с помощью функции ЕСЛИОШИБКА
Вы можете использовать функцию ЕСЛИШИБКА для «отлова» любых ошибок, не только этой. Достаточно обернуть вашу формулу этой функцией:
Если результат выражения – ошибка, функция вернет «значение_если_ошибка». В противном случае, результат вычисления выражения. Вот так:
Всегда с осторожностью используйте формулы и функции отлова ошибок. Они не разбираются, корректны ли исходные данные. Возможно, в таблице опечатка, а вы её не исправите, т.к. не увидите сообщения об ошибке!
Неплохим решением для минимизации неправильного ввода данных будет применение выпадающего списка.
А у меня на этом всё. Если у вас что-то не получается по теме статьи – пишите комментарии с вопросами!
Исправление ошибки #DIV/0! #BUSY!
Ошибка #ДЕЛ/0! возникает в Microsoft Excel, когда число делится на ноль (0). Это происходит, если вы вводите простую формулу, например =5/0, или если формула ссылается на ячейку с 0 или пустую ячейку, как показано на этом рисунке.
Есть несколько способов исправления этой ошибки.
Убедитесь в том, что делитель в функции или формуле не равен нулю и соответствующая ячейка не пуста.
Измените ссылку в формуле, чтобы она указывала на ячейку, не содержащую ноль (0) или пустое значение.
Введите в ячейку, которая используется в формуле в качестве делителя, значение #Н/Д, чтобы изменить результат формулы на #Н/Д, указывая на недоступность делителя.
Во многих случаях это #DIV/0! их можно избежать, поскольку формулы ожидают ввода от вас или другого пользователя. В этом случае сообщение об ошибке отображаться не нужно, поэтому для ее подавления можно использовать несколько способов обработки ошибок.
Оценка знаменателя на наличие нуля или пустого значения
Самый простой способ избавиться от ошибки #ДЕЛ/0! — воспользоваться функцией ЕСЛИ для оценки знаменателя. Если он равен нулю или пустой, в результате вычислений можно отображать значение «0» или не выводить ничего. В противном случае будет вычислен результат формулы.
Например, если ошибка возникает в формуле =A2/A3, можно заменить ее формулой =ЕСЛИ(A3;A2/A3;0), чтобы возвращать 0, или формулой =ЕСЛИ(A3;A2/A3;»»), чтобы возвращать пустую строку. Также можно выводить произвольное сообщение. Пример: =ЕСЛИ(A3;A2/A3;»Ожидается значение.»). Он сообщает Excel ЕСЛИ(существует, то вернуть результат формулы, в противном случае игнорировать ее).
Чтобы подавления этой #DIV/0, используйте #DIV. #BUSY!
Вы также можете скрыть эту ошибку, вложенную операцию деления в функцию ЕСЛИERROR. При использовании A2/A3 можно также использовать =ЕСЛИERROR(A2/A3;0). Он сообщает Excel, если формула возвращает ошибку, возвращается 0, в противном случае возвращается результат формулы.
В версиях до Excel 2007 можно использовать синтаксис ЕСЛИ(ЕОШИБКА()): =ЕСЛИ(ЕОШИБКА(A2/A3);0;A2/A3) (см. статью Функции Е).
Примечание. Как методы ЕСЛИERROR, так и ЕСЛИ(ЕERROR()) — это подавляющие обработчики всех ошибок, а не только #DIV/0!. Прежде чем применять обработку ошибок, убедитесь, что формула работает правильно. В противном случае вы можете не понимать, что формула работает неправильно.
Совет: Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.
У вас есть вопрос об определенной функции?
Помогите нам улучшить Excel
У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Внесение даже небольшого изменения на рабочем листе Excel может привести к образованию ошибок в других ячейках. К примеру, вы можете случайно ввести значение в ячейку, которая раньше содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы не сможете обнаружить ее, пока не сделаете какие-нибудь изменения на листе.
Ошибки в формулах делятся на несколько категорий:
Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.
Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.
Ошибки неправильных ссылок: Логика формул верна, но формула использует некорректную ссылку на ячейку. Простой пример, диапазон данных для суммирования в формуле СУММ может содержать не все элементы, которые вы хотите суммировать.
Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?
Циклические ошибки: Циклические ссылки возникают, когда формула ссылается на саму себя, прямо или косвенно. Циклические ссылки иногда могут быть полезны, но зачастую они указываю на наличие проблемы.
Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.
Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.
Проще всего найти и скорректировать синтаксические ошибки. Чаще всего, вы знаете, когда формула содержит синтаксическую ошибку. К примеру, Excel не даст ввести формулу с несогласованными скобками. Иные ситуации синтаксических ошибок приводят к выводу следующих ошибок в ячейке листа.
Ошибка #ДЕЛ/0!
Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!
Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).
Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!
Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:
Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.
Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:
Ошибка #Н/Д
Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.
Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.
Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().
Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:
Ошибка #ИМЯ?
Excel может вернуть ошибку #ИМЯ? в следующих случаях:
- Формула содержит неопределенный именованный диапазон
- Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
- Формула содержит текст не заключенный в кавычки
- Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
- Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена
Ошибка #ПУСТО!
Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.
Ошибка #ЧИСЛО!
Ошибка #ЧИСЛО! будет возвращена в следующих случаях:
- В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
- В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
- Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
- Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.
Ошибка #ССЫЛКА!
Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:
- Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
- Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
- Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
- Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!
Ошибка #ЗНАЧ!
Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:
- Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
- Аргумент функции является диапазоном, когда он должен быть одним значением
- Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
- Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
- Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива
Вам также могут быть интересны следующие статьи
5 комментариев
Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.
Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru
Людмила, есть вариант, что мастер диаграмм перестает быть доступен, если книга находится в общем доступе. Если это ваш случай, сделайте монопольный доступ в книге, вставляйте диаграммы и снова делайте книгу общей.
Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:
Причем заметьте, что итоги в нашей таблице тоже уже не считаются — одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.
Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.
Синтаксис функции следующий:
=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )
Так, в нашем примере можно было бы все исправить так:
Все красиво и ошибок больше нет.
Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:
Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.
Какие существуют обозначения ошибок и способы их исправления?
Если формула содержит ошибку, то «Excel 2007» отобразит специальное сообщение об ошибке. При этом каждый тип ошибки обозначается своим сообщением, вызывается разными причинами и соответственно требует различных способов разрешения.
##### — Что обозначает и как исправить?
Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.
В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).
Во втором же случае надо:
- проверить формулу, если вычисляется число дней между двумя датами;
- если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.
#ЗНАЧ! — Что обозначает и как исправить?
Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ! .
#ДЕЛ/0! — Что обозначает и как исправить?
Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.
- В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
- Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
- Нажмите Enter или кнопку «Ввод» на строке формул.
#ИМЯ? — Что обозначает и как исправить?
Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.
1 вариант
Если используется имя, которое не было определено, то необходимо сделать следующее:
- В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
- В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.
Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».
2 вариант
Если существует ошибка в написании имени, то необходимо проверить его орфографию.
- В окне открытого листа нажмите клавишу F3.
- В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
- Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
- Для закрепления нажмите клавишу Enter.
3 вариант
Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .
- В окне открытого листа выделите ячейку с ошибкой в написании функции.
- Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
- В списке команд выберите пункт «Изменить в строке формул».
- На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
- Закрепите результат щелчком по клавише Enter.
4 вариант
Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .
5 вариант
Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .
6 вариант
Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).
#Н/Д — Что обозначает и как исправить?
Эти символы сообщают, что нужное значение не доступно для функции или формулы.
1 вариант
Если в формулу были введены недостающие данные, а также #Н/Д или НД() , то #Н/Д необходимо заменить новыми данными.
2 вариант
Если в функциях ГПР , ПРОСМОТР , ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.
3 вариант
Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.
4 вариант
Если в формуле используется недоступная в данный момент функция, то необходимо проверить, что книга, использующая функцию листа, открыта, а также то, что функция правильно работает.
5 вариант
Если для просмотра значений в несортированной таблице используются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.
6 вариант
Если в формуле массива используется аргумент, не соответствующий диапазону, указанному в формуле массива, то необходимо проверить диапазон ссылок формулы на соответствие числу строк и столбцов или ввести формулу массива в меньшее число ячеек.
7 вариант
Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.
#ССЫЛКА! — Что обозначает и как исправить?
Эти символы сообщают о неверной ссылке на ячейку.
1 вариант
Если ячейка, на которую ссылается формула, была удалена или же в данную ячейку помещено значение скопированных ячеек, то необходимо изменить формулу с учетом новых ссылок.
2 вариант
Если используется функция OLE, связанная с незапущенной программой, то необходимо запустить требуемую программу.
3 вариант
Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.
4 вариант
Если используется макрос, вызывающий макрофункцию, которая при определенных вариантах выдает значение #ССЫЛКА! . Необходимо проверить аргумент функции и удостовериться, что он ссылается на допустимые ячейки или диапазоны ячеек.
#ЧИСЛО! — Что обозначает и как исправить?
Это сообщение об использовании неправильных числовых значений в формуле или функции.
1 вариант
Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.
2 вариант
Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.
3 вариант
Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.
#ПУСТО! — Что обозначает и как исправить?
Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.
1 вариант
Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:
- для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20) .
- для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20) .
2 вариант
Если указанные диапазоны не имеют общих ячеек, то необходимо изменить ссылки, добиваясь нужного пересечения.
Исправление
распространенных ошибок в формулах
Для
проверки формул на наличие ошибок можно
применять определенные правила. Эти
правила действуют как средство проверки
орфографии, которое проверяет данные,
введенные в ячейки, на наличие ошибок.
Эти правила не гарантируют полного
отсутствия ошибок на листе, но помогают
избежать распространенных ошибок.
Каждое из этих правил можно включать и
выключать независимо от других.
Ошибки
можно отмечать и исправлять двумя
способами: по одной ошибке (как в средстве
проверки орфографии) или немедленно по
мере их возникновения на листе во время
ввода данных. В обоих случаях при
обнаружении ошибки в ячейке в ее левом
верхнем углу появляется треугольник.
Ячейка
с ошибкой в формуле
Ошибку
можно устранить, используя предлагаемые
MS
Excel варианты, или пропустить, нажав
кнопку Пропустить ошибку. Если пропустить
ошибку в определенной ячейке, то при
последующих проверках наличие ошибки
в этой ячейке определяться не будет.
Однако состояние всех пропущенных
ошибок можно сбросить, чтобы они
отображались снова.
Включение
и отключение правил проверки ошибок
-
На вкладке Файл
выберите команду Параметры,
а затем — категорию Формулы. -
В области Правила
контроля ошибок
установите или снимите флажок для
любого из перечисленных далее правил.
-
Ячейки, которые
содержат формулы, приводящие к
ошибкам. В
данной формуле используется неправильный
синтаксис, аргументы или типы данных.
Значения таких ошибок: #ДЕЛ/0!, #Н/Д,
#ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
Каждое из этих значений ошибки вызывается
различными причинами, и такие ошибки
устраняются разными способами.
Примечание Если
значение ошибки ввести непосредственно
в ячейку, оно будет сохранено как значение
ошибки, но отмечаться как ошибка не
будет. Однако если на эту ячейку будет
ссылаться формула из другой ячейки, для
этой ячейки будет возвращено значение
ошибки.
-
Несогласованная
формула в вычисляемом столбце
таблицы. Вычисляемый
столбец может содержать формулы,
отличающиеся от формулы столбца, что
приводит к возникновению исключения.
Исключения вычисляемого столбца
возникают при указанных ниже действиях.-
Ввод в ячейку
вычисляемого столбца данных, отличных
от формулы. -
Ввод в ячейку
вычисляемого столбца формулы с
последующим нажатием кнопкиОтменить
на
панели
быстрого доступа. -
Ввод новой формулы
в вычисляемый столбец, который уже
содержит одно или несколько исключений. -
Копирование в
вычисляемый столбец данных, которые
не соответствуют формуле вычисляемого
столбца.
-
на
Примечание Если
копируемые данные содержат формулу,
эта формула перезапишет данные в
вычисляемом столбце.
-
Перемещение или
удаление ячейки в другой области
листа, на которую ссылается одна из
строк вычисляемого столбца. -
Ячейки, которые содержат
годы, представленные 2 цифрами. Ячейка
содержит дату в текстовом формате,
которая в случае использования в
формулах может быть отнесена к
неправильному веку. Например, дата в
формуле =ГОД(«1.1.31») может относиться
как к 1931-му, так и к 2031-му году. Это
правило служит для выявления неоднозначных
дат в текстовом формате. -
Числа,
отформатированные как текст или с
предшествующим апострофом. Ячейка
содержит числа, хранящиеся в виде
текста. Обычно это является следствием
импорта данных из других источников.
Числа, хранящиеся в виде текста, могут
стать причиной неправильной сортировки,
поэтому лучше преобразовать их в
числовой формат. -
Формулы,
несогласованные с остальными формулами
в области. Формула
не соответствует шаблону других смежных
формул. В большинстве случаев формулы,
расположенные в соседних ячейках,
отличаются только используемыми
ссылками. В приведенном, далее примере,
состоящем из четырех смежных формул,
приложение MicrosoftExcel
показывает ошибку в формуле =СУММ(A10:F10),
поскольку значения в смежных формулах
изменились на одну строку, а в формуле
=СУММ(A10:F10)
— на 8 строк. В данном случае, ожидаемой
формулой является =СУММ(A3:F3). -
Если используемые в формуле
ссылки не соответствуют ссылкам в
смежных формулах, приложение
MicrosoftExcelвыводит
ошибку. -
Формулы, не
охватывающие смежные ячейки. Формула
может не включать ссылки на данные,
вставленные между исходным диапазоном
и ячейкой с формулой. Это правило
позволяет сравнить ссылку в формуле
с фактическим диапазоном ячеек, смежных
с ячейкой формулы. Если смежные ячейки
содержат дополнительные значения и
не являются пустыми, MicrosoftExcelвыведет
рядом с формулой ошибку.
Например, в случае
применения этого правила приложение
MicrosoftExcel
выведет ошибку рядом с формулой
=СУММ(A2:A4),
поскольку между указанным в формуле
диапазоном ячеек и ячейкой с формулой
(A8) находятся заполненные ячейки A5, A6 и
A7, на которые также должна быть ссылка
в формуле.
-
Незаблокированные
ячейки, содержащие формулы. Формула
не заблокирована в целях ее защиты. По
умолчанию все ячейки блокируются, но
защита была снята для этой ячейки.
Когда формула защищена, ее нельзя
изменить, не сняв защиту. Убедитесь,
что защита этой ячейку действительно
не требуется. Блокирование ячеек,
содержащих формулы, позволяет защитить
их от изменений и избежать возникновения
ошибок в будущем. -
Формулы, которые
ссылаются на пустые ячейки. Формула
содержит ссылку на пустую ячейку. Это
может привести к неверным результатам,
как показано в приведенном далее
примере.
Предположим, нужно
вычислить среднее значение для чисел
из указанного ниже столбца ячеек. Если
третья ячейка будет пустой, она не будет
учтена при выполнении вычисления, и
результатом будет 22,75. Если третья ячейка
будет содержать значение 0, результатом
будет 18,2.
-
В таблицу введены
недопустимые данные. В
таблице произошла ошибка проверки.
Чтобы просмотреть параметры проверки
для ячейки, на вкладке Данные
в группе Работа
с данными
нажмите кнопку Проверка
данных.
Последовательное
исправление распространенных ошибок
в формулах
Внимание! Если
на листе уже выполнялась проверка
ошибок, то ошибки, которые были пропущены,
не будут отображаться, пока их состояние
не будет сброшено.
-
Выберите лист, на
котором требуется проверить наличие
ошибок. -
Если расчет листа
выполнен вручную, нажмите клавишу F9,
чтобы выполнить расчет повторно. -
На вкладке Формулы
в группе Зависимости
формул
нажмите кнопку группы Проверка
наличия ошибок.
В случае обнаружения
ошибок открывается диалоговое окно
Контроль
ошибок.
-
Чтобы повторно
проверить пропущенные ранее ошибки,
выполните указанные ниже действия.-
Нажмите кнопку
Параметры. -
В разделе Контроль
ошибок
нажмите кнопку Сброс
пропущенных ошибок. -
Нажмите кнопку
ОК. -
Нажмите
кнопкуПродолжить.
-
Примечание Сброс
пропущенных ошибок применяется ко всем
ошибкам, которые были пропущены на всех
листах активной книги.
-
Расположите
диалоговое окно Контроль
ошибок
непосредственно под строкой
формул .
-
Нажмите одну из
управляющих кнопок в правой части
диалогового окна. Доступные действия
зависят от типа ошибки.
Примечание Если
нажать кнопку Пропустить
ошибку,
помеченная ошибка при последующих
проверках будет пропускаться.
-
Нажмите кнопкуДалее.
-
Выполняйте эти
действия, пока проверка ошибок не будет
завершена.
К
началу страницы
Пометка
и исправление распространенных ошибок
формул на листе
-
Откройте вкладку
Файл. -
Нажмите кнопку
Параметры
и выберите категорию Формулы. -
Убедитесь, что в
области Контроль
ошибок
установлен флажокВключить
фоновый поиск ошибок. -
Чтобы изменить
цвет треугольника, которым помечаются
ошибки, выберите нужный цвет в поле
Цвет
индикаторов ошибок.
Нажмите кнопку «ОК», чтобы закрыть
диалоговое окно Параметры
Excel. -
Чтобы исправить
ошибку на листе, выберите ячейку с
треугольником в левом верхнем углу. -
Нажмите появившуюся
рядом с ячейкой кнопку Контроль
ошибоки
выберите нужный пункт. Доступные команды
зависят от типа ошибки. Первый пункт
содержит описание ошибки.
и
Если нажать
кнопкуПропустить
ошибку,
помеченная ошибка при последующих
проверках будет пропускаться.
-
Повторите два
предыдущих действия.
Исправление
значения ошибки
Если формула
содержит ошибку, которая не позволяет
правильно выполнить вычисления, будет
показано значение ошибки, например
#####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!,
#ССЫЛКА! и #ЗНАЧ!. Каждый тип ошибки
вызывается разными причинами, и такие
ошибки устраняются разными способами.
В таблице ниже
приведены ссылки на статьи с подробным
описанием ошибок и краткий вступительный
обзор каждой ошибки.
|
Ссылка на статью |
Описание |
|
Исправление |
Эта ошибка
Так, при вычислении |
|
Исправление |
Эта ошибка |
|
Исправление |
Эта ошибка |
|
Исправление |
Эта ошибка |
|
Исправление |
Эта ошибка |
|
Исправление |
Эта ошибка
Например, области |
|
Исправление |
Эта ошибка |
|
Исправление |
Эта ошибка |
-
Вычисления
в таблицах
Ознакомление
с правилами ввода простых формул
Формулы представляют
собой выражения, по которым выполняются
вычисления значений на листе. Формула
начинается со знака равенства (=).Формула
может содержать некоторые или все из
следующих элементов: функции, ссылки,
операторы и константы.
Части формулы
Функции.
Функция ПИ() возвращает значение числа
Пи: 3,142…
Ссылки.
A2 возвращает значение ячейки A2.
Константы.
Числа или текстовые значения, введенные
непосредственно в формулу, например 2.
Операторы.
Оператор ^ («крышка») возводит число
в степень, а оператор * («звездочка»)
перемножает два или более числа.
Исправление
распространенных ошибок во время ввода
формул
В приведенной ниже
таблице перечислены некоторые наиболее
распространенные ошибки, совершаемые
пользователями при вводе формул, и
способы их исправления.
|
Убедитесь, что… |
Дополнительные |
|
Каждая формула |
Если знак равенства |
|
Все открывающие |
Убедитесь, что |
|
Для указания |
При указании |
|
Введены все |
Некоторым функциям |
|
Введены аргументы |
Для некоторый |
|
Количество |
Внутри одной |
|
Имена других |
Если в формуле |
|
Ссылаясь на |
Например, чтобы |
|
Включен путь |
Убедитесь, что
Ссылка на книгу
Например, формула
Если книга, на
Примечание Если |
|
Числа введены |
Не форматируйте
Например, если |
|
Избегайте |
При попытке |
Формулы
Вычисления
в Excel
выполняются с помощью Формул. Формула
начинается со знака равно (=)
и состоит из элементов (операндов:
константы, ссылки на ячейки или диапазоны
ячеек, функции), соединенных операторами
(знаки операций).
Применение
операторов в формулах
Оператор. Знак или
символ, задающий тип вычисления в
выражении. Существуют математические,
логические операторы, операторы сравнения
и ссылок
Арифметические
операторы.
|
Арифметическийоператор |
Значение |
Пример |
|
+ |
Сложение |
23+3 |
|
– (знак |
Вычитание |
35–166 |
|
* |
Умножение |
23*(-321) |
|
/ |
Деление |
3/3 |
|
% |
Процент |
20% |
|
^ |
Возведение |
3^2 |
Операторы
сравнения.
Используются для
сравнения двух значений. Результатом
сравнения является логическое значение:
либо ИСТИНА, либо ЛОЖЬ.
|
Операторсравнения |
Значение |
Пример |
|
= (знак |
Равно |
A1=B1 |
|
> |
Больше |
A1>B1 |
|
< |
Меньше |
A1<B1 |
|
>= |
Больше |
A1>=B1 |
|
<= |
Меньше |
A1<=B1 |
|
<> |
Не |
A1<>B1 |
Текстовый оператор
конкатенации.
Операция (&) используется для объединения
нескольких текстовых строк в одну
строку.
|
Текстовый |
Значение |
Пример |
|
& |
Объединение |
Выражение «Северный «Северный |
Оператор
ссылки.
Для описания ссылок на диапазоны ячеек
используются следующие операторы.
|
Оператор |
Значение |
Пример |
|
: |
Оператор |
B5:B15 |
|
, |
Оператор |
СУММ(B5:B15,D5:D15) |
|
(пробел) |
Оператор задает |
A6:D6 |
Порядок выполнения
действий в формулах
Если
в формуле содержится несколько операторов,
то порядок вычислений в MicrosoftExcel
определяется приоритетом операторов.
Раньше выполняются операции с более
высоким приоритетом, затем — с менее
высоким. Для изменения порядка выполнения
операций используются скобки. Вначале
вычисляются те части формулы, которые
заключены в скобки, затем — остальные
части.
|
Оператор |
Описание |
|
|
|
: (пробел) , |
Операторы |
|
|
– |
Унарный |
|
|
% |
Процент |
|
|
^ |
Возведение |
|
|
* и / |
Умножение |
|
|
+ и – |
Сложение |
|
|
& |
Объединение |
|
|
= <><= |
Сравнение |
Адреса,
которые используются в формулах, получили
название ссылок.
Ссылки позволяют связывать между собой
любые ячейки
электронной
таблицы и проводить необходимую обработку
табличных
данных.
Ссылка
— это адрес объекта (ячейки, строки,
столбца, диапазона),
используемый при записи формулы.
Способы
ввода формул: набрать
ее
|
вручную |
с |
|
в Строка
|
В |
— отмена
—
—Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
23.08.2019159.74 Кб3оп.doc