Как просуммировать ячейки на разных листах excel
Продолжаем описание инструментов по оптимизации работы с листами книги EXCEL.
Пример – отчётная таблица по продажам товаров за каждый месяц в разрезе покупателей, создаваемая вручную.
Если у вас уже есть ранее созданные таблицы на каждый месяц, расположенные на листах одной книги (файла), и вам необходимо просуммировать показатели за закрытые периоды на сегодняшний день, вы можете воспользоваться так называемыми «трехмерными формулами». Эти формулы охватывают диапазоны, в которые входят сразу несколько листов. Для этого:
- Создаем дополнительный лист, в котором будем производить расчеты (Сводный) и копируем пустую таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Как просуммировать данные с нескольких листов, в том числе по условию
В данной статье я хочу рассказать, как можно просуммировать данные на одном листе из других листов. К примеру: на листах Январь, Февраль и Март расположены данные по продажам, а под ними итог. Допустим, это будет ячейка 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 — диапазон суммирования(при необходимости указать больше строк).
В чем главное неудобство — необходимо так или иначе указывать листы, с которых суммировать. Это очень неудобно, если листов много. Во вложении ниже эта формула представлена в двух вариантах: как выше и с использованием дополнительного столбца для перечисления листов (формула в данном случае получается более универсальной и короткой).
Скачать пример суммирования с листов формулой
Но в приложенном примере тоже стоит учитывать один момент: при ссылке на диапазон с именами листов, в этом диапазоне не должно быть:
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 774 скачиваний)
Дополнил статью функцией, которая суммирует данные только с указанных листов, либо со всех, кроме листа с функцией:
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 522 скачиваний)
Очередное дополнение статьи — функция, в которой помимо перечисления листов можно указать книгу, в которой эти листы просматривать:
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 не указан — листы просматриваются в книге, с листа которой вызвана функция. Если какого-либо из указанных листов не будет в указанной книге — функция вернет ошибку.
Сумма одной и той же ячейки на нескольких листах
Задача: у вас есть 12 листов с одинаковой структурой данных, по одному на каждый месяц. Вы бы хотели просуммировать одну и ту же ячейку на каждом листе. Есть ли способ лучше, чем использование =Янв!B4+Фев!B4+Мар!B4+…+Дек!B4?
Решение: вы можете использовать «3D-формулу», например, =СУММ(Янв:Дек!B4), как показано на рис. 1.
Рис. 1. 3D-формулу для суммирования одной и той же ячейки на нескольких листах
Скачать заметку в формате Word или pdf, примеры в формате Excel
Если в имени первого и/или последнего листа содержится пробел, используйте апострофы вокруг пары имен листов: =СУММ( ‘ Янв 2009:Дек ‘ !B5), как показано на рис. 2. Любопытно, что, если пробел есть в имени любого промежуточного листа, апострофы не требуются. Более того, если вы введете апострофы, когда они не требуются, Excel автоматически их уберет.
Рис. 2. Используйте апострофы, если в имени листа есть пробел
Вы можете легко скопировать эту формулу в другие ячейки на листе Итоги.
Подводный камень: не размещайте сводный лист между листами Янв и Дек. Это приведет к циклической ссылке (подробнее о борьбе с последней см. Как найти циклическую ссылку).
Дополнительные сведения: можно создать именованный диапазон, который ссылается на 3D-область. Для этого перейдите к ячейке B4 на лист Янв. Пройдите по меню ФОРМУЛА –> Присвоить имя. В открывшемся окне Создание имени (рис. 3) перейдите в поле Диапазон путем многократного нажатия клавиши Табуляция. При этом всё содержимое поле Диапазон становится выделенным. Удерживая нажатой клавишу Shift кликните на лист Дек. Обратите внимание, что на рис. 3 все листы от Янв по Дек выделены. Нажмите Ok.
Рис. 3. Присвоение имени 3D-диапазону
После этого формулы приобретают «человеческий» вид. Например, =СУММ(Объем_продаж).
Сумма с разных листов в Excel
Часто возникают ситуации, когда нужно просуммировать одни и те же ячейки на разных листах одного документа.
Рассмотрим ситуацию из жизненной практики.
Например, существует документ (файл excel), состоящий из нескольких вкладок.
Вкладками документа являются сформированные в виде таблиц Excel счета на оплату коммунальных услуг за несколько месяцев с апреля по июнь включительно, то есть за один квартал.
Необходимо посчитать сумму выставленных счетов за указанный период времени — второй квартал года.
Для этого создаем дополнительный лист Excel, на котором составляем формулу суммирования.
Вписываем в ячейку:
— знак равно «=»;
— далее функцию «СУММ()»;
— в скобках указываем диапазон суммирования (Лист_Апрель:Лист_июнь!H26) — где Лист_апрель:Лист_Июнь — диапазон листов из которых берутся данные, H26 — суммируемая ячейка;
Нажимаем клавишу «Enter» на клавиатуре.
Формула «=СУММ(Лист_Апрель:Лист_июнь!H26)» считает сумму одной и той же ячейки с разных листов.