Работа с функциями

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

Ссылки на другие листы и книги

Автозаполнение ячеек формулами

Если активизировать ячейку Е5 из предыдущего примера и протянуть ее за маркер заполнения (МЗ) вниз по столбцу, то:

· в ячейку Е6 автоматически введется формула =А2+В2*С5,

· в ячейку Е7 автоматически введется формула =А3+В3*С6,

· в ячейку Е8 автоматически введется формула =А4+В4*С7 и т.д.

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

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

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

Бывает, что один из элементов формулы не должен модифицироваться, например ссылка С4. В этом случае ссылка С4 должна быть записана так – $C$4 (знаки $$ вводятся автоматически при нажатии клавиши F4). Тогда при протяжке в ячейках: Е6, Е7, Е8 и т.д. наша формула будет принимать вид:

· Е6= А2+В2*$C$4

· Е7= А3+В3*$C$4

· Е8= А4+В4*$C$4

При любом протягивании МЗ активной ячейки ссылки А1 и В1 модифицируются (относительные ссылки), а ссылка $C$4 – не модифицируется. Такие ссылки называются абсолютными. В ссылке может быть зафиксирован один из её элементов. Например, $C4 или C$4. Такие ссылки называются смешанными. Знак $ определяет, какой элемент ссылки не модифицируется.

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

= А1+В1*ЛИСТ3!С4

Используемый лист может иметь любое название. Если в названии листа есть пробелы, то оно (название) заключается в кавычки. Связывание двух ячеек можно упростить, для чего на листе 1 в ячейке Е5 набрать знак = затем через корешок обратиться к Листу 3 и щелкнуть по ячейке С4.

Техника копирования, перемещения, удаления ячеек, содержащих формулу такая же, как ячеек содержащих данные (см. 6.2.2.) Но если в формуле содержатся относительные ссылки, то при копировании и перемещении они модифицируются. Рассмотрим на примере. Пусть в ячейке Е1 содержится формула

= Al+$Bl+C$l+$D$l.

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

=А4+$B4+E$l+$D$l.

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

Вызывается функция, как правило, с помощью Мастера функций(вкладка «Вставка») или кнопка fx в центре строки формул.

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

Например:

Существуют различные типы аргументов: число, текст, логическое значение (ИСТИНА и ЛОЖЬ), массивы, значение ошибки (например #Н/Д), или ссылку на ячейку (блок ячеек). В качестве аргументов используются константы формулы или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.

Диалоговое окно Мастер функций облегчает ввод функций при создании формул, содержащих функции (рис.7.16).

Рис.7.16. Окно мастера функций

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

В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 40.

Приведенная функция обеспечивает проведение расчета при условии:

«если среднее значение блока С5:С9 больше 40, ТО суммируются данные блока F2:F5; ИНАЧЕ 0»