Как связать графы в экселе. Рабочая книга Excel

Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать , чтобы проанализировать тенденции продаж по годам и месяцам.

Все таблицы в книге указываются в списках полей сводной таблицы и Power View.

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

Если команда Отношения недоступна, значит книга содержит только одну таблицу.

В окне Управление связями нажмите кнопку Создать .

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

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

В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица .

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

Нажмите кнопку ОК .

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

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

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

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

Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.

В главной таблице нажмите Сортировка по столбцу .

В поле "Сортировать" выберите MonthInCalendar .

В поле "По" выберите MonthOfYear .

Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.

"Могут потребоваться связи между таблицами"

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

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

Шаг 1. Определите, какие таблицы указать в связи

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

Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение "Могут потребоваться связи между таблицами". Наиболее вероятной причиной является то, что вы столкнулись со связью "многие ко многим". Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей "один ко многим" между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы , чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.

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

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

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

Кроме совпадающих значений есть несколько дополнительных требований для создания связей.

Если нам нужно, чтобы автоматически данные из одной таблице Excel переносились в другую , нужно связать эти таблицы. Есть несколько способов, как связать данные двух таблиц в Excel. Один способ - с помощью формулы, смотрите в статье «Как связать таблицы в Excel ».
Здесь рассмотрим, как связать две таблицы в разных книгах Excel , с помощью установки связи между таблицами. Когда все настроим, то достаточно открыть одну таблицу, чтобы данные перенеслись в эту таблицу из другой таблицы. Не нужно открывать все книги, чтобы связь работала. Она будет работать с одной открытой книгой.
Итак, у нас есть две книги Excel – Книга1 и Книга2. В Книге1 есть такая таблица, уже заполненная. Таблица1. Внимание!
Если в таблице есть объединенные ячейки, то в них связь не будет работать или будет сбиваться.
В Книге2 делаем такую же таблицу, но не заполняем ее.

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

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

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

В зависимости от техники исполнения связывание бывает “прямым“ и через командуСПЕЦИАЛЬНАЯ ВСТАВКА .

1 Способ – "Прямое связывание ячеек"

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

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

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

Лист3! В2*100%

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

Примечание.

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

2 Способ – Связывание ячеек через команду "Специальная вставка"

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.

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

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

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

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

! При связывании ячеек определите, какие ячейки являются исходными.

! Для одной связываемой таблицы исходными могут быть ячейки из разных таблиц на различных рабочих листах или на текущем листе.

Задания для самостоятельной работы.

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

Например:

на листе Цена в таблицеРасходы на закупку ячейки А4:А8 связаны с ячейками таблицыКоличество закупленной продукции на листеЗакупка ;

ячейки В4:В8 являются исходными, т.к. содержат первоначальные сведения о ценах закупленного товара;

ячейки С4:С8 связаны с ячейками Н4:Н8 на листе Закупка ;

ячейки D4:D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейкеD4 имеет вид =В4*С4, что означает умножение цены товара на его количество);

ячейка D9 является суммой ячеекD4:D8;

во второй таблице Расчет цен на этом же листе ячейки А14:А18 связаны аналогично п.1;

ячейки В14:В18 являются связанными с исходными ячейками текущего листа В4:В8;

ячейки С4:С8 являются исходными, т.к. содержат первоначальные сведения о наценке салона на закупленный товар;

ячейки D14:D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейкеD14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене);

После выполнения всех операций с этими таблицами произведите проверку их "работоспособности".

Измените наименование товара –Диван в ячейке А4 на листеЗакупка на другое – напримерСофа.

Измените количество закупленного товараСофа в июне (в ячейкеG4 на листеЗакупка введите число 11).

Измените цену закупки Софы в ячейке В4 на листеЦена на другую – 2500,00 р.

Измените процент наценки Софы в ячейке С14 на листеЦена с 50% на 32%.

Проверьте , произошли изменения в связанных таблицах или нет?

Обратите внимание, на какие ячейки различных таблиц повлияли внесенные изменения.

Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ... копирование на соседние ячейки становится проблематичным из-за абсолютной адресации ячеек.

Задание 1. Выполните связывание ячеек остальных таблиц рабочей книги, используя различные способы.

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

Задание 2. Создайте на листах Выручка и Доход таблицы по расчету за 2 квартал. Свяжите эти таблицы с соответствующими исходными данными.

Указание.

Задание 3. Постройте круговую диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции.

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

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

Информацию в электронной таблице Microsoft Excel можно объединить с извлеченными из чертежа данными.

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

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

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

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

После изменения связанной электронной таблицы, например после добавления строки или столбца, можно обновить таблицу на чертеже в соответствии с этим изменением с помощью команды СВЯЗЬОБНОВИТЬ. Аналогичным образом, при изменении таблицы в чертеже связанную электронную таблицу можно обновить с помощью этой же команды.

Прим.: Для удаления связи с данными служит команда СВЯЗЬ. Связи с данными из палитры "Внешние ссылки" удалить невозможно.

О связывании и установлении соответствия данных

Связывание данных заключается в выборе электронной таблицы Microsoft Excel с целью объединения всей таблицы или ее части с извлеченными данными чертежа. Дополнительные сведения о связывании данных см. в разделе "Создание связи таблицы с внешними данными".

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

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

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

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

Проверка сопоставленных данных

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

Соответствия между значениями в данных чертежа и внешними данными не обнаружены.

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

Столбец данных во внешних данных не содержит уникальных значений.

• Столбец внешних данных содержит одну или несколько ячеек с одинаковыми данными. В этой ситуации невозможно однозначно задать соответствие между данными чертежа и внешними данными.

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

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

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

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

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

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

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

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

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. С задачей мы справились, все ссылки работают правильно. Единственное, хотелось бы добавить пару слов по оформлению таблицы. Сейчас в колонке «