Lost your password?


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

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

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

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

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

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

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

B3:B100- диапазон с критериями(при необходимости указать больше строк).
C3:C100 - диапазон суммирования(при необходимости указать больше строк).

В чем главное неудобство - необходимо так или иначе указывать листы, с которых суммировать. Это очень неудобно, если листов много. Во вложении ниже эта формула представлена в двух вариантах: как выше и с использованием дополнительного столбца для перечисления листов (формула в данном случае получается более универсальной и короткой).
Скачать пример суммирования с листов формулой

  Tips_All_SumIf_AllSheets_Formula.xls (67,5 KiB, 12 308 скачиваний)


Но в приложенном примере тоже стоит учитывать один момент: при ссылке на диапазон с именами листов, в этом диапазоне не должно быть:
1. Пустых ячеек
2. Имен листов, которые заведомо отсутствуют в книге
При несоблюдении этих правил формула вернет ошибку #ССЫЛКА(#REF!)


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

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)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).

Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный.

rRange - Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий.
rCriteria - Ссылка на одну ячейку. Указывается ячейка, в которой содержится значение, данные по которому надо просуммировать.
rSumRange - Ссылка на диапазон ячеек. Указывается диапазон сумм или чисел, которые необходимо просуммировать на основании критерия.
bAllSh - Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ, то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.

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

  Tips_All_SumIf_Few_Sheets.xls (57,5 KiB, 4 804 скачиваний)

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

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional sSheets = "")
    Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long
    sRange = Right(rRange.Address, Len(rRange.Address) - InStr(rRange.Address, "!"))
    sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) - InStr(rSumRange.Address, "!"))
    If sSheets = "" Then
        For Each wsSh In Worksheets
            If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & "?" & wsSh.Name
        Next wsSh
        sSheets = Mid$(sSheets, 2)
    End If
    asSheets = Split(sSheets, "?")
    For li = LBound(asSheets) To UBound(asSheets)
        Set wsSh = Sheets(asSheets(li))
        If Not wsSh Is Nothing Then
            All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
        End If
    Next li
End Function

rRange - Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий.
rCriteria - Ссылка на одну ячейку. Указывается ячейка, в которой содержится значение, данные по которому надо просуммировать.
rSumRange - Ссылка на диапазон ячеек. Указывается диапазон сумм или чисел, которые необходимо просуммировать на основании критерия.
sSheets - Необязательный аргумент. Указываются имена листов книги, с которых надо суммировать данные. Имена листов должны быть записаны через вопросительный знак: Февраль?Март. Если аргумент не указан или равен пустой ячейке, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция.
Скачать пример

  Tips_All_SumIf_Show_Sheets.xls (59,5 KiB, 3 562 скачиваний)

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

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional sSheets = "", Optional wsAnotherWB As String = "")
    Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long
    Dim wbB As Workbook
    If wsAnotherWB = "" Then
        Set wbB = Application.Caller.Parent.Parent
    Else
        Set wbB = Workbooks(wsAnotherWB)
    End If
 
    sRange = Right(rRange.Address, Len(rRange.Address) - InStr(rRange.Address, "!"))
    sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) - InStr(rSumRange.Address, "!"))
    If sSheets = "" Then
        For Each wsSh In wbB.Worksheets
            If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & "?" & wsSh.Name
        Next wsSh
        sSheets = Mid$(sSheets, 2)
    End If
    asSheets = Split(sSheets, "?")
    For li = LBound(asSheets) To UBound(asSheets)
        Set wsSh = wbB.Sheets(asSheets(li))
        If Not wsSh Is Nothing Then
            All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
        End If
    Next li
End Function

Аргументы и их использование полностью совпадают с описанием выше. Опишу только последний аргумент:
wsAnotherWB - Необязательный аргумент. Указываются имя книги, в которой будут просматриваться листы, указанные параметром sSheets. Если аргумент wsAnotherWB не указан - листы просматриваются в книге, с листа которой вызвана функция. Если какого-либо из указанных листов не будет в указанной книге - функция вернет ошибку.


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

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

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

    Виктор, такое бывает в случае с пользовательскими фукнциями.

  2. Светлана:

    Я прошу прощение за "ап", это произошло случайно и не имело никакого отношения к переписке с Вами. И я попросила помочь по человечески, т.к. хочу разобраться в программе, что мне поможет и в работе тоже.

  3. Виктор - попробуйте самой первой стокой в функции записать: application.Volatile = True

  4. Светлана :

    Я прошу прощение за «ап», это произошло случайно и не имело никакого отношения к переписке с Вами. И я попросила помочь по человечески, т.к. хочу разобраться в программе, что мне поможет и в работе тоже.

    Светлана - я по-человечески отозвался и написал, где взять мои контакты. Все, что далее - относится именно к Вашему "ап".

  5. Виктор:

    @Дмитрий(Админ)

    Дмитрий, добрый день,
    благлдарю за пояснение - ваш совет помог!
    Application.Volatile True

  6. Руслан:

    Здравствуйте, никак не получается задать параметр sSheets. У Вас в примере этот параметр вообще указан как С5, С6? А если поставить - 1,0. - то начитнается #ЗНАЧ!

  7. Руслан, а Вы повнимательнее прочитайте пояснения к аргументам - я там очень четко написал как должен записываться данный аргумент. Дословно себя цитирую:
    "Имена листов должны быть записаны через вопросительный знак: Февраль?Март"

    Из этого следует, что "1.0" вполне логично ошибку вызовет.

  8. Григорий:

    Здравствуйте! Как будет выглядеть код с двумя условиями?

  9. Анжелика:

    Привет. Как осуществлять пересчет этой формулы при добавлении новых листов и ввода значений?

  10. Вячеслав:

    Добрый день Дмитрий,
    Спасибо за идею, сочетания суммприоз и двссыл - супер
    Но для меня было важным отсутствие необходимости в формуле прописывать имена листов,
    Получилось так =+СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ($N$5:$N$35&"!m1:m200");$M31;ДВССЫЛ($N$5:$N$35&"!e1:e200")))
    при этом в диапазоне $N$5:$N$35&" находятся названия листов в формате Текст(01.01.1900;"дд.ММ.гг"), самое интерестное нет необходимости использовать ' для листов с разделительными знаками.

    • Вячеслав, в примере к статье есть подобная формула(в статье так же об этом написано) - там как раз используются ячейки для записи в них листов. А апострофы все же лучше использовать, т.к. в Вашем конкретном случае они может и не нужны, а если в имени какого либо листа будет служебный или недопустимый символ(например пробел) - то формула ДВССЫЛ вернет ошибку(#ССЫЛКА!).

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2022 Excel для всех   Войти