Настройка сводных таблиц Excel 2010

На протяжении многих лет между Microsoft Excel и платформами бизнес-аналитики высшего уровня (BI) существует разрыв. Улучшения в сводной таблице Microsoft Excel 2010 вместе с несколькими другими функциями BI сделали его настоящим конкурентом для бизнес-BI. Excel традиционно используется для автономного анализа и стандартного инструмента, в который каждый экспортирует свои окончательные отчеты. Профессиональная бизнес-аналитика традиционно зарезервирована для SAS, Business Objects и SAP.

01 из 15

Конечный результат

Microsoft Excel 2010 (с сводной таблицей Excel 2010) вместе с SQL Server 2008 R2, SharePoint 2010 и бесплатным надстройкой Microsoft Excel 2010 «PowerPivot» привела к созданию высококачественного решения для бизнес-аналитики и отчетности.

В этом руководстве рассматривается простой сценарий с помощью сводной таблицы Excel 2010, связанной с базой данных SQL Server 2008 R2, с использованием простого SQL-запроса.

Продолжить чтение ниже

02 из 15

Вставить сводную таблицу

Вы можете вставить сводную таблицу в новую или существующую книгу Excel. Возможно, вам стоит подумать о том, чтобы позиционировать курсор на несколько строк сверху. Это даст вам пространство для заголовка или информации о компании, если вы разделите рабочий лист или распечатаете его.

• Откройте новую или существующую книгу Excel 2010 и нажмите на ячейку, где вы хотите, чтобы верхний левый угол таблицы сводных данных был.
• Перейдите на вкладку «Вставка» и нажмите «Сводная таблица» в разделе «Таблицы». Выберите Сводная таблица. Это запустит диалоговое окно Create PivotTable.

Продолжить чтение ниже

03 из 15

Подключите сводную таблицу к SQL Server

Excel 2010 может извлекать данные со всех основных поставщиков СУБД (Relational Database Management System). Для подключения по умолчанию должны использоваться драйверы SQL Server. Проверьте их сайт, если вам нужно загрузить драйверы ODBC.

В этом уроке мы подключаемся к SQL Server 2008 R2 (бесплатная версия SQL Express).

1. Откройте форму Create PivotTable. Выберите «Использовать внешний источник данных» и нажмите кнопку «Выбрать соединение». Оставьте место, где будет размещена сводная таблица.
2. Откройте форму «Существующие соединения». Нажмите кнопку «Обзор для большего».
3. Нажмите кнопку «Создать источник», чтобы запустить мастер подключения к данным.
4. Выберите Microsoft SQL Server и нажмите «Далее».
5. Введите имя сервера и учетные данные для входа. Выберите подходящий метод проверки подлинности:
   1. Использовать проверку подлинности Windows: Этот метод использует вашу учетную запись в сети для доступа к базам данных SQL Server.
   2. Используйте следующие имя пользователя и пароль: Этот метод используется, когда SQL Server настроен с автономными пользователями для доступа к базам данных.
6. Замените таблицу на пользовательский SQL, который обеспечит точно данные, которые мы хотим в нашей книге Excel:
   1. Выберите базу данных, к которой вы будете подключаться. В этом примере мы подключаемся к базе данных примеров AdventureWorks, предоставленной Microsoft. Проверьте подключение к определенной таблице и выберите первую таблицу. Помните, что мы не собираемся извлекать данные из этой таблицы.
   2. Нажмите «Готово», который закроет мастер и вернет вас в книгу. Мы будем заменять таблицу-заполнитель для нашего пользовательского SQL-запроса.

Вы вернетесь в форму Create PivotTable (A). Нажмите «ОК».

04 из 15

Сводная таблица временно подключена к таблице SQL

На этом этапе вы подключились к таблице-заполнителю, и у вас есть сводная сводная таблица. Вы можете видеть слева, где находится сводная таблица, а справа - список доступных полей.

Продолжить чтение ниже

05 из 15

Свойства открытого подключения

Убедитесь, что вы находитесь на вкладке «Параметры» и нажмите «Изменить источник данных» в разделе «Данные». Выберите Свойства подключения.

Это вызывает форму Свойства подключения. Перейдите на вкладку «Определение». Это показывает вам информацию о подключении для текущего подключения к SQL Server. Хотя он ссылается на файл соединения, данные фактически встроены в электронную таблицу.

06 из 15

Обновить свойства подключения с запросом

Измените тип команды из таблицы на SQL и перезапишите существующий текст команды с помощью SQL Query. Вот запрос, который мы создали из базы данных примеров AdventureWorks:

SELECT Sales.SalesOrderHeader.SalesOrderID,Sales.SalesOrderHeader.OrderDate,Sales.SalesOrderHeader.ShipDate,Sales.SalesOrderHeader.Status,Sales.SalesOrderHeader.SubTotal,Sales.SalesOrderHeader.TaxAmt,Sales.SalesOrderHeader.Freight,Sales.SalesOrderHeader.TotalDue,Sales.SalesOrderDetail.SalesOrderDetailID,Sales.SalesOrderDetail.OrderQty,Sales.SalesOrderDetail.UnitPrice,Sales.SalesOrderDetail.LineTotal,Production.Product.Name,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Production.Product.ListPrice,Production.Product.ProductLine,Производство.ПродукцияСубтегория.Название AS ProductCategoryFROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINПроизводство.ПродукцияПодробности ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

Нажмите «ОК».

Продолжить чтение ниже

07 из 15

Предупреждение о получении соединения

Появится диалоговое окно Microsoft Excel Warning. Это связано с тем, что мы изменили информацию о соединении. Когда мы изначально создали соединение, он сохранил информацию во внешнем файле .ODC (ODBC Data Connection). Данные в рабочей книге были такими же, как файл .ODC, пока мы не изменили с типа команды таблицы на тип команды SQL на шаге # 6. Предупреждение сообщает вам, что данные больше не синхронизированы, и ссылка на внешний файл в книге будет удалена. Хорошо. Нажмите «Да».

08 из 15

Сводная таблица, подключенная к SQL Server с запросом

Это вернет книгу Excel 2010 с пустой сводной таблицей. Вы можете видеть, что доступные поля теперь разные и соответствуют полям в запросе SQL. Теперь мы можем добавлять поля в сводную таблицу.

Продолжить чтение ниже

09 из 15

Добавить поля в сводную таблицу

В списке полей сводной таблицы перетащите категорию ProductCategory в область Row Labels, OrderDate в область Label столбцов и область TotalDue в значения. Как вы можете видеть, поле даты имеет отдельные даты, поэтому сводная таблица создала столбец для каждой уникальной даты. В Excel 2010 есть встроенные функции, которые помогут нам организовать поля даты.

10 из 15

Добавить группировку для полей даты

Функция группирования позволяет нам организовывать даты по годам, месяцам, кварталам и т. Д. Это поможет суммировать данные и облегчить для пользователя взаимодействие с ним. Щелкните правой кнопкой мыши один из заголовков столбцов даты и выберите «Группа», которая отобразит форму группировки.

Продолжить чтение ниже

11 из 15

Выберите «Группировать по значениям»

В зависимости от типа данных, которые вы группируете, форма будет выглядеть несколько иначе. Excel 2010 позволяет группировать даты, цифры и выбранные текстовые данные. Мы группируем OrderDate в этом учебнике, чтобы форма отображала параметры, относящиеся к группировкам дат.

Нажмите «Месяцы и годы» и нажмите «ОК».

12 из 15

Сводная таблица, сгруппированная по годам и месяцам

Данные группируются по годам сначала, а затем по месяцам. Каждый из них имеет знак «плюс» и «минус», который позволяет вам расширяться и сворачиваться в зависимости от того, как вы хотите видеть данные.

На данный момент сводная таблица довольно полезна. Каждое из полей может быть отфильтровано, но проблема заключается в отсутствии визуальной подсказки относительно текущего состояния фильтров. Чтобы изменить представление, требуется несколько кликов.

13 из 15

Вставить слайсер (новый в Excel 2010)

Слайсеры являются новыми в Excel 2010. Слайсеры в основном эквивалентны визуально настраиваемым фильтрам существующих полей и созданию фильтров отчетов в том случае, если элемент, который вы хотите фильтровать, не находится в текущем представлении сводной таблицы. Эта приятная вещь о Slicers становится очень простой для пользователя, чтобы изменить представление данных в сводной таблице, а также предоставить визуальные индикаторы относительно текущего состояния фильтров.

Чтобы вставить Slicers, откройте вкладку «Параметры» и нажмите «Вставить слайсер» в разделе «Сортировка и фильтр». Выберите Insert Slicer, который открывает форму Insert Slicers. Проверьте, сколько полей вы хотите иметь.

14 из 15

Сводная таблица с удобными слайсерами

Как вы можете видеть, Slicers отображает все данные как выбранные. Пользователю очень ясно, какие данные находятся в текущем представлении сводной таблицы.

15 из 15

Выберите значения из слайсеров, которые обновляют сводную таблицу

Нажмите на различные комбинации значений и посмотрите, как изменяется представление сводной таблицы. Вы можете использовать обычный щелчок Microsoft в Slicers, что означает, что если вы можете использовать Control + Click для выбора нескольких значений или Shift + Click, чтобы выбрать диапазон значений.

Каждый Slicer отображает выбранные значения, что делает его действительно очевидным, каково состояние сводной таблицы в отношении фильтров. Вы можете изменить стили Slicers, если хотите, щелкнув по кнопке Quick Styles в разделе Slicer на вкладке «Параметры».