Как удалить модель данных из книги excel
Обновлено: 03.07.2024
"Эта книга содержит модель данных Power Pivot, созданную в предыдущей версии надстройки Power Pivot. Вам нужно обновить эту модель данных с помощью Power Pivot в Microsoft Excel 2013.
Перед обновлением ознакомьтесь с описанием известных проблем при обновлении.
Начните с сохранения копии файла на случай, если вам впоследствии понадобится его предыдущая версия. После обновления книга будет работать только в Excel 2013 или более поздней. Узнайте подробнее, чем отличается обновленная модель.
Нажмите Power Pivot > Управление, чтобы начать обновление.
Появится уведомление о необходимости обновления.
Обновите модель данных. После этого вы больше не сможете работать с этой книгой в предыдущей версии Power Pivot.
Нажмите ОК, чтобы обновить книгу.
Сохраните и перезагрузите книгу. Этот шаг фиксирует изменения, внесенные при обновлении. После сохранения отменить изменения нельзя. Перезагрузка займет всего несколько секунд в зависимости от размера и месторасположения книги.
Нажмите Да, чтобы перезагрузить книгу.
В этой статье
Известные проблемы
Удаление или перекомпоновка таблиц, связь которых с исходной таблицей в Excel утрачена
Устаревшие и потерянные таблицы не обновляются вместе с остальной моделью, из-за чего данные удаляются или недоступны. Чтобы избежать этой проблемы, необходимо, чтобы существующие связанные таблицы были связаны с существующей таблицей в Excel.
В Power Pivot для Excel 2010 щелкните связанную таблицу в окне Power Pivot.
Выберите на ленте вкладку Связанная таблица.
Убедитесь, что параметр Таблица Excel указывает на существующую таблицу. Если таблица в Excel была переименована, нажмите стрелку вниз рядом с параметром Таблица Excel и выберите таблицу под новым именем.
Нажмите Перейти к таблице Excel и убедитесь, что связанная таблица подключена к исходной таблице в Excel.
Если параметр Таблица Excel пуст:
удалите связанную таблицу из модели данных, если она больше не используется,
После этого все готово к обновлению книги, если только не возникает другая проблема.
Удаление таблиц запросов детализации
Эта проблема возникает только при обновлении моделей данных Power Pivot, основанных на кубах Analysis Services, которые поддерживают детализацию. Если книга включает таблицы запросов детализации, обновление пройдет успешно, но выборе любого поля в сводной таблице будет возвращаться ошибка обновления.
В Power Pivot для Excel 2010 найдите в книге все таблицы запросов детализации.
Таблица запросов детализации создается в книге, если щелкнуть правой кнопкой мыши меру и выбрать Показать подробные сведения. Результирующая таблица представлена как отдельный лист книги. Первая строка начинается со слов: "Возвращены данные для…".
Удалите все листы таблиц запросов детализации. Их можно создать снова после обновления.
Повторно создайте таблицы запросов детализации, если это необходимо. Щелкните правой кнопкой мыши вычисляемое поле сводной таблицы и выберите Показать подробные сведения. Таблица запросов детализации будет создана на отдельном листе.
Удаление защиты паролем
"Не удалось обновить модель данных книги".
Чтобы избежать этой проблемы, удалите пароль перед обновлением.
Откройте книгу в Excel 2013 или Excel 2016.
Выберите Файл > Сведения > Защита книги > Зашифровать с использованием пароля.
Удалите звездочки и нажмите ОК.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После обновления снова защитите книгу паролем.
Снятие ограничений доступа
"Не удалось обновить модель данных книги".
Чтобы избежать этой проблемы, снимите все ограничения доступа перед обновлением.
Откройте книгу в Excel 2013 или Excel 2016.
Выберите Файл > Сведения > Защита книги > Ограничить доступ.
Установите флажок Неограниченный доступ, чтобы отменить все ограничения доступа.
Сохраните книгу в Excel.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После обновления снова установите ограничения доступа для книги.
В Excel 2013 или Excel 2016 2013 перейдите в отчет или с полями, которые возникли проблемы при обновлении.
Удалите эти поля из сводной таблицы или отчета. Они по-прежнему будут отображаться в списке полей, просто их не будет в отчете.
Сохраните и закройте книгу.
Откройте книгу снова.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После успешного обновления верните поля в сводную таблицу или отчет.
Не удается обновить подключение к источнику данных в обновленной книге
"Excel не удалось получить необходимые сведения об этом кубе. Возможно, куб был реорганизованы или изменен на сервере. Обратитесь к администратору куба OLAP и при необходимости настройте новый источник данных для подключения к кубу".
Когда вы пытаетесь изменить существующее подключение, то видите имя модели данных, но не можете изменить источник данных.
Проблема
Подключение к источнику данных больше не является допустимым, и его необходимо обновить, но его нельзя обновить в Excel 2013 или Excel 2016.
Решение
Откройте книгу в Excel 2010 с надстройкой Power Pivot.
Щелкните Power Pivot > Модель данных > Управление, чтобы открыть окно Power Pivot.
Выберите подключение к текущей базе данных и нажмите кнопку Изменить.
В диалоговом окне Изменение соединения нажмите кнопку Обзор, чтобы выбрать другую базу данных того же типа с другим именем или в другом расположении.
Сохраните и закройте книгу.
Откройте книгу в Excel 2013 или Excel 2016.
Завершение частичного обновления
В большинстве случаев обновление книги должно проходить без изменений, но иногда для завершения работы требуется внести дополнительные изменения. В этом разделе вы узнаете, как реагировать на определенные ошибки, указывающие на частичное или неполное обновление. Книга обновлена, но отсутствует определенная функциональность.
Отсутствуют изображения ключевых показателей эффективности (KPI)
Если отсутствуют изображения ключевых показателей эффективности (цветные значки или значки с рисунками), удалите KPI из области Значения списка полей, после чего добавьте их снова.
Ошибка обновления вычислений DAX: Не удалось обновить книгу надлежащим образом.
"Не удалось полностью обновить книгу. После сохранения некоторые из функций обработки данных Excel не работают. Подробнее см. по адресу http://go.microsoft.com/fwlink/?LinkID=259419."
Мы получили такую ошибку: "Не удалось выполнить запрос ко внутренней структуре, обеспечивающей ‘<таблица>[<имя столбца>]’, поскольку она зависит от столбца, связи или меры, которые не обработаны. Обновите или повторно вычислите модель".
Примечание: Если вам будет предложено сохранить сводную таблицу в Power Pivot, закрыть и повторно открыть книгу, нажмите кнопку Да.
Найдите следующие известные проблемы совместимости DAX:
Обозначение NULL пустыми двойными кавычками (“”) больше не допускается. Замените двойные кавычки нулями.
Попытка сравнения несовместимых типов в функциях LOOKUPVALUE, CONTAINS или SWITCH. Необходимо либо указать [Тип] в аргументе, либо гарантированно обеспечить, что в операции будут участвовать только числовые, строковые или логические значения. Для преобразования типов значений можно использовать функции VALUE или FORMAT.
Функции сводной таблицы, ссылающиеся на "Power Pivot Data", больше не работают
Если в функциях Excel для работы с аналитическими кубами определена связь с моделью Power Pivot из предыдущего выпуска, необходимо вручную указать новое имя модели ("ThisWorkbookDataModel").
=CUBEVALUE("Power Pivot Data",[Measures].[TotalSales])
В Excel для каждого листа выберите Формулы > Показать формулы, чтобы на всех листах книги отображались используемые вычисления.
В функциях куба Excel, которые определяют связь, найдите значение "Power Pivot Data".
Замените значение "Power Pivot Data" на "ThisWorkbookDataModel".
Утрата данных: данные модели удаляются при обновлении
"Не удалось обновить следующие связанные таблицы: <имятаблицы>. Эти таблицы были удалены из модели."
В особых случаях данные удаляются из модели во время обновления. Это происходит, если в модели данных имеются потерянные связанные таблицы, для которых не получается найти исходную таблицу в той же книге, либо если исходная таблица удалена или переименована.
Обновление проходит успешно, но связанные таблицы удаляются
Связанная таблица — это таблица, которую вы создаете в книге и затем включаете в модель данных, чтобы связать с другими таблицами и добавить вычисления DAX. Связанные таблицы, не связанные ни с одной существующей в книге исходной таблицей, будут удалены при обновлении.
Вот сценарий: вы создали или импортировали таблицу в Excel, добавили ее в качестве связанной таблицы в Power Pivot, где она стала частью модели данных, а затем удалили исходную таблицу, на которой она основана. В модели сохраняется связанная таблица, хотя таблица в Excel не существует.
"Не удалось обновить следующие связанные таблицы: <имятаблицы>. Эти таблицы были удалены из модели."
По возможности отмените обновление и измените модель либо создайте резервную копию, если собираетесь использовать таблицу впоследствии.
На предложение сохранить, закрыть и повторно открыть книгу ответьте Нет.
Закройте файл в Excel 2013 без сохранения.
Откройте книгу в Excel 2010 и устраните проблемы со связанными таблицами.
Связанная таблица больше не нужна? Удалите его из модели или перезапустите обновление в Excel 2013 и позвольте обновить таблицу.
Excel таблица была переименована, а связанная таблица не была? Обновите сведения о под Power Pivot, как описано в шаге 1 в верхней части этой страницы.
Связанная таблица должна оставаться, так как она используется в вычислениях или в Excel таблице? Повторно создать таблицу в Excel и обновить ссылку:
скопируйте строки из связанной таблицы в окне Power Pivot;
вставьте строки в книгу Excel;
отформатируйте строки как таблицу;
вернитесь в окно Power Pivot;
на вкладке Связанная таблица нажмите черную стрелку вниз рядом с полем Таблица Excel и выберите только что созданную таблицу;
после внесения изменений откройте файл в Excel 2013;
вновь запустите обновление, нажав Power Pivot > Управление.
После сохранения книги откат к предыдущей версии невозможен. Связанную таблицу придется создавать заново. Подробнее см. в разделе Добавление данных листа в модель данных с помощью связанной таблицы.
Чем отличается обновленная модель
В большинстве случаев обновленная книга идентична предыдущей. Однако в новой версии есть некоторые изменения, о которых стоит упомянуть.
Срезы и описания столбцов больше не отображаются в списке полей
В предыдущих версиях описания столбцов или полей в списке полей в столбце отображались в качестве подскаутых. В Excel 2013 или более поздней не поддерживается список полей. Все описания столбцов, которые существуют в модели, будут игнорироваться в визуализациях данных в Excel.
Срезы поддерживаются, но теперь вы создаете их другим способом. В списке полей нет параметров для создания среза.
После обновления невозможно работать с книгами в предыдущих версиях Excel и Power Pivot
До Excel 2013 было выпущено две версии надстройки Power Pivot. Обе версии предназначены только для Excel 2010. Это:
SQL Server 2008 R2 Power Pivot для Excel;
SQL Server 2012 Power Pivot для Excel 2010.
"Не удалось выполнить инициализацию источника данных."
Эта ошибка возникает, потому что при взаимодействиях с данными, таких как создание среза, формируется запрос к модели данных, а это источник данных сводной таблицы. Так как модель данных недействительна в Excel 2010, попытка взаимодействия с данными приводит к ошибке инициализации их источника.
Предположим, что теперь вы пытаетесь открыть модель данных в Power Pivot окно. Модель не открывается, и возникает следующая ошибка:
"Книга содержит модель данных Excel, созданную в более новой версии Excel. Эту книгу можно открыть в более старой версии Excel, но нельзя будет загрузить или работать с Power Pivot, если программа сосуществует вместе с моделью данных Excel".
Способа выхода из этой ситуации нет, обратной совместимости при обновлении не предусмотрено. Переход на Excel 2013 должен выполняться всем коллективом организации одновременно. Кроме того, может потребоваться временно хранить отдельные версии книг, если ваша организация планирует использовать Excel 2010 и Excel 2013 или Excel 2016 рядом.
В Excel 2013 появился новый аналитический механизм: модель данных. Каждая рабочая книга располагает собственной внутренней моделью данных, упрощающей анализ разрозненных источников данных. [1] Идея, заложенная в основу модели данных, проста. Предположим, что в вашем распоряжении имеются две таблицы: Заказы (рис. 1) и Сотрудники (рис. 2). В таблице Заказы содержится информация о сделках (код сотрудника, дата и сумма счета-фактуры, период продаж). В таблице Сотрудники находится информация о сотрудниках: код, фамилия, имя и должность. Если нужно проанализировать суммы продаж в зависимости от должности сотрудника, следует объединить информацию, содержащуюся в двух таблицах. Чтобы ваши данные имели вид Таблицы – инструмента Excel(поэтому пишется с заглавной буквы) – кликните на любой ячейке таблицы данных и выполните команду Создать Таблицу: Ctrl+T.
Рис. 1. Информация о сделках
Рис. 2. Информация о сотрудниках
Выполнение подобной задачи в прошлом потребовало бы использования множества формул ВПР, СУММЕСЛИ и других. После появления модели данных в Excel 2013 достаточно объявить обе таблицы связанными (по коду сотрудника) и включить их в модель данных. Модель данных Excel создает куб аналитики на основе связи между кодами сотрудников и передает нужные данные в сводную таблицу.
Наша задача заключается в том, чтобы отобразить суммы продаж в зависимости от должности сотрудника. В прежних версиях Excel решить подобную задачу было непросто, поскольку сведения о продажах и должностях сотрудников находились в отдельных таблицах. После появления модели данных достаточно выполнить следующие действия:
1. Щелкните в области таблицы Заказы и начните создавать новую сводную таблицу, выбрав команду Вставка → Сводная таблица.
2. В диалоговом окне Создание сводной таблицы установите флажок Добавить данные в модель данных (рис. 3). Обратите внимание на то, что в окне Создание сводной таблицы находится ссылка на именованный диапазон (Таблицу данных); в нашем примере – Заказы. Рекомендуется присваивать говорящие имена Таблицам данных. Это облегчит распознавание таблиц, находящихся в модели данных. Если не присваивать названия Таблицам данных, модель данных отобразит их под названиями Таблица 1, Таблица2 и т.д. Чтобы назначить Таблице данных имя, кликните на Таблице, перейдите на контекстную вкладку Работа с данными → Конструктор и в поле Имя таблицы введите говорящее имя. Повторите эту операцию для всех остальных Таблиц.
Рис. 3. Создайте сводную таблицу на основе таблицы данных Заказы
3. Щелкните в области таблицы данных Сотрудники и начните создавать новую сводную таблицу. Не забудьте установить флажок Добавить эти данные в модель данных.
4. После добавления таблиц в модель данных перейдите к списку полей сводной таблицы и выберите переключатель ВСЕ. В списке полей появятся два диапазона, соответствующие таблицам данных (рис. 4).
Рис. 4. В списке полей сводной таблицы выберите параметр ВСЕ, чтобы получить доступ ко всем таблицам, находящимся в модели данных
5. Создайте сводную таблицу обычным образом. В новой таблице поле Должность появится в области СТРОКИ, а поле Сумма по столбцу Объем продаж — в области ЗНАЧЕНИЯ. Программа тут же распознает, что используются две таблицы из модели данных, и предлагает создать связь между ними (рис. 5). Щелкните на кнопке Создать.
Рис. 5. Выберите создание связи между таблицами после отображения соответствующего запроса
6. На экране появится диалоговое окно Создание связи (рис. 6). В этом окне можно выбрать таблицы и поля, для которых создается связь. На этом рисунке показана таблица Заказы с полем Код сотрудника. Эта таблица связана с таблицей Сотрудники с помощью поля Код сотрудника.
Рис. 6. Создайте подходящую связь, используя раскрывающиеся списки
7. После создания связи можно использовать данные из двух таблиц для отображения требуемых результатов. Применение модели данных проиллюстрировано на рис. 7.
Рис. 7. Отображение объема продаж по должностям сотрудников
Первичные ключи. Обратите внимание на рис. 6 на раскрывающийся список Связанный столбец (первичный ключ). Это означает, что модель данных использует поле, относящееся к связанной таблице, в качестве первичного ключа. Первичный ключ — это поле, содержащее лишь уникальные ненулевые значения (без дубликатов и пустых значений). Первичные ключи применяются в модели данных в целях предотвращения ошибок суммирования и дубликатов. В каждой связи должен быть хотя бы один первичный ключ. Поскольку поле Код сотрудника, находящееся в таблице Сотрудники, является первичным ключом, оно должно включать только уникальные значения (без пробелов и нулевых значений). Первичный ключ обеспечивает единственный способ сохранения целостности данных при объединении нескольких таблиц.
Управление связями в модели данных
После включения таблиц во внутреннюю модель данных может потребоваться настройка связей, установленных между ними. Чтобы изменить связи модели данных, отобразите диалоговое окно Управление связями. Для этого выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. Требуемое окно появится на экране (рис. 8).
Рис. 8. В диалоговом окне Управление связями можно изменить связи, заданные в модели данных
В этом окне доступны следующие команды, применяемые для изменения связей между таблицами:
- Создать. Создание новой связи между таблицами модели данных.
- Изменить. Изменение выделенной связи.
- Активировать. Активизация выбранной связи, вследствие чего Excel будет рассматривать данную связь при агрегировании и анализе данных в модели.
- Деактивировать. Отключение выбранной связи. Это приведет к тому, что Excel будет игнорировать связь при агрегировании и анализе данных в модели.
- Удалить. Удаление выбранной связи.
Добавление новой таблицы в модель данных. Чтобы добавить новую таблицу в модель данных, воспользуйтесь одним из следующих двух способов.
Во-первых, можно создать сводную таблицу на основе новой Таблицы данных (в нашем примере – Местоположения). В окне создания сводной таблицы установите флажок Добавить эти данные в модель данных. Excel добавит Таблицу в модель данных и создаст сводную таблицу. После добавления сводной таблицы можно открыть диалоговое окно Управление связями и создать нужные связи.
Второй (и более гибкий) способ заключается в том, чтобы создать таблицу вручную и добавить ее в модель данных. Выполните следующие действия.
1. Если вы еще не создали Таблицу, поместите курсор в таблицу данных (лист ExcelМестоположения, любую ячейку в диапазоне А1:С55), перейдите на вкладку Вставка щелкните на кнопке Таблица (рис. 9). Можно, встав в ячейку таблицы данных, нажать Ctrl+T. На экране появится диалоговое окно (рис. 10), в котором задается диапазон данных. Нажмите Ok. Программа преобразует этот диапазон в Таблицу, которая может распознаваться внутренней моделью данных.
Рис. 9. Создание Таблицы на основе исходных данных
Рис. 10. Преобразование диапазона в таблицу
2. На контекстной вкладке Работа с таблицами → Конструктор, измените значение поля Имя таблицы, выбрав легко запоминаемое имя; в нашем примере – Отделения.
3. Перейдите на вкладку ленты Данные и в области Подключения щелкните на кнопке Подключения. На экране появится диалоговое окно Подключения к книге (рис. 11). Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Добавить, и выберите пункт Добавить в модель данных.
Рис. 11. Откройте диалоговое окно Подключения к книге и выберите пункт меню Добавить в модель данных
4. На экране появится диалоговое окно Существующие подключения (рис. 12). Перейдите на вкладку Таблицы и выделите только что созданную Таблицу. Щелкните на кнопке Открыть, чтобы добавить выбранную таблицу в модель данных.
Рис. 12. В окне Существующие подключения перейдите на вкладку Таблицы, выделите только что созданную Таблицу и щелкните Открыть
5. Все сводные таблицы, созданные на основе модели данных, обновляются с учетом добавления новой таблицы. Не забудьте открыть диалоговое окно Управление связями и создать требуемую связь.
Удаление таблицы из модели данных. Иногда возникает необходимость в удалении Таблицы или источника из модели данных. Чтобы выполнить эту задачу, выберите вкладку ленты Данные и щелкните на кнопке Подключения. На экране появится диалоговое окно Подключения к книге (рис. 13). Выберите таблицу, а затем кликните на кнопке Удалить.
Рис. 13. С помощью окна Подключения к книге можно удалить любую таблицу из внутренней модели данных
Создание новой сводной таблицы с помощью модели данных. Иногда приходится создавать сводные таблицы «с нуля», используя в качестве источника данных существующую внутреннюю модель данных. Для этого выполните следующие действия.
1. Выполните команду Вставка → Сводная таблица. Отобразится диалоговое окно Создание сводной таблицы. Установите флажок Использовать внешний источник данных (рис. 14), и щелкните на кнопке Выбрать подключение.
Рис. 14. Окно Создание сводной таблицы
2. На экране появится диалоговое окно Существующие подключения (рис. 15). На вкладке Таблицы выберите параметр Таблицы в модели данных книги и щелкните на кнопке Открыть.
Рис. 15. Окно Существующие подключения
3. Вы вернитесь обратно в диалоговое окно Создание сводной таблицы. Щелкните ОК.
4. Если создание сводной таблицы завершилось удачно, появится панель области задач Поля сводной таблицы, отображающая все таблицы, включенные во внутреннюю модель данных (рис. 16).
Рис. 16. После успешного завершения создания сводной таблицы отображаются все таблицы, относящиеся к модели данных
Ограничения внутренней модели данных
Как и все остальное в Excel, внутренняя модель данных имеет определенные ограничения:
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.
2 комментария для “Сводная таблица на основе внутренней модели данных”
Раскрывающийся список – удобный визуальный компонент таблиц Microsoft Excel, который часто оказывается полезным. Однако в некоторых случаях потребность в нем отпадает, и юзеры задаются вопросом об удалении списка из листа. Иногда с этим возникают проблемы, поскольку непонятно, как сразу убрать все содержимое.
В этой статье я покажу три подходящих метода, а вы сможете выбрать любой из них.
Вариант 1: Контекстное меню ячейки
Элементы управления ячейками отображаются в контекстном меню, которое можно вызвать в любое время. В нем есть пункт, отвечающий за удаление содержимого клетки, который и пригодится в тех ситуациях, когда нужно избавиться от раскрывающегося списка в Эксель. Однако учитывайте, что будет удалено даже текущее выбранное значение, поэтому запомните его или скопируйте заранее.
Выделите ячейку с раскрывающимся списком и кликните по ней правой кнопкой мыши.
Из контекстного меню выберите пункт «Удалить».
Программа предлагает несколько вариантов удаления со сдвигом. В этом случае не так важно, где ставить маркер, поэтому выберите любой параметр и подтвердите удаление.
Далее вы увидите, что содержимое ячейки полностью очищено и в ней можно написать любую функцию, число или текст. Предварительно рекомендую убедиться в том, что формат ячейки вас устраивает, поскольку математические вычисления в ее текстовом представлении невозможны.
Вариант 2: Меню «Проверка вводимых значений»
Если ранее вы самостоятельно создавали раскрывающийся список, то уже знаете, через какое именно меню это делается. Его можно использовать и для очистки данных, удалив все значения кроме выбранного. Это отличный вариант в тех случаях, когда нужно сохранить текущее число или текст в ячейке, но удалить все остальные варианты списка.
Выделите список левой кнопкой мыши, перейдите на вкладку «Данные» и откройте окно «Проверка данных».
Слева внизу вы увидите кнопку «Очистить все», которая и удаляет все выставленные условия проверки, то есть в данном случае – выпадающий список.
Теперь вы увидите, что тип данных изменился на «Любое значение» и пропала строка со значениями списка.
Вернитесь к таблице и проверьте результативность выполненных действий.
Вариант 3: Изменение типа данных в ячейке
В завершение быстро разберу вариант, связанный с предыдущим, но выполняющийся немного другим образом. Он заключается в изменении типа данных в ячейке, что автоматически удаляет выпадающий список и применяет другие необходимые значения для клетки. Используйте этот вариант, если уже знаете, какое форматирование ячейки хотите произвести.
Выделите ячейку и перейдите в «Проверка данных» точно так же, как это было показано выше.
На этот раз разверните меню «Тип данных» и установите параметр «Любое значение» или другой, в зависимости от ваших личных предпочтений.
Вернитесь к ячейке и убедитесь в том, что теперь вы можете редактировать ее содержимое требуемым вам образом.
Все действия в Microsoft Excel, как и в практически любой другой программе, можно отменить, используя комбинацию клавиш Ctrl + Z. Нажмите ее один раз, если после удаления списка вы решили, что хотите его вернуть, или забыли сохранить конкретные значения.
Детальную информацию о том, как производится создание раскрывающегося списка, вы найдете в другой статье. Она пригодится, если вдруг после удаления одного перечня вы задумали создать другой, с отличающимися или такими же значениями.
Функция модели данных в Excel позволяет легко выстраивать взаимосвязи между простыми отчетами и их исходными наборами данных. Это значительно упрощает анализ данных. Это позволяет интегрировать данные из множества таблиц, распределенных по нескольким рабочим листам, просто выстраивая отношения между соответствующими столбцами. Он работает полностью за кулисами и значительно упрощает такие функции отчетности, как сводная таблица и т. Д.
В нашей статье мы попытаемся показать, как создать сводную таблицу из двух таблиц, используя функцию модели данных, устанавливая таким образом связь между двумя объектами таблицы и создавая тем самым сводную таблицу.
Как создать модель данных в Excel?
Давайте разберемся, как создать модель данных в Excel, на нескольких примерах.
Вы можете скачать этот шаблон данных модели Excel здесь - Шаблон данных модели Excel
Пример № 1
- У нас есть список продуктов, и у нас есть код полки для каждого продукта. Нам нужен стол, где у нас есть описание стеллажей вместе с кодами стеллажей. Так как же мы включаем описания полок в каждый код полок? Возможно, многие из нас прибегнут к использованию VLOOKUP здесь, но мы полностью удалим необходимость использовать VLOOKUP здесь, используя Excel Data Model.
- Таблица слева - это таблица данных, а таблица справа - это таблица поиска. Как видно из данных, можно создать связь на основе общих столбцов.
- Теперь модель данных совместима только с объектами таблицы. Поэтому иногда может потребоваться преобразовать наборы данных в объекты таблиц. Для этого выполните следующие шаги.
- Щелкните левой кнопкой мыши в любом месте набора данных.
- Перейдите на вкладку «Вставка» и перейдите к таблице в группе «Таблицы» или просто нажмите Ctrl + T.
- Снимите флажок или проверьте, что у Моей таблицы есть опция Заголовок. В нашем примере он действительно имеет заголовок. Нажмите ОК.
- Несмотря на то, что мы по-прежнему сосредоточены на новой таблице, нам необходимо указать имя, которое имеет смысл в поле «Имя» (слева от строки формул).
В нашем примере мы назвали таблицу Personnel.
- Теперь нам нужно выполнить тот же процесс для таблицы поиска и назвать ее Shelf Code.
Создание отношений
Итак, во-первых, мы перейдем на вкладку «Данные», а затем выберите «Отношения» в подгруппе «Инструменты данных». После того, как мы нажмем на опцию Отношения, в начале, так как нет никакой связи, следовательно, у нас ничего не будет.
Сначала мы нажмем «Создать», чтобы создать отношения. Теперь нам нужно предоставить имена основной и поисковой таблиц из раскрывающегося списка, а затем также упомянуть столбец, который является общим для двух таблиц, чтобы мы могли установить связь между двумя таблицами из раскрывающегося списка. колонн.
- Теперь первичная таблица - это таблица с данными. Это первичная таблица данных - Таблица5. С другой стороны, Связанная таблица - это таблица, в которой есть данные поиска - это наша таблица поиска ShelfCodesTable. Первичная таблица - это та, которая анализируется на основе справочной таблицы, которая содержит справочные данные, что в конечном итоге сделает представленные данные более значимыми.
- Таким образом, общий столбец между двумя таблицами - это столбец кода полки. Это то, что мы использовали, чтобы установить связь между двумя таблицами. Что касается столбцов, столбец (внешний) - это тот, который ссылается на таблицу данных, в которой могут быть повторяющиеся значения. С другой стороны, Связанный столбец (основной) ссылается на столбец в таблице поиска, где у нас есть уникальные значения. Мы просто настраиваем поле для поиска значений из таблицы поиска в таблице данных.
- Как только мы настроим это, Excel создаст отношения между двумя за сценой. Он объединяет данные и создает модель данных на основе общего столбца. Это не только облегчает требования к памяти, но и намного быстрее, чем использование VLOOKUP в больших книгах. После определения модели данных Excel будет обрабатывать эти объекты как таблицы модели данных вместо таблицы рабочего листа.
- Теперь, чтобы увидеть, чем занимался Excel, мы можем щелкнуть Управление моделями данных в разделе Данные -> Инструменты данных.
- Мы также можем получить схематическое представление модели данных, изменив представление. Мы нажмем на опцию просмотра. Это откроет варианты просмотра. Затем мы выберем представление схемы. Затем мы увидим схематическое представление, показывающее две таблицы и взаимосвязь между ними, т.е. общий столбец - Код полки.
- На приведенной выше диаграмме показано отношение «один ко многим» между уникальными значениями таблицы поиска и таблицей данных с дублированными значениями.
- Теперь нам нужно будет создать сводную таблицу. Для этого перейдем на вкладку «Вставка» и затем щелкните опцию «Сводная таблица».
В диалоговом окне «Создание сводной таблицы» в сводной таблице мы выберем источник «Использовать модель данных этой книги».
- Это создаст сводную таблицу, и мы увидим, что обе исходные таблицы доступны в разделе источников.
- Теперь мы создадим сводную таблицу, показывающую количество каждого человека, у которого есть полки.
- Мы выберем Персонал в разделе Строки из Таблицы 5 (таблица данных), а затем Описание (Таблица поиска).
- Теперь перетащите код полки из таблицы 5 в раздел «Значения».
- Теперь мы добавим месяцы из таблицы 5 в раздел строк.
- Или мы могли бы добавить месяцы в качестве фильтра и добавить его в раздел «Фильтры».
Пример № 2
- Теперь у нас есть господин Басу, управляющий заводом под названием Корпорация Басу. Г-н Басу пытается оценить выручку за 2019 год на основе данных за 2018 год.
- У нас есть таблица, в которой мы имеем доход за 2018 год и последующий доход на разных уровнях приращения.
- Итак, у нас выручка на 2018 год - 1, 5 млн долларов, а минимальный рост, ожидаемый в следующем году, составляет 12%. Г-н Басу хочет таблицу, которая будет показывать доход на разных уровнях.
- Мы создадим следующую таблицу для прогнозов на разных дополнительных уровнях на 2019 год.
- Теперь мы дадим в первой строке «Доход» ссылку на предполагаемый минимальный доход на 2019 год, т. Е. $ 1, 68 млн.
- После использования формулы ответ показан ниже.
- Теперь мы выберем всю таблицу, то есть D2: E12, а затем перейдем в Данные -> Прогноз -> Анализ «что если» -> Таблица данных.
- Это откроет диалоговое окно Data Table. Здесь мы введем минимальный процент приращения от ячейки B4 в ячейке ввода столбца. Причина этого заключается в том, что наши прогнозируемые процентные доли роста в таблице расположены в столбчатой форме.
- Как только мы нажмем кнопку ОК, анализ «Что, если» автоматически заполнит таблицу прогнозируемым доходом с различными добавочными процентами.
Пример № 3
- Теперь предположим, что у нас тот же сценарий, что и выше, за исключением того, что теперь у нас есть еще одна ось для рассмотрения. Предположим, в дополнение к показу прогнозируемой выручки в 2019 году на основе данных за 2018 год и минимального ожидаемого темпа роста, у нас теперь также есть предполагаемая ставка дисконтирования.
- Во-первых, у нас будет таблица, показанная ниже.
- Теперь мы дадим ссылку на минимальный прогнозируемый доход на 2019 год, т.е. от ячейки B5 до ячейки D8.
- Теперь мы выберем всю таблицу, т. Е. D8: J18, а затем перейдем в Данные -> Прогноз -> Анализ «что если» -> Таблица данных.
- Это откроет диалоговое окно Data Table. Здесь мы введем минимальный процент приращения от ячейки B3 в ячейке ввода столбца. Причина этого заключается в том, что наши прогнозируемые процентные доли роста в таблице расположены в столбчатой форме. Теперь мы также дополнительно введем минимальный процент скидки от ячейки B4 в ячейке ввода строки. Причина этого заключается в том, что наши прогнозируемые проценты дисконтирования в таблице расположены в ряд.
- Нажмите ОК. Это позволит анализу «что, если» автоматически заполнить таблицу прогнозируемым доходом с различными процентами приращения в соответствии с процентами дисконта.
Что нужно помнить о модели данных в Excel
- После успешного вычисления значений из таблицы данных простая отмена, то есть Ctrl + Z, не будет работать. Однако можно вручную удалить значения из таблицы.
- Невозможно удалить одну ячейку из таблицы. Он описывается как массив внутри Excel, поэтому нам придется удалить все значения.
- Нам нужно правильно выбрать ячейку ввода строки и ячейку ввода столбца.
- Таблицу данных, в отличие от сводной таблицы, не нужно обновлять каждый раз.
- Используя модель данных в Excel, мы можем не только повысить производительность, но и облегчить требования к памяти в больших рабочих листах.
- Модели данных также значительно упрощают наш анализ по сравнению с использованием ряда сложных формул по всей книге.
Рекомендуемые статьи
Это руководство по модели данных в Excel. Здесь мы обсудим, как создать модель данных в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: