Использование имен ячеек и диапазонов в формулах
Если в формуле используются имена ячеек иди диапазонов, вы можете набрать имя вместо адреса или выбрать имя из списка, чтобы OpenOffice Calc вставила его автоматически.
Задание:
Перейдите в ячейку F8.
В строку формул методом указания ячеек введите формулу (суммируйте итоговые значение окладов и надбавок) . Убедитесь, что вместо адресов ячеек отображаются их имена рис. 16.
Рис. 16
Перейдите в ячейку F9.
В строке формул наберите выражение =SUM(.
Рис. 17
Выберите команду «Вставкаð Названиеð Вставить». В диалоговом окне «Вставка имени», в котором перечислены все определенные в данной рабочей книге имена, выберите имя «Суммы» и нажмите кнопку «ОК» рис. 17.
Закройте скобку.
Рис. 18.
Нажмите клавишу
Сохраните рабочую книгу.
Логические функции. Функция IF (ЕСЛИ)
Логические функции выполняют условное вычисление по формулам и позволяют осуществить реализацию простых алгоритмов принятия решений.
Функции IF (ЕСЛИ) возвращает одно значение, если заданное условие при вычислении дает значение TRUE (ИСТИНА), и другое значение, если FALSE (ЛОЖЬ). Функция IF (ЕСЛИ) используется при проверке условий для значений и формул.
Синтаксис функции:
IF (ЕСЛИ) (Лог_выражение; Значение_если_истина; Значение_если_ложь)
Предположим, что вам нужно подсчитать в рабочей таблице комиссионные по результатам продаж какого-либо товара. Если продавец продал продукции более, чем на $100 000, то ставка его комиссионных составляет 7,5%, если меньше, то 5%. Без использования функции вам пришлось бы создавать две разные формулы и правильно употреблять их для каждого значения объема продаж. Ниже приведена функция, которая позволяет рассчитать комиссионные с учетом суммы продаж.
IF (А1<100000; A1*0,05; A1*0,075), где
IF (ЕСЛИ) – функция, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет;
А1<100000 – условие;
A1*0,05 – значение, которое возвращается, если условие выполняется;
A1*0,075 – значение, которое возвращается, если условие не выполняется.
Таким образом, если значение ячейки А1 (сумма продаж) будет меньше 100000 (условие выполняется) программа умножит значение ячейки А1 на 0,05 (ставка комиссионных), если же сумма продаж будет больше 100000 (условие не выполняется), программа умножит значение ячейки А1 на 0,075.
Задание:
Откройте окно Мастер функций, выберите категорию Логические функции.
На Листе3 создайте таблицу табл.3.
Таблица 3
| Норма продаж | 150 000,00 | ||
| Ставка комиссионных | 0,055 | ||
| Ставка премиальных | 0,075 | ||
| Менеджеры | Продажи | Комиссионные | |
| Орлов | 149 823,00 | ||
| Воробьев | 162 023,00 | ||
| Скворцов | 209 123,00 | ||
| Синицын | 122 354,00 | ||
| Грачев | 83 351,00 | ||
| Петухов | 204 861,00 | ||
| Ганиев | 150 000,00 | ||
| Самойлов | 110 500,00 | ||
| Дроздов | 220 120,00 | ||
| Кротов | 170 450,00 |
Отформатируйте таблицу согласно рисунку
Рис. 19.
Вычислите комиссионные от продаж, если ставка комиссионных составляет 5,5%, ставка премиальных – 7,5%, норма продаж – 150 000р. Для этого выполните следующие действия.
Активизируйте ячейку С6 (комиссионные Орлова), вызовите Мастер функций, найдите в нем функцию IF (ЕСЛИ).
В поле Логическое выражение введите условие B6<В1 (продажи Орлова меньше нормы продаж).
В поле Значение если true (истина) введите выражение B6*B2 (если продажи Орлова меньше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку комиссионных).
В поле Значение если false (ложь) введите выражение B6*B3 (если продажи Орлова больше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку премиальных).
Проверьте правильность ввода аргументов рис.20
Рис. 20
Рассчитайте комиссионные по остальным продавцам. Для этого используйте функцию Автозаполнение, предварительно предусмотрев абсолютные ссылки.
Рис. 21
Сохраните рабочую книгу.
Статистические функции. Функция COUNTIF (СЧЕТЕСЛИ) (СЧЕТЕСЛИ)
К категории статистических функций отнесено огромное количество функций – 80, позволяющих выполнить разнообразные расчеты. Многие из этих функций достаточно специализированные, но некоторые из них полезны и для тех, кто мало знаком со статистикой.
Функция COUNTIF (СЧЕТЕСЛИ) подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
Синтаксис функции: COUNTIF (СЧЕТЕСЛИ) (Диапазон; Критерий).
Диапазон - диапазон, в котором нужно подсчитать ячейки.
Критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".
Задание:
Откройте окно Мастер функций, выберите категорию Статистические.
Просмотрите список функций, ознакомьтесь с их описанием.
Дополните электронную таблицу согласно рис. 22.
Рис. 22.
Вычислите количество менеджеров, продавших больше нормы. Для этого выполните следующие действия.
Перейдите в ячейку Е7. Вызовите Мастер функций, найдите в нем функцию COUNTIF (СЧЕТЕСЛИ).
В поле Диапазон укажите диапазон ячеек В6:В15 (в этом диапазоне ведется поиск значений, превышающих 150 000).
В поле Критерий введите ячейку Е6 (в этой ячейке содержится условие, выполнение которого проверяется в диапазоне В6:В15).
Проверьте правильность ввода аргументов.
Рис. 23.
Вычислите количество менеджеров, которые имеют объем продаж, равный норме или превышающих норму.
Рис. 24
Сравните полученные результаты с рисунком.
Сохраните рабочую книгу.
Математические функции. Функция SUMIF (СУММЕСЛИ)
В программу OpenOffice Calc включены 50 функций данной категории, что вполне достаточно для выполнения сложных математических расчетов.
Функция SUMIF (СУММЕСЛИ) суммирует ячейки, заданные критерием.
Синтаксис функции: SUMIF (СУММЕСЛИ)(Диапазон;Критерий;Диапазон_суммирования).
Диапазон - диапазон вычисляемых ячеек.
Критерий - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".
Диапазон_суммирования - фактические ячейки для суммирования.
Ячейки в Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе Диапазон удовлетворяют критерию. Если Диапазон_суммирования опущен, то суммируются ячейки в аргументе Диапазон.
Задание:
Откройте окно Мастер функций, выберите категорию математических функций.
Просмотрите список функций, ознакомьтесь с их описанием.
На Листе3 создайте таблицу табл. 4. Верхняя левая ячейка таблицы соответствует ячейке А20.
Таблица 4
| Месяц | Регион | Продажи | Итоги по регионам | |||
| Январь | Север | Север | ||||
| Февраль | Юг | Юг | ||||
| Январь | Запад | Запад | ||||
| Февраль | Восток | Восток | ||||
| Март | Север | ВСЕГО | ||||
| Январь | Юг | |||||
| Февраль | Запад | |||||
| Март | Восток | Итоги по месяцам | ||||
| Февраль | Север | Январь | ||||
| Март | Юг | Февраль | ||||
| Март | Запад | Март | ||||
| Январь | Восток | ВСЕГО | ||||
| ИТОГО |
Отформатируйте таблицу согласно рисунку.
Рис. 25.
Вычислите итоговые значения по регионам и по месяцам, а также суммарные значения продаж. Для этого выполните следующие действия.
Активизируйте ячейку F21 (итоги по северу), вызовите Мастер функций, найдите в нем функцию SUMIF (СУММЕСЛИ).
В поле Диапазон укажите диапазон ячеек В21:В32 (в этом диапазоне ведется поиск критерия «Север»).
В поле Критерий введите ячейку Е21 (с этим значением происходит сравнение содержимого диапазона В21:В32).
В поле Диапазон суммирования укажите диапазон С21:С32 (при нахождении в диапазоне В21:В32 значения, отвечающего критерию, происходит суммирование соответствующих значений из диапазона С21:С32).
Проверьте правильность ввода аргументов рис.26.
Рис. 26.
Рассчитайте итоги по остальным регионам и месяцам (используйте Автозаполнение и абсолютные ссылки).
Вычислите итоговые значения.
Сравните полученные результаты с рис. 27.
Рис. 27.
Сохраните рабочую книгу.
Задание для самостоятельной работы:
Вставьте новый лист, выполнив команду Вставкаð Лист, создайте таблицу табл. 5
Таблица 5
| Фамилия | Имя | Отчество | Отдел | Оклад | Премия | Ставки | |
| Андреева | Анна | Семеновна | Бухгалтерия | 0,2 | |||
| Бутаков | Андрей | Викторович | Сбыт | 0,15 | |||
| Горбатов | Иван | Андреевич | Склад | ||||
| Ерохин | Иван | Олегович | Склад | ||||
| Иванов | Сергей | Александрович | Бухгалтерия | ||||
| Крылова | Ольга | Сергеевна | Кадров | ||||
| Маметов | Иван | Алексеевич | Сбыт | ||||
| Петрова | Мария | Павловна | Кадров | ||||
| Чарушин | Семен | Максимович | Склад | ||||
| Яровцева | Елена | Викторовна | Бухгалтерия | ||||
| ИТОГО | |||||||
| Кол-во сотрудников | Суммы окладов | ||||||
| Бухгалтерия | Бухгалтерия | ||||||
| Сбыт | Сбыт | ||||||
| Склад | Склад | ||||||
| Кадров | Кадров | ||||||
| ИТОГО | |||||||
| >6000 | |||||||
| Иван |
Отформатируйте таблицу согласно рис. 28.
Рис. 28.
Определите общее количество сотрудников по каждому из отделов.
Подсчитайте количество сотрудников, имеющих оклад больше 6000 р.
Определите количество сотрудников с именем «Иван».
Подсчитайте общую сумму окладов сотрудников каждого отдела.
Рассчитайте величину премии: для сотрудников, имеющих оклад меньше 5000 р., премия равна 20% от оклада, для остальных сотрудников – 15% от оклада.
Сохраните рабочую книгу.
Одно из назначений электронных таблиц - это вычисления, поэтому сейчас будут рассмотрены основные правила написания формул.
Как уже отмечалось, ввод формулы начинается со знака равенства, далее пишется сама формула. К примеру: =4+16 . Записав такую формулу и нажав Enter , мы увидим в ячейке число 20. Конечно, формулы без переменных обычно не имеют особого смысла, поэтому теперь посмотрим, как использовать переменные, в качестве которых в OpenOffice.org Calc служат адреса ячеек. К примеру, если в A1 мы записали число 20, то после записи в B1 формулы =A1^2 и нажатия Enter в ячейке B1 появится число 400.
Основные арифметические операции, доступные OpenOffice.org Calc :
- "+" - сложение;
- "-" - вычитание;
- "*" - умножение;
- "/" - деление;
- "^" - возведение в степень;
- ":" - задание диапазона.
Кроме этих операций, в OpenOffice.org Calc доступен обширный набор функций следующих категорий:
- работа с базами данных;
- обработка времени и дат;
- финансовые;
- информационные;
- логические;
- математические;
- работа с массивами;
- статистические;
- текстовые;
- дополнительные.
Для удобства написания формул в OpenOffice.org Calc разработан автопилот функций.
В окне автопилота можно набирать функции и проверять правильность их набора; в зависимости от выбора категории список доступных функций будет изменяться. Кроме перечисленных выше категорий, для удобства добавлены Все и Недавно использованные .
В поле редактирования "Формула" отображается текущая формула, которую можно непосредственно редактировать - а можно, поставив курсор в необходимую позицию, дважды щёлкнуть по имени функции из списка, и выбранная функция вставится в окно ввода. Останется только либо ввести аргумент с клавиатуры, либо нажать кнопку:
В закладке Структура , набранная формула развернута в дерево, что очень помогает в редактировании формул, позволяя отслеживать порядок вычисления формулы.
Для случая, когда формула достаточно проста (содержит знаки "+" , "-" , "*" , "/" , "^" ), но состоит из относительно большого числа переменных, рассмотрим следующий пример:
Пусть требуется вычислить A1+C5*B4 ; для этого:
Нажмите "=", после чего выберите с помощью стрелок управления курсором ячейку A1 (при первом же нажатии на клавишу управления курсором появится красный прямоугольник-курсор). Затем нажмите "+" и выберите C5 , нажмите "*" и, наконец, выберите B4 . Таким способом с помощью клавиатуры можно быстро формировать формулы (ячейки можно выбирать и указателем мыши).
После ввода "=" и какой-либо буквы OpenOffice.org Calc автоматически высвечивает имя функции, начинающейся на эту букву. Эта возможность позволяет набирать не всю формулу, а только первые её буквы, а дальше, если предложенная функция является именно той, которая нужна, останется только нажать "Enter" .
Бывает так, что при вводе формул в качестве их аргументов требуется передавать не адрес ячейки, а целую область - к примеру, необходимо просуммировать все значения в столбце A , начиная с адреса A2 по адрес A11. Конечно, можно написать =A2+A3+...+A10+A11 - но гораздо проще и в любом случае правильнее будет написать "=Су", затем воспользоваться подсказкой (Сумм ) и, нажав "Enter", в скобках вписать диапазон "A2:A11" .
Область рабочего листа задается указанием адреса левой верхней ячейки, далее ставится двоеточие и указывается правая нижняя ячейка. Область можно задать и с помощью мыши.
Функция SUM является встроенной функцией OpenOffice Calc и служит для подсчета суммы как отдельных чисел, так одного или нескольких диапазонов.
Синтаксис
SUM(число1 или диапазон1; число2 или диапазон2;…;число30 и диапазон30)
В OpenOffice Calc помощью функции SUM можно вычислить сумму тридцати различных аргументов, в качестве которых могут быть числа или диапазоны чисел.
Диапазон в SUM записывается через двухточие. Сперва указывается адрес ячейки с первым числом в диапазоне, а затем адрес ячейки с последним числом диапазона. Например диапазон можно записать как A1:A10. Выглядеть формула будет следующим образом SUM(A1:A10). Данная формула суммирует все значения в диапазоне с A1 по A10.
Также с помощью функции Sum в OpenOffice Calc можно вычислить сумму значений отдельных ячеек. В этом случае ячейки записываются через точку с запятой. Например формула SUM(A1;B2;C6) суммирует значения в ячейках A1, B2 и C6. Вместо отдельных ячеек также можно записать отдельные диапазоны. Например, функция SUM(A1:A7;B3:B8;D3:D9) подсчитывает сумму всех значений, указанных в 3 диапазонах.
В данной статье рассматривается применение функции Sum для подсчета значений в одном диапазоне. Подсчет значений нескольких чисел и диапазонов будет рассматриваться в статье «Математическая функция SUM. Суммирование нескольких чисел или диапазонов».
Рассмотрим применение функции SUM на конкретном примере. Предположим, что есть таблица с условными данными по приходу денежных средств на расчетный счет фирмы в ноябре 2012 года за поставленные ранее товары.
В первом столбце указана дата прихода, во втором день недели, в третьем столбце непосредственно сумма прихода за конкретный день в рублях.
1. Выделим ячейку C32, которая находится под столбцом «Приход, руб.». Для этого наводим на нее курсор мыши и щелкаем левой кнопкой. Далее в эту ячейку будет записана формула для подсчета итоговой суммы в столбце.
2. Откроем в Calc Мастер функций, последовательно выбрав следующие пункты Вставка/Функция/Мастер функций. Или нажав сочетание клавиш Ctrl+F2.
4. Нажимаем два раза на SUM. В строке «число1» нажимаем справа на значок со стрелкой и выделяем диапазон C2:C31.
5. После нажатия кнопки OK в ячейке C32 появится сумма значений в столбце. Сумма равна 11 566 508.
Если нажать два раза левой кнопкой мышки на ячейку, то в ячейке отобразится формула, а рамкой будет выделен диапазон ячеек, в которых подсчитывается сумма. После нажатия клавиши Enter вновь появится итоговое значение диапазона.
При необходимости диапазон формулы можно изменить следующим образом.
Если два раза нажать левой клавишей мыши на формулу, то в ячейке будет видна формула и будет выделен диапазон суммируемых ячеек. Если навести курсор мыши на нижнюю правую точку рамки, то удерживая нажатой левую клавишу мыши сдвинем нижнюю границу диапазона до ячейки C10. После нажатия Enter произойдет автоматический пересчет результатов.
При задании формул наряду с основными арифметическими операциями, OpenOffice Calc предоставляет множество специальных функций, которые можно ввести в интерактивном режиме при помощи Автопилота функций . OpenOffice Calc поддерживает, в частности, многие статистические методы: от регрессионных расчетов до доверительных интервалов. Особенно интересна возможность изменять отдельные параметры в вычислениях, зависящих от многих факторов, и прослеживать, как это влияет на результат. Это так называемые расчеты "что было бы, если". Например, при расчете кредита путем простого изменения периода, процентной ставки или сумм выплаты можно сразу же увидеть, как изменяются остальные факторы.
Как и все другие средства работы с электронными таблицами, OpenOffice Calc позволяет использовать относительные и абсолютные ссылки. Перед каждым значением, которое должно использоваться как абсолютное, ставится знак доллара $. Для превращения текущей ссылки, в которой находится курсор в строке ввода, из относительной в абсолютную и наоборот, следует использовать комбинацию клавиш Shift+F4.
Практически все функции (за исключением математических и некоторых статистических) в программе OpenOffice Calc локализованы, т. е. используют русскоязычные имена. К таковым относятся и все функции из раздела Дата&Время (однако функция ПАСХАЛЬНОЕВОСКРЕСЕНЬЕ() определяет дату католической, а не православной Пасхи).
Для ввода функций в ячейку можно воспользоваться мастером функций. Выберите ячейку, в которую надо ввести функцию, и нажмите кнопку , которая находится на панели инструментов, или выберите команду Функция... из меню Вставка . Перед вами появится окно Автопилот функций , в котором надо выделить функцию и нажать кнопку Далее>> или OK , после чего появится окно ввода аргументов выбранной функции. Функцию можно вводить не только с помощью мастера функций, но и вручную, если вы помните, как она называется и сколько у нее параметров.
Для задания суммы чисел, находящихся в столбце или строке следует использовать кнопку . В ячейке появится формула вида =СУММ(...). Программа пытается догадаться, каков интервал суммирования. Если вас не устраивает предложенный диапазон, то выделите левой кнопкой мыши требуемую область ячеек так, чтобы вокруг нее появилась красная рамка. То же самое можно сделать и вручную, указав диапазон ячеек в строке формул. Отметим, что пустые ячейки при суммировании трактуются как содержащие нулевые значения.
Познакомимся поподробнее с функцией ДАТА, обеспечивающей ввод дат в электронную таблицу. OpenOffice Calc хранит такие данные в виде чисел, но отображает в ячейке в формате даты, выравнивая их (аналогично числам) по правому краю. Конечно, можно отформатировать ячейку, содержащую дату, и в числовом формате. Синтаксис функции - ДАТА(Год; Месяц; День). Год - целое число от 1600 до 3000, при вводе от 0 до 29 добавляется 2000, а при вводе числа от 30 до 99 - 1900. Месяц - число от 1 до 12, задающее номер месяца. День - число от 1 до 31, которое устанавливает день месяца. Когда значения месяца и дня больше допустимых, они пересчитываются на следующую позицию (год, месяц) с переполнением. Формула =ДАТА(00;12;31) дает 31.12.2000, а при вводе =ДАТА(00;13;31) получится дата 31.01.2001.
В программе OpenOffice Calc можно просто ввести даты в формате "месяц.число.год" (без указания кавычек, иначе ввод будет интерпретирован как текст), например, "5.17.2" для 17 мая 2002 года. В этом случае любой ввод со значениями, выходящими за границы допустимых, трактуется не как дата, а как текст. Функция ТДАТА() возвращает дату и время в соответствии с системным временем компьютера, которые обновляются при каждом пересчете документа.
Пример
Вычислим количество дней, часов, минут и секунд, прошедших с 0 часов некоторой даты. Откройте новый документ электронной таблицы и введите в ячейку B1 дату, например, "1.17.84". В ячейку B3 введите формулу =ТДАТА()-A1, которая по умолчанию отформатируется как дата. Поскольку нам нужно получить разницу между двумя датами в виде числа дней, то необходимо отформатировать ячейку B3 как целое число. Введите остальные формулы: в B4 =B3*24 для часов, в B5 =B4*60 для минут и в B6 =B5*60 для секунд.
Если все выполнено правильно, то время, прошедшее с указанного момента, вычислено и отображено в различных единицах. При этом значение, выраженное в секундах, относится к тому моменту времени, когда была зафиксирована формула в ячейке B6. Нажмите на клавишу F9 (для принудительного пересчета таблицы) и не отпускайте ее - вы увидите как изменяется вывод.
Для отображения формул, примененных при этих вычислениях, в столбец D помещена функция ФОРМУЛА(), аргументом которой является адрес соответствующей ячейки.
При создании сложных таблиц удобно использовать именованные ячейки. Если нужно составить, к примеру, формулу для вычисления НДС, то она будет более понятной в виде "= Сумма * Размер_налога", чем в форме "= A5 * B12", где ячейка A5 содержит данные о сумме, а ячейка B12 - о размере налога.
Для того чтобы задать имя области (ячейки), необходимо эту область сначала выделить и с помощью пункта Имена - Задать меню Вставка (или при помощи комбинации клавиш Ctrl+F3) вызвать диалоговое окно Присвоить имя . Имя должно начинаться с буквы, отличаться от стандартных имен ячеек и не может содержать пробелов. После ввода имени следует нажать кнопку Добавить . В этом же диалоге можно задать имена других областей, введя сначала имя, а затем выделив ячейки листа, которые должны получить это имя. В нем можно присвоить имена даже часто используемым формулам или элементам формул.
Мощным инструментом при работе с электронными таблицами является подбор параметра . С помощью этого инструмента можно узнать значение, которое при подстановке в формулу дает желаемый результат. Для того чтобы воспользоваться подбором параметра нужно иметь формулу с несколькими постоянными значениями и одним переменным. Лучше всего можно объяснить подбор параметра на примере.
Для вычисления годовых процентов создайте небольшую таблицу, в которой из значений капитала (Капитал), количества лет (Количество_лет) и процентной ставки (Ставка) получается результат - сумма процентов за год (Процент). Формула для вычисления результата имеет вид
Процент = Капитал * Количество_лет * Ставка / 100
Ячейкам лучше всего дать имена, чтобы формула имела осмысленный вид. В примере сначала вычисляется, какова будет сумма процентов при вложенном капитале 150 000 р. и процентной ставке 7,5% за год.
Теперь, считая, что процентная ставка (7,5%) и количество лет (1) не изменяются, определим, какого размера должен быть капитал, чтобы давать определенные годовые проценты, скажем, 15 000 р. Выделите ячейку Проценты и выберите в меню Сервис пункт Подбор параметра . Появится одноименное окно, в поле Ячейка формул которого уже указана нужная ячейка.
Поместите курсор в поле диалога изменяемая ячейка. В таблице выделите ячейку, значение которой изменяется, т. е. B2. В текстовом поле Конечное значение диалога введите желаемый результат формулы и нажмите кнопку OK. Если параметр был успешно подобран, то отобразится результат, который можно занести в таблицу.
Цели занятия:
- Дидактические : познакомиться с финансовыми функциями OpenOffice.org Calc и научиться применять их для решения задач.
- Развивающие :
- развивать мышление учащихся;
- развивать трудолюбие, аккуратность.
- Воспитательные : осуществлять экономическое воспитание.
Тип занятия: комбинированный.
План занятия
- Организационный момент – 2 мин.
- Опрос – 10 мин.
- Изучение нового материала – 45 мин.
- Самостоятельная работа – 30 мин.
- Итог урока, выставление оценок, домашнее задание – 3 мин.
ХОД ЗАНЯТИЯ
1. Организационный момент
2. Опрос
– Прежде чем перейти к изучению новой темы, повторим предыдущую тему.
Вопрос |
Примерный ответ |
|
| 1. | Что такое функция? | Функция – это переменная величина значение которой зависит от других величин (аргументов). Функция имеет имя и, как правило, аргументы, которые записываются в круглых скобках следом за именем функции. Скобки обязательная принадлежность функции, даже если у нее нет аргументов. |
| 2. | Что может являться аргументом функции. | В качестве аргументов функции могут использоваться числа, адреса ячеек, диапазоны ячеек, арифметические выражения и функции. |
| 3. | Что такое мастер функции? | Мастер функций предназначен для упрощения ввода функций. |
| 4. | Назовите способы вызова мастера функций. | 1 способ. Вставка
– Функция
2 способ Нажатие кнопки fx на панели формул. |
| 5. | Какие категории функций вы знаете? | Математические, Статистические, Логические. |
| 6. | Перечислите все известные вам математические функции. | COS, SIN, TAN, SUM, LOG, SORT |
| 7. | Перечислите все известные вам статистические функции. | AVERAGE, MAX, MIN. |
3. Изучение нового материала
– Сегодня на занятии мы рассмотрим 3 финансовые функции: FV, PV, NPER.
Определение будущей стоимости
Функция FV вычисляет будущее значение вклада с постоянными выплатами и постоянным процентом. Общая форма записи этой функции:
FV (Процент;Кпер;Выплата;ТЗ;Тип)
Аргументы функции имеют следующий смысл:
Процент – процент прибыли за период. Если проценты начисляются один раз в год, то это годовая процентная ставка. Если начисление процентов производится чаще, то годовая процентная ставка делится на количество начислений в году. Ставка в 20 процентов при формировании функции может быть представлена как 20% или 0,2.
Кпер – общее число периодов выплат годовой ренты;
Выплата – дополнительная выплата, производимая в каждый период. Это значение не может меняться в течение всего периода выплаты ренты;
ТЗ – текущее значение или общая сумма всех будущих платежей с настоящего момента. Если аргумент пс опущен, то он полагается равным 0.
Тип – это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент опущен, то он полагается равным 0. Если аргумент равен 0, то выплата производится в конце периода, если аргумент равен 1, то выплата производится в начале периода.
При использовании функции FV
должны использоваться согласованные единицы
измерения для аргументов Процент
и
Кпер
. Так, если производятся
ежемесячные платежи по четырехгодичному займу
из расчета 12 процентов годовых, то Процент
должен быть 0.12/12, а Кпер
должно быть
4 х 12 = 48. Если производятся ежегодные платежи по
тому же займу, то Процент
должен
быть 0,12, а Кпер
должно быть равно 4.
Функцию FV
можно рассматривать с двух
точек зрения: заемщика и кредитора. Все
аргументы, означающие деньги, которые Вы платите
(например, депозитные вклады), представляются
отрицательными числами; деньги, которые Вы
получаете (например, дивиденды), представляются
положительными числами.
Пример 1.
Необходимо определить будущую стоимость
единовременного вклада с текущей стоимостью 20000
рублей, на который в течение 10 лет ежегодно
начисляются сложные проценты по ставке 7
процентов годовых. Начисление процентов
производится 1 раз в год.
Расчет реализуется с использованием функции FV
:
FV(7%;10;;–20000). В результате расчета получается
величина 39343,03 рубля.
В записи аргументов функции последовательно
показаны:
7% – годовая процентная ставка;
10 – число лет начисления процентов;
далее опущен аргумент, показывающий, что вклад
может ежегодно пополняться (по условию задачи –
вклад единовременный);
–20000 – величина единовременного вклада, знак
минус показывает, что это наши затраты.
Пример 2.
Организация взяла заем размером 100000 рублей на
срок 3 года под 30 процентов годовых. Необходимо
определить сумму, подлежащую возврату.
Расчет величины возвращаемых средств ведется с
помощью функции:
FV(30%;3;;100000).
Расчет дает ответ: возвращать придется –219700
рублей. Результат расчета на экране
воспроизводится со знаком минус, показывающим
что это действительно деньги, которые мы отдаем.
Пример 3.
Предположим, фирма собирается зарезервировать
деньги для специального проекта, который будет
осуществлен через год. Фирма открывает счет,
единовременно на него вносятся 10000 рублей под 6%
годовых (это составит в месяц 6/12, или 0,5%). Далее
предполагается вносить 1000 рублей в начале
каждого месяца в течение следующих 12 месяцев.
Необходимо определить сумму денег на счете через
год.
Для расчета может быть использована функция
FV(0,5%; 12; – 1000; – 10000; 1). Результат расчета равен
23014,02 рубля.
По сравнению с предыдущими примерами, у функции
появился новый аргумент – 1000, показывающий, что
осуществляется регулярное поступление денег на
вклад, и 1, показывающий, что пополнение денежных
средств на счете осуществляется в начале месяца.
Пример 4.
Есть два варианта инвестирования средств на 4
года. Первый вариант предполагает начисление 26
процентов годовых в начале каждого года. Второй
вариант – начисление 38 процентов годовых в конце
каждого года. Фирма имеет возможность ежегодно
вносить 300000 рублей. Необходимо определить, какой
вариант предпочтительнее.
Расчет по первому варианту инвестиций
предполагает использование функции FV(26%;4;–300000;;1),
дающей результат 2210534,93 руб.
Расчет по второму варианту – FV(38%;4;–300000).
Результат расчетов – 2073741,60 руб. Сопоставление
вариантов позволяет сделать вывод, что вариант с
26 процентами годовых при их начислении в начале
года оказывается более предпочтительным, чем 38
процентов годовых при начислении процентов в
конце года.
Определение текущей стоимости
Функция PV вычисляет текущую стоимость инвестиций. Общая форма записи этой функции:
PV (Ставка;Кпер;Платеж;Остаток;Тип).
Ставка – фиксированная процентная ставка. Если проценты начисляются один раз в год, то это годовая процентная ставка. Если начисление процентов производится чаще, то годовая процентная ставка делится на количество начислений в году. Ставка в 20 процентов при формировании функции может быть представлена как 20% или 0,2;
Кпер – общее число периодов платежей. Если проценты начисляются раз в год, то число периодов равно числу лет. Если начисления происходят чаще, то число лет должно быть умножено на количество начислений процентов на протяжении года;
Платеж – фиксированная сумма платежа за каждый период. Это значение не может меняться в течение всего периода выплат;
Остаток – остаток средств в конце выплат. Если Остаток опущен, он полагается равным 0 (будущая стоимость займа равна 0). Например, если необходимо накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц;
Тип
При использовании функции PV
должны
использоваться согласованные единицы
измерения для аргументов Ставка
и Кпер
.
Так, если производятся ежемесячные платежи по
четырехгодичному займу из расчета 12 процентов
годовых, то Ставка
должна быть 0.12/12,
а Кпер
должно быть 4 х 12 = 48. Если
производятся ежегодные платежи по тому же займу,
то Ставка
должна быть 0.12, а Кпер
должно быть равно 4.
Функцию PV можно рассматривать с двух точек зрения: заемщика и кредитора. Все аргументы, означающие деньги, которые Вы платите (например, депозитные вклады), представляются отрицательными числами; деньги, которые Вы получаете (например, дивиденды), представляются положительными числами.
Пример 1.
Рассматриваются два варианта покупки
оборудования. Первый вариант предполагает, что
сразу будет выплачена вся сумма 99000 рублей.
Второй вариант – рассрочка на 15 лет при
ежемесячной выплате по 940 рублей в конце каждого
месяца. Годовая процентная ставка – 8 процентов.
Необходимо определить, какой вариант
предпочтительнее.
Для решения задачи необходимо привести
стоимость потока будущих регулярных платежей к
текущей стоимости. Расчет выполняется с
использованием функции PV(8%/12; 15*12; – 940). Результат
равен 98362.16 руб.
Смысл аргументов функции:
8%/12 – величина ежемесячно начисляемых
процентов;
15*12 – количество ежемесячных платежей на
протяжении 15 лет;
940 – величина ежемесячного платежа, знак <–>
показывает, что это деньги, которые платим мы.
Расчет показывает, что при годовой ставке 8
процентов оказывается более выгодным платить в
рассрочку. В этом случае текущая стоимость всех
периодических платежей 98362.16 рублей меньше
единовременной суммы 99000 рублей. Возможно, при
другой годовой процентной ставке это окажется
неверным.
Пример 2.
Организации потребуется 5000000 рублей через 12
лет. В настоящее время фирма располагает
средствами и готова разместить деньги на депозит
единым вкладом с тем, чтобы через 12 лет иметь
необходимые деньги. Годовая процентная ставка –
12 процентов. Необходимо определить, сколько
средств размещать на депозите.
Расчет ведется с помощью функции PV
(12%;12;;5000000).
В результате расчета получается число – 1283375.46
руб. Знак <–> означает, что первоначально
деньги отдавались. Через 12 лет будет получено
5000000 рублей.
Определение сроков платежа
Вычисляет общее количество периодов выплат для данного вклада с постоянными выплатами и постоянным процентом..
NPER(Процент;Выплата;НС;КС;Тип)
Аргументы функции имеют смысл:
Процент – фиксированная процентная ставка за период. Если проценты начисляются один раз в год, то это годовая процентная ставка. Если начисление процентов производится чаще, то годовая процентная ставка делится на количество начислений в году. Ставка в 20 процентов при формировании функции может быть представлена как 20%, или 0,2;
Выплата – сумма выплат, выполняемая каждый период. Это значение не может меняться в течение всего периода выплат;
НС – единовременная сумма платежа в начале срока. Если аргумент НС опущен, то он полагается равным 0;
КС – остаток средств, выплачиваемых в конце срока. Если КС опущено, оно полагается равным 0 (будущая стоимость займа равна 0). Например, если необходимо накопить 50000 руб. для оплаты специального проекта в течение ряда лет, то 50000 руб. это и есть будущая стоимость. Делается предположение о сохранении заданной процентной ставки;
Тип – число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент опущен, то он полагается равным 0. Если аргумент равен 0, то выплата производится в конце периода, если аргумент равен 1, то выплата производится в начале периода.
Пример 1.
Для обеспечения будущих расходов фирмы
создается фонд, величина которого должна
составить 1000000 рублей. Деньги на создание фонда
поступают ежегодно в конце года в виде
фиксированных отчислений в 160000 рублей. На
поступившие платежи начисляется 11,18 процентов
годовых. Необходимо определить, через сколько
лет будет накоплена необходимая сумма.
Решение находится с помощью функции:
NPER
(11,18%;–160000;;1000000)
Результат получается равным 5 годам.
Пример 2.
Фирма получила ссуду в размере 66000 рублей под 36
процентов годовых и предполагает рассчитываться
ежемесячно по 6630 рублей в конце каждого месяца.
Необходимо определить срок полного расчета по
полученной ссуде.
Решение определяется функцией
NPER
(36%/12; – 6630;66000;0) и равно 12 периодам
(месяцам).
Смысл аргументов функции:
36%/12 – месячная процентная ставка;
– 6630 – ежемесячные выплаты по ссуде;
66000 – первоначальный размер полученной ссуды;
0 – будущий размер ссуды (равен 0, так как
произойдет полный расчет).
4. Самостоятельная работа <Приложение 1 >
5. Итог занятия, выставление оценок, домашнее задание.
Литература, использованная при подготовке к занятию
- Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности: Учебное пособие для сред.проф.образования. – М.: Издательский цент «Академия», 2006. – 192с.
- Михеева Е.В. Информационные технологии в профессиональной деятельности: Учебное пособие Для сред.проф.образования. – М.: Издательский цент «Академия», 2005. – 384с.
- Чуканов С.Н. Анализ динамики финансовых потоков в MS Excel: Учебное пособие. – Омск, 1999. – 142 с.