Как установить связь между таблицами в excel: Задание связей между таблицами в модели данных

Задание связей между таблицами в модели данных

Excel

Импорт и анализ данных

Модели данных

Модели данных

Задание связей между таблицами в модели данных

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

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

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

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

Результаты в стеблях могут неожиданно изменяться после удаления или отключения связи.

Начните с Excel

1. Щелкните Данные> Отношения.

2. В диалоговом окне Управление связями выберите одну связь из списка.

3. Нажмите кнопку Удалить.

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

   ОК.

5. org/ListItem»>

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

Начните с Power Pivot

1. Щелкните Главная > схемы.

2. Щелкните правой кнопкой мыши линию связи, соединяющую две таблицы, и выберитеудалить . Чтобы выбрать несколько связей, щелкните каждую из них, удерживая нажатой кнопку CTRL.

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

   Примечания: 

   • org/ListItem»>

     Связи существуют в модели данных. Excel также создает модель при импорте нескольких таблиц или при их подмыве. Вы можете специально создать модель данных для использования в качестве основы для отчетов в сбитых, сводных и power View. Дополнительные сведения см. в статье Создание модели данных в Excel.

   • Вы можете откатить удаление в Excel закрыть управление связями и нажать кнопку Отменить. Если вы удаляете связь в Power Pivot, отменить удаление связи нельзя. Связь можно создать повторно, но для выполнения этой действия требуется полный пересчет формул в книге. Поэтому всегда проверяйте перед удалением связи, используемой в формулах. Подробные сведения см. в обзоре связей.

   • org/ListItem»>

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

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

К началу страницы

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

Содержание

• 1 Создание связанных таблиц
  • 1.1 Способ 1: прямое связывание таблиц формулой
  • 1.2 Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ
  • 1.3 Способ 3: выполнение математических операций со связанными данными
  • 1. 4 Способ 4: специальная вставка
  • 1.5 Способ 5: связь между таблицами в нескольких книгах
• 2 Разрыв связи между таблицами
  • 2.1 Способ 1: разрыв связи между книгами
  • 2.2 Способ 2: вставка значений
  • 2.3 Помогла ли вам эта статья?
  • 2.4 Создание связей между рабочими книгами
  • 2.5 Прежде чем создавать связи между таблицами
  • 2.6 Обновление связей
  • 2.7 Разорвать связи в книгах Excel
• 3 Проблема
• 4 Пример
  • 4.1 Скачать пример
• 5 Создание модели данных
  • 5.1 Читайте также:
    • 5.1.1 Введение в сводные таблицы
    • 5.1.2 Автоматизация форматирования сводных таблиц

При выполнении определенных задач в 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 имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

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

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

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

Да Нет

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать 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. В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

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

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

Связь между таблицами

Занятие 9

Работа с несколькими таблицами

• Организация связи между таблицами.

• Поиск влияющих ячеек на других листах.

• Выполнение консолидации данных.

• Создание сводной таблицы с помощью мастера.

• Редактирование сводной таблицы.

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

Лист6!$С$7

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

‘С:\ЕХСЕL\[ТАВL]Лист6’!$С$7

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

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

Для того чтобы связать данные, нужно:

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

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

• Выбрать в меню Правка команду Копировать.

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

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

• Выбрать в меню Правка команду Специальная вставка.

Занятие 9

186

Работа с несколькими таблицами

На экране появится диалоговое окно.

• Установить в группе Вставить флажок опции все.

• Установить в группе Операция флажок опции нет.

• Щелкнуть кнопку Вставить связь.

Задание 1.

Откройте книгу exampI8. xls. Введите в листе Калькуляция цену из листа Прейскурант, организовывая связь между листами.

Выполнение задания.

1. Откройте книгу exampl8.xls.

2. Посмотрите на листе Калькуляция, какие данные нужно взять из листа Прейскурант.

3. Перейдите на лист Прейскурант.

4. Поместите курсор на цену 1 литра молока.

5. Выберите в меню Правка команду Копировать.

6. Перейдите на лист Калькуляция.

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

8. Выберите в меню Правка команду Специальная вставка. На экране появится диалоговое окно.

9. Установите в группе Вставить флажок опции все.

10. Установите в группе Операция флажок опции нет.

11. Щелкните кнопку Вставить связь.

12. Посмотрите, какая ссылка ввелась в ячейку с ценой.

Занятие 9

187

Работа с несколькими таблицами

Поиск влияющих и зависимых ячеек на другом листе

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

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

• Щелкнуть кнопку Влияющие ячейки

или

Зависимые ячейки. Появится черная пунктирная стрелка. В начале стрелки

есть значок Щ

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

Задание 2.

Откройте книгу Гранд.хls, Лист5. Отразите стрелку зависимостей к ячейке В5 и перейдите к влияющей ячейке.

Выполнение задания:

1. Выделите ячейку В5.

2. Щелкните кнопку Влияющие ячейки • направленная к выбранной ячейке черная со значком

Появится пунктирная стрелка

3. Дважды щелкните стрелку. Появится окно Переход.

4. Выберите ссылку в поле Перейти к:.

5. Щелкните кнопку ОК. Произойдет переход на Лист4, выделится ячейка С5.

Занятие 9

188

Работа с несколькими таблицами

Задание 3.

Самостоятельно отразите стрелку зависимостей от ячейки С5 и перейдите к зависимой ячейке.

Консолидация рабочих листов

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

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

Итоговая таблица может располагаться в любой книге на любом рабочем листе.

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

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

Занятие 9

189

Работа с несколькими таблицами

Для того чтобы выполнить консолидацию, нужно:

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

• Выбрать в меню Данные команду Консолидация. На экране появится диалоговое окно:

• В раскрывающемся списке Функция выбрать функцию консолидации. По умолчанию используется функция суммы.

• В поле Ссылка определить области объединяемых данных. Для этого:

— открыть нужный лист с данными;

— выделить область с данными, включая в выделение заголовки данных;

— щелкнуть кнопку Добавить.

Выделенный интервал занесется в поле Список диапазонов:.

• Повторить предыдущий пункт для следующих объединяемых таблиц.

• В области Использовать в качестве имен установить флажок проверки:

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

Если нужно, то можно установить оба флажка.

• Установить флажок проверки Создавать связи с исходными данными,

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

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

• Щелкнуть кнопку ОК.

При любых изменениях исходных обновление данных в итоговой таблице.

данных происходит автоматическое

Занятие 9

190

Работа с несколькими таблицами

Задание 4.

Перейдите в окно с документом exampl8.xls. Подсчитайте итоговые данные по первому разделу балансов двух филиалов. Расчеты произведите, используя консолидацию данных. Данные баланса находятся на листах Филиал №1 и Филиал №2. Результаты разместить на листе Баланс.

Выполнение задания.

1. Перейдите на лист Баланс.

2. Выделите область для итоговой таблицы А5:С11.

3. Выберите в меню Данные команду Консолидация. На экране появится диалоговое окно.

4. В раскрывающемся списке Функция выберите функцию Сумма.

5. Перейдите на лист Филиал №1.

6. Выделите интервал с данными первого раздела А5:С11.

7. Щелкните мышью в диалоговом окне по кнопке Добавить.

8. Перейдите на лист Филиал №2.

9. Выделите интервал с данными первого раздела А5:С11.

10. Щелкните мышью по кнопке Добавить.

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

12. Установите флажок проверки Создавать связи с исходными данными.

13. Щелкните кнопку ОК.

14. Посмотрите на итоги и сравните их с исходными данными.

Сводная таблица

Сводная таблица предназначена для анализа данных таблицы большого объема. Для создания сводной таблицы необходимо определить:

— какие данные исходной таблицы будут метками строк сводной таблицы;

— какие данные исходной таблицы будут метками столбцов сводной таблицы;

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

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

Занятие 9

191

Работа с несколькими таблицами

поля, используемого в качестве столбца, не будет включена в сводную

таблицу.

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

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

В данном примере метками строк сводной таблицы являются элементы столбца Наименование исходной таблицы. Метками столбцов сводной таблицы являются элементы столбца Отправка исходной таблицы.

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

192

Занятие 9

Работа с несколькими таблицами

Как создать сводную таблицу

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

Для того чтобы создать сводную таблицу с помощью Мастера Сводных Таблиц, нужно:

• Открыть рабочую книгу с данными, на основе которых будет строиться сводная таблица.

• Выбрать в меню Данные команду Сводная Таблица. На экране появится диалоговое окно Шага 1:

• Выбрать источник исходных данных:

— Список или база данных Microsoft Excel;

— Внешняя база данных, созданная в другом приложении;

— Несколько интервалов консолидации Microsoft Excel;

— Другая сводная таблица этой же рабочей книги.

• Щелкнуть кнопку Далее

Занятие 9

193

Работа с несколькими таблицами

На экране появится диалоговое окно Шага 2:

• Перейти на лист, где расположены исходные данные.

• Выделить интервал данных, на основе которых будет строиться сводная таблица.

Щелкнуть кнопку Макет… На экране появится диалоговое окно:

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

Занятие 9

194

Работа с несколькими таблицами

установку параметров, щелкнув ОК.

Поместить таблицу в: .

таблицы появится панель инструментов Сводные таблицы:

Задание 5.

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

Выполнение задания.

1. Перейдите на лист Школьник.

2. Выберите в меню Данные команду Cводная Таблица. На экране появится диалоговое окно Шага 1.

3. Включите опцию В списке или базе данных Microsoft Excel.

4. Щелкните кнопку Далее. Появится окно Шага 2.

5. Выделите на листе Школьник диапазон ячеек АЗ:Е21.

6. Щелкните кнопку Далее. Появится окно Шага 3.

7. Щелкните кнопку Макет.

8. Перетащите на макет мышью метку поля Дата поставки в область страница, метку поля Наименование в область Строка, метки полей Поставщик и Размер в область столбец, метку поля Количество в область данные.

9. Щелкните кнопку ОК. На экран вернется диалоговое окно Шага 3 с

установленным ключом новый лист. Ю.Щелкните кнопку Готово. Вставится новый лист 1 со сводной таблицей.

Занятие 9

195

Работа с несколькими таблицами

Вывод данных по страницам

Для просмотра сводной таблицы для одного элемента поля страницы нужно:

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

• Выбрать из списка нужный элемент и щелкнуть кнопку ОК.

Задание 6.

Выведите в сводной таблице данные на 15.08.01.

Выполнение задания.

1. Щелкните мышью по кнопке раскрывающегося списка, расположенной справа от поля страницы Дата поставки.

2. Выберите из списка дату 15.08.01.

Как создать отношения между несколькими таблицами с помощью модели данных в Excel

Excel — мощный инструмент для анализа данных и последующей автоматизации при обработке больших наборов данных. Вы можете потратить много времени на анализ тонн данных с помощью ВПР, ИНДЕКС-ПОИСКПОЗ, СУММЕСЛИ и т. д.

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

Основные требования

Вам понадобятся Power Pivot и Power Query (Get & Transform) для выполнения нескольких задач при создании модели данных Excel. Вот как вы можете получить эти функции в своей книге Excel:

Как получить Power Pivot

1. Excel 2010: Вам необходимо загрузить надстройку Power Pivot с веб-сайта Microsoft, а затем установить ее для своей программы Excel на свой компьютер.

2. Excel 2013: Версия Office профессиональный плюс Excel 2013 включает Power Pivot. Но вам нужно активировать его перед первым использованием. Вот как:

1. Нажмите File на ленте книги Excel.
2. Затем нажмите Параметры , чтобы открыть Параметры Excel .
3. Теперь нажмите Надстройки .
4. Выберите Надстройки COM , щелкнув раскрывающееся меню в поле Управление .
5. Нажмите Перейти и установите флажок Microsoft Power Pivot для Excel .

3. Excel 2016 и более поздние версии: Меню Power Pivot находится на ленте .

Связано: Как добавить вкладку «Разработчик» на ленту в Microsoft Word и Excel

Как получить Power Query (получить и преобразовать)

1. Excel 2010: Надстройку Power Query можно загрузить с сайта Microsoft. После установки Power Query появится на ленте .

2. Excel 2013: Вам необходимо активировать Power Query, выполнив те же действия, которые вы только что сделали, чтобы включить Power Pivot в Excel 2013.

3. Excel 2016 и более поздние версии: Вы можете найти Power Query (получение и преобразование), перейдя на вкладку Данные на ленте Excel .

Создание модели данных путем импорта данных в книгу Excel

Для этого руководства вы можете получить предварительно отформатированные образцы данных от Microsoft:

Загрузить : Пример данных учащихся (только данные) | Пример данных учащегося (полная модель)

Вы можете импортировать базу данных с несколькими связанными таблицами из многих источников, таких как книги Excel, Microsoft Access, веб-сайты, SQL Server и т. д. Затем вам нужно отформатировать набор данных, чтобы Excel мог его использовать. Вот шаги, которые вы можете попробовать:

1. В Excel 2016 и более поздних версиях щелкните вкладку Данные и выберите Новый запрос .

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

3. При использовании версии Excel 2013 щелкните Power Query на ленте , а затем выберите Получить внешние данные , чтобы выбрать данные для импорта.

4. Вы увидите Навигатор , где вам нужно выбрать, какие таблицы вам нужно импортировать. Установите флажок Выберите несколько элементов , чтобы выбрать несколько таблиц для импорта.

5. Нажмите Загрузить , чтобы завершить процесс импорта.

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

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

1. Отформатируйте данные в табличной модели, выбрав все ячейки, содержащие данные, и затем нажмите Ctrl+T .

2. Теперь выберите всю таблицу, а затем щелкните вкладку Power Pivot на ленте .

3. В разделе Tables щелкните Add to Data Model .

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

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

Связано: Как создать анализ «что, если» в Microsoft Excel

Построить отношения между таблицами в модели данных

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

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

1. На ленте рабочей книги Excel щелкните меню Power Pivot .

2. Теперь нажмите Управление в разделе Модель данных . Вы увидите редактор Power Pivot , как показано ниже:

3. Нажмите на представление схемы 9.0014, расположенная в разделе View на вкладке Power Pivot Home . Вы увидите заголовки столбцов таблицы, сгруппированные в соответствии с именем таблицы.

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

.

Ниже описывается связь между таблицами:

• Стол для студентов | Студенческий билет в таблицу оценок | Студенческий билет
• Таблица семестров | Идентификатор семестра для таблицы оценок | Семестр
• Табличные классы | Номер класса в таблице оценок | Идентификатор класса

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

.

6. Вы увидите Звездочка (*) с одной стороны и Один (1) с другой стороны в диаграмме взаимосвязей. Он определяет, что между таблицами существует отношение «один ко многим».

7. В редакторе Power Pivot щелкните вкладку Дизайн и выберите Управление связями , чтобы узнать, какие поля создают связи.

Создание сводной таблицы с использованием модели данных Excel

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

Связанный: что такое интеллектуальный анализ данных и является ли он незаконным?

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

1. В редакторе Power Pivot щелкните вкладку Home .

2. На ленте щелкните сводную таблицу .

3. Выберите любой из вариантов «Новый рабочий лист» или «Существующий рабочий лист».

4. Выберите OK . Excel добавит сводную таблицу , которая покажет панель списка полей справа.

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

Преобразование сложных наборов данных в простые отчеты с использованием модели данных Excel

Модель данных Excel использует преимущества создания отношений между таблицами для создания осмысленных сводных таблиц или диаграмм для целей представления данных.

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

Связать таблицы InDesign с Excel

Примечание . Это вторая часть серии из трех статей о таблицах InDesign. Часть 1 посвящена трем различным способам создания таблицы в InDesign. Не забудьте подписаться на нашу рассылку новостей , чтобы быть в курсе будущих сообщений или крупных обновлений.

Как связать файл Excel с InDesign

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

Проблема:

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

Неправильный путь:

Если вы просто импортируете файл Excel в InDesign, то когда придет время изменить цены, вам придется:

• либо снова импортировать файл Excel в InDesign
• , либо ценообразование вручную

Это НЕ то, что я хотел бы сделать в любое время, когда будет обновление цен.

{{banner-component}}

Связывание электронной таблицы Excel с InDesign (правильный способ)

В этом случае лучший способ создать таблицу — импортировать файл Excel в InDesign, убедившись, что InDesign создает ссылка с таблицей.

Вот как это делается:

Задайте настройки

1. Щелкните InDesign (в MacOS) или Edit (в Windows) в строке главного меню, перейдите к Настройки в открывшемся меню и нажмите Обработка файлов…
   

2. Отметьте Создать ссылки при размещении текстовых и табличных файлов в окне Обработка файлов и нажмите OK .
   

Теперь давайте импортируем новый файл Excel в InDesign.

Поместите таблицу

1. Щелкнем Файл > Поместите (В противном случае вы можете нажать Cmd+D в Mac Os или Ctrl+D в Windows)
   

2. Установите флажок Показать параметры импорта в окне импорта (если он еще не установлен), выберите файл и щелкните Открыть , чтобы импортировать файл Excel в InDesign.
   

Это важно: как мы видели в разделе «Импорт файла Excel» выше, здесь вы можете персонализировать несколько параметров. В этом конкретном случае я уже создал стиль таблицы (подробнее об этом в части 3) и собираюсь его использовать.
Имейте в виду, что если на этом шаге вы не примените стиль таблицы, при каждом изменении связанного Excel вы потеряете свойства макета таблицы, размещенной в InDesign.

3. Щелкните OK , а затем нарисуйте текстовый фрейм в InDesign, чтобы разместить таблицу.
   

Это таблица в InDesign, связанная с электронной таблицей Excel.

Мы хотим установить первую строку в качестве заголовка.

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

Я также адаптирую ширину столбцов. Это таблица, которую мы только что создали, импортируя электронную таблицу из Excel в InDesign.

Изменение данных в электронной таблице

Допустим, вы хотите изменить цены в долларах (сейчас они указаны в евро).

Вам необходимо:

• заменить символ € на символ $
• преобразовать все цены в их значение в долларах

Внутри Excel

Начнем с этой таблицы.

1. Давайте внесем это изменение в файл Excel. Первое, что мы меняем, это € на символ $.
   

2. Затем с помощью выражения я изменю значение цен и сохраню файл в Excel.
   

Назад в InDesign

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

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

Отлично! Таблица в InDesign обновлена!

Вы можете задаться вопросом: «Что произойдет, если я добавлю строку в электронную таблицу Excel?»

Вот таблица в InDesign до (слева) и после (справа) обновления. Как видите, обновленная таблица потеряла настройки заголовка .

Снова выберите первую строку, щелкните правой кнопкой мыши и выберите Преобразовать в строки заголовков .

Важно! Хотя ширина ваших столбцов будет сохраняться каждый раз, когда таблица обновляется в Excel и изменения применяются в InDesign, если вы вносите важные изменения в электронную таблицу (например, добавляете строку), вам всегда нужно будет верните заголовок.

Заключение

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

Как видите, связать электронную таблицу Excel с InDesign довольно просто, но очень эффективно. Один из самых удобных способов использования этой функции — локализация документов для рынка США.

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

• Электронная таблица в €
• Электронная таблица в $
• Электронная таблица для переменных (например, значение изменения между € и $)

И мой процесс:

• Я создаю итальянскую версию (которая имеет связанную таблицу в евро)
• затем я создаю английскую версию с помощью Redokun и меняю ссылку таблицы на электронную таблицу в долларах

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

Основные сочетания клавиш InDesign

Команда	ОС Х	Окна
Поместите файл (изображение, электронную таблицу и т. д.) в InDesign	Cmd + D	Ctrl + D

Полезные ресурсы

Вот полезное видео Лидии о привязке таблицы Excel к InDesign: https://www.youtube.com/watch?v=yjEa0Zk75-o

В следующем посте (часть 3)

В части 3 мы научимся использовать стили таблиц InDesign с помощью небольшого упражнения. Мы создадим стили, которые нам нужны, чтобы разместить таблицу, которую мы видели в этом посте (часть 2).

Обязательно подпишитесь на нашу рассылку новостей , чтобы быть в курсе будущих сообщений или крупных обновлений. Если вам понравился пост, я был бы очень признателен, если бы вы им поделились!

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Соединение двух или более таблиц в Excel с помощью Power Query

В этом руководстве мы рассмотрим, как можно соединить таблицы в Excel на основе одного или нескольких общих столбцов с помощью Power Query и мастера объединения таблиц.

Объединение данных из нескольких таблиц — одна из самых сложных задач в Excel. Если вы решите сделать это вручную, вы можете потратить часы только на то, чтобы обнаружить, что вы испортили важную информацию. Если вы опытный профессионал в Excel, то, возможно, вы можете положиться на формулы ВПР и ПОИСКПОЗ ИНДЕКС. Вы верите, что макрос мог бы выполнить эту работу в мгновение ока, если бы вы только знали, как это сделать. Хорошие новости для всех пользователей Excel: Power Query или мастер слияния таблиц могут сэкономить ваше время. Выбор за вами.

Как объединять таблицы с помощью Excel Power Query

Проще говоря, Power Query (также известный как Get & Transform ) — это инструмент для объединения, очистки и преобразования данных из нескольких источников в нужный формат, например таблица, сводная таблица или сводная диаграмма

Помимо прочего, Power Query может объединять 2 таблицы в 1 или объединять данные из нескольких таблиц путем сопоставления данных в столбцах , что является предметом этого руководства. оправдать ваши ожидания, пожалуйста, имейте в виду следующее:

• Power Query — это встроенная функция в Excel 2016 — Excel 365, но ее также можно загрузить в Excel 2010 и Excel 2013 и использовать в качестве надстройки. В вашей версии некоторые окна могут отличаться от изображений в этом руководстве, которые были захвачены в Excel 2016.
• Для корректного объединения таблиц в них должен быть хотя бы один общий столбец (также называемый общим идентификатором или ключевым столбцом или уникальным идентификатором ). Кроме того, общие столбцы должны содержать только уникальные значения без повторов.
• Исходные таблицы могут располагаться на одном листе или на разных рабочих листах.
• В отличие от формул, Power Query не извлекает данные из одной таблицы в другую. Он создает новую таблицу, которая объединяет данные из исходных таблиц.
• Результирующая таблица не обновляется автоматически. Вы должны явно указать Excel, чтобы сделать это. Посмотрите, как обновить объединенную таблицу.

Исходные данные

В качестве примера объединим 3 таблицы на основе общих столбцов ID заказа и продавца. Обратите внимание, что наши таблицы имеют разное количество строк, и хотя в таблице 1 есть дубликаты в столбце Продавец , таблица 3 содержит только уникальные записи.

Наша задача — сопоставить данные в таблице 1 с соответствующими записями из двух других таблиц и объединить все данные в новую таблицу вида:

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

• Таблица 1 названа Заказы
• Таблица 2 названа Продукты
• Таблица 3 названа Комиссии

Создание подключений Power Query

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

Чтобы сохранить таблицу как соединение в Power Query, выполните следующие действия:

1. Выберите свою первую таблицу ( Заказы ) или любую ячейку в этой таблице.
2. Перейдите на вкладку Данные > группу Получить и преобразовать и щелкните Из таблицы/диапазона .
   
3. В открывшемся редакторе Power Query щелкните стрелку раскрывающегося списка Close & Load (не саму кнопку!) и выберите параметр Close and Load To… .
   
4. В Импорт данных , выберите параметр Only Create Connection и нажмите OK .
   

   Это создаст соединение с именем вашей таблицы/диапазона и отобразит это соединение на панели «Запросы и соединения», которая появляется в правой части вашей книги.

5. Повторите вышеуказанные шаги для всех других таблиц, которые вы хотите объединить (еще две таблицы, Товары и Комиссии , в нашем случае).

Когда закончите, вы увидите все соединения на панели:

Объединение двух подключений в одну таблицу

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

1. На вкладке Данные в группе Получить и преобразовать данные Получить данные , выбрать Объединить запросы в раскрывающемся списке и нажать Объединить :
   
2. В диалоговом окне Объединить выполните следующие действия:
   • Выберите 1-й стол ( Заказы ) из первого раскрывающегося списка.
   • Выберите вторую таблицу ( Продукты ) из второго раскрывающегося списка.
   • В обоих предварительных просмотрах щелкните соответствующий столбец ( ID заказа ), чтобы выбрать его. Выбранный столбец будет выделен зеленым цветом.
   • В раскрывающемся списке Join Kind оставьте значение по умолчанию: Left Outer (все с первого, совпадение со второго).
   • Нажмите OK .

После выполнения вышеуказанных шагов редактор Power Query отобразит вашу первую таблицу ( Orders ) с одним дополнительным столбцом с именем, аналогичным вашей второй таблице ( Products ), добавленным в конец. В этом дополнительном столбце пока нет никаких значений, только слово «Таблица» во всех ячейках. Но не расстраивайтесь, вы все сделали правильно, и мы это исправим в ближайшее время!

Выберите столбцы для добавления из второй таблицы

На данный момент у вас есть таблица, похожая на ту, что показана на скриншоте ниже. Чтобы завершить процесс слияния, выполните следующие действия в редакторе Power Query:

1. В добавленном столбце ( Продукты ) щелкните двустороннюю стрелку в заголовке.
   
2. В открывшемся окне сделайте следующее:
   • Оставьте переключатель Expand выбранным.
   • Отмените выбор всех столбцов, а затем выберите только те столбцы, которые вы хотите скопировать из второй таблицы. В этом примере мы выбираем только Продукт , потому что в нашей первой таблице уже есть Продавец и ID заказа .
   • Снимите флажок Использовать исходное имя столбца в качестве префикса (если только вы не хотите, чтобы перед именем столбца стояло имя таблицы, из которой этот столбец взят).
   • Нажмите OK .

В результате вы получите новую таблицу, содержащую все записи из вашей первой таблицы и дополнительные столбцы из второй таблицы:

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

Объединить больше таблиц (необязательно)

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

1. Сохраните таблицу, полученную на предыдущем шаге (показана на скриншоте выше), как соединение:
   • В редакторе Power Query щелкните стрелку раскрывающегося списка Закрыть и загрузить и выберите Закрыть и загрузить в… .
   • В диалоговом окне Import Data выберите Only Create Connection и нажмите OK .

   Это добавит еще одно подключение с именем Merge1 на панель Запросы и подключения. Вы можете переименовать это соединение, если хотите (щелкните правой кнопкой мыши и выберите Переименовать во всплывающем меню).
   

2. Объедините Объединить1 с вашей третьей таблицей ( Комиссии ), выполнив следующие действия (вкладка Данные > Получить данные > Объединить запросы > Объединить ).

   На скриншоте ниже показаны мои настройки:
   

3. При нажатии кнопки OK в диалоговом окне Merge открывается редактор Power Query , в котором вы выбираете столбцы для добавления из таблицы 3.

В этом примере мы добавляем только Комиссия столбец:

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

Импорт объединенной таблицы в Excel

Получив таблицу в редакторе Power Query, вам остается сделать только одно — загрузить ее в книгу Excel. И это самая легкая часть!

1. В редакторе Power Query щелкните стрелку раскрывающегося списка Закрыть и загрузить и выберите Закрыть и загрузить в… .
2. В диалоговом окне Импорт данных выберите параметры Таблица и Новый рабочий лист .
3. Нажмите OK .

На новом рабочем листе появится новая таблица, объединяющая данные из двух или более источников. Поздравляем, вы сделали это!

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

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

Как объединить таблицы на основе нескольких столбцов с помощью Power Query

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

В диалоговом окне Merge , удерживая клавишу Ctrl, щелкните ключевые столбцы один за другим, чтобы выбрать их. Важно, чтобы вы нажимали на столбцы в одном и том же порядке в обоих предварительных просмотрах, чтобы совпадающие столбцы имели одинаковые номера. Например, Продавец — это ключевой столбец 1, а Продукт — это ключевой столбец 2. Пустые ячейки или строки, которым не удалось сопоставить Power Query, показывают null :

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

Как обновить/обновить объединенную таблицу

Лучшее в Power Query то, что это однократная настройка. Когда вы вносите некоторые изменения в исходную таблицу, вам не нужно повторять весь процесс снова. Просто нажмите кнопку Обновить на панели Запросы и подключения , и объединенная таблица сразу же обновится:

, чтобы вернуть его.

В качестве альтернативы можно нажать кнопку Обновить все на вкладке Данные или кнопку Обновить на Запрос (эта вкладка активируется после выбора любой ячейки в объединенной таблице).

Мастер объединения таблиц — быстрый способ объединения 2 таблиц в Excel

Теперь, когда вы знакомы со встроенным инструментом, позвольте мне показать вам наш подход к объединению таблиц в Excel.

В этом примере мы будем объединять те же таблицы, к которым только что присоединились с помощью Power Query. Я только что добавил еще несколько строк во вторую таблицу, чтобы показать вам больше возможностей нашей надстройки:

Если в Excel установлен мастер объединения таблиц, вам нужно сделать следующее:

1. Выберите первую таблицу или любую ячейку в ней и нажмите кнопку Объединить две таблицы на вкладке Ablebits Data :
   
2. Быстро просмотрите выбранный диапазон, чтобы убедиться, что надстройка правильно его восприняла, и нажмите Далее .
   
3. Выберите вторую таблицу и нажмите Далее . Обратите внимание, что наша вторая таблица содержит 26 строк по сравнению с 10 строками в первой таблице:
   
4. Выберите один или несколько совпадающих столбцов и нажмите Далее . Так как мы соединяем две таблицы одним общим столбцом, Идентификатор заказа , мы выбираем только этот столбец:
   
   Обратите внимание на поле С учетом регистра, соответствующее вверху. Выберите его, если вы хотите рассматривать текст в верхнем и нижнем регистре в ключевых столбцах как разные символы. В этом примере нам это не нужно, поэтому мы оставляем поле невыбранным.
5. Выберите столбцы до обновление в первой таблице. Этот шаг не является обязательным, и если вы не хотите никаких обновлений, вы можете нажать Далее , не выбирая здесь ничего.

   Мы выбираем столбец Продавец , потому что у нас больше строк во второй таблице, и мы хотим, чтобы имена новых продавцов отображались в существующем столбце Продавец :
   

6. Выберите столбцы, которые необходимо добавить в первую таблицу, в нашем случае Продукт , и нажмите Далее :
   
7. Этот шаг очень важен, потому что он определяет, как ваши таблицы будут объединены.
   В этом примере мы используем параметры по умолчанию, показанные на снимке экрана ниже. Но я хотел бы обратить ваше внимание на следующие 2 поля, которые могут предотвратить перезапись ваших существующих данных, если вы выбрали обновление некоторых столбцов:
   • Только пустые ячейки
   • Только если ячейки таблицы поиска содержат данные

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

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

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

В отличие от Power Query, мастер объединения таблиц не поддерживает связь между результирующей и исходной таблицами.