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

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

 

При копировании формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ “$”, т.е. относительные ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4.

 

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

 

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

 

Выделить формулу – Копировать – Вставить – Специальная вставка

 

Распространение формул

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

1. Установите курсор в ячейку с формулой.

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

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

 

Общения об ошибках

 

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

 

Ячейки заполнены знаками # # # # # # # # # #

 

Ячейка может заполняться набором символов “решетки” (#) по одной из следующих причин:

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

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

 

Ошибка #ДЕЛ/0! – возникает, при делении на ноль

Для исключения вывода ошибки можно воспользоваться функцией ЕСЛИ, которая проверяет наличие “неправильного” значения в ячейке B2:

=ЕСЛИ(B2=0; ““; –B3/B2)

 

Данная формула выводит “пустое” значение (““), если ячейка пустая или содержит 0; в противном случае выводится вычисленное значение корня уравнения.

 

Другое решение заключается в использовании функции ЕСЛИ для проверки существования любой ошибки. Следующая формула также отображает “пустое” значение в случае получения ошибки любого типа:

=ЕСЛИ(ЕОШИБКА(–B3/B2); ““; –B3/B2)

С особенностями функции ЕОШИБКА ознакомьтесь самостоятельно.

Ошибка Имя?

возникает в следующих случаях:

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

формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?.

 

Ошибка #Знач!

 

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

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

 

Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7.

В качестве аргументов функции использованы данные несоответствующего типа (например, в функции ЕСЛИ вместо условия использовано число или текст).

В качестве аргумента функции используется диапазон, тогда как аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6).

 

Ошибка #Число! возникает в одном из двух случаев:

 

1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);

2) формула содержит слишком большое или слишком маленькое значение. Excel поддерживает величины в пределах от 1Е-307 до 1Е307 (от 10^-307 до 10³⁰⁷).

Ошибка #Ссылка!

 

Ошибка #Ссылка! возникает в случае использования формулой ошибочной ссылки на ячейку. Данная ошибка может встречаться в следующих ситуациях.

1. Ячейка, на которую ссылалась формула, была удалена. Например, следующая формула отображает ошибку #Число!, если удалена строка 20, столбец A или столбец B:

=A20/B20

Формула скопирована в новое место, где относительные ссылки на ячейки становятся недействительными. Например, если формулу = A1–1 в ячейке A2 скопировать в ячейку A1, формула вернет ошибку #Число!, так как в ней будет присутствовать ссылка на несуществующую ячейку.

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

 

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

 

Поиск ошибок в формулах

Вкладка «Формулы» - команда «Зависимости формул»

 

Если необходимо увидеть все ячейки, влияющие на формулу, выбираем команду «Влияющие ячейки», появятся стрелки

Если необходимо посмотреть, в каких формулах участвует ячейка, выделяем ячейку и выбираем команду «Зависимые ячейки»

Команда «Показать формулы» отображает формулы, а не числовые значения.

Команда «Проверка наличия ошибок» выявляет ошибки или определяет источник ошибки

Команда «Вычислить формулу» позволяет проверять вычисление по шагам.

 

Мастер функций

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

 

Функции состоят из имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ.

 

Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9 — ее аргументы.

 

Эта формула суммирует числа в ячейках A2, и A9. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, например функция ПИ(). При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов.

 

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

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

Вызвать Мастер функций можно командой «Вставить функцию» вкладка «Формулы» или с помощью кнопки Вставка функции на строке ввода формул, будет открыто окно диалога Мастер функций – шаг 1 из 2.

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

 

Excel введет знак равенства (если вы вставляете функцию в начале формулы), имя функции и круглые скобки. Затем будет открыто второе окно диалога мастера функций, в котором необходимо установить аргументы функции (в нашем случае ссылки на A2 и A9).

Второе окно диалога Мастера функций содержит по одному полю для каждого аргумента выбранной функции. Справа от каждого поля аргумента отображается его текущее значение (21 и 33). Текущее значение функции отображается внизу окна диалога (54). Нажмите кнопку ОК или клавишу Enter, и созданная функция появится в строке формул.

 

Аргумент может содержать значение одной ячейки, диапазон или интервал ячеек. Для указания значения одной ячейки следует щелкнуть по этой ячейки, для указания диапазона выделить диапазон, для указания интервала – указать ячейки при нажатой клавише «Enter».

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

Математические функции

– возвращают значение математических функций

Тригонометрические функции – sin, cos, tan, asin, acos

ABS Возвращает модуль (абсолютную величину) числа. EXP Возвращает число e, возведенное в указанную степень. ФАКТР Возвращает факториал числа. НОД Возвращает наибольший общий делитель. НОК Возвращает наименьшее общее кратное. LN Возвращает натуральный логарифм числа. LOG Возвращает логарифм числа по заданному основанию. LOG10 Возвращает десятичный логарифм числа. ОСТАТ Возвращает остаток от деления. РИМСКОЕ Преобразует арабские цифры в римские в виде текста.   ЗНАК Возвращает знак числа.   КОРЕНЬ Возвращает положительное значение квадратного корня.   КОРЕНЬПИ Возвращает квадратный корень из значения выражения (число * ПИ).

Функции округления

ОКРВНИЗ Округляет число до ближайшего меньшего по модулю значения.

ОКРУГЛТ Возвращает число, округленное с требуемой точностью.

ЦЕЛОЕ Округляет число до ближайшего меньшего целого.

ОТБР	Отбрасывает дробную часть числа.
ОКРУГЛ	Округляет число до указанного количества десятичных разрядов.
ОКРУГЛВНИЗ	Округляет число до ближайшего меньшего по модулю значения.
ОКРУГЛВВЕРХ	Округляет число до ближайшего большего по модулю значения.

 

РАДИАНЫ Преобразует градусы в радианы.
ГРАДУСЫ Преобразует радианы в градусы.