Лабораторная работа 9-10

Лабораторная работа 9-10. Статистические функции в Excel.

Первое знакомство с надстройкой «Пакет анализа»

Установка надстройки «Пакет анализа». При создании новой или открытии существующей книги MicrosoftExcel появится окно активного рабочего листа. Для того чтобы отыскать команду вызова надстройки Пакет анализа, необходимо воспользоваться меню

Сервис.

Здесь возможны три ситуации, в которых нужно действовать следующим образом:

1.В меню Сервис присутствует команда Анализ данных.... Это идеальный случай - достаточно щелкнуть указателем мыши по данной команде, чтобы попасть в окно надстройки.

2.В меню Сервис отсутствует команда Анализ данных....В этом случае необходимо в том же меню выполнить команду Надстройки.... Раскроется одноименное окно со списком доступных надстроек. В этом списке нужно найти элемент Пакет анализа, поставить рядом с ним «галку» и щелкнуть по кнопке ОК. После этого в меню Сервис появится команда Анализ данных....

Если у Вас MSExcel 2007, 2010, то для подключения Пакета анализа необходимо зайти в меню Файл команда Параметры. В появившемся меню в левой части выбрать Надстройки, а в правой Пакет анализа. Нажать кнопку Перейти. В открывшемся списке нужно найти элемент Пакет анализа, поставить рядом с ним «галку» и щелкнуть по кнопке ОК.

Технология работы в режиме «Анализ данных»

Выберем в меню Сервис пункт Анализ данных(дляMSExcel 2007, 2010командаАнализ

данных появится на панели Данные) появится окно с одноименным названием. Это окно – по существу «центр управления» надстройки Пакет анализа, главным элементом которого является область Инструменты анализа.В данной области представлен список реализованных в MicrosoftExcel методов статистической обработки данных.

Наряду с надстройкой Пакет анализа в практике статистической обработки могут широко применяться статистические функции MicrosoftExcel. В состав Excel входит библиотека, содержащая 78 статистических функций, ориентированных на решение самых различных задач прикладного статистического анализа.

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

Вставка, или щелчком по кнопке вызова мастера функций , или активизацией комбинации клавиш Shift+F3.

Задание 1. Общий объем розничного товарооборота по районам Ярославской области за 1998 г. приведен в таблице.

По набору данных необходимо построить гистограмму и кумуляту.

1.Введите данные.

2.Зайдите Сервис→Анализданных→Гистограмма. Введите данные как на рисунке. Частоты, рассчитанные в данном режиме и гистограмма, представлены ниже на рисунке.

Выборка

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

Выборка должна быть представительной (репрезентативной) чтобы по ней можно было судить о генеральной совокупности. Репрезентативность означает, что объекты выборки достаточно хорошо представляют генеральную совокупность.

В табличном процессоре MicrosoftExcel реализована собственно-случайная выборка. Собственно-случайная выборка состоит в том, что выборочная совокупность образуется в

результате случайного (непреднамеренного) отбора отдельных единиц из генеральной совокупности. Именно принцип случайности попадания любой единицы генеральной совокупности в выборку предупреждает возникновение систематических (тенденциозных) ошибок выборки.

Собственно-случайная выборка может быть осуществлена по схемам повторного и бесповторного отбора. Повторный отбор предполагает возможность включения в выборку одного и того же элемента генеральной совокупности два раза и более. Бесповторныйотбор исключает такую возможность. В MicrosoftExcel реализована схема повторного отбора.

Задание2. Втаблице приведена сравнительная динамика платных услуг населению Ярославской обл. в 1997 и 1998 гг (в сопоставимых ценах). На основе представленной информации необходимо построить графики динамики по квартальным данным.

1.Введите данные таблицы.

2.Посчитайте сумму за каждый квартал. Для этого воспользуйтесь функцией СУММ

3. Для построения графиков необходимо предварительно сформировать таблицу квартальных данных. Это делается в режиме работы «Выборка».

4. Используя Мастер диаграммпостройте графики по полученным данным.

Описательная статистика

Режим «Описательная статистика» служит для генерации одномерного статистического отчета по основным показателям положения, разброса и асимметрии выборочной совокупности.

Задание3. Стоимость набора из 25 продуктов питания по некоторым городам центрального региона России по состоянию на декабрь 1998 г. приведена в таблице.

Необходимо рассчитать основные показатели описательной статистики и сделать соответствующие выводы. Для решения задачи используем режим работы «Описательная статистика».

1.Введите данные таблицы.

2.Зайдите Сервис→Анализ данных→Описательная статистика. Введите данные как на рисунке.

На основании проведенного выборочного обследования и рассчитанных по данной выборке показателей описательной статистики с уровнем надежности 95% можно предположить, что средняя стоимость набора из 25 продуктов питания в целом по всем городам центрального региона России в декабре 1998 г. находилась в пределах от 382,11 до 449,95 руб.

Поясним, на основании каких показателей описательной статистики был сформулирован

соответствующий вывод. Такими показателями являются: средняя арифметическая выборки х (показатель Среднее) и предельная ошибка выборки х (показатель Уровень надежности

(95,0%).

3. Округлим полученные значения, используя функцию ОКРУГЛ.

4.Округлите все значения.

5.Из выражения для доверительного интервала х х х х х найдем левую и правые

границы.

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

Статистические функции, связанные с режимом «Описательная статистика»

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

невзвешенной (простой) и взвешенной

Задание 4. Рассмотрим, как рассчитывается средний курс продажи долларов США по итогам торгов на российских валютных биржах.

Ячейка D12 содержит формулу

=СУММПРОИЗВ(С5:С11;D5:D11)/CУMM(C5:C11), по которой рассчитывается средневзвешенный курс доллара США по проведенным торгам.

Функция МЕДИАНА

МЕДИАНА (число 1; число2;...) Рассчитывает медиану заданных аргументов.

Математико-статистическая интерпретация:

Медианой (Me) называется значение признака, приходящееся на середину ранжированной

(упорядоченной) совокупности.

Для ранжированного ряда с нечетным числом элементов медианой является варианта, расположенная в центре ряда.

Функция МЕДИАНА не требует предварительной ранжировки данных, она проводит ее автоматически.

Задание 5. Рассчитайте медиану для таблицы из предыдущего примера.

Главное свойство медианы заключается в том, что сумма абсолютных отклонений членов ряда от медианы есть величина наименьшая:

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

Статистические функции, связанные с режимом «Ранг и персентиль»

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

пользуются также понятиями ранга и процентранга.

Под рангом (К) понимают номер (порядковое место) значения случайной величины в наборе данных.

Под процентрангом(Т) понимают процентное отношение для каждого значения в наборе данных.

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

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

Задание 6.

Данные о количестве проданных спортивных костюмов «Reebok» фирмой «Чемпион» за 2000 год приведены в таблице.

1.Создайте таблицу.

2.Зайдите Сервис→Анализданных→Ранг и персентиль

3. У вас получится следующая таблица

Ковариация и корреляция

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

функциональной, либо стохастической (вероятностной). Для оценки тесноты и направления связи между изучаемыми переменными при их стохастической зависимости пользуются показателями ковариации и корреляции.

Ковариацией cov(x, у) случайных величин Х и Y называют среднее произведений отклонений каждой пары значений величин X иYв исследуемых массивах данных:

Ковариация есть характеристика системы случайных величин, описывающая помимо рассеивания величин Х и Y еще и линейную связь между ними.

Использование ковариации в качестве меры связи признаков не совсем удобно, так как показатель ковариации не нормирован и при переходе к другим единицам измерения (например, от метров к километрам) меняет значение. Поэтому в статистическом анализе показатель ковариации сам по себе используется редко; он фигурирует обычно как промежуточный

элемент расчета линейного коэффициента корреляции rXY :

пределах –1 < rXY шкалы Чеддока.

y ax b , то

Линейный коэффициент корреляции характеризует степень тесноты не всякой, а только линейной зависимости. При нелинейной зависимости между явлениями линейный коэффициент корреляции теряет смысл, и для измерения тесноты связи применяют так называемое корреляционное отношение, известное также под названием «индекс корреляции».

Если случайные величины Х и Y связаны точной линейной функциональной зависимостью rXY 1. В общем случае, когда величины Х и Y связаны произвольной

вероятностной зависимостью, линейный коэффициент корреляции принимает значение в <1, тогда качественная оценка тесноты связи может быть выявлена на основе

Режим работы «Ковариация» служит для расчета генеральной ковариации на основе

выборочных данных.

Режим работы «Корреляция» предназначен для расчета генерального и выборочного коэффициентов корреляции соответственно на основе генеральных и выборочных данных.

Задание7. Показатели уровня образования, уровня преступности, а также отношение числа безработных к числу вакансий в некоторых центральных областях России в 1995 г. (по данным Госкомстата РФ) приведены в таблице.

По выборочным данным, представленным в таблице, требуется установить наличие взаимосвязи между указанными показателями в центральном регионе России.

Зайдите Сервис→Анализ данных→Ковариация (дляMSExcel 2007, 2010панель Данные

команда Анализ данных ). Введите данные как на рисунке.

Зайдите Сервис→Анализ данных→Корреляция (дляMSExcel 2007, 2010панель Данные

команда Анализ данных ). Введите данные как на рисунке.