Приложение 2 Виды ошибок при задании формул

Формула представляет собой синтаксическую конструкцию со знаком “=” в первой позиции, набирается с клавиатуры или кнопкой Изменить формулу. По этому признакуExcelзапускает Палитру формул, которая проверяет синтаксис, автоматически исправляет распространенные ошибки и обеспечивает справочными сведениями.

Формула может содержать следующие элементы:

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

Таблица 1

Операции в формулах Excel

Группа	Операции
Операции над ссылками	: ссылка на диапазон ; объединение ссылок  (операция пробел) пересечение диапазонов
Арифметические операции (по приоритету)	унарный – % ^ * и / + и -
Операции сравнения	>, <, =, >=, <=, <>
Текстовая операция	& объединение текста

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

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

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

При создании формул используются следующие типы ссылок.

Абсолютная ссылка – фиксированный адрес ячейки на листе, например, $А$3. Абсолютные адреса предваряются знаком $.

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

Продолжение прил. 2

Смешанная ссылка. Например, А$1 – абсолютная ссылка на первую строку, $A1 – абсолютная ссылка на первый столбец.

Ссылки на ячейки других листов той же книги, Лист2!А1.

Внешние ссылки на другие книги, [Книга2]Лист1!A1 на открытую книгу, иначе с полным путем к книге на локальном диске, ‘С:\Мои документы\[Книга2]Лист1’!$A$1.

Удаленные ссылки на данные других приложений; сетевые адреса и URL (Uniform Resource Locator, унифицированный указатель ресурса, адреса интернета).

Трехмерные ссылки на несколько листов, Лист2:Лист13!A1.

Циклические ссылки – это последовательность ссылок, при которой формула ссылается на себя напрямую или через другие ссылки. Вычисление следующих значений по предыдущим в таких замкнутых последовательностях называется итерацией. Excel прекращает итерационный процесс по заранее установленным ограничениям на количество итераций, по умолчанию 100, или на невязку (разность значений с двух соседних итераций), по умолчанию 0,001.

Ячейки, на которые есть ссылки в указанной ячейке, называются влияющими. Ячейки, в которых есть ссылки на указанную ячейку, называютсязависимыми.

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

При копировании формулы абсолютные ссылки не обновляются. Относительные ссылки при копировании формулы в другую ячейку обновляются автоматически так, что взаимное расположение влияющих ячеек и формулы сохраняется. Например, формула в ячейке A3 =$A$1+A2 после копирования в ячейку В3 =$A$1+B2. Копирование формулы в примыкающий диапазон можно быстро осуществить протаскиванием маркера заполнения.

Диапазон – это несколько ячеек листа. Ссылка на непрерывный прямоугольный диапазон задается как адрес левой верхней ячейки :адрес правой нижней ячейки. В ссылке на несмежный диапазон поддиапазоны перечисляются через;и выделяются на листе с нажатым Ctrl.

Формула может содержать одну или несколько функций, связанных между собой арифметическими операторами или вложенных друг в друга. Функции – это встроенные в программу Excel формулы, которые выполняют вычисления по своим аргументам. Обращение к функции идет по имени функции и в скобках списку аргументов через запятую. Вставку функции можно выполнить, используя меню Вставка –Функция… либо одноименную кнопку на панели инструментов Стандартная. В результате будет запущен двухшаговый Мастер функций, содержащий всю коллекцию функцийExcelв более чем 10 различных категорий.

Продолжение прил. 2

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

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

Ошибка #####

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

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

Меры по устранению ошибки– увеличьте ширину столбца путем перемещения границы, расположенной между заголовками столбцов. Кроме того, можно изменить формат числа ячейки (меню Формат –Ячейки…, вкладка Число).

2. При работе с датами получается отрицательное значение.

Меры по устранению ошибки– введите правильную формулу.

Ошибка #ЗНАЧ!

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

1. Вместо числового или логического значения введен текст.

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

2. После ввода или редактирования формулы массива нажимается клавиша Enter.

Меры по устранению ошибки– для редактирования формулы укажите ячейку или диапазон ячеек, содержащих формулу массива, нажмите клавишуF2, а затем – комбинацию клавишCtrl+Shift+Enter.

3. Использована неправильная размерность матрицы данных в одной из матричных функций.

Меры по устранению ошибки– укажите правильную размерность при работе с матрицами данных.

Ошибка #ДЕЛ/0!

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

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

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

Продолжение прил. 2

Ошибка #ИМЯ?

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

1. Используемое в формуле имя было удалено или не определено.

Меры по устранению ошибки– определите имя. Для этого выполните команду Имя меню Вставка

2. Имеется ошибка в написании имени функции.

Меры по устранению ошибки– исправьте написание имени функции вручную или вставьте функцию с помощью мастера функций.

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

Меры по устранению ошибки– заключите текст формулы в двойные кавычки. Например, ессли в ячейке А1 содержится значение 200, я в ячейке В1 – формула = «Итого:»&А1, то в ячейке В1 будет выведен результат Итого:200.

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

Меры по устранению ошибки– исправьте формулу так, чтобы во всех ссылках на диапазон ячеек использовался знак двоеточия, например =СУММА(А1:С10).

Ошибка #Н/Д

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

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

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

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

Меры по устранению ошибки– задайте все необходимые аргументы функции.

3. Используется пользовательская функция, обращение к которой приводит к ошибке.

Меры по устранению ошибки– проверьте, что книга, использующая функцию листа, открыта, и убедитесь в правильности работы функции (проведите отладку в программной средеVBA).

Ошибка #ССЫЛКА!

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

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

Окончание прил. 2

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

Ошибка #ЧИСЛО!

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

1. В функции с числовым аргументом используется неприемлемый аргумент.

Меры по устранению ошибки– проверьте правильность использования в функции аргументов.

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

Меры по устранению ошибки– используйте другое начальное приближение для этой функции.

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

Меры по устранению ошибки– измените формулу так, чтобы в результате ее вычисления получалось число, попадающее в диапазон от -110³⁰⁷до 110³⁰⁷.

Ошибка #ПУСТО!

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

1. Использован оператор, задающий пересечение диапазонов, не имеющих общих ячеек.

Меры по устранению ошибки– задайте правильно размерность пересекающихся диапазонов или не используйте оператор пересечения, если диапазоны не являются таковыми. ВMicrosoftExcelоператором пересечения диапазонов является пробел ( ).