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

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

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

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

  • якщо діапазон (наприклад 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")

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

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

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

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

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

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

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

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

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

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

Приклад

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

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

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

Відділ

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 використовується для підрахунку клітинок.

Приклади

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

Область

Продавець

Символи

Продажі:

Південь

Пустовіт

Напої

3571

Захід

Давидова

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

3338

Схід

Семенів

Напої

5122

Північ

Семенів

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

6239

Південь

Діденко

Овочі

8677

Південь

Давидова

М’ясо

450

Південь

Давидова

М’ясо

7673

Схід

Семенів

Овочі

664

Північ

Давидова

Овочі

1500

Південь

Діденко

М’ясо

6596

Формула

Опис

Результат

' = COUNT (ЯКЩО ((A2:A11="South")*(C2:C11="Meat"),D2:D11))

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

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

' = COUNT (ЯКЩО ((B2:B11="Suyama") * (D2: D11 > = 1000), D2: D11))

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

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

Примітки : 

  • У цьому прикладі формули потрібно вводити як формули масивів. Якщо ви відкрили цієї книги у програмі Excel для Windows і Excel 2016 для Mac і хочете, щоб змінити формулу або створити формулу, схожі, натисніть клавішу F2і натисніть Сполучення клавіш Ctrl + Shift + Enter , щоб повернути результати формули, які ви очікуєте. У попередніх версіях програми Excel для Mac за допомогою КОМАНДА + Shift + Enter.

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

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

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

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

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

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

Приклади

Продавець

Рахунок

Пустовіт

15000

Пустовіт

9000

Семенів

8000

Семенів

20000

Пустовіт

5000

Діденко

22500

Формула

Опис

Результат

' = SUM (IF ((A2:A7="Buchanan")+(A2:A7="Dodsworth"),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 = "Пустовіт", ЯКЩО (B2: B7 < 9000,1,0)))

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

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

Примітка : У цьому прикладі формули потрібно вводити як формули масивів. Якщо ви відкрили цієї книги у програмі Excel для Windows і Excel 2016 для Mac і хочете, щоб змінити формулу або створити формулу, схожі, натисніть клавішу F2і натисніть Сполучення клавіш Ctrl + Shift + Enter , щоб повернути результати формули, які ви очікуєте. У попередніх версіях програми Excel для Mac за допомогою КОМАНДА + Shift + Enter.

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

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

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

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

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

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

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

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

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

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

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

  3. Натисніть кнопку Виберіть таблицю або діапазон.

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

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

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

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

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

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

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

Див. також

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Пошук і виправлення помилок у формулах

Сполучення клавіш у програмі Excel а також функціональні клавіші

Статистична функції (довідка)

Математичні та тригонометричні функції (довідка)

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

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

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

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

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

×