Решение статистических задач в EXCEL

Р ассмотрим методику расчета на примере расчета дисперсии и СКО размера семьи группы

1. Исходные значения признака хi и частоту fi надо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (АТ46:AY46) и (АТ47:AY47).

Количественные данные следует определить как «Числовые».

1. Рассчитать среднее арифметическое взвешенное - в примере в ячейке AW49 установлена формула =СУММПРОИЗВ (AT46:AY46;AT47:AY47)/СУММ(AT47:AY47)

2. Рассчитать квадраты отклонения индивидуальных значений от средних (xi- )2.

Для этого в ячейку первого значения устанавливается формула:

«=(AT46-$AW$49)^2»,

где вычисляется разность значения x_i (в ячейке AT46) исреднего (в ячейке$AW$49), возведенная в степень 2 оператором «^».

Координата средней задана как неизменяемая (абсолютная).

1. Формула квадрата разности копируется в соседние ячейки строки (или столбца).

2. В ячейку результата дисперсии (например «AW50») установить функцию
«=СУММПРОИЗВ(AT48:AY48;AT47:AY47)/СУММ(AT47:AY47)»

где «СУММПРОИЗВ (AT48:AY48;AT47:AY47)» реализует сумму попарных произведений (xi- )2 (в ячейках «AT48:AY48») на fi (в ячейках «AT47:AY47»), т.е. числитель

;

СУММ(AT47:AY47) - сумма частот f _i (в ячейках «AT47:AY47»);

«/» - оператор деления.

1. В ячейку результата СКО (например «AW51») установить функцию «=(AW50)^0,5»,

где вычисляется корень квадратный «^0.5» дисперсии в ячейке «AW50».

Показатели динамики.

Коэффициент роста К _р, темп роста Т _р, темп прироста, абсолютный прирост Δ, абсолютное значение 1% прироста А цепные вычисляются с использованием относительных ссылок, базисные - абсолютных и относительных.

Цепные показатели динамики.

Пример расчета цепных показателей.

1. Исходные значения признака х i надо записать в массив ячеек расположенных в строке или столбце в примере в строке (S8:X8).

Количественные данные следует определить как «Числовые».

2. Формула коэффициента роста Кр=Хi+1/Хi вводится для первого 2001г. В ячейку «Т9» записывается результат деления содержимого ячейки «Т8» на содержимое ячейки «S8».

1. Формула копируется в соседние ячейки U8-X8.

2. Расчет темпа роста Тр=(Хi+1/Хi)*100% выполняется по той же формуле – для 2001г. «=T8/S8» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

3. Расчет темпа прироста Тп=(Хi+1/Хi)*100% -100% реализуется для 2001г. формулой «=T8/S8-1» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

4. Абсолютный прирост Δ= Хi+1-Хi для 2001г. - «=T8-S8» в «Числовом» формате. Затем формула копируется в соседние ячейки.

5. Абсолютное значение 1% прироста А= Δ/ Тп=(Хi+1-Хi)/((Хi+1/Хi)*100% -100%) реализуется для 2001г. формулой «=(T8-S8)/((T8/S8)*100-100)» в «Числовом» формате. Затем формула копируется в соседние ячейки.

Базисные показатели динамики.

Пример расчета базисных показателей.

1. Исходные значения признака х i надо записать в массив ячеек расположенных в строке или столбце в примере в строке (S16:X16).

2. Количественные данные следует определить как «Числовые».

3. Формула коэффициента роста Кр=Хi/Х0 вводится для первого 2001г. В ячейку «Т17» записывается результат деления содержимого ячейки «Т16» на содержимое ячейки «$S$16» (базисное неизменное значение Х0 в ячейке «$S$16» задается как абсолютная ссылка).

1. Формула копируется в соседние ячейки U17-X17.

2. Расчет темпа роста Тр=(Хi/Х0)*100% выполняется по той же формуле – для 2001г. «=T16/$S$16» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

3. Расчет темпа прироста Тп=(Хi/Х0)*100% -100% реализуется для 2001г. формулой «=T16/$S$16-1» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

4. Абсолютный прирост Δ= Хi-Х0 для 2001г. - «=T16-$S$16» в «Числовом» формате. Затем формула копируется в соседние ячейки.

5. Абсолютное значение 1% прироста А= Δ/ Тп=(Хi-Х0)/(Хi/Х0)*100% -100%) реализуется для 2001г. формулой «=(T16-$S$16)/((T16/$S$16)*100-100)» в «Числовом» формате. Затем формула копируется в соседние ячейки.

Средние хронологические.

С равными интервалами, с равно отстоящими моментами, с неравными интервалами.

Средние хронологические с равными интервалами

вычисляется как среднее арифметическое простое.

Средние хронологические с равно отстоящими моментами.

Для расчетов в Excel используется функция:

СРЗНАЧ (диапазон данных).

Рассмотрим методику расчета на следующем примере:

1. Исходные значения признака х i надо записать в массив ячеек расположенных в строке или столбце в примере в строке (AA26:AM26), пронумеровать временные моменты (AA25:AM25).

Количественные данные следует определить как «Числовые».

2. В ячейку результата средней хронологической (AJ28) установить формулу: «=(AA26/2+СУММ(AB26:AL26)+AM26/2)/(AM25-1)»

где в числителе вычисляется сумма содержимого строки (AB26:AL26), к которой прибавляются половины значений первого и последнего отсчетов (AA26/2, AM26/2).

Затем результат делится на номер последнего отсчета за вычетом 1 (AM25-1). Порядок вычислений регулируется «()».

Средние хронологические с
неравно отстоящими моментами

В основу положена взвешенная средняя арифметическая, где весом является длительность периода между соседними отсчетами признака x_i. (датами) – «Δ t_i».

Для работы с датами при вводе им необходимо задать формат «Дата».

Расчеты выполняются на основе функций СУММ (диапазон данных) и СУММПРОИЗВ (диапазоны перемножаемых данных)

Рассмотрим методику расчета на примере расчета среднего размера остатка на счете за 2 месяца, за январь и февраль:

1. Исходные значения признака х i и соответствующие им даты надо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (AP31:AZ31) и (AP31:AZ31).

Количественные данные следует задать форматы «Числовые».

2. Даты определить в формате «Дата».

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

Обратите внимание на наличие в датах начала и конца периода для вычисления его длительности (в примере в ячейках AU30 и ВА30 записаны даты начала следующего месяца «1.02. 1.03», хотя остатки в эти моменты не менялись).

3. Вычислить длительности периодов между соседними отсчетами (в ячейках(AP32:AZ32)) как разность между соседними датами. Например: длительность хранения остатка 25 тыс. руб. – разность между ячейками хранящими даты 1.01 и 11.01 («=AQ30-AP30»). Затем копировать формулу в соседние ячейки. Координаты ячеек задавать относительными ссылками.

4. Средний остаток за 2 месяца определяется в ячейке АХ34 формулой

«=СУММПРОИЗВ (AP31:AZ31;AP32:AZ32)/СУММ(AP32:AZ32)»

СУММПРОИЗВ(AP31:AZ31;AP32:AZ32)реализует числитель

.

Функция выполняет попарное умножение элементов массива х_i в ячейках (AP31:AZ31)на элементы массива Δt_i, в ячейках (AP32:AZ32), а затем суммирует произведения.

СУММ (AP32:AZ32) – знаменатель

.

Функция суммирует элементы массива Δt_i, в ячейках (AP32:AZ32).

«/» -знак деления.

5. Средний остаток за январь определяется в ячейку АХ35 формулой «=СУММПРОИЗВ(AP31:AT31;AP32:AT32)/СУММ(AP32:AT32)»

Координаты ячеек соответствуют данным января – «AP31:AT31;AP32:AT3».

6. Средний остаток за февраль определяется в ячейку АХ36 формулой «=СУММПРОИЗВ(AU31:AZ31;AU32:AZ32)/СУММ(AU32:AZ32)»

Координаты ячеек соответствуют данным февраля – «AU31:AZ31;AU32:AZ32».

Агрегатные индексы.

агрегатный индекс переменного состава	агрегатный индекс структурных сдвигов	агрегатный индекс фиксированного состава

Расчеты выполняются на основе функции СУММПРОИЗВ (диапазоны перемножаемых данных)

Рассмотрим методику расчета на примере расчета агрегатных индексов в анализе выпуска продукции объединения, состоящего из 4-х предприятий.

1. Исходные значения количественных признаков базисного -q0- и текущего периодов -q1- записаны в массивы ячеек расположенных в столбце или строке (в примере в строках (BF41: BF44) и (BG41: BG44).

Значения качественных признаков базисного -p ₀ - и текущего периодов -p ₁ - записаны в массивы ячеек расположенных в столбце или строке (в примере в строках (BH41: BH44) и (BI41: BI44).

Количественным данным следует задать форматы «Числовые».

2. Агрегатный индекс переменного состава определяется в ячейке BH46 формулой
«=СУММПРОИЗВ(BG41:BG44;BI41:BI44)/СУММПРОИЗВ(BF41:BF44;BH41:BH44)»

где

СУММПРОИЗВ(BG41:BG44;BI41:BI44) реализует числитель

.

Функция выполняет попарное умножение элементов массива q _1i в ячейках (BG41:BG44)на элементы массива p _1i, в ячейках (BI41: BI44), а затем суммирует произведения;

СУММПРОИЗВ(BF41:BF44;BH41:BH44) реализует знаменатель

.

Функция выполняет попарное умножение элементов массива q _0i в ячейках (BF41:BF44)на элементы массива p _0i, в ячейках (BH41:BH44), а затем суммирует произведения

3. Агрегатный индекс структурных сдвигов определяется в ячейке BH47 формулой
«=СУММПРОИЗВ(BG41:BG44;BH41:BH44)/СУММПРОИЗВ(BF41:BF44;BH41:BH44)»

где

СУММПРОИЗВ (BG41:BG44;BH41:BH44) реализует числитель

.

Функция выполняет попарное умножение элементов массива q _1i в ячейках (BG41:BG44)на элементы массива p _0i, в ячейках (BH41: BH44), а затем суммирует произведения;

СУММПРОИЗВ(BF41:BF44;BH41:BH44) реализует знаменатель

.

Функция выполняет попарное умножение элементов массива q _0i в ячейках (BF41:BF44)на элементы массива p _0i, в ячейках (BH41:BH44), а затем суммирует произведения

4. Агрегатный индекс фиксированного состава определяется в ячейке BH48 формулой «=СУММПРОИЗВ(BG41:BG44;BI41:BI44)/СУММПРОИЗВ(BG41:BG44;BH41:BH44)»

где

СУММПРОИЗВ (BG41:BG44;BI41:BI44) реализует числитель

.

Функция выполняет попарное умножение элементов массива q _1i в ячейках (BG41:BG44)на элементы массива p _1i, в ячейках (BI41: BI44), а затем суммирует произведения;

СУММПРОИЗВ (BG41:BG44;BH41:BH44) реализует знаменатель

.

Функция выполняет попарное умножение элементов массива q _1i в ячейках (BG41:BG44)на элементы массива p _0i, в ячейках (BH41:BH44), а затем суммирует произведения.

В ячейке BI49 выполняется проверка увязки индексов I _ПC =I _CC *I _ФC. формулой «=BH47*BH48» - содержимое ячейки «BH47» -I _CC - умножается на содержимое ячейки «BH48» - I _ФC.

Раздел 2.
Используемые в практикуме команды Excel.

Задание массивов ячеек исходных данных.

Для функции часто надо указать диапазоны ячеек, в которых находятся исходные данные.

· Сплошной диапазон задается первой и последней ячейкой, разделенными знаком «:».

.Для несплошного ячейки разделяются знаком «;».

· Диапазон записывается в скобках
«()», стоящих после названия функции. Его можно задать либо непосредственно установив курсор в окне ввода,

либо в окнах «Аргументы функции» при использовании «Мастера функций»,

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

Для отдельных ячеек надо задавать ячейки, щелкая на них мышкой при нажатой клавише «Ctrl».

Задание данным формата.

Введенные числовые данные Excel может распознавать как число, текст, дату, процент и т. д. Поэтому после ввода чисел их необходимо определить – отформатировать – как числовые, текст, процент, дату…... Для этого:

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

· в контекстном меню «Формат ячеек» выбрать вкладку «Число», в которой указать формат «Числовой» либо другой нужный формат,

· указать для него количество знаков после запятой и другие параметры по меню,

· щелкнуть в меню «ОК» или клавишу «Enter».

Абсолютные и относительные координаты (ссылки).

Относительные координаты (ссылки) ячеек в Excel при копировании формул и функций из одной ячейки в другую автоматически меняют координаты исходных данных в соответствии со смещение координаты ячейки результата.

Например, копируется формула в том же столбце в соседнюю строку (из F10 в F11).:

В ячейке F10 находится функция расчета среднего значения для данных ячеек B8-M8.

В результате копирования в ячейке F11 все исходные данные взяты из ячеек с координатами строки на «1» больше исходных B9-M9

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

Абсолютные координаты обозначаются знаком «$» перед постоянной частью ссылки: $AX$48, AX$48, $AX48.

$AX$48 – при любом перемещении формулы исходные данные будут взяты из данной ячейки.

В примере выполняется вычитание содержимого ячейки $AX$48 поочередно из содержимого ячеек AU45-AZ45. Формула введена в ячейку AU47 и скопирована в ячейки AV47-AZ47. При перемещении формулы менялась координата уменьшаемого (относительная), вычитаемое всегда находился в ячейке $AX$48.

AX$48 – при копировании формулы в соответствии с перемещением изменится координата столбца, номер строки $48 не изменится;

$AX48 - при копировании формулы в соответствии с перемещением изменится координата строки, номер столбца $AX не изменится.

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

Сомножители Х расположены в строке С2-L2; Y – в столбце В3-В12. Формула введена в ячейку С3 и скопирована в квадратный массив до ячейки L12.

Ввод формул.

Активизируется (щелчком мыши) ячейка, в которой выполняется расчет по формуле (в примере ячейка Р3).

В строку формул устанавливается с клавиатуры знак «=». После него без пробелов между знаками вводится формула.

Для исходных данных указываются координаты ячеек щелчком мыши в них.

Арифметические знаки вводятся с клавиатуры (расположены на правой числовой части).

Знаки: «*» - умножение; «/» - деление; «^» - возведение в степень.

Порядок цепочки действий устанавливается вложением «(() ())».

Например, введем формулу

в ячейку Р3:

Ввод формулы заканчивается нажатием клавиши «Enter» либо щелчком мыши на знаке строки формул.

Ввод функции в ячейку.

Вызов функции можно выполнить различными способами.

Вариант 1.

· в строку ввода записать знак «=»

· в выпадающем списке функций выбрать нужную функцию

Если в списке последних 10 используемых функций нет нужной, выбрать в выпадающем списке функций «Другие функции», затем в «Категории» указать «Полный алфавитный перечень», в котором найти нужную функцию,

· в появившемся меню «Аргументы функции» задать координаты ячеек с исходными данными, выделив их мышью,

· щелкнуть в меню «ОК» или нажать клавишу «Enter».

Вариант 2

· щелкнуть знак ,

· в появившемся меню «Мастер функций» в окне «Категория» вызвать выпадающее меню, в котором выбрать «Полный алфавитный перечень»

· в перечне выбрать нужную функцию.

· щелкнуть в меню «ОК» или клавишу «Enter».

· в появившемся меню «Аргументы функции» задать координаты ячеек с исходными данными, выделив их мышью,

· щелкнуть в меню «ОК» или нажать клавишу «Enter».

Копирование формул и функций.

Существует несколько вариантов выполнения процедуры копирования. Все начинаются с выделения мышкой ячейки с копируемой формулой.

Вариант 1.

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

Вариант 2.

При копировании в соседние ячейки использовать протяжку мышкой. Для этого надо навести мышку на правый нижний угол выделенной ячейки, позиционировать ее до появления «+».

Затем, нажав левую кнопку мыши, протащить курсор по соседним ячейкам. В них выполнится копирование формулы.

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

Абсолютные ссылки сохранят свои координаты для ячейки, для столбца или для строки.

Раздел3 3
Задания для самостоятельного решения.

Задание 1.

Рассчитайте:

· среднедушевой доход за каждый год;

· дисперсию и среднее квадратическое отклонение душевого дохода за каждый год;

· цепные и базисные показатели динамики по каждой группе и средним;

· среднюю долю по каждой группе;

· общий средний доход по всем группам с 2000 по 2004г.

Таблица 1

РАСПРЕДЕЛЕНИЕ НАСЕЛЕНИЯ ПО ВЕЛИЧИНЕ СРЕДНЕДУШЕВЫХ ДЕНЕЖНЫХ ДОХОДОВ (в процентах)

Задание 2.

Рассчитайте:

· среднюю численность сельского населенного пункта за каждый год;

· дисперсию и среднее квадратическое отклонение численности сельского населенного пункта за каждый год;

· цепные и базисные показатели динамики по каждой группе и среднему по всем группам;

· среднее число сельских населенных пунктов по каждой группе численности с 1959 по 2002;

· общую среднюю численность по всем группам с 1959 по 2002г.

Таблица 2

РАСПРЕДЕЛЕНИЕ СЕЛЬСКИХ НАСЕЛЕННЫХ ПУНКТОВ ПО ЧИСЛУ ЖИТЕЛЕЙ

Задание 3.

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

Рассчитайте:

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

· дисперсию и среднее квадратическое отклонение размера частного домохозяйства в общем, для городского и сельского населенных пунктов;

Таблица 3

ЧИСЛО И РАЗМЕР ЧАСТНЫХ ДОМОХОЗЯЙСТВ (по данным переписи населения 2002 г.)

Задание 4.

Рассчитайте:

· средний возраст за каждый год;

· дисперсию и среднее квадратическое отклонение среднего возраста за каждый год;

· цепные и базисные показатели динамики по каждой группе и среднему по всем группам;

· среднюю численность по каждой возрастной группе и общее среднее по всем группам с 1995 по 2005г.

Таблица 4

РАСПРЕДЕЛЕНИЕ ЧИСЛЕННОСТИ населения ПО ВОЗРАСТНЫМ ГРУППАМ
на 1 января 2004 г. (тысяч человек)

Задание 5.

Рассчитайте:

· средний возраст каждой фракции;

· дисперсию и среднее квадратическое отклонение возраста каждой фракции;

· доли возрастной группы и полового состава в составе каждой фракции;

· средние доли возрастной группы и полового состава Государственной Думы;

· средний возраст депутата Государственной Думы.

Таблица 5

СОСТАВ ДЕПУТАТОВ ГОСУДАРСТВЕННОЙ ДУМЫ СОЗЫВА 2004-2007 гг.
(на 2 февраля 2004 г.; человек)