Изтеглете примерната работна книга
Този урок ще покаже как да се изчислят междинните суми по групи, използвайки функцията SUMIFS в Excel и Google Sheets.
Таблица между междинна сума по категория или група
Първо, ние ще демонстрираме как да създадем динамична обобщена таблица с междинни суми от диапазон от данни в Excel 365 нататък или в Google Sheets.
Използваме функцията UNIQUE и функцията SUMIFS за автоматично междинно изчисление на Брой продукти от Продуктова група:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
За да създадем тази таблица с междинни суми, използваме стандартното приложение на функцията SUMIFS, за да сумираме Брой продукти които съответстват на всеки Продуктова група. Въпреки това, преди това да е възможно, трябва да създадем списък с уникални Групи продукти. Потребителите на Microsoft Excel 365 и Google Sheets имат достъп до функцията UNIQUE за създаване на динамичен списък с уникални стойности от диапазон от клетки. В този пример добавяме следната формула към клетка E3:
| 1 | = УНИКАЛЕН (B3: B11) |
Когато тази формула бъде въведена, автоматично се създава списък под клетката, за да се покажат всички уникални стойности, намерени в Продуктова група диапазон от данни. В този пример списъкът се разшири, за да обхване E3: E5, за да покаже и трите уникални Продуктова група стойности.
Това е функция с динамичен масив, където размерът на списъка с резултати не е необходимо да се дефинира и той автоматично ще се свива и расте с промяната на стойностите на входните данни.
Обърнете внимание, че в Excel 365 функцията UNIQUE не е чувствителна към регистъра, но в Google Sheets е така. Помислете за списъка {“A”; „А“; „В”; "° С"}. Изходът на функцията UNIQUE зависи от програмата:
- {“А”; „В”; „C“} в Excel 365
- {“А”; „А“; „В”; „C“} в Google Таблици
Ако използвате версия на Excel преди Excel 365, ще трябва да използвате различен подход. Това е обсъдено в следващия раздел.
Таблица с междинни суми по категория или група - предварително Excel 365
Ако използвате версия на Excel преди Excel 365, функцията UNIQUE не е налична за използване. За да повторите същото поведение, можете да комбинирате функцията INDEX и функцията MATCH с функция COUNTIF, за да създадете формула за масив, за да създадете списък с уникални стойности от диапазон от клетки:
| 1 | {= ИНДЕКС ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
За да функционира тази формула, фиксираните препратки към клетки трябва да бъдат написани внимателно, като функцията COUNTIF препраща диапазона $ E $ 2: E2, който е диапазонът, започващ от E2 до клетката над клетката, съдържаща формулата.
Формулата също трябва да бъде въведена като формула на масив чрез натискане на CTRL + SHIFT + ENTER, след като е написана. Тази формула е а Формула с 1-клетъчен масив, които след това могат да бъдат копирани в клетките E4, E5 и т.н. Не въвеждайте това като формула на масив за целия диапазон E3: E5 в едно действие.
По същия начин, както в предишния пример, след това се използва функция SUMIFS за междинна сума на Брой продукти от Продуктова група:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Сума по категория или група - междинни суми в таблици с данни
Като алтернатива на метода за обобщена таблица, показан по -горе, можем да добавим междинни суми директно в таблица с данни. Ще демонстрираме това, като използваме IF функциите заедно с функцията SUMIFS, за да добавим a Междинна сума по групи към оригиналната таблица с данни.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Този пример използва SUMIFS функция, вложена в IF функция. Нека разбием примера на стъпки:
За да добавим обобщена статистика директно в таблица с данни, можем да използваме функцията SUMIFS. Започваме, като обобщим Брой продукти които съответстват на съответните Продуктова група:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Тази формула създава междинна стойност за всеки ред данни. Да се показват междинни суми само в първия ред с данни на всеки Продуктова група, ние използваме функцията IF. Имайте предвид, че данните вече трябва да бъдат сортирани по Продуктова група за да се уверите, че междинните суми се показват правилно.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Функцията IF сравнява всеки ред данни Продуктова група стойност с реда с данни над него и ако те имат една и съща стойност, тя извежда празна клетка („”).
Ако Продуктова група стойностите са различни, сумата се показва. По този начин всеки Продуктова група сумата се показва само веднъж (в реда на първата си инстанция).
Сортиране на набори от данни по групи
Ако данните още не са сортирани, все още можем да използваме същата формула за междинната сума.
Наборът от данни по -горе не е сортиран по Продуктова група, така че Междинна сума по групи колоната показва всяка междинна сума повече от веднъж. За да получим данните в желания от нас формат, можем да изберем таблицата с данни и да кликнете върху „Сортиране от A до Z“.
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме някои от формулите без заключени клетъчни препратки:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Използване на обобщени таблици за показване на междинни суми
За да се премахне изискването за предварително сортиране на данните по Продуктова група, вместо това можем да използваме силата на обобщените таблици, за да обобщим данните. Обобщените таблици автоматично изчисляват междинните суми и показват общите и промените в няколко различни формата.
Сума по категория или група в Google Таблици
Тези формули работят същите в Google Sheets, както и в Excel. Функцията UNIQUE обаче е чувствителна към регистъра в Google Sheets.
