Обобщение на начините за сумиране и преброяване на данни на Excel

Обобщение на начините за сумиране и преброяване на данни на Excel

Разликата между преброяване и сумиране
Сумирането (събирането на стойности) е неразделна част от анализа на данни, независимо дали търсите междинната сума от продажбите в северозападен регион, или подвижни общи суми от седмичните квитанции. Excel предоставя няколко техники, които можете да използвате за сумиране на данни.

За да ви помогне да направите най-добрия избор, тази статия предоставя изчерпателно резюме на методите, поддържаща информация, която да ви помогне да решите бързо коя техника да използвате, и връзки към по-подробни статии.

Сумирането не трябва да се бърка с преброяване. За повече информация за преброяването на клетки, колони или редове с данни, вижте Преброяване на клетки, редове или колони с данни.

В тази статия

Просто събиране и изваждане

Можете да събирате и изваждате числа с помощта на проста формула, чрез щракване върху бутон или с помощта на функция за работен лист.

Събиране на стойностите в клетка с помощта на проста формула

Ако ви трябва просто бърз резултат, можете да използвате Excel като миникалкулатор. Направете това с помощта на аритметичния оператор знак "плюс" (+). Например ако въведете формулата =5+10 в клетка, Excel показва 15 като резултат.

За повече информация как да използвате аритметични оператори във формула, вж. статията Използване на Excel като калкулатор.

Най-горе на страницата

Изваждане на стойностите в клетка с помощта на проста формула

Направете това с помощта на аритметичния оператор знак минус (-). Например формулата =12-9 показва резултат 3.

За повече информация как да използвате аритметични оператори във формула, вж. статията Използване на Excel като калкулатор.

Най-горе на страницата

Събиране на стойностите в колона или ред с помощта на бутон

Можете да използвате автосумиране, за да сумирате бързо диапазон от числа в колона или ред. Щракнете върху празна клетка под колоната от числа или вдясно от реда с числа и след това щракнете върху Автосумиране. Excel избира това, което е определил като най-вероятния диапазон от данни. Щракнете върху Автосумиране отново, за да приемете диапазона избран от Excel, или изберете ваш собствен диапазон и след това щракнете върху Автосумиране.

Преоразмеряване на таблица

За повече информация как да използвате автосумиране, вж. статията Използване на Excel като калкулатор.

Най-горе на страницата

Събиране на стойностите в диапазон с помощта на функция

Функцията SUM е полезна, когато искате да събирате или изваждате стойности от различни диапазони или да комбинирате числови стойности с диапазони от числа. Използвайте функцията SUM, за да съберете всички аргументи, които задавате между отварящата и затварящата кръгла скоба. Всеки аргумент може да бъде диапазон, препратка към клетка или положителна или отрицателна числова стойност.

За да въведете проста формула, въведете =SUM в клетка, последвано от отваряща кръгла скоба. След това въведете едно или повече числа, препратки към клетки или диапазони от клетки, разделени с точка и запетая. След това въведете затварящата кръгла скоба и натиснете ENTER, за да се покаже резултатът. Можете също да използвате мишката, за да изберете клетките, съдържащи данните, които искате да сумирате.

1

2

3

A

Участие

4823

12 335

Като използвате например данните в таблицата по-горе, всяка една от следващите формули използва функцията SUM, за да върне една и съща стойност (17 158):

  • =SUM(4823;12 335)

  • =SUM(A2;A3)

  • =SUM(A2:A3)

  • =SUM(A2;12 335)

Следващата фигура показва формулата, която използва функцията SUM, за да събере стойността на клетката A2 и 12 335. Под формулата има екранно пояснение, което предоставя указания за използването на функцията SUM.

Използване на функцията SUM за добавяне на клетка и стойност

Забележки: 

  • В Excel няма функция SUBTRACT. За да извадите стойности с помощта на функция, използвайте отрицателни стойности във функцията SUM. Например формулата =SUM(30;A3;-15;-B6) събира 30 и стойността в клетка A3, изважда 15 и също така изважда стойността в клетка B6.

  • Можете да включите до 255 числови стойности или препратки към клетка или диапазон, във всякакви комбинации, като аргументи на функцията SUM.

За повече информация вж. статията SUM функция.

Най-горе на страницата

Изваждане на стойностите в диапазон с помощта на функция

Използвайте функцията SUM за изваждане на числа, като въведете числата, които искате да извадите, като отрицателни числа във формулата.

1

2

3

A

Участие

29 072

12 335

Като използвате например данните в таблицата по-горе, всяка една от следващите формули използва функцията SUM, за да върне една и съща стойност (16 737):

  • =SUM(29 072;12 335)

  • =SUM(A2;-A3)

  • =SUM(A2;-12 335)

  • =SUM(A2;(-1*(A3)))

Най-горе на страницата

Сумиране на данни чрез групиране и обобщаване

Можете да използвате структура или отчет с обобщена таблица, за да групирате и обобщавате данните си.

Събиране на стойностите в колона с помощта на структура

Ако данните ви са в списък и можете логически да ги групирате по стойности в колони, можете да групирате и обобщавате данните, като създадете структура. Създаването на структура може да обобщи данните чрез вмъкване на междинни суми и крайни общи суми. Да предположим, че имате следните стойности за продажбите, подредени по регион и месец.

1

2

3

4

5

6

7

A

B

C

Регион

Месец

Продажби

Изток

Янр

18 000 лв.

Изток

Фев

23 000 лв.

Изток

Март

19 000 лв.

Запад

Янр

17 000 лв.

Запад

Фев

27 000 лв.

Запад

Март

21 000 лв.

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

Структуриране с междинни суми и обща сума

Използвайте командата Междинна сума (раздел Данни, група Структура) за създаване на структура, междинни суми и обща сума.

Първоначалните данни, включваха три реда с данни за източния регион и три реда с данни за западния регион (редовете от 2 до 7). Обърнете внимание, че операцията "междинна сума" вмъкна "Обща сума за източния регион" в ред 5 между последния ред на данните за източния регион и първия ред с данни за западния регион.

Ако щракнете върху клетка A4 и след това щракнете върху командата Междинна сума, Excel създава структура и вмъква редове за Обща сума за източния регион, Обща сума за западния регион и Крайна обща сума, като също така попълва клетките в колоната Продажби с тези общи суми.

За повече информация вж. следните статии:

Структуриране на списък с данни в работен лист

Вмъкване на междинни суми в списък от данни в работен лист

Най-горе на страницата

Събиране на стойностите в списък или колона на таблица на Excel с помощта на функция

Като по-гъвкава алтернатива на командата Междинна сума можете да използвате функцията SUBTOTAL за изчисляване на междинни суми и крайни общи суми в списъци или таблици на Excel, които съдържат скрити редове или колони. Във функцията SUBTOTAL задавате аргумент, за да включите или да не включите скритите стойности.

Забележка: Функцията SUBTOTAL може да контролира дали са включени скрити редове в резултатите, и винаги игнорира редове, които са филтрирани.

Да предположим например, че искате да изчислите междинните суми и общата сума за данните в клетките от C2 до C7, но искате да игнорирате скритите данни в ред 3 и ред 6. Функцията, която използвате, прилича на следната:

= SUBTOTAL( 109;C2:C7)

Първият аргумент (109) указва, че искате да съберете стойностите в диапазона и да игнорирате скритите стойности. За да включите скритите стойности, вместо това използвайте 9 като първи аргумент.

За подробности вж. статиите SUBTOTAL функция и Сумиране на данни в таблица на Excel.

Най-горе на страницата

Сумиране на вашите данни и преглед на данните от различни перспективи

Когато даден работен лист съдържа стотици или дори хиляди редове с подобни данни, е възможно да е трудно да схванете смисъла от тези числа. Можете да създадете отчет с обобщена таблица в Excel от тези данни, който показва междинни и общи суми и който може да обобщава данните въз основа на зададени от вас категории.

Можете бързо да създадете обобщена таблица, като изберете клетка в диапазон от данни или в таблица на Excel и след това в раздела Вмъкване, в групата Таблици щракнете върху Обобщена таблица.

За да покажем големите възможности на обобщената таблица, обърнете внимание, че в следващия пример данните за продажби съдържат много редове (всъщност има 40 реда с данни, но графиката показва само част от тях). Освен това данните не са обобщени и нямат междинни суми или обща сума.

Данни, използвани в отчет с обобщена таблица

Отчет с обобщена таблица на базата на същите данни показва междинни суми и общи суми и предоставя кратко резюме, което можете да обхванете с един поглед.

Обобщени и сумирани данни в отчет с обобщена таблица

Възможно е създаването и работата с обобщени таблици да изисква някаква първоначална подготовка на вашите данни и трябва да сте запознати с някои концепции.

За подробна информация, която да ви помогне да започнете, вижте следните статии:

Изчисляване на стойности в отчет с обобщена таблица

Полета за междинни и общи суми в отчет с обобщена таблица

Най-горе на страницата

Сумиране на данни на базата на едно или няколко условия

Като използвате функции за работен лист, можете да прилагате условия (известни също като критерии), така че да събирате само данните, които отговарят на зададени от вас условия.

Събиране на стойностите в диапазон на базата на едно условие с помощта на една функция или комбинация от функции

Използвайте функцията SUMIF или вложете функцията SUM във функцията IF.

Можете да събирате числа на базата на един критерий чрез функцията SUMIF или с помощта на комбинация от функциите SUM и IF.

Например формулата =SUMIF(A2:A6;">20") събира само числата в диапазона от A2 до A6, които са по-големи от 20.

Забележка: Следващата информация е малко по-техническа, но определено си струва да я знаете.

Ако функцията SUMIF не съществуваше, пак щяхте да можете да получите същия отговор с комбиниране на функциите IF и SUM. Комбинирането на функциите по този начин е известно също като "влагане", тъй като една функция се използва в друга функция.

За да създадете формула, която наподобява функцията SUMIF, можете да въведете формула, която третира диапазона от A2 до A6 като масив, което означава, че диапазонът се счита за един запис, съдържащ пет клетки.

Вашата формула изглежда по следния начин: {=SUM(IF(A2:A6>20;A2:A6))}. Фигурните скоби, които ограждат формулата, показват, че това е формула за масив. Формулата за масив изисква по-специално третиране: Вместо да въвеждате фигурните скоби сами, трябва да натиснете CTRL+SHIFT+ENTER – Excel тогава огражда формулата =SUM(IF(A2:A6>20;A2:A6)) с фигурни скоби. Ако въведете фигурните скоби сами, няма да създадете формула за масив.

Изпробвайте това!    

Копирайте данни от таблицата по-долу в работен лист в клетка A1. След като поставите данните, ще забележите, че клетка A10 съдържа грешка #VALUE!. Това означава, че трябва да конвертирате клетка A10 във формула за масив. Направите това, като натиснете F2 и след това натиснете CTRL+SHIFT+ENTER. Excel показва резултата (65) в клетките A8 и A10.

Как да копирате данните от примерния работен лист

  • Създайте празна работна книга или работен лист.

  • Изберете примера от темата в "Помощ".

    Забележка: Не избирайте заглавките на редовете или колоните.

    Избиране на пример от ''Помощ''
    Избиране на пример от ''Помощ''
  • Натиснете CTRL+C.

  • В работния лист изберете клетката A1 и натиснете CTRL+V.

  • За да превключвате между показване на резултатите и показване на формулите, които връщат резултатите, натиснете клавиша CTRL+` (тежко ударение).

1

2

3

4

5

6

7

8

9

10

A

Тегло

18

29

36

11

16

Използване на функцията SUMIF

=SUMIF(A2:A6;">20")

Използване на функциите SUM и IF

=SUM(IF(A2:A6>20;A2:A6))

Също така статията в Базата знания XL: Кога да използвате SUM(IF()) вместо CountBlank() предоставя допълнителни указания кога да използвате функцията SUMIF.

Най-горе на страницата

Събиране на стойностите в колона на базата на едно или няколко условия с помощта на функция

Използвайте функцията DSUM за сумиране на числата в поле (колона) от записи в списък или база данни, които отговарят на зададени от вас условия (наричане също критерии).

Можете също да използвате функцията DSUM, когато имате списък с колони и смятате, че е по-лесно да дефинирате вашите условия в отделен диапазон от клетки, вместо да използвате вложена функция.

За повече информация вж. статията DSUM функция.

Най-горе на страницата

Събиране на стойностите в диапазон на базата на няколко условия с помощта на съветник (само за Excel 2007)

Можете да използвате съветника за условно сумиране (Формули > Решения > Условно сумиране) за събиране на стойности, които се базират на няколко условия.

Важно: Съветникът за условно сумиране е наличен само в Excel 2007 и предишните версии. За повече информация вижте Какво стана с добавката "Съветник за условно сумиране"?.

Ако тази команда не е налична (възможно е групата Решения също да не е видима), инсталирайте и заредете добавката Analysis ToolPak.

Как да заредите Analysis ToolPak

Щракнете върху бутона Microsoft Office Изображение на бутона Office , върху Опции на Excel и след това върху категорията Добавки.

  1. В списъка Управление изберете Добавки на Excel, а след това щракнете върху Започване.

  2. В списъка Налични добавки изберете полето Analysis ToolPak и след това щракнете върху OK.

  3. Ако съветникът се появява в списъка Добавки на неактивно приложение, изберете съветника и след това щракнете върху Започване, за да го направите активен.

  4. Ако е необходимо, следвайте инструкциите на инсталиращата програма.

За да използвате съветника за условно сумиране, изберете диапазона от данни и щракнете върху командата Условно сумиране (групата Решения на раздела Формули). Следвайте стъпки от 1 до 4, за да изпълните съветника. Следващата илюстрация показва стъпка 2 на съветника, в която има едно условие: Стойностите, които ще се сумират, трябва да бъдат по-големи от 100.

Съветник за условно сумиране: стъпка 2 от 4

Най-горе на страницата

Събиране на стойностите в диапазон на базата на множество условия с помощта на една функция или комбинация от функции

Използвайте функцията SUMIFS. Задайте диапазона, който искате да сумирате, диапазона, който съдържа условията, и условията, които се прилагат към диапазона с условия. Като алтернатива можете да вложите функциите SUM и IF.

За повече информация вж. статията SUMIFS функция.

Също така вижте следните статии в Базата знания за допълнителна информация:

Най-горе на страницата

Сумиране на стойности на квадрат и стойности в масив

Можете да използвате функции за работен лист, за да събирате стойности в две или повече колони, които си съответстват. Този тип операция може да бъде толкова проста, колкото определянето на претеглена средна стойност (вж. следващия пример, който изчислява средна оценка), или може да бъде сложно статистическо или инженерно изчисление.

Сумиране на произведението от съответните стойности в един или няколко масива

Използвайте функцията SUMPRODUCT. Например можете да изчислите средната оценка на ученик, като използвате функцията SUMPRODUCT и разделите резултата на броя кредити, както е показано на илюстрацията по-долу.

Умножаване на съответните стойности в два масива

Формулата =SUMPRODUCT(A2:A6;B2:B6)/SUM(B2:B6) умножава оценката по всеки предмет по броя кредити за него, сумира тези произведения (61,3), разделя сумата на общия брой кредити (18) и определя средната оценка (3,41). Този тип изчисление е известно също като претеглена средна стойност.

Можете също да използвате функцията SUMPRODUCT в по-сложни математически операции, като умножите няколко масива.

Функцията SUMPRODUCT е лесна за използване с масиви, които имат еднакви измерения, но можете също да използвате функцията SUM във формула за масив, ако имате нужда от по-голяма гъвкавост.

За по-подробна информация вижте статията Функция SUMPRODUCT.

Следващите четири функции обикновено се използват само в разширени статистически или сложни математически приложения, така че предоставяме само кратко описание. За повече информация щракнете върху името на функция, за да отидете директно в нейната тема за справка.

  • Функция SUMSQ  Връща сумата от квадратите на списък от числа или стойности в диапазон. Например SUMSQ(2,3) връща 13.

  • Функция SUMX2PY2  Събира сумата от квадратите на съответните стойности в два масива.

  • Функция SUMX2MY2  Връща сумата от разликите между квадратите на съответните стойности в два масива.

  • Функция SUMXMY2  Връща сумата от квадратите на разликите между съответните стойности в два масива.

Най-горе на страницата

Специални случаи (подвижна обща сума, уникални стойности)

Можете да използвате функции за работен лист, за да създавате общи суми, които се актуализират автоматично при добавяне на още данни в диапазон или таблица, или да събирате само уникалните стойности, които се намират в диапазон или таблица.

Създаване на подвижна обща сума с помощта на функция

Използвайте функцията SUM. Можете например да създадете заглавия на колони в работен лист с имена Приходи, Разходи и Баланс.

  1. В първата клетка, под колоната Баланс въведете своя начален баланс.

  2. В клетката непосредствено под баланса (в този случай C3) въведете формула, която събира стойността от колоната "Приходи" на същия ред и изважда стойността от колоната "Разходи" (=SUM(C2;A3;-B3).

  3. Плъзнете формулата надолу за всички нови редове, които добавяте. В следващия пример тя е плъзната до ред 4.

    Примерът може да се разбере по-лесно, ако го копирате в празен работен лист.

    Как се копира пример

    • Създайте празна работна книга или работен лист.

    • Изберете примера от темата в "Помощ".

      Забележка: Не избирайте заглавията на редовете или колоните.

      Избиране на пример от ''Помощ''

      Избиране на пример от помощта

    • Натиснете CTRL+C.

    • В работния лист изберете клетката A1 и натиснете CTRL+V.

    • За да превключвате между показване на резултатите и показване на формулите, които връщат резултатите, натиснете клавиша CTRL+` (тежко ударение) или в раздела Формули, в групата Проверка на формули щракнете върху бутона Показване на формули.

1

2

3

4

A

B

C

Приходи

Разходи

Баланс

500

1000

625

=SUM(C2;A3;-B3)

1000

740

=SUM(C3;A4;-B4)

Можете да използвате текущия баланс, както бихте използвали чеков регистър, за да следите как се увеличават или намаляват стойностите на елементите в клетките, когато въвеждате нови елементи и стойности с течение на времето.

За повече информация вж. статията Изчисляване на текущ баланс.

Най-горе на страницата

Събиране на уникалните стойности в диапазон с помощта на сложна формула

В колона въведете списък от стойности, който съдържа дублирани стойности, а след това използвайте комбинация от функциите SUM, IF и FREQUENCY, за да сумирате само уникалните стойности, намерени в този диапазон.

Най-горе на страницата

Включване на стойности от други работни листове или работни книги във формула

Можете да събирате или изваждате клетки или диапазони от данни от други работни листове или работни книги във формула, като включите препратка към тях. За да създадете препратка към клетка или диапазон в друг работен лист или работна книга, използвайте инструкциите в следващата таблица.

За препратка към:

Въведете това

Примери

Клетка или диапазон в друг работен лист в същата работна книга

Името на работния лист, последвано от удивителен знак и от препратката към клетката или името на диапазона.

Лист2!B2:B4
Лист3!СтойностиПродажби

Клетка или диапазон в друга работна книга, която е отворена в момента

Името на файла на работната книга в скоби ([]) и името на работния лист, последвано от удивителен знак и от препратката към клетката или името на диапазона.

[МоятаРаботнаКнига.xlsx] Лист1!A7

Клетка или диапазон в друга работна книга, която не е отворена

Пълният път и името на файла на работната книга в скоби ([]) и името на работния лист, последвано от удивителен знак и от препратката към клетката или името на диапазона. Ако пълният път съдържа знаци за интервал, оградете от началото на пътя до края на името на работния лист с единични кавички (вж. примера).

['C:\Моите документи\[МоятаРаботнаКнига.xlsx]Лист1'!A2:A5

Най-горе на страницата

Сумиране или изваждане на стойности за дата и час

Можете да използвате функциите за дата и час и дори просто събиране или изваждане, за да изчислявате изминало време, да преценявате датите на завършване на проекти и др. Следващата таблица показва някои формули, които изчисляват изминалото време и дни. Обърнете внимание, че ако изваждате дата или час, което води до отрицателна стойност, Excel показва знаците ### в клетката, съдържаща формулата.

Примерът може да се разбере по-лесно, ако го копирате в празен работен лист.

Как се копира пример

  • Създайте празна работна книга или работен лист.

  • Изберете примера от темата в "Помощ".

    Забележка: Не избирайте заглавията на редовете или колоните.

    Избиране на пример от ''Помощ''

    Избиране на пример от помощта

  • Натиснете CTRL+C.

  • В работния лист изберете клетката A1 и натиснете CTRL+V.

  • За да превключвате между показване на резултатите и показване на формулите, които връщат резултатите, натиснете клавиша CTRL+` (тежко ударение) или в раздела Формули, в групата Проверка на формули щракнете върху бутона Показване на формули.

1

2

3

4

5

A

B

C

Начален час

Краен час

Формула за изминалото време

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

11:55:24 преди обяд

2:48:13 след обяд

=B2-A2

Показва изминалото време в часове, минути и секунди. В този случай Excel показва резултата (2:52:49 преди обяд) във формат "преди обяд", тъй като разликата е по-малко от 12 часа. Ако разликата беше 12 часа или повече, Excel щеше да покаже формат "след обяд".

Начална дата

Крайна дата

Формула за изминалите дни

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

28.5.2008 г.

03.6.2008 г.

=B5-A5

Показва изминалите дни като число (6).

Работата със стойности за дата и час може да доведе до неочаквани резултати, така че работете внимателно.

Най-горе на страницата

Показване на изчисления на лентата на състоянието на Excel

За бърз поглед върху общите суми на клетките, които са избрани в работния лист, погледнете в лентата на състоянието на Excel (лентата на състоянието е в долния край на прозореца на документа).

Когато са избрани една или повече клетки, информация за данните в тези клетки се показва на лентата на състоянието на Excel. Например ако са избрани четири клетки в работния лист и те съдържат стойности 2, текстов низ (например "облак"), 3 и 4, всяка една от следните стойности може да се покаже по едно и също време на лентата на състоянието: "Средно", "Брой", "Брой числа", "Минимум", "Максимум" и "Сума". Щракнете с десния бутон върху лентата на състоянието, за да покажете или скриете някои или всички стойности. Тези стойности са показани на следващата фигура.

Лента на състоянието, която показва изчисления и преброявания на избраните клетки

Най-горе на страницата

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×