Аналіз даних за допомогою зведених таблиць – ЧАСТИНА 2

додаткові умови відбору для зниження числа імпортованих запісей1

Завдання початкової структури таблиці

Коли ви натиснете кнопку Готово в останньому діалоговому вікні майстра, Excel відобразить на робочому листі порожній макет таблиці, як показано на рис 307 Крім того, на екран буде виведено вікно Список полів зведеної таблиці (PivotTable Field List) Ви можете задати початкову структуру зведеної таблиці, перетягуючи заголовки полів з цього списку в підходящі області макета

Щоб повторити створення зведеної таблиці, підготовленої нами як приклад (див рис 302), перетягніть заголовки полів Інв № і Канал вобласть рядків(З написом Перетягніть сюди поля рядків), заголовки полів Рік і Квартал –

1 Microsoft рекомендує відразу вступати саме таким чином, якщо, звичайно, не можна вивільнити дискову память Оскільки створюваний питання тимчасово поміщається на жорсткий диск, необхідно мати хоча б 3-5 Мбайт вільного місця Взагалі кажучи, всі параметри зведеної таблиці обмежені обємом оперативної (віртуальної) памяті (число рядків, стовпців, полів даних, формул) Єдине фіксоване обмеження – на число унікальних елементів у полі – 32 500 – Прямуючи ред

вобласть стовпців (З написом Перетягніть сюди поля стовпців), заголовок Отримано

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

Рис 307 Завдання початкової структури зведеної таблиці

Кількість полів у кожній області макета НЕ огранічено1 Щоб видалити поле,

перетягніть його заголовок за межі макета

РАДА

У меню кнопки Зведена таблиця, розташованої на панелі інструментів Зведені таблиці, містяться команди, недоступні через меню Дані Під час роботи зі зведеною таблицею зручно тримати панель інструментів на екрані, щоб мати можливість звертатися до цих команд Однак якщо ви не хочете, щоб панель інструментів затуляла вашу робочу область, закрийте її, а для доступу до команд використовуйте контекстне меню будь-якої комірки зведеної таблиці

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

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

1 Для області даних і сторінок є обмеження – 256 – Прямуючи ред

Крім транспонування рядків і стовпців можна також змінювати порядок відображення полів по осі стовпців або рядків зведеної таблиці Таким чином ми тепер помістили поле Канал зліва від Інв № Як показано на рис 308, елементи поля Інв № тепер групуються по каналах розповсюдження, а проміжні підсумки підбиваються по кожному каналу

Рис 308 Помістивши поле Канал лівіше поля Інв №, ми змінили порядок виведення інформації по осі рядків

РАДА

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

Використання осі сторінок

Якщо поле знаходиться на осі рядків або стовпців, всі елементи цього поля доступні для перегляду тільки за допомогою смуг прокрутки На осі сторінок одночасно відображається тільки один елемент поля Так, на рис 303 видно тільки один елемент 2002 поля Рік і елемент 1 поля Квартал Щоб відобразити інше значення, потрібно вибрати його в списку, що розкривається поля Вибираючи по черзі елементи в списку, ви можете переглянути двомірні зрізи даних для кожного значення поля, розміщеного на осі сторінок

Відображення підсумків для поля сторінки

Першим пунктом кожного списку, що розкривається поля сторінки є елемент Все (All) Вибір цього пункту дозволяє вивести на екран підсумкові значення для кожного поля, «нанизаного» на вісь сторінок На рис 309 представлений результат вибору елемента Все для полів Рік і Квартал в зведеній таблиці, раніше показаної на рис 303

Рис 309 Виберіть елемент (Всі) у списку, поля, щоб відобразити підсумкові значення для цього поля

Переміщення елементів поля сторінки на окремі аркуші книги

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

Приховання елементів на осях рядків і стовпців

Спочатку після розміщення поля на осі рядків або стовпців у зведеній таблиці відображаються всі, елементи цього поля Наприклад, на рис 309 по осі стовпців показані всі три канали розповсюдження, а по осі рядків виводяться всі ідентифікаційні номери Ви вправі клацнути на стрілці списку, що розкривається праворуч від заголовка

поля і зняти прапорці для тих елементів, які хочете приховати

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

«Відображення найбільших або найменших елементів поля»

Створення зведеної діаграми

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

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

Рис 3010 Ця діаграма повязана зі зведеною таблицею, що на рис 309

Ви можете перебудувати зведену діаграму точно так само, як і таблицю, перетягуючи заголовки полів з однієї осі на іншу І оскільки таблиця і діаграма повязані, реорганізація зведеної таблиці також призводить до перебудови діаграми Щоб додати поле, перетягніть його заголовок зі списку полів зведеної таблиці Для видалення поля досить перетягнути його заголовок за межі діаграми Щоб відобразити тільки певні елементи поля, виберіть їх у списку цього поля

Зверніть увагу, що, хоча у зведеній діаграмі з рис 3010 відображаються одне поле по осі категорій і одне поле по осі значень, ви вправі показати одночасно декілька полів На рис 3011 представлена ​​та ж сама діаграма, але перебудована таким чином, щоб відобразити обидва поля Канал і Інв № по осі категорій і обидва поля Рік і Квартал по осі значень

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

Хоча зведена таблиця повязана з вихідними даними, вона не оновлюється автоматично при їх зміні Щоб оновити зведену таблицю, виділіть в ній будь-яку клітинку і потім у меню Дані виберіть команду Оновити дані (Refresh External Data) або натисніть однойменну кнопку на панелі інструментів Зведені таблиці

Рис 3011 Ми перебудували зведену діаграму, перетягнувши заголовки полів

Оновлення таблиці при відкритті файлу

Щоб при кожному відкритті книги, в якій знаходиться зведена таблиця, ця таблиця автоматично оновлювалася, виберіть команду Параметри таблиці (Table Options) у меню Зведена таблиця на панелі інструментів Зведені таблиці Потім у вікні Параметри зведеної таблиці (PivotTable Options) встановіть прапорець Оновити при відкритті (Refresh On Open) Якщо дані в таблицю потрапляють за допомогою запиту до зовнішнього джерелу, що займає досить багато часу, прапорець Оновити при відкритті краще зняти

Виділення елементів зведеної таблиці

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

Наприклад, якщо в таблиці, представленої на рис 302, підвести покажчик миші до межі осередку В6 із заголовком Усередині країни і клацнути один раз, у виділення будуть включені всі заголовки Всередині країни (тобто осередку В6, B10, B14 і т д) і всі дані, які стосуються цим заголовкам Друге клацання в цьому ж місці виділить тільки заголовок Усередині країни в комірці В6 і його дані (комірки С6: М6) Насправді пояснення до даної операції виглядають складніше, ніж сама операція

При виділенні елементів слід керуватися трьома основними правилами:

1 Для виділення самої комірки досить простого клацання на ній

2 При «структурному» виділення (тобто всіх примірників елемента разом) підведіть покажчик до кордону елемента і клацніть в той момент, коли покажчик прийме вигляд маленької чорної стрілки При цьому для «структурного» виділення по рядках слід підвести курсор до лівої кордоні елемента рядка, а для виділення по стовпцях – до верхньої межі елемента стовпця

3 Для того щоб виділити тільки елемент з відносяться до нього даними, клацніть ще раз

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

Зміна зовнішнього вигляду клітинок зведеної таблиці виконується стандартними прийомами форматування Excel збереже ваше форматування після оновлення або реорганізації таблиці при умови, що ви не зняли прапорець Зберігати форматування (Preserve Formatting) у діалоговому вікні Параметри зведеної таблиці (PivotTable Options) Щоб застосовані вами формату не губилися при оновленні або реорганізації таблиці, виберіть у меню Зведена таблиця команду

Параметри таблиці (Table Options) і потім в діалоговому вікні Параметри зведеної таблиці встановіть прапорець Зберігати форматування

Автоформатирование зведеної таблиці

Excel надає 21 вбудований формат для зведених таблиць Щоб застосувати автоформат, виділіть будь-яку клітинку у зведеній таблиці та натисніть кнопку Формат звіту (Format Report) на панелі інструментів Зведені таблиці (або виберіть однойменну команду в меню Зведена таблиця) Потім в діалоговому вікні Автоформат (AutoFormat) вкажіть відповідний формат таблиці Для того щоб видалити автоформатирование з таблиці, відкрийте діалогове вікно Автоформат і виберіть варіант Немає (None) – останній елемент у цьому вікні

Застосування числових форматів

Для того щоб змінити уявлення чисел в області даних зведеної таблиці, виділіть будь-яку клітинку в області даних і натисніть кнопку Параметри поля (Field Settings) на панелі інструментів Зведені таблиці (або виберіть однойменну команду в меню Зведена таблиця) У діалоговому вікні, Обчислення поля зведеної таблиці (PivotTable Field), представленому на рис 3012, натисніть кнопку Формат (Number) і виберіть зі списку потрібний вам формат Якщо область даних містить кілька полів, ви повинні форматувати кожне поле окремо

Рис 3012 Натисніть кнопку Формат в діалоговому вікні Обчислення поля зведеної таблиці,

щоб застосувати числовий формат до всіх значень поля даних

Відображення порожніх клітинок

Зазвичай в осередках зведеної таблиці, для яких відсутні дані, відображаються

«Порожні» значення Наприклад, якби у вихідному списку (див рис 301) осередок F2 була незаповненою, то осередок С8 в зведеній таблиці на рис 302 також виявилася б марною через відсутність даних Оскільки порожні значення мало-інформативні, при бажанні ви можете відображати 0 або деякий текстове значення в таких осередках Для цього виконайте такі дії:

1 Виділіть у зведеній таблиці будь-яку клітинку

2 У меню Зведена таблиця на панелі інструментів Зведені таблиці виберіть команду Параметри таблиці (Table Options)

3 У діалоговому вікні Параметри зведеної таблиці переконайтеся, що прапорець Для порожніх клітинок відображати (For Empty Cells, Show) Потім у сусідньому полі введіть 0 або інше значення

Відображення значень помилки

Як ви вже знаєте, якщо формула посилається на клітинку, яка містить значення помилки, вона повертає ту ж саму помилку Тобто, наприклад, формула, яка звертається до осередку зі значенням # Н / Д, повертає # N / A Те ж саме зазвичай вірно і для зведених таблиць Помилки у вихідних даних породжують ті ж самі помилки у зведеній таблиці Залишивши осторонь корисність цієї дії, зауважимо, що Excel може замінити помилкові значення у зведеній таблиці «порожніми» або текстовими значеннями

Щоб змінити спосіб відображення значень помилки у зведеній таблиці, виконайте такі дії:

1 Виділіть у зведеній таблиці будь-яку клітинку

2 Виберіть команду Параметри таблиці в меню Зведена таблиця на панелі інстр-

рументов Зведені таблиці

3 У діалоговому вікні Параметри зведеної таблиці встановіть прапорець Для помилок відображати (For Error Values, Show) Щоб значення помилки генерували порожні клітинки, залиште сусіднє поле порожнім Якщо ви хочете на місці повідомлень про помилки бачити щось більш інформативне, введіть у це поле значимий текст

Центрування написів

На рис 3013 показана та ж сама зведена таблиця, що й на рис 30,2, але в ній написи зовнішніх полів відцентровані щодо (або зліва) відповідних написів внутрішніх полів Так, номера по каталогу в стовпці А відцентровані по вертикалі стосовно каналів розповсюдження, що зявлятимуться в стовпці В, а роки в рядку 5 відцентровані по горизонталі над номерами кварталів, розташованими у рядку 6 Щоб отримати такий ефект, виберіть команду Параметри таблиці в меню Зведена таблиця на панелі інструментів Зведені таблиці і потім в діалоговому вікні Параметри зведеної таблиці встановіть прапорець Обєднувати осередку заголовків (Merge Labels)

Використання декількох полів даних

Якщо в область даних зведеної таблиці додати друге поле, Excel виведе проміжні підсумки для кожного поля На рис 3014 показана зведена таблиця з двома полями даних

Рис 3013 Написи зовнішніх полів можна центрувати по відношенню до відповідних написів внутрішніх полів

Рис 3014 Ми додали в область даних друге поле (Кількість)

Зверніть увагу, що тепер таблиця містить новий заголовок поля – Дані Перетягуючи цей заголовок, як і інші заголовки полів, ви можете перебудувати таблицю, щоб надати їй вигляду, як на рис 3015, де ми помістили поле Дані на осі рядків праворуч від заголовка Канал

Рис 3015 Тема Дані, що зявляється, коли область даних містить кілька полів,

можна переміщати в зведеній таблиці, як і будь-який інший заголовок поля

Щоб у цьому випадку видалити поле з області даних, перетягніть заголовок Дані за межі зведеної таблиці, і Excel видалить всі поля з області даних Потім поверніть назад з вікна Список полів зведеної таблиці в область даних ті поля, які ви хочете відобразити

Перейменування полів і елементів

Ви не зобовязані використовувати імена полів і елементів, призначені Майстром зведених таблиць і діаграм Змінюються вони за допомогою редагування заголовка поля або елемента прямо в зведеній таблиці При цьому нове імя отримають всі екземпляри елемента Наприклад, заголовок Інв № у зведеній таблиці з рис 3015 можна виправити на РегНом, виділивши комірку А6, ввівши текст РегНом і натиснувши клавішу Enter

УВАГА –

При зміні заголовка поля нове імя не повинно збігатися з імям іншого поля в джерелі даних Конфлікт імен чреватий непередбачуваними наслідками

Сортування елементів

До зведених таблиць застосовна стандартна для діапазонів і списків команда Сортування (Sort) меню Дані (Data) Результуючий порядок примірників елементів Excel зберігає при перетворенні таблиці Наприклад, щоб у зведеній таблиці, показаної на рис 3015, елементи поля Канал (Усередині країни, Замовлення поштою, Міжнародний) розташувати у зворотному алфавітному порядку, можна виділити вічко В7, вибрати команду Сортування в меню Дані та потім задати напрямок за спаданням Excel змінить послідовність відображення каналів поширення на зворотну (Міжнародний, Замовлення поштою, Усередині країни) у всіх місцях зведеної таблиці, де ці елементи зустрічаються

Додатково Excel пропонує ще один засіб, так звануавтосортіровка, яка, подібно звичайній команді Сортування, впорядковує всі екземпляри елементів поля і зберігає заданий порядок при перетворенні таблиці Але автосортіровка надає більше можливостей, дозволяючи, зокрема, впорядковувати елементи полів на основі значень даних Так, якщо в таблиці, представленої на рис 3015, застосувати автосортіровка по полю Канал, то можна відсортувати елементи цього поля на основі даних Сума по полю Отримано або Сума по полю Кількість Можна змінити таблицю так, що для кожного елемента поля Інв № спочатку буде відображатися канал з найбільшими підсумками продажів, а канал з найменшими підсумками виявиться останнім

Автосортування має перевагу перед звичайною сортуванням Це означає, що якщо автосортіровка включена для поля зведеної таблиці, вона не дозволить замінити себе звичайною командою Сортування меню Дані доти, поки буде активна

Автосортування

Щоб активувати автоматичне сортування, виконайте такі дії: L

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

2 Клацніть на кнопці Параметри поля (Field Settings) панелі інструментів Звід-

ві таблиці (або виберіть команду Зведена таблиця ► Установки поля)

3 Натисніть кнопку Додатково (Advanced)

4 У діалоговому вікні Додаткові параметри поля зведеної таблиці (PivotTable Field Advanced Options), показаному на рис 3016, виберіть варіант По зростанню (Ascending) або За спаданням (Descending) і потім в списку, що розкривається За допомогою поля (Using Field) вкажіть поле, значення якого використовуватимуться при автосортіровка Наприклад, щоб впорядкувати елементи поля Канал так, щоб першим відображався канал розповсюдження з найбільшими загальними підсумками продажів, встановіть перемикач в положення За спаданням і виберіть пункт Сума по полю Отримано в списку За допомогою поля

Для відключення автосортіровка поверніться до діалогового вікна Додаткові параметри поля зведеної таблиці і встановіть перемикач Параметри сортування (AutoSort Options) в положення Вручну (Manual)

Рис 3016 Це діалогове вікно дозволяє задати параметри автосортіровка і автоотображенія

Ручне сортування елементів

Якщо режим автосортіровка відключений, ви можете сортувати елементи, перетягуючи їх Наприклад, якщо в таблиці, представленої на рис 3015, для книги з кодом 23524 ви хочете побачити канали розповсюдження, впорядковані у порядку появи елементів Міжнародний, Замовлення поштою, Усередині країни, виконайте такі дії:

1 Виділіть заголовок Міжнародний з Інв № рівним 23524

2 Розташуйте вказівник біля верхньої межі осередку так, щоб він прийняв вид четирехнаправленной стрілки

3 Перетягніть осередок вгору в позицію вище елемента Замовлення поштою

Іншим способом сортування елементів вручну є присвоєння заголовків елементам зведеної таблиці Тобто описані вище дії можна було б замінити такими операціями:

1 Виділити заголовок Міжнародний з ідентифікаційним номером 23524

2 Ввести текст Замовлення поштою

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

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

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


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

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

Ваш отзыв

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

*

*