Підрахування частоти появи значення у програмі Excel

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Припустімо, потрібно дізнатися, скільки разів зустрічається той чи інший текст або число в діапазоні клітинок, наприклад:

  • якщо діапазон (наприклад A2:D20) містить числа 5, 6, 7 і 6, то число 6 зустрічається двічі;

  • якщо стовпець містить "Пустовіт", "Діденко", "Діденко" та "Діденко", то "Діденко" зустрічається тричі.

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

У цій статті

Підрахування частоти появи окремого значення за допомогою функції

Підрахування частоти появи за кількох умов із використанням функції COUNTIFS

Підрахування частоти появи за певної умови з одночасним використанням функцій COUNT та IF

Підрахування частоти появи кількох текстових або числових значень з одночасним використанням функцій SUM та IF

Підрахування частоти появи кількох значень за допомогою звіту зведеної таблиці

Підрахування частоти появи окремого значення за допомогою функції

Використовуйте функцію COUNTIF для виконання цього завдання.

Приклад

Скопіюйте дані з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат формул, виберіть їх, натисніть клавішу F2, а потім натисніть клавішу Enter. За потреби відрегулюйте ширину стовпців, щоб відобразити всі дані.

Торговий представник

Рахунок

Пустовіт

15 000

Пустовіт

9 000

Семенів

8 000

Семенів

20 000

Пустовіт

5 000

Діденко

22 500

Формула

Опис

Результат

'=COUNTIF(A2:A7,"Пустовіт")

Кількість записів Пустовіт (3)

=COUNTIF(A2:A7,"Пустовіт")

'=COUNTIF(A2:A7,A4)

Кількість записів Семенів (2)

=COUNTIF(A2:A7,A4)

'=COUNTIF(B2:B7,"< 20000")

Кількість значень рахунків, менших за 20 000 (4)

=COUNTIF(B2:B7,"< 20000")

'=COUNTIF(B2:B7,">="&B5)

Кількість значень рахунків, більших або рівних 20 000 (2)

=COUNTIF(B2:B7,">="&B5)

Докладні відомості про цю функцію див. в статті COUNTIF (функція COUNTIF).

На початок сторінки

Підрахування частоти появи за кількох умов із використанням функції COUNTIFS

Уперше представлена в програмі Excel 2007, функція COUNTIFS схожа на функцію COUNTIF, але має одну важливу відмінність: функція COUNTIFS дає змогу застосувати умови до кількох діапазонів клітинок і підрахувати кількість разів, коли ці умови виконуються. Функція COUNTIFS підтримує до 127 пар діапазонів і умов. Ця функція має такий синтаксис:

COUNTIFS(діапазон_умови1, умова1, [діапазон_умови2, умова2],…)

Приклад

Ідентифікаційний номер працівника

Номер регіону

Відділ

20552

2

Продажі

21268

2

Фінанси

23949

1

Адміністратор

24522

4

Адміністратор

28010

3

Інформаційні технології

29546

4

Продажі

31634

3

Інформаційні технології

32131

1

Інформаційні технології

35106

4

Фінанси

40499

1

Управління персоналом

42051

1

Продажі

43068

2

Управління персоналом

45382

2

Фінанси

47971

1

Інформаційні технології

Скільки працівників працює в регіоні № 2 й у відділі фінансів?

'=COUNTIFS(B2:B15,"2",C2:C15,"Фінанси")

=COUNTIFS(B2:B15,"2",C2:C15,"Фінанси")

Перший діапазон умов відповідає номерам регіонів, а другий – назвам відділів. Умова, яка застосовуються до першого діапазону умов – це «2», а умова, яка застосовується до другого діапазону умов – це «Фінанси». Функція COUNTIFS перевіряє, чи обидві ці умови задовольняються.

Докладні відомості про використання цієї функції для підрахунку частоти появи в кількох діапазонах із кількома різними умовами див. в статті COUNTIFS (функція COUNTIFS).

На початок сторінки

Підрахування частоти появи за певної умови з одночасним використанням функцій COUNT та IF

Припустімо, потрібно визначити, скільки продавців продавали конкретний товар у певному регіоні, або скільки товару більше певного обсягу продав конкретний продавець. Для цього можна скористатися функціями IF і COUNT одночасно; тобто спочатку використовується функція IF для перевірки умови, і потім, якщо результат функції IF – позитивний, функція COUNT використовується для підрахунку клітинок.

Приклади

Регіон

Торговий представник

Тип

Продажі

Південь

Пустовіт

Напої

3571

Захід

Давидова

Молочні продукти

3338

Схід

Семенів

Напої

5122

Північ

Семенів

Молочні продукти

6239

Південь

Діденко

Продукти

8677

Південь

Давидова

М’ясо

450

Південь

Давидова

М’ясо

7673

Схід

Семенів

Продукти

664

Північ

Давидова

Продукти

1500

Південь

Діденко

М’ясо

6596

Формула

Опис

Результат

=COUNT(IF((A2:A11="Південь")*(C2:C11="М’ясо"),D2:D11))

Кількість продажів м’яса в південному регіоні. (3)

=COUNT(IF((A2:A11="Південь")*(C2:C11="М’ясо"),D2:D11))

=COUNT(IF((B2:B11="Семенів")*(D2:D11>=1000),D2:D11))

Кількість продажів, сума яких перевищує 1 000 грн., здійснених торговим представником Семенів. (2)

=COUNT(IF((B2:B11="Семенів")*(D2:D11>=1000),D2:D11))

Примітка : 

  • Формули в цьому прикладі необхідно вводити як формули масивів. Якщо цю книгу відкрито у програмі Excel для настільних комп’ютерів і потрібно змінити формулу або створити схожу, натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter, щоб формула повернула очікуваний результат.

  • Для роботи з такими формулами необхідно, щоб другий аргумент функції IF був числом.

Функція COUNT підраховує кількість клітинок з числами, а також кількість чисел у межах списку аргументів. Функція IF повертає одне значення, якщо обчислене значення відповідає заданій умові – ІСТИНА, та інше значення, якщо обчислене значення не відповідає заданій умові – ХИБНІСТЬ.

Докладні відомості про ці функції див. в статтях COUNT (функція COUNT) і Функція IF.

На початок сторінки

Підрахування частоти появи кількох текстових або числових значень з одночасним використанням функцій SUM та IF

У прикладах нижче функції IF і SUM використовуються разом. Функція IF спочатку перевіряє значення в деяких клітинках, а потім функція SUM підсумовує значення, які пройшли перевірку з істинним результатом.

Приклади

Торговий представник

Рахунок

Пустовіт

15000

Пустовіт

9000

Семенів

8000

Семенів

20000

Пустовіт

5000

Діденко

22500

Формула

Опис

Результат

=SUM(IF((A2:A7="Пустовіт")+(A2:A7="Діденко"),1,0))

Кількість рахунків Пустовіт і Діденко (4)

=SUM(IF((A2:A7="Пустовіт")+(A2:A7="Діденко"),1,0))

=SUM(IF((B2:B7<9000)+(B2:B7>19000),1,0))

Кількість рахунків зі значеннями, меншими за 9 000 грн. або більшими за 19 000 грн.

=SUM(IF((B2:B7<9000)+(B2:B7>19000),1,0))

=SUM(IF(A2:A7="Пустовіт",IF(B2:B7<9000,1,0)))

Кількість рахунків Пустовіт зі значеннями, меншими за 9 000 грн.

=SUM(IF(A2:A7="Пустовіт",IF(B2:B7<9000,1,0)))

Примітка : Формули в цьому прикладі необхідно вводити як формули масивів. Якщо цю книгу відкрито у програмі Excel для настільних комп’ютерів і потрібно змінити формулу або створити схожу, натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter, щоб формула повернула очікуваний результат.

На початок сторінки

Підрахування частоти появи кількох значень за допомогою звіту зведеної таблиці

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

Приклад джерела даних і створеного на його основі звіту зведеної таблиці

1.    Вихідні дані з аркуша.

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

3.    Весь звіт зведеної таблиці.

4.    Зведення вихідних значень у клітинках C2 й C8 із вихідних даних.

Створення звіту зведеної таблиці

  1. Виберіть стовпець, який містить дані.
    Переконайтеся, що стовпець має заголовок.

  2. На вкладці Вставлення в групі Таблиці натисніть кнопку Зведена таблиця.
    Відкриється діалогове вікно Створення зведеної таблиці.

  3. Установіть перемикач Виберіть таблицю або діапазон.

  4. Розташуйте звіт зведеної таблиці на новому аркуші, починаючи із клітинки A1, за допомогою команди Новий аркуш.

  5. Натисніть кнопку ОК.
    Пустий звіт зведеної таблиці додається в указаному розташуванні зі списком полів зведеної таблиці.

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

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

Примітка : Якщо в даних містяться числа, то у звіті зведеної таблиці записи підсумовуються, а не підраховуються. Щоб змінити функцію зведення Sum на функцію зведення Count, виберіть клітинку у відповідному стовпці, а потім на вкладці Аналізувати у групі Активне поле виберіть команду Параметри поля, відкрийте вкладку Операція, виберіть розділ Кількість, і натисніть кнопку ОК.

На початок сторінки

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×