Как суммировать данные с разных листов книг Excel
Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .
Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.
Для создания такой формулы выполните приведенные ниже действия.
- Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
- Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
- Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
- Нажмите Enter, и формула будет введена в ячейку.
В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .
Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .
Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .
Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.
Как суммировать несколько листов в Excel
Вы можете использовать следующий базовый синтаксис для суммирования значений на нескольких листах в Excel:
=SUM(Sheet1!A1, Sheet2!B5, Sheet3!A12, . ) В следующем примере показано, как использовать этот синтаксис на практике.
Пример: суммирование на нескольких листах в Excel
Предположим, у нас есть три листа с названиями неделя1 , неделя2 и неделя3 , каждый из которых содержит данные о восьми баскетболистах и их общем количестве очков, набранных за эту неделю:
Каждый лист имеет одинаковый макет с «Игроком» в столбце A и «Очками» в столбце B.
Теперь предположим, что мы хотим взять сумму очков, набранных каждым игроком в течение каждой недели, и отобразить сумму на новом листе с именем total :
Для этого мы можем использовать следующую формулу:
=SUM(week1!B2, week2!B2, week3!B2) На следующем снимке экрана показано, как использовать эту формулу на практике:
Столбец «Всего очков» содержит сумму очков, набранных каждым игроком за неделю 1 , неделю 2 и неделю 3 .
- Всего за три недели игрок А набрал 20 очков.
- Игрок Б набрал в общей сложности 18 очков за три недели.
- Игрок C набрал в общей сложности 21 очко за три недели.
Обратите внимание: если каждая ячейка, которую вы хотите суммировать, находится в одном и том же месте на каждом листе, вы можете использовать следующий ярлык, чтобы получить сумму значений в ячейке B2 для каждого листа между week1 и week3 :
=SUM(week1:week3!B2) На следующем снимке экрана показано, как использовать эту формулу на практике:
Обратите внимание, что значения в столбце Total Points совпадают с теми, которые мы рассчитали ранее.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как суммировать данные с разных листов в эксель
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как суммировать данные с разных листов в эксель
Сумма данных со всех (нескольких) листов
Продолжаем описание инструментов по оптимизации работы с листами книги EXCEL.
Пример – отчётная таблица по продажам товаров за каждый месяц в разрезе покупателей, создаваемая вручную.
Если у вас уже есть ранее созданные таблицы на каждый месяц, расположенные на листах одной книги (файла), и вам необходимо просуммировать показатели за закрытые периоды на сегодняшний день, вы можете воспользоваться так называемыми «трехмерными формулами». Эти формулы охватывают диапазоны, в которые входят сразу несколько листов. Для этого:
-
Создаем дополнительный лист, в котором будем производить расчеты (Сводный) и копируем пустую таблицу.
Переключаемся на лист «Янв» и выбираем (кликаем) первую, соответствующую по расположению нашей формуле в сводном листе, ячейку.
Далее самое важное!
Нажимаем на кнопку «Shift» на клавиатуре, и кликаем на ярлык последнего листа, который необходимо добавить в расчет(«Дек», или закрытый период – «Апр», как в примере).
Будут сгруппированы все листы с «Янв» по «Апр», все данные на этих листах попадут в расчет.
Заканчиваем ввод нажатием Enter, формула на сводном листе будет иметь вид =СУММ(Янв:Апр!C6).
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже: