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

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

Оглавление

  • Что такое модель данных
  • Простая задача
  • Преимущества модели данных
  • Добавить данные в модель данных
  • Создание отношений между данными
  • Использование модели данных

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

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

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

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

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

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

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

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

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

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

  • Щелкните вкладку Данные -> Щелкните ячейку в нужной таблице для импорта
  • Выберите Из таблицы/диапазона

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

  • Выберите Close & Load -> затем Закрыть и загрузить в…

  • Выберите Только создать соединение .
  • Обязательно установите флажок Добавить эти данные в модель данных

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

Создание отношений между данными

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

  • Перейдите на вкладку Данные -> выберите Управление моделью данных

Откроется экран Power Pivot.

  • Щелкните Представление диаграммы. Это дает вам обзор всех таблиц в модели данных.
  • Затем свяжите общий столбец “Продавец” в первая таблица, со столбцом «Продавец» во второй таблице. Вы можете сделать это, щелкнув и перетащив один столбец на другой. Должна появиться связь.

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

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

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

  • Перейдите на вкладку Вставить -> нажмите Pivot Таблица

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

  • Нажмите Использовать модель данных этой книги

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

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

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

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


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

Ответ


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

Ответ


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

Ответ


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

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

Ответ


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

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

Ответить


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

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

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

Есть шансы?

Спасибо,

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

Ответ


Привет, Рик,

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

https://www.dropbox.com/scl/fi/1xxmqyb9a0vwpj00mfkgj/Sample.xlsx?dl=0&rlkey=n2zc09cvc7gzxgv0p3g9kwi70

https://www.dropbox.com/s/hpcmtf6q1rfehwl/Capture.PNG?dl=0

https://www.dropbox.com/s/418zg5uupy6g3wj/Capture2.PNG? dl = 0

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

Спасибо,
K

Ответить


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

Ответ


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

Ответ


Так что в основном Excel становится больше похожим на Access?

Ответить


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

Ответ


Спасибо, отличная информация

Ответ


Одна важная вещь, которая мне нравится в моделях данных, – это дополнительные функции, которые вы можете использовать в сводных таблицах, особенно «Уникальные счетчики».

Ответ



Анализировать в Excel – Модель данных отсутствует в Excel – Сообщество Microsoft Power BI

Привет всем –

I Я пытаюсь использовать «Анализировать в Excel», чтобы перенести мою модель данных Power BI в Microsoft Excel для создания отчетов для моих клиентов (которые предпочитают работать в Excel). Я успешно сохранил файл Excel с подключением pbiazure из службы Power BI и построил сводную таблицу. Однако, когда я пытаюсь щелкнуть «Управление моделью данных» в Excel, чтобы увидеть взаимосвязи (которые, как я ожидаю, будут такими же, как для рабочего стола Power BI), модель данных не отображается и остается пустой.

Кто-нибудь знает, почему это так? В случае, если он напрямую подключен к набору данных в службе Power BI, я предполагаю, что он сможет отображать и редактировать модель данных непосредственно в Excel..

Может ли кто-нибудь пролить свет на то, почему отображается пустой экран без модели данных?


Привет, @ sviswan2!

Параметр «Управление моделью данных» в Excel будет отображать модель данных только при подключении источника данных в Excel. Модель данных создается автоматически при одновременном импорте двух или более таблиц из базы данных. Когда вы импортируете одну таблицу, вы можете выбрать «добавить эти данные в модель данных». См. Эту статью: Advanced Excel – модель данных

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

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

С уважением,
Инцзе Ли

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


Привет @ v-yingjl –

Спасибо за пояснение, которое немного помогает понять. Поэтому, пожалуйста, поясните следующее:

1) Если модель данных не импортирована, будут ли работать все отношения между данными, которые я настроил в файле рабочего стола, когда я использую его как «Анализировать в Excel “?

2) Если я хочу добавить какую-либо связь или меру, я должен сначала сделать это на рабочем столе Power BI, опубликовать в службе, а затем он должен появиться в моем файле Excel?

спасибо!


Привет @ sviswan2,

  1. Да, «Анализировать в excel» цитирует набор данных в сервисе power bi, отношения сохранятся.
  2. Если вы хотите добавить взаимосвязь или меру, у вас есть сделать это сначала на рабочем столе power bi, затем опубликовать в сервисе и повторно использовать «Анализировать в Excel», хотя вы не можете видеть взаимосвязь и конкретную формулу меры, вы можете видеть только поля таблицы и значение меры в excel.

С уважением,
Инцзе Ли

Если этот пост поможет, пожалуйста, примите его как решение чтобы помочь другим участникам найти его быстрее.


Привет @ sviswan2,
Анализировать в Excel позволяет взаимодействовать с набором данных из Power BI, но он имеет только соединение с набором данных, он не имеет данных из модели.

Вот ссылка: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
“Вы можете сохранить книгу Excel вы создаете с помощью набора данных Power BI, как и любую другую книгу. Однако вы не можете публиковать или импортировать книгу обратно в Power BI, потому что вы можете публиковать или импортировать в Power BI только те книги, которые имеют данные в таблицах или имеют модель данных.. Поскольку новая книга просто подключается к набору данных в Power BI , публикация или импорт в Power BI будет происходить по кругу “.

Оцените статью
futurei.ru
Добавить комментарий