Как просуммировать данные с нескольких листов, в том числе по условию

Достаточно распространенная ситуация: в книге ведутся данные по продажам, разбивая каждый месяц на отдельный лист с именем месяца. Т.е. в листах Январь, Февраль и Март и т.д. расположены данные по продажам, а в ячейке C33 подводится общий итог за месяц. И на отдельном листе необходимо просуммировать все продажи по всем листам. Если структура всех таблиц одинакова (одинаковое кол-во строк, товар может различаться) и Итог расположен во всех таблицах в одной ячейке, то можно воспользоваться простой формулой:
=СУММ(Январь:Март!C33)
=SUM(Январь:Март!C33)
Как это сделать без указания имен листов вручную:

• вбиваем в итоговую ячейку функцию СУММ(SUM)
• для указания аргументов переходим на первый лист, данные которого необходимо суммировать
• далее зажимаем клавишу Shift и выделяем последний лист(если необходимо выделить несмежные листы – зажимаем клавишу Ctrl)
• указываем адрес ячейки для суммирования и жмем Enter

Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом Январь и Март добавить еще какой-нибудь лист - то данные с него будут также автоматически включены в функцию суммирования, приведенную выше. Поэтому необходимо следить, чтобы указывались только нужные листы или диапазон листов ОТ и ДО. Если листов много и суммировать необходимо данные одной ячейки со всех листов, кроме того, в котором сама функция, то можно записать так:
=СУММ('*'!C33)
=SUM('*'!C33)
После того, как введем такую функцию в ячейку, Excel сам подставит в правильном виде имена всех листов для суммирования, даже если они идут не по порядку. Т.е. лист суммирования не обязательно должен быть в самом начале книги или в конце - он может быть и в середине - функция его автоматически уберет из суммирования в момент ввода функции.
Звездочка в данном случае является подстановочным символом, который заменяет собой любое количество любых символов. Т.е. в данном случае, как бы говорящим Excel: имя листа может быть любым. Поэтому, если надо суммировать только конкретные ячейки, но не со всех листов, а только с листов, начинающихся со слова "Отчет" - можно функцию записать так:
=СУММ('Отчет*'!C33)
=SUM('Отчет*'!C33)
Зная этот прием, можно заранее подготовить книгу, чтобы имена листов имели такие названия, по которым можно будет суммировать данные только с нужных в зависимости от ситуации(например, для каждой категории листов свое обозначение: "-год-","-месяц-","-квартал-" и т.п.).
К слову, подобное использование функции доступно и для родственных СУММ: СЧЁТ(COUNT), СРЗНАЧ(AVERAGE), МИН(MIN) и МАКС(MAX)

Минус такой функции в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне(т.е. со всех листов будет взята сумма всех ячеек конкретного диапазона - в примере будут просуммированы данные из одной ячейки C33 всех листов). А использование функции ДВССЫЛ(INDIRECT) для динамического указания имен первого и последнего листа при этом не допускается.

А значит, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товары расположены в каждом листе в хаотичном порядке, разном для каждого листа и количество строк различается - такая формула не подойдет. Но можно воспользоваться чуть более сложной формулой:
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ({"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь"}&"!B3:B100");B3;ДВССЫЛ({"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь"}&"!C3:C100")))
=SUMPRODUCT(SUMIF(INDIRECT({"Январь";"Февраль";"Март";"Апрель";"Май";"Июнь"}&"!B3:B100"),B3,INDIRECT({"Январь";"Февраль";"Март";"Апрель";"Май";"Июнь"}&"!C3:C100")))
В данном случае названия товаров записаны в столбце B, а суммы - в столбце С

• "Январь":"Февраль":"Март":"Апрель":"Май":"Июнь" - имена листов, с которых происходит суммирование. Важно помнить, что имена листов должны быть точь-в-точь как в книге(со всеми пробелами, точками и запятыми), иначе будет ошибка. Не буду останавливаться подробно на всех вложенных функциях. Про СУММЕСЛИ(SUMIF) можно прочитать в этой статье. ДВССЫЛ(INDIRECT) используется для создания ссылки на диапазон, состоящей из имени листа и адреса ячеек: Январь!B3:B100, Февраль!B3:B100, Март!B3:B100, Апрель!B3:B100, Май!B3:B100, Июнь!B3:B100. Т.е. мы в формуле перебираем все указанные листы, создаем диапазоны в каждом из них и суммируем на основании критерия при помощи СУММЕСЛИ. А при помощи СУММПРОИЗВ(SUMPRODUCT) суммируем полученные от СУММЕСЛИ результаты в единое число.
  

  Важно: если в имени листа встречается пробел, либо иной знак препинания, то имя листа необходимо заключать в апострофы: "'Январь 2014'":"'Февраль 2014'":"Март":"Апрель":"Май":"Июнь"
  либо ставить апострофы заранее для всех листов:
  ДВССЫЛ("'"&{"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь"}&"'!C3:C100")

• B3:B100- диапазон в каждом листе, в котором искать критерий (при необходимости указать больше строк)
• B3- ссылка на ячейку в итоговом листе, содержащую критерий
• C3:C100 - диапазон в каждом листе, ячейки из которого надо суммировать (при необходимости указать больше строк)

В чем главное неудобство - необходимо так или иначе указывать листы, с которых суммировать. Если листов много, то перечислить их все в формуле будет не так-то просто. В таком случае можно записать имена листов в отдельный диапазон и ссылаться на него в формуле(формула в данном случае получается более универсальной и короткой):
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&$H$3:$H$8&"'!B3:B100");B3;ДВССЫЛ("'"&$H$3:$H$8&"'!C3:C100")))
=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$3:$H$8&"'!B3:B100"),B3,INDIRECT("'"&$H$3:$H$8&"'!C3:C100")))
Но и она имеет свои недостатки:

• нельзя указать диапазон имен листов "с запасом". Т.е. если у нас имена листов записаны в диапазоне H3:H8, то нельзя в формуле указать H3:H10
• в диапазоне с перечислением листов не должно быть пустых ячеек
• в диапазоне с перечислением листов не должно быть имен листов, которые заведомо отсутствуют в книге. Например, если имя листа записано с опечаткой

Если хоть одно из этих условий не выполняется - формула вернет ошибку #ССЫЛКА!(#REF!)
Плюс иногда именно перечисление листов напрямую в формуле удобнее. Например, если нет возможности выделить для этого отдельный диапазон.
Поэтому во вложении ниже эта формула представлена в двух вариантах: со статичным перечислением имен листов и с использованием дополнительного столбца для перечисления листов. А какую именно использовать - придется решать по ситуации.
Так же в примере перечислены все варианты суммирования, включая разбираемые ниже с использованием Visual Basic for Applications
Скачать пример суммирования с листов формулой

  Суммировать со всех листов.xls (134,0 KiB, 14 078 скачиваний)

'---------------------------------------------------------------------------------------
' Function  : СУММЕСЛИ_ВСЕ_ЛИСТЫ
' Purpose   : Функция суммирует на основании критерия значения со всех листов, кроме листа с функцией
'             Диапазон              - ссылка на диапазон ячеек для поиска критерия
'             Критерий              - значение, на основании которого суммировать данные
'             ДиапазонСуммирования  - ссылка на диапазон ячеек, значения которых суммировать
'             ВсеЛисты              - 1(по умолчанию) - суммирование происходит со всех листов, кроме листа с функцией
'                                     0 - суммирование происходит только в листах, расположенных левее листа с функцией
'---------------------------------------------------------------------------------------
Function СУММЕСЛИ_ВСЕ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ВсеЛисты As Boolean = True)
    Dim wsSh As Worksheet
    Dim wbFunc As Workbook
    Dim sRange As String, sSumRange As String
    Dim dblSum As Double
    Dim rRange As Range, rCriteria As Range, rSumRange As Range, bAllSh As Boolean
 
    Set rRange = Диапазон
    If IsObject(Критерий) Then
        Set rCriteria = Критерий
    Else
        rCriteria = Критерий
    End If
    Set rSumRange = ДиапазонСуммирования
    bAllSh = ВсеЛисты
 
    'определяем книгу, в которой записана функция
    Set wbFunc = Application.Caller.Parent.Parent
    'определяем адреса поиска критерия и суммирования
    sRange = rRange.Address
    sSumRange = rSumRange.Address
    'цикл по всем листам книги с функцией
    For Each wsSh In wbFunc.Worksheets
        If bAllSh Then
            'если это не лист с функцией
            If wsSh.Name <> Application.Caller.Parent.Name Then
                dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
            End If
        Else
            'если лист ДО листа с функцией
            If wsSh.Index < Application.Caller.Parent.Index Then
                dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
            End If
        End If
    Next wsSh
    'выводим результат
    СУММЕСЛИ_ВСЕ_ЛИСТЫ = dblSum
End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).
Синтаксис функции:
суммируем со всех листов
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(B3:B100;B3;C3:C100)
суммируем со всех листов ДО листа с функцией
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(B3:B100;B3;C3:C100;0)
Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный. Принцип работы и все нюансы полностью совпадают с таковыми для функции СУММЕСЛИ(можно использовать подстановочные символы и операторы сравнения), т.к. внутри функции применяется непосредственно СУММЕСЛИ, применительно к каждому листу, а результаты затем суммируются в единое значение.
Диапазон - Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий(Критерий).
Критерий - Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования - Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия(Критерий).
ВсеЛисты - Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА(TRUE), то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ(FALSE), то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.

Применение всех функций вы найдете в примере к статье:
Скачать пример

  Суммировать со всех листов.xls (134,0 KiB, 14 078 скачиваний)

'---------------------------------------------------------------------------------------
' Function  : СУММЕСЛИ_ЛИСТЫ
' Purpose   : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией
'             Диапазон              - ссылка на диапазон ячеек для поиска критерия
'             Критерий              - значение, на основании которого суммировать данные
'             ДиапазонСуммирования  - ссылка на диапазон ячеек, значения которых суммировать
'             ИмяЛистов             - Имена листов, записанные через вопр.знак(?), с которых суммировать данные
'                                     Пример: "Январь?Февраль?Май"
'---------------------------------------------------------------------------------------
Function СУММЕСЛИ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = "")
    Dim asSheets, li As Long
    Dim wsSh As Worksheet
    Dim wbFunc As Workbook
    Dim sRange As String, sSumRange As String
    Dim dblSum As Double
    Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String
 
    Set rRange = Диапазон
    If IsObject(Критерий) Then
        Set rCriteria = Критерий
    Else
        rCriteria = Критерий
    End If
    Set rSumRange = ДиапазонСуммирования
    sSheets = ИмяЛистов
 
    'определяем книгу, в которой записана функция
    Set wbFunc = Application.Caller.Parent.Parent
    'определяем адреса поиска критерия и суммирования
    sRange = rRange.Address
    sSumRange = rSumRange.Address
 
    If sSheets = "" Then
        For Each wsSh In wbFunc.Worksheets
            If wsSh.Name <> Application.Caller.Parent.Name Then
                sSheets = sSheets & "?" & wsSh.Name
            End If
        Next wsSh
        sSheets = Mid$(sSheets, 2)
    End If
    asSheets = Split(sSheets, "?")
    'цикл по всем листам книги
    For li = LBound(asSheets) To UBound(asSheets)
        Set wsSh = wbFunc.Sheets(asSheets(li))
        If Not wsSh Is Nothing Then
            dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
        End If
    Next li
    'выводим результат
    СУММЕСЛИ_ЛИСТЫ = dblSum
End Function

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ($B$3:$B$100;$A3;$C$3:$C$100;"Январь?Март")
Диапазон - Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий(Критерий).
Критерий - Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования - Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия(Критерий).
ИмяЛистов - Необязательный аргумент. Указываются имена листов книги, с которых надо суммировать данные. Имена листов должны быть записаны через вопросительный знак: Январь?Март. Если аргумент не указан или указана пустая ячейка, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция.

Скачать пример с кодом функции:
Скачать пример

  Суммировать со всех листов.xls (134,0 KiB, 14 078 скачиваний)

'---------------------------------------------------------------------------------------
' Function  : СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ
' Purpose   : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией
'             Диапазон              - ссылка на диапазон ячеек для поиска критерия
'             Критерий              - значение, на основании которого суммировать данные
'             ДиапазонСуммирования  - ссылка на диапазон ячеек, значения которых суммировать
'             ИмяЛистов             - Имена листов, записанные через вопр.знак(?), с которых суммировать данные
'                                     Пример: "Январь?Февраль?Май"
'             ИмяКниги              - имя книги, данные с которой необходимо суммировать
'---------------------------------------------------------------------------------------
Function СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = "", Optional ИмяКниги As String = "")
    Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long
    Dim wbB As Workbook
    Dim dblSum As Double
    Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String, sWbName As String
 
    Set rRange = Диапазон
    If IsObject(Критерий) Then
        Set rCriteria = Критерий
    Else
        rCriteria = Критерий
    End If
    Set rSumRange = ДиапазонСуммирования
    sSheets = ИмяЛистов
    sWbName = ИмяКниги
 
    If sWbName = "" Then
        Set wbB = Application.Caller.Parent.Parent
    Else
        Set wbB = Application.Workbooks(sWbName)
    End If
 
    'определяем адреса поиска критерия и суммирования
    sRange = rRange.Address
    sSumRange = rSumRange.Address
    If sSheets = "" Then
        For Each wsSh In wbB.Worksheets
            If wsSh.Name <> Application.Caller.Parent.Name Then
                sSheets = sSheets & "?" & wsSh.Name
            End If
        Next wsSh
        sSheets = Mid$(sSheets, 2)
    End If
    asSheets = Split(sSheets, "?")
    'цикл по всем листам указанной книги
    On Error Resume Next
    For li = LBound(asSheets) To UBound(asSheets)
        Set wsSh = wbB.Sheets(asSheets(li))
        If Not wsSh Is Nothing Then
            dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
        End If
    Next li
    'выводим результат
    СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ = dblSum
End Function

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ($B$3:$B$100;$A3;$C$3:$C$100;"Январь?Март";"Книга1.xlsx")
Аргументы и их использование полностью совпадают с описанными выше для функции СУММЕСЛИ_ЛИСТЫ. Поэтому опишу только последний аргумент:
ИмяКниги - Необязательный аргумент. Указываются имя книги, в которой будут просматриваться листы, указанные параметром ИмяЛистов. Если аргумент ИмяКниги не указан - листы просматриваются в книге, в которой записана сама функция. Если какого-либо из указанных листов не будет в указанной книге - ошибка будет проигнорирована. Если среди открытых книг не будет книги с указанным именем - функция вернет ошибку #ЗНАЧ!(#VALUE!). Важно: имя книги необходимо указывать вместе с расширением: "Книга1.xlsx"

Скачать пример с кодом функций:
Скачать пример

  Суммировать со всех листов.xls (134,0 KiB, 14 078 скачиваний)

    Видеоуроки

Поиск по меткам