Статистичні функції Excel: Підручник із прикладами для дослідження, частина четверта

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

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

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

Приблизний час читання: 18 хвилин

Функції пошуку

MAX

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

синтаксис

= MAX(number1, [number2], …)

предметів

• number1:  число, клітинку, що містить число, або діапазон клітинок, що містять числа, з яких потрібно отримати найбільше число.
• [number2]:  число — це клітинка, яка містить число або діапазон клітинок, що містять числа, з яких потрібно отримати найбільше число.

приклад

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

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

Примітка: Ви також можете ввести число, використовуючи подвійні лапки.

У наступному прикладі ми посилалися на діапазон, і результат повернув 1861 як найбільше значення. Ви також можете посилатися на масив.

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

MAXA

Функція Excel Maxa дуже схожий на Функція Excel Max.

Єдина різниця між двома функціями виникає, коли аргумент надається функції як посилання на комірку або масив комірок.

Функція Max ігнорує логічні та текстові значення під час функції Maxa враховується логічне значення TRUE як 1, логічне значення FALSE як 0, а текстові значення як 0.

Функція MAXA Excel повертає найбільше значення з наданого набору числових значень, підраховуючи текст і логічне значення FALSE як значення 0 і підрахунок логічного значення TRUE як значення 1.

синтаксис

= MAXA(number1, [number2], …)

предметів

• number1:  число (або масиви числових значень), комірка, що містить число, або діапазон комірок, що містять числа, з яких потрібно отримати найбільше число.
• [number2]:  число — це комірка, яка містить число (або масиви числових значень) або діапазон комірок, що містять числа, з яких потрібно отримати найбільше число.

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

Есемпі

Приклад 1

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

Приклад 2

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

Зверніть увагу, що значення TRUE у комірці A1 електронної таблиці функція розглядає як числове значення 1 Maxa. Тому це найбільше значення в діапазоні A1-A3.

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

Помилка функції MAXA

Якщо ви отримуєте помилку від функції Maxa Excel, ймовірно, це помилка #VALORE!: виникає, якщо значення надаються безпосередньо функції Maxa вони не числові.

MAXIFS

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

синтаксис

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

предметів

• max_range:  Масив числових значень (або діапазон комірок, що містять числові значення), з якого потрібно повернути максимальне значення, якщо критерії виконуються.
• criteria_range1:  масив значень (або діапазон клітинок, що містять значення) для перевірки criteria1 .(Цей масив має бути такої ж довжини, як max_range).
• criteria1: Умова для перевірки щодо значень у criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: додаткові додаткові масиви значень для перевірки та відповідні умови для перевірки.

Функція Maxifs може обробляти до 126 пар тем criteria_range e criteria.

Кожен із запропонованих критеріїв може бути:

• числове значення (яке може бути цілим числом, десятковим числом, датою, часом або логічним значенням) (наприклад, 10, 01/01/2017, TRUE)

або

• текстовий рядок (наприклад, «Ім’я», «Мercoleз")

або

• вираз (наприклад, “>1”, “<>0”).

Ней criteria до тексту можна використовувати символи підстановки:

• ? щоб відповідати будь-якому окремому символу
• * для відповідності будь-якій послідовності символів.

Якщо criteria є текстовим рядком або виразом, його потрібно надати функції Maxifs в лапках.

Функція Maxifs Він не чутливий до регістру. Тому, наприклад, при порівнянні значень в criteria_range з i criteria, текстові рядки “TEXT"А"text» вважатимуться рівними.

Функція Maxifs його вперше було представлено в Excel 2019, тому він недоступний у попередніх версіях Excel.

Есемпі

Електронна таблиця нижче показує квартальні дані про продажі для 3 торгових представників.

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

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

Приклад 1

Щоб знайти максимальний показник продажів протягом першого кварталу:

=MAXIFS( D2:D13, A2:A13, 1 )

що дає результат  456.000 XNUMX$.

У цьому прикладі Excel Maxifs визначає рядки, де значення в стовпці A дорівнює 1, і повертає максимальне значення з відповідних значень у стовпці D.

Тобто функція знаходить максимальне зі значень $223.000 125.000, $456.000 2 і $3 4 (з комірок DXNUMX, DXNUMX і DXNUMX).

Приклад 2

Знову ж таки, використовуючи електронну таблицю даних вище, ми також можемо скористатися функцією Maxifs, щоб знайти максимальну цифру продажів для «Джеффа» протягом кварталів 3 і 4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Ця формула повертає результат  310.000 XNUMX$ .

У цьому прикладі Excel Maxifs визначає рядки, в яких:

• Значення в стовпці A більше 2

E

• Запис у стовпці C дорівнює «Джефф»

і повертає максимальне з відповідних значень у стовпці D.

Тобто ця формула знаходить максимум зі значень $310.000 261.000 і $8 11 (з комірок DXNUMX і DXNUMX).

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

Помилка функції MAXIFS

Якщо ви отримуєте помилку від функції Excel Maxifs, імовірно, це буде одне з наступного:

#VALUE!: Перевіряє наявність масивів max_range e criteria_range що постачаються, не всі мають однакову довжину.

@NAME?: виникає, якщо ви використовуєте старішу версію Excel (до 2019 року), яка не підтримує цю функцію Maxifs.

MIN

Функція MIN це функція пошуку, яка повертає найнижче значення зі списку значень. MIN означає мінімум, і коли ви вказуєте список значень, він шукає найнижче значення в ньому та повертає це значення в результаті.

синтаксис

= MIN(number1, [number2], …)

предметів

• number1:  число, комірка, що містить число, або діапазон комірок, що містять числа, з яких потрібно отримати найменше число.
• [number2]:  число, комірка, що містить число, або діапазон комірок, що містять числа, з яких потрібно отримати найменше число.

приклад

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

Ви також можете ввести число, використовуючи подвійні лапки. Тепер у наступному прикладі ми посилаємося на діапазон і повертаємо результат 1070.

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

MINA

Функція Excel MINA дуже схожий на Функція Excel MIN.

Єдина різниця між двома функціями виникає, коли аргумент надається функції як посилання на комірку або масив комірок.

У цьому випадку функція MIN ігнорує логічні та текстові значення під час функції MINA враховується логічне значення TRUE як 1, логічне значення FALSE як 0, а текстові значення як 0.

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

синтаксис

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

предметів

• number1:  число, комірка, що містить число, або діапазон комірок (або масивів числових значень), що містять числа, з яких потрібно отримати найменше число.
• [number2]:  число, комірка, що містить число, або діапазон комірок (або масивів числових значень), що містять числа, з яких потрібно отримати найменше число.

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

Есемпі

Приклад 1

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

Приклад 2

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

Майте на увазі, що значення TRUE в камері A1 електронної таблиці функція розглядає як числове значення 1 MINA. Тому це найменше значення в діапазоні A1-A3.

Ще приклади функції Excel MINA надаються на Веб-сайт Microsoft Office .

Помилка функції MINA

Якщо ви отримуєте помилку від функції MINA Excel, ймовірно, це помилка #VALORE!. Виникає, якщо значення, надані функції MINA, не є числовими.

MINIFS

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

синтаксис

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

предметів

• min_range:  Масив числових значень (або діапазон комірок, що містять числові значення), з якого потрібно повернути максимальне значення, якщо критерії виконуються.
• criteria_range1:  масив значень (або діапазон клітинок, що містять значення) для перевірки criteria1 .(Цей масив має бути такої ж довжини, як min_range ).
• criteria1: Умова для перевірки щодо значень у criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: додаткові додаткові масиви значень для перевірки та відповідні умови для перевірки.

Функція Minifs може обробляти до 126 пар тем criteria_range e criteria.

Кожен із запропонованих критеріїв може бути:

• числове значення (яке може бути цілим числом, десятковим числом, датою, часом або логічним значенням) (наприклад, 10, 01/01/2017, TRUE)

або

• текстовий рядок (наприклад, «Ім’я», «Мercoleз")

або

• вираз (наприклад, “>1”, “<>0”).

Ней criteria до тексту можна використовувати символи підстановки:

• ? щоб відповідати будь-якому окремому символу
• * для відповідності будь-якій послідовності символів.

Якщо criteria є текстовим рядком або виразом, його потрібно надати функції Minifs в лапках.

Функція Minifs Він не чутливий до регістру. Тому, наприклад, при порівнянні значень в criteria_range з i criteria, текстові рядки “TEXT» і «текст» вважатимуться одним і тим же.

Функція Minifs його вперше було представлено в Excel 2019, тому він недоступний у попередніх версіях Excel.

Есемпі

Електронна таблиця нижче показує квартальні дані про продажі для 3 продавців.

Функція Minifs можна використовувати для визначення мінімального обсягу продажів для будь-якого кварталу, регіону чи торгового представника.

Це показано в наступних прикладах.

Приклад 1

Щоб знайти мінімальну цифру продажів протягом першого кварталу:

=MINIFS( D2:D13, A2:A13, 1 )

що дає результат  125.000 XNUMX$ .

У цьому прикладі Excel Minifs визначає рядки, де значення в стовпці A дорівнює 1, і повертає мінімальне значення з відповідних значень у стовпці D.

Тобто функція знаходить мінімальне зі значень $223.000 125.000, $456.000 2 і $3 4 (з комірок DXNUMX, DXNUMX і DXNUMX).

Приклад 2

Знову ж таки, використовуючи електронну таблицю даних вище, ми також можемо використовувати функцію Minifs щоб знайти мінімальну цифру продажів для «Джеффа» протягом кварталів 3 і 4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Ця формула повертає результат $261.000 .

У цьому прикладі Excel Minifs визначає рядки, в яких:

• Значення в стовпці A більше 2

E

• Запис у стовпці C дорівнює «Джефф»

і повертає мінімальне з відповідних значень у стовпці D.

Тобто ця формула знаходить мінімальне зі значень $310.000 261.000 і $8 11 (з комірок DXNUMX і DXNUMX).

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

Помилка функції MINIFS

Якщо ви отримуєте помилку від функції Excel Minifs, імовірно, це одна з таких причин:

• #VALORE! - Перевіряє наявність масивів min_range e criteria_range що постачаються, не всі мають однакову довжину.
• #NOME? – Виникає, якщо ви використовуєте старішу версію Excel (до 2019 року), яка не підтримує цю функцію Minifs.

LARGE

Функція Excel Large це функція пошуку, яка повертає k'-те найбільше значення з масиву числових значень.

синтаксис

= LARGE( array, k )

предметів

• масив – масив числових значень для пошуку k-го найбільшого значення.
• K – індекс, тобто функція повертає k-те найбільше значення зarray надається.

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

приклад

У наведеній нижче таблиці показано функцію Excel Large, який використовується для отримання 1-го, 2-го, 3-го, 4-го та 5-го найбільших значень із набору значень у клітинках A1-A5.

Деякі думки щодо прикладу електронної таблиці вище:

• У камері B1, де k дорівнює 1, функція Large виконує ту саму дію, що й Функція Excel Макс ;
• У камері B5, коли k встановлено на 5 (кількість значень у наданому масиві), функція Large виконує ту саму дію, що й Функція Excel Min .

Додаткову інформацію та приклади функції Excel Large можна знайти на Веб-сайт Microsoft Office .

Помилка функції LARGE

Якщо Excel Large повертає помилку, ймовірно, це одне з наступного:

• #NUM! – Виникає, якщо:
  • Надане значення k менше 1 або більше, ніж кількість значень у наданому масиві
    або
    L 'array надається порожній.
• #VALUE! – Виникає, якщо надане k не є числом.

Однак під час обчислення функції LARGE можуть виникнути помилки, навіть якщо надане значення k знаходиться між 1 і кількістю значень у наданому масиві. Можливою причиною може бути те, що функція Large ігнорує текстові значення, зокрема текстові представлення чисел у наданому масиві. Таким чином, ця проблема може виникнути, якщо значення в наданому масиві є текстовими представленнями чисел, а не фактичними числовими значеннями.

Рішення може бути досягнуто шляхом перетворення всіх значень масиву в числові значення. 

SMALL

Функція Excel Small — це функція пошуку, яка повертає k-те найменше значення з масиву числових значень.

синтаксис

= SMALL( array, k )

предметів

• array – Масив числових значень для пошуку k'-го найбільшого значення.
• K – індекс, тобто функція повертає k-те найбільше значення зarray надається.

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

приклад

У наведеній нижче таблиці показано функцію Excel Small, який використовується для отримання 1-го, 2-го, 3-го, 4-го та 5-го найменших значень із набору значень у клітинках A1-A5.

У прикладі необхідно врахувати, що:

• У клітинці B1, де k дорівнює 1, функція Small виконує ту саму дію, що й Функція Excel Min ;
• У клітинці B5, коли k встановлено на 5 (кількість значень уarray за умови ), функція Small виконує ту саму дію, що й Функція Max Excel .

Детальніше та приклади функції Excel Small надаються на Веб-сайт Microsoft Office .

Помилка функції SMALL

Якщо Excel SMALL повертає помилку, ймовірно, це одне з наступного:

• #NUM! – Виникає, якщо:
  • Надане значення k менше 1 або більше, ніж кількість значень у наданому масиві
    або
    Наданий масив порожній.
• #VALUE! – Виникає, якщо надане k не є числом.

Однак при обчисленні функції можуть виникнути помилки LARGE навіть якщо дане значення k знаходиться між 1 і кількістю значень уarray надається. Можливою причиною може бути те, що текстові значення, включаючи текстові представлення чисел у межахarray за умови, вони ігноруються функцією Large. Таким чином, ця проблема може виникнути, якщо значення вarray надаються текстові представлення чисел, а не фактичні числові значення.

Рішення можна отримати шляхом перетворення всіх значеньarray в числових значеннях. 

Пов'язані читання

• Статистичні функції Excel: навчальний посібник із прикладами, частина перша
• Діаграми Excel, що це таке, як створити діаграму та як вибрати оптимальну діаграму
• JSP Request, усі методи неявного об’єкта Request
• Управління проектами в навчальному досвіді
• Зведені таблиці: що це таке, як створити в Excel і Google. Підручник з прикладами

Ercole Palmeri