Ввод функций на рабочий лист

Можно вводить функции на рабочий лист прямо с клавиатуры или с помощью команды Функция меню Вставка. Если выделитель ячейку и выбрать опции Вставка/Функция, Excel выведет окно диалога Мастер функций – шаг 1 из 2 (рис. 5). Открыть это окно можно также с помощью кнопки Вставка функции на строке ввода формул (рис. 5_1).

Рис. 5

Рис. 5_1

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

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

Рис. 6

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

На примере рассмотрим, как воспользоваться “Мастером функции”, для того чтобы написать формулу.

Пример (Рис. 6_1)

Дано:

Рис. 6_1

Необходимо вычислить:

1. Определить выручку по каждому товару.

2. Определить общую сумму выручки.

3. Определить среднее количество проданного товара.

Для того чтобы выполнить первое задание необходимо в ячейку F4 ввести формулу: =(D4-C4)*E4. Затем скопировать введенную формулу в смежные ячейки.

Для того чтобы определить среднее количество проданного товара воспользуемся “Мастером функции”. Выполним щелчок мышкой на ячейке E8, а затем щелкнем на кнопке вставка функции . На экране монитора появится диалоговое окно “Мастер функций шаг 1 из 2” (см. рис. 6_2). В этом окне в поле «Категория» необходимо выбрать Статистические (если пользователь не знает, к какой категории принадлежит искомая функция, то он может выбрать категорию Полный алфавитный перечень). После выбора категории в поле “Выберете функцию” появится список функций, среди которых выбираем СРЗНАЧ и щелкаем на кнопке ОК. На экране монитора появится диалоговое окно “Аргументы функции” (см. рис. 6_3). Далее необходимо щелкнуть в поле Число1 и ввести либо при помощи мыши, либо с клавиатуры диапазон ячеек Е4:Е7 и щелкнуть на кнопке ОК.

Для того чтобы определить общую сумму выручки можно воспользоваться мастером функции, а можно воспользоваться таким средством, как Автосумма. Рассмотрим, как воспользоваться средством “Автосумма”. Выполним щелчок мышкой на ячейке F9, затем щелкнем на кнопке Автосумма . В ячейке F9 появится формула =СУММ(F4:F8). В этой формуле нужно изменить диапазон суммирования, поэтому с помощью мыши выделяем диапазон F4:F7 и щелкаем на клавише <Enter> .

рис. 6_2

рис. 6_3

Абсолютные, относительные, смешанные адрес (ссылки)

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

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

Однако в некоторых формулах необходимо чтобы при копировании или перемещении формулы ссылка на ячейку не изменялась. В этом случае следует применять ссылку иного типа – абсолютную или смешанную ссылку. Абсолютная ссылка остается неизменной при изменении адреса ячейки с формулой. Чтобы заменить относительную ссылку на абсолютную или смешанную, необходимо ввести знак доллара ($) перед той частью ссылки, которая должна стать абсолютной. Примеры:

$A1 Всегда ссылается на столбец А, ссылка на строку может изменятся

A$1 Всегда ссылается на строку 1, ссылка на столбец может изменятся

$A$1 Всегда ссылается на на ячейку А1.

Ссылки вида А1 называются относительными, вида $A$1 – абсолютными, вида $A1 или A$1 – смешанными.

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

Внешние ссылки на ячейки

Excel разрешает ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других листов или других книг называются внешними ссылками (Январь!В2; Лист!В2). Ссылки на данные других приложений называются удаленными ссылками ('[Задание Excel.xls]Лист1'!$B$2).

Рассмотрим как включить в формулу внешние ссылки с помощью мыши. На листах рабочей книги январь и февраль приведена информация о кол-ве произведенной продукции в соответствующих месяцах. На листе итого необходимо вывести суммарное количество произведенной продукции за январь и февраль (см. рис. 6_4). Для этого необходимо выполнить следующие действия:

1. Выделить ячейку В2 на листе Итого. Набрать знак равенства (=) и щелкнуть на листе Январь.

2. Выделить ячейку В2 и ввести знак +. Щелкнуть на листе Февраль и выделить ячейку В2.

3. Для завершения ввода формулы нажать клавишу <ENTER>.

Ссылки на ячейки, находящиеся в других рабочих книгах, обязательно включают имя книги, заключенной в прямоугольные скобки. Например, ='[Задание Excel.xls]Лист1'!$B$2.

Рис. 6_4