Анализ данных средствами Microsoft Excel

Решение.

В таблице 9 представлен результат решения задачи. В ячейке А12 использована следующая формула: =КОРРЕЛ(C4:K4;C5:K5). В ячейках А13, А14 аналогичная формула.

Таблица 9 - Использование функций КОРРЕЛ

С помощью функции КОРРЕЛ я нашла коэффициенты корреляции выручки магазинов №1, №2, №3, сравнивая их попарно за весь год. При сравнивании магазинов №2 и №3 (ячейка А13)  и магазинов №1 и №3 (ячейка А14) видно что значения коэффициентов положительные, значит между ними прямая взаимосвязь. А при сравнении магазинов №1 и №2 (ячейка А12), коэффициент отрицательный, и значит между ними обратная взаимосвязь.

Задание 11

Excel имеет специальный аппарат для графического анализа моделей, в том числе построения по заданному в виде таблицы временному ряду {ti , yi} аппроксимационных зависимостей (линий тренда) P(t), которые приближенно отражают функциональную связь y=f(t).

Линии тренда обычно используются в задачах прогнозирования. Такие задачи решают с помощью методов регрессионного анализа. С помощью регрессионного анализа можно показать тенденцию изменения рядов данных, экстраполировать их (то есть продолжить линию тренда вперед или назад за пределы известных данных).

Линиями тренда можно дополнить ряды данных, представленные на линейчатых диаграммах, гистограммах, графиках, биржевых, точечных и пузырьковых диаграммах.

Excel позволяет выбрать один из пяти типов линии тренда P(t) – линейный, логарифмический, экспоненциальный, степенной или полиномиальный (2...6 степени) и проверить (по различным критериям), какой из типов лучше всего подходит в данной ситуации.

Критерием может служить критерий R2 (коэффициент детерминации, или достоверность аппроксимации), автоматическое вычисление которого встроено в диалоговое окно Линия тренда,

Чем ближе коэффициент детерминации к единице, тем лучше тренд.

Используя статистические данные о работе торговых организаций (таблица 1), построить график функции одного переменного Прибыль(месяц). Для построения использовать диаграмму – График. Выделив линию графика, построить различные линии тренда, выражающие зависимость прибыли от времени (наведя курсор на линию графика, щелкнув правой клавишей мыши; в появившемся контекстно-зависимом меню выбрав Добавить линию тренда).

Таблица 10 – Прибыль торговых организаций

Проверить линейную, полиномиальную (n=2), экспоненциальную, степенную линии: Тип | Построение линии тренда (рис. 4).

Рисунок 5 – Диалоговое окно Линия тренда\Тип

Для каждого тренда:

а) выдать аналитическую зависимость Прибыль(месяц): Показывать уравнение на диаграмме (рис. 4);

б) подсчитать по этим зависимостям соответствующую теоретическую (трендовую) численность, заполнив столбцы Теория; 

в) найти погрешность С–Т (разницу между статистической и трендовой численностью);

г) рассчитать квадратичные отклонения Si (i=1…4), используя функцию СУММКВ.

Сравнив эти отклонения, выбрать лучший тренд и по нему оценить прибыль организации за любой будущий месяц, не указанный в исходных данных.

д) спрогнозировать значение прибыли торговой организации на один из будущих месяцев.

Решение.

В таблице 11 представлен результат решения задачи. В ячейке С35 формула =-14,73*A35+1597, в ячейках С36, С37, С38, С39, С40, С41, С42, С43 использована аналогичная формула. В ячейке E35 формула =-18,93*A35^2+174,6*A35+1250, в ячейках E36, E37, E38, E39, E40, E41, E42, E43, E45 использована аналогичная формула. В ячейке G35 формула =1586*EXP(-0,01*A35), в ячейках G36, G37, G38, G39, G40, G41, G42, G43 использована аналогичная формула. В ячейке I35 формула =1533*A35^-0,01, в ячейках I36, I37, I38, I39, I40, I41, I42, I43 использована аналогичная формула. В ячейке D35 формула =B35-C35, в ячейках D36, D37, D38, D39, D40, D41, D42, D43, F36, F37, F38, F39, F40, F41, F42, F43, H36, H37, H38, H39, H40, H41, H42, H43,  J36, J37, J38, J39, J40, J41, J42, J43 использована аналогичная формула.

В ячейке D44 формула =СУММКВ(D35:D43), в ячейках F44, H44, J44 аналогичная формула.

Таблица 11 – Прибыль торговой организации №1

Рисунок 6 – График прибыли организации №1

При сравнении отклонений (таблица 11) видно, что лучшим трендом является полиномиальный, т.к. у него самое меньшее отклонение. По нему оцениваем прибыль организации за 10 месяц. Следовательно прибыль магазина №1 на октябрь составит 1103 тыс. руб.

   Заключение

В ходе выполнения работы,  я провела статистический анализ данных, используя функции Excel и пакет сервисных программ Анализ данных.

В первом задании была найдена суммарная выручка каждой организации с помощью функции СУММ.

Во втором задании я нашла сколько организаций перевыполнили план за год с помощью функции СЧЕТЕСЛИ.

В третьем задании с помощью функции СРЗНАЧ, я нашла среднюю ежемесячную выручку всех торговых организаций и среднюю выручку каждой за год.

В четвертом задании с помощью Мастера диаграмм, построила диаграммы месячной выручки.

В пятом задании было посчитано место каждой торговой организации по объему продаж за год с помощью функции РАНГ.

В шестом задании с помощью функции ПРОЦЕНТРАНГ, было оценено для каждой торговой организации, какова доля значений месячных выручек, не превосходящих 1500 рублей.

В седьмом задании найдены медианы и первые квартили массивов месячных выручек каждого магазина.

В восьмом  задании была использована функции ЧАСТОТА. В этом задании годовые выручки магазинов были разбиты по интервалам.

В девятом задании были вычислены те же частоты, но с помощью пакета сервисных программ Анализ данных.

В десятом задании с помощью функции КОРРЕЛ найдены коэффициенты корреляции выручки трех организаций за весь год.

В одиннадцатом задании был построен график функции одного переменного, а также были построены различные линии тренда, выражающие зависимость прибыли от времени. А также проведен анализ между линиями тренда и выбран лучший тренд. И по нему спрогнозирована будующая прибыль организации.

Библиографический список

1 Балдин, К. В.   Информационные     системы   в   экономике [Текст] / К. В. Балдин,

В. Б.Уткин. – М. : Издательско – торговая корпорация «Дашков и К0» , 2005. – 395 с.

2 Барановская, Т. П. Информационные системы и технологии в экономике [Текст]: учебник для вузов / Т. П. Барановская [и др.]. - М. : Финансы и статистика, 2005.- 414с.

3 Информатика [Текст]: учебник / под ред. Н. В. Макаровой. - М. : Финансы и статистика, 2004.-768 с.

4 Советов, Б. Я. Информационные технологии [Текст]: учебник для вузов / Б. Я. Советов, В. В. Цехановский. – М. : Высшая школа, 2005. -264с.

5              Шафрин, Ю. А. Информационные технологии [Текст]: в 2 ч. Ч. 2. Офисная технология и информационные системы / Ю. А. Шафрин.- М. : Лаборатория Базовых Знаний, 2000.- 336 с.

6              Середа, О. В. Превращаем текст в число [Текст] / О. В. Середа // CHIP. – 2006. - №4. – с.85

7              Информатика и информационные технологии [Электронный ресурс] / Центр информационных технологий. – М. : «Дашков и Ко», 2008.- Режим доступа : http: // www.tening.ru.