Способы группировки данных в электронной таблице Excel

Лабораторные работы

Содержание семинарских занятий и лабораторных работ по курсу

Тема занятия	Количество часов
Лабораторная работа 1.Доступные и недоступные источники статистической информации. Особенности организации статистического наблюдения.
Лабораторная работа 2. Способы группировки данных в электронной таблице Excel.
Лабораторная работа 3. Вычисление абсолютных и относительных величин в электронной таблице Excel.
Лабораторная работа 4. Вычисление средних величин и показателей вариации в электронной таблице Excel.
Лабораторная работа 5. Исследование больших выборок. Кластерный анализ в электронной таблице Excel и пакете Statistica.
Лабораторная работа 6. Исследование малых выборок. Однофакторный и многофакторный анализ в электронной таблице Excel и пакете Statistica.
Лабораторная работа 7.Описательная статистика малых и больших выборок. Исследование выборок средствами факторного анализа.
Лабораторная работа 8. Вычисление агрегатных индексов (общих и индивидуальных) в электронной таблице Excel.
Лабораторная работа 9.Вычисление средних индексов (общих и индивидуальных) в электронной таблице Excel.
Лабораторная работа 10. Эффективность использования в России системы национальных счетов.
Лабораторная работа 11.Статистическое исследование показателей численности населения, занятости и безработицы в России и г. Н. Тагил на основе электронных таблиц Excel и пакета Statistica.
Лабораторная работа 12. Статистическое исследование макроэкономических показателей национального богатства на основе электронных таблиц Excel и пакета Statistica.
Лабораторная работа 13. Статистическое исследование производства, производительности и обращения продукции и услуг на основе электронных таблиц Excel и пакета Statistica.
Лабораторная работа 14. Статистическое исследование численности работников и использования рабочего времени на основе электронных таблиц Excel и пакета Statistica.
Лабораторная работа 15. Статистическое исследование финансов. Финансовые вычисления на основе электронных таблиц Excel.
Всего

 

Лабораторная работа №1-2

Вопросы для отчета по лабораторной работе

1. Перечислите основные источники статистической информации.

2. Укажите особенности организации статистического наблюдения.

3. Укажите этапы проведения сбора статистической информации.

4. Что такое группировка?

5. Опишите основные виды группировки.

6. Опишите подробнее основные виды аналитической группировки (деление на интервалы).

7. Что такое статистическая сводка.

Доступные и недоступные источники статистической информации. Особенности организации статистического наблюдения.

Способы группировки данных в электронной таблице Excel.

Одним из основных наиболее распространенных методов об­работки и анализа первичной статистической информации явля­ется группировка.

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

§ разделение всей совокупности на качественно однородные группы - выделение социально-экономических типов. Эти груп­пировки называются типологическими (например, группировки хозяйственных объектов по формам собственности, населения по общественным группам и др.);

§ характеристика структуры явления и структурных сдвигов. Эти группировки называются структурными (например, опреде­ление значения каждого вида транспорта в транспортном балансе страны, изучение состава населения по полу, возрасту и другим признакам и т. д.);

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

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

Признак, на основе которого производится подразделение единиц наблюдения на группы, называется группировочным при­знаком или основанием группировки. Группировка может выпол­няться по одному признаку (простая группировка) и по несколь­ким признакам {комбинированная группировка).

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

При группировке по атрибутивному признаку число групп опре­деляется количеством соответствующих наименований, если чис­ло этих наименований не очень велико. Если признак имеет боль­шое количество разновидностей, то при группировке ряд наиме­нований объединяют в одну группу. Для обоснованного объедине­ния их в группы разрабатываются классификации. В отличие от группировок при классификации группировочные признаки уста­новлены заранее на длительный период для решения многих за­дач, в то время как группировки выполняются для целей конкрет­ного исследования. Примерами могут служить классификации от­раслей экономики, автотранспортных предприятий по целевому назначению (грузовые, автобусные, таксомоторные и др.).

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

При непрерывном изменении признак принимает любые зна­чения (например, стаж работы или возраст рабочих), поэтому группы ограничиваются значениями признака в интервале «от -до». Интервалом называется разница между максимальным и ми­нимальным значениями признака в каждой группе. На практике используются три вида интервалов: равные, неравные (постепен­но увеличивающиеся) и специализированные.

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

Величина равного интервала исчисляется по формуле:

где х_max, х_min - соответственно наибольшее и наименьшее значения при­знака в изучаемой совокупности; т - принятое число групп.

Для расчета величины интервала по этой формуле необходи­мо заранее установить число групп (при числе наблюдений более 200 используют 10-15 групп).

Возможен и другой способ определения величины интервала, не требующий предварительного установления числа групп. В этом случае используется формула Стерджесса:

где п — число наблюдений.

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

Неравные интервалы (постепенно увеличивающиеся) часто применяются в аналитических группировках. В этом случае ин­тервалы выбираются так, чтобы число единиц в образованных группах было достаточно велико (т. е. чтобы группы были при­близительно одинаково заполнены).

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

Интервалы группировки могут быть закрытыми и открытыми.

Иногда имеющуюся группировку необходимо несколько из­менить: объединить ранее выделенные относительно мелкие группы в небольшое число более крупных, типичных групп или изменить границы прежних групп, с тем чтобы сделать группи­ровку сопоставимой с другими. Такая переработка результатов первичной группировки называется перегруппировкой или вто­ричной группировкой.

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

Результаты группировки и сводки материалов оформляются в виде статистических таблиц.

В статистической таблице выделяются два элемента:

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

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

Над таблицей помещается заголовок, отражающий в сжатой форме ее основное содержание, время и место, к которым отно­сятся изложенные в таблице данные.

Задание 1. Объем инвестиций в основной капитал характеризуется в России следующими данными (в фактически действовавших це­нах, млрд руб.): 1998 г.– 402,4; 1999 г.– 565,6; в том числе в отрасли: а) производящие товары - 1998 г. - 163,8; 1999 г. - 269,4; б) оказывающие рыночные и нерыночные услуги - 1998 г. -238,6; 1999 г.-296,2.

Представить приведенные данные в виде статистической таб­лицы. Сформулировать выводы, охарактеризовав произошедшие изменения в объеме и составе инвестиций (построить графическую зависимость).

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

Обратите внимание, что при решении задачи удобнее использовать при необходимости абсолютность и относительность адресов ячеек. В столбцах, где измерения проходят в % не забудьте установить соответствующий формат (Формат/ Ячейки…) с необходимым количеством знаков после запятой.

Задание 2. Имеются следующие данные по заработной плате водителей за сентябрь:

Таблица 1.

Табельный номер водителя	Класс водителя	Процент выполнения сменных заданий	Заработная плата за месяц, руб.
		110,2	4103,3
		102,0	3600,8
		111,0	3970, 7
		107,9	4050,2
		106,4	3740,5
		109,0	3985,4
		115,0	4300,8
		112,2	4015,7
		105,0	3790,2
		107,4	3700,7
		112,5	4280,2
		108,6	4170,1

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

Решение: Наберем данные в электронных таблицах, а затем произведем группировку по классу водителя методом сортировки. Для этого выделите всю таблицу, зайдите в меню Данные/ Сортировка и обозначьте, что параметром сортировки будет класс водителя и процент выполнения задания (см. рис. 2). Не забудьте убедиться, что после сортировки данные переместились вместе с табельным номером.

Выделим интервалы для группировки по % выполнения сменных заданий для 1 класса водителей. Для этого по формуле Стерджесса рассчитаем шаг интервала, а затем рассчитаем границы каждого из интервалов (см. рис. 3).

При распределении по интервалам будем использовать фильтрацию (Данные/Фильтр/Автофильтр). Первым условием ставится класс водителя и без его отмены делаются все последующие отборы. Табельные номера отобранных водителей будем выписывать в ручную (или можно сохранять информацию путем копирования) (см. рис. 4.).

Построим график зависимости заработной платы от процента выполненных заданий. В целом сводная таблица примет следующий вид (см. рис. 6). В заключении построим тренд, определяющий характер зависимости. Для этого щелкните правой кнопкой мыши по построенному графику и нажмите Добавить линию … тренда. Среди имеющегося набора трендов выберите наиболее подходящий (см. рис. 5). Построенный тренд говорит о прямопропорциональной зависимости между заработной платой и процентом выполненного водителями задания.

Аналогично сделайте расчеты для водителей второго класса.

Задания для самостоятельной работы.

1. По данным первой таблице произведите аналитическую группировку по заработным платам водителей.

2. Объем инвестиций в основной капитал характеризуется в России следующими данными: 1998г. – 402,4; 1999 г. – 565,6; в отрасли:

А) производящие товары – 1998 г. – 163,8; 1999г. – 269, 4; б) оказывающие рыночные и нерыночные услуги – 1998 г. – 238,2; 1999 г. – 296,2.

Представить приведенные данные в статистические таблицы. Сделать выводы об изменениях, произошедшие в объеме и составе инвестиций (вычислить процентное изменение).

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

 

Лабораторная работа 3. Вычисление абсолютных и относительных величин в электронной таблице Excel.

4.

 

Таблица 2

Табельный №	% выполнения норм выработки	Заработная плата за месяц, руб.
	110,8
	102,0
	111,0
	107,8
	106,4
	109,0
	100,0
	105,0
	102,3
	107,3
	108,6