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


Звідна таблиця покликана допомогти користувачеві в інтерактивному режимі упорядкувати і узагальнити велику кількість даних, приведених в списках, таблицях і в базах даних. Перегляд великих таблиць вимагає значних витрат часу. Звідні таблиці плануються так, щоб наочно відображувати інформацію, що цікавить користувача. На їх основі можна створити діаграму, яка відображуватиме всі зміни, що сталися.
Якщо звичайні таблиці можуть бути лише двовимірними, то звідні таблиці багатовимірні, що дозволяє уникнути дублювання даних. Створюючи звідну таблицю, користувач вказує, які Поля і які елементи мають бути представлені в ній. Наприклад, якщо у вас є списки товарів, які продаються в різних магазинах, то назви товарів утворюватимуть поля, а їх конкретна кількість в кожному магазині — елементи. Дані по магазинах, які розташовані в різних містах, можна розташувати на окремих листах. Звідна таблиця допоможе вам проаналізувати сумарний продаж конкретних товарів по тижнях, місяцях, кварталах, позбавить від необхідності переглядати все наявні списки. У звідну таблицю можна включати проміжні і підсумкові суми, розрахункові поля.
Нові дані вносяться до вихідних таблиць, а звідні таблиці призначені лише для читання. Після створення звіту звідної таблиці її структуру можна змінити, перетягуючи поля і елементи за допомогою миші. Звідна таблиця дозволяє узагальнити і проаналізувати дані, які знаходяться в зовнішніх джерелах даних, створених без використання Excel. Для наочнішого відображення даних, що містяться в звідній таблиці, можна на їх основі створити діаграму. При створенні звідної таблиці можна використовувати базу даних, наприклад, таблицю, створену в Access.

Створення звідної таблиці


Як приклад розглянемо створення звідної таблиці, що дозволяє на основі таблиць з вихідними даними виконати аналіз продажу певних товарів в різних містах Росії. У книзі, приведеній на мал. 18.16, показаний продаж декількох моделей автомобілів: Волга, Жигулі, Ока в різних містах Росії: у Москві, Саратові і Тулі. Кожне місто показане на окремому аркуші. Передбачається, що звідні таблиці складаються по чотирьох місяцях: січень, лютий, березень і квітень. Таблиці відформатували з використанням команди Автоформат (AutoFormat) у меню Формат (Format) . Вибраний зразок з підписом Простій (Simple) .



Мал. 18.16 Вихідний список для складання звідної таблиці

Створення звідної таблиці бажано почати з виділення вічка усередині використовуваного списку (це дозволяє автоматично виділити діапазон, що містить вихідні дані).
Положенням перемикача в групі Створити таблицю на основі даних, що знаходяться: (Create Pivot table from data in:) встановите перемикач в положення: у декількох діапазонах консолідації (Multiple consolidation ranges) оскільки джерела даних для створення звідної таблиці, розташовані на різних аркуші Excel.
Призначення інших положень перемикача:

  •  у списку або базі даних Microsoft Office Excel (Microsoft Office Excel list or Database ) — дозволяє створити звіт звідної таблиці або звідної діаграми за даними рядків або стовпців з підписами, розташованих на одному аркуші Excel;
  •  у зовнішньому джерелі даних (External data source) — дозволяє створити звіт звідної таблиці або звідної діаграми за даними зовнішнього файлу або бази даних, наприклад, Microsoft Access, SQL Server, Paradox;.
  •  у декількох діапазонах консолідації — дозволяє створити звіт звідної таблиці або звідної діаграми за даними рядків або стовпців з підписами, розташованих в декількох діапазонах консолідації;
  •  у іншій звідній таблиці або звідній діаграмі (Another PivotTable report or Pivot Chart report) — використовується для створення звіту звідної таблиці або звідної діаграми за даними іншого звіту звідної таблиці в активній книзі.



Мал. 18.17 Вікно майстра звідних таблиць

У розділі Вигляд створюваного звіту (What kind of report do you want to create?) поставте перемикач в положення звідна таблиця (PivotTable) для створення лише звідної таблиці і натискуйте кнопку Далі (Next).
Якщо вихідні дані розташовані на декількох листах, то в наступному діалоговому вікні Майстер звідних таблиць і діаграм — крок 2а з 3 (PivotTable and Pivot Chart Wizard — Step 2a of3) поставте перемикач в положення Створити одне поле сторінки (Create а single page field for ті) оскільки всі листи аналогічні і відрізняються лише містом, в якому реалізовувалася продукція. Натискуйте кнопку Далі (Next) .
На екрані відображуватиметься діалогове вікно Майстер звідних таблиць і діаграм — крок 26 з 3 (PivotTable and PivotChart Wizard — Step 2b of 3) . Клацніть мишею в полі Діапазон (Range) (мал. 18.18). Виділите по черзі на всіх листах вічка з А1 по Е4, і натискуйте кнопку Додати (Add) після кожного виділення для додавання діапазону до списку вихідних діапазонів. Заслання на вихідну область буде додано в список Всі заслання (All references). Кнопка згортання вікна праворуч від поля дозволяє згортати діалогове вікно для виділення кожного діапазону. Повторне натиснення на цю кнопку відновлює вікно.
Можна зрушити діалогове вікно, аби був видний один з кутів діапазону, що виділявся. Поки йде виділення діапазону діалогове вікно автоматично згортається.



Мал. 18.18 Виділення діапазонів таблиць, що підлягають консолідації

Якщо вихідна таблиця знаходиться в іншій книзі, до неї можна перейти за допомогою кнопки Огляд (Browse) . Закінчивши вибір даних для звіту звідної таблиці, натискуйте кнопку Далі (Next).
На останньому кроці майстра звідних таблиць і діаграм вам запропонують положенням перемикача задати місце, де слід помістити звідну таблицю (мал. 18.19):

  •  новий аркуш (New worksheet) — звіт звідної таблиці буде розміщений на новому аркуші тієї ж книги;
  •  існуючий аркуш (Existing worksheet) — звіт звідної таблиці буде розміщений на тому ж аркуші, де знаходиться вихідна таблиця. Кнопка згортання діалогового вікна праворуч від поля введення тимчасово прибирає діалогове вікно з екрану, що дозволяє вказати діапазон вічок, де буде розташована звідна таблиця, шляхом виділення вічок на аркуші. Після цього можна натискувати цю кнопку ще раз для відновлення діалогового вікна.


Кнопку Готово (Finish) доцільно натискувати перш, ніж кнопку Макет (Layout) в наступних випадках:

  •  використовуються зовнішні дані з низькою швидкістю завантаження і оновлення;
  •  планується створення поля сторінки і потрібно задати параметр запиту для кожного елементу окремо;



Мал. 18.19 Вибір місця розташування звідної таблиці

  •  планується створити декілька полів даних і потрібно вказати порядок їх відображення.


Звідну таблицю (мал. 18,20) можна створити шляхом перетягання заголовків полів в необхідну зону аркуша. Для деяких зовнішніх джерел даних, особливо для великих баз даних, .это може виявитися зручнішим в порівнянні з налаштуванням макету безпосередньо на аркуші. Так, якщо звіт створюється на основі даних куба (за допомогою майстра створення куба в Microsoft Query), налаштування макету в діалоговому вікні може значно скоротити час, який потрібний для витягання даних. Тут же можна вибрати параметри для створення полів сторінок, аби дані елементів витягувалися окремо. Параметри полів сторінок доступні лише в тому випадку, якщо джерелом даних звіту не є куб.



Мал. 18.20 Завдання місця розташування полів звідної таблиці

Вправи


1. Складіть список замовлень книг для постачання в різні бібліотеки декількох міст. Проаналізуйте сумарні замовлення на конкретні книги по містах.
2. Складіть список працівників вашої фірми з основними анкетними даними, використовуючи форму (мал. 18.4). Виконаєте пошук даних і їх сортування по заданих критеріях.
3. Створіть звідну таблицю з продажу товарів трьох найменувань за чотири місяці: січень, лютий, березень і квітень для трьох міст: Тула, Орел і Пенза. Відформатуйте таблицю за допомогою команди Автоформат (AutoFormat) у меню Формат (Format). Перейменуйте ярлички листів по назвах міст. Порахуйте виручку (у тисячах рублів) по місяцях і товарах в різних містах і загальну виручку.



Мал. 18.21 Діалогове вікно, використовуване для автоматичного обчислення полів звідної таблиці

Таблиці з даними магазинів матимуть вигляд:

Таблиця №1 місто Тула

 
Січень

Лютий

Березень

Апрель

Овочі

20

30

14

23

Фрукти

30

48

15

24

Ягоди

25

24

16

25


Таблиця №2 місто Орел

 
Січень

Лютий

Березень

Апрель
 
31

21

31

25

Д)п\лкты

32

22

32

23

Ягоди

33

23

33

24


Таблиця №3 місто Пенза

 
Січень

Лютий

Березень

Апрель

Овочі

12

24

24

23

Фрукти

14

21

45

33

Ягоди

17

26

44

32


Роботу виконаєте в наступному порядку:

  •  Клацніть мишею у вічку А2. Введіть текст Овочі. Аналогічно у вічка A3 і А4 введіть відповідно Фрукти і Ягоди.
  •  Виділите вічка з А1 по Е4 і виберіть команду Автоформат (AutoFonnat) у меню Формат (Format).
  •  У списку форматів виберіть Класичний! (Classicl) і натискуйте кнопку ОК.
  •  Клацніть правою кнопкою миші по ярлику першого аркуша і виберіть в контекстному меню команду Переместіть/ськопіровать (Move or Copy) . і встановите в діалоговому вікні, що з'явилося, прапорець Створити копію (Create а сміттю) .
  •  Аналогічним чином створіть третю копію аркуша.
  •  Клацніть правою кнопкою миші по ярлику Аркуш 1(3) (Sheet 1(3)). Виберіть команду Перейменувати (Rename). У полі з ім'ям аркуша введіть Тула. Аналогічним чином двом іншим листам з таблицею привласніть назви міст Орла, Пенза.
  •  Заповните дані по реалізації продукції по кожному з трьох міст, як показано в таблицях 1, 2 і 3 нижче.
  •  Для побудови звідної таблиці виберіть в меню Дані (Data) команду Звідна таблиця (Pivot Table and PivotChart Report). Встановите перемикач в положення В декількох діапазонах консолідації (Multiple consolidation ranges), оскільки дані розташовані на декількох листах книги і натискуйте кнопку Далі.
  •  У наступному діалоговому вікні Майстер звідних таблиць і діаграм — крок 2а з 3 (PivotTable and PivotChart Wizard — Step 2a of3) поставте перемикач в положення Створити одне поле сторінки (Create а single page field for me) оскільки всі листи аналогічні і відрізняються лише містом, в якому реалізовувалася продукція. Клацніть мишею в полі Діапазон (Range). Виділите по черзі на всіх листах вічка з Al no E4, і натискуйте кнопку Додати (Add) після кожного виділення. Кнопка згортання вікна праворуч від поля дозволяє згортати діалогове вікно для виділення кожного діапазону. Повторне натиснення на цю кнопку відновлює вікно. Потім натискуйте кнопку Далі (Next).
  •  Поставте перемикач в положення Помістити таблицю на новий аркуш (New worksheet) і натискуйте кнопку Готово (Finish ).
  •  У звідній таблиці, що з'явилася, двічі клацніть по полю із словом Рядок (Row) . Відкриється діалогове вікно Обчислення звідної таблиці PivotTable Field) (мал. 18.21). Введіть слово Товар замість Рядок і натискуйте ОК. Аналогічно Стовпець поміняйте на Місяць, а сторінка на Місто. Після створення звідної таблиці значення доходів підсумовані по місяцях і назвах товарів. Для аналізу доходів по різних містах відкрийте список міст, що розкривається, у вічку В1, виділите те місто який вас цікавить і натискуйте кнопку ОК. Ви отримаєте підсумовані дані по даному місту.
  •  Клацніть за списком, що розкривається, Товари і зніміть галочок поряд з тими товарами, які вас не цікавить. У таблиці відображуватимуться звідні дані без цього продукту.


Виводи


1. Аби упорядкувати дані по декількох полях, виділите діапазон вічок, який необхідно відсортувати, і виберіть команду Сортування (Sort) у меню Дані (Data).
2. Аби спростити введення і редагування даних при складанні списків в Excel, встановите курсор в одному з вічок списку і виберіть в меню Дані (Data) команду Форма (Form).
3. Для прогнозування залежності виділите діапазон вічок, що містить вихідні значення, і використовуйте діалогове вікно, що відображується після вибору в меню Правка (Edit) команди Заповнити (Fill), Прогресія (Series) .
4. Знайти аргумент, що забезпечує результат, що задається, дозволяє команда Підбір параметра (Goal Seek ) у меню Сервіс (Tools). Рішення знаходиться шляхом послідовних ітерацій.
5. Аби узагальнити однорідні дані, розташовані в декількох областях таблиці або на різних листах, в одній таблиці, вкажіть верхнє ліве вічко кінцевої області, де мають бути поміщені консолідовані дані, і виберіть команду Консолідація (Consolidate) у меню Дані (Data) .
6. Аби створити звідну таблицю, виберіть команду Звідна таблиця (Pivot Table and PivotChart Report) у меню Дані (Data) . Майстер звідних таблиць полегшує обробку великих масивів даних і здобуття підсумкових результатів в зручному вигляді.

 

Друзі сайту:


1
2 3 4

::  Меню ::

Введення
Початкові відомості про Microsoft Office System і Microsoft Office 2003 Перегляд документа у вікні додатка Microsoft Office 2003. Створення, відкриття, збереження і відновлення документа
Знайомимося з Microsoft Word 2003 Редагування документа Word 2003 Автоматизація виконання окремих операцій в Word 2003
Форматування текстового документа
Робота з таблицею і написом
Використання меню Вставка. Вставка і форматування малюнка в Word
Стилі і шаблони, структура документа
Оформлення документа
Налаштування параметрів роботи Word 2003
Друк документа Word Знайомимося з Excel 2003
Введення і редагування даних Excel
Форматування і захист аркуша Excel 2003
Виконання розрахунків по формулах в Excel 2003 Побудова діаграм, свідомість малюнка на аркуші Excel
Аналіз даних в Excel 2003
Друк документа і налаштування Excel 2003
Знайомимося з Outlook 2003
Теки Outlook і їх призначення
PowerPoint 2003 — засіб для створення і демонстрації презентацій
Знайомиться з Microsoft Access 2003 Використання запитів для роботи з даними
Створення і використання форм в Access 2003 Звіти, сторінки доступу до даних, макроси, налаштування бази даних Access 2003
Використання Microsoft Office 2003 для роботи в Інтерлеті
Використання декількох додатків Microsoft Office 2001 в одному документі. Підтримка розпізнавання мови і голосове управління комп'ютером


:: Каталог ::

 

Автомобили Производство Наука и образование Домашний очаг Спорт Новости и СМИ Компьютеры Товары и услуги Отдых и развлечения Мобильники  Медицина и здоровье Бизнес и финансы Работа и заработок Непознанное Интернет Справки Общество и политика Культура и искусство Юмор  


:: Підтримка ::

Практичне ознайомлення з пакетом офісних програм MS Office 2003
Отримати код:
Підтримайте наш сайт і розмістіть нашу кнопку на своєму ресурсі.


:: Статистика ::

 

:: Навігация ::

  Головна
  Гостьова книга
  Додати у вишукане  

 

 

 


 

Copyright © Asentli, 2008