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

В вычислениях могут использоваться разнообразные функции, например, для определения синуса или среднего арифметическо­го. При этом в качестве аргумента может выступать константа ссылка на ячейку или имя диапазона ячеек. 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 в остальные ячейки. Теперь все готово к вводу записей. Введите несколько записей и проверьте правильность определения значений. После ввода нескольких записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть дан­ные списка фильтрации, например, чтобы определить, какой клиент пользовался услугами фирмы наиболее часто или насколь­ко популярным был тот или иной товар. Используйте для этого функцию автоматической фильтрации. После вставки автофильт­ра выбор нужных записей не составит труда.

Рисунок 2.2 – Таблица списка заказов

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

1. Форматирование ячеек таблицы;

2. Задание формул;

3. Задание функций;

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

5. Функции СУММ, ПРОСМОТР, ЕСЛИ