Ссылка на другой лист в excel в формуле: Эксель ссылка на другой лист

Ссылки в Excel — statanaliz.info

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

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

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

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

Относительные ссылки Excel

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

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

=A1,

где буква обозначает столбец, а число – строку.

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

Абсолютные ссылки

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

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ (<Shift+4> в английской раскладке):

=$A$1

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

Смешанные ссылки

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

=A$1

=$A1

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т. д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

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

Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

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

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Всего доброго и до новых встреч.

Поделиться в социальных сетях:

Функция ДВССЫЛ в Excel — TutorExcel.Ru

Функция ДВССЫЛ в Excel

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

Функция ДВССЫЛ чрезвычайно полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и диапазоны в формуле не изменяя при этом саму формулу.
Другими словами, введенная формула =B2 идентична формуле =ДВССЫЛ(«B2»), однако в первом варианте мы оперируем ссылкой, а во втором — текстом, который можно изменять.

Описание функции ДВССЫЛ

ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строкой.

• Ссылка на ячейку (обязательный аргумент) — ссылка в виде текста вида A1 или R1C1;
• A1 (необязательный аргумент) — вид ссылки, в случае когда аргумент принимает значение ИСТИНА (или опущен), то ссылка трактуется как вид A1, когда принимает значение ЛОЖЬ, то как вид R1C1.

Примеры использования функции ДВССЫЛ

Пример 1. Ссылка на ячейку

Начнем с простой задачи, который мы уже частично разобрали.
Введем произвольное значение в ячейку A1, теперь чтобы сделать ссылку на ячейку введем формулу =ДВССЫЛ(«A1»), например, в ячейку A2:

Пример 2.

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

Немного усложним задачу, и применим формулу ДВССЫЛ для ссылки на другой лист.
Перейдем на любой другой лист книги и вводим формулу =ДВССЫЛ(«Пример_1!A1»), где лист Пример_1 — лист из первого примера:

Пример 3. Функции

Рассмотрим примеры с одновременным применением функции ДВССЫЛ и других функций.

Функция СУММ

Предположим, что у нас имеется таблицами с помесячными данными о продажах фруктов за 12 месяцев, и мы хотим просуммировать продажи накопленным итогом за переменное количество месяцев:

Функция СУММ с прямой ссылкой на диапазон решает эту задачу, например, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
Однако тогда при изменении периода нам придется менять и диапазон в исходной формуле.
Обойдем эту проблему записав диапазон в текстовом виде с использованием ссылок на другие ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&»:»&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:

Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

Функция ПОИСКПОЗ

Рассмотрим еще один пример. Предположим, что у нас есть результаты тестирования группы кандидатов и критерии их оценки:

Записываем в оценку кандидатов формулу =ДВССЫЛ(«G»&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Поделиться с друзьями:

Поиск по сайту:

Текст,Формулы,Функции

• ← Всплывающие подсказки в Excel
• Количество и сумма ячеек по цвету в Excel →

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

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

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

• Данные из одной или нескольких смежных ячеек на листе.

• Данные, содержащиеся в разных областях рабочего листа.

• Данные на других рабочих листах в той же рабочей книге.

Например:

Эта формула:	Относится к:	и возвращает:
= С2	Ячейка C2	Значение в ячейке C2.
=A1:F4	Ячейки от A1 до F4	Значения во всех ячейках, но после ввода формулы необходимо нажать Ctrl+Shift+Enter. Примечание . Эта функция не работает в Excel для Интернета.
= Актив-Обязательство	Ячейки с именем Актив и пассив	Значение в ячейке с именем Обязательство вычитается из значения в ячейке с именем Актив.
{=Неделя1+Неделя2}	Диапазоны ячеек с именами Week1 и Week2	Сумма значений диапазонов ячеек с именами Week1 и Week 2 в виде формулы массива.
=Лист2!B2	Ячейка B2 на Листе 2	Значение в ячейке B2 на Листе2.

1. Щелкните ячейку, в которую вы хотите ввести формулу.

2. В строке формул введите = (знак равенства).

3. Выполните одно из следующих действий:

   • org/ListItem»>

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

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

   • Ссылка на определенное имя     Чтобы создать ссылку на определенное имя, выполните одно из следующих действий:

     • Введите имя.

     • org/ListItem»>

       Нажмите F3, выберите имя в поле Вставить имя и нажмите OK .

       Примечание.

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

4. Выполните одно из следующих действий:

   • Если вы создаете ссылку в одной ячейке, нажмите Enter.

   • Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

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

     Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

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

! ) в начале ссылки на ячейку. В следующем примере функция листа с именем AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе с именем Marketing в той же книге.

1. Ссылается на рабочий лист с именем Marketing

2. Ссылается на диапазон ячеек между B1 и B10 включительно

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

1. Щелкните ячейку, в которую вы хотите ввести формулу.

2. В строке формул введите

   = (знак равенства) и формулу, которую вы хотите использовать.

3. Щелкните вкладку рабочего листа, на который будет сделана ссылка.

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

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

Можно также скопировать и вставить ссылку на ячейку, а затем использовать команду Link Cells для создания ссылки на ячейку. Вы можете использовать эту команду для:

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

• Упростить создание ссылок на ячейки между рабочими листами и рабочими книгами. Команда Link Cells автоматически вставляет правильный синтаксис.

1. Щелкните ячейку, содержащую данные, на которые вы хотите создать ссылку.

2. Нажмите Ctrl+C или перейдите на вкладку Главная и в группе Буфер обмена нажмите Копировать .

3. org/ListItem»>

   Нажмите Ctrl+V или перейдите на вкладку Главная , в группе Буфер обмена нажмите Вставить .

   По умолчанию при вставке скопированных данных появляется кнопка Параметры вставки.

4. Нажмите кнопку Параметры вставки , а затем нажмите Вставить ссылку .

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

2. org/ListItem»>

   Выполните одно из следующих действий:

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

   • Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

   • В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

   • org/ListItem»>

     Нажмите F3, выберите имя в поле Вставить имя и нажмите OK .

3. Нажмите Enter или, для формулы массива, нажмите Ctrl+Shift+Enter.

   Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав 9.0027 CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

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

1. Выполните одно из следующих действий:

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

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

2. На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с Определить имя , а затем щелкните Применить имена .

3. В поле Применить имена щелкните одно или несколько имен, а затем щелкните ОК .

1. Выберите ячейку, содержащую формулу.

2. org/ListItem»>

   В строке формул выберите ссылку, которую вы хотите изменить.

3. Нажмите F4 для переключения между типами ссылок.

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

1. Щелкните ячейку, в которую вы хотите ввести формулу.

2. В строке формул введите = (знак равенства).

3. org/ListItem»>

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

4. Выполните одно из следующих действий:

   • Если вы создаете ссылку в одной ячейке, нажмите Enter.

   • Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

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

     Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать 9.0027 ВВЕДИТЕ , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

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

1. Относится к рабочему листу под названием «Маркетинг 9».0003

2. Относится к диапазону ячеек от B1 до B10 включительно

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

1. Щелкните ячейку, в которую вы хотите ввести формулу.

2. В строке формул введите = (знак равенства) и формулу, которую вы хотите использовать.

3. Щелкните вкладку рабочего листа, на который будет сделана ссылка.

4. org/ListItem»>

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

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

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

2. Выполните одно из следующих действий:

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

   • Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

   • В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

3. Нажмите Enter или, для формулы массива, нажмите Ctrl+Shift+Enter.

   Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав 9.0027 CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

1. Выберите ячейку, содержащую формулу.

2. В строке формул выберите ссылку, которую вы хотите изменить.

3. org/ListItem»>

   Нажмите F4 для переключения между типами ссылок.

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

Нужна дополнительная помощь?

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

Ссылка на ячейку Excel на другом листе или в рабочей книге — таблицы Excel и Google

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

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

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

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

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

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

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

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

имя_листа!адрес_соты

Например:

 =Northern!C10 

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

Например:

 ='Северный офис'!C10 

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

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

Ссылка на другой лист – функция INDIRECT

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

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

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

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

 = ДВССЫЛ ("'" & Северный & "!C10") 

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

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

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

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

Например:

Выберите C8:E8

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

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

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

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

Ссылка на имя диапазона

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

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

Щелкните на листе Head Office, выделите нужные ячейки и нажмите на клавиатуре знак равенства.

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

Нажмите Введите .

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

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

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

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

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

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

Нажмите Введите .

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

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

[Workbook_name]Sheet_name!Cell_address

Например:

 =[SalesFigures.xlsx]Northern!$C$10 

 

Вы заметите, что ссылка на ячейку стала абсолютной. Это означает, что вы НЕ МОЖЕТЕ перетащить его в столбцы D и E, если вы не удалите абсолют.

Щелкните ячейку, затем щелкните строку формул и щелкните адрес ячейки строки формул.

Нажимайте F4 до тех пор, пока не будет удален абсолют.

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

Перетащите формулу в столбцы D и E.

Ссылка на другой лист в Документах Google

Связывание листов с формулами работает в Документах Google так же, как и в Excel.

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

Нажмите Enter.

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

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

Имя_листа!Адрес_соты

Например:

 =Северный!C10 

 

Перетащите формулу для всех листов D и E, чтобы заполнить их.

 

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

Формула массива также будет работать таким же образом.

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

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

Нажмите Shift + Введите .

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

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

Если вы хотите связать файлы Google Sheets вместе, вам нужно использовать функцию IMPORTRANGE

Откройте исходный файл листа Google, чтобы скопировать URL-адрес файла.

Например,

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

Нажмите на клавиатуре знак равенства и введите имя функции, например: IMPORTRANGE, затем скобки и кавычки.

Вставьте URL-адрес, скопированный из исходного листа Google, в формулу.

Закройте кавычки

Например:

 =IMPORTRANGE("https://docs.google.