Підрахунок кількості значень в стовпці в Microsoft Excel

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

Процедура підрахунку значень в стовпці

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

Спосіб 1: індикатор в рядку стану

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

Для виконання даного завдання досить затиснути ліву кнопку миші і виділити весь стовпець, в якому ви хочете зробити підрахунок значень. Як тільки виділення буде вироблено, в рядку стану, яка розташована внизу вікна, близько параметра «Кількість» буде відображатися число значень, що містяться в стовпці. У підрахунку братимуть участь осередки, заповнені будь-якими даними (числові, текстові, дата і т.д.). Порожні елементи при підрахунку будуть ігноруватися.

У деяких випадках індикатор кількості значень може не висвітлюватися в рядку стану. Це означає те, що він, швидше за все, відключений. Для його включення слід натиснути правою кнопкою миші по рядку стану. З’являється меню. У ньому потрібно встановити галочку біля пункту «Кількість». Після цього кількість заповнених даними осередків буде відображатися в рядку стану.

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

Спосіб 2: оператор СЧЁТЗ

За допомогою оператора СЧЁТЗ, як і в попередньому випадку, є можливість підрахунку всіх значень, розташованих в стовпці. Але на відміну від варіанту з індикатором в панелі стану, даний спосіб надає можливість зафіксувати отриманий результат в окремому елементі листа.

Головним завданням функції СЧЁТЗ, яка відноситься до статистичної категорії операторів, як раз є підрахунок кількості непустих осередків. Тому ми її з легкістю зможемо пристосувати для наших потреб, а саме для підрахунку елементів стовпця, заповнених даними. Синтаксис цієї функції наступний:

Всього у оператора може налічуватися до 255 аргументів загальної групи «Значення». В якості аргументів якраз виступають посилання на комірки або діапазон, в якому потрібно зробити підрахунок значень.

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

Як бачимо, на відміну від попереднього способу, даний варіант пропонує виводити результат в конкретний елемент листа з можливим його збереженням там. Але, на жаль, функція СЧЁТЗ все-таки не дозволяє задавати умови відбору значень.

Спосіб 3: оператор РАХУНОК

За допомогою оператора РАХУНОК можна зробити підрахунок тільки числових значень в обраній колонці. Він ігнорує текстові значення і не включає їх в загальний підсумок. Ця функція також відноситься до категорії статистичних операторів, як і попередня. Її завданням є підрахунок осередків у виділеному діапазоні, а в нашому випадку в стовпці, який містить числові значення. Синтаксис цієї функції практично ідентичний попередньому оператору:

Як бачимо, аргументи у РАХУНОК і СЧЁТЗ абсолютно однакові і являють собою посилання на комірки або діапазони. Різниця в синтаксисі полягає лише в найменуванні самого оператора.

Виділяємо елемент на аркуші, куди буде виводитися результат. Натискаємо вже знайому нам іконку «Вставити функцію».

Спосіб 4: оператор СЧЁТЕСЛІ

На відміну від попередніх способів, використання оператора СЧЁТЕСЛІ дозволяє задавати умови, що відповідають значення, які будуть брати участь у підрахунку. Всі інші осередки будуть ігноруватися.

Оператор СЧЁТЕСЛІ теж зарахований до статистичної групі функцій Excel. Його єдиним завданням є підрахунок непустих елементів в діапазоні, а в нашому випадку в стовпці, які відповідають заданій умові. Синтаксис у даного оператора помітно відрізняється від попередніх двох функцій:

Аргумент «Діапазон» представляється у вигляді посилання на конкретний масив осередків, а в нашому випадку на колонку.

Аргумент «Критерій» містить заданий умова. Це може бути як точне числове або текстове значення, так і значення, задане знаками «більше» (>), «менше» ( <),«не дорівнює» (<>) і т.д.

Порахуємо, скільки осередків з найменуванням «М’ясо» розташовуються в першій колонці таблиці.

Виділяємо елемент на аркуші, куди буде проводитися висновок готових даних. Клацаємо по значку «Вставити функцію».

Давайте трохи змінимо завдання. Тепер порахуємо кількість осередків в цій же колонці, які не містять слово «М’ясо».

Виділяємо осередок, куди будемо виводити результат, і вже описаним раніше способом викликаємо вікно аргументів оператора СЧЁТЕСЛІ. В поле «Діапазон» вводимо координати все того ж першого стовпчика таблиці, який обробляли раніше. В поле «Критерій» вводимо такий вираз: <>Мясо Тобто, цей критерій задає умову, що ми підраховуємо всі заповнені даними елементи, які не містять слово «М’ясо». Знак «<>» означає в Ексель «не дорівнює». Після введення цих налаштувань у вікні аргументів тиснемо на кнопку «OK».

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

Виділяємо осередок для виведення результату і виробляємо перехід у вікно аргументів функції СЧЁТЕСЛІ. В поле «Діапазон» вводимо координати третього стовпця нашої таблиці. В поле «Критерій» записуємо така умова: >150 Це означає, що програма буде підраховувати тільки ті елементи стовпця, які містять числа, що перевищують 150. Далі, як завжди, тиснемо на кнопку «OK».

Таким чином, ми бачимо, що в Excel існує цілий ряд способів підрахувати кількість значень в стовпці. Вибір певного варіанту залежить від конкретних цілей користувача. Так, індикатор на рядку стану дозволяє тільки подивитися кількість всіх значень в стовпці без фіксації результату; функція СЧЁТЗ надає можливість їх число зафіксувати в окремій клітинці; оператор РАХУНОК проводить підрахунок тільки елементів, що містять числові дані; а за допомогою функції СЧЁТЕСЛІ можна задати більш складні умови підрахунку елементів.

COUNTIF (функція COUNTIF)

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

У найпростішому випадку COUNTIF працює за таким принципом:

COUNTIF(діапазон;умова)

Ім’я аргументу

діапазон (обов’язковий аргумент)

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

умова (обов’язковий аргумент)

Число, вираз, посилання на клітинку або текстовий рядок, що визначає, які клітинки потрібно підрахувати.

Наприклад, можна використати число, як-от 32, порівняння, як-от “>32”, клітинку, як-от B4, або слово, як-от “яблука”.

У функції COUNTIF використовується лише одна умова. Використовуйте функцію COUNTIFS, якщо потрібно застосувати кілька умов.

Приклади

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

Рахує кількість клітинок із текстом “яблука” в клітинках від A2 до A5. У результаті отримаємо 2.

Рахує кількість клітинок із текстом “кавуни” (значення в клітинці A4) у клітинках від A2 до A5. У результаті отримаємо 1.

Рахує кількість клітинок із текстом “яблука” (значення в клітинці A2) і “апельсини” (значення в клітинці A3) у клітинках від A2 до A5. У результаті отримаємо 3. Функцію COUNTIF використано у формулі двічі, щоб визначити кілька умов – по одній умові на вираз. Також можна скористатися функцією COUNTIFS.

Рахує кількість клітинок зі значенням, більшим за 55, у клітинках B2:B5. У результаті отримаємо 2.

Рахує кількість клітинок зі значенням, яке не дорівнює 75, у клітинках B2:B5. Амперсанд (&) об’єднує оператор порівняння “не дорівнює” (<>) і значення в клітинці B4 для прочитання функції =COUNTIF(B2:B5;”<>75″). У результаті отримаємо 3.

Рахує кількість клітинок зі значеннями, більшими (>) або рівними (=) 32 та меншими (<) або рівними (=) 85, у клітинках B2:B5. У результаті отримаємо 1.

Рахує кількість клітинок, що містять будь-який текст, у клітинках від A2 до A5. Зірочка (*) використовується як символ узагальнення для будь-яких символів. У результаті отримаємо 4.

Рахує кількість клітинок, які мають точно 6 символів і закінчуються буквами “ни”, у клітинках від A2 до A5. Знак питання (?) використовується як символ узагальнення для заміни будь-яких окремих символів. У результаті отримаємо 1.

Поширені проблеми

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

Функція COUNTIF повертає неправильні результати, якщо зіставляються рядки довжиною більше 255 символів

Щоб зіставити рядки, довші за 255 символів, використовуйте функцію CONCATENATE або оператор об’єднання &. Наприклад, =COUNTIF(A2:A5;”довгий рядок”&”інший довгий рядок”).

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

Переконайтеся, що аргумент умова взято в лапки.

Формула COUNTIF отримує #VALUE! під час звернення до іншого аркуша.

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

Практичні поради

Пам’ятайте, що функція COUNTIF ігнорує верхній і нижній регістри в текстових рядках.

Умови нечутливі до регістра. Іншими словами, рядкам “яблука” та “ЯБЛУКА” відповідатимуть одні й ті ж клітинки.

Використовуйте символи узагальнення.

Символи узагальнення – знак питання (?) і зірочку (*) – можна використовувати для умов. Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).

Наприклад, =COUNTIF(A2:A5;”яблуко?”) підрахує всі екземпляри “яблука” з останньою буквою, яка може відрізнятися.

Переконайтеся, що ваші дані не містять помилкові символи.

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

Спробуйте використати функцію CLEAN або TRIM.

Для зручності використовуйте іменовані діапазони

Функція COUNTIF підтримує іменовані діапазони у формулі (наприклад, =COUNTIF(фрукти;”>=32″)-COUNTIF(фрукти;”>85″). Іменований діапазон може бути розташовано на поточному аркуші, на іншому аркуші тієї самої книги або в іншій книзі. Щоб створити посилання на іншу книгу, її також потрібно відкрити.

Примітка.: Під час підрахунку функція COUNTIF не враховує колір фону або шрифту клітинок. Проте Excel підтримує користувацькі функції (UDF) на основі операцій із клітинками Microsoft Visual Basic for Applications (VBA), які враховують колір фону або шрифту. Нижче наведено приклад того, як можна підрахувати кількість клітинок із певним кольором за допомогою VBA.

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

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