Модель данных Excel (Power Pivot) позволяет связать большой набор подробных фактических данных с бюджетом верхнего уровня с помощью объединяющих таблиц.
Бюджеты составляются на верхнем уровне - доход по линейке продуктов по регионам по месяцам. Фактические данные накапливаются медленно с течением времени - счет за счетом, позиция за позицией. Сравнивать небольшой бюджетный файл с объемными фактическими данными всегда было мучительно. Мне нравится этот трюк от Роба Колли, он же PowerPivotPro.com.
Чтобы настроить пример, у вас есть таблица бюджета с 54 строками: одна строка в месяц на регион для каждого продукта.
Файл счета-фактуры находится на уровне детализации: пока в этом году 422 строки.
В мире нет функции ВПР, которая позволила бы вам сопоставить эти два набора данных. Но благодаря Power Pivot (также известной как модель данных в Excel 2013+) это становится легко.
Вам необходимо создать крошечные столики, которые я называю «объединителями», чтобы связать два больших набора данных. В моем случае Product, Region и Date являются общими для двух таблиц. Таблица Product - это крошечная таблица с четырьмя ячейками. То же для региона. Создайте каждый из них, скопировав данные из одной таблицы и используя команду «Удалить дубликаты».
Календарную таблицу справа было сложнее создать. Данные бюджета имеют одну строку за месяц, всегда приходящуюся на конец месяца. Данные счета показывают ежедневные даты, обычно будние дни. Итак, мне пришлось скопировать поле даты из обоих наборов данных в один столбец, а затем удалить дубликаты, чтобы убедиться, что представлены все даты. Затем я =TEXT(J4,"YYYY-MM")создал столбец «Месяц» из ежедневных дат.
Если у вас нет полной надстройки Power Pivot, вам необходимо создать сводную таблицу из таблицы бюджета и установить флажок «Добавить эти данные в модель данных».
Как обсуждалось в предыдущем совете, по мере добавления полей в сводную таблицу вам нужно будет определить шесть отношений. Хотя вы могли сделать это за шесть посещений диалогового окна «Создание взаимосвязи», я запустил надстройку Power Pivot и использовал представление диаграммы для определения шести взаимосвязей.
Вот ключ к выполнению всей этой работы: вы можете использовать числовые поля из бюджета и фактического. Но если вы хотите отобразить в сводной таблице регион, продукт или месяц, они должны быть взяты из объединяемых таблиц!
Вот сводная таблица с данными из пяти таблиц. Столбец A исходит от соединителя области. Строка 2 поступает из средства объединения календаря. Слайсер продукта взят из модуля объединения продуктов. Числа бюджета взяты из таблицы бюджета, а фактические числа взяты из таблицы счета-фактуры.
Это работает, потому что объединяющие таблицы применяют фильтры к таблицам бюджета и фактов. Это прекрасный метод, показывающий, что Power Pivot предназначен не только для больших данных.
Смотреть видео
- У вас есть небольшой набор данных по нисходящему бюджету
- Вы хотите сравнить с набором фактических данных снизу вверх
- Фактические данные могут быть получены из реестра счетов.
- Модель данных позволит вам сравнивать эти наборы данных разного размера.
- Сделайте оба набора данных в таблице Ctrl + T
- Для каждого текстового поля, по которому вы хотите составить отчет, создайте объединительную таблицу
- Скопируйте значения и удалите дубликаты
- Для дат вы можете включить даты из обеих таблиц и преобразовать их в конец месяца.
- Сделайте объединяющими столы Ctrl + T
- Необязательно, но полезно назвать все пять таблиц
- Создайте сводную таблицу из бюджета и выберите модель данных
- Создайте сводную таблицу, используя бюджет и факт из исходных таблиц.
- Все остальные поля должны быть взяты из таблиц объединения.
- Добавить слайсеры по продукту
- Создайте три отношения от бюджета до участников
- Создайте три отношения от фактического к присоединяющимся
- Завтра: как легче строить отношения с Power Pivot и формулами DAX
Стенограмма видео
Изучите Excel из подкаста, выпуск 2016 - Бюджет сверху вниз против фактических данных снизу вверх!
Привет, я подкастирую всю эту книгу, нажмите «i» в правом верхнем углу и следуйте списку воспроизведения.
Эй, я собираюсь прервать разговор, это Билл Джелен из 15 минут. Теперь я понимаю, что это невероятно длинный подкаст, и у вас возникает соблазн просто пролистать его, но позвольте мне коротко рассказать вам об этом. Если вы работаете в Excel 2013, и у вас когда-либо была небольшая таблица бюджета и большая таблица фактических данных, и вам нужно сопоставить их вместе, это потрясающая новая возможность, которая есть у нас в Excel 2013, которую не многие люди объяснили. , и вы, вероятно, не знаете об этом. Если это вы, вы находитесь в 2013 году, и вам нужно сопоставить эти два набора данных, не торопитесь, может быть, сегодня, может быть, завтра, может быть, добавьте его в список для наблюдения, это того стоит, это потрясающая техника.
Хорошо, вот что у нас есть: слева у нас бюджет, этот бюджет, он выполняется на верхнем уровне, сверху вниз, справа для каждой линейки продуктов, для каждого региона, на каждый месяц есть бюджет . Здесь не так много записей, количество 55, в правой части мы пытаемся сравнить это с фактическими. Фактические данные поступают из реестра счетов-фактур, поэтому у нас есть регион, продукт и доход, но это отдельные счета-фактуры, здесь гораздо больше данных, мы уже прошли половину года, а у меня уже есть 423 записи. Хорошо, а как сопоставить эти 55 с этими 423? Может быть, сложно сделать ВПР, сначала вам нужно подвести итоги, но, к счастью, в Excel 2013 модель данных делает это действительно очень просто. Что нам нужно, чтобы позволить этой большой массивной таблице взаимодействовать с этой крошечной таблицей, так это посредники, я называю их участниками.Крошечные столики «Продукт», «Регион» и «Календарь», мы собираемся присоединить бюджет к этим трем таблицам, мы собираемся присоединить фактический к этим трем таблицам, и каким-то чудом сводная таблица заработает. Хорошо, вот как мы это делаем.
Во-первых, мне нужно создать соединителей, поэтому я беру это поле Product из столбца A и копирую его в столбец F, а затем Data, Remove Duplicates, нажимаю OK, и у нас остается крошечная небольшая таблица 1 заголовок 3 ряда. То же самое для региона, возьмите регионы, Ctrl + C, перейдите к столбцу G, вставьте, удалите дубликаты, нажмите ОК, 3 строки 1 заголовок, хорошо. Что касается дат, даты не совпадают, это даты окончания месяца, они фактически хранятся как даты окончания месяца, и это дни недели. Я возьму оба списка, Ctrl + C второй список и вставлю его сюда, Ctrl + V, затем я возьму более короткий список, скопирую его и вставлю ниже, хорошо. И действительно раздражает то, что, даже если они хранятся как даты, они отображаются как месяцы, и Remove Duplicates не видит их одинаковыми.Поэтому, прежде чем я использую «Удалить дубликаты», я должен изменить его на короткую дату. Выберите эти данные, «Данные», «Удалить дубликаты», нажмите «ОК», а затем немного отсортируйте их здесь, чтобы заставить их работать.
Хорошо, теперь я не хочу составлять отчеты по ежедневной дате, поэтому я собираюсь добавить сюда столбец, столбец подстановки с надписью Месяц, и это будет равно EOMONTH этой дате,, 0, что приведет нас к конец месяца. Он отформатирует это как короткую дату и скопирует ее, хорошо. Теперь нам нужно превратить каждое из них в таблицу Ctrl + T, так что отсюда Ctrl + T, Моя таблица имеет заголовки, красиво. Маленькие, он не понимает, что это заголовки там, поэтому мы должны обязательно отметить это и Ctrl + T, хорошо, и они называют эти таблицы Table1, Table2, Table3, действительно скучными именами, верно? Итак, я собираюсь переименовать их и назвать BudTable, ProdTable, RegTable, my CalTable, а затем ActTable, хорошо.
Мы начнем с самой первой таблицы, и, кстати, сегодня мы не собираемся использовать PowerPivot, мы собираемся сделать все это с помощью модели данных. Итак, Excel 2013 или новее, у вас есть эта вставка, сводная таблица, мы собираемся установить флажок «Добавить эти данные в модель данных», нажать ОК, и мы получим наш список полей с волшебной кнопкой Все, которая позволяет я выбираю из всех пяти таблиц в книге «Фактический», «Бюджет», «Календарь», «Продукт», «Регион». Хорошо, поэтому числа будут взяты из таблицы бюджета, я помещу туда бюджет, а из таблицы фактических данных я введу туда фактическое, но тогда вот что для остальной части сводной таблицы. Любые другие текстовые поля, которые мы собираемся разместить в области строк или столбцов или в качестве срезов, должны исходить от объединяющих элементов, они должны исходить из этих таблиц между таблицами.
Хорошо, из таблицы Calendar мы возьмем это поле «Месяц» и поместим его вверху, мы собираемся игнорировать другие отношения прямо сейчас. Я буду создавать отношения, но я хочу создать их все сразу. И в таблице "Регион" положите регионы вниз. Я мог бы положить продукты сбоку, но на самом деле я собираюсь использовать таблицу Product в качестве среза, поэтому «Анализируйте, вставьте срез», вам снова нужно перейти к «Все», если вы еще не использовали таблицу «Продукт». Итак, перейдите в раздел «Все», и вы увидите, что продукт доступен для создания в виде слайсера из продуктов, вот так. Хорошо, на данный момент мы не создали отношений, поэтому все эти числа неверны. И отношения, которые мы должны создать, мы должны создать 3 таблицы из этой небольшой таблицы бюджета, одну для продуктов, одну для регионов, одну для календаря,это 3 отношения. А затем нам нужно создать связи из таблицы Фактические данные с областью продукта в календаре, итого 6 таблиц. И да, это определенно было бы проще, если бы у нас был PowerPivot, но у нас его нет или, допустим, нет.
И поэтому я собираюсь использовать старомодный способ, диалог Create здесь, где у нас есть таблица бюджета слева, и мы собираемся использовать поле Region и свяжем его с таблицей Region, полем Region. . Хорошо, 1/6 созданы. Я выберу «Создать», снова из таблицы «Бюджет» мы перейдем к «Продукт», а затем свяжем его с таблицей «Продукт» и «Продукт», нажмите «ОК». Из таблицы бюджета в поле «Дата» мы переходим в таблицу «Календарь», а в поле «Судьба» нажимаем «ОК», мы на полпути, хорошо. Из таблицы «Фактические данные» мы переходим «Регион», затем в таблицу «Регион» нажимаем «ОК», из таблицы «Фактические данные» - в «Продукт», а из таблицы «Фактические данные» - в «Календарь». Я на самом деле возьму Ценности и опущу их, хорошо. Дизайн, Макет отчета, Показать в табличной форме, чтобы получить вид, который я предпочитаю, Повторить все метки элементов, хорошо,это просто потрясающе! Теперь у нас есть эта крошечная таблица, около 50 записей в этой таблице из сотен записей, и мы создали единую сводную таблицу благодаря модели данных. Для каждого, где мы видим бюджет, мы можем видеть доход, он разбит по регионам, по месяцам и может быть разрезан по продуктам.
Эта концепция пришла ко мне от Роба Колли, который работает с Power Pivot Pro, и Роб написал много книг, последняя из которых - «Power Pivot и Power BI». Я думаю, что этот был на самом деле в книге «Алхимия Power Pivot», это тот, который я видел и сказал: «Ну, это, хотя у меня нет миллионов строк для отчета через Power Pivot, это тот, который сделали ОГРОМНОЕ изменение в моей жизни, имея два набора данных несоответствующих размеров и требуя отчитываться по обоим ». Что ж, этот и многие другие примеры находятся в этой книге, в конечном итоге я получу весь подкаст с книгой, который, похоже, займет два с половиной месяца. Но вы можете получить всю книгу сегодня, в то же время, пойти туда, купить книгу, 10 долларов за электронную книгу, 25 долларов за печатную книгу, и вы можете получить все эти советы сразу.
Хорошо, это действительно длинный эпизод: у нас есть небольшой бюджет сверху вниз и фактический снизу вверх, они разных размеров, но с использованием модели данных в Excel 2013 … И, кстати, если вы в 2010 году, вы могли бы Теоретически, сделайте это, установив надстройку Power Pivot, и выполните все эти шаги еще в 2010 году. Сделайте оба набора данных в таблице Ctrl + T, а затем объедините свои таблицы для всего, о чем вы хотите сообщить, в метка строки, или метка столбца, или срезы, поэтому скопируйте эти значения и удалите дубликаты для дат. Я фактически взял значения из обеих таблиц, потому что в каждой было несколько уникальных значений, а затем я использовал EOMONTH, чтобы добраться туда, сделать эти объединяемые таблицы управляемыми. Это необязательно, но я назвал все 5 таблиц, потому что при настройке этих отношений проще, чем называться Table1,Таблица2, Таблица3.
Итак, начните с таблицы бюджета, вставки, сводной таблицы, установите флажок для модели данных, а затем создайте сводную таблицу с использованием бюджета и факта. Все остальное поступает из объединяющих таблиц, поэтому область и месяц в области строк и столбцов, срезы взяты из таблицы Product. А затем нам пришлось создать 3 отношения от бюджета к участникам, 3 отношения от фактического к участникам, и у нас получилась потрясающая сводная таблица. А завтра мы рассмотрим использование вкладки Power Pivot и выполнение некоторых дополнительных расчетов. Так что все это возможно, это когда мы хотим вставить вычисляемое поле, и тогда вам нужно платить дополнительно 2 доллара в месяц, чтобы получить версию Office 365 Pro Plus.
Что ж, спасибо Робу Колли из Power Pivot Pro за этот совет, и спасибо, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2016.xlsx
