Связанные таблицы в Excel, как их сделать? Простые советы Блог Ивана Кунпана
Оглавление:
- Связанные таблицы в Excel, что это такое и зачем с ними работать
- Как связать две таблицы в Excel, варианты
- Заключение
Как сделать связанные таблицы в Excel? В статье будет раскрыт ответ на вопрос. С помощью простой инструкции мы соединим таблицы.
Связанные таблицы в Excel, что это такое и зачем с ними работатьЗдравствуйте друзья! Во время работы с таблицами Excel приходится их связывать. Что такое связанные таблицы в Эксель? Их называют Эксель таблицами, с помощью которых переносится информация с одной таблицы на другую таблицу.
С помощью связанных таблиц, можно легко работать с большими таблицами. Например, быстро проводить по ним поиск нужных Вам данных, менять различные значения, добавлять в них символы, текст и так далее.
А слишком большая таблица,занимает много времени в работе, когда в ней пишешь или ищешь какую-нибудь информацию.
Связанные таблицы могут разделяться на несколько листов и работа с ними будет проходить гораздо быстрее. Далее, будем разбирать инструкцию, которая поможет в создании связанных таблиц.
к оглавлению ↑
Как связать две таблицы в Excel, вариантыРазберём два эффективных варианта создания связанных Эксель таблиц. В начале работы, создайте на своём компьютере две таблицы. Например, первая таблица будет с таким названием, как «Даты», а вторая таблица пустая.
Вместе с тем, запускаете на компьютере ещё одну таблицу – «Даты» и записываете в неё значения. Например, «0.1.11.2019» Январь и так далее. Выделяете столбцы в таблице с любой информацией и нажимаете правой кнопкой мыши – «Копировать» (Скрин 1).
Запускаем второй лист таблицы Excel. Затем, нужно кликнуть в любую ячейку таблицы компьютерной мышкой и кликните кнопки – «Вставить» и «Вставить связь» (Скрин 2).
После их нажатия, в Вашей таблице появятся данные из другой таблицы (Скрин 3).
Таким образом она приобретёт связь с другой таблицей. Это способ одиночный. Вам придётся несколько раз нажимать на рассмотренные выше кнопки. Поэтому, рассмотрим ещё один метод, как связать две таблицы в Excel по пошаговой инструкции.
Запустите на своём компьютере пустую Excel таблицу. Далее, в ней нажмите кнопку – «Вставка» затем, «Объект» (Скрин 4).
Выбираете из появившегося окна раздел – «Из файла». Далее, нажимаете кнопку «Обзор» и загружаете другую таблицу Эксель нажатием кнопки «Вставить». После этого, Вы сможете соединить сразу две таблицы.
к оглавлению ↑
ЗаключениеВ статье мы научились создавать связанные таблицы в Excel. Мы разобрали два способа, которые наиболее эффективны для новичков. Конечно, есть и другие варианты. Например, вставить таблицу в Эксель, через кнопки «Файл» и «Открыть» или с помощью формул.
Эти практические советы Вам должны помочь в соединении двух таблиц Excel. Спасибо за внимание, удачи Вам!
С уважением, Иван Кунпан.
P.S. Практические статьи по работе с Excel-таблицами:
(Excel-таблицы для бизнеса, как создать формулу в таблице, эксель онлайн, как перенести Эксель в Гугл).
Просмотров: 6518
Создание связанных таблиц
Скачать файл-примера
Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу 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.
Далее, коды продуктов соответствующие цвету «Red» через установленную связь, из таблицы поиска (t_products) передаются основной таблице (t_sales), которая на основании полученных данных применяет фильтры к столбцу КодПродукта.
ВАЖНО!
Фильтры, применённые к таблице поиска передаются через установленную связь основной таблице.
Однако, фильтры применённые к основной таблице, не передаются таблице поиска.
Функция CALCULATE () и связанные таблицы
Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.
В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении каждого клиента. Создадим меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детьми:
[ПродажиРодителям]=
CALCULATE([ИтогоПродаж],
t_clients[КоличествоДетейНаПопечении]>0)
Как Вы надеюсь поняли из вышеприведённого примера, при работе со связанными таблицами фильтр-аргументы функции CALCULATE() можно применять и к таблицам поиска
Отношения между таблицами в модели данных
Расширьте возможности анализа данных, создав связи между разными таблицами.
Связь — это связь между двумя таблицами, содержащими данные: один столбец в каждой таблице является основой для связи. Чтобы понять, почему отношения полезны, представьте, что вы отслеживаете данные о заказах клиентов в своем бизнесе. Вы можете отслеживать все данные в одной таблице, имеющей такую структуру:идентификатор клиента | Имя | Электронная почта | Дисконтная ставка | ID заказа | Дата заказа | Товар | Количество |
|---|---|---|---|---|---|---|---|
1 | Эштон | chris. | .05 | 256 | 07.01.2010 | Компактный цифровой | 11 |
1 | Эштон | .05 | 255 | 03.01.2010 | Зеркальная камера | 15 | |
2 | michal. | .10 | 254 | 03.01.2010 | Бюджетный кинопроизводитель | 27 |
Этот подход может работать, но требует хранения большого количества избыточных данных, таких как адрес электронной почты клиента для каждого заказа. Хранение стоит дешево, но если адрес электронной почты изменится, вы должны убедиться, что обновили каждую строку для этого клиента. Одним из решений этой проблемы является разделение данных на несколько таблиц и определение отношений между этими таблицами. Этот подход используется в реляционных базах данных, таких как SQL Server. Например, импортируемая база данных может представлять данные о заказах с помощью трех связанных таблиц:
Клиенты
[идентификатор клиента] | Имя | Электронная почта |
|---|---|---|
1 | Эштон | chris. |
2 | Яворски |
Скидки для клиентов
[идентификатор клиента] | Дисконтная ставка |
|---|---|
1 | .05 |
2 | .10 |
Заказов
[идентификатор клиента] | ID заказа | Дата заказа | Продукт | Количество |
|---|---|---|---|---|
1 | 256 | 07. | Компактный цифровой | 11 |
1 | 255 | 03.01.2010 | Зеркальная камера | 15 |
2 | 254 | 03.01.2010 | Бюджетный кинопроизводитель | 27 |
01.2010 Отношения существуют в модели данных, которую вы явно создаете или которую Excel автоматически создает от вашего имени, когда вы одновременно импортируете несколько таблиц.
Вы также можете использовать надстройку Power Pivot для создания модели или управления ею. Дополнительные сведения см. в разделе Создание модели данных в Excel.
Если вы используете надстройку Power Pivot для импорта таблиц из той же базы данных, Power Pivot может обнаруживать отношения между таблицами на основе столбцов, которые находятся в [скобках], и может воспроизводить эти отношения в модели данных, которую он создает. за кулисами. Дополнительные сведения см. в разделе Автоматическое обнаружение и вывод отношений в этой статье. Если вы импортируете таблицы из нескольких источников, вы можете вручную создать связи, как описано в разделе Создание связи между двумя таблицами.
Связи основаны на столбцах в каждой таблице, содержащих одни и те же данные. Например, вы можете связать таблицу Customers с таблицей Orders , если каждая из них содержит столбец, в котором хранится идентификатор Customer ID . В примере имена столбцов совпадают, но это не обязательно.
Одним из них может быть CustomerID, а другим — CustomerNumber, если все строки в таблице «Заказы» содержат идентификатор, который также хранится в таблице «Клиенты».
В реляционной базе данных есть несколько типов ключей. Ключ обычно представляет собой столбец со специальными свойствами. Понимание назначения каждого ключа может помочь вам управлять моделью данных с несколькими таблицами, которая предоставляет данные для сводной таблицы, сводной диаграммы или отчета Power View.
Несмотря на то, что существует множество типов ключей, для нашей цели здесь наиболее важны следующие:
Первичный ключ: однозначно идентифицирует строку в таблице, например CustomerID в таблице Customers .
Альтернативный ключ (или ключ-кандидат): уникальный столбец, отличный от первичного ключа.
Например, в таблице «Сотрудники» может храниться идентификатор сотрудника и номер социального страхования, оба из которых уникальны.Внешний ключ: столбец, ссылающийся на уникальный столбец в другой таблице, например CustomerID в таблице Orders , который ссылается на CustomerID в таблице Customers.
Например, в таблице «Сотрудники» может храниться идентификатор сотрудника и номер социального страхования, оба из которых уникальны. В модели данных первичный или альтернативный ключ называется связанным столбцом. Если таблица имеет как первичный, так и альтернативный ключ, вы можете использовать любой из них в качестве основы для связи между таблицами. Внешний ключ называется исходным столбцом или просто столбцом. В нашем примере отношения будут определены между CustomerID в таблице Orders (столбец) и CustomerID в таблице Customers (столбец поиска). Если вы импортируете данные из реляционной базы данных, по умолчанию Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы.
Однако вы можете использовать любой столбец с уникальными значениями для столбца подстановки.
Отношение между покупателем и заказом является отношением «один ко многим». У каждого клиента может быть несколько заказов, но у заказа не может быть несколько клиентов. Еще одна важная связь между таблицами — один к одному. В нашем примере здесь Таблица CustomerDiscounts , которая определяет единую ставку скидки для каждого клиента, имеет отношение «один к одному» с таблицей «Клиенты».
В этой таблице показаны отношения между тремя таблицами ( Customers, CustomerDiscounts и Orders ):
Отношения | Тип | Столбец поиска | Столбец |
|---|---|---|---|
Клиенты-КлиентыСкидки | один к одному | Клиенты. | CustomerDiscounts.CustomerID |
Клиенты-заказы | один ко многим | Клиенты.CustomerID | Заказы.CustomerID |
CustomerIDПримечание. Отношения «многие ко многим» не поддерживаются в модели данных. Примером связи «многие ко многим» является прямая связь между «Продукты» и «Клиенты», в которой клиент может купить много продуктов, и один и тот же продукт может быть куплен многими клиентами.
После создания любой связи Excel обычно должен пересчитать все формулы, использующие столбцы из таблиц во вновь созданной связи.
Обработка может занять некоторое время, в зависимости от объема данных и сложности взаимосвязей. Дополнительные сведения см. в разделе Пересчет формул.
Модель данных может иметь несколько отношений между двумя таблицами. Для построения точных вычислений Excel требуется единый путь от одной таблицы к другой. Поэтому одновременно активна только одна связь между каждой парой таблиц. Хотя другие неактивны, вы можете указать неактивную связь в формулах и запросах.
В представлении диаграммы активные отношения отображаются сплошной линией, а неактивные — пунктирными. Например, в AdventureWorksDW2012 таблица DimDate содержит столбец DateKey , который связан с тремя разными столбцами в таблице FactInternetSales : OrderDate, DueDate и ShipDate . Если активная связь находится между DateKey и OrderDate, это связь по умолчанию в формулах, если не указано иное.
Связь может быть создана при соблюдении следующих требований:
Критерии | Описание |
|---|---|
Уникальный идентификатор для каждой таблицы | В каждой таблице должен быть один столбец, однозначно идентифицирующий каждую строку в этой таблице. |
Уникальные столбцы поиска | Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликатов. В модели данных нули и пустые строки эквивалентны пробелу, который является отдельным значением данных. Это означает, что в столбце поиска не может быть нескольких нулей. |
Совместимые типы данных | Типы данных в исходном столбце и столбце поиска должны быть совместимы. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые в моделях данных. |
Этот столбец часто называют первичным ключом. В модели данных нельзя создать связь между таблицами, если ключ является составным.
Вы также ограничены созданием отношений один-к-одному и один-ко-многим. Другие типы отношений не поддерживаются.
Составные ключи и столбцы поиска
Составной ключ состоит из нескольких столбцов. Модели данных не могут использовать составные ключи: в таблице всегда должен быть ровно один столбец, который однозначно идентифицирует каждую строку в таблице. Если вы импортируете таблицы, имеющие существующую связь на основе составного ключа, мастер импорта таблиц в Power Pivot проигнорирует эту связь, поскольку ее невозможно создать в модели.
Чтобы создать связь между двумя таблицами с несколькими столбцами, определяющими первичный и внешний ключи, сначала объедините значения, чтобы создать один ключевой столбец, прежде чем создавать связь. Это можно сделать перед импортом данных или путем создания вычисляемого столбца в модели данных с помощью надстройки Power Pivot.
Отношения «многие ко многим»
Модель данных не может иметь отношения «многие ко многим».
Вы не можете просто добавить соединительные таблицы в модель. Однако вы можете использовать функции DAX для моделирования отношений «многие ко многим».
Самосоединения и циклы
Самостоятельные соединения не разрешены в модели данных. Самосоединение — это рекурсивная связь между таблицей и самой собой. Самосоединения часто используются для определения иерархий родитель-потомок. Например, вы можете соединить таблицу «Сотрудники» с самой собой, чтобы создать иерархию, показывающую цепочку управления в компании.
Excel не позволяет создавать циклы между отношениями в книге. Другими словами, следующий набор отношений запрещен.
Таблица 1, столбец a – Таблица 2, столбец f
Таблица 2, столбец f – Таблица 3, столбец n в результате создается цикл, генерируется ошибка.
Одним из преимуществ импорта данных с помощью надстройки Power Pivot является то, что Power Pivot иногда может обнаруживать связи и создавать новые связи в модели данных, создаваемой в Excel.
При импорте нескольких таблиц Power Pivot автоматически обнаруживает любые существующие отношения между таблицами. Кроме того, при создании сводной таблицы Power Pivot анализирует данные в таблицах. Он обнаруживает возможные отношения, которые не были определены, и предлагает соответствующие столбцы для включения в эти отношения.
Алгоритм обнаружения использует статистические данные о значениях и метаданных столбцов, чтобы делать выводы о вероятности взаимосвязей.
Типы данных во всех связанных столбцах должны быть совместимы. Для автоматического обнаружения поддерживаются только целые числа и текстовые типы данных. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые в моделях данных.
Чтобы связь была успешно обнаружена, количество уникальных ключей в столбце поиска должно быть больше, чем значения в таблице на стороне многих.
Другими словами, ключевой столбец на стороне отношения «многие» не должен содержать значений, отсутствующих в ключевом столбце таблицы поиска. Например, предположим, что у вас есть таблица, в которой перечислены продукты с их идентификаторами (таблица поиска), и таблица продаж, в которой перечислены продажи для каждого продукта (многосторонняя связь). Если ваши записи о продажах содержат идентификатор продукта, который не имеет соответствующего идентификатора в таблице «Продукты», связь не может быть создана автоматически, но вы можете создать ее вручную. Чтобы Excel обнаружил взаимосвязь, необходимо сначала обновить таблицу поиска продуктов, указав идентификаторы отсутствующих продуктов.Убедитесь, что имя ключевого столбца на стороне многих совпадает с именем ключевого столбца в таблице поиска. Имена не обязательно должны быть одинаковыми. Например, в бизнес-настройках у вас часто есть варианты имен столбцов, которые содержат по существу одни и те же данные: Emp ID, EmployeeID, Employee ID, EMP_ID и т.
д. Алгоритм обнаруживает похожие имена и присваивает более высокую вероятность тем столбцам, которые имеют похожие или точно совпадающие имена. Поэтому, чтобы увеличить вероятность создания связи, вы можете попробовать переименовать столбцы в данных, которые вы импортируете, во что-то похожее на столбцы в ваших существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.
Другими словами, ключевой столбец на стороне отношения «многие» не должен содержать значений, отсутствующих в ключевом столбце таблицы поиска. Например, предположим, что у вас есть таблица, в которой перечислены продукты с их идентификаторами (таблица поиска), и таблица продаж, в которой перечислены продажи для каждого продукта (многосторонняя связь). Если ваши записи о продажах содержат идентификатор продукта, который не имеет соответствующего идентификатора в таблице «Продукты», связь не может быть создана автоматически, но вы можете создать ее вручную. Чтобы Excel обнаружил взаимосвязь, необходимо сначала обновить таблицу поиска продуктов, указав идентификаторы отсутствующих продуктов.
д. Алгоритм обнаруживает похожие имена и присваивает более высокую вероятность тем столбцам, которые имеют похожие или точно совпадающие имена. Поэтому, чтобы увеличить вероятность создания связи, вы можете попробовать переименовать столбцы в данных, которые вы импортируете, во что-то похожее на столбцы в ваших существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.Эта информация может помочь вам понять, почему обнаруживаются не все взаимосвязи или как изменения в метаданных, таких как имя поля и типы данных, могут улучшить результаты автоматического обнаружения взаимосвязей. Дополнительные сведения см. в разделе Устранение неполадок отношений.
Автоматическое определение именованных наборов
Отношения не обнаруживаются автоматически между именованными наборами и соответствующими полями в сводной таблице. Вы можете создать эти отношения вручную. Если вы хотите использовать автоматическое определение отношений, удалите каждый именованный набор и добавьте отдельные поля из именованного набора непосредственно в сводную таблицу.
Вывод отношений
В некоторых случаях связи между таблицами автоматически связываются. Например, если вы создаете связь между первыми двумя наборами таблиц ниже, предполагается, что связь существует между двумя другими таблицами, и связь устанавливается автоматически.
Товары и категории — созданы вручную
Категория и подкатегории — созданы вручную
Товары и подкатегории — связь предполагается
Для того, чтобы отношения были автоматически объединены в цепочку, отношения должны идти в одном направлении, как показано выше. Если первоначальные отношения были между, например, продажами и продуктами и продажами и клиентами, связь не выводится. Это связано с тем, что отношения между продуктами и клиентами представляют собой отношения «многие ко многим».
Как использовать модель данных и взаимосвязи Excel » Chandoo.org
Вы когда-нибудь были в аду ВПР?
Это то, что происходит , когда вам нужно написать много формул vlookup, прежде чем вы сможете начать анализировать свои данные .
Каждый день миллионы аналитиков и менеджеров попадают в ад ВПР и страдают. Они соединяют таблицу 1 с таблицей 2, чтобы все данные, необходимые для создания этого сводного отчета, находились в одном месте. Если вы один из них, то вам понравится функция модели данных и отношений Excel.
Проще говоря, эта функция помогает вам соединить один набор данных с другим набором данных, чтобы вы могли создавать комбинированные сводные отчеты.
Практический пример — V(X)LOOKUP hell против модели данных Heaven
Допустим, вы ищете данные о продажах для своей компании. У вас есть данные транзакции, как показано ниже.
И вы хотите узнать, сколько единиц вы продаете по категориям продуктов и полу покупателей.
К сожалению, у вас есть только идентификатор продукта и идентификатор клиента.
С функцией VLOOKUP Hell,
- Сначала вы извлекаете все данные о клиентах и продуктах и помещаете их в отдельные диапазоны.
- Затем напишите формулу vlookup для получения категории продукта, а другую — для определения пола клиента.
- Затем заполните формулы для всего списка транзакций.
- Теперь сделайте сводную таблицу.
Предполагая, что у вас есть 30 000 транзакций, вам нужно написать 60 000 формул ВПР, чтобы создать этот отчет !!!
С небом модели данных,
- Создание взаимосвязей между таблицами «Продажи», «Продукты» и «Клиенты»
- Создание сводной таблицы
Создание отношений в Excel – пошаговое руководство
- Сначала настройте свои данные в виде таблиц. Чтобы создать таблицу, выберите любую ячейку в диапазоне и нажмите CTRL+T. Укажите имя для своей таблицы на вкладке «Дизайн». Прочтите введение в таблицы Excel, чтобы понять больше.
- Теперь перейдите на ленту данных и нажмите кнопку отношений.
- Щелкните Создать , чтобы создать новую связь.
- Выберите исходную таблицу и имя столбца. Сопоставьте его с целевой таблицей и именем столбца. Неважно, какой порядок вы используете здесь. Excel достаточно умен, чтобы настроить отношения.
- При необходимости добавьте дополнительные связи.
Использование отношений в сводных отчетах и анализе
- Выберите любую таблицу и вставьте сводную таблицу (Вставка > Сводная таблица, подробнее о сводных таблицах).
- Убедитесь, что установлен флажок «Добавить эти данные в модель данных».
- В списке полей сводной таблицы отметьте «ВСЕ» вместо «АКТИВНО», чтобы увидеть все имена таблиц.
- Выбор полей из разных таблиц для создания комбинированного сводного отчета или сводной диаграммы
Пример: Отчет о продажах по категориям и полу
- Добавьте категорию к меткам строк
- Добавьте пол к меткам столбцов
- Добавьте количество к значениям
- и ваш отчет готов!
О чем следует помнить при использовании взаимосвязей
- Одинаковые типы данных в обоих столбцах: Соединяемые столбцы в обеих таблицах должны иметь одинаковый тип данных (т. е. числа, даты, текст и т. д.)
- Только отношения «один к одному» или «один ко многим»: Excel 2013 поддерживает только отношения один ко многим или один к одному. Это означает, что в одной из таблиц не должно быть повторяющихся значений в столбце, на который вы ссылаетесь. (например, в таблице продуктов не должно быть повторяющихся идентификаторов продуктов).
- Вы также можете добавить срезы: Вы можете нарезать эти сводные таблицы по любому полю (так же, как обычные сводные таблицы). Например, вы можете дополнительно нарезать приведенный выше отчет по профессии клиента или размеру артикула продукта.
е. числа, даты, текст и т. д.)Преимущества сводных таблиц на основе модели данных
Если у вас есть модель данных в электронной таблице, вы получите несколько преимуществ (кроме сводных таблиц для нескольких таблиц). Вот они:
- Отдельные числа: Это простое, но часто сложное для вычисления число легко получить, если у вас есть опорная точка на основе модели данных. Просто перейдите к настройкам поля значений и измените тип сводки на «Число уникальных». Вот совет, объясняющий , как получить различные значения в сводках Excel .
- Показатели и DAX: Получив модель данных, вы можете использовать все функции Power Pivot в своей книге. Вы можете создавать показатели (используя язык DAX) и вычислять то, что иначе невозможно в обычном Excel. Вот пример процент чего-то, вычисляемого с помощью DAX & Data Model , для начала.
- Сводные данные из данных в других файлах и базах данных : вы можете комбинировать модель данных с возможностями Power Query для создания сводных данных из данных в других местах. Например, вы можете сделать сводку данных о продажах в SAP с данными о клиентах в системе CRM. Вот обзор того, что такое Power Query?
- Сводки из более чем 1 млн строк данных: Вы можете подключаться к очень большим наборам данных и делать из них сводки с помощью модели данных. Вот демонстрация того, как настроить модель данных для 1+ млн строк данных.
- Преобразование сводных таблиц в формулы: Если у вас есть сводная таблица на основе модели данных, вы можете преобразовать ее в набор формул. Вы можете получить доступ к этой функции с ленты «Анализ». Это заменит ваш пивот кучей формул CUBE. Вот обзор формул CUBE.
Просто перейдите к настройкам поля значений и измените тип сводки на «Число уникальных». Вот совет, объясняющий , как получить различные значения в сводках Excel .
Вот демонстрация того, как настроить модель данных для 1+ млн строк данных.Недостатки модели данных:
Конечно, это не все кексы и кофе с моделью данных. У сводных таблиц на основе модели данных есть несколько недостатков.
- Совместимость: Функция модели данных и отношений доступна только в Excel 2013 или более поздней версии. Это означает, что вы не можете создавать такие сводные отчеты или делиться ими с людьми, использующими более ранние версии Excel .
- Невозможно сгруппировать данные: В обычных сводных таблицах можно группировать числовые поля, поля данных или текстовые поля. Но со сводными таблицами модели данных вы больше не можете группировать данные.
