Что значит формула не охватывает смежные ячейки в эксель

Настройка параметров формул в Excel 2010

Вычисления в книге — эта группа переключателей определяет режим вычислений:

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

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

В разделе Работа с формулами задаются параметры, управляющие вводом и вычислением формул.

Стиль ссылок R1C1 — переход от стандартного для Excel именования ячеек (A1, D6, E4 и т. д.) к стилю ссылок, при котором нумеруются не только строки, но и столбцы. При этом буква R (row) означает строку, а C (column) — столбец. Соответственно, запись в новом стиле R5C4 будет эквивалентна записи D5 в стандартном стиле.

Автозавершение формул — в этом режиме предлагаются возможные варианты функций во время ввода их в строке формул (рис. 2.11).

Рис. 2.11. Автозавершение формул

Рис. 2.12. Использование имени таблицы в формуле

С помощью элементов управления раздела Контроль ошибок настраивается режим контроля ошибок:

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

Источник

Почему в Excel появляется сообщение «Незащищенная формула»?

Проблема

Это сообщение может появляться в двух ситуациях:

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

Если вы проверяете ошибки в поле Поиск ошибок.

Причина

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

Решение

Заблокируйте ячейку, выполнив одно из следующих действий:

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

В диалоговом окне Поиск ошибок щелкните Блокировать ячейку.

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

Дополнительные сведения о защите книги см. в теме «Защита книги».

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

Дополнительные сведения о том, как управлять проверкой ошибок, см. в теме «Обнаружение ошибок в формулах в Excel».

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Источник

Функция ЕСЛИ — вложенные формулы и типовые ошибки

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

Заявления ЕСЛИ являются исключительно надежными и являются основой для многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале утверждение ЕСЛИ должно применяться к минимальным условиям, таким как «Мужчина/женщина», «Да/Нет/Возможно», но иногда может потребоваться оценить более сложные сценарии, для которых требуется вложенное* более 3 функций ЕСЛИ.

* «Вложенность» означает объединение нескольких функций в одной формуле.

Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

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

Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.

Примеры

Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.

97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«;ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))» loading=»lazy»>

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

Если тестовых баллов больше 79, учащийся получает оценку B.

Если тестовых баллов больше 69, учащийся получает оценку C.

Если тестовых баллов больше 59, учащийся получает оценку D.

В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

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

Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

Дополнительные примеры

Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))» loading=»lazy»>

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д.

Хотя она выглядит примерно так же, как в примере с более ранними оценками, эта формула является отличным примером того, насколько сложно использовать крупные выписки ЕСЛИ. Что делать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить существующие значения в рублях или процентах? У вас будет много работы на руках!

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

Перед вами пример сценария для расчета комиссионных с неправильной логикой:

5000;10%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>15000;20%;0)))))» loading=»lazy»>

Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

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

В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:

Таблицы ссылок функции ВПР открыты и их легко увидеть.

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

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

Вы знали?

Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

Примечание: Эта функция доступна только при наличии подписки на Microsoft 365.. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

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

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

Источник

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

Форматирование данных

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

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

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

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

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

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

Ошибку можно устранить, используя предлагаемые 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. На вкладке Формулы в группе Зависимости формул нажмите кнопку группы Проверка наличия ошибок.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Источник