Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

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

Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом Январь и Март добавить еще какой-нибудь лист - то данные с него будут также автоматически просуммированы. Поэтому необходимо следить, чтобы указывались только нужные листы. Минус в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне.

Но, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товар расположен в хаотичном порядке, разном для каждого листа и количество строк различается, то здесь такая формула не подойдет. Можно воспользоваться формулой массива, которая несколько неудобна именно в таком виде:
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ({"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь"}&"!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, 3 292 скачиваний)

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

Чтобы правильно воспользоваться кодом советую прочитать статью: Что такое функция пользователя(UDF)?

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

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

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

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

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

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

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

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

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


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

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

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

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

  2. Шахислам:

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

  3. Олег:

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

  4. Виталий:

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

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

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

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

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

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

  6. Ирина:

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

  7. Ирина:

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

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

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти