Преброяването е неразделна част от анализа на данни независимо дали изчислявате броя на хората в отдел във вашата организация, или бройките, които са продадени за всяко тримесечие. Excel предоставя няколко техники, които можете да използвате за преброяване на клетки, редове или колони с данни. За да ви помогне да направите най-добрия избор, тази статия предоставя изчерпателно резюме на методите, работна книга за изтегляне с интерактивни примери и връзки към свързани теми за по-нататъшно разбиране.
Забележка: Преброяването не трябва да се бърка със събиране. За повече информация за сумирането на стойности в клетки, колони или редове вижте Сумиране на начини за добавяне и преброяване на данни на Excel.
Изтеглете нашите примери
Можете да изтеглите примерна работна книга, която дава примери за допълване на информацията в тази статия. Повечето раздели в тази статия препращат към подходящия работен лист в примерната работна книга, който предоставя примери и повече информация.
Изтегляне на примери за преброяване на стойности в електронна таблица
В тази статия
-
Преброяване на базата на едно или няколко условия
-
Преброяване на клетките в диапазон с помощта на функцията COUNT
-
Преброяване на клетките в диапазон на базата на едно условие с помощта на функцията COUNTIF
-
Преброяване на клетките в колона на базата на едно или няколко условия с помощта на функцията DCOUNT
-
Преброяване на клетките в диапазон на базата на няколко условия с помощта на функцията COUNTIFS
-
Преброяване на базата на критерии със съвместното използване на функциите COUNT и IF
-
Преброяване, когато вашите данни съдържат празни стойности
-
Преброяване на непразните клетки в диапазон с помощта на функцията COUNTA
-
Преброяване на непразните клетки в списък с конкретни условия с помощта на функцията DCOUNTA
-
Преброяване на празни клетки в непрекъснат диапазон с помощта на функцията COUNTBLANK
-
Преброяване на празни клетки в прекъснат диапазон с помощта на комбинация от функциите SUM и IF
-
-
Специални случаи (преброяване на всички клетки, преброяване на думи)
Просто преброяване
Можете да преброите стойностите в диапазон или таблица с помощта на проста формула, чрез щракване върху бутон или с помощта на функция за работен лист.
Excel може също да покаже броя на избраните клетки в Excel лента на състоянието. Вижте видео демонстрацията, която следва, за бърз поглед върху използването на лентата на състоянието. Също така вижте раздела Показване на изчисления и брой на лентата на състоянието за повече информация. Можете да направите справка със стойностите, показани в лентата на състоянието, когато искате бърз поглед върху данните си и нямате време да въвеждате формули.
Видео: Преброяване на клетки с помощта на лентата на състоянието на Excel
Гледайте следващото видео, за да научите как да преглеждате броя на лентата на състоянието.
Използване на автосумиране
Използвайте "Автосумиране" , като изберете диапазон от клетки, който съдържа поне една числова стойност. След това в раздела Формули щракнете върху Автосумиране > Брой числа.
Excel връща броя на числовите стойности в диапазона в клетка, която е съседна на избрания от вас диапазон. По принцип този резултат се показва в клетка отдясно за хоризонтален диапазон или в клетка отдолу за вертикален диапазон.
Добавяне на ред с междинни суми
Можете да добавите ред с междинни суми към вашите данни на 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.
Забележка: A7 е стойност за час, но съдържа текст (в.м.), следователно COUNT не я счита за числова стойност. Ако трябва да премахнете ч. от клетката COUNT ще разглежда A7 като числова стойност и ще промени резултата на 4.
Преброяване на клетките в диапазон на базата на едно условие с помощта на функцията COUNTIF
Използвайте функцията COUNTIF , за да преброите колко пъти дадена стойност се показва в диапазон от клетки.
Преброяване на клетките в колона на базата на едно или няколко условия с помощта на функцията DCOUNT
Функцията DCOUNT преброява клетките, съдържащи числа в поле (колона) от записи в списък или база данни, които отговарят на зададени от вас условия.
В следващия пример искате да намерите броя месеци, включително или по-късно от март 2016 г., които са с повече от 400 продадени бройки. Първата таблица в работния лист, от A1 до B7, съдържа данните за продажбите.
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];…)
Вижте следния пример:
Преброяване на базата на критерии със съвместното използване на функциите COUNT и IF
Да речем, че трябва да определите колко продавачи са продали даден артикул в определен регион или искате да знаете колко продажби над определена стойност са извършени от конкретен продавач. Можете да използвате съвместно функциите IF и COUNT: първо използвате функцията IF, за да проверите условието, и след това, само ако резултатът от функцията IF е True, използвате функцията COUNT за преброяване на клетките.
Забележки:
-
Формулите в този пример трябва да са въведени като формули за масиви. Ако сте отворили тази работна книга в Excel за Windows или Excel 2016 for Mac и искате да промените формулата или да създадете подобна формула, натиснете клавиша F2 и след това натиснете Ctrl+Shift+Enter, за да накарате формулата да върне очакваните резултати. В по-старите версии на Excel for Mac използвайте +Shift+Enter.
-
За да работят примерните формули, вторият аргумент за функцията IF трябва да бъде число.
Преброяване на повторенията на множество текстови или числови стойности със съвместното използване на функциите 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.
Важно: Формулите в този пример трябва да са въведени като формули за масиви. Това означава, че натиснете F2 и след това натиснете Ctrl+Shift+Enter. В по-старите версии на Excel for Mac използвайте +Shift+Enter.
Вижте следните статии в Базата знания за допълнителни съвети:
Преброяване на клетките в колона или ред в обобщена таблица
Обобщената таблица обобщава вашите данни и ви помага да анализирате и детайлизирате данните си, като ви позволява да изберете категориите, за които искате да видите данните си.
Можете бързо да създадете обобщена таблица, като изберете клетка в диапазон от данни или в таблица на Excel и след това в раздела Вмъкване, в групата Таблици щракнете върху Обобщена таблица.
Нека разгледаме примерен сценарий за електронна таблица за продажби, където можете да преброите колко стойности за продажби има за голф и тенис за определени тримесечия.
Забележка: За интерактивна среда за работа можете да изпълните тези стъпки за примерните данни, предоставени в листа с обобщена таблица в работната книга, която може да се изтегли.
-
Въведете следните данни в електронна таблица на Excel.
-
Избор на A2:C8
-
Щракнете върху Вмъкване > Обобщена таблица.
-
В диалоговия прозорец Създаване на обобщена таблица щракнете върху Избор на таблица или диапазон, след това щракнете върху Нов работен лист и след това щракнете върху OK.
Празна обобщена таблица се създава в нов лист.
-
В екрана Полета на обобщената таблица направете следното:
-
Плъзнете Sport в областта Редове .
-
Плъзнете Тримесечие в областта Колони .
-
Плъзнете Продажби в областта Стойности .
-
Повторете стъпка c.
Името на полето се показва като SumofSales2 както в обобщената таблица, така и в областта стойности.
В този момент екранът с полета на обобщената таблица изглежда така:
-
В областта Стойности щракнете върху падащото меню до SumofSales2 и изберете Настройки на поле за стойност.
-
В диалоговия прозорец Настройки на поле за стойност направете следното:
-
В секцията Обобщаване на полетата за стойности по изберете Брой.
-
В полето Име по избор променете името на Брой.
-
Щракнете върху 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 функция , за да се върне броят на празните клетки в непрекъснат диапазон (клетките са непрекъснати, ако всички те са свързани в непрекъсната последователност). Ако една клетка съдържа формула, която връща празен текст (""), тази клетка се брои.
Когато броите клетките, може да има случаи, когато искате да включите празни клетки, тъй като имат смисъл за вас. В следващия пример на електронна таблица за продажба на хранителни стоки. да предположим, че искате да разберете колко клетки не са с споменати цифри за продажбите.
Забележка: Функцията за работен лист 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 . Вижте изображението по-долу за пример:
Преброяване на думите в диапазон с помощта на комбинация от функциите SUM, IF, LEN, TRIM и SUBSTITUTE
Можете да използвате комбинация от функциите SUM, IF, LEN, TRIM и SUBSTITUTE във формула за масив. Следващият пример показва резултата от използването на вложена формула за намиране на броя на думите в диапазон от 7 клетки (3 от които са празни). Някои клетки съдържат начални или крайни интервали – функциите TRIM и SUBSTITUTE премахват тези излишни интервали преди преброяването. Вижте следния пример:
Сега, за да работи правилно горната формула, трябва да направите това формула за масив, в противен случай формулата връща #VALUE! грешка. За да направите това, щракнете върху клетката, в която е формулата, и след това в лентата За формули натиснете Ctrl + Shift + Enter. Excel добавя къдрава квадратна скоба в началото и края на формулата, като по този начин я прави формула за масив.
За повече информация за формулите за масиви вижте Общ преглед на формулите в Excel и Създаване на формула за масив.
Показване на изчисления и брой на лентата на състоянието
Когато са избрани една или повече клетки, информация за данните в тези клетки се показва на лентата на състоянието на Excel. Например ако са избрани четири клетки в работния лист и те съдържат стойности 2, текстов низ (например "облак"), 3 и 4, всяка една от следните стойности може да се покаже по едно и също време на лентата на състоянието: "Средно", "Брой", "Брой числа", "Минимум", "Максимум" и "Сума". Щракнете с десния бутон върху лентата на състоянието, за да покажете или скриете някои или всички стойности. Тези стойности са показани на следващата фигура.
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.