Изтеглете примерната работна книга
Този урок ще покаже как да използвате функцията SUMIFS за сумиране на данни, съответстващи на конкретни месеци в Excel и Google Sheets.
Сума, ако по месец
Първо ще демонстрираме как да сумираме данни, съответстващи на дати, които попадат в определен месец и година.
Можем да използваме функцията SUMIFS, заедно с функциите DATE, YEAR, MONTH и EOMONTH, за да сумираме Брой продажби в рамките на всеки Месец.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (ГОДИНА (E3), МЕСЕЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
За да изградим формулата по -горе, започваме с дефиниране на периода от време за всеки месец. Използваме функцията DATE, за да определим първия ден от месеца (напр. 5/1/2021). Можем да направим това, като „твърдо кодираме“ датата във формулата:
| 1 | "> =" & ДАТА (2021,5,1) |
Или още по -добре, можем да направим критериите гъвкави, като се позовем на дата в клетката E3, извлечем годината и месеца на датата (и зададем деня равен на 1), както е показано в горния пример:
| 1 | "> =" & ДАТА (ГОД. (E3), МЕСЕЦ (E3), 1) |
За да определим последния ден от месеца, можем да използваме функцията EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Събирайки всички тези критерии, можем да напишем следната формула SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (ГОДИНА (E3), МЕСЕЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (ГОДИНА (E3), МЕСЕЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & ДАТА (ГОДА (E3), МЕСЕЦ (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Форматиране на месечни стойности
В този пример сме изброили месеци в колона E. Тези месечни стойности всъщност са дати, форматирани така, че да пропуснат деня, използвайки форматиране по избор.
Персонализираният формат на данни е „ммм гггг“, за да се покаже май 2022 г.
Сума по месеци за няколко години
Горният пример обобщава данни с дати, които попадат в рамките на определен месец и година. Вместо това можете да сумирате данни с дати, които попадат в рамките на един месец през всяка година, като използвате функцията SUMPRODUCT.
| 1 | = СУМПРОДУКТ (C3: C8,-(МЕСЕЦ (B3: B8) = МЕСЕЦ (G3))) |
В този пример използваме функцията SUMPRODUCT, за да извършим сложни изчисления на „сума, ако“. Нека преминем през формулата.
Това е нашата последна формула:
| 1 | = СУМПРОДУКТ (C3: C8,-(МЕСЕЦ (B3: B8) = МЕСЕЦ (G3))) |
Първо, функцията SUMPRODUCT изброява Брой продажби за всеки Дата на продажба и след това сравнява месеца на всеки Дата на продажба срещу посоченото Месец, връщане на TRUE, ако месеците съвпадат, или FALSE, ако не са:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
След това двойните тирета (-) преобразуват стойностите TRUE и FALSE в 1s и 0s:
| 1 | = СУМПРОДУКТ ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
След това функцията SUMPRODUCT умножава всяка двойка записи в масивите:
| 1 | = СУМПРОДУКТ ({30; 0; 51; 0; 17; 0}) |
И накрая, числата в масива се сумират:
| 1 | =98 |
Повече подробности за използването на булеви изрази и командата “-” във функция SUMPRODUCT можете да намерите тук
Сума, ако по месец в Google Таблици
Тези формули работят абсолютно същите в Google Sheets, както и в Excel.
