Лабораторная Работа № 3. Финансовый анализ в MS Excel. Анализ инвестиций

ЛАБОРАТОРНАЯ РАБОТА № 3. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL. АНАЛИЗ ИНВЕСТИЦИЙ

 

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

− функции для анализа инвестиций;

− функции расчета амортизации;

− функции работы с ценными бумагами.

В данной работе рассмотрено использование функций анализа инвестиций.

Многие функции для анализа инвестиций имеют одинаковые аргументы. Эти аргументы представлены в табл. 2.

 

Таблица 2

Аргументы для анализа инвестиций

 

Аргумент	Название	Описание
Пс	Приведенная стоимость(начальная)	Общая сумма, которая на настоящий момент равноценна ряду будущих выплат.
Бс	Будущая стоимость инвестиции	Сумма, которая будет достигнута после последней выплаты.
Плт	Выплата	Сумма, выплачиваемая в каждый период и не меняющаяся за все время выплаты инвестиции.
Кпер	Общее число периодов выплаты инвестиции-	Рассчитывается как произведение количества лет инвестиции на количество выплат в год. Например, кредит выдан на 5 лет с выплатой 2 раза в год. Число периодов равно 5 · 2 = 10.
Ставка	Процентная ставка за период	Рассчитывается как процентная ставка за год, деленная на количество выплат в год. Например кредит выдан под 10 % годовых с ежеквартальны ми выплатами. Процентная ставка за период равна 10 / 4=2,5 %.
Тип	Вид аннуитета (тип)	Принимает логическое значение 0 или 1. Значение0 обозначает, что выплата производится в конце периода, 1 – в начале периода.

 

Финансовые функции связаны с интервалами выплат. Аннуитет – это ряд постоянных денежных выплат, делаемых в течение длительного периода. Например, заем под автомобиль или заклад являются аннуитетами.

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

Например, депозит в банк на сумму 1000 тг.представляется аргументом –1000 – для вкладчика – и аргументом 1000 – для банка.

 

3.1. Расчет текущей стоимости инвестиции. Функция ПС (ПЗ)

 

Функция ПС (приведенная стоимость) является одним из наиболее рас-пространенных способов оценки привлекательности долговременных вложе-ний. Текущее значение вложения (чистый текущий объем вклада) определяется дисконтированием (приведением к стоимости на настоящий момент) поступле-ний по этому вложению. Если текущая стоимость поступлений оказывается больше вклада, вложение считается удачным.

 

Синтаксис функции: ПС(Ставка;Кпер;Плт;Бс;Тип).

 

Функция ПС вычисляет текущую стоимость ряда равных по величине пе-риодических выплат или единовременной выплаты.

Для вычисления текущей стоимости ряда выплат используется аргумент

Плт (выплата).

Для вычисления текущей стоимости единовременной выплаты используется аргумент Бс (будущая стоимость).

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

 

Задание 1

Определение текущей стоимости ряда периодических выплат. Существу-ет два варианта вложения денежных средств с целью заработать 5 000 тг.в те-чение 2,5 лет:

1. Совершить сделку, которая каждые полгода возвращает 1 000 тг.в тече-ние следующих 2,5 лет. Для этого нужно вложить 4 000р.

2. Вложить деньги в банк на краткосрочный счет под 8,0 % с выплатами ка-ждые полгода в размере 1 000 тг.

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

Пояснения к задаче

 

Решение этой задачи требует оценить текущую стоимость ряда поступле-ний по 1 000 тг.Проценты, которые предлагает банк, послужат в качестве учетной ставки вложения. Учетная ставка является своего рода «барьером», ко-торый должен быть превзойден, прежде чем инвестиция станет привлекатель-ной, поэтому ее часто называют барьерной ставкой.

Для определения текущей стоимости вложения используется функция ПС, в которой будет задействован аргумент Плт и не задействован аргумент Бс.

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПС.

3. Оформите таблицу согласно (рис. 17).

 

 

Рис. 17. Исходные данные для расчета текущей стоимости ряда периодических выплат

 

4. Вычислите количество периодов выплат и ставку за период согласно рис. 18).

5. Запустите Мастер функций, ознакомьтесь с функцией ПС (ПЗ).

6. Вычислите текущую стоимость инвестиции. Результаты сравните с рис. 18.

 

Рис. 18. Результаты вычисления текущей стоимости ряда периодических выплат

 

7. Сохраните файл и продемонстрируйте результат преподавателю.

Анализ результатов решения

Функция возвращает значение –4 451,82. Это означает, что нужно вложить в банк 4 451,82 тг.под 8,0 % годовых сегодня, чтобы получить 5 000 тг.в течение следующих 5 лет. Поскольку в предлагаемой сделке вклад равен 4 000 тг., можно считать, что совершить сделку выгоднее, чем вложить деньги в банк.

 

Задание 2 Определение текущей стоимости диновременной выплаты

Существует два варианта вложения денежных средств с целью получения

5 000 тг.по истечению 2,5 лет:

1. Совершить сделку, вложив 4 000 тг.

2. Вложить деньги в банк на краткосрочный счет под 8,0 % с начислением процентов каждые полгода.

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

 

Пояснения к задаче

 

Решение этой задачи требует оценить текущую стоимость единовремен-ной выплаты. Для определения текущей стоимости вложения используется

функция ПС, в которой будет задействован аргумент Бс и не задействован ар-гумент Плт.

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПС.

3. Оформите таблицу согласно (рис. 19).

 

 

Рис. 19. Исходные данные для расчета текущей стоимости единовременной выплаты

4. Вычислите количество периодов выплат и ставку за период (рис. 20).

5. Вычислите текущую стоимость инвестиции. Результаты сравните с рис. 20.

 

Рис. 20. Результаты вычисления текущей стоимости единовременной выплаты

 

 

6. Сохраните файл и продемонстрируйте результат преподавателю.

Анализ результатов решения.

 

Функция возвращает значение –4 109,64. Это означает, что нужно вло-жить в банк 4 109,64р. под 8,0% годовых сегодня, чтобы получить 5 000 тг.через 5 лет. Поскольку значение 4 109,64 тг.больше, чем текущая стоимость сделки (4 000 тг.), вложить деньги в сделку более выгодно, чем в банк.

 

Задание 3 Определение текущей стоимости ряда платежей и единовременной выплаты

 

Существует два варианта вложения денежных средств сроком на 2,5 года.

1. Совершить сделку, которая ежегодно возвращает 500 тг.в течение сле-дующих 2,5 лет. Кроме того, в конце периода будет выплачено 3 000 тг.

2. Вложить деньги в банк на краткосрочный счет под 8,0 % с начислением процентов каждые полгода.

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

 

Пояснения к задаче

 

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

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПС.

3. Оформите таблицу согласно (рис. 21).

 

 

Рис. 21. Исходные данные для расчета текущей стоимости платежей и единовременной выплаты

 

4. Вычислите количество периодов выплат и ставку за период (рис. 22).

5. Вычислите текущую стоимость инвестиции. Результаты сравните с рис. 22.

 

 

Рис. 22. Результаты вычисления текущей стоимости платежей и единовременной выплаты

 

6. Сохраните файл и продемонстрируйте результат преподавателю.

 

Анализ результатов решения

 

Функция возвращает значение –4 691,69. Это означает, что нужно вло-жить в банк 4 691,69 тг.под 8,0 % годовых. Поскольку значение 4 691,69 тг.больше, чем текущая стоимость сделки (4 000 р.), вложить деньги в сделку бо-лее выгодно, чем в банк.

Контрольное задание

 

Существует несколько вариантов вложения денежных средств с целью получения 70 000 тг.в течение 7 лет, величина годовой ставки – 12 %. Опре-делите наиболее выгодный вариант вложения денежных средств.

 

1. Сделка, которая каждые полгода возвращает 5 000 тг.

2. Сделка, в результате которой возвращается единовременная выплата в размере 70 000 тг.с начислением процентов каждые полгода.

3. Сделка, которая каждые полгода возвращает 2 500 тг.в течение сле-дующих 7 лет. Кроме того, в конце периода будет выплачено 40 000 тг.

Пояснения к задаче

 

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

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПС.

3. Оформите таблицы согласно (рис. 23).

 

 

Рис. 23. Исходные данные для контрольного задания

 

4. Для каждого из вариантов вычислите текущую стоимость инвестиции.

5. Проанализируйте полученные значения, определите наиболее выгодный вариант вложения.

6. Сохраните файл и продемонстрируйте результат преподавателю.

 

3.2. Расчет будущей стоимости инвестиции. Функция БС (БЗ)

 

Функция БС (будущая стоимость) вычисляет для некоторого будущего момента времени величину вложения, которая образуется в результате едино-временной выплаты или/и ряда постоянных периодических платежей.

 

Синтаксис функции: БС(Ставка;Кпер;Плт;Пс;Тип).

 

Для определения будущего значения ряда выплат используется аргумент

 

Плт (выплата),аргумент Пс (приведенная стоимость) служит для определения будущего значения единовременной выплаты.

 

Задание 4 Определение наиболее выгодного варианта вложения денежных средств

 

Вкладчик планирует открыть счет сроком на 5 лет с ежемесячными пла-тежами и рассчитывает на среднюю скорость оборота 11 % в год. Выплаты производятся в начале периода. Существует несколько вариантов вложения:

1. Вносить на счет 2 000 тг.каждый месяц в течение 5 лет.

2. Вложить 100 000 тг.с целью получения по истечении 5 лет единовре-менной выплаты.

3. Внести на счет 30 000 р. и далее вносить на счет 1 500 тг.каждый ме-сяц в течение 5 лет.

Определите наиболее выгодный вариант вложения денежных средств.

 

Пояснения к задаче

 

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

Для определения текущей стоимости вложения используется функция БС. Аргументу Тип задано значение 1 (вложения производятся в начале каждого периода).

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в БС.

3. Оформите таблицы согласно (рис. 24).

 

 

Рис. 24. Исходные данные для расчета будущих стоимостей инвестиций

4. Для каждого из вариантов вычислите будущую стоимость инвестиции.

5. Проанализируйте полученные значения, определите наиболее выгодный вариант вложения.

6. Сохраните файл и продемонстрируйте результат преподавателю.

 

3.3. Расчет периодического платежа. Функция ПЛТ (ППЛАТ)

 

Функция ПЛТ вычисляет размер периодической выплаты, необходимой для погашения ссуды за указанное число периодов.

Синтаксис функции: ПЛТ(Ставка;Кпер;Пс;Бс;Тип).

 

Задание 5 Определение величины периодического платежа

 

Взята ссуда размером 50 000 р. на срок 2 года под 6 % годовых с еже-квартальной выплатой платежей. Необходимо определить величину периоди-ческого платежа.

 

Ход решения задачи

 

1. Запустите Excel.

2. Переименуйте первый лист в ПЛТ.

3. Оформите таблицу согласно (рис. 25).

 

 

Рис. 25. Исходные данные для вычисления величины платежа

 

4. Вычислите количество периодов выплат и ставку за период (рис. 26).

5. Вычислите величину платежа. Результаты сравните с рис. 26.

 

 

Рис. 26. Результаты вычисления величины платежа

 

6. Сохраните файл и продемонстрируйте результат преподавателю.

7. Составление графика погашения займа. Функции ПРПЛТ (ПЛПРОЦ), ОСПЛТ (ОСНПЛАТ)

 

Функция ПРПЛТ вычисляет платежи по процентам за заданный период при постоянных периодических выплатах и постоянной процентной ставке.

Синтаксис функции: ПРПЛТ(Ставка;Период;Кпер;Пс;Бс;Тип).

Функция ОСПЛТ вычисляет основные платежи за заданный период при постоянных периодических выплатах и постоянной процентной ставке.

Синтаксис функции: ОСПЛТ(Ставка;Период;Кпер;Пс;Бс;Тип).

 

Задание 6

Взята ссуда размером 50 000 тг.на срок 2 года под 6 % годовых с еже-квартальной выплатой платежей. Необходимо составить график погашения займа, включающий платежи по процентам и основные платежи.

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПЛТ.

3. Оформите таблицу согласно (рис. 27).

 

 

Рис. 27. Исходная таблица для составления графика погашения займа

 

4. Используя функцию ПРПЛТ, вычислите величину платежа по процентам для 1-го квартала, необходимые данные возьмите из задания 5 (рис. 26).

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

 

 

Рис. 28. Расчет платежей по процентам

 

 

6. С помощью функции ОСПЛТ вычислите величину основного платежа для 1-го квартала, используя необходимые значения из условия задачи задания 5 (рис. 26).

7. Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений основных платежей остальных кварталов (рис. 29).

 

 

Рис. 29. Расчет платежей по основной сумме

 

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

9. Вычислите итоговые значения всех видов платежей.

 

10. Результаты сравните с рис. 30.

 

Рис. 30. Расчет платежей за период

 

11. Сохраните файл и продемонстрируйте результат преподавателю.

 

 

Задание 7

 

Взята ссуда размером 50 000 тг.на срок 2 года под 6 % годовых с еже-квартальной выплатой платежей. Необходимо составить график погашения займа, включающий платежи по процентам и основные платежи.

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПЛТ.

3. Оформите таблицу согласно (рис. 31).

4.

 

 

Рис. 31. Составление графика платежей

 

 

4. Вычислите величину выплаты по процентам с помощью функции ОБ-ЩПЛАТ, используя необходимые значения из условия задачи 6 и указав в качестве начального и конечного периодов соответственно 1 и 8 квартал.

5. Аналогичным образом вычислите величину выплаты по основной сумме помощью функции ОБЩДОХОД.

6. Определите общую сумму выплат, суммировав величины выплат по про-центам и по основной сумме.

7. Результаты сравните с рис. 32.

 

 

Рис. 32. Результаты вычислений выплат

 

8. Определите величину накопления по процентам для 1-го квартала, ис-пользуя необходимые значения из условия задачи 6.

 

 

9. Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений накоплений по процентам остальных кварталов. 10. Результаты сравните с рис. 33.

 

 

Рис. 33. Результаты вычислений выплат и накоплений по процентам

 

11. Вычислите величину накопления по основной сумме для 1-го квартала, используя необходимые значения из условия задачи 6.

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

13. Результаты сравните с рис. 34.

 

 

Рис. 34. Результаты вычислений выплат, накоплений по процентам и по основной сумме

14. Определите остаток долга, как разницу между суммой займа и накопле-нием по основной сумме.

15. Результаты сравните с рис. 35.

 

 

Рис. 35. Результаты вычислений выплат, накоплений по процентам, по основной сумме и остаток долга

 

16. Сохраните файл и продемонстрируйте результат преподавателю.

 

Контрольное задание

 

Рассчитайте размер ежегодной выплаты для погашения ссуды размером 220 000 тг., взятой на 7 лет под 9 % годовых.

Определите основные платежи, платежи по процентам, остаток долга по-сле очередной выплаты, общие суммы платежей по процентам, по основной сумме, платежей за период.

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в ПЛТ.

3. Оформите таблицу согласно (рис. 36).

 

 

Рис. 36. Размер ежегодной выплаты для погашения ссуды

 

4. Вычислите необходимые значения в соответствии с условием задачи.

5. Сохраните файл и продемонстрируйте результат преподавателю.

3.6. Расчет общего числа периодов выплат инвестиции. Функция КПЕР

Функция КПЕР вычисляет, сколько периодов необходимо для погашения ссуды при заданной величине периодических выплат.

 

Синтаксис функции: КПЕР(Ставка;Плт;Пс;Бс;Тип).

 

Задание 8 Расчет срока ссуды

 

В какой срок будет погашен долг в размере 100 000 тг., взятый под 8 % годовых, если выплачивать ежемесячно по 1 000 тг.? Определите сумму ко-миссионных и общую сумму выплат по долгу.

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в КПЕР.

3. Оформите таблицу согласно (рис. 37).

 

Рис. 37. Исходные данные для расчета срока инвестиции

 

4. Вычислите количество периодов выплат с помощью функции КПЕР.

5. Определите срок инвестиции, разделив количество выплат на их перио-дичность.

6. Результаты сравните с рис. 38.

 

 

Рис. 38. Результаты вычисления срока инвестиции

7. Сохраните файл и продемонстрируйте результат преподавателю.

Контрольное задание

 

Вы берете в долг 320 000 тг.Под 4,5 % годовых и собираетесь выплачи-вать по 36 000 тг.в год. Сколько лет займут выплаты? Определите размер ко-миссионных и сумму выплат по займу.

 

Ход решения задачи

1. Запустите Excel.

2. Переименуйте первый лист в КПЕР.

3. Оформите таблицу согласно (рис. 39).

 

 

Рис. 39. Исходные данные для определения размера комиссионных выплат по займу

 

4. Вычислите необходимые значения в соответствии с условием задачи.

5. Сохраните файл и продемонстрируйте результат преподавателю.

 

 

3.7. Расчет нормы прибыли инвестиции. Функция СТАВКА (НОРМА)

 

Функция СТАВКА позволяет определить скорость оборота (норму при-были) вложения по ряду постоянных периодических выплат или/и по едино-временной выплате. Другими словами, функция вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов.

 

Синтаксис функции: СТАВКА (Кпер; Плт; Пс; Бс; Тип; Предположение).

Аргумент Плт (выплата) используется при определении скорости оборо-та для ряда постоянных периодических выплат, аргумент Бс (будущая стои-мость) –при определении скорости оборота для единовременной выплаты.

Аргумент Предположение (по умолчанию равен 10%) дает начальное приближение нормы, т.к. Excel использует итерационный процесс для вычис-ления.

 

Задание 9 Определение годовой скорости оборота

 

Организация сделала заем в размере $ 8 000 на 4 года. Платежи должны производиться каждый месяц в размере $ 200. Определите, под какой процент взят заем. Учтите, что будущее значение инвестиции для займа составляет 0.

 

Ход решения задачи

 

1. Запустите Excel.

2. Переименуйте первый лист в СТАВКА.

3. Оформите таблицу согласно (рис. 40).

 

Рис. 40. Исходные данные для расчета годовой ставки

 

4. Вычислите ставку за период.

5. Определите годовую ставку, умножив ставку за период на периодичность платежей.

6. Результаты сравните с рис. 41.

 

Рис. 41. Результаты вычисления годовой ставки

 

7. Сохраните файл и продемонстрируйте результат преподавателю.

 

Контрольное задание

 

Рассматривается вложение сроком на 5 лет, которое гарантирует получе-ние 5 000 тг.Сумма вложения составляет 3 000 тг.Определите годовую ско-рость оборота этого вложения.

 

Ход решения задачи

 

1. Запустите Excel.

2. Переименуйте первый лист в СТАВКА.

3. Оформите таблицу согласно (рис. 42).

 

 

 

Рис. 42. Исходные данные для расчета годовой скорости оборота вложений

 

4. Вычислите годовую ставку.

5. Сохраните файл и продемонстрируйте результат преподавателю.

 

Задание 10

 

Рассматривается вложение сроком на 5 лет, которое гарантирует получе-ние 5 000 тг.Сумма вложения составляет 3 000 тг.Определите годовую ско-рость оборота этого вложения.

 

Ход решения задачи

 

1. Запустите Excel.

2. Переименуйте первый лист в СТАВКА.

3. Оформите таблицу согласно (рис. 43).

 

 

 

Рис. 43. Исходные данные для расчета годовой скорости оборота вложений

 

4. Вычислите модифицированную ставку.

5. Сохраните файл и продемонстрируйте результат преподавателю.

 

Вопросы для самоконтроля

 

1. На какие группы можно разделить финансовые функции?

2. Какие функции используются для анализа инвестиций?

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

 

4. Как вычисляется общее количество выплат?

5. Как определяется процент за период?

6. Какими числами представляются выплачиваемые и получаемые денеж-ные средства?

7. Для чего служит функция ПС?

8. Какие аргументы функции ПС используются для расчета стоимости ряда выплат?

9. Какие аргументы функции ПС используются для расчета стоимости еди-новременной выплаты?

10. Для чего служит функция БС?

11. Какие аргументы функции БС используются для расчета стоимости ряда выплат?

12. Какие аргументы функции БС используются для расчета стоимости еди-новременной выплаты?

13. Для чего служит функция ПЛТ?

14. С помощью каких функций можно вычислить платежи по процентам и по основной сумме?

15. Для чего служит функция ОБЩПЛАТ?

16. Для чего служит функция ОБЩДОХОД?

17. Как можно вычислить количество периодов для погашения ссуды?

18. Как определить срок займа, зная общее количество периодов выплат?

19. Как вычисляется скорость оборота вложения?

20. Как определить годовую ставку, зная норму прибыли за период?

21. Для чего служит функция МВСД?

EXCEL. ПОДВЕДЕНИЕ ИТОГОВ

 

Задание 1

1. Запустите Excel.

2. Переименуйте первый лист в ИТОГИ_1.

3. Создайте на листе базу данных сотрудников согласно табл. 1.

 

Таблица 1

База данных сотрудников

 

Фамилия	Имя	Отчество	Оклад, тг	Годовой фонд зар- платы, тг	Отдел	Дата приема на работу
Андреева	Анна	Семеновна	7166,00	85992,00	Бухгалтерия	04.11.2002
Ноткин	Иван	Семенович	9000,00	108000,00	Бухгалтерия	09.07.1998
Горбатов	Иван	Семенович	9916,50	118998,00	Склад	11.05.2001
Крылова	Ольга	Сергеевна	8083,00	96996,00	Склад	06.09.1999
Андреева	Анна	Олеговна	6250,00	75000,00	ОК	02.01.1999
Ерохин	Иван	Федорович	8541,00	102492,00	Бухгалтерия	07.08.2000
Петрова	Мария	Андреевна	10375,00	124500,00	Склад	11.04.1994
Крылова	Ирина	Максимовна	9458,50	113502,00	Цех №1	09.06.2096
Васин	Игорь	Петрович	7625,00	91500,00	Цех №2	05.10.2001
Самойлов	Семен	Петрович	6708,00	80496,00	Бухгалтерия	03.12.2000
Бершев	Никита	Иванович	8450,00	101400,00	Цех №1	03.01.2000
Быстрова	Татьяна	Олеговна	7120,50	85446,00	ОК	05.12.1999
Доценко	Иван	Сергеевич	9800,00	117600,00	Цех №2	15.07.2003
Фандеев	Петр	Иванович	8657,00	103884,00	Цех №2	09.06.2001
Конов	Алексей	Алексеевич	6852,50	82230,00	Цех №1	05.11.2001

 

4. Отсортируйте исходный список по полю Отдел.

5. Выполните команду Данные ⇒ Итоги.

6. В списке при каждом изменении укажите поле Отдел (рис. 1). Так как список был отсортирован по полю Отдел, то строки с одинаковым отде-лом располагаются непосредственно рядом друг с другом. Как только происходит изменение в поле Отдел, значит, информация о сотрудниках одного отдела закончилась, и далее следуют строки, касающиеся сотруд-ников другого отдела.

7. В списке Операция выберите Сумма – это операция, с помощью которой будут подводиться итоги (данные будут суммироваться).

8. В списке Добавить итоги по укажите поля Оклад и Годовой фонд зарпла-ты – по этим полям будут подводиться итоги.

9. Установите соответствующие флажки, как показано на рис. 1. Нажмите кнопку ОК.

 

Рис. 1. Диалоговое окно «Промежуточные итоги»

10. Результат подведения итогов приведен на рис. 2.

 

 

Рис. 2. Результат применения функции «Промежуточные итоги»

 

11. Для того чтобы просмотреть только промежуточные и общие итоги, щелкните на кнопке (2-й уровень списка), расположенной в верхнем левом углу списка. Результат представлен на рис. 3.

 

 

 

Рис. 3. Сворачивание «Промежуточных итогов» до 2-го уровня

 

12. Чтобы вернуть список в первоначальное положение, щелкните на кнопке

(3-й уровень списка).

13. Отобразите только общие итоги (рис. 4).

 

 

Рис. 4. Сворачивание «Промежуточных итогов» до 3-го уровня

 

14. Разверните весь список. Исследуйте назначение кнопок и .

15. Сохраните файл и продемонстрируйте результат преподавателю.

 

Функции баз данных

 

Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех Д-функций один и тот же синтаксис:

 

=Д-функция (база_данных; поле; критерий)

 

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

 

(суммирование, усреднение и т. п.). Если есть формула, с помощью мастера функций при заполнении второго аргумента достаточно указать ячейку рабоче-го листа, в котором хранится имя соответствующего поля.

 

Аргумент критерий задает диапазон критериев.

 

Функция БДСУММ

 

1. Переименуйте второй лист в Д-Функ.

2. Скопируйте на лист базу данных сотрудников с листа ИТОГИ_1.

3. Выделите всю таблицу (диапазон А1:G16) и присвойте ей имя База.

4. Определите сумму окладов, превышающих 8 000р. Для этого после таблицы введите следующий критерий (рис. 5).

 

 

Рис. 5. Сумма окладов

 

5. Активизируйте ячейку В20. Вызовите Мастер функций, выберите категорию функций Работа с базой данных и функцию БДСУММ.

6. Заполните поля Мастера функций (рис. 6).

 

 

Рис. 6. Диалоговое окно функции БДСУММ

 

7. Подсчитайте суммарный годовой фонд зарплаты по отделу Бухгалте-рия. Для этого определите критерий(рис. 7).

 

Рис. 7. Фонд ЗП по отделу «Бухгалтерия»

 

8. Заполните поля мастера функций в соответствии с рис. 8.

 

 

Рис. 8. Диалоговое окно функции БДСУММ

 

9. Сравните полученное значение с промежуточными итогами с листа ИТОГИ_1.

10. Аналогично вычислите суммарные значения годового фонда зарплаты по остальным отделам.

11. Сохраните файл и продемонстрируйте результат преподавателю.

 

Функция БДСЧЕТ

 

1. Определите количество сотрудников, принятых в период с 01.01.2000 по 31.12.2003. Для этого оформите данные, как показано на рис. 9.

 

 

 

 

Рис. 9. Определение количества сотрудников

 

2. Заполните поля мастера функций в соответствии с рис. 10.

 

 

Рис. 10. Диалоговое окно функции БДСУММ

 

3. Подсчитайте количество сотрудников, имеющих оклад в диапазоне от 7 000 тг.до 10 000 тг.

 

Функция ДСРЗНАЧ

1. Самостоятельно ознакомьтесь с функцией ДСРЗНАЧ.

2. Определите величины средних окладов по каждому из отделов.

3. Определите среднюю величину годового фонда зарплаты сотрудников, принятых на работу в 2000 г.

 

Функции ДМАКС и ДМИН

 

1. Самостоятельно ознакомьтесь с функциями ДМАКС и ДМИН.

2. Определите величины наибольших и наименьших окладов по каждому из отделов.

3. Определите наибольшую и наименьшую величину годового фонда зар-платы сотрудников, принятых на работу в 1999 г.

 

Задание 1

1. Создайте новую рабочую книгу Excel.

2. Переименуйте первый лист в Фильтр и введите данные, в соответствии с рис. 11, (можете использовать данные из предыдущей лабораторной ра-боты).

3. Установите табличный курсор на одну из ячеек списка.

4. Выполните команду Данные ⇒ Фильтр ⇒ Автофильтр. Excel проана-лизирует список и добавит в строку заголовков полей кнопки раскры-вающихся списков (кнопки автофильтра): (рис. 11).

 

 

Рис. 11.Список с фильтрами

 

5. Щелкните на кнопке автофильтра в ячейке Отдел. Список раскроется и покажет все значения, содержащиеся в этом столбце (рис. 12).

 

Рис. 12. Список с фильтрами

 

6. Выберите наименование какого-нибудь отдела, и Excel спрячет все стро-ки, кроме тех, которые включают отмеченное значение (рис. 13). Други-ми словами, критерием отбора служит выбранное вами значение.

 

Рис. 13. Список с фильтрами

 

7. Обратите внимание на то, что после фильтрации в строке состояния поя-вилась сообщение о том, сколько строк отобрано: . Кроме того, изменился цвет кнопки автофильтра , чтобы напомнить вам, что список отфильтрован по значениям, содержащимся в этом столбце.

8. Снова щелкните на кнопке автофильтра и выберите опцию (Все). Про-грамма отобразит весь список полностью.

9. Сохраните файл и продемонстрируйте результат преподавателю.

 

 

Задание 2

1. Щелкните на кнопке автофильтра в ячейке Годовой фонд заработной платы. Выберите опцию (Условие …). Открывшееся диалоговое окно Пользовательский автофильтр позволяет фильтровать списки с ис-пользованием нескольких критериев.

2. В поле Годовой фонд заработной платы из раскрывающегося списка выберите критерий больше, в поле справа введите или выберите из спи-ска значение критерия, например, 80 000 тг. Установите переключатель И. В нижнем поле выберите критерий меньше и установите для него зна-чение, например, 100 000 тг. (рис. 14).

 

 

Рис. 14. Окно пользовательского фильтра

 

3. Нажмите кнопку ОК. Таким образом, вы вывели