Підсумкові функції зведеної таблиці
Лабораторна робота №4
Тема: Робота зі звітами зведених таблиць в Excel. Застосування фільтра та обчислення даних у звітах зведених таблиць.
Мета: Придбання навички використання звітів зведених таблиць для аналізу даних та підведення підсумків; навчитися приховувати та відображати вибрані дані у зведених звітах за допомогою фільтра; навчиться підбивати підсумки за даними з використанням підсумкових функцій, відмінних від функції СУММ, таких як функції СЧЕТ або МАКС, за допомогою додаткових обчислень навчиться відображати дані у вигляді частки від загальної суми; створювати власні формули у звітах зведених таблиць.
Порядок виконання лабораторної роботи
1. Ознайомтесь з теоретичними відомостями, які складаються з трьох розділів, які познайомлять вас з використанням зведених таблиць, звітів та фільтрів.
2. В кінці кожного розділу теоретичних відомостей викладені завдання, виконання яких має відобразитись у звіті з лабораторної роботи.
3. Зробіть висновки по роботі.
4. Оформіть звіт відповідно до вимог по оформленню технічної документації.
Теоретичні відомості
Зміст
1. Початок роботи зі звітами зведених таблиць в Excel 2007.
2. Застосування фільтра до даних зведеного звіту в Excel 2007.
3. Обчислення даних у звітах зведених таблиць в Excel 2007
1. Початок роботи зі звітами зведених таблиць в Excel 2007.
Зведені таблиці – потужний і ефективний засіб упорядкування і аналізу даних.
Вони називаються зведеними таблицями тому, що в них можна різним чином підбирати рядки і стовпці для аналізу даних їхніх різних стовпців списку. Зведені таблиці можна створювати на основі списків Excel або використовувати зовнішні джерела даних, наприклад, Microsoft Access.
Для створення зведених таблиць у Excel можна використовувати Майстра зведених таблиць.
Зведена таблиця допускає виконання операцій тільки з числовими даними. Тому в списку на рис.1, за допомогою зведеної таблиці можливий аналіз тільки даних із стовпця «Термін», у якому містяться числові значення. У стовпцях «Рахунок №» і «Код» також використовуються числа, але в якості підписів.
Уявіть собі лист Excel з сотнями або тисячами рядків даних. Цей лист містить всі дані про продавців в двох країнах і про те, скільки вони продали в кожен конкретний день. Це великий обсяг даних, що йдуть рядок за рядком і розбитих на кілька стовпців. Як витягти потрібні відомості з цього листа? Як зробити правильні висновки на базі всіх цих даних?
Хто продав найбільше товарів? Хто став кращим з продажу за квартал або за рік? В якій країні обсяг продажів вище? Відповіді на всі ці питання можна отримати за допомогою звітів зведених таблиць. Це нагадує перетворення натовпу в організоване військо. У звіті зведеної таблиці дані перетворюються в невеликі наочні звіти, які дають чіткі відповіді на поставлені питання.
Перед початком роботи зі звітом зведеної таблиці погляньте на аркуш Excel і переконайтеся, що він підготовлений для створення звіту.
При створенні звіту зведеної таблиці кожний із стовпців вихідних даних стає полем, яке можна використовувати в звіті. Поля об'єднують кілька рядків вихідних даних.
Імена полів для звіту утворюються на основі заголовків стовпців у вихідних даних. Переконайтеся, що в першому рядку листа, використовуваного в якості джерела даних, для кожного з стовпців вказано ім'я.
На малюнку вище заголовки стовпців «Країна», «Продавець», «Сума замовлень», «Дата замовлення» та «Код замовлення» перетворяться у відповідні імена полів.
Решта рядків під заголовками повинні містити у відповідних стовпцях подібні дані. Наприклад, в одному стовпці повинен знаходитися текст, в іншому - числа, а в третьому - дати. Іншими словами, стовпець, що містить числові дані, не повинен містити текст, і так далі.
Нарешті, в даних, використовуваних як вихідні для побудови звіту зведеної таблиці, не повинно бути порожніх стовпців. Крім того, рекомендується видалити порожні рядки, наприклад рядки, використовувані для розділення блоків даних.
Після того як дані підготовлені, помістіть покажчик у будь-якому місці області даних. При цьому в звіт будуть включені всі дані аркуша. Можна також виділити тільки ті дані, які повинні використовуватися в звіті. Потім на вкладці Вставка у групі Таблиці натисніть кнопку Зведена таблиця, а потім ще раз натисніть кнопку Зведена таблиця. Відкриється діалогове вікно Створення зведеної таблиці.
Автоматично вибрано Обрати таблицю або діапазон. У полі Таблиця або діапазон відображається вибраний діапазон даних. Крім того, вибрано місця розміщення звіту На новий аркуш (якщо не потрібне розміщувати звіт на новому аркуші, виберіть параметр На існуючий лист)
1 - Область макета у звіті зведеної таблиці.
2 - Список полів зведеної таблиці.
Ця область буде відображена на новому аркуші, якщо закрити діалогове вікно Створення зведеної таблиці.
На одній стороні знаходиться область макета, де буде створено звіт зведеної таблиці, а на іншій - Список полів зведеної таблиці. У цьому списку показані заголовки стовпців вихідних даних. Як згадувалося раніше, кожен із заголовків являє собою поле, наприклад «Країна», «Продавець» і так далі.
Звіт зведеної таблиці створюється шляхом переміщення одного з полів у область макета звіту зведеної таблиці. Для цього слід встановити прапорець поруч з іменем відповідного поля або клацнути ім'я поля правою кнопкою миші і вибрати місце, куди буде переміщено поле.
Якщо робота зі звітами зведених таблиць виконувалася раніше, то, можливо, цікаво буде з'ясувати, чи збережена можливість перетягувати поля, щоб включити їх у звіт. Така можливість залишилася.
Якщо натиснути мишею за межі макету (звіту зведеної таблиці), список полів зведеної таблиці зникне. Щоб знову вивести список полів на екран, клацніть область макета зведеної таблиці або звіт.
Скільки продав кожен продавець?
Тепер можна приступити до створення звіту зведеної таблиці. Вибір полів для звіту залежить від мети його створення.
Для початку з'ясуємо обсяг продажів для кожного з продавців. Щоб отримати потрібну відповідь, потрібні дані про продавців. Тому встановіть прапорець у вікні Список полів зведеної таблиці для поля Продавець. Крім того, потрібні дані про виконані ними продажах. Тому слід встановити прапорець для поля Сума замовлень. Зверніть увагу, що при створенні звіту немає необхідності використовувати всі поля зі списку.
При виборі поля воно автоматично поміщається в заданої за замовчуванням області макета. При бажанні поле можна перемістити в іншу область (наприклад, якщо потрібно розмістити поле в області стовпців, а не в області рядків).
Дані в поле Продавець (ПІБ продавців), яке не містить чисел, автоматично відображаються у вигляді рядків у лівій частині звіту. Дані в поле Сума замовлень складаються з чисел і відображаються в області праворуч.
Поле даних про продавців має заголовок «Назви рядків». Поле сум замовлень має заголовок «Сума по полю Сума замовлень». Частина цього заголовка («Сума по полю») вказує, що для об'єднання числових полів в Excel використовується функція «Сума».
Зверніть увагу, що послідовність установки прапорців для полів Продавець і Сума замовлень не має значення. У будь-якому випадку вони будуть автоматично поміщені у відповідні області. Поля, що не містять чисел, поміщаються ліворуч, числові поля - справа, незалежно від порядку їх вибору.
За допомогою всього двох кліків мишкою з'ясовано, скільки товару продали продавці. До речі, на цьому можна і зупинитися. Звіт зведеної таблиці може використовуватися як швидкий спосіб отримання відповіді на один або два питання. Звіт необов'язково повинен бути складним.
Якщо звіт буде створений неправильно, в Excel легко спробувати різні варіанти, подивитися, як виглядають дані в різних областях звіту. Якщо звіт не відповідає задуманому, можна дуже швидко розташувати дані іншим способом, перемістивши їх у відповідності зі своїми завданнями, або створити повністю новий звіт.
Які обсяги продажів для кожної країни?
Тепер обсяг продажів для кожного з продавців відомий. Однак у вихідній таблиці містяться дані про продавців в двох країнах - Україна і Росія. Наступним може бути питання про те, які продажу кожного з продавців по країнах.
Щоб отримати відповідь, додайте до звіту зведеної таблиці поле Країна в якості фільтра звіту. Фільтр звіту дозволяє працювати з підмножиною даних у звіті, наприклад з лінією продуктів, інтервалом часу або географічним регіоном.
Скориставшись полем Країна в якості фільтра звіту, можна переглянути окремі звіти для Росії або України, а також дізнатися сукупні продажі по обом країнам.
Щоб додати це поле в якості фільтра звіту, клацніть правою кнопкою миші поле Країна у вікні Список полів зведеної таблиці та натисніть кнопку Додати в фільтр звіту. У верхню частину звіту буде доданий фільтр звіту Країна. Стрілка поруч з полем Країна містить параметр (Усі), при якому відображаються дані по обом країнам. Щоб вивести дані тільки по Росії або Україні, клацніть стрілку та виберіть потрібну країну. Щоб знову відобразити дані по обом країнам, клацніть стрілку та виберіть параметр (Усі).
Щоб видалити поле зі звіту, зніміть прапорець поруч з іменем цього поля у вікні Список полів зведеної таблиці. Щоб видалити із звіту всі поля для повторного створення звіту, на вкладці Параметри стрічки в групі Дії натисніть стрілку кнопки Очистити і виберіть команду Очистити все
Які обсяги продажів для кожної країни?
У вихідних даних є стовпець «Дата замовлення», що містить відомості про дати, тому у вікні Список полів зведеної таблиці присутній поле Дата замовлення. Це означає, що можна отримати відповідь на питання про обсяг продажів для кожного продавця по датах. Для цього встановіть прапорець поруч із полем Дата замовлення, щоб додати це поле в звіт.
Поле Дата замовлення автоматично додається зліва у вигляді назви рядка. Це пояснюється тим, що дане поле не містить числових даних (дати можуть виглядати як число, однак вони мають формат дати). Так як поле Дата замовлення є другим нечислової полем, яке додано до звіту, воно розміщується всередині поля Продавець з відступом вправо.
Тепер у звіті показані продажу для кожного продавця на окремі дати, проте обсяг отриманих даних дуже великий. Можна з легкістю зробити дані більш наочними, згрупувавши дані по днях в дані по місяцях, кварталах або роках.
Щоб згрупувати дати, клацніть дату у звіті. Потім на вкладці Параметри в групі Група натисніть кнопку Поле групи. У діалоговому вікні Угрупування виберіть параметр Квартали, що є прийнятним рішенням в даному випадку, а потім натисніть кнопку ОК.
Тепер видно дані з продажу для кожного з продавців, згруповані в чотири квартали.
Створення зведеного звіту за допомогою переміщення рядків в область стовпців
Хоча до цих пір за допомогою звіту зведеної таблиці можна було отримати відповіді на поставлені запитання, для читання всього звіту потрібен якийсь час. Для перегляду всіх даних слід здійснювати прокручування сторінки вниз.
Щоб отримати інше уявлення, можна виконати зведення звіту. Для цього слід перемістити поле з області «Назви рядків» в область стовпців звіту (яка має заголовок «Назви стовпців»). Ця область макета до справжнього моменту не використовувалася. При зведенні звіту виконується транспонування вертикального або горизонтального представлення поля шляхом переміщення рядків в область стовпців або стовпців в область рядків. Така задача вирішується просто.
Щоб виконати зведення цього звіту, клацніть правою кнопкою миші один з рядків «Квартал», а потім послідовно виберіть команди Перемістити і Перемістити поле «Дата замовлення» в стовпці. У результаті все поле Дата замовлення буде переміщене з області «Назви рядків» в область «Назви стовпців» звіту.
Тепер імена продавців об'єднані, дані по продажах розташовані в стовпцях, а над даними за перший квартал вказані «Заголовки стовпців». Крім того, в нижній частині кожного з стовпців знаходиться загальний підсумок по кварталу. Дані можна переглянути відразу, не виконуючи прокручування донизу.
Якщо потрібно повернути початкове уявлення звіту, клацніть правою кнопкою миші один із заголовків «Квартал» і виберіть команду Перемістити поле «Дата замовлення» в рядки. В результаті поле Дата замовлення буде переміщено назад в область рядків звіту.
Використання методу перетягування
Як згадувалося раніше, якщо при створенні звіту зведеної таблиці необхідно перетягувати поля мишею, як це робилося в більш ранніх версіях Excel, така можливість є і тут.
У нижній частині вікна Список полів зведеної таблиці доступно чотири поля. Ці поля носять назву Фільтр звіту, Назви рядків, Назви стовпців і Значення. Можна перетягувати поля в цю область.
Як правило, в Excel 2007 для додавання полів в макет звіту зі списку полів встановлюється прапорець поруч з іменем потрібного поля або виконується клацання поля правою кнопкою миші, щоб у меню вибрати розташування. У цьому випадку поля автоматично включаються в звіт і одночасно містяться у відповідне поле в нижній частині списку.
Наприклад, при розміщенні поля Продавець в області «Назви рядків» макета звіту ім'я поля Продавець також відображається в полі Назви рядків в нижній частині списку полів.
Тим не менше, якщо необхідно скористатися способом перетягування полів мишею, можна просто перетягнути потрібні поля з верхньої частини списку полів в поля, розташовані в нижній частині списку полів. Назва вгорі кожного поля вказує, в яку область звіту потрапить це поле. Наприклад, при перетягуванні поля Сума замовлень в полі Значення це поле буде поміщено в область звіту «Значення». Поля можна також перетягнути між полями, щоб змінити їх розташування в макеті звіту, а також перетягнути з полів, щоб видалити із звіту.
Завдання
По ходу ознайомлення з теоретичними відомостями необхідно створити таблицю за зразком:
Таблиця має містити не менше 20 рядків вигаданих даних з продажу будь-яких товарів для 4 країн.
Для створеної зведеної таблиці необхідно виконати всі пункти за зразком виконання в теоретичних відомостях.
2. Застосування фільтра до даних зведеного звіту в Excel 2007
Зведений звіт допомагає зрозуміти, що означають дані. Звіт можна зробити навіть більш наочним, застосувавши фільтр до його даних. При фільтрації відображається тільки потрібні дані. Всі інші дані тимчасово приховуються.
У цій лабораторній роботі ви дізнаєтеся, як можна в Excel 2007 переглядати тільки потрібні дані, а також познайомитеся зі способами, що дозволяють підвищити ефективність аналізу даних.
Використовуйте фільтр, щоб переглянути один продукт з багатьох.
Припустимо, необхідно переглянути дані по продажах дорожніх велосипедів, приховавши всі інші дані. Щоб застосувати до звіту фільтр, натисніть стрілку поруч із написом Назви рядків. Клацнути саме тут слід тому, що дані про дорожніх велосипедах відображаються в області рядків звіту. Після клацання з'явиться меню з полем Виберіть поле у верхній частині, в якому можна вказати, де буде застосований фільтр.
Меню містить список, в якому відображаються всі рядки у вибраному полі. Перегляд елементів даного списку дозволяє переконатися, що вибрано потрібне поле. У цьому прикладі потрібно застосувати фільтр до поля «Тип продукту», яке включає в себе продукт «Дорожні велосипеди».
Щоб застосувати до звіту фільтр, зніміть у списку прапорець (Виділити все). При цьому будуть зняті прапорці для всіх елементів списку. Потім встановіть прапорець поруч із елементом Дорожні велосипеди. Тепер у зведеному звіті будуть відображатися тільки дані по дорожнім велосипедів. Інші дані не змінилися, але зараз вони не відображаються.
Не завжди просто зрозуміти по відображуваним даними, застосований до них фільтр. Про те, що фільтр був застосований, свідчить значок фільтра на стрілці, з натискання якої починалася установка фільтра 
. Крім того, значок фільтра 
відображається в списку полів зведеної таблиці поряд з ім'ям поля, до якого був застосований фільтр.
Використовуйте фільтр, щоб переглянути один тип продуктів
Після того як фільтр був встановлений, у звіті відображаються тільки дані по продажах дорожніх велосипедів. Однак компанія продає багато різних типів дорожніх велосипедів, і може виникнути необхідність переглянути підсумки продажів тільки для одного типу моделі - Road-350-W.
Відфільтрувати звіт для відображення даних тільки по одному типу продукту можна за допомогою методу, розглянутого в останньому розділі. Для цього потрібно вибрати в полі Виберіть поле Найменування продукту замість поля Категорія продуктів. У списку найменувань продуктів слід встановити прапорці для моделі дорожнього велосипеда Road-350-W.
Однак є інший, більш ефективний метод, який полягає в уточненні вже встановленого фільтра. У звіті, до якого вже був застосований фільтр, виділіть комірки з даними по моделі Road-350-W. Потім клацніть правою кнопкою миші і послідовно виберіть команди Фільтр і Зберегти тільки виділені елементи.
Тепер відображаються тільки дані по моделі Road-350-W. Новий фільтр приховує всі інші найменування дорожніх велосипедів і дані по них, раніше виводилися в звіті.
Застосуйте фільтр, щоб побачити, які дорожні велосипеди дають найбільший прибуток
Припустимо, що потрібно дізнатися, для яких моделей дорожніх велосипедів загальний обсяг продажів перевищує 100 000 рублів. Щоб відобразити тільки потрібні рядки, приховавши інші, в Excel використовується фільтр за значенням.
Спочатку слід встановити фільтр по дорожнім велосипедів, скориставшись методом, показаним в першій анімації. Щоб встановити фільтр за значенням, клацніть стрілку на значку фільтра поруч з елементом Назви рядків. У полі Виберіть поле потрібно вибрати поле Найменування продукту. Це поле слід вибрати в зв'язку з тим, що в ньому наводяться всі моделі дорожніх велосипедів.
Потім виберіть команду Фільтри за значенням. Даний фільтр зчитує дані і відбирає рядки з комірками, що відповідають встановленим критеріям фільтра. Виберіть параметр Більше або дорівнює та в діалоговому вікні Фільтр по значенню введіть в порожньому полі значення 100 000.
Звіт містить відомості по 38 моделям дорожніх велосипедів, з яких для 13 моделей загальний обсяг продажів перевищує 100 000 рублів. У звіті будуть тепер відображатися дані тільки по цих моделях. Щоб змінити значення фільтра, виберіть команду Фільтри за значенням і повторно виконайте описану вище процедуру, використовуючи інше значення. Таким чином було наочно продемонстровано, як просто можна аналізувати різноманітні дані за допомогою таких фільтрів.
Використовуйте фільтр, щоб переглянути один тип продуктів
Нарешті, припустимо, що необхідно дізнатися, як продавалися дорожні велосипеди протягом певного року, місяця або іншого періоду часу. Шляхом встановлення відповідного фільтра можна задати використовуваний для звіту тимчасової інтервал і тимчасово приховати дані по всім іншим періодам.
Встановити фільтр по окремому році досить просто. Щоб переглянути дані тільки за 2011 рік, натисніть стрілку поруч із написом Назви стовпців. Процедура починається з цієї стрілки, оскільки дати відображаються в області стовпців звіту. У списку зніміть прапорець поруч із параметром (Виділити все) і встановіть прапорець для елемента 2011 року. Дані по роках 2009 і 2010 будуть приховані. Ось і все.
Тепер припустимо, що потрібно дізнатися, як продавалися дорожні велосипеди протягом окремого місяця в 2011 році. Щоб задати період часу в якості фільтра по даті, натисніть стрілку поруч із написом Назви стовпців. Виберіть команду Фільтри за датою. Клацніть елемент між. У діалоговому вікні Фільтр за датою в першому порожньому полі введіть 8.11.2011. У поле та введіть 8.12.2011. У звіті будуть відображені дані по дорожнім велосипедам тільки за вказаний місяць.
Видалення фільтра
Щоб знову відобразити всі приховані дані та повернутися до загальної картини, можна вибрати або простий і вимагає значного часу спосіб, послідовно знімаючи всі фільтри, або очистити всі фільтри одночасно.
Для послідовного зняття фільтрів скористайтеся значком фільтра, який відображається в двох різних місцях - у зведеному звіті та у списку полів зведеної таблиці.
Щоб зняти всі фільтри одночасно, скористайтеся командами на стрічці у верхній частині вікна.
Видалення фільтра у зведеному звіті
Щоб зняти фільтр з окремого поля, клацніть значок фільтра там, де поле з застосованим фільтром відображається у звіті, в області Назви рядків або в області Назви стовпців. Потім виберіть команду Зняти фільтр з <Ім'я поля>. Можна також установити прапорець для параметра (Виділити все), щоб відобразити всі дані в цьому полі.
Якщо команда Зняти фільтр з для поля з фільтром недоступна, виконайте такі дії:
• Переконайтеся, що для видалення фільтра обрана правильна область звіту: рядки або стовпці.
• Переконайтеся, що ім'я поля в полі Виберіть поле вказано вірно. Це поле відображається, якщо клацнути значок фільтра. Ім'я поля в цьому полі має збігатися з ім'ям поля, з якого потрібно зняти фільтр. Якщо ім'я поля в даному полі зазначено невірно, виберіть потрібне поле зі списку, який виводиться на екран при натисканні стрілки поруч з даним полем.
Видалення фільтра у вікні «Список полів зведеної таблиці»
Перемістіть покажчик миші на значок фільтра, розташований поруч з ім'ям того поля, з якого потрібно зняти фільтр. Натисніть стрілку і виберіть команду Зняти фільтр з <Ім'я поля>. Можна також установити прапорець для параметра (Виділити все), щоб відобразити всі дані в цьому полі.
Видалення всіх фільтрів
У верхній частині вікна на стрічці виберіть вкладку Параметри під кнопкою Робота зі зведеними таблицями. У групі Дії натисніть кнопку Очистити і виберіть команду Очистити фільтри. Переконайтеся, що це дійсно необхідно, оскільки при виконанні даної дії будуть зняті всі фільтри.
1 - Щоб видалити фільтр із зведеного звіту, клацніть значок фільтра, а потім виберіть команду Зняти фільтр з «Тип продукту».
2 - Щоб видалити фільтр у вікні Список полів зведеної таблиці, перемістіть покажчик миші на значок фільтра, розташований поруч з ім'ям поля, натисніть стрілку, а потім виберіть команду Зняти фільтр з «Тип продукту».
Завдання
По ходу ознайомлення з теоретичними відомостями необхідно створити таблицю на основі наведеної зведеної таблиці та отримати цю зведену таблицю як результат:
Таблиця має містити дві групи товарів: Фляги та Перчатки, по три види товару в кожній групі та результати їх продажу на протязі трьох років.
Виконати всі пункти роботи над створенням фільтрів за зразком в теоретичних відомостях.
3. Обчислення даних у звітах зведених таблиць в Excel 2007
В Excel числа в звітах зведених таблиць автоматично підсумовуються з використанням функції СУММ, яка називається підсумкової функцією. Для обчислення значень іншим способом можна використовувати інші підсумкові функції (наприклад, щоб обчислити середнє значення або підрахувати число показників).
Значення можна також представити у вигляді частки від загальної суми або створити наростаючий підсумок, скориставшись додатковими обчисленнями. Крім того, у звітах зведених таблиць можна створювати власні формули.
Обчислення в зведених таблицях в Excel 2003
Існує декілька типів обчислень, результати яких можуть бути відображені в зведеній таблиці. Крім того, можна задавати додаткові операції обчислень, як, наприклад, С нарастающим итогом в поле.При використанні цієї операції значення комірок області даних відтворюється у вигляді наростаючого підсумку зведеної таблиці.
У табл. 1 перераховані деякі функції Excel, що можна використовувати для обчислення підсумків у зведених таблицях.
Таблиця 1
Підсумкові функції зведеної таблиці
| Ім’я функції | Опис |
| СУМ | Підсумовує числа в списку по обраних полях |
| СРЗНАЧ | Обчислює середнє значення по полях у списку |
| МИН | Повертає найменше значення у списку |
| МАКС | Повертає найбільше значення у списку |
| СЧЕТЗ | Підраховує тільки кількість чисел у стовпці, що може містити і текст |
| ПРОИЗВЕД | Обчислює добуток всіх чисел у списку |
У зведеній таблиці можна здійснювати додаткові обчислення в полі даних: для окремого елемента поля і для всіх його елементів. Для деяких додаткових обчислень необхідно визначити обчислювальне поле, і обчислювальний елемент поля, з якими порівнюються всі інші значення.
У табл.2 перераховані деякі види додаткових обчислень, які можна виконувати над елементами зведеної таблиці.
Таблиця 2
Додаткові обчислення в полі даних зведеної таблиці.
| Ім’я операнда | Опис |
| Відмінність | Значення комірок області даних відтворюються у вигляді різниці з заданим обчислювальним елементом поля |
| Частка | Значення комірок області даних відтворюються у відсотках до заданого обчислювального елементу поля |
| З наростаючим підсумком у полі | Значення комірок області даних відтворюються у вигляді наростаючого підсумку для послідовних елементів |
| Частка від суми по рядку | Значення комірок області даних відтворюється у відсотках від підсумку рядка |
| Частка від суми по стовпцю | Значення комірок області даних відтворюються у відсотках від підсумку стовпчика |
| Частка від загальної суми | Значення комірок області даних відтворюються у відсотках від підсумку зведеної таблиці |
Змініть функцію, що підсумовує дані
Потрібно замінити в звіті додавання сум продажів на підрахунок кількості продажів, що припадають на одного продавця протягом року. Це можна зробити за допомогою зміни функції підсумовування, використовуваної в області звіту «Значення», з функції СУММ на функцію СЧЕТ.
Щоб змінити цю функцію, клацніть правою кнопкою миші в будь-якій частині області «Значення» звіту, яка знаходиться під заголовком «Сума по полю Сума продажів». Вкажіть команду Підсумки по, а потім клацніть елемент Кількість. Числа зміняться з суми значень на кількість значень. Заголовок над числами зміниться з варіанту «Сума по полю Сума продажів» на варіант «Кількість по полю Сума продажів».
Потім можна відсортувати замовлення, щоб відразу зрозуміти, у кого їх було найбільше. Для цього клацніть правою кнопкою миші проміжний підсумок для будь-якого з продавців, виберіть команду Сортувати, а потім - команду Сортування від максимального до мінімального. Першим виявився Єгоров із загальним підсумком 100 замовлень. За ним слідує Гладких, у якого 74 замовлення, а за Гладких - Ільїна (73 заказу).
Щоб переключитися назад на підсумовування замовлень, знову клацніть правою кнопкою миші в області «Значення», вкажіть команду Підсумки по, а потім клацніть елемент Сума
Відсортуйте дані за спаданням
Перегляньте обсяги продажів як відсоток від загальної суми продажів
Тепер потрібно переглянути, яка частка підсумкової суми продажів доводиться на кожного з продавців. У Єгорова найбільше замовлень, проте неясно, чи має він найбільшу частку в загальній сумі продажів.
Це можна з'ясувати за допомогою додаткових обчислень. Додаткові обчислення дозволяють створити наростаючий підсумок або обчислити процентне відношення між продажами окремого продавця і загальними продажами.
Щоб зробити це, клацніть правою кнопкою миші в області «Значення». Послідовно виберіть команди Підсумки пої Додаткові параметри. Відкриється діалогове вікно Параметри поля значень, де потрібно клацнути вкладку Додаткові обчислення. Потім у полі Додаткові обчислення клацніть стрілку та виберіть параметр Частка від загальної суми.
На частку Єгорова припадає 18 відсотків загальних продажів за рік, що більше, ніж у інших продавців, так що в даному випадку максимальна частка в загальних продажах відповідає найбільшій кількості проданих замовлень. Далі слід Гладких, у якого 14,42 відсотка, а за ним - Клімов з часткою в 12,52 відсотка.
Щоб повернутися в звичайне представлення чисел, виконайте ті ж кроки, а потім виберіть параметр Звичайне. Додаткові обчислення будуть відключені. 
Розрахуйте суму премії за Квартальні продажі, що перевищують 30 000 р..
Тепер належить визначити, створивши формулу з використанням елемента обчислюване поле, хто має отримати премію і яка буде сума преміальних. Формули, які створюються з використанням обчислюваного поля, можуть бути засновані на будь-якому з полів, на базі яких створений звіт. При використанні обчислюваного поля в звіт зведеної таблиці додається нове поле.
Припустимо, що кожен продавець, який продав за квартал товару на суму більше 30 000 рублів, отримує в цьому кварталі премію в розмірі 3%.
Щоб створити формулу, у верхній частині вікна на стрічці виберіть вкладку Параметри під кнопкою Робота зі зведеними таблицями. У групі Сервіс клацніть стрілку кнопки Формули та виберіть команду Обчислюване поле.
У діалоговому вікні Вставка обчислюваного поля введіть ім'я формули в полі Ім'я. У полі Формула введіть наступну формулу, що дозволяє виявити продавців, яким вважається премія: = 'Сума продажів' * ЕСЛИ ('Сума продажів'> 30000,3%). Натисніть кнопку ОК.
Ця формула означає, що в разі, якщо сума продажів за квартал перевищила 30 000, премія становитиме 3 відсотки. Ці 3 відсотки помножуються на значення поля «Сума продажів». Якщо сума продажів за квартал менше 30 000, сума премії за цей квартал дорівнює нулю.
У звіт вставляється нове поле «Сума по полю Премія руб.», В якому показані премії, належні кожному з продавців.
Можна зауважити наступне. Додаткова премія в 3 відсотки наводиться в рядках підсумків по продавцях. Чому? Формула обчислюваного поля виконується в Excel порядково. У кожному рядку підсумків, де загальний підсумок перевищує 30 000, формула обчислюється так само, як і в інших рядках. Відповідно до математикою формула діє в рядку підсумків вірно, однак дає невірний результат.
Як вирішити цю проблему? Відключіть автоматичне обчислення підсумків, щоб у звіті виводилися тільки суми по кварталах без підсумків по продавцях. Про те, як це зробити, розповідається на практичному занятті.
Завдання
Ознайомтесь з теоретичними відомостями. Виконайте всі пункти роботи за зразком в теоретичних відомостях в Excel для таблиці даних, яка наведена нижче.
1. Виконайте наступні завдання:
Завдання 1.
Створіть реєстраційний журнал виконання замовлень за червень, який зображений на рисунку. Список відсортований за датами.
Завдання 2
На основі попереднього списку створіть декілька зведених таблиць:
· Загальне число годин, відпрацьованих кожним співробітником.
· Загальне число годин, відпрацьованих за кожним замовленням (кожне замовлення подається за своїм номером у стовпці «Рахунок №»).
· Загальне число годин, відпрацьованих по днях.
· Загальне число годин, витрачених на виконання різних видів роботи, представлених номерами в стовпчику «Код».
· Загальне число годин, витрачених на виконання замовлень різних компаній.
· Загальне число годин, витрачених кожним співробітником на виконання певної роботи.
Завдання 3 .
· Створіть зведену таблицю, у якій підраховується загальне число годин, витрачених на виконання замовлень різних компаній.
· Створіть зведену таблицю, у якій підраховується загальне число годин, витрачених на виконання різних видів робіт, представлених номерами в стовпці «Код».
Завдання 4.
Відформатуйте вашу зведену таблицю за допомогою одного з вмонтованих форматів діалогового вікна Автоформат.
Завдання 5.
Створіть зведену таблицю за списком, представленим таблицею, наведеною нижче. Згрупуйте позиції «квітень», «травень», «червень» у групу «Квартал 2».
Завдання 6.
Випробуйте на вашій зведеній таблиці види функцій і додаткових обчислень, приведених в теоретичних відомостях.
Завдання 7.
Створіть зведену таблицю, помістивши в поле сторінки поле «Замовник», в область рядків – поле «Співробітник», в область стовпців поле «Код», в область даних – поле «Термін».
Завдання 8.
По одній зі створених зведених таблиць побудувати діаграму.
| Торгівельні представники | Місяць | Обсяг продажів |
| Дьомін Г.В. | Січень | 32 000 |
| Дьомін Г.В. | Лютий | 9 750 |
| Дьомін Г.В. | Березень | 12 250 |
| Дьомін Г.В. | Квітень | 22 100 |
| Дьомін Г.В. | Травень | 13 500 |
| Дьомін Г.В. | Червень | 14 785 |
| Зайченко M.I. | Січень | 12 250 |
| Зайченко M.I. | Лютий | |
| Зайченко M.I. | Березень | 13 500 |
| Зайченко M.I. | Квітень | 28 457 |
| Зайченко M.I. | Травень | 29 785 |
| Зайченко M.I. | Червень | 16 895 |
| Зима Л Д. | Січень | 8 500 |
| Зима Л .Д. | Лютий | 32 000 |
| Зима Л .Д. | Березень | 9 750 |
| Зима Л .Д.. | Квітень | 12 250 |
| Зима Л .Д. | Травень | 22 100 |
| Зима Л .Д. | Червень | 13 500 |
| Литвинов А.А. | Січень | 24 500 |
| Литвинов А.А. | Лютий | 18 251 |
| Литвинов А.А. | Березень | 45 441 |
| Литвинов А.А. | Квітень | 32 916 |
| Литвинов А.А. | Травень | 21 247 |
| Литвинов А.А. | Червень | |
| Степаненко B.I. | Січень | 22 100 |
| Степаненко B.I. | Лютий | 19 785 |
| Степаненко B.I. | Березень | 12 458 |
| Степаненко B.I. | Квітень | 10 223 |
| Степаненко B.I. | Травень | 32 000 |
| Степаненко B.I. | Червень | 9 750 |
| Степанов С.Ф. | Січень | 19 000 |
| Степанов С.Ф. | Лютий | 24 175 |
| Степанов С.Ф. | Березень | 25 180 |
| Степанов С.Ф. | Квітень | 42 578 |
| Степанов С.Ф. | Травень | 41 785 |
| Степанов С.Ф. | Червень | 34 596 |
| Цвігун Л.В. | Січень | 45 781 |
| Цвігун Л.В. | Лютий | 25 180 |
| Цвігун Л.В. | Березень | 42 578 |
| Цвігун Л.В. | Квітень | 41 785 |
| Цвігун Л.В. | Травень | 34 596 |
| Цвігун Л.В. | Червень | 36 237 |
Таблиця - Список даних для створення зведеної таблиці