Вишня В.Б., Ілюхін С.М., Кравець В.П., Шумейко О.О. Основи інформатики та обчислювальної техніки (навчально-методичний посібник для курсантів, студентів та слухачів)
Подождите немного. Документ загружается.
211
• Збережіть робочу книгу book.xls.
Ми навчилися умовно форматувати документ Excel. При умовному
форматуванні оформлення осередків залежить від їхнього змісту. Ми також
навчилися поширювати умовне форматування одного осередка на цілий
діапазон.
Самостійна робота. Використовуючи умовне форматування зробіть
так, щоб під час заповнювання другого та третього стовбця таблиці
“Динаміка загальної злочинності” (з вправи
1) найбільше число мало
червоний колір шрифту, а найменше – зелений.
Вправа 6. Побудова діаграми.
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls, створену раніше.
• Відкрийте робочий лист “Додаткові витрати по місяцях”.
• Методом протягання виділіть діапазон осередків А2:С25.
• Клацніть на значку Мастер диаграмм на стандартній панелі
інструментів.
• У списку Тип виберіть пункт Гистограмма (для відображення даних у
виді стовпчастої діаграми). У палітрі Вид виберіть нижній пункт у
першому стовпці (тривимірна гістограма). Клацніть на кнопці Далее.
• Тому що діапазон осередків був виділений заздалегідь, майстер діаграм
автоматично визначає розташування рядів даних. Переконаєтеся, що
дані на діаграмі обрані правильно.
• На вкладці Ряд виберіть пункт Ряд1, клацніть у поле Ім’я, а потім на
осередку В1. Аналогічно, виберіть пункт Ряд2 і клацніть спочатку в
поле Ім’я, а потім на осередку С1. Клацніть на кнопці Далее.
• Виберіть вкладку Заголовки. Задайте заголовок діаграми, ввівши в
поле Название диаграммы текст “Діаграма витрат”. Клацніть на
212
кнопці Далее.
• Установите перемикач Отдельном. За бажанням, задайте довільне ім’я
робочого листа, що додається. Клацніть на кнопці Готово.
• Переконайтеся, що діаграма побудована і впроваджена в новий
робочий лист. Розгляньте її. Спробуйте навести покажчик миші на
кожний з елементів діаграми. Переконайтеся, що в спливаючому вікні
відображається точне значення даного елемента діаграми.
• Клацніть на одному з елементів ряду “Наростаючий підсумок”.
Переконайтеся, що весь ряд виділений.
• Дайте команду Формат > Выделенный ряд. Відкрийте вкладку Вид.
• Клацніть на кнопці Способы заливки. Поставте перемикач Заготовка,
у списку, що розкривається, виберіть пункт Океан, задайте тип
штрихування діагональна 1. Клацніть на кнопці ОК і ще раз на кнопці
ОК. Подивіться, як змінився вид ряду даних.
• За своїм розсудом змініть оформлення ряду даних “Витрати” й інші
елементи діаграми.
• Збережіть робочу книгу book.xls.
Ми навчилися будувати графіки на основі даних, що містяться на
робочому листі, налагоджувати формат діаграми, задавати відображувані
дані й оформляти готову діаграму. Ми також довідалися, як можна змінити
формат готової діаграми.
Самостійна робота. Наберіть таблицю:
Таблиця 5.5.
№ HAЙMEHУBAHHЯ
ОБЛАСТЕЙ,МІСТ
ЗAPEEСТРО
ВАНО
ЗЛОЧИНІВ
ДИН
А-
МІКА
ПИТОМА
ВАГА ВІД
РОЗСЛІДУВ
АНИХ
ЗЛОЧИНІВ
(В %)
НЕРОЗКРИТО
1999 2000 1999 2000 1999 2000
1 PECПУБЛІKA KPИM 8052 7380 727 997
213
2 BІHHИЦЬKA 3659 3879 404 541
3 BOЛИHCЬKA 1820 2101 288 402
4 ДHІПPOПETPOBCЬKA 15663 16603 3494 3098
5 ДOHEЦЬKA 14144 14355 3205 3024
6 ЖИTOMИPCЬKA 3449 3615 521 365
7 ЗAKAPПATCЬKA 1937 1888 239 208
8 ЗAПOPІЗЬKA 7828 7474 1399 1397
9 ІBAHO-
ФPAHKІBCЬKA
2172 2209 214 189
10 KИЇBCЬKA 3407 3782 673 563
11 M. K И Ї B 7147 6641 1048 1336
12 KІPOBOГPAДCЬKA 3108 3113 427 553
13 ЛУГAHCЬKA 11343 11183 1451 1503
14 ЛЬBІBCЬKA 5340 5457 537 683
15 MИKOЛAЇBCЬKA 4010 3764 764 799
16 OДECЬKA 6449 6743 1410 1243
17 ПOЛTABCЬKA 4792 4702 665 754
18 PІBHEHCЬKA 2239 2209 688 220
19 M. CEBACTOПOЛЬ 1150 1146 170 170
20 CУMCЬKA 3586 3521 679 648
21 TEPHOПІЛЬCЬKA 1787 1849 174 150
22 XAPKІBCЬKA 9680 11352 1777 1782
23 XEPCOHCЬKA 3489 3946 588 618
24 XMEЛЬHИЦЬKA 2492 2600 299 365
25 ЧEPKACЬKA 3503 3415 642 681
26 ЧEPHІГІBCЬKA 3270 3162 407 279
27 ЧEPHІBEЦЬKA 1414 1346 98 111
BCЬOГO ПO УМВС
Заповніть таблицю за допомогою відповідних формул та побудуйте графік
рівня злочинності по областям.
Вправа 7. Найпростіші операції з базою даних.
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls, створену раніше.
• Створіть новий робочий лист (Вставка > Лист), двічі клацніть на його
ярличку і привласніть йому ім’я “Зведення про постачання”.
• Передбачається, що підприємство одержує п’ять видів матеріалів:
папір, фанеру, картон, поліетилен і тканина — від п’яти
постачальників, що знаходяться в Чернівцях, Києві, Запорожжі,
Черкасах і Дніпропетровську. Кожний з постачальників може
поставляти будь-який вид матеріалів. Постачання ведуться не частіше
214
разу на місяць, одиниця виміру — тонна.
• В осередки А1 -D1 введіть заголовки полів бази даних, відповідно:
“Місяць”, “Постачальник”, “Товар”,” Обсяг”.
• Введіть кілька записів, що мають описану вище структуру. Реальні
«обсяги постачання» значення не мають.
• Загальне сортування бази даних. Зробіть поточним будь-який осередок
бази даних і дайте команду Данные > Сортировка. Переконайтеся, що
при цьому виділяється вся (крім заголовків полів) база даних.
• У списку Сортировать по виберіть пункт “Місяць” і режим по
зростанню.
• У списку Потом по (вторинне сортування) виберіть пункт
“Постачальник” і режим по зростанню.
• У списку В последнюю очередь, по виберіть пункт “Товар” і режим по
зростанню. Клацніть на кнопці ОК.
• Переконайтеся, що база даних відсортована за зазначеними критеріями.
• Послідовне сортування бази даних. За допомогою кнопки Отменить на
панелі інструментів відновіть колишній порядок записів бази даних.
Того ж порядку сортування можна домогтися іншим способом.
• Виберіть будь-який осередок у стовпці “Товар” і клацніть на кнопці
Сортировка по возрастанию на панелі інструментів.
• Виберіть будь-який осередок у стовпці “Постачальник” і клацніть на
кнопці Сортировка по возрастанию на панелі інструментів.
• Виберіть будь-який осередок у стовпці “Місяць” і клацніть на кнопці
Сортировка по возрастанию на панелі інструментів. Переконайтеся,
що підсумковий порядок сортування той же, що й у попередньому
випадку. Зверніть увагу, що в цьому випадку ми спочатку провели
третинне сортування, потім вторинне і на останньому етапі первинне.
• Фільтрація даних. Щоб включити режим фільтрації, дайте команду
Данные > Фильтр > Автофильтр. Зверніть увагу на появу
215
розкриваючих кнопок у заголовків полів бази даних.
• Щоб відібрати тільки записи, що описують постачання з
Дніпропетровська, клацніть на розкриваючій кнопці у полі
“Постачальник” і виберіть у списку пункт “Дніпропетровськ”. Зверніть
увагу на те, що розкриваюча кнопка діючого фільтра і номера
відібраних рядків відображаються синім кольором. Щоб скасувати
поточний фільтр, ще раз клацніть на розкриваючій стрілці і виберіть
пункт Все.
• Щоб відібрати найбільш великі разові постачання, клацніть на
розкриваючій стрілці у полі “Обсяг” і виберіть у списку варіант
Первые 10.
• Виберіть за допомогою лічильника число 20 і далі пункт наибольших і
варіант % от количества элементов. Клацніть на кнопці ОК. У
результаті буде відібрано 20% записів, що містять найбільші значення
обсягу постачань.
• Щоб скасувати режим фільтрації записів, ще раз дайте команду
Данные > Фильтр > Автофильтр.
Ми навчилися використовувати електронну таблицю Excel як базу
даних. Ми навчилися виконувати операції сортування і фільтрації по якій-
небудь ознаці. При цьому можливий добір як усіх записів, що відповідають
визначеній умові, так і частки (визначеної кількості) записів
зі значеннями,
екстремальними за визначеним критерієм.
Самостійна робота. Використовуючи наступні дані крадіжок та
угонів транспортних засобів, побудуйте таблицю з сортуванням, як по
кожному показнику, так і по сумі.
Таблиця 5.6. Крадiжки транспортних засобiв.
216
№ HAЙMEHУBAHHЯ
ОБЛАСТЕЙ
ЗАРЕEСТРОВАНО
КРАДIЖОК
ТРАНСПОРТНИХ
ЗАСОБIВ
ЗАЛИШОК
НЕРОЗКРИТИХ
1999 2000 1999 2000
1 PECПУБЛIKA KPИM 175 100 15 20
2 ДHIПPOПETPOBCЬKA 356 229 113 85
3 ДOHEЦЬKA 173 116 66 52
4 ЗAПOPIЗЬKA 201 163 39 37
5 KIPOBOГPAДCЬKA 88 60 7 16
Таблиця 5.7. Угони транспортних засобiв
№ HAЙMEHУBAHHЯ
ОБЛАСТЕЙ
ЗАРЕEСТРОВАНО
КРАДIЖОК
ТРАНСПОРТНИХ
ЗАСОБIВ
ЗАЛИШОК
НЕРОЗКРИТИХ
1999 2000 1999 2000
1 PECПУБЛIKA KPИM 33 34 1 2
2 ДHIПPOПETPOBCЬKA 108 79 30 11
3 ДOHEЦЬKA 67 40 20 9
4 ЗAПOPIЗЬKA 69 38 5 7
5 KIPOBOГPAДCЬKA 21 13 0 2
Вправа 8. Побудова зведеної таблиці
• Запустіть програму Excel і відкрийте робочу книгу book.xls, створену
раніше.
• Відкрийте робочий лист “Зведення про постачання”.
• Зробіть поточним осередок у межах бази даних. Дайте команду
Данные > Сводная таблица.
• Переконайтеся, що встановлено перемикачі в списку чи в базі даних
Microsoft Excel і зведена таблиця. Клацніть на кнопці Далее.
• Переконайтеся, що діапазон бази даних обраний правильно. Клацніть
на кнопці Далее.
• Переконайтеся, що встановлено перемикач Новый лист. Клацніть на
кнопці Макет.
• Перетягніть кнопки у відповідні області макета зведеної таблиці:
кнопку “Місяць” — в область “Сторінка”, кнопку “Постачальник” — в
217
область “Стовпець”, кнопку “Товар” — в область “Рядок”, кнопку
“Обсяг” — в область “Дані”.
• Кнопка в області “Дані” буде мати вид Сумма по полю Объем. Нас це
влаштовує. Клацніть на кнопці ОК.
• Клацніть на кнопці Параметры. У поле “Ім’я” введіть текст “Зведена
таблиця постачань”. Клацніть на кнопці ОК. Клацніть на кнопці
Готово.
• Перейменуйте робочий лист зі зведеною таблицею, двічі клацнувши на
його корінці. Дайте йому ім’я “Зведена таблиця постачань”.
• Подивіться на готову зведену таблицю. Вона показує, скільки
матеріалів визначеного типу прийшло від конкретного постачальника,
незалежно від часу постачання. Двічі клацніть на кожнім з осередків
зведеної таблиці, щоб побачити на новому робочому листі записи, на
основі яких сформовані дані в цьому осередку.
• Кнопки, що розкриваються поруч з іменами полів таблиці, дозволяють
виконати сортування по відповідному полю. Виберіть конкретний
місяць у розкритому списку “Місяць”, щоб побачити дані, що
відносяться до цього місяця.
• Перетягніть кнопку “Місяць” в область “Стовпець”, а кнопку “Товар”
— в область “Сторінка”. Зведена таблиця автоматично перешикується
відповідно до нової структури.
• Двічі клацніть на кнопці Сумма по полю Объем. У діалоговому вікні,
що відкрилося, Вычисление поля зведеної таблиці клацніть на кнопці
Дополнительно. У розкритому списку, Дополнительные
вычисления виберіть пункт Доля от суммы по строке. Клацніть на
кнопці ОК.
• Подивіться на новий вигляд зведеної таблиці. Серед іншого, ми
визначили, яку частку в загальному потоці постачань має кожний з
постачальників.
218
• Збережіть робочу книгу book.xls.
Ми навчилися створювати зведені таблиці для одержання
підсумкових зведень на основі вмісту бази даних. Ми довідалися, як
з’ясувати, виходячи з яких даних отриманий елемент зведеної таблиці. Ми
також навчилися фільтрувати дані зведеної таблиці, динамічно
перебудовувати зведену таблицю, а також змінювати спосіб відображення
області даних.
Самостійна робота.
Використовуючи попередні дані, побудуйте
зведену таблицю.
Вправа 9. Побудова зведеної діаграми
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls, створену раніше.
• Відкрийте робочий лист “Зведена таблиця постачань”.
• Клацніть на кнопці Мастер диаграмм на панелі інструментів Сводная
таблица. Зведена діаграма будується автоматично на новому робочому
листі.
• Перейменуйте цей робочий лист, давши йому ім’я “Зведена діаграма”.
• Проекспериментуйте зі зведеною діаграмою, щоб з’ясувати, які її
параметри можна змінювати.
• Змініть величину, відображувану на діаграмі.
• Перемістіть поле бази даних в іншу область діаграми.
• Змініть тип діаграми.
• Змініть формат відображення елементів діаграми.
Ми навчилися створювати зведені діаграми на основі зведених
таблиць. При прямому створенні зведеної діаграми відповідна зведена
таблиця створюється в будь-якому випадку. Ми з’ясували, що при
219
використанні зведеної діаграми нам доступні як операції, що відповідають
зведеній таблиці, так і ті, котрі застосовують при побудові діаграм. Іншими
словами, зведені діаграми поєднують засоби аналізу і засоби графічного
представлення інформації, що мається в базі даних.
Самостійна робота. Використовуючи попередні дані, побудуйте
зведену діаграму.
Вправа 10. Настроювання режиму перевірки даних, що вводяться
.
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls, створену раніше.
• Відкрийте робочий лист “Зведення про постачання”.
• Передбачається, що в нас мається фіксований список постачальників і
товарів, так що ручне введення цих величин необов’язкове. Також
будемо вважати, що разове постачання будь-якого матеріалу не може
перевищувати 10 тонн.
• Для наступних експериментів внесіть довільні перекручування в базу
даних: задайте в одному-двох записах невірне ім’я постачальника, у
одному-двох записах використовуйте невірне найменування матеріалу,
в одному-двох записах укажіть завищений обсяг постачання (більш 10
тонн).
• За межами бази даних, наприклад у стовпці F, в осередках F2-F6
вкажіть імена постачальників по одному в осередку.
• Аналогічним образом в осередках G2-G6 укажіть правильні
найменування товарів.
• Виділіть всі осередки бази даних у стовпці В (“Постачальник”), крім
заголовка стовпця. Дайте команду Данные > Проверка.
• У розкритому списку Тип данных виберіть варіант Список.
Переключіться на поле Источник і введіть (чи виберіть) діапазон
F2:F6. Якщо діапазон обраний, посилання автоматично береться як
220
абсолютне. Після цього клацніть на кнопці ОК.
• Аналогічним образом виберіть припустимі значення для полі “Товар”.
У полі Источник необхідно вказати діапазон G2:G6.
• Виберіть всі осередки в стовпці D. Дайте команду Данные >
Проверка. У полі Тип данных виберіть варіант Действительное. У
полях Минимум і Максимум укажіть, відповідно, значення 0 і 10.
Клацніть на кнопці ОК.
• Зробіть поточним будь-який осередок у стовпці В. Переконайтеся, що
ввести в неї довільне значення, відсутнє в списку, тепер неможливо.
Клацніть на розкриваючій кнопці щоб вибрати припустиме значення зі
списку.
• Аналогічним образом, спробуйте ввести неприпустиме значення
(текстове чи виходяче за межі заданого інтервалу) в осередок стовпця
D. Переконайтеся, що це також неможливо.
• Тому що дані вводилися в базу до включення перевірки, вони можуть
містити помилки. Дайте команду Сервис > Зависимости > Панель
зависимостей.
• Клацніть на кнопці Обвести неверные данные. Переконайтеся, що
осередки з помилками (створені в п. 4) виявлені й обведені червоним
кольором.
• Виправте помилки і ще раз клацніть на кнопці Обвести неверные
данные. Переконайтеся, що позначки зникли.
• Збережіть робочу книгу book.xls.
Ми навчилися використовувати засоби автоматичної перевірки даних,
що вводяться. Ми довідалися, що при цьому гарантується відповідність
уведених даних визначеній умові. Ми також познайомилися з новим
способом введення даних при наявності обмежень. Ми навчилися знаходити
помилки в тому випадку, якщо умови накладено на дані вже після того, як
дані
були введені.