Формулы в Excel

Рис. 10.  Изменения аргумента формулы

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

В формулу можно  добавлять новые операторы и  аргументы. Например, в существующую формулу в ячейку В14 в таблице на рис. 8 можно добавить оператор "/" (деление) и аргумент А20 (рис. 11).

Рис. 11.  Редактирование формулы

В процессе редактирования можно запускать мастер функций. Например, в существующую формулу  в ячейку D2 в таблице на рис. 1 можно добавить оператор "/" (деление) и в качестве аргумента округление значение ячейки А20. В этом случае после ввода знака "/" (деление) следует любым способом ввести функцию ОКРУГЛ и ее аргументы (рис. 12).

Рис. 12.  Использование мастера функций при редактировании формул

 

 

 

 

 

Перемещение и копирование формул

 

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

Кроме того, при  копировании ячеек с формулами  можно пользоваться возможностями специальной вставки. Это позволяет копировать только формулу без копирования формата ячейки.

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

Использование ссылок в формулах

Ссылки на ячейки других листов и книг

 

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

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

Например, в  формуле в ячейке D2 в таблице на рис. 13 использована ячейка А4 листа Курс текущей книги.

Рис. 13.  Ссылка на ячейку другого листа текущей книги

При переходе к  ячейке другой книги, имя файла в  квадратных скобках автоматически добавляется к ссылке на ячейку. Например, в формуле в ячейке D2 в таблице на рис. 14 использована ячейка А4 листа Курс файла ЦБР.xlsx.

Рис. 14.  Ссылка на ячейку другой книги

Относительные и абсолютные ссылки

 

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

Например, при копировании автозаполнением ячейки D2 (рис. 15) на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D4 будет формула =В4*С4 и т. д.

Рис. 15.  Копирование формул

В некоторых  случаях использование относительных  ссылок недопустимо. Например, в таблице на рис. 16 при копировании ячейки Е2 на нижерасположенные ячейки ссылка на ячейки В2 и С2 должны изменяться, а ссылка на ячейку А20 должна оставаться неизменной.

Рис. 16.  Использование абсолютных ссылок

Для того чтобы  ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ – служебный символ, показывающий абсолютную ссылку.

Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолютной, необходимо ввести $А$20.

Ссылка может  быть не только относительной или  абсолютной, но и смешанной.

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

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

 

 

 

 

 

 

 

 

 

 

Использование трехмерных ссылок

 

Трехмерные  ссылки используются при выполнении действий с данными из одной и  той же ячейки или диапазона ячеек  на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ('Комплект1:Комплект5'!D14) суммирует все значения, содержащиеся в ячейке D14 на всех листах в диапазоне от Комплект1 до Комплект5 включительно (рис. 17).

Рис. 17.  Использование трехмерных ссылок

1. Выделите ячейку, в которую нужно ввести функцию.
2. Введите = (знак равенства), имя функции, а затем открывающую круглую скобку.
3. Выделите ярлык первого листа, на который нужно создать ссылку.
4. Удерживая нажатой клавишу Shift, выделите ярлык последнего листа, на который необходимо создать ссылку.
5. Выделите диапазон ячеек, на которые нужно создать ссылку.
6. Подтвердите ввод формулы.

Трехмерные  ссылки могут быть использованы для  создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МИН, ПРОИЗВЕД, и некоторых других.

 

 

 

 

 

 

 

Использование имен ячеек и диапазонов

Использование имен

 

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

Присваивание  имен

 

Имя можно присвоить  ячейке или диапазону ячеек.

1. Выделите ячейку или диапазон ячеек.
2. В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя.
3. В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 18).

Рис. 18.  Присвоение имени ячейке

4. Для задания области действия имени в поле со списком Область выберите Книга или имя листа в книге.
5. При желании в поле Примечание можно ввести примечание к имени, которое затем будет отображаться в окне Диспетчера имен.

Для удобства работы рекомендуется создавать имена  короткие и хорошо запоминающиеся. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть буквами, числами, точками и знаками подчеркивания. Пробелы не допускаются. Также не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1. В имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2007 или Год.2007. Имя может содержать до 255 знаков. Имя может состоять из строчных и прописных букв, но Excel их не различает.

Имя можно присвоить  формуле или постоянному значению (константе). Например, имя "Скидка", которому присвоено значение 33 процента, можно использовать в любом месте для вычисления цены со скидкой.

Для присвоения имени формуле или константе  в поле Диапазон окна Создание имени следует ввести знак равенства (=) и формулу или константу (рис. 19).

Рис. 19.  Присвоение имени константе

Применение  имен

 

При создании формул короткие имена можно вводить  с клавиатуры.

Во избежание  возможных ошибок при использовании  имен в процессе создания формулы следует в группе Определенные имена вкладки Формулы щелкнуть кнопку Использовать в формуле и выбрать нужное имя в списке имен (рис. 20).

Рис. 20.  Вставка имени в формулу

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

Имена ячеек являются абсолютными ссылками.

 

 

 

 

 

 

Удаление имени

 

Ненужное или  ошибочное имя можно удалить.

1. В группе Определенные имена вкладки Формулы нажмите кнопку Диспетчер имен.
2. В окне Диспетчер имен выделите имя и нажмите кнопку Удалить (рис. 21).

Рис. 21.  Удаление имени

Проверка ошибок

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

Ошибки синтаксиса

 

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

Рис.22.  Сообщение об ошибке во введенной формуле

 

При обнаружении  лишних символов операторов, лишних или  недостающих скобок и т. п. выводится  сообщение об ошибке в окне предупреждения. Например, при вводе формулы в ячейку D2 в таблице на рис. 22 ошибочно введено два оператора (умножение и деление). В сообщение предлагается возможный вариант исправления ошибки. Чтобы принять его следует нажать кнопку Да, для самостоятельного исправления ошибки следует нажать кнопку Нет.

Рис. 23.  Сообщение об ошибке в аргументах функции

При вводе формул с использованием мастера функций или их редактировании Excel обнаруживает недостаток или избыток аргументов. В этом случае появляется соответствующее окно (рис. 23). Следует нажать кнопку ОК, а затем внести исправления в формулу с клавиатуры или нажать кнопку Вставить функцию в строке формул и изменить аргументы функции в окне Аргументы функции (см. рис. 4).

Рис. 24.  Сообщение о циклической ошибке

Excel обнаруживает  ошибки, связанные с циклическими  ссылками. Циклической ссылкой называется последовательность ссылок, при которой формула ссылается (через другие ссылки или напрямую) сама на себя. Например, в диапазон аргументов формулы СУММ в ячейке В7 в таблице на рис. 24 включена ячейка В8. При обнаружении циклической ссылки выходит окно сообщения. Нажатие кнопки ОК не приведет к исправлению ошибки. Лучше нажать кнопку Отмена и внести исправления самостоятельно.

Ошибки в  функциях и аргументах

 

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

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

Рис. 25.  Кнопка и меню кнопки Источник ошибки

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

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

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

 

 

 

 

 

 

Основные ошибки и некоторые возможные причины  их появления приведены в таблице.

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