Главная » Excel » Вы здесь

Работа со связанными таблицами в Microsoft Excel

Связанные таблицы в Microsoft Excel

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

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

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

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

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

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

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

Таблица заработной платы в Microsoft Excel

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

Таблица со ставками сотрудников в Microsoft Excel

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

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

  3. Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».
  4. Связывание с ячейкой второй таблицы в Microsoft Excel

  5. Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:

    =Лист2!B2

  6. Две ячейки двух таблиц связаны в Microsoft Excel

  7. Теперь нужно перенести данные о ставках всех остальных работников предприятия. Конечно, это можно сделать тем же путем, которым мы выполнили поставленную задачу для первого работника, но учитывая, что оба списка сотрудников расположены в одинаковом порядке, задачу можно существенно упростить и ускорить её решение. Это можно сделать, просто скопировав формулу на диапазон ниже. Благодаря тому, что ссылки в Excel по умолчанию являются относительными, при их копировании происходит сдвиг значений, что нам и нужно. Саму процедуру копирования можно произвести с помощью маркера заполнения.

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

  8. Маркер заполнения в Microsoft Excel

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

Все данные столбца второй таблицы перенесены в первую в Microsoft Excel

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

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

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

  3. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
  4. Переход в окно аргуметов функции ИНДЕКС в Microsoft Excel

  5. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
  6. Выбор формы функции ИНДЕКС в Microsoft Excel

  7. Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

    =ИНДЕКС(массив;номер_строки;[номер_столбца])

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

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

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

    Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».

  8. Аргумент Массив в окне аргументов функции ИНДЕКС в Microsoft Excel

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

  11. Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».
  12. Переход в окно аргуметов функции ПОИСКПОЗ в Microsoft Excel

  13. Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:

    =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

    «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

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

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

  14. Аргумент Искомое значение в окне аргументов функции ПОИСКПОЗ в Microsoft Excel

  15. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
  16. Аргумент Просматриваемый массив в окне аргументов функции ПОИСКПОЗ в Microsoft Excel

  17. После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
  18. Окно аргуметов функции ПОИСКПОЗ в Microsoft Excel

  19. Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:

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

  20. Преобразование ссылки в абсолютную в Microsoft Excel

  21. Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
  22. Маркер заполнения в программе Microsoft Excel

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

Значения связаны благодаря комбинации функций ИНДЕКС-ПОИСКПОЗ в Microsoft Excel

Читайте также:
Функция ИНДЕКС в Экселе
Функция ПОИСКПОЗ в Экселе

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

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

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

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

  3. Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».
  4. Переход в окно аргуметов функции СУММ в Microsoft Excel

  5. Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:

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

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

  6. Окно аргметов функции СУММ в Microsoft Excel

  7. После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».
  8. Суммирование данных с помощью функции СУММ в Microsoft Excel

  9. После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.
  10. Общая сумма ставок работников в Microsoft Excel

  11. Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент. Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Дописываем к имеющейся в ней формуле знак умножения (*), а затем щелкаем по элементу, в котором располагается показатель коэффициента. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре. Как видим, программа рассчитала общую заработную плату по предприятию.
  12. Общая зарплата по предприятию в Microsoft Excel

  13. Возвращаемся на Лист 2 и изменяем размер ставки любого работника.
  14. Изменение ставки работника в Microsoft Excel

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

Сумма заработной платы по предприятию пересчитана в Microsoft Excel

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

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

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

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

    Вставка связи через контекстное меню в Microsoft Excel

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

  4. Переход в специальную вставку в Microsoft Excel

  5. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
  6. Окно специальной вставки в Microsoft Excel

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

Значения вставлены с помощью специальной вставки в Microsoft Excel

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

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

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

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

  3. Затем перемещаемся к той книге, в которую эти данные нужно будет вставить. Выделяем нужный диапазон. Кликаем правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем пункт «Вставить связь».
  4. Вставка связи из другой книги в Microsoft Excel

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

Связь из другой книги вставлена в Microsoft Excel

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

Информационное сообщение в Microsoft Excel

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

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

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

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

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

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

  3. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».
  4. Окно изменения связей в Microsoft Excel

  5. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».
  6. Информационное предупреждение о разрыве связи в Microsoft Excel

  7. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Ссылки заменены на статические значения в Microsoft Excel

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

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

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

  3. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
  4. Вставка как значения в Microsoft Excel

  5. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Значения вставлены в Microsoft Excel

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

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



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

Опрос: помогла ли вам эта статья?

Да Нет

Еще статьи по этой теме:

Поделиться статьей в социальных сетях

Оставьте свое мнение о статье