Функции MS Excel

Функция
Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.

Категории функций, предоставляемые MS Excel
Финансовые
Дата и время
Математические
Статистические
Ссылки и массивы
Работа с базой данных
Текстовые
Логические
Проверка свойств и значений

Аргументы функций
Чтобы использовать функцию, нужно ввести ее как формулу или часть формулы в ячейку рабочего листа. После имени функции в круглых скобках указывается список аргументов. Аргументы отделяются друг от друга точкой с запятой " ; ".
Аргументами функции могут быть числа, текст, адреса ячеек и блоков ячеек, а также выражения, содержащие другие функции. Некоторые функции могут иметь необязательные аргументы, которые можно опускать.

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

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

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

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

Статистические функции
СРЗНАЧ(число1; число2; ...)
Возвращает среднее арифметическое своих аргументов.
Число1, число2, ... — от 1 до 255 аргументов, для которых вычисляется среднее.
МАКС(число1;число2; ...) / МИН(число1;число2; ...)
Возвращает наибольшее / наименьшее значение из набора значений.
Число1, число2,... — от 1 до 255 чисел, среди которых требуется найти наибольшее/наименьшее.
Аргументы должны быть либо числами, либо содержащими числа именами, массивами или ссылками.

Пример

Логические функции. «ЕСЛИ».
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ.
Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Например, если данный аргумент — строка «В пределах бюджета», а аргумент «лог_выражение» имеет значение ИСТИНА, то функция ЕСЛИ отобразит текст «В пределах бюджета». Аргумент «значение_если_истина» может быть формулой.
Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Например, если данный аргумент — строка «Превышение бюджета», а аргумент «лог_выражение» имеет значение ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Аргумент «значение_если_ложь» может быть формулой.

Пример

Пример

Вложенные друг в друга функции ЕСЛИ
В качестве значений аргументов «значение_если_истина» и «значение_если_ложь» можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ.
Пример: Если средний балл студента менее или равен 3, то студент стипендию не получает. Если средний балл – от 3 до 4, то студент получает минимальный размер стипендии, если же средний балл превышает 4, то студент получает две минимальных стипендии.

Пример

Логические функции. «И».
И(логическое_значение1; логическое_значение2; ...)
Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Логическое_значение1, логическое_значение2, ... — от 1 до 255 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Аргументы должны быть логическими значениями (такими, как ИСТИНА или ЛОЖЬ), массивами или ссылками, содержащими логические значения

Пример

В «ЕСЛИ» вложена функция «И»

Логические функции. «ИЛИ».
ИЛИ(логическое_значение1;логическое_значение2;...)
Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА или ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Логическое_значение1, логическое_значение2,... — от 1 до 255 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Аргументы должны принимать логические значения (ИСТИНА или ЛОЖЬ) или быть массивами либо ссылками, содержащими логические значения .

Пример

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

Аргументы финансовых функций
Финансовые функции Excel для расчета операций по вкладам и кредитам имеют одинаковый набор аргументов:
Ставка — процентная ставка за период.
Кпер — общее число периодов платежей.
Пс — начальное значение, текущая стоимость вклада (приведенная к текущему моменту стоимость, т. е. сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой).
Бс — будущая стоимость вклада в конце срока операции (требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 , т. е. для займа, например, значение бс равно 0).
Плт — постоянный периодический платеж, взнос (выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам).
Период — порядковый номер периода: значение должно находиться в интервале от 1 до «кпер».
Тип — тип платежа: число 0 или 1, обозначающее, когда должна производиться выплата; 0 или опущен - выплата производится в конце периода, 1 - в начале периода.

Кредитные расчеты
Кредит - это деньги, переданные в распоряжение кому-нибудь на определенный период времени, в течение которого он должен выплачивать владельцу денег определенные проценты, а в конце указанного периода возвратить всю вложенную сумму.
Как правило, кредит погашается одинаковыми платежами в конце каждого расчетного периода. Вычислить платеж С можно с помощью функции ПЛТ(ставка;кпер;пс;бс;тип).
Выплаты по основному займу за период i (от 1 до n) вычисляет функция ОСПЛТ(ставка;период;кпер;пс;бс;тип).
Выплаты по процентам (доход банка за период i) определяет функция ПРПЛТ(ставка;период;кпер;пс;бс;тип).
Величина периодических платежей равна сумме выплат по основному займу и по процентам за любой период i : ПЛТ=ОСПЛТ+ПРПЛТ.

ПЛТ(ставка;кпер;пс;бс;тип)
ПЛТ(ставка;кпер;пс;бс;тип)
Возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.
Ставка — процентная ставка за период.
Кпер — общее число выплат (периодов) по ссуде.
Пс — начальное значение, текущая стоимость вклада.
Бс — будущая стоимость вклада в конце срока операции (требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 , т. е. для займа, например, значение бс равно 0).
Тип — тип платежа: 0 или опущен - выплата производится в конце периода, 1 - в начале периода.

ОСПЛТ(ставка;период;кпер;пс;бс;тип)
Возвращает величину платежа в погашение основной суммы по инвестиции за данный период.
ПРПЛТ(ставка;период;кпер;пс;бс;тип) Возвращает сумму платежей процентов по инвестиции за данный период.
Период — порядковый номер периода: значение должно находиться в интервале от 1 до «кпер».
В ПЛТ, ОСПЛТ, ПРПЛТ должно быть соответствие в единицах измерения аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Составные элементы платежа

Другие финансовые функции
БС(ставка ;кпер;плт;пс;тип)
Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
КПЕР(ставка;плт;пс;бс;тип)
Возвращает общее количество периодов выплаты.
ПС(ставка;кпер;плт;бс;тип)
Возвращает приведенную (к текущему моменту) стоимость инвестиции. Например, в момент займа его сумма.
СТАВКА(кпер;плт;пс;бс;тип;прогноз)
Возвращает процентную ставку за один период.

План погашения кредита (эадача)
Разработка планов погашения кредитов – одна из важнейших и часто встречающихся на практике задач. Как правило, кредит погашается одинаковыми платежами, равномерно распределенными во времени. Основная задача планирования поступлений (выплат) по кредитам сводится к исчислению составных элементов платежей и распределению их во времени.
Задача:
Банком выдан кредит в 10 000 ден.ед. на 5 лет под 12% годовых, который должен быть погашен равными долями, выплачиваемыми раз в конце каждого года.
Разработать план погашения кредита с указанием, какая часть платежа идет на погашение основного долга, а какая - на выплату процентов.

Решение эадачи с помощью MS Excel