Статистичні функції Excel для обчислення середніх значень: навчальний посібник із прикладами, частина друга

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

У цій статті ми розглянемо статистичні функції Excel для обчислення середнього значення.

Зауважте, що деякі статистичні функції були представлені в останніх версіях Excel, тому вони недоступні в старіших версіях.

Функції для обчислення середніх значень

AVERAGE

Функція AVERAGE це одна зі статистичних функцій Excel. Функція повертає середнє числових значень, введених у функцію. Простими словами, він додає всі значення, зазначені у функції, потім ділить їх на кількість і повертає результат.

синтаксис

= AVERAGE(number1,number2,…)

предметів

• numero1 : перше число, яке ви хочете використовувати для обчислення середнього.
• [numero2] : друге число, яке ви хочете використовувати для усереднення.

приклад

Щоб побачити, як працює функція AVERAGE давайте подивимося на приклад:

У першому прикладі ми вставили аргументи безпосередньо у функцію.

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

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

У третьому прикладі ми посилалися на діапазон, у якому клітинки відформатовано як текстові значення. У цьому випадку ви можете перетворити ці текстові числа на дійсні числа, щоб обчислити середнє значення.

У четвертому прикладі ми маємо апостроф перед кожним значенням у кожній клітинці, і тому функція ігнорує його.

AVERAGEA

Функція AVERAGEA Excel внесено до категорії статистичних функцій Microsoft Excel. Повертає середнє значення вказаних чисел у функції, але на відміну від AVERAGE, розглядає логічні значення та числа, відформатовані як текст.

синтаксис

=AVERAGEA(valore1,valore2,…)

предметів

• value1 : значення, яке є числом, логічним значенням або числом, що зберігається як текст.
• [valore2] : значення, яке є числом, логічним значенням або числом, що зберігається як текст.

приклад

Щоб зрозуміти функцію AVERAGEA нам потрібно побачити приклад:

Функція повертає значення 10,17, тобто «(0+0+1+10+20+30)/6».

AVERAGEIF

Функція AVERAGEIF Excel внесено до категорії статистичних функцій Microsoft Excel. Повертає середнє значення чисел, які задовольняють кілька заданих умов . 

синтаксис

= AVERAGEIF( range, criteria, [average_range] )

теми

• range:  масив значень (або діапазон клітинок, що містять значення) для перевірки відповідності наданим критеріям.
• criteria:  Умова для перевірки кожного зі значень у наданому діапазоні.
• [average_range]:  Додатковий масив числових значень (або комірок, що містять числа), які повинні бути усереднені, якщо відповідне значення в діапазоні відповідає наданим критеріям.

Якщо тема [average_range] опущено, середнє обчислюється для значень у початковому наданому діапазоні.

Наданими критеріями можуть бути:

числове значення (включаючи цілі числа, десяткові дроби, дати, час і логічні значення) (наприклад, 10, 01, ІСТИНА)
O
текстовий рядок (наприклад, «Текст», «Четвер») – ПОВИНЕН надаватися в лапках
O
вираз (наприклад, “>12”, “<>0”) – ПОВИНЕН надаватися в лапках.
Також зауважте, що функція AVERAGEIF Excel не чутливий до регістру. Так, наприклад, текстові рядки "TEXT"А"text” будуть оцінюватися як рівні.

приклад

Щоб зрозуміти функцію AVERAGEIF ми повинні спробувати це на прикладі.

Клітини A16-A20 таблиці нижче показано п’ять прикладів функції AVERAGEIF програми Excel.

Для кожного виклику функції AVERAGEIF Excel, тема range (для перевірки criteria) – діапазон комірок A1-A14 і тему [average_range] (що містить значення для усереднення) - це діапазон комірок B1-B14.

Зауважте, що в клітинках A16, A18 і A20 електронної таблиці вище текстове значення «Четвер» і вирази «>2» і «<>TRUE» беруться в лапки. Це важливо для всіх текстів або виразів.

AVERAGEIFS

Функція AVERAGEIFS Excel внесено до категорії статистичних функцій Microsoft Excel. Повертає середнє значення чисел, які задовольняють кілька заданих умов . На відміну від AVERAGEIF, ви можете встановити кілька умов і обчислити середнє значення лише для чисел, які відповідають усім умовам.

синтаксис

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

теми

• average_range:  Масив числових значень (або клітинок, що містять числа), які потрібно усереднити.
• criteria_range1, [criteria_range2], …: Масиви значень (або діапазони комірок, що містять значення) для порівняння один з одним criteria1, criteria2, … (Масиви criteria_range всі надані повинні бути однакової довжини).
• criteria1, [criteria2], …: Умови, які необхідно перевірити щодо значень у criteria_range1, [criteria_range2], …

приклад

Тепер давайте розглянемо приклад функції AVERAGEIFS:

У наступному прикладі ми використали функцію AVERAGEIFS обчислити середню кількість, продану продавцем «П'єтро» і для товару «Б». Ми ввели критерії безпосередньо у функцію та маємо два записи про продаж Петром продукту B.

У наступному прикладі ми використали AVERAGEIFS із зірочкою для розрахунку середньої ціни фруктів, кількість яких перевищує 20 одиниць і має B у назві.

У наведених нижче даних ми маємо два фрукти, які відповідають цим критеріям.

MEDIAN

Функція MEDIAN Excel повертає статистичне медіане (середнє значення) списку наданих чисел.

синтаксис

= MEDIAN( number1, [number2], ... )

теми

числові аргументи - це набір з одного або кількох числових значень (або масивів числових значень), для яких потрібно обчислити медіану

Зауважте, що:

• Якщо в даному наборі даних є парна кількість значень, повертається середнє з двох середніх значень;
• Якщо наданий масив містить порожні клітинки, текст або логічні значення, ці значення ігноруються під час обчислення медіани.
• У поточних версіях Excel (Excel 2007 і новіших) ви можете надати до 255 числових аргументів для функції «Медіана», але в Excel 2003 функція може приймати лише до 30 числових аргументів. Проте кожен із числових аргументів може бути масивом багатьох значень.

приклад

У наведеній нижче таблиці показано три приклади функції Median:

Розглянемо, що в попередніх прикладах:

• Приклад у клітинку B2 отримує парну кількість значень, тому медіана обчислюється як середнє двох середніх значень, 8 і 9;
• Приклад у клітинку B3 містить порожню клітинку A8. Ця клітинка ігнорується під час обчислення медіани.

Щоб дізнатися більше про функцію MEDIAN Excel див Веб-сайт Microsoft Office .

MODE

Функція MODE Excel повертає MODE статистика (найчастіше значення) списку наданих чисел. Якщо в наданих даних є 2 або більше повторюваних значень, функція повертає найменше значення серед них

синтаксис

= MODE( number1, [number2], ... )

теми

є набором одного або кількох числових значень (або масивів числових значень), для яких потрібно обчислити MODE статистика.

Примітка:

• У поточних версіях Excel (Excel 2007 і пізніші) ви можете надати функції до 255 числових аргументів MODE, але в Excel 2003 функція може приймати лише до 30 числових аргументів.
• Функція ігнорує текстові та логічні значення в наданому масиві чисел Mode.

Приклади функцій MODE

Приклад 1

Наступна електронна таблиця показує функцію MODE Excel, який використовується для обчислення MODE статистика набору значень у клітинках A1-A6.

Приклад 2

Наступна електронна таблиця показує функцію MODE, що використовується для розрахунку MODE статистика набору значень у клітинках A1-A10.

Зверніть увагу, що в даному випадку їх два mode в даних.

У наведеному вище випадку, коли дані в стовпці A попередньої електронної таблиці мають два MODE статистики (3 і 4), функц MODE повертає найменше з цих двох значень.

Щоб отримати додаткові відомості та приклади функції MODE Excel див Веб-сайт Microsoft Office .

MODE.SNGL

Функція MODE.SNGL Excel повертає MODE статистика (найчастіше значення) списку наданих чисел. Якщо в наданих даних є 2 або більше повторюваних значень, функція повертає найменше значення серед них.

Функція Mode.Sngl є новим у Excel 2010 і тому недоступний у попередніх версіях Excel. Однак функція є просто перейменованою версією функції MODE доступні в попередніх версіях Excel.

синтаксис

= MODE.SNGL( number1, [number2], ... )

теми

є набором одного або кількох числових значень (або масивів числових значень), для яких потрібно обчислити MODE.SNGL статистика.

Приклади функцій MODE.SNGL

Приклад 1

Наступна електронна таблиця показує функцію MODE.SNGL Excel, використовується для обчислення статистичного РЕЖИМу набору значень у клітинках A1-A6.

Приклад 2

Наступна електронна таблиця показує функцію MODE.SNGL, що використовується для обчислення статистичного режиму набору значень у клітинках A1-A10.

Зверніть увагу, що в даному випадку їх два mode в даних.

У наведеному вище випадку, коли дані в стовпці A попередньої електронної таблиці мають два MODE статистики (3 і 4), функц MODE.SNGL повертає найменше з цих двох значень.

Щоб отримати додаткові відомості та приклади функції MODE.SNGL Excel див Веб-сайт Microsoft Office .

GEOMEAN

Середнє геометричне — це міра середнього, яка вказує на типове значення набору чисел. Це вимірювання можна використовувати лише для позитивних значень.

Середнє геометричне набору значень, у ₁ І ₂ , …, у _n розраховується за формулою:

Зверніть увагу, що середнє геометричне завжди менше або дорівнює середньому арифметичному.

Функція Geomean Excel обчислює середнє геометричне заданого набору значень.

синтаксис

= GEOMEAN( number1, [number2], ... )

теми

одне або кілька позитивних числових значень (або масивів числових значень), для яких потрібно обчислити середнє геометричне.

У поточних версіях Excel (Excel 2007 і новіших) функція може приймати до 255 числових аргументів, але в Excel 2003 функція може приймати лише до 30 числових аргументів. Однак кожен аргумент може бути масивом значень або діапазоном клітинок, кожна з яких може містити багато значень.

приклад

клітина B1 електронної таблиці показує простий приклад функції geomean в Excel, використовується для обчислення середнього геометричного значень у клітинках A1-A5.

У цьому прикладі функція Geomean повертає значення 1.622671112 .

HARMEAN

Середнє гармонічне — це міра середнього, обчислена як зворотна величина середнього арифметичного зворотних величин. Це можна розрахувати лише для позитивних значень.

Тому середнє гармонійне набору значень y1, y2, ..., yn визначається формулою:

середнє гармонічне завжди менше або дорівнює середньому геометричному, а середнє геометричне завжди менше або дорівнює середньому арифметичному.

Функція Harmean Excel обчислює середнє гармонічне заданого набору значень.

синтаксис

= HARMEAN( number1, [number2], ... )

теми

одне або кілька позитивних числових значень (або масивів числових значень), для яких потрібно обчислити середнє гармонічне.

У поточних версіях Excel (Excel 2007 і новіших) функція може приймати до 255 числових аргументів, але в Excel 2003 функція може приймати лише до 30 числових аргументів. Однак кожен аргумент може бути масивом значень або діапазоном клітинок, кожна з яких може містити багато значень.

приклад

Комірка B1 в електронній таблиці праворуч показує простий приклад функції Harmean в Excel, використовується для обчислення середнього гармонійного значень у клітинках A1-A5.

У цьому прикладі функція Harmean повертає значення 1.229508197.

TRIMMEAN

Функція TRIMMEAN (також відомий як урізане середнє) – це міра середнього, яка вказує на центральну тенденцію набору значень.

Урізане середнє обчислюється шляхом відкидання деяких значень на кінцях діапазону значень перед обчисленням середнього арифметичного решти значень. Це запобігає спотворенню розрахованого середнього через екстремальні значення (також відомі як викиди, технічно outliers).

синтаксис

= TRIMMEAN( array, percent )

теми

• масив – Масив числових значень, для яких потрібно обчислити усічене середнє.
• відсотків – Відсоток значень, які потрібно видалитиarray надається.

Зверніть увагу, що вказане відсоткове значення є загальним відсотком значень, які потрібно виключити з обчислення. Цей відсоток ділиться на два, щоб отримати кількість значень, видалених з кожного кінця діапазону.

Також слід зазначити, що коли Excel обчислює, скільки значень видалено зarray наданих значень, обчислений відсоток округлюється до найближчого кратного 2. Наприклад, якщо ви хочете обчислити скорочене середнє значення a array з 10 значень, тому:

• Відсоток 15% відповідає 1,5 значенням, які будуть округлені до 0 (тобто жодні значення не будуть викидані зarray перед обчисленням середнього);
• Відсоток 20% відповідає 2 значенням, тому 1 значення буде відкинуто з кожного кінця діапазону перед усередненням решти значень;
• Відсоток у 25% відповідає 2,5 значенням, які будуть округлені до 2 (тобто 1 значення буде відкинуто з кожного кінця діапазону перед усередненням решти значень).

приклад

Клітини B1-B3 у таблиці нижче показано 3 приклади функції trimmean в Excel, усі вони використовуються для обчислення скороченого середнього значень у клітинках A1-A10, для різних відсоткових значень.

Майте на увазі, що в камері B1 електронної таблиці вище наведений відсотковий аргумент становить 15%. Оскільки вarray якщо є 10 значень, кількість значень, які потрібно ігнорувати, становить 1,5, округлене до найближчого кратного 2, яке дорівнює нулю.

Функції для обчислення перестановок

PERMUT

Кількість перестановок для заданої кількості об’єктів — це кількість комбінацій у будь-якому можливому порядку.

Перестановки відрізняються від комбінацій тим, що для перестановки порядок об’єктів має значення, а в комбінації порядок не має значення.

Кількість можливих перестановок визначається формулою:

голуб k кількість обраних об’єктів e n кількість можливих об'єктів.

Функція Excel Permut обчислює кількість перестановок заданої кількості об’єктів із набору об’єктів.

синтаксис

= PERMUT( number, number_chosen )

теми

• number: Загальна кількість доступних елементів
• number_chosen: кількість об’єктів у кожній перестановці (тобто кількість об’єктів, вибраних із набору)

Зауважте, що якщо будь-який з аргументів надано як десяткові значення, вони будуть скорочені до цілих чисел функцією Permut.

приклад

У наступній електронній таблиці Excel Permut використовується для обчислення кількості перестановок шести об'єктів, вибраних із наборів різного розміру:

PERMUTATIONA

Функції Excel Обмін і Permutationa обчислюють кількість перестановок вибору об’єктів із набору.

Однак ці дві функції відрізняються тим, що Функція перестановки не підраховує повторення, тоді як функція Permutationa підраховує повторення.

Наприклад, у наборі з 3 предметів, a , b , c , скільки перестановок у 2 об’єктів?

• La Функція перестановки повертає результат 6 (перестановки: ab , ac , ba , bc , ca , cb );
• Функція Permutationa повертає результат 9 (перестановки: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Функція Excel Permutationa обчислює кількість перестановок заданої кількості об’єктів із набору об’єктів.

синтаксис

= PERMUTATIONA( number, number_chosen )

теми

• number: Загальна кількість об’єктів у наборі (має бути ≥ 0).
• number_chosen: кількість об’єктів, вибраних із набору (має бути ≥ 0).

Зауважте, що якщо будь-який з аргументів надано як десяткові значення, вони будуть скорочені до цілих чисел функцією PERMUTATIONA.

приклад

У наступній електронній таблиці Excel PERMUTATIONA використовується для обчислення кількості перестановок шести об'єктів, вибраних із наборів різного розміру:

Функції для обчислення довірчих інтервалів

CONFIDENCE

В Excel 2010 функція CONFIDENCE було замінено функцією Confidence.Norm.

Хоча його було замінено, поточні версії Excel все ще мають цю функцію Confidence (зберігається у списку функцій сумісності), щоб забезпечити сумісність із попередніми версіями Excel.

Проте функція Confidence може бути недоступним у майбутніх версіях Excel, тому ми рекомендуємо використовувати цю функцію Confidence.Norm, якщо можливо.

Функція Confidence Excel використовує нормальний розподіл для обчислення довірчого значення, яке можна використовувати для побудови довірчого інтервалу для середнього значення сукупності, заданої ймовірності та розміру вибірки. Передбачається, що стандартне відхилення сукупності відомо.

синтаксис

= CONFIDENCE( alpha, standard_dev, size )

теми

• alfa: Рівень значущості (= 1 – рівень достовірності). (Наприклад, рівень значущості 0,05 відповідає 95% рівню достовірності).
• standard_dev: стандартне відхилення сукупності.
• size: розмір вибіркової сукупності.

Щоб обчислити довірчий інтервал для середнього значення генеральної сукупності, отримане значення довіри потрібно додати до середнього значення вибірки та відняти від нього. Що означає. для вибіркового середнього x:

Confidence Interval =   x   ±   CONFIDENCE

приклад

У наведеній нижче електронній таблиці функція достовірності Excel використовується для обчислення довірчого інтервалу зі значущістю 0,05 (тобто 95% рівня довіри) для середнього значення вибірки 100 зростів чоловіків. Середнє значення вибірки становить 1,8 метра, а стандартне відхилення становить 0,07 метра.

Попередня функція повертає значення довіри 0,013719748

Таким чином, довірчий інтервал становить 1,8 ± 0,013719748, що еквівалентно діапазону між 1,786280252 і 1,813719748

CONFIDENCE.NORM

У статистиці довірчий інтервал — це діапазон, у межах якого ймовірно потрапляє параметр генеральної сукупності за заданої ймовірності.

Наприклад. Для заданої генеральної сукупності та ймовірності 95% довірчий інтервал – це діапазон, у якому параметр генеральної сукупності з вірогідністю 95% потрапляє.

Зверніть увагу, що точність довірчого інтервалу залежить від того, чи генеральна сукупність має нормальний розподіл.

Функція Confidence.Norm Excel використовує нормальний розподіл для обчислення довірчого значення, яке можна використовувати для побудови довірчого інтервалу для середнього значення сукупності, заданої ймовірності та розміру вибірки. Передбачається, що стандартне відхилення сукупності відомо.

синтаксис

= CONFIDENCE.NORM( alpha, standard_dev, size )

теми

• alfa: Рівень значущості (= 1 – рівень достовірності). (Наприклад, рівень значущості 0,05 відповідає 95% рівню достовірності).
• standard_dev: стандартне відхилення сукупності.
• size: розмір вибіркової сукупності.

Щоб обчислити довірчий інтервал для середнього значення генеральної сукупності, отримане значення довіри потрібно додати до середнього значення вибірки та відняти від нього. Що означає. для вибіркового середнього x:

Confidence Interval =   x   ±   CONFIDENCE

приклад

У наведеній нижче електронній таблиці функція достовірності Excel використовується для обчислення довірчого інтервалу зі значущістю 0,05 (тобто 95% рівня довіри) для середнього значення вибірки 100 зростів чоловіків. Середнє значення вибірки становить 1,8 метра, а стандартне відхилення становить 0,07 метра.

Попередня функція повертає значення довіри 0,013719748

Таким чином, довірчий інтервал становить 1,8 ± 0,013719748, що еквівалентно діапазону між 1,786280252 і 1,813719748

Ercole Palmeri