Житомир
Р.
Введення.
Для початку все-таки варто прояснити деякі речі, наприклад що таке "зведені таблиці" і для чого вони взагалі існують у природі...
Зведена таблиця - це таблиця, яка використовується для швидкого підведення підсумків або об'єднання великих обсягів даних. Міняючи місцями рядки і стовпчики, можна створити нові підсумки вихідних даних; відображаючи різні сторінки можна здійснити фільтрацію даних, а також відобразити детально дані області.
Іншими словами ці таблиці дозволяють об'єднати дані з різних джерел, таких як бази даних, таблиці, а як і зовнішніх джерел даних, наприклад Інтернет.
Створення зведеної таблиці
Перед тим як створити зведену таблицю, необхідно спочатку встановити дані для цієї таблиці, виділивши частину книги, сторінки, а у разі якщо це база даних, або ж список, то необхідно виділити клітинку списку або бази даних.
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel Для випадку, коли зведену сторінку створюють з одного документа (листа Microsoft Excel), то найбільш раціонально користуватися майстром зведеної таблиця, кнопка якого знаходиться на панелі "зведених таблиць":
Після натискання на цю кнопку користувачеві відкриється вікно, в якому йому буде запропонований вибір (в даному випадку не обов'язково виділяти область до запуску майстра) джерела даних:
у списку або базі даних Microsoft Excel
по-зовнішньому джерелі даних
в декількох діапазонах консолідації
думаю з першим і другим пунктом все відносно зрозуміло, їх назви говорять самі за себе, а що стосується третього пункту, то тут мається на увазі, що існує кілька джерел даних, наприклад, наскільки аркушів книги.
При виборі першого пункту меню, користувач перейде у вікно, в якому потрібно безпосередньо вказати джерело даних у документі, виділивши їх (якщо звичайно це не було зроблено до старту майстра).
Після завершення цього етапу з'явиться вікно третього кроку майстра (рис.1)
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel Тут доведеться безпосередньо розмістити поля, що знаходяться праворуч на малюнку, затиснувши кожне з них мишкою та пересунувши в потрібне поле: стовпець, рядок, дані, сторінка. Важливо щоб всі поля були заповнені, інакше продовження роботи буде неможливо.
Наступне вікно, яке з'явиться, є останнім. Тут необхідно вибрати один з двох параметром, "куди буде поміщена таблиця": новий лист, існуючий лист. Крім цієї опції в цьому вікні розташована також ще і кнопка "параметрів" для створюваної таблиці, в якій можна:
F Вибрати ім'я таблиці
F У пункті "формат" вибрати опції: загальна сума по стовпцях; загальна сума по рядках; автоформат; включати приховані значення; об'єднувати осередки заголовків; зберігати форматування.
F У тому ж пункті вибрати макет сторінки: вниз потім впоперек; поперек, потім вниз.
F Символи, які будуть виводиться замість порожніх клітинок, а так само замість помилок.
F У пункті дані: зберігати дані разом з таблицею, розгортання (Якщо прапорець розгортання "дозволено" знятий, то при подвійному клацанні осередку в області даних зведеної таблиці деталі не відображаються.); Оновлення даних з джерела при кожному відкритті.
Після завершення всіх настройок і натискання на кнопку готово, буде виведена зведена таблиця з вибраними полями.
Якщо при створенні зведеної таблиці використовувався пункт "по-зовнішньому джерелі даних", у першому вікні бази, то при продовженні з'явиться вікно, в якому потрібно буде відкрити або створити новий файл запиту (є засобом для вилучення інформації з бази даних, причому дані можуть бути розподілені серед декількох таблиць) на дані, натиснувши кнопку "отримати дані". Після цього моменту майстер продовжить свою роботу ідентично до попереднього пункту.
Що ж стосується опції "в декількох діапазонах консолідації" все в тому ж першому вікні майстра, то там буде дещо інший порядок виконання. Друге вікно запропонує вибрати, як слід створювати поля на сторінці: створити поля сторінка; створити одне поле.
При виборі першого пункту далі буде необхідно вибрати джерела (декілька) даних, а також вказати кількість полів на сторінці в інтервалі від 0 до 4, а після цього у віконцях вибрати діапазони значень.
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel
Після чого продовжиться стандартний вибір значень для полів. Якщо ж буде вибрано пункт "створити одне поле", то з'явиться таке ж вікно, як і в попередньому випадку, але без полів для вибору діапазону.
Після завершення роботи майстра по одному з перерахованих вище шляхів, до книги буде вставлена зведена таблиця, з вибраними значеннями рядків і стовпців з підсумований значеннями полів, внесених в область "дані" на малюнку 1.
Основні елементи зведених таблиць
Перш ніж продовжити цю роботу, я думаю, що буде не зайвим розповісти з яких все-таки елементів складаються зведені таблиці, і для цього розглянемо приклад на малюнку 2.
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel
Поле сторінки - це поле вихідного списку або таблиці, поміщене в область сторінкової орієнтації зведеної таблиці. У цьому прикладі «Область» є полем сторінки, яку можна використовувати для підведення підсумків по регіонах. При вказівці іншого елемента поля сторінки відбувається перерахунок зведеної таблиці для відображення підсумків, пов'язаних з цим елементом.
Значення поля сторінки - елементи поля сторінки об'єднують записи або значення поля або стовпця вихідного списку (таблиці). Іншими словами це перемиканням цього поля, фактично кожен раз відбувається перехід до нового джерела даних по "новому регіону".
Поля рядки - це поля вихідного списку або таблиці, поміщені в область рядкової орієнтації зведеної таблиці. У цьому прикладі «Продукти» і «Продавець» є полями рядка. Внутрішні поля рядка (наприклад «Продавець») в точності відповідають області даних; зовнішні поля рядка (наприклад «Продукти») групують внутрішні. Таким чином тут простежується певне правило, кожен елемент (поле) більш високого рівня як би уконкречівают всі поля більш низького рівня: параметр "продукт" обмежує всі дані під ним - "продавці продуктів", "дані по продажу продуктів цими продавцями".
Поле стовпця - це поле вихідного списку або таблиці, поміщене в область стовпців. У цьому прикладі «Квартали» є полем стовпця, що включає два елементи поля «КВ2» і «КВ3». Внутрішні поля стовпців містять елементи, відповідні області даних; зовнішні поля стовпців розташовуються вище внутрішніх (у прикладі показано тільки одне поле стовпця).
Областю даних називається частина зведеної таблиці, яка містить підсумкові дані. У осередках області даних відображаються підсумки для елементів полів рядка або стовпця. Значення в кожному осередку області даних відповідають вихідним даним. У цьому прикладі в осередку C6 підсумовуються всі записи вихідних даних, що містять однакову назву продукту, розповсюджувача і певний квартал («М'ясо», «ТОВ Мясторг» і «КВ2»).
Елементи поля - це підкатегорії поля зведеної таблиці. У даному прикладі значення «М'ясо» і «Дари моря» є елементами поля в полі «Продукти».
Елементи поля представляють записи в полі або стовпці вихідних даних.
Елементи поля з'являються у вигляді заголовків рядків або стовпців, а також у спадному списку для полів сторінок.
Поле даних - це поле вихідного списку або таблиці, що містить дані. У цьому прикладі поле «Сума по Замовлення» є полем даних, що підводять підсумки вихідних даних в полі або стовпці «Сума замовлення». У поле даних зазвичай підводяться підсумки групи чисел (наприклад, статистика або кількість збуту), хоча поточні дані можуть бути і текстовими. За замовчуванням у зведеній таблиці підведення підсумків текстових даних здійснюється з допомогою підсумкової функції «Кількість значень», а числових даних - за допомогою підсумкової функції «Сума».
Зміна структури зведеної таблиці
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel Зовнішній вигляд зведеної таблиці можна змінити безпосередньо на аркуші, перетягуючи назви кнопок полів або елементів полів. Так само можна поміняти порядок розташування елементів у полі, для чого необхідно виділити назву елемента, а потім встановити вказівник на межу клітинки. Коли покажчик прийме вигляд стрілки, перетягнути клітинку поля на нове місце. Щоб видалити поле, перетягніть кнопку поля за межі області відомості. Видалення поля призведе до прихованих у зведеній таблиці всіх залежних від нього величин, але не вплине на вихідні дані. Якщо ж треба використовувати всі передбачені кошти структурування зведеної таблиці, або якщо в поточну таблицю не були раніше включені всі поля вихідних даних, слід скористатися майстер зведених таблиць. Якщо ж зведена таблиця містить велику групу полів сторінки, то їх можна розмістити в рядках або стовпцях.
Крім того, потрібно відзначити, що зміна структури зведеної таблиці не зачіпає вихідні дані.
Детальні дані зведеної таблиці та виконуються над ними дії
Детальні дані є підкатегорією зведеної таблиці. Ці елементи зведеної таблиці є унікальними елементами таблиці або списку. Наприклад, поле "Місяць" може містити назви місяців: "Січень", "Лютий» і т.д. Над детальними даними зведеної таблиці можна виконати наступні дії:відобразити або приховати поточні деталі елемента поля або осередку в області даних зведеної таблиці;
виділити елементи, які необхідно відобразити або приховати в полі зведеної таблиці; відобразити максимальні або мінімальні елементи в полі зведеної таблиці. Наприклад, 10 великих угод або п'ять дрібних угод.
Крім того, можна обмежити кількість відображуваних осередків в області даних для поточних даних.
Відображення або приховування детальних даних зведеної таблиці
Для того щоб відобразити детальні дані, які були приховані раніше, або ж приховати наявні потрібно виділити елемент поля, детальні дані якого необхідно приховати або показати.
Далі необхідно натиснути на відповідну кнопку "Відобразити деталі" або "Сховати деталі" (див. малюнок праворуч), які розташовані на панелі інструментів Зведені таблиці, або ж у меню "Дані" у пункті "група і структура". При появі діалогового вікна, виберіть поле, для якого необхідно показати деталі.
Примітка:
Якщо для комірки зведеної таблиці необхідно відобразити деталі поточного джерела даних, на панелі інструментів "Зведені таблиці" виберіть команду "Виділити" в меню "Зведена таблиця" і перевірте, що кнопка "Дозволити виділення" не натиснута. Далі необхідно встановити покажчик на осередок і двічі натиснути кнопку миші. Детальні дані будуть розміщені на окремому аркуші книги. Якщо було вказано елемент, який не має детальних даних, з'являється можливість вставити в зведену таблицю поле рядка або поле стовпця з тих полів вихідних даних, які ще не включені в зведену таблицю. У списку виберіть необхідне поле.
Відображення або приховування елементів поля зведеної таблиці
Якщо ж необхідно відобразити або приховати елемент поля, то для цього необхідно, по-перше, виділити поле, в якому необхідно відобразити або приховати елементи. По-друге, потрібно натиснути кнопку "Поле" зведеної таблиці на панелі інструментів "Зведені таблиці" (див. малюнок). Далі, у вікні, потрібно вибирати елементи, які потрібно приховати або відобразити, в списку "сховати елементи".
Щоб показати або приховати пусті елементи поля вихідних даних, потрібно у вікні "обчислення полі зведеної таблиці" встановити або зняти прапорець "Показувати порожні елементи".
Примітка:
Основні та проміжні підсумки зведеної таблиці будуть перераховані автоматично, і, крім того, приховування даних зведеної таблиці не вплине на вихідні дані.
Відображення максимальних або мінімальних елементів у полі зведеної таблиці
Для відображення максимальних або мінімальних елементів необхідно виділити поле, в якому надалі будуть показані елементи.
Після цього потрібно натиснути кнопку "Поле зведеної таблиці" на панелі інструментів "Зведені таблиці". У вікні, що з'явилося у списку необхідно вибрати пункт "мінімуму" або "максимум" після чого натиснути кнопку "Далі".
Після цього, у новому вікні встановити перемикач "Параметри відображення" у положення "вручну", а так само виберіть варіанти найбільших або найменших у спадному списку відображати, а також кількість відображуваних елементів.
Після чого виберіть поле, що містить дані, що слід використовувати в якості критерію для визначення максимальних або мінімальних елементів, у списку за допомогою поля.
Примітки:
При відновленні зведеної таблиці, зміні її структури або відображенні інших елементів поля сторінки відбувається перерахунок відображення максимальних або мінімальних елементів.
Якщо задано відображення максимальних або мінімальних елементів поля, інші елементи поля ховаються. Щоб відобразити всі елементи поля, необхідно встановити перемикач Параметри відображення у положення вручну.
Відключення доступу до детальним відомостями зведеної таблиці
Якщо встановити вказівник на клітинку області даних зведеної таблиці і двічі натиснути кнопку миші, відобразиться підсумковий список вихідних даних для цього осередку. Іноді буває корисно обмежити доступ до докладних відомостей, і для виконання цієї процедури необхідно виконати наступне:
Потрібно виділити клітинку у зведеній таблиці, для якої будуть введені обмеження.
Виберіть команду "Параметри" знаходиться в меню "Зведена таблиця", панель інструментів "Зведені таблиці".
Зніміть прапорець "розгортання дозволено" у групі "Дані".
Групування та розгрупування даних в зведеній таблиці
У зведеній таблиці можна групувати дати, часи, числа і виділені елементи. Наприклад, можна об'єднати місяці, щоб підвести підсумки за квартал. Якщо тимчасово змінити орієнтацію поля сторінки на орієнтацію по рядку або за стовпцем, можна згрупувати елементи цього поля і потім знову змінити орієнтацію на орієнтацію по сторінці.
При виконанні операції угрупування і разгруппировки даних зведеної таблиці автоматично перераховуються всі зведені таблиці, створені на основі поточної. Якщо елементи поля згруповані, додавання обчислюваних елементів у полі неможливо.
Групування та розгрупування елементів поля зведеної таблиці
Для здійснення угруповання і разгруппировки елементів потрібно виділити згрупованих елементи.
Далі натиснути кнопку "Згрупувати" Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel на панелі інструментів "Зведені таблиці" або в меню "Дані" у пункті "Група й структура" щоб створити новий елемент, об'єднуючий виділені елементи. Новий елемент додасться в згрупованих полі зведеної таблиці.
Примітки:
Щоб розгрупувати елементи, виділіть групу, а потім натисніть кнопку "Розгрупувати" на панелі інструментів "Зведені таблиці" або в меню "Дані" у пункті "Група й структура". При виконанні команди разгруппировки чисел, дат або часів розформовується вся група; розформувати окремий елемент групи неможливо.
Якщо розгрупованим останній елемент у полі рядка або у полі шпальти, групове полі видаляється зі зведеної таблиці.
Групування та розгрупування чисел у зведеній таблиці
Для здійснення угруповання і разгруппировки чисел виділіть число в полі, в якому слід згрупувати елементи. Далі натисніть кнопку "Згрупувати" на панелі інструментів Зведені таблиці або в меню "Дані" у пункті "Група й структура". У полі "Починаючи з" введіть початкове значення, якщо це необхідно, а в полі "за" уведіть кінцеве значення, якщо це необхідно. У полі з кроком необхідно ввести значення періоду, за яким будуть створюватися групи чисел.
Примітка:
Щоб розгрупувати числа, виділіть групу, а потім натисніть кнопку "Розгрупувати" на панелі інструментів "Зведені таблиці". При виконанні команди разгруппировки чисел, дат або часів розгрупованим вся група; розгрупувати окремий елемент групи неможливо.
Групування та розгрупування дат часів у зведеній таблиці
Для здійснення угруповання і разгруппировки дати або часу, перейдіть до дати або час у полі, в якому слід згрупувати елементи. Далі натисніть кнопку "Згрупувати" на панелі інструментів Зведені таблиці або в меню "Дані" у пункті "Група й структура". У полі "починаючи з" введіть початкове значення, а в полі "за" кінцеве значення, якщо це необхідно. У списку "За" виберіть один або декілька тимчасових інтервалів для завдання кроку, з яким будуть створюватися групи.
Якщо в полі "За" виокремлено єдиний інтервал "Дні", введіть крок угруповання в полі "Кількість днів". Наприклад, введіть 7 у полі "Кількість днів", щоб згрупувати елементи за тижнями.
Примітка:
Щоб розгрупувати дати і часи, виділіть групу, а потім натисніть кнопку "Розгрупувати" на панелі інструментів Зведені таблиці. При виконанні команди разгруппировки поля зі значеннями дати або часу розгрупованим всі групи поля; розгрупувати один елемент групи неможливо.
Групування та розгрупування елементів поля сторінки зведеної таблиці
Якщо зведена таблиця створена із зовнішнього джерела даних, перевірте, що у полі сторінки здійснюється вибірка зовнішніх даних для всіх елементів поля одночасно.
Якщо це відбувається, то перетягніть поле сторінки в області орієнтації по рядках або стовпцях, щоб елементи поля сторінки стали заголовками в полі рядка або стовпця. Далі слід виділити згрупованих елементи.
Після цього натисніть кнопку "Згрупувати" на панелі інструментів "Зведені таблиці", або в меню "Дані" у пункті "Група й структура". І, нарешті, перетягніть поле тому в області орієнтації по сторінках.
Примітка:
Щоб розгрупувати елементи поля сторінки, перетягніть поле сторінки в області орієнтації по рядках або стовпцях, виділіть групу, а потім натисніть кнопку "Розгрупувати" на панелі інструментів Зведені таблиці.
Оновлення або зміна джерела даних зведеної таблиці
У процесі роботи іноді виникає така ситуація, коли з'являються нові дані, які необхідно вставити в створюваний документ, і тому існує кілька способів управління процесом оновлення та зміни даних зведеної таблиці:
Для зведених таблиць, створених на основі даних Microsoft Excel, відновлення дістає нові поле первісного інтервалу вихідних даних або інтервалу даних, заданого на ім'я. При додаванні нових рядків або стовпців даних у вихідний список Microsoft Excel, заданий у якості посилання на інтервал, можна змінити інтервал вихідних даних для зведеної таблиці, щоб у нього входили додаткові рядки і стовпці.
Для зведених таблиць, створених на основі зовнішніх джерел даних, відновлення дістає нові рядки або записи зовнішнього файлу або бази даних, які задовольняють умовам запиту. При додаванні нових рядків або стовпців у зовнішнє джерело даних для зведеної таблиці, а також при необхідності вибору інших даних для зведеної таблиці, можна внести відповідні зміни до запиту.
У зведених таблицях з полями сторінок можна налаштувати поле сторінки для виконання запиту до зовнішніх даних при виборі кожного елемента. Це дозволяє працювати з великим об'ємом даних без збільшення оперативної пам'яті комп'ютера. При наявності достатнього для обробки даних обсягу вільної оперативної пам'яті для швидкого перегляду сторінок можна налаштувати поля сторінок таким чином, щоб отримувати зовнішні дані для всіх елементів одночасно.
Оновлення даних у зведеній таблиці
Для того щоб оновити дані в зведеній таблиці необхідно виконати наступні операції:
Необхідно виділити комірку у зведеній таблиці, вміст якої необхідно оновити.
Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel Після цього натисніть кнопку "Оновити" дані на панелі інструментів Зведені таблиці або ж у пункті меню "Дані" "оновити дані". Після цього відбудеться автоматичне оновлення осередки.
Примітки:
Щоб зведена таблиця була оновлена при відкритті книги, виберіть команду Параметри в меню "Зведена таблиця" на панелі інструментів "Зведені таблиці". Встановіть прапорець "оновити" при відкритті в групі "Джерело".
Якщо зведена таблиця створена на основі даних, що знаходяться у списку Microsoft Excel, і відбувається додавання нових полів у вихідний діапазон, додати ці поля після оновлення таблиці можна за допомогою майстра зведених таблиць. Для цього виділіть комірку зведеної таблиці, натисніть кнопку "Майстер зведених таблиць" на панелі інструментів "Зведені таблиці" і перетягніть нові поля в область відомості.
Щоб оновити всі зведені таблиці у книзі, на панелі інструментів "Зведені таблиці" виберіть команду "Оновити дані" в меню "Зведена таблиця".
Порада:
Щоб не вводити пароль для доступу до зовнішньої базі даних при відновленні зведеної таблиці, можна зберегти його як частину запиту. На панелі інструментів Зведені таблиці виберіть команду Параметри в меню Зведена таблиця і встановіть прапорець зберегти пароль у групі Зовнішні дані.
Відключення оновлення зведеної таблиці при відкритті файлу книги
Для відключення відновлення зведеної таблиці при відкритті файлу виділіть клітинку зведеної таблиці, для якої оновлення не буде відбуватися при відкритті. Далі на панелі інструментів "Зведені таблиці" виберіть команду "Параметри"
в меню "Зведена таблиця".
Після цього у вікні зніміть прапорець "оновити при відкритті" групи "Джерело".
Порада:
Щоб створити неизменяемую копію зведеної таблиці, скопіюйте зведену таблицю, а потім виберіть команду Спеціальна вставка в меню Правка та установіть перемикач у положення значення.
Сортування даних зведеної таблиці
Елементи поля зведеної таблиці автоматично сортуються в порядку зростання, згідно з їх іменами. Сортування елементів за іменами дозволяє відновити початковий порядок, якщо елементи були переміщені або відсортовані інакше.
У Microsoft Excel передбачено наступний порядок для сортування даних за зростанням: числа, текст, логічні значення, значення помилок (наприклад # ПОСИЛАННЯ! І # значить!) І порожні клітинки. Для сортування за спаданням використовується зворотній черговість, за винятком порожніх клітинок, які завжди містяться в кінець відсортованого списку. Щоб створити особистий порядок сортування або застосувати вбудований в Microsoft Excel, необхідно скористатися спеціальним порядком сортування.
Сортування елементів зведеної таблиці за назвами
Для того, що б зробити сортування елементів зведеної таблиці по назвах виділіть сортируемое полі. Після чого необхідно вибрати команду "Сортування" в меню "Дані".
У вікні встановіть перемикач "Діапазон" в положення "по зростанню" або "за спаданням". Перевірте, що перемикач "Сортувати" має значення "Мітки".
Примітка:
Якщо при оновленні або зміні структури зведеної таблиці, необхідно автоматично сортувати елементи, виділіть сортируемое поле і натисніть кнопку Поле зведеної таблиці на панелі інструментів Зведені таблиці. Натисніть кнопку "Далі" і встановіть перемикач Параметри сортування в положення за зростанням. Виберіть той же сортируемое поле в розкривному списку за допомогою поля.
Сортування елементів зведеної таблиці за значеннями клітинок області даних
Для сортування елементів зведеної таблиці за значеннями клітинок області даних виділіть поле, що містить сортовані елементи. Якщо вказано поле стовпця, елементи будуть сортуватися зліва направо. Якщо вказано поле рядка, елементи будуть сортуватися зверху вниз.
Після виділення натисніть кнопку "Поле зведеної таблиці" на панелі інструментів "Зведені таблиці". Після чого натисніть кнопку "Далі".
У вікні встановіть перемикач "Параметри сортування" у положення "по зростанню" або "за спаданням". Виділіть поле даних, значення якого необхідно використовувати для сортування елементів, у списку за допомогою поля.
Примітка:
Якщо при оновленні або зміні структури таблиці, непотрібно зберігати порядок сортування, сортування можна провести вручну. Для цього встановіть перемикач "Параметри сортування" у положення "вручну" і два рази натисніть кнопку "OK". Після цього "Перейдіть" сортируемое поле, і виберіть команду "Сортування" в меню "Дані". У вікні потрібно ввести клітинку сортируемое області даних у полі "Діапазон".
Використання у зведеній таблиці користувальницького порядку сортування
Для використання у зведеній таблиці користувальницького порядку сортування необхідно виділити сортируемое полі. Після чого вибрати команду "Сортування" вменю "Дані".
У вікні натиснути кнопку "Параметри".
Далі в новому вікні виберіть параметр сортування у списку "Порядок сортування по першому ключу".
Примітки:
Користувальницький порядок сортування порушується при відновленні зведеної таблиці. Щоб відновити порядок сортування після оновлення зведеної таблиці, виконайте сортування повторно.
Якщо зведена таблиця містить елементи, які зазвичай сортуються у хронологічному, а не в алфавітному порядку - наприклад «Січ», «лютий», «Мар» - у Microsoft Excel автоматично використовується користувальницький порядок сортування. Якщо пізніше елементи полів були переміщені або впорядковані інакше, вихідний порядок можна відновити сортуванням з використанням користувальницького порядку сортування.Підведення підсумків і обробка даних зведеної таблиці
Поля даних зведеної таблиці можна налаштувати на обчислення, відмінні від використовуваних за умовчанням в підсумковій функції. Для виконання складних обчислень можна створити формулу. Підсумкова функція використовується для виконання основних обчислень (наприклад, «Сума», «Середнє», «Мінімум» і «Максимум»). Додаткові обчислення використовуються для порівняння, індексування, а також для обчислення часток.
Що ж стосується безпосередньо обчислюваного поля або обчислюваних елементів поля, то вони використовується для створення користувальницької формули, обробній дані зведеної таблиці.
Зміна підсумкової функції в області даних зведеної таблиці
Для того щоб провести зміну підсумкової функції в області даних зведеної таблиці потрібно в області даних зведеної таблиці виділіть клітинку, для якої необхідно змінити підсумкову функцію. Після цього потрібно натиснути кнопку "Поле зведеної таблиці" на панелі інструментів "Зведені таблиці".
Далі, у вікні, необхідно вибрати підсумкову функцію, яка буде використовуватися для підбиття підсумків поточних даних поля, в списку "Операція".
Використання кількох підсумкових функцій для обробки даних зведеної таблиці
Крім стандартних операцій з функціями в MS Excel'e передбачені деякі додаткові, такі як можливість використання декількох підсумкових функцій для обробки даних зведеної таблиці. Для цього необхідно виділити комірку зведеної таблиці, в яку буде вставлена функція.
Після цього натисніть кнопку "Майстер зведених таблиць" на панелі інструментів "Зведені таблиці".
Далі в діалоговому вікні "Майстер зведених таблиць" - в кроці 3 виберіть поле, по якому вже підводяться підсумки в області даних зведеної таблиці, і перетягніть це поле до області даних ще раз. В області даних структури таблиці помістіть покажчик на нове поле, і двічі натисніть кнопку миші. Далі виберіть іншу функцію для цього поля у списку "Операція".
І на завершення натисніть кнопку "OK", а потім - кнопку "Готово".
Використання додаткових обчислень для поля даних зведеної таблиці
Для використання додаткових обчислень для поля даних зведеної таблиці виділіть клітинку поля, для якої необхідно поставити додаткові обчислення, в області даних зведеної таблиці. Потім натисніть кнопку "Поле зведеної таблиці" на панелі інструментів "зведені таблиці". У вікні натисніть кнопку "Додатково".
Виберіть додаткове обчислення в списку "Додаткові обчислення". Якщо список полі доступний, виділіть поле, дані якого буде використовуватися в обчисленнях. Поле має відрізнятися від поля, зазначеного в першому кроці.
Якщо список елемент доступний, виділіть елемент поля, дані якого буде використовуватися в обчисленнях.
Примітка:
Поле і елемент визначають дані, які використовуються в додаткових обчисленнях.
Створення обчислюваного поля у зведеній таблиці
Для створення обчислюваного поля у зведеній таблиці виділіть клітинку у зведеній таблиці.
Після цього виберіть команду "Формули" в меню "Зведена таблиця", на панель інструментів "Зведені таблиці", а потім виберіть команду "Обчислюване поле".
У вікні введіть ім'я обчислюваного поля в полі "Ім'я", а як і формулу в полі "Формула".
Щоб використовувати у формулі дані поля, виберіть поле у списку "Поля", а потім натисніть кнопку "Додати поле". Потім натисніть кнопку "Додати", а потім - кнопку "OK".
І у висновку необхідно розташувати обчислюване поле у зведеній таблиці.
Примітка:
Формули для обчислюваних полів завжди впливають на всі доступні дані зведеної таблиці; використання окремої частини даних у формулі неприпустимо.
Створення обчислюваного елемента поля у зведеній таблиці
Для створення обчислюваного елемента поля у зведеній таблиці необхідно:
Якщо елементи в полі, що використовується для створення формули, згруповані, розгрупуйте їх. Далі потрібно виділити поле або елемент поля. Після виділення виберіть команду "Формули" в меню "Зведена таблиця" на панелі інструментів "Зведені таблиці", а потім виберіть обчислюваний об'єкт. Відповідно потрібно ввести ім'я обчислюваного елемента в полі зі спадним списком "Ім'я", а як і формулу для цього ж елемента в полі "Формула". Крім того, щоб використовувати у формулі дані елемента поля, виберіть поле у списку "Поля", а елемент-в списку "Елементи", а потім натисніть кнопку "Додати елемент". Якщо елемент знаходиться в іншому полі, то його використовувати не можна. Далі натисніть кнопку "Додати", а потім - кнопку "OK".
Якщо в кроці 1 були розформовані елементи поля, після створення обчислюваного елемента поля можна повторити угруповання. У групи можна включати і обчислювані елементи поля.
Примітки:
Якщо поля містять обчислювані елементи, не можна змінювати підсумкову функцію.
Формула для обчислюваного елемента замінюється формулою для обчислюваного поля, якщо осередок у сфері даних знаходиться на перетині обчислюваного елемента і обчислюваного поля.
Використання загальних і проміжних підсумків у зведеній таблиці
Зведену таблицю можна налаштувати на виконання обчислень за допомогою підсумкової функції (підсумкова функція - це тип обчислення, виробленого Microsoft Excel при об'єднанні даних у зведеній таблиці або при обчисленні підсумків у списках і базах даних. Прикладами підсумкових функцій є «Сума», «Кількість возначеній»і" Середнє ".), вставивши додаткові рядки або стовпці проміжних підсумків для полів даних.
Проміжні підсумки автоматично відображаються в самій останньому рядку або стовпці, якщо у зведеній таблиці створено кілька полів рядка або стовпця. При додаванні проміжних підсумків їх можна розміщувати і всередині полів рядків або стовпців.
Відображення або приховання загальних підсумків у зведеній таблиці
Для відображення або приховання загальних підсумків у зведеній таблиці виділіть клітинку зведеної таблиці, в якій вони будуть відображені.
Після цього на панелі інструментів "Зведені таблиці" потрібно вибрати команду "Параметри" в меню "Зведена таблиця".
У новому вікні встановіть прапорець "загальна сума по стовпцях" або "загальна сума по рядках" або обидва прапорці у групі Формат, щоб відобразити загальні підсумки, або ж зніміть прапорці, щоб приховати загальні підсумки.
Вставлення або видалення проміжних підсумків у зведеній таблиці
Для того, що б здійснити вставку або видалення проміжних підсумків необхідно встановити покажчик на кнопку поля, в яке необхідно помістити або з якого слід видалити проміжні підсумки, і двічі натисніть кнопку миші.
А щоб відображати проміжні підсумки для кожного елемента рядка або стовпця, встановіть перемикач "Проміжні підсумки" у положення "автоматичні".
Щоб відображати проміжні підсумки для кожного елемента внутрішнього поля, встановіть перемикач "Проміжні підсумки" в положення "інші", після вибравши підсумкову функцію в списку.
Щоб видалити проміжні підсумки, встановіть перемикач "Проміжні підсумки" в положення "ні".
Щоб відобразити кілька проміжних підсумків, виберіть підсумкові функції в списку.
Примітки:
Для кожної обраної підсумкової функції додається один рядок проміжних підсумків.
Щоб включити приховані елементи поля сторінки в проміжні підсумки, на панелі інструментів Зведені таблиці виберіть команду Параметри в меню Зведена таблиця і встановіть прапорець включати приховані значення у групі Формат.
Зміна підсумкової функції, використовуваної в обчисленнях проміжних підсумків зведеної таблиці
Для зміна підсумковій функції, використовуваної в обчисленнях проміжних підсумків встановіть покажчик на кнопку поля, в якому необхідно змінити підсумкову функцію проміжних підсумків, і двічі натисніть кнопку миші.
Потім виберіть декілька підсумкових функцій в групі "Проміжні підсумки".
Примітка:
Щоб додати кілька проміжних підсумків (наприклад «Сума» і «Середнє») для кожного елемента внутрішнього поля або окремого поля зведеної таблиці, виберіть пункт інші.
Створення діаграми для зведеної таблиці
Для створення діаграми для зведеної таблиці необхідно на панелі інструментів "Зведені таблиці" вибрати команду "Виділити" в меню Зведена таблиця. Перевірте, що кнопка "Дозволити виділення" не натиснута.
Після цього видаліть будь-які проміжні підсумки зі зведеної таблиці.
Далі виділіть зведену таблицю цілком, в тому числі поля стовпців і рядків. Не виділяйте загальні підсумки або поля сторінок. Щоб виділити перший рядок і стовпець зведеної таблиці, почніть виділення з правого нижнього кута області даних.
Після цього натисніть кнопку "Майстер діаграм", яка знаходиться на панелі "стандартні", а так само в меню "Вставка" Обробка табличної інформації за допомогою зведених таблиць засобами MicroSoft Excel, І далі необхідно просто дотримуватися інструкцій майстра діаграм.
Примітки:
При прихованні, відображенні або впорядкування полів зведеної таблиці відповідним чином змінюється діаграма. Крім того, якщо у зведеній таблиці містяться поля сторінок, при виборі іншої сторінки діаграма також зміниться.
Щоб зберегти і роздрукувати діаграми для всіх полів сторінок зведеної таблиці, натисніть кнопку "Показати сторінки" на панелі інструментів "Зведені таблиці". Кожна сторінка відобразиться на окремому аркуші. Після цього для кожної сторінки можна побудувати діаграму.
Якщо в поточній зведеної таблиці використовуються зовнішні джерела даних і за допомогою Microsoft Query додаються або видаляються поля зовнішніх даних, слід оновити зведену таблицю, в іншому випадку діаграма не зміниться.
Список літератури:
1. Вбудована довідка Microsoft Excel.
2. М. Хелворсон, М. Янг "Ефективна робота з Microsoft Office "
3. Http: / / www. Microsoft. Com / rus / - Російська сторінка компанії Microsoft.
4. "Microsoft Office 8.0", Ед Ботте, БІНОМ, Москва, 1998 рік.
Дата добавления: 2015-04-11; просмотров: 29 | Поможем написать вашу работу | Нарушение авторских прав |