Спосіб підрахунку значень на аркуші

Діалогове вікно «Надіслати до Microsoft Word» Підрахунок – це невід’ємна складова аналізу будь-яких даних, наприклад кількості працівників організації або одиниць товару, проданого за кожен квартал. У програмі Excel є кілька способів обчислити кількість клітинок, рядків або стовпців із даними.

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

Підрахунок не слід плутати з підсумуванням. Докладні відомості про підсумування даних у клітинках, рядках або стовпцях див. в статті Способи підсумування значень на аркуші.

У цій статті

Простий підрахунок

Відео "Підрахунок клітинок за допомогою рядка стану в програмі Excel"

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

Підрахунок клітинок у діапазоні за допомогою функції COUNT

Підрахунок клітинок у стовпці за допомогою команди "Проміжні підсумки" групи "Структура"

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

Підрахунок клітинок у списку або стовпці таблиці Excel за допомогою функції SUBTOTAL

Підрахунок на основі однієї або кількох умов

Відео "Використання функцій COUNT, COUNTIF і COUNTA"

Підрахунок клітинок у діапазоні на основі однієї умови за допомогою функції COUNTIF

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

Підрахунок клітинок у діапазоні на основі кількох умов за допомогою функції COUNTIFS або поєднання функцій COUNT та IF

Підрахунок даних, серед яких є пусті клітинки

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

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

Підрахунок пустих клітинок у суцільному діапазоні за допомогою функції COUNTBLANK

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

Підрахунок унікальних значень

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

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

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

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

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

Особливі випадки (підрахунок усіх клітинок, кількості слів)

Підрахунок усіх клітинок у діапазоні за допомогою функцій ROWS і COLUMNS

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

Відображення підрахунків і кількостей у рядку стану

Простий підрахунок

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

Крім того, кількість виділених клітинок може бути зазначено в рядку стану програми Excel. Перегляньте демонстраційне відео нижче, щоб швидко дізнатися про використання рядка стану. Докладні відомості див. також у розділі Відображення підрахунків і кількостей у рядку стану. Значення в рядку стану корисні, коли потрібно швидко проаналізувати дані, але немає часу вводити формули.

Відео "Підрахунок клітинок за допомогою рядка стану в програмі Excel"

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

Створення завдання

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

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

Скористайтеся функцією "Кількість" команди "Автосума". Для цього виберіть діапазон клітинок принаймні з одним числовим значенням, а потім на вкладці Основне в групі Редагування натисніть стрілку біля кнопки Автосума. Виберіть пункт Кількість. Програма Excel поверне кількість числових значень діапазону в клітинці, суміжній із виділеним діапазоном. Зазвичай цей результат відображається в клітинці праворуч від горизонтального діапазону або під вертикальним діапазоном.

Використання елемента "Кількість" команди "Автосума"

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

Підрахунок клітинок у діапазоні за допомогою функції COUNT

Щоб підрахувати числові значення в діапазоні, скористайтеся функцією COUNT у формулі. У прикладі нижче діапазон A2:A5 містить три числа (5, 32 та 10) і одне текстове значення ("привіт"). Функція COUNT у формулі має такий вигляд: =COUNT(A2:A5). У результаті отримаємо 3, кількість числових значень у діапазоні.

1

2

3

4

5

6

A

Зразок значення

5

32

привіт

10

=COUNT(A2:A5)

Докладні відомості див. в розділі "Підрахунок клітинок, які містять числа в окремому рядку або стовпці" статті Функція COUNT.

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

Підрахунок клітинок у стовпці за допомогою команди "Проміжні підсумки" групи "Структура"

Щоб згрупувати й підсумувати дані в стовпці, скористайтеся командою Проміжні підсумки (вкладка Дані, група Структура).

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

Команда "Проміжні підсумки" групує дані в структуру

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

Докладні відомості див. в цих статтях:

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

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

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

Щоб швидко створити зведену таблицю, виберіть клітинку в діапазоні даних або таблиці Excel, а потім на вкладці Вставлення в групі Таблиці натисніть кнопку Зведена таблиця.

Зверніть увагу: у прикладі нижче дані про збут складаються з багатьох рядків (насправді їх 40, але на знімку екрана показано лише їхню частину). Дані не підсумовано, і для них не вказано загальний і проміжний підсумки.

Невеликий триб "Настройки", що відображається замість елемента "Настроювання сайту".

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

Математичний символ

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

Докладні відомості, які допоможуть почати роботу, див. в таких статтях:

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

Підрахунок клітинок у списку або стовпці таблиці Excel за допомогою функції SUBTOTAL

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

За допомогою функції SUBTOTAL можна вказати, чи включати в підрахунок приховані рядки. Ця функція завжди ігнорує відфільтровані рядки.

Наприклад, якщо застосувати функцію до семи значень у таблиці нижче (клітинки A2–A8), отримаємо результат 7.

1

2

3

4

5

6

7

8

9

A

Продано одиниць

25

8

12

32

11

40

16

=SUBTOTAL(2;A2:A8)

Частина формули "2" вказує на те, що функція має використовувати функцію SUM, щоб повернути кількість значень у діапазоні A2:A8, і всі приховані рядки мають враховуватися. Кількість (результат у клітинці A9) – 7.

Якщо рядки 4, 5 і 6 приховано та їх не потрібно включати в підрахунок, скористайтеся функцією SUBTOTAL дещо інакше. Замість "2" вкажіть у формулі "102". Таким чином ви накажете програмі Excel ігнорувати приховані рядки. Аркуш може мати приблизно такий вигляд (якщо приховано рядки 4, 5 і 6):

1

2

3

7

8

9

A

Продано одиниць

25

8

40

16

=SUBTOTAL(102;A2:A8)

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

Докладні відомості див. в статті Функція SUBTOTAL.

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

Підрахунок на основі однієї або кількох умов

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

Відео "Використання функцій COUNT, COUNTIF і COUNTA"

Перегляньте відео нижче, щоб дізнатися, як за допомогою функції COUNT і функцій COUNTIF та COUNTA обчислити тільки кількість клітинок, які відповідають указаним умовам.

Створення завдання

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

Підрахунок клітинок у діапазоні на основі однієї умови за допомогою функції COUNTIF

Щоб обчислити кількість клітинок, які відповідають одній умові (так званому критерію), скористайтеся функцією COUNTIF. У прикладі нижче функція знаходить кількість значень у діапазоні A2:A8, які перевищують 20, тобто повертає значення 3. Зверніть увагу: умову ">20" потрібно взяти в лапки.

1

2

3

4

5

6

7

8

9

A

Продано одиниць

25

8

12

32

11

40

16

=COUNTIF(A2:A8;">20")

Докладні відомості див. в статті Функція COUNTIF.

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

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

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

Використовуйте функцію DCOUNT, коли у вас є список стовпців і вам простіше визначити умови в окремому діапазоні клітинок, ніж використовувати вкладену функцію.

У прикладі нижче потрібно визначити кількість місяців, починаючи з березня 2008 р., за які було продано понад 400 одиниць товару. Проаналізувавши таблицю, можна побачити, що цій умові відповідають два місяці: квітень (442) і червень (405).

Примітка : Наведені нижче вказівки не зовсім зрозумілі, проте ефективні. Важливо точно дотримуватися їх і упорядковувати дані на аркуші, як показано нижче. Ви можете скопіювати дані з таблиці та вставити їх на аркуш у клітинку A1. Результат має з’явитися в клітинці B13.

Додайте ще один діапазон клітинок, які впорядковано так само, до даних про збут, тобто в клітинки A1–B7. Додатковий діапазон складається з клітинок A10–B11 і містить такі самі підписи стовпців ("Продано одиниць" і "Завершення місяця") і умову в рядку безпосередньо під кожним підписом стовпця (клітинки A11 і B11).

Потім введіть формулу в будь-яку пусту клітинку (у цьому прикладі ми ввели її в клітинку B13). Функція DCOUNT у формулі має такий вигляд: =DCOUNT(A1:B7;;A10:B11).

1

2

3

4

5

6

7

8

9

10

11

12

13

A

B

Продано одиниць

Завершення місяця

339

31.01.2008

270

29.02.2008

314

31.03.2008

442

30.04.2008

336

31.05.2008

405

30.06.2008

Продано одиниць

Завершення місяця

=">400"

=">=31.03.2008"

=DCOUNT(A1:B7;;A10:B11)

Функція DCOUNT перевіряє дані в діапазоні A2–A7, застосовує умови, зазначені в клітинках A11 і B11, і повертає 2, тобто кількість рядків, які відповідають обом умовам (рядки 5 і 7).

Докладні відомості див. в статті Функція DCOUNT.

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

Підрахунок клітинок у діапазоні на основі кількох умов за допомогою функції COUNTIFS або поєднання функцій COUNT та IF

Скористайтеся функцією COUNTIFS або поєднанням функцій COUNT та IF.

Вкладки об’єктів у базі даних Access 2007

На цьому знімку екрана продемонстровано, як за допомогою функції COUNTIFS знайти автомобілі, потужність яких становить понад 250 кінських сил і середнє споживання пального – понад 25 миль на галон на автостраді. Функція повертає 2, кількість рядків, які відповідають обом умовам (рядки 3 та 4).

Примітка : Усі умови у функції COUNTIFS потрібно брати в лапки (""). Наприклад: "<250", ">25" або навіть "240".

Ви можете підрахувати, скільки разів одне значення з’являється в діапазоні, за допомогою функції COUNTIF. Наприклад, щоб дізнатися, скільки разів значення 70 з’являється в діапазоні A2–A40, скористайтеся формулою =COUNTIF(A2:A40;70).

Докладні відомості див. в статтях Підрахунок частоти появи значення та Функція COUNTIFS.

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

Підрахунок даних, серед яких є пусті клітинки

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

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

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

Іноді під час підрахунку потрібно ігнорувати пусті клітинки, якщо важливі тільки їхні значення. Наприклад, потрібно порахувати всіх продавців, які здійснили принаймні один продаж у регіоні.

У прикладі нижче застосування функції до значень стовпця "Збут у західному регіоні" повертає результат 3.

1

2

3

4

5

6

7

8

A

Б

Продавець

Збут у західному регіоні

Друбич

24 000

Колесник

Мороз

Гнатенко

31 000

Снігур

Руденко

8000

=COUNTA(B2:B7)

Оскільки клітинки B3, B4 та B6 пусті, функція COUNTA ігнорує їх. Враховуються лише клітинки, які містять значення 24 000, 31 000 та 8000. Кількість (результат у клітинці B8) – 3.

Докладні відомості див. в статтях Підрахунок непустих клітинок і Функція COUNTA.

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

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

Щоб підрахувати кількість непустих клітинок у стовпці записів у списку або базі даних, які відповідають указаним умовам, скористайтеся функцію DCOUNTA.

У прикладі нижче за допомогою функції DCOUNTA ми отримали кількість записів бази даних у діапазоні A4:B9, які відповідають умовам, указаним у діапазоні A1:B2. Це такі умови: значення "Код продукту" має перевищувати або дорівнювати 4000, значення "Оцінки" має перевищувати або дорівнювати 50. Обом умовам відповідає лише один запис у рядку 7.

1

2

3

4

5

6

7

8

9

A

B

Код продукту

Оцінки

=">=4000"

=">=50"

Код продукту

Оцінки

2048

61

16 384

35 336

83

1024

113

512

47

=DCOUNTA(A4:B9;"Оцінки";A1:B2)

Докладні відомості див. в статті Функція DCOUNTA.

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

Підрахунок пустих клітинок у суцільному діапазоні за допомогою функції COUNTBLANK

Щоб повернути кількість пустих клітинок у суцільному діапазоні (діапазон суцільний, якщо містить безперервну послідовність клітинок), скористайтеся функцією COUNTBLANK. Якщо клітинка містить формулу, яка повертає пустий текст (""), вона включається до підрахунку.

Примітка : Іноді під час підрахунку потрібно враховувати пусті клітинки, якщо вони важливі. Наприклад, може знадобитися порахувати всіх продавців у певному регіоні незалежно від того, чи здійснювали вони продажі.

Докладні відомості див. в статті Функція COUNTBLANK.

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

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

Скористайтеся поєднанням функцій SUM та IF. Зазвичай за допомогою функції IF у формулі масиву можна визначити, чи кожна клітинка, на яку посилається формула, містить значення, а потім підсумувати кількість значень FALSE, які повернула формула.

Докладні відомості див. в статті бази знань XL: Використання функції SUM(IF()) замість CountBlank().

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

Підрахунок унікальних значень

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

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

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

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

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

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

Щоб обчислити кількість значень у діапазоні, скористайтеся функцією COUNTIF.

У прикладі нижче функція COUNTIF повертає 2, кількість екземплярів значення 250 у діапазоні A2:A7.

1

2

3

4

5

6

7

8

A

Продано одиниць

245

250

250

Недоступно

=COUNTIF(A2:A7;250)

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

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

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

Скористайтеся функціями SUM та IF. Зазвичай за допомогою функції IF у формулі масиву можна визначити, чи виконується складена умова, а потім підсумувати кількість значень FALSE, які повернула формула.

У прикладі нижче за допомогою функції IF ми перевірили кожну клітинку в діапазоні A2:A10, щоб з’ясувати, чи містить вона прізвище Друбич або Мороз. За допомогою функції SUM отримуємо результат 7, кількість екземплярів значення TRUE. Ви можете скопіювати та вставити цей приклад на аркуш у клітинку A1. Вставивши цей приклад, ви помітите, що в клітинці A11 з’явиться помилка #VALUE!. Щоб формула почала працювати, потрібно перетворити її на формулу масиву, натиснувши клавішу F2, а потім – клавіші Ctrl+Shift+Enter. Після цього в клітинці A11 з’явиться цифра 7.

1

2

3

4

5

6

7

8

9

10

А

Б

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

Рахунок

Друбич

15 000

Мороз

11 000

Друбич

11 000

Мороз

4000

Снігур

8000

Мороз

6000

Снігур

14 000

Друбич

7000

12 000

Формула

Опис (результат)

=SUM(IF((A2:A10="Друбич")+(A2:A10="Мороз");1;0))

Кількість рахунків для прізвищ Друбич і Мороз (7)

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

Додаткові поради див. в таких статтях бази знань:

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

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

За допомогою діалогового вікна Розширений фільтр можна знаходити унікальні значення в стовпці даних. Ви можете відфільтрувати значення на місці або видобути та вставити їх у нове місце. Після цього можна обчислити кількість елементів у новому діапазоні за допомогою функції ROWS.

Примітки : 

  • Якщо фільтрувати дані на місці, значення не видаляються з аркуша – може бути приховано один або кілька рядків. Натисніть кнопку Очистити в групі Сортування й фільтр на вкладці Дані, щоб знову відобразити ці значення.

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

Унікальні значення можна знайти за допомогою команди Додатково (вкладка Дані, група Сортування й фільтр).

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

Лупа аналізу даних

На знімку екрана нижче стовпець C містить п’ять унікальних значень, скопійованих із діапазону в стовпці A.

Вихідні значення в стовпці A й унікальні значення зі стовпця A, створені в стовпці C

Докладні відомості див. в розділі "Обчислення кількості унікальних значень за допомогою фільтра" в статті Обчислення кількості унікальних значень серед повторюваних.

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

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

Скористайтеся різними поєднаннями функцій IF, SUM, FREQUENCY, MATCH і LEN.

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

Крім того, див. статтю XL: Визначення кількості унікальних елементів у списку.

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

Особливі випадки (підрахунок усіх клітинок, кількості слів)

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

Підрахунок усіх клітинок у діапазоні за допомогою функцій ROWS і COLUMNS

Припустімо, що потрібно визначити розмір великого аркуша, щоб вирішити, як здійснювати підрахунки в книзі: уручну або автоматично. Щоб обчислити кількість усіх клітинок у діапазоні, скористайтеся формулою, що перемножує значення, отримані функціями ROWS і COLUMNS.

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

Копіювання прикладу

  1. Створіть пустий аркуш або книгу.

  2. Виділіть приклад у розділі довідки.

    Примітка : Заголовки рядків і стовпців виділяти не потрібно.

    Виділення прикладу в довідці
    Виділення прикладу в довідці
  3. Натисніть сполучення клавіш Ctrl+C.

  4. На аркуші виділіть клітинку А1 і натисніть клавіші Control+V.

  5. Щоб переключатися між режимом перегляду результатів і режимом перегляду формул, що повертають ці результати, натискайте клавіші Control + тупий наголос («`»). Також на вкладці Формули у групі Аудит формули ви можете натиснути кнопку Показати формули.

1

2

3

4

5

6

A

B

C

Регіон

Місяць

Продажі

Схід

Січ

18 000₴

Схід

Лют

23 000₴

Схід

Березень

19 000₴

Формула

Опис (результат)

=ROWS(A2:C4)*COLUMNS(A2:C4)

Загальна кількість клітинок у діапазоні (9)

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

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

Скористайтеся поєднанням функцій SUM, IF, LEN і SUBSTITUTE у формулі масиву. У прикладі нижче за допомогою складеної формули ми визначили кількість слів у діапазоні із семи клітинок (три з яких пусті). Деякі клітинки на початку та в кінці текстового фрагмента містять пробіли. Функції TRIM і SUBSTITUTE видаляють ці зайві пробіли, перш ніж починати підрахунок.

У таблиці нижче скопіюйте текст із клітинок A2–A11. Перш ніж вставляти текст у клітинку A1 на аркуші, змініть ширину стовпця A приблизно до значення 100.

1

2

3

4

5

6

7

8

9

10

11

A

Текстовий рядок

Сім слів і чотири пробіли наприкінці фрагмента     

    Вісім слів і три пробіли на початку фрагмента

Дев’ять слів, кілька ком і два пробіли наприкінці фрагмента  

Деякі символи ігноруються, наприклад коми або крапки.

Формула

=SUM(IF(LEN(TRIM(A2:A8))=0;0;LEN(TRIM(A2:A8))-LEN(SUBSTITUTE(A2:A8;" ";""))+1))

Вставте текст на аркуш у клітинку A1, виділіть клітинку A11, натисніть клавішу F2, а потім – клавіші Shift+Ctrl+Enter, щоб ввести формулу як формулу масиву. У клітинці A11 має з’явитися правильний результат, 29.

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

Відображення підрахунків і кількостей у рядку стану

Якщо виділити одну або кілька клітинок, відомості про дані в них відобразяться в рядку стану програми Excel. Наприклад, якщо на аркуші виділено чотири клітинки зі значеннями 2 й 3, текстовий рядок (наприклад, "хмара") і значення 4, усі наведені нижче значення можуть відображатися в рядку стану одночасно: середнє значення, кількість, сума, мінімальне й максимальне значення. Клацніть правою кнопкою миші рядок стану, щоб відобразити або приховати всі чи деякі з цих значень. Ці значення показано на знімку екрана нижче.

Рядок стану з обчисленнями та кількостями виділених клітинок

Примітка : У попередніх версіях Excel ці значення також можуть відображатися в рядку стану, але тільки по одному.

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

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

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

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

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

×