Лабораторная работа
Анализ данных в Excel
- Сводный анализ.
- Создаем на Лист1 таблицу, которая содержит сведения о совершенных сделках.
- Добавляем столбец Сумма, рассчитываем его значения как произведение Цена на Количество (например, для ячейки F2 вводим формулу =D2*E2).
- Строим сводные таблицы, содержащие следующие данные:
- объем сделок между всеми покупателями и продавцами;
- число сделок каждого их покупателей для каждого из товаров;
- количество каждого проданного товара каждым из продавцов для каждого из покупателей;
- объем сделок, средняя сумма сделок и количество сделок каждого из покупателей для каждого из продавцов по каждому из товаров;
- процентная доля объема сделок каждого из покупателей по всем продавцам;
- объем сделок и процентная доля продаж каждого из товаров относительно товара «компьютер» для всех продавцов.
- Называем первый лист «Сводный анализ».
- Статистический анализ.
- На Лист2 генерируем по 1-му столбцу из 1000 случайных величин, подчиняющихся закону, указанному в таблице:
|
Распределение |
Параметры |
Округление до целых |
|
Равномерное |
Между 50 и 80 |
Требуется |
|
Нормальное |
Среднее – 60; Отклонение – 3 |
Требуется |
|
Пуассона |
Лямбда – 60 |
Не требуется |
|
Биноминальное |
P – 0,8; число испытаний - 60 |
Не требуется |
- С помощью пакета анализа определите среднее, минимальное и максимальное значение, а также интервал значений для числа покупателей по каждому из распределений (инструмент «Описательная статистика»).
- Строим гистограммы для наших данных, используя в качестве интервала карманов диапазон целых чисел от минимального значения до максимального с шагом, обеспечивающим построение 8 столбцов.
- Назовите второй лист «Статистический анализ».
- Таблицы подстановки.
- Переходим на Лист3. Для расчета ежемесячной выплаты по займу необходимо составить таблицу данных. С помощью функции ПЛТ (ППЛАТ) рассчитываем размер ежемесячной выплаты по 12-летнему займу на 60000$ под 9% годовых, если первоначальный минимальный взнос составляет 15% от суммы займа при ежемесячной капитализации.
- С помощью одномерной таблицы подстановки рассчитаем размер ежемесячных выплат и номинальный поток платежей (сумму всех выплат) за срок погашения займа для годовых процентный ставок от 7% до 12% с шагом 0,5%.
- С помощью одномерной таблицы подстановки рассчитаем размер ежемесячных выплат и номинальный поток платежей за срок погашения займа для значения минимального первоначального взноса от 10% до 20% с шагом 1%.
- С помощью двухмерной таблицы подстановки рассчитаем размер ежемесячных выплат для годовых процентных ставок от 7% до 12% с шагом 1% при сроке займа от 10 до 20 лет с шагом 2 года.
3.5. С помощью условного форматирования построим карту полученной таблицы чувствительности следующим образом:
- a) если значение ежемесячных выплат меньше 500$ включительно, то для исходной ячейки выбираем шрифт зеленого цвета полужирного начертания (зона I);
- b) если значение ежемесячных выплат от 500$ до 600$ включительно, то для исходной ячейки выбираем шрифт синего цвета полужирного начертания (зона II);
- c) если значение ежемесячных выплат более 600$, то для исходной ячейки выбираем шрифт красного цвета полужирного начертания (зона III).
3.6. С помощью подбора параметра найдите критические значения суммы займа при прочих неизменных параметрах, для которой вся карта будет покрыта:
- a) зоной I;
- b) зоной I и II;
- c) зоной III.
3.7. Называем третий лист «Таблицы подстановки».
Скачать: