Как в экселе связать ячейки на разных листах
Создание связи между таблицами 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?
Переполнение стека- Около
- Продукты
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
- Вакансии Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
- Реклама Обратитесь к разработчикам и технологам со всего мира
dynamic - Связывание одного листа с другим в Excel
Переполнение стека- Около
- Продукты
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами