Преброяването е неразделна част от анализа на данни независимо дали изчислявате броя на хората в отдел във вашата организация, или бройките, които са продадени за всяко тримесечие. Excel предоставя няколко техники, които можете да използвате за преброяване на клетки, редове или колони с данни. За да ви помогне да направите най-добрия избор, тази статия предоставя изчерпателно резюме на методите, работна книга, която може да се изтегли, с интерактивни примери и връзки към свързани теми за по-нататъшно разбиране.
Забележка
Преброяването не трябва да се бърка със събиране. За повече информация за събирането на стойности в клетки, колони или редове, вижте "Обобщаване на начините за сумиране и преброяване на данни на Excel".
Изтеглете нашите примери
Можете да изтеглите примерна работна книга, която дава примери в допълнение към информацията в тази статия. Повечето раздели в тази статия ще препращат към съответния работен лист в рамките на примерната работна книга, който предоставя примери и повече информация.
Изтегляне на примери за преброяване на стойности в електронна таблица
В тази статия
Преброяване на базата на едно или няколко условия
- Видео: Използване на функциите COUNT, COUNTIF и COUNTA
- Преброяване на клетките в диапазон с помощта на функцията COUNT
- Преброяване на клетките в диапазон на базата на едно условие с помощта на функцията COUNTIF
- Преброяване на клетките в колона на базата на едно или няколко условия с помощта на функцията DCOUNT
- Преброяване на клетките в диапазон на базата на няколко условия с помощта на функцията COUNTIFS
- Преброяване на базата на критерии със съвместното използване на функциите COUNT и IF
- Преброяване на повторенията на множество текстови или числови стойности със съвместното използване на функциите SUM и 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 е стойност за време, но тя съдържа текст (a.m.), следователно COUNT не я счита за числова стойност. Ако премахнете a.m. от клетката 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 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 в областта на редовете .
Плъзнете Тримесечие в областта на колоните .
Плъзнете "Продажби " в областта на стойностите .
Повторете стъпка в.
Името на полето се показва като SumofSales2 както в обобщената таблица, така и в областта на стойностите.
На този етап екранът с полета на обобщената таблица изглежда така:
В областта " Стойности " изберете падащото меню до "Сума на продажбите2 " и изберете "Настройки на поле за стойност".
В диалоговия прозорец " Настройки на поле за стойност " направете следното:
В полето за обобщаване на стойности по секции изберете "Брой".
В полето за име по избор променете името на "Брой".
Изберете OK.
Обобщената таблица показва броя на рекордите за голф и тенис през третото тримесечие и четвъртото тримесечие, заедно с данните за продажбите.
Преброяване, когато вашите данни съдържат празни стойности
Можете да преброите клетките, които съдържат данни или които са празни с помощта на функции за работен лист.
Преброяване на непразните клетки в диапазон с помощта на функцията COUNTA
Използвайте функцията COUNTA , за да преброите само клетките в диапазон, които съдържат стойности.
Когато броите клетки, понякога искате да игнорирате всички празни клетки, тъй като само клетките със стойности имат смисъл за вас. Например искате да преброите общия брой продавачи, които са направили продажба (колона D).
COUNTA игнорира празните стойности в D3, D4, D8 и D11 и преброява само клетките, съдържащи стойности, в колона D. Функцията намира шест клетки в колона D, съдържащи стойности, и показва 6 като резултат.
Преброяване на непразните клетки в списък с определени условия с помощта на функцията DCOUNTA
Използвайте функцията DCOUNTA, за да преброите непразните клетки в колона от записи в списък или база данни, които отговарят на зададени от вас условия.
Следващият пример използва функцията DCOUNTA , за да преброи записите в базата данни, съдържаща се в диапазона A1:B7, които отговарят на условията, зададени в диапазона за критериите – А9: B10. Тези условия са, че стойността на ИД на продукт трябва да бъде по-голяма или равна на 2000 и стойността за "Рейтинги" трябва да бъде по-голяма или равна на 50.
DCOUNTA намира два реда, които изпълняват условията – редове 2 и 4 – и показва стойността 2 като резултат.
Преброяване на празни клетки в непрекъснат диапазон с помощта на функцията COUNTBLANK
Използвайте функцията COUNTBLANK , за да се върне броят на празните клетки в непрекъснат диапазон (диапазонът е непрекъснат, ако всички клетки са свързани в непрекъсната последователност). Ако една клетка съдържа формула, която връща празен текст (""), тази клетка се брои.
Когато броите клетките, може да има случаи, когато искате да включите празни клетки, тъй като имат смисъл за вас. В следващия пример на електронна таблица за продажби на хранителни стоки. Да предположим, че искате да намерите в колко клетки не са споменати данните за продажбите.
Забележка
Функцията за работен лист COUNTBLANK предоставя най-удобния метод за определяне на броя на празните клетки в диапазон, но той не работи много добре, когато клетките, които ги интересуват, са в затворена работна книга или когато не образуват непрекъснат диапазон.
Преброяване на празни клетки в прекъснат диапазон с помощта на комбинация от функциите 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 или да получите поддръжка в общностите.