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

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

Завантаження прикладів

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

Завантаження прикладів підрахунку значень в електронній таблиці

У цій статті

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

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

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

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

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

Ваш браузер не підтримує відео.

Використання функції "Автосума"

Використовуйте функцію "Автосума", вибравши діапазон клітинок, який містить принаймні одне числове значення. Потім на вкладці Формули натисніть кнопку Автосума >кількість чисел.

Кількість чисел

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

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

Додавання рядка проміжних підсумків

До даних можна додати рядок проміжних підсумків Excel даних. Клацніть будь-де в даних, а потім виберіть пункт Дані > проміжні підсумки.

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

Натисніть кнопку Проміжні підсумки на вкладці Дані, щоб додати рядок проміжних підсумків у Excel даних.

Крім того, статтю наведено в таких статтях:

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

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

За допомогою функції SUBTOTAL можна підрахувати кількість значень у Excel таблиці або діапазоні клітинок. Якщо таблиця або діапазон містить приховані клітинки, за допомогою функції SUBTOTAL можна включити або виключити ці приховані клітинки, і це найбільша різниця між функціями SUM і SUBTOTAL.

Синтаксис SUBTOTAL має такий вигляд:

SUBTOTAL(номер_функції;посилання1;[посилання2];...)

SubTOTAL example

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

Щоб виключити приховані значення в діапазоні, установіть для function_num значення 102.

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

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

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

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

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

Ваш браузер не підтримує відео.

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

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

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

Приклад функції COUNT

У наведеному вище прикладі клітинки A2, A3 та A6 – це лише клітинки, які містять числові значення в діапазоні, тому результат буде 3.

Примітка.: A7 – це значення часу, але воно містить текст(a.m.), тому функція COUNT не вважає його числовим значенням. Якщо ви вилучили ім'я A.m. у клітинці функція COUNT вважатиме клітинку A7 числовим значенням, а результат буде змінюється на 4.

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

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

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

Приклади функції COUNTIF

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

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

Функція DCOUNT підраховує клітинки, які містять числа в полі (стовпці) записів у списку або базі даних, які відповідають указаним умовам.

У прикладі нижче потрібно обчисити кількість місяців, включно з пізнішими за березень 2016 р., за які було продано понад 400 одиниць товару. Перша таблиця на аркуші від A1 до B7 містить дані про збут.

Зразок даних для DCOUNT

Функція DCOUNT використовує умови, щоб визначити, звідки слід повертати значення. Умови зазвичай вводяться в клітинки самого аркуша, а потім посилаються на ці клітинки в аргументі умови. У цьому прикладі клітинки A10 і B10 містять дві умови: одна вказує на те, що повернуте значення має бути більше 400, а інше – те, що кінцевий місяць має дорівнювати або більшим за 31 березня 2016 р.

Потрібно використовувати такий синтаксис:

=DCOUNT(A1:B7;"Завершення місяця";A9:B10)

Функція DCOUNT перевіряє дані в діапазоні A1–B7, застосовує умови, указані в діапазоні A10 і B10, і повертає 2,загальна кількість рядків, що задовольняє обом умовам (рядки 5 і 7).

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

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

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

Функція COUNTIFS має такий синтаксис:

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

Див. наведений нижче приклад:

CountIFS example

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

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

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

Примітки.: 

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

  • Наприклад, щоб формули працювали, другий аргумент функції IF має бути числом.

Приклади вкладених функцій COUNT та IF

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

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

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

Приклад 1

Приклад 1. Вкладені функції SUM та IF у формулі

У наведеній вище функції зазначено, що діапазон клітинок C2:C7 містить значення Buchanan і Dodsworth,функція SUM має відобразити суму записів, у яких умова виконана. Формула знайде три записи для Buchanan і один для Додсворта в заданому діапазоні та відображає 4.

Приклад 2

Приклад 2. Вкладені функції SUM та IF у формулі

Наведена вище функція показує, що D2:D7 містить значення, менші за 9000 грн. або більше 19 000 грн., функція SUM має відобразити суму всіх записів, у яких умова виявлялася. Формула знайде два записи D3 та D5 зі значеннями, меншими за 9000 грн., а потім D4 та D6 зі значеннями, більшими за 19 000 грн. відображаються 4.

Приклад 3

Приклад 3. Вкладені функції SUM та IF у формулі

У наведеній вище функції зазначено, що В діапазоні D2:D7 є рахунки-фактури Buchanan за суму менше 9000 грн., функція SUM має відобразити суму записів, для яких умова виконана. Формула знайде, що клітинка C6 відповідає умові, і відображає результат 1.

Увага!: Формули в цьому прикладі необхідно вводити як формули масивів. Тобто натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter. У попередніх версіях Excel для Mac використовуються клавіші КОМАНДА+Shift+Enter.

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

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

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

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

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

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

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

Примітка.: Для інтерактивної роботи ви можете виконати ці кроки на зразку даних, наданих на аркуші зведеної таблиці в книзі, яку можна завантажити.

  1. Введіть наведені нижче дані в Excel таблиці.

    Зразок даних для зведеної таблиці
  2. Виберіть A2:C8.

  3. Послідовно виберіть елементи Вставлення > Зведена таблиця.

  4. У діалоговому вікні Створення зведеної таблиці натисніть кнопку Вибрати таблицю абодіапазон , а потім виберіть пункт Новий аркуші натисніть кнопку OK.

    На новому аркуші буде створено пусту зведену таблицю.

  5. В області Поля зведеної таблиці виконайте такі дії:

    1. Перетягніть рядок Sport до області Рядки.

    2. Перетягніть квартал до області Стовпці.

    3. Перетягніть sales до області значень.

    4. Повторіть крок в.

      Ім'я поля відображається як SumofSales2 в області зведеної таблиці та значень.

      На цьому етапі область Поля зведеної таблиці має такий вигляд:

      Поля зведеної таблиці
    5. В області Значення клацніть розкривний список біля поля SumofSales2 і виберіть поле значення Настройки.

    6. У діалоговому вікні Настройки значення виконайте такі дії:

      1. У розділі Summarize value field by (Підсумувати значення поля за) виберіть елемент Count (Кількість).

      2. У полі Настроюване ім'я змініть ім'я на Кількість.

        Value Field Настройки dialog box
      3. Натисніть кнопку OK.

    У зведеній таблиці відображається кількість записів для гольфа та гольфа у кварталі 3 та 4 кварталі, а також показники збуту.

    PivotTable

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

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

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

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

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

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

Приклад функції COUNTA

Функція COUNTA ігнорує пусті значення в клітинках D3, D4, D8 і D11 і підраховує лише клітинки, які містять значення у стовпці D. Функція знаходить шість клітинок у стовпці D, що містить значення, і відображає 6 як результат.

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

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

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

У наведеному нижче прикладі функція DCOUNTA використовується, щоб підрахувати кількість записів у базі даних у діапазоні A1:B7, які відповідають умовам, указаним у діапазоні A9:B10. Це такі умови: значення "Код товару" має бути більше або дорівнювати 2000, а значення "Оцінки" має бути більше або дорівнювати 50.

Приклад функції DCOUNTA

DCOUNTA знайде два рядки, які відповідають умовам ( рядки 2 та 4) і відображає значення 2 як результат.

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

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

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

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

Приклад функції COUNTBLANK

Примітка.: Функція аркуша COUNTBLANK забезпечує найзручніший спосіб визначення кількості пустих клітинок у діапазоні, але вона працює дуже добре, якщо клітинки, які цікавляться, розташовано в закритій книзі або не утворюють суцінний діапазон. У статті бази знань XL: Використання функції SUM(IF()) замість CountBlank() показує, як у таких випадках використовувати формулу масиву SUM(IF()).

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

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

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

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

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

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

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

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

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

Щоб скористатися розширеним фільтром, на вкладці Дані в групі Сортування & натисніть кнопку Додатково.

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

Розширений фільтр

На рисунку нижче стовпець E містить значення, скопійовані з діапазону в стовпці D.

Стовпець, скопійований з іншого розташування

Примітки.: 

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

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

Докладні відомості див. в розділі Фільтрування за допомогою розширених умов.

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

Підрахунок унікальних значень у діапазоні, які відповідають одній або книжці умов, за допомогою функцій IF, SUM, FREQUENCY, MATCH і LEN

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

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

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

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

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

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

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

Приклад функції ROWS і COLUMNS для підрахунку кількості клітинок у діапазоні

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

Підрахунок слів у діапазоні за допомогою поєднання функцій SUM, IF, LEN, TRIM і SUBSTITUTE

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

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

Тепер, щоб наведена вище формула працювала правильно, потрібно зробити цю формулу масиву, інакше формула поверне #VALUE! помилку #REF!. Для цього клацніть клітинку з формулою, а потім у рядку формул натисніть сполучення клавіш Ctrl+Shift+Enter. Excel фігурну дужку на початку та в кінці формули, зробивши її формулою масиву.

Докладні відомості про формули масивів див. в описах формул у Excel та Створення формули масиву.

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

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

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

рядок стану

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

Потрібна додаткова довідка?

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

Чи були ці відомості корисні?

Наскільки ви задоволені якістю перекладу?
Що вплинуло на ваші враження?

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

×