Как удалить модель данных из книги Excel

Обновлено: 21.11.2024

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

Сообщить о нарушении

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

Ответы (5) 

Попробуйте нажать кнопку функции «Управление моделью данных» на вкладке «Данные»> группа «Инструменты данных», затем щелкнуть правой кнопкой мыши ненужную таблицу> «Удалить», чтобы удалить ее.

Если вы не видите кнопку «Управление моделью данных», попробуйте добавить ее на ленту, щелкнув вкладку «Файл»> «Параметры»> «Настроить ленту»> «Выбрать команды из всех команд»> «Найти «Управление моделями данных», а затем добавьте ее на ленту.

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

<р>1. Скриншот страницы с информацией о продукте Excel на вкладке "Файл" > "Учетная запись".

<р>2. Как вы импортировали эти таблицы в свою книгу? Предоставьте подробные инструкции, чтобы мы могли протестировать это на нашей стороне.

Сообщить о нарушении

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

9 человек сочли этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Вы проверили приведенную выше информацию?

Сообщить о нарушении

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

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

Когда я делаю то, что вы мне предложили, я просто нашел две таблицы, и насколько я понимаю, эти две единственные, которые интегрируют WDM, которые представляют собой необработанные данные, только две вверху, а не все те, которые вы видите на изображении ниже. Я имею в виду, что под «Таблицами в модели данных рабочей книги» находятся таблицы и сводные таблицы, которые я создал на основе этих первых двух таблиц необработанных данных, но опять же, когда я открываю «Управление моделью данных», я их не вижу, я не могу найти любой из них, поэтому я не смог удалить те, которые мне не нужны.

Данные>Существующие соединения>Таблицы вкладок… Итак, еще раз Допустим, я хочу удалить приведенную ниже таблицу MCSI Sales_6mths, как вы можете видеть ниже, я открыл «Управление моделью данных», но не нашел ее там. Я попытался удалить таблицу на листе/вкладке, содержащую информацию непосредственно в книге Excel, но это вызвало проблему при синхронизации книги (WDM рухнул). Пожалуйста, сообщите, надеюсь, что мой ответ и обмен информацией достаточно ясны для вас, чтобы помочь мне. Мой вопрос в том, как мне это удалить

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

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

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

Нажмите Данные > Отношения.

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

Нажмите "Удалить".

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

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

Запуск в Power Pivot

Нажмите Главная > Диаграмма.

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

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

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

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

СВЯЗАННАЯ функция выражения анализа данных (DAX) использует отношения между таблицами для поиска связанных значений в другой таблице. Он будет возвращать разные результаты после удаления отношения. Для получения дополнительной информации см. СВЯЗАННЫЕ функции.

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

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

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

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

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

Предположим, у вас есть два рабочих листа в вашей книге –

Один, содержащий данные о продавцах и регионах, которые они представляют, в таблице "Продавец".

Другой, содержащий данные о продажах по регионам и месяцам, в виде таблицы – Продажи.

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

Нажмите на таблицу – Продажи.

Перейдите на вкладку "ВСТАВИТЬ" на ленте.

Выберите "Сводная таблица" в группе "Таблицы".

Будет создана пустая сводная таблица с полями из таблицы «Продажи» — «Регион», «Месяц» и «Сумма заказа». Как вы можете заметить, под списком полей сводной таблицы есть команда MORE TABLES.

Нажмите ДОПОЛНИТЕЛЬНЫЕ ТАБЛИЦЫ.

Появится окно сообщения "Создать новую сводную таблицу". Отображаемое сообщение: Чтобы использовать несколько таблиц в анализе, необходимо создать новую сводную таблицу с использованием модели данных. Нажмите Да

Новая сводная таблица будет создана, как показано ниже —

В полях сводной таблицы есть две вкладки — АКТИВНЫЕ и ВСЕ.

Перейдите на вкладку ВСЕ.

Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.

Нажмите поле "Продавец" в таблице "Продавец" и перетащите его в область ROWS.

Нажмите поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.

Нажмите поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ VALUES.

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

Нажмите кнопку СОЗДАТЬ рядом с сообщением. Появится диалоговое окно «Создать отношение».

В разделе "Таблица" выберите "Продажи".

В поле "Столбец (иностранный)" выберите "Регион".

В разделе "Связанная таблица" выберите "Продавец".

В поле "Связанный столбец (основной)" выберите "Регион".

Ваша сводная таблица из двух таблиц на двух листах готова.

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

Перейдите на вкладку POWERPIVOT на ленте.

Нажмите «Управление» в группе «Модель данных». Появится представление данных Power Pivot.

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

Добавление таблиц Excel из другой книги в модель данных

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

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

Нажмите на таблицу "Продажи".

Перейдите на вкладку "ВСТАВИТЬ".

Нажмите "Сводная таблица" в группе "Таблицы". Появится диалоговое окно «Создать сводную таблицу».

В поле "Таблица/диапазон" введите "Продажи".

Нажмите "Новый рабочий лист".

Установите флажок Добавить эти данные в модель данных.

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

Вы добавили данные таблицы Sales в модель данных. Затем вы должны получить данные таблицы «Продавец» также в модели данных следующим образом —

Нажмите на рабочий лист, содержащий таблицу "Продажи".

Перейдите на вкладку ДАННЫЕ на ленте.

Нажмите «Существующие подключения» в группе «Получить внешние данные». Появится диалоговое окно «Существующие подключения».

Перейдите на вкладку "Таблицы".

В разделе "Модель данных этой рабочей книги" отображается 1 таблица (это таблица "Продажи", которую вы добавили ранее). Вы также найдете две рабочие книги, в которых отображаются таблицы.

Нажмите "Продавец" в разделе "Продавец.xlsx".

Нажмите «Открыть». Появится диалоговое окно «Импорт данных».

Нажмите "Отчет сводной таблицы".

Нажмите "Новый рабочий лист".

Вы можете видеть, что флажок Добавить эти данные в модель данных установлен и неактивен. Нажмите "ОК".

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

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

Добавление таблиц Excel в модель данных из ленты PowerPivot

Еще один способ добавления таблиц Excel в модель данных — это сделать это с помощью ленты PowerPivot.

Предположим, у вас есть два рабочих листа в вашей книге –

Одна, содержащая данные продавцов и регионов, которые они представляют, в виде таблицы — Продавец.

Другой, содержащий данные о продажах по регионам и месяцам, в виде таблицы – Продажи.

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

Нажмите на таблицу Excel – Продажи.

Перейдите на вкладку POWERPIVOT на ленте.

Нажмите «Добавить в модель данных» в группе «Таблицы».

Появится окно Power Pivot с добавленной в него таблицей данных Salesperson. Далее на ленте в окне Power Pivot появится вкладка «Связанная таблица».

Нажмите на вкладку "Связанная таблица" на ленте.

Нажмите Таблицу Excel: Продавец.

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

Нажмите "Перейти к таблице Excel".

Откроется окно Excel с рабочим листом, содержащим таблицу "Продавец".

Перейдите на вкладку "Продажи".

Нажмите на таблицу "Продажи".

Нажмите «Добавить в модель данных» в группе «Таблицы» на ленте.

Таблица Excel "Продажи" также добавляется в модель данных.

Если вы хотите провести анализ на основе этих двух таблиц, как вы знаете, вам необходимо создать связь между двумя таблицами данных. В Power Pivot это можно сделать двумя способами:

Из представления схемы

Создание отношений из представления данных

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

Перейдите на вкладку "Дизайн" в окне Power Pivot.

Нажмите «Создать связь» в группе «Отношения». Появится диалоговое окно «Создать отношение».

Нажмите «Продажи» в поле «Таблица». Это таблица, с которой начинаются отношения. Как вы знаете, столбец должен быть полем, присутствующим в связанной таблице «Продавец», которое содержит уникальные значения.

Нажмите «Регион» в поле «Столбец».

Нажмите «Продавец» в поле «Связанная связанная таблица».

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

Нажмите кнопку "Создать". Связь создана.

Создание связей из представления диаграммы

Создание отношений в представлении схемы относительно проще. Следуйте указанным шагам.

Перейдите на вкладку "Главная" в окне Power Pivot.

Нажмите «Просмотр диаграммы» в группе «Вид».

Представление схемы модели данных отображается в окне Power Pivot.

Нажмите «Регион» в таблице «Продажи». Регион в таблице продаж выделен.

Перетащите в регион в таблице продавцов. Регион в таблице продавцов также выделен. В направлении перетаскивания появится линия.

Появится строка из таблицы "Продажи" в таблицу "Продавец", указывающая связь.

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

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

Управление отношениями

Вы можете изменить или удалить существующую связь в модели данных.

Перейдите на вкладку "Дизайн" в окне Power Pivot.

Нажмите «Управление отношениями» в группе «Отношения». Появится диалоговое окно "Управление отношениями".

Отображаются все отношения, существующие в модели данных.

Чтобы изменить отношение

Нажмите на связь.

Нажмите кнопку «Изменить». Появится диалоговое окно «Редактировать отношение».

Внесите необходимые изменения в отношения.

Нажмите "ОК". Изменения отражаются в отношениях.

Чтобы удалить отношения

Нажмите на связь.

Нажмите кнопку «Удалить». Появится предупреждающее сообщение, показывающее, как таблицы, затронутые удалением связи, повлияют на отчеты.

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

Обновление данных Power Pivot

Предположим, вы изменили данные в таблице Excel. Вы можете добавлять/изменять/удалять данные в таблице Excel.

Чтобы обновить данные PowerPivot, выполните следующие действия: 

Перейдите на вкладку "Связанная таблица" в окне Power Pivot.

Нажмите «Обновить все».

Таблица данных обновляется с учетом изменений, внесенных в таблицу Excel.

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

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

Использование инспектора документов для удаления скрытых данных

Чтобы открыть инспектор документов, нажмите «Файл» > «Информация» > «Проверить наличие проблем» > «Проверить документ». Откроется окно проверки документов Excel, показанное ниже.


Нажмите «Проверить», чтобы определить скрытое содержимое, а затем нажмите «Удалить все», чтобы удалить выбранный элемент.

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

Редактировать свойства документа, личную информацию и другие данные документа

Чтобы выборочно изменить или удалить данные документа, нажмите «Файл» > «Информация» > «Свойства». Щелкните Показать все свойства. Удалить или изменить информацию.

Выборочное удаление или редактирование комментариев

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

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

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

Выборочное отображение скрытых ячеек, строк и столбцов

В Microsoft Excel можно скрыть целые строки и столбцы ячеек на листе.

Чтобы отобразить все ячейки на листе:

  • Нажмите кнопку "Выделить все" в верхнем левом углу рабочего листа или нажмите клавиши CTRL + A.
  • Перейдите на вкладку "Главная" > "Формат" (в группе "Ячейки") > "Скрыть и показать" > "Показать строки" или "Показать столбцы".
  • Теперь видны все ячейки.

Выборочное отображение скрытых листов

Рабочие книги в Microsoft Excel могут содержать скрытые листы с информацией.

Чтобы отобразить листы:

  • Перейдите на вкладку "Главная" > "Формат" (в группе Ячейки) > "Скрыть и показать" > "Показать лист".
  • Нажмите имя листа, который хотите отобразить, и нажмите кнопку "ОК".
  • Теперь лист виден.

Удалить отслеженные изменения вручную или выборочно


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

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

Оглавление

Что такое модель данных

Модель данных Excel позволяет загружать данные (например, таблицы) в память Excel. Он сохраняется в памяти, где вы его напрямую не видите. Затем вы можете указать Excel связать данные друг с другом, используя общий столбец. Часть «Модель» модели данных относится к тому, как все таблицы связаны друг с другом.

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

Простая задача

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

Преимущества модели данных

Метод before отлично подходит, когда вы работаете с очень небольшим объемом данных.Тем не менее, есть преимущества использования функции модели данных в Excel. Вот несколько преимуществ:

  1. Проверка и обновление формул могут быть произвольными при работе со многими таблицами. В конце концов, вам нужно убедиться, что все формулы заполнены до нужной ячейки. И после добавления новых столбцов ваши формулы ПРОСМОТР также необходимо расширить. Модель данных требует лишь небольшой работы по настройке, чтобы связать таблицу. Он использует общий столбец при настройке. Однако столбцы, которые вы добавляете позже, автоматически добавляются в модель данных.
  2. Работа с большими объемами данных часто приводит к очень медленной работе листа из-за вычислений. Однако модель данных изящно обрабатывает большие объемы данных, не замедляя работу вашей компьютерной системы.
  3. Excel 2016 имеет ограничение в 1 048 576 строк. Однако количество строк, которые вы можете добавить в память модели данных, практически не ограничено. 64-битная среда не накладывает жестких ограничений на размер файла. Размер книги ограничен только доступной памятью и системными ресурсами.
  4. Если ваши данные находятся только в вашей модели данных, вы значительно сэкономите на размере файла.

Добавить данные в модель данных

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

На главной вкладке редактора Power Query

Это добавляет данные в модель данных. Обязательно выполните эти действия для обеих таблиц.

Создание связей между данными

После добавления данных в модель данных вы можете связать общие столбцы друг с другом. Чтобы создать отношения между таблицами:

Появится экран Power Pivot.

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

Примечание. Когда вы устанавливаете связь между двумя столбцами, обычной практикой является наличие уникальных значений в одном из столбцов. Это называется отношением один ко многим. Наличие дубликатов с обеих сторон может привести к ошибке. Для расширенных вычислений могут существовать отношения «многие ко многим» (например, в Power BI). Это, однако, слишком сложно для рассмотрения в этой статье. Если вас интересуют эти темы, обязательно изучите раздел «Отношения «многие ко многим»».

Использование модели данных

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

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

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

Поскольку две таблицы связаны друг с другом, вы можете использовать поля из обеих таблиц в одной сводной таблице! Прочтите предыдущее предложение еще раз. Разве это не удивительно?? В приведенном ниже примере используются поля «Продажи» и «Продавец» из таблицы «Продажи товаров», а поле «Пол» — из другой таблицы. И цифры по-прежнему правильные!

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

Чтобы свести к минимуму использование формул ПРОСМОТРА, можно воспользоваться замечательным инструментом Power Query. На моем сайте можно найти несколько статей об этом. Например, вы можете прочитать, как использовать Power Query для создания уникальных комбинаций или преобразования столбцов с накоплением.

О Рике де Гроте

Рик — основатель и редактор BI Gorilla. Он считает, что обучение — одно из самых больших удовольствий в жизни, и хочет поделиться своими знаниями, чтобы помочь вам улучшить свои навыки.

Узнайте больше о нем здесь, свяжитесь с ним в Twitter, Facebook и LinkedIn и подпишитесь на его канал YouTube.

Похожие сообщения

7 способов открыть несколько экземпляров Excel

2 отличных совета по использованию функции ПЕРЕКЛЮЧЕНИЕ в Excel

Расширенный прием: массовое добавление ссылок на ячейки

СУММПРОИЗВ с несколькими критериями ИЛИ — часть 2/2

25 мыслей о том, как использовать модель данных в Excel

Я сделал некоторые преобразования и добавил еще несколько столбцов в свою модель данных.
Теперь мне нужно экспортировать эти данные в CSV-файл из модели данных. Как мне это сделать, Рик? У меня 7,5 млн записей.

Я никогда не пробовал это раньше. Но вы можете попробовать экспортировать данные с помощью DAX Studio. Это отдельный инструмент, который вы можете скачать бесплатно. Он может подключаться к модели данных Excel. После этого вы можете попробовать экспортировать его. Дайте мне знать, если у вас все получилось! С наилучшими пожеланиями, Рик

Привет, Рик! - Спасибо, что поделился!
У меня есть к вам вопрос, надеюсь, вы сможете ответить:
У меня есть CSV-файл размером 2 млн. rows (Записи GL с транзакциями за каждый день). Я использую Power Query для подключения к файлу и добавления в модель данных.
Если я ничего не делаю, модель данных должна обрабатывать 2 млн. ряды.
Если я сделаю группировку с агрегированием суммы в месяц в Power Query, то будет всего 80 тысяч строк.
Будет ли это преимуществом, чтобы впоследствии в модели данных было быстрее использовать PowerQuery для создания такой агрегации?

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

Привет, Рик, спасибо за ответ. На данный момент я только пытаюсь создать информационный список. Я понял это, хотя; когда я объединяю их в Power Query, он идентифицирует уникальный ключ цветового кода и дает мне то, что я ищу. Большое спасибо!

У меня есть 5 списков в Sharepoint на основе производственных частей, которые я хочу включить в модель данных (сначала попробуйте).

Я только начинаю с двух из них: списка «Основные части» и списка «Цветовые коды». Я проверил, что столбец «Код цвета» в списке «Коды цветов» уникален (2000 уникальных кодов). В списке «Основные детали» около 1800 номеров деталей. Соединение осуществляется по «Цветовому коду», который есть в каждом списке. Когда я пытаюсь получить описание цвета из списка цветовых кодов, я получаю сообщение о том, что отчет сводной таблицы не помещается на листе — он пытается назначить каждый цветовой код каждому отдельному номеру детали, как будто нет реляционной связи между Колонки Color Code в каждом списке, но в моей модели данных явно нет. Какие-либо предложения? На первый шаг кажется, что это не проблема, но потом…

Спасибо за ваш комментарий. Вы также добавили значение? Например, сумма продаж? Если это не поможет, не могли бы вы поделиться со мной файлом примера?

Привет, Рик!
Ваша статья понятна, проста и понятна.
Я читал много статей о модели данных и Power Pivot, но ваша очень проста.
У меня к вам вопрос.
Я создал информационную панель в формате xlsb и вставил множество слайсеров, сводных таблиц и графиков.
Две таблицы, из которых берутся данные, состоят из 200 тыс. строк, но они постоянно растут каждую неделю.
Каждую неделю мне приходится дорабатывать данные в таблицы, потому что источники этих данных разные (SAP, Coupa) и, кроме того, я вношу в них некоторые дополнительные изменения.
Теперь я заметил, что время загрузки модели данных слишком сильно увеличивается, и я ищу способ выполнять запросы быстрее.
Я сохранил файл как xlsb. Я также удалил исходные данные (изначально я поместил таблицы в тот же файл, что и информационная панель на разных листах): после того, как я обновлю исходные данные и обновлю информационную панель, я привык удалять листы, где были исходные данные, чтобы уменьшить размер файла. На данный момент размер файла 20mb (xlsb).
Основной проблемой является время загрузки модели данных. Все еще слишком много.
Я еще не использовал power query /pivot, потому что эта панель мониторинга используется многими пользователями, и, если я хорошо помню, все они должны активировать эту функцию, чтобы панель инструментов работала нормально. Это может быть очень сложно управлять.
Я уже пробовал не использовать функции.
Исходный источник данных с множеством столбцов и всеми функциями в нем сохраняется как отдельный файл. Затем я помещаю в тот же файл, что и панель инструментов, но на другой лист, только те столбцы, которые мне действительно нужны, чтобы заставить его работать.
Как сказано выше, после обновления сводных таблиц и графиков я удаляю исходные данные (сохраненные в другом файле Excel).

Итак, у вас есть предложение сделать запросы быстрее?
Спасибо,
Майкл

Привет, Рик. Спасибо, что нашли время написать это.

Итак, я в основном зациклен на том факте, что у меня есть рабочая книга, которая добавляет и вычисляет столбцы и показатели.
Теперь… я не хочу использовать эту модель данных в ДРУГОМ файле Excel. Соединение должно автоматически обновляться, так как оба файла находятся в Sharepoint.

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

Сантьяго из Перу

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

Централизованное создание моделей данных и показателей часто выполняется с помощью SSAS. Excel не подходит для этого. Подходит ли вашей компании использование SQL Server Analysis Services (SSAS)?

Надеюсь, вы поможете с моим вопросом. У меня есть данные, которые повторяются через годы. Я отметил его стрелкой на картинке. Вот ссылки на файл Excel.

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

В вашей модели в настоящее время дублируются некоторые данные. В сводной таблице поле FY взято из таблицы «ModuleInfo». Если вы удалите его и вместо этого получите поле FY из «CourseRunInfo», ваши данные для стоимости печати не будут дублироваться.

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

Надеюсь, это поможет!

У меня есть информационная панель, заполненная сводными таблицами, заполненными моделью данных. Я хочу включить слайсеры для фильтрации данных. Если я затем отправлю файл кому-то, у кого нет доступа к исходному источнику данных, будут ли слайсеры работать для этого человека? Или они получат ошибку, потому что Excel и модель данных больше не могут найти источник данных? (Я знаю, что они не могут обновить модель данных, я просто беспокоюсь о том, чтобы просмотреть то, что я уже заполнил перед отправкой им)

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

у меня не работает! В модели данных все дублируется между двумя таблицами! Итак, у меня есть 99 единичных строк в таблице 1 и 99 уникальных строк в таблице 2, поэтому есть сопоставление 1-1. Стол один. Я переместил один столбец из таблицы 1 в таблицу 2 и попытался объединить два с помощью модели данных, и все, что он делает, это повторяет 99 полей из таблицы 2 под ключом в таблице 1 — vlookups намного проще. Кроме того, у меня возникают всевозможные проблемы с памятью, так что это не так хорошо, как виртуальный поиск для больших наборов данных.

Из вашего описания мне трудно понять, почему ваши данные дублируются. По моему опыту, модель данных особенно хороша для больших наборов данных.

Не могли бы вы предоставить образец файла и пояснить, где возникают проблемы. Я бы с удовольствием посмотрел!

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