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

Обновлено: 21.11.2024

При работе с электронными таблицами в Microsoft Excel может возникнуть необходимость суммировать бизнес-данные из ячеек в нескольких электронных таблицах. Можно сделать так, чтобы формула суммы Excel ссылалась на другой лист или несколько листов и суммировала диапазон ячеек на этих листах.

Как использовать функцию суммирования для добавления ячеек на один лист

Вы можете использовать функцию "Сумма" для суммирования данных в двух или более ячейках или всех данных в диапазоне ячеек. Для этого вы можете ввести функцию Sum вручную или использовать кнопку Auto*Sum*, расположенную в разделе Editing в строке меню Excel.

Чтобы использовать функцию "Сумма" для суммирования столбца ячеек на листе, над которым вы работаете, просто щелкните первую открытую ячейку в нижней части столбца, который вы хотите добавить, и нажмите кнопку "Автосумма", чтобы вставить функцию "Сумма". . Это автоматически подсчитает сумму ячеек в столбце, который вы выбрали после нажатия Enter.

Если вы хотите ввести функцию суммы, чтобы указать, какие отдельные ячейки добавлять независимо от столбцов, напишите функцию суммы следующим образом: =СУММ(ячейка1, ячейка2). Например, это будет выглядеть примерно так =СУММ(A14,B17) при форматировании с двумя ячейками. Вы можете суммировать диапазон ячеек с помощью формулы суммы, используя двоеточие вместо запятой, например: =СУММ(A1:A14).

Как найти сумму Excel на другом листе.

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

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

Как сделать так, чтобы формула суммы Excel ссылалась на другой лист

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

Если у вас есть имя листа, на который вы хотите сослаться, вот как вы можете изменить формулу суммы Excel, чтобы можно было ссылаться на несколько листов одновременно. Добавьте имя листа, затем восклицательный знак, а затем имя ячейки, которую вы хотите добавить: =СУММ(Лист1!A14,Лист2!B17). Это заставит формулу Excel ссылаться на другой лист, но вы также можете упростить формулу до =СУММ(A14,Лист2!B17), что добавит ячейку из другого листа в ячейку на текущем листе.

Допустим, у вас есть рабочая книга с данными о доходах вашей компании за каждый месяц на отдельном листе. Если вы хотите сложить вместе доходы за январь и февраль, вы можете либо щелкнуть и выбрать ячейки за январь и февраль, либо ввести формулу вручную, чтобы она выглядела примерно так: =СУММ(Янв!C12,Февраль!C12) . После того, как вы нажмете Enter, в вашей ячейке должна быть сумма общего дохода за январь и февраль.

Формула суммы Excel: несколько листов, несколько ячеек

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

В качестве альтернативы вы можете сослаться на диапазон ячеек, чтобы суммировать их по всем листам, например: =СУММ(Лист1:Лист12!A12:C12). Это добавляет все ячейки от A12 до C12 на листах Sheet1 и Sheet12. В нашем примере, если вы хотите найти сумму диапазона ячеек на листе каждого месяца, вместо добавления одной ячейки каждого листа эта формула будет выглядеть так: =СУММ(Янв:Декабрь!A12:C12).< /p>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<р>1. Мы можем указать отдельные рабочие листы, как обсуждалось выше. Если мы хотим суммировать только Милан и Торонто. наша формула будет такой: =СУММ( Милан!B3 , Торонто!B3 )

<р>2. Мы можем суммировать по диапазону. Предположим, что в нашей книге также есть лист Тампа.

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

<р>3. Мы можем комбинировать вышеуказанные методы и смешивать диапазоны с отдельными рабочими листами, например: =СУММ(Лист1:Лист3!B3 , Лист6!B3 , Лист8!B3 )

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

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

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

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

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

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

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

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

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

Обзор

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

Об авторе

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

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

У меня есть несколько файлов Excel (количество файлов варьируется, но сейчас их около 30) в одной папке. Все файлы имеют одинаковый формат. Различаются только имена файлов и содержимое ячеек.
Есть ли способ суммировать (или получить) значения одной и той же ячейки и/или диапазона во всех файлах в новый рабочий лист файла Excel?

Пример: просуммируйте или получите значение в ячейке "A1" из Sheet1 всех файлов в C:\excelfolder.
и
Суммируйте или получите значения в диапазоне "A1:B2" из Sheet1 из всех файлов в папке C:\excelfolder.

Спасибо за любую помощь.

Факты об Excel

Несмотря на то, что Power Query была надстройкой в Excel 2010 и Excel 2013, в 2016 году Power Query стала частью Excel в разделе "Данные, получение и преобразование данных".

Военный корабль

Известный участник

трипп

Новый участник

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

Военный корабль

Известный участник

Позвольте мне обдумать ваши детали и опубликовать ответ.

(P.S. "HTH" = "Надеюсь, это поможет")

Легенда

трипп

Новый участник

Warship & VoG
Очень крутой процесс обучения с моей стороны. но на удивление весело тем не менее. Я очень признателен за любые предложения и попытаюсь объяснить решение (если я его выясню).

Позвольте мне немного пояснить, что я "хотел бы иметь" с функцией get.

Получить значение в ячейке "A1" из Sheet1 файла .xls в C:\excelfolder и поместить в ячейку "A1" активного листа.

Получить значение в ячейке "A1" из Sheet1 из следующего файла .xls в C:\excelfolder и поместить в "A2" активного листа.

Продолжайте так далее и так далее, пока значение "A1" не будет извлечено из всех файлов .xls в C:\excelfolder и помещено в столбец "A" активного листа. А1, А2, А3, А4.

Военный корабль

Известный участник

Военный корабль

Известный участник

Кеннет Хобсон

Известный участник

хоукзенг

Новый участник

Я нашел эту страницу, когда погуглил ту же проблему, у меня это работает, и большое спасибо, Warship.

Просто хочу подчеркнуть, чтобы код работал, имя листа в активной книге должно совпадать с именем нескольких файлов. В этом случае он должен называться «Лист1».(Я новичок в VBA, не уверен, что это очевидная вещь в мире VBA, :P).

У меня есть два дополнительных вопроса:

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