Одна из вещей, которые я перечисляю больше всего в PowerPivot для Excel, - это возможность добавлять таблицы поиска к вашим наборам данных. В большинстве случаев данные, с которыми вы работаете, не имеют каждого поля, необходимого для анализа. Например, у вас может быть поле даты, но вам нужно группировать данные по кварталам. Вы можете написать формулу, но проще создать простую таблицу поиска в среде PowerPivot.
Вы также можете использовать эту таблицу поиска для другой группировки, такой как имя месяца и первая / вторая половина года. В условиях хранилища данных вы фактически создаете таблицу измерений даты. В этой статье я расскажу вам пару примерных таблиц измерений, чтобы улучшить проект PowerPivot for Excel.
Таблица новых размеров текста (Lookup)
Рассмотрим таблицу с данными заказа. Предположим, что в таблице есть поля для клиента, дата заказа, общая сумма заказа и тип заказа. Мы собираемся сосредоточиться на поле типа заказа. Предположим, что поле типа заказа содержит такие значения, как:
- Нетбуки
- Настольные компьютеры
- Мониторы
- Проекторы
- Принтеры
- Сканеры
- Цифровые фотоаппараты
- Цифровые зеркальные фотокамеры
- Пленочные фотоаппараты
- Видеокамеры
- Офисные телефоны
- Смартфоны
- КПК
- Аксессуары для сотовых телефонов
В действительности у вас бы были коды для них, но чтобы этот пример был простым, предположим, что это фактические значения в таблице заказов. Используя PowerPivot для Excel, вы легко сможете группировать свои заказы по типу заказа.
Что делать, если вам нужна другая группа? Например, предположим, что вам нужна группировка категорий, такая как компьютеры, камеры и телефоны. В таблице заказов нет поля категории, но вы можете легко создать его в качестве таблицы поиска в PowerPivot for Excel. Полная таблица поиска примеров приведена ниже в Таблица 1 .
Вот шаги:
- Шаг 1: Вам нужен отдельный список из поля типа для вашей таблицы поиска. Это будет ваше поле поиска. Из вашего набора данных создайте отдельный список значений из поля типа заказа. Введите отдельный список типов в книгу Excel. Обозначить столбец Тип .
- Шаг 2: В столбце рядом с столбцом поиска (Тип) добавьте новое поле, которое вы хотите сгруппировать. В нашем примере добавьте столбец с меткой, называемой категория .
- Шаг 3: Для каждого значения в вашем отдельном списке значений (типы в этом примере) добавьте соответствующий категория ценности. В нашем простом примере введите либо компьютеры , камеры или же телефоны в категория колонка.
- Шаг 4: Скопируйте Тип а также категория таблицы данных в ваш буфер обмена.
- Шаг 5: Откройте книгу Excel с данными заказа в PowerPivot для Excel. Запустите окно PowerPivot. Нажмите «Вставить», в котором будет отображаться новая таблица поиска. Дайте таблице имя и убедитесь, что вы проверяете Использовать первую строку в качестве заголовков столбцов, Нажмите Хорошо, Вы создали таблицу поиска в PowerPivot.
- Шаг 6: Создайте связь между Тип поле в порядок таблицы и категория в таблице поиска. Нажми на дизайн ленту и выбрать Создание отношений, Сделайте выбор в Создание отношений диалога и нажмите Создайте.
Когда вы создаете сводную таблицу в Excel на основе данных PowerPivot, вы сможете группировать свои новые категория поле. Имейте в виду, что PowerPivot for Excel поддерживает только внутренние подключения. Если у вас есть тип заказа отсутствует в вашей таблице поиска, все соответствующие записи для этого типа будут отсутствовать в любой сводной таблице на основе данных PowerPivot. Вам нужно будет время от времени проверять это.
Таблица измерения даты (Lookup)
В большинстве ваших проектов PowerPivot для Excel, скорее всего, понадобится таблица поиска Date. Большинство наборов данных имеют тип (ы) даты. Существуют функции для расчета года и месяца.
Однако, если вам нужен текст месяца или квартал, вам нужно написать сложную формулу. Гораздо проще включить таблицу измерения даты (lookup) и сопоставить ее с номером месяца в вашем основном наборе данных. Вам нужно будет добавить столбец в свою таблицу заказов, чтобы указать номер месяца из поля даты заказа. Формула DAX для месяц в нашем примере = МЕСЯЦ (Дата заказа), Это вернет число от 1 до 12 для каждой записи. Наша таблица измерений предоставит альтернативные значения, которые ссылаются на номер месяца. Это обеспечит вам гибкость в анализе. Полная таблица размеров выборки приведена ниже в Таблица 2 .
Размер даты или таблица поиска будут включать 12 записей. Столбец месяца будет иметь значения 1 - 12. Другие столбцы будут содержать текст сокращенного месяца, полный текст месяца, квартал и т. Д. Вот шаги:
- Шаг 1: Скопируйте таблицу из Таблицы 2 ниже и вставьте ее в PowerPivot. Вы можете создать эту таблицу в Excel, но я экономлю ваше время. Вы должны иметь возможность вставлять непосредственно из выбранных данных ниже, если вы используете Internet Explorer или Edge. PowerPivot подбирает форматирование таблицы в моем тестировании. Если вы используете другой браузер, вам, возможно, придется сначала вставить в Excel и скопировать его из Excel, чтобы получить форматирование таблицы.
- Шаг 2: Откройте книгу Excel с данными заказа в PowerPivot для Excel. Запустите окно PowerPivot. Нажмите Вставить который приведет к вашей таблице поиска, скопированной из таблицы ниже или из Excel. Дайте таблице имя и убедитесь, что вы проверяете Использовать первую строку в качестве заголовков столбцов, Нажмите Хорошо, Вы создали таблицу поиска даты в PowerPivot.
- Шаг 3: Создать связь между Месяц поле в порядок таблицы и MonthNumber в таблице поиска. Нажми на дизайн ленту и выбрать Создание отношений, Сделайте выбор в Создание отношений диалога и нажмите Создайте.
Опять же, добавив измерение даты, вы сможете группировать данные в сводной таблице с использованием любых значений из таблицы поиска даты. Группировка по кварталу или название месяца будут быстрыми.
Таблицы размеров выборки (Lookup)
Таблица 1
| Тип | категория |
| Нетбуки | компьютер |
| Настольные компьютеры | компьютер |
| Мониторы | компьютер |
| Проекторы и экраны | компьютер |
| Принтеры, сканеры и факс | компьютер |
| Настройка и обслуживание компьютеров | компьютер |
| Компьютеры и комплектующие | компьютер |
| Цифровые фотоаппараты | камера |
| Цифровые зеркальные фотокамеры | камера |
| Пленочные фотоаппараты | камера |
| Видеокамеры | камера |
| Аксессуары для фотоаппаратов и видеокамер | камера |
| Домашние и офисные телефоны | Телефон |
| Телефоны с сенсорным экраном | Телефон |
| Смартфоны и КПК | Телефон |
Таблица 2
| MonthNumber | MonthTextShort | MonthTextFull | четверть | Семестр |
| 1 | январь | январь | Q1 | H1 |
| 2 | февраль | февраль | Q1 | H1 |
| 3 | март | марш | Q1 | H1 |
| 4 | апрель | апрель | Q2 | H1 |
| 5 | май | май | Q2 | H1 |
| 6 | июнь | июнь | Q2 | H1 |
| 7 | июль | июль | Q3 | H2 |
| 8 | август | августейший | Q3 | H2 |
| 9 | сентябрь | сентябрь | Q3 | H2 |
| 10 | октябрь | октября | Q4 | H2 |
| 11 | ноябрь | ноябрь | Q4 | H2 |
| 12 | декабрь | Декабрь | Q4 | H2 |
