Учебно-методическое пособие для студентов вузов Павлодар (стр. 39 из 76)

6 Сохраните рабочую книгу.

Контрольные вопросы

1 Для чего применяются логические функции в Excel?

2 Какие логические функции вы знаете?

3 Опишите аргументы функции ЕСЛИ? Что может быть использовано в качестве аргументов?

4 Проведите синтаксический анализ следующей формулы: ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала").

5 В каких случаях применяется функция НЕ?

6 Сколько уровней вложенности может иметь функция ЕСЛИ?

Лабораторная работа №20

Финансовые функции. Подбор параметра

Цель: научиться использовать финансовые функции для решения задач, а также применять аппарат подбора параметра.

Краткие теоретические сведения

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

БЗ(норма, число_периодов, выплата, нз, тип)

Для пренумерандо БЗ(норма, число_периодов, выплата, , 1)

Для постнумерандо БЗ(норма, число_периодов, выплата)

Если процентная ставка меняется в течении времени, то для расчета будущего значения единой суммы после начисления сложных процентов можно использовать функцию БЗРАСПИС.

БЗРАСПИС(инвестиция, {ставка1; ставка2; … ; ставкаN})

Задание

1 Создайте новую рабочую книгу, назовите ее «Финансовые задачи».

2 Решите приведенные ниже задачи, используя финансовые функции БЗ и БЗРАСПИС

Задача №1 Рассчитайте, какая сумма окажется на счете, если сумма размером 50 тысяч тенге размещена под 19% годовых на три года, а проценты начисляются каждый день.

Задача №2 По вкладу размером 20 тысяч тенге начисляется 15 % годовых. Рассчитайте какая сумма будет на сберегательном счете через 7 лет, если проценты начисляются ежегодно.

Задача №3 На сберегательный счет вносятся платежи по 10 тысяч тенге в начале каждого месяца. Рассчитайте, какая сумма окажется на счете через 2 года при ставке 23, 5% годовых. Сравните будущее значение счета, если платежи вносятся в конце каждого периода.

Задача №4 Рассчитайте будущую стоимость облигации номиналом 40 тысяч тенге выпущенной на 10 лет, если предусмотрен следующий порядок начисления процентов: в первые четыре года — 10% годовых, в следующие 4 года — 15% годовых и в последние 2 года — 17% годовых.

Задача №5 Ожидается, что будущая стоимость инвестиции размером 650 тысяч тенге к концу третьего года составит 1800 тысяч тенге. При этом за первый год доходность составит 15%, за второй — 17?. Рассчитайте доходность инвестиции за третий год.

Задача №6 Решите финансовую задачу, используя аппарат подбора параметра. Внесите на рабочий лист следующие данные (рисунок 33)

Рисунок 33 – Вид рабочего листа Финансовые задачи

Предположим, что фирма, в которой Вы работаете экономистом, имеет возможность вложить деньги в 4 инвестиции, условия которых приведены в таблице. (Заметим, что фирма имеет возможность вложить в 4-ю инвестицию любую сумму, которая пока не определена.) Вам необходимо сделать следующее:

- рассчитать будущую стоимость всех инвестиций через 5 лет;

- подсчитать общую будущую стоимость всех инвестиций через 5 лет;

- подсчитать общую сумму предполагаемых расходов фирмы в течении 5 лет;

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

Практическая работа №11

Отбор данных в электронных таблицах. Автоматическое подведение итогов. Консолидация данных. Сводные таблицы

Цель: научиться использовать возможности электронного процессора Microsoft Excel для хранения и обработки данных.

Ход работы

Занесение информации в базу данных

1 Загрузите программу Microsoft Excel.

2 Сохраните рабочую книгу в папке ПР11 под именем Учет товаров.

3 Создайте на рабочем листе таблицу следующего содержания: (рисунок 34)

Рисунок 34 – Вид рабочего листа Учет товаров

4 Таблица имеет вид базы данных, состоящей из записей продажи товаров со склада. Запись указывает – какой организации продан товар, когда проведена продажа, товар, единицу измерения товара, его стоимость и количество. В столбец Сумма заносится стоимость покупки, рассчитываемая по формуле =Цена *Кол-во. В последнем столбце указывается форма оплаты: безналичный расчет (б/р), бартер (бар), наличный расчет (н/р). Рассчитайте значения столбца Сумма самостоятельно.

5 Скопируйте содержимое листа Лист1 на Лист2.

Освоение технологии сортировки и отбора записей по заданному критерию

6 На листе 2 требуется отсортировать наименования организаций в алфавитном порядке и внутри каждой организации наименование товара в алфавитном порядке, и внутри каждого товара отсортировать по возрастанию количество проданного товара. Проделайте следующие действия:

- установите курсор в область базы данных и выполните команду Данные – Сортировка. В первом уровне сортировки выберите поле Организация, во втором – Товар, в третьем – Кол-во.

7 Для фильтрации записей необходимо выполнить команду Данные – Фильтр – Автофильтр. В строке заголовка таблицы появились значки выпадающего меню.

8 Допустим нам необходимо просмотреть весь список товара, проданного АО «Белокуриха». Щелкнуть на значок в столбце Организация

и выбрать АО «Белокуриха». Появились только те записи, где присутствует указанная организация. Чтобы вернуть все записи, надо опять щелкнуть на значок раскрывающегося списка и выбрать строку Все.

9 Выведите на экран записи, содержащие информацию о проданном товаре – хлеб для организации АО «Белокуриха», то есть осуществить выборку по двум полям. Отобразите все записи.

10 Вывести на экран записи, содержащие организацию АО «Белокуриха», в которых цена товара не превышает 100 тенге. Для выборки по столбцу Цена при открытии меню выбрать строку Условие. В появившемся окне Пользовательский автофильтр при помощи значков открывающегося меню установите условие >100. Отобразите все записи.

11 Вывести на экран все записи, содержащие колхоз «Восток» и дату покупки товара в промежутке от 2 февраля до 13 июня. В данном случае в окне Пользовательский автофильтр заполните обе строки. Правильно выберите соединение условий И или ИЛИ. Отобразите все записи.

12 Скопируйте таблицу с Листа 2 на Лист 3 и дайте ему имя – Расширенный фильтр. К примеру требуется отобрать всю информацию о товарах – хлеб, лимон, бензин. Так как критериев больше двух, то следует использовать расширенный фильтр. Для этого:

- ниже таблицы, оставив 3 пустые строки, скопируйте строку заголовка таблицы, например, в строку 22. В строке 23 сформируем критерий отбора записей. В ячейку C23 введем – хлеб, C24 – лимон, C25 – бензин;

- установим курсор в область таблицы, в которой будет производиться выборка данных и выполним команду Данные – Фильтр – Расширенный фильтр;

- в появившемся диалоговом окне в строке Исходный диапазон появится запись A1:H18.Активизируйте строку Диапазон условий, перейдите в таблицу и выделите диапазон A22:H25.

Освоение технологии автоматического подведения итогов

13 Добавьте в рабочую книгу Учет товаров лист и назовите его Промежуточные итоги.

14 Необходимо подвести итоги о продаже товаров каждой организации, затем еще итоги в каждой организации по датам.

15 Сначала необходимо упорядочить таблицу по полю Организация, второй уровень сортировки Дата. Затем выполнить команду Данные – Итоги. В появившемся окне в первой строке выбрать Организация, в строке Операция из списка выбрать Сумма, в третьей строке выбрать поле, по которым необходимо подвести итоги – Сумма.

16 Просмотрите результаты. В левой половине экрана появились символы структуры (значки + и -). Ознакомьтесь самостоятельно с их назначением.

17 Далее вновь выполнить подведение итогов. Вводим поле Дата. Чтобы предыдущие итоги не стерлись, значок Заменять текущие итоги должен быть выключен. В результате получим таблицу итогов о продаже товара каждой организации и вдобавок еще итоговые данные по датам продажи.

Освоение технологии консолидации данных

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

- добавьте в рабочую книгу 3 листа и назовите их соответственно Склад1, Склад2, Склад3;

- скопируйте таблицу (диапазон ячеек A1:H18) с Листа 1 на добавленные листы;

- изменим данные на листе Склад 2 – введите число 10 в ячейки F4, F6, F15. Таким образом, мы изменили количество проданного товара – хлеб АО «Белокуриха»;

- изменим данные на листе Склад 3 – введите число 30 в ячейки E4, E6, E15. Таким образом, мы изменили цену на товар – хлеб проданного АО «Белокуриха».

19 Допустим, необходимо подвести итоги о продаже хлебу организации АО «Белокуриха» в сумме в этих трех точках. Для этого:

- упорядочите все три таблицы по полю Товар с помощью команды Данные – Сортировка;

- подведите итоги по полю Товар, суммирующие значения по полю Сумма;

- добавьте лист в рабочую книгу и назовите его Консолидация. Находясь на этом листе, выполните команду Данные – Консолидация;

- в появившемся диалоговом окне выберите функцию Сумма;

- щелкните мышью в поле Ссылки, перейдите на лист Склад1 и выделите итоговую сумму продажи хлеба АО «Белокуриха». Данные появятся в поле ссылки. Нажмите кнопку Добавить;