Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
Аннотация. В конце предыдущего учебника создание отчетов Power Viewна основе карт, книга Excel содержит данные из различных источников, модель данных, основанную на связях, основанных на Power Pivot, и отчет Power View на основе карт с некоторыми базовыми сведениями об Олимпийских играх. В этом учебнике мы расширяем и оптимизируем книгу с дополнительными данными, интересными рисунками и подготовим ее к созданию потрясающих отчетов Power View.
Примечание: В этой статье описаны модели данных Excel 2013. Тем не менее те же функции моделирования данных и Power Pivot, Excel 2013, также относятся к Excel 2016.
Ниже перечислены разделы этого учебника.
В конце учебника есть тест, с помощью которого можно проверить свои знания.
В этой серии учебников используются данные об олимпийских медалях и спортивных состязаниях, а также странах, принимавших Олимпийские игры. Учебники этой серии
-
Расширение связей модели данных с использованием Excel 2013, Power Pivot и DAX
-
Объединение интернет-данных и настройка параметров отчета Power View по умолчанию
Рекомендуется изучить их по порядку.
В учебниках используется Excel 2013 с включенной надстройкой Power Pivot. Дополнительные сведения о Excel 2013 можно найти здесь. Чтобы получить инструкции по Power Pivot, щелкните здесь.
Импорт ссылок на изображения из Интернета в модель данных
Объем данных постоянно растет, что также является ожиданиями от их визуализации. Дополнительные данные имеют разные перспективы, а также возможности анализа и анализа взаимодействия данных различными способами. Power Pivot Power View объединяет ваши данные , а также внешние данные, и наглядно представить их в интересном и интересном представлении.
В этом разделе вы расширяете модель данных, включая изображения флагов регионов или стран, которые участвуют в Олимпийских играх, а затем добавляете изображения, которые представляют дисциплины Олимпийских игр.
Добавление изображений флага в модель данных
Изображения окаймят визуальное влияние отчетов Power View. В следующих действиях вы добавляете две категории изображений: изображение для каждой дисциплины и изображение флажка, представляю которое представляет каждый регион или страну.
У вас есть две таблицы, которые хорошо подытогот для включения этой информации: таблица Discipline для изображений дисциплин и Таблица Hosts для флагов. Чтобы сделать это интересным, используйте изображения, найденные в Интернете, и ссылки на каждое из них, чтобы они могли отрисовки для всех, кто просматривает отчет, независимо от их местонахождения.
-
После поиска в Интернете вы найдете хороший источник для изображений флагов для каждой страны или региона: CIA.gov World Factbook. Например, при щелчке по следующей ссылке вы получаете изображение флага Для Франции.
https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/fr-lgflag.gif При дальнейшем изучение и поиске других URL-адресов изображений флагов на сайте вы понимаете, что URL-адреса имеют согласованный формат и что единственная переменная — код страны или региона из двух букв. Таким образом, если вы знаете код страны или региона из двух букв, можно просто вставить этот код из двух букв в каждый URL-адрес и получить ссылку на каждый флаг. Это плюс, и при внимательном анализе данных вы понимаете, что таблица Hosts содержит две буквы кодов стран или регионов. Отлично! -
Чтобы сохранить URL-адреса флагов, необходимо создать новое поле в таблице Hosts. В более ранней части учебника для двух полей использовался DAX, и мы будем делать то же самое для URL-адресов флагов. В Power Pivot выберите пустой столбец с заголовком Добавить столбец в таблице Hosts. Введите в столбец формулы следующую формулу DAX (или скопируйте ее и введите в столбец формулы). Он выглядит длинным, но в основном это URL-адрес, который мы хотим использовать из книги фактов CIA.=REPLACE("https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/fr-lgflag.gif",82,2,LOWER([Alpha-2 code])) В этой функции DAX вы сделали несколько вещей, все в одной строке. Во-первых, функция DAX REPLACE заменяет текст в заданной текстовой строке, поэтому с помощью этой функции вы заменяете часть URL-адреса, ссылаясь на флаг Франции (fr) соответствующим кодом из двух букв для каждой страны или региона. Число 82 указывает функции REPLACE на начало замены 82 знаков в строке. 2, которое следует заменить, указывает на количество заменяемой символики. После этого вы могли заметить, что URL-адрес с учетом регистра (вы, конечно же, проверили это), а наши коды из двух букв — в верхнем регистре, поэтому нам пришлось преобразовать их в нижний регистр, вставив их в URL-адрес с помощью функции DAX LOWER.
-
Переименование столбца с помощью URL-адресов флагов в FlagURL. Экран Power Pivot теперь выглядит так, как на следующем экране.
-
Вернись Excel и выберите таблицу на листе Лист1. В области Поля pivotTable выберитеALL. Вы увидите, что добавленное поле FlagURL доступно, как показано на следующем экране.
Примечания: В некоторых случаях код "Альфа-2", используемый сайтом CIA.gov World Factbook, не соответствует официальному коду ISO 3166-1 Alpha-2 в таблице Hosts, что означает, что некоторые флаги отображаются неправильно. Вы можете исправить эту ошибку и получить правильные URL-адреса флагов, выделив следующие подстановки непосредственно в таблице Hosts Excel для каждой затронутых записей. Хорошая новость заключается в том, Power Pivot автоматически обнаруживает изменения, внесенные в Excel, и пересчитывает формулу DAX:
-
изменение AT на AU
-
Добавление спортивных пиктограмм в модель данных
Отчеты Power View интереснее, если изображения связаны с олимпийскими событиями. В этом разделе вы добавим изображения в таблицу Disciplines.
-
Выполнив поиск в Интернете, вы видите, что в Wikimedia Commons есть отличные пиктограммы для каждой олимпийских дисциплин, отправленные Парутакупю. По следующей ссылке показано множество изображений из Parutakupiu.http://commons.wikimedia.org/wiki/user:parutakupiu
-
Однако при взгляде на каждое отдельное изображение общая структура URL-адресов не дает возможность использовать DAX для автоматического создания ссылок на изображения. Вы хотите узнать, сколько дисциплин есть в модели данных, чтобы определить, следует ли вводить ссылки вручную. В Power Pivot выберите таблицу Disciplines и посмотрите в нижней части окна Power Pivot дисциплины. Здесь вы увидите число записей 69, как показано на следующем экране.
Вы решаете, что 69 записей — это не так много, чтобы копировать и вкопировать их вручную, тем более что они будут настолько привлекательны при создании отчетов. -
Чтобы добавить URL-адреса пиктограммы, вам нужен новый столбец в таблице Disciplines. Это представляет интересную проблему: таблица Disciplines была добавлена в модель данных путем импорта базы данных Access, поэтому таблица Disciplines отображается только в Power Pivot, а не в Excel. Но в Power Pivot, вы не можете непосредственно вводить данные в отдельные записи, также называемые строками. Чтобы решить эту проблему, можно создать таблицу на основе данных из таблицы Disciplines, добавить ее в модель данных и создать связь.
-
В Power Pivot скопируйте три столбца в таблице Disciplines. Чтобы выбрать их, наведите курсор на столбец Дисциплина и перетаскиванием в столбец SportID, как показано на следующем экране, а затем выберите главная > буфер обмена > Копировать.
-
В Excel создайте новый или скопированные данные. Отформатировать в виде таблицы в качестве таблицы, как в предыдущих учебниках этого ряда, указав верхнюю строку в качестве меток, а затем привяжем таблицу DiscImage. Также привяжем имя для таблицы DiscImage.
Примечание: Книга со всеми ручным вводом, которая называется DiscImage_table.xlsx, — это один из файлов, скачаемых в первом учебнике этой серии. Чтобы у вас было проще, вы можете скачать его, щелкнув здесь . Ознакомьтесь с следующими шагами, которые можно применить к аналогичным ситуациям с собственными данными.
-
В столбце рядом с sportID в первой строке введите DiscImage. Excel автоматически расширяет таблицу, чтобы включить в нее строку. Ваш экран DiscImage выглядит так же, как на следующем экране.
-
Введите URL-адреса для каждой дисциплины на основе пиктограмм из Wikimedia Commons. Если вы скачали книгу, в которую они уже введены, вы можете скопировать и вкопировать их в этот столбец.
-
В Excel выберитеPower Pivot > Таблицы > добавить в модель данных, чтобы добавить созданную таблицу в модель данных.
-
В Power Pivot, впредставлении диаграммы , создайте связь, перетащив поле DisciplineID из таблицы Disciplines в поле DisciplineID в таблице DiscImage.
Настройка категории данных для правильного отображения изображений
Чтобы отчеты Power View правильно отображали изображения, необходимо правильно указать для категории данных URL-адрес изображения. Power Pivot пытается определить тип данных в модели данных. В этом случае термин (Рекомендуемый) добавляется после выбранной категории, но это не обязательно. Подтвердим это.
-
В Power Pivot выберите таблицу DiscImage, а затем выберите столбец DiscImage.
-
На ленте выберите Расширенные свойства > отчетов > Категория данных и выберите URL-адрес изображения ,как показано на следующем экране. Excel попытки определить категорию данных, а затем пометить выбранную категорию данных как (предложенную).
Ваша модель данных теперь содержит URL-адреса для пиктограмм, которые можно связывать с каждой дисциплиной, а для категории данных правильно установлено url-адрес изображения.
Использование интернет-данных для завершения модели данных
На многих сайтах в Интернете предлагаются данные, которые можно использовать в отчетах, если они надежны и полезны. В этом разделе вы добавите данные о численности населения в модель данных.
Добавление сведений о численности населения в модель данных
Чтобы создать отчеты, которые содержат сведения о численности населения, необходимо найти и включить данные о численности населения в модель данных. Отличным источником таких сведений является банк Worldbank.org данных. После посещения сайта вы найдете следующую страницу, на которую можно выбрать и скачать данные разных стран или регионов.
Существует множество вариантов скачивания данных из Worldbank.org, а также различные интересные отчеты, которые можно создать в результате. В настоящее время вас интересует численность населения в странах или регионах в модели данных. В следующих действиях вы скачиваете таблицу данных о численности населения и добавляете ее в модель данных.
Примечание: Иногда веб-сайты изменяются, поэтому макет Worldbank.org может немного отличаться от описанного ниже. Кроме того, вы можете скачать книгу Excel с именем Population.xlsx, которая уже содержит данные Worldbank.org, созданные с помощью следующих действий, щелкнув здесь.
-
Перейдите на веб-worldbank.org по ссылке выше.
-
В центральном разделе страницы в разделе СТРАНАщелкните выбрать все.
-
В области РЯДнайщите и выберите population, total. На следующем экране показано изображение этого поиска со стрелкой, указывательной на поле поиска.
-
В области ВРЕМЯвыберите 2008 (года, но он соответствует данным Об Олимпийских играх, использованным в этих учебниках)
-
После этого нажмите кнопку СКАЧАТЬ, а затем выберите Excel тип файла. После скачивания имя книги будет не очень прочитано. Переименуем книгу, чтобыPopulation.xls, а затем сохраните ее там, где вы сможете получить к ней доступ в следующем ряду действий.
Теперь можно импортировать данные в модель данных.
-
В книге Excel данных Об Олимпийских играх вставьте новый электронный таблицу и назовите его Population (Население).
-
Перейдите к загруженной Population.xls, откройте ее и скопируйте данные. Помните, что при выборе любой ячейки в наборе данных можно нажать CTRL+A, чтобы выбрать все смежные данные. В книге "Олимпийские игры" в конце книги "Олимпийские игры" в таблицу "Население" в таблицу "Население" в таблицу "Вся таблица".
-
В книге "Олимпийские игры" вы хотите отформатировать данные, которые вы только что вклеили как таблицу, и назвать таблицу "Население". Выбрав любую ячейку в наборе данных, например A1, нажмите CTRL+A, чтобы выбрать все смежные данные, а затем нажмите CTRL+T, чтобы отформатировали данные как таблицу. Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы, как показано ниже.
Форматирование данных в виде таблицы имеет множество преимуществ. Таблице можно присвоить имя, чтобы ее было легче идентифицировать. Также можно установить связи между таблицами, позволяющие просматривать и анализировать данные в сводных таблицах, Power Pivot и Power View. -
На вкладке > КОНСТРУКТОР найдите поле Имя таблицы и введите Population ( Население). Данные о численности населения в столбце "2008". Чтобы все было прямо, переименуем столбец "2008" в таблице "Население" в "Население". Книга будет выглядеть так же, как на следующем экране.
Примечания: В некоторых случаях код страны, используемый сайтом Worldbank.org, не соответствует официальному коду ISO 3166-1 Alpha-3 в таблице Medals, что означает, что некоторые страны и региона не будут отображать данные о численности населения. Это можно исправить, сделав следующие подстановки непосредственно в таблице Population Excel для каждой затронутых записей. Хорошая новость заключается в том, Power Pivot определяет изменения, внесенные в Excel:
-
изменение NLD на NED
-
изменение CHE на SUI
-
-
В Excel добавьте таблицу в модель данных, выбравPower Pivot > Таблицы > Добавитьв модель данных , как показано на следующем экране.
-
Теперь создадим связь. Мы заметили, что код страны или региона в population — это тот же трехзначный код, который был NOC_CountryRegion поле Medals. Отлично, мы можем легко создать связь между этими таблицами. В Power Pivot представлении диаграммы перетащите таблицу Population, чтобы она располагалась рядом с таблицей Medals. Перетащите NOC_CountryRegion таблицы Medals в поле Код страны или региона в таблице Population. Устанавливается связь, как показано на следующем экране.
Это было не так сложно. Модель данных теперь содержит ссылки на флаги, ссылки на изображения дисциплин (ранее они назывались пиктограммами) и новые таблицы, которые содержат сведения о численности населения. У нас есть все виды данных, и мы почти готовы создать привлекательные визуализации для отчетов.
Но сначала давайте упростим создание отчетов, скрыв некоторые таблицы и поля, которые не будут использовать наши отчеты.
Скрытие таблиц и полей для упростить создание отчетов
Возможно, вы заметили, сколько полей в таблице Medals. Многие из них, в том числе многие из них не будут использовать для создания отчета. В этом разделе вы узнаете, как скрыть некоторые из этих полей, чтобы упростить процесс создания отчетов в Power View.
Чтобы увидеть это самостоятельно, выберите лист Power View в Excel. На следующем экране показан список таблиц в полях Power View. Это длинный список таблиц, и во многих таблицах есть поля, которые никогда не будут использовать в отчетах.
Данные по-прежнему важны, но список таблиц и полей слишком длинный и может немного ошеломить. Таблицы и поля можно скрыть в клиентских средствах, таких как совые таблицы и Power View, не удаляя их из модели данных.
На следующих шагах вы скроем несколько таблиц и полей, используя Power Pivot. Если для создания отчетов нужны скрытые таблицы или поля, вы всегда можете вернуться к Power Pivot и отпустить их.
Примечание: При скрытии столбца или поля вы не сможете создавать отчеты и фильтры на основе этих скрытых таблиц или полей.
Скрытие таблиц с помощью Power Pivot
-
В Power Pivot выберите Главная > просмотр >, чтобы убедиться, что выбрано представление данных, а не представление диаграммы.
-
Скроем следующие таблицы, которые, по нашему мнению, не требуют создания отчетов: S_Teams и W_Teams. Вы заметили несколько таблиц, в которых удобно использовать только одно поле. далее в этом учебнике вы найдете решение для них.
-
Щелкните правой кнопкой мыши вкладку W_Teams, найденной в нижней части окна, и выберите скрыть из клиентских средств. На следующем экране показано меню, которое появляется при щелчке правой кнопкой мыши скрытой вкладки таблицы в Power Pivot.
-
Скроем другую таблицу, S_Teams, а также. Обратите внимание на то, что вкладки для скрытых таблиц за серым цветом, как показано на следующем экране.
Скрытие полей с помощью Power Pivot
Кроме того, существуют некоторые поля, которые не пригодятся при создании отчетов. Данные могут иметь важное значение, но благодаря скрытию полей из клиентских средств, таких как скайбли и Power View, навигация и выбор полей, которые нужно включить в отчеты, становятся более четкими.
Ниже кроется набор полей из различных таблиц, которые не нужны в отчетах.
-
В Power Pivot щелкните вкладку Medals. Щелкните правой кнопкой мыши столбец Edition и выберите скрыть от клиентских средств, как показано на следующем экране.
Обратите внимание на то, что столбец становится серым, как вкладки скрытых таблиц серые. -
На вкладке Medals скроем следующие поля из клиентских средств: Event_gender MedalKey.
-
На вкладке События скроем следующие поля из клиентских средств: EventID, SportID.
-
На вкладке Спорт скроем SportID.
Теперь при просмотре листа Power View и полей Power Viewмы видим следующий экран: Это более управляемая.
Скрытие таблиц и столбцов из клиентских средств помогает более плавно создавать отчеты. При необходимости вы можете скрыть как можно меньше таблиц или столбцов, а при необходимости в дальнейшем их можно откроть.
Завершив моделирование данных, вы можете поэкспериментировать с данными. В следующем учебнике мы создадим все виды интересных и привлекательных визуализаций с использованием данных "Олимпийские игры" и созданной вами модели данных.
Контрольная точка и тест
Повторите изученный материал
В этом учебнике вы узнали, как импортировать интернет-данные в модель данных. В Интернете доступно много данных, и знание того, как их найти и включить в отчеты, — отличный инструмент, который можно использовать в наборе знаний для создания отчетов.
Вы также узнали, как включать изображения в модель данных и как создавать формулы DAX, чтобы сглаживать процесс получения URL-адресов в вашем окне обработки данных, чтобы использовать их в отчетах. Вы узнали, как скрыть таблицы и поля, что удобно, если вам нужно создать отчеты и уменьшить беспорядок в таблицах и полях, которые, вероятнее всего, не будут использоваться. Скрытие таблиц и полей особенно удобно, когда другие люди создают отчеты на сфере данных, которые вы предоставляете.
ТЕСТ
Хотите проверить, насколько хорошо вы усвоили пройденный материал? Приступим. Этот тест посвящен функциям, возможностям и требованиям, о которых вы узнали в этом учебнике. Внизу страницы вы найдете ответы на вопросы. Удачи!
Вопрос 1. Какой из следующих способов является допустимым способом включите интернет-данные в модель данных?
А. Скопируйте и вкопируйте данные в виде необработанных Excel, и они будут автоматически включены.
Б. Скопируйте и вкопируйте данные в Excel, отформатируйте их как таблицу, а затем выберитеPower Pivot > Таблицы > Добавитьв модель данных.
В. Создайте формулу DAX в Power Pivot, которая заполняет новый столбец URL-адресами, которые указывают на ресурсы данных Интернета.
Г. Б и В.
Вопрос 2. Что из следующего верно в том, чтобы форматирование данных в виде таблицы в Excel?
А. Вы можете назначить имя таблице, что упрощает ее определение.
Б. Вы можете добавить таблицу в модель данных.
В. Вы можете устанавливать отношения между таблицами, тем самым изумляя и анализируя данные в сводных таблицах, Power Pivot и Power View.
Г. Все вышеперечисленные.
Вопрос 3. Что из следующего верно для скрытых таблиц в Power Pivot ?
А. При скрытии таблицы Power Pivot данные из модели данных.
Б. Скрытие таблицы в Power Pivot предотвращает ее просмотр в клиентских средствах и, следовательно, не позволяет создавать отчеты, в которые используются поля этой таблицы для фильтрации.
В. Скрытие таблицы в Power Pivot не влияет на клиентские средства.
Г. Таблицы в Power Pivot нельзя скрыть, а только поля.
Вопрос 4. True или False: после скрытие поля в Power Pivot, вы не сможете увидеть его или получить к нему доступ, даже из Power Pivot поля.
А. Да
B: ЛОЖЬ
Ответы на вопросы теста
-
Правильный ответ: D
-
Правильный ответ: D
-
Правильный ответ: Б
-
Правильный ответ: Б
Примечания: Ниже перечислены источники данных и изображений в этом цикле учебников.
-
Набор данных об Олимпийских играх © Guardian News & Media Ltd.
-
Изображения флагов из справочника CIA Factbook (cia.gov).
-
Данные о населении из документов Всемирного банка (worldbank.org).
-
Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.