Информацию в электронной таблице Microsoft Excel можно объединить с извлеченными из чертежа данными.
С помощью создания связи с электронной таблицей Excel можно включить информацию из всего листа или только его части, т. е. именованного диапазона . В диалоговом окне "Связь с внешними данными" можно выполнить следующие действия.
На странице "Исправление данных" предусмотрен доступ к диалоговому окну "Связь с внешними данными", в котором можно настроить связь извлеченных данных со всей электронной таблицей Excel или с диапазоном строк и столбцов.
Для создания связи с электронной таблицей необходимо, чтобы на компьютере, выполняющем извлечение данных, было установлено приложение Microsoft Excel. Если не установлена программа Microsoft Excel или файл XLS не сохранен локально на компьютере, на котором выполняется извлечение данных, установить связь с данными невозможно.
После изменения связанной электронной таблицы, например после добавления строки или столбца, можно обновить таблицу на чертеже в соответствии с этим изменением с помощью команды СВЯЗЬОБНОВИТЬ. Аналогичным образом, при изменении таблицы в чертеже связанную электронную таблицу можно обновить с помощью этой же команды.
Прим.: Для удаления связи с данными служит команда СВЯЗЬ. Связи с данными из палитры "Внешние ссылки" удалить невозможно.
Связывание данных заключается в выборе электронной таблицы Microsoft Excel с целью объединения всей таблицы или ее части с извлеченными данными чертежа. Дополнительные сведения о связывании данных см. в разделе "Создание связи таблицы с внешними данными".
В процессе задания соответствия данных создается связь между двумя наборами данных: данные в столбце таблицы извлеченных данных чертежа и данные в столбце электронной таблицы Excel.
В следующем примере рассматривается простая спецификация. Из чертежа извлекаются три столбца данных. В этих столбцах содержатся следующие данные: № детали , Редакция и Количество . В спецификацию можно включить два дополнительных столбца данных из электронной таблицы. В этих столбцах содержатся следующие данные Стоимость и Поставщик .
В данном примере электронная таблица содержит столбец номеров деталей, который имеет заголовок Номер по каталогу и соответствует столбцу Номер детали в таблице извлеченных данных. В диалоговом окне "Связь с внешними данными" выберите "Номер детали" в качестве столбца для данных чертежа и "Номер по каталогу" в качестве столбца для внешних данных. Поскольку в столбце данных чертежа и в столбце внешних данных существует по крайней мере одна ячейка соответствующих данных, функция извлечения данных "знает", как связаны между собой оба источника.
Соответствие данных можно проверить путем нажатия кнопки "Проверить соответствие" в диалоговом окне "Связь с внешними данными". При ошибочном соответствии отображается предупреждающее сообщение, указывающее причину. В некоторых случаях правильное создание связи между данными чертежа и данными электронной таблицы невозможно.
Сбой при проверке соответствия данных означает, что невозможно найти допустимое соответствие между данными чертежа и внешними данными (в электронной таблице). Отображаются следующие сообщения об ошибках.
Соответствия между значениями в данных чертежа и внешними данными не обнаружены.
Столбец данных во внешних данных не содержит уникальных значений.
Связанная таблица - это набор данных, которыми можно управлять как единым целым.
Для создания связанной таблицы предназначена кнопка "Форматировать как таблицу" на панели "Стили" ленты "Главная" .
Выберите нужный стиль будущей таблицы и задайте диапазон ячеек, на основе которого будет создана связанная таблица.
После настройки всех параметров мы получим связанную таблицу. Рядом с заголовками столбцов появятся кнопки со стрелочками (по аналогии с фильтрацией), а в окне программы появится контекстный инструмент "Работа с таблицами" , содержащий ленту "Конструктор" .
Каждой связанной таблице дается уникальное имя. По умолчанию - "Таблица_номер". Изменить название таблицы можно на панели "Свойства" .
На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы.
Связанную таблицу можно обратно превратить в обычный диапазон ячеек. Для этого предназначена кнопка "Преобразовать в диапазон" на панели "Сервис" контекстной ленты "Конструктор" .
Как уже упоминалось ранее, рядом с заголовками столбцов расположены кнопки сортировки и фильтрации, при помощи которых можно сортировать данные и применять фильтры.
В связанной таблице можно подсчитывать итоговые значения в столбцах. Для этого надо установить флажок "Строка итогов" на панели "Параметры стилей таблиц" .
При помощи выпадающего списка можно выбрать нужное значение итоговой функции.
В связанную таблицу можно добавлять/удалять строки и столбцы.
Это можно делать несколькими способами.
1. Воспользоваться кнопкой "Изменить размер таблицы" на панели "Свойства" .
2. Установите курсор в ячейке связанной таблицы, рядом с которой надо добавить новый столбец (строку) и на панели "Ячейки" ленты "Главная" воспользуйтесь кнопкой "Вставить" .
Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого ячейки на одном листе или таблице (источнике ) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках ). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.
В зависимости от техники исполнения связывание бывает “прямым“ и через командуСПЕЦИАЛЬНАЯ ВСТАВКА .
Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.
Термин “прямое” связывание обозначает, чтопользователь сам непосредственно при вводе формулыуказывает имя листа иабсолютный адрес ячейки , разделенные восклицательным знаком "!".
Примеры формул: = C5*Лист1! A4
Лист3! В2*100%
Лист1! A1- Лист2! A1
Примечание.
Для указания ссылки на ячейки и листы, находящиеся в незагруженных (неоткрытых) рабочих книгах, в формуле нужно без пробелов задать полный путь местонахождения файла. Путь задается в одинарных кавычках, где указывается название диска, каталога (папки), имя рабочей книги (имя файла) в квадратных скобках и имя листа, на который идет ссылка.
Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.
Чтобы отразить в ячейке С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 квартала.
Иногда необходимо создать неизменяемую связь одной ячейки со значением в другой ячейке, введенном в нее вручную или полученным в результате вычисления формулы.
Примечание
Дальнейшее изменения значений в исходной ячейке будут автоматически отображаться в связанной ячейке.
Рис. 4.4. Вкладка «Главная». Меню кнопки «Вставить». Пункт «Вставить связь»
Связь между таблицами Excel – это формула, которая возвращает данные с ячейки другой рабочей книги. Когда вы открываете книгу, содержащую связи, Excel считывает последнюю информацию с книги-источника (обновление связей)
Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.
Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки , имя листа с восклицательным знаком на конце и ссылку на ячейку.
Есть еще один, более простой, вариант создания связи между таблицами. В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter.
Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:
Делайте легко отслеживаемые связи. Не существует автоматического поиска всех ячеек, содержащих ссылки. Поэтому, используйте определенный формат, чтобы быстро идентифицировать связи с другими таблицами, в противном случае, документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.
Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.
Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения . Щелкните по кнопке Изменить связи.
В появившемся диалоговом окне Изменение связей, Обновить.
Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.
Перейдите по вкладке Данные в группу Подключения . Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.
При выполнении определенных задач в Excel иногда приходится иметь дело с несколькими таблицами, которые к тому же связаны между собой. То есть, данные из одной таблицы подтягиваются в другие и при их изменении пересчитываются значения во всех связанных табличных диапазонах.
Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.
Прежде всего, давайте остановимся на вопросе, какими способами существует возможность создать связь между различными табличными диапазонами.
Самый простой способ связывания данных – это использование формул, в которых имеются ссылки на другие табличные диапазоны. Он называется прямым связыванием. Этот способ интуитивно понятен, так как при нем связывание выполняется практически точно так же, как создание ссылок на данные в одном табличном массиве.
Посмотрим, как на примере можно образовать связь путем прямого связывания. Имеем две таблицы на двух листах. На одной таблице производится расчет заработной платы с помощью формулы путем умножения ставки работников на единый для всех коэффициент.
На втором листе расположен табличный диапазон, в котором находится перечень сотрудников с их окладами. Список сотрудников в обоих случаях представлен в одном порядке.
Нужно сделать так, чтобы данные о ставках из второго листа подтягивались в соответствующие ячейки первого.
Итак, ставим курсор в нижнюю правую область элемента с формулой. После этого курсор должен преобразоваться в маркер заполнения в виде черного крестика. Выполняем зажим левой кнопки мыши и тянем курсор до самого низа столбца.
Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.
ИНДЕКС(массив;номер_строки;)
«Массив» - аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.
«Номер строки» - аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.
«Номер столбца» - аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.
Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)
«Искомое значение» - аргумент, содержащий наименование или адрес ячейки стороннего диапазона, в которой оно находится. Именно позицию данного наименования в целевом диапазоне и следует вычислить. В нашем случае в роли первого аргумента будут выступать ссылки на ячейки на Листе 1, в которых расположены имена сотрудников.
«Просматриваемый массив» - аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.
«Тип сопоставления» - аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.
Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.
ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))
Читайте также:
Функция ИНДЕКС в ЭкселеФункция ПОИСКПОЗ в Экселе
Прямое связывание данных хорошо ещё тем, что позволяет не только выводить в одну из таблиц значения, которые отображаются в других табличных диапазонах, но и производить с ними различные математические операции (сложение, деление, вычитание, умножение и т.д.).
Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.
СУММ(число1;число2;…)
Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.
Связать табличные массивы в Excel можно также при помощи специальной вставки.
Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.
Урок: Специальная вставка в Экселе
Кроме того, можно организовать связь между табличными областями в разных книгах. При этом используется инструмент специальной вставки. Действия будут абсолютно аналогичными тем, которые мы рассматривали в предыдущем способе, за исключением того, что производить навигацию во время внесений формул придется не между областями одной книги, а между файлами. Естественно, что все связанные книги при этом должны быть открыты.
Но нужно отметить, что в этом случае вставка будет произведена в виде неизменяемого массива. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.
Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.
Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.
Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.
Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.
Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Иногда данные, которые нужно найти, могут находится на разных таблицах. Часто приходится делать выборку данных по нескольким таблицам Excel. Пользователь должен иметь возможность определять из какой таблицы делать выборку по критериям условий для того или иного типа данных. Для этого можно создать специальную формулу.
Ниже на рисунке представлена таблица для вычисления налоговой суммы. Пользователь имеет возможность определять семейное положение сотрудника (женат или Неженат). Если пользователь выберет условие «Неженат», выборка должна выполнятся по таблице «Неженатые сотрудники». Если будет выбран критерий «Женат» выборка будет произведена по таблице «Женатые сотрудники». Формула для расчета налогов при условии женат или Неженат сотрудник фирмы:
Чтобы создать переключатель между таблицами можно использовать имена диапазонов ячеек и функцию ДВССЫЛ. После чего нужно составить формулу. Необходимо сначала создать два именных диапазона:
Чтобы присвоить отдельные имена для каждого из диапазонов этих двух таблиц сделайте следующее:
Для точности и удобства ввода входных значений в ячейке… используется выпадающий список создан инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных:»-«Список».
Выпадающий список состоит только из двух значений: «Женат» «Неженат». Точно такие же как названия имен диапазонов ячеек, созданных ранее. Значение ячейки E12 будет использовано для переключения между таблицами при поиске по условию. Поэтому значения и имена диапазонов должны быть идентичны.
В основе данной формулы лежит функция ВПР. Ее второй аргумент где указывается исходная таблица содержит функцию ДВССЫЛ. Данная функция имеет первый аргумент «Ссылка на ячейку», который преобразует входящий текст в ссылку на ячейку или диапазон. На самом первом рисунке ячейка E12 содержит значение «Неженат». Функция ДВССЫЛ пытается преобразовать этот текст в ссылку на ячейку или в имя диапазона. Если текст не преобразовывается в ссылку на ячейку (как в данном примере), тогда функция ДВССЫЛ проверяет нет ли в данной рабочей книге имен диапазонов ячеек с таким же названием. Если небыли бы созданы такие имена диапазонов, тогда функция вернула бы ошибку с кодом #ССЫЛКА!
В синтаксисе функции ДВССЫЛ имеется второй необязательный для заполнения аргумент – называется «A1». Значение ИСТИНА в данном аргументе значит, что ссылка на ячейку записана в формате A1, а значение ЛОЖЬ – формате R1C1. В случае названых имен диапазонов ячеек функция ДВССЫЛ вернет правильный результат в независимости от того, что указано во втором опциональном ее аргументе «A1»: ИСТИНА или ЛОЖЬ.
Функция ДВССЫЛ может также возвращать внешние ссылки на другие листы и даже другие рабочие книги Excel. Но при условии, что рабочая книга, на которую ссылается функция будет открыта. Иначе будет возвращена ошибка с кодом #ССЫЛКА!
