В тази статия ще се научим да връщаме SUM цена по делнични дни в Excel.
С прости думи, докато работите с пълни с данни данни. Понякога трябва да намерим сумата от стойност, която има условие. Условието е да получите сумата от стойностите на цените в отделни колони.
За тази статия ще се нуждаем от използването на следните функции:
- Функция SUMPRODUCT
- Функция COLUMN
- MOD функция
Сега ще направим формула от тези функции. Тук ще ни бъде даден списък със стойности и трябва да получим сумата от стойности, лежащи във всеки кратен на дадената n -та колона.
Използвайте формулата:
= SUMPRODUCT ( - (MOD (COLUMN (списък) - COLUMN (първо) + 1, n) = 0), списък)
списък : списък на стойностите по колоната
първо : първа клетка от списъка, която връща номера на колоната
н : стойности на n -та колона за добавяне.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук сме дали данни, съдържащи числа. Тук трябва да намерим SUM на числата във всяко кратно на третата колона. Така че за това сме задали списъци в колона за формулата.
Сега ще използваме формулата по -долу, за да получим SUM на числата на 3 -ти, 6 -ти, 9 -ти, 12 -ти и така нататък, докато списъкът приключи
Формула:
= SUMPRODUCT ( - (MOD (КОЛОНА (списък) - КОЛОНА (C5) + 1, P5) = 0), списък)
списък: списък на стойностите по колоната, дадени като използващи посочените диапазони.
C5: първата клетка от списъка, която връща номера на колоната, дадена като препратка към клетка
P5: стойността на n, дадена като препратка към клетката
Обяснение:
- MOD (COLUMN (списък) -COLUMN (C5)+1, P5) връща относителния номер на колоната за всеки n интервал. като
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Горният масив ще бъде съпоставен със стойност 0 и връща масив от True & False.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- Функцията SUMPRODUCT отчита стойност 1 за всяка получена TRUE стойност и 0 за FALSE стойност, както е показано по -долу.
= SUMPRODUCT ( - ({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})
- - преобразува TRUE в 1 и FALSE в 0.
- Стойностите, съответстващи на 1s, ще се добавят само както е показано в горната обяснена формула.
- Сега функцията SUMPRODUCT добавя всички стойности, както е показано по -долу.
= СУМПРОДУКТ ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
Сумата на стойностите {51 + 30 + 34 + 56} трябва да върне 171.
Тук масивът на функцията е даден като именен диапазон, а клетката като cell_reference. Натиснете Enter, за да получите резултата.
Както можете да видите в горната снимка, сумата от стойности, извлечени на всеки трети интервал, връща 171. Стойностите са маркирани под червени полета, както е показано в горната снимка.
Или можете да използвате списъчния масив, като използвате формулата по -долу:
= SUMPRODUCT ( - (MOD (КОЛОНА (C5: O5) - КОЛОНА (C5) + 1, P5) = 0), C5: O5)
Горната формула ще върне същия резултат. Има само един заместващ списък с име range се заменя с диапазона от масиви (C5: O5).
Както можете да видите, формулата работи добре. Сега трябва да копираме формулата за другите списъци. Така че използвайте CTRL + D или плъзнете надолу опцията на Excel.
Както можете да видите от горната формула, можете да получите условни стойности.
Бележки:
- Функцията SUMPRODUCT разглежда нецифрени стойности като 0s.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина, в противен случай формулата връща грешка.
Надявам се, че тази статия за това как да върнете SUM само всяка n -та колона с формули в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Конвертирайте инчове в крака и инчове в Excel 2016
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В