Можете да сумирате колона с едно щракване, функцията AutoSum, функцията SUM, функцията за филтриране и чрез преобразуване на набор от данни в таблица.
Добавянето на колони или редове с числа е нещо, което повечето от нас трябва да правят доста често. Например, ако съхранявате важни данни като записи за продажби или ценови листи в клетките на една колона, може да искате бързо да научите общия брой на тази колона. Така че е необходимо да знаете как да сумирате колона в Excel.
Има няколко начина, по които можете да сумирате или сумирате колона/ред в Excel, включително с едно щракване, функцията AutoSum, функция SUM, функция за филтриране, функция SUMIF и чрез преобразуване на набор от данни в таблица. В тази статия ще видим различните методи за добавяне на колона или ред в Excel.
СУМАЙТЕ колона с едно щракване с помощта на лентата на състоянието
Най-лесният и бърз начин да изчислите общата стойност на колона е да щракнете върху буквата на колоната с числата и да проверите лентата „Състояние“ в долната част. Excel има лента на състоянието в долната част на прозореца на Excel, която показва различна информация за работен лист на Excel, включително средна стойност, брой и сума на избраните клетки.
Да предположим, че имате таблица с данни, както е показано по-долу, и искате да намерите общата сума на цените в колона Б.
Всичко, което трябва да направите, е да изберете цялата колона с числата, които искате да сумирате (колона B), като щракнете върху буквата B в горната част на колоната и погледнете лентата на състоянието на Excel (до контрола за увеличение).
Там ще видите общия брой на избраните клетки, заедно със средните стойности и стойностите за броене.
Можете също да изберете диапазон от данни от B2 до B11 вместо цялата колона и да видите лентата на състоянието, за да знаете общата сума. Можете също да намерите общия брой числа в един ред, като изберете реда със стойности вместо колона.
Предимството от използването на този метод е, че той автоматично игнорира клетките с текстови стойности и само сумира числата. Както можете да видите по-горе, когато избрахме цялата колона B, включително клетка B1 с текстово заглавие (Цена), тя само сумира числата в тази колона.
SUM колона с функция AutoSum
Друг най-бърз начин да обобщите колона в Excel е да използвате функцията AutoSum. AutoSum е функция на Microsoft Excel, която ви позволява бързо да добавяте набор от клетки (колона или ред), съдържащи числа/цели числа/десетични числа, като използвате функцията SUM.
В раздела „Начало“ и „Формула“ на лентата на Excel има команден бутон „AutoSum“, който ще вмъкне „функция SUM“ в избраната клетка при натискане.
Да предположим, че имате таблицата с данни, както е показано по-долу, и искате да сумирате числата в колона B. Изберете празна клетка точно под колоната или десния край на ред с данни (за да сумирате ред), който трябва да сумирате.
След това изберете раздела „Формула“ и щракнете върху бутона „AutoSum“ в групата „Библиотека с функции“.
Или отидете в раздела „Начало“ и кликнете върху бутона „Автоматична сума“ в групата Редактиране.
Така или иначе, след като щракнете върху бутона, Excel автоматично ще вмъкне „=SUM()“ в избраната клетка и ще подчертае диапазона с вашите числа (маршируващи мравки около диапазона). Проверете дали избраният диапазон е правилен и ако не е правилният диапазон, можете да го промените, като изберете друг диапазон. И параметрите на функцията ще се настройват автоматично според това.
След това просто натиснете Enter на клавиатурата си, за да видите сумата на цялата колона в избраната клетка.
Можете също да извикате функцията AutoSum с помощта на клавишна комбинация.
За да направите това, изберете клетката, която е точно под последната клетка в колоната, за която искате общата сума, и използвайте пряк път по-долу:
Alt+= (Натиснете и задръжте клавиша Alt и натиснете знака за равенство =И автоматично ще вмъкне функцията SUM и ще избере диапазона за нея. След това натиснете Enter, за да сумирате колоната.
AutoSum ви позволява бързо да сумирате колона или ред с едно щракване или натискане на клавишна комбинация.
Въпреки това, има известно ограничение за функцията AutoSum, тя няма да открие и избере правилния диапазон, в случай че има празни клетки в диапазона или клетка, която има текстова стойност.
Както можете да видите в горния пример, клетка B6 е празна. И когато въведохме функцията AutoSum в клетка B12, тя избира само 5 клетки по-горе. Това е, защото функцията възприема, че клетка B7 е краят на данните и връща само 5 клетки за общата сума.
За да коригирате това, трябва да промените диапазона, като щракнете и плъзнете с мишката или въведете ръчно правилните препратки към клетки, за да маркирате цялата колона и натиснете Enter. И ще получите правилния резултат.
За да избегнете това, можете също да въведете ръчно функцията SUM, за да изчислите сумата.
SUM колона, като въведете ръчно функцията SUM
Въпреки че командата AutoSum е бърза и лесна за използване, понякога може да се наложи да въведете ръчно функцията SUM, за да изчислите сумата на колона или ред в Excel. Особено, ако искате да добавите само някои от клетките във вашата колона или ако вашата колона съдържа празни клетки или клетки с текстова стойност.
Освен това, ако искате да покажете стойността на сумата в някоя от клетките в работния лист, различна от клетката точно под колоната или клетката след реда с числа, можете да използвате функцията SUM. С функцията SUM можете да изчислите сумата или общия брой клетки навсякъде в работния лист.
Синтаксисът на функцията SUM:
=SUM(число1, [число2],...).номер 1(задължително) е първата числова стойност, която трябва да се добави.номер 2(по избор) е втората допълнителна числова стойност, която трябва да се добави.
Докато номер 1 е задължителният аргумент, можете да сумирате до максимум 255 допълнителни аргумента. Аргументите могат да бъдат числата, които искате да добавите, или препратки към клетки към числата.
Друго предимство от ръчното използване на функцията SUM е, че събирате числа в несъседни клетки на колона или ред, както и множество колони или редове. Ето как ръчно използвате функцията SUM:
Първо изберете клетката, където искате да видите общата сума на колона или ред навсякъде в работния лист. След това започнете формулата, като напишете =SUM( в клетката.
След това изберете диапазона от клетки с числата, които искате да сумирате, или въведете препратките към клетките за диапазона, който искате да сумирате във формулата.
Можете или да щракнете и плъзнете с мишката, или да задържите клавиша shift и след това да използвате клавишите със стрелки, за да изберете диапазон от клетки. Ако искате да въведете препратка към клетка ръчно, въведете препратката към клетката на първата клетка от диапазона, последвана от двоеточие, последвана от препратката към клетката на последната клетка от диапазона.
След като въведете аргументите, затворете скобата и натиснете клавиша Enter, за да получите резултата.
Както можете да видите, въпреки че колоната има празна клетка и текстова стойност, функцията ви дава сумата от всички избрани клетки.
Сумиране на непродължителни клетки в колона
Вместо да сумирате набор от непрекъснати клетки, можете също да сумирате непродължителни клетки в колона. За да изберете несъседни клетки, задръжте клавиша Ctrl и щракнете върху клетките, които искате да добавите, или въведете ръчно препратки към клетки и ги разделете с comms (,) във формулата.
Това ще покаже сбора само от избраните клетки в колоната.
Сумиране на множество колони
Ако искате сумата от множество колони, изберете няколко колони с мишката или въведете референтната клетка на първата в диапазона, последвана от двоеточие, последвана от последната препратка към клетката на диапазона за аргументите на функцията.
След като въведете аргументите, затворете скобата и натиснете клавиша Enter, за да видите резултата.
Сумиране на несъседни колони
Можете също да сумирате несъседни колони, като използвате функцията SUM. Ето как:
Изберете която и да е клетка в работния лист, където искате да покажете общата сума на несъседните колони. След това стартирайте формулата, като напишете функцията =SUM( в тази клетка. След това изберете диапазона на първата колона с мишката или въведете ръчно препратката към диапазона.
След това добавете запетая и изберете следващия диапазон или въведете втората препратка към диапазона. Можете да добавите толкова диапазони, колкото искате по този начин и да разделите всеки от тях със запетая (,).
След аргументите затворете скобата и натиснете Enter, за да получите резултата.
Сумираща колона, използваща наименован диапазон
Ако имате голям работен лист с данни и искате бързо да изчислите общия брой числа в колона, можете да използвате наименувани диапазони във функцията SUM, за да намерите общата сума. Когато създавате наименовани диапазони, можете да използвате тези имена вместо препратките към клетките, което улеснява препращането към набори от данни в Excel. Лесно е да използвате именуван диапазон във функцията, вместо да превъртате надолу по стотици редове, за да изберете диапазона.
Друго добро нещо при използването на Named range е, че можете да се обърнете към набор от данни (диапазон) в друг работен лист в аргумента SUM и да получите стойността на сумата в текущия работен лист.
За да използвате именуван диапазон във формула, първо трябва да създадете такъв. Ето как създавате и използвате именуван диапазон във функцията SUM.
Първо, изберете диапазона от клетки (без заглавки), за който искате да създадете наименован диапазон. След това отидете на раздела „Формули“ и кликнете върху бутона „Дефиниране на име“ в групата „Дефинирани имена“.
В диалоговия прозорец Ново име посочете името, което искате да дадете на избрания диапазон в полето „Име:“. В полето „Обхват:“ можете да промените обхвата на посочения диапазон като цялата работна книга или конкретен работен лист. Обхватът определя дали посоченият диапазон ще бъде достъпен за цялата работна книга или само за конкретен лист. След това щракнете върху бутона „OK“.
Можете също да промените препратката на диапазона в полето „Отнася се за“.
Като алтернатива можете също да наименувате диапазон, като използвате полето „Име“. За да направите това, изберете диапазона, отидете в полето „Име“ отляво на лентата с формули (точно над буквата A) и въведете името, което искате да зададете на избрания период от време. След това натиснете Enter.
Но когато създадете именуван диапазон с помощта на полето Име, той автоматично задава обхвата на наименувания диапазон за цялата работна книга.
Сега можете да използвате създадения от вас именуван диапазон, за да намерите бързо стойността на сумата.
За да направите това, изберете всяка празна клетка навсякъде в работната книга, където искате да покажете резултата Sum. И въведете формулата SUM с посочения диапазон, както е нейните аргументи, и натиснете Enter:
=SUM(цени)
В горния пример формулата в лист 4 се отнася до колоната с име „Цени“ в лист 2, за да се получи сумата от колона.
Сумирайте само видимите клетки в колона с помощта на функция SUBTOTAL
Ако имате филтрирани клетки или скрити клетки в набор от данни или колона, използването на функцията SUM за сумиране на колона не е идеално. Тъй като функцията SUM включва филтрирани или скрити клетки в своето изчисление.
Примерът по-долу показва какво се случва, когато сумирате колона със скрити или филтрирани редове:
В горната таблица сме филтрирали колона B по цени, които са по-малки от 100. В резултат имаме някои филтрирани редове. Можете да забележите, че има филтрирани/скрити редове в таблицата по липсващите номера на редове.
Сега, когато сумирате видимите клетки в колона B с помощта на функцията SUM, трябва да получите „207“ като стойност на сумата, но вместо това тя показва „964“. Това е така, защото функцията SUM също взема предвид филтрираните клетки при изчисляване на сумата.
Ето защо не можете да използвате функцията SUM, когато са включени филтрирани или скрити клетки.
Ако не искате филтрираните/скрити клетки да бъдат включени в изчислението при сумиране на колона и искате да сумирате само видимите клетки, тогава трябва да използвате функцията SUBTOTAL.
SUBTOTAL Функция
SUBTOTAL е мощна вградена функция в Excel, която ви позволява да извършвате различни изчисления (SUM, AVERAGE, COUNT, MIN, VARIANCE и други) върху диапазон от данни и връща общ или обобщен резултат от колоната. Тази функция само обобщава данните във видимите клетки, като игнорира филтрирани или скрити редове. SUBTOTAL е универсална функция, която може да изпълнява 11 различни функции във видими клетки на колона.
Синтаксисът на функцията SUBTOTAL:
=ОБЩО (номер_функция, ref1, [ref2], ...)Аргументи:
номер_функция(задължително)– Това е номер на функция, който определя коя функция да се използва за изчисляване на общата сума. Този аргумент може да приеме всяка стойност от 1 до 11 или от 101 до 111. Тук трябва да сумираме видимите клетки, като игнорираме филтрираните клетки. За това трябва да използваме „9“.ref1(задължително)– Първият именуван диапазон или препратка, която искате да сумирате.ref2(по избор) – Вторият именуван диапазон или препратка, която искате да сумирате. След първата препратка можете да добавите до 254 допълнителни препратки.
Сумиране на колона с помощта на функция SUBTOTAL
Ако искате да сумирате видими клетки и да изключите филтрирани или скрити клетки, следвайте тези стъпки, за да използвате функцията SUBTOTAL за сумиране на колона:
Първо, трябва да филтрирате таблицата си. За да направите това, щракнете върху която и да е клетка във вашия набор от данни. След това отидете до раздела „Данни“ и щракнете върху иконата „Филтър“ (икона на фуния).
Стрелките ще се появят до заглавките на колоните. Щракнете върху стрелката до заглавката на колоната, с която искате да филтрирате таблицата. След това изберете опцията за филтриране, която искате да приложите към вашите данни. В примера по-долу искаме да филтрираме колона B с числа по-малки от 100.
В диалоговия прозорец Персонализиран автофилтър въвеждаме „100“ и щракваме върху „OK“.
Числата в колоната се филтрират по стойности, по-малки от 100.
Сега изберете клетката, в която искате да покажете сумата и започнете да въвеждате функцията SUBTOTAL. След като отворите функцията SUBTOTAL и въведете скобата, ще видите списък с функции, които можете да използвате във формулата. Щракнете върху „9 – SUM“ в списъка или въведете „9“ ръчно като първи аргумент.
След това изберете диапазона от клетки, който искате да сумирате, или въведете ръчно препратката за диапазон и затворете скобата. След това натиснете Enter.
Сега ще получите сумата (междинна сума) само от видимите клетки – „207“
Като алтернатива можете също да изберете диапазона (B2:B11) с числата, които искате да добавите, и да щракнете върху „AutoSum“ под раздела „Начало“ или „Формули“.
Той автоматично ще добави функцията SUBTOTAL в края на таблицата и ще обобщи резултата.
Преобразувайте данните си в таблица на Excel, за да получите сумата от колоната
Друг лесен начин, който можете да използвате, за да сумирате колоната си, е като конвертирате данните си от електронна таблица в таблица на Excel. Преобразувайки данните си в таблица, можете не само да сумирате колоната си, но и да изпълнявате много други функции или операции с вашия списък.
Ако вашите данни все още не са във формат на таблица, трябва да ги конвертирате в таблица на Excel. Ето как да конвертирате данните си в таблица на Excel:
Първо изберете всяка клетка от набора от данни, която искате да преобразувате в таблица на Excel. След това отидете на раздела „Вмъкване“ и щракнете върху иконата „Таблица“.
Или можете да натиснете пряк път Ctrl+T, за да преобразувате диапазона от клетки в таблица на Excel.
В диалоговия прозорец Създаване на таблица потвърдете диапазона и щракнете върху „OK“. Ако вашата таблица има заглавки, оставете отметната опцията „Моята таблица има заглавки“.
Това ще преобразува вашия набор от данни в таблица на Excel.
След като таблицата е готова, изберете която и да е клетка в нея. След това отидете до раздела „Дизайн“, който се появява само когато изберете клетка в таблицата, и поставете отметка в квадратчето „Общ ред“ под групата „Опции за стил на таблица“.
След като отметнете опцията „Общ ред“, нов ред веднага ще се появи в края на вашата таблица със стойности в края на всяка колона (както е показано по-долу).
И когато щракнете върху клетка в този нов ред, ще видите падащо меню до тази клетка, от което можете да приложите функция, за да получите общо. Изберете клетката в последния ред (нов ред) на колоната, която искате да сумирате, щракнете върху падащото меню до нея и се уверете, че функцията „SUM“ е избрана от списъка.
Можете също да промените функцията на Average, Count, Min, Max и други, за да видите съответните им стойности в новия ред.
Сумирайте колона въз основа на критерии
Всички предишни методи ви показаха как да изчислите общата сума на цялата колона. Но какво ще стане, ако искате да сумирате само конкретни клетки, които отговарят на критерии, а не всички клетки. След това трябва да използвате функцията SUMIF вместо функцията SUM.
Функцията SUMIF търси конкретно условие в диапазон от клетки (колона) и след това сумира стойностите, които отговарят на даденото условие (или стойности, съответстващи на клетките, които отговарят на условието). Можете да сумирате стойности въз основа на числово състояние, текстово състояние, условие за дата, заместващи знаци, както и въз основа на празни и непразни клетки.
Синтаксис на функция SUMIF:
=SUMIF(диапазон, критерии, [сума_диапазон])Аргументи/параметри:
обхват– Диапазонът от клетки, в който търсим клетките, които отговарят на критериите.критерии– Критериите, които определят кои клетки трябва да бъдат сумирани. Критерият може да бъде число, текстов низ, дата, препратка към клетка, израз, логически оператор, заместващ знак, както и други функции.сума_диапазон(по избор) – Това е диапазонът от данни със стойности за сумиране, ако съответният запис на диапазон отговаря на условието. Ако този аргумент не е посочен, тогава вместо това „диапазонът“ се сумира.
Да предположим, че имате набор от данни по-долу, който съдържа данни за продажбите на всеки представител от различни региони и искате да сумирате сумата на продажбите само от региона „Юг“.
Можете лесно да направите това със следната формула:
=SUMIF(B2:B19,"Юг",C2:C19)Изберете клетката, където искате да покажете резултата, и въведете тази формула. Формулата по-горе SUMIF търси стойността „Юг“ в колона B2:B19 и събира съответната сума на продажбите в колона C2:C19. След това показва резултата в клетка E7.
Можете също да се обърнете към клетката, която съдържа текстово условие, вместо директно да използвате текста в аргумента за критерии:
=SUMIF(B2:B19,E6,C2:C19)
Това е.