Проміжні підсумки. Умовне форматування

1.    Що таке сортування даних таблиці? Якими способами можна виконати сортування?

2.    Що таке фільтрування даних таблиці? Якими способами можна його виконати?

3.    Що таке розширене фільтрування? Наведіть приклад його використання.

ПРОМІЖНІ ПІДСУМКИ

Часто в одному або кількох стовпцях таблиці містяться дані, що повторюються. Це можуть бути, наприклад, гуртки, які відвідують учні, назви фірм-постачальників, товарів, пунктів призначення руху потягів та ін.

На малюнку 7.31 подано таблицю з даними про надходження та продаж книжок у деякому книжковому магазині.

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

з авторів більш популярні, а які - менше, яка з фірм постачає книжки, що користуються більшим попитом, та ін.

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

Одним зі способів виконати такі операції є знаходження проміжних підсумків.

Щоб знайти проміжні підсумки, потрібно:

1.    Виділити клітинки, для значень яких будуть знаходитися проміжні підсумки.

2. Виконати сортування виділеного діапазону клітинок за даними в тому стовпці, за яким потрібно згрупувати дані.

3.    Виконати Дані ^ Структура ^ Проміжні підсумки.

4.    Вибрати у вікні Проміжні підсумки (мал. 7.32) у списку поля Використовувати функцію ту функцію, яка буде опрацьовувати дані (Сума, Кількість, Середнє, Максимум тощо).

5.    Установити у вікні Проміжні підсумки в списку поля Додати підсумки до позначки прапорців поруч з назвами тих стовпців, для яких будуть обчислені проміжні підсумки.

6.    Вибрати кнопку ОК.

На малюнку 7.33 подано результат виконання цього алгоритму для таблиці, розглянутої вище. Дані в таблиці об’єднані в групи за значеннями в стовпці Прізвище і для кожної групи утворився рядок Підсумок, у кожному з яких підраховані суми в стовпцях Надійшло, Продано, Прибуток і Остача. Кожна з груп повністю розкрита, про що свідчать кнопки - ліворуч від кожної з них. Якщо вибрати одну із цих кнопок, то відповідна група рядків згорнеться, відображатиметься тільки рядок Підсумок, а кнопка

ліворуч від групи набуде вигляду + . У будь-який момент можна вибрати цю кнопку і знову розгорнути відповідну групу.

Над кнопками згортання/розгортання груп знаходяться три кнопки [В' Щ швидкого згортання/розгортання рівнів згрупованої таблиці: перший рівень - це вся таблиця, другий - групи, третій - члени груп.

УМОВНЕ ФОРМАТУВАННЯ

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

Умовне форматування автоматично змінює формат клітинки на заданий, якщо виконується задана умова.

Наприклад, в електронному журналі класу зручно позначати різними кольорами дані про учнів, які мають оцінки низького, середнього, достатнього та високого рівнів. Тобто клітинки, значення яких знаходяться в діапазоні від 1 до 3, виділити червоним кольором, оцінки від 4 до 6 -зеленим, від 7 до 9 - жовтим, від 10 до 12 - синім кольором.

Звертаємо вашу увагу: на відміну від фільтрування, умовне форматування не приховує клітинки, значення в яких не задовольняють заданої

умови, а лише виділяє в таблиці певним чином ті клітинки, значення в яких задовольняють задану умову.

В Excel 2007 для умовного форматування існує п’ять способів виділення клітинок (мал. 7.34):

•    Виділити правила клітинок.

•    Правила для визначення перших і останніх елементів.

•    Гістограми.

•    Кольорові шкали.

•    Набори піктограм.

Для встановлення перших двох видів

умовного форматування, а саме встановлення правил форматування, слід:

1.    Виділити потрібний діапазон клітинок.

2.    Виконати Основне ^ Стилі ^ Умовне форматування.

3.    Вибрати у списках кнопки Умовне форматування потрібний спосіб (мал. 7.34).

4.    Указати у списку вибраного способу потрібне правило форматування.

5.    Задати у вікні, що відкриється, умову форматування та формат, який буде встановлено, якщо умова виконуватиметься (мал. 7.35). Формат можна вибрати з наведених або, вибравши команду Настроюваний формат, установити власний у вікні Формат клітинок.

6.    Вибрати кнопку ОК.

На малюнку 7.35 подано, як приклад, вікно Між, у якому встановлено умову форматування Між 7 і 9, зі списком стандартних форматів, а також попередній перегляд вибраного умовного форматування клітинок таблиці.

Установлення іншого виду умовного форматування типу Гістограми приводить до вставлення в клітинки виділеного діапазону гістограм, довжина кольорового рядка яких пропорційна значенню в клітинці (мал. 7.36).

Установлення умовного форматування типу Кольорові шкали приводить до встановлення заливки клітинок виділеного діапазону так, що клітинки з однаковими значеннями мають один і той самий колір заливки (мал. 7.37).

Можна також вибрати вид умовного форматування зі списку Набори піктограм. Під час такого форматування в клітинках виділеного діапазону з’являтимуться піктограми з вибраного набору. Поява конкретної піктограми з набору в клітинці означає, що значення в цій клітинці задовольняє умову, встановлену для кожної піктограми набору.

Звертаємо вашу увагу: до одного діапазону клітинок може бути застосовано кілька правил, заданих послідовно.

Для видалення умовного форматування потрібно виконати Умовне форматування ^ Правила очищення ^ Очистити правила у виділених клітинках (Очистити правила у всьому аркуші).

Дайте відповіді на запитання

1°. Що таке проміжні підсумки? Коли їх доцільно створювати? 2*. Як знайти проміжні підсумки в Excel 2007?

3*. Які функції можна обчислювати в проміжних підсумках?

4° Що таке умовне форматування? Для чого воно виконується? 5°. Які є способи умовного форматування?

6*. Як виконати умовне форматування?

7°. Як очистити умовне форматування на всьому аркуші?

Виконайте завдання

і°. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.і.х1ях). На аркуші Аркуші подано таблицю зі значеннями властивостей вінчестерів. Обчисліть проміжні п ідсумки за значеннями в стовпці Фірма. Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.1.х1ях.

2*. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.2.х1ях). На аркуші Аркуші подано таблицю зі значеннями властивостей моніторів. Утворіть проміжні підсумки за значеннями в стовпці Діагональ. Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.2.х1ях.

3*. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.3.х1ях). На аркуші Аркуші подано таблицю з даними про ТЕТ-монітори. Зафарбуйте в рожевий колір усі клітинки, у яких діагональ монітора менша ніж 21". Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.3.х1ях.

4*. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.4.х1ях). На аркуші Аркуші подано таблицю з даними про жорсткі магнітні диски. Установіть синій колір клітинок, у яких ємність кеш-пам’яті більша ніж 8 Мбайт. Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.4.х1ях.

5*. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.5.х1ях). На аркуші Аркуші подано таблицю з даними про температуру за останній місяць в м. Києві. Установіть умовне форматування Гістограми (фіолетова лінійка даних) для всіх числових даних таблиці. Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.4.х1ях.

6*. Відкрийте вказаний учителем файл (наприклад, Розділ 7\Пункт 7.5\зразок 7.5.6.х1ях). Знайдіть в Інтернеті дані про кількість населення у вашому обласному центрі за останні 5 років. На аркуші Аркуші створіть електронну таблицю за цими даними. Установіть

умовне форматування з використанням Наборів піктограм (4 світлофори) для даних таблиці. Збережіть книгу у вашій папці у файлі з іменем завдання 7.5.6.х1вх.

ПРАКТИЧНА РОБОТА № 16

«Упорядкування даних у таблицях. Автоматичні та розширені фільтри.

Умовне форматування»

Увага! Під час роботи з комп’ютером дотримуйтеся правил безпеки та санітарно-гігієнічних норм.

1.    Відкрийте вказаний учителем файл (наприклад, Розділ 7\Практична 16\зразок пріб.хівх).

2.    На аркуші Аркуші виконайте сортування таблиці за значеннями в стовпці Прізвище.

3.    На аркуші Аркуш2 виконайте сортування таблиці за значеннями в кількох стовпцях в указаному порядку: Прізвище (за зростанням), Ім’я (за зростанням), Рік народження (за спаданням).

4. На аркуші АркушЗ відобразіть дані про учнів, зріст яких вище за 1 м 80 см.

5.    На аркуші Аркуш4 відобразіть дані про учнів, які відвідують гуртки малювання або співу.

6. На аркуші Аркуш5 знайдіть проміжні підсумки за значеннями у стовпці Вага учнів, які знаходять середню та максимальну вагу.

7.    На аркуші Аркушб установіть умовне форматування, щоб клітинки, в яких вага учнів більше ніж 80 кг, зафарбовувалися червоним кольором, а клітинки, у яких вага учнів менше ніж 75 кг, - синім.

8. Збережіть книгу у вашій папці у файлі з іменем практична іб.хівх.

Це матеріал з підручника Інформатика 8 клас Ривкінд