Excel 2013 включає 52 нові функції, більшість з яких було додано, щоб відповідати стандартам електронної таблиці Open Document.
У цій публікації буде розглянуто функцію Excel 2013 Gauss.
Наразі допомога 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 стандартне відхилення). Функція NORM.S.DIST.RANGE відсутня, тому ви можете просто запитати про ймовірність між -0,5,1). Натомість вам потрібно знайти відповідь у двох підформулах. Обчисліть ймовірність бути меншим за +1 з, =NORM.S.DIST(1,True)а потім відніміть ймовірність бути меншим за -0,5 за =NORM.S.DIST(-.5,True). Ви можете зробити це в одній формулі, як показано на малюнку 3.
Я усвідомлюю, що це довгий допис, але зображення вище - найважливіше зображення для розуміння нової функції GAUSS. Перечитайте цей абзац, щоб переконатися, що ви розумієте поняття. Щоб отримати ймовірність того, що член сукупності потрапить між двома точками на кривій, ви починаєте з NORM.S.DIST правої точки і віднімаєте NORM.S.DIST лівої точки. Це не ракетобудування. Це навіть не так складно, як VLOOKUP. Функція завжди повертає ймовірність з лівого краю кривої (-нескінченність) до значення z.
Що робити, якщо вас цікавить ймовірність перевищення певного розміру? Щоб знайти шанс бути більшим за (середнє + 1 стандартне відхилення), ви можете почати зі 100% і відняти можливість бути меншим за (середнє + 1 стандартне відхилення). Це було б =100%-NORM.S.DIST(1,True). Оскільки 100% - це те саме, що 1, ви можете скоротити формулу до =1-NORM.S.DIST(1,True). Або ви можете усвідомити, що крива симетрична, і попросити NORM.S.DIST (-1, True) отримати ту саму відповідь.
Для тих із вас, хто страждає на ОКР, як і я, я можу запевнити вас, що якщо ви =SUM(30.85,53.28,15.87)отримаєте 100%. Я знаю, бо перевірив це на робочому аркуші.
Повертаючись до рисунка 3 - ви повинні знати, як розрахувати ймовірність з будь-яких двох точок z1 і z2. Відніміть NORM.S.DIST (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%!". Отже, відповідь В вище можна переписати як
=NORM.S.DIST(1.5,True)-50%
Але якщо ви любите ярлики, ви ненавидите вводити текст на 50% і скорочуєте його до .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(1.5).
Так … вони додали функцію для людей, які не можуть відняти 0,5 з NORM.S.DIST!
Якщо ви схожі на мене, ви запитуєте: "Серйозно? Вони витратили ресурси на додавання цієї функції?" Ну, ще в Excel 2007 команда Excel прийняла рішення дозволити нам зберігати документи у форматі .ODS. Це формат електронної таблиці Open Document. Це не формат, керований корпорацією Майкрософт. Оскільки вони пропонують підтримку ODS, Microsoft змушена додати всі функції, які підтримує таблиця Open Document. Очевидно, більшість людей у консорціумі електронних таблиць Open Document не змогли зрозуміти, що відповідь на мою першу вікторину була A, тому вони додали зовсім нову функцію.
Я припускаю, що Microsoft не була в захваті від додавання підтримки функцій, подібних до інших функцій, що вже є в Excel. Я майже уявляю розмову між технічним письменником, якому доручено писати про GAUSS у довідці Excel, та керівником проекту в команді Excel:
Письменник: "Отже, розкажи мені про ГАУС"
ПМ: "Це безглуздо. Візьміть =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 In Depth пропонується повний опис усіх 452 функцій Excel. Ознайомтесь із попередньою версією Excel 2010 In Depth або новою програмою Excel 2013 In Depth, яка вийде у листопаді 2012 року.
