МУ ИСЭ ЭК 2014 (часть 1)
.pdf42
Расширенный фильтр.
Для выделения из таблицы более сложных условий можно воспользоваться командой Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из таблицы записей белгородских производителей хлеба. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю – имя поля, в нижнюю – знак отношения (>, <, >= ,<= ,< >) и значение. В нашем случае в ячейкуK1 введем Наименование товара, в
ячейку K2 – Хлеб, в ячейку L1 – Город, в ячейку L2 – Белгород (рис. 18, а).
а) |
б) |
Рис. 18. Область условий Расширенного фильтра:
а– условия фильтра, соединенные логическим действием И,
б– условия фильтра, соединенные логическим действием ИЛИ
Теперь выполним команду Дополнительно меню Данные (рис. 19).
Рис. 19. Условия отбора расширенного фильтра
В данном случае два условия соединены логическим действиемИ.
Для объединения с |
помощьюИЛИ необходимо между именем поля и |
|||||
условием пропустить строчку (рис. 18, б). |
|
|
|
|||
При |
копировании |
отфильтрованных |
данных |
в |
другое место |
|
необходимо, чтобы |
копируемый диапазон |
начинался |
со |
строки, в |
||
которой указываются имена полей таблицы. |
|
|
|
Сводная таблица
Сводные таблицы – одно из наиболее мощных средств по работе с таблицами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в базе.
Создадим из нашей базы сводную таблицу для расчета прибыли по каждому товару. Выполним команду Сводная таблица из меню Вставка. В
43
диалоговом окне необходимо указать диапазон данных таблицы и место расположения сводной таблицы (рис. 20).
Рис. 20. Окно команды Сводная таблица
На новом листе в области задач сводной таблицы создадим макет по подсчету прибыли.
Поля базы данных, на основании которой строится сводная таблица, представлены в области создания сводной таблицы в виде списка полей (рис. 21). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.
В окне имеются следующие области:
Названия столбцов – для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;
Названия строк – для использования данных поля, расположенного в этой области, в качестве заголовка строки;
Значения – для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы.
Рис. 21. Макет создания сводной таблицы
44
Задания
Вариант 1
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Фамилия, |
Табельный |
|
|
|
Трудовой |
Трудовой |
|
Имя, |
Отдел |
Должность |
Оклад |
договор |
договор |
||
номер |
|||||||
Отчество |
|
|
|
(номер) |
(дата) |
||
|
|
|
|
||||
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
|
Отдел 1 |
|
Отдел 2 |
|
|
… |
|||
|
|
Трудовой |
Трудовой |
|
Трудовой |
|
Трудовой |
|
|
|
|
|
|
договор |
договор |
… |
договор |
|
договор |
… |
… |
… |
… |
|
|
(номер)1 |
(номер)2 |
|
(номер)1 |
|
(номер)2 |
|
|
|
|
Должность1 |
Ф.И.О.1 |
Оклад |
|
|
|
|
|
|
|
|
|
Ф.И.О.2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Должность2 |
Ф.И.О.1 |
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
|
Вариант 2
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
|
Табельный |
|
|
Фамилия, |
|
|
Профессия, |
|
|
|
|
|
Условия |
|
|
Дата |
|
|
Номер |
|
|
|
|
Имя, |
|
|
|
|
Оклад |
|
|
|
|
принятия |
|
|
|
||||
|
номер |
|
|
|
|
должность |
|
|
|
|
труда |
|
|
|
|
телефона |
|
|||
|
|
|
Отчество |
|
|
|
|
|
|
|
|
|
на работу |
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Профессия 1 |
Профессия 2 |
|
|
… |
||||
|
|
Дата |
Дата |
|
Дата |
Дата |
|
|
|
|
|
|
принятия на |
принятия на |
… |
принятия на |
принятия на |
… |
… |
… |
… |
|
|
работу 1 |
работу 2 |
|
работу 1 |
работу 2 |
|
|
|
|
Условия |
Ф.И.О. 1 |
Оклад |
|
|
|
|
|
|
|
|
труда 1 |
Ф.И.О. 2 |
|
|
|
|
|
|
|
|
|
Условия |
Ф.И.О. 1 |
|
|
|
|
|
|
|
|
|
труда 2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
Вариант 3
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Табельный |
Фамилия, |
Организация |
Оклад |
Отдел |
Категория |
Должность |
номер |
Имя, Отчество |
|
|
|
персонала |
|
|
|
|
|
|
|
|
45
2. На основании введенных данных получить сводную таблицу.
|
|
Категория персонала 1 |
Категория персонала 2 |
|
|
… |
|||||
|
|
Отдел 1 |
|
Отдел 2 |
… |
Отдел 1 |
Отдел 2 |
… |
… |
… |
… |
|
|
Оклад |
|
|
|
|
|
|
|
|
|
Организация 1 |
Ф.И.О. 1 |
|
|
|
|
|
|
|
|
||
|
Ф.И.О. 2 |
|
|
|
|
|
|
|
|
|
|
Организация 2 |
Ф.И.О. 1 |
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
|
Вариант 4
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Табельный |
Фамилия, |
Профессия, |
Условия |
Размер |
Количество |
Дата |
тарифной |
дней |
предоставления |
||||
номер |
Имя, Отчество |
должность |
труда |
ставки |
отпуска |
отпуска |
|
|
|
|
|||
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Профессия 1 |
|
Профессия 2 |
|
|
… |
|||
|
|
Табельный |
Табельный |
… |
Табельный |
Табельный |
… |
… |
… |
… |
|
|
номер 1 |
номер 2 |
номер 1 |
номер 2 |
|||||
|
|
|
|
|
|
|
||||
Условия |
Ф.И.О.1 |
Количество |
дней отпуска |
|
|
|
|
|
|
|
труда 1 |
Ф.И.О.2 |
|
|
|
|
|
|
|
|
|
Условия |
Ф.И.О.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
труда 2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
Вариант 5
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Табельный |
Фамилия, |
|
Профессия, |
Продолжительность |
Дата |
Дата конца |
|
Имя, |
Отдел |
начала |
|||||
номер |
должность |
отпуска |
отпуска |
||||
Отчество |
|
отпуска |
|||||
|
|
|
|
|
|||
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
|
|
|
Продолжительность отпуска 1 |
Продолжительность отпуска 2 |
|
… |
||||||
|
|
|
|
|
|
Дата |
Дата |
|
Дата |
Дата |
|
|
|
|
|
|
|
|
|
|
начала |
начала |
… |
начала |
начала |
… |
… |
… |
… |
|
|
|
|
|
|
отпуска 1 |
отпуска 2 |
|
отпуска1 |
отпуска 2 |
|
|
|
|
|
|
|
Ф.И.О. 1 |
|
|
Продолжительность |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
Отдел1 |
|
|
|
отпуска |
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
Ф.И.О. 2 |
|
|
|
|
|
|
|
|
|
|
|
Отдел2 |
|
Ф.И.О. 1 |
|
|
|
|
|
|
|
|
|
|
||
|
… |
|
… |
|
|
|
|
|
|
|
|
|
|
46
Вариант 6
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Табельный |
Фамилия, |
Семейное |
Кол-во |
Военно- |
Домашний |
|
номер |
Имя, |
положение |
детей |
обязанность |
адрес |
Телефон |
Отчество |
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Военнообязанный 1 |
Военнообязанный 2 |
|
|
… |
||||
|
|
Телефон1 |
Телефон2 |
… |
Телефон1 |
Телефон2 |
… |
… |
… |
… |
Семейное |
Ф.И.О.1 |
Кол-во |
детей |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
положение1 |
Ф.И.О.2 |
|
|
|
|
|
|
|
|
|
Семейное |
Ф.И.О.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
положение2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
Вариант 7
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
|
Табельный |
|
|
Фамилия, |
|
|
Профессия, |
|
|
|
|
|
Условия |
|
|
Дата |
|
|
Номер |
|
|
|
|
Имя, |
|
|
|
|
Оклад |
|
|
|
|
принятия |
|
|
|
||||
|
номер |
|
|
|
|
должность |
|
|
|
|
труда |
|
|
|
|
телефона |
|
|||
|
|
|
Отчество |
|
|
|
|
|
|
|
|
|
на работу |
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Условия труда 1 |
|
Условия труда 2 |
|
… |
|||||
|
|
Дата |
|
Дата |
|
Дата |
Дата |
|
|
|
|
|
|
принятия на |
принятия на |
… |
принятия на |
принятия на |
… |
… |
… |
… |
|
|
|
работу 1 |
|
работу 2 |
|
работу 1 |
работу 2 |
|
|
|
|
Профессия1 |
Ф.И.О.1 |
|
Оклад |
|
|
|
|
|
|
|
|
Ф.И.О.2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Профессия2 |
Ф.И.О.1 |
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
|
Вариант 8
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
|
Название |
|
|
Автор |
|
|
Год |
|
|
Место |
|
|
Количество |
|
|
Жанр |
|
|
Серийный |
|
|
книги |
|
|
|
|
издания |
|
|
издания |
|
|
экземпляров |
|
|
книги |
|
|
номер |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
47
2. На основании введенных данных получить сводную таблицу.
|
|
Год издания1 |
|
Год издания2 |
|
|
… |
||||
|
|
Жанр |
|
Жанр |
… |
Жанр |
Жанр |
… |
… |
… |
… |
|
|
книги 1 |
|
книги 2 |
книги 1 |
книги 2 |
|||||
|
|
|
|
|
|
|
|
||||
Автор1 |
Название книги1 |
Кол-во |
|
экземпляров |
|
|
|
|
|
|
|
Название книги2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Автор2 |
Название книги1 |
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
|
Вариант 9
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
Табельный |
Фамилия, |
|
Профессия, |
Продолжитель- |
Дата |
Дата конца |
|
Имя, |
Отдел |
начала |
|||||
номер |
должность |
ность отпуска |
отпуска |
||||
Отчество |
|
отпуска |
|||||
|
|
|
|
|
|||
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Профессия 1 |
Профессия 2 |
|
|
… |
||||
|
|
Дата начала |
Дата начала |
… |
Дата начала |
Дата начала |
… |
… |
… |
… |
|
|
отпуска 1 |
отпуска 2 |
|
отпуска 1 |
отпуска 2 |
|
|
|
|
Отдел 1 |
Ф.И.О. 1 |
Продолжительность |
|
|
|
|
|
|
|
|
|
отпуска |
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
||
|
Ф.И.О. 2 |
|
|
|
|
|
|
|
|
|
Отдел 2 |
Ф.И.О. 1 |
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
Вариант 10
1. В среде Microsoft Excel создать представленный список данных, содержащий не менее15 записей, и произвести поиск данных с помощью средств автофильтр и расширенный фильтр.
|
|
|
Фирма |
Номер |
|
|
|
Наименование |
Страна |
Год |
грузовой |
Цена, |
Количество, |
||
изгото- |
|||||||
товара |
изготовитель |
изготовления |
таможенной |
руб. |
шт. |
||
|
|
|
витель |
декларации |
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
2. На основании введенных данных получить сводную таблицу.
|
|
Фирма изготовитель 1 |
|
Фирма изготовитель 2 |
|
|
… |
||||
|
|
Год |
|
Год |
… |
Год |
Год |
… |
… |
… |
… |
|
|
изготовления |
изготовления |
|
изготовления |
изготовления |
|
|
|
|
|
|
|
1 |
|
2 |
|
1 |
2 |
|
|
|
|
Страна |
Наименование |
Количество, |
|
|
|
|
|
|
|
||
изготовитель 1 |
товара. 1 |
|
шт. |
|
|
|
|
|
|
|
|
|
Наименование |
|
|
|
|
|
|
|
|
|
|
|
товара 2 |
|
|
|
|
|
|
|
|
|
|
Страна |
Наименование |
|
|
|
|
|
|
|
|
|
|
изготовитель 2 |
товара 1 |
|
|
|
|
|
|
|
|
|
|
… |
… |
|
|
|
|
|
|
|
|
|
|
48
Лабораторная работа № 4. Применение стандартных функций для решения инвестиционных задач
Цель работы: изучение технологии построения компьютерных моделей с использованием финансовых функций и приобретение практических навыков построения вычислительных схем.
Содержание
Изучаются вопросы:
1.Понятие основных стандартных функций финансового раздела.
2. Технология |
построения |
компьютерных |
моделей |
с |
||||||
использованием стандартных функций. |
|
|
|
|
|
|||||
Выполняется вариант задания. |
|
|
|
|
|
|||||
|
|
|
|
Указания |
|
|
|
|
|
|
Среди |
функций, |
имеющихся |
в Excel, раздел, |
посвященный |
|
|||||
финансовым операциям, занимает значительное место. При помощи |
|
|||||||||
представленных |
в |
нем |
функций |
можно |
выполнять вычисления, |
|
||||
связанные с ценными бумагами, амортизацией, а также связанные с |
|
|||||||||
оценкой эффективности принимаемого решения о расчете будущей |
|
|||||||||
стоимости вклада, о величине периодических выплат, прогнозировании |
|
|||||||||
значений процентных ставок и т.д. |
|
|
|
|
|
|
||||
Задача |
1. |
Рассчитать, |
какая |
сумма |
окажется |
на |
счете, если |
|
||
27 тыс. руб. положены |
на 10 |
лет по 18,5% |
годовых. |
Проценты |
|
|||||
начисляются каждые полгода. |
|
|
|
|
|
|
Решение:
Функция БС рассчитывает будущую стоимость периодических постоянных платежей и будущее значение единой суммы вклада или займа на основе постоянной процентной ставки.
Будущую стоимость единовременной суммы вклада, по которой начисляются сложные проценты определенное количество периодов, рассчитывают по формуле
fv = pv × (1 + r)n,
где fv – будущая стоимость вклада или займа; pv – текущая стоимость вклада (займа); r – процентная ставка по вкладу(займу); n – общее число периодов начисления процентов.
Синтаксис БС(Ставка; Кпер; Плт; [Пс]; [Тип]),
где Ставка – процентная ставка за период; Кпер – общее количество периодов платежей по аннуитету; Плт – выплата, производимая в каждый период; Пс – приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей; Тип – число 0 или 1, обозначающее срок выплаты.
49
В зависимости от базы начисления значения аргументов должны быть пересчитаны (табл. 5).
Модель решения задачи приведена на рис. 22.
|
|
|
Таблица 5 |
|
Схема начисления процентов |
||
Метод начисления |
|
Общее число периодов |
Ставка процента на период |
процентов |
|
начисления процентов |
начисления, % |
Годовой |
|
n |
r |
Полугодовой |
|
n × 2 |
r/2 |
Квартальный |
|
n × 4 |
r/4 |
Ежемесячный |
|
n × 12 |
r/12 |
Ежедневный |
|
n × 365 |
r/365 |
Рис. 22. Схема решения задачи
Задача 2. Фирме потребуется 500 000 руб. через 7 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит отдельным вкладом. Определить необходимую сумму вклада, если ставка процента по нему составляет 12% в год.
Решение:
Эта задача базируется на понятии текущей стоимости будущих доходов и расходов, т.е. на начальный момент времени полученная в будущем сумма денег имеет меньшую стоимость, чем ее эквивалент, полученный в начальный момент времени.
В задаче известно будущее значение вклада и требуется определить текущее значение вложений, т.е. сумму, которую необходимо положить на счет сегодня, чтобы в концеn-го периода она достигла заданного значения. Это значение можно получить из формулы
fv
pv = (1 + r)n ,
где pv – текущая стоимость фиксированных периодических платежей; fv – будущая стоимость вклада; r – постоянная процентная ставка; n – общее число периодических выплат.
50
Для решения задачи воспользуемся функциейПС, которая предназначена для расчета текущей стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей.
Синтаксис ПС(Ставка; Клер; Плт; [Бс]; [Тип]),
где Ставка – процентная ставка за период; Кпер – общее число периодов платежей для ежегодного платежа; Плт – выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа; Бс – значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа; Тип – число 0 или 1, обозначающее, когда должна производиться выплата.
Модель решения задачи приведена на рис. 23.
Рис. 23. Модель решения задачи
Функция ПС также позволяет решить задачу, в которой требуется
найти |
текущую |
стоимость |
будущих |
периодических |
постоянных |
платежей, которые |
производятся |
в начале или в конце каждого |
расчетного периода. Согласно концепции временной стоимости, чем дальше от настоящего момента будет поступление или расходование
средств, тем меньшую текущую ценность оно представляет. Таким |
|
||||||||
образом, при |
равных |
|
|
условиях |
текущая |
стоимость |
вкладов |
||
пренумерандо больше, чем текущая стоимость вкладов постнумерандо. |
|
||||||||
Расчет |
текущей |
стоимости |
серии |
будущих |
постоянных |
||||
периодических |
платежей, |
пренумерандо |
каждого |
периода и |
|
||||
дисконтированных нормой дохода r, выполняется по формуле: |
|
||||||||
|
|
|
1 - |
1 |
|
|
|
|
|
|
pv = pmt |
(1 + r)n |
(1 + r ), |
|
|
||||
|
|
|
|
|
|||||
|
|
|
|
|
|
r
где pmt – фиксированная периодическая сумма платежа.
В общем виде для решения таких задач функция ПС примет вид:
ПС(Ставка; Кпер; Плт; ; 1).
Для расчета текущей стоимости постоянных периодических выплат постнумерандо формула имеет вид:
|
51 |
|
1 |
|
|
|
1 - |
|
|
||
|
|
+ r)n |
|||
|
(1 |
||||
pv = pmt |
|
|
|
|
. |
|
|
|
|
r
И соответствующая этому расчету функция Excel
ПС(Ставка; Кпер; Плт; ;).
По умолчанию аргумент Тип = 0, поэтому его можно не указывать.
Задача 3. Рассчитать, через сколько лет вклад размером 1 млн руб. достигнет величины 1 млрд руб., если годовая ставка процента по вкладу 16,79% и начисление процентов производится ежеквартально.
Решение:
Общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения, а также число периодов, через которое начальная сумма вклада достигнет заданного значения можно найти с помощью встроенной функции КПЕР.
Синтаксис КПЕР(Ставка; Плт; Пс; [Бс]; [Тип]),
где Ставка – процентная ставка за период; Плт – выплата, производимая в каждый период; Пс – приведенная (текущая) стоимость; Бс – значение будущей стоимости; Тип – число 0 или 1, обозначающее, когда должна производиться выплата.
Модель решения задачи приведена на рис. 24.
Рис. 24. Схема решения задачи
Задача 4. Компании потребуется 1 млн руб. через 2 года. Компания готова вложить 50 тыс. руб. сразу и по 25 тыс. руб. каждый последующий месяц. Каким должен быть процент на инвестированные средства, чтобы получить необходимую сумму в конце второго года?
Решение:
Функция СТАВКА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки