Как да сумирате дублиращи се стойности в Excel

Сумирането е популярна аритметична операция в Excel. Да предположим, че имаме списък със стоки в таблица и трябва да получим общата им цена. За да направите това, трябва да използвате функцията SUM. Или компанията иска да определи общото потребление на електроенергия за определен период. Отново трябва да обобщите тези данни.

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

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

Селективно сумиране в Excel

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

Функция SUMIF

Да предположим, че имаме такъв набор от данни.

Това е справка, предоставена от склада на магазина за зеленчуци. Въз основа на тази информация трябва да направим следното:

1. Определете колко остава на склад за определен артикул.
2. Изчислете балансите на инвентара заедно с цена, която съответства на дефинирани от потребителя правила.

Използване на функцията СУМЕСЛИ можем да изолираме специфични значения и да ги обобщим изключително. Нека изброим аргументите на този оператор:

1. Обхват. Това е набор от клетки, които трябва да бъдат анализирани за съответствие с определен критерий. В този диапазон може да има не само числови, но и текстови стойности.
2. Състояние. Този аргумент указва правилата, по които ще се избират данните. Например само стойности, които съответстват на думата „Круша“ или числа, по-големи от 50.
3. диапазон на сумиране. Ако не е необходимо, можете да пропуснете тази опция. Трябва да се използва, ако набор от текстови стойности се използва като диапазон за проверка на условие. В този случай трябва да посочите допълнителен диапазон с числови данни.

За да изпълним първата цел, която си поставихме, трябва да изберете клетката, в която ще бъде записан резултатът от изчисленията, и да напишете следната формула там: =SUMIF(A2:A9;”Бяло грозде”;B2:B9).

Резултатът ще бъде стойност 42. Ако имахме няколко клетки със стойност „Бяло грозде“, тогава формулата ще върне общата сума от всички позиции на този план.

Функция SUM

Сега нека се опитаме да се справим с втория проблем. Основната му трудност е, че имаме няколко критерия, на които диапазонът трябва да отговаря. За да го разрешите, трябва да използвате функцията SUMMESLIMN, чийто синтаксис включва следните аргументи:

1. диапазон на сумиране. Тук този аргумент означава същото като в предишния пример.
2. Диапазон от условия 1 е набор от клетки, в които да изберете тези, които отговарят на критериите, описани в аргумента по-долу.
3. Условие 1. Правило за предишния аргумент. Функцията ще избере само онези клетки от диапазон 1, които отговарят на условие 1.
4. Диапазон на условие 2, условие 2 и т.н.

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

Да предположим, че трябва да определим какво е общото тегло на ябълките, останали в склада, които струват повече от 100 рубли. За да направите това, напишете следната формула в клетката, в която трябва да бъде крайният резултат: =СУММЕСЛИМН(B2:B9;A2:A9;»яблоки*»;C2:C9;»>100″)

С прости думи, оставяме диапазона на сумиране същият, какъвто беше. След това предписваме първото условие и диапазона за него. След това поставихме изискването цената да бъде над 100 рубли.

Обърнете внимание на звездичката (*) като термин за търсене. Това показва, че всякакви други стойности могат да го следват.

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

Да предположим, че имаме такава таблица. Направен е с помощта на инструмента Smart Table. В него можем да видим дублирани стойности, поставени в различни клетки.

Третата колона изброява цените на тези артикули. Да кажем, че искаме да знаем колко ще струват общо повтарящите се продукти. Какво трябва да направя? Първо трябва да копирате всички дублирани данни в друга колона.

След това трябва да отидете в раздела „Данни“ и да кликнете върху бутона „Изтриване на дубликати“.

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

Специално преобразуване на паста

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

Трябва да ги копираме и да отидем в раздела „Начало“. Там трябва да отворите менюто под бутона „Вмъкване“. За да направите това, щракнете върху стрелката и в списъка, който се показва, намираме елемента „Специално поставяне“. Ще се появи подобен диалогов прозорец.

Транспониране на ред в колони

Поставете отметка в квадратчето до „Транспониране“ и щракнете върху OK. Този елемент разменя колони и редове. След това записваме функцията в произволна клетка СУМЕСЛИ.

Формулата в нашия случай ще изглежда така.

След това, като използвате маркера за автоматично попълване, попълнете останалите клетки. Можете също да използвате функцията МЕЖДИННИ СУМИ за да се обобщят стойностите на таблицата. Но първо трябва да зададете филтър за интелигентната таблица, така че функцията да брои само повтарящи се стойности. За да направите това, щракнете върху иконата със стрелка в заглавката на колоната и след това поставете отметки в квадратчетата до тези стойности, които искате да покажете.

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

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