Объединение статистических данных и их анализ

Лекция 4 – Объединение статистических данных и их анализ Рассмотрим вариант группировки данных в Excel и формирование выводов на основе полученной группировки с помощью надстройки Пакета анализа. По умолчанию в Excel надстройка Пакет анализа отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office, щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК. Рисунок 1 – Подключение надстройки Пакет анализа В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Пакет анализа и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office). После успешной установки надстройки в меню Данные появится пункт: Анализ данных, а в окне мастера функций становится доступной категория функций - Статистические. Рисунок 2 – Доступ к подключенным функциям пакета анализа С помощью команд, доступных из окна Анализ данных можно провести: • описательный статистический анализ (описательная статистика); • ранжирование данных (ранг и персентиль); • графический анализ (гистограмма); • прогнозирование данных (скользящее среднее, экспоненциальное сглаживание); • регрессионный анализ (регрессия) и т.д. Гистограмма представляет собой столбиковую диаграмму частот. По горизонтальной оси диаграммы откладывают измеренные значения из набора данных, по вертикальной – частоту встречаемости этих значений. Высота каждого столбца показывает частоту (количество) значений из набора данных, принадлежащих соответствующему интервалу, равному ширине этого столбца. Необходимо построить и проанализировать гистограмму процентных ставок ссуд под залог недвижимости. Таблица 1 - Процентные ставки ссуд под залог недвижимости В таблице представлены размеры фиксированной процентной ставки ссуд под залог недвижимости, предоставляемых на 30 лет ипотечными компаниями. Чтобы построить гистограмму в Excel с помощью статистического пакета Анализ данных, выполните следующие действия. • На Листе1 в ячейках D2:D3 задайте метку (название) Процентная ставка, а в диапазон D4:D48 введите из Таблицы значения процентных ставок. Рисунок 3 – Ввод исходных данных • Выберите из меню команду: Сервис®Анализ данных®Гистограмма. Щелкните на кнопке OK. Рисунок 4 – Выбор пункта "Гистограмма" • В появившемся диалоговом окне в области Входные данные укажите Входной интервал:, выделяя диапазон данных D2:D48 вместе с меткой. Установите флажок Метки, чтобы указать, что метка включена в диапазон входного интервала. • В области Параметры вывода установите флажок Вывод графика и включите параметр Выходной интервал: для вывода гистограммы на том же листе, где располагаются входные данные. Для определения места вывода гистограммы сначала щелкните в текстовом поле параметра Выходной интервал:, а затем выделите ячейку F2, указывающую адрес левого верхнего угла поля вывода гистограммы. Щелкните на кнопке OK. Если не указывать параметр Интервал карманов:, определяющий ширину столбца, то программа Excel устанавливает равные интервалы автоматически. Количество интервалов приближенно равно квадратному корню из числа значений данных. Рисунок 5 – Задание параметров Excel разместит на листе таблицу распределения частот и гистограмму. Рисунок 6 – Полученная таблица распределения частот и гистограмма Таблица распределения частот включает исходные карманы. Значения карманов указывают правые границы интервалов. Например, карману со значением 6,5625 соответствует интервал (6,333333; 6,5625]. В данный интервал попали 4 значения процентных ставок. На гистограмме Excel размещает значения карманов по оси Х в середине интервалов, а не у отметок, разделяющих интервалы. Отформатируйте гистограмму средствами Excel: • Введите новое название диаграммы Процентные ставки ссуд под залог недвижимости. Уменьшите шрифт. • Введите новое название оси Y Частота (количество ипотечных компаний). Уменьшите шрифт. • Введите новое название оси Х Процентная ставка. Уменьшите шрифт. • Удалите легенду (Частота, справа от гистограммы). • Для уменьшения разрядности значений процентных ставок на гистограмме до одного знака после запятой выделите диапазон значений карманов F3:F8 и преобразуйте ее в числовой вид с двумя знаками после запятой. Рисунок 7 – Преобразование в числовой формат • Щелкните на значениях оси Y левой кнопкой мыши, а затем правой и в полученном списке выберете Формат оси (рис. 8), выберите в появившемся диалоговом окне пункт Параметры оси и установите в текстовом поле цена основных делений: значение 4, а в текстовом поле цена промежуточных делений: значение 1 (рис. 9). Рисунок 8 – Формат оси Рисунок 9 – Установка промежуточных делений • Выделите Область построения диаграммы и увеличьте ее высоту. Щелкните на кнопке Сохранить. Ниже показан вид полученной гистограммы.  Рисунок 10 – Отформатированный результат Проведем анализ полученной группировки в виде гистограммы: • Размах (диапазон) значений. Размах процентных ставок определяется по левой и правой границам гистограммы. Самая низкая процентная ставка равна приблизительно 5,9%, самая высокая процентная ставка – около 7,2%. Таким образом, размах процентной ставки составляет 1,3% (7,2% – 5,9% = 1,3%). • Типичные значения. Типичным значениям соответствует самый высокий столбец гистограммы. Наиболее часто встречаются ставки в интервале от 6,8% до 7,0% (26 из 45 организаций предлагают ставки в данном интервале). • Рассеяние. Рассеяние указывает разницу между типичным значением и остальными значениями, то есть характеризует, как отстоят другие столбцы относительно самого высокого столбца (типичного). Типичная разница ставок для различных организаций составляет приблизительно 0,5% (6,79%-6,33% = 0,46%), где 6,79% – начальное значение интервала типичного столбца, а 6,33% – начальное значение интервала среднего столбца в группе (таким столбцом является четвертый столбец слева, который формируется на основе данных по интервалу от 6,33% до 6,56%). Таким образом, умеренно высокие столбцы отстоят от типичного столбца приблизительно на 0,5%. • Общая конфигурация данных. Большинство организаций сконцентрировано правее середины диапазона процентных ставок (здесь самые высокие столбцы) и немного организаций предлагают либо очень низкие, либо очень высокие ставки (короткие столбцы слева и справа от типичного столбца). • Характерные особенности. На гистограмме пропущена область от 5,9% до 6,1%. По-видимому, ни одна компания не предлагает ставку в этом интервале. Кроме надстройки Пакет анализа, мощным средством анализа данных Excel является надстройка  Поиск решения. С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Можно установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями: • количество неизвестных (decision variable) – 200; • количество формульных ограничений (explicit constraint) на неизвестные – 100; • количество предельных условий (simple constraint) на неизвестные – 400. По умолчанию в Excel надстройка Поиск решения также отключена. Ее включение происходит аналогичным образом. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office, щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК. Рисунок 11 – Надстройка Поиск решений В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office). В ячейки матрицы стоимости перевозки заносятся затраты на перевозку груза от каждого поставщика к каждому потребителю. Таблица 2 – Матрица стоимости перевозки   Потребитель 1 Потребитель 2 Потребитель 3 Потребитель 4 Потребитель 5 Поставщик 1 40 55 52 66 66 Поставщик 2 70 55 52 25 40 Поставщик 3 55 40 40 52 52 Поставщик 4 55 25 52 52 52 Для того, чтобы запретить перевозку по какому либо каналу, вводится значительно большая (по сравнению с остальными) стоимость (например, 1000). Введем потребности потребителей и запасы поставщиков. Таблица 3 – Матрица потребностей потребителей и запасов поставщиков Потребности потребителей 100 200 50 250 150 Запасы поставщиков   Потреби тель 1 Потреби тель 2 Потреби тель 3 Потреби тель 4 Потреби тель 5 200 Поставщик 1 100 50 50 150 Поставщик 2 150 225 Поставщик 3 25 100 100 175 Поставщик 4 175 На основе этих данных рассчитаем суммарные перевозки каждого поставщика (СППС, сумма по соответствующей строке таблицы). Разницы между перевозками и фактическими запасами поставщиков должны быть равны нулю (РПС). Разница между рассчитанным суммарным количеством грузов (СППТ), получаемым каждым потребителем и фактическим получением грузов (строка Потребности потребителей) также равна нулю в каждом из случаев (РПТ). Таблица 4 – Расчет итогов по потребностям и запасам   РПТ СППТ 100 200 50 250 150 Потребности потребителей 100 200 50 250 150 РПС СППС Запасы поставщиков   Потребитель 1 Потребитель 2 Потребитель 3 Потребитель 4 Потребитель 5 200 200 Поставщик 1 100 50 50 150 150 Поставщик 2 150 225 225 Поставщик 3 25 100 100 175 175 Поставщик 4 175 Целевой функцией являются суммарные затраты на перевозки, которые определяются произведениями фактических перевозок на соответствующие затраты (с помощью функции СУММПРОИЗВ, параметрами которой являются два массива - матрица стоимости перевозки и матрица потребностей потребителей и запасов поставщиков). Таблица 5 – Расчет итогов по потребностям и запасам с целевой функцией Далее следует нажать поле Поиск решения согласно рис. 12. Рисунок 12 – Поиск надстройки Поиск решений После возникновения окна надстройки необходимо заполнить параметры согласно рисунку 13. Рисунок 13 – Заполнение полей поиска решений Целевой ячейкой является целевая функция, она минимизируется, изменяются ячейки матрицы потребностей потребителей и запасов поставщиков и накладывается ряд ограничений: • Элементы матрицы потребителей и запасов поставщиков не нулевые; • Ячейки РПС равны нулю (требование полного исчерпания запасов поставщиков); • Ячейки РПТ равны нулю (требование точного удовлетворения потребности потребителей). Отформатируем числовые значения в ячейках на чистовой тип без нулей (за исключением целевой функции) и решим данную задачу по следующим исходным данным, сохранив отчет по оптимизации: Рисунок 14 – Исходные данные для поиска решений Рисунок 15 – Результат оптимизации