СКАЧАТЬ РАБОТУ БЕСПЛАТНО -
1. Статистический анализ и прогнозирование в MS Excel
1.1 Использование равномерного распределения
Постановка задачи и ее математическая формулировка
Необходимо сгенерировать в MS Excel на Лист1 столбец из 300 случайных величин, подчиняющихся закону Равномерное распределение. Параметры между 20 и 60.
Описание входной и выходной информации
Для того, чтобы создать последовательность, моделирующую реальные данные, необходимо воспользоваться функцией из Пакета анализа Генерация случайных чисел. Для этого сначала следует задать на рабочем листе MSExcel диапазон, который будет содержать данную последовательность. Затем выбрать команду Анализ данных на вкладке Данные. В появившемся окне диалога в списке Инструменты анализа выбрать элемент Генерация случайных чисел в соответствии с рисунком 1.1.
Рисунок 1.1. Окно диалога Анализ данных
В окне диалога Генерация случайных чисел в поле Число переменных необходимо ввести единицу (рис. 1.2). Это будет означать, что число столбцов, которые будут заполнены последовательностью смоделированных данных, будет равно единице. В поле Число случайных чисел ввести 300. Затем в списке Распределение выбрать элемент Равномерное, в поля Между и ввести значения 20 и 60. В поле Случайное рассеивание можно ввести некоторую величину, в случае, если необходимо создать несколько одинаково распределенных последовательностей с отличающимися значениями. Если достаточно одной последовательности, оставить это поле пустым. В поле Выходной интервал ввести ссылку на первую ячейку диапазона, который должен быть заполнен последовательностью и нажать кнопку ОК для генерации последовательности.
Рисунок. 1.2. Окно диалога Генерация случайных чисел
Результаты расчетов, графики и диаграммы
Результатом этих действий будет столбец из 300 случайных значений от 20 до 60 распределенных равномерно в соответствии с рисунком 1.3.
Рисунок 1.3. Сгенерированная последовательность случайных чисел, распределенных равномерно
1.2 Гистограмма
Постановка задачи и ее математическая формулировка
Построить гистограмму для ваших данных, используя в качестве интервала карманов диапазон целых чисел от 18 до 54 с шагом 3.
Описание входной и выходной информации
Это средство используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. При этом рассчитываются частота попаданий для заданного диапазона ячеек. Измеряемые величины условно делятся на две группы:
– По своей природе измеряемая величина является случайной. При этом результат отдельного наблюдения такой величины заранее неизвестен.
– Измеряемая величина является по своей природе постоянной. На измерении величин такого типа оказывают влияние многочисленные неконтролируемые внешние факторы, приводящие к тому, что результаты отдельных измерений неодинаковы. Поэтому в процессе измерения постоянная величина проявляется как случайная.
В списке Инструменты анализа выбрать пункт Гистограмма. В поле Входной интервал указать диапазон $C$1:$C$300. В поле Интервал карманов ввести диапазон $E$1:$E$37. Если не ввести Интервал карманов, MSExcel по умолчанию создаст равномерно распределенный диапазон. В качестве Выходного интервала ввести ссылку на левую верхнюю ячейку диапазона, в который будут помещены результаты. Установить переключатель Вывод графика в соответствии с рисунком 1.4 и 1.5.
При построении гистограмм можно использовать дополнительные возможности, установив соответствующие флажки в окне диалога Гистограмма:
– Парето – позволяет создавать копию результата, в которой интервалы разбиения отсортированы по возрастанию количества значений случайной величины, попавших в интервал.
– Интегральный процент – позволяет создавать дополнительный столбец в результатах, отражающий процент попаданий в каждый интервал разбиения.
Рисунок 1.4. Окно диалога Гистограмма
Результаты расчетов, графики и диаграммы
Рисунок 1.5 Гистограмма
При помощи построенных описанным способом гистограмм можно быстро построить необходимое распределение, если исходные значения не подвергаются изменениям. В противном случае следует воспользоваться статистическими функциями MS Excel.
1.3 Сглаживание данных. Скользящее среднее
Постановка задачи и ее математическая формулировка
Построить Скользящее среднее для 4 интервалов.
Описание входной и выходной информации
При анализе данных частота из-за некоторых внешних факторов и случайных колебаний исследуемые величины не имеют четко выраженной закономерности. Для того, чтобы лучше понять и увидеть на гистограмме закономерностей изменения величин, используют сглаживание колебаний. Пакет анализа предоставляет для этого два метода: Скользящее среднее и экспоненциальное сглаживание. При использовании этих методов формулы в ячейки результатов помещаются сразу, поэтому при изменении входных данных будут автоматически пересчитываться сглаживающие величины.
При использовании метода Скользящее среднее для каждого интервала вычисляется среднее арифметическое значение на основе значений из нескольких предыдущих интервалов (количество используемых для этого интервалов задается в параметрах сглаживания). При использовании метода Экспоненциальное сглаживание следующее значение вычисляется как среднее от значения точки данных на текущем интервале и экспоненциального сглаженного, полученного на предыдущей итерации. При этом все предшествующие текущему интервалы автоматически включаются в вычисления на каждой итерации; можно задать весовой коэффициент для текущего интервала, который будет являться фактором затухания. Чем выше этот коэффициент этот коэффициент, тем выше степень затухания.
Для того, чтобы воспользоваться методом Скользящее среднее в окне диалога Анализ данных следует выбрать средство Скользящее среднее в соответствии с рисунком 1.6 и 1.7.
Рисунок 1.6 Окно диалога Скользящее среднее
Рисунок 1.7 Результат работы метода скользящее среднее
1.4 Сглаживание данных. Экспоненциальное сглаживание
Постановка задачи и ее математическая формулировка
Построить Экспоненциальное сглаживание с фактором затухания 0,3.
Описание входной и выходной информации
Чтобы построить Экспоненциальное сглаживание, в диалоговом окне команды Анализ данных выбрать элемент Экспоненциальное сглаживание. Ниже представлены заполненные поля ввода окна диалога Экспоненциальное сглаживание в соответствии с рисунком 1.8.
Рисунок 1.8 Окно диалога Экспоненциальное сглаживание
Результаты расчетов, графики и диаграммы
Результаты экспоненциального сглаживания представлены ниже в соответствии с рисунком 1.9.
Рисунок 1.9 Результат работы метода Экспоненциальное сглаживание
Если при изменении применении методов Скользящее среднее и Экспоненциальное сглаживание некоторые ячейки содержат значения ошибки, это означает, что у этих ячеек не существует трех предшествующих интервалов.
1.5 Прогнозирование данных при помощи линии тренда
Постановка задачи и ее математическая формулировка
При помощи линии тренда спрогнозировать данные на 3 периода вперед, определить историю данных на 1 период назад, дать представление о степени достоверности отображаемых на гистограмме данных.
Описание входной и выходной информации
Линии тренда являются статистическим инструментом, при помощи которого можно анализировать тренды и делать прогнозы, а также давать представление о степени достоверности отображаемых на диаграмме данных. Линию тренда можно добавить к любому ряду данных, использующему ненормированные плоскости диаграммы. Линия тренда связывается с рядом данных, к которому была добавлена, и автоматически пересчитывается и обновляется при изменении значений любых точек ряда данных. При создании линии тренда на основе данных диаграммы применяется один из пяти типов аппроксимирующих линий: линейная, логарифмическая, полиноминальная, степенная и экспоненциальная. При построении линии тренда предоставляется возможность выбирать значения пересечения линии тренда с осью Y, добавлять к диаграмме уравнение аппроксимации и величину достоверности аппроксимации (R-квадрат). Чем ближе значение величины достоверности аппроксимации к 1, тем достовернее прогноз данных.
Для того, чтобы добавить линию тренда, следует выделить нужный ряд данных, в контекстном меню выбрать команду Добавить линию тренда в соответствии с рисунком 1.10.
В появившемся окне диалога на вкладке Параметры линии тренда указать тип аппроксимирующей линии; установить параметры линии тренда: автоматическое (для линии тренда используется название ряда данных), другое (позволяет ввести новое название линии тренда в текстовое поле), прогноз вперед на и назад на (соответственно прогнозируются данные вперед на указанное число периодов или определяется история данных назад на указанное число периодов); пересечение кривой с осью Y в точке (по умолчанию вычисляется на основе данных); показывать уравнение на диаграмме (Выводится уравнение аппроксимации на диаграмму в виде несвязанного текстового поля); поместить на диаграмму величину достоверности аппроксимации (осуществляется вывод на диаграмму величины R^2 в виде несвязанного текстового воля).
Рис. 1.10. Окно диалога Формат линии тренда
Результаты расчетов, графики и диаграммы
Результатом этих действий будет линия тренда, прогнозирующая данные на 3 периода вперед, определяющая историю данных на 3 период назад, дающая представление о степени достоверности отображаемых на гистограмме данных в соответствии с рисунком 1.11.
Рис. 1.11. Прогнозирование данных
2. Финансовые расчеты и оптимизационные задачи в MS Excel
2.1 Расчеты с использованием финансовых функций в MS Excel
Постановка задачи и ее математическая формулировка
Фонд размером 21 млн. руб. был сформирован за два года за счет отчислений 770000 руб. в начале каждого месяца. Определить годовую ставку процента.
Описание входной и выходной информации
Финансовые функции MS Excel предназначены для вычисления базовых величин, необходимых при проведении сложных финансовых расчетов. Работа с финансовыми функциями MS Excel предполагает использование следующей методики:
Первым шагом является подготовка на рабочем листе значений основных аргументов.
Следующий шаг – переход к ячейке, в которую вводится формула, использующая встроенную финансовую функцию.
Затем следует вызвать Мастера функций командой на вкладке Формула Вставка-Функция – категория Финансовые.
В списке функций выбрать необходимую финансовую функцию, причем, в поле ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов.
СТАВКА (кпер; плт; пс; бс; тип)
Функция ВСТАВКА возвращает процентную ставку по аннуитету за один период.
Кпер – общее число периодов выплат займа или инвестиции.
Плт – выплата, производимая в каждый период и не меняющаяся за все время выплаты займа или инвестиции.
Бс – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.
Тип – число 0 или 1, обозначающее, когда должна производиться выплата.
Результаты расчетов, графики и диаграммы
Решение заданной задачи реализовано в соответствии с рисунками 2.1, 2.2.
Рисунок 2.1 – Окно диалога функции СТАВКА
Результатом расчетов является ставка процента за один период в соответствии с рисунком 2.2 в ячейке В8.
Рисунок 2.2 Формула для функции СТАВКА
Чтобы вычислить годовую ставку процента, необходимо полученный результат увеличить в 12 раз.
Рисунок 2.3 Результат вычислений
2.2 Расчеты с использованием подбора параметра в MS Excel
Постановка задачи и ее математическая формулировка
Определить необходимый уровень ставки купонных выплат, производящихся раз в полугодие, если курс покупки облигации – 87,91, облигации приобретены 1.03.01 и будут погашены 5.03.03; ожидаемая годовая ставка помещения (доход) – 14,45, номинал облигации (погашение) – 100, базис расчета – 1. Для решения задачи воспользоваться функцией ЦЕНА().
Описание входной и выходной информации
Ниже приведен результат решения задачи при помощи функции СТАВКА в соответствии с рисунком 2.4.
Рис. 2.4 Решение задачи
Средство Подбор параметра позволяет найти аргумент, при котором получается задаваемое решение. Excel находит ответ с помощью последовательных итераций. Достаточно указать целевую ячейку, ее желаемое значение и изменяемую ячейку-параметр, влияющую на содержимое целевой ячейки. При этом ячейка-параметр должна содержать значение, но не формулу, и влиять на результат, который следует получить. Целевая же ячейка должна содержать формулу, включающую ссылку на ячейку параметр (прямо или опосредованно).
Результат использования Подбора параметра приведен ниже в соответствии с рисунком 2.5.
Результаты расчетов, графики и диаграммы
Рис. 2.5 Результат работы Подбор параметра
2.3 Оптимизационные задачи в MS Excel
Постановка задачи и ее математическая формулировка
Четверо рабочих могут выполнять пять видов работ. Стоимость выполнения каждого вида работ составляет: для первого рабочего – 1, 6, 3, 5, 7; для второго – 5, 2, 7, 8, 3; для третьего – 3, 5, 1, 9, 2; для четвертого – 6, 4, 2, 10, 5. Составить план выполнения работ таким образом, чтобы каждый рабочий был занят не более чем на одной работе, а суммарная стоимость выполненных работ была минимальной.
Описание входной и выходной информации
Средство анализа Поиск решения применяется для подбора оптимального решения при заданных ограничениях. Формулировка задач, решаемых при помощи этого средства обычно представляет собой систему уравнений с несколькими неизвестными и набор ограничений на решения. Обычно при помощи надстройки Поиск решения решаются следующие задачи:
Ассортимент продукции (максимизация выпуска товаров при ограничениях на сырье для производства этих товаров).
Составление штатного расписания для достижения наилучших результатов при наименьших расходах.
Планирование перевозок (минимизация затрат на транспортировку товаров при условии удовлетворения потребностей потребителей).
Составление смеси (получение заданного качества смеси при наименьших расходах)
Все эти задачи обладают следующими свойствами:
1. Наличие единственной цели.
2. Наличие ограничений, выражающихся, как правило, в виде неравенств.
3. Наличие набора входных значений-переменных, непосредственно или косвенно влияющих на ограничения и на оптимизируемые величины.
Правильная формулировка ограничений является самой ответственной частью при создании модели для поиска решения, например:
1. Если в модели в наличии несколько периодов времени, величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода.
2. В модели поставок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец периода плюс количество отправленного.
3. Некоторые величины в модели по своему физическому смыслу не могут быть отрицательными либо дробными.
Ограничения имеют тот же синтаксис, что и логические формулы. Но, если в найденном решении логические формулы будут выполнены точно, то ограничения – с некоторой возможной погрешностью. Величина этой погрешности задается параметром Относительная погрешность и по умолчанию равна 0,000001.
Ход решения.
Подготовку рабочего листа осуществляем в соответствии с рисунком 2.6, формулы для расчета приведены ниже в таблице.
Таблица: Формулы для расчета в задаче
Рисунок 2.6 Подготовка рабочего листа для решения задачи
Устанавливаем ограничения в окне Поиск решения, в соответствии с рисунком 2.7. В окне Параметры поиска решения необходимо также установить флажок Линейная модель.
Рисунок 2.7 Установка параметров в окне Поиск решения
Результаты расчетов, графики и диаграммы
Решение задачи представлено на рисунке 2.8.
Рисунок 2.8 Решение задачи
2.5 Анализ бизнес-ситуаций с помощью Диспетчера сценариев в MS Excel
Постановка задачи и ее математическая формулировка
Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение n лет (табл. Исходные данные). При какой процентной ставке эта сделка имеет смысл?
Таблица: Исходные данные
Описание входной и выходной информации
Перед созданием Сценариев необходимо подготовить рабочий лист, на котором будет решаться анализируемая задача.
Рисунок 2.9 Подготовка рабочего листа
Для решения этой задачи используется функция ПС:
Функция ПС возвращает текущий объем вклада на основе постоянных периодических платежей. При выборе ставки в 0% сделка не будет выгодна. Поэтому, для нахождения минимальной процентной ставки, я воспользовалась функцией СТАВКА. Результаты вычислений приведены ниже, в соответствии с рисунком 2.10 и 2.11.
Рисунок 2.10 Использование функции ПС
Рисунок 2.11 Использование функции СТАВКА
Теперь необходимо использовать Диспетчер сценариев. Диспетчер сценариев вызывается с помощью команды Анализ «что если» на вкладке Данные.
В появившемся окне с помощью соответствующих кнопок можно добавить новый сценарий, изменить, удалить или вывести существующий, а также объединить несколько различных сценариев и получить итоговый отчет для существующих сценариев в соответствии с рисунком 2.12.
Рисунок 2.12 Окно Диспетчера сценариев
Процесс изменения процентной ставки представлен на рисунке 2.13.
Рисунок 2.13 Процент изменения ставки
Результаты расчетов, графики и диаграммы
В результате изменяется исходная таблица, представленная рисунком 2.14.
Рисунок 2.14 Измененная исходная таблица
Результатом работы Диспетчера сценариев является таблица «Структура сценария», которая отображает текущие (измененные) и исходные значения с соответствующими результатами. Столбец «Текущие значения» представляет значения изменяемых ячеек в момент создания Итогового отчета по Сценарию.
Рисунок 2.15 Структура сценария
3. Структуризация и первичная обработка в MS Excel
3.1 Создание списка в режиме «Форма»
Постановка задачи и ее математическая формулировка
Создать список Доставка товаров на дом с использованием Формы.
Описание входной и выходной информации
Для демонстрации возможностей работы со списками в электронной таблице MS Excel необходимо проанализировать предметную область, затем выделить примерный перечень информации, который будет содержать исходный список. Подготовить его и разместить на листе рабочей книги.
При составлении списка Доставка товаров на дом можно выделить следующую информацию:
Клиент;
Товар;
Стоимость;
Дата заказа;
Дата доставки;
Стоимость доставки;
Экранная форма данных создается с помощью команды на вкладке «Данные» Форма. Перед вызовом команды курсор устанавливается в любой ячейке списка, либо выделяется весь список. Форма представляет собой аналог карточки для одной записи в соответствии с рисунком 3.2. В ней перечислены все поля и представлена информация для одной записи. Отображён номер текущей записи и общее их количество. С помощью кнопок «Назад» и «Далее» можно перебирать карточки. Кнопка «Добавить» добавляет пустой бланк в базу данных. Кнопка «Удалить» удаляет текущую запись из базы данных. Отменить удаление, сделанное в режиме формы нельзя. Редактирование самих данных производится непосредственно в полях ввода. «Вернуть» – отказ от сделанных изменений. Кнопка «Критерий» – предназначена для задания критериев поиска записей с указанным содержимым полей. При этом по кнопкам «назад» и «далее» будет отображаться информация согласно выбранному критерию.
3.2 Сортировка списков в MS Excel
Постановка задачи и ее математическая формулировка
Отсортировать исходный список по 3 ключам сортировки (по выбору).
Описание входной и выходной информации
Сортировка списка предназначена для более наглядного представления данных и осуществляется при помощи команды Сортировка на вкладке Данные. При сортировке данных списка можно задавать до 3-х ключей сортировки.
При помощи кнопки «Параметры» можно задать особый порядок сортировки (по дням недели, например). Кроме того, кнопка позволяет учитывать регистр символов (большие буквы идут раньше маленьких), также задавать режим сортировки по строкам или столбцам.
Следует быть внимательными при сортировке списков, содержащих формулы. Если строка с формулой перемещается, относительные ссылки в формуле автоматически изменяются, что может привести к неверным результатам.
Необходимо следить за тем, чтобы часть строк – столбцов не была скрыта перед сортировкой. Это может привести к неупорядочиванию части записи, ибо скрытые строки – столбцы в сортировке не участвуют, за исключении скрытых путём структуризации.
Результаты расчетов, графики и диаграммы
Исходный список сортируется по фамилии клиента в алфавитном порядке, затем по заказам, а затем по стоимости товара.
3.3 Использование Автофильтра
Постановка задачи и ее математическая формулировка
Применить к списку Автофильтр. Отобразить только «Первые 10…» записей.
Описание входной и выходной информации
Фильтрация списков позволяет отобрать записи по определённому критерию. Для фильтрации данных можно использовать 2 вида фильтров: Автофильтр и Расширенный фильтр.
Автофильтр применяется по команде на вкладке Данные – Фильтр – Автофильтр. После выбора команды, ячейки имён полей превращаются в поля со списком в соответствии, из которых можно выбрать желаемый критерий отбора. Для отмены Автофильтра следует повторно выбрать эту команду.
Результаты расчетов, графики и диаграммы
Результатом работы Автофильтра является исходная таблица, содержащая 10 наибольших записей по полю «Стоимость».
3.4 Использование Расширенного фильтра в MS Excel
Постановка задачи и ее математическая формулировка
Применить к списку Расширенный фильтр. Диапазон условий задать самостоятельно в соответствии с предметной областью (не менее 3 условий).
Описание входной и выходной информации
Расширенный фильтр вызывается по команде на вкладке Данные – Фильтр – Расширенный фильтр. Перед его использованием необходимо подготовить список и создать диапазон критериев, где задаются условия отбора записей. Верхняя строка диапазона должна содержать заголовки полей, которые в точности совпадают с заголовками полей в области данных. Точность совпадения можно обеспечить путём копирования заголовков полей таблицы. В диапазон критериев можно заносить несколько условий, как в строке, так и в столбце.
После вызова Расширенного фильтра необходимо указать исходный диапазон и диапазон условий, а также выбрать место для результата.
Результаты расчетов, графики и диаграммы
Результатом работы Расширенного фильтра является исходная таблица, содержащая записей удовлетворяющие критериям отбора.
3.5 Вычисление промежуточных итогов в MS Excel
Постановка задачи и ее математическая формулировка
Вычислить промежуточные итоги, итоговые поля и операцию задать самостоятельно (предварительно выполнить сортировку по выбранному полю).
Описание входной и выходной информации
Назначение промежуточных итогов состоит в обобщении данных. Для подведения промежуточных итогов в соответствии с рисунком 3.12 необходимо:
1. Выделить произвольную ячейку списка, затем выбрать команду на вкладке Данные – Итоги.
2. Указать, как будет идти группировка данных, выбрав значение в списке «При каждом изменении в». Предварительно столбец, по которому будет идти группировка, должен быть отсортирован.
3. Из списка «Операция» выбирается функция, которая будет использоваться при обработке данных.
4. Выбираются данные, которые должны участвовать в расчётах, установив в списке «Добавить итоги по» флажки для требуемых столбцов данных.
3.6 Проведение сводного анализа в MS Excel
Постановка задачи и ее математическая формулировка
Проанализировать данные списка с использованием сводной таблицы.
Описание входной и выходной информации
Сводная таблица создаётся при помощи Мастера сводных таблиц (Данные – Сводная таблица) пошагово:
1. Указываем местонахождение исходных данных и тип создаваемого отчета (сводная таблица или сводная диаграмма).
2. Указываем диапазон, содержащий исходные данные.
3. При помощи кнопки «Макет» конструируем конфигурацию сводной таблицы.
4. Указывается место размещения сводной таблицы.
Результаты расчетов, графики и диаграммы
| 
