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

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

Малюнок 3019 ілюструє результат такої дії У зведеній таблиці все з того ж рис 302 ми двічі клацнули на підсумку за 2003 рік для розпізнавального номера 23524 (осередок G9) Відповіддю на це був виведення всіх значень з джерела даних, на підставі яких обчислюється відповідний підсумок у зведеній таблиці

Рис 3019 Подвійне клацання на значенні змушує Excel відобразити вихідні дані,

залучаються для обчислення цього значення

Ви вправі відмовитися від такої можливості Виберіть команду Параметри таблиці в меню Зведена таблиця на панелі інструментів Зведені таблиці та зніміть прапорець Розгортання дозволено (Enable Drill To Details) Згодом подвійне клацання на значенні в області даних перестане приводити до описаного вище ефекту

Групування даних

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

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

Припустимо, що після перегляду зведеної таблиці, представленої на рис 302, ви вирішили порівняти передплату з роздрібним продажем, обєднавши канали Усередині країни та Міжнародний в один зведений канал з імям Роздрібний Щоб створити таку групу, виконайте такі дії

1 Виділіть заголовки Всередині країни та Міжнародний в будь-якому місці таблиці

(Наприклад, комірки В10 і В12)

2 Виберіть команду Зведена таблиця ► Група й структура ► Групувати

(Pivot-Table ► Group And Show Detail ► Group)

У відповідь Excel створить нове поле з імям Канал2 і згрупує виділені елементи в новий елемент з імям Группа1, як показано на рис 3020

Рис 3020 Ми створили групу з двох елементів поля Канал: Усередині країни та Міжнародний

3 Виділіть будь-яку клітинку із заголовком Группа1 і введіть нове імя Роздрібний

Після виконання цієї процедури у зведеній таблиці відображаються група і елементи, які в неї входять Ви можете приховати елементи групи, двічі клацнувши на заголовку групи в будь-якому місці таблиці Щоб знову вивести елементи групи, зробіть подвійне клацання на заголовку групи ще раз Можна повністю видалити з таблиці поле, що містить елементи групи, в даному випадку – Канал Для цього просто перетягніть заголовок поля Канал за межі таблиці Excel перебудує зведену таблицю до виду рис 3021

Групування числових елементів

Щоб згрупувати числові елементи поля, наприклад поля Квартал в таблиці з рис

302, виділіть будь-який елемент цього поля і виберіть команду Зведена таблиця

► Група й структура ► Групувати (PivotTable ► Group And Show Detail ►

Group) Ви побачите діалогове вікно, аналогічне представленому на рис 3022

Рис 3021 Ми видалили початкове поле Канал зі зведеної таблиці,

показаної на рис 3020

Рис 3022 Діалогове вікно Групування дозволяє групувати числові значення поля

У цьому вікні введіть необхідні значення в поля Починаючи з (Starting At), По (Ending At) і С кроком (By) Наприклад, щоб у нашому прикладі (див рис 302) створити двухквартальную групу, в поле Починаючи з введіть 1, в поле По – 4 і в полі З кроком – 2

Угруповання елементів за тимчасовими діапазонами

Зведена таблиця на рис 3023 призначена для підсумовування підсумків щоденних грошових операцій по одержувачам Як легко помітити, її осередки в основному порожні

Рис 3023 Угруповання елементів поля Дата зробила б цю зведену таблицю більш корисною

Ця зведена таблиця була б ще корисніше, якби ми згрупували елементи поля Дата Що і зробимо: виділимо будь-який елемент в полі Дата, виберемо команду Зведена таблиця ► Група й структура ► Групувати і заповнимо діалогове вікно за зразком рис 3024

Рис 3024 Excel дозволяє групувати елементи по тимчасових інтервалах різними способами

Excel забезпечує достатню гнучкість при угруповання часових значень У списку З кроком (By) діалогового вікна Групування (Grouping) ви маєте право поставити проміжок часу від секунди до року Якщо стандартних інтервалів недостатньо, можна вказати будь-яку кількість днів Крім того, допускається одночасне створення двох і більше груп, як показано на рис 3025 Для цього ми виділили кілька елементів у списку З кроком при клавіші Ctrl

Рис 3025 Ця зведена таблиця отримана з таблиці, показаної на рис 3023, після угруповання поля дати по місяцях і кварталах

Вилучення груп

Щоб видалити всі групи і повернути поле до первісного стану, просто виділіть згрупований елемент і виберіть команду Зведена таблиця ► Група й структура ► Разгруппировать (PivotTable ► Group And Show Detail ► Ungroup)

Загальні і проміжні підсумки

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

Загальні підсумки

При використанні декількох полів даних Майстер зведених таблиць і діаграм підводить окремі загальні підсумки для кожного з цих полів Загальні підсумки завжди підраховуються з тієї ж функції, що і для поля даних, за яким вони обчислюються Тобто якщо у вашій зведеній таблиці використовується функція Сума, то ця ж функція застосовується і для обчислення загальних підсумків Якщо такою була б функція Середнє, замість «загальних сум» ви отримали б «загальні середні»

РАДА—:

Щоб переключитися на іншу підсумкову функцію, виділіть елемент в області даних зведеної таблиці, натисніть кнопку Параметри поля (Field Settings) на панелі інструментів Зведені таблиці і потім виберіть потрібну функцію в діалоговому вікні Обчислення поля зведеної таблиці (PivotTable Field)

Щоб видалити загальні підсумки з таблиці, виберіть команду Параметри таблиці (Table Options) у меню Зведена таблиця на панелі інструментів Зведені таблиці і потім в діалоговому вікні Параметри зведеної таблиці (PivotTable Options) зніміть прапорці Загальна сума по стовпцях (Grand Totals For Columns) та / або Загальна сума по рядках (Grand Totals For Rows)

Проміжні підсумки

За замовчуванням Excel обчислює проміжні підсумки для всіх полів, поміщених в область стовпців і рядків зведеної таблиці, за винятком самих внутрішніх полів Наприклад, у зведеній таблиці, показаної на рис 302, в стовпці G виводяться проміжні підсумки для елементів поля Рік, а в рядках 9, 13,17 і 21 – для елементів поля Інв № Excel не виробляє проміжних підсумків для елементів полів Канал і Квартал, оскільки вони є найбільш внутрішніми полями у відповідних областях

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

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

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

1 Двічі клацніть на заголовку (але не на елементі) поля Або виділіть заголовок поля та натисніть кнопку Параметри поля (PivotTable Field) на панелі інструментів Зведені таблиці

2 У діалоговому вікні Обчислення поля зведеної таблиці (PivotTable Field) виділіть функцію або декілька функцій (при клавіші Ctrl) і натисніть кнопку ОК

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

1 Двічі клацніть на заголовку поля (але не на його елементі) Або виділіть заголовок поля та натисніть кнопку Параметри поля на панелі інструментів Зведені таблиці

2 У діалоговому вікні Обчислення поля зведеної таблиці встановіть перемикач

Проміжні підсумки в положення Ні (None) і натисніть кнопку ОК

Висновок в таблиці проміжних підсумків можна відновити:

1 Двічі клацніть на заголовку поля (але не на його елементі) або виділіть заголовок поля та натисніть кнопку Параметри поля на панелі інструментів Зведені таблиці

2 У діалоговому вікні Обчислення поля зведеної таблиці встановіть перемикач в положення Автоматичні (Automatic) і натисніть кнопку ОК

Проміжні підсумки для самих внутрішніх полів

Хоча за замовчуванням Excel не генерує проміжних підсумків для самих внутрішніх полів, ви вправі зробити це самі, причому використовуючи декілька підсумкових функцій Ці підсумки, якщо вони є, розміщуються в нижній або правій частині зведеної таблиці безпосередньо вище або ліворуч від загальних підсумків На рис 3026 показані проміжні підсумки для самого внутрішнього поля (вони розташовані в рядках з 35-ї по 40-ю)

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

1 Двічі клацніть на заголовку внутрішнього поля (але не на елементі поля) Замість цього можна виділити заголовок поля і натиснути кнопку Параметри поля на панелі інструментів Зведені таблиці

2 У діалоговому вікні Обчислення поля зведеної таблиці виділіть одну або-

скільки функцій і натисніть кнопку ОК

Обчислення в зведених таблицях

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

Використання інших підсумкових функцій

Щоб переключитися на іншу підсумкову функцію, виділіть будь-яку клітинку в області даних і натисніть кнопку Параметри поля на панелі інструментів Зведені таблиці Excel виведе на екран діалогове вікно Обчислення поля зведеної таблиці (див рис 3012) Виберіть цікаву для вас функцію і натисніть кнопку ОК

Рис 3026 Ми вирахували проміжні підсумки для поля Канал (самого внутрішнього поля на осі рядків), використовуючи функції Сума і Середнє

Кілька підсумкових функцій для одного поля

Ви вправі застосувати до поля даних будь-яку кількість підсумкових функцій Якщо це поле вже знаходиться в області даних зведеної таблиці, перетягніть його заголовок зі списку полів зведеної таблиці ще раз в область даних, створюючи, таким чином, копію Потім виділіть клітинку в області даних і натисніть кнопку Параметри поля на панелі інструментів Зведені таблиці, виберіть потрібну функцію і клацніть на кнопці ОК, щоб повернутися в зведену таблицю Доступні наступні підсумкові функції: Сума (Sum), Кількість (Count), Середнє (Average), Максимум (Мах), Мінімум (Min), Твір (Product), Кількість чисел (Count Nums), StdDev (несмещенная оцінка стандартного відхилення генеральної сукупності), StdDevp (несмещенная оцінка стандартного відхилення вибірки), Зміщена дисперсія (Var), Несмещенная дисперсія (Varp)

Додаткові обчислення

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

Виділіть будь-яку клітинку в області даних і натисніть кнопку Параметри поля на панелі інструментів Зведені таблиці Потім у діалоговому вікні Обчислення поля зведеної таблиці (PivotTable Field) натисніть кнопку Додатково (Options) Діалогове вікно розшириться, як показано на рис 3027

Рис 3027 Діалогове вікно Обчислення поля зведеної таблиці дозволяє застосувати до поля даних додаткові обчислення

За замовчуванням такі обчислення не виробляються (у списку Додаткові обчислення (Show Data As) вибраний елемент Нормальна (Normal)) Щоб застосувати допоміжну операцію (табл 301), розкрийте список Додаткові обчислення, виберіть у ньому потрібний варіант і потім в списках Поле (Base Field) і Елемент (Base Item) вкажіть базове поле і базовий елемент

Таблиця 301 Додаткові обчислення при підведенні підсумків

Додаткове обчислення Опис

Різниця (Difference From) Значення осередків в області даних відображаються у вигляді різниці з заданим елементом базового поля

Частка (% Of) Значення осередків в області даних відображаються

у відсотках від заданого елемента базового поля

Наведене відмінність Значення осередків в області даних відображаються у вигляді

(% Difference From) різниці з заданим елементом базового поля,

нормованої на значення цього елемента

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

(Running Total In) у вигляді наростаючого підсумку

Частка від суми по рядку Значення осередків в області даних відображаються

(% Of Row) у відсотках від підсумку рядка

Додаткове обчислення Опис

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

(% Of Column) у відсотках від підсумку стовпця

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

(% Of Total) у відсотках від загального підсумку

Індекс (Index) При визначенні значень осередків в області даних

використовується наступний вираз: ((Значення в комірці) х

(Загальний підсумок)) / ((Підсумок рядка) х (Підсумок стовпця))

Список Елемент містить спеціальні елементи Назад (Previous) і Далі (Next) Наприклад, щоб в комірці зведеної таблиці відображався відсоток різниці між поточним значенням і значенням за попередній квартал, слід заповнити діалогове вікно Обчислення поля зведеної таблиці так, як показано на рис 3028 На рис 3029 представлена ​​зведена таблиця з новим полем Кв / Кв% Зміна

Рис 3028 Для створення поля, в якому буде відображатися процентне відхилення в порівнянні з попереднім кварталом, необхідно заповнити діалогове вікно саме так

Обчислювані поля й елементи

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

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

Рис 3029 Ми додали поле Кв / Кв% Зміна в нашу зведену таблицю

Створення обчислюваного поля

Щоб створити обчислюване поле, виділіть у зведеній таблиці будь-яку клітинку Потім виберіть команду Зведена таблиця ► Формули ► Обчислюване поле (Pivot-Table

► Formulas ► Calculated Field), і ви побачите діалогове вікно, зображене на мал

У полі Імя (Name) введіть назву для обчислюваного поля, а в полі Формула (Formula) – формулу, по якій буде розраховуватися його значення Для того щоб вставити в формулу імя поля, виберіть це імя в списку Поля (Field) і клацніть на кнопці Додати поле (Insert Field) Приклад обчислюваного поля наведено на рис 3031

За натисканні кнопки Додати (Add) або OK Excel автоматично вставить в зведену таблицю створене обчислюване поле Згодом ви можете працювати з цим полем, використовуючи ті ж прийоми, що і при роботі з існуючими полями

Рис 3030 Створення обчислюваного поля в діалоговому вікні Вставка обчислюваного поля

Рис 3031 Це обчислюване поле отримано множенням існуючого поля на константу

Створення обчислюваного елемента

Щоб створити обчислюваний елемент для поля, виділіть заголовок поля або будь-який елемент цього поля Потім виберіть команду Зведена таблиця ► Формули ► Обчислюваний обєкт (PivotTable ► Formulas ► Calculated Item), і ви побачите діалогове вікно, представлене на рис 3032

Задайте імя елемента в полі Імя (Name) і потім введіть формулу для обчислення в полі Формула (Formula) Списки Поля та Елементи разом з кнопками Додати поле і Додати елемент (Insert Item) дозволяють вставляти у формулу імена необхідних полів і елементів

Рис 3032 Це діалогове вікно використовується для створення обчислюваного елемента

ПРИМІТКА

Не можна створювати елементи поля, для якого визначені користувач-

ські проміжні підсумки (додаткові обчислення)

На рис 3033 показаний приклад обчислюваного елемента Його значення визначається як відношення обсягу продажів усередині країни до суми обсягів міжнародної торгівлі та замовлень поштою

Рис 3033 Цей обчислюваний елемент буде автоматично зявлятися при включенні поля Канал в зведену таблицю

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

Для того щоб переглянути список обчислюваних полів і елементів разом з їх формулами, виберіть команду Зведена таблиця ► Формули ► Вивести формули

(PivotTable ► Formulas ► List Formulas) Excel відобразить список на новому аркуші, ана-

логічно рис 3034

Рис 3034 Так виглядає список обчислюваних полів і елементів, що відображається Excel на окремому робочому листі

Примітка, розташоване під списком обчислюваних елементів, говорить про те, що якщо значення комірки оновлюється в результаті обчислення за кількома формулами, то воно визначається формулою, яка обчислювалася останньої З метою допомогти визначити, в якому порядку відбуваються обчислення формул, в список включені пункти Порядок рішення (Solve Order) Щоб змінити поточний порядок вирішення, в меню Зведена таблиця на панелі інструментів Зведені таблиці виберіть команду Формули ► Порядок обчислень (Formulas ► Solve Order)

Джерело: Ефективна робота: 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>

*

*