Поняття функції

Для здійснення статистичного аналізу даних в MS Excel використовують статистичні функції. Знаходяться ці функції в категорії Статистичні. Більшість функцій у цій категорії використовує вузьке коло спеціалістів, проте частина з них є досить часто вживаними. Так, до статистичних функцій належать уже відомі вам MAX, MIN, AVERAGE та COUNT. В таблиці 12.1 наведено ще деякі функції, які використовують при обчисленнях, з категорії Статистичні.

Таблиця 12.1. Функції з категорії Статистичні

Функція	В російському інтерфейсі	Призначення
COUNTA(значення1; значення2;...)	СЧЕТЗ	Визначення кількості непорожніх клітинок
COUNTIF(діапазон; критерій)	СЧЕТЕСЛИ	Визначення кількості клітинок, що відповідають заданому критерію
AVERAGEIF(діапазон; критерій; діапазон_середн)	СРЗНАЧЕСЛИ	Пошук середнього у діапазоні, що відповідає заданому критерію
RANK.AVG(число; посилання; порядок)	РАНГ.СР	Визначення рангу числа в переліку чисел
RANK.EQ(число; посилання; порядок)	РАНГ.РВ	Визначення рангу числа в переліку чисел

12.2.

Функція COUNTA

Вам уже знайома функція COUNT — вона визначає кількість числових значень у діапазоні. На відміну від неї, функція COUNTA визначає кількість не тільки числових, але й будь-яких інших значень, виключаючи порожні клітинки. Розглянемо використання даної функції, виконавши вправу.

ВПРАВА 12.1

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

в заданій кількості учнів). Буква класу визначає його профіль (математичний, фізичний, хімічний та економічний). У діапазоні F3:F7 визначити кількість класів кожної паралелі, а у діапазоні B8:E8 кількість класів кожного профілю.

1. Завантажте файл BnpaBa_12_1.xlsx з матеріалів до даного параграфа.

2. Визначте кількість класів у кожній паралелі.

a. Виділіть клітинку F3.

b. На вкладці Формули в меню інструменту та Інші функції оберіть категорію Статистична.

c. Оберіть функцію COUNTA.

d. В полі аргументу значення 1 виділіть необхідний діапазон В3:Е3.

e. Натисніть ОК. В результаті буде підраховано кількість 7-х класів.

f. Скопіюйте одержану формулу нижче, використовуючи автозапо-внення.

3. Обчисліть кількість класів у профілі.

a. Оберіть клітинку B8.

b. У рядку формул уведіть дорівнює та почніть уведення функції. Оберіть функцію COUNTA у списку.

c. Після дужки вкажіть діапазон В3:В7.

d. Закрийте дужку та натисніть Enter. В результаті кількість математичних класів буде підраховано.

e. Знайдіть кількість класів в інших профілях, застосувавши до формул спосіб автозаповнення.

5²-³v

Функція COUNIF

Ще одна досить поширена у використанні функція COUNTIF або СЧЕТЕСЛИ. Вона визначає кількість клітинок, що відповідають заданому критерію. Розглянемо практичну вправу з її використанням.

ВПРАВА 12.2

Завдання. Дано таблицю, що містить інформацію про результати участі учнів в олімпіаді з математики. Визначити кількість десятикласників, що брали участь в олімпіаді та кількість переможців олімпіади. (Переможцями є учні, що одержали більше 16 балів.)

А 8 С D Е

1 Прізвище Клас Кількість балів Нижня межа переможців

2 Іванов 10 16 16

3 Іііевцов 9 25

4 Бондаренко 11 ЗО

5 Ігнатов 10 18

6 Крохмаленко 11 24

7 Васильєв 10 13

8

9 Кількість десятикласників

10 Кількість переможців

1. Завантажте файл BnpaBa_12_2.xlsx з матеріалів до даного параграфа.

2. Визначте кількість десятикласників.

a. Виділіть клітинку В9.

b. Перейдіть до категорії Статистична.

c. Оберіть функцію COUNTIF.

d. Як перший аргумент установіть діапазон класів, а саме, В2:В7.

e. Другим аргументом є критерій. У даному випадку це буде число 10. Уведіть число 10.

f. Натисніть ОК. В результаті кількість десятикласників буде знайдено.

3. У клітинці В10 знайдіть кількість переможців.

a. У рядку формул уведіть дорівнює та почніть уводити назву функції. Оберіть зі списку функцію COUNTIF.

b. Як перший аргумент укажіть діапазон, що містить бали, а саме С2:С7. Установіть крапку з комою.

c. Як другий аргумент має бути умова, що значення є більше 16. Запишіть цю умову (">16").

Примітка. Зверніть увагу, що умова, в якій використовується знак порівняння, має бути записана в лапках.

d. Закрийте дужку та натисніть Enter. В результаті кількість переможців буде знайдено.

Розглянемо випадок, коли нижня межа переможця вказана у певній клітинці (наприклад, Е2) і може за певних обставин змінюватися. В цьому випадку критерій підрахунку має містити посилання на дану клітинку.

4. Змініть умову визначення кількості переможців наступним чином: ">"&E2. Натисніть Enter та переконайтеся, що кількість переможців для введеної до клітинки Е2 умови знайдено.

5. Змініть значення умови у клітинці Е2. Уведіть, наприклад, 20. Переконайтеся, що кількість переможців буде змінено.

12.47

Функція AVERAGEIF

Схожою до функції COUNT за своїм призначенням є функція AVERAGEIF (СРЗНАЧЕСЛИ). З нє

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

ВПРАВА 12.3

Завдання. В таблиці подано оцінки учнів. Визначити їхній середній бал без урахування оцінок з фізкультури.

1. Завантажте файл BnpaBa_12_3.xlsx з матеріалів до даного параграфа.

2. Виділіть клітинку І2.

3. В категорії Статистична оберіть функцію AVERAGEIF.

4. Як перший аргумент укажіть діапазон пошуку критерію $С$1:$Н$1. Оскільки цей діапазон буде сталим для кожної наступної формули, фіксуємо його.

5. Як другий аргумент уведіть умову «не дорівнює фізкультура» ("<>фізкультура").

6. Третім аргументом уведіть діапазон оцінок учня С2:Н2.

7. Натисніть ОК.

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

12.5.

Функції RANK.AVG та RANK.EQ.

Наступні дві функції RANK.AVG та RANK.EQ схожі за своїм призначенням. Вони визначають ранг числа в переліку чисел.

Ранг числа — це його порядок відносно інших значень у списку.

Якщо сортувати список, то ранг буде його позицією. Цю функцію часто використовують для визначення рейтингу навчання учнів, виступу спортсменів на змаганнях тощо. Так, учень з найкращим середнім балом буде першим за рейтингом і матиме ранг, що дорівнює одиниці. Відповідно, спортсмен, що має найменший час на дистанції з бігу, також буде першим, і ранг його дорівнюватиме одиниці (рис. 12.1).

Аргументами даних функцій є: число, для якого визначається ранг;

посилання, що містить діапазон чисел, серед яких визначається ранг;

порядок, який визначає спосіб упорядкування даних.

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

Чим же відрізняються функції RANK.AVG та RANK.EQ? Відмінність цих функцій у тому, що якщо в діапазоні посилань будуть однакові значення, то перша функція визначить середній ранг, а друга функція визначить вищий ранг. На рис. 12.2 видно, що в першому

випадку це 1,5 (середнє значення між 1 та 2). При застосуванні другої формули для обох випадків цей ранг дорівнює 1. В обох випадках наступне за величиною значення одержує ранг 3.

Розглянемо, як знаходити ранг, виконавши практичну вправу.

ВПРАВА 12.4

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

1. Завантажте файл BnpaBa_12_4.xlsx

з матеріалів до даного параграфа.

2. Виділіть клітинку D2.

3. В категорії Статистична оберіть функцію RANK.EQ.

4. Як перший аргумент укажіть значення довжини стрибка першого спортсмена, а саме, клітинку С2.

5. Другим аргументом є посилання на значення довжин стрибків усіх спортсменів, отже, вкажіть діапазон $С$2:$С$11. Діапазон зафіксовано, оскільки він є сталим для кожної наступної формули.

6. Третій аргумент можна не вказувати, оскільки першим має бути результат у спортсмена, що має найбільше значення. Натисніть ОК.

7. Скопіюйте формулу нижче, застосувавши автозаповнення. В результаті ви маєте одержати рейтинг спортсменів.

Контрольні запитання та завдання

1. Які ви знаєте статистичні функції?

2. Що є спільного та відмінного у функцій COUNT та COUNTA?

3. З допомогою якої функції можна визначити рейтинг успішності учнів?

4. Чим відрізняються функції RANK.AVG та RANK.EQ?

Питання для роздумів

1*. Дано прайс-лист, що містить вартості комп’ютерних деталей. З допомогою якої функції можна визначити кількість деталей, вартість яких більша за 100 грн?

2*. З допомогою яких статистичних функцій (крім MAX) можна знайти максимальне значення числа з наданого переліку чисел.

Завдання для досліджень

1*. Дослідіть призначення статистичних функцій COUNTIFS та AVERAGEIFS. Наведіть можливі приклади їхнього використання.

 

 

Це матеріал з підручника Інформатика 9 клас Казанцева, Стеценко 2022