Использование функций

<1 2 345 6 7 >

В вычислениях могут использоваться разнообразные функции, например, для определения синуса или среднего арифметического. При этом в качестве аргумента может выступать константа ссылка на ячейку или имя диапазона ячеек. Excel 7.0 предоставляет в распоряжение пользователей множество специальных функций, в которые эти формулы уже встроены. Указание значений, к которым должна быть применена та или иная функция, происходит путем задания аргументов. Способ задания всех функций один и тот же, различие заключается только в количестве аргументов:

=ИМЯ ФУНКЦИИ (Аргументы) [2]

Так, для вычисления суммы предназначена функция с выразительным названием СУММ. Если Вы запомнили название функции, то можете смело вводить его в ячейку с клавиатуры. Аргументы функции должны быть указаны после ее названия в круглых скобках (например, СУММ(А1;С4)). Введите открывающую круглую скобку и выделите с помощью мыши ячейки, содержимое которых должно использоваться в качестве аргументов. Адрес выделенного диапазона ячеек сразу отобразится в позиции курсора ввода. Завершите задание функции вводом закрывающей скобки и нажатием клавиши [Enter]. Ячейки, содержимое которых используется в качестве аргументов, могут принадлежать как к смежным, так и к несмежным диапазонам. Адрес диапазона смежных ячеек задается путем указания адресов первой и последней ячеек (адреса двух ячеек разделяются двоеточием). Адреса несмежных ячеек разделяются точкой с запятой. Символы разделения вставляются в формулу автоматически при указании диапазона ячеек с помощью мыши.

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

С функциями наиболее удобно работать, используя Мастер функций. В этом случае аргументы задаются в диалоговом окне (при вводе функции с клавиатуры можно допустить ошибку, например, пропустить обязательный аргумент). При работе с Мастером функций сначала указывается функция, а затем — ее аргументы. Для запуска Мастера функций предназначены команда Функция из меню Вставка и комбинация клавиш [Shift+РЗ].

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

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

Если краткой информации недостаточно, нажмите в диалоговом окне кнопкуСправка (или воспользуйтесь клавишей [FI]). В результате на экране отобразится страница справочной подсистемы с информацией о выделенной функции.

После выбора функции выполните щелчок на кнопке Далее для перехода в следующее диалоговое окноМастера функций, в котором должны быть заданы аргументы. В этом диалоговом окне программа информирует пользователя о том, какие аргументы должны быть указаны обязательно (обязательные аргументы), а какие — нет. По завершении ввода аргументов следует нажать кнопкуГотово, и результат вычисления будет представлен в соответствующей ячейке. Теперь перейдем от теории к практике. В столбце Е должно быть указано наименование товара. Вряд ли Вы откажетесь от автоматической вставки данных с помощью формулы. Для этого введите в ячейку Е2 формулу:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Товар) [3]

Думаем, что приведенная формула требует небольших пояснений. Функция ЕСЛИ обеспечивает проверку содержимого ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если ячейка D2 содержит номер товара, произойдет поиск номера товара в диапазоне Номер, а в ячейку D2 будет занесено соответствующее значение диапазона Товар. Для этого мы и используем функциюПРОСМОТР. Вставьте данную формулу в ячейку Е2 с помощью Мастера функций (для активизации Мастера функций воспользуйтесь одноименной кнопкой в панели инструментов Стандартная), если Вы не уверены, что аргументы в ячейке указаны правильно. Обратите внимание, что для ячейки D2 задана комбинированная ссылка, благодаря которой при копировании формулы будет изменен номер строки.

Столбец F предназначен для указания количества товара, заказанного клиентом, а столбец G (поле Цена за ед.) — для указания цены единицы товара. Значения цен можно вставить с помощью формулы, аналогичной формуле в ячейке Е2. Формула в ячейке G2 должна иметь такой вид:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Цена) [4]

Целесообразно скопировать формулу из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона. Значение (пробел), представленное в ячейке как результат применения формулы, убедит Вас в ее правильности. Наверное, Вы уже заметили, что иногда при указании адреса ячейки в ссылке используется символ $. О том, в каких случаях следует применять данный символ, читайте в следующих строках.

Копирование формул

При копировании формул содержащиеся в них ссылки автоматически заменяются новыми только в том случае, если они являются относительными. По умолчанию в Excel применяются относительные ссылки. Они используются в формулах для указания адреса ячейки относительно позиции ячейки, содержащей формулу. При изменении позиции формулы относительные ссылки будут содержать адреса других ячеек. Если при перемещении или копировании формулы адреса ячеек не должны изменяться, следует использовать абсолютные ссылки. Абсолютную ссылку можно установить, задав имя или вставив символ $ в адрес ячейки (например $А$1). Можно создать комбинацию абсолютной и относительной ссылок, например $А1. В этом случае при копировании формулы в ссылке изменяется только строка, а столбец все время остается тем же. Итак, продолжим выполнять наш пример. В ячейке Н2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры, поскольку невозможно предвидеть, какой будет очередность заказов, и задать автоматическое (с помощью одной из формул Excel) заполнение ячеек этого столбца. Автоматически мы заполним ячейки столбцов Название фирмы и Скидка. Теперь анализируемой ячейкой будет ячейка Н2. Введите в ячейку I2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Фирма) [5]

В столбце Сумма заказа следует указать общую стоимость заказа без учета скидок. Для этого умножьте значения в полях Количество и Цена. С помощью логической функции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки. Таким образом, формула в ячейке J2 должна иметь следующий вид:

= ЕСЛИ(F2=""; ""; F2*G2) [6]

Если в ячейке F2 указано количество единиц заказываемого товара, то ячейка J2 должна содержать произведение значений ячеек F2 и G2. В противном случае она должна остаться незаполненной.

Величину скидки (столбец Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Скидка) [7]

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

= ЕСЛИ($J2=""; ""; J2-J2*K2) [8]

Значительная часть работы по заполнению таблицы уже сделана. Осталось выполнить форматирование: центрировать значения, назначить стили (денежный и процентный), а также заполнить ячейки, в которые должны быть введены значения, голубым цветом, а ячейки, в которых значения будут определены на основе формул, — желтым. Выделите ячейки В2— L2 и выберите в менюПравка команду Заполнить/Вниз. Таким образом мы задали копирование значений ячеек сроки 2 в остальные ячейки. Теперь все готово к вводу записей. Введите несколько записей и проверьте правильность определения значений. После ввода нескольких записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть данные списка фильтрации, например, чтобы определить, какой клиент пользовался услугами фирмы наиболее часто или насколько популярным был тот или иной товар. Используйте для этого функцию автоматической фильтрации. После вставки автофильтра выбор нужных записей не составит труда.