Преузмите пример радне свеске
Овај водич ће показати како се помоћу функција СУМПРОДУЦТ и СУМИФС збрајају подаци који задовољавају одређене критеријуме на више листова у Екцелу и Гоогле табелама.
Редовна сума на више листова
Понекад ваши подаци могу обухватити неколико радних листова у Екцел датотеци. Ово је уобичајено за податке који се периодично прикупљају. Сваки лист у радној свесци може да садржи податке за одређени временски период. Желимо формулу која сумира податке садржане у два или више листова.
Функција СУМ омогућава вам да једноставно саберете податке на више листова помоћу а 3Д Референце:
| 1 | = СУМ (Лист1: Лист2! А1) |
Међутим, то није могуће са функцијом СУМИФС. Уместо тога, морамо користити сложенију формулу.
Зброј ако на више листова
Овај пример ће сажети Број планираних испорука за сваки Купац на више радних листова, од којих сваки садржи податке који се односе на различити месец, користећи функције СУМИФС, СУМПРОДУЦТ и ИНДИРЕЦТ:
| 1 | = СУМПРОДУЦТ (СУМИФС (ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Д3: Д7"), ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Ц3: Ц7"), Х3)) |
Прођимо кроз ову формулу.
Корак 1: Креирајте СУМИФС формулу само за 1 улазни лист:
Користимо функцију СУМИФС да збројимо Број планираних испорука од стране Купац за један улазни лист са подацима:
| 1 | = СУМИФС (Д3: Д7, Ц3: Ц7, Х3) |
Корак 2: Додајте формулу референцу на лист
Задржавамо резултат формуле исти, али прецизирамо да су улазни подаци у позваном листу 'Корак 2'
| 1 | = СУМИФС ('Корак 2'! Д3: Д7, 'Корак 2'! Ц3: Ц7, Х3) |
Корак 3: Уметните се у функцију СУМПРОДУЦТ
Да бисмо припремили формулу за извођење СУМИФС прорачуна на више листова, а затим за сумирање резултата, додајемо функцију СУМПРОДУЦТ око формуле
| 1 | = СУМПРОДУЦТ (СУМИФС ('Корак 3'! Д3: Д7, 'Корак 3'! Ц3: Ц7, Х3)) |
Коришћење функције СУМИФС на једном листу даје једну вредност. На више листова функција СУМИФС приказује низ вредности (по једну за сваки радни лист). Користимо функцију СУМПРОДУЦТ да збројимо вредности у овом низу.
Корак 4: Замените референцу листа листом имена листова
Желимо да заменимо Назив листа део формуле са листом података која садржи вредности: Јан, Феб, Мар, и Апр. Ова листа је смештена у ћелије Ф3: Ф6.
Функција ИНДИРЕЦТ то осигурава да се листа текста приказује Имена листова се третира као део важеће референце ћелије у функцији СУМИФС.
| 1 | = СУМПРОДУЦТ (СУМИФС (ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Д3: Д7"), ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Ц3: Ц7"), Х3)) |
У овој формули, претходно написана референца опсега:
| 1 | 'Корак 3'! Д3: Д7 |
Замењује се са:
| 1 | ИНДИРЕКТНО ("'" & Ф3: Ф6 & "'!" & "Д3: Д7") |
Наводници отежавају читање формуле, па се овде приказује са додатним размацима:
| 1 | ИНДИРЕКТНО ("'" & Ф3: Ф6 & "'!" & "Д3: Д7") |
Коришћење овог начина упућивања на листу ћелија такође нам омогућава да сумирамо податке са више листова који не следе нумерички стил листе. Стандардна 3Д референца би захтевала да називи листова буду у стилу: Инпут1, Инпут2, Инпут3 итд., Али горњи пример вам омогућава да користите листу било којих Имена листова и да их наведете у засебној ћелији.
Закључавање ћелијских референци
Да бисмо олакшали читање наших формула, приказали смо формуле без закључаних референци ћелија:
| 1 | = СУМПРОДУЦТ (СУМИФС (ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Д3: Д7"), ИНДИРЕЦТ ("'" & Ф3: Ф6 & "'!" & "Ц3: Ц7"), Х3)) |
Али ове формуле неће радити исправно када их копирате и залепите на друго место у датотеци. Уместо тога, требало би да користите закључане ћелијске референце попут ове:
| 1 | = СУМПРОДУЦТ (СУМИФС (ИНДИРЕЦТ ("'" & $ Ф $ 3: $ Ф $ 6 & "'!" & "Д3: Д7"), ИНДИРЕЦТ ("'" & $ Ф $ 3: $ Ф $ 6 & "'!" & "Ц3: Ц7"), Х3)) |
Прочитајте наш чланак о закључавању ћелијских референци да бисте сазнали више.
Збир ако на више табела у Гоогле табелама
Коришћење функције ИНДИРЕЦТ за позивање на листу листова у функцији СУМПРОДУЦТ и СУМИФС тренутно није могуће у Гоогле табелама.
Уместо тога, могу се направити засебни СУМИФС прорачуни за сваки улазни лист и резултати се сабирају:
| 1234 | = СУМИФС (Јан! Д3: Д7, Јан! Ц3: Ц7, Х3)+СУМИФС (фебруар! Д3: Д7, фебруар! Ц3: Ц7, Х3)+СУМИФС (Мар! Д3: Д7, Мар! Ц3: Ц7, Х3)+СУМИФС (апр! Д3: Д7, апр! Ц3: Ц7, Х3) |
