Как просуммировать данные с нескольких листов, в том числе по условию
В данной статье я хочу рассказать, как можно просуммировать данные на одном листе из других листов. К примеру: на листах Январь, Февраль и Март расположены данные по продажам, а под ними итог. Допустим, это будет ячейка D7. Если структура всех таблиц одинакова(одинаковое кол-во строк, товара, различается) и Итог расположен во всех таблицах в одной ячейке, то можно воспользоваться простой формулой:
=СУММ(Январь:Март!D7)
Подобная ссылка на диапазоны называется трехмерной ссылкой. Минус в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне.
Но, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товар расположен в хаотичном порядке, разном для каждого листа и количество строк различается, то здесь такая формула не подойдет. Возможно, есть и иные способы, но я смог найти только один — написать свою пользовательскую функцию:
Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional bAllSh As Boolean = True) Dim wsSh As Worksheet, sRange As String, sSumRange As String sRange = Right(rRange.Address, Len(rRange.Address) - InStr(rRange.Address, "!")) sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) - InStr(rSumRange.Address, "!")) For Each wsSh In Sheets If bAllSh Then If wsSh.Name <> Application.Caller.Parent.Name Then All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Else If wsSh.Index < Application.Caller.Parent.Index Then All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If End If Next wsSh End Function
Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional bAllSh As Boolean = True)
Dim wsSh As Worksheet, sRange As String, sSumRange As String
sRange = Right(rRange.Address, Len(rRange.Address) - InStr(rRange.Address, "!"))
sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) - InStr(rSumRange.Address, "!"))
For Each wsSh In Sheets
If bAllSh Then
If wsSh.Name <> Application.Caller.Parent.Name Then
All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
End If
Else
If wsSh.Index < Application.Caller.Parent.Index Then
All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
End If
End If
Next wsSh
End FunctionЧтобы правильно воспользоваться кодом советую прочитать статью: Что такое функция пользователя(UDF)?
Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный.
rRange - Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий.
rCriteria - Ссылка на одну ячейку. Указывается ячейка, в которой содержится значение, данные по которому надо просуммировать.
rSumRange - Ссылка на диапазон ячеек. Указывается диапазон сумм или чисел, которые необходимо просуммировать на основании критерия.
bAllSh - Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ, то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.
Применение обех функций вы найдете в примере к статье.
Tips_All_SumIf_Few_Sheets.xls (54,0 KiB, 12 скачиваний)
Единственное, что могу добавить: пользовательский тип SaveRange может быть дополнен еще какими-либо переменными, помимо vFormula, sAddr и lColor. Например цвет границ ячейки, цвет шрифта и т.д. Все зависит от того, какие изменения Вы будете делать кодом и что захотите затем вернуть.
