Материал: 64

16

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

тера функций (рисунок 4).

В ситуации, когда следует воспользоваться функцией с известным именем, в диалоговом окне достаточно выбрать требуемую категорию функций, затем в поле Выберите функцию: выделить имя функции и нажать кнопку ОК для перехода ко второму шагу работы Мастера функций. В рамках этого процесса вначале полезно просмотреть перечень функций категории 10 недавно использовавшихся, так как не исключено, что нужная функция была востребована в предыдущих сеансах работы с MS Excel. Если возникают трудности при выборе категории функции, необходимо воспользоваться категорией Полный алфавитный перечень.

На втором шаге работы Мастера функций в поля диалогового окна Аргументы функции вводятся нужные сведения (рисунок 5), затем нажимается кнопка ОК.

Рисунок 5 – Диалоговое окно второго шага Мастера функций

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

18

Скидка для стоимости перевозок рассчитывается для каждой заявки. Она зависит от количества купленных билетов и составляет 5 % от исходной стоимости заказа (произведение количества билетов на цену одного билета), если число билетов больше 20.

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

ЕСЛИ(УСЛОВИЕ; ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2),

где УСЛОВИЕ – логическое выражение, принимающее значение ИСТИНА

или ЛОЖЬ;

ВЫРАЖЕНИЕ1 – выражение, которое выполняется, если результатом выполнения УСЛОВИЯ является ИСТИНА;

ВЫРАЖЕНИЕ2 – выражение, которое выполняется, если результатом выполнения УСЛОВИЯ является ЛОЖЬ.

ВЫРАЖЕНИЕ1 и ВЫРАЖЕНИЕ2 могут быть константами, ссылками на адреса ячеек, формулами.

В рассматриваемом примере формула в ячейке D2 будет иметь вид:

=C2*$C$10*ЕСЛИ(С2>20;5%;0)

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

Функции ЕСЛИ могут быть вложенными друг в друга. Предположим, что величина скидки зависит не только от количества купленных билетов, как в предыдущем примере, но и от даты поездки (31 декабря 2012 г. она составляет 10 % от стоимости заказа). При этих условиях формула для расчёта величины скидки будет выглядеть следующим образом:

=C2*$C$10*ЕСЛИ(C2>20;ЕСЛИ(B2=$B$3;10%;5%);0)

В данной формуле значение даты 31.12.2012 г. в параметре УСЛОВИЕ для вложенной функции ЕСЛИ вводится с помощью абсолютной ссылки $B$3 на адрес ячейки, в которой расположена эта дата.

19

Можно создавать различные комбинации логических функций ЕСЛИ, И,

ИЛИ:

ЕСЛИ( И (УСЛОВИЕ1; УСЛОВИЕ2;…); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2) ЕСЛИ(ИЛИ(УСЛОВИЕ1;УСЛОВИЕ2;…);ВЫРАЖЕНИЕ1;ВЫРАЖЕНИЕ2)

В первом случае для выполнения ВЫРАЖЕНИЯ1 необходимо, чтобы значение ИСТИНА принимали все логические выражения УСЛОВИЕ1, УСЛОВИЕ2 и т. д., во втором случае достаточно, чтобы это требование выполнялось хотя бы для одного условия.

Допустим, скидка на стоимость перевозок величиной 5 % от стоимости заказа будет предоставляться только для поездок 31 декабря 2012 г. при количестве купленных билетов более 15. Формула для расчёта скидки:

=C2*$C$10*ЕСЛИ(И(B2=ДАТАЗНАЧ( 31.12.2012 );C2>15);5%;0)

В данной формуле значение даты в параметре УСЛОВИЕ для функции ЕСЛИ вводится в явном виде. Функция ДАТАЗНАЧ используется для преобразования даты в число.

Для расчёта величины скидки на стоимость перевозок при значении скидки 2,5 % от стоимости заказа при поездках после 29 декабря 2012 г. или количестве купленных билетов более 20, можно создать формулу

=C2*$C$10*ЕСЛИ(ИЛИ(B2>ДАТАЗНАЧ( 29.12.2012 );C2>20);2,5%;0)

При вычислении стоимости каждого заказа (с учётом сделанной скидки) округлим с точностью до копеек полученные значения. Для решения этой задачи используем функцию ОКРУГЛ, синтаксис которой имеет вид:

ОКРУГЛ(ЧИСЛО; КОЛИЧЕСТВО РАЗРЯДОВ),

где ЧИСЛО – округляемое значение (числовая константа, адрес ячейки, в которую введены число, формула, результатом выполнения которой является числовое значение);

КОЛИЧЕСТВО РАЗРЯДОВ – количество дробных разрядов, до которого округляется ЧИСЛО.

20

Если параметр КОЛИЧЕСТВО РАЗРЯДОВ положителен, округление выполняется до указанного количества десятичных разрядов после запятой. При равенстве параметра КОЛИЧЕСТВО РАЗРЯДОВ нулю округление реализуется до целых. Если параметр КОЛИЧЕСТВО РАЗРЯДОВ отрицателен, округление осуществляется до десятков, сотен, тысяч и т. д. Примеры округления числовой константы с помощью функции ОКРУГЛ:

ОКРУГЛ(725,89;1) = 725,9 ОКРУГЛ(725,89;0) = 726

ОКРУГЛ(725,89;–1) = 730

ОКРУГЛ(725,89;–2) = 700 ОКРУГЛ(725,89;–3) = 1000

Следовательно, формула для вычисления стоимости каждого заказа (с учётом сделанной скидки), округлённой с точностью до копеек, например, в ячейке Е2 будет следующей:

=ОКРУГЛ(С2*$C$10–D2;2)

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

В ячейках строки Итоги введём формулы для расчёта общего числа поданных заявок, среднего количества купленных билетов, максимальной величины сделанной скидки, суммарной стоимости билетов (с учётом скидки). Эти формулы будут иметь вид:

=СЧЁТ(B2:B7) =СРЗНАЧ(С2:С7)

=МАКС(D2:D7)

=СУММ(E2:E7)

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

СУММЕСЛИ(ДИАПАЗОН ПРОВЕРКИ;КРИТЕРИЙ; ДИАПАЗОН СУММИРОВАНИЯ),

где ДИАПАЗОН ПРОВЕРКИ – диапазон ячеек рабочего листа, в котором проверяется выполнение параметра КРИТЕРИЙ;

КРИТЕРИЙ – константа, адрес ячейки, выражение или функция;