Как рассчитать вероятность в Excel (с примером)

9 сентября 2021 г.

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

Зачем использовать Excel для расчета вероятности?

Многие предприятия используют компьютерные программы, такие как Microsoft Excel, при выполнении вычислений, таких как вероятности. Основное преимущество выполнения расчетов вероятности в Excel заключается в том, что он использует встроенные макросы, предоставляемые программным обеспечением. Это ускоряет вычисления, а также снижает вероятность ошибки в вычислениях, поскольку компьютер выполняет все необходимые математические операции.

Вот некоторые ситуации, когда бизнес может рассмотреть возможность использования таблицы вероятностей:

• Прогнозирование роста на основе данных, предоставленных маркетинговой кампанией

• Оценка потенциальных задержек проекта из-за прогнозов суровой погоды

• Создание диапазона прогноза продаж для прогнозирования наиболее вероятных уровней успеха продукта.

• Выполнение оценки рисков проекта для определения потенциальной стоимости рисков, с которыми сталкивается проект.

• Оценка сроков сдачи проекта

Важные термины при расчете вероятности в Excel

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

• Таблица: для выполнения расчета вероятности Excel требует, чтобы вы вводили информацию в таблицу, состоящую как минимум из двух столбцов и нескольких строк данных.

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

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

• Функция: Excel предоставляет пользователям предварительно определенные математические вычисления, которые автоматизируют многие математические процессы. Excel использует функцию ПРОБ, чтобы найти вероятности из таблицы данных, и функцию СУММ, чтобы сложить ряд чисел.

Как рассчитать вероятность в Excel

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

1. Соберите данные

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

2. Назовите свою диаграмму

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

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

3. Введите данные категории

Столбец категории диаграммы вероятности содержит различные уровни, которые вы оцениваете для вероятности. Чтобы функция PROB работала, все данные в проверяемом столбце должны быть числовыми значениями, поскольку функцию PROB нельзя использовать для поиска диапазонов вероятностей на основе текстовых меток. Например, попытка найти вероятности на диаграмме с использованием диапазона от «Самый низкий риск» до «Самый высокий риск» возвращает сообщение об ошибке.

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

4. Сортировать по категориям

Функция PROB требует от вас сортировки данных в таблице от меньшего к большему. Если вы не сделали этого при вводе данных, Excel предлагает действие «Сортировка», чтобы быстро упорядочить данные от низкого к высокому. Выделите данные в столбце, щелкнув первую ячейку под меткой категории, удерживая кнопку мыши нажатой и перетащив вниз до последней ячейки. Если в вашей таблице есть столбец «Порядок сортировки», вместо этого щелкните первую ячейку под ярлыком «Порядок сортировки», затем перетащите вниз к последней ячейке под ярлыком категории, чтобы выбрать оба столбца.

Нажмите на вкладку «Данные» в верхней части окна Excel. В разделе «Сортировка и фильтрация» щелкните значок с буквами A, Z и стрелкой, указывающей вниз — это отсортирует ваши данные от низшего к высокому на основе крайнего левого выбранного столбца.

5. Рассчитайте и введите свои вероятности

Если ваш набор данных включает вероятности для каждой метки, введите каждую вероятность в столбец «Вероятность». Если вы еще не преобразовали свои данные в вероятности, вместо этого введите экземпляры в столбец «Экземпляры». Найдите общее количество записанных экземпляров, сложив их. Для больших наборов данных Excel предоставляет формулу для быстрого вычисления суммы набора чисел. Введите следующую формулу в ячейку под последней ячейкой, содержащей данные экземпляра на диаграмме:

=СУММ( [top cell containing instance data] : [bottom cell containing instance data] )

Функция СУММ возвращает общее значение всех ячеек между двумя ячейками, разделенными двоеточием в формуле, и автоматически обновляется, если вы изменяете значение ячейки в пределах диапазона.

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

знак равно[cell containing instance data] / [cell containing SUM function]

Повторите это для всех ячеек в столбце «Вероятность», чтобы преобразовать их.

6. Создайте таблицу расчета

Чтобы создать свой калькулятор вероятности, вам нужно создать три новые ячейки: по одной для ввода верхнего и нижнего пределов вашей вероятности и третью для вывода результата. Наиболее организованный метод для этого — создать новую таблицу с двумя столбцами и тремя строками. Пометьте три ячейки в левом столбце следующим образом:

• Нижний предел:

• Верхний предел:

• Вероятность:

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

=ПРОБ( [top category cell] : [bottom category cell], [top probability cell] : [bottom probability cell], [cell next to “Lower limit:” label], [cell next to “Upper limit:” cell] )

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

Найдите вероятность диапазона результатов, введя нижний и верхний пределы диапазона в ячейки рядом с соответствующими метками. При использовании столбца «Порядок сортировки» используйте соответствующие числа в этом столбце, а не имена в столбце категорий. Функция PROB автоматически вычисляет вероятность всех результатов в этом диапазоне и отображает результирующую вероятность.

Советы по расчету вероятности в Excel

При создании калькулятора вероятности в Excel помните об этих советах, чтобы обеспечить правильную работу калькулятора:

• Запишите свои вероятности в виде десятичных дробей, а не процентов. Ввод числа больше единицы или меньше нуля возвращает “#ЧИСЛО!” сообщение об ошибке.

• Сумма ваших вероятностей должна равняться единице, иначе ваша функция PROB вернет “#ЧИСЛО!” сообщение об ошибке.

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

• Если в вашем диапазоне категорий и диапазоне вероятностей нет равного количества ячеек, PROB возвращает сообщение об ошибке “#N/A”.

• Пробелы в примерах формул в этой статье облегчают чтение, но вы можете удалить пробелы, чтобы сделать ваши формулы короче, так как Excel игнорирует пробелы при выполнении функций PROB и SUM.

Примеры расчета вероятности

В этом примере вычисления PROB показано, как создать таблицу расчета вероятности в Excel. В первом примере показано, что нужно ввести в каждую ячейку для создания таблицы. Он также включает строку и столбец, показывающий номера строк и буквы столбцов в Excel. Во второй таблице показана таблица расчета вероятности, которую учитель может использовать, чтобы определить, сколько учеников получат на экзамене оценку C или выше на основе результатов предыдущих учеников:

Пример создания таблицы

A B C D 1 Порядок сортировки Классы Экземпляры Вероятность 2 1

=С2/С7 3 2

=С3/С7 4 3

=С4/С7 5 4

=С5/С7 6 5

=С6/С7 8

Итого: =СУММ(C2:C6)

9

10 Нижний предел:

11 Верхний предел:

12 Вероятность: =ПРОБ(A2:A6,D2:D6,B10,B11)

Пример расчета вероятности

A B C D 1 Порядок сортировки Классы Экземпляры Вероятность 2 1 F 1,055 3 2 D 3,167 4 3 C 5,278 5 4 B 6,333 6 5 A 3,167 8

Всего: 18

9

10 Нижний предел: 3

11 Верхний предел: 5

12 Вероятность: 0,778