Припустімо, вам потрібно дізнатися, скільки разів певний текст або числове значення зустрічається в діапазоні клітинок. Наприклад:
-
Якщо діапазон, наприклад A2:D20, містить числові значення 5, 6, 7 і 6, то число 6 зустрічається два рази.
-
Якщо стовпець містить "Пустовіт", "Додсворт", "Додсворт" і "Додсворт", то "Додсворт" зустрічається тричі.
Існує кілька способів, за допомогою яких можна підрахувати частоту появи значення.
Скористайтеся функцією COUNTIF , щоб обчислити, скільки разів певне значення відображається в діапазоні клітинок.
Докладні відомості див. в статті Функція COUNTIF.
Функція COUNTIFS подібна до функції COUNTIF з одним важливим винятком: функція COUNTIFS дає змогу застосовувати умови до клітинок у кількох діапазонах і підраховувати кількість разів, коли виконуються всі умови. З функцією COUNTIFS можна використовувати до 127 пар діапазонів і умов.
Синтаксис функції COUNTIFS:
COUNTIFS(діапазон_умови1, умова1, [діапазон_умови2, умова2],…)
Див. наведений нижче приклад.
Докладні відомості про використання цієї функції для підрахунку частоти появи в кількох діапазонах із кількома різними умовами див. в статті COUNTIFS (функція COUNTIFS).
Припустімо, потрібно визначити, скільки продавців продавали конкретний товар у певному регіоні, або скільки товару більше певного обсягу продав конкретний продавець. Для цього можна скористатися функціями IF і COUNT одночасно; тобто спочатку використовується функція IF для перевірки умови, і потім, якщо результат функції IF – позитивний, функція COUNT використовується для підрахунку клітинок.
Примітки.:
-
Формули в цьому прикладі необхідно вводити як формули масивів.
-
Якщо ви маєте поточну версію Microsoft 365, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву.
-
Якщо ви відкрили цю книгу в новіших версіях Excel для Windows або Excel для Mac і хочете змінити формулу або створити подібну формулу, натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter , щоб формула повернула очікувані результати.
-
-
Для прикладу формул, які потрібно використовувати, другим аргументом для функції IF має бути число.
Докладні відомості про ці функції див. в статті Функція COUNT і функція IF .
У прикладах нижче функції IF і SUM використовуються разом. Функція IF спочатку перевіряє значення в деяких клітинках, а потім функція SUM підсумовує значення, які пройшли перевірку з істинним результатом.
Примітки.: Формули в цьому прикладі необхідно вводити як формули масивів.
-
Якщо ви маєте поточну версію Microsoft 365, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву.
-
Якщо ви відкрили цю книгу в новіших версіях Excel для Windows або Excel для Mac і хочете змінити формулу або створити подібну формулу, натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter , щоб формула повернула очікувані результати.
Приклад 1
У наведеній вище функції зазначено, якщо C2:C7 містить значення Пустовіт і Додсворт, то функція SUM має відобразити суму записів, у яких виконується умова. Формула знаходить три записи для Пустовіту та один для Додсворта в цьому діапазоні та відображає 4.
Приклад 2
У наведеній вище функції зазначено, якщо D2:D7 містить значення, менші за 9000 $, або більші за 19 000 грн. тоді функція SUM має відобразити суму всіх записів, у яких виконується умова. Формула знаходить два записи D3 та D5 зі значеннями, меншими за 9000 $, а потім D4 та D6 зі значеннями, більшими за 19 000 грн, і відображає результат 4.
Приклад 3
У наведеній вище функції зазначено, якщо D2:D7 має рахунки Пустовіт менш ніж за 9000 грн., тоді функція SUM має відобразити суму записів, у яких виконується умова. Формула знаходить, що C6 відповідає умові, і відображає 1.
За допомогою зведеної таблиці можна відобразити підсумки та підрахувати кількість унікальних значень. Зведена таблиця – це інтерактивний засіб, що допоможе підсумувати великі обсяги даних. Його можна використовувати, щоб згортати й розгортати рівні даних, відображаючи лише потрібну інформацію зі зведеними даними. Крім того, можна перемістити рядки до стовпців або стовпців до рядків ("зведення"), щоб дізнатися, скільки разів значення зустрічається у зведеній таблиці. Розгляньмо зразок сценарію роботи з електронною таблицею "Продажі", де можна підрахувати кількість значень продажів у полях "Гольф" і "Теніс" для певних кварталів.
-
Введіть наведені нижче дані в електронну таблицю Excel.
-
Виберіть A2:C8
-
Виберіть Вставлення > Зведена таблиця.
-
У діалоговому вікні Створення зведеної таблиці виберіть пункт Вибрати таблицю або діапазон, а потім – Новий аркуш, а потім натисніть кнопку OK.
На новому аркуші створюється пуста зведена таблиця.
-
В області Поля зведеної таблиці виконайте такі дії:
-
Перетягніть поле Sport до області Рядки .
-
Перетягніть елемент Квартал до області Стовпці .
-
Перетягніть елемент Збут до області Значення .
-
Повторіть крок c.
Ім'я поля відображається як SumofSales2 як у зведеній таблиці, так і в області значень.
На цьому етапі область полів зведеної таблиці має такий вигляд:
-
В області Значення виберіть розкривний список поруч із пунктом SumofSales2 і виберіть Параметри значення поля.
-
У діалоговому вікні Параметри значення поля виконайте такі дії:
-
У розділі Summarize value field by (Підсумувати значення за ) виберіть Count (Кількість).
-
У полі Настроюване ім'я змініть ім'я на Кількість.
-
Натисніть кнопку OK.
-
Зведена таблиця відображає кількість записів для гольфу та тенісу в кварталі 3 та 4 кварталі разом із показниками продажів.
-
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Додаткові відомості
Способи уникнення недійсних формул
Виявлення та виправлення помилок у формулах