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 302 скачиваний)


Но в приложенном примере тоже стоит учитывать один момент: при ссылке на диапазон с именами листов, в этом диапазоне не должно быть:
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 803 скачиваний)

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

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 558 скачиваний)

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

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. Дмитрий:

    Возможно ли на основе данного примера просуммировать данные на одном листе из однотипных листов других рабочих книг?
    Структура всех рабочих листов всех рабочих книг одинакова но трехмерных функций для рабочих книг вроде таких же для листов
    =СУММ(Январь:Март!D7)
    еще не изобрели :(
    Поможет ли в этом случае написание пользовательской функции или лучше воспользоваться средствами VBA по алгоритму: подсчет файлов *.xls в папке, открытие каждого в цикле, считывание нужного диапазона ячеек в массив, вставка данных из массива в аналогичный диапазон нового файла?

  2. функция может помочь тольк в том случае, если все книги будут открыты. В ином случае только написание отдельного кода для суммирования(что предпочтительнее, на мой взгляд).

  3. Максим:

    Как можно записать в аргументе sSheets диапазон листов другой книги? Например, из другой книги "Брак" суммировать данные с листов диапазона имен с "2" по "5" лист.

  4. Максим - никак. Функция не предусмотрена для суммирования с других книг.

  5. Светлана:

    Дмитрий, добрый день. а возможно ли Вам как-нибудь скинуть файл, чтоб Вы глянули. что то я совсем запуталась в формулах. да и вообще не понимаю как проше сделать итоговые подсчеты.

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

  7. Светлана:

    ап

  8. Светлана:

    как мне скинуть файл?

  9. Виктор:

    Дмитрий, добрый день,
    не могли бы пояснить, почему функция All_SumIf выдает в ячейках B5,B6 на листе "ИТОГИ" #ЗНАЧ,
    но после того как встать курсором на любую из этих ячеек и нажать клавишу ENTER в ячейке выдается результат суммирования.

  10. Виктор:

    Дмитрий, уточняю предыдцщий вопрос:

    :не могли бы пояснить, почему функция All_SumIf выдает в ячейках B5,B6 на листе «ИТОГИ» #ЗНАЧ, даже после нажатия клавиши F9,
    но после того как встать курсором на любую из этих ячеек и нажать клавишу F2 а затем ENTER в ячейке выдается результат суммирования.

    Какие опции следует установить, чтобы функция выдавала сразу результат вычислений?

    Буду благодарен за разяснение.

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

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


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