I. Решение типовых задач

Расчет средних величин и показателей вариации в MS EXCEL

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

$ Файл средние и вариации.xls

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ:

FСоздать на компьютере, за которым работаете, Личную папку (ФИ_дата выполнения работы), в которую будете сохранять все результаты работы на практическом занятии.

F Скопировать файл с сетевого ресурса из папки Статистика/…/ 2/ средние и вариации.xls в Личную папку.

ПРИМЕР 1. Имеются данные о дневной выработке рабочих экспериментального цеха предприятия (Таблица 1):

Таблица 1

Группы рабочих по величине дневной выработки, кг	Число рабочих
до 100
100-120
120-140
140-160
160 и более
итого

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

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

В ячейке А3 используется комбинация следующих функций =ЗНАЧЕН(ЛЕВСИМВ(C4;3)), соответственно в ячейке В3: =ЗНАЧЕН(ПРАВСИМВ(C4;3)). Эти формулы скопировать в диапазон А4:А6, В4:В6. Эти формулы заложены в соответствующие ячейки.

Выполнить самостоятельно:

1. В следующие ячейки ввести формулы:

В ячейку D7 вычислить число всех рабочих.

В Е3: =(A3+B3)/2. Скопировать эту формулы в Е4:Е5.

В Е2: =E3-(B3-A3).

В Е6: =E5+(B5-A5).

В F2: =E2*D2. Скопировать формулу в F3: F6.

В F7 вычислить сумму всех значений диапазона F2: F6.

Теперь можно рассчитать среднее в ячейке D9: записать формулу соотношения , определив, в какой ячейке содержится значение числителя, в какой ячейке находится значение знаменателя.

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

3. Чтобы рассчитать медиану, необходимо рассчитать накопленные частоты. Для этого:

В ячейку J1 назвать «Накопленные частоты».

Расчет проводить следующим образом:

В J2: =D2 (ссылка на значение частоты первой варианты);

В J3: = J2+ D3. Эту формулу скопировать по этому столбу в нужный диапазон.

В J5:=D6/2. Это – половина всех значений совокупности.

Просматривая накопленные частоты, определите, в какой интервал войдет значение, рассчитанное в ячейке J5. Это и будет медианный интервал. Для расчета значения медианы воспользуйтесь формулой, которая приведена выше.

В ячейку D11 введите формулу для расчета медианы.

4. Рассчитаем дисперсию, для этого в G3 ввести формулу: =E3-$D$9, скопировать в диапазон G4: G6. В Н3: =G3^2, скопировать формулу в диапазон Н4:Н6. В I3: =H3*D3, формулу скопировать в диапазон I4:I6. В I7 вычислить сумму значений I2:I6.

В ячейку D12 записать формулу расчета дисперсии

, указав имена ячеек, в которых рассчитан числитель и знаменатель.

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

В ячейку D14 вычислить размах вариации R=x _max- x _min.

В ячейку D15 вычислить коэффициент вариации .

F Сохранить результаты выполненного задания в Личной папке под именем средние и вариации.xls.

Пример2. Вычислить среднюю урожайность по трем фермерским хозяйствам (таблицу 2 и таблицу 3 можно скопировать в Excel). Выполнить необходимые вычисления. Какие виды средних при этом были использованы? (Ответ на вопрос подписать под каждой таблицей.)

Таблица 2

номер хохяйства	урожайность зерновых, ц/га	валовый сбор зерновых, ц

Таблица 3

номер хохяйства	урожайность зерновых, ц/га	посевная площадь, га

II. ЗАВЕРШЕНИЕ РАБОТЫ: Скопируйте Личную папку с результатами в папку своей группы, расположенной на сетевом ресурсе. Завершите корректно работу за компьютером.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ:

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

Группы предприятий по уровню механизации труда, %

уровню механизации труда, %	число предприятий
70-73
73-76
76-79
79-82
82-85

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

Распределение проданной обуви по размерам:

Размер обуви	Число пар, в % к итогу
45 и выше

Распределение проданных костюмов по размерам:

Размер	Число проданных костюмов

Задания по расчету средних величин выполнить в Excel.

Дата добавления: 2015-07-13