План лекции 5 n n Финансовые функции Списки

План лекции 5. n n Финансовые функции Списки (базы данных) 1

Финансовый анализ Ø Функции для анализа инвестиций Ø Функции для вычисления амортизации Ø Функции для анализа ценных бумаг 2

Кредитор Дебитор S Р 3

Аргумент n n Число периодов (кпер) Ставка Выплата (плт) тип Описание – – – Общее количество периодов платежей Процентная ставка Постоянные периодические выплаты 0 выплата в конце периода 1 – в начале периода 4

Аргумент n Приведенная стоимость (пс) Описание – Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей 0, если аргумент опущен. 5

Аргумент n Будущая – стоимость (бс) Описание Требуемое значение будущей стоимости или остатка средств последней выплаты. 0, если аргумент опущен. 6

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

= ПC (ставка; кпер; плт; бс; тип) Вклад 4000$ 5 лет по 1000$ Выгодно ли? 8

=ПС(4, 5%; 5; 1000) n Результат - 4389, 98 =ПС(4, 5%; 5; ; 5000) n Результат - 4012, 26 9

Функция БС n Вычисляет для некоторого будущего момента времени величину вложения, которая образуется в результате единовременной выплаты или ряда постоянных периодических выплат 10

= БС (ставка; кпер; плт; пс; тип) Взнос 2000$ в начале каждого года в течении 35 лет 9% годовых =БС(9%; 35; -2000; ; 1) 470 249, 45$ . 11

Взнос 2000$ в конце каждого года в течении 35 лет 9% годовых =БС(9%; 35; -2000) 431 421, 51$ 12

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

=ПЛТ(ставка; кпер; пс; бс; тип) Ссуда 100000$ на 25 лет 8% годовых =ПЛТ(8%/12; 300; 100000) Рассчитываем ежемесячные выплаты кредита 100000, взятого на 25 лет, ставка 8% 14

Выдан кредит 1 000 р. с 15. 01. 2013 по 15. 03. 2013 под 20% годовых. Рассчитать сумму погасительного платежа. 15

A B 1 Годовая ставка 20% 2 Дата выдачи кредита 15. 01. 13 3 Дата возврата 15. 03. 13 4 Сумма кредита 1 000 5 Срок в днях 59 =В 3 -В 2 6 Срок в годах 0, 162 =В 5/365 7 Ставка для периода Сумма возврата 3, 23% =В 1*В 6 -1032328, 77 =БС(В 7; 1; ; В 4) 8 16

СПИСКИ (БАЗЫ ДАННЫХ) 17

1. 2. 3. 4. 5. Создание и ведение списков Сортировка списков и диапазонов Использование промежуточных итогов для анализа списка Использование фильтров для анализа списков Сводные таблицы. 18

Создание и ведение списков 1. 2. 3. 4. 5. 6. Каждый столбец должен содержать информацию одного типа. Верхняя строка должна содержать заголовки, описывающие содержимое расположенного ниже столбца. Лучше для списка отвести отдельный лист. Не рекомендуется размещать данные слева и справа от списка. В списки не включаются пустые строки и столбцы. Столбцы должны содержать однородную информацию. 19

Пример списка Продавец Товар Дата продажи Кол-во Цена Стоимость Бусыгин кузов 13. 03. 2004 1 2890 Бусыгин кузов 13. 03. 2004 1 4900 Селиверстова журналы 14. 03. 2004 50 25 1250 Бусыгин бензин 24. 03. 2004 35 16, 5 577, 5 Бусыгин кузов 14. 10. 2004 1 7000 Селиверстова велосипед 15. 10. 2004 10 28000 Селиверстова журналы 15. 10. 2004 35 15 525 Авдеев колесо 10. 02. 2004 20 700 14000 Авдеев капот 13. 01. 2004 26 4200 109200 Лебедева кузов 29. 09. 2004 2 5900 11800 Лебедева капот 29. 09. 2004 2 3100 6200 Лягушкин покрышки 22. 10. 2004 36 600 21600 Нервозов кузов 23. 10. 2004 3 1900 5700 Нервозов шины 23. 10. 2004 12 560 6720 Нервозов покрышки 23. 10. 2004 10 5600 Обозов бензин 24. 10. 2004 200 15 3000 Обозов велосипед 24. 10. 2004 8 3600 28800 Гусельникова велосипед 25. 10. 2004 4 4280 17120 Гусельникова велосипед 22. 10. 2004 3 5200 15600 Лягушкин покрышки 24. 10. 2004 28 600 16800 Лебедева колесо 25. 10. 2004 12 500 6000 20

Список иначе называется базой данных. Основные элементы базы данных: поля - столбцы в списке записи - строки имя поля - заголовок столбца тип данных - тип данных ячейки 21

Списки и таблицы В Excel 2007 использование списков считается устаревшим и вместо них предлагается использовать таблицы. 22

Использование форм при работе со списками Сначала нужно добавить кнопку вызова формы на панель быстрого доступа. Вызываем контекстное меню в любом месте панели быстрого доступа Настройка панели быстрого доступа. . . Выбрать команды из: «Команды не на ленте» Находим команду «Форма. . . » Добавить>> OK. Панель быстрого доступа Форма Продажи сотовых телефонов в салоне "Рублесеть на Миклухо" в феврале 2009 года Продавец Авдеев Обозов Селиверстова Авдеев Гусельникова Бусыгин Лебедева Бусыгин Лягушкин Лебедева Обозов Бусыгин Авдеев Лебедева Селиверстова Бусыгин Селиверстова Гусельникова Лягушкин Нервозов Селиверстова Нервозов Карамазов Покупатель Леонов Данилко Морозов Курочкин Греков Усманов Морозов Леонов Морозов Усманов Леонов Данилко Морозов Леонов Данилко Леонов Греков Морозов Курочкин Греков Морозов Греков Усманов Леонов Данилко Усманов Производитель Марка Дата продажи Количество Samsung i 560 13. 02. 2009 26 Philips 192 13. 02. 2009 44 Nokia N 95 23. 02. 2009 5 Sony Ericsson W 960 i 10. 02. 2009 20 Sony Ericsson T 250 i 24. 02. 2009 33 Motorola W 220 15. 02. 2009 21 Motorola W 510 28. 02. 2009 2 Nokia 5610 24. 02. 2009 12 Nokia 1650 13. 02. 2009 2 Sony Ericsson W 910 i 22. 02. 2009 36 Samsung F 250 28. 02. 2009 22 Motorola W 220 24. 02. 2009 45 Sony Ericsson Z 750 24. 02. 2009 58 Philips 192 03. 02. 2009 34 Philips P 292 04. 02. 2009 35 Samsung F 250 14. 02. 2009 33 Motorola W 510 25. 02. 2009 5 Samsung J 210 15. 02. 2009 35 Philips P 292 14. 02. 2009 12 Samsung F 250 14. 02. 2009 50 Samsung P 520 25. 02. 2009 8 Samsung G 800 22. 02. 2009 5 Motorola W 510 24. 02. 2009 28 Nokia N 95 23. 02. 2009 3 Sony Ericsson T 250 i 23. 02. 2009 45 Motorola W 220 15. 02. 2009 35 Nokia N 81 23. 02. 2009 12 Sony Ericsson T 250 i 22. 02. 2009 22 Цена 13 590, 00 р. 1 289, 00 р. 19 450, 00 р. 21 890, 00 р. 3 050, 00 р. 2 169, 00 р. 6 590, 00 р. 14 990, 00 р. 2 220, 00 р. 14 650, 00 р. 6 890, 00 р. 2 169, 00 р. 9 690, 00 р. 1 289, 00 р. 3 299, 00 р. 6 890, 00 р. 6 590, 00 р. 4 590, 00 р. 3 299, 00 р. 6 890, 00 р. 29 990, 00 р. 13 590, 00 р. 6 590, 00 р. 19 450, 00 р. 3 050, 00 р. 2 169, 00 р. 16 590, 00 р. 3 050, 00 р. Стоимость 353 340, 00 р. 56 716, 00 р. 97 250, 00 р. 437 800, 00 р. 100 650, 00 р. 45 549, 00 р. 13 180, 00 р. 179 880, 00 р. 4 440, 00 р. 527 400, 00 р. 151 580, 00 р. 97 605, 00 р. 562 020, 00 р. 43 826, 00 р. 115 465, 00 р. 227 370, 00 р. 32 950, 00 р. 160 650, 00 р. 39 588, 00 р. 344 500, 00 р. 239 920, 00 р. 67 950, 00 р. 184 520, 00 р. 58 350, 00 р. 137 250, 00 р. 75 915, 00 р. 199 080, 00 р. 67 100, 00 р. 23

Поиск записей в списке Панель быстрого доступа Форма Критерии Задать критерии отбора Далее Будут найдены записи, удовлетворяющие одновременно всем критериям Продажи сотовых телефонов в салоне "Рублесеть на Миклухо" в феврале 2009 года Продавец Авдеев Обозов Селиверстова Авдеев Гусельникова Бусыгин Лебедева Бусыгин Лягушкин Лебедева Обозов Бусыгин Авдеев Лебедева Селиверстова Бусыгин Селиверстова Гусельникова Лягушкин Нервозов Селиверстова Нервозов Карамазов Покупатель Леонов Данилко Морозов Курочкин Греков Усманов Морозов Леонов Морозов Усманов Леонов Данилко Морозов Леонов Данилко Леонов Греков Морозов Курочкин Греков Морозов Греков Усманов Леонов Данилко Усманов Производитель Марка Дата продажи Количество Samsung i 560 13. 02. 2009 26 Philips 192 13. 02. 2009 44 Nokia N 95 23. 02. 2009 5 Sony Ericsson W 960 i 10. 02. 2009 20 Sony Ericsson T 250 i 24. 02. 2009 33 Motorola W 220 15. 02. 2009 21 Motorola W 510 28. 02. 2009 2 Nokia 5610 24. 02. 2009 12 Nokia 1650 13. 02. 2009 2 Sony Ericsson W 910 i 22. 02. 2009 36 Samsung F 250 28. 02. 2009 22 Motorola W 220 24. 02. 2009 45 Sony Ericsson Z 750 24. 02. 2009 58 Philips 192 03. 02. 2009 34 Philips P 292 04. 02. 2009 35 Samsung F 250 14. 02. 2009 33 Motorola W 510 25. 02. 2009 5 Samsung J 210 15. 02. 2009 35 Philips P 292 14. 02. 2009 12 Samsung F 250 14. 02. 2009 50 Samsung P 520 25. 02. 2009 8 Samsung G 800 22. 02. 2009 5 Motorola W 510 24. 02. 2009 28 Nokia N 95 23. 02. 2009 3 Sony Ericsson T 250 i 23. 02. 2009 45 Motorola W 220 15. 02. 2009 35 Nokia N 81 23. 02. 2009 12 Sony Ericsson T 250 i 22. 02. 2009 22 Цена 13 590, 00 р. 1 289, 00 р. 19 450, 00 р. 21 890, 00 р. 3 050, 00 р. 2 169, 00 р. 6 590, 00 р. 14 990, 00 р. 2 220, 00 р. 14 650, 00 р. 6 890, 00 р. 2 169, 00 р. 9 690, 00 р. 1 289, 00 р. 3 299, 00 р. 6 890, 00 р. 6 590, 00 р. 4 590, 00 р. 3 299, 00 р. 6 890, 00 р. 29 990, 00 р. 13 590, 00 р. 6 590, 00 р. 19 450, 00 р. 3 050, 00 р. 2 169, 00 р. 16 590, 00 р. 3 050, 00 р. Стоимость 353 340, 00 р. 56 716, 00 р. 97 250, 00 р. 437 800, 00 р. 100 650, 00 р. 45 549, 00 р. 13 180, 00 р. 179 880, 00 р. 4 440, 00 р. 527 400, 00 р. 151 580, 00 р. 97 605, 00 р. 562 020, 00 р. 43 826, 00 р. 115 465, 00 р. 227 370, 00 р. 32 950, 00 р. 160 650, 00 р. 39 588, 00 р. 344 500, 00 р. 239 920, 00 р. 67 950, 00 р. 184 520, 00 р. 58 350, 00 р. 137 250, 00 р. 75 915, 00 р. 199 080, 00 р. 67 100, 00 р. 24

Группа "Сортировка и фильтр" --> Сортировка При" src="https://present5.com/presentation/22432801_173391870/image-25.jpg" alt="Сортировка списков и таблиц Вкладка "Данные" --> Группа "Сортировка и фильтр" --> Сортировка При" /> Сортировка списков и таблиц Вкладка "Данные" --> Группа "Сортировка и фильтр" --> Сортировка При сортировке можно использовать до 64 уровней (в Excel 2003 не более трех). Продажи сотовых телефонов в салоне "Рублесеть на Миклухо" в феврале 2009 года Продавец Авдеев Обозов Селиверстова Авдеев Гусельникова Бусыгин Лебедева Бусыгин Лягушкин Лебедева Обозов Бусыгин Авдеев Лебедева Селиверстова Бусыгин Селиверстова Гусельникова Лягушкин Нервозов Селиверстова Нервозов Карамазов Покупатель Леонов Данилко Морозов Курочкин Греков Усманов Морозов Леонов Морозов Усманов Леонов Данилко Морозов Леонов Данилко Леонов Греков Морозов Курочкин Греков Морозов Греков Усманов Леонов Данилко Усманов Производитель Марка Дата продажи Количество Samsung i 560 13. 02. 2009 26 Philips 192 13. 02. 2009 44 Nokia N 95 23. 02. 2009 5 Sony Ericsson W 960 i 10. 02. 2009 20 Sony Ericsson T 250 i 24. 02. 2009 33 Motorola W 220 15. 02. 2009 21 Motorola W 510 28. 02. 2009 2 Nokia 5610 24. 02. 2009 12 Nokia 1650 13. 02. 2009 2 Sony Ericsson W 910 i 22. 02. 2009 36 Samsung F 250 28. 02. 2009 22 Motorola W 220 24. 02. 2009 45 Sony Ericsson Z 750 24. 02. 2009 58 Philips 192 03. 02. 2009 34 Philips P 292 04. 02. 2009 35 Samsung F 250 14. 02. 2009 33 Motorola W 510 25. 02. 2009 5 Samsung J 210 15. 02. 2009 35 Philips P 292 14. 02. 2009 12 Samsung F 250 14. 02. 2009 50 Samsung P 520 25. 02. 2009 8 Samsung G 800 22. 02. 2009 5 Motorola W 510 24. 02. 2009 28 Nokia N 95 23. 02. 2009 3 Sony Ericsson T 250 i 23. 02. 2009 45 Motorola W 220 15. 02. 2009 35 Nokia N 81 23. 02. 2009 12 Sony Ericsson T 250 i 22. 02. 2009 22 Цена 13 590, 00 р. 1 289, 00 р. 19 450, 00 р. 21 890, 00 р. 3 050, 00 р. 2 169, 00 р. 6 590, 00 р. 14 990, 00 р. 2 220, 00 р. 14 650, 00 р. 6 890, 00 р. 2 169, 00 р. 9 690, 00 р. 1 289, 00 р. 3 299, 00 р. 6 890, 00 р. 6 590, 00 р. 4 590, 00 р. 3 299, 00 р. 6 890, 00 р. 29 990, 00 р. 13 590, 00 р. 6 590, 00 р. 19 450, 00 р. 3 050, 00 р. 2 169, 00 р. 16 590, 00 р. 3 050, 00 р. Стоимость 353 340, 00 р. 56 716, 00 р. 97 250, 00 р. 437 800, 00 р. 100 650, 00 р. 45 549, 00 р. 13 180, 00 р. 179 880, 00 р. 4 440, 00 р. 527 400, 00 р. 151 580, 00 р. 97 605, 00 р. 562 020, 00 р. 43 826, 00 р. 115 465, 00 р. 227 370, 00 р. 32 950, 00 р. 160 650, 00 р. 39 588, 00 р. 344 500, 00 р. 239 920, 00 р. 67 950, 00 р. 184 520, 00 р. 58 350, 00 р. 137 250, 00 р. 75 915, 00 р. 199 080, 00 р. 67 100, 00 р. 25

Сортировкасписковидиапазонов 26

Промежуточные итоги в анализе списков Для того, чтобы подвести промежуточные итоги необходимо выполнить следующие действия: • Выполнить сортировку по полю (полям), по которому будут подводиться промежуточные итоги • Вкладка "Данные" --> Группа "Структура" --> Промежуточные итоги • Заполнить окно «Промежуточные итоги» Возможно подведение итогов по нескольким столбцам. 27

Промежуточныеитогиванализесписков Продажи сотовых телефонов в салоне "Рублесеть на Миклухо" в феврале 2013 года № продажи Продавец Производитель Марка Дата продажи Количество Цена 1 Авдеев Samsung GT-I 9001 Galaxy S Plus 13. 02. 2013 26 15 290, 00 р. 2 Авдеев HTC Sensation XE 10. 02. 2013 20 23 990, 00 р. 3 Лебедева Fly IQ 260 Blackbird 28. 02. 2013 2 6 490, 00 р. 4 Бусыгин Nokia Lumia 800 24. 02. 2013 12 19 490, 00 р. 5 Бусыгин Nokia C 2 -05 13. 02. 2013 2 2 590, 00 р. 6 Лягушкин HTC Desire A 8181 22. 02. 2013 36 13 990, 00 р. 7 Лебедева Samsung GT-S 5830 Galaxy Ace 28. 02. 2013 22 8 990, 00 р. 8 Обозов Fly Q 420 24. 02. 2013 45 3 190, 00 р. 9 Обозов HTC Wildfire A 3333 24. 02. 2013 58 8 990, 00 р. 10 Бусыгин Philips Xenium X 116 13. 02. 2013 34 1 890, 00 р. 11 Лебедева Fly Q 200 Swivel 25. 02. 2013 5 4 990, 00 р. 12 Селиверстова. Samsung S 3350 Chat 335 15. 02. 2013 35 3 650, 00 р. 13 Бусыгин Philips Xenium X 806 14. 02. 2013 12 6 280, 00 р. 14 Селиверстова. Samsung GT-S 5670 Galaxy Fit 14. 02. 2013 50 6 740, 00 р. 15 Гусельникова Samsung I 9100 Galaxy S II 25. 02. 2013 8 24 290, 00 р. 16 Гусельникова Samsung GT-I 8150 Galaxy W 22. 02. 2013 5 12 900, 00 р. 17 Лягушкин Fly DS 103 24. 02. 2013 28 790, 00 р. 18 Нервозов Nokia Lumia 800 23. 02. 2013 3 19 490, 00 р. 19 Нервозов HTC Wildfire S 23. 02. 2013 45 8 490, 00 р. 20 Селиверстова. Fly E 147 TV 15. 02. 2013 35 2 990, 00 р. 21 Нервозов Nokia Oro 23. 02. 2013 12 28 900, 00 р. 22 Карамазов HTC Desire A 8181 22. 02. 2013 22 13 990, 00 р. Стоимость 397 540, 00 р. 479 800, 00 р. 12 980, 00 р. 233 880, 00 р. 5 180, 00 р. 503 640, 00 р. 197 780, 00 р. 143 550, 00 р. 521 420, 00 р. 64 260, 00 р. 24 950, 00 р. 127 750, 00 р. 75 360, 00 р. 337 000, 00 р. 194 320, 00 р. 64 500, 00 р. 22 120, 00 р. 58 470, 00 р. 382 050, 00 р. 104 650, 00 р. 346 800, 00 р. 307 780, 00 р. 28

29

30

Фильтрация списков Отфильтровать список – значит скрыть все строки из списка за исключением тех, которые удовлетворяют заданным условиям отбора. В Excel для этого есть две команды: • Автофильтр для простых условий отбора • Расширенный фильтр для более сложных критериев 31

АВТОФИЛЬТР 32

Автофильтр Выделить любую ячейку в списке Вкладка "Данные" Группа "Сортировка и фильтр" Фильтр Автофильтр можно последовательно применить к любому количеству столбцов списка. 33

Расширенный фильтр Выделить любую ячейку в списке Вкладка "Данные" Группа "Сортировка и фильтр" Дополнительно 34

Расширенный фильтр Требуется: 1. Задание строк условий отбора в отдельном диапазоне рабочего листа. Диапазон условий должен содержать не менее двух строк. 2. Рекомендуется располагать его ниже списка. 3. Верхняя строка диапазона должна содержать один или более заголовков столбцов. 35

Расширенный фильтр Для копирования отобранных строк нужно указать диапазон, в который копируются строки списка. 36

РАСШИРЕННЫЙ ФИЛЬТР Критерий Блок вывода 37

Данные, Фильтр, Расширенный фильтр 38

Результат 39

условие =СРЗНАЧ(G 3: G 23) =G 2>$I$25 40

Задание Сконструировать формулу, выводящую время, оставшееся до конца пары. Кроме этого формула должна выводить текст «Сегодня информатики нет» , в те дни, когда нет информатики. В тот день недели, когда информатика есть, вне пары должен выводиться текст «Пара еще не идет» или «Пара уже закончилась. 41

42