Как вставить формулу с другого листа в Excel

Обновлено: 01.07.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>

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

Ссылка на другой рабочий лист

Нажмите на ячейку, в которую хотите вставить ссылку.
Введите =, чтобы начать создание ссылки.
Выберите лист, содержащий ячейку, на которую вы хотите сослаться.

Ссылка на рабочий лист в формуле имеет восклицательный знак (!) после имени листа.

В выбранной ячейке появится значение из другого рабочего листа.

Ссылка на другую книгу

Вы также можете ссылаться на ячейки из совершенно разных файлов.

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

Если вы переместите, удалите или переименуете файл, на который ссылаетесь, ваша формула сломается.

Значение из другой книги отображается на листе.

Когда в формуле есть ссылка на другую книгу, имя файла отображается в квадратных скобках, например: [ ].

БЕСПЛАТНЫЙ краткий справочник

Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.

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

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

Хотя в том, как это работает, нет большой разницы, когда вы ссылаетесь на другой лист в том же файле или ссылаетесь на совершенно отдельный файл Excel, формат этой ссылки немного меняется.

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

Но волнуйтесь… ничего особенного!

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

Начнем!

Это руководство охватывает:

Ссылка на ячейку на том же листе

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

Например, если я нахожусь в ячейке B1 и хочу сослаться на ячейку A1, формат будет таким:

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

Ссылка на ячейку на другом листе

Если вам нужно сослаться на другой лист в той же книге, вам нужно использовать следующий формат:

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

Поэтому, если вам нужно сослаться на ячейку A1 на листе 1, вам нужно использовать следующую ссылку:

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

Итак, если вы хотите сослаться на диапазон A1:C10 на другом листе в той же книге, вам нужно использовать следующую ссылку:

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

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

Например, если вы хотите сослаться на ячейку A1 на листе с названием "Данные о продажах", вы будете использовать следующую ссылку:

И если лист называется Sales-Data, то для ссылки на ячейку A1 на этом листе необходимо использовать следующую ссылку:

Если вы ссылаетесь на лист в той же книге, а затем меняете имя листа, вам не нужно беспокоиться о том, что ссылка сломается. Excel автоматически обновит эти ссылки.

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

Позвольте мне показать вам лучший способ создания ссылок на ячейки в Excel.

Автоматическое создание ссылки на другой лист в той же книге

Гораздо лучший способ создать ссылку на ячейку на другом листе — просто указать Excel ячейку/диапазон, на который вы хотите создать ссылку, и позволить Excel создать ее самостоятельно.

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

Ниже приведены шаги для автоматического создания ссылки на другой лист:

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

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

Например, если у вас есть данные в ячейке A1:A10 на листе с именем "Данные о продажах" и вы хотите получить сумму этих значений на текущем листе, выполните следующие действия:

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

При этом вы заметите, что Excel автоматически вставляет ссылку на лист, на котором находится формула. Хотя это нормально и не вредит, в этом нет необходимости. В таком случае вы можете сохранить ссылку или удалить ее вручную.

Еще одна вещь, которую необходимо знать при создании ссылок путем выбора листа, а затем ячейки/диапазона, заключается в том, что Excel всегда будет создавать относительную ссылку (т. е. ссылки со знаком $ n0). Это означает, что если я скопирую и вставлю формулу (одну со ссылкой на другой лист) в другую ячейку, она автоматически скорректирует ссылку.

Вот пример, объясняющий относительные ссылки.

Предположим, я использую следующую формулу в ячейке A1 на текущем листе (для ссылки на ячейку A1 в имени листа SalesData)

Теперь, если я скопирую эту формулу и вставлю в ячейку A2, формула изменится на:

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

Если я хочу, чтобы эта ссылка всегда относилась к ячейке A1 на листе SalesData, мне придется использовать следующую формулу:

Знак доллара перед номером строки и столбца блокирует эти ссылки, чтобы они не изменялись.

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

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

Как создать ссылку на другую книгу в Excel

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

И, конечно же, название книги и рабочих листов также играют роль в определении формата (в зависимости от того, есть ли в имени пробелы или неалфавитные символы).

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

Внешняя ссылка на открытую книгу

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

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

Предположим, у вас есть книга «ExampleFile.xlsx», и вы хотите сослаться на ячейку A1 на листе Sheet1 этой книги.

Ниже приведена ссылка на это:

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

Ниже приведены примеры, в которых имена должны быть заключены в одинарные кавычки:

Как создать ссылку на другую книгу (автоматически)

Опять же, знать формат полезно, но лучше не вводить его вручную.

Вместо этого просто направьте Excel в правильном направлении, и он создаст эти ссылки для вас. Это намного быстрее и намного меньше шансов на ошибку.

Например, если у вас есть данные в ячейке A1:A10 в рабочей книге с именем "Файл примера" на листе "Данные о продажах" и вы хотите получить сумму этих значений на текущем листе, быть шаги:

Введите следующую формулу на текущем листе (где вам нужен результат): =Sum(
Перейдите к рабочей книге «Файл примера».
Выберите лист «Данные о продажах».
Выберите диапазон, который вы хотите добавить (A1:A10). Как только вы это сделаете, Excel автоматически создаст ссылку на этот диапазон (это можно увидеть в строке формул)
Нажмите клавишу ввода.

Это мгновенно создаст формулу с правильными ссылками.

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

Если вы хотите изменить это, вам нужно изменить ссылки вручную.

Внешняя ссылка на закрытую книгу

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

Но когда это закрыто, Excel не знает, где искать ячейки/диапазон, на которые вы ссылаетесь.

Поэтому при создании ссылки на закрытую книгу также необходимо указать путь к файлу.

Ниже приведена ссылка на ячейку A1 на листе Лист1 в книге Пример файла. Поскольку этот файл не открыт, он также относится к месту, где файл сохранен.

Приведенный выше справочник состоит из следующих частей:

Путь к файлу – место в вашей системе или сети, где находится внешний файл.
Имя файла — имя внешней книги. Это также будет включать расширение файла.
Имя листа — имя листа, на котором вы ссылаетесь на ячейки/диапазоны.
Адрес ячейки/диапазона – точный адрес ячейки/диапазона, на который вы ссылаетесь.

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

Влияние изменения местоположения файла на ссылки

Когда вы создаете ссылку на ячейку/диапазон во внешнем файле Excel, а затем закрываете его, ссылка теперь также использует путь к файлу.

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

Поэтому, если вы закроете и откроете эту книгу, она сообщит вам, что ссылка не работает, и вам нужно либо обновить ссылку, либо полностью ее разорвать. Он покажет вам подсказку, как показано ниже:

Когда вы нажмете «Обновить», вам будет показано другое приглашение, в котором вы можете выбрать параметры для редактирования ссылок (которое покажет вам диалоговое окно ниже)

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

Ссылка на определенное имя (в той же или внешней книге)

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

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

Например, проще использовать =SalesData вместо =[Example File.xlsx]Sheet1′!$A$1:$A$10

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

Вот шаги для создания именованного диапазона для диапазона ячеек:

Теперь ваш именованный диапазон создан, и вы можете использовать его вместо ссылок на ячейки с адресами ячеек.

Например, если я хочу получить сумму всех этих ячеек в диапазоне SalesData, вы можете использовать следующую формулу:

А что, если вы хотите использовать этот именованный диапазон на других листах или даже в других книгах?

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

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

Именованные диапазоны на уровне рабочей книги и рабочего листа. Когда вы создаете именованный диапазон на уровне рабочей книги, может быть только один диапазон с таким именем. Поэтому, если вы ссылаетесь на него из внешних книг или листов в той же книге, вам не нужно указывать имя листа (поскольку оно доступно для использования во всей книге). Если вы используете именованный диапазон на уровне рабочего листа, вы сможете использовать его только на рабочем листе, и при ссылке на него вам также нужно будет указать имя рабочего листа.

Ссылка на определенное имя на том же листе или в книге

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

Например, если я хочу получить сумму всех ячеек в созданном нами именованном диапазоне (SaledData), я могу использовать следующую формулу:

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

Если вы хотите использовать его на другом листе (скажем, на Листе2), вам нужно использовать следующую формулу:

И если в имени листа есть пробелы или буквенно-цифровые символы, вам придется поместить имя листа в одинарные кавычки.

Ссылка на определенное имя в другой книге (открытой или закрытой)

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

Например, если у вас есть книга Excel с именем ExampleFile.xlsx и именованный диапазон с именем SalesData, вы можете использовать приведенную ниже формулу, чтобы получить сумму этого диапазона из другой книги:

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

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

Ниже приведен пример ссылки на именованный диапазон на уровне листа:

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

Если вы ссылаетесь на именованный диапазон в закрытой книге, вам также потребуется указать путь к файлу. Ниже приведен пример этого:

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

Как создать ссылку на именованный диапазон

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

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

Предположим, вы создали именованный диапазон SalesData, который хотите использовать в формуле для СУММИРОВАНИЯ всех значений в именованном диапазоне.

Вот как это сделать:

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

Примечание. Этот метод работает только для диапазонов имен, которые находятся в одной книге. Если вы хотите получить имена во внешней книге, вам нужно будет пойти туда, проверить имя в диалоговом окне «Вставить имя», а затем вернуться и использовать его в своей формуле (в правильном формате, используя имя файла). < /p>

Это все, что вам нужно знать о том, как ссылаться на другие листы или книги и как создать внешнюю ссылку в Excel.

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

Выберите ячейку, в которую должна быть помещена формула, например: C7

Нажмите знак равенства, а затем щелкните лист, на который вы хотите сослаться.

Нажмите на ячейку, содержащую нужное вам значение.

Нажмите Enter или щелкните галочку в строке формул.

Теперь ваша формула будет отображаться с правильной суммой в ячейке C7.

Имя листа всегда будет иметь восклицательный знак в конце. Далее следует адрес ячейки.

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

Если значение в исходном листе изменится, значение этой ячейки также изменится.

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

Ссылка на другой лист — функция ДВССЫЛ

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

Когда вы ссылаетесь на другой лист в Excel, вы обычно вводите имя листа, а затем восклицательный знак и ссылку на ячейку. Поскольку имена листов часто содержат пробелы, мы часто заключаем имя листа в одинарные кавычки.

Поэтому в приведенной выше формуле мы использовали функцию ДВССЫЛ, чтобы обратиться к названию листа в ячейке B7, например: «Северный».

Поэтому вся приведенная выше формула будет

где мы заменили название листа «Северный» на ячейку B7.

Затем мы можем скопировать эту формулу вниз до C8 и C9, и имя листа «Северный» будет заменено на «Южный» и «Западный» по мере копирования формулы.

Ссылка на другой лист — формула массива

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

Нажмите знак равенства, а затем щелкните лист, содержащий исходные данные.

Выделите соответствующие ячейки исходных данных.

Нажмите Enter, чтобы ввести формулу на рабочий лист Target.

Вы заметите, что ячейки содержат диапазон (C10:E10), но каждый соответствующий столбец будет отображать значение только из соответствующего столбца в исходной книге.

Ссылка на название диапазона

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

В приведенном выше примере общие значения западного офиса в строке 10 называются Western.

Нажмите на листе Головной офис, выделите нужные ячейки и нажмите знак равенства на клавиатуре.

Введите название диапазона, которое вы создали, например: Western.

Нажмите Enter.

Будет создана формула массива.

Ссылка на другую книгу

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

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

Нажмите на ячейку, в которую вы хотите поместить исходные данные, например: C12

Нажмите клавишу равенства на клавиатуре и щелкните исходную ячейку в другой книге.

Нажмите Enter.

Формула, введенная в исходный лист, будет иметь ссылку на внешний файл, а также ссылку на имя листа во внешнем файле.

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

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