Как просуммировать данные с нескольких листов, в том числе по условию
Достаточно распространенная ситуация: в книге ведутся данные по продажам, разбивая каждый месяц на отдельный лист с именем месяца. Т.е. в листах
- вбиваем в итоговую ячейку функцию
СУММ (SUM) - для указания аргументов переходим на первый лист, данные которого необходимо суммировать
- далее зажимаем клавишу
Shift и выделяем последний лист(если необходимо выделить несмежные листы – зажимаем клавишуCtrl ) - указываем адрес ячейки для суммирования и жмем
Enter
Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом
После того, как введем такую функцию в ячейку, Excel сам подставит в правильном виде имена всех листов для суммирования, даже если они идут не по порядку. Т.е. лист суммирования не обязательно должен быть в самом начале книги или в конце - он может быть и в середине - функция его автоматически уберет из суммирования в момент ввода функции.
Звездочка в данном случае является подстановочным символом, который заменяет собой любое количество любых символов. Т.е. в данном случае, как бы говорящим Excel: имя листа может быть любым. Поэтому, если надо суммировать только конкретные ячейки, но не со всех листов, а только с листов, начинающихся со слова "Отчет" - можно функцию записать так:
Зная этот прием, можно заранее подготовить книгу, чтобы имена листов имели такие названия, по которым можно будет суммировать данные только с нужных в зависимости от ситуации(например, для каждой категории листов свое обозначение: "-год-","-месяц-","-квартал-" и т.п.).
Минус такой функции в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне(т.е. со всех листов будет взята сумма всех ячеек конкретного диапазона - в примере будут просуммированы данные из одной ячейки
А значит, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товары расположены в каждом листе в хаотичном порядке, разном для каждого листа и количество строк различается - такая формула не подойдет. Но можно воспользоваться чуть более сложной формулой:
"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь" - имена листов, с которых происходит суммирование. Важно помнить, что имена листов должны быть точь-в-точь как в книге(со всеми пробелами, точками и запятыми), иначе будет ошибка. Не буду останавливаться подробно на всех вложенных функциях. ПроСУММЕСЛИ можно прочитать в этой статье.(SUMIF) ДВССЫЛ используется для создания ссылки на диапазон, состоящей из имени листа и адреса ячеек:(INDIRECT) Январь!B3:B100, Февраль!B3:B100, Март!B3:B100, Апрель!B3:B100, Май!B3:B100, Июнь!B3:B100 . Т.е. мы в формуле перебираем все указанные листы, создаем диапазоны в каждом из них и суммируем на основании критерия при помощи СУММЕСЛИ. А при помощиСУММПРОИЗВ суммируем полученные от СУММЕСЛИ результаты в единое число.(SUMPRODUCT)
Важно: если в имени листа встречается пробел, либо иной знак препинания, то имя листа необходимо заключать в апострофы:" ' Январь 2014' ":"' Февраль 2014' ":"Март":"Апрель":"Май":"Июнь"
либо ставить апострофы заранее для всех листов:
ДВССЫЛ(" ' "&{"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь"}&"' !C3:C100")B3:B100 - диапазон в каждом листе, в котором искать критерий (при необходимости указать больше строк)B3 - ссылка на ячейку в итоговом листе, содержащую критерийC3:C100 - диапазон в каждом листе, ячейки из которого надо суммировать (при необходимости указать больше строк)
В чем главное неудобство - необходимо так или иначе указывать листы, с которых суммировать. Если листов много, то перечислить их все в формуле будет не так-то просто. В таком случае можно записать имена листов в отдельный диапазон и ссылаться на него в формуле(формула в данном случае получается более универсальной и короткой):
Но и она имеет свои недостатки:
- нельзя указать диапазон имен листов "с запасом". Т.е. если у нас имена листов записаны в диапазоне
, то нельзя в формуле указатьH3:H8 H3:H10 - в диапазоне с перечислением листов не должно быть пустых ячеек
- в диапазоне с перечислением листов не должно быть имен листов, которые заведомо отсутствуют в книге. Например, если имя листа записано с опечаткой
Если хоть одно из этих условий не выполняется - формула вернет ошибку
Плюс иногда именно перечисление листов напрямую в формуле удобнее. Например, если нет возможности выделить для этого отдельный диапазон.
Поэтому во вложении ниже эта формула представлена в двух вариантах: со статичным перечислением имен листов и с использованием дополнительного столбца для перечисления листов. А какую именно использовать - придется решать по ситуации.
Суммировать со всех листов.xls (134,0 KiB, 13 960 скачиваний)
Возможно, есть и иные способы суммирования формулой. Может даже есть способ суммировать, не указывая имена листов, но у меня не получилось так сделать без использования 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(
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(
Применение всех функций вы найдете в примере к статье:
Суммировать со всех листов.xls (134,0 KiB, 13 960 скачиваний)
Дополнил статью функцией, которая суммирует данные только с указанных листов, либо со всех, кроме листа с функцией:
'--------------------------------------------------------------------------------------- ' 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 |
Скачать пример с кодом функции:
Суммировать со всех листов.xls (134,0 KiB, 13 960 скачиваний)
Очередное дополнение статьи - функция, в которой помимо перечисления листов можно указать книгу, в которой эти листы просматривать:
'--------------------------------------------------------------------------------------- ' 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 |
Аргументы и их использование полностью совпадают с описанными выше для функции
Скачать пример с кодом функций:
Суммировать со всех листов.xls (134,0 KiB, 13 960 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Возможно ли на основе данного примера просуммировать данные на одном листе из однотипных листов других рабочих книг?
Структура всех рабочих листов всех рабочих книг одинакова но трехмерных функций для рабочих книг вроде таких же для листов
=СУММ(Январь:Март!D7)
еще не изобрели :(
Поможет ли в этом случае написание пользовательской функции или лучше воспользоваться средствами VBA по алгоритму: подсчет файлов *.xls в папке, открытие каждого в цикле, считывание нужного диапазона ячеек в массив, вставка данных из массива в аналогичный диапазон нового файла?
функция может помочь тольк в том случае, если все книги будут открыты. В ином случае только написание отдельного кода для суммирования(что предпочтительнее, на мой взгляд).
Как можно записать в аргументе sSheets диапазон листов другой книги? Например, из другой книги "Брак" суммировать данные с листов диапазона имен с "2" по "5" лист.
Максим - никак. Функция не предусмотрена для суммирования с других книг.
Дмитрий, добрый день. а возможно ли Вам как-нибудь скинуть файл, чтоб Вы глянули. что то я совсем запуталась в формулах. да и вообще не понимаю как проше сделать итоговые подсчеты.
Светлана, скидывайте, но с подробным описанием того, что хочется получить от функции и не получается.
ап
как мне скинуть файл?
Дмитрий, добрый день,
не могли бы пояснить, почему функция All_SumIf выдает в ячейках B5,B6 на листе "ИТОГИ" #ЗНАЧ,
но после того как встать курсором на любую из этих ячеек и нажать клавишу ENTER в ячейке выдается результат суммирования.
Дмитрий, уточняю предыдцщий вопрос:
:не могли бы пояснить, почему функция All_SumIf выдает в ячейках B5,B6 на листе «ИТОГИ» #ЗНАЧ, даже после нажатия клавиши F9,
но после того как встать курсором на любую из этих ячеек и нажать клавишу F2 а затем ENTER в ячейке выдается результат суммирования.
Какие опции следует установить, чтобы функция выдавала сразу результат вычислений?
Буду благодарен за разяснение.