Для чего устанавливается связь между листами книги в экселе

Обновлено: 07.07.2024

На этом шаге мы рассмотрим связывание рабочих книг.

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

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

• Набрать ссылку на ячейку вручную . Эти ссылки могут быть достаточно длинными, так как они содержат, кроме адреса ячейки, имена рабочей книги и рабочего листа. Приемущество этого метода состоит в том, что исходная рабочая книга необязательно должна быть открыта.
• Указать ссылку на ячейку . Если исходная рабочая книга открыта, то чтобы создать формулы с внешними ссылками, можно воспользоваться стандартным методом указания.
• Выполнить команду Правка | Специальная вставка , а затем щелкнуть на кнопке Вставить связь . Этот метод требует, чтобы исходная рабочая книга была открыта.
• Воспользоваться командой Данные | Консолидация .

Общий синтаксис для формулы с внешней ссылкой выглядит следующим образом:
=[Имя_Рабочей_книги] Имя_Листа! Адрес_Ячейки .

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

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

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

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

Рис. 2. Диалоговое окно Файл не найден

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

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

Рис. 3. Диалоговое окно Связи

Чтобы обновить связанные формулы, активизируйте диалоговое окно Связи , выберите исходную рабочую книгу и щелкните на кнопке Обновить . Программа обновит связанные формулы согласно последней версии исходной рабочей книги. Для связей с рабочими листами в диалоговом окне Связи всегда установлена опция обновления Автоматическое , и ее нельзя заменить на опцию По запросу . Это означает, что эти связи будут обновляться во время открытия рабочей книги. Excel не обновляет связи автоматически, если в исходный файл будут внесены изменения.

Изучение этого сэкономит много времени и путаницы в долгосрочной перспективе.

Зачем связывать данные ячейки в Excel

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

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

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

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

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

Как связать две отдельные клетки

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

• В Sheet2 введите символ равенства (=) в клетку.
• Перейти на другую вкладку (Лист1) и щелкните ячейку, на которую вы хотите сослаться.
• Нажмите Войти завершить формулу.

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

Например, = Лист1! C3, где Лист1 это имя листа, C3 является ли ячейка, на которую вы ссылаетесь, и восклицательный знак (!) используется как разделитель между ними.

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

Примечание. Если имя листа содержит пробелы (например, Лист 1), то при вводе ссылки в ячейку необходимо указать имя в одинарных кавычках. подобно = ’Лист 1 ′! C3, Поэтому иногда проще и надежнее позволить Excel написать для вас справочную формулу.

Как связать диапазон ячеек

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

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

• Щелкните правой кнопкой мыши по ячейке (ям) и выберите Специальная паста…

• В левом нижнем углу меню выберите Вставить ссылку,

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

Как связать ячейку с функцией

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

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

У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.

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

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

• Откройте оба документа Excel.
• Во втором файле (Мастер службы поддержки), выберите ячейку и введите символ равенства знак равно
• Переключиться на оригинальный файл (Интернет Tech Советы) и нажмите на ячейку, на которую хотите сослаться.
• Нажмите Войти завершить формулу.

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

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

Станьте профессиональным пользователем Microsoft Excel

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

Урок № 7.
РАБОЧАЯ КНИГА EXCEL. СВЯЗЬ ТАБЛИЦ,

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

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

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

ХОД РАБОТЫ

ЗАДАНИЕ 1. На <Листе 1> заполните и оформите таблицу со­гласно рисунку:

Для чисел в ячейках, содержащих даты проведения занятий, задайте формат Дата (Код: Д.ММ).
Оценки за 1 четверть вычислите по формуле как среднее ариф­метическое текущих оценок, используя функцию СРЗНАЧ.

ЗАДАНИЕ 2. Сохраните таблицу в личном каталоге рабочего диска под именем jurnal.xks.

ЗАДАНИЕ 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего:
3.1.Скопируйте таблицу Литература на следующий лист, ис­пользуя команды меню: Правка – Переместить/скопировать

После выполнения команды появится лист < Лист 1 [2]>.
3.2. Скопируйте таблицу еще раз, используя те же команды меню.
После выполнения этой команды появится лист <Лист 1 [3]>.

ЗАДАНИЕ 4. Переименуйте листы: <Лист 1> в <Литература>, <Лист 1 [2]> в <;Алгебра>, <Лист 1 [3]> в <Геометрия>.

Для этого дважды щелкните на ярлычке соответствующего листа и задайте в диалоговом окне новое имя. Можно один раз щелкнуть по ярлычку правой кнопкой мыши и открыть контекст­но-зависимое меню, в котором выбрать пункт Переименовать.

ЗАДАНИЕ 5. На листах <Алгебра> и <Геометрия> в таблицах соответственно измените названия предметов, текущие оценки, даты.

Связь рабочих листов

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

ЗАДАНИЕ 6. На <Листе 2> создайте таблицу – Ведомость итоговых оценок за 1 четверть, для чего:
6.1. Переименуйте <Лист 2> в Лист <1 четверть>
6.2.Заполните таблицу ссылками на соответствующие ячейки других листов:

Литература! - ссылка на другой лист, символ ! обязателен А2 - адрес ячейки на листе <Литература>, используется относительная адресация.

• Размножьте формулу на последующие 5 ячеек столбца А и соответствующие ячейки столбца В.

В ведомости заполнятся колонки № и Фамилия учащегося.

• В ячейку СЗ занесите формулу =Литература! L3
• Размножьте формулу на последующие 4 ячейки столбца.

Столбец заполнился оценками за i четверть по литературе. Таким образом будет установлена связь между листом <1 четверть> - и листом <Литература>.

ЗАДАНИЕ 7. Удалите листы, которые не будут использоваться, в рабочей книге (3-16).
Для удаления листа выберите команду Удалить из контекстно-зависимого для ярлычков. Для удаления сразу несколь­ких рабочих листов предварительно выделите их при нажатой клавише <Cirl>.
Пока информация рабочего листа занимает один экран, дос­таточно одного окна. Если это не так, то можно открыть несколь­ко окон и одновременно отслеживать на экране разные области рабочего файла.
В нашем примере это также удобно сделать, расположив в разных окнах разные рабочие листы.

ЗАДАНИЕ 8. Проверьте правильность заполнения таблицы.
8.1. Откройте для просмотра еще одно окно. Выполните команды меню: Окно - Новое окно.
8.2. В новом окне выберите рабочий лист <Литература>.
8.3. Выполните команды меню: Окно - Упорядочить окна - Упо­рядочить , (*) каскадом.

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

Активным всегда является только одно из окон. Для активизации другого окна нужно щелкнуть по нему.

ЗАДАНИЕ 9. Проверьте, как работает связь таблиц.
9.1. На листе <Литература> Руслановой Галине исправьте две текущие оценки 3 на 4.
Внимание! Изменилась итоговая оценка Руслановой Галины за 1 четверть, как на листе <Литература>, так и на листе < 1 чет­верть>.
9.2. Исправьте текущие оценки Руслановой Галине опять на 3.
Таким образом, связь между различными листами одной ра­бочей книги действует.

Связь между файлами

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

ЗАДАНИЕ 10. Осуществите связь между листами разных рабочих книг. Заполните столбец в Ведомости оценками по литературе, взяв их из файла jurnal.xls.
10. 1. Очистите от оценок столбец Литература, используя ко­манды меню: Правка – Очистить содержимое.
10.2.В ячейку СЗ занесите формулу: ='A:\PETROV\[jurnal1.хls]Литература ‘!L3,

'A:\PETROV\[jurnal1.хls]Литература ‘!L3 - путь к файлу jurnal.xls и листу <Литература>. Обязательно в «». Имя файла обязательно в квадратных скобках. В пути должен быть указан ваш рабочий диск и ваш личный каталог.
10.3. Размножьте формулу на последующие 4 ячейки столбца.
Столбец заполнился оценками по литературе, т.е., связь установлена.

ЗАДАНИЕ 11. Самостоятельно заполните ведомость 1 четверть по предметам алгебра и геометрия.

ЗАДАНИЕ 12. Раскройте еще два окна и разместите в них листы <Алгебра> и <Геометрия>, упорядочив окна каскадом.

ЗАДАНИЕ 13. Закройте все окна, кроме окна <Литература>. Разверните это окно на весь экран.

ЗАДАНИЕ 14. На листе <Литература> напечатайте список учеников, которые закончили 1 четверть с оценкой 5,4,3 по предмету:
14.1. На листе <Литература>. В ячейку А10 введите текст: «Получили оценку 5:»
14.2. Скопируйте это текст в ячейки А17 и А24.
14.3. В ячейке А17 измените текст на: «Получили оценку 4:», а в ячейке А24 на: «Получили оценку 3:».
14.4. С использованием Автофильтра выберите записи с итоговой оценкой 5 за 1 четверть.
14.5. Выделите фамилии учеников и скопируйте их в 11-ю строку в столбце В.
14.6. С ячеек с фамилиями, которые были только что скопированы, снимите обрамление и фон.
14.7.Аналогичные действия произведите для учеников, которые получили оценку 3 и 4.
14.8.Отмените Автофильтр, выполнив команду: Данные – Фильтр – Автофильтр.
В результате всех действий лист <Литература> будет иметь вид:

ЗАДАНИЕ 15. Сохраните таблицу на диске в личном каталоге под именем work7.xls.

ЗАДАНИЕ 16. Распечатайте лист <Литература>, предваритель­но сняв колонтитулы. Меню: Файл - Печать, (*) - выделенные листы.

ЗАДАНИЕ 17. Подведите итоги.
Проверьте:
знаете ли вы, что такое:

• рабочая книга Excel;
• рабочий лист;
• правила записи формул для связи рабочих листов;
• упорядочивание окон каскадом;
• упорядочивание окон мозаикой.

умеете ли вы:

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

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

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

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

Создание связанных таблиц

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.

Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.

Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».

Способ 3: выполнение математических операций со связанными данными

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

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

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

Способ 4: специальная вставка

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

Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.

Способ 5: связь между таблицами в нескольких книгах

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

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

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

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

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

Способ 2: вставка значений

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7 комментариев

Спасибо! очень полезный материал!

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

  
• На вашем компьютере не установлен astral toolbox или его версия устарела
  
• Stronghold 2 как играть против компьютера
  
• Как переименовать пользователя oracle
  
• Digma s 37 обзор
  
• Archicad bim что это