Какая из формул приведенных ниже на пустом рабочем листе возвращает значение ошибки

Опубликовано: 17.09.2024

Цель:Научиться производить расчеты в электронных таблицах с использованием формул.Изучить организацию расчетов с относительной и абсолютной адресацией данных в таблицах MS Excel.

Теоретические сведения

РАБОТА С ФОРМУЛАМИ. АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ ЯЧЕЕК.

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

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

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

сложение (+), умножение (*), вычитание (-), деление (/), процент (%), возведение в степень (^), а также операторы сравнения: равно (=), больше (>), меньше (<), не равно (<>), больше или равно (=>), меньше или равно (<=).

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

Примеры формул

Формула	Описание
=В1+В2	Складывает содержимое ячеек В1 и В2
=А1^(1/3)	Возводит в степень (1/3) содержимое ячейки А1
=СУММ(А1:А5)	Возвращает сумму значений из диапазона А1:А5

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.

Ошибки, возвращаемые формулами

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

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

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

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

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

А1	Относительная ссылка
$A$1	Абсолютная ссылка

Комбинация предыдущих типов (например: F$7 или $F7) – смешанные ссылки. Смешанные ссылки представляют собой комбинацию абсолютных и относительных ссылок. Например, $A1 – смешанная ссылка. В смешанной ссылке знаком $ защищается строка или столбец.

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

Абсолютные ссылки – это ссылки, которые при копировании не изменяются ($H$5).

Смешанные ссылки – это ссылки, которые сочетают в себе и относительную и абсолютную адресацию ($H5, H$5).

По умолчанию ссылки на ячейку в формулах рассматриваются как относительные.

Для обращения к группе ячеек используются специальные символы:

· : (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например. С4 : D6 – обращение к ячейкам С4, С5, С6, D4, D5, D6.

· ; (точка с запятой) – обозначает объединение ячеек. Например, D2:D4; D6:D8 – обращение к ячейкам D2, D3, D4, D6, D7, D8. Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

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

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

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

Индивидуальные задания

Краткая справка. Переключение режимов просмотра формул и просмотра значений формул на листе - Нажмите CTRL + ` (апостроф). Или на ленте - вкладка Формулы, группа Зависимости формул, кнопка Показать формулы.

Задание 1.

Создать таблицу расчета рентабельности продукции потребительского общества по кварталам. Константы вводить в расчетные формулы в виде абсолютной адресации. (Рис.1)

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

Если ячейка вдруг целиком заполнилась символами решётки, то варианта всего два: либо значение ячейки не помещается в нее, либо в ней введено отрицательное значение времени (случается, если формат ячейки - "Время").

Самая распространенная ошибка. Возникает тогда, когда функция поиска данных не находит искомое значение в диапазоне. Функции поиска данных это: ВПР, ГПР, ПОИСКПОЗ, ПРОСМОТР. Соответственно, решается либо изменением поискового запроса ("что ищем"), либо внесением в диапазон искомого значения. Однако, чаще всего эта ошибка вполне ожидаема и просто помогает проверить наличие того или иного значения в списке. Многие пользователи предпочитают выводить вместо нее пустые значения или какой-то значимый текст с помощью функции ЕСЛИОШИБКА. Например:

Возникает, когда в формуле используется нераспознанное программой имя. Именем Excel считает любой текст, не являющийся названием функции, ссылкой на ячейку/диапазон и не взятый в кавычки. Например, в формуле =СЕГОДНЯ()+СЕГ-A4 слово СЕГ будет распознано как имя.

Когда распознанного имени нет в списке именованных диапазонов, появляется данная ошибка. Способы решения:

Создать нужное имя в диспетчере имен;
Проверить правильность написания уже существующего имени;
Проверить, верно ли написаны функции рабочего листа (опечатки приведут к возникновению ошибки)

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

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

Возникает чаще всего тогда, когда в формуле использован неверный тип данных. Помните, что текст, число или дата - разные типы данных и обрабатываются по разному. Если передать, например, функции ДЕНЬНЕД() ссылку на ячейку с текстом, то появится указанная ошибка. То же самое произойдет, если попытаетесь вычесть из 100 слово "десять". Для исправления - проверьте все аргументы Соответствуют ли они требуемым типам данных? Если нет - укажите правильные типы.

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

выдаст ошибку. А формула:

будет работать безошибочно и вернет диапазон A5:B5

выдаст эту ошибку, так как аргумент "Ставка" не может быть отрицательным.

Для исправления - введите допустимый числовой аргумент.

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

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot

Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.

Для проверки ошибок необходимо выполнить следующие шаги:

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

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

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.

a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;

c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;

d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;

e) Чтобы завершить вычисления, нажмите кнопку Закрыть.

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

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

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

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.

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

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

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

Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.

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

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

Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.

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

В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.

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

Добавление ячеек в окно контрольных значений

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

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

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

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

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

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

Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.

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

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

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

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

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

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

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

Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

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

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

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

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.

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

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

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

Ввод простой формулы

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

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.

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

Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142.

Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.

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

Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.

Примечание: Для некоторых функций требуются так называемые аргументы. Аргументы — это значения, которые некоторые функции используют при вычислениях. Аргументы функции указываются в ее скобках (). Функция ПИ не требует аргументов, поэтому у нее пустые скобки. У некоторых функций несколько аргументов, в том числе необязательные. Аргументы разделяются точкой с запятой (;).

Например, функция СУММ требует только один аргумент, но у нее может быть до 255 аргументов (включительно).

Пример одного аргумента: =СУММ(A1:A10).

Пример нескольких аргументов: =СУММ(A1:A10;C1:C10).

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

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

Если знак равных не завис, то введите его в виде текста или даты. Например, если ввести СУММ(A1:A10),Excel отобразит текстовую строку СУММ(A1:A10) и не вычислит ее. Если ввести 11/2,Excel отобразит дату 2–ноя (предполагается, что формат ячейки — общий),а не деление 11 на 2.

Следите за соответствием открывающих и закрывающих скобок

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

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

Вводите все обязательные аргументы

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

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

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

Число уровней вложения функций не должно превышать 64

В функцию можно вводить (или вкладывать) не более 64 уровней вложенных функций.

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

Если формула содержит ссылки на значения или ячейки на других листах или в других книгах, а имя другой книги или листа содержит пробелы или другие небуквенные символы, его необходимо заключить в одиночные кавычки ('), например: ='Данные за квартал'!D3 или =‘123’!A1.

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

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

Указывайте путь к внешним книгам

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

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

В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК('C:\My Documents\[Показатели за 2-й квартал.xlsx]Продажи'!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8).

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

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

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

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

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

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

В Excel для Windows щелкните файл >параметры > формул или
В Excel для Mac щелкните меню Excel > параметры > проверки ошибок.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры Excel > Формулы.

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

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

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

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

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

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

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

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

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

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

Ячейки, содержащие годы,представленные как 2 цифры: ячейка содержит дату в текстовом текстовом виде, которую можно интерпретовать как неправильное век при ее использовании в формулах. Например, дата в формуле =ГОД("1.1.31") может относиться как к 1931, так и к 2031 году. Используйте это правило для выявления дат в текстовом формате, допускающих двоякое толкование.

Числа, отформатированные как текст,или с предшествующим апострофом: ячейка содержит числа, сохраненные как текст. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся как текст, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат. '=СУММ(A1:A10) является текстом.

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

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

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

Например, при использовании этого правила Excel отображает ошибку для формулы =СУММ(D2:D4), поскольку ячейки D5, D6 и D7, смежные с ячейками, на которые ссылается формула, и ячейкой с формулой (D8), содержат данные, на которые должна ссылаться формула.

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

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

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

В таблицу введены недопустимыеданные: в таблице есть ошибка проверки. Проверьте параметры проверки для ячейки, на вкладке "Данные" > "Средства работы с данными" > проверки данных.

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

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

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

Чтобы повторно проверить пропущенные ранее ошибки, щелкните Файл > Параметры > Формулы. В Excel для Mac щелкните меню Excel > параметры > проверки ошибок.

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

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

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

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

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

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

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

Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.

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

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

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

Совет: Добавьте обработчик ошибок, как в примере ниже: =ЕСЛИ(C2;B2/C2;0).

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

Если вы используете такую функцию, как ВПР, есть ли для искомого значения соответствие в диапазоне поиска? Скорее всего, нет.

=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)

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

Примечание: Если вы используете функцию, убедитесь, что ее имя написано неправильно. В данном случае слово СУММ введено с ошибкой. Удалите "а", и Excel исправит формулу.

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

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

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

Вы случайно удалили строку или столбец? Смотрите, что произошло после удаления столбца B в формуле =СУММ(A2;B2;C2).

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

Вы используйте математические операторы (+, -, *, / ^) с разными типами данных? В таком случае попробуйте использовать вместо них функцию. В этом случае =СУММ(F2:F5) поможет устранить проблему.

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

Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.

Примечание: Для каждой ячейки может быть только одно контрольное значение.

Добавление ячеек в окно контрольного значения

Выделите ячейки, которые хотите просмотреть.

Чтобы выделить все ячейки с формулами, на вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (вы также можете нажать клавиши CTRL+G или CONTROL+G на компьютере Mac). Затем выберите Выделить группу ячеек и Формулы.

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

Убедитесь, что вы выделили все ячейки, которые хотите отследить, и нажмите кнопку Добавить.

Чтобы изменить ширину столбца, перетащите правую границу его заголовка.

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

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

Удаление ячеек из окна контрольного значения

Если окно контрольного значения не отображается, на вкладке Формула в группе Зависимости формул нажмите кнопку Окно контрольного значения.

Выделите ячейки, которые нужно удалить.

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

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) будет легче понять, если вы увидите промежуточные результаты:

В диалоговом окне "Вычисление формулы"

Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.

Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.

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

Откройте вкладку Формулы и выберите Зависимости формул > Вычислить формулу.

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

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

Чтобы посмотреть вычисление еще раз, нажмите кнопку Начать сначала.

Чтобы закончить вычисление, нажмите кнопку Закрыть.

Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).

Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

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

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

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

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

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

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

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

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

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

Устранение ошибки: исправьте формулу.

При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

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

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

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

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Причины возникновения ошибки

Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

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

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

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

Читайте также: