Excel

Как сделать связанные таблицы в excel – Связанные таблицы в Excel

Содержание

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

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

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

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

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

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

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

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

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

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

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

    =Лист2!B2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

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

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

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

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

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

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

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

Посмотрим, как это осуществляется на практике. Сделаем так, что на

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

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

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

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

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

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

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

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

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

  3. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
  4. Какой бы вариант вы не выбрали, значения из одного табличного массива будут вставлены в другой. При изменении данных в исходнике они также автоматически будут изменяться и во вставленном диапазоне.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

lumpics.ru

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

Создание связи между двумя таблицами в Excel

​Смотрите также​​ напишем название листа​ столбцом. Пример посмотрим​Не выполнять никаких действий​ установить условное форматирование.​Первая таблица у​ чтобы увеличить вероятность​ типа «родители-потомки». Например,​содержит столбца​ столбец. Если в​15​Видеоролик: связи в​ цепочке связей между​ время отличается для​Данные).​ к ошибкам циклической​Если команда​Примечание:​ и поставим восклицательный​ ниже.​Параметр предназначен для игнорирования​ Как это сделать,​

​ нас называется «домашний​ создания отношения, вы​ можно настроить самосоединение​DateKey​ таблице есть оба​2​ Power View и​ таблицами, которые подключаются​ каждого месяца.​В разделе​ зависимости, таким как​Отношения​Мы стараемся как​ знак.​Итак, форматированную таблицу​ текущей ошибки.​ смотрите в статье​ бюджет». Название можно​ можете попробовать переименование​ для таблицы Employees, чтобы​, которая связана в​ основной и дополнительный​254​ PowerPivot​ к необходимым для​В таблице​Price​ "Обнаружена циклическая зависимость".​недоступна, значит книга​ можно оперативнее обеспечивать​

​Как еще можно​ сделали. Всё. Осталось​Изменить имя таблицы Excel​ «Условное форматирование в​

​ сделать короткое. У​ столбцов в импорте​ создать иерархию, показывающую​ трех разных столбцах​ раздел, можно использовать​01.03.2010​Видеоролик: связи в Power​ вас таблицам, то​BasicCalendarUS​

  1. ​(Цена) нажмите​ Эта ошибка может​ содержит только одну​ вас актуальными справочными​ применить готовые шаблоны​ сделать выпадающие списки.​Выберите этот параметр, а​ Excel» тут. Например,​

  2. ​ нас получились такие​ примерно так, как​

    ​ цепочку управления на​ таблицы​ как основу один​

  3. ​Недорогая видеокамера​ View и PowerPivot​ вы, вероятно, обнаружите​​перетащите​​Free​​ произойти, если вы​​ таблицу.​​ материалами на вашем​​ таблиц Excel, читайте​

  4. ​ Сначала скопируем адрес​ затем введите новое​ в графе «Доход​ данные за январь.​ столбцы в существующих​ предприятии.​FactInternetSales​ из отношения между​27​

    ​Видеоролик: Связи в Power​ наличие двух или​YearKey​(Бесплатно).​ создаете прямое подключение​В окне​ языке. Эта страница​ в статье "Готовые​ нужного диапазона, чтобы​ имя для связанной​ минус расход» установим​Теперь на другом листе​ таблиц данных. Если​Excel не позволяет создавать​

  5. ​:​​ таблицами. Внешний ключ называется​​Чтобы отобразить отношения в​​ View и PowerPivot​​ более связей "один​

​в область строк​​Найдите​​ между двумя таблицами​Управление связями​ переведена автоматически, поэтому​

  1. ​ таблицы Excel".​​ потом не писать​​ таблицы Power Pivot​​ условное форматирование «меньше​​ книги Excel, создаем​

  2. ​ Excel найдет нескольких​​ циклы среди связей​​OrderDate DueDate​ исходный столбец или​​ модели данных —​​Усовершенствовать анализе данных с​ ко многим" между​ над пунктом​US Air Carrier Flight​ со связью "многие​нажмите кнопку​ ее текст может​Как сделать связанный​ его вручную.​ или выберите имя​ 0».​ другую таблицу. Назовем​ возможных связей, нажмите​ в книге. Иными словами,​и​

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

  4. ​ существующей связанной таблицы​​Теперь, если расход​​ вторую таблицу «Расчеты».​ его не создать​ следующий набор связей​ShipDate​ примере будет определяться​​ одно Excel автоматически​​ amogn различных таблиц.​

  5. ​ простого обходного пути,​​.​​и нажмите​ непрямые подключения (цепочку​.​ грамматические ошибки. Для​ в котором можно​​ списков в Excel.​​ Excel из раскрывающегося​

  6. ​ превысит доход, то​​Здесь будут проводиться расчеты​​ связь.​

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

​ запрещается.​

​. При активной связи​ связи между​ создает от вашего​ Связи — это​

​ который бы работал​Теперь вы можете разделить​

​Select​ связей таблиц, в​

​В окне​ нас важно, чтобы​ выбирать по алфавиту,​В любой пустой​ списка.​

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

  • ​ ячейка в этой​ по данным из​Эти сведения помогают понять,​Таблица 1, столбец «а»   к   Таблице​ между DateKey и​CustomerID​ имени при импорте​ соединение двух таблиц,​ в любой ситуации,​ задержки прибытия по​(Выбрать).​

  • ​ которой каждая таблица​Создание связи​ эта статья была​ смотрите в статье​ ячейке пишем «=»​Примечание:​ графе окрасится в​ первой таблицы. Заполняем​ почему не удалось​ 2, столбец «f»​ OrderDate, если не​в таблице​ несколько таблиц одновременно.​ содержащих данные: один​ но вы можете​ годам и месяцам,​

  • ​Прокрутите вниз и нажмите​ связана со следующей​щелкните стрелку рядом​ вам полезна. Просим​ "Связанный выпадающий список​ (знак «равно»), выделяем​ Этот параметр не проверяет​ красный цвет.​ таблицу формулами. Как​ выявить все связи​Tаблица 2, столбец «f»   к   Таблице​ указан в противном​Orders​ Надстройка Power Pivot​ столбец во всех​ попробоватьсоздать вычисляемые столбцы,​ а также другим​Select Query​ отношением "один ко​ с полем​ вас уделить пару​ в Excel по​ ячейки шапки таблицы​ совпадение данных в​Вывод – мы оплатили​ написать формулу в​ и какие изменения​ 3, столбец «n».​ случае — это​(столбец) и​ можно также использовать​ таблицах, основана на​ чтобы консолидировать столбцы,​ значениям в календаре.​

  • ​(Запрос на выборку).​ многим", но между​Таблица​ секунд и сообщить,​ алфавиту".​ – это будет​

  • ​ таблицах, а предназначен​ расходы из заначки.​ Excel, смотрите в​ в метаданных (именах​Таблица 3, столбец «n»   к   Таблице​ связи по умолчанию​CustomerID​ для создания и​ отношения. Чтобы увидеть,​

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

​ которые вы хотите​Советы:​Нажмите кнопку​ первой и последней​и выберите таблицу​ помогла ли она​Alex_b​ первый выпадающий список.​ только для изменения​ :)​ разделе сайта «Формулы​ полей и типах​ 1, столбец «a».​

  1. ​ в формулах.​в таблице «​ управление моделью. Подробнее​ почему отношения полезны,​

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

  3. ​ в Excel».​​ данных) могут повысить​​При попытке создания связи,​​Связь мо

my-excel.ru

Создание связи между таблицами Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вам также могут быть интересны следующие статьи

exceltip.ru

Как связать две таблицы в Excel.

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

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

Еще функцию «Вставить связь» можно вызвать так. Так же копируем, но в контекстном меню нажимаем на функцию «Специальная вставка». В появившемся диалоговом окне нажимаем на кнопку «Вставить связь».      Внимание!
Если нажмем клавишу «Enter», то связь может не установиться. Поэтому, когда установим связь, просто нажать на пустую ячейку.  А пульсирующую рамку у ячейки А1 в Таблице1 убрать клавишей «Esc».
      Когда установим связь в ячейке, в строке формул будет написан путь.
В ячейке А2 Таблицы2 появилось название столбца, как в Таблице1.
      Теперь, чтобы скопировать связь по всему столбцу, в адресе пути в ячейке А1 уберем знак $ - заменим ссылку абсолютную на относительную. Подробнее о ссылках смотрите в статье «Относительные и абсолютные ссылки в Excel».
      Копируем формулу со связью из ячейки А1 вниз по столбцу. Так же настраиваем и, затем, копируем, связь в других ячейках. Получилась такая таблица2.

Изменим цифры в Таблице1. 

В Таблице2 получилось так.

Сохраним обе книги, закроем. Теперь, если мы откроем Книгу1, в ней изменим цифры, поработаем и закроем. Но, когда мы откроем Книгу2, то все равно данные в Таблице2 при открытии Книги2 изменятся на те, что мы изменили в Таблице1.
      О других функциях специальной вставки смотрите в статье "Специальная вставка Excel".
Как связать таблицы на разных компьютерах, смотрите в статье "Общий доступ к файлу Excel".
      В Excel можно настроить автоматический перенос данных из таблицы Excel в Word и наоборот. Смотрите в статье "Как перенести таблицу из Excel в Word".

www.excel-office.ru

Как в excel связать две таблицы

Создание связи между двумя таблицами в Excel

​Смотрите также​​ нужный лист, там​ документы, сайт, т.д.​ ячейке С5 формула​Первая таблица у​Типы данных столбца подстановок​ месте модели соответствующие​ диаграммы обеспечивает визуализацию​.​В списке полей, в​ столбце​ займет около минуты.​ помощью свободных данных​ в таблице логики​ выбранной в поле​ один раз в​ полей сводной таблицы​Примечание:​ выбираешь ячейку, кот.​ Подробнее об этом​ такая: ='домашний бюджет'!R5​ нас называется «домашний​

​ и исходного столбца​ значения.​ всех таблиц в​В поле "Сортировать" выберите​ разделе "Могут потребоваться​DateKey​ После выполнения вы​ на Microsoft Azure​ операций со временем.​Таблица​ столбце.​ и Power View.​Мы стараемся как​ нужна​ смотрите в статье​ Копируем формулу вниз​ бюджет». Название можно​ должны быть совместимы.​Предположим, у вас есть​ модели данных. С​MonthInCalendar​ связи между таблицами"​в таблице​ увидите отчет о​ Marketplace. Некоторые из​В модели данных связи​.​Щелкните​При импорте связанных таблиц​ можно оперативнее обеспечивать​

​Sergey s.​ "Специальная вставка Excel".​ по столбцу расходов.​ сделать короткое. У​

​ Подробнее о типах​ модель, которая содержит​ помощью него вы​.​ нажмите​BasicCalendarUS​ состоянии перемещения 73 414​ этих наборов данных​ таблиц могут быть​

  1. ​В поле​Данные​ из реляционной базы​ вас актуальными справочными​: скопируй с одного​В таблице можно​В графе «Доход​ нас получились такие​

  2. ​ данных см. в​ продажи продукции по​

    ​ можете быстро определить,​В поле "По" выберите​Создать​

  3. ​указаны в формате​ строк. Нажмите​ очень велики, и​​ типа "один к​​Связанный столбец (первичный ключ)​​>​​ данных Excel часто​​ материалами на вашем​​ листа и вставь​

  4. ​ установить условное форматирование.​ минус расход» пишем​ данные за январь.​ статье Типы данных​ территории, и вы​ какие таблицы отделены​MonthOfYear​.​ 01.01.2012 00:00:00. В​

    ​Закрыть​ для их загрузки​ одному" (у каждого​выберите столбец, содержащий​Отношения​ может создавать эти​ языке. Эта страница​ на другой где​ Как это сделать,​ формулу разности. Получилось​Теперь на другом листе​ в моделях данных.​ впоследствии импортируете демографические​ от остальной части​

  5. ​.​​В поле "Связанная таблица"​​ таблице​​.​​ за разумное время​

​ пассажира есть один​​ уникальные значения, которые​​.​ связи в модели​ переведена автоматически, поэтому​

  1. ​ 1 таблица​​ смотрите в статье​​ такая формула: =B5-C5​​ книги Excel, создаем​​Подробнее о связях таблиц​

  2. ​ данные, чтобы узнать,​​ модели.​​Сводная таблица теперь сортирует​ выберите​​On_Time_Performance​​Чтобы импортировать второй набор​ необходимо быстрое подключение​ посадочный талон) или​ соответствуют значениям в​Если команда​ данных, формируемой в​ ее текст может​Рыбак​ «Условное форматирование в​Теперь,​ другую таблицу. Назовем​ см. в статье​ есть ли корреляция​Примечание:​ каждую комбинацию "месяц​

  3. ​On_Time_Performance​​также есть столбец​​ данных, нажмите​ к Интернету.​ "один ко многим"​​ столбце, выбранном в​​Отношения​ фоновом режиме. В​ содержать неточности и​: Через знак =.​ Excel» тут. Например,​

  4. ​как посчитать проценты в​​ вторую таблицу «Расчеты».​​ Связи между таблицами​ между продажами и​ Можно создавать неоднозначные связи,​ и год" (октябрь​, а в поле​​ даты и времени​​Получение внешних данных​

  5. ​Запустите надстройку Power Pivot​​ (в каждом рейсе​​ поле​недоступна, значит книга​ других случаях необходимо​ грамматические ошибки. Для​ Только вопрос что​​ в графе «Доход​​ Excel.​

  6. ​Здесь будут проводиться расчеты​​ в модели данных.​​ демографическими тенденциями на​

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

​ которые являются недопустимыми​

​ 2011, ноябрь 2011)​ "Связанный столбец (первичный​FlightDate​ >​

​ в Microsoft Excel​ много пассажиров), но​

​Столбец​ содержит только одну​

​ создавать связи вручную.​ нас важно, чтобы​ и как связывать.​ минус расход» установим​В графе «Процент​

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

  • ​ по данным из​К началу страницы​ каждой территории. Так​ при использовании в​ по номеру месяца​ ключ)" —​, значения которого указаны​Из службы данных​ и откройте окно​ не "многие ко​.​

  • ​ таблицу.​Убедитесь, что книга содержит​ эта статья была​ Связывать можно данные​ условное форматирование «меньше​ расхода от дохода»​ первой таблицы. Заполняем​Можно сделать в​ как демографические данные​ сводной таблице или​ в году (10,​FlightDate​ в том же​ >​ Power Pivot.​ многим". Связи "многие​

  • ​Нажмите кнопку​В окне​ хотя бы две​ вам полезна. Просим​ по колонкам, строкам​ 0».​ пишем такую формулу:​ таблицу формулами. Как​ Excel несколько таблиц,​ поступают из различных​ отчете Power View.​ 11). Изменить порядок​.​ формате: 01.01.2012 00:00:00.​Из Microsoft Azure Marketplace​Нажмите​ ко многим" приводят​ОК​Управление связями​ таблицы и в​ вас уделить пару​ и т. д.​Теперь, если расход​ =C5/B5​ написать формулу в​ в которых данные​ источников, то их​ Пусть все ваши​ сортировки несложно, потому​В поле "Таблица" выберите​ Два столбца содержат​.​Получение внешних данных​ к ошибкам циклической​

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

  • ​Проверьте, чтобы в​ Excel, смотрите в​ из одной таблицы​ таблицы первоначально изолированы​ таблицы связаны каким-то​ что канал​BasicCalendarUS​ совпадающие данные одинакового​В разделе​

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

​ >​ зависимости, таким как​Примечания о связях​Создать​ есть столбец, который​ помогла ли она​ на листах можно​ ячейка в этой​ этой графе в​ разделе сайта «Формулы​ переносятся автоматически в​ от остальной части​ образом с другими​

  1. ​DateStream​, а в поле​ типа и по​Type​

  2. ​Из службы данных​​ "Обнаружена циклическая зависимость".​​Пример. Связывание данных логики​​.​​ можно сопоставить со​​ вам, с помощью​​ и с разными​ графе окрасится в​ ячейке стоял​ в Excel».​

  3. ​ другую -​​ модели. Для интеграции​​ таблицами в модели,​​предоставляет все необходимые​​ "Столбец (чужой)" —​

  4. ​ крайней мере один​​(Тип) нажмите​​ >​​ Эта ошибка может​​ операций со временем​

  5. ​В окне​​ столбцом из другой​​ кнопок внизу страницы.​​ файлами.​​ красный цвет.​

  6. ​формат «Процентный».​Сначала переносим итоговые​​связать таблицы Excel​​ демографических данных с​ но при попытке​

  7. ​ столбцы для работы​​DateKey​​ из столбцов (​

  8. ​Data​​Из Microsoft Azure Marketplace​​ произойти, если вы​

  9. ​ с данными по​​Создание связи​​ таблицы.​​ Для удобства также​​Dixi inc​Вывод – мы оплатили​Как установить формат,​ суммы дохода и​. Есть несколько вариантов.​ остальной частью своей​ объединения полей из​​ этог

my-excel.ru

Сводная таблица на основе двух и более связанных таблиц - Сводные таблицы - Excel - Каталог статей

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы, Строки заказов, Товары, Клиенты.

Таблица заказов:

Таблица Строк заказов:

Таблица Товаров

Таблица Клиентов

Исходные таблицы оформлены в виде умных таблиц: Orders, OrderLines, Goods и Clients.

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа, таблицы Orders и Clients - по полю ID_клиента, таблицы OrderLines и Goods - по полю ID_товара.

Скачать пример

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

  1. Выбираем в меню Вставка пункт Сводная таблица. В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных. Мы могли бы её выбрать, но я рекомендую другой, более удобный способ. Просто нажмите OK.

  2. В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ...

  3. Нажмём её. Появится такой вопрос:

  4. Отвечаем Да и видим, что в список полей добавились все наши таблицы:

  5. Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ...

  6. Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines. Обратите внимание, что Excel умеет создавать связь типа "один к одному" или "один ко многим". Причём первой надо указывать таблицу, где "много", в противном случае Excel ругается и предлагает поменять их местами.

  7. Аналогично создаём другие связи.


  8. В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

  9. Чтобы видеть больше полей на панеле Поля сводной таблицы, можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

  10. Результат будет таким:

  11. В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

Просто и удобно!

Читайте также:

perfect-excel.ru

Добавление данных с листа в модель данных с помощью связанной таблицы

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

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

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

Чтобы связать данные с таблицей, выполните указанные ниже действия.

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

  2. ОтФорматируйте строки и столбцы как таблицу.

    • На вкладке Главная _Гт_ Форматирование таблицы, а затем выберите стиль таблицы. Вы можете выбрать любой стиль, но не забудьте выбрать вариант таблица с заголовками. Если в таблице нет заголовков, подумайте о ее создании. В противном случае в Excel будут использоваться произвольные имена (Столбец1, Столбец2 и т. д.), которые не сообщают никаких значимых сведений о содержимом столбца.

    • Присвоение имени таблице. В окне Excel щелкните Работа с таблицами > Конструктор. В группе Свойства введите имя таблицы.

  3. Поместите курсор в любую ячейку таблицы.

  4. Щелкните Power Pivot > Добавить в модель данных, чтобы создать связанную таблицу. В окне Power Pivot вы увидите таблицу со значком ссылки, который означает, что таблица связана с исходной таблицей в Excel.

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

Если книга ранее не содержала такой, она теперь содержит модель данных. Модель создается автоматически при создании связи между двумя таблицами или нажатия кнопки Добавить в модель данных в Power Pivot. Дополнительные сведения см. в статье Создание модели данных в Excel.

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

Имена таблиц являются исключением. При переименовании таблицы в Excel вам потребуется вручную обновить таблицу в Power Pivot.

Синхронизация изменений между таблицей и моделью

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

В некоторых случаях может потребоваться управление поведением обновления. Переключение в режим обновления вручную с помощью надстройки Power Pivot.

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

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

  2. Откройте окно Power Pivot.

  3. На вкладках внизу щелкните связанную таблицу. Любая связанная таблица обозначается значком небольшой ссылки рядом с именем таблицы.

  4. На ленте в верхней части откройте вкладку Связанная таблица.

  5. В режиме обновления выберите ручной или Автоматический. По умолчанию выбрано значение авто. Если вы перейдете на раздел "вручную", обновления будут происходить только при использовании команд Обновить все или Обновить выбранные в ленте таблицы в окне Power Pivot или команда Обновить все на ленте Power Pivot в Excel.

support.office.com

Отправить ответ

avatar
  Подписаться  
Уведомление о