Среднее: типическое значение для количественных данных

Задание 3.

Определить количество бракованных изделий в дневном выпуске.

Каждая партия изделий компании содержит 1000 изделий. В дневном выпуске произведено 253 партии. Для проведения контроля качества изделий была случайным образом взята выборка, включающая 10 партий. Число бракованных изделий в каждой партии составило: 3, 8, 2, 5, 0, 7, 14, 7, 4, 1. Определить, какое количество бракованных изделий можно ожидать в дневном выпуске, состоящем из 253 000 изделий.

1. Откройте файл Описательная статистика.xls.

2. На Листе2 в ячейке А1 задайте метку Брак, а в диапазон A2:A11 введите исходные данные.

3. В ячейке С1 задайте метку Среднее, а в ячейке С2 вычислите Среднее (уровень брака) для бракованных изделий из заданной выборки данных.

Замечание. При расчете среднего воспользуйтесь статистической функцией СРЗНАЧ.

4. В ячейке А13 задайте метку Количество бракованных изделий. В ячейке А14 вычислите ожидаемое количество бракованных изделий в дневном выпуске.

Задание 4.

Определить средний балл результатов обучения.

Предположим, что в университете каждой дисциплине, в зависимости от ее важности, присваивается определенное количество очков. Система оценок включает оценки от 1,0 (незачет) до 5,0 (отлично). Студент в конце семестра имеет результаты, указанные в таблице 2. Определить средний балл студента.

Таблица 2. Оценки студента за семестр

Дисциплина	Очки	Оценка
Статистика		4,7
Экономика		4,3
Маркетинг		4,5
Спецкурс		3,8

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

1. Откройте файл Описательная статистика.xls.

2. На Листе2 в ячейках F1 и G1 задайте метки Очки и Оценка соответственно. В диапазон F2:G5 введите данные Таблицы 2.

3. В ячейке F7 задайте метку Взвешенное среднее.

4. Чтобы найти взвешенное среднее, вначале дайте имена каждой колонке чисел. Для этого выделите обе колонки вместе с метками и выберите команду:

Вставка®Имя®Создать

В появившемся диалоговом окне включите опцию в строке выше и щелкните на кнопке ОК. Теперь выделите ячейку F8 и вычислите взвешенное среднее, используя из категории Полный алфавитный перечень функции Excel СУММПРОИЗВ и СУММ. Формула вычисления имеет следующий вид: =СУММПРОИЗВ(Очки;Оценка)/СУММ(Очки). Уменьшите разрядность результата до двух десятичных знаков после запятой. Вы получите средневзвешенное значение, равное 4,45.

5. Вычислите средний балл студента, просто усреднив оценки. Для этого в ячейке F10 задайте метку Среднее, а в ячейке F11 получите среднее (4,33), используя функцию СРЗНАЧ.

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

Задание 5.

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

В таблице 3 приведены данные о падении стоимости акций 29 промышленных компаний в первый день кризиса биржи в октябре 1987 года.

Таблица 3. Падение акций при открытии торгов

Фирма	Изменение стоимости, %	Фирма	Изменение стоимости, %	Фирма	Изменение стоимости, %
	-4,1		-0,9		-15,7
	-5,1		-3,5		-12,3
	-4,5		-10,5		-9,6
	-5,4		-7,2		-12,0
	-4,0		-8,9		-12,4
	-11,6		-6,8		-8,6
	-4,0		-2,1		-11,4
	-3,0		-17,2		-10,8
	-4,4		-15,7		-8,6
	-9,3		-8,9

9. В файле Описательная статистика.xls на Листе3 в ячейке A1 задайте метку Изменение стоимости. В диапазон A2:A30 введите данные Таблицы 3. Увеличьте ширину столбца, дважды щелкнув на правой границе заголовка столбца A.

10. Выделите диапазон A1:A30 и выполните упорядочивание данных с помощью команды:

Данные®Сортировка…

Параметры сортировки задайте, как указано ниже на рисунке.

Среднее: типическое значение для количественных данных - student2.ru

11. В ячейку B1 введите метку Ранг, а в ячейку B2 введите число 1. Выделите диапазон B2:B30 и выберите команду Правка®Заполнить®Прогрессия …В диалоговом окне установите опцию Шаг: равной 1 и щелкните на кнопке ОК.

12. Теперь определите значение медианы. Для этого сначала присвойте имя переменной n ячейке B30. В ячейке D1 задайте метку Ранг, а в ячейку D2 введите формулу =(n+1)/2. В ячейке D5 задайте метку Медиана.В ячейкуD6 скопируйте из столбца процентного изменения стоимости акций значение, соответствующее вычисленному рангу медианы.

13. Постройте гистограмму процентных изменений стоимости акций для диапазона данных A1:A30, включая метку. Интервал входных значений задайте явно равным 2. Нижнюю границу диапазона входных значений примите равной –20, а верхнюю границу равной 0. Значения карманов вместе с меткой Карман расположите в ячейках F1:F12. Выведите гистограмму вместе с таблицей распределения частот в область H1:Р20. Можно ли считать, что распределение данных близко к нормальному?

14. В ячейке D9 задайте метку Среднее, а в ячейке D10 рассчитайте среднее значение процентного изменения. Уменьшите разрядность полученного значения до одного знака после запятой. Существенно ли отличаются друг от друга значения среднего и медианы?

Задание 6.

Определить медиану и среднее для служащих сферы общественного питания.

В таблице 4 содержатся данные о количестве служащих в 10 фирмах общественного питания.

Таблица 4. Количество служащих в фирмах общественного питания

Фирма	Количество служащих	Фирма	Количество служащих

1. В файле Описательная статистика.xls добавьте Лист4.

2. На Листе4 в ячейке A1 задайте метку Количество служащих, а в диапазон A2:A11 введите данные из Таблицы 4.

3. Постройте гистограмму для диапазона данных A1:A11, включая метку. Интервал входных значений задайте явно равным 20000. Нижнюю границу диапазона входных значений примите равной 26000, а верхнюю границу равной 486000. Значения карманов вместе с меткой Карман расположите в ячейках D1:D25. Расположите таблицу распределения частот в диапазоне F1:G26, а гистограмму – в диапазоне I1:M20. Какой характер распределения имеют данные?

4. В ячейку J25 введите метку Медиана. В ячейке J26 вычислите медиану, используя статистическую функцию МЕДИАНА.

5. В ячейку L25 введите метку Среднее. В ячейке L26 вычислите среднее, используя статистическую функцию СРЗНАЧ. Объясните причину отличия значений медианы и среднего.

Задание 7.

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

В таблице 5 содержится список о размере выплат (заработная плата и премия) руководителям финансовых компаний (n=30).

Таблица 5. Выплаты руководителям финансовых компаний

Фирма	Зарплата и премия, дол.	Фирма	Зарплата и премия, дол.	Фирма	Зарплата и премия, дол.

1. В файле Описательная статистика.xls добавьте Лист5.

2. На Листе5 в ячейке A1 задайте метку Выплаты, а в диапазон A2:A31 введите данные из Таблицы 5.

3. Выберите команду Сервис®Анализ данных®Ранг и персентиль и задайте параметры как указано ниже на рисунке. Щелкните на кнопке ОК.

Среднее: типическое значение для количественных данных - student2.ru

В полученной таблице выделите столбец со значениями персентилей (диапазон F2:F31) и уменьшите разрядность до одного знака после запятой.

4. Объедините диапазон ячеек H1:N1 и введите метку Базовые показатели.

5. В ячейку H2 введите метку Наименьшее значение, а в ячейку H3 скопируйте значение из ячейки D31.

6. В ячейку K2 введите метку Наибольшее значение, а в ячейку K3 скопируйте значение из ячейки D2.

7. В ячейке N2 задайте метку Медиана. В ячейке N3, используя функцию СРЗНАЧ, вычислите медиану как среднее двух значений с рангами 15 и 16 (ячейки D16:D17).

8. В ячейках H6 и L6 задайте метки Нижний квартиль и Верхний квартиль соответственно.

9. В ячейку H7 скопируйте значение из ячейки D24, что соответствует нижнему 24,1-персентилю (это число самое близкое к 25%) с рангом 23.

10. В ячейку L7 скопируйте значение из ячейки D9, что соответствует верхнему 75,8-персентилю (это число самое близкое к 75%) с рангом 8.

11. Чтобы сделать выводы о симметричности распределения, надо сравнить три значения: медиану и квартили. Проведем визуальное сравнение. Для этого постройте для соответствующих значений (выделив ячейки N3, H7, L7) точечную диаграмму, проставьте значения точек и удалите все надписи. Вид диаграммы показан на рисунке.

Среднее: типическое значение для количественных данных - student2.ru

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

Контрольные вопросы

1. Перечислите обобщающие показатели, характеризующие типические значения набора данных.

2. Перечислите показатели, характеризующие разброс данных.

3. Перечислите и кратко опишите показатели, характеризующие степень симметричности данных.

4. Что такое среднее? Объясните среднее с точки зрения суммы всех значений набора данных.

5. Что такое взвешенное среднее? В каких случаях этот показатель используется вместо обычного среднего?

6. Что такое медиана? Как найти медиану для набора данных:

а) С четным количеством значений?

б) С нечетным количеством значений?

7. Как вычислить медиану для порядковых категорийных данных?

8. Что такое мода? В каком случае вычисляется модальный интервал?

9. Какой типический показатель (или показатели) можно использовать для:

а) Количественных данных?

б) Порядковых категорийных данных?

в) Категорийных данных, которые нельзя содержательно упорядочить?

10. Какие показатели лучше использовать:

а) При нормальном распределении данных?

б) При планировании общей суммы?

в) При ассиметричном распределении, когда общая сумма не важна?

11. Что такое персентиль? В каких единицах он выражается?

12. Что такое квартили?

13. Назовите пять базовых характеристик распределения.

Контрольные задания

Добавьте Лист6 и выполните задание с использованием базы данных служащих (файл База данных служащих.xls находится в папке Мои документы).

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

Данные®Фильтр®Автофильтр

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

После окончания анализа необходимо в файле База данных служащих.xls отменить действие фильтра. Для этого сначала выполните команду: Данные®Фильтр®Отобразить все, чтобы вывести все записи базы, а затем выключите автофильтр, повторно выполнив команду:

Данные®Фильтр®Автофильтр.

Вариант	Задание
	Для заработной платы служащих, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении заработной платы?
	Для возраста служащих, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении возраста?
	Для стажа работы служащих, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении стажа работы?
	Для заработной платы служащих, используя инструмент анализа Ранг и персентиль, найдите медиану, квартили и экстремумы. Определите 10-й и 90-й персентили. Чему равен персентиль служащего, имеющего в базе данных номер 6?
	Для возраста служащих, используя инструмент анализа Ранг и персентиль, найдите медиану, квартили и экстремумы. Определите 10-й и 90-й персентили. Чему равен персентиль служащего, имеющего в базе данных номер 6?
	Для стажа работы служащих, используя инструмент анализа Ранг и персентиль, найдите медиану, квартили и экстремумы. Определите 10-й и 90-й персентили. Чему равен персентиль служащего, имеющего в базе данных номер 6?
	Рассматривая пол служащих, проведите сортировку и вычислите ранги (см. Задание 5, пункты 1, 2, 3). Найдите моду. О чем она свидетельствует?
	Рассматривая уровень подготовки служащих, проведите сортировку и вычислите ранги (см. Задание 5, пункты 1, 2, 3). Найдите моду. О чем она свидетельствует?
	Для заработной платы мужчин, используя инструмент анализа Описательная статистика, определите среднее и медиану. Постройте гистограмму и определите приблизительное значение моды. Сравните эти три показателя. Что вы можете сказать о типическом значении заработной платы среди мужчин?
	Для заработной платы женщин, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении заработной платы женщин?
	Для заработной платы служащих с уровнем подготовки А, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении заработной платы?
	Для заработной платы служащих со стажем от 5 и более лет, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении заработной платы?
	Для заработной платы служащих с возрастом от 40 и более лет, используя инструмент анализа Описательная статистика, определите среднее, медиану и моду. Постройте гистограмму и покажите эти показатели на гистограмме. Сравните показатели. Что вы можете сказать о типическом значении заработной платы?
	Для заработной платы мужчин, используя инструмент анализа Ранг и персентиль, найдите медиану, квартили и экстремумы. Определите 10-й и 90-й персентили. Найдите в базе данных номер мужчины, получающего самую высокую зарплату, и прочтите его данные: возраст, стаж, уровень подготовки.