Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

Достаточно распространенная ситуация: в книге ведутся данные по продажам, разбивая каждый месяц на отдельный лист с именем месяца. Т.е. в листах Январь, Февраль и Март и т.д. расположены данные по продажам, а в ячейке 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, 13 931 скачиваний)


Возможно, есть и иные способы суммирования формулой. Может даже есть способ суммировать, не указывая имена листов, но у меня не получилось так сделать без использования VBA. Поэтому я написал свою пользовательскую функцию:

'---------------------------------------------------------------------------------------
' 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, 13 931 скачиваний)


Дополнил статью функцией, которая суммирует данные только с указанных листов, либо со всех, кроме листа с функцией:

'---------------------------------------------------------------------------------------
' 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, 13 931 скачиваний)


Очередное дополнение статьи - функция, в которой помимо перечисления листов можно указать книгу, в которой эти листы просматривать:

'---------------------------------------------------------------------------------------
' 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, 13 931 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 34 комментария
  1. Вячеслав:

    Дмитрий, спасибо!

  2. Шахислам:

    большое спасибо всем и Дмитрий(Админ)

  3. Олег:

    Частично проблема решается присвоением имени диапазонам. Тогда формула понятна и проста: =СУММПРОИЗВ((дата1>=$M$2)*(дата1=$M$2)*(дата<=$M$3)*(отдел=L5);знач2), как то так...

  4. Виталий:

    Дмитрий, день добрый!
    Спасибо за Ваши решения, помогают понять премудрости vba. Вопрос по пользовательской функции: почему при компиляции кода отдельного модуля макрос "перескакивает" на выполнение пользовательской функции, хотя в коде она не используется?

    • Виталий, скорее всего не при компиляции, а при выполнении. И код Ваш вносит изменения на листы(изменяет данные ячеек), что вызывает пересчет формул и функций. В том числе и UDF. Чтобы избежать пересчета(это к тому же может значительно ускорить работу кода) надо перед выполнением кода отключить автоматический пересчет формул:

      application.calculation = xlmanual

      а по окончании кода вернуть автопересчет:

      application.calculation = xlautomatic

      Конечно, если код планируется выполнять разными людьми, то правильнее сначала запомнить состояние данного свойства, затем установить в ручной пересчет и по окончании вернуть как было.

  5. Королюк Руслан:

    Дмитрий, здравствуйте! Подскажите, пожалуйста, как сделать автоматический пересчет функции SumIf, при вводе новых данных соответствующих критерию. Заранее блегодарен. Руслан

  6. Ирина:

    Дмитрий! Надеюсь, статьей не ошиблась, подскажите, пожалуйста, как можно прописать проще суммирование по формуле с разным количеством строк, кол-во строк зависит от внешнего документа. Например, в столбце 3 в ячейке С1 надо суммировать ячейки С2:С5, далее в ячейке С6 суммировать диапозон С7:С9, в ячейке С10 другой диапозон. И кол-во строк каждый раз зависит от критерия в колонке А. В данном случае заполнены Ячейки А1,А6,А10, соответственно с них начинаются диапазоны суммирования. и каждый раз кол-во строк для суммирования разное. Буду очень признательна! Смотрела темы и по суммированию по цвету, но тоже не подходит: все время разное кол-во строк, необходимое для суммирования. При этом все в одном столбике, как бы друг под другом. Спасибо!

  7. Ирина:

    Сорри за опечатки :)

    • Ирина, я не просто так посоветовал обратиться в форум - там можно файлы выкладывать. Из описания плохо ясна проблема, и тем более невозможно дать Вам пример формулы только по описанию.
      Создайте тему на форуме, приложите файл с примером данных. Уверен, ответ получите быстрее.

  8. Манзар:

    ОГРОМНОЕ СПАСИБО!!!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти