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

Добавил пользователь Alex
Обновлено: 03.10.2024

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

Знание некоторых особенностей работы алгоритма позволяет получать предсказуемые результаты при помощи функций управления связями USERELATIONSHIP и CROSSFILTER.

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

Одно из правил выбора пути фильтрации при наличии двунаправленных связей
Влияние этого правила на работу функции USERELATIONSHIP

Проблема двунаправленных связей

Попробую вкратце сформулировать эту проблему так:

Два активных пути между таблицами

Учет транзакций ведется в разрезе счетов в таблице 'Transactions' .
Клиент (таблица 'Customers' ) может управлять несколькими счетами (таблица 'Accounts' ), и у одного счёта может быть несколько владельцев.
Для связи клиентов со счетами в таком случае используется таблица-мост 'AccountsCustomers' , которая содержит в себе пары значений AccountKey — CustomerKey

Модель данных с двунаправленной связью

Мы можем включить двустороннюю фильтрацию двумя способами:

Изменив направление кросс-фильтрации между 'AccountsCustomers' и 'Accounts' на двунаправленное в свойствах связи в модели (как на рисунке), и используя простую меру суммирования по столбцу:

SumOfAmt =
SUM ( Transactions[Amount] )

Используя в мере функцию CROSSFILTER с третьим аргументом Both :

SumOfAmt CF =
CALCULATE (
SUM ( Transactions[Amount] ) ,
CROSSFILTER ( Accounts[AccountKey], AccountsCustomers[AccountKey], BOTH )
)

Оба способа дают нам ответ на поставленный выше вопрос:

Оборот по счетам клиента

В этой модели нет видимой неоднозначности связей – единственный путь от 'Customers' до 'Transactions' не создает альтернатив.

Чтобы создать ситуацию неоднозначности, я немного модифицировал эту упрощенную модель, добавив еще две таблицы:

'Agreements' – справочник договоров, заключенных с клиентами. У одного клиента может быть несколько договоров.
'Addendums' – справочник дополнительных соглашений к договорам. У одного договора может быть несколько дополнительных соглашений.

Также я добавил в таблицу 'Transactions' еще один столбец Transactions[AddendumKey] , который позволяет определить, в соответствии с каким из дополнительных соглашений была проведена транзакция. Теперь в этой таблице одна строка показывает операцию и в разрезе счёта Transactions[AccountKey] , и в разрезе допсоглашения Transactions[AddendumKey] .

В итоге модель приобрела вот такой вид:

Измененная модель данных

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

Теперь между таблицами 'Customers' и 'Transactions' есть два активных пути – через 'Accounts' и через 'Addendums' , и связи в модели очевидно неоднозначные. Попробуйте предположить, не заглядывая вперед, по какому же из путей пойдет фильтрация в данном случае?

Если мы теперь посмотрим на результаты расчетов нашей меры [SumOfAmt] , то можем увидеть следующую картину:

При добавлении новых связей результат расчета меры изменился

Как же понять, почему движком был выбран именно этот путь?

Анализ этой модели и дополнительные изыскания позволили мне сделать вывод о существовании Правила , который подтвердил один из создателей DAX Джеффри Вэнг (Jeffrey Wang):

SumSumOfAmt Old Path =
CALCULATE (
[SumOfAmt],
CROSSFILTER ( Transactions[AddendumKey], Addendums[AddendumKey], NONE )
)

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

Деактивированная двунаправленная связь

SumOfAmtUR =
CALCULATE (
[SumOfAmt],
USERELATIONSHIP ( AccountsCustomers[AccountKey], Accounts[AccountKey] )
)

Активация связи при помощи USERELATIONSHIP ничего не изменила

В общем-то, трудно было ожидать изменения в данном случае:

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

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

Казалось бы – всё на этом, мы разобрались? Отнюдь.

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

Пусть в нашей модели есть две таблицы:

справочник 'Entries' , содержащий в себе ссылки на два разных типа документов: на расходный документ в одном столбце и на приходный документ в другом. Столбец расходных документов Entries[Issue] всегда заполнен уникальными значениями кодов документов, а в столбце приходных документов Entries[Receipt] могут встречаться незаполненные значения.
'Documents' , содержащий в себе уникальный список документов всех видов и дополнительную информацию, которую нам нужно проанализировать.

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

В модели также присутствуют и другие таблицы. Наша задача – построить связи таким образом, чтобы, приходя по связям из других таблиц, фильтрующих таблицу ‘Entries’, получить из таблицы 'Documents' значения, соответствующие либо расходному, либо приходному документу. Иными словами, фильтр должен распространяться от таблицы 'Entries' к таблице 'Documents' в двух вариантах:

от Entries[Issue] к Documents[DocumentID]
от Entries[Receipt] к Documents[DocumentID]

Power BI автоматически создал связь один-к-одному, основываясь на кардинальности столбцов

Неизменяемая двунаправленность этой связи нас вполне устраивает – фильтр вполне может проходить от 'Entries' к 'Documents' , и наша задача будет отчасти решена.

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

SumOfIssues =
SUM ( Documents[Value] )

SumOfReceipts =
CALCULATE (
SUM ( Documents[Value] ) ,
USERELATIONSHIP ( Documents[DocumentID], Entries[Receipt] )
)

Мы ожидаем, что первая мера покажет нам суммы по расходным документам (связь по умолчанию), а вторая покажет суммы по приходным (за счет активации второй связи). Однако результат далёк от ожидаемого:

Почему не сработала USERELATIONSHIP?

В таблице слева мы получили то, что и должны были – для каждого расходного документа посчитана его сумма, а те документы, которые не нашлись в таблице 'Entries' , сгруппировались в пустое значение. Это стандартное поведение.

А вот в правой таблице произошло что-то странное. Если мы еще раз посмотрим на исходные данные, то заметим, что документу E должно соответствовать значение 16, а для F мы должны получить 32. Но мы по-прежнему получили значения для спаренных с E и F расходных документов В и С.

Несмотря на то, что мы активировали связь от столбца Entries[Receipt] , движок проигнорировал наш запрос и по-прежнему считает по первой связи – от Entries[Issue] . Это очень странно, неправда ли?

Давайте вспомним, что мы (думаем что) знаем о связях в таком случае:

Между двумя таблицами может быть только одна активная прямая связь (что вполне логично).
Когда мы используем USERELATIONSHIP для активации отключенной связи между таблицами, другие прямые связи между этими двумя таблицами перестают действовать (тоже логично, иначе противоречило бы пункту 1).

Эти два пункта на самом деле работают в абсолютном большинстве случаев (а в Power Pivot на настоящий момент – наверное, в 100% случаев). Но здесь что-то пошло не так…

Чтобы не ходить вокруг да около, я просто еще раз приведу здесь то Правило, которое мы вывели ранее в этой статье:

Сдвиг парадигмы, неправда ли?

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

Что же делать в таком случае – как победить правило и получить требуемый результат?

На самом деле – довольно просто, и для этого есть даже не одно решение:

SumOfReceipts CF =
CALCULATE (
SUM ( Documents[Value] ) ,
USERELATIONSHIP ( Documents[DocumentID], Entries[Receipt] ) ,
CROSSFILTER ( Documents[DocumentID], Entries[Issue], NONE )
)

Теперь обе связи имеют одинаковую кардинальность

В обоих случаях мы получим нужный результат — фильтрация заработает так, как нам нужно:

Использование функции CROSSFILTER дает тот же результат, что и изменение кардинальности связи

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

В заключение хочу привести еще одну цитату из статьи Альберто Феррари:

The fun part is not in analyzing the numbers; the fun part lies in finding the path that DAX had to discover within the maze to find the exit.

Действительно, DAX нам всегда что-то посчитает (в крайнем случае, выдаст ошибку, если мы грубо ошибемся), но мы должны понимать, что же именно он посчитал. А знание – сила!

Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу t_sales. Но вся прелесть Power Pivot в том, что с его помощью можно производить расчёты, комбинируя данные из нескольких таблиц. По моему личному мнению, если бы даже Powe Pivot не имел встроенного движка функций DAX, одна только способность связывания таблиц, уже оправдывала бы его существование.

Создание связей

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

Либо по кнопке "Представление диаграммы" на вкладке "Главная".

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

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

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

Но можно ли создавать связь между таблицами по любым идентичным столбцам? Например столбцы "ЦенаЗаШтуку" в t_sales и "Цена" в t_products содержат одинаковые данные. Попробуем создать между ними связь путём перетаскивания. Power Pivot выдаст ошибку: "Не удалось создать связь, поскольку в каждом столбце содержатся повторяющиеся значения. Выберите по крайней мере один столбец, содержащий только уникальные значения."

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

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

Ниже представлена сводная таблица на основе данных таблицы t_sales.

Теперь, после того как мы установили связь между таблицами t_sales и t_products, попробуем в поле Строки сводной таблицы вместо столбцац КодПродукта поставить столбец АнглийскоеНазваниеМодели из таблицы t_products.

Как видим всё работает. Теперь мы можем комбинировать данные из обоих таблиц в одной сводной таблице!!

Как это работает

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

Далее, коды продуктов соответствующие цвету "Red" через установленную связь, из таблицы поиска (t_products) передаются основной таблице (t_sales), которая на основании полученных данных применяет фильтры к столбцу КодПродукта.

Функция CALCULATE () и связанные таблицы

Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.

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

Как Вы надеюсь поняли из вышеприведённого примера, при работе со связанными таблицами фильтр-аргументы функции CALCULATE() можно применять и к таблицам поиска

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

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

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

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

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

Также эту формулу можно ввести вручную. Она должна выглядеть так: = ! , где вместо подставьте имя исходного листа.

Например, если вы извлекаете данные из ячейки D12 на листе Лист1, формула будет выглядеть так: =Лист1!D12 .

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

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

Как связать две таблицы одной формулой для выборки ВПР по условию

Чтобы создать переключатель между таблицами можно использовать имена диапазонов ячеек и функцию ДВССЫЛ. После чего нужно составить формулу. Необходимо сначала создать два именных диапазона:

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

= Мир MS Excel/Статьи об Excel

Приёмы работы с книгами, листами, диапазонами, ячейками [6]

Приёмы работы с формулами [13]

Настройки Excel [3]

Инструменты Excel [4]

Интеграция Excel с другими приложениями [4]

Форматирование [1]

Выпадающие списки [2]

Примечания [1]

Сводные таблицы [1]

Гиперссылки [1]

Excel и интернет [1]

Excel для Windows и Excel для Mac OS [2]

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

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

1 способ. Через буфер обмена
Это самый простой и очевидный способ. Выделяем на листе Excel диапазон ячеек или диаграмму, любым способом выполняем команду Копировать, переходим в документ Word и выполняем команду Вставить. При этом таблица вставляется как таблица Word с возможностью редактирования средствами Word, а диаграмма в версиях до Word 2007 включительно вставляется как внедрённый объект (см. ниже) , а начиная с Word 2010 - как рисунок . Чтобы диаграмма начиная с Word 2010 вставилась как внедренный объект, следует использовать Параметры вставки

и в раскрывшемся диалоговом окне на вкладке Создание ищем в списке строчку Лист Microsoft Excel и нажимаем ОК

После этого на странице документа мы видим фрагмент листа Excel, а также ленту с вкладками (или меню) Excel.

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

В версиях до Word 2003 включительно можно использовать кнопку Добавить таблицу Excel на Стандартной панели инструментов.

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

Если мы хотим создать внедрённую диаграмму, то в диалоговом окне Вставка объекта на вкладке Создание выбираем строчку Диаграмма Microsoft Excel. В этом случае будет создан не один лист Excel, а два: на первом будет пример диаграммы, а на втором - исходные данные для неё, которые следует заменить своими исходными данными.

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

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

Для этого на вкладке Вставка в группе Текст нажимаем кнопку Объект и в раскрывшемся диалоговом окне переходим на вкладку Создание из файла. С помощью кнопки Обзор находим в Проводнике нужный файл Excel и нажимаем ОК.

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

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

В чём отличие? Если Вы перемещаете размерные маркеры, находясь в режиме редактирования объекта, то Вы увеличиваете/уменьшаете количество видимых строк/столбцов.

Если же Вы ту же самую операцию делаете, не входя в режим редактирования, то Вы просто растягиваете/сжимаете рисунок

Создать внедрённый объект из файла можно также, перетащив мышкой значок файла Excel на страницу документа Word

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

В этом случае следует вернуться в режим редактирования и обратить внимание, какая ячейка осталась активной.

Выделяем на листе Excel диапазон ячеек или диаграмму, выполняем команду Копировать, переходим в документ Word и даём команду Специальная вставка. Размещение этой команды зависит от версии Word.

В версиях до Word 2003 включительно она находится в меню Правка. Начиная с Word 2007 эту команду можно найти в раскрывающемся списке кнопки Вставить на вкладке Главная. Кроме того, начиная с Word 2010 в контекстном меню присутствует команда Параметры вставки, с помощью которой можно выбрать варианты связывания.

Для установления связи с файлом источником при создании объекта из файла достаточно в диалоговом окне Вставка объекта поставить флажок Связь с файлом

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

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

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

Как видим, в этом диалоговом окне есть возможность Сменить источник , а также Разорвать связь , если таковая больше не требуется. Кроме того, здесь же можно выбрать способ обновления связи: автоматически или по запросу .

В версии до Word 2007 это диалоговое окно также открывается при выполнении команды меню Правка -- Связи.

В Word 2007 его можно открыть, нажав кнопку Office и выбрав команду Подготовить -- Изменить ссылки на файлы.

Начиная с Word 2010 для этого выбираем вкладку Файл -- Сведения -- Связанные документы -- Изменить связи с файлами.

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