Изтеглете примерната работна книга
Този урок ще покаже как да сумирате данни, съответстващи на конкретни номера на седмицата в Excel и Google Sheets.
Сума Ако по номер на седмицата
За да „сумираме ако“ по номер на седмица, ще използваме функцията SUMIFS. Но първо трябва да добавим помощна колона, съдържаща функцията WEEKNUM.
The Номер на седмицата помощната колона се изчислява с помощта на функцията WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
След това ще използваме функцията SUMIFS, за да сумираме всички Продажби които се провеждат в определена Номер на седмицата.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Сума, ако по номер на седмицата - без помощна колона
Методът на помощната колона е лесен за следване, но можете също да повторите изчислението в една формула, като използвате функцията SUMPRODUCT в комбинация с функцията WEEKNUM, за да сумирате Общ брой продажби от Номер на седмицата.
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
В този пример можем да използваме функцията SUMPRODUCT за извършване на сложни изчисления на „сума, ако“. Нека преминем през горния пример.
Това е нашата последна формула:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Първо, функцията SUMPRODUCT изброява масива от стойности от диапазоните на клетките:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
След това функцията WEEKNUM изчислява Номер на седмицата на всеки от Дати на продажби.
Функцията WEEKNUM не е проектирана да работи със стойности на масива, затова трябва да добавим нула („+0“), за да WEEKNUM да обработи правилно стойностите.
| 1 | = СУМПРОДУКТ (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Номер на седмицата стойности, равни на 1, се променят на TRUE.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
След това двойните тирета (-) преобразуват стойностите TRUE и FALSE в 1s и 0s:
| 1 | = СУМПРОДУКТ ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
След това функцията SUMPRODUCT умножава всяка двойка записи в масивите, за да създаде масив от Брой продажби които имат а Номер на седмицата от 1:
| 1 | = СУМПРОДУКТ ({4; 0; 0; 0; 0; 0; 0}) |
И накрая, числата в масива се сумират заедно:
| 1 | =4 |
След това тази формула се повтаря за другите възможни стойности на Номер на седмицата.
Повече подробности за използването на булеви изрази и командата “-” във функция SUMPRODUCT можете да намерите тук.
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | = SUMPRODUCT (-(WEEKNUM ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Сума Ако по номер на седмица в Google Таблици
Тези формули работят абсолютно същите в Google Sheets, както и в Excel.
Функцията WEEKNUM обаче е по -гъвкава в Google Sheets, отколкото в Excel, и приема входове и изходи от масиви. Следователно операцията {Array} +0 във формулата WEEKNUM (B3: B9+0,1) не е задължителна.
Пълната формула на SUMPRODUCT може да бъде записана в Google Sheets като:
| 1 | =СУМПРОДУКТ(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
