Несогласующаяся формула excel что это

Несогласующаяся формула excel как исправить

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще…Меньше

Эта ошибка означает, что формула в ячейке не соответствует шаблону формул рядом с ней.

Выяснение причины несоответствия

Щелкните Формулы > Показать формулы.

Это позволяет просматривать в ячейках формулы, а не вычисляемые результаты.

Сравните несогласованную формулу с соседними формулами и исправьте любые случайные несоответствия.

По завершении щелкните Формулы > Показать формулы. Это переключит отображение на вычисляемые результаты для всех ячеек.

Если это не помогает, выберите смежную ячейку, в которой отсутствует проблема.

Щелкните Формулы > Влияющие ячейки.

Выделите ячейку, содержащую проблему.

Щелкните Формулы > Влияющие ячейки.

Сравните синие стрелки или синие диапазоны. Исправьте все проблемы с несогласованной формулой.

Щелкните Формулы > Убрать стрелки.

Другие решения

Выделите ячейку с несогласованной формулой и, удерживая клавишу 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.

См. также

Обнаружение ошибок в формулах

Скрытие значений и индикаторов ошибок

Нужна дополнительная помощь?

My formula is showing error: Inconsistent calculated column formula. But still its working and returning result as expected.

I’m using Microsoft Office 2007. Please help.

asked Oct 23, 2017 at 10:18

That’s because the formula on the previous row/column is a bit different.
This is what Microsoft says about it:

Calculated columns in Excel tables are a fantastic tool for entering formulas efficiently. They allow you to enter a single formula in one cell, and then that formula will automatically expand to the rest of the column by itself. There’s no need to use the Fill or Copy commands. This can be incredibly time saving, especially if you have a lot of rows. And the same thing happens when you change a formula; the change will also expand to the rest of the calculated column.
A calculated column can include a cell that has a different formula from the rest. This creates an exception that will be clearly marked in the table. This way, inadvertent inconsistencies can easily be detected and resolved.

answered Oct 23, 2017 at 10:20

42.1k 8 gold badges 55 silver badges 95 bronze badges

If you intend to have different formulas in the same column in a table, you can turn off this «annoying» feature of excel globally as shown below. I wish I could turn it off, per table or at least per sheet.

under Excel Options / Formulas

answered Dec 30, 2018 at 22:58

1,128 11 silver badges 22 bronze badges

Как показано ниже, в ячейке появится зеленый индикатор ошибки, если формула не соответствует шаблону формулы других ячеек, которые расположены рядом с ней. Фактически, вы можете скрыть эту несогласованную ошибку формулы. Эта статья покажет вам, как этого добиться.

Скрыть одну несогласованную ошибку формулы с игнорированием ошибки
Скрыть все несогласованные ошибки формул при выборе с помощью кода VBA

Скрыть одну несогласованную ошибку формулы с игнорированием ошибки

Вы можете скрыть одну несогласованную ошибку формулы за раз, игнорируя ошибку в Excel. Пожалуйста, сделайте следующее.

1. Выберите ячейку, содержащую индикатор ошибки, который вы хотите скрыть, затем нажмите кнопку отображения. рядом с камерой. Смотрите скриншот:

2. Выбрать Игнорировать ошибку из раскрывающегося списка, как показано на скриншоте ниже.

Тогда индикатор ошибки сразу скроется.

Скрыть все несогласованные ошибки формул при выборе с помощью кода VBA

Следующий метод VBA может помочь вам скрыть все несогласованные ошибки формул в выделенном фрагменте на листе. Пожалуйста, сделайте следующее.

1. На рабочем листе вам нужно скрыть все несогласованные ошибки формул, нажмите кнопку другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окно, пожалуйста, нажмите Вставить > Модули, затем скопируйте и вставьте код VBA в окно кода.

Код VBA: скрыть все несогласованные ошибки формул на листе

3. нажмите F5 ключ для запуска кода. В всплывающем Kutools for Excel В диалоговом окне выберите диапазон, в котором необходимо скрыть все несогласованные ошибки формул, а затем нажмите кнопку OK кнопка. Смотрите скриншот:

Тогда все несовместимые ошибки формул сразу скрываются из выбранного диапазона. Смотрите скриншот:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

• Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон …
• Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы … Предотвращение дублирования ячеек; Сравнить диапазоны …
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое …
• Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии …
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
• Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF …
• Более 300 мощных функций . Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии ( 7 )

Оценок пока нет. Оцените первым!

• Устранение проблем с вычислением
  • Способ 1: изменение формата ячеек
  • Способ 2: отключение режима «Показать формулы»
  • Способ 3: исправление ошибки в синтаксисе
  • Способ 4: включение пересчета формулы
  • Способ 5: ошибка в формуле

  

  Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.

  Устранение проблем с вычислением

  Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.

  Способ 1: изменение формата ячеек

  Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.

  Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.

  

  

  

  

  Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.

  

  Способ 2: отключение режима «Показать формулы»

  Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».

  Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.

  

  

  Способ 3: исправление ошибки в синтаксисе

  Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

  

  

  В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.

  Способ 4: включение пересчета формулы

  Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.

  Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».

  

  

  Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.

  Способ 5: ошибка в формуле

  Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:

  • #ЧИСЛО!;
  • #ЗНАЧ!;
  • #ПУСТО!;
  • #ДЕЛ/0!;
  • #Н/Д.

  В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).

  

  Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.

  Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».

  

  

  Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.

  С помощью Excel можно выполнять почти какого-угодно порядка: вычислять и даже программировать действия компьютера. Все потому, что в функционал заложены функции и формулы. Но иногда пользователю приходится сталкиваться с ошибками при их вводе. Особенно часто они возникают, когда человек пытается ввести формулу вручную. Если пользователь опытный, такая проблема встречается не очень часто, поскольку он уже знает, какой синтаксис правильный, а также довольно внимательный. Но даже профессионалы могут допускать ошибки.

  Часто мы не понимаем, почему они возникают. И профессионалы тоже могут не знать, какие причины. Он может понимать, что означает тот или иной код ошибки, но найти конкретное место в формуле не может. Но для профессионала это не проблема, поскольку он знает, как находить ошибки методами самого Excel. Сегодня мы более детально разберемся в причинах самых распространенных ошибок при вводе формул в Excel и разберем методы решения этой проблемы. Тем самым мы значительно ближе будем к профессионалам. Ну что же, начнем?

  1. Изменение формата ячеек Excel
  2. Отключение режима “Показать формулы” в Excel
  3. Активизация автоматического пересчета формул в Excel
  4. Исправление ошибок в формулах
  5. Исправление ошибки в синтаксисе
  6. Выводы

  Изменение формата ячеек Excel

  

  Довольно нередкая причина того, почему у человека появляется неудача в ходе записи формул, заключается в том, что он неверно выбрал формат ячеек, на какие она ссылается. Так, если в ней поставили формат «текст», то вместо итогового значения перед нами будет просто показана формула в виде текстовой строки.

  

  Есть случаи, когда формат считается правильно, но отображается неверно. Причина этого явления — также ошибочно выбранный формат. Эта проблема может появиться и сама собой, через глюки в работе программы. Как ни странно, человеческий фактор влияет и на работу компьютера, который не является человеком. Но это уже лирическое отступление.

  Конечно, в таком случае следует выставить правильный формат. Для этого нужно следовать таким шагам:

  1. Выделить ячейку и перейти на вкладку «Главная» на ленте. После этого перемещаем курсор мыши в группу «число». Здесь также есть поле, в котором демонстрируется формат, в данный момент присвоенные ячейке.
  2. Нужно нажать на стрелку вниз возле этого поля. После этого появится список с разными форматами ячеек. Нужно выбрать тот, который соответствует тому, который нужно применять в конкретной формуле. После этого делается клик по выбранному варианту.

  Есть еще один инструмент, настройки которого более широки. Чтобы его применить, необходимо выполнять следующую инструкцию:

  1. Определить, нам нужно изменить формат одной ячейки или нескольких. После этого выбираем ту, которая нам подходит (или целый диапазон выделяем), после чего делаем правый клик по ней (ним). Далее ищем в появившемся перечне пункт «Формат ячеек». Задача может быть существенно упрощена, если пользователь нажмет сочетание Ctrl+1.

  

  1. После этого появляется диалоговое окно, в котором по умолчанию будет открыта вкладка «Число». Важно убедиться, что открыта именно она. После этого следует обратить ваш взор на список в левой части окна. Там находится перечень доступных форматов ячеек. Нужно выбрать тот, который подходит в конкретной ситуации и нажать ОК.

  Для сохранения полученных изменений необходимо воспользоваться функцией редактирования для каждой ячейки с ошибкой при вводе формулы. После того, как мы найдем тот элемент, который нужен, следует отредактировать с помощью клавиши F2 или выполнения двойного клика по соответствующей ячейке. Также внесение изменений возможно с помощью левого щелчка мыши в строке формул. После внесения всех изменений в документ нужно подтвердить свои действия путем нажатия клавиши Enter.

  

  Внимание. При наличии большого количества информации, последний шаг может потребовать очень много времени. В этом случае можно упростить задачу, применив маркер автозаполнения. Правда, этим методом можно воспользоваться только в одном случае. Главное — требование — в каждой ячейке должна применяться та же формула.

  Что следует делать на практике?

  1. Выделяем ячейку, которая расположена в самом верху диапазона.
  2. Наводим курсор на ее правую нижнюю часть (то есть, на угол), убеждаемся в том, что появился знак плюса черного цвета, нажимаем левую кнопку, зажимаем ее и перемещаем курсор до того места, где должен закончиться этот диапазон.

  

  После выполнения этих операций перед нашим взором стоит готовый столбец, который был сгенерирован почти без нашего участия.

  

  Отключение режима “Показать формулы” в Excel

  Случаются ситуации, когда пользователь не обнаруживает результатов, хотя он их ждал после выполнения операций функцией. Вместо них отображаются только формулы. Причина этого может крыться в том, что включена демонстрация формул, и естественно, нам нужно ее отключить, чтобы увидеть итог вычислений. Выполняем следующие действия:

  1. Находим в главном меню вкладку «Формулы». Немного ниже и правее нее находится группа инструментов «Зависимость формул», в которой находится кнопка «Показать формулы». Нужно сделать один левый клик мышью при условии, что эта функция активирована.

  

  1. После этого в каждой ячейке теперь можно увидеть итоги вычислений. Естественно, это может повлиять на границы колонок, но в этом нет никакой супер страшной проблемы. Этот вопрос решается буквально в несколько кликов.

  

  Активизация автоматического пересчета формул в Excel

  Время от времени появляются ситуации, когда результат формулы не меняется, если в одной из ячеек, данные которой использовались для подсчета, происходит коррекция значений. Чтобы исправить эту проблему, необходимо воспользоваться меню настроек. Следуйте этим действиям:

  1. Переходим в меню «Файл».
  2. После этого ищем раздел «Параметры» и переходим в него.
  3. Далее мы увидим окно. В списке справа находится большое количество различных разделов, но нас интересует «Формулы». После клика мы увидим, что правая часть окна изменилась. Переходим в группу «Параметры вычислений», где ставим флажок возле функции «автоматически». После того, как действия будут выполнены, нам нужно нажать кнопку ОК.

  

  Теперь можно отдыхать, поскольку проблема решена. Все вычисления осуществляются автоматически. И если какое-то из значений вдруг изменяется, программа автоматически его пересчитывает.

  Исправление ошибок в формулах

  Бывают ситуации, когда человек допускает ошибку при вводе формулы. В этом случае программа будет просто ее определять, как простое значение в текстовом формате. То есть, и расчетов также не будет. Так, самой распространенной ошибкой является ситуация, когда перед вводом знака «равно» ставится пробел. Здесь важно помнить, что перед знаком = не должно быть никаких пробелов, и он всегда должен располагаться перед формулой.

  

  

  Кроме этого, нередко ошибки появляются в синтаксисе функций. Причина этого проста — не все они просты для заполнения обычными людьми. Особенно это касается ситуаций, когда применяется сразу несколько аргументов. Поэтому ручной ввод формул рекомендуется лишь если пользователь опытный, и в таком случае синтаксис нужно тщательно перепроверять. Для того, чтобы сделать ввод функций более простым, был создан Мастер функций, который вызывается путем нажатия клавиши fx слева от строки ввода формулы.

  

  Для обеспечения работоспособности формулы необходимо тщательно проверять ее перед вводом. В том числе, необходимо убедиться в том, что в ней нигде не стоят знаки, которые не видимы невооруженным глазом. В нашем случае таким знаком служил пробел.

  Время от времени лучше удалить формулу, где стоят некоторые непечатные символы или ее синтаксис написан уж откровенно неправильно, чем искать ошибку и ее исправлять. Это же касается не только названия формулы, но и ее функций. О том, что при вводе формулы была допущена ошибка, Excel сообщит определенным кодом. Каждый из кодов имеет конкретное значение. Давайте перечислим наиболее распространенные из них.

  

  Если хотя бы одна из этих ошибок появляется, то нужно проверить, правильно ли заполнена информация в аргументах этой функции. После этого осуществляется проверка самой формулы и то, есть ли в ней ошибки. Особенно нужно акцентировать внимание на тех из них, которые не соответствуют законам математики. Например, очень частая проблема — деление на ноль. Нередко она возникает случайно. Просто в результате вычисления определенного значения функцией, которая входит в состав формулы и служит аргументом для функции более высокого порядка, появляется число ноль. В результате, формула выдает ошибку #ДЕЛ/0.

  Бывают и ситуации потруднее, когда точно сказать, где кроется ошибка, нельзя. Если приходится иметь дело со сложными функциями, то в таком случае нужно использовать специальные инструменты проверки. Следуйте этой инструкции:

  

  1. Выделяем ту ячейку, в которой есть ошибочная формула. Далее переходим во вкладку «Формулы». Там нужно найти инструмент «Зависимости формул» — «Вычислить формулу».
  2. Перед глазами пользователя появится диалоговое окно. В нем детально будет описано, как осуществлять отчет правильно. Чтобы решить проблему, необходимо нажать кнопку «Вычислить». После этого функция будет последовательно проверяться на каждом шагу. Поэтому кнопку нужно нажать несколько раз. Таким образом можно отследить, на каком шагу возникла проблема и оперативно ее решить.

  

  Есть еще один способ определения причин неполадок при работе с формулами. Называется этот инструмент «Проверка ошибок». Его можно найти в том же блоке, что и предыдущий.

  

  После того, как мы нажмем на соответствующую кнопку, появится окно. В нем будет информация о том, что вызвало ошибку, а также будут приведены рекомендации по исправлению сложившейся ситуации.

  

  Чтобы исправить проблему, необходимо нажать на кнопку «Изменить в строке формул». После этого формула будет работать правильно.

  Исправление ошибки в синтаксисе

  Надо сказать, что все особенности исправления ошибок в синтаксисе были описаны выше, потому что это частный случай ошибок в формулах. Тем не менее, давайте резюмируем то, что было сказано, поскольку ошибка в синтаксисе — очень частая проблема начинающих пользователей Excel. Для проверки синтаксиса существует два специализированных инструмента: проверка формул и вычисление. Также можно воспользоваться ручным методом проверки. Когда какой лучше использовать?

  1. В целом, если формула небольшая, ее можно проверить и самостоятельно. Это не потребует большого количества времени. Также можно без проблем проверять формулы самостоятельно, если их количество небольшое. Дело в том, что при увеличении количества формул увеличивается и количество ошибок при проверке. Мозг загромождается большим количеством информации, пусть и простой для обработки.
  2. Если есть большое количество формул или сами формулы довольно большие, лучше воспользоваться специализированным инструментом. Каким именно — решать только вам. Каждый из них подходит, просто один предусматривает выполнение действий более в ручном режиме, а другой — автоматическом.

  Настоятельно рекомендуется на досуге потренироваться использовать эти инструменты, чтобы в реальной рабочей ситуации не теряться. Так, в Excel часто делаются отчеты, которые имеют свои дедлайны. И если близко к дедлайну появится ошибка, которую невозможно оперативно исправить, пользователь может столкнуться с большой паникой и завалить весь отчет. Поэтому все навыки, связанные с Excel, нужно доводить до автоматизма перед тем, как использовать их на практике.

  Выводы

  С ошибками приходится сталкиваться каждому человеку, который пользуется электронными таблицами. Это неотъемлемая часть рабочего процесса. Поэтому умение обрабатывать ошибки — важнейший навык. Выше были перечислены самые распространенные ошибки, с которыми могут сталкиваться пользователи при вводе формул. Кроме того, были описаны методы решения возникающих проблем и приведены ситуации, в которых необходимо использовать каждый из них.

  7. Общие сведения о значениях ошибок.

  Для проверки формул на наличие ошибок можно применять определенные правила. Эти правила действуют как средство проверки орфографии, которое проверяет данные, введенные в ячейки, на наличие ошибок. Эти правила не гарантируют полного отсутствия ошибок на листе, но помогают избежать распространенных ошибок. Каждое из этих правил можно включать и выключать независимо от других.

  Ошибки можно отмечать и исправлять двумя способами: по одной ошибке (как в средстве проверки орфографии) или немедленно по мере их возникновения на листе во время ввода данных. В обоих случаях при обнаружении ошибки в ячейке в ее левом верхнем углу появляется треугольник.

  Ячейка с ошибкой в формуле

  Ошибку можно устранить, используя предлагаемые MS Excel варианты, или пропустить, нажав кнопку Пропустить ошибку. Если пропустить ошибку в определенной ячейке, то при последующих проверках наличие ошибки в этой ячейке определяться не будет. Однако состояние всех пропущенных ошибок можно сбросить, чтобы они отображались снова.

  Включение и отключение правил проверки ошибок

  На вкладке Файл выберите команду Параметры, а затем — категорию Формулы.

  В области Правила контроля ошибок установите или снимите флажок для любого из перечисленных далее правил.

  Ячейки, которые содержат формулы, приводящие к ошибкам. В данной формуле используется неправильный синтаксис, аргументы или типы данных. Значения таких ошибок: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!. Каждое из этих значений ошибки вызывается различными причинами, и такие ошибки устраняются разными способами.

  Примечание Если значение ошибки ввести непосредственно в ячейку, оно будет сохранено как значение ошибки, но отмечаться как ошибка не будет. Однако если на эту ячейку будет ссылаться формула из другой ячейки, для этой ячейки будет возвращено значение ошибки.

  Несогласованная формула в вычисляемом столбце таблицы. Вычисляемый столбец может содержать формулы, отличающиеся от формулы столбца, что приводит к возникновению исключения. Исключения вычисляемого столбца возникают при указанных ниже действиях.

  Ввод в ячейку вычисляемого столбца данных, отличных от формулы.

  Ввод в ячейку вычисляемого столбца формулы с последующим нажатием кнопкиОтменитьна панели быстрого доступа.

  Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.

  Копирование в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.

  Примечание Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.

  Перемещение или удаление ячейки в другой области листа, на которую ссылается одна из строк вычисляемого столбца.

  

  Числа, отформатированные как текст или с предшествующим апострофом. Ячейка содержит числа, хранящиеся в виде текста. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся в виде текста, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат.

  Формулы, несогласованные с остальными формулами в области. Формула не соответствует шаблону других смежных формул. В большинстве случаев формулы, расположенные в соседних ячейках, отличаются только используемыми ссылками. В приведенном, далее примере, состоящем из четырех смежных формул, приложение MicrosoftExcel показывает ошибку в формуле =СУММ(A10:F10), поскольку значения в смежных формулах изменились на одну строку, а в формуле =СУММ(A10:F10) — на 8 строк. В данном случае, ожидаемой формулой является =СУММ(A3:F3).

  

  Формулы, не охватывающие смежные ячейки. Формула может не включать ссылки на данные, вставленные между исходным диапазоном и ячейкой с формулой. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой формулы. Если смежные ячейки содержат дополнительные значения и не являются пустыми, MicrosoftExcelвыведет рядом с формулой ошибку.

  Например, в случае применения этого правила приложение MicrosoftExcel выведет ошибку рядом с формулой =СУММ(A2:A4), поскольку между указанным в формуле диапазоном ячеек и ячейкой с формулой (A8) находятся заполненные ячейки A5, A6 и A7, на которые также должна быть ссылка в формуле.

  

  Формулы, которые ссылаются на пустые ячейки. Формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.

  Предположим, нужно вычислить среднее значение для чисел из указанного ниже столбца ячеек. Если третья ячейка будет пустой, она не будет учтена при выполнении вычисления, и результатом будет 22,75. Если третья ячейка будет содержать значение 0, результатом будет 18,2.

  В таблицу введены недопустимые данные. В таблице произошла ошибка проверки. Чтобы просмотреть параметры проверки для ячейки, на вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

  Последовательное исправление распространенных ошибок в формулах

  Внимание! Если на листе уже выполнялась проверка ошибок, то ошибки, которые были пропущены, не будут отображаться, пока их состояние не будет сброшено.

  Выберите лист, на котором требуется проверить наличие ошибок.

  Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.

  На вкладке Формулы в группе Зависимости формул нажмите кнопку группы Проверка наличия ошибок.

  

  В случае обнаружения ошибок открывается диалоговое окно Контроль ошибок.

  Чтобы повторно проверить пропущенные ранее ошибки, выполните указанные ниже действия.

  Нажмите кнопку Параметры.

  В разделе Контроль ошибок нажмите кнопку Сброс пропущенных ошибок.

  Нажмите кнопку ОК.

  Нажмите кнопкуПродолжить.

  Примечание Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.

  Расположите диалоговое окно Контроль ошибок непосредственно под строкой формул .

  

  Нажмите одну из управляющих кнопок в правой части диалогового окна. Доступные действия зависят от типа ошибки.

  Примечание Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.

  Нажмите кнопкуДалее.

  Выполняйте эти действия, пока проверка ошибок не будет завершена.

  К началу страницы

  Пометка и исправление распространенных ошибок формул на листе

  Откройте вкладку Файл.

  Нажмите кнопку Параметры и выберите категорию Формулы.

  Убедитесь, что в области Контроль ошибок установлен флажокВключить фоновый поиск ошибок.

  Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок. Нажмите кнопку «ОК», чтобы закрыть диалоговое окно Параметры Excel.

  Чтобы исправить ошибку на листе, выберите ячейку с треугольником в левом верхнем углу.

  Нажмите появившуюся рядом с ячейкой кнопку Контроль ошибоки выберите нужный пункт. Доступные команды зависят от типа ошибки. Первый пункт содержит описание ошибки.

  Если нажать кнопкуПропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.

  Повторите два предыдущих действия.

  Исправление значения ошибки

  Если формула содержит ошибку, которая не позволяет правильно выполнить вычисления, будет показано значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!. Каждый тип ошибки вызывается разными причинами, и такие ошибки устраняются разными способами.

  В таблице ниже приведены ссылки на статьи с подробным описанием ошибок и краткий вступительный обзор каждой ошибки.

  Ссылка на статью с подробным описанием

  Эта ошибка появляется в том случае, если ширины столбца недостаточно, чтобы показать все знаки в ячейке, или если ячейка содержит отрицательные значения даты или времени.

  Так, при вычислении формулы, которая вычитает более позднюю дату из более ранней, например =15.06.2008-01.07.2008 , получится отрицательное значение даты.

  Исправление ошибки #ДЕЛ/0!

  Эта ошибка появляется в том случае, если число делится на ноль (0) или на ячейку, в которой нет значения.

  Исправление ошибки #ЗНАЧ!

  Эта ошибка появляется в том случае, если формула содержит ячейки с разными типами данных. Если включена проверка ошибок в формулах, при наведении указателя мыши на смарт-тег появится всплывающая подсказка «Значение, используемое в формуле, имеет неправильный тип данных». Обычно для исправления этой ошибки достаточно внести в формулу небольшие изменения.

  Исправление ошибки #ИМЯ?

  Эта ошибка появляется в том случае, если текст в формуле не удается распознать. Например, имя диапазона или функции может иметь неверное написание.

  Исправление ошибки #Н/Д

  Эта ошибка появляется в том случае, если значение для функции или формулы недоступно.

  Исправление ошибки #ПУСТО!

  Эта ошибка появляется в том случае, если указано пересечение двух областей, которые на самом деле не пересекаются. Оператором пересечения служит пробел, который разделяет ссылки в формуле.

  Например, области A1:A2 и C3:C5 не пересекаются, и если ввести формулу =СУММ(A1:A2 C3:C5) , будет показана ошибка #ПУСТО!.

  Исправление ошибки #ССЫЛКА!

  Эта ошибка появляется в том случае, если ссылка на ячейку является неверной. Например, вы могли удалить ячейки, на которые ссылались другие формулы, или вставить перемещенные ячейки на место тех ячеек, на которые ссылались другие формулы.

  Исправление ошибки #ЧИСЛО!

  Эта ошибка появляется в том случае, если формула или функция содержит недопустимые числовые значения.

  Вычисления в таблицах

  Ознакомление с правилами ввода простых формул

  Формулы представляют собой выражения, по которым выполняются вычисления значений на листе. Формула начинается со знака равенства (=).Формула может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

  Функции. Функция ПИ() возвращает значение числа Пи: 3,142.

  Ссылки. A2 возвращает значение ячейки A2.

  Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

  Операторы. Оператор ^ («крышка») возводит число в степень, а оператор * («звездочка») перемножает два или более числа.

  Исправление распространенных ошибок во время ввода формул

  В приведенной ниже таблице перечислены некоторые наиболее распространенные ошибки, совершаемые пользователями при вводе формул, и способы их исправления.

  Убедитесь, что…

  Дополнительные сведения

  Каждая формула начинается со знака равенства (=)

  Если знак равенства опустить, введенные значения могут быть отображены как текст или дата. Например, если ввести СУММ(A1:A10), MicrosoftExcel отобразит текстовую строку СУММ(A1:A10) и не станет вычислять значение формулы. Если ввести 11/2, Excel отобразит дату (например, «2 ноября» или «02.11.2009») вместо того, чтобы разделить 11 на 2.

  Все открывающие и закрывающие скобки согласованы

  Убедитесь, что у каждой скобки имеется соответствующая ей пара. Чтобы функция в формуле работала правильно, важно, чтобы каждая скобка стояла на своем месте. Например, формула =ЕСЛИ(B5<0);»Неверно»;B5*1,05) работать не будет, потому что в ней две закрывающие и только одна открывающая скобка. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0;»Неверно»;B5*1,05).

  Для указания диапазона используется двоеточие

  При указании ссылки на диапазон ячеек используйте двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например: A1:A5.

  Введены все необходимые аргументы

  Некоторым функциям листа требуются аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.), тогда как другие функции (например, ПИ) аргументов не принимают. Кроме того, убедитесь, что вы не ввели слишком много аргументов. Например, функция ПРОПИСН принимает в качестве аргумента только одну строку текста.

  Введены аргументы правильного типа

  Для некоторый функций листа, например СУММ, требуются числовые аргументы. Другие функции, напримерЗАМЕНИТЬ, требуют текстового значения по меньшей мере для одного из аргументов. Прииспользования в качестве аргумента данных неверного типа можно получить неправильные результаты или ошибку.

  Количество вложенных функций не превышает 64

  Внутри одной функции можно ввести (или вложить) не более 64 уровней функций. Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2;»Меньше двух!»;»Больше двух!») содержит три функции: функция ПИ вложена в функцию КОРЕНЬ, которая, в свою очередь, вложена в функцию ЕСЛИ.

  Имена других листов заключены в одинарные кавычки

  Если в формуле имеются ссылки на значения или ячейки других листов или книг, имена которых содержат небуквенные знаки, такие имена необходимо заключить в одинарные кавычки ( ‘ ).

  Ссылаясь на лист в формуле, следует поместить после его имени восклицательный знак (!).

  Например, чтобы вернуть значение ячейки D3 листа с именем «Данные за квартал» в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3.

  Включен путь к внешним книгам

  Убедитесь, что каждая внешняя ссылка (Внешняя ссылка. Ссылка на ячейку или диапазон ячеек в другой книге Microsoft Excel или ссылка на имя, определенное в другой книге.) содержит имя книги и путь к ней.

  Ссылка на книгу включает в себя имя этой книги и должна быть заключена в квадратные скобки ([]). Кроме того, в ссылке должно быть указано имя листа в книге.

  Например, формула со ссылкой на ячейки с A1 по A8 на листе с именем «Продажи» в книге (в настоящий момент открытой в Excel) с именем Кв2 Операции.xlsx, будет выглядеть примерно так: =[Кв2 Операции.xlsx]Продажи!A1:A8.

  Если книга, на которую требуется сослаться, не открыта в Excel, ссылку на нее все же можно включить в формулу. Нужно указать полный путь к файлу, как в следующем примере: =ЧСТРОК(‘C:\Мои документы\[Кв2 Операции.xlsx]Продажи’!A1:A8). Эта формула возвращает число строк в диапазоне, включающем ячейки с A1 по A8 в другой книге (а именно, 8).

  Примечание Если полный путь содержит пробелы, как в предыдущем примере, его необходимо заключить в одинарные кавычки (в начале пути и после имени листа, перед восклицательным знаком).

  Числа введены без форматирования

  Не форматируйте числа, которые вводите в формулах. Например, если вам нужно ввести значение $1,000, введите в формулу просто 1000. Если ввести запятую как часть числа, Excel будет трактовать ее как разделительный знак. Если требуется, чтобы числа отображались с разделителями тысяч и миллионов или с символами валюты, отформатируйте ячейки после ввода чисел.

  Например, если нужно добавить 3100 к значению в ячейке A3, и вы ввели формулу =СУММ(3,100;A3), приложение Excel сложит числа 3 и 100, а затем добавит результат к значению ячейки A3, вместо того чтобы добавить 3100 к значению A3. А если введена формула =ABS(-2,134), будет показана ошибка, поскольку функция ABS принимает только один аргумент.

  Избегайте деления на ноль

  При попытке разделить значение в ячейке на значение в другой ячейке, содержащей ноль или не содержащей никакого значения, будет показана ошибка #ДЕЛ/0!.

  Вычисления в Excel выполняются с помощью Формул. Формула начинается со знака равно (=) и состоит из элементов (операндов: константы, ссылки на ячейки или диапазоны ячеек, функции), соединенных операторами (знаки операций).

  Применение операторов в формулах

  Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок

  Несогласующаяся формула excel что это

  Предыдущие ветки топика: Часть 1 | Часть 2 | Часть 3 | Часть 4

  Все вопросы по программированию (макросы, скрипты, пользовательские функции и т.п.) обсуждаются в теме Excel VBA в разделе Прикладное программирование.

  При необходимости выложить скриншот, пользуйтесь сервисом ImageShack® (ограничение на размер файла <1.5 Mb)

  К стати для теста это я преобразовывал вариант "+" в вариант "СУММ"

  У меня подозрение, что все таки какая то фишка в количестве знаков после запятой.
  Потому Excell как-то считает не с тем округлением в разных режимах.

  Такое подозрение — что Excell где то у себя внутри файла сохранил неправильность с точностью копеек, а после исправления сохраняет по новому и ок.

  Цитата:

  есть несколько макросов в сети, каждый из которых лучше работает в зависимости от исходных файлов — с одним листом или несколькими исходные файлы

  Спасибо за помощь! Какой макрос работает наилучшим образом в Офисе 2010 для однолистовых файлов с двумя пустыми вкладками (которые по-умолчанию создаются), которые желательно чтобы игнорировались при объединении?

  Уже писал, на мой взгляд, нет никакой ошибки. Есть ячейки с формулами, есть без них.
  (ну да ладно, всяко бывает, тем более с переведенными программами)
  И потому, вот отключаемый список:

  Проверять наличие следующих ошибок:
  — вычисление формулы вызывает ошибку;
  — текстовая дата с 2-значным годом;
  — число сохранено как текст;
  — несогласующаяся формула в области;
  — формула не охватывает смежные ячейки;
  — не заблокирована ячейка, содержащая формулу;
  — формула ссылается на пустые ячейки;
  — ошибка при проверке данных в списках.

  И вот оно. пока переписывал — дошло или вернее вроде догадался — несогласующаяся формула в области; по одинаковому — "в области".
  Пропали предупреждения!

  Как сделать чтобы колонтитул/ы были только на определённых страницах, скажем только на 1-ой ?

  Проблемы с вычислением формул в Microsoft Excel

  

  Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.

  Устранение проблем с вычислением

  Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.

  Способ 1: изменение формата ячеек

  Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.

  

  Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.

  Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.

  

  Способ 2: отключение режима «Показать формулы»

  Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».

  

  1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.
  2. После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.

  Способ 3: исправление ошибки в синтаксисе

  Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

  

  В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.

  Способ 4: включение пересчета формулы

  Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.

  

  1. Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».
  2. Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».

  Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.

  Способ 5: ошибка в формуле

  Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:

  • #ЧИСЛО!;
  • #ЗНАЧ!;
  • #ПУСТО!;
  • #ДЕЛ/0!;
  • #Н/Д.

  В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).

  

  Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.

  

  1. Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».
  2. Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.

  Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.

  Похожие публикации:

  1. Как сделать первую букву заглавной в excel
  2. Как сделать сетевой график в excel
  3. Как сделать точечную диаграмму в excel
  4. Как скопировать группировку строк в excel