Excel

Как сделать ссылку на ячейку в excel на другой лист: Работа со ссылками в Excel

Содержание

Как сделать ссылку с одного листа на другой в excel?

Опубликовано автором Konsultant

Содержание

  • 1 Ссылка на лист в формуле Excel
  • 2 Как сделать ссылку на лист в Excel?
  • 3 Ссылка на лист в другой книге Excel
  • 4 Создаем ссылку в Excel на другой лист
    • 4.1 Как создать ссылку на другую книгу Excel

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

Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.

Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”

 или выбрать данный пункт, перейдя в раздел “Вставка”.

После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.

Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.

Спасибо за внимание. Будем рады помочь.

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

Excel позволяет делать ссылки в формулах и функциях на другие листы и даже книги. Можно сделать ссылку на данные отдельного файла. Кстати в такой способ можно восстановить данные из поврежденного файла xls.

Ссылка на лист в формуле Excel

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

Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:

  1. Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
  2. Перейдите на Лист4, ячейка B2.
  3. Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
  4. Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
  5. Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

Как сделать ссылку на лист в Excel?

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

  1. Имя листа.
  2. Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
  3. Адрес на ячейку в этом же листе.

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

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

Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.

Описание элементов ссылки на другую книгу Excel:

  1. Путь к файлу книги (после знака = открывается апостроф).
  2. Имя файла книги (имя файла взято в квадратные скобки).
  3. Имя листа этой книги (после имени закрывается апостроф).
  4. Знак восклицания.
  5. Ссылка на ячейку или диапазон ячеек.

Данную ссылку следует читать так:

  • книга расположена на диске C: в папке Docs;
  • имя файла книги «Отчет» с расширением «.xlsx»;
  • на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.

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

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

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

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

A1 на листе Лист1, ссылка будет выглядеть так: Лист1!A1.

Обратите внимание, если в названии листа содержатся пробелы, то его необходимо заключить в одинарные кавычки (‘ ‘). Например, если вы хотите создать ссылку на ячейку A1, которая находится на листе с названием Бюджет июля. Ссылка будет выглядеть следующим образом: ‘Бюджет июля’!А1.

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

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

  1. Найдите ячейку, на которую хотите сослаться, и запомните, где она находится. В нашем примере это ячейка E14 на листе Заказ меню.
  2. Перейдите к нужному листу. В нашем примере, мы выберем лист Счет за услуги по питанию.
  3. Выбранный лист откроется.
  4. Найдите и выделите ячейку, в которой должно появиться значение. В нашем примере мы выделим ячейку B2.
  5. Введите знак равенства (=), название листа с восклицательным знаком(!) и адрес ячейки. В нашем примере мы введем =’Заказ меню’!E14.
  6. Нажмите Enter на клавиатуре. Появится значение, на которое идет ссылка. Если значение ячейки E14 на листе Заказ меню изменить, то и значение на листе Счет за услуги по питанию автоматически обновится.

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

Если Вы введете название листа неправильно, в ячейке появится ошибка #ССЫЛКА!

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

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

Помимо создания ссылок на другие листы, Excel также позволяет создавать ссылки на другие книги. Для этого перед ссылкой на рабочий лист необходимо подставить имя книги в квадратных скобках. Например, если имя книги – Книга1, листа – Лист1, то ссылка на ячейку А1 будет выглядеть следующим образом: =Лист1!А1

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

Оцените качество статьи. Нам важно ваше мнение:

Excel

Как в excel сделать ссылку на данные из другого файла?

Содержание

  • 1 Типы ссылок
  • 2 Процесс создания ссылки на ячейку в другом файле
  • 3 Внешний вид Excel ссылки на ячейку в другом файле
  • 4 Обновление данных в файле
  • 5 Ссылка на лист в формуле Excel
  • 6 Как сделать ссылку на лист в Excel?
  • 7 Ссылка на лист в другой книге Excel

Рубрика Excel

Также статьи о ссылках в Экселе:

  • Как добавить в Excel ссылку на файл?
  • Эксель ссылка на ячейку
  • Как сделать абсолютную ссылку в Excel?
  • Пример относительной ссылки в Excel

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

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

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

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

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

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

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

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

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

Типы ссылок

От типа ссылки зависит, на какие значения будет указывать ссылка при ее перемещении:

  1. Относительная ссылка показывает значение ячейки, удаленной от самой себя на одинаковое расстояние. При перемещении такой ссылки на один столбец вправо, ее значение будет соответствовать значению ячейки, расположенной на один столбец правее изначальной. Приложение Microsoft Office Excel обычно по умолчанию использует этот вид ссылок.
  2. Абсолютная ссылка показывает значение одной и той же ячейки исходных данных. Создается она путем некоторой корректировки готовой формулы. Для этого в ссылке перед номером столбца и строки устанавливается символ $. Если символ доллара устанавливается только перед номером столбца/строки, то с перемещением ссылки ее значения будут меняться в пределах указанного столбца/строки исходных данных.
  3. Смешанная ссылка включает в себя оба способа использования исходных данных.

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

Процесс создания ссылки на ячейку в другом файле

Как происходит?

  1. Открываем исходный файл Excel, в котором проводится работа, и второй, данные которого необходимо использовать в текущем файле.
  2. В исходном выделяем ячейку, в которой требуется создать ссылку, то есть использовать значения другого документа.
  3. Вводим в ячейку знак равенства. Если с данными ссылки необходимо выполнить вычисления, то далее вносится функция либо другие значения, которые должны предшествовать значению ссылки.
  4. Переходим ко второму файлу с необходимыми нам данными, выбираем лист документа, содержащий ячейки, на которые нужно сослаться.
  5. Выделяем ячейку или группу ячеек, на данные которых требуется создать ссылку.
  6. Если формула предполагает внесение дополнительных функций или данных, то переходим к исходному документу и дополняем формулу, после чего завершаем операцию нажатием клавиш Ctrl+Shift+Enter.
  7. В исходном файле просматриваем итоговую формулу, при необходимости корректируем ее, после чего снова нажимаем Ctrl+Shift+Enter.

Внешний вид Excel ссылки на ячейку в другом файле

Как выглядит?

  1. Ссылка на значение одной ячейки: =Лист1!А1
  2. Ссылка на значение диапазона ячеек: =Лист1!А1:В5

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

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

=D:ПапкаЛист1!A1

Обновление данных в файле

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

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

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

Гиперссылки широко используются в Интернете для навигации по сайтам и документам. Работая с файлами Excel вы также можете создавать гиперссылки, как на интернет-ресурсы, так и на ячейки, файлы или форму отправку Email.

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

Excel позволяет создавать гиперссылки для:

  • Перехода в определенное место в текущей книге;
  • Открытия другого документа или перехода к определенному месту в этом документе, например лист в файле Excel или закладке в документе Word;
  • Перехода на веб-страницу в Интернете;
  • Создания нового файла Excel;
  • Отправки сообщения электронной почты по указанному адресу.

Гиперссылку в Excel легко заметить, она выглядит как подчеркнутый текст, выделенный синим цветом:

В Excel существует два типа гиперссылок: абсолютные и относительные.

Абсолютные гиперссылки содержат в себе полный интернет-адрес или полный путь на компьютере. Например:

“https://excelhack.ru/funkciya-rept-povtor-v-excel/”

C:DesktopПрезентации

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

funkciya-rept-povtor-v-excel/

DesktopПрезентации

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

Чтобы создать гиперссылку проделайте следующие шаги:

  • Выделите ячейку, в которой вы хотите создать гиперссылку;
  • Нажмите правую клавишу мыши;
  • В выпадающем меню выберите пункт “Ссылка”:
  • В диалоговом окне выберите файл или введите веб-адрес ссылки в поле “Адрес”:
  • Нажмите “ОК”

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

  • На другой документ;
  • На веб-страницу;
  • На конкретную область в текущем документе;
  • На новую рабочую книгу Excel;
  • На окно отправки Email.

Чтобы указать гиперссылку на другой документ, например Excel, Word или Powerpoint файлы:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите “Файлом, веб-страницей”;
  • В поле “Искать в” выберите папку, в которой лежит файл, на который вы хотите создать ссылку;
  • В поле “Текст” введите текст, который будет отображаться в качестве ссылки;
  • Нажмите “ОК”.

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

Чтобы указать гиперссылку веб-страницу:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите пункт “Файлом, веб-страницей”;
  • Нажмите на кнопку “Интернет”;
  • Введите адрес веб-страницы в поле “Адрес”;
  • В поле “Текст” укажите текст, отображаемый в виде ссылки.

Для создания гиперссылки на конкретный лист текущего файла Excel или ячейки:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Файлом, веб-страницей”;
  • В диалоговом окне нажмите кнопку “Закладка…” и выберите лист создания ссылки. В поле “Введите адрес ячейки” укажите ячейку.

Для вставки гиперссылки, после нажатия на которую будет создан новый Excel-файл:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Новый документ”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Имя нового документа” укажите название нового Excel-файла;
  • В поле “Путь” укажите место хранения, где будет сохранен новый файл;
  • В поле “Когда вносить правку в новый документ” укажите настройку, когда следует приступить к редактированию нового файла после нажатия ссылки.
  • Нажмите кнопку “ОК”

Для вставки гиперссылки, после нажатия на которую будет создан e-mail:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Электронная почта”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Адрес эл. почты” укажите E-mail адрес, куда будет отправлено письмо;
  • В поле “Тема” укажите тему создаваемого письма;
  • Нажмите кнопку “ОК”

Как редактировать гиперсcылку в Excel

Для редактирования уже созданной гиперссылки, кликните по ячейке с ссылкой правой клавишей мыши и в выпадающем меню выберите “Edit Hyperlink”.

В диалоговом окне внесите корректировки в ссылку.

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

  • Перейдите на вкладку панели инструментов “Главная”, затем в раздел “Стили ячеек”:
  • Кликните на “Гиперссылка” правой кнопкой мыши и выберите пункт “Изменить” для редактирования формата ссылки:
  • Кликните на “Открывавшаяся гиперссылка” правой кнопкой мы и выберите пункт “Изменить” для редактирования формата ссылки;
  • В диалоговом окне “Стили” нажмите кнопку “Формат”:
  •  диалоговом окне “Format Cells” перейдите на вкладки “Шрифт” и/или “Заливка” для настройки формата ссылок:
  • Нажмите кнопку “ОК”.

Удаление гиперссылки осуществляется в два клика:

  • Нажмите правой клавишей мыши на ячейки с ссылкой;
  • В выпадающем меню выберите пункт “Удалить гиперссылку”.

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

Excel позволяет делать ссылки в формулах и функциях на другие листы и даже книги. Можно сделать ссылку на данные отдельного файла. Кстати в такой способ можно восстановить данные из поврежденного файла xls.

Ссылка на лист в формуле Excel

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

Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:

  1. Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
  2. Перейдите на Лист4, ячейка B2.
  3. Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
  4. Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
  5. Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

Как сделать ссылку на лист в Excel?

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

  1. Имя листа.
  2. Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
  3. Адрес на ячейку в этом же листе.

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

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

Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.

Описание элементов ссылки на другую книгу Excel:

  1. Путь к файлу книги (после знака = открывается апостроф).
  2. Имя файла книги (имя файла взято в квадратные скобки).
  3. Имя листа этой книги (после имени закрывается апостроф).
  4. Знак восклицания.
  5. Ссылка на ячейку или диапазон ячеек.

Данную ссылку следует читать так:

  • книга расположена на диске C: в папке Docs;
  • имя файла книги «Отчет» с расширением «.xlsx»;
  • на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.

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

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

Как связать файлы в Excel

Работая с Excel, наступит день, когда вам нужно будет ссылаться на данные из одной книги в другой. Это распространенный вариант использования, и его довольно легко осуществить. Присоединяйтесь к нам, и мы объясним, как связать два файла Excel, и обсудим множество возможных сценариев.

Как связать файлы Excel — какие есть доступные варианты?

Как связать локальные файлы в Excel?

Как связать файлы в настольном Excel?

Как связать облачные файлы в Excel?

Как связать два файла Excel?

Как связать широкий диапазон ячеек с Excel или другим сервисом?

Как связать файлы Excel и синхронизировать их в режиме реального времени?

Часто задаваемые вопросы: как связать файлы Excel

Как связать ячейки в разных файлах Excel?

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

Как связать столбцы Excel между файлами?

Как связать поля между несколькими файлами в Excel?

Как связать файлы Excel – подведение итогов

Как связать файлы Excel — какие есть доступные варианты?

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

Выбор способа связывания данных зависит также от общего объема того, что вы хотите связать. Если мы говорим здесь об определенных ячейках или столбцах из вашей книги, то методы по умолчанию вполне подойдут. Если вам нужно связать целые файлы Excel, использование таких инструментов, как Coupler.io с его интеграцией с Excel, может оказаться более эффективным. Но мы к этому придем!

Как связать локальные файлы в Excel?

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

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

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

Чтобы связать 2 файла Excel, хранящихся локально, у вас есть два варианта:

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

Как связать файлы в настольном Excel?

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

=[SourceWorkbook.xlsx]Sheet1!$A$1

Замените SourceWorkbook.xlsx на имя файла, хранящегося на вашем машина. Затем укажите нужный лист и ячейку. Ссылка на диапазон ячеек может выглядеть так:

=[MyFile.xlsx]Sheet1!A1:C8

Нажмите ENTER , чтобы сохранить формулу и извлечь данные. Если вы используете более старую версию Excel, вам может потребоваться вместо этого нажать CTRL + SHIFT + ENTER .

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

='C:\[MyFile.xlsx]Sheet1'!A1:C8

В качестве альтернативы , вы можете: 

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

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

Как связать облачные файлы в Excel?

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

Включающая его функция называется «Ссылки на книгу». Мы объясним, как это работает, в следующих главах.

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

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

Как связать два файла Excel?

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

Процесс прост:

  • В рабочей книге 1 (источник) выделите данные, которые вы хотите связать, и скопируйте их.
  • В рабочей книге 2 (назначение) щелкните правой кнопкой мыши первую строку и выберите значок Ссылка .

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

Чтобы включить синхронизацию данных, выберите Включить содержимое.

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

Для синхронизации нажмите кнопку Enable Content 9Кнопка 0068 с желтой панели.

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

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

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

Как связать широкий диапазон ячеек с Excel или другой службой?

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

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

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

При желании вы также можете импортировать данные Excel в другие службы, такие как Google Sheets или Google BigQuery, или перенести их в Excel из Airtable, Pipedrive, Hubspot и многих других.

Чтобы начать работу с Coupler.io, создайте учетную запись, войдите в систему и нажмите кнопку Добавить импортера .

Из списка исходных приложений выберите Excel .

Далее нажмите кнопку Подключить . Войдите в свою учетную запись Microsoft и разрешите подключение для Coupler.io.

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

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

Вы можете использовать стандартное форматирование Excel и вытащить, например, ячейки C1:D8. Вы также можете получить весь столбец, набрав, например, C1:C.

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

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

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

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

Как связать файлы Excel и синхронизировать их в режиме реального времени?

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

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

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

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

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

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

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

Напоминаем, что если вы используете Coupler.io для импорта данных из одной книги Excel в другую, вы сами выбираете расписание обновления. Некоторым вполне подойдет утренняя синхронизация с понедельника по пятницу. Другие предпочтут более частую синхронизацию — с Coupler.io вы можете делать это даже каждые 15 минут.

Часто задаваемые вопросы: Как связать файлы Excel

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

Как связать ячейки в разных файлах Excel?

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

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

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

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

В приведенном ниже примере вы можете увидеть данные, которые мы импортировали из нашего примера магазина Shopify с помощью Coupler.io.

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

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

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

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

= 'https://d.docs.live.net/18644c626caae38c/[myworkbook1.xlsx]Sheet1'!$C$2

ID будет следовать сразу после live.net ссылка:

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

Как связать столбцы Excel между файлами?

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

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

Затем выберите первую строку столбца, на который вы хотите добавить ссылку, и выберите значок «Ссылка». Будут импортированы все строки из выбранного столбца.

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

При вставке формулы для первой строки обязательно удалите второй знак $ (доллара), указывающий на конкретную ячейку. Итак, вместо ссылки:

(. ..)Лист1'!$C$2

Сделайте это:

(...)Лист1'!$C2

Как связать поля между несколькими файлами в Экселе?

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

Как вы помните из предыдущих глав, формулы для связывания ячеек между файлами Excel Online выглядят примерно так: ]Sheet1′!$C$8

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

Для этого создайте отдельный рабочий лист в целевом файле, где вы будете связывать все внешние данные. Назовите его соответствующим образом — например, «Источники». Свяжите нужные данные, скопировав их из исходного файла, а затем вставив в виде ссылок (так же, как мы делали это раньше).

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

Имя_листа!$A$1

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

=СРЗНАЧ(Источники!B2:B12 )

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

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

Как связать файлы Excel – подведение итогов

Связывание файлов Excel может сэкономить вам много времени и помочь автоматизировать многие скучные процессы.

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

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

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

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

Спасибо за внимание!

Назад в блог

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

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

Скачать практическую рабочую тетрадь

5 простых способов связать данные с одного листа на другой в Excel

1. Использование знака равенства для связывания данных

2. Применение символа «плюс» к данным связи

3. Связывание данных электронной таблицы с помощью параметра «Вставить ссылку»

4. Использование функции SUM для связывания данных

5. Связать данные на основе условия

Как автоматически перенести данные с одного листа Excel на другой

Как связать листы в Excel с мастер-листом

Вывод

Загрузить рабочую тетрадь

Вы можете скачать учебную тетрадь отсюда.

5 простых способов связать данные с одного листа на другой в Excel

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

1. Использование знака равенства для связывания данных

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

Шаги:

  • Сначала перейдите на лист Equal2 и введите = внутри ячейки C5 , но не нажимайте Введите .

  • Теперь перейдите на лист Equal1 и выберите ячейки из C5 до C10 .

  • Наконец, нажмите Введите , и это свяжет данные Equal1 с Equal2 .

2. Применение символа «плюс» к данным связи

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

Шаги:

  • Для начала перейдите на лист Plus2 и введите символ + внутри ячейки C5 .

  • Затем перейдите на лист Plus1 и выберите ячейки от C5 до C10 .
  • Теперь нажмите клавишу Enter , и это должно связать столбец Sales , как в предыдущем методе.

3. Связывание данных электронной таблицы с помощью параметра «Вставить ссылку»

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

Шаги:

  • Для начала перейдите на лист Paste Link1 и скопируйте ячейки с C5 на C10 .

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

4. Использование функции SUM для связывания данных

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

Шаги:

  • Как и ранее, щелкните лист SUM2 и вставьте следующую формулу в ячейку 9.0067 С10 :

  • После этого нажмите на листе SUM1 и выберите ячейки с C5 по C9 .

  • Наконец, нажмите клавишу Enter , и это свяжет данные двух листов и вычислит значение Total внутри листа SUM2 .

5. Связать данные на основе условия

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

Шаги:

  • Для этого метода щелкните на листе Condition2 и введите приведенную ниже формулу внутри ячейки C5 :

=ЕСЛИ(Условие1!C5>=1000,Условие1!C5,"")

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

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

Если вы хотите автоматически переносить данные с одного листа Excel на другой, то VBA будет лучшим вариантом. Давайте посмотрим, как это использовать.

Шаги:

  • Чтобы начать этот метод, перейдите на вкладку Разработчик и выберите Visual Basic .

  • Теперь выберите Вставить в окне VBA и нажмите Module .

  • Далее введите формулу ниже в новом окне:
 Опция Явный
Sub Automatic_Transfer()
Тусклый смартфон как строка, цена как строка
Рабочие листы ("VBA1"). Выберите
Смартфон = Диапазон ("B5")
Цена = Диапазон ("C5")
Рабочие листы ("VBA2"). Выберите
Рабочие листы ("VBA2"). Диапазон ("B4"). Выбрать
Если Рабочие Листы("VBA2").Range("B4").Смещение(1, 0) <> "" Тогда
Рабочие листы ("VBA2"). Диапазон ("B4"). Конец (xlDown). Выберите
Конец, если
ActiveCell.Смещение(1, 0).Выбрать
ActiveCell.Value = Смартфон
ActiveCell.Смещение(0, 1).Выбрать
ActiveCell.Value = Цена
Рабочие листы ("VBA1"). Выберите
Рабочие листы ("VBA1").  Диапазон ("B5: C5"). ClearContents
Конец суб

  • Затем щелкните лист VBA1 и вставьте некоторые данные в два столбца.
  • Затем откройте макрос на вкладке Разработчик , нажав Макросы .

  • Теперь в окне Macro выберите макрос Automatic_Transfer и нажмите Run .

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

Как связать листы в Excel с мастер-листом

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

Шаги:

  • Прежде всего, щелкните на листе MASTER1 и выберите ячейку C5 .
  • Теперь перейдите на вкладку Вставить и нажмите Вставить ссылку под ссылку .

  • Затем в новом окне выберите Поместить в этот документ и введите B5 как Введите ссылку на ячейку .
  • После этого выберите лист MASTER2 и нажмите OK .

  • Следовательно, это должно вставить ссылку на мастер-лист рядом с именем листа.

Вывод

Я надеюсь, что вы смогли применить методы, которые я показал в этом руководстве, о том, как связать данные в Excel с одного листа на другой. Как видите, способов добиться этого довольно много. Поэтому мудро выбирайте метод, который лучше всего подходит для вашей ситуации. Если вы застряли на каком-либо из шагов, я рекомендую пройти их несколько раз, чтобы устранить путаницу. Наконец, чтобы узнать больше о методах Excel , посетите наш веб-сайт ExcelDemy . Если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать в комментариях.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *