2015-04-17
606
Самостоятельная работа выполняется по индивидуальным вариантам, полученным от преподавателя.
Контрольные вопросы.
1. Как осуществляется сортировка данных?
2. Как действует автофильтр?
3. Для чего применяется расширенный фильтр?
4. Можно ли использовать формулы в критериях поиска?
5. Влияет ли применение фильтра на печать?
6. Как воспользоваться командой “Итоги”?
7. Как создать сводную таблицу?
8. В чем отличие данных, предоставляемых командой “Итоги”, от данных, полученных при создании сводной таблицы?
9. Для чего используется консолидация данных?
10. Что означает закрепление областей?
Лабораторная работа № 11**.
Статистическая обработка данных с помощью Excel
Цель работы: уметь проводить прогноз поведения различных параметров и характеристик по текущей динамике их развития.
Перед выполнением лабораторной работы необходимо изучить следующие разделы:
- статистические характеристики;
- средние значение, дисперсия и среднее квадратичное отклонение случайной величины;
- основные статистические инструменты анализа, применяемые в Excel для описания случайных величин;
- линия тренда.
Задание А. Изучить с помощью встроенной Справки Excel функции СРЗНАЧ, КВАДРОТКЛ, ДИСП, КВАРТИЛЬ, КОРРЕЛ, МЕДИАНА, МОДА, СКОС. Создать таблицу с входными данными для случайной величины X. Получить информацию об основных тенденциях и изменчивости данных. Для этого вычислить среднее значение, среднеквадратичное отклонение, дисперсию, медиану, скос и моду для каждого ряда данных. Выполнить прогноз изменения данных на следующий временной период. Представить исходные данные в виде гистограммы. Показать направление изменения данных на гистограмме с помощью линейного тренда. Использовать в качестве Х данные о продажах акций различных эмитентов в РТС (из заданий А лабораторных работ №№ 3,4).
УКАЗАНИЯ
Список одномерных статистических характеристик можно создать с помощью инструмента анализа “Описательная статистика”. Для выполнения прогноза изменения данных на следующий временной период следует воспользоваться функцией ТЕНДЕНЦИЯ. Эта функция аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений данных. Чтобы показать тенденцию или направление изменения данных на диаграмме, к ряду данных добавляют направленную линию – линию тренда. Линиями трендов можно дополнить ряды данных, представленные в виде диаграмм с областями, линейчатых диаграмм, гистограмм и точечных диаграмм. При построении линии тренда следует выделить ряд данных, с которым ее нужно связать, а затем воспользоваться командой “Линиятренда” из меню “Вставка”.
Задание В. Используя генератор случайных чисел (функцию СЛЧИС) заполните две таблицы входных данных случайными числами, характеризующими дискретные случайные величины X и Y. Считая, что динамические изменения величин X и Y вызваны различными объективными факторами сезонного характера, найти индексы сезонности (см. Указания) для каждой из величин по всем периодам. Сделать графическую иллюстрацию сезонных колебаний, представив индексы сезонности в виде лепестковой диаграммы. Определить периоды наименьшего и наибольшего сезонных колебаний. Провести анализ структуры величин и их распределения по объему. Проверить исследуемые величины на однородность. Проверить, имеется ли связь между исследуемыми величинами X и Y. Результаты анализа привести в выходной таблице. Сделать вывод о степени однородности исследуемой совокупности и о наличии связи между величинами.
Считать, что
Х – среднемесячные цены на энергоносители (газ, нефть);
Y – среднемесячная цена на автомобиль ВАЗ 2106.
Таблица входных данных
| Месяцы | В среднем | Индекс сезонности | |||||
| Январь | |||||||
| Февраль | |||||||
| ......... | |||||||
| Декабрь | |||||||
| Итого | |||||||
| В среднем |
ВЫХОДНАЯ ТАБЛИЦА
| Первый квартиль интервала X | |
| Медиана интервала X | |
| Третий квартиль интервала X | |
| Первый квартиль интервала Y | |
| Медиана интервала Y | |
| Третий квартиль интервала Y | |
| Коэффициент вариации величины X, Vx | |
| Коэффициент вариации величины Y, Vy | |
| Коэффициент корреляции между X и Y, Rxy |
УКАЗАНИЯ
Средние величины используются при решении довольно широкого спектра задач экономического анализа. Рассмотрим применение аппарата средних для определения уровня сезонности явлений (так называемой “сезонной волны”). Под сезонностью понимают изменения показателей величин, вызванные различными объективными факторами сезонного характера (например, такими факторами могут выступать смена времен года или изменения природно-климатических условий). В качестве показателей сезонности обычно применяют индексы сезонности. Наиболее часто для определения индексов сезонности применяют метод простой средней. В этом случае индекс сезонности 
вычисляют по следующей формуле:
,
где 
– среднее определенного периода времени (месяц, квартал), взятое в течение t лет; n – число анализируемых периодов; 
– общее среднее, взятое за общее число периодов времени N=tn.
Для того чтобы изучить структуру входных данных, определите первый квартиль, медиану и третий квартиль величин X и Y, используя такие встроенные функции Excel, как КВАРТИЛЬ и МЕДИАНА, и поместите полученные значения в соответствующие ячейки таблицы выходных данных.
Для определения степени однородности случайной величины следует вычислить ее коэффициент вариации. Коэффициент вариации Vx используется для установления степени однородности величины X и определяется по формуле: Vx = sx / <x>. Если величина Vx < 0,33, то совокупность значений случайной величины X можно считать достаточно однородной, в противном случае – неоднородной, состоящей из различных по своему содержанию совокупностей.
Для исследования тесноты связи между случайными величинами необходимо определить меру тесноты связи, именуемую коэффициентом корреляции r. Величину r можно вычислить с помощью встроенной функции Excel КОРРЕЛ. После вычисления коэффициента корреляции r необходимо оценить его значение. Принято, что между величинами имеется некоторая корреляционная зависимость при коэффициенте корреляции, большем по модулю 0,1. При | r |>0,3 корреляционная связь признается существенной, при | r |>0,5 – значительной, при | r |>0,7 – тесной. Если величина коэффициента корреляции близка к 1, то можно считать, что между случайными величинами имеется прямая причинно-следственная связь, если коэффициент корреляции близок к –1, то это свидетельствует об обратной зависимости исследуемых величин, если же коэффициент корреляции близок к 0, то можно считать, что связь между величинами отсутствует.
Контрольные вопросы
1. Что называется случайной величиной?
2. Приведите пример случайной величины.
3. Перечислите наиболее часто используемые статистические характеристики.
4. Что называют средним значением случайной величины?
5. Что называют дисперсией и средним квадратичным отклонением случайной величины?
6. Что такое мода и скос случайной величины?
7. Почему недостаточно использовать одну статистическую характеристику случайной величины? Приведите пример.
8. Какая статистическая характеристика характеризует тесноту связи между двумя случайными величинами?
9. Перечислите основные статистические инструменты анализа, применяемые в Excel для описания случайных величин.
10. Как в Excel осуществляется прогноз изменения данных на следующий временной период?
11. Как добавить к диаграмме линию тренда?
12. Как в Excel можно получить набор случайных величин?
