Microsoft Excel
трюки • приёмы • решения
Как суммировать данные с разных листов книг 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 счета на оплату коммунальных услуг за несколько месяцев с апреля по июнь включительно, то есть за один квартал.
Необходимо посчитать сумму выставленных счетов за указанный период времени — второй квартал года.
Для этого создаем дополнительный лист Excel, на котором составляем формулу суммирования.
Вписываем в ячейку:
— знак равно «=»;
— далее функцию «СУММ()»;
— в скобках указываем диапазон суммирования (Лист_Апрель:Лист_июнь!H26) — где Лист_апрель:Лист_Июнь — диапазон листов из которых берутся данные, H26 — суммируемая ячейка;
Нажимаем клавишу «Enter» на клавиатуре.
Формула «=СУММ(Лист_Апрель:Лист_июнь!H26)» считает сумму одной и той же ячейки с разных листов.