Как сделать связанные таблицы в excel. Как Связать Ваши Данные из Разных Книг в Excel

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

Изменение содержимого ячейки на одном листе или таблице (источнике ) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках ). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.

В зависимости от техники исполнения связывание бывает «прямым » и через команду «Специальная вставка» .

Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа 1) и оба листа загружены данными, то такое связывание листов называется "прямым".

Термин "прямое" связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!".

Примеры формул: = C5*Лист1! A4

Лист3! В2*100%

Лист1! A1-Лист2! A1

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

Например:

= "D:\Документы студентов[Расчет дохода.xls]Закупка продукции"!H19

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

Чтобы отразить в ячейке С4 на листе Цена продукции значение ячейки Н4 на исходном листе Закупка продукции, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка/Копировать. На листе Цена продукции поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка/Специальная вставка/Вставить связь (см. рис. 6). Тогда на листе Цена реализации появится указание на ячейку исходного листа Закупка продукции, например: = Закупка продукции!$Н$4

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

Задание 7. Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена реализации с соответствующими ячейками на листе Закупка продукции, используя при этом различные способы связывания ячеек (рис. 28).

Рис. 28. Связывание ячеек различных рабочих листов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345 ” и “12345-новый_суффикс “. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания ” в одной таблице может превратиться в “ЗАО «Некоторая Компания» ” в другой таблице, а “Новая Компания (бывшая Некоторая Компания) ” и “Старая Компания ” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP) , ПОИСКПОЗ (MATCH) , ГПР (HLOOKUP) и так далее.

Выберите подходящий пример, чтобы сразу перейти к нужному решению:

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

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

Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper :

Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

Другие формулы

Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:
ПРАВСИМВ(A2;6)
=RIGHT(A2,6)
Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:
ПСТР(A2;8;4)
=MID(A2,8,4)
Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:
ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)
=LEFT(A2,FIND("-",A2)-1)

Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – , мы сделаем всё возможное, чтобы помочь Вам.

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY , где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

В ячейке C2 запишем такую формулу:

СЦЕПИТЬ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Здесь A2 – это адрес ячейки, содержащей код группы; символ “– ” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.

Хорошая новость: Это придётся сделать только один раз, и получившуюся вспомогательную таблицу можно будет сохранить для дальнейшего использования. Далее Вы сможете объединять эти таблицы автоматически и сэкономить таким образом массу времени

1. Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter . Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description ). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз:-).

В результате мы имеем вот такую таблицу:

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU .

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter Our.SKU , а для обновлённых данных – столбец Supp.SKU .

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU , соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

3. Переносим данные из таблицы поиска в главную таблицу

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

При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1 , используя для поиска соответствий столбец Supp.SKU .

Вот пример обновлённых данных в столбце Wholesale Price :

Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции - вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:
ИНДЕКС(массив;номер_строки;)

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

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

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

Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».
После того, как координаты отобразились в окошке оператора, ставим курсор в поле «Номер строки». Данный аргумент мы будем выводить с помощью оператора ПОИСКПОЗ. Поэтому кликаем по треугольнику, который расположен слева от строки функций. Открывается перечень недавно использованных операторов. Если вы среди них найдете наименование «ПОИСКПОЗ», то можете кликать по нему. В обратном случае кликайте по самому последнему пункту перечня – «Другие функции…».
Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».
Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

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

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

Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.
После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:
ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))
Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКС-ПОИСКПОЗ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первое и самое главное: связывание таблиц по общим признакам — не типичная функция для MS Excel. Для таких задач есть специальные программы — так называемые базы данных. Хотя правильнее их называть «системы управления базами данных», или СУБД. К ним относятся, например, MS Access, MS SQL Server, Oracle и многие другие. В этих системах объединение таблиц по общим ключевым полям — едва ли не самая распространенная операция. И для ее решения там есть мощные и удобные инструменты. Они позволяют организовать самые разнообразные типы связей между таблицами, контролировать целостность данных, выполнять каскадное удаление записей и т. п. Однако для полноценного использования СУБД нужна определенная подготовка, знание языка запросов и т. п. Поэтому в практике бухгалтера СУБД в чистом виде применяют редко, и всю обработку данных делают обычно в программе Excel. Что же касается MS Excel, то здесь возможности связывания таблиц ограничены. В основном эта связь заключается в написании формул, которые ссылаются на разные листы или рабочие книги. Вы можете создать печатный отчет или сводную таблицу, которая оперирует несколькими базами данных. Но специальных инструментов для полноценной организации связей между наборами данных в Excel нет, да и быть не должно.

Однако не все так плохо… Дело в том, что в реальной работе все варианты связывания таблиц бухгалтеру обычно не нужны. Да и без каскадного удаления записей он тоже как-то обойдется. А вот связать одну большую таблицу со справочником — задача вполне актуальная и практически применимая. И, что самое главное(!), такая функция вполне реализуема в MS Excel. Причем единственное, что для этого потребуется, — это пара несложных трюков и система гиперссылок.

Теперь посмотрим, как это выглядит практически. Начнем, конечно же, с исходных данных.

Чем мы располагаем

В качестве примера я воспользуюсь двумя таблицами, форма которых показана на рис. 1 и 2. Первая таблица (она расположена на листе « Заказы ») состоит из четырех полей: « Заказ », « Дата », « Клиент », « Сумма » и пока пустое поле « Коммент. ». Это упрощенный вариант базы данных о заказах. В поле « Коммент. » мы в конечном итоге и поставим ссылку на элемент справочника. Понятно, что в базе « Заказы » один контрагент может фигурировать много раз, — это зависит от количества заказов, сделанных этим клиентом.

Вторая таблица называется « Клиенты ». В ней я оставил всего пять колонок: « Название » (наименование предприятия), далее идут фамилия директора, юридический адрес, телефон и e-mail.

Важно! В справочнике « Клиенты » одна строка содержит данные об одном предприятии. Повторы здесь недопустимы, каждый клиент присутствует в базе только один раз.

Теперь вернемся непосредственно к самой задаче.

Что нужно сделать

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

Кстати, в терминологии баз данных такая связь называется « один ко многим » (если смотреть со стороны таблицы « Клиенты »). То есть один элемент справочника о клиентах ссылается на несколько записей в базе заказов. В MS Excel для создания такой связи специальных инструментов нет. Мы должны построить их сами. Для этого, исходя из алгоритма, нам понадобятся: встроенная функция для создания гиперссылки (такая функция называется « ГИПЕРССЫЛКА() ») и функция для поиска данных в таблице. Я предлагаю для поиска использовать функцию « ПОИСКПОЗ() ». Но это не все. Гиперссылке нужно указать точный адрес для перехода: имя файла, название рабочего листа и ячейку, которую нужно сделать активной. Для получения имени файла, листа и ячейки мы воспользуемся функцией « ЯЧЕЙКА() ». Кроме того, для формирования строки с адресом перехода нам придется обратиться к встроенным функциям работы с текстом: « ПСТР() » и « ПОИСК() ». Вот собственно и все основные инструменты. Можно переходить к практической реализации.

Связываем две таблицы

Вначале выполним несколько предварительных действий. Начнем с функции « ГИПЕРССЫЛКА() », которая будет ключевой при организации связей между таблицами. У нее два параметра. Первый — это адрес, т. е. имя файла и рабочего листа, на который указывает ссылка. Второй параметр — адрес конкретной ячейки на этом листе, куда будет указывать гиперссылка. У нас гиперссылок будет много. И каждый раз для их создания придется указывать в параметрах функции имя файла и название листа. Это долго и неудобно. Поэтому лучше один раз создать переменную с названием листа и файла, а затем указывать ее переменную в функции « ГИПЕРССЫЛКА() » по мере необходимости. Применительно к MS Excel в качестве такой переменной удобно использовать именованный диапазон ячеек . С этого мы и начнем.

Формируем переменную с названием листа

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

1. Вызываем Excel, загружаем документ и переходим в меню « Формулы ». Лента примет вид, как на рис. 3.

2. В группе « Определенные имена » щелкаем на иконке « Диспетчер имен ». Откроется окно, изображенное на рис. 4.

3. В этом окне нажимаем кнопку « Создать ». Откроется окно « Создание имени », изображенное на рис. 5.

4. В этом окне в поле « Имя: » вводим текст « Мой_Лист ». В поле « Диапазон: » печатаем формулу « =ПСТР(ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1);ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1));256)& " ! " » (рис. 3).

5. В окне « Создание имени » нажимаем « ОК ».

6. В окне « Диспетчер имен » нажимаем « ОК ».

Теперь разберемся, что же мы сделали на самом деле? В рабочей книге появился новый именованный диапазон « Мой_Лист ».

Важно! В нашем случае название именованного диапазона должно быть БЕЗ пробелов. В противном случае формулы адресации будут работать неправильно.

Как и любой именованный диапазон, он указывает на ячейку или группу ячеек рабочей книги Excel. Но в нашем случае адрес этой группы не постоянный, а динамический . Его формирует формула, которую мы ввели в поле « Диапазон: ». Вкратце о работе этой формулы. Начнем изнутри — с функции « ЯЧЕЙКА() ».

Выражение « ЯЧЕЙКА(" имяфайла " ;Клиенты! $A$1) » обращается к ячейке « A1 » на листе « Клиенты » и возвращает для этой ячейки ее полный адрес , т. е. путь к файлу, имя файла и название листа, где эта ячейка расположена. Например, документ с таблицами у меня называется « ДинСсылкиExcel_.xls ». Хранится он на диске « D: » в папке « !Фактор ». Тогда результат работы формулы будет такой: « D:!Фактор[ДинСсылкиExcel_.xls]Клиенты ».

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

Вырезать часть строки можно функцией « ПСТР() ». Но ей нужно указать: исходный текст (у нас это полный путь к ячейке), начальную позицию и количество знаков , которое нужно вырезать из исходной строки.

Начальную позицию определить просто. Для этого с помощью функции « ПОИСК() » мы находим первое вхождение квадратной открывающей скобки (« [ ») в текст, где хранится путь к ячейке. Фрагмент формулы, который выполняет эту операцию, выглядит так: « ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ; Клиенты!$A$1)) ». Для строки « D:!Фактор [ДинСсылкиExcel_.xls]Клиенты » эта формула вернет « 12 ». Количество знаков, которые нужно вырезать из исходного текста, я выбрал максимальное — « 256 ».

Остается подставить полученные значения в функцию « ПСТР() ». Первым ее параметром идет текст с адресом к ячейке. Второй параметр — номер начальной позиции для вырезания части строки. Последний параметр — максимально допустимое количество символов в строке. Формула для этой операции будет такой: « =ПСТР(ЯЧЕЙКА (" имяфайла " ;Клиенты!$A$1);ПОИСК

(" [ " ;ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1));256) ». В нашем примере такая формула вернет результат в виде « [ДинСсылкиExcel_.xls]Клиенты ». По сути — это ссылка на лист « Клиенты » рабочей книги « ДинСсылкиExcel_.xls ». Дальше может идти адрес ячейки. Но пока не хватает одного элемента — между именем листа и адресом ячейки должен стоять восклицательный знак (« ! »). Этот символ мы можем присоединить к формуле при помощи операции « & », и в окончательной редакции выражение будет выглядеть так: « =ПСТР(ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1);ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ; Клиенты!$A$1)); 256)& " ! " ». А результат ее работы будет такой: « ».

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

Кстати, работу формулы внутри именованного диапазона легко проверить. Введите в любую свободную ячейку листа выражение « =Мой_Лист » и нажмите клавишу « Enter ». В ячейке должен появиться результат: « [ДинСсылкиExcel_.xls]Клиенты! ».

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

1. Переходим на лист « Заказы », становимся на ячейку « E2 ».

2. Вводим формулу « =ГИПЕРССЫЛКА(Мой_ Лист&АДРЕС(ПОИСКПОЗ(C2;Клиенты!$A:$A;0); 1); " > " ) ».

3. Копируем эту формулу на всю высоту таблицы. Результат нашей работы показан на рис. 6.

Проверяем, что у нас получилось. В таблице « Заказы » щелкаем левой кнопкой, например, на ячейке « E6 ». В этой строке расположены сведения о заказе с номером « 5 » от фирмы « ЧП " Коло " ». После щелчка Excel переключится на лист « Клиенты », а указатель активной ячейки станет на адрес « A6 ». Именно в этой позиции справочника записана информация о « ЧП " Коло " » (рис. 7).

Рассмотрим вкратце алгоритм работы формулы. Выражение « =ПОИСКПОЗ(C2;Клиенты! $A:$A;0) » находит ячейку в колонке « А » на листе « Клиенты », в которой встречается название контрагента из ячейки « С2 » листа « Заказы ». Последний параметр функции « ПОИСКПОЗ() » равен « 0 ». Это означает, что она будет искать значение по принципу точного совпадения. Вернемся к нашему примеру. Предположим, что мы работаем со строкой « 6 » таблицы « Заказы » (рис. 6). В этой строке находится заказ с номером « 5 » от фирмы « ЧП " Коло " ». После копирования гиперссылки из ячейки « E2 » вниз по колонке « E » в ячейке « E6 » формула с функцией поиска получится такой: « ПОИСКПОЗ(C6;Клиенты!$A:$A;0) ». А результат работы этого выражения будет равен « 6 ». Это означает, что в таблице « Клиенты » описание фирмы « ЧП «Коло» » расположено в шестой строке рабочего листа.

Идем дальше. Результат работы этого выражения мы подставим в качестве первого параметра функции адрес. Вторым параметром укажем « 1 ». Тогда выражение « АДРЕС(ПОИСКПОЗ (C2;Клиенты!$A:$A;0);1) » (вторая строка таблицы « Заказы ») по сути означает « АДРЕС(2;1) ». Эта функция вернет в виде текста адрес ячейки, которая находится во второй строке и первой колонке рабочего листа. Для заказа « 1 » (вторая строка) это будет адрес « $A$2 ». Для заказа в шестой строке таблицы (фирма « ЧП " Коло " ») выражение вернет строку « $A$6 » и т. д.

С адресацией ячейки понятно. Теперь нужно использовать полученный результат в функции « ГИПЕРССЫЛКА() ».

Здесь ситуация такова. Напомню, что у функции « ГИПЕРССЫЛКА() » два параметра. Первый — это адрес, куда указывает гиперссылка. Он включает название файла, листа и адрес ячейки для перехода. Имена файла и листа у нас уже есть, эта информация хранится в переменной « Мой_Лист ». Адрес ячейки для ссылки на контрагента по конкретному заказу мы получили. Остается объединить эти две части операцией « & ». И тогда окончательное выражение для адреса перехода получится таким: « Мой_Лист&АДРЕС(ПОИСКПОЗ(C2; Клиенты!$A:$A;0);1) ».

Смотрим, что у нас получилось. Для ячейки « С2 » такая формула вернет результат « [ДинСсылкиExcel_.xls]Клиенты!$A$2 » — т. е. ссылку на ячейку « A2 » в справочнике « Клиенты ». Все верно: для заказа с номером « 1 » ссылка указывает на контрагента « ТОВ " Смит " ». Если обратиться к ячейке « С6 » (заказ с номером « 5 » от « ЧП " Коло " »), то в этой строке таблицы « Заказы » выражение для адреса перехода будет выглядеть так: « Мой_Лист&АДРЕС(ПОИСКПОЗ(C6;Клиенты!$A:$A;0);1) ». А результат формулы будет такой: « [ДинСсылкиExcel_.xls]Клиенты!$A$6 ».

Переходим ко второму параметру функции « ГИПЕРССЫЛКА() ». Здесь должен находиться текст, который Excel покажет на месте гиперссылки. Иными словами, это название самой гиперссылки. Я выбрал в качестве такого названия символ « > » (знак « больше »). И тогда окончательная формула для создания гиперссылки будет такой: « =ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(C2; Клиенты!$A:$A; 0);1); " > " ) ».

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