Перейти до основного
Office

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

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

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

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

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

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

У цій статті

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

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

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

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

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

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

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

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

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

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

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

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

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

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

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

Крім того, можна скористатися такими статтями:

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

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

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

Синтаксис проміжного підсумку має такий вигляд:

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

Приклад проміжного підсумку

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

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

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

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

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

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

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

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

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

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

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

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

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

Примітка.: A7 – це значення часу, але він містить текст (ранку), тому підрахунок не враховує числове значення. Якщо ви вилучили a. m. у клітинках ПІДРАХУНОК буде розглянемо 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 дає змогу використовувати умови для клітинок різних діапазонів і Підраховує кількість випадків, коли виконуються всі умови. Ви можете використовувати до 127 діапазону або умови для пар за допомогою COUNTIFS.

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

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

Нижче наведено приклад.

Приклад COUNTIFS

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

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

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

Примітки.: 

  • Формули в цьому прикладі необхідно вводити як формули масивів. Якщо ви відкрили цю книгу в програмі Excel для Windows або Excel 2016 для Mac і хочете змінити формулу або створити подібну формулу, натисніть клавішу F2, а потім натисніть сполучення клавіш CTRL + SHIFT + ENTER, щоб формула поверне результати, які ви очікуєте. У попередніх версіях Excel для Mac використовуйте COMMAND + SHIFT + клавішу вводу.

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

Приклади вкладених РЯДКІВ і функцій IF

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

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

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

Приклад 1

Приклад 1: сума та вкладення у формулі

Ця функція говорить, якщо C2: C7 містить значення " Петренко " і " Василенко", функція Sum має відображати суму записів, у яких виконується умова. Формула знаходить три записи для Петренко та одного для Додуворт в заданому діапазоні, а також відображає 4.

Приклад 2

Приклад 2: сума та вкладення у формулі

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

Приклад 3

Приклад 3: сума та вкладення у формулі

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

Увага!: Формули в цьому прикладі необхідно вводити як формули масивів. Це означає, що ви натискаєте клавішу F2 , а потім натисніть клавіші CTRL + SHIFT + "введіть". У попередніх версіях Excel для Mac використовуйте COMMAND + Shift + клавіша вводу.

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

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

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

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

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

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

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

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

  1. У електронній таблиці Excel вкажіть наведені нижче дані.

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

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

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

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

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

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

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

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

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

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

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

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

    6. У діалоговому вікні Параметри поля значення виконайте наведені нижче дії.

      1. У розділі " підсумувати значення за розділом" натисніть кнопку " кількість".

      2. У полі настроюване ім'я змініть ім'я, яке потрібно обчислити.

        Діалогове вікно «Настройки поля значення»
      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 () , у цих випадках можна використовувати формулу МАСИВУ (IF ()).

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

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

Використання комбінації функції SUM і функції IF . Загалом, за допомогою функції IF у формулі масиву можна визначити, чи має кожна з них посилання значення, а потім ПІДСУМОВУВАННЯ кількості помилкових значень, повернутих формулою.

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

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

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

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

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

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

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

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

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

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

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

Примітки.: 

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

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

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

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

Підрахунок кількості унікальних значень у діапазоні, які відповідають одній або кільком умовам, за допомогою функцій IF, SUM, частота, ЗІСТАВЛЕННЯ та LEN

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

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

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

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

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

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

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

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

Приклад функції "РЯДКИ та СТОВПЦІ" для підрахунку кількості клітинок у діапазоні

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

Підрахунок слів у діапазоні за допомогою комбінації функцій SUM, IF, LEN, ОБРІЗКА та ЗАМІНИ

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

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

Тепер для належної роботи наведеної вище формули потрібно зробити цю формулу масиву, інакше формула повертає #VALUE! поверне помилку #VALUE!. Щоб зробити це, клацніть у клітинках, що містить формулу, а потім у рядку формул натисніть клавіші CTRL + SHIFT + клавіша вводу. Програма Excel додає фігурний кронштейн на початку та в кінці формули таким чином, щоб він був формулою масиву.

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

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

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

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

рядок стану

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

Маєте запитання щодо конкретних функцій Excel?

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо покращення наступної версії Excel? Якщо так, перегляньте розділи на веб-сайті Excel User Voice.

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

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

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

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

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×