На третьому листі книги Microsoft Excel вирішити наступну задачу:
Клієнт отримує дисконт у розмірі 15% у випадку придбання більше двох путівок на загальну суму, що перевищує 100000 грн.
Методичні вказівки з виконання завдання 3:
Дана задача передбачає виконання відразу двох умов, тому доцільно використати разом з функцією ЕСЛИ функцію И. Для цього у клітинку F2 необхідно ввести формулу: =ЕСЛИ(И(С2>2;Е2>=100000);E2*0,15;0).
Завдання 4
На четвертому листі книги Microsoft Excel побудувати таблицю з використанням аналогічних способів форматування і заповнити її даними.
У клітинках на місці символу * створити необхідні формули, використовуючи відповідні стандартні функції, для вирішення наступної задачі:
За заданим списком клієнтів і дати їх народження розрахувати вік клієнта на поточну дату, визначити кількість повнолітніх клієнтів та найстаршого клієнта.
Методичні вказівки з виконання завдання 4:
Перейти на Лист 4 книги Microsoft Excel та у клітинці Е2 ввести формулу: =ГОД(СЕГОДНЯ()-С2)-1900, яка дозволить розрахувати вік клієнтів на поточну дату.
Визначення кількості повнолітніх клієнтів виконується за допомогою функції СЧЁТЕСЛИ. Для цього слід поставити курсор у клітинку Е12 і скористатися майстром функцій, що визивається командою Вставка – Функция. У діалоговому вікні вибрати категорію «Статистические» і функцію СЧЁТЕСЛИ та заповнити наступне вікно за зразком, що наведено на рис. 7.6.
Рисунок 7.6 – Діалогове вікно функції СЧЁТЕСЛИ
В результаті отримаємо формулу: =СЧЁТЕСЛИ(Е2:Е11;>=18)
Для визначення найстаршого клієнта слід використати функцію МАКС. Для цього необхідно поставити курсор у клітинку Е13 і скористатися майстром функцій, що визивається командою Вставка – Функция. У діалоговому вікні вибрати категорію «Статистические» і функцію МАКС та заповнити наступне вікно за зразком, що наведено на рис. 7.7.
Рисунок 7.7 – Діалогове вікно функції МАКС
В результаті отримаємо формулу: =МАКС(Е2:Е11)
8. Робота зі зведеними таблицями. Консолідація даних
8.1 Створення зведених таблиць
Зведена таблиця є одним з інтерактивних інструментів аналізу даних. Зведені таблиці дають змогу виконати угрупування даних таблиць Microsoft Excel за одним або декількома критеріями і обробку інформації по групах, підгрупах.
Визначимо основні терміни.
Джерело даних – це список або база даних, на основі яких створюється зведена таблиця. Зведені таблиці можна створювати на основі відомостей, що знаходяться:
у списку даних Excel;
у декількох діапазонах консолідації;
у інших зведених таблицях;
у зовнішніх базах даних.
В якості зовнішніх баз даних можуть використовуватися бази даних MS SQL Server, MS FoxPro, MS Access та інші.
Зведені таблиці формуються за допомогою майстра зведених таблиць, який забезпечує об’єднання і розміщення даних, що аналізуються з можливістю їх впорядкування та перерахунку.
Поля зведеної таблиці генеруються майстром зведених таблиць, виходячи з заголовків стовбців списків Excel або імен полів бази даних. Назви полів виводяться на третьому етапі роботи майстра у правій частині екрану.
Поля сторінки дозволяють виводити дані по одному елементу. Це забезпечує можливість роботи з великим обсягом даних.
Областю даних називається частина зведеної таблиці, що містить підсумкові дані для елементів полів рядку або стовпця.
Позиції – це підкатегорії зведеної таблиці, що відображають спосіб підведення підсумків.
Розглянемо порядок створення зведеної таблиці нам основі списку Excel.
Щоб побудувати зведену таблицю слід подати команду меню Данные – Сводная таблица. У результаті буде відчинено вікно майстра зведених таблиць, що включає в себе 4 етапи.
На першому етапі роботи з майстром зведених таблиць потрібно обрати джерело даних для зведених таблиць - в списке или базе данных Microsoft Excel і тип звіту, що створюється, - сводная таблица. Натиснути на кнопці Далее.
На другому етапі слід виділити діапазон клітинок, у якому містяться дані для зведеної таблиці. Незалежно від способу угрупування та обробки потрібно виділити усю таблицю одним блоком без підсумкового рядка. У кожній клітинці першого рядка блока повинні знаходитись назви показників, що можуть використовуватись для угрупування або обробки, тому неприпустимою є наявність порожньої клітинки у першому рядку. Всі клітинки починаючи з другого рядка блоку розглядаються табличним процесором як інформаційні – використовуються для групування або обробки. Тому шапка таблиці повинна мати просту структуру.
На третьому етапі роботи з майстром слід визначити макет зведеної таблиці та її місце розташування. За допомогою кнопки Макет можна визначити порядок групування та обробки. Щоб згрупувати дані за певним показником слід перетягти назву показника в область Строка або Столбец. Якщо елемент показника поміщено в область Строка, то в зведеній таблиці буде стільки рядків, скільки значень даного показника існує в таблиці – джерелі даних. Відповідно можна згруповані дані вивести у стовпцях. В область Строка або Столбец можна помістити декілька показників. У цьому випадку перший показник буде створювати групу, другий підгрупу в групі і т.д. Для обробки даних відповідні елементи слід перетягти у область Данные. За замовчуванням застосовується операція сумування. Для зміни операції можна виконати подвійне клацання на елементі в області Данные.
На четвертому етапі визначаємо місце розташування зведеної таблиці. Зведену таблицю можна розташувати на новому листі робочої книги (до книги додається новий стандартний робочий лист) або на існуючому. В останньому випадку слід визначити адресу клітинки, яка є верхнім лівим кутом електронної таблиці.
Для редагування зведеної таблиці слід виділити будь-яку клітинку всередині таблиці, подати команду меню Данные – Сводная таблица.