Excel mac os добавить в модель данных
Обновлено: 07.07.2024
Модель данных позволяет интегрировать данные из нескольких таблиц, эффективно построение источника реляционных данных Excel книге. В Excel данных модели данных применяются прозрачно, что обеспечивает табличные данные, используемые в сводных таблицах и на сводных таблицах. Модель данных визуализируются как набор таблиц в списке полей, и в большинстве моментов вы даже не узнаете, что она там.
Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.
Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.
Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.
Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,
Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.
Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.
Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.
Начало работы
Во-первых, вам нужно получить некоторые данные.
В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.
В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.
Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, проверьте параметр Включить выбор нескольких таблиц. При выборе нескольких таблиц Excel автоматически создает модель данных.
Примечание: В этих примерах мы используем книгу Excel с вымышленными сведениями об учащихся в классах и оценках. Вы можете скачать образец книги модели данныхдля учащихся и следуйте этим командам. Вы также можете скачать версию с завершенной моделью данных..
Выберите одну или несколько таблиц и нажмите кнопку Загрузить.
Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).
Теперь у вас есть модель данных, которая содержит все импортируемые таблицы, и они будут отображаться в списке полей таблицы.
Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.
Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.
Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.
Связи между таблицами могут создаваться автоматически при импорте связанных таблиц, у которых есть связи по первичному и внешнему ключу. Excel обычно может использовать импортированные данные о связях в качестве основы для связей между таблицами в модели данных.
Советы по уменьшению размера модели данных см. в Excel и Power Pivot.
Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.
Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.
Создание связей между таблицами
Далее нужно создать связи между таблицами, чтобы можно было извлекть данные из любой из них. Каждая таблица должна иметь первичный ключ или уникальный идентификатор поля, например Код учащегося или номер класса. Проще всего перетащить эти поля, чтобы соединить их в представлении диаграммы Power Pivot.
Перейдите на > PowerPivot .
На вкладке Главная выберите представление диаграммы.
Будут показаны все импортируемые таблицы, и вам может потребоваться некоторое время, чтобы их можно было отобразить в зависимости от их количество.
Затем перетащите поле первичного ключа из одной таблицы в следующую. Ниже приведен пример представления диаграммы таблиц наших учащихся:
Мы создали следующие ссылки:
tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося
Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".
tbl_Semesters | ИД семестра > tbl_Grades | Семестр
tbl_Classes | Номер > tbl_Grades | Номер класса
Имена полей не должны быть одинаковыми для создания связи, но они должны быть одного типа данных.
Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.
Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).
Создание и создание с помощью модели данных для создания сводная диаграмма
Книга Excel может содержать только одну модель данных, но она содержит несколько таблиц, которые можно многократно использовать во всей книге. Вы можете добавить дополнительные таблицы в существующую модель данных в любое время.
В Power Pivot, перейдите вуправление .
На вкладке Главная выберите вкладку "PivotTable".
Выберите место для размещения таблицы: новый или текущий.
Затем создайте или создайтесетовую диаграмму. Если вы уже создали связи между таблицами, вы можете использовать любое из их полей в таблице. Мы уже создали связи в образце книги модели данных для учащихся.
Добавление имеющихся несвязанных данных в модель данных
Сначала выберите любую ячейку в данных, которые вы хотите добавить в модель. Это может быть любой диапазон данных, но лучше всего отформатированные как Excel таблицы.
Добавьте данные одним из следующих способов.
Щелкните Power Pivot > Добавить в модель данных.
Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".
Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.
Добавление данных в Power Pivot таблицу
В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Вы можете анализировать данные из нескольких таблиц, только если между ними существуют отношения. С Power Pivot вы можете создавать отношения из представления данных или представления схемы. Более того, если вы решили добавить таблицу в Power Pivot, вам также необходимо добавить отношения.
Добавление таблиц Excel в модель данных с помощью сводной таблицы
При создании сводной таблицы в Excel она основана только на одной таблице / диапазоне. Если вы хотите добавить больше таблиц в сводную таблицу, вы можете сделать это с помощью модели данных.
Вы можете суммировать продажи в зависимости от продавца, как указано ниже.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите на БОЛЬШЕ СТОЛОВ.
Нажмите на БОЛЬШЕ СТОЛОВ.
Нажмите вкладку ВСЕ.
Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.
Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.
Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.
Нажмите вкладку ВСЕ.
Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.
Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.
Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.
Под таблицей выберите Продажи.
Под полем Column (Foreign) выберите Region.
Под связанной таблицей выберите продавца.
В поле « Связанный столбец (основной)» выберите «Регион».
Под таблицей выберите Продажи.
Под полем Column (Foreign) выберите Region.
Под связанной таблицей выберите продавца.
В поле « Связанный столбец (основной)» выберите «Регион».
Ваша сводная таблица из двух таблиц на двух листах готова.
Нажмите вкладку POWERPIVOT на ленте.
Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.
Нажмите вкладку POWERPIVOT на ленте.
Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.
Вы можете заметить, что две таблицы Excel, которые вы использовали при создании сводной таблицы, преобразуются в таблицы данных в модели данных.
Добавление таблиц Excel из другой книги в модель данных
Вы можете добавить таблицу Excel из другой книги в модель данных следующим образом:
Нажмите на таблицу продаж.
Нажмите вкладку INSERT.
Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».
Нажмите на таблицу продаж.
Нажмите вкладку INSERT.
Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».
В поле Таблица / Диапазон введите Продажи.
Нажмите на новый лист.
Установите флажок Добавить эти данные в модель данных.
В поле Таблица / Диапазон введите Продажи.
Нажмите на новый лист.
Установите флажок Добавить эти данные в модель данных.
Вы получите пустую сводную таблицу на новом листе только с полями, соответствующими таблице продаж.
Вы добавили данные таблицы продаж в модель данных. Затем вы должны получить данные таблицы Salesperson также в Data Model следующим образом:
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».
Нажмите на вкладку Таблицы.
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».
Допустим вам предстоит работать в Excel, но перед вами стоит выбор: Apple с операционной системой macOS или компьютер на Windows. Прежде чем принимать решение, давайте рассмотрим, чем отличается Excel на этих операционных системах. Различий десятки, но некоторые из них мы описали.
MS Excel for Windows
MS Excel for macOS
Интерфейс Excel macOS и Windows отличается. Приспособиться — не проблема: пользователь может самостоятельно настроить ленту и добавить удобные функции на панель.
Но следующие операции на macOS он точно сделать не сможет:
Автоматическое свертывание ленты. Вы можете ее свернуть, но для того, чтобы она появилась, вам нужно закрепить ее снова. На Windows можно настроить автоматическое разворачивание и свертывание ленты при наведении на панель инструментов;
Масштабирование документа при печати. Если перед тем, как напечатать документ, вы хотите увеличить его и разглядеть мелкие детали, вы не сможете этого сделать.
MS Excel for Windows «Быстрый анализ»
Если вам понадобится провести быстрый анализ каких-либо данных, значок с табличкой и молнией в macOS вы не увидите. Но не волнуйтесь — эти функции можно найти на панели инструментов.
Читайте полезные лайфхаки применения Excel для бизнеса по ссылке.
MS Excel for Windows «Найти и заменить» Это очень удобная функция. Особенно полезна, когда приходится работать с большими массивами данных.
Допустим, у вас есть отчет, в котором какое-то значение повторяется сотни раз и вам нужно его заменить. В macOS это возможно. Но если нужно выбрать параметры форматирования ячеек (жирный шрифт, заливка красным цветом и т. д.), сделать это не удастся. Придется искать другие пути решения проблемы.
MS Excel for Windows «Сводные диаграммы»
MS Excel for macOS «Сводные диаграммы»
При построении сводных диаграмм нет возможности настроить отображение полей с фильтрами на самой диаграмме. Придется возвращаться к сводной таблице и изменять фильтры непосредственно в ней, после чего диаграмма перестроится.
В macOS нет возможности добавить таблицу в модель данных (инструмент, позволяющий структурировать и объединять данные по ключевому признаку). Придется искать другой способ связывания данных, например, писать дополнительные формулы и создавать таблицы.
Этот инструмент сокращает время обработки данных в десятки раз. Но возможности Visual Basic for Application (язык, который используется при написании программных кодов в MS Office) в macOS ограничены.
Запись макроса — наиболее популярная функция среди обычных пользователей в этом разделе. Часто нам приходится повторять действия в работе с Excel. С помощью этой функции их можно записать, и в следующий раз, после запуска кода, Excel все сделает сам.
Вот только в macOS вы не сможете записать макрос, используя относительные ссылки. Если не знаете, что это, пример ниже все объяснит.
Переходим на вкладку «Разработчик» (по умолчанию не отображается, но в настройках Excel можно ее отобразить), находим функцию «Запись макроса», называем его и запускаем.
MS Excel for macOS «Запись макроса»
Допустим, мы занесли в ячейки значения 1, 2, 3, 4 по диагонали.
MS Excel for macOS «Результат записи макроса»
Затем, удаляем данные с листа и запускаем наш макрос, делая при этом активной ячейку, к примеру, A8.
MS Excel for macOS «Запуск макроса»
MS Excel for macOS «Результат запуска макроса»
Как видно из скриншота, значения 2, 3, 4 не переместились ниже относительно значения 1. Это существенно ограничивает при работе с записью макросов.
При выполнении аналогичной функции с включенным параметром «Относительные ссылки» в Excel для Windows мы получаем следующий результат: значения 2, 3 и 4 перемещаются вниз относительно
значения 1.
MS Excel for Windows «Результат запуска макроса»
Вместо вывода
Если вы обрабатываете большие массивы данных, пишете макросы, сводите информацию с разных источников и работаете в Power Query и Power Pivot, стоит задуматься. Excel для macOS не позволит справляться с этими задачами в полном объеме. Но Microsoft исправляет недоработки, а по ссылке вы можете мониторить обновления для macOS.
Сравнение проводилось с использованием MS Office следующих версий:
Функция модели данных в 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. Вы также можете просмотреть наши другие предлагаемые статьи -
В 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 комментария для “Сводная таблица на основе внутренней модели данных”
Читайте также: