Несогласованная формула 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 как исправить

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

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

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

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

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

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

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

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

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

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

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

#SPILL возвращаются ошибки, если формула возвращает несколько результатов, Excel не удается вернуть результаты в сетку. Дополнительные сведения об этих типах ошибок см. в следующих разделах справки:

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

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

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

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

Динамические измеления массива могут активировать дополнительные вычисления, чтобы обеспечить полное вычисление электронных таблиц. Если размер массива будет изменяться во время этих дополнительных пропусков и не будет разоючен, Excel разрешит динамический массив #SPILL!.

Это значение ошибки обычно связано с использованием функций СЛ RAND,RANDARRAYи RANDBETWEEN. Другие переменные функции, такие как СМЕЩЕНИЕ,ДВСИМВи СЕГОДНЯ, не возвращают разных значений при каждом прочете вычислений.

Например, при размещении в ячейке E2, как в примере ниже, формула =В.В;A:C;2;ЛОЖЬ) ранее только подыскала бы ИД в ячейке A2. Однако при динамическом Excel массива формула приведет к #SPILL! поскольку Excel будет искать весь столбец, возвращать 1 048 576 результатов и Excel сетки.

Существует три простых способа решения этой проблемы:

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

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

Запрос на Excel неявное пересечение с помощью оператора @, а затем скопируйте формулу вниз. Этот стиль формулы работает в таблицах,но не возвращаетдинамический массив.

Формулы разлитого массива не поддерживаются в Excel таблицах. Попробуйте вынуть формулу из таблицы или преобразовать ее в диапазон (щелкните Конструктор таблиц > Инструменты > Преобразовать в диапазон).

Из-за пролитой формулы массива, который вы попытались ввести, Excel не потери памяти. Попробуйте ссылку на меньший массив или диапазон.

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Решаем проблемы с формулами в Excel. Что если формула не работает или показывает ошибку

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы

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

Проблемы с формулами в таблице Excel

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

Решение 1: меняем формат ячеек

Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

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

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

Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

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

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

Решение 2: отключаем режим “Показать формулы”

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

Решение 3: активируем автоматический пересчет формул

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

Решение 4: исправляем ошибки в формуле

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

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

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

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

Распространенные ошибки

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

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

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

Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

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

Заключение

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

12 наиболее распространённых проблем с Excel и способы их решения

Представляем вам гостевой пост, из которого вы узнаете, как избежать самых распространённых проблем с Excel, которые мы создаём себе сами.

Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

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

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

Функция OFFSET (СМЕЩ) в Excel. Как использовать? ЭКСЕЛЬ ХАК

1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Ещё одна удивительная особенность — формула ИНДЕКС возвращает ссылку на ячейку, а не значение ячейки. Внимательный читатель, конечно же, запальчиво воскрикнет, что, мол за ерунда, почему тогда предыдущий пример не вернул нам С3 , а вернул 9? Дело в том, что данный факт маскируется оператором присваивания (знак равно), с которого начинается любая формула.

Синтаксис

Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)

При использовании функции ЕСЛИ в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:

Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна «35», то формула возвращает “Сдал”, иначе возвращается “Не сдал”.

Не суммируются ячейки в Excel?

• Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
• В случае, если первое условие — ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.

Теперь нажимаю (не убирая курсор с ячейки “итоги”) кнопку “Зависимые ячейки” и на экране появляется ещё одна стрелка. Она ведет к ячейке “результат с поправкой”, то есть той, результат вычислений в которой зависит от текущей.

Примеры использования функции СМЕЩ в Excel

Пример 1. Ищем последнюю заполненную ячейку в колонке

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

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку «A1».

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку «A1» на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

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

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

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

Поиск и исправление ошибок в формулах MS Excel — Вектор развития. Офисные системы для бизнеса

• Функция OFFSET (СМЕЩ) — волатильная функция. Она пересчитывается каждый раз, как только вы открываете Excel файл. Работа этой функции может сильно сказываться на скорости работы всего файла.
• Если значения высоты и ширины не указаны, функция учитывает только первые три аргумента;
• Если значения аргументов rows (смещ_по_строкам) и cols (смещ_по_столбцам) отрицательны, то смещение будет происходить в обратную сторону.

По аналогии, конструкция ИНДЕКС ( A1:C5 ;3;) вернёт вектор A3:C3 , то есть строку. Заметьте, что наличие второй точки с запятой принципиально, и за ней должна быть либо пустота (пробелы игнорируются), либо ноль.

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

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

К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.

Влияющие и зависимые ячейки в MS Excel

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

Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые. Различить и запомнить их просто:

• Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
• Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).

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

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

Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.

Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».

Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке

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

А теперь зависимые ячейки. Весь лист теперь как на ладони

Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.

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

Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой

Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки».

Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой

Исправление ошибок возникающих в MS Excel

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

Ищем ошибку в формуле Excel

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

А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей

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

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