Вишня В.Б., Ілюхін С.М., Кравець В.П., Шумейко О.О. Основи інформатики та обчислювальної техніки (навчально-методичний посібник для курсантів, студентів та слухачів)
Подождите немного. Документ загружается.
201
##### (символи
заповнюють
осередок цілком)
Числові дані не вміщаються в осередок по ширині чи при
обчисленні по формулі з дати віднімається більш пізня.
#ДЕЛ/0!
У знаменнику формули виходить нульове значення чи
стоїть посилання на порожній осередок. Останнє може
виявитися пов’язаним з невірним використанням
відносного посилання.
#ЗНАЧ!
У функції пропущений обов’язковий параметр, або
параметр використовує посилання на порожній осередок,
або на осередок з даними, тип яких не відповідає типу
пот
р
ібного па
р
амет
р
а.
#ИМЯ?
Помилка в імені чи функції імені осередку (діапазону).
Може бути викликана пропуском двокрапки при вказівці
діапазону чи лапок при вказівці текстового параметра.
#ПУСТО!
У формулі використане перетинання діапазонів, що не
включає ні одного осередку.
#ЧИСЛО!
При обчисленні вийшло число, занадто велике, чи занадто
маленьке для програми Excel. Як правило, це стається
внаслідок неправильності формули.
#ССЫЛКА!
Формула вказує на осередок, відсутній на робочому листі.
Найчастіше така помилка виникає внаслідок копіювання
формули з відносною адресацією вліво чи нагору, через
що виникають посилання на стовпці, що передують
стовпцю А, і рядка, що передуює першому.
202
Вправа 1. Застосування засобів автоматизації введення.
• Запустіть програму Excel (Пуск > Програми > Microsoft Excel).
• Створіть (Файл > Создать) нову робочу книгу. Збережіть її (Файл >
Сохранить как) під ім’ям book.xls.
• Виберіть щигликом на ярлику не використовуваний робочий лист,
чи створіть новий (Вставка > Лист). Двічі клацніть на ярлику нового
листа і перейменуйте його як Додаткові витрати по місяцях.
• Зробіть поточним осередок А1 і введіть текст: “Місяці”.
• Зробіть поточним осередок B1 і введіть текст: “Витрати”.
• Зробіть поточним осередок А2. Введіть текст: “Январь 2002”.
Натисніть клавішу Enter. Переконайтеся, що текст був автоматично
розпізнаний як дата (змінився формат, а дані вирівнялися по правому
краю осередка). Натисніть клавішу Вверх. При бажанні, дайте
команду Формат > Ячейка і вкажіть інший формат запису дати.
• Поставте покажчик миші на маркер заповнення в правому нижньому
куті рамки поточного осередка. Натисніть праву кнопку миші і
простягніть рамку так, щоб вона охопила всі осередки від А2 до А25.
• Відпустіть кнопку миші. В меню виберіть пункт Заполнить по
місяцях. Переконаєтеся, що в стовпці А з’явилися позначення для
всіх місяців, по грудень 2002 року включно.
• У даному прикладі будемо вважати, що в перший місяць витрати
складали 10 гривень, а в кожному наступному зростали на 10%.
Зробіть поточним осередок В2. Введіть число 10. Натисніть клавішу
Enter.
203
•
Клацніть на осередку В2 правою кнопкою миші і виберіть у
контекстному меню пункт Формат ячеек. На вкладці Число
виберіть варіант Денежный і клацніть на кнопці ОК. Переконайтесь,
що число тепер записане як грошова сума.
• Клацніть на осередку В3 і наберіть текст =B2*1,1.
• Клацніть правою кнопкою миші на маркері заповнення і простягніть
рамку так, щоб вона охопила осередки з В3 по В25. Відпустите
кнопку миші.
• В осередок С1 введіть текст: “Наростаючий підсумок”.
• Зробіть поточним осередок С2. Введіть текст =В2. Це формула, що
означає, що значення осередку С2 дорівнює значенню осередка В2.
Ця проста формула гарантує, що якщо значення в осередку В2 буде
змінено, то осередок С2 усе рівно буде містити вірні дані.
• Клацніть на осередку С3. Клацніть на значку Змінити формулу в
рядку формул. Клацніть на осередку В3. Переконайтеся, що
посилання на цей осередок поміщено в рядок формул. Натисніть
клавішу +. Клацніть на осередку С2. Натисніть клавішу Enter.
• Знову зробіть осередок С3 поточним. Наведіть покажчик миші на
маркер заповнення, натисніть ліву кнопку і простягніть рамку так,
щоб вона охоплювала осередки з С3 по C25.
• Клацніть на одному з осередків стовпця С и подивіться, яка формула
в ній записана. Переконайтеся, що усі формули були скоректовані за
принципом відносної адресації.
• Збережіть робочу книгу book.xls.
204
Ми познайомилися з засобами автоматичного введення даних. Ми
навчилися робити заповнення послідовністю чисел (дат), прогресією,
формулами. Ми довідалися, як при заповненні формулами враховується
відносна адресація.
Самостійна робота. Наберіть таблицю:
Таблиця 5.3. Динаміка загальної злочинності
Зареєстровано
злочинів у
звітному
місяці
Динаміка
Зареєстровано
злочинів з
початку року
(зростаючим
підсумком)
Динаміка
1999 2000 АБС. % 1999 2000 АБС. %
Січень 42852 41395
Лютий 51610 50957
Березень 56848 55262
Квітень 50229 48290
Травень 48382 45933
Червень 51519 48896
Липень 49114 45296
Серпень 46298 44952
Вересень 48526 48319
Жовтень 48100 44149
Листопад 47504 41917
Грудень 56654 57185
Введіть відповідні формули і за допомогою автозаповнення облічить
осередки 4, 6, 7 та 8 стовпців.
Вправа 2. Застосування підсумкових функцій.
• Запустіть програму Excel та відкрийте робочу книгу book.xls.
205
• Виберіть робочий лист Додаткові витрати по місяцях, створений в
попередній вправі.
• Зробіть поточним перший вільний осередок у стовпці В(В26).
• Клацніть на кнопці Автосумма на стандартній панелі інструментів.
• Переконаєтеся, що програма автоматично підставила у формулу
функцію СУМ і правильно вибрала діапазон осередків для
підсумовування. Натисніть клавішу Enter.
• Перевірте правильність обчислень, порівнявши значення в осередках
В26 і С25.
• Зробіть поточним наступний вільний осередок у стовпці В.
• Клацніть на кнопці Вставка функции на стандартній панелі
інструментів.
• У списку Категория виберіть пункт Статистические.
• У списку Функция виберіть функцію СРЗНАЧ і клацніть на кнопці
ОК.
• Перемістіть методом перетаскування палітру формул, якщо вона
закриває потрібні осередки. Зверніть увагу, що автоматично обраний
діапазон включає всі осередки з числовим вмістом, включаючи і ту,
котра містить суму даних. Виділіть правильний діапазон методом
протаскування і натисніть клавішу Enter.
• Використовуючи порядок дій, описаний у пп. 8-12, обчислите
мінімальне число в заданому наборі (функція МИН), максимальне
число (МАКС), кількість елементів у наборі (СЧЕТ).
• Збережіть робочу книгу book.xls.
Ми познайомилися з деякими підсумковими функціями. Ми
навчилися використовувати підсумкові функції для обчислення значень, що
характеризують набір даних. Ми з’ясували, як автоматично визначається
діапазон значень, оброблюваних функцією, і як змінити його вручну.
206
Самостійна робота. В таблиці 5.3. “Динаміка загальної злочинності”
обчисліть осередки 5-го та 9-го стовпців, тобто відсотки з точністю до
десятих. Знайдіть суму по другому та третьому стовпцю та їх середнє
значення.
Вправа 3. Підготовка і форматування прейскуранта
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls.
• Виберіть щигликом на ярличку не використовуваний робочий лист, чи
створіть новий (Вставка > Лист). Двічі клацніть на ярличку нового
листа і перейменуйте його як “Прейскурант”.
• В осередок А1 введіть текст “Прейскурант” і натисніть клавішу Enter.
• В осередок А2 введіть текст “Курс перерахування:” і натисніть клавішу
Enter. В осередок В2 введіть текст 1 у.о.= і натисніть клавішу Enter. В
осередок С2 введіть поточний курс перерахування і натисніть клавішу
Enter.
• В осередок A3 введіть текст “Найменування товару” і натисніть
клавішу Enter. В осередок ВЗ введіть текст “Ціна (у.о.)” і натисніть
клавішу Enter. В осередок СЗ введіть текст “Ціна (грн.)” і натисніть
клавішу Enter.
• У наступні осередки стовпця А введіть назви товарів, включених у
прейскурант.
• В осередки стовпця В введіть ціни товарів в умовних одиницях.
• В осередок С4 введіть формулу: =В4*$С$2, що використовується для
перерахування ціни з умовних одиниць у гривні.
• Методом автозаповнення скопіюйте формули в усі осередки стовпця С,
яким відповідають заповнені осередки стовпців А и В. Чому при такім
копіюванні вийдуть вірні формули?
• Замініть курс перерахування в осередку С2. Зверніть увагу, що всі ціни
207
в гривнях при цьому оновлюються автоматично.
• Виділіть методом протягання діапазон А1: С1 і дайте команду Формат
> Ячейка. На вкладці Выравнивание задайте вирівнювання по
горизонталі По центру й поставте прапорець Объединение ячеек.
• На вкладці Шрифт задайте розмір шрифту в 14 пунктів і в списку
Начертание виберіть варіант Полужирный. Клацніть на кнопці ОК.
• Клацніть правою кнопкою миші на осередку В2 і виберіть у
контекстному меню команду Формат ячеек. Задайте вирівнювання по
горизонталі По правому краю і клацніть на кнопці ОК.
• Клацніть правою кнопкою миші на осередку С2 і виберіть у
контекстному меню команду Формат ячеек. Задайте вирівнювання по
горизонталі По лівому краю і клацніть на кнопці ОК.
• Виділіть методом протягування діапазон В2:С2. Клацніть на кнопці, що
розкривається, поруч із кнопкою Границы на панелі інструментів,
Форматирование і задайте для цих осередків широку зовнішню рамку
(кнопка в правому нижньому куті палітри, що відкрилася).
• Двічі клацніть на границі між заголовками стовпців А и В, В и С, С и
D. Зверніть увагу, як при цьому змінюється ширина стовпців А, В и С.
• Збережіть робочу книгу book.xls.
Ми навчилися форматувати документ Excel. При цьому ми
використовували такі засоби, як зміна ширини стовпців, об’єднання
осередків, керування вирівнюванням тексту, створення рамок осередків. Ми
з’ясували, що в готовому документі задані й обчислені осередки
відображаються однаково.
Самостійна робота. Наберіть таблицю:
Таблиця 5.4.
Всього Динаміка
Види злочинів
1999 2000 В абс. В %
Розбій 5173 5636
Тяжке тілесне ушкодження 7047 6852
З застосу- Вогнепальної зброї 29 33
208
ванням Бойових припасів та
вибухових речовин
1 3
Крадіжка державного або колективного
майна
82768 94608
Із складів, баз, магазинів
та інших торгових точок
14969 11337
З них
Вантажів на транспорті 1614 1890
Напишіть формули для заповнення осередків двох останніх стовпців,
та зробіть формат таблиці такий же, як наведено.
Вправа 4. Попередній перегляд і друк прейскуранта
• Запустіть програму Excel (Пуск > Программы> Microsoft Excel) і
відкрийте робочу книгу book.xls.
• Виберіть робочий лист Прейскурант, створений у попередній вправі.
• Переконайтеся, що зовнішній вигляд документа вас влаштовує, і
клацніть на кнопці Предварительный просмотр на стандартній панелі
інструментів, щоб побачити, як буде виглядати друкований документ.
• Клацніть на кнопці Масштаб, щоб побачити зображення сторінки в
натуральну величину.
• Клацніть на кнопці Поля, щоб визначити величину полів сторінки.
Змініть розміри полів шляхом перетаскування граничних маркерів.
• Клацніть на кнопці Страница, щоб вибрати параметри сторінки. У
діалоговому вікні Параметры страницы виберіть вкладку
Колонтитулы.
• У списку Нижний колонтитул виберіть варіант: Сторінка 1 з ?
• Клацніть на кнопці Создать верхний колонтитул. У діалоговому
вікні, що відкрилося, сформуйте верхній колонтитул з своїм
прізвищем.
• Змініть шрифт, скориставшись кнопкою Шрифт. Включіть в
колонтитул ім’я робочого листа, клацнувши на кнопці Имя листа.
Клацніть на кнопці ОК.
209
• Подивіться, як виглядає сторінка з колонтитулами.
• Клацніть на кнопці Разметка страницы, щоб повернутися до
звичайного режиму перегляду робочого листа, але з розбивкою на
сторінки.
• Скористайтеся командою Вставка > Разрыв страницы, щоб задати
примусовий поділ робочого листа на сторінки печаті.
• Ще раз скористайтеся кнопкою Предварительный просмотр, щоб
повернутися в режим попереднього перегляду.
• Клацніть на кнопці Печать, щоб роздрукувати робочий лист.
Ми познайомилися з використанням засобу попереднього перегляду і
зробили друк документа. Ми також навчилися формувати колонтитули і
керувати розбивкою документа на друковані сторінки.
Самостійна робота. В таблицях для самостійної роботи зробіть
колонтитули та зробіть розмітку для друку.
Вправа 5. Форматування відомості.
Робочий
лист повинен містити зведену відомість студентських оцінок
за підсумками сесії. Якщо іспити здані без трійок, то відповідний рядок
таблиці повинен мати зелений колір, якщо в студента залишилися
заборгованості — червоний.
• Запустіть програму Excel (Пуск > Программы > Microsoft Excel) і
відкрийте робочу книгу book.xls, створену раніше.
• Виберіть щигликом на ярлику не використовуваний робочий лист, чи
створіть новий (Вставка > Лист). Двічі клацніть на ярлику листа і
перейменуйте його як Відомість.
• У перший рядок робочого листа, починаючи з осередку В1, введіть
назви іспитів.
• У перший стовпець робочого листа, починаючи з осередку А2, введіть
210
прізвища студентів.
• Заповніть таблицю екзаменаційними оцінками за своїм розсудом.
Оцінки повинні змінюватися від 2 до 5 балів.
• Виділіть осередок А2 і дайте команду Формат > Условное
форматирование.
• У розкритому списку на панелі Условие 1 виберіть варіант Формула.
• У поле для формули введіть наступну формулу: =МИН($В2:$Е2)>3.
Зверніть увагу на спосіб використання абсолютних і відносних
посилань у формулі, тому що її планується поширити на всю таблицю.
Ця формула розрахована на чотири іспити, при іншому їхньому числі
обраний діапазон трохи зміниться.
• Клацніть на кнопці Формат. У діалоговому вікні, що відкрилося,
Формат ячеек, виберіть вкладку Вид, і клацніть на ясно-зеленому
кольорі для його використання в якості тіла осередків. Клацніть на
кнопці ОК.
• Клацніть на кнопці А также, щоб задати другу умову форматування.
Далі дійте аналогічно тому, як зазначено в п. 8. Потрібна формула
повинна мати вигляд: =МИН($В2:$Е2)<3.
• Подібно тому, як зазначено в п. 9, задайте ясно-рожевий колір для
осередків.
• Клацніть на кнопці ОК. Колір осередка А2 повинен змінитися, якщо
відповідний студент не має трійок, навпроти, має заборгованість.
• Виділіть весь діапазон осередків відомості і дайте команду Формат >
Условное форматирование. Діалогове вікно умовного форматування
повинне містити настроювання, підготовлені для осередку А2.
• Клацніть на кнопці ОК. Умовне форматування пошириться на усю
виділену область з автоматичною корекцією відносних посилань.
Переконайтеся, що формат відомості відповідає тому, що було
потрібно.