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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

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

У цій статті

Просте підраховування

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Просте підраховування

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

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

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

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

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

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

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

Використовуйте функцію 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 (функція COUNT).

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

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

Використовуйте команду Усього (вкладка Дані, група Структура), щоб групувати й підсумовувати стовпець із даними.

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

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

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

Додаткові відомості можна знайти в таких статтях:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Докладні відомості див. в статтях COUNT (функція COUNT) і Обчислення підсумків даних у таблиці Excel.


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

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

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

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

Використовуйте функцію 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 (функція COUNTIF).

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

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

Щоб підрахувати клітинки, які відповідають указаним умовам, використовуйте функцію бази даних 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 (Функція DCOUNT).

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

1

2

3

4

5

6

7

8

A

B

Продавець

Продажі в західному регіоні

Мельник

24000

Ткачук

Бондар

Бойко

31000

Петренко

Кочур

8000

=COUNTA(B2:B7)

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

Докладні відомості див. в статтях Кількість непустих клітинок і COUNTA (функція COUNTA).

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

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

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

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

1

2

3

4

5

6

7

8

9

A

B

Код товару

Оцінки

=">=4000"

=">=50"

Код товару

Оцінки

2048

61

16384

35336

83

принаймні 1024

113

512

47

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

Додаткові відомості див. в статті DCOUNTA (функція DCOUNTA).

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

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

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

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

Для отримання додаткових відомостей див. статтю COUNTBLANK (функція COUNTBLANK).

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

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

Використовуйте поєднання функцій 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. Зазвичай для цього використовується функція IF у формулі масиву. Вона дає змогу визначити, чи виконується умова, яка складається з кількох критеріїв, і підсумувати кількість значень TRUE, які повернула формула.

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

1

2

3

4

5

6

7

8

9

10

A

B

Продавець

Рахунок

Мельник

15000

Бондар

11000

Мельник

11000

Бондар

4000

Петренко

8000

Бондар

6000

Петренко

14000

Мельник

7000

12000

Формула

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

=SUM(IF((A2:A10="Мельник")+(A2:A10="Бондар");1;0))

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

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

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

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

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

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

Примітки : 

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

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

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

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

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

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

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

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

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

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

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

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

Див. також статтю Визначення кількості унікальних значень у списку.

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

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

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

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

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

Щоб цей приклад було легше зрозуміти, скопіюйте його до пустого аркуша.

Як скопіювати приклад

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

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

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

    Виділення прикладу в довідці

    Виділення прикладу в довідці

  3. Натисніть клавіші Ctrl+C.

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

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

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 у формулі масиву. У наведеному нижче прикладі показано результат використання складеної формули для визначення кількості слів у діапазоні із 7 клітинок (3 з яких – пусті). Деякі клітинки містять пробіли на початку або в кінці фрагменту – функції 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. Формула відкриється як формула масиву. Правильний результат, 29, відобразиться в клітинці A11.

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

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

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

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

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

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

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

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

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

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

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

×