Как посчитать цифры исходя из среднего значения

Как посчитать цифры исходя из среднего значения

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

покупка

Сгенерировать случайное число по заданному среднему значению и стандартному отклонению в Excel

В некоторых случаях вы можете захотеть сгенерировать серию случайных чисел на основе заданного среднего значения и стандартного отклонения, как вы могли бы быстро решить эту проблему? Теперь это руководство познакомит вас с тем, как справиться с этим в Excel.

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

Чтобы решить эту проблему, вам необходимо сделать следующие шаги:

1. Во-первых, вам нужно ввести необходимое среднее значение и стандартное отклонение в две пустые ячейки, здесь я выбираю A1 и A2. Смотрите скриншот:

2. Затем в ячейке B3 введите эту формулу = НОРМОБР (СЛЧИС (); $ B $ 1; $ B $ 2) , и перетащите маркер заполнения в нужный диапазон. Смотрите скриншот:

Функции: В формуле = НОРМОБР (СЛЧИС (); $ B $ 1; $ B $ 2) , $ B $ 1 указывает среднее значение, $ B $ 2 указывает стандартное отклонение.

3. Затем в ячейках D1 и D2 вам нужно вычислить среднее значение и стандартное отклонение случайного числа, которое вы вставили на шаге 2. В D1 вычислите среднее значение, введите = СРЕДНИЙ (B3: B16) , нажмите клавишу Enter и в D2 вычислите стандартное отклонение, введите = СТАНДОТКЛОН.P (B3: B16) и нажмите клавишу Enter.

Совет: В Excel 2007 вам нужно ввести формулу = СТАНДОТКЛОНП (B3: B16) для вычисления стандартного отклонения первых случайных чисел.

Функции: B3: B16 — это диапазон чисел, который вы рандомизировали на шаге 2.

4. Теперь вы можете генерировать нужные реальные случайные числа. В ячейке D3 введите эту формулу =$B$1+(B3-$D$1)*$B$2/$D$2 , и перетащите стрелку заполнения в нужный диапазон. Смотрите скриншот:

Функции: B1 указывает необходимое вам среднее значение, B2 указывает необходимое стандартное отклонение, B3 указывает первую ячейку ваших первых случайных чисел, D1 — среднее значение первых случайных чисел, D2 — стандартное отклонение первых случайных чисел.

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

5. В ячейке D17 введите = СРЗНАЧ (D3: D16) и нажмите клавишу Enter, затем в ячейке D18 введите = СТАНДОТКЛОН.P (D3: D16) (или = СТАНДОТКЛОНП (D3: D16) в Excel 2007) и нажмите Enter .

Функции: D3: D16 — это диапазон последней серии случайных чисел.

Легко вставляйте случайные данные без дубликатов в диапазон ячеек

Относительные статьи:

• Генерация случайных чисел без повторов в Excel
• Генерация положительных или отрицательных случайных чисел в Excel
• Прекратить изменять случайные числа в Excel
• Генерация случайного да или нет в Excel

Как правильно вычислить среднее значение?

Средняя зарплата… Средняя продолжительность жизни… Практически каждый день мы с вами слышим эти словосочетания, используемые для описания множества одним единственным числом. Но как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека.

В чем проблема?

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

Давайте обратимся к классическому примеру со средней зарплатой.

В какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).

Средним значением в данном случае будет 195 150 рублей, что согласитесь, неправильно.

Какие способы вычисления среднего бывают?

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

• x – среднее арифметическое;
• x_n – конкретное значение;
• n – количество значений .

• Хорошо работает при нормальном распределении значений в выборке;
• Легко вычислить;
• Интуитивно понятно.

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

Вторым способом является вычисление моды, то есть наиболее часто встречающегося значения.

• M₀ – мода;
• x₀ – нижняя граница интервала, который содержит моду;
• n – величина интервала;
• f_m– частота (сколько раз в ряду встречается то или иное значение);
• f_m-1 – частота интервала предшествующего модальному;
• f_m+1 – частота интервала следующего за модальным.

• Прекрасно подходит для получения представления об общественном мнении;
• Хорошо подходит для нечисловых данных (цвета сезона, хиты продаж, рейтинги);
• Проста для понимания.

• Моды может просто не быть (нет повторов);
• Мод может быть несколько (многомодальное распределение).

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

• M_e – медиана;
• x₀ – нижняя граница интервала, который содержит медиану;
• h – величина интервала;
• f _i – частота (сколько раз в ряду встречается то или иное значение);
• S_m-1 – сумма частот интервалов предшествующих медианному;
• f_m – число значений в медианном интервале (его частота).

• Дает самую реалистичную и репрезентативную оценку;
• Устойчива к выбросам.

• Сложнее вычислить, так как перед вычислением выборку нужно упорядочить.

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

А теперь давайте вернемся к нашему примеру и посчитаем все три варианта среднего при помощи специальных функций Excel:

• СРЗНАЧ(число1;[число2];…) — функция для определения среднего арифметического;
• МОДА.ОДН(число1;[число2];. ) — функция моды (в более старых версиях Excel использовалась МОДА(число1;[число2];. ) );
• МЕДИАНА(число1;[число2];. ) — функция для поиска медианы.

И вот какие значения у нас получились:

В данном случае мода и медиана гораздо лучше характеризуют среднюю зарплату в компании.

Но что делать, когда в выборке не 10 значений, как в примере, а миллионы? В Excel это не посчитать, а вот в базе данных где хранятся ваши данные, без проблем.

Вычисляем среднее арифметическое на SQL

Тут все достаточно просто, так как в SQL предусмотрена специальная агрегатная функция AVG .

И чтобы ее использовать достаточно написать вот такой запрос:

/* Здесь и далее salary - столбец с зарплатами, а employees - таблица сотрудников в нашей базе данных */ SELECT AVG(salary) AS 'Средняя зарплата' FROM employees

Вычисляем моду на SQL

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

/* WITH TIES необходимо добавлять к TOP() если множество многомодально, то есть у множества несколько мод */ SELECT TOP(1) WITH TIES salary AS 'Мода зарплаты' FROM employees GROUP BY salary ORDER BY COUNT(*) DESC

Вычисляем медиану на SQL

Как и в случае с модой, в SQL нет встроенной функции для вычисления медианы, зато есть универсальная функция для вычисления процентилей PERCENTILE_CONT .

Выглядит все это так:

/* В данном случае процентиль 0.5 и будет являться медианой */ SELECT TOP(1) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER() AS 'Медианная зарплата' FROM employees

Подробнее о работе функции PERCENTILE_CONT лучше почитать в справке Microsoft и Google BigQuery.

Какой способ все-таки использовать?

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

Но это не всегда так. Если вы работаете со средним, то остерегайтесь многомодального распределения:

На графике представлено бимодальное распределение с двумя пиками. Такая ситуация может возникнуть, например, при голосовании на выборах.

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

А еще лучше разделить выборку на две группы и собрать статистические данные для каждой.

Вывод:

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

Окончательный выбор меры центральной тенденции всегда лежит на аналитике.

Полезные ссылки:

• SQL и теория вероятностей (YouTube)
• Анализ нормальности распределения данных (YouTube)
• Меры центральной тенденции

Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта

Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту. Выделение ячеек обращает на себя внимание и служит своего рода цветовым кодом. Напр., можно применить зелёный фон как знак успешности показателей, или назначить значениям красный шрифт как сигнал тревоги. Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.

Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:

Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист

Перед началом работы добавьте «Счёт по цвету» в Excel

«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как посчитать значения ячеек на основе цвета заливки

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона .

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Как посчитать значения ячеек на основе цвета шрифта

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта .

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Как посчитать значения ячеек на основе цвета условного форматирования

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.

Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта , в зависимости от типа условного форматирования.

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) – сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) – наименьшее значение в диапазоне по цвету
МАКС (MAX) – наибольшее значение в диапазоне по цвету

Какие ячейки и значения учитываются при вычислениях

Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.

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

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

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