В тази статия ще научим как да изчисляваме сумата по групи в редове и колони в Excel.
За Instance имате голям списък с данни и трябва да намерите SUM на числата с набор от n стойности.
За тази статия ще се нуждаем от използването на следните функции:
- SUM функция
- OFFSET функция
SUM функцията е вградена математическа функция, която връща SUM на числата, даден аргумент на функцията
Синтаксис:
= SUM (номер1, [номер2], …)
Функцията OFFSET връща клетка или определен брой диапазони от клетки, преминаващи през препратка към клетка.
Синтаксис:
= OFFSET (справка, редове, колони, [височина], [ширина])
Справка: препратка към клетка, от която да започнете
Редове: брой редове, прекосени от препратката към клетката
Колони: брой колони, прекосени от препратката към клетката.
[височина]: височина или брой клетки в колоната за избор.
[width]: ширина или брой клетки в реда за избор.
Първите 3 аргумента на функцията е пътят към необходимата клетка.
Четвърти и пети аргумент на функцията показва броя на клетките за връщане.
Сега ще направим формула от тези функции. Тук ще дадем данните и трябваше да намерим SUM с определен брой клетки.
Използвайте формулата:
= SUM (OFFSET (справка, ro, co, he, wi))
Обяснение:
- Функцията OFFSET започва от препратката и хода ро брой редове и co брой колони от препратката към клетката.
- Сега функцията започва да избира групи от числа той брой стойности в колоната и ние брой стойности в реда.
- Функцията OFFSET сега връща масива от числа към функцията SUM.
- Функцията SUM добавя всички числа и връща SUM.
Нека тестваме тази формула, като я изпълним на пример
Тук имаме данни от A1: D16. трябва да намерим сумата на количеството и общата цена в група от 5 с дадената препратка от клетката А1.
Първо трябва да намерим SUM на първите 5 количества, които са стойности {100, 23, 38, 91, 53}
Използвайте формулата:
= SUM (OFFSET (A1, 1, 2, 5, 1))
Обяснение:
- Функцията OFFSET започва от клетката A1 и достига до клетката C2, пресичайки 1 ред и 2 колони.
- Имаме нужда от първите 5 клетки от С2 до С6 клетка. Така че височината 5 и ширината ще ни върнат числата, както е показано по -долу.
= SUM ({100; 23; 38; 91; 53})
- Сега функцията SUM връща SUM на стойностите.
Тук A1 се дава като препратка към клетка, а останалите аргументи се предоставят директно на функцията.
Както можете да видите в горната снимка, излиза сумата от първите пет количества 305 .
Сега ще получим SUM за всички останали групи от 5, като променим аргумента Row на функцията
Използвайте формулата:
= SUM (OFFSET (A1, 6, 2, 5, 1))
Както можете да видите SUM на следващите пет в снимката по -горе. За следващите пет променете аргумента на ред на 11.
Вземете SUM на TotalPrice за същото, като промените аргумента Column на функцията.
Както можете да видите от горната формула, че можете да получите стойностите на SUM по групи, използвайки функцията OFFSET.
Забележки: Функцията връща #VALUE! грешка, ако аргументът на функцията е нецифрен.
Надявам се, че тази статия за това как да сумирате чрез OFFSET групи в редове и колони в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията OFFSET в Excel
Как да използвате функцията SUM в Excel
Как да използвате функцията ISFORMULA в Excel
Как да премахнете текст в Excel, започвайки от позиция
Как да използвате функцията ROWS в Excel
Валидиране на текстови записи
Създайте падащ списък в Excel с цвят
Премахнете водещите и крайните интервали от текста в Excel
Популярни статии
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В
Конвертирайте инчове в крака и инчове в Excel 2016
50 Преки пътища в Excel за повишаване на вашата производителност