Зведені таблиці Excel

Ідея зведення даних заснована на відображенні підсумків, розміщених на перекрестиях категорій, розміщених в заголовках рядків і стовпців У міру приміщення категорій в область заголовків таблиця автоматично оновлюється, групуючи дані по всіх вибраних в поточний момент категоріями (рис 481)

Рис 481 Зведена таблиця Excel

# Всі малюнки і описи функцій Excel засновані на версії Microsoft Excel 2003

На замітку Еквівалентні функції доступні у всіх версіях, починаючи з Excel 2000, хоча деякі деталі можуть відрізнятися

Доступні поля даних, відображені в списку Field List, можна перетягувати в будь-яку з чотирьох областей таблиці

■ Data Ця область розташована в центрі таблиці і містить підсумкові значення даних, наприклад суми обсягів Інтернет-продажів (Див рис 481)

■ Row headers Дані категорій, що забезпечують угруповання даних в рядки Вони відображаються в лівій частині таблиці (на рис 481 це категорія Country)

■ Column headers Дані категорій, що забезпечують угруповання по стовпцях, – вони відображаються у верхній частині таблиці (На рис 481 це Fiscal Year)

■ Page Область, що відповідає за установку загального фільтра даних, що консолідуються у зведеній таблиці Вона не робить ніякого впливу на композицію таблиці На рис 481 ця область не відображено – вона відзначена в таблиці міткою Drop Page Fields Here

На додаток до фільтрів, застосовуваним до всієї таблиці, в будь-якому полі заголовків стовпця або рядка можна вибрати будь-яке підмножина значень категорій Після деякої практики процес створення потрібного подання в середовищі зведеної таблиці стане надзвичайно ефективним Єдиним стримуючим фактором стане тільки швидкість роботи вихідного джерела даних

При створенні зведеної таблиці на основі реляційного джерела даних відображаються дані кешуються в робочій книзі Excel Таким чином, аналіз великих обсягів даних зажадає створення файлів робочих книг великих розмірів При використанні багатовимірних джерел даних (кубів) швидкість перебудови зведених таблиць при виконанні в них змін залежить в основному від швидкодії виконання запитів сервером служби аналізу Для роботи з багатовимірними джерелами даних в автономному режимі буде потрібно створення локального файлу куба

Підключення до багатовимірним джерел даних

У той час як зведені таблиці можуть створюватися на основі списків даних, згенерованих в Excel, вони можуть використовувати і зовнішні джерела даних Доступ до цих даних вимагає створення підключення до ним Як приклад ми створимо зведену таблицю, вибравши в меню Excel пункт Дані ^ Зведена таблиця (Data1^ PivotTable) Відкриється майстер зведених таблиць, що містить кілька послідовних сторінок

1 У розділі вибору типу джерела даних встановіть перемикач в положення У зовнішньому джерелі даних (External Data), а в розділі виду створюваного звіту-в положення Зведена таблиця (PivotTable) або Зведена діаграма (зі зведеною таблицею) (PivotTable / PivotChart) Клацніть на кнопці Далі (Next)

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Клацніть на кнопці Отримати дані (Get Data) Відкриється діалогове вікно вибору джерел даних, в якому слід перейти до вкладки Куби OLAP (OLAP Cubes) Якщо потрібний куб вже існує в списку, виберіть його, клацніть на кнопці Далі і відразу переходите до п 3

• Додайте до списку новий куб, вибравши пункт снів джерело даних> () і клацнувши на кнопці ОК Після цього відкриється діалогове вікно створення нового джерела даних, показане на рис 482

Puc 482 Діалогове вікно створення нового джерела даних

• Введіть імя джерела даних і в якості постачальника виберіть Microsoft OLE DB Provider for Analysis Services 90

Так як джерело даних специфічний для кожного куба, найкраще в імені джерела даних відображати назви бази даних служби аналізу і назва самого куба При бажанні в імя можна також додати й імя сервера і середовища, якщо опрашиваемая база даних існує в багатьох місцях Наприклад, імя джерела даних може бути наступним:

Development-AdventureWorksDW-Adventureworks Cube

• Клацніть на кнопці Content, щоб за допомогою майстра багатовимірних підключень вибрати сервер і базу даних

• На першій сторінці відкрився майстра (рис 483) введіть імя сервера Зазвичай реєстраційна інформацію не потрібно, тому що служба аналізу, як правило, використовує довірчі підключення Якщо був створений локальний файл куба, встановіть перемикач в положення Cube File і виберіть потрібний файл з розширенням cub (Процес створення локального файлу куба і вилучення в нього даних докладно описаний в документації до інструкції MDX CREATE GLOBAL CUBE в утиліті Books Online)

• На другій сторінці майстра виберіть базу даних, яка містить цікавий для вас куб

Puc 483 Діалогове вікно створення підключення до багатомірного джерела даних

• Повернувшись до діалогового вікна створення нового джерела даних, виберіть в списку куб Зверніть увагу на те, що до списку кубів включені також і їх проекції Клацніть на кнопці ОК

• Повернувшись до діалогового вікна вибору джерел даних, виберіть щойно створений джерело і клацніть на кнопці ОК

• Повернувшись до майстра зведених таблиць, клацніть на кнопці Далі, щоб перейти до п 3

3 Перевірте правильність розміщення створюваної зведеної таблиці (робочий лист і координати лівого верхнього кута) і клацніть на кнопці Готово (Finish), щоб завершити роботу майстра

Як бачите, при першій доступі до кубу Excel збирає деякі фрагменти інформації

про нього наступні ж операції створення зведених таблиць на основі цього ж куба зажадають виконання простого трехшаговий процесу Після завершення роботи майстра займіться компонуванням отриманих даних, як буде описано в розділі Проектування зведених таблиць.

Підключення до реляційних джерел даних

Для отримання даних з реляційного джерела ви також можете скористатися майстром зведених таблиць і діаграм

1 Виберіть у меню Excel пункт Дані ^ Зведена таблиця

2 Клацніть на кнопці Отримати дані, щоб відкрити діалогове вікно вибору джерел даних У цьому діалоговому вікні зверніть увагу на прапорець Використовувати майстер запитів (Use the Query Wizard), що знаходиться в нижній частині Якщо цей прапорець встановлений, то запуститься майстер запитів, що пропонує вибрати джерело даних і послідовно проводить користувача по процесу створення запиту до однієї таблиці Якщо зняти цей прапорець, то аплет Microsoft Query дозволить створити більш складні запити У нашому прикладі ми встановимо даний прапорець

3 У вікні, майстра запитів, перейдіть до вкладки Бази даних (Databases), щоб почати створення нового запиту, або до вкладки Запити (Queries), щоб почати процес з раніше збереженого запиту Якщо ви виберете існуючий запит, відразу переходите до п 4

Вкладка Бази даних заповнена усіма джерелами реляційних даних, раніше створеними в майстрові або певними аплету Джерела даних (ODBC) папки Адміністрування панелі управління Windows Якщо потрібної бази даних у списку немає, виберіть пункт <Нове джерело даних> () Заповнення діалогового вікна створення нового джерела даних вимагає виконання кількох додаткових дій

• Присвойте джерела даних імя Так як джерела даних нерозривно повязані з базами даних, непогано в імені відобразити імя бази даних і, при необхідності, імя сервера, на якому вона розміщена (наприклад, Development-Adventureworks)

• Виберіть відповідний драйвер (наприклад, SQL Server Native Client для бази даних SQL Server)

• Клацніть на кнопці Звязок, щоб підключитися до вибраного сервера і бази даних за допомогою специфічного для драйвера діалогового вікна (приклад для SQL Server показаний на рис 484) Введіть імя сервера та реєстраційні дані, після чого розширте діалогове вікно, клацнувши на кнопці Параметри (Options), і виберіть необхідну базу даних Клацніть на кнопці ОК

• Повернувшись до діалогового вікна створення нового джерела даних, якщо потрібно, виберіть у джерелі даних таблицю, використовувану за замовчуванням Ця таблиця буде автоматично вибиратися при кожному використанні джерела даних Клацніть на кнопці ОК

• Повернувшись до вікна вибору джерел даних, виберіть щойно створений джерело і клацніть на кнопці ОК У Залежно від установки прапорця Використовувати майстер запитів буде відкритий або майстер запитів, або аплет Microsoft Query Особливості використання кожного з цих інструментів при створенні запитів будуть описані в наступних розділах

Рис 484 Діалогове вікно параметрів підключення до SQL Server

Коли управління буде повернуто у майстер зведених таблиць, поряд з кнопкою Отримати дані відобразиться повідомлення про отримання даних

4 Перевірте правильність розміщення створюваної зведеної таблиці (робочий лист і координати лівого верхнього кута) і клацніть на кнопці Готово (Finish), щоб завершити роботу майстра

5 Перевірте правильність розміщення створюваної зведеної таблиці (робочий лист і координати лівого верхнього кута) і клацніть на кнопці Готово (Finish), щоб завершити роботу майстра

Після завершення роботи майстра займіться компонуванням отриманих даних, як описано нижче, в розділі Проектування зведених таблиць.

Майстер запитів

Як було описано вище, майстер запитів запускається після того, як було вибрано джерело даних, за умови, що прапорець Використовувати майстер запитів також був встановлений Цей майстер проведе вас по чотирьох сторінкам визначення запиту до однієї таблиці при цьому ви не зможете створити жодного обєднання або звернення до збереженої процедурою Замість цього у формах, пропонованих майстром, вам буде потрібно вибрати таблицю, стовпці, умови фільтрації і порядок сортування Для роботи з майстром-яких знань про мову SQL не потрібно По завершенні роботи майстра вам буде запропоновано або зберегти запит для подальшого використання, або продовжити роботу над ним аплету Microsoft Query Слід зазначити, що клацання на кнопці Відміна в майстрові запитів також призведе до перемикання на аплет Microsoft Query, де доведеться продовжити процес формування запиту

Microsoft Query

Аплет Microsoft Query запускається після вибору джерела даних і зняття прапорця Використовувати майстер запитів Цей аплет реалізує графічне середовище, подібну інтерфейсу програми Microsoft Access, з областю для графічного включення таблиць і створення відносин між ними, областю створення умов фільтрації і областю перегляду результатів запиту У цьому середовищі конструктора доступний ряд корисних операцій

■ Клацніть на кнопці SQL панелі інструментів, і ви отримаєте можливість вручну ввести запит, не звертаючись до графічного інтерфейсу Зазвичай цю можливість використовують при створенні складних запитів, зокрема, що використовують збережені процедури, вкладені запити та пропозиції HAVING

■ Виберіть у меню пункт Таблицям Додати таблицю (Tables ^ Add Tables) або клацніть на однойменній кнопці панелі інструментів Обрані в діалоговому вікні таблиці будуть додані на верхню панель конструктора запитів з певними за замовчуванням відносинами За допомогою перетягування назв полів ви можете створити додаткові відносини ви також можете видалити ті лінії відносин, які вважаєте непотрібними

■ Вимикачі Таблиці (Tables) і Критерії (Criteria) меню Вид (View) дозволяють відображати і приховувати відповідні панелі вікна аплета

■ Установка критерію виконується за допомогою вибору імені стовпця у верхньому рядку і введення однієї чи більше рядків критеріїв в наступних рядках Для створення одного критерію просто введіть його Щоб застосувати оператор подоби LIKE з використанням шаблону, введіть його, як у наступному прикладі: LIKE А% 1 (У даному прикладі будуть відібрані всі записи, що починаються з символу А) Еслй для відбору слід використовувати кілька значень, введіть їх в окремих рядках

■ Використовуйте перемикач Автоматичний режим (Automatic Query) меню Записи (Records) для включення і відключення повторного запуску запиту після кожного його зміни В довгограючих запитах відключення цього режиму дозволить заощадити масу часу При відключеному автоматичному режимі перезапуск запиту виконується з допомогою пункту меню Записи ^ Виконати запит (Records ^ Query Now)

■ Вибравши пункт меню Записи ^ Сортувати (Records ^ Sort), можна встановити порядок сортування результатів запиту

Проектування зведених таблиць

Найпростішим способом створення і видалення полів у перехресної таблиці є їх перетягування в неї з панелі списку полів Для того щоб таке стало можливим, поточна комірка повинна знаходитися у зведеній таблиці Клацніть правою кнопкою миші в зведеній таблиці і виберіть у контекстному меню пункт Show Field List або Hide Field List, щоб відобразити або приховати список полів відповідно

Формат списку полів зведеної таблиці реляційного джерела даних являє собою список імен полів, в той час як для багатовимірних джерел відображаються список вимірювань у верхній частині і список заходів – У нижній Заходи можуть відображатися лише у середній частині (даних) зведеної таблиці, у той час як вимірювання можна поміщати в заголовки рядків, стовпців і сторінки Для спрощення ідентифікації вимірювання і заходи позначаються різними значками

Кожне поле, поміщене в зведену таблицю, закріплюється заголовком поля, які представляють собою список, що розкривається Розкриваються списки заголовків рядків і стовпців містять дерево значень, в якому елементи можна вибрати декількома способами Клацання на елементі Show All Items дозволяє встановити прапорці у всіх невибраних значень Зняття прапорця з елемента виключає відповідне значення з таблиці, а подвійний прапорець призводить до відображення всіх нащадків обраного елемента На рис 485 наведено приклад виділення елементів списку, що розкривається значень Дочірні елементи і подвійні прапорці недоступні в реляційних джерелах даних і однорівневих вимірах

Puc 485 Вибір значень в заголовках рядків / стовпців (а) і вийшли в результаті заголовки (б)

Всі поля в центральній області (даних) відображаються під єдиним заголовком – Data Цей заголовок можна використовувати для видалення непотрібних полів даних, знявши відповідні прапорці в розкривається списку

Розкриваються списки, повязані з полем сторінки, дозволяють застосовувати фільтри до всієї таблиці У прикладі на рис 486, а зведена таблиця обмежується відображенням даних, повязаних з клієнтами з Німеччини Багатовимірні джерела даних дозволяють включати у фільтр безліч значень, як показано на рис 486, б

Рис 486 Вибір полів фільтра сторінки в одиночному (а) і множині (б) режимах

При роботі зі зведеними таблицями скористайтеся наступними порадами

■ У багаторівневих вимірах після подвійного клацання на елементі відобразяться всі його нащадки Відображення / приховування всіх нащадків деякого рівня виконується за допомогою клацання правою кнопкою миші на заголовку поля і вибору відповідної команди в підміню Group and Show Detail

■ У зведених таблицях, заснованих на реляційних джерелах даних, подвійне клацання на комірці в області даних призведе до створення нового робочого листа, що відображає рядки деталей, на основі яких було сформовано вбрання підсумкове значення

■ Для відкриття діалогового вікна PivotTable Field двічі клацніть на заголовку поля (або клацніть на ньому правою кнопкою миші і виберіть в контекстному меню пункт Field Settings) У цьому вікні можна виконати ряд дій

• Змінити екранне імя поля

• Вибрати відповідний числовий формат для поля даних На відміну від прямих команд форматування вибрані у вікні формати зберігаються при оновленнях зведеної таблиці

• Змінити для полів даних, заснованих на реляційних джерелах, використовувану підсумкову функцію, наприклад на Min, Max, Average, Sum, Count і тп

• Вибрати альтернативне відображення полів даних за допомогою клацання на відповідній кнопці параметрів, наприклад Running Total (Накопичувальний підсумок),% of Total (Відсоток щодо підсумку) і тп

• Включити і відключити відображення проміжних підсумків для полів заголовків рядків і стовпців

• Вибрати клацанням на кнопці Advanced полів заголовків рядків і стовпців режим сортування та порядок відображення найбільших (Найменших) значень

• Відкоригувати параметри розкладки заголовків рядків і стовпців Доступні таблична розкладка і організована за допомогою виділення, відображення підсумків над або під деталями, розриви сторінок і тд

■ Клацніть правою кнопкою миші на зведеній таблиці і виберіть у контекстному меню пункт Table Options Відкриється діалогове вікно, що дозволяє скорегувати специфічні для таблиці настройки, такі як відображення загальних підсумків і параметри форматування

■ Клацніть правою кнопкою миші на зведеній таблиці і виберіть у контекстному меню пункт Refresh Data Зведена таблиця буде оновлена ​​поточними даними, а список полів – назвами поточних полів (у разі зміни структури джерела даних)

■ На панелі інструментів зведеної таблиці в списку PivotTable виберіть пункт Format Report, щоб відкрити доступ до списку визначених форматів, які можна застосувати до всієї зведеної таблиці Ці формати допоможуть однією дією надати таблиці такий зовнішній вигляд, якого достатньо складно було б домогтися за допомогою ручного форматування

Проектування зведених діаграм

Робота зведених діаграм в чому схожа з звичайними діаграмами Excel, за винятком того, що вони нерозривно повязані зі зведеними таблицями Коли зведена таблиця змінюється, це відразу ж позначається на діаграмі, і навпаки Зведена діаграма створюється автоматично, якщо на першій сторінці майстра зведених таблиць перемикач був встановлений у відповідне положення Також зведену діаграму можна створити і вручну, клацнувши правою кнопкою миші на зведеній таблиці і вибравши в контекстному меню пункт Зведена діаграма (PivotChart) За замовчуванням зведена діаграма створюється на окремій вкладці (робочому аркуші), однак при бажанні її можна скопіювати і вставити на будь-який інший лист

У той час як відокремлені зведені таблиці часто містять безліч заголовків рядків і стовпців, зведені таблиці, повязані з діаграмами, повинні бути відносно простими, щоб полегшити читання діаграм Як приклад на рис 487 показана звід

ная діаграма, заснована на зведеній таблиці з одним заголовком рядків (час) і одним заголовком стовпців (ряд даних) Незважаючи на те що зведена таблиця, повязана з цією діаграмою, відображає всі три рівня часового виміру, на діаграмі показаний тільки нижній рівень, щоб уникнути проблем масштабування

Рис 487 Приклад зведеної діаграми

На зведеній діаграмі відображаються ті ж заголовки полів, що і в асоційованої зведеній таблиці Заголовки рядків відображаються вздовж нижньої, горизонтальній осі, заголовки стовпців – вздовж лівої, вертикальної осі, а заголовки сторінок – вгорі Розкриваються списки заголовків дозволяють виконати практично таке ж конфігурування даних, як і у зведеній таблиці При відображеному списку полів можна змінити і вміст діаграми Якщо потрібно, заголовки полів можна приховати, клацнувши правою кнопкою миші на одному з заголовків і вибравши в контекстному меню пункт Hide PivotChart Field Buttons Подібно звичайної діаграмі Excel, тип діаграми, її параметри і команди форматування доступні в контекстному меню

Діапазони даних Excel

Визначення діапазонів даних аналогічно створенню зведеної таблиці, заснованої на реляційному джерелі даних, за винятком того, що повернуті дані відображаються у вигляді детального списку Це дозволяє відобразити у рядках і стовпцях робочого листа вміст таблиць, результати збереженої процедури або будь-якого запиту Такий список даних називають діапазоном

Щоб визначити новий діапазон даних, виберіть у меню Excel пункт Дані ^ Імпорт зовнішніх даних ^ Створити запит (Data ^ lmport External Data ^ New Database Query), після чого створіть запит (див вище розділ Підключення до реляційних джерел даних)

Після того як діапазон даних буде поміщений на робочий лист, клацання на ньому правою кнопкою миші відкриває контекстне меню з низкою доступних операцій

■ Оновити дані (Refresh Data) Перезапуск запиту і приміщення в діапазон найсвіжіших даних

■ Змінити запит (Edit Query) Відкриття майстра запитів або аплета Microsoft Query для зміни вмісту або організації повертаються даних

■ Властивості діапазону даних (Data Range Properties) Відкриває можливість змінити графік оновлення даних, параметри розкладки і форматування, визначити режим застосування змін в процесі оновлення, і тд

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*