Информационные технологии в экономике Контрольная работа 1

1. Сводные таблицы.
Меню: Данные – Сводная таблица. Включается мастер сводных таблиц. 4 шага. Настройка сводных таблиц. Параметры сводной таблицы. Работа со сводной таблицей: изменение структуры, удаление, добавление поля, обновление данных, изменение полей. Форматирование сводной таблицы. Группировка элементов сводной таблицы. Просмотр деталей. Создание диаграмм по сводной таблице. Консолидация листов рабочей книги. Примеры сводных таблиц: анализ опытных данных (результаты опросов респондентов) и т.д.
Задание 1. В исходном файле Excel рабочий лист «Сводная таблица» запустить мастер сводных таблиц и создать сводную таблицу на новом рабочем листе.
Задание 2. В исходном файле Excel - консолидация – создание сводной таблицы по трем рабочим листам: конс магазин 1, конс магазин 2, конс магазин 3.

2. Анализ электронных таблиц с помощью сценария «что-если».

Анализ «что-если». Например, расчет ипотечной ссуды. Лист разделен на 2 секции, в которых расположены ячейки ввода и ячейки результатов. С помощью этого листа можно ответить на вопросы «что-если», подобные следующим:
- Что, если я смогу договориться о более низкой цене на имущество?
- Что, если кредитор потребует 20% выплату?
- Что, если я смогу получить 40-летнюю ссуду?
- Что, если ставка снизится до 7,5%?
Анализ данных с использованием процедур подбора параметров.

1. Подбор параметра для одной ячейки. («Что – если» наоборот – определяет значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке). Обратная задача. Сервис – подбор параметров.
Пример 1. Цена 125000, Процентная ставка 8,25%. Какую максимальную стоимость покупки вы можете себе позволить, если нужно, чтобы месячная выплата была не более 10000р.

Пример 1. - изображен на рисунке 1
Задание 3. В исходном файле Excel – рабочий лист Пример 1 - Какую цену квартиры можно заплатить, если в месяц платить 10000 руб.
2. Анализ «что-если» с использованием макросов. Макрос – программа, выполняющая автоматически несколько операций. Например, несколько макросов для лучшего, наиболее вероятного и худшего случаев.
Пример 2. - изображен на рисунке 2
Задание 4. В исходном файле Excel – рабочий лист Пример 2 - Создать три макроса: Лучший случай – стоимость услуг -30, стоимость материалов - 57; Худший – 38, 62; Вероятный – 34, 59. Создать три кнопки на рабочем листе, каждая соответствует своему макросу.

3. Анализ «что-если» с использованием таблиц подстановки. Данные – таблица подстановки. Создание таблицы подстановки с одной ячейкой исходных данных. Создание таблицы подстановки с двумя ячейками исходных данных.

Задание 5. В исходном файле Excel – рабочий лист Пример 2. – создать таблицу подстановки - Строки – стоимость услуг. Столбцы – стоимость материалов. Пересечение стоимости услуг и стоимости материалов – суммарная прибыль. Найти точку безубыточности – при каких стоимостях услуг и материалов суммарная прибыль будет наиболее близка к 0.


4. Диспетчер сценариев – основной инструмент «Что-если». Сервис – Сценарии. Отчет по сценарию. Недостатки – не более 32 изменяемых ячеек.
Задание 6 - В исходном файле Excel – рабочий лист Пример 2 – создать три сценария, таких же, как и макросы – лучший, худший и вероятный случай.

3. Анализ данных с использованием процедур поиска решений.

1. Простой пример. Необходимо получить максимальную прибыль от изделий. Но есть несколько ограничений;
Исходный файл - Пример 5.
- Общий объем производства всего 300 изделий в день;
- Компании нужно произвести не менее 50 изделий А для выполнения существующего заказа;
- Компании нужно произвести не менее 40 изделий В для выполнения планового заказа;
- Поскольку сбыт изделий С относительно небольшой, то должно быть произведено не более 40 единиц этого изделия.
- Неизвестные величины – количество изделий – должны быть неотрицательны и целыми числами.

Сервис – надстройки – поиск решения.

Задачи по линейному программированию с использованием Excel

Исходный файл - Задача 1. Оптимальный план выпуска продукции мебельного цеха.

Таблица 1. Параметры задачи. - изображено на рисунке 3
Примечание: ДСП и стекло в метрах, труд в человеко-день трудозатрат, запасы – ограничения, прибыль от продаж в у.е.
Цель: при каком количестве шкафов и тумб сделать прибыль максимальной.

Таблица 2. Элементы модели. - изображено на рисунке 4
Исходный файл - Задача 2. Мини-кейс «На кондитерской фабрике»
Маленькая кондитерская фабрика должна закрыться на реконструкцию. Необходимо реализовать оставшиеся запасы сырья для производства продуктов из ассортимента фабрики, получив максимальную прибыль. Запасы и расход каждого вида сырья для производства единицы продукции каждого вида, а также получаемая при этом прибыль представлена в табл. 3.
Мастер предлагает вариант на глазок (опыт работы 20 лет): выпустить по 200 пакетов каждого продукта, а прибыль соответственно получится 1080 у.е. Используйте научный подход (линейное программирование) и предложите лучший план.

Таблица 3. Параметры задачи. - изображены на рисунке 5
1. Переменные
2. Целевая функция.
3. Элементы модели.
Изображение на рисунке 6
Расход темного шоколада.

Расход = а11 Х1 + а12 Х2 + а13 Х3 + а14 Х4 + а15 Х5

Количество пакетов каждого из продуктов должно быть таково, чтобы этот расход был меньше запасов b из таблицы параметров.
Изображение на рисунке 7
Создание макроса.

1. Меню: Сервис – макрос – начать запись.
2. Имя макроса – например, Вопрос 1. Можете назначить сочетание клавиш – в дальнейшем при наборе этого сочетания клавиш у вас запустится данный макрос. Затем нажать ENTER. У вас должна появиться панель, где написан Ос, а под ним синий квадратик.
3. В данный момент вы находитесь в режиме Запись макроса. Все, что вы введете в компьютер, запишется в тело макроса. Выполните последовательность операций по созданию сводной таблицы для листа социологических исследований:
a. Меню: данные – сводная таблица – новый лист.
b. В поле элементов данных перетащите Имя из списка полей;
c. поле вопрос (Например, 1) перетащите под Количество по полю Имя.
4. Далее обязательно нажать на синий квадратик на панели Ос, или пойти в меню: сервис – макрос – остановить запись.
5. Создание кнопки, по нажатию которой будет исполнен макрос.
a. Меню: вид – панели инструментов – формы.
b. На появившейся или имеющейся панели формы нажать кнопку и нарисовать на свободном месте (где нет данных) листа рабочей книги квадрат.
c. Вам будет предложено назначить макрос объекту. Выберете тот макрос, который вы создали (например, вопрос1). Нажать Ок.
d. Название кнопки изменить, вместо кнопка 1 написать, например Вопрос 1.
e. Теперь, если вы нажмете курсором на созданную вами кнопку, у вас вновь будет создана сводная таблица по данному вопросу.
6. Сделать то же самое по всем вопросам, то есть создать 6 кнопок для выполнения макросов по обработке анкет.
7. Теперь заменить исходные данные в таблице (число ответивших на разные ответы на вопросы) и выполнить макросы еще раз.