Изтеглете примерната работна книга
Този урок ще покаже как да използвате функциите SUMPRODUCT и SUMIFS за сумиране на данни, които отговарят на определени критерии в няколко листа в Excel и Google Sheets.
Редовна сума в няколко листа
Понякога вашите данни могат да обхващат няколко работни листа във файл на Excel. Това е обичайно за данни, които се събират периодично. Всеки лист в работна книга може да съдържа данни за зададен период от време. Искаме формула, която сумира данни, съдържащи се в два или повече листа.
Функцията SUM ви позволява лесно да сумирате данни в няколко листа, като използвате a 3D справка:
| 1 | = SUM (лист1: лист2! A1) |
Това обаче не е възможно с функцията SUMIFS. Вместо това трябва да използваме по -сложна формула.
Сума, ако в няколко листа
Този пример ще обобщи Брой планирани доставки за всеки Клиент над множество работни листове, всеки от които съхранява данни, свързани с различен месец, като използва функциите SUMIFS, SUMPRODUCT и INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Нека преминем през тази формула.
Стъпка 1: Създайте формула SUMIFS само за 1 входен лист:
Използваме функцията SUMIFS, за да сумираме Брой планирани доставки от Клиент за един лист с входни данни:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Стъпка 2: Добавете препратка към лист към формулата
Запазваме резултата от формулата същият, но уточняваме, че входните данни са в извикания лист 'Стъпка 2'
| 1 | = SUMIFS („Стъпка 2“! D3: D7, „Стъпка 2“! C3: C7, H3) |
Стъпка 3: Вложете вътре функция SUMPRODUCT
За да подготвим формулата за извършване на изчисления на SUMIFS върху множество листове и след това да сумираме резултатите заедно, добавяме функция SUMPRODUCT около формулата
| 1 | = SUMPRODUCT (SUMIFS ('Стъпка 3'! D3: D7, 'Стъпка 3'! C3: C7, H3)) |
Използването на функцията SUMIFS на един лист дава единична стойност. На няколко листа функцията SUMIFS извежда масив от стойности (по една за всеки работен лист). Използваме функцията SUMPRODUCT, за да обобщим стойностите в този масив.
Стъпка 4: Заменете препратката към лист със списък с имена на листове
Искаме да заменим Име на листа част от формулата със списък с данни, съдържащ стойностите: Ян, Февр, Март, и Април. Този списък се съхранява в клетките F3: F6.
Функцията INDIRECT to гарантира, че текстовият списък се показва Имена на листове се третира като част от валидна препратка към клетка във функцията SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
В тази формула по -рано написаната справка за диапазон:
| 1 | „Стъпка 3“! D3: D7 |
Заменя се с:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Кавичките правят формулата трудна за четене, така че тук тя се показва с добавени интервали:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Използването на този начин за препращане към списък с клетки също ни позволява да обобщим данни от множество листове, които не следват стила на числовия списък. Стандартна 3D препратка би изисквала имената на листа да са в стил: Input1, Input2, Input3 и т.н., но горният пример ви позволява да използвате списък с всякакви Имена на листове и да ги посочите в отделна клетка.
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Сума, ако в няколко листа в Google Таблици
Използването на функцията INDIRECT за справка към списък с листове във функция SUMPRODUCT и SUMIFS понастоящем не е възможно в Google Sheets.
Вместо това могат да се направят отделни изчисления на SUMIFS за всеки входен лист и резултатите да се добавят заедно:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (февруари! D3: D7, февруари! C3: C7, H3)+SUMIFS (март! D3: D7, март! C3: C7, H3)+SUMIFS (април! D3: D7, април! C3: C7, H3) |
