Excel суммеслимн с разных листов

Ссылки в Excel на несколько листов сразу.

​Смотрите также​​ для них надо​ копировать формулы в​ взаимосвязанны.​​ одном документе, то​ данные.​Имеем несколько однотипных таблиц​ и повторяются в​ диапазон ячеек, а​ и отдела продаж​ всех диапазонов, сделайте​ и диаграмм на​ для элементов, которые​Маринова​ с данными, в​районе (из столбца​ способами быстро скопировать​Есть простой способ​ выделить РАЗНЫЕ листы,​ любой другой фаил.»​
​:hands:​ создайте еще одну​Для того, чтобы выполнить​ на разных листах​ разных диапазонах, на​ затем укажите его​ без данных производственного​
​ следующее:​ панель быстрого доступа.​ вы хотите объединить.​Мясо​ котором находится​ A) (из столбца​ формулу, смотрите в​
​установить в Excel сылки​ на одном не​
​Извини Ворода, намучалься​
​Решенно, надо уроки​ страницу (внизу такие​ такую консолидацию:​
​ одной книги. Например,​ любом листе может​ имя.​ отдела, а в​Добавьте мастер сводных таблиц​ Для этого:​ В выбранные данные​6596​условие1​ C).​ статье «Копирование в​ на несколько листов​ получится​ ты со смной.​
​ по-екселлу брать.​ закладочки есть) и​Заранее откройте исходные файлы​ вот такие:​ быть несколько таких​Пример​
​ другом поле —​ и диаграмм на​Щелкните стрелку рядом с​ не следует включать​Формула​— “Южный”.​Вот формулу можно использовать​ Excel» тут.​ быстро​Genytech​Для формул используйте​mabika​ там уже ввести​
​Создайте новую пустую книгу​
​Необходимо объединить их все​ диапазонов (пример -​Если в разделе​ данные всех трех​ панель быстрого доступа.​ панелью инструментов и​
​ итоговые строки и​Описание​По мере того, как​ для acomplish это:​Получился такой результат.​. Такие формулы в​: Здравствуйте!​ теги — кнопка​
​: Boroda, огромное спасило​
​ формулу = (навести​ (Ctrl + N)​ в одну общую​ Лист2), ячейки в​Во-первых, укажите количество полей​ отделов. Чтобы создать​ Для этого:​
​ выберите​ итоговые столбцы. В​Результат​
​ вы вводите формулу,​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​Как выделить не​ Excel работают всегда.​Прошу прощение, если​ fx. Поправил Вам​ за решение проблемы.​ мышь на значение​Установите в нее активную​ таблицу, просуммировав совпадающие​ В и С​ страницы сводной таблицы​
​ консолидацию с использованием​
​Щелкните стрелку рядом с​Дополнительные команды​ приведенном ниже примере​
​’ = СУММЕСЛИМН (a2:​ в автозавершении формулы​Результатом является значение 14,719.​ все листы, а​ Обычно, когда мы​
​ эта тема похожа​mabika​gling​ на первой странице)​ ячейку и выберите​ значения по кварталам​ на листах 1:5​задано число​ нескольких полей страницы,​

excel-office.ru

Суммирование значений с учетом нескольких условий

​ панелью инструментов и​​.​ показано четыре диапазона​ a11; D2: D11​ появятся остальные аргументы​Рассмотрим изучить все части​ некоторые, выборочно, смотрите​ пишем формулу, в​ на другие, но​:​: Здравствуйте. Может так?​ + (навести мышь​ на вкладке (в​ и наименованиям.​ могут быть пустыми.​1​ сделайте следующее:​ выберите​В списке​ в формате перекрестной​«Южно» C2: C11,​ (здесь они не​

​ формулы.​ в статье «Как​ которой нужно сделать​ очень нужна Ваша​Огромнейшее спасибо.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(‘1′!D2:D29;»membership»;’1’!E2:E28)​ на значение на​ меню)​Самый простой способ решения​

​ На листе Результат​, выберите каждый из​Добавьте мастер сводных таблиц​Дополнительные команды​Выбрать команды из​​ таблицы.​​ «Мясо»)​​ показаны);​​= СУММЕСЛИМН является формулой​ заполнить таблицу в​ ссылку на другой​

​ помощь!​mabika​

​mabika​

​ второй странице) +​

​Данные — Консолидация​ задачи «в лоб»​

​ связки функций ИНДЕКС​ диапазонов, а затем​ и диаграмм на​.​выберите пункт​При консолидации данных можно​Суммирует продаж мяса в​

​диапазон_условия2​

​ арифметического. Он вычисляет​ Excel сразу на​ лист книги Excel,​Описание:​: Привет опять, :)​: Здравствуйте, спасибо, что​ и т. д.​​(Data — Consolidate)​​ — ввести в​ и ПОИСКПОЗ в​ введите уникальное имя​ панель быстрого доступа.​В списке​

​Все команды​ использовать поля страницы,​Столбец C в​— это диапазон​ числа, которые находятся​ нескольких листах».​ мы переходим на​Есть 3 листа​Извините за беспокойство,​ откликнулись.​ и потом нажать​. Откроется соответствующее окно:​

​ ячейку чистого листа​

​ столбце В проходят​ в поле​ Для этого:​

​Выбрать команды из​.​ содержащие элементы, которые​ южном​ C2:C11, представляющий собой​ в этом случае​У этой формулы есть​ этот лист или​ в книге, как​ но никак не​​Формула работает только​​ enter​Установите курсор в строку​ формулу вида​

​ по значениям столбцов​

​Первое поле​Щелкните стрелку рядом с​выберите пункт​Выберите в списке пункт​ представляют один или​область в столбце​​ столбец с данными,​​ в столбце D.​ преимущество перед формулой​ пишем формулу вручную​ прописать формулу, что​

​ могу подкоректировать формулу.​ для указанного диапазона.​Игорь максимов​Ссылка​=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3​ В листов 1:5,​. Если у вас​ панелью инструментов и​Все команды​Мастер сводных таблиц и​ несколько исходных диапазонов.​ A (результат —​ в котором находится​ Первым делом нужно​​ простого суммирования.​​ в каждой ячейке​

​ бы она подсчитывала​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((DAY(‘1’!B$2:B$999)=ROW(B2))*(‘1′!D$2:D$999=»Membership»)*’1’!E$2:E$999)​ Таблица отоброжает продажи​: СУММ=(…+++…)​(Reference)​которая просуммирует содержимое ячеек​​ в столбце С​​ четыре диапазона, каждый​ выберите​.​ диаграмм​​ Например, при консолидации​​ 14 719).​

​условие2​​ указать расположение номера:​​Если мы уберем​ таблицы. Если листов​

Попробуйте попрактиковаться

​ сумму, используя данные​ПОжалуйста, помогите разобраться,​ целого месяца и​Открываем страницу, выделяем​и, переключившись в​

​ B2 с каждого​ соответственно по С.​ из которых соответствует​Дополнительные команды​Выберите в списке пункт​и нажмите кнопку​ данных бюджета отдела​= SUMIFS(D2:D11,A2:A11,​— “Мясо”.​

​=СУММЕСЛИМН(D2:D11;​ из этого диапазона​ много, то это​ со 2-го и​ что деляю не​ каждый день есть​ ячекйу, открываем страницу…​ файл Иван.xlsx, выделите​

​ если они удовлетворяют​​ таблицей, то в​

• ​Мы помним, что​ и Сумма (которую​

• ​http://www.excelworld.ru/forum/​ каждой отдельной ячейке​ формулы закрываем скобку​Добавить​ вправо.​ на листах 1:5​

support.office.com

Консолидация нескольких листов в одной сводной таблице

​ введите имя «Кв2″​Все команды​, а затем — кнопку​В книге щелкните пустую​ с данными по​Дополнительные примеры можно найти​Если вы хотите поэкспериментировать​ условиям. Что диапазон​ итоговой таблице все​ ссылки на ячейки​ нужно подсчитать)​Evgesha807​ суммировала, например только​ и ставим галочку​(Add)​Если листов очень много,​ есть незаполненные ячейки​ и повторите процедуру​.​ОК​

Консолидация нескольких диапазонов

​ ячейку, которая не​ каждому из этих​ в статьеФункция СУММЕСЛИМН.​ с функцией СУММЕСЛИМН,​ ячеек — это​ равно всё посчитается,​ бывают разные. Подробнее​на втором листе​: Добрый день. Помогите​ «membership» каждого дня?​ по формуле (расчет​в окне консолидации,​ то проще будет​ в В или​ для диапазонов «Кв3″​Выберите в списке пункт​

​.​ является частью сводной​ отделов, а также​Если вы хотите подсчитать​​ то некоторые образцы​​ первый​ с учетом наших​ об этом читайте​ колонки: Наименование товара​ плиз. необходимо суммировать​Столбец А дата,​ завершен)​ чтобы добавить выделенный​ разложить их все​

Настройка исходных данных

​ С.​ и «Кв4».​Мастер сводных таблиц и​В книге щелкните пустую​ таблицы.​ элемент, содержащий сводные​ итоговое значение только​ данных и формулы,​аргумент​ изменений. Например, удалим​ в статье «Относительные​ и Код этого​ значения ячеек с​ Столбец В наименование​Abram pupkin​

Поля страницы при консолидации данных

​ диапазон в список​ подряд и использовать​Вот формулы листа​Если в разделе​ диаграмм​ ячейку, которая не​Щелкните значок мастера на​ данные. В примере​ для одного диапазона​ использующей функцию.​в этой формуле​ страницу «Магазин 2»​ и абсолютные ссылки​ товара (у одного​ разных листов по​ продукции. Столбец С​: ТРЕХМЕРНЫЕ ССЫЛКИ​ объединяемых диапазонов.​ немного другую формулу:​ Результат и требуют​Во-первых, укажите количество полей​

Использование именованных диапазонов

​и нажмите кнопку​ является частью сводной​ панели быстрого доступа.​ ниже показана сводная​ с учетом значения​Вы можете работать с​ — первая часть​ с таблицей.​ в Excel».​ товара могут быть​ значению Название области.​ сумма, Столбец Д​Именно в Вашем​Повторите эти же действия​=СУММ(‘2001 год:2003 год’!B3)​ оптимизации или полной​ страницы сводной таблицы​Добавить​ таблицы.​На странице​ таблица, в которой​ в другом диапазоне,​

Другие способы консолидации данных

​ демонстрационными данными непосредственно​ данных, функция требует​Получилось так.​Итак, у нас​ разные коды)​ Во вложении пример.​ = ячейке из​ случае будет такая​ для файлов Риты​Фактически — это суммирование​​ переработки. Здесь в​​задано число​​, а затем — кнопку​​На странице​​Шаг 1​​ выбраны одно поле​

Консолидация нескольких диапазонов

​ используйте функцию СУММЕСЛИМН.​ в этой книге​ в качестве входных​Формула все равно работает.​ есть три таблицы​на третьем листе​ Проблема еще в​ Столбца С если​ формула:​

Консолидация данных без использования полей страницы

​ и Федора. В​ всех ячеек B3​ примере они работают​2​ОК​

1. ​Шаг 1​мастера выберите параметр​ страницы и несколько​Консолидация данных представляет собой​

   1. ​ веб-приложения Excel Online.​ данных.​ При простом суммировании​​ на разных страницах,​​ колонки: Код товара​

   2. ​ том, что название​​ та же ячейка​​=СУММ (Лист1:Лист100!D9)​​ итоге в списке​​ на листах с​

   3. ​ с шестью диапазонами,​​, выполните аналогичные действия​.​​мастера выберите параметр​​в нескольких диапазонах консолидации​​ элементов.​​ удобный способ объединения​​ Изменяйте значения и​

2. ​После этого необходимо найти​ – покажет ошибку.​ отчет магазинов -​ и количество (из​

3. ​ области повторяется с​ в столбце В​

4. ​P.S.​​ должны оказаться все​​ 2001 по 2003,​​ а в реальной​​ в поле​В книге щелкните пустую​​в нескольких диапазонах консолидации​​, а затем нажмите​

5. ​Если велика вероятность того,​​ данных из нескольких​​ формулы или добавляйте​​ данные, которые соответствуют​​Сложить данные таблицы​ № 1, №​​ этого количества нужно​​ точкой, как мне​

6. ​ «membership».​​Обратите внимание :​​ три диапазона:​

   1. ​ т.е. количество листов,​ таблице диапазонов больше​Первое поле​ ячейку, которая не​, а затем нажмите​ кнопку​​ что в следующий​​ источников в одном​

      ​ свои собственные, чтобы​​ двух условий, поэтому​ можно другим способом.​ 2 и №​ подбить сумму для​ и его посчитать?​

   2. ​Спасибо огромное​​ Восклицат знак ставится​Обратите внимание, что в​​ по сути, может​​ 30. Конечно всё​​. Затем выберите два​ является частью сводной​​ кнопку​​Далее​

7. ​ раз при консолидации​​ отчете. Например, если​​ увидеть, как мгновенно​ введите свой первый​ Смотрите статью «Как​ 3.​ конкретного вида товара​ спасибо​​_Boroda_​​ только после последнего​

Консолидация данных с использованием одного поля страницы

​ данном случае Excel​ быть любым. Также​ оно работает и​ диапазона и введите​ таблицы.​Далее​.​

1. ​ данных исходный диапазон​ в каждом из​ изменятся результаты.​ условие, указав для​

   1. ​ объединить таблицы в​Нам нужно сложить данные​ на первом листе)​​Evgesha807​​: Так нужно?​

   2. ​ Листа​​ запоминает, фактически, положение​​ в будущем возможно​​ работает правильно, но​​ в поле​

   3. ​На странице​​.​На странице​​ данных изменится (например,​​ региональных филиалов вашей​​Скопируйте все ячейки из​​ функции расположение данных​​ Excel».​

2. ​ в этих отчетах.​Если не получится​: Там не только​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((ДЕНЬ(‘1’!B$2:B$999)=СТРОКА(A1))*(‘1′!D$2:D$999=»Membership»)*’1’!E$2:E$999)​

3. ​mabika​​ файла на диске,​​ поместить между стартовым​​ хочется сделать это​​Второе поле​Шаг 1​​На странице​​Шаг 2а​

4. ​ изменится число строк),​​ компании есть сводная​​ приведенной ниже таблицы​​ (a2: a11), а​​Любую формулу можно​Внимание!​​ это сделать формулой,​​ лишние точки, но​

5. ​mabika​​: Всем привет. Нужна​​ прописывая для каждого​

   1. ​ и финальным листами​ красивее. В идеале,​одинаковое имя, например​мастера выберите параметр​Шаг 2а​выберите параметр​​ рекомендуется задать имя​​ таблица расходов, с​

      ​ и вставьте их​​ также какие условием​ проверить, правильно ли​Этот способ удобен,​ подскажите, пожалуйста, какой​ и пробелы, и​

6. ​: Огромная благодарность за​​ помощь. Запуталась в​​ из них полный​

7. ​ дополнительные листы с​​ что бы на​​ «Пг1» и «Пг2».​в нескольких диапазонах консолидации​выберите параметр​Создать поля страницы​ для каждого из​ помощью консолидации данных​​ в ячейку A1​​ является — наилучшим​

Консолидация данных с использованием нескольких полей страницы

​ она считает (составлна),​ когда таблицы одинаковые​ написать макрос.​ написание: в одном​ помощь, за Ваше​ выборе правильной функций​ путь (диск-папка-файл-лист-адреса ячеек).​ данными, которые также​ листе Результат всё​ Выберите первый диапазон​, а затем нажмите​Создать одно поле страницы​, а затем нажмите​ исходных диапазонов на​ можно объединить такие​ нового листа Excel.​ образом «Южный». Обратите​ найти ошибку на​ по форме.​

1. ​Заранее благодарен!​ случае Респ, в​ время и ваше​ и формул для​

   1. ​ Чтобы суммирование происходило​ станут автоматически учитываться​ происходило в одном​​ и введите имя​​ кнопку​

   2. ​, а затем нажмите​​ кнопку​​ разных листах. Эти​​ значения в корпоративный​​ Вы можете отрегулировать​

   3. ​ внимание, запятые между​​ любом этап вычисления,​На новой странице​​Serge_007​​ другом респ; Адыгея​​ терпение.​​ обработки данных. Хотя​​ с учетом заголовков​

2. ​ при суммировании.​ столбце, а не​ «Пг1», выберите второй​Далее​

3. ​ кнопку​​Далее​​ имена можно использовать​​ отчет о расходах.​​ ширину столбцов, чтобы​ отдельные аргументы:​​ найти циклическую ссылку.​​ будем устанавливать формулу​

4. ​: См. вложение​​ и Адыгая. Потом​​Извините за беспокойство.​​ бы подсказка, что​​ столбцов и строк​Если исходные таблицы не​​ в трёх. Сейчас​​ диапазон и введите​

5. ​.​​Далее​​.​

   1. ​ при консолидации диапазонов​ Такой отчет может​ формулы лучше отображались.​= SUMIFS(D2:D11,A2:A11,»South»,​ Смотрите в статье​ сложения данных из​​Genytech​​ уже можно применять​

      ​ Отдельно задавала вопросы​​ использовать было бы​ необходимо включить оба​ абсолютно идентичны, т.е.​ поделено на три​ имя «Пг1», выберите​

   2. ​На странице​​.​На странице​​ на главном листе.​ содержать итоговые и​Регион​

   3. ​Кавычки вокруг слова «Южный»​​ «Как проверить формулы​ ячеек первых трех​: Serge_007,​ что-либо.​ надеясь в остольном​ великолено, не говоря​ флажка​​ имеют разное количество​ только из-за громоздкости​ третий диапазон и​Шаг 2а​На странице​

      ​Шаг 2б​

      • ​ В случае расширения​​ средние данные по​Продавец​​ укажите, что это​​ в Excel».​​ таблиц. Пишем формулу​Спасибо, это работает.​Evgesha807​ самой разобраться. К​​ уже об готовой​​Использовать в качестве имен​ строк, столбцов или​ всей конструкции и​ введите имя «Пг2»,​выберите параметр​Шаг 2б​сделайте следующее:​ исходного диапазона перед​ продажам, текущим уровням​Что следует ввести​ текстовые данные.​

      • ​Примечание:​​ сложения данных из​ А можно как-то​​: Это я Знаю.​​ сожалению знания не​​ фопмуле.​(Use labels)​​ повторяющиеся данные или​​ ограничения на количество​ выберите четвертый диапазон​Создать поля страницы​​сделайте следующее:​​Перейдите в книгу и​ обновлением сводной таблицы​ запасов и наиболее​Продажи​Наконец, введите аргументы для​Мы стараемся как​ ячеек В3 всех​ реализовать все в​ как поменять и​ хватает.​Если не лень​. Флаг​

   4. ​ находятся в разных​​ аргументов или чего-то​​ и введите имя​

6. ​, а затем нажмите​​Перейдите в книгу и​​ выделите диапазон ячеек,​ можно обновить диапазон​ популярным продуктам в​Южный​ второго условия –​ можно оперативнее обеспечивать​​ таблиц. Делаем активной​​ одной формуле на​

​ выровнять текст в​

support.office.com

Суммирование данных с нескольких листов по условию (Формулы/Formulas)

​Помогите если можете​​ просмотрите прикреплённый файл.​
​Создавать связи с исходными​ файлах, то суммирование​ там ещё… Суммирование​ «Пг2».​
​ кнопку​ выделите диапазон ячеек,​ а затем вернитесь​ на отдельном листе​ рамках всей организации.​Орехов​ диапазон ячеек (C2:​ вас актуальными справочными​ первую верхнюю ячейку​ первом листе? Просто​ больших объемах я​ разобраться, что я​_Boroda_​ данными​ при помощи обычных​ двух диапазонов В​Нажмите кнопку​Далее​ а затем вернитесь​ в мастер сводных​ для указанного имени​Для подведения итогов и​Напитки​ C11), содержащий слово​ материалами на вашем​ (В3) таблицы сводной,​ такие требования поставлены​ примерно знаю.Это не​ делаю не правильно.​: Так нужно? Для​(Create links to source​ формул придется делать​ и С должно​Далее​.​ в мастер сводных​ таблиц и диаграмм​ таким образом, чтобы​ обобщения результатов по​3571​
​ «Мясо», плюс само​ языке. Эта страница​ но, вместо значка​_Boroda_​ проблема. У меня​_Boroda_​ любого месяца​ data)​ для каждой ячейки​ происходить на листе​.​На странице​ таблиц и диаграмм​ и нажмите кнопку​ включить в него​ данным в отдельных​Западный​ слово (заключено в​ переведена автоматически, поэтому​ «Равно», нажимаем кнопку​:​ проблема как просуммировать?​: Ну если у​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(‘1’!B$2:B$999=ЛЕВСИМВ(‘1′!B$2;8)&ТЕКСТ(СТРОКА(A1);»00″));’1’!E$2:E$999)-A3;)​позволит в будущем​ персонально, что ужасно​ Результат.​На странице​
​Шаг 2б​ и нажмите кнопку​Добавить​ новые данные.​ диапазонах можно консолидировать​Егоров​ кавычки) таким образом,​

​ ее текст может​​ «Автосумма» (на закладке​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИ(ТРАНСП(ЕСЛИ(A2=Лист2!A$2:A$8;Лист2!B$2:B$8))=Лист3!A$2:A$8;Лист3!B$2:B$8))​Evgesha807​

​ Вас там теперь​​Добавлено​​ (при изменении данных​​ трудоемко. Лучше воспользоваться​Работает вся эта​Шаг 3​сделайте следующее:​Добавить​.​В Excel также доступны​ их в сводной​Молочные продукты​ Excel может соответствовать​

​ содержать неточности и​​ «Главная»).​формула массива. Вводить​

​: консолидация не подходит?​​ даты есть, то​И переписал Вам​
​ в исходных файлах)​

excelworld.ru

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

​ принципиально другим инструментом.​ красота под Excel​укажите, следует ли​Перейдите в книгу и​

​.​Совет:​ другие способы консолидации​ таблице на главном​3338​

​ его. Завершить формулу​ грамматические ошибки. Для​Затем переходим на​ одновременным нажатием Контрл​Суммесли()​

​ зачем огород городить?​

​ еще формулу для​ производить пересчет консолидированного​Рассмотрим следующий пример. Имеем​ 2007. При решении​ добавлять сводную таблицу​ выделите диапазон ячеек,​Совет:​

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

​ нас важно, чтобы​

​ страницу первой таблицы​ Шифт Ентер​Я почитала как​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((‘1’!B$2:B$999=A3)*(‘1′!C$2:C$999=»Total:»)*’1’!E$2:E$999)​ листа 1. Теперь​ отчета автоматически.​ три разных файла​ хочется обойтись без​ на новый или​ а затем вернитесь​ Если диапазон ячеек находится​ в другой книге,​ работать с данными​ находиться в той​

Способ 2. Если таблицы неодинаковые или в разных файлах

​Песоцкий​)​ эта статья была​ (Магазин 1) и​Да, и обратите​ делать консолидацию в​Даже так можно​ она одинаковая для​После нажатия на​ (​ макросов, а вот​ же на существующий​ в мастер сводных​

​ в другой книге,​ сначала откройте ее,​ в разных форматах​​ же книге, что​​Напитки​​и нажмите клавишу​​ вам полезна. Просим​​ нажимаем на первую​​ внимание на то,​​ excel, но, к​

​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(‘1′!B$2:B$999;A3;’1’!E$2:E$999)/2​ любого Тотала, не​ОК​Иван.xlsx​ против массивов ничего​ лист, и нажмите​ таблиц и диаграмм​ сначала откройте ее,​ чтобы упростить выбор​ и макетах. Например,​ и главный лист,​5122​ ВВОД. Результат, снова​ вас уделить пару​ верхнюю ячейку этой​ как у Вас​ сожалению, ничего не​А для TOTAL​ нужно менять диапазоны​видим результат нашей​

​,​ не имею.​

1. ​ кнопку​
2. ​ и нажмите кнопку​ чтобы упростить выбор​
3. ​ данных.​ вы можете создавать​ или в других​Северный​​ нажмите — 14,719.​ ​ секунд и сообщить,​​ таблицы (В3).​
   
4. ​ в файле написано​​ поняла.Я сделала все​ ​ Bar что Вам​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ($B$1:$B28;$B29;E$1:E28)Файл перевложил​ работы:​Рита.xlsx​sboy​Готово​​Добавить​ ​ данных.​​В разделе​ формулы с объемными​ книгах. Консолидированные данные​Песоцкий​
5. ​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​ помогла ли она​Теперь нажимаем кнопку​ слово «количество».​ по рекомендации, но​ нужно — я​
   

​Еще добавлено​Наши файлы просуммировались по​и​: Исправьте название темы,​.​.​Нажмите кнопку​Во-первых, укажите количество полей​ ссылками или использовать​ легче обновлять и​Молочные продукты​По мере ввода функции​​ вам, с помощью​​ на клавиатуре «Shift»​​PowerBoy​​ итог у меня​ не понял.​ ​Там все проще​ совпадениям названий из​​Федор​ оно должно отражать​К началу страницы​Совет:​Далее​

​ страницы сводной таблицы​​ команду​​ обобщать на регулярной​6239​

​ СУММЕСЛИМН в Microsoft​ кнопок внизу страницы.​ и, удерживая её​: Попробовал сделать в​ не получилсяПриложите итоговый​И эту фразу​ -​ крайнего левого столбца​.xlsx​ суть вопроса​klgfinn​ Если диапазон ячеек находится​.​введите​Консолидация​

planetaexcel.ru

Как с разных страниц эксель подсчитать сумму ?

​ основе или по​​Южный​ Excel, если вы​ Для удобства также​ нажатой, нажимаем на​
​ надстройке «Активные таблицы»​

​ файл, который надо​​ тоже не понял​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(ДЕНЬ(‘1′!B$2:B$999)=СТРОКА(A1));’1’!E$2:E$999)-A3;)Файл перевложил​ и верхней строки​) с тремя таблицами:​klgfinn​: Всем доброго дня.​ в другой книге,​На странице​0​(доступную на вкладке​ мере необходимости.​Маринова​ не помните аргументы​ приводим ссылку на​ ярлык последней таблицы,​

​Вот такой SQL​​ посчитать.Я вложила файл.​
​mabika​Или (при отсутствии​ выделенных областей в​Хорошо заметно, что таблицы​:​Помогите, пожалуйста, оптимизировать​ сначала откройте ее,​Шаг 3​, а затем нажмите​

​Данные​​Итоговый консолидированный отчет сводной​
​Сельхозпродукты​ справки готов под​ оригинал (на английском​
​ данные из которой​
​ запрос:​
​ Вы его видели.​: Спасибо :)​ такого дня даст​ каждом файле. Причем,​

Суммирование данных с нескольких ячеек и страниц (Формулы/Formulas)

​ не одинаковы -​​sboy​ формулы, если это​ чтобы упростить выбор​укажите, следует ли​ кнопку​в группе​ таблицы может содержать​8677​ рукой. После ввода​ языке) .​
​ складываем (Магазин 3).​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​

​ Итоговый вид должен​​В прикрепленном файле​ минус)​
​ если развернуть группы​
​ у них различные​
​, да я с​ возможно, или совсем​ данных.​ добавлять сводную таблицу​Далее​Работа с данными​
​ следующие поля в​
​Южный​
​= СУММЕСЛИМН (​Допустим, вам нужно суммировать​
​ Эта страница (Магазин​
​SELECT​ быть в вкладке​ формулы работают замечательно.​
​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((ДЕНЬ(‘1′!B$2:B$999)=СТРОКА(A1))*’1’!E$2:E$999)/2-A3​

​ (значками плюс слева​​ размеры и смысловая​ удовольствием, но на​​ переписать их.​В разделе​​ на новый или​

​.​​).​ области​Егоров​, автозавершение формул появится​ значения с более​ 3) не откроется​[Лист2$].Наименование AS Наименование,​ «нужно получить». Грубо​ Одна проблема, когда​
​китин​
​ от таблицы), то​ начинка. Тем не​

​ какое? Не срабатывает​​По задумке на​Во-первых, укажите количество полей​

​ же на существующий​​На странице​​Для консолидации нескольких диапазонов​

​Список полей сводной таблицы​​Мясо​ под формулы, в​
​ чем одного условия,​ и не нужно.​SUM([Лист3$].[Колличество товара]) AS​ говоря надо сложить​ я их копирую​: и вам не​ можно увидеть из​ менее их можно​ у меня соображалка,​ нескольких листах есть​ страницы сводной таблицы​
​ лист, и нажмите​Шаг 3​ вы можете воспользоваться​, добавляемой в сводную​450​ списке аргументов в​ например сумму продаж​ Нажимаем кнопку «Enter».​ [Колличество товара]​
​ лист ПН1 и​

​ в другой файл,​​ хворать!​
​ какого именно файла​

​ собрать в единый​​ как обозвать тему​ диапазоны наименований, взятые​щелкните число полей,​ кнопку​
​укажите, следует ли​ мастером сводных таблиц​ таблицу: «Строка», «Столбец»​Южный​ их правильном порядке.​ товара в определенной​
​ У нас получилась​FROM​ лист ПН2 получить​

​ тогда она выглядит​​так надо? формула​ какие данные попали​ отчет меньше, чем​ с таким вопросом​
​ с листа Результат​
​ которые вы хотите​
​Готово​
​ добавлять сводную таблицу​ и диаграмм. В​ и «Значение». Кроме​Егоров​
​Если посмотреть на изображение​ области. Это хороший​

​ такая формула.​​[Лист3$] AS [Лист3$]​
​ результат по шт​ так:​ массива (вводится Ctrl+Shift+Enter)​ в отчет и​ за минуту. Единственным​ и уместить всё​ (Лист1:5 столбец А),​
​ использовать.​​.​
​ на новый или​ нем можно указать,​ того, в отчет​Мясо​ использования функции автозавершения​ обращения для использования​=СУММ(‘Магазин 1:Магазин 3′!B3)​LEFT JOIN [Лист2$]​ и по дс​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((‘[1556855.xlsx]1’!B$2:B$999=A3)*(‘[1556855.xlsx]1′!C$2:C$999=»Total:»)*'[1556855.xlsx]1’!E$2:E$999)​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(‘1’!$E$2:$E$61;НАИМЕНЬШИЙ(ЕСЛИ(‘1’!$C$2:$C$61=»Total:»;СТРОКА(‘1’!$E$2:$E$61)-1);СТРОКА(A1)))-A3;»»)​ ссылки на исходные​
​ условием успешного объединения​ это в отведённое​ и к этим​

​В группе​​Вы можете создать несколько​
​ же на существующий​

​ сколько полей страницы​​ можно включить до​
​7673​ формул и списка​ функции СУММЕСЛИМН в​
​В этой формуле​
​ AS [Лист2$]​ , аналогично с​-​
​mabika​
​ файлы:​​ (консолидации) таблиц в​

Суммирование ячеек из разных листов

​ количество знаков в​​ наименования есть две​Затем выберите диапазон в​ полей страницы и​ лист, и нажмите​ будет использоваться: ни​ четырех полей фильтра,​Восточный​ аргументов, то в​ формуле.​ указан диапазон всех​ON [Лист3$].Код=[Лист2$].Код​ листами ПР1 и​

​»[1556855.xlsx]» — это​​: Огромное спасибо за​Imobilazer​ подобном случае является​ названии темы?​ группы числовых значений​ списке и укажите​ назначить имена элементов​ кнопку​ одного, одно или​

​ которые называются «Страница1»,​​Песоцкий​ нашем примере диапазон_суммирования​Просмотрите в этом примере,​ листов (таблиц) -​GROUP BY​ ПР2. спасибоНадо обязательно​ имело в виду,​

​ помощь. Для меня​​: Писать в формуле​
​ совпадение заголовков столбцов​

​sboy​ (Лист1:5 столбец В​ метку элемента в​ каждому из исходных​Готово​ несколько.​ «Страница2», «Страница3» и​Сельхозпродукты​ — это диапазон​ в котором у​ Магазин1:Магазин3 , из​[Лист2$].Наименование​ привести названия к​ говоря об «Мне​ они почти, что​ название листа и​ и строк. Именно​: Подсказка: что формула​ и С). Наименования​ каждом из доступных​ диапазонов. Это позволяет​.​Чтобы объединить данные всех​ «Страница4».​664​ D2:D11, представляющий собой​ нас есть двумя​ которых значение ячеек​ORDER BY​ единому виду, а​ бы формулу без​ универсальны, смогу их​

CyberForum.ru

Подсчитать сумму используя данные с нескольких листов (Формулы)

​ ячейки через восклицательный​​ по первой строке​
​ должна сделать?​ в столбце А​ окон полей. Повторите​ выполнять частичную или​Чтобы включить одно поле​
​ диапазонов и создать​
​Каждый из диапазонов данных​Северный​ столбец с числами,​ условиями: необходимо узнать​ В3 суммируются.​[Лист2$].Наименование​ затем применить консолидацию.​
​ указания маршутов (как​ применить к моим​ знак, например:​ и левому столбцу​
​sboy​ на листах 1:5​ операцию для каждого​ полную консолидацию. Например,​ страницы, содержащее элемент​ консолидированный диапазон без​
​ следует преобразовать в​Егоров​ которые вы хотите​ сумму продаж​Теперь эту формулу​Genytech​ Но так как​
​ в этой ячейкп​ 12 листовым огромным​=Лист1!А1+Лист2!А2​ каждой таблицы Excel​
​: Вот такой лобовой​

​ находятся в произвольном​​ диапазона​

​ в одном поле​​ для каждого исходного​
​ полей страницы, сделайте​ формат перекрестной таблицы​Сельхозпродукты​ просуммировать; диапазон_условия1 —​мяса​ копируем по всей​

​: Всем большое спасибо​​ ПН и ПР​​ ссылка на фаил),​
​ файлам, где все​Ирина логинова​ будет искать совпадения​
​ вариант​ порядке, не повторяются​для каждого из​ страницы могут консолидироваться​ диапазона, а также​

​ следующее:​​ с совпадающими именами​1500​
​ это диапазон A2.A11,​в​
​ таблице (в строну,​
​ за помощь!​
​ должны суммироваться отдельно,​
​ так чтобы смогла​ данные переплетенны и​
​: если страницы в​
​ и суммировать наши​
​=СУММЕСЛИ(Лист1!$A:$A;$A2;Лист1!B:B)+СУММЕСЛИ(Лист2!$A:$A;$A2;Лист2!B:B)+СУММЕСЛИ(Лист3!$A:$A;$A2;Лист3!B:B)+СУММЕСЛИ(Лист4!$A:$A;$A2;Лист4!B:B)+СУММЕСЛИ(Лист5!$A:$A;$A2;Лист5!B:B)​ в одном диапазоне​
​ полей страницы выберите​
​ данных отдела маркетинга​
​ элемент для консолидации​
​Добавьте мастер сводных таблиц​
​ строк и столбцов​


​Южный​​ представляющий собой столбец​южном​

excelworld.ru

​ затем, вниз). Какими​

Хитрости »

14 Май 2012              167877 просмотров

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

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

• вбиваем в итоговую ячейку функцию СУММ(SUM)
• для указания аргументов переходим на первый лист, данные которого необходимо суммировать
• далее зажимаем клавишу Shift и выделяем последний лист(если необходимо выделить несмежные листы – зажимаем клавишу Ctrl)
• указываем адрес ячейки для суммирования и жмем Enter

Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом Январь и Март добавить еще какой-нибудь лист — то данные с него будут также автоматически включены в функцию суммирования, приведенную выше. Поэтому необходимо следить, чтобы указывались только нужные листы или диапазон листов ОТ и ДО. Если листов много и суммировать необходимо данные одной ячейки со всех листов, кроме того, в котором сама функция, то можно записать так:
=СУММ(‘*’!C33)
=SUM(‘*’!C33)
После того, как введет такую функцию в ячейку, Excel сам подставит в правильном виде имена всех листов для суммирования, даже если они идут не по порядку. Т.е. лист суммирования не обязательно должен быть в самом начале книги или в конце — он может быть и в середине — функция его автоматически уберет из суммирования в момент ввода функции.
Звездочка в данном случае является подстановочным символом, который заменяет собой любое количество любых символов. Т.е. в данном случае, как бы говорящим Excel: имя листа может быть любым. Поэтому, если надо суммировать только конкретные ячейки, но не со всех листов, а только с листов, начинающихся со слова «Отчет» — можно функцию записать так:
=СУММ(‘Отчет*’!C33)
=SUM(‘Отчет*’!C33)
Зная этот прием, можно заранее подготовить книгу, чтобы имена листов имели такие названия, по которым можно будет суммировать данные только с нужных в зависимости от ситуации(например, для каждой категории листов свое обозначение: «-год-«,»-месяц-«,»-квартал-» и т.п.).
К слову, подобное использование функции доступно и для родственных СУММ: СЧЁТ(COUNT), СРЗНАЧ(AVERAGE), МИН(MIN) и МАКС(MAX)

Минус такой функции в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне(т.е. со всех листов будет взята сумма всех ячеек конкретного диапазона — в примере будут просуммированы данные из одной ячейки C33 всех листов). А использование функции ДВССЫЛ(INDIRECT) для динамического указания имен первого и последнего листа при этом не допускается.

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

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

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

• B3:B100- диапазон в каждом листе, в котором искать критерий (при необходимости указать больше строк)
• B3- ссылка на ячейку в итоговом листе, содержащую критерий
• C3:C100 — диапазон в каждом листе, ячейки из которого надо суммировать (при необходимости указать больше строк)

В чем главное неудобство — необходимо так или иначе указывать листы, с которых суммировать. Если листов много, то перечислить их все в формуле будет не так-то просто. В таком случае можно записать имена листов в отдельный диапазон и ссылаться на него в формуле(формула в данном случае получается более универсальной и короткой):
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(«‘»&$H$3:$H$8&»‘!B3:B100»);B3;ДВССЫЛ(«‘»&$H$3:$H$8&»‘!C3:C100»)))
=SUMPRODUCT(SUMIF(INDIRECT(«‘»&$H$3:$H$8&»‘!B3:B100»),B3,INDIRECT(«‘»&$H$3:$H$8&»‘!C3:C100»)))
Но и она имеет свои недостатки:

• нельзя указать диапазон имен листов «с запасом». Т.е. если у нас имена листов записаны в диапазоне H3:H8, то нельзя в формуле указать H3:H10
• в диапазоне с перечислением листов не должно быть пустых ячеек
• в диапазоне с перечислением листов не должно быть имен листов, которые заведомо отсутствуют в книге. Например, если имя листа записано с опечаткой

Если хоть одно из этих условий не выполняется — формула вернет ошибку #ССЫЛКА!(#REF!)
Плюс иногда именно перечисление листов напрямую в формуле удобнее. Например, если нет возможности выделить для этого отдельный диапазон.
Поэтому во вложении ниже эта формула представлена в двух вариантах: со статичным перечислением имен листов и с использованием дополнительного столбца для перечисления листов. А какую именно использовать — придется решать по ситуации.
Так же в примере перечислены все варианты суммирования, включая разбираемые ниже с использованием Visual Basic for Applications
Скачать пример суммирования с листов формулой

  Суммировать со всех листов.xls (134,0 KiB, 12 701 скачиваний)

Возможно, есть и иные способы суммирования формулой. Может даже есть способ суммировать, не указывая имена листов, но у меня не получилось так сделать без использования 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(Alt+F11) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).
Синтаксис функции:
суммируем со всех листов
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(B3:B100;B3;C3:C100)
суммируем со всех листов ДО листа с функцией
=СУММЕСЛИ_ВСЕ_ЛИСТЫ(B3:B100;B3;C3:C100;0)
Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный. Принцип работы и все нюансы полностью совпадают с таковыми для функции СУММЕСЛИ(можно использовать подстановочные символы и операторы сравнения), т.к. внутри функции применяется непосредственно СУММЕСЛИ, применительно к каждому листу, а результаты затем суммируются в единое значение.
Диапазон — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий(Критерий).
Критерий — Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования — Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия(Критерий).
ВсеЛисты — Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА(TRUE), то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ(FALSE), то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.

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

  Суммировать со всех листов.xls (134,0 KiB, 12 701 скачиваний)

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

'---------------------------------------------------------------------------------------
' 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

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ($B$3:$B$100;$A3;$C$3:$C$100;»Январь?Март»)
Диапазон — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий(Критерий).
Критерий — Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования — Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия(Критерий).
ИмяЛистов — Необязательный аргумент. Указываются имена листов книги, с которых надо суммировать данные. Имена листов должны быть записаны через вопросительный знак: Январь?Март. Если аргумент не указан или указана пустая ячейка, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция.

Скачать пример с кодом функции:
Скачать пример

  Суммировать со всех листов.xls (134,0 KiB, 12 701 скачиваний)

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

'---------------------------------------------------------------------------------------
' 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

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ($B$3:$B$100;$A3;$C$3:$C$100;»Январь?Март»;»Книга1.xlsx»)
Аргументы и их использование полностью совпадают с описанными выше для функции СУММЕСЛИ_ЛИСТЫ. Поэтому опишу только последний аргумент:
ИмяКниги — Необязательный аргумент. Указываются имя книги, в которой будут просматриваться листы, указанные параметром ИмяЛистов. Если аргумент ИмяКниги не указан — листы просматриваются в книге, в которой записана сама функция. Если какого-либо из указанных листов не будет в указанной книге — ошибка будет проигнорирована. Если среди открытых книг не будет книги с указанным именем — функция вернет ошибку #ЗНАЧ!(#VALUE!). Важно: имя книги необходимо указывать вместе с расширением: «Книга1.xlsx»

Скачать пример с кодом функций:
Скачать пример

  Суммировать со всех листов.xls (134,0 KiB, 12 701 скачиваний)

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

    Видеоуроки

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



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

Примеры использования функции СУММЕСЛИМН в Excel

В отличие от СУММЕСЛИ, в рассматриваемой функции предусмотрена возможность внесения до 127 критериев отбора данных для суммирования. СУММЕСЛИМН удобна для работы с объемными таблицами, содержащими большое число полей данных. Например, если требуется рассчитать общую сумму средств, полученных в результате поставки определенного типа товара указанной компанией в определенную страну, удобно использовать функцию СУММЕСЛИМН.

Динамический диапазон суммирования по условию в Excel

Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Вид таблицы данных:

Для расчета суммарного балла согласно установленным критериям используем следующую формулу:

Описание аргументов:

• C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
• C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
• «>5» – первое условие отбора значений из указанного выше диапазона;
• B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
• «А*» – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).

Результат расчетов:

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



Выборочное суммирование по условию в Excel

Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.

Вид таблицы данных:

Для расчета итоговой суммы используем формулу:

Результат расчетов:

Примечание: формула выглядит громоздкой несмотря на то, что для столбца «Месяц» было использовано всего 2 критерия отбора. Формулы с несколькими критериями для одного диапазона данных можно существенно сократить способом, который будет показан в следующем примере.

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).

Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):

Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.

Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.

Полученное значение:

Особенности использования функции СУММЕСЛИМН в Excel

Функция имеет следующую синтаксическую запись:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

Описание аргументов:

• диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
• диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
• условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.

Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.

Примечания:

1. Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
2. В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
3. Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
4. Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
5. Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
6. Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
7. Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)