Как суммировать ячейки с разных листов в Excel

Обновлено: 03.07.2024

Microsoft Excel предоставляет возможность суммирования по нескольким листам, даже если ссылки на ячейки различаются. В этом руководстве мы рассмотрим два метода.

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

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

Обратите внимание, что рабочий лист, на который поступают данные из других листов, является целевым рабочим листом, а листы, предоставляющие данные, являются исходными рабочими листами. Ссылка на ячейку из другого рабочего листа: SheetName ! Ссылка на ячейку .

Пример ссылки и суммы

Мы показываем два разных способа создания формулы связи суммы:

Примечание. Формулы ссылок ссылаются на имена рабочих листов; а не заголовки файла Excel. Имена рабочих листов находятся на вкладках в нижней части рабочей книги, как показано на рисунке ниже. (Дополнительную информацию см. в разделе Управление рабочими книгами и рабочими листами.)

✦ Первый метод: написание формул вручную ✦

В этом методе используется функция СУММ Excel. Функция СУММ добавляет аргументы, перечисленные в скобках; например =СУММ(число1;число2. ) . Изучите изображение ниже.

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

Формула ссылки в нашем примере:
=СУММ( Вена ! B5 , Лондон ! B6 , Торонто ! B4 )

Формула ссылки в нашем примере:
=СУММ( Вена ! B5 , Лондон ! B6 ,
Торонто ! B4 ) .

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

Если имя исходного листа содержит пробел или специальный символ, оно должно быть заключено в одинарные кавычки. Например: ='Нью-Йорк'!B7 . (Примечание. Excel автоматически заключает любой рабочий лист с именем C или R в одинарные кавычки по внутренним причинам.)

✦ Второй метод: использование мыши и клавиатуры ✦

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

Откройте книгу Excel, содержащую рабочие листы.
На целевом листе щелкните ячейку, которая будет содержать формулу ссылки, и введите знак равенства , но НЕ нажимайте клавишу ВВОД (рис. 1 ниже).
Перейдите к первому исходному листу (Вена), щелкните ячейку, содержащую данные для связи (B5), и волнистые линии окружат ее (рис. 2). Нажмите Enter.

Excel возвращается к целевому листу (Итоги) и выделяет ячейку под формулой ссылки, которую мы только что создали в B6 (рис. 3). Отобразится текущее значение формулы.
Нажмите в ячейке с формулой ссылки (B6). В строке формул поместите курсор в конец формулы и введите знак +, но НЕ нажимайте Enter (рис. 4).

Перейдите к следующему исходному листу (Лондон), щелкните ячейку "Единицы" (рис. 5) и нажмите клавишу ВВОД. Excel возвращается на лист назначения, обновив значение формулы (24) и саму формулу: =Вена!B5+Лондон!B6 .

Повторите шаги 5 и 6, чтобы остальные рабочие листы были включены в формулу связи.

Обзор

Чтобы узнать, как связаны формулы, когда листы находятся в разных книгах или если листы и книги перемещаются, см. наш обзорный учебник Как связать данные электронной таблицы Excel.

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

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

Краткий обзор связывания данных

Давайте кратко рассмотрим основы связывания данных листа. Рабочие листы, предоставляющие данные, являются исходными рабочими листами. Лист, содержащий формулу для суммирования данных из исходных листов, называется рабочим листом назначения. А ссылка на ячейку на другом листе имеет следующий формат: SheetName ! Ссылка на ячейку .

В этом руководстве мы ссылаемся на имена рабочих листов. Это названия вкладок в нижней части окна книги, а не название или заголовок листа.

Суммирование по всем листам в книге

У нас есть лист назначения, итоги и 3 листа городов. Как показано ниже, все 3 листа городов имеют одинаковый формат. Формула в ячейке D4 рабочего листа «Итоги» суммирует «Единицы» в ячейке B3 (элемент синего цвета) по всем рабочим листам города (источника).

Чтобы просуммировать ячейку B3 по всем листам книги, введите: =СУММ('*' ! b3 )

Синтаксис =СУММ('*' ! CellReference ) . Используется функция СУММ, а звездочка, заключенная в одинарные кавычки, сообщает Excel о суммировании по ВСЕМ листам в книге.

После нажатия клавиши ввода в строке формул отображается =СУММ (Милан:Торонто ! B3 ). Это формула, которую Excel действительно хранит. Excel указывает диапазон ячеек: первое и последнее имена рабочих листов, разделенные двоеточием, а буква "b" заглавная.

Формула также может быть написана длинным образом, перечисляя каждый рабочий лист и ячейку:

=СУММ( Милан!B3 , Лондон!B3 , Торонто!B3 )

=СУММ( Милан!B3 , Лондон!B3 ,
Торонто!B3 )

Суммирование по выбранным листам в книге

Как нам написать формулу, если мы хотим суммировать только некоторые рабочие листы? Посмотрите на 3 примера ниже. [Если вам не нравится писать код, просмотрите эти методы, а также ознакомьтесь с методом мыши и клавиатуры, который мы объясняем в соответствующем руководстве.]

Чтобы суммировать значения Тампы и Торонто и исключить Милан, введите: =СУММ (Тампа:Торонто ! B3 )

Вставка рабочего листа с существующими формулами SUM Link

Будьте осторожны при вставке новых рабочих листов. Если вы использовали простую формулу, например. =СУММ('*' ! B3 ), чтобы просуммировать по всем листам книги, Excel включит новые данные листа в формулу суммирования. Это может быть или не быть тем, что вам нужно.

Аналогично, если вы указали диапазон в формуле, например =СУММ (Милан:Торонто ! B3 ) и вставьте лист внутри диапазона, Excel включит лист в формулу.

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

Положение целевого листа в книге

Что делать, если рабочий лист назначения не первый и не последний в порядке, а где-то посередине? Будет ли работать формула ссылки? да. Изучите изображение ниже.

Рабочий лист "Итоги" находится посередине шести других листов. Когда мы ввели =СУММ('*' !b3 ), Excel создал формулу, показанную в строке формул и показанную ниже, указав два диапазона рабочего листа: от Милана до Лондона и от Парижа до Торонто:

=СУММ( Милан:Лондон!B3 , Париж:Торонто!B3 )

=СУММ( Милан:Лондон!B3 ,
Париж:Торонто!B3 )

Обзор

Если это ваша первая попытка связать данные листа в Microsoft Excel, мы рекомендуем вам прочитать наше вводное руководство «Как связать данные электронной таблицы Excel».

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

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

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

Получите пример книги по приведенной выше ссылке, чтобы следовать ей.

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

Каждый лист имеет одинаковый формат, а таблица находится в одном и том же месте на каждом листе.

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

Создание этой формулы не очень эффективно, так как для этого нужно выбрать лист января, затем выбрать ячейку C3, затем ввести +, затем выбрать лист февраля и т. д.

Всего просматриваю 12 листов. Есть лучший способ!

Добавить формулу суммы в итоговую таблицу.

Введите начало формулы суммы =СУММ( .
Щелкните левой кнопкой мыши на листе января.
Удерживая нажатой клавишу Shift, щелкните левой кнопкой мыши лист Dec. Теперь выберите ячейку C3 на листе Dec. Добавьте в формулу закрывающую скобку и нажмите Enter .

Теперь ваша формула суммирования должна выглядеть так =СУММ(Янв:Декабрь!C3) .

Формула суммирует C3 по каждому листу с января по декабрь.

Вы также можете использовать этот метод с другими формулами, такими как COUNT, AVERAGE и т. д. Это более простой способ, чем циклический просмотр каждого листа по отдельности.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

< бр />

Приходилось ли вам когда-нибудь суммировать одну и ту же ячейку на нескольких листах? Это часто происходит, когда информация хранится на нескольких листах в едином формате. Например, это может быть ежемесячный отчет с вкладкой для каждого месяца (см. скриншот ниже в качестве примера).

< /p>

Посмотреть видео

Я вижу много примеров, когда пользователь щелкал одну и ту же ячейку на каждом листе, ставя символ «+» между ссылками. Если рабочих листов много, потребуется некоторое время, чтобы щелкнуть их все. Кроме того, если имена листов длинные, формула начинает выглядеть совершенно нечитаемой. На снимке экрана ниже показан пример такого подхода.

Формула в ячейке C5:

Как узнать, нажали ли вы на каждый рабочий лист? Что делать, если вы случайно пропустили один? Есть только один способ узнать это — проверить!

Скорее всего, вам не нужно делать все эти клики. И просто подумайте о времени, которое вы потратите впустую, если будет добавлена новая вкладка.

Хорошая новость заключается в том, что мы можем использовать другой подход, который позволит нам легко суммировать данные на разных листах.Я до сих пор помню, как коллега по работе впервые показал мне этот трюк; моя челюсть от изумления упала на землю. Я думал, что он гений Excel. Вот почему я делюсь этим здесь; используя этот подход, вы можете выглядеть гением Excel и для своих коллег по работе 🙂

СУММ по нескольким листам — базовая

Замените FirstSheet и LastSheet именами рабочих листов, между которыми вы хотите произвести суммирование. Если имена ваших рабочих листов содержат пробелы или являются именем диапазона (например, Q1 может быть именем листа или ссылкой на ячейку), то имена листов должны быть заключены в одинарные кавычки ('). В противном случае одинарные кавычки можно не указывать.
Замените A1 ссылкой на ячейку, которую вы хотите использовать.

С помощью этой красивой небольшой формулы мы можем увидеть все рабочие листы, включенные в расчет, просто взглянув на вкладки внизу. Взгляните на скриншот ниже. В расчет включены все вкладки с января по декабрь.

Формула в ячейке C5:

СУММ по нескольким листам — динамический

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

Посмотрите на скриншот ниже. Начальный и конечный листы пусты. Перетаскивая листы в начальный и конечный форзацы и из них, мы можем суммировать почти все, что захотим.

Формула в ячейке C5

Мы также можем создавать промежуточные расчеты. Например, мы могли бы добавить четверти в качестве временных подставок для книг. Дополнительным преимуществом является то, что вкладки также служат полезными разделителями презентаций. В приведенном ниже примере показано вычисление только таблиц за январь, февраль и март.

Формула в ячейке C5:

Excel может ошибочно принять Q1 и Q2 за ссылки на ячейки; поэтому необходимо добавлять одинарные кавычки вокруг имен листов.

Читайте также: