Поскольку Excel — это программа электронных таблиц, а не просто табличный редактор, вычисления являются очень важной частью ее функциональности. Соответственно, настройка параметров вычислений (формул), выполняемая с помощью элементов управления категории Формулы, — процесс, знание которого может весьма облегчить вам жизнь, а незнание — сделать ее невыносимой.
Все элементы управления этой категории распределены по группам Параметры вычислений, Работа с формулами, Контроль ошибок и Правила контроля ошибок. Параметры вычислений задаются в одноименном разделе.
Вычисления в книге — эта группа переключателей определяет режим вычислений:
- Автоматически — каждый раз, когда вы меняете значение в ячейке, которая используется в формуле в качестве параметра, формула пересчитывается.
- Автоматически, кроме таблиц данных — пересчет выполняется автоматически для всех формул, кроме тех, результатом выполнения которых являются таблицы данных.
- Вручную — пересчет выполняется только после нажатия клавиши F9 или выбора команды Пересчет в группе Вычисление на вкладке Формула ленты инструментов.
Пересчитывать книгу перед сохранением — перед сохранением книги все формулы в ней будут пересчитаны, чтобы сохранить актуальные значения (это имеет смысл, только если выключен режим автоматического вычисления).
Включить итеративные вычисления — итеративными называются вычисления, в которых для вычисления значений задается шаг изменения какого-то начального значения. Применяются такие вычисления для того, чтобы найти сочетание параметров, удовлетворяющее заданному условию. Путем итеративных вычислений можно решать самые разные задачи, от поиска корня n-й степени до формирования оптимального бюджета. Итерации используются в таких инструментах Excel, как инструменты поиска решения и подбора параметра. Более подробно мы рассмотрим эти инструменты в следующих публикациях.
- Предельное число итераций — в это поле вводится значение, определяющее, сколько раз с подстановкой разных значений будет выполняться пересчет листа. Чем больше итераций вы зададите, тем больше времени уйдет на пересчет. В то же время большое число итераций позволит получить более точный результат. Поэтому это значение надо подбирать, основываясь на реальной потребности. Если для вас важно получить точный результат любой ценой, а формулы в книге достаточно сложные, вы можете установить значение 10 000, щелкнуть на кнопке пересчета и уйти заниматься другими делами. Рано или поздно пересчет будет закончен. Если же вам важно получить результат быстро, то значение надо установить поменьше.
- Относительная погрешность — максимальная допустимая разница между результатами пересчетов. Чем это число меньше, тем точнее будет результат и тем больше потребуется времени на его получение.
В разделе Работа с формулами задаются параметры, управляющие вводом и вычислением формул.
Стиль ссылок R1C1 — переход от стандартного для Excel именования ячеек (A1, D6, E4 и т. д.) к стилю ссылок, при котором нумеруются не только строки, но и столбцы. При этом буква R (row) означает строку, а C (column) — столбец. Соответственно, запись в новом стиле R5C4 будет эквивалентна записи D5 в стандартном стиле.
Автозавершение формул — в этом режиме предлагаются возможные варианты функций во время ввода их в строке формул (рис. 2.11).
Рис. 2.11. Автозавершение формул
Использовать имена таблиц в формулах — вместо того, чтобы вставлять в формулы диапазоны ссылок в виде A1:G8, вы можете выделить нужную область, задать для нее имя и затем вставить это имя в формулу. На рис. 2.12 приведен такой пример — сначала был выделен диапазон E1:I8, этому диапазону было присвоено имя MyTable, затем в ячейке D1 была создана формула суммирования, в которую в качестве аргумента передано имя данного диапазона.
Рис. 2.12. Использование имени таблицы в формуле
Использовать функции GetPivotData для ссылок в сводной таблице — в этом режиме данные из сводной таблицы выбираются при помощи вышеуказанной функции. Если вы вставляете в формулу ссылку на ячейку, которая расположена в сводной таблице, то вместо ссылки на ячейку будет автоматически вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вам все же нужна именно ссылка на ячейку, этот флажок нужно сбросить.
С помощью элементов управления раздела Контроль ошибок настраивается режим контроля ошибок:
- Включить фоновый поиск ошибок — ошибки будут обнаруживаться и отображаться по мере ввода значений и формул, не дожидаясь пересчета.
- Цвет индикаторов ошибок — выбор цвета, которым будет помечаться ячейка с ошибкой.
В разделе Правила контроля ошибок расположены флажки, управляющие режимом контроля ошибок. Я опишу не все флажки, а только те, названия которых недостаточно полно характеризуют их назначение:
- Ячейки, которые содержат формулы, приводящие к ошибкам — поиск ячеек, в которых использован неверный синтаксис, недопустимое для данной формулы число или тип аргументов.
- Несогласованная формула в вычисляемом столбце таблицы — формулы, расположенные в вычисляемом столбце, обычно получаются в результате заполнения столбца одной и той же формулой по образцу. Это значит, что формулы в вычисляемом столбце отличаются друг от друга только ссылками на соответствующие ячейки, а сами ссылки обычно отличаются друг от друга на один шаг. Если это правило нарушается, то в данном режиме формула помечается как ошибочная.
- ормулы, несогласованные с остальными формулами в области — этот режим аналогичен предыдущему, но только не для столбца, а для области.
- Формулы, не охватывающие смежные ячейки — эта ошибка возникает тогда, когда вы создаете формулу для диапазона ячеек, а затем в этот диапазон добавляете ячейки. Формула не всегда автоматически изменяет ссылки, и, например, если вы суммировали 4 ячейки в столбце, а затем вставили пятую, она в сумму не войдет. Такая ситуация будет считаться ошибкой.
- Незаблокированные ячейки, содержащие формулы — для ячеек, в которые были введены формулы, автоматически включается защита. Если вы затем редактировали формулу или снимали режим защиты с диапазона ячеек, то ячейка с формулой может оказаться незащищенной. Данная ситуация будет оцениваться как ошибка.
Формула не охватывает смежные ячейки что это
Эта ошибка означает, что формула в ячейке не соответствует шаблону формул рядом с ней.
Выяснение причины несоответствия
Щелкните Формулы > Показать формулы.
Это позволяет просматривать в ячейках формулы, а не вычисляемые результаты.
Сравните несогласованную формулу с соседними формулами и исправьте любые случайные несоответствия.
По завершении щелкните Формулы > Показать формулы. Это переключит отображение на вычисляемые результаты для всех ячеек.
Если это не помогает, выберите смежную ячейку, в которой отсутствует проблема.
Щелкните Формулы > Влияющие ячейки.
Выделите ячейку, содержащую проблему.
Щелкните Формулы > Влияющие ячейки.
Сравните синие стрелки или синие диапазоны. Исправьте все проблемы с несогласованной формулой.
Щелкните Формулы > Убрать стрелки.
Другие решения
Выделите ячейку с несогласованной формулой и, удерживая клавишу SHIFT, нажимайте одну из клавиш со стрелками. В результате несогласованная формула будет выделена вместе с другими. Затем выполните одно из указанных ниже действий.
Если выделены ячейки снизу, нажмите клавиши CTRL+D, чтобы заполнить формулой ячейки вниз.
Если выделены ячейки сверху, выберите Главная > Заполнить > Вверх, чтобы заполнить формулой ячейки вверх.
Если выделены ячейки справа, нажмите клавиши CTRL+R, чтобы заполнить формулой ячейки справа.
Если выделены ячейки слева, выберите Главная > Заполнить > Влево, чтобы заполнить формулой ячейки слева.
При наличии других ячеек, в которые нужно добавить формулу, повторите указанную выше процедуру в другом направлении.
Выделите ячейку с проблемой.
Нажмите кнопку 
и выберите вариант Скопировать формулу сверху или Скопировать формулу слева.
Если это не подходит и требуется формула из ячейки снизу, выберите Главная > Заполнить > Вверх.
Если требуется формула из ячейки справа, выберите Главная > Заполнить > Влево.
Если формула не содержит ошибку, можно ее пропустить:
Щелкните Формулы > Поиск ошибок.
Нажмите кнопку Пропустить ошибку.
Нажмите кнопку ОК или Далее для перехода к следующей ошибке.
Примечание: Если не нужно использовать в Excel этот способ проверки на несогласованные формулы, закройте диалоговое окно «Поиск ошибок». Выберите Файл > Параметры > Формулы. В нижней части снимите флажок Формулы, не согласованные с остальными формулами в области.
На компьютере Mac выберите Excel > Параметры > Поиск ошибок и снимите флажок Формулы, несогласованные с формулами в смежных ячейках.
Если формула не похожа на смежные формулы, отображается индикатор ошибки. Это не всегда означает, что формула неправильная. Если формула неправильная, проблему часто можно решить, сделав ссылки на ячейки единообразными.
Например, для умножения столбца A на столбец B используются формулы A1*B1, A2*B2, A3*B3 и т. д. Если после A3*B3 указана формула A4*B2, Excel определяет ее как несогласованную, так как ожидается формула A4*B4.
Щелкните ячейку с индикатором ошибки и просмотрите строку формул, чтобы проверить правильность ссылок на ячейки.
Щелкните стрелку рядом с появившейся кнопкой.
В контекстном меню приведены команды для устранения предупреждения.
Выполните одно из указанных ниже действий.
Скопировать формулу сверху
Согласует формулу с формулой в ячейке сверху. В нашем примере формула изменяется на A4*B4 в соответствии с формулой A3*B3 в ячейке выше.
Удаляет индикатор ошибки. Выберите эту команду, если несоответствие является преднамеренным или приемлемым.
Изменить в строке формул
Позволяет проверить синтаксис формулы и ссылки на ячейки.
Параметры проверки ошибок
Здесь можно выбрать типы ошибок, которые должен помечать Excel. Например, если вы не хотите, чтобы выводились индикаторы ошибки для несогласованных формул, снимите флажок Помечать формулы, несогласованные с формулами в смежных ячейках.
Чтобы пропустить индикаторы одновременно нескольких ячеек, выделите диапазон с этими ячейками. Затем щелкните стрелку рядом с появившейся кнопкой и в контекстном меню выберите команду Пропустить ошибку.
Чтобы пропустить индикаторы ошибок на всем листе, сначала щелкните ячейку с индикатором. Затем выделите лист, нажав клавиши +A. Затем щелкните стрелку рядом с появившейся кнопкой и в контекстном меню выберите команду Пропустить ошибку.
Дополнительные ресурсы
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Настройка параметров формул в Excel 2010
Поскольку Excel — это программа электронных таблиц, а не просто табличный редактор, вычисления являются очень важной частью ее функциональности. Соответственно, настройка параметров вычислений (формул), выполняемая с помощью элементов управления категории Формулы, — процесс, знание которого может весьма облегчить вам жизнь, а незнание — сделать ее невыносимой.
Все элементы управления этой категории распределены по группам Параметры вычислений , Работа с формулами , Контроль ошибок и Правила контроля ошибок . Параметры вычислений задаются в одноименном разделе.
Вычисления в книге — эта группа переключателей определяет режим вычислений:
- Автоматически — каждый раз, когда вы меняете значение в ячейке, которая используется в формуле в качестве параметра, формула пересчитывается.
- Автоматически, кроме таблиц данных — пересчет выполняется автоматически для всех формул, кроме тех, результатом выполнения которых являются таблицы данных.
- Вручную — пересчет выполняется только после нажатия клавиши F9 или выбора команды Пересчет в группе Вычисление на вкладке Формула ленты инструментов.
Пересчитывать книгу перед сохранением — перед сохранением книги все формулы в ней будут пересчитаны, чтобы сохранить актуальные значения (это имеет смысл, только если выключен режим автоматического вычисления).
Включить итеративные вычисления — итеративными называются вычисления, в которых для вычисления значений задается шаг изменения какого-то начального значения. Применяются такие вычисления для того, чтобы найти сочетание параметров, удовлетворяющее заданному условию. Путем итеративных вычислений можно решать самые разные задачи, от поиска корня n-й степени до формирования оптимального бюджета. Итерации используются в таких инструментах Excel, как инструменты поиска решения и подбора параметра. Более подробно мы рассмотрим эти инструменты в следующих публикациях.
- Предельное число итераций — в это поле вводится значение, определяющее, сколько раз с подстановкой разных значений будет выполняться пересчет листа. Чем больше итераций вы зададите, тем больше времени уйдет на пересчет. В то же время большое число итераций позволит получить более точный результат. Поэтому это значение надо подбирать, основываясь на реальной потребности. Если для вас важно получить точный результат любой ценой, а формулы в книге достаточно сложные, вы можете установить значение 10 000, щелкнуть на кнопке пересчета и уйти заниматься другими делами. Рано или поздно пересчет будет закончен. Если же вам важно получить результат быстро, то значение надо установить поменьше.
- Относительная погрешность — максимальная допустимая разница между результатами пересчетов. Чем это число меньше, тем точнее будет результат и тем больше потребуется времени на его получение.
Стиль ссылок R1C1 — переход от стандартного для Excel именования ячеек (A1, D6, E4 и т. д.) к стилю ссылок, при котором нумеруются не только строки, но и столбцы. При этом буква R (row) означает строку, а C (column) — столбец. Соответственно, запись в новом стиле R5C4 будет эквивалентна записи D5 в стандартном стиле.
Автозавершение формул — в этом режиме предлагаются возможные варианты функций во время ввода их в строке формул (рис. 2.11).
Рис. 2.11. Автозавершение формул
Использовать имена таблиц в формулах — вместо того, чтобы вставлять в формулы диапазоны ссылок в виде A1:G8 , вы можете выделить нужную область, задать для нее имя и затем вставить это имя в формулу. На рис. 2.12 приведен такой пример — сначала был выделен диапазон E1:I8 , этому диапазону было присвоено имя MyTable , затем в ячейке D1 была создана формула суммирования, в которую в качестве аргумента передано имя данного диапазона.
Рис. 2.12. Использование имени таблицы в формуле
Использовать функции GetPivotData для ссылок в сводной таблице — в этом режиме данные из сводной таблицы выбираются при помощи вышеуказанной функции. Если вы вставляете в формулу ссылку на ячейку, которая расположена в сводной таблице, то вместо ссылки на ячейку будет автоматически вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ . Если вам все же нужна именно ссылка на ячейку, этот флажок нужно сбросить.
С помощью элементов управления раздела Контроль ошибок настраивается режим контроля ошибок:
- Включить фоновый поиск ошибок — ошибки будут обнаруживаться и отображаться по мере ввода значений и формул, не дожидаясь пересчета.
- Цвет индикаторов ошибок — выбор цвета, которым будет помечаться ячейка с ошибкой.
В разделе Правила контроля ошибок расположены флажки, управляющие режимом контроля ошибок. Я опишу не все флажки, а только те, названия которых недостаточно полно характеризуют их назначение:
Формула не охватывает смежные ячейки что это
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
не совсем понимаю что Вы хотите пропустить, да еще макросом
это проверка ошибок.
есть ситуации, которые не являются ошибками, но подозрительны для ексель
в параметрах ексель
формулы
правила контроля ошибок
можете настроить на что должен реагировать ексель, а на что может не обращать внимание
это настройки ексель, не листа, не книги, а ексель
достаточно настроить 1 раз без макроса
>>не совсем понимаю что Вы хотите пропустить, да еще макросом
бывают случаи когда, формула не согласована с соседними формулами
в этом случае возникает зеленый маркер
отчеты строим для пользователей, чтоб у них была красота
думаю есть разные способы отключения этого маркера
не лезть ведь в глобальные настройки чужого компьютера макросом
в контекстном меню для ячейки, нажав «пропустить ошибку», маркер исчезает
то же самое нельзя ли делать макросом применительно к ячейке
а тут уже Ваш выбор:
или
при открытии файла внести настройки в Excel, на котором открыт файл (в том числе и у пользователя) и убрать метки с ячеек
или
забить на красоту отчета и оставить пользователя недоумевать от качества отчета, в котором ОШИБКИ! (с точки зрения пользователя)
знаете ошибки в программе, описанные в сопроводительной документации, являются не ошибками, а «особенностями работы программы»)))
Выяснение причины несоответствия
-
Щелкните Формулы > Показать формулы.
-
Это позволяет просматривать в ячейках формулы, а не вычисляемые результаты.
-
Сравните несогласованную формулу с соседними формулами и исправьте любые случайные несоответствия.
-
По завершении щелкните Формулы > Показать формулы. Это переключит отображение на вычисляемые результаты для всех ячеек.
-
Если это не помогает, выберите смежную ячейку, в которой отсутствует проблема.
-
Щелкните Формулы > Влияющие ячейки.
-
Выделите ячейку, содержащую проблему.
-
Щелкните Формулы > Влияющие ячейки.
-
Сравните синие стрелки или синие диапазоны. Исправьте все проблемы с несогласованной формулой.
-
Щелкните Формулы > Убрать стрелки.
Другие решения
Выделите ячейку с несогласованной формулой и, удерживая клавишу SHIFT, нажимайте одну из клавиш со стрелками. В результате несогласованная формула будет выделена вместе с другими. Затем выполните одно из указанных ниже действий.
-
Если выделены ячейки снизу, нажмите клавиши CTRL+D, чтобы заполнить формулой ячейки вниз.
-
Если выделены ячейки сверху, выберите Главная > Заполнить > Вверх, чтобы заполнить формулой ячейки вверх.
-
Если выделены ячейки справа, нажмите клавиши CTRL+R, чтобы заполнить формулой ячейки справа.
-
Если выделены ячейки слева, выберите Главная > Заполнить > Влево, чтобы заполнить формулой ячейки слева.
При наличии других ячеек, в которые нужно добавить формулу, повторите указанную выше процедуру в другом направлении.
-
Выделите ячейку с проблемой.
-
Нажмите кнопку
и выберите вариант Скопировать формулу сверху или Скопировать формулу слева. -
Если это не подходит и требуется формула из ячейки снизу, выберите Главная > Заполнить > Вверх.
-
Если требуется формула из ячейки справа, выберите Главная > Заполнить > Влево.
Если формула не содержит ошибку, можно ее пропустить:
-
Щелкните Формулы > Поиск ошибок.
-
Нажмите кнопку Пропустить ошибку.
-
Нажмите кнопку ОК или Далее для перехода к следующей ошибке.
Примечание: Если не нужно использовать в Excel этот способ проверки на несогласованные формулы, закройте диалоговое окно «Поиск ошибок». Выберите Файл > Параметры > Формулы. В нижней части снимите флажок Формулы, не согласованные с остальными формулами в области.
На компьютере Mac выберите Excel > Параметры > Поиск ошибок и снимите флажок Формулы, несогласованные с формулами в смежных ячейках.
Если формула не похожа на смежные формулы, отображается индикатор ошибки. Это не всегда означает, что формула неправильная. Если формула неправильная, проблему часто можно решить, сделав ссылки на ячейки единообразными.
Например, для умножения столбца A на столбец B используются формулы A1*B1, A2*B2, A3*B3 и т. д. Если после A3*B3 указана формула A4*B2, Excel определяет ее как несогласованную, так как ожидается формула A4*B4.
-
Щелкните ячейку с индикатором ошибки и просмотрите строку формул, чтобы проверить правильность ссылок на ячейки.
-
Щелкните стрелку рядом с появившейся кнопкой.
В контекстном меню приведены команды для устранения предупреждения.
-
Выполните одно из указанных ниже действий.
|
Параметр |
Действие |
|---|---|
|
Скопировать формулу сверху |
Согласует формулу с формулой в ячейке сверху. В нашем примере формула изменяется на A4*B4 в соответствии с формулой A3*B3 в ячейке выше. |
|
Пропустить ошибку |
Удаляет индикатор ошибки. Выберите эту команду, если несоответствие является преднамеренным или приемлемым. |
|
Изменить в строке формул |
Позволяет проверить синтаксис формулы и ссылки на ячейки. |
|
Параметры проверки ошибок |
Здесь можно выбрать типы ошибок, которые должен помечать Excel. Например, если вы не хотите, чтобы выводились индикаторы ошибки для несогласованных формул, снимите флажок Помечать формулы, несогласованные с формулами в смежных ячейках. |
Советы:
-
-
Чтобы пропустить индикаторы одновременно нескольких ячеек, выделите диапазон с этими ячейками. Затем щелкните стрелку рядом с кнопкой, которая
, и в контекстном меню выберите » Пропустить ошибку». -
Чтобы пропустить индикаторы ошибок на всем листе, сначала щелкните ячейку с индикатором. Затем выделите лист, нажав клавиши
+A. Затем щелкните стрелку рядом с кнопкой, которая , и в контекстном меню выберите » Пропустить ошибку».
-
Исправление
распространенных ошибок в формулах
Для
проверки формул на наличие ошибок можно
применять определенные правила. Эти
правила действуют как средство проверки
орфографии, которое проверяет данные,
введенные в ячейки, на наличие ошибок.
Эти правила не гарантируют полного
отсутствия ошибок на листе, но помогают
избежать распространенных ошибок.
Каждое из этих правил можно включать и
выключать независимо от других.
Ошибки
можно отмечать и исправлять двумя
способами: по одной ошибке (как в средстве
проверки орфографии) или немедленно по
мере их возникновения на листе во время
ввода данных. В обоих случаях при
обнаружении ошибки в ячейке в ее левом
верхнем углу появляется треугольник.
Ячейка
с ошибкой в формуле
Ошибку
можно устранить, используя предлагаемые
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 Кб1оп.doc
Причин, почему не протягивается формула в Эксель, множество. Что делать, если в Excel не работает протягивание формулы?
1 Простое протягивание формулы
Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца. Он требует выполнения следующих действий:
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.
Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них — автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.
Источник: http://ichip.ru/sovety/ekspluataciya/kak-v-eksele-protyanut-formulu-po-stroke-ili-stolbcu-5-sposobov-752522
Неправильный формат ячеек
Распространенная причина, почему в Экселе не протягивается формула — ошибка в формате ячеек. Если диапазон представлен в текстовом виде, расчет вообще не выполняется, а данные отображаются в формате простого текста. Для решения проблемы в Эксель сделайте следующее:
- Войдите в раздел «Главная» и в разделе инструментов «Число» посмотрите значение. Если стоит «Тестовый», не удивляйтесь, почему не протягивается формула в Excel.
- Дважды жмите по этому полю и выберите оптимальное значение.
- При отсутствии нужного варианта выделите целевой диапазон, жмите по нему правой кнопкой мышки (ПКМ) и выберите «Формат ячеек».
- Выделите секции, в которых расчета не производилась и жмите F2 для проведения новых вычислений.
После этого проверьте, протягивается значение или нет. Если не удалось добиться результата, переходите к следующему варианту.
Источник: http://WindowsTips.ru/pochemu-ne-protyagivaetsya-formula-v-excel
Ошибки
Следующая причина, почему в Экселе не протягивается формула — наличие ошибок. В таком случае программа показывает соответствующее замечание о невозможности расчета. Для устранения проблемы проверьте, правильно ли записаны сведения в ячейках, отсутствуют ли ошибки в синтаксической части или нет ли какого-то неправильного действия в вычисления. Для проверки сбоев в Эксель можно сделать следующее:
- Зайдите в раздел «Формулы».
- В блоке «Зависимости …» жмите «Вычислить …».
- Жмите на кнопку «Вычислить» и ищите ошибку, после чего устраните ее.
Источник: http://WindowsTips.ru/pochemu-ne-protyagivaetsya-formula-v-excel
Отключение режима “Показать формулы” в Excel
Случаются ситуации, когда пользователь не обнаруживает результатов, хотя он их ждал после выполнения операций функцией. Вместо них отображаются только формулы. Причина этого может крыться в том, что включена демонстрация формул, и естественно, нам нужно ее отключить, чтобы увидеть итог вычислений. Выполняем следующие действия:
- Находим в главном меню вкладку «Формулы». Немного ниже и правее нее находится группа инструментов «Зависимость формул», в которой находится кнопка «Показать формулы». Нужно сделать один левый клик мышью при условии, что эта функция активирована.
- После этого в каждой ячейке теперь можно увидеть итоги вычислений. Естественно, это может повлиять на границы колонок, но в этом нет никакой супер страшной проблемы. Этот вопрос решается буквально в несколько кликов.
Источник: http://office-guru.ru/excel/reshaem-problemy-s-formulami-v-excel-chto-esli-formula-ne-rabotaet-ili-pokazyvaet-oshibku.html
Активизация автоматического пересчета формул в Excel
Время от времени появляются ситуации, когда результат формулы не меняется, если в одной из ячеек, данные которой использовались для подсчета, происходит коррекция значений. Чтобы исправить эту проблему, необходимо воспользоваться меню настроек. Следуйте этим действиям:
- Переходим в меню «Файл».
- После этого ищем раздел «Параметры» и переходим в него.
- Далее мы увидим окно. В списке справа находится большое количество различных разделов, но нас интересует «Формулы». После клика мы увидим, что правая часть окна изменилась. Переходим в группу «Параметры вычислений», где ставим флажок возле функции «автоматически». После того, как действия будут выполнены, нам нужно нажать кнопку ОК.
Теперь можно отдыхать, поскольку проблема решена. Все вычисления осуществляются автоматически. И если какое-то из значений вдруг изменяется, программа автоматически его пересчитывает.
Источник: http://office-guru.ru/excel/reshaem-problemy-s-formulami-v-excel-chto-esli-formula-ne-rabotaet-ili-pokazyvaet-oshibku.html
Неправильная настройка вычислений
Бывает, что программа показывает правильное значение, но при внесении изменений, которые с ней связаны, пересчет результата не происходит. Это свидетельствует о неправильной настройке параметров, из-за чего Excel не протягивает формулу. Для восстановления нормальной работоспособности сделайте следующие шаги:
- Зайдите в раздел «Файл» в Эксель.
- Кликните на пункт «Параметры».
- Зайдите в раздел «Формулы».
- Найдите блок «Параметры вычислений», который находится в верхней части.
- Обратите внимание на параметр «Вычисления в книге».
- Если он стоит в позиции «Автоматически», это является причиной неактуальности результата.
- Переставьте выключатель в требуемое место.
- Жмите на «ОК» для сохранения результата.
После этого проверьте работу — необходимый элемент в Эксель, как правило, протягивается, и происходит автоматический пересчет при корректировки любого параметра.
Источник: http://WindowsTips.ru/pochemu-ne-protyagivaetsya-formula-v-excel
Исправление ошибок в формулах
Бывают ситуации, когда человек допускает ошибку при вводе формулы. В этом случае программа будет просто ее определять, как простое значение в текстовом формате. То есть, и расчетов также не будет. Так, самой распространенной ошибкой является ситуация, когда перед вводом знака «равно» ставится пробел. Здесь важно помнить, что перед знаком = не должно быть никаких пробелов, и он всегда должен располагаться перед формулой.
Кроме этого, нередко ошибки появляются в синтаксисе функций. Причина этого проста — не все они просты для заполнения обычными людьми. Особенно это касается ситуаций, когда применяется сразу несколько аргументов. Поэтому ручной ввод формул рекомендуется лишь если пользователь опытный, и в таком случае синтаксис нужно тщательно перепроверять. Для того, чтобы сделать ввод функций более простым, был создан Мастер функций, который вызывается путем нажатия клавиши fx слева от строки ввода формулы. 
Для обеспечения работоспособности формулы необходимо тщательно проверять ее перед вводом. В том числе, необходимо убедиться в том, что в ней нигде не стоят знаки, которые не видимы невооруженным глазом. В нашем случае таким знаком служил пробел.
Время от времени лучше удалить формулу, где стоят некоторые непечатные символы или ее синтаксис написан уж откровенно неправильно, чем искать ошибку и ее исправлять. Это же касается не только названия формулы, но и ее функций. О том, что при вводе формулы была допущена ошибка, Excel сообщит определенным кодом. Каждый из кодов имеет конкретное значение. Давайте перечислим наиболее распространенные из них.
Если хотя бы одна из этих ошибок появляется, то нужно проверить, правильно ли заполнена информация в аргументах этой функции. После этого осуществляется проверка самой формулы и то, есть ли в ней ошибки. Особенно нужно акцентировать внимание на тех из них, которые не соответствуют законам математики. Например, очень частая проблема — деление на ноль. Нередко она возникает случайно. Просто в результате вычисления определенного значения функцией, которая входит в состав формулы и служит аргументом для функции более высокого порядка, появляется число ноль. В результате, формула выдает ошибку #ДЕЛ/0.
Бывают и ситуации потруднее, когда точно сказать, где кроется ошибка, нельзя. Если приходится иметь дело со сложными функциями, то в таком случае нужно использовать специальные инструменты проверки. Следуйте этой инструкции:
- Выделяем ту ячейку, в которой есть ошибочная формула. Далее переходим во вкладку «Формулы». Там нужно найти инструмент «Зависимости формул» — «Вычислить формулу».
- Перед глазами пользователя появится диалоговое окно. В нем детально будет описано, как осуществлять отчет правильно. Чтобы решить проблему, необходимо нажать кнопку «Вычислить». После этого функция будет последовательно проверяться на каждом шагу. Поэтому кнопку нужно нажать несколько раз. Таким образом можно отследить, на каком шагу возникла проблема и оперативно ее решить.
Есть еще один способ определения причин неполадок при работе с формулами. Называется этот инструмент «Проверка ошибок». Его можно найти в том же блоке, что и предыдущий.
После того, как мы нажмем на соответствующую кнопку, появится окно. В нем будет информация о том, что вызвало ошибку, а также будут приведены рекомендации по исправлению сложившейся ситуации.
Чтобы исправить проблему, необходимо нажать на кнопку «Изменить в строке формул». После этого формула будет работать правильно.
Источник: http://office-guru.ru/excel/reshaem-problemy-s-formulami-v-excel-chto-esli-formula-ne-rabotaet-ili-pokazyvaet-oshibku.html
Excel не считает числа: не суммирует, не вычитает, не перемножает, не делит и не вычитает.(числа не складываются): 4 комментария
подруга дала лицензию и офис не работал, все искал почему, но решил удалить. скачал с торрента другую версию и все работает, теперь буду учиться в экселе работаьь вот!
Не тянется формула по столбцу, он остается пустым, хотя формула присутствует. Автоматический расчет включен, не понимаем в чем дело. Подскажите, пожалуйста.
Добрый день, попробуйте изменить формат ячеек с текстового на общий.
Источник: http://planshet-info.ru/kompjutery/excel-perestal-schitat-formuly-avtomaticheski