Excel 2013 включает 52 новые функции, большинство из которых были добавлены в соответствии со стандартами Open Document Spreadsheet.
Этот пост будет посвящен функции Гаусса в Excel 2013.
В настоящее время справка Excel немного скучно описывает функцию.
Синтаксис: =GAUSS(x)- Возвращает на 0,5 меньше, чем стандартное нормальное совокупное распределение.
Напоминаем, что стандартное нормальное распределение - это частный случай со средним значением 0 и стандартным отклонением 1. Вы узнаете его как кривую колокола.
В Excel всегда был способ вычисления вероятностей для стандартной нормальной кривой. Сначала NORMSDIST, а затем в Excel 2010 NORM.S.DIST (z, True) будут вычислять вероятности. Аргумент «z» - это количество стандартных отклонений от среднего.
Вот тривиальный пример использования NORM.S.DIST для вычисления вероятности. Какова вероятность того, что случайный член из совокупности будет меньше, чем -0,5 стандартного отклонения от среднего? Это область, заштрихованная на рисунке 2. Формула проста =NORM.S.DIST(-0.5,True).
Достаточно просто, правда? Если бы вас интересовали только мелочи, эта формула была бы всем, что вам нужно. Однако исследователей часто интересуют другие диапазоны, кроме левой части кривой.
На рисунке 3 вы хотите знать вероятность того, что случайный член окажется между (среднее-0,5 стандартного отклонения) и (среднее + 1 стандартное отклонение). Функция НОРМ.С.РАСП.ДИАПАЗОН отсутствует, поэтому вы можете просто запросить вероятность между -0,5,1). Вместо этого вам нужно найти ответ в двух подформулах. Вычислите вероятность быть меньше +1 с, =NORM.S.DIST(1,True)а затем вычтите вероятность того, что будет меньше -0,5 с =NORM.S.DIST(-.5,True). Вы можете сделать это с помощью одной формулы, как показано на рисунке 3.
Я понимаю, что это длинный пост, но изображение выше является наиболее важным для понимания новой функции GAUSS. Перечитайте этот абзац, чтобы убедиться, что вы понимаете концепцию. Чтобы получить вероятность того, что член популяции окажется между двумя точками на кривой, вы начнете с НОРМ.С.РАСП правой точки и вычтите НОРМ.С.РАСП левой точки. Это не ракетостроение. Это даже не так сложно, как ВПР. Функция всегда возвращает вероятность от левого края кривой (-infinity) до значения z.
Что, если вас интересует вероятность быть больше определенного размера? Чтобы найти вероятность быть больше (среднее + 1 стандартное отклонение), вы можете начать со 100% и вычесть возможность быть меньше (среднее + 1 стандартное отклонение). Это было бы =100%-NORM.S.DIST(1,True). Поскольку 100% равно 1, вы можете сократить формулу до =1-NORM.S.DIST(1,True). Или вы можете понять, что кривая симметрична, и запросить НОРМ.С.РАСП (-1, Истина), чтобы получить тот же ответ.
Для тех из вас, у кого есть ОКР, я могу заверить вас, что если =SUM(30.85,53.28,15.87)вы в конечном итоге получите 100%. Я знаю, потому что проверил это на листе.
Возвращаясь к рисунку 3, вы должны знать, как рассчитать вероятность из любых двух точек z1 и z2. Вычтите НОРМ.С.РАСП (z2, True) -NORM.S.DIST (z1, True), и вы получите ответ. Давайте рассмотрим очень частный случай, когда z1 - среднее значение. Вы пытаетесь вычислить вероятность того, что кто-то окажется между средним значением и +1,5 стандартного отклонения от среднего, как показано на рисунке 6.
Используя то, что вы узнали из рисунка 3, какие из них найдут вероятность площади под кривой выше?
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) - Ни один из вышеперечисленных
Как ты это сделал? Если вы ответили A, B или C, вы набрали 100% баллов по тесту. Поздравляю. Как я уже сказал, на самом деле это не ракетостроение.
Тем из вас, кто любит ярлыки, помните, что вероятность того, что что-то будет меньше или равно среднему, составляет 50%. Когда вы видите = NORM.S.DIST (0, True), вы можете сразу подумать: «О, это 50%!». Итак, ответ B выше можно переписать как
=NORM.S.DIST(1.5,True)-50%
Но если вам нравятся сочетания клавиш, вы ненавидите печатать 50% и сократите их до 0,5:
=NORM.S.DIST(1.5,True)-.5
Не могли бы вы использовать симметричную противоположность области под кривой? Да, = .5-NORM.S.DIST (-1,5; True) даст тот же результат. Итак, приведенная выше викторина может быть такой:
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-.5 -
=.5-NORM.S.DIST(-1.5,True) - Все вышеперечисленное
Если вы выберете ответ, я буду полностью доверять вам. В конце концов, это Excel. Есть пять способов сделать что-нибудь, и я приму любой ответ, который работает (ну, кроме жесткого кодирования = 0,433 в ячейке).
Те из вас, кто правильно ответили на последний вопрос, прекратите читать. Всем остальным понадобится GAUSS:
А как насчет функции GAUSS? Что ж, функция GAUSS дает нам еще один способ решить конкретный случай, когда диапазон идет от среднего до точки выше среднего. Вместо того, чтобы использовать ответы выше, вы можете использовать =GAUSS(1.5).
Да … они добавили функцию для людей, которые не могут вычесть 0,5 из NORM.S.DIST!
Если вы похожи на меня, вы спросите: «Серьезно? Они потратили ресурсы на добавление этой функции?» Что ж, еще в Excel 2007 команда Excel приняла решение разрешить нам сохранять документы в формате .ODS. Это формат электронной таблицы открытого документа. Это не формат, контролируемый Microsoft. Поскольку они предлагают поддержку ODS, Microsoft вынуждена добавить все функции, которые поддерживает Open Document Spreadsheet. По-видимому, большинство людей в консорциуме Open Document Spreadsheet не могли понять, что ответом на мой первый тест был A, поэтому они добавили совершенно новую функцию.
Я предполагаю, что Microsoft не была в восторге от добавления поддержки функций, которые были похожи на другие функции, уже имеющиеся в Excel. Я почти могу представить разговор между техническим писателем, которому поручено написать о GAUSS в справке Excel, и менеджером проекта в команде Excel:
Писатель: "Итак, расскажите мне о GAUSS"
ПМ: «Это глупо. Возьмите =NORM.S.DISTи вычтите 0,5. Не могу поверить, что нам пришлось это добавить».
Затем автор отредактировал редакционные комментарии и предложил этот раздел справки:
Итак, позвольте мне предложить эту альтернативную тему помощи:
GAUSS (z) - вычисляет вероятность того, что член стандартной нормальной популяции окажется между средним значением и стандартным отклонением + z от среднего.
- z Обязательно. Количество стандартных отклонений выше среднего. Обычно в диапазоне от +0,01 до +3.
- Добавлен в Excel 2013 для поддержки людей, которые не могут вычитать два числа.
- Не имеет особого смысла для отрицательных значений Z. Чтобы вычислить вероятность того, что что-то попадает в диапазон от -1,5 до среднего, используйте
=GAUSS(1.5). - Не будет работать в Excel 2010 и ранее. В Excel 2010 и более ранних версиях используйте
=NORM.S.DIST(z,True)-0.5.
Вот и все … больше, чем вы когда-либо хотели узнать о GAUSS. Это определенно больше, чем я когда-либо хотел знать. Между прочим, мои книги «Подробно об Excel» предлагают полное описание всех 452 функций Excel. Ознакомьтесь с предыдущим выпуском Excel 2010 In Depth или новым Excel 2013 In Depth, который будет выпущен в ноябре 2012 года.
