Загрузите образец книги
В этом руководстве будет показано, как использовать функции СУММПРОИЗВ и СУММЕСЛИМН для суммирования данных, соответствующих определенным критериям, на нескольких листах в Excel и Google Таблицах..
Обычная сумма на нескольких листах
Иногда ваши данные могут занимать несколько листов в файле Excel. Это обычное дело для данных, которые собираются периодически. Каждый лист в книге может содержать данные за установленный период времени. Нам нужна формула, которая суммирует данные, содержащиеся на двух или более листах.
Функция СУММ позволяет легко суммировать данные на нескольких листах, используя 3D ссылка:
| 1 | = СУММ (Лист1: Лист2! A1) |
Однако это невозможно с функцией СУММЕСЛИМН. Вместо этого мы должны использовать более сложную формулу.
Сумма на нескольких листах
Этот пример суммирует Количество запланированных поставок для каждого Покупатель на нескольких листах, каждый из которых содержит данные, относящиеся к разному месяцу, с помощью функций СУММЕСЛИМН, СУММПРОИЗВ и КОСВЕННО:
| 1 | = СУММПРОИЗВ (СУММЕСЛИМН (КОСВЕННО ("'" & F3: F6 & "'!" & "D3: D7"), КОСВЕННОЕ ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Давайте пройдемся по этой формуле.
Шаг 1. Создайте формулу СУММЕСЛИМН только для 1 входного листа:
Мы используем функцию СУММЕСЛИМН, чтобы суммировать Количество запланированных поставок к Покупатель для одного листа входных данных:
| 1 | = СУММЕСЛИМН (D3: D7; C3: C7; H3) |
Шаг 2. Добавьте ссылку на лист в формулу
Мы сохраняем результат формулы таким же, но указываем, что входные данные находятся на листе с именем 'Шаг 2'
| 1 | = СУММЕСЛИМН ('Шаг 2'! D3: D7; 'Шаг 2'! C3: C7, H3) |
Шаг 3. Вложение в функцию СУММПРОИЗВ
Чтобы подготовить формулу для выполнения вычислений СУММЕСЛИМН на нескольких листах, а затем для суммирования результатов, мы добавляем функцию СУММПРОИЗВ вокруг формулы
| 1 | = СУММПРОИЗВ (СУММЕСЛИМН ('Шаг 3'! D3: D7, 'Шаг 3'! C3: C7, H3)) |
Использование функции СУММЕСЛИМН на одном листе дает одно значение. На нескольких листах функция СУММЕСЛИМН выводит массив значений (по одному для каждого листа). Мы используем функцию СУММПРОИЗВ для суммирования значений в этом массиве.
Шаг 4. Замените ссылку на лист списком имен листов.
Мы хотим заменить Имя листа часть формулы со списком данных, содержащим значения: Янв, Фев, Мар, а также Апр. Этот список хранится в ячейках F3: F6.
Функция ДВССЫЛ, чтобы гарантировать, что текстовый список, показывающий Имена листов рассматривается как часть действительной ссылки на ячейку в функции СУММЕСЛИМН.
| 1 | = СУММПРОИЗВ (СУММЕСЛИМН (КОСВЕННАЯ ("'" & F3: F6 & "'!" & "D3: D7"), КОСВЕННАЯ ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
В этой формуле ранее записанная ссылка на диапазон:
| 1 | «Шаг 3»! D3: D7 |
Заменяется на:
| 1 | КОСВЕННО ("'" & F3: F6 & "'!" & "D3: D7") |
Кавычки затрудняют чтение формулы, поэтому здесь она показана с добавленными пробелами:
| 1 | КОСВЕННО ("'" & F3: F6 & "'!" & "D3: D7") |
Использование этого способа ссылки на список ячеек также позволяет нам суммировать данные из нескольких листов, которые не соответствуют стилю числового списка. Стандартная 3D-ссылка требует, чтобы имена листов были в стиле: Input1, Input2, Input3 и т. Д., Но приведенный выше пример позволяет использовать список любых Имена листов и чтобы они указывались в отдельной ячейке.
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММПРОИЗВ (СУММЕСЛИМН (КОСВЕННО ("'" & F3: F6 & "'!" & "D3: D7"), КОСВЕННОЕ ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = СУММПРОИЗВ (СУММЕСЛИМН (КОСВЕННО ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), КОСВЕННОЕ ("'" & $ F $ 3: $ F $ 6 & "'!" & «C3: C7»), H3)) |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Сумма, если на нескольких листах в Google Таблицах
Использование функции ДВССЫЛ для ссылки на список листов в функциях СУММПРОИЗВ и СУММЕСЛИМН в настоящее время невозможно в Google Таблицах.
Вместо этого для каждого входного листа можно выполнить отдельные вычисления СУММЕСЛИМН и суммировать результаты:
| 1234 | = СУММЕСЛИМН (янв! D3: D7; янв! C3: C7, H3)+ СУММЕСЛИМН (фев! D3: D7, фев! C3: C7, H3)+ СУММЕСЛИМН (мар! D3: D7, мар! C3: C7, H3)+ СУММЕСЛИМН (апр! D3: D7, апр! C3: C7, H3) |
