Теоретичні відомості

Системний аналіз і управління

Методичні вказівки та завдання до лабораторних робіт з курсу “Основне програмне забезпечення ”за темою “ Робота з електронними таблицями Excel ” для студентів денної форми навчання спеціально­сті 7.080203 Системний аналіз і управління / Укл. Денисенко О.І.,
Корніч Г.В., Кузіна В.М.– Запоріжжя: ЗНТУ, 2004.– 62с.

Містить індивідуальні завдання, теоретичні відомості та при­клади для виконання лабораторних робіт з курсу “ Основне програмне забезпечення” для студентів денної форми навчання спеціальності 7.080203 Системний аналіз і управління.

Укладачі: Денисенко О.І., доцент

Корніч Г.В., доктор фіз.-мат. наук, доцент

Кузіна В.М., ст. викладач

Рецензент: Біла Н.І., доцент

Відповідальний за випуск Корніч Г.В., доцент

Затверджено Затверджено

Вченою Радою факультету на засіданні кафедри

Інформатики та обчислювальної Обчислювальної математики

техніки

Протокол № 3 від 30.11.04 Протокол № 3 від 25.11.04

Зміст

Лабораторна робота № 1................................................................... 4

1.1 Теоретичні відомості................................................................ 4

1.2 Розрахунки в таблицях............................................................. 5

1.3 Контрольні питання.................................................................. 9

1.4 Індивідуальні завдання............................................................. 9

Лабораторна робота № 2................................................................. 16

2.1 Вимоги до списків................................................................... 16

2.2 Сортування даних................................................................... 17

2.3 Формування підсумків в списках........................................... 19

2.4 Робота з функціями бази даних.............................................. 21

2.5 Контрольні питання................................................................ 25

2.6 Індивідуальні завдання........................................................... 25

Лабораторна робота № 3................................................................. 29

3.1 Використання Автофільтру.................................................... 29

3.2 Розширений фільтр................................................................. 31

3.3 Контрольні питання................................................................ 36

3.4 Індивідуальні завдання........................................................... 36

Лабораторна робота № 4................................................................. 39

4.1 Створення діаграми................................................................ 39

4.2 Коригування діаграми............................................................ 41

4.3 Побудова лінії тренду............................................................ 42

4.4 Побудова графіків функцій.................................................... 42

4.3 Контрольні питання................................................................ 45

4.4 Індивідуальні завдання........................................................... 45

Лабораторна робота №5.................................................................. 49

5.1 Створення зведених таблиць.................................................. 49

5.2 Контрольні питання................................................................ 53

5.3 Індивідуальні завдання........................................................... 53

Лабораторна робота № 6................................................................. 55

6.1 Консолідація даних................................................................. 55

6.2 Індивідуальні завдання........................................................... 58

Лабораторна робота № 7................................................................. 59

7.1 Функції для роботи з масивами............................................. 59

7.2 Індивідуальні завдання........................................................... 61

Література........................................................................................ 62

Лабораторна робота № 1

Тема роботи: Створення таблиць та розрахунки в Excel.

Мета роботи: навчитися створювати таблиці даних в Excel, коригувати їх та виконувати обчислення, виконувати форматування таблиць і редагування даних.

Теоретичні відомості

Діапазон клітинок – це сусідні клітинки, що утворюють прямо­кутник. Діапазон клітинок може складатися з однієї клітинки, кліти­нок одного стовпчика, одного рядка або з комбінації клітинок, розта­шованих поруч у різних стовпчиках і рядках

До клітинок можна вводити дані декількох типів: числа, дати, час, формули, та ін. Після введення дані можна виправити у самій клі­тинці, двічі кликнувши на ній лівою кнопкою мишки, або у рядку формул. Після закінчення редагування можна натиснути клавішу Enter або клікнути мишкою на зеленій позначці в рядку формул.

При використанні функцій та формул вираз треба починати зі знака рівності =. Функції можна вставляти в клітинку за допомогою Майстра функцій або власноруч. Адреси клітинок та діапазонів зруч­ніше задавати за допомогою мишки, але можна й набирати їх.

Адреса клітинки може бути відносною, абсолютною (з двома символами $, наприклад $F$5) або змішаною (з одним знаком $). При копіюванні відносні адреси автоматично змінюються на нові значення в залежності від відстані, на яку здійснюється копіювання. Абсолютні адреси – не змінюються.

Копіювання даних можна здійснювати засобами Windows за допомогою кнопок Копировать і Вставить панелі інструментів або контекстного меню. Щоб скопіювати введені дані на сусідні клітинки, найпростіше встановити покажчик миші в правому нижньому куточку діапазону клітинок так, щоб курсор прийняв вигляд чорного хрестика, та перетягнути курсор на необхідні клітинки.

Якщо виділити стовпчик та вибрати в меню пункт Добавить ячейки…, то буде вставленийвільний стовпчик перед виділеним. Якщо виділити рядок та вибрати в меню пункт Добавить ячейки…, то буде вставленийвільний рядок над виділеним.

Якщо виділена одна клітинка або діапазон клітинок, то вибір пункту меню Добавить ячейки… відкриває вікно Добавить, в якому треба вибрати один з перемикачів: Ячейки, со сдвигом влево; Ячейки, со сдвигом вверх; Строку або Столбец

Аналогічно виконується видалення діапазону клітинок.

1.2 Розрахунки в таблицях

Розглянемо роботу з таблицями в Excel на прикладі.

Завдання. В трьох бригадах працюють робітники четвертого, п’ятого та шостого розрядів. Оплата праці здійснюється за такими правилами. Для кожного розряду діє своя норма оплати в день. Щомі­сяця встановлюється нормативна кількість робочих днів. Якщо робіт­ник працює понаднормово, то він отримує подвійну оплату за додат­кові дні. Деякі робітники можуть працювати не повний місяць. Всі ро­бітники сплачують податок у розмірі 13% від заробітку.

Розміри тарифів записати до окремих клітинок. Номер бригади вибирати зі списку, створеного в меню Данные – Проверка. Встано­вити контроль введених даних для поля розряд робітника.

Скласти таблицю розрахунку заробітної плати, яка містить такі дані: номер бригади; прізвище робітника; розряд; кількість відпрацьо­ваних днів; заробіток за 1 день; всього нараховано; податок; одержати.

Розв’язок завдання. На Листі Excel запишемо нормативну кіль­кість робочих днів, відсоток податку та норми заробітків за 1 день в залежності від розряду (див. рис. 1.1).

Далі створимо заголовки стовпців. Для того, щоб вони мали вигляд як на рис. 1.1, треба ввести текст, потім виділити всі клітинки і правою кнопкою мишки в меню обрати пункт Формат ячеек. На вкладці Выравнивание треба встановити прапорець Переносить по словам, вибрати значення “ по центру” для вирівнювання по вертикалі та горизонталі і задати кут в 90⁰ для параметру Ориентация.

Створимо список для введення номеру бригади. Для цього виді­лимо діапазон А8: А22, в меню Данные виберемо пункт Проверка. В полі Тип данных виберемо Список, а в полі Источник запишемо через крапку з комою елементи списку №1; №2; №3. Тепер під час вводу номеру бригади Excel буде пропонувати вибрати його зі списку.

Можна в полі Источник вікна Проверка вказати діапазон, в якому записаний список. Але якщо діапазон розташований не на тому Листі, куди треба заносити дані, то діапазону треба надати ім’я.

Виділимо діапазон С8: С22 і для поля розряд встановимо конт­роль вводу. В меню Данные знову виберемо пункт Проверка. У вікні, що відкриється, встановимо такі параметри. Тип данных: Целое число, Значение: между, Минимум:4, Максимум: 6. На вкладці Сообщение для ввода в полі Сообщение запишемо підказку: 4, 5 або 6 розряд.

Прізвища та кількість відпрацьованих днів заповнимо даними як на рис. 1.1. В усіх інших стовпчиках запишемо формули для першого робітника (тобто в 8 рядку) і скопіюємо ці формули на всі рядки.

Рисунок 1.1 – Лист Excel з даними

Формула для визначення зарплати за 1 день для конкретного робітника =ЕСЛИ(C8=$F$4;$F$5;ЕСЛИ(C8=$G$4;$G$5;$H$5))

=ЕСЛИ(C8=$F$4;$F$5;ЕС в залежності від його розряду (клітинка Е8):

ЛИ(C8=$G$4;$G$5;$H$5))

Зверніть увагу на те, що вибір однієї з трьох розцінок визнача­ється за допомогою двох функцій ЕСЛИ, а для посилання на розряди та розцінки використовуються абсолютні адреси.

Формула для підрахунку зарплати з урахуванням праці в понад­нормові робочі дні (клітинка F8, поле Всього нараховано):

=ЕСЛИ(D8>$C$3;$C$3*E8+(D8 – $C$3)*E8*2;D8*E8)

: = F8 – G8

=ЕСЛИ(D8>$C$3;$C$3*E8+(D8 – $C$3)*E8*2;D8*E8)

Формула для розрахунку податку: = F8*$C$4

Формула для поля Одержати: = F8 – G8

Щоб знайти загальну нараховану суму, введемо до клітинки F23 формулу: = СУММ(F8:F22) та скопіюємо її на діапазон G23: H23.

Для підрахунку середньої отриманої суми до клітинки H24 введемо формулу: = СРЗНАЧ (H8:H22).

Встановимо порядок відображення даних в полях Податок та Одержати. Для цього виділімо мишкою діапазон клітин G8: Н24 та виберемо в меню пункт Формат ячеек. У вікні, що відкриється, на вкладці Число, встановимо Числовой формат та Число десятинных знаков = 2. Таблиця прийме такий вигляд як на рис. 1.1.

Змінимо ім’я Лист1 на ім’я Січень. Для цього треба клікнути двічі на старому імені Листа та ввести замість нього нове.

Надамо імена клітинкам. На Листі Січень присвоїмо клітинці С3, де записана кількість робочих днів у місяці, ім’я “ кількість ”. Для цього зробимо цю клітинку активною, в полі Имя (див рис. 1.2) за­мість адреси С3 запишемо ім’я – кількість і натиснемо клавішу Enter.

Поле Имя

↓

Рисунок 1.2 – Надання імен клітинкам

Надамо таким же чином імена всім клітинкам з тарифами згідно таблиці 1.1. Пробіли в іменах використовувати не можна!

Таблиця 1.1 – Імена клітинок

Адреса клітинки	Ім’я
С3	кількість
С4	податок
F4	розряд4
G4	розряд5
H4	розряд6
F5	тариф4
G5	тариф5
H5	тариф6

Тепер змінимо в формулах адреси клітинок з тарифами посилан­нями на їх імена. Для цього в меню Вставка виберемо пункт Имя, а потімкоманду Применить. Відкриється вікно (див. рис. 1.3).

Рисунок 1.3 – Використання імен

Позначимо всі імена в списку і натиснемо на кнопку ОК. Тепер формули стануть більш наглядними. Наприклад, формули в клітинках Е8 та F8 зміняться на такі:

=ЕСЛИ(C8=розряд4;тариф4;ЕСЛИ(C8=розряд5;тариф5;тариф6)) =ЕСЛИ(D4>кількість;кількість*E4+(D4-кількість)*E4*2;D4*E4)

Виділімо окремий Лист для запису тарифів. Надамо Листу 2 ім’я Довідки. Якщо просто записати тарифи на Листі Довідки, то формули на інших Листах стануть дуже великими, бо до адрес тарифів треба буде дописувати спочатку ім’я Листа та відокремлювати його знаком оклику, тобто замість $F$3 використовувати Довідки!$F$3 і т.д. Якщо вирізати рядки з третього по п’ятий на Листі Січень та вставити їх на лист Довідки, то формули на Листі Січень не зміняться, а всі дані під­німуться на 3 рядки вгору.

1.3 Контрольні питання

1. Які види даних можна заносити до ЕТ Excel?

2. Які можливості надає використання пункту меню „Формат ячеек“?

3. Як надати імена клітинам та як їх потім використовувати?

4. Які існують способи копіювання даних в Excel?

5. Що таке абсолютна адреса і відносна адреса? Що відбувається з адресами при копіюванні та переміщенні?

6. Як надати ім’я Листу? Як використовувати це ім’я в формулах?

7. Які групи функцій існують в Excel? Використання функцій ЕСЛИ, СУММ, МАКС, МИН, СРЗНАЧ.