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

Создание связи между таблицами Excel

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

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

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

Создание связей между рабочими книгами

Открываем обе рабочие книги в Excel
В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

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

Прежде чем создавать связи между таблицами

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

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

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

Обновление связей

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

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

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Вам также могут быть интересны следующие статьи

Как вставить гиперссылку на другой лист в Excel 2016, 2013, 2010

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

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

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

Вставить гиперссылку в Excel

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

Добавить гиперссылку из контекстного меню

Первый метод создания гиперссылки в одной книге - использовать команду Hyperlink .

Выберите ячейку, в которую вы хотите вставить гиперссылку.
Щелкните ячейку правой кнопкой мыши и выберите в контекстном меню опцию Гиперссылка .

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

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

Функция ГИПЕРССЫЛКА в Excel

Excel есть функция ГИПЕРССЫЛКА, которую также можно использовать для создания ссылок между электронными таблицами в книге. Если вы не умеете сразу вводить формулы Excel на панели формул, сделайте следующее:

Выберите ячейку, в которую вы хотите добавить гиперссылку.
Перейдите к библиотеке функций на вкладке FORMULAS .
Откройте раскрывающийся список Lookup & Reference и выберите HYPERLINK .

Теперь вы можете увидеть имя функции в строке формул. Просто введите следующие два аргумента функции ГИПЕРССЫЛКА в диалоговом окне: link_location и friendly_name .

В нашем случае link_location относится к определенной ячейке в другом листе Excel, а friendly_name - это текст перехода для отображения в ячейке.

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

Адрес отображается в текстовом поле Link_location .
Добавьте цифровой знак (#) перед указанным местом.
Примечание. Очень важно ввести цифровой знак. Это указывает на то, что расположение находится в текущей книге. Если вы забудете ввести его, ссылка не будет работать, и при нажатии на нее появится ошибка.

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

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

Вставка ссылки путем перетаскивания ячейки

Самый быстрый способ создания гиперссылок в одной книге - это перетаскивание. Позвольте мне показать вам, как это работает.

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

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

Выберите ячейку назначения гиперссылки на листе 2.
Укажите на одну из границ ячейки и щелкните правой кнопкой мыши.
Зажмите кнопку и спуститесь к вкладкам листа.
Нажмите клавишу Alt и наведите указатель мыши на вкладку «Лист 1».
Если нажать клавишу Alt, вы автоматически перейдете на другой лист. Как только Лист 1 активирован, вы можете перестать удерживать клавишу.
Продолжайте перетаскивать в то место, куда вы хотите вставить гиперссылку.
Отпустите правую кнопку мыши, чтобы появилось всплывающее меню.
Выберите в меню Создать здесь гиперссылку .

После этого в ячейке появится гиперссылка. Щелкнув по нему, вы переключитесь на целевую ячейку на Листе 2.

Несомненно, перетаскивание - это самый быстрый способ вставить гиперссылку в рабочий лист Excel. Он объединяет несколько операций в одно действие. Это займет меньше времени, но немного больше концентрации внимания, чем два других метода. Так что вам решать, какой путь
выбрать.

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

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

Изменить назначение ссылки

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

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

На экране появится диалоговое окно Редактировать гиперссылку . Вы видите, что он выглядит так же, как диалог Insert Hyperlink , и имеет те же поля и макет.

Примечание. Есть еще как минимум два способа открыть диалоговое окно Edit Hyperlink . Вы можете нажать Ctrl + K или щелкнуть Hyperlink в группе Links на вкладке INSERT . Но не забудьте перед этим выделить нужную ячейку.
Обновите информацию в соответствующих полях диалогового окна Изменить гиперссылку .
Щелкните ОК и проверьте, куда теперь переходит гиперссылка. Примечание. Если вы использовали метод 2 для добавления гиперссылки в Excel, вам необходимо отредактировать формулу, чтобы изменить место назначения гиперссылки.Выберите ячейку, содержащую ссылку, а затем поместите курсор на панель формул, чтобы отредактировать ее.

Изменить формат гиперссылки

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

Перейдите в группу Styles на вкладке HOME .
Откройте список Стили ячеек .
Щелкните правой кнопкой мыши Hyperlink , чтобы изменить внешний вид гиперссылки, по которой не щелкнули. Или щелкните правой кнопкой мыши Followed Hyperlink , если гиперссылка была активирована.
Выберите опцию Изменить из контекстного меню.
Щелкните Формат в диалоговом окне Стили .
Внесите необходимые изменения в диалоговое окно Формат ячеек . Здесь вы можете изменить выравнивание гиперссылки и шрифт или добавить цвет заливки.
Когда вы закончите, нажмите ОК.
Убедитесь, что все изменения отмечены в разделе Стиль включает в диалоговом окне Стиль .
Нажмите ОК.

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

Удалить гиперссылку

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

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

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

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

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

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

Вас также может заинтересовать

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

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

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

Взаимодействие с другими людьми

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

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

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

Для заполнения Sheet1 (январь), Sheet2 (февраль) и Sheet3 (март), как показано на рисунке выше.
Перейти к Sheet4 в ячейке B2.
Поставить знак «=» и выйти на Лист1 (январь). Там нужно щелкнуть левой кнопкой мыши по ячейке B2.
Поставить знак «+» и повторить шаги предыдущего абзаца, но только на Листе 2 (февраль), а затем на Листе 3 (март).
Когда формула имеет следующий вид: = январь! B2 + февраль! B2 + март! B2, необходимо нажать Enter. Результат должен быть таким, как на рисунке.

Взаимодействие с другими людьми

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

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

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

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

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

Ссылка на лист в другой книге уже имеет 5 элементов. Выглядит это так: = 'C: \ Docs \ [Report.xlsx] Sheet1'! B2.

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

Путь к файлу книги (после знака открывается апостроф).
Название файла книги (название файла в квадратных скобках).
Именной лист этой книги (после имени закрывается апостроф).
Восклицательный знак.
Ссылка на ячейку или диапазон ячеек.

Эта ссылка должна читаться следующим образом:

книга находится на диске C: \ в папке Docs;
имя файла книги «Отчет» с расширением «.xlsx»;
на «Sheet1» в ячейке B2 - это значение, к которому относится формула или функция.

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

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

Как связать 2 ячейки листа Excel?

Переполнение стека

Около
Продукты
Для команд

Переполнение стека Общественные вопросы и ответы
Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
Вакансии Программирование и связанные с ним технические возможности карьерного роста
Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
Реклама Обратитесь к разработчикам и технологам со всего мира