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

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

Например, для умножения столбца A на столбец B используются формулы A1*B1, A2*B2, A3*B3 и т. д. Если после A3*B3 указана формула A4*B2, Excel определяет ее как несогласованную, так как ожидается формула A4*B4.

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

2. Щелкните стрелку рядом с появившейся кнопкой.

   

   В контекстном меню приведены команды для устранения предупреждения.

3. Выполните одно из указанных ниже действий.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Ячейки, которые содержат
  годы, представленные 2 цифрами.  Ячейка
  содержит дату в текстовом формате,
  которая в случае использования в
  формулах может быть отнесена к
  неправильному веку. Например, дата в
  формуле =ГОД(«1.1.31») может относиться
  как к 1931-му, так и к 2031-му году. Это
  правило служит для выявления неоднозначных
  дат в текстовом формате.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Части формулы

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

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

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

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

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

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

Формулы

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

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

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

Арифметические
операторы.

Операторы
сравнения.  

Используются для
сравнения двух значений. Результатом
сравнения является логическое значение:
либо ИСТИНА, либо ЛОЖЬ.

Текстовый оператор
конкатенации.
Операция (&) используется для объединения
нескольких текстовых строк в одну
строку.

Оператор
ссылки.  
Для описания ссылок на диапазоны ячеек
используются следующие операторы.

Порядок выполнения
действий в формулах

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

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

Ссылка
— это адрес объекта (ячейки, строки,
столбца, диапазона),
используемый при записи формулы.

Способы
ввода формул: набрать
ее

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

  23.08.2019159.74 Кб1оп.doc

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

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

Изменение формата ячеек 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, нужно доводить до автоматизма перед тем, как использовать их на практике.

Выводы

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