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

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

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

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

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

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

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

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

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

Крім того, див. такі статті:

• Створення структури (групування) даних в аркуші

• Вставлення проміжних підсумків у список даних на аркуші

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функція DCOUNT використовує умови для визначення місця повернення значень. Умови зазвичай вводяться в клітинки самого аркуша, а потім посилаються на ці клітинки в аргументі умови . У цьому прикладі клітинки A10 і B10 містять дві умови: одна, яка вказує на те, що повернуте значення має бути більше 400, а інше – до 31 березня 2016 року або дорівнювати 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];…)

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

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

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

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

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

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

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

Приклад 1

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

Приклад 2

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

Приклад 3

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

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

• XL: використання функції SUM(IF()) як функції масиву замість COUNTIF() із функцією AND

• XL: Обчислення кількості екземплярів числа або тексту в діапазоні

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

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

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

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

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

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

   

2. Виберіть A2:C8

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

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

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

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

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

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

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

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

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

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

      

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

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

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

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

         

      3. Натисніть кнопку OK.

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

   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Підрахунок усіх клітинок у діапазоні за допомогою функцій 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 і Створення формули масиву.

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