Решение статистических задач в EXCEL: Практикум , страница 3
Количественные данные следует определить как «Числовые».
2. Для выполнения расчета необходимо закрыть имеющиеся открытые интервалы – первый – «до 500», последний — «от 700».
Формула вычисления левой границы первого интервала вводится в ячейку Z19 : «=АА19-(АА20-Z20)» (– из ячейки АА19 вычесть разницу между содержимым ячейки АА20 и Z20).
Правая граница последнего интервала в ячейку АА23 устанавливается формулой «=Z23+АА22-Z22» (от значения в ячейке Z23 откладывается размер предшествующего интервала «АА22-Z22»).
3. Рассчитывается среднее по каждой группе , как середина интервала. Для этого в ячейку первой группы (АС19) устанавливается функция СРЗНАЧ(Z19;AA19). После появления среднего значения первого интервала формула копируется в соседние ячейки. При этом автоматически смещаются координаты исходных данных в соответствии со смещением координат ячейки результата.
4. Рассчитывается величина средней взвешенной (в примере в ячейку АВ25)
СУММПРОИЗВ (АС19:АС23;АВ19:АВ23) реализует числитель
СУММ (АВ19:АВ23) – знаменатель;
«/» — знак деления.
Дисперсия. Среднее квадратическое
отклонение.
Дисперсия и среднее квадратическое отклонение (СКО) могут вычисляться по простой и взвешенной формулам.
Дисперсия, среднее квадратическое отклонение
по простой форме.
Для расчетов дисперсии по простой форме в Excel используется функция:
ДИСПР (диапазон данных).
СКО определяется как квадратный корень дисперсии, реализуемый оператором возведения в степень «^».
Рассмотрим методику расчета на примере расчета дисперсии и СКО зарплаты подразделения:
1. Исходные значения признака хi надо записать в массив ячеек расположенных в столбце или строке (в примере в строке (AF42:AQ42)).
Количественные данные следует определить как «Числовые».
2. В ячейку результата дисперсии (например «AF44») установить функцию ДИСПР(AF42:AQ42)
1. В ячейку результата СКО (например «AР44») установить функцию (ДИСПР(AF42:AQ42))^0,5.
Знак «^0,5» — означает возведение в степень 0,5 величины стоящей перед ним.
Дисперсия, среднее квадратическое
отклонение по взвешенной форме.
Для расчетов дисперсии по взвешенной форме в Excel используется функция:
СУММ (диапазон данных) и
СУММПРОИЗВ (диапазоны перемножаемых данных),
СКО определяется как квадратный корень дисперсии, реализуемый оператором возведения в степень «^».
Рассмотрим методику расчета на примере расчета дисперсии и СКО размера семьи группы
1. Исходные значения признака хi и частоту fi надо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (АТ46:AY46) и (АТ47:AY47).
Количественные данные следует определить как «Числовые».
1. Рассчитать среднее арифметическое взвешенное — в примере в ячейке AW49 установлена формула =СУММПРОИЗВ (AT46:AY46;AT47:AY47)/СУММ(AT47:AY47)
Решение задач по статистике и выводы к ним
Задача по статистике №1. Найти параметры интервального ряда распределения по данным таблицы, а именно: моду, медиану, среднюю арифметическую величину, среднюю взвешенную величину, коэффициент вариации, среднее квадратическое отклонение.
Группы компаний по основным производственным фондам, млн. руб. (х)
Число компаний (fi)
Середина интервала (Xi) = (начало интервала+конец интервала)/2
Мы сразу добавили столбец «середина интервала». Для первой группы компаний рассчитали следующим образом: (10+25)/2=17,5 млн. руб. Для 2-5 групп расчеты произведены аналогично.
Теперь рассчитаем среднюю арифметическую величину.
средняя арифметическая = = (17,5+29+37,5+45,5+55,5)/5=37 млн. руб.
Далее рассчитаем среднюю взвешенную величину.
средняя взвешенная = = (17,5*2+29*8+37,5*14+45,5*9+55,5*3)/36=38 млн. руб.
Значение средневзвешенной величины можно считать более корректным, чем значение средней арифметической величины, поэтому далее в расчетах будем использовать среднюю взвешенную.
Теперь добавим в таблицу столбцы, данные которых нам понадобятся для расчета дисперсии.
Число компаний (f)
Середина интервала (Xi) = (начало интервала+конец интервала)/2
СРЗНАЧ (функция СРЗНАЧ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции С AVERAGE в Microsoft Excel.
Описание
Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.
Синтаксис
Аргументы функции СРЗНАЧ описаны ниже.
- Число1 Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.
- Число2. Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых нужно вычесть среднее значение, не более 255.
Замечания
- Аргументы могут быть числами, именами или ссылками на диапазоны или ячейки, содержащие числа.
- Логические значения и текстовые представления чисел, которые непосредственно введите в список аргументов, не учитываются.
- Если аргумент является ссылкой на диапазон или ячейку, содержащую текст или логические значения, или ссылкой на пустую ячейку, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
- Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
- Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.
- Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.
Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.
- Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
- Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
- Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При ассиметричном распределении множества чисел они могут отличаться.
Совет: При вычислении средних значений ячеек следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если в диалоговом окне Параметры Excel настольного приложения Excel снят флажок Показывать нули в ячейках, которые содержат нулевые значения. Если этот флажок установлен, пустые ячейки игнорируются, но нулевые значения учитываются.
Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения
- Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИМН в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
- Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
- Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
- Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
Замечания
- Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
- Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
- Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
- В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
- Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
Примечание: Функция СРЗНАЧЕСЛИМН измеряет среднее значение распределения, то есть расположение центра набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:
- Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
- Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
- Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.