Как сделать связь между ячейками в excel?

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

• В открытом окне листа выберите ячейку с исходным значением.
• Воспользуйтесь любым известным методом копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и так далее).
• Выберите ячейку для ссылки на оригинал.
• Перейдите на вкладку «Главная» и в группе «Буфер обмена» разверните меню до кнопки «Вставить».
• В списке команд выберите пункт «Вставить ссылку» (рис. 4.4).
• Исправьте результат, нажав клавишу Esc.

Примечание

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

 

 

Рис. 4.4. Вкладка «Главная». Вставить меню кнопки. Пункт «Вставить ссылку»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В появившемся диалоговом окне «Редактировать ссылки» выберите интересующую вас ссылку и нажмите кнопку «Обновить.

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

разрыв ссылки на источник заменит существующие формулы ссылки значениями, которые они возвращают. Например, ссылка =Prices!$B$4 будет заменена на 16. Ссылку нельзя отменить, поэтому перед выполнением операции рекомендуется сохранить книгу.

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

 

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

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

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

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

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

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

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

 

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

 

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

1. На первом листе выберите первую ячейку в столбце «Ставка». Ставим в нем знак "=". Затем нажмите на вкладку «Лист 2», которая находится в левой части интерфейса Excel над строкой состояния.
2. Переходит в другую область документа. Нажмите на первую ячейку в столбце «Оценка». Затем нажимаем кнопку Enter на клавиатуре для ввода данных в ячейку, где ранее был установлен знак «равно».
3. Затем происходит автоматический переход на первый лист. Как видите, в соответствующую ячейку прорисовывается значение ставки первого сотрудника из второй таблицы. Поставив курсор на ячейку, содержащую скорость, мы видим, что для вывода данных на экран используется обычная формула. Но перед координатами ячейки, где отображаются данные, стоит выражение «Лист2!», которое указывает на название области документа, где он находится. Общая формула в нашем случае выглядит так:

   =Лист2!B2

4. Теперь вам нужно передать данные о ценах всем остальным сотрудникам компании. Это конечно можно сделать так же, как мы выполняли задачу для первого сотрудника, но учитывая, что оба списка сотрудников идут в одном порядке, задачу можно значительно упростить и ускорить. Это можно сделать, скопировав формулу в область ниже. Из-за того, что ссылки в Excel по умолчанию относительные, значения при копировании меняются, что нам и нужно. Фактическую процедуру копирования можно выполнить с помощью маркера заполнения.

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

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

 

 

 

 

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

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

1. Выберите первый элемент в столбце «Оценка». Перейдите к Мастеру функций, нажав значок «Вставить функцию».
2. В Мастере функций в группе «Ссылки и массивы» найдите и выберите имя «ИНДЕКС».
3. Этот оператор имеет две формы: форму для работы с массивами и справочную форму. В нашем случае требуется первый вариант, поэтому в следующем открывшемся окне выбора формы выбираем его и нажимаем кнопку «ОК».
4. Запущено окно Аргументы оператора INDEX. Задачей указанной функции является отображение значения, находящегося в выбранном диапазоне, в строке с заданным номером. Общая формула для оператора ИНДЕКС:

   =ИНДЕКС(массив;номер строки;)

   «Массив» — аргумент, содержащий адрес области, из которой мы будем извлекать информацию с номером указанной строки.

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

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

   Поместите курсор в поле «Массив». Затем перейдите на лист 2 и, удерживая левую кнопку мыши, выделите все содержимое столбца «Оценка».

5. После отображения координат в окне оператора установите курсор в поле «Номер линии». Мы покажем этот аргумент с помощью оператора ПОИСКПОЗ. Поэтому нажимаем на треугольник, который находится слева от панели функций. Откроется список недавно использовавшихся операторов. Если вы найдете среди них название «ПОИСК», то можете нажать на него. В противном случае нажмите на самый последний пункт в списке — «Другие функции…».
6. Откроется окно мастера функций по умолчанию. Отправляем в ту же группу «Ссылки и массивы». На этот раз в списке выберите пункт «ПОИСК». Производим щелчок по кнопке «ОК».
7. Окно MATCH Operator Arguments включено. Указанная функция предназначена для отображения номера значения в конкретном массиве по имени. Именно благодаря этой функции мы будем вычислять номер строки до определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ следующий:

   =ПОИСКПОЗ(искомое значение, искомый массив;)

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

   Массив поиска — это аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его положения. В нашем случае эту роль будет играть адрес графы «Имя» на листе 2.

   «Тип соответствия» — необязательный аргумент, но, в отличие от предыдущего оператора, нам нужен этот необязательный аргумент. Он указывает, как оператор будет сопоставлять искомое значение с массивом. Этот аргумент может принимать одно из трех значений: -1; 0; 1. Для неупорядоченных массивов выберите «0». Этот вариант подходит для нашего случая.

   Итак, приступим к заполнению полей в окне аргументов. Ставим курсор в поле «Ищем значение», нажимаем на первую ячейку в столбце «Имя» на листе 1.

8. После отображения координат установите курсор в поле «Просмотренный массив» и перейдите на метку «Лист 2», которая находится внизу окна Excel над строкой состояния. Удерживая левую кнопку мыши, выделите курсором все ячейки в столбце «Имя».
9. После того, как их координаты отобразятся в поле «Просмотренный массив», перейдите в поле «Тип соответствия» и с помощью клавиатуры введите туда цифру «0». После этого возвращаемся в поле «Просмотренный массив». Дело в том, что мы будем копировать формулу, как делали это в предыдущем способе. Произойдет сдвиг адресов, но нам нужно зафиксировать координаты отображаемой матрицы. Он не должен двигаться. Выберите координаты курсором и нажмите функциональную клавишу F4. Как видите, перед координатами появился знак доллара, что означает, что связь из относительной превратилась в абсолютную. Затем нажмите на кнопку «ОК».
10. Результат отображается в первой ячейке столбца «Оценка». Но перед копированием нам нужно исправить еще одну область, а именно первый аргумент функции ИНДЕКС. Для этого выберите элемент столбца, содержащий формулу, и перейдите к строке формул. Выберите первый аргумент оператора ИНДЕКС (B2:B7) и нажмите кнопку F4. Как видите, рядом с выбранными координатами появился знак доллара. Щелкаем по клавише Enter. В общем случае формула приняла следующий вид:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Теперь вы можете копировать, используя дескриптор заполнения. Мы вызываем его так же, как говорили ранее, и расширяем его до конца диапазона таблицы.
12. Как видите, несмотря на то, что порядок строк в двух связанных таблицах не совпадает, все значения оформляются по фамилиям сотрудников. Это было достигнуто с помощью комбинации операторов ИНДЕКС-ПОИСКПОЗ.

Также читайте:

Функция ИНДЕКС в Excel Функция ПОИСКПОЗ в Excel

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

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

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

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

   =СУММ(число1;число2;…)

   Поля в окне соответствуют аргументам указанной функции. Хотя количество может достигать 255 штук, но для нашей цели будет достаточно всего одного. Ставим курсор в поле «Число1». Нажмите на метку «Лист 2» над строкой состояния.

4. После того, как мы переместились в нужную часть книги, выбираем столбец для суммирования. Делаем это курсором, зажав левую кнопку мыши. Как видите, координаты выделенной области сразу отображаются в поле окна аргумента. Затем нажмите на кнопку «ОК».
5. Далее мы автоматически переходим на Лист 1. Как видите, в соответствующем элементе уже указана общая сумма ставок сотрудников.
6. Но это не все. Как мы помним, зарплата рассчитывается путем умножения суммы ставки на коэффициент. Поэтому снова выделяем ячейку, где находится суммируемое значение. После этого перейдите в строку формул. Добавляем в нее знак умножения (*) к формуле, а затем нажимаем на элемент, где находится показатель коэффициента. Чтобы выполнить расчет, нажмите клавишу Enter на клавиатуре. Как видите, программа подсчитала общую зарплату по компании.
7. Возвращаемся на лист 2 и меняем размер ставки любого работника.
8. После этого снова заходим на страницу с итоговой суммой. Как видите, в связи с изменениями в связанной таблице, результат общей зарплаты был автоматически пересчитан.

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

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

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

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

3. Откроется окно «Специальная вставка». Нажмите кнопку «Вставить ссылку» в левом нижнем углу ячейки.
4. Какой бы вариант вы ни выбрали, значения из одного массива таблиц будут вставлены в другой. При изменении данных в исходном коде они автоматически изменятся и во вставленной области.

Урок: Специальная вставка в Excel

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

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

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

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

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

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

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

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

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

1. В рабочей книге, где значения извлекаются из других файлов, перейдите на вкладку «Данные». Нажимаем на иконку «Редактировать ссылки», которая находится на ленте в блоке инструментов «Подключения». Следует отметить, что если текущая книга не содержит ссылок на другие файлы, то эта кнопка неактивна.
2. Откроется окно редактирования ссылок. Выбираем из списка связанных книг (если их несколько) файл, с которым мы хотим разорвать ссылку. Нажмите кнопку «Удалить ссылку».
3. Откроется информационное окно, в котором есть предупреждение о последствиях дальнейших действий. Если вы уверены, что делать, нажмите кнопку «Разорвать ничью».
4. Тогда все ссылки на указанный файл в текущем документе будут заменены статическими значениями.

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

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

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

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