При выполнении определенных задач в Excel иногда необходимо управлять несколькими таблицами, которые также связаны друг с другом. То есть данные из одной таблицы вставляются в другие, и при их изменении значения во всех связанных диапазонах таблиц пересчитываются.
Связанные таблицы очень полезны для обработки больших объемов информации. Сложить всю информацию в единую таблицу, тем более, что она неоднородна, не очень удобно. С такими объектами сложно работать и найти. Эта проблема предназначена для устранения связанных таблиц, информация между которыми распределена, но в то же время она взаимосвязана. Связанные диапазоны таблиц можно размещать не только внутри листа или книги, но и в отдельных книгах (файлах). Последние два варианта чаще всего используются на практике, так как цель этой технологии — только отойти от накопления данных и накопление их на одной странице принципиально не решает проблему. Давайте узнаем, как создавать и как работать с этим типом управления данными.
Создание связанных таблиц
Прежде всего, остановимся на вопросе, как можно создать взаимосвязь между разными диапазонами таблиц.
Способ 1: прямое связывание таблиц формулой
Самый простой способ привязать данные — использовать формулы, которые относятся к другим диапазонам таблиц. Это называется прямым подключением. Этот метод интуитивно понятен, поскольку он очень похож на создание ссылок на данные в единой матрице таблиц.
Давайте посмотрим, как на примере можно образовать связь путем прямого подключения. У нас есть две таблицы на двух листах. В таблице заработная плата рассчитывается по формуле путем умножения ставки сотрудника на единый коэффициент для всех.
Второй лист содержит табличный диапазон, содержащий список сотрудников с их заработной платой. Список сотрудников в обоих случаях представлен в одинаковом порядке.
вам необходимо убедиться, что данные тарифа со второго листа занесены в соответствующие ячейки первого.
- На первом листе выберите первую ячейку в столбце «Ставка». Мы ввели знак «=». Затем щелкните вкладку «Лист 2», которая находится в левой части интерфейса Excel над строкой состояния.
- Перейдите ко второй области документа. Щелкните первую ячейку в столбце «Ставка». Затем нажимаем кнопку Enter на клавиатуре, чтобы вставить данные в ячейку, где ранее был установлен знак «равно».
- Затем происходит автоматический переход к первому листу. Как видите, ставка первого сотрудника из второй таблицы заносится в соответствующую ячейку. Поместив курсор на ячейку, содержащую курс, мы видим, что для вывода данных на экран используется обычная формула. Но перед координатами ячейки, из которой выводятся данные, стоит выражение «Sheet2!», Которое указывает название области документа, в которой они расположены. Общая формула в нашем случае выглядит так:
= Лист2! B2
- Теперь вам нужно передать данные о ставках всех других сотрудников на предприятии. Конечно, это можно сделать так же, как мы выполнили задачу для первого сотрудника, но поскольку оба списка сотрудников находятся в одном порядке, задачу можно значительно упростить и ускорить. Это можно сделать, просто скопировав формулу в указанный ниже диапазон. Из-за того, что ссылки в Excel по умолчанию относительные, при копировании происходит смещение значений, что нам и нужно. Эту же процедуру копирования можно выполнить с помощью маркера заполнения.
Затем мы помещаем курсор в нижнюю правую область элемента с формулой. Курсор должен превратиться в черный квадрат с перекрестной заливкой. Заблокируйте левую кнопку мыши и перетащите курсор в конец столбца.
- Все данные из аналогичного столбца на листе 2 были вставлены в таблицу на листе 1. При изменении данных на листе 2 они также автоматически изменятся на первые.
Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ
Но что, если список сотрудников в массивах таблиц находится в другом порядке? В этом случае, как обсуждалось ранее, один из вариантов — вручную установить связь между каждой из этих ячеек, которые необходимо связать. Но это подходит только для маленьких столиков. Для огромных диапазонов в лучшем случае реализация такого варианта займет много времени, а в худшем — вообще не будет реализована на практике. Но эту проблему можно решить с помощью группы операторов ИНДЕКСА — ПОИСКПОС. Давайте посмотрим, как это можно сделать, связав данные в диапазонах таблиц, о которых говорилось в предыдущем методе.
- Выберите первый элемент в столбце «Ставка». Перейдите к мастеру, щелкнув значок «Вставить функцию».
- В мастере в группе «Ссылки и массивы» найдите и выберите имя «ИНДЕКС».
- Этот оператор имеет два модуля: модуль для работы с массивами и справочный модуль. В нашем случае первый вариант обязателен, поэтому в следующем окне выбора открывающейся формы выберите ее и нажмите кнопку «ОК».
- Открылось окно аргументов оператора INDEX. Задача указанной функции — вывести значение, находящееся в выбранном диапазоне, в строке с указанным номером. Общая формула оператора ИНДЕКС:
= ИНДЕКС (массив; номер_строки; [номер_столбца])
«Массив» — это аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию на основе указанного номера строки.
«Номер строки» — это аргумент, который является номером этой же строки. Важно знать, что номер строки нужно указывать не по отношению ко всему документу, а только по отношению к выбранному массиву.
Номер столбца — необязательный аргумент. Мы не будем использовать его для решения нашей конкретной задачи, поэтому нет необходимости описывать его суть отдельно.
Ставим курсор в поле «Массив». Затем перейдите к Листу 2 и, удерживая левую кнопку мыши, выделите все содержимое столбца «Тариф».
- После отображения координат в окне оператора установите курсор в поле «Номер строки». Мы выдадим этот аргумент с помощью оператора ПОИСК. Поэтому нажимаем на треугольник, который находится слева от ряда функций. Откроется список недавно использованных операторов. Если вы найдете среди них название «ПОИСК», вы можете щелкнуть по нему. В противном случае щелкните последний элемент в списке — «Дополнительные функции…».
- Запускается стандартное окно мастера. Передаем в ту же группу «Ссылки и массивы». На этот раз выбираем в списке пункт «ПОИСК». Нажимаем на кнопку «ОК».
- Активизируется окно аргументов оператора ПОИСК. Указанная функция предназначена для отображения номера значения в конкретном массиве на основе его имени. Именно благодаря этой возможности мы будем вычислять номер строки определенного значения для функции ИНДЕКС. Синтаксис MATCH следующий:
= ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_соответствия])
«Подстановочное значение»: аргумент, содержащий имя или адрес сторонней ячейки диапазона, в которой он расположен. Необходимо вычислить позицию этого имени в целевом диапазоне. В нашем случае первым аргументом будут ссылки на ячейки листа 1, где находятся имена сотрудников.
«Массив для поиска» — это аргумент, который является ссылкой на массив, в котором выполняется поиск указанного значения для определения его положения. Для нас эту роль будет играть адрес столбца «Имя» на Листе 2.
«Тип соответствия» — это необязательный аргумент, но, в отличие от предыдущего оператора, нам понадобится этот необязательный аргумент. Указывает, как оператор сопоставит значение поиска с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для несортированных массивов выберите вариант «0». Такой вариант подходит для нашего случая.
Итак, приступим к заполнению полей в окне темы. Ставим курсор в поле «Значение поиска», щелкаем по первой ячейке в столбце «Имя» на листе 1.
- После отображения координат поместите курсор в поле «Viewable Matrix» и щелкните ссылку «Sheet 2», расположенную в нижней части окна Excel над строкой состояния. Удерживая левую кнопку мыши, выделите курсором все ячейки в столбце «Имя».
- После того, как их координаты будут отображены в поле «Display Matrix», перейдите в поле «Match Type» и установите там число «0» с клавиатуры. Затем вернемся снова к полю «Отображаемый массив». Дело в том, что мы скопируем формулу, как и в предыдущем способе. Адреса будут перемещены, но нам нужно исправить координаты отображаемого массива. Он не должен двигаться. Выберите координаты курсором и нажмите функциональную клавишу F4. Как видите, перед координатами появился знак доллара, что означает, что ссылка изменилась с относительной на абсолютную. Затем нажмите кнопку «ОК».
- Результат отображается в первой ячейке столбца «Ставка». Но перед копированием нам нужно заморозить другую область, которая является первым аргументом функции ИНДЕКС. Для этого выберите элемент столбца, содержащий формулу, и перейдите к строке формул. Выберите первый аргумент оператора ИНДЕКС (B2: B7) и нажмите кнопку F4. Как видите, рядом с выбранными координатами появился знак доллара. Нажмите клавишу Enter. В целом формула имела следующий вид:
= ИНДЕКС (Лист2! $ B $ 2: $ B $ 7; ПОИСК (Лист1! A4; Лист2! $ A $ 2: $ A $ 7,0))
- Теперь вы можете сделать копию, используя маркер заполнения. Мы называем это так же, как мы говорили раньше, и растягиваем до конца интервала стола.
- Как видите, хотя порядок строк в двух связанных таблицах не совпадает, тем не менее, все значения извлекаются на основе имен сотрудников. Это было достигнуто за счет использования комбинации операторов INDEX — SEARCH.
Способ 3: выполнение математических операций со связанными данными
Прямая привязка данных хороша еще и тем, что позволяет не только просматривать значения, отображаемые в других диапазонах таблиц в одной из таблиц, но и выполнять с ними различные математические операции (сложение, деление, вычитание, умножение и т.д.на).
Посмотрим, как это делается на практике. Мы следим за тем, чтобы на Листе 3 отображались общие данные о заработной плате компании без разбивки по сотрудникам. Для этого ставки сотрудников будут извлечены из листа 2, суммированы (с использованием функции СУММ) и умножены на коэффициент с использованием формулы.
- Выберите ячейку, в которой будет отображаться общая сумма зарплаты на листе 3. Нажмите кнопку «Вставить функцию».
- Должно запуститься окно мастера. Зайдите в группу «Математика» и выберите там название «СУММ». Затем нажмите кнопку «ОК».
- Перейдите в окно аргументов функции СУММ, предназначенное для вычисления суммы выбранных чисел. Он имеет следующий синтаксис:
= СУММ (число1; число2;…)
Поля в окне соответствуют аргументам указанной функции. Хотя их количество может достигать 255 штук, но для наших целей хватит и одной. Ставим курсор в поле «Число1». Щелкните ссылку «Лист 2» над строкой состояния.
- Перейдя к нужному разделу книги, мы выбираем столбец, который нужно подытожить. Мы делаем это с зажатой левой кнопкой мыши курсором. Как видите, координаты выделенной области сразу отображаются в поле окна аргументов. Затем нажмите кнопку «ОК».
- Затем мы автоматически переходим к Листу 1. Как видите, общая сумма измерения ставки сотрудника уже отображается в соответствующем элементе.
- Но это еще не все. Напомним, зарплата рассчитывается путем умножения суммы взноса на коэффициент. Поэтому снова выбираем ячейку, в которой находится суммированное значение. Затем перейдите к строке формул. Мы добавляем знак умножения (*) к содержащейся в нем формуле, затем щелкаем по элементу, где находится индикатор коэффициента. Чтобы выполнить расчет, нажмите клавишу Enter на клавиатуре. Как видите, программа рассчитывала общую заработную плату фирмы.
- Вернитесь к Листу 2 и измените ставку любого сотрудника.
- После этого возвращаемся на страницу с общей суммой. Как видите, из-за изменений в связанной таблице общий результат зарплаты был автоматически пересчитан.
Способ 4: специальная вставка
Вы также можете связывать массивы таблиц в Excel с помощью специальной вставки.
- Подбираем значения, которые придется «подтянуть» в другой таблице. В нашем случае это диапазон столбца «Тариф» на листе 2. Щелкните выделенный фрагмент правой кнопкой мыши. В открывшемся списке выберите пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl + C. Затем перейдите к листу 1.
- Перейдя в нужную нам область книги, выберите ячейки, в которых нам нужно будет получить значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню на панели инструментов «Параметры вставки» щелкните значок «Вставить ссылку».
Также есть альтернативный вариант. Кстати, он единственный для старых версий Excel. В контекстном меню установите курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выберите одноименный пункт.
- Откроется окно Специальная вставка. Нажмите кнопку «Вставить ссылку» в нижнем левом углу ячейки.
- Какой бы вариант ни был выбран, значения одного массива таблиц будут вставлены в другой. Когда данные в источнике изменяются, они также автоматически изменяются в введенном диапазоне.
Способ 5: связь между таблицами в нескольких книгах
Также вы можете настроить соотношение между областями таблиц в разных книгах. Для этого используется специальный инструмент «Вставить». Действия будут абсолютно аналогичны тем, которые мы рассмотрели в предыдущем методе, за исключением того, что навигация во время введения формул должна происходить не между областями одной книги, а между файлами. Конечно, в этом случае должны быть открыты все связанные книги.
- Выберите диапазон данных, который вы хотите перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и в открывшемся меню выбираем позицию «Копировать».
- Итак, перейдем к книге, где вам нужно будет ввести эти данные. Выберите необходимый интервал. Щелкните правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выберите пункт «Вставить ссылку».
- Далее будут введены значения. Когда данные в исходной книге изменяются, массив таблиц из книги автоматически извлекает их. Более того, совсем не обязательно, чтобы обе книги были открыты для этого. Вам нужно открыть только одну книгу, и она автоматически извлечет данные из закрытого связанного документа, если вы внесли какие-либо изменения ранее.
Но следует отметить, что в этом случае вставка будет выполняться как неизменный массив. Когда вы попытаетесь изменить любую ячейку с введенными данными, появится сообщение о том, что это невозможно.
Изменить такой массив, связанный с другой книгой, можно только путем разрыва ссылки.
Разрыв связи между таблицами
Иногда вы хотите разорвать связь между диапазонами таблиц. Причиной этого может быть либо описанный выше случай, когда необходимо изменить массив, вставленный из другой книги, либо просто нежелание пользователя автоматически обновлять данные в одной таблице из другой.
Способ 1: разрыв связи между книгами
Вы можете разорвать связь между книгами во всех ячейках, фактически выполнив операцию. В этом случае данные в ячейках останутся, но уже будут статическими не обновляемыми значениями, которые никак не зависят от других документов.
- В книге, где значения извлекаются из других файлов, перейдите на вкладку «Данные». Щелкаем по значку «Изменить ссылки», расположенному на ленте в панели инструментов «Подключения». Следует отметить, что если текущая книга не содержит ссылок на другие файлы, эта кнопка неактивна.
- Открывается окно редактирования ссылок. Выберите из списка связанных книг (если их несколько) файл, связь с которым мы хотим разорвать. Нажимаем на кнопку «Разорвать ссылку».
- Откроется информационное окно, в котором есть предупреждение о последствиях дальнейших действий. Если вы уверены, что будете делать, нажмите кнопку «Разорвать связи».
- Впоследствии все ссылки на указанный файл в текущем документе будут заменены статическими значениями.
Способ 2: вставка значений
Но описанный выше метод подходит только в том случае, если вам нужно полностью разорвать все связи между двумя книгами. Что, если вы хотите разделить связанные таблицы, находящиеся в одном файле? Вы можете сделать это, скопировав данные, а затем вставив их в то же место, что и значения. Кстати, таким же образом можно разорвать связь между отдельными диапазонами данных разных книг, не разрывая общую связь между файлами. Посмотрим, как этот метод работает на практике.
- Выберите диапазон, в котором мы хотим удалить связь с другой таблицей. Щелкните по нему правой кнопкой мыши. В открывшемся меню выберите пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl + C.
- Также, не снимая выделения с того же фрагмента, еще раз щелкните по нему правой кнопкой мыши. На этот раз в списке действий щелкните значок «Значения», который находится в группе инструментов «Параметры вставки».
- После этого все ссылки в выбранном диапазоне будут заменены статическими значениями.
Как видите, в Excel есть методы и инструменты для связывания нескольких таблиц вместе. При этом табличные данные можно размещать на других листах и даже в разных книгах. При необходимости это соединение можно легко разорвать.
