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