Перелік замовлених товарів
Лабораторна робота №1
Тема: Створення та редагування електронних таблиць. Налаштування середовища MS Excel.
Мета: Навчитися налагоджувати робоче середовище прикладної програми MS Excel; набути та закріпити навички створення, збереження, форматування та виведення на друк документів MS Excel, роботи з елементами робочої книги.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР1_Прізвище.xlsх.
3. Вилучити робочий аркуш Аркуш2 виконавши команду Видалити аркуш колекції Видалити групи Клітинки вкладки Основне.
4. Видалити робочий аркуш Аркуш3 виконавши команду Видалитиконтекстного меню ярлика вказаного робочого аркуша.
5. Перейменувати робочий аркуш Аркуш1 на Графік.
Вказівки до виконання:
Ÿ виконати команду Перейменувати контекстного меню ярлика аркуша.
6. Зняти відображення ярлика аркуша Графік
Вказівки до виконання:
Ÿ перейти на вкладку Файл;
Ÿ виконати команду Параметри;
Ÿ у діалоговому вікні Параметри Excel перейти до категорії Додатково
Ÿ у групі Параметри відображення цієї книги вимкнути прапорець Відображати вкладки аркуша.
7. Відмінити відображення горизонтальної та вертикальної смуг прокручування, для цього вимкнути відповідні прапорці у групі Параметри відображення цієї книги категорії Додатково вікна Параметри Excel.
8. На аркуші Графік створити таблицю "Графік прийому на роботу" наведену на рис 1.1.
Вказівки до виконання:
Ÿ виділити діапазон A1:I1;
Ÿ об’єднати комірки виділеного діапазону натиснувши кнопку Об’єднати та розташувати в центрі групи Вирівнювання вкладки Основне;
Ÿ ввести «Графік прийому на роботу»
Ÿ визначити такі параметри форматування: шрифт Arial, розмір – 12, напівжирний курсив, вирівнювання по горизонталі – по центру;
Ÿ виділити діапазон A1:I1;
Ÿ у комірки діапазону А2:І3 ввести наведені значення;
Ÿ видалити діапазон А2:І3, до виділених комірокі застосовувати такі параметри форматування: шрифт Times New Roman, розмір – 12, напівжирний курсив, вирівнювання по горизонталі та по вертикалі – по центру; у певних помірках змінити орієнтацію тексту;
Ÿ виділити діапазон А4:А13, визначити формат комірок виділеного діапазону – Числовий, без знаків після коми;
Ÿ виділити діапазон несуміжних комірок В4:Е13;G4:G13, визначити формат комірок виділеного діапазону – Текстовий;
Ÿ виділити діапазон F4:F13, визначити формат комірок виділеного діапазону – Дата, вибрати відповідний формат;
Ÿ виділити діапазон H4:H13, визначити формат комірок виділеного діапазону – Грошовий, два знаки після коми, вибрати відповідну грошову одиницю;
Ÿ виділити діапазон І4:І13, визначити формат комірок виділеного діапазону – Відсотковий.
Рис. 1.1. Таблиця "Графік прийому на роботу"
9. Відновити відображення ярликів аркушів та смуг прокручування.
Вказівки до виконання:
Ÿ у діалоговому вікні Параметри Excel у групі Параметри відображення цієї книги встановити відповідні прапорці.
10. Додати до робочої книги новий робочий аркуш.
Вказівки до виконання:
Ÿ натиснути кнопку Вставити аркуш, що знаходиться у лівому нижньому кутку поруч з ярликами аркушів робочої книги або натиснути комбінацію клавіш Shift+F11.
11. Перейменувати новий робочий аркуш назвавши його Вартість.
Вказівки до виконання:
Ÿ виконати команду Перейменувати аркуш категорії Упорядкувати аркуші колекції Формат групи Клітинки вкладки Основне.
12. Перемістити робочий аркуш Вартість після робочого аркуша Графік, якщо це необхідно.
13. Змінити колір ярлика аркуша Графік на жовтий, колір ярлика Вартість на зелений.
Вказівки до виконання:
Ÿ виконати команду Колір вкладки контекстного меню ярлика аркушів або команду Колір вкладки категорії Упорядкувати аркуші колекції Формат групи Клітинки вкладки Основне.
14. На аркуші Вартість створити таблицю "Розрахунок вартості товарів" наведену на рис 1.2.
Рис. 1.2. Таблиця "Розрахунок вартості товару"
15. Застосувати до даних таблиці такі формати:
15.1. Назва таблиці – шрифт Arial, 12, напівжирний курсив, вирівнювання по горизонталі та по вертикалі – по центру;
15.2. Заголовок таблиці – шрифт Times New Roman, 12, напівжирний курсив, вирівнювання по горизонталі та по вертикалі – по центру; у відповідних помірках змінити орієнтацію тексту.
16. Роздрукувати створені таблиці.
Вказівки до виконання:
Ÿ перейти на вкладку Файл;
Ÿ в області Backstage категорії Друк встановити потрібні параметри.
Лабораторна робота №2
Тема: Створення, редагування та форматування електронних таблиць
Мета: Закріпити навички налагоджування робочого середовища прикладної програми MS Excel; набути та закріпити навички створення, збереження, форматування та виведення на друк документів MS Excel, створення колонтитулів, роботи з елементами робочої книги
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ під іменем ЛР2_Прізвище.xlsx.
3. Вилучити робочі аркуші Лист2, Лист3.
Вказівки до виконання:
Ÿ виділити робочі аркуші Лист2 і Лист2 використовуючи для виділення клавішу Shift;
Ÿ виконати команду Видалити контекстного меню ярликів виділених робочих аркушів.
4. Назвати робочий аркуш Аркуш1 – Підсилювачі.
5. На робочому аркуші Підсилювачі створити таблицю "Будинкові підсилювачі серії SHA" наведену на рис. 2.1.
Вказівки до виконання:
Ÿ для вставлення символу „±” потрібно натиснути кнопку Символ групи Символи вкладки Вставлення та діалоговому вікні Символ на вкладці Символи вибрати потрібний знак;
Ÿ для коректності введення даних у комірки J14 і J15 перед знаком „+” необхідно ввести символ одинарна лапка «’».
6. У комірку В17 ввести значення курсу умовної грошової одиниці, який діє на поточний день. Визначити формат даних в комірці В17 як Грошовий, встановити – 3 знаки після коми.
Рис. 2.1. Таблиця "Будинкові підсилювачі серії SHA".
7. Додати до таблиці стовпці Ціна (грн.); Розд.; Опт, за зразком наведеним на рис. 2.2.
Рис. 2.2. Таблиця "Будинкові підсилювачі серії SHA" після вставки додаткових стовпців
8. У створених стовпцях обчислити оптову та роздрібну ціну наведеної продукції в гривнях, враховуючи курс умовної грошової одиниці.
9. Для друку створеної таблиці встановити орієнтацію сторінки – альбомна.
Вказівки до виконання:
Ÿ перейти на вкладку Розмітка сторінки;
Ÿ виконати Альбомна колекції Орієнтація групи Параметри сторінки;
10. Визначити поля сторінки таких розмірів:
- ліве і праве поле – 1 см;
- верхнє і нижнє поле – 1,5 см;
- поле верхнього і нижнього колонтитулів – 1 см.
Вказівки до виконання:
Ÿ виконати команду Настроювані поля… колекції Поля групи Параметри сторінки;
Ÿ у діалоговому вікні Параметри сторінки на вкладці Поля і встановити відповідні розміри полів.
Рис. 2.3. Діалогове вікно Параметри сторінки, вкладка Поля
11. Додати до робочого аркуша колонтитули, у яких відобразити таку інформацію:
- у нижньому колонтитулі, по центру – номер сторінки;
- у верхньому колонтитулі зліва – прізвище, ім’я виконавця, курс, номер групи; справа – номер і тема лабораторної роботи.
Вказівки до виконання:
Ÿ натиснути на кнопку Параметри сторінки 
групи Параметри сторінки вкладки Розмітка сторінки;
Ÿ перейти на вкладку Колонтитули;
Рис. 2.4. Діалогове вікно Параметри сторінки, вкладки Колонтитули
Ÿ для створення верхнього колонтитулу натиснути на кнопку Створити верхній колонтитул;
Ÿ у діалоговому вікні Верхній колонтитул (рис. 2.5.) додати необхідну інформацію у відповідні області.
Рис. 2.5. Діалогове вікно Верхній колонтитул
Ÿ зі списку поля Нижній колонтитул вибрати елемент Сторінка 1;
Ÿ закрити діалогове вікно Параметри сторінки;
Ÿ переглянути отриманий результат натиснувши кнопку Макет сторінки групи Режими перегляду книги вкладки Вигляд.
12. Роздрукувати створену таблицю.
Лабораторна робота №3
Тема: Здійснення обчислень на робочому аркуші з використанням формул MS Excel.
Мета: Закріпити навички використання умовних форматів; набути та закріпити навички створення, редагування та використання формул MS Excel.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР3_Прізвище.xlsх.
3. На робочому аркуші Лист1 створити таблицю "Розрахунок вартості установки вікон житлового будинку" наведену на рис. 3.1. Назвати аркуш, на якому створена таблиця, Вікна.
Рис. 3.1. Таблиця "Розрахунок вартості установки вікон житлового будинку"
4. На робочому аркуші Вікна здійснити необхідні обчислення, використовуючи можливість введення формул у комірки робочого аркуша.
5. У таблиці передбачити автоматичне виділення комірок з загальною вартістю:
– оранжевим кольором, якщо значення загальної вартості менше 500 у.о.;
– блакитним кольором, якщо значення загальної вартості знаходиться в межах 500 у. о. – 4000 у. о.;
– зеленим кольором, якщо значення загальної вартості більше 4000 у. о.
Вказівки до виконання:
Ÿ виділити діапазон С12:N12;
Ÿ виконати команду Менше… категорії Правила виділення комірок колекції Умовне форматування групи Стилі вкладки Основне;
Ÿ у діалоговому вікні Менше у лівому полі ввести число 500, у правому полі зі списком вибрати елемент Настроюваний формат…;
Ÿ у діалоговому вікні Формат клітинок перейти на вкладку Заливка та вказати потрібний колір;
Ÿ закрити діалогові вікна натискуючи почергово кнопки ОК;
Ÿ аналогічно створити інші умови форматування вибираючи відповідно команди Більше… та Між… колекції Умовне форматування.
6. На робочому аркуші Лист2 створити таблицю "Розрахунок вартості установки міжкімнатних дверей житлового будинку" наведену на рис. 3.2. Назвати аркуш, на якому створена таблиця Двері.
Рис. 3.2. Таблиця "Розрахунок вартості установки міжкімнатних дверей житлового будинку"
7. На робочому аркуші Двері здійснити необхідні обчислення, використовуючи можливість введення формул у комірки робочого аркуша.
8. Роздрукувати створені таблиці з результатами обчислень.
Лабораторна робота №4
Тема: Здійснення обчислень на робочому аркуші з використання формул MS Excel.
Мета: Закріпити навички використання умовних форматів; набути та закріпити навички створення, редагування та використання формул MS Excel.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР4_Прізвище.xlsх.
3. На робочому аркуші Аркуш1 створити таблицю "Розрахунок економічних характеристик громадського транспорту" наведену на рис. 4.1. Назвати робочий аркуш, на якому створена таблиця, Характеристики.
Рис. 4.1. Таблиця "Розрахунок економічних характеристик громадського транспорту"
4. Обчислити вартість перевезення одного пасажира якщо відомо:
– тролейбус використовує 20 кВт·год на один кілометр, вартість 1кВт·год – 1,05 грн., місткість тролейбуса – 70 пасажирів;
– автобус використовує дизпаливо, витрати дизпалива – 25 л на 100 кілометрів, вартість 1 л дизпалива – 10,05 грн., місткість автобуса – 50 пасажирів.
5. Застосувати до комірок з обчисленою вартістю проїзду в тролейбусі умовне форматування використовуючи кольорові шкали, а з обчисленою вартістю проїзду в автобусі використовуючи набори піктограм. Налаштувати власну кольорову гамму та набір піктограм.
Вказівки до виконання:
Ÿ виділити певний діапазон комірок;
Ÿ виконати команду Додаткові правила… з відповідної категорії (Кольорові шкали або Набори піктограм) та у діалоговому вікні Нове правило форматування здійснити налаштування.
6. Обчислити прибуток перевезення пасажирів громадським транспортом, якщо вартість проїзду у тролейбусі становить – 1,25 грн., в автобусі – 2,50 грн.
7. Обчислити час виконання одного рейсу у хвилинах, якщо середня швидкість руху тролейбуса – 18 км/год, автобуса – 25 км/год.
8. Аналогічно до стовпців "Прибуток" і "Час виконання одного рейсу" додати до таблиці стовпці "Інтервал руху машин на маршруті". Заповнити комірки створених стовпців, враховуючи, що протягом години необхідно перевезти 350 пасажирів по кожному маршруту в одну сторону, необхідну кількість машин на маршруті і час виконання одного рейсу.
9. Роздрукувати створену таблицю з результатами обчислень.
Лабораторна робота №5
Тема:Використання спеціальних засобів введення даних, автозаповнення. Засіб Автосума, її функції та способи використання.
Мета:Набути та закріпити навички використання спеціальних засобів введення даних, створення списків автозаповнення. Набути навичок використання Автосуми для обчислень.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР5_Прізвище.xlsх.
На робочому аркуші Аркуш1
3. Заповнити діапазон комірок А1:А20 послідовними числами від 1 до 20.
Вказівки до виконання:
Ÿ використати маркер заповнення та клавішу Ctrl.
4. Заповнити діапазон комірок B1:B20 числами, що утворюють арифметичну прогресію, перший елемент якої 1, другий – 3.
Вказівки до виконання:
Ÿ у комірки B1 і B2 ввести значення відповідно числа 1 та 3;
Ÿ виділити комірки B1 і B2;
Ÿ використати маркер заповнення виділеного діапазону.
5. Заповнити діапазон комірок С1:С20 числами, що утворюють арифметичну прогресію, в якій перший елемент – 25, а крок – 5.
Вказівки до виконання:
Ÿ у комірку C1 ввести значення першого елементу прогресії 25;
Ÿ виділити діапазон комірок C1:C20;
Ÿ виконати команду Прогресія… колекції Заповнити 
групи Редагування вкладки Основне;
Ÿ у діалоговому вікні Прогресія встановити потрібні параметри:
Рис. 5.1. Діалогове вікно Прогресія
6. Заповнити діапазон комірок D1:D20 числами, що утворюють геометричну прогресію, перший елемент якої – 3, а показник – 2. Елементи геометричної прогресії не повинні перевищувати 200000.
Вказівки до виконання:
Ÿ у діалоговому вікні Прогресія встановити параметри: Тип, Крок і Граничне значення.
7. Заповнити діапазон комірок Е1:Е20 датами від 22.12.2012 до 22.02.2016.
Вказівки до виконання:
Ÿ у комірку Е1 ввести дату 22.12.2012;
Ÿ виділити діапазон комірок Е1:Е20;
Ÿ виконати команду Прогресія… колекції Заповнити групи Редагування вкладки Основне;
Ÿ встановити одиницею зміни – місяць, крок – 2.
8. Створити список продавців ТОВ «Діамед» – Кучерявий, Мельник, Сердюк, Богданець, Семенко, Андрійчук.
Вказівки до виконання:
Ÿ виконати команду Параметри вкладки Файл;
Ÿ у діалоговому вікні Параметри Excel відкрити категорію Додатково перейти до групи Загальні та натиснути кнопку Редагувати користувацькі списки…;
Ÿ створити список з вказаних елементів згідно поданого зразка (рис. 5.2.).
Рис. 5.2. Діалогове вікно Списки
9. Заповнити діапазон комірок F1:F20 послідовністю з елементів списку створеного в п 8. починаючи з елементу Богданець.
10. Змінити у списку продавців ТОВ «Діамед» продавця Семенко на продавця Бержун.
Вказівки до виконання:
Ÿ внести зміни у список у діалоговому вікні Списки.
11. Заповнити діапазон комірок G1:G20 послідовністю з елементів зміненого списку.
12. Вилучити створений список після заповнення вказаних діапазонів.
13. Вибрати фоновий малюнок для робочого аркуша Аркуш1.
Вказівки до виконання:
Ÿ натиснути кнопку Тло групи Параметри сторінки вкладки Розмітка сторінки й у діалоговому вікні Тло вибрати файл для фонового малюнку.
14. Роздрукувати діапазон заповнених комірок.
Вказівки до виконання:
Ÿ виділити заповнений діапазон комірок на робочому аркуші Аркуш1;
Ÿ виконати команду Друк вкладки Файл;
Ÿ встановити параметри друку.
На робочому аркуші Аркуш2
15. Створити таблицю "Книги 2011 – Результати аукціонів в різних регіонах України" наведену на рис 5.3. Назвати аркуш, на якому створена таблиця, Аукціон.
Рис. 5.3. Таблиця "Книги 2011 – Результати аукціонів в різних
регіонах України".
16. Здійснити необхідні обчислення використовуючи команди колекції Автосума групи Редагування вкладки Основне
Вказівки до виконання:
Ÿ виділити необхідний діапазон комірок;
Ÿ відкрити список елементів інструмента Автосума 
;
Ÿ з пропонованого списку вибрати необхідну дію.
Лабораторна робота №6
Тема:Використання функцій MS Excel для обчислень.
Мета:Набути та закріпити навички використання формул масивів, математичних, логічних і статистичних функцій MS Excel для обчислень.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР6_Прізвище.xlsх.
3. На робочому аркуші Аркуш1 створити таблицю "Фірма "Меркурій". Дані про виконання плану за 1-ше півріччя 2011 р." наведену на рис. 6.1. Назвати аркуш, на якому створена таблиця – Дані.
4. На робочому аркуші Дані здійснити необхідні обчислення.
5. Відформатувати створену таблицю використовуючи можливості колекції Стилі клітинок групи Стилі вкладки Основне.
Рис. 6.1. Таблиця "Фірма "Меркурій".
Дані про виконання плану за 1-ше півріччя 2011 р.".
6. Захистити від змін комірки, із значеннями фактичного та планового товарообігу за 2010 і 2011 роки.
Вказівки до виконання:
Ÿ виділити весь робочий аркуш;
Ÿ відкрити діалогове вікно Формат клітинок виконавши команду Формат клітинок… контекстного меню клітинки виділення робочого аркуша, яка знаходить у верхньому лівому кутку на перетині рядка заголовків стовпців та стовпця номерів рядків аркуша;
Ÿ відмінити параметр Захистити клітинку на вкладці Захист діалогового вікна Формат клітинки;
Ÿ виділити діапазон комірок, в яких знаходяться значення фактичного та планового товарообігу за 2010 і 2011 роки;
Ÿ встановити для виділеного діапазону параметр Захистити клітинку;
Ÿ натиснути кнопку Захистити аркуш групи Зміни вкладки Рецензування для встановлення захисту робочого аркуша Дані.
7. На робочому аркуші Аркуш2 створити таблицю "Звітна відомість фірми "Меркурій" за 1-ше півріччя 2011 р." наведену на рис 6.2. Назвати аркуш, на якому створено таблицю, І півріччя.
Рис. 6.2. Таблиця "Звітна відомість фірми "Меркурій" за 1-ше півріччя 2011 р.".
8. Здійснити обчислення на робочому аркуші І півріччя за поданими алгоритмами:
Вказівки до виконання:
Ÿ при обчисленні значень у стовпчиках Пві та Ді використати формули масивів;
Ÿ при обчисленні значень у стовпчиках Впі та Вні використати функцію IF().
9. На робочому аркуші І півріччя визначити:
9.1. кількість місяців, протягом яких виконано план;
9.2. кількість місяців, протягом яких виконано план більше ніж на 105 відсотків;
9.3. кількість місяців, протягом яких план не виконано;
9.4. загальну суму фактичного товарообігу за ті місяці, протягом яких план було виконано більше ніж на 105 відсотків.
Вказівки до виконання:
Ÿ створити на робочому аркуші таблицю, комірки якої міститимуть обчислені значення;
Ÿ при виконанні пп. 9.1.-9.3. використати функцію COUNTIF();
Ÿ при виконанні пп. 9.4. використати функцію SUMTIF().
10. Відобразити на робочому аркуші І півріччя формули, за якими здійснюються обчислення.
11. Роздрукувати створені таблиці з даними і використаними формулами.
Лабораторна робота №7
Тема:Використання функцій MS Excel.
Мета:Набути та закріпити навички використання функцій MS Excel категорії Посилання та масиви.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР7_Прізвище.xlsх.
3. Збільшити кількість робочих аркушів до 5.
4. Сформувати на робочому аркуші Аркуш1 вид платіжного доручення, який наведений на рис. 7.1. Змінити назву Аркуш1 на Бланк.
5. На робочому аркуші Аркуш2 створити таблицю "Реквізити платника" (рис. 7.2.), в якій містяться всі необхідні реквізити для заповнення розділу Платник платіжного доручення.
6. Назвати робочий аркуш Аркуш2 – Платник.
7. На робочому аркуші Аркуш3 створити таблицю "Реквізити одержувача" (рис. 7.3.), в якій містяться всі необхідні реквізити для заповнення розділу Одержувач платіжного доручення. Назвати аркуш, на якому створена таблиця, Одержувач.
8. На робочому аркуші Аркуш4 створити таблицю "Дані платежу" (рис. 7.4.), яка містить дані необхідні для заповнення платіжного доручення. Назвати робочий аркуш Лист4 – Дані.
Рис. 7.1. Вид платіжного доручення
Рис. 7.2. Таблиця "Реквізити платника"
Рис. 7.3. Таблиця "Реквізити одержувача"
Рис. 7.4. Таблиця "Дані платежу"
9. На робочому аркуші Бланк заповнити розділ Платник платіжного доручення використовуючи дані робочого аркуша Платник.
Вказівки до виконання:
Ÿ для кожної комірки вказаного розділу створити посилання на відповідну комірку робочого аркуша Платник.
10. На робочому аркуші Аркуш5 побудувати таблицю "Допоміжна інформація" (рис. 7.5.).
Рис. 7.5. Таблиця "Допоміжна інформація".
Вказівки до виконання:
Ÿ значення комірки Номер платіжки, що формується потрібно вводити вручну;
Ÿ для визначення значення комірок Номер рядка даних платіжки та Номер рядка реквізитів одержувача використати функцію LOOKUP() категорії Посилання та масиви;
Ÿ для визначення значення комірки Одержувач використати функцію VLOOKUP() категорії Посилання та масиви.
11. Заповнити три платіжних доручення використовуючи дані таблиці Допоміжна інформація і функцію INDEX() категорії Посилання та масиви.
Вказівки до виконання:
Ÿ для заповнення комірок розділу Одержувач використовувати дані комірок Одержувач і Номер рядка реквізитів одержувача таблиці Допоміжна інформація та дані з робочого аркуша Одержувач;
Ÿ для заповнення інших комірок платіжного доручення використовувати дані комірок Номер платіжки, що формується і Номер рядка даних платіжки та дані з робочого аркуша Дані.
12. Роздрукувати заповнені платіжні доручення.
Лабораторна робота №8
Тема:Використання функцій MS Excel
Мета:Набути та закріпити навички роботи з функціями MS Excel різних категорій, використання даних зв’язних робочих аркушів
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ p іменем ЛР8_Прізвище.xlsx.
3. На робочому аркуші Аркуш1 створити таблицю "Діючі тарифи на комунальні послуги в жовтні 2011р. (грн.)", наведену на рис. 9.1. Змінити назву робочого аркуша Лист1 на Тарифи.
Рис. 8.1. Таблиця "Діючі тарифи на комунальні послуги в жовтні 2011р. (грн.)".
4. На робочому аркуші Аркуш2 створити таблицю "Розрахунок нарахувань за комунальні послуги у жовтні 2011 р. (грн.)", наведену на рис. 9.2. Змінити назву Аркуш2 на Нарахування.
Рис. 8.2. Таблиця "Розрахунок нарахувань за комунальні послуги в жовтні 2011р. (грн.)".
5. На робочому аркуші Нарахування здійснити необхідні розрахунки.
Пояснення: При виконанні розрахунків використати діючі тарифи та правила:
ü квартплата обчислюється як добуток двох величин: тарифу (квартплата) і загальної площі;
ü нарахування плати за водопостачання:
за наявності гарячої води дорівнює добутку кількості зареєстрованих та суми тарифу холодної води і тарифу гарячої води;
без гарячої води дорівнює добутку тарифу холодної води і кількості зареєстрованих;
за наявності водолічильників обчислюється як добуток кількості витрачених кубометрів на відповідний тариф;
ü нарахування плати за газопостачання визначається як добуток відповідного тарифу і кількості зареєстрованих;
ü нарахування плати за опалення дорівнює добутку відповідного тарифу і загальної площі квартири (м2).
ü при визначенні пільг на оплату комунальних послуг вказані пільги визначають відсоток нарахувань, який не оплачується споживачем.
6. На робочому аркуші Нарахування нижче створеної таблиці знайти:
6.1. Третій за порядком порядку найбільший борг,
Вказівки до виконання:
Ÿ використати функцію LARGE() категорії Статистичні.
6.2. Прізвище квартиронаймача з третім за порядком найбільшим боргом;
Вказівки до виконання:
Ÿ використати функції LOOKUP(), INDEX() категорії Посилання та масиви.
6.3. Другу за порядком найменшу суму нарахувань з урахуванням боргу.
Вказівки до виконання:
Ÿ використати функцію SMALL() категорії Статистичні.
6.4. Прізвище квартиронаймача з другою за порядком найменшою сумою нарахувань з урахуванням боргу.
6.5. Ранг загальної суми нарахувань з урахуванням боргу і без врахування боргу Іванової Лілії;
Вказівки до виконання:
Ÿ використати функцію RANK() категорії Статистичні.
7. На робочому аркуші Аркуш3 створити таблицю "Графік оплати за комунальні послуги" (рис. 8.3.). Заповнити таблицю згідно зразка. Змінити назву Аркуш3 на Графік.
Рис. 8.3. Таблиця "Графік оплати за комунальні послуги".
Вказівки до виконання:
Ÿ виділити відповідний діапазон комірок на аркуші Нарахування;
Ÿ натиснути кнопку Копіювати групи Буфер обміну вкладки Основне або комбінацію клавіш Ctrl+C;
Ÿ виділити відповідний діапазон комірок на аркуші Графік;
Ÿ виконати команду Спеціальне вставлення… колекції Вставити групи Буфер обміну вкладки Основне і у діалоговому вікні Спеціальне вставлення натиснути кнопку Вставити зв’язок, або натиснути кнопку Вставити зв’язок категорії Інші параметри вставлення колекції Вставити.
8. Визначити в який день тижня була здійснена остання оплата за комунальні послуги кожним споживачем. Якщо оплата здійснена у неділю, змінити дату оплати на дату робочого дня. При цьому доповнити таблицю додатковими стовпцями День оплати та Змінена дата.
Вказівки до виконання:
Ÿ використати функцію WEEKDAY() категорії Дата й час.
9. Визначити в якому місяці була здійснена остання оплата.
Вказівки до виконання:
Ÿ використати функцію MONTH() категорії Дата й час.
Лабораторна робота №9
Тема:Графічне подання даних за допомогою діаграм MS Excel
Мета:Набути та закріпити навички створення діаграм різного типу. Закріпити навички форматування та редагування діаграм MS Excel. Набути та закріпити навички роботи з елементами діаграм різного типу
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР9_Прізвище.xlsх.
3. На робочому аркуші Аркуш1 створити таблицю "Обсяги товарообігу за асортиментною структурою НКВФ "Колос", наведену на рис. 9.1. Змінити назву робочого аркуша Аркуш1 на Дані.
Рис. 9.1. Таблиця "Обсяги товарообігу за асортиментною структурою НКВФ "Колос".
4. На робочому аркуші Дані виконати необхідні розрахунки.
5. Побудувати звичайну гістограму, на якій відобразити структуру фактичної закупівлі товарів у 2010 та 2011 роках (рис. 9.2.). Додати до діаграми заголовок "Порівняльний аналіз обсягів закупівлі товарів у 2010-2011 рр". Розмістити діаграму на окремому аркуші діаграм, назвати аркуш зі створеною діаграмою Факт 2010-2011.
5.1. Змінити формат області побудови діаграми.
5.2. Змінити формат ряду даних Факт закупівлі 2010 р. (колір заливки).
5.3. Встановити розмір шрифта заголовка – 12 пт.
5.4. Змінити вирівнювання підписів по осі категорій.
6. Побудувати об’ємну звичайну лінійчату діаграму, на якій відобразити виконання плану продажу товарів у 2010 році (рис. 9.3.). Додати до діаграми заголовок "Аналіз виконання плану продажу товарів у 2010 році". По осі значень вивести основні і проміжні лінії. Розмістити діаграму на робочому аркуші Аркуш2, назвати робочий аркуш Аркуш2 – Діаграми.
6.1. Змінити колір стінок діаграми.
6.2. Змінити кольори, якими відображаються ряди даних.
6.3. Встановити ціну проміжних поділів ліній сітки – 10.
7. Побудувати об’ємну гістограму з стовпцями у вигляді конусів і циліндрів, на якій відобразити виконання плану продажу товарів у 2011 році (рис. 9.4.). Додати до діаграми заголовок "Аналіз виконання плану продажу товарів у 2011 році". Розмістити діаграму на робочому аркуші Діаграми. Відформатувати діаграму згідно поданого зразка.
8. Побудувати об’ємну розрізану кругову діаграму, на якій відобразити асортименту структуру обсягів фактичного продажу товарів у 2010 році (рис. 9.5.). Додати до діаграми заголовок "Асортиментна структура обсягів продажу товарів у 2010 році". Відобразити на діаграмі підписи даних – долі. Розмістити діаграму на робочому аркуші Діаграми. Відформатувати діаграму згідно поданого зразка.
8.1. Здійснити поворот діаграми таким чином, щоб найменший сектор був на першому плані.
8.2. Змінити колір найтемнішого сектора.
9. Побудувати кільцеву діаграму, на якій відобразити асортименту структуру обсягів фактичного продажу товарів у 2010-2011 рр (рис. 9.6.). Додати до діаграми заголовок "Асортиментна структура обсягів продажу товарів у 2010-2011 рр". Відобразити на діаграмі підписи даних – долі. Розмістити діаграму на робочому аркуші Діаграми. Відформатувати діаграму згідно поданого зразка.
10. Побудувати вторинну кругову діаграму, з частиною даних винесених у іншу кругову діаграму, на якій відобразити асортиментну структуру прибутку у 2010 році (рис. 9.7.). Додати до діаграми заголовок "Асортиментна структура прибутку у 2010 році". Розмістити діаграму на робочому аркуші Діаграми.
10.1. Відобразити заголовок діаграми напівжирним курсивом, розміром у 11 пт.
10.2. У другу кругову діаграму винести точки даних, значення яких менше 4.
10.3. Відформатувати діаграму згідно зразка (рис 9.7.)
11. Побудувати нормовану гістограму, на якій відобразити питому вагу груп товарів у загальному товарообігу за 2010-2011 рр. (рис. 9.8.). Додати до діаграми заголовок "Відображення долі кожної групи товарів у загальному товарообігу". Вивести на діаграмі проміжні лінії осі значень. Розмістити діаграму на робочому аркуші Діаграми.
11.1. Відобразити заголовок діаграми курсивом, розміром у 11 пт.
11.2. Встановити ціну проміжних поділів – 5.
11.3. Змінити тип проміжних ліній сітки.
12. Роздрукувати таблицю та створені діаграми.
Рис. 9.2. Діаграма "Порівняльний аналіз обсягів закупівлі товарів у 2010-2011 рр.".
Рис. 9.3. Діаграма "Аналіз виконання плану продажу товарів у 2010 році".
Рис. 9.4. Діаграма "Аналіз виконання плану продажу товарів у 2011 році".
Рис. 9.5. Діаграма "Асортиментна структура обсягів продажу товарів у 2010 році".
Рис. 9.6. Діаграма "Асортиментна структура обсягів продажу товарів у 2010-2011 рр".
Рис. 9.7. Діаграма "Асортиментна структура прибутку у 2010 році".
Рис. 9.8. Діаграма "Відображення долі кожної групи товарів у загальному товарообігу".
Лабораторна робота №10
Тема:Робота зі списками в MS Excel. Сортування даних та обчислення проміжних підсумків.
Мета:Закріпити навички створення та впорядкування списків. Набути навички обчислення простих та складних проміжних підсумків, створення структури робочого аркуша.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР10_Прізвище.xlsх.
3. На робочому аркуші Аркуш1 створити таблицю "Перелік товарів на складі фірми "Веселка", наведену на рис. 10.1. Змінити назву робочого аркуша Аркуш1 на Склад. Для заповнення комірок поля Фасування створити власний формат даних.
Вказівки до виконання:
Ÿ виділити діапазон комірок поля Фасування;
Ÿ відкрити діалогове вікно Формат клітинок, виконавши наприклад відповідну команду контекстного меню виділеного діапазону;
Ÿ активізувати вкладку Число, зі списку Числові формати вибрати елемент (усі формати);
Ÿ у полі Тип ввести маску 0,0_\л.
Рис. 10.1. Таблиця "Перелік товарів на складі фірми "Веселка ".
4. Використовуючи функції RAND() або RANDBETWEEN() та ROUND() заповнити даними комірки поля Залишок по складу так, щоб значення знаходились в межах від 100 до 1000. Замінити формули на її результат, інакше значення у цьому стовпці будуть постійно змінюватися.
Вказівки до виконання:
для заміни формули її значенням необхідно:
Ÿ виділити діапазон комірок з формулами;
Ÿ скопіювати виділений діапазон у буфер обміну виконавши відповідну команду контекстного меню виділеного діапазону;
Ÿ виконати команду Спеціальне вставлення… колекції Вставити групи Буфер обміну вкладки Основне;
Ÿ вставити інформацію з буферу на теж саме місце, вказавши параметр вставки – Значення.
5. Обчислити вартість залишків всіх товарів на складі.
6. Скопіювати створений список на робочі аркуші Аркуш2-Аркуш4.
7. На робочому аркуші Склад впорядкувати список за полем Ціна за одиницю у порядку зменшення значень.
8. Роздрукувати впорядкований список.
9. На робочому аркуші Склад впорядкувати список за складним ключем: за полями Виробник, Група – у алфавітному порядку, за полями Фасування, Вартість – у порядку зменшення значень. Роздрукувати впорядкований список.
Вказівки до виконання:
Ÿ виділити діапазон комірок списку або активізувати одну довільну його комірку;
Ÿ натиснути кнопку Сортувати групи Сортування й фільтр вкладки Дані;
Ÿ у діалоговому вікні Сортування встановити необхідні параметри для впорядкування списку (рис. 10.2.);
Рис. 10.2. Діалогове вікно Сортування
Ÿ натиснути кнопку ОК для завершення виконання команди сортування.
10. Перейменувати робочий аркуш Аркуш2 на Загальна_вартість.
11. На робочому аркуші Загальна_вартість обчислити загальну вартість залишків товарів на складі кожного виробника.
Вказівки до виконання:
Ÿ впорядкувати список за значеннями поля Виробник;
Ÿ натиснути кнопку Проміжні підсумки групи Структура вкладки Дані;
Ÿ у діалоговому вікні Проміжні підсумки встановити необхідні параметри для обчислення загальної вартості товарів кожного виробника (рис. 10.3.).
Рис. 10.3. Діалогове вікно Проміжні підсумки
для обчислення загальної вартості залишків товарів.
12. За отриманими підсумковими значеннями у п.11 побудувати кругову діаграму, на якій відобразити долю кожного виробника у загальному залишку товару на складі. Додати до діаграми назву "Порівняльний аналіз обсягів товарів різних виробників на складі фірми "Веселка", підписи даних – долі. Розмістити діаграму на робочому аркуші Загальна_вартість.
Вказівки до виконання:
Ÿ на робочому аркуші Загальна_вартість відобразити обчислені проміжні підсумки використовуючи відповідні елементи управління структурою робочого аркуша;
Ÿ використовуючи клавішу Ctrl виділити комірки зі значеннями проміжних підсумків;
Ÿ використовуючи інструменти вкладки Вставлення побудувати діаграму.
13. Роздрукувати побудовану діаграму.
14. Перейменувати робочий аркуш Аркуш3 на Граничні_значення.
15. Використовуючи команду Проміжні підсумки на робочому аркуші Граничні_значення визначити середню вартість залишків товарів кожної групи, а в межах групи – мінімальну ціну товару в залежності від фасування.
Вказівки до виконання:
Ÿ впорядкувати список за значеннями полів Група і Фасування;
Ÿ натиснути кнопку Проміжні підсумки групи Структура вкладки Дані для визначення середньої вартості залишків товарів на складі;
Рис. 10.4. Діалогове вікно Проміжні підсумки
для обчислення середньої вартості залишків товарів
Ÿ виділити будь-яку комірку в межах списку;
Ÿ натиснути кнопку Проміжні підсумки групи Структура вкладки Дані для визначення мінімальної ціни товару;
Ÿ для збереження обчислених раніше підсумків у діалоговому вікні Проміжні підсумки зняти прапорець Замінити поточні підсумки.
16. На робочому аркуші Граничні_значення відобразити проміжні підсумки для груп Морс, Напій і Сік, для групи Мінеральна вода відобразити дані і обчислені проміжні підсумки.
17. Перейменувати робочий аркуш Аркуш4 на Кількість.
18. Використовуючи діалогове вікно Проміжні підсумки на робочому аркуші Кількість визначити кількість найменувань товарів кожної групи, що залишилися на складі, в межах групи – кількість товарів, дата виготовлення яких знаходиться в межах одного з місяців року.
Вказівки до виконання:
Ÿ змінити формат представлення даних поля Дата виготовлення так, щоб відображалися тільки місяць і рік введених дат;
Ÿ впорядкувати список за значеннями полів Група і Дата виготовлення;
Ÿ натиснути кнопку Проміжні підсумки групи Структура вкладки Дані для обчислення необхідних підсумків.
19. Роздрукувати отримані результати.
Лабораторна робота №11
Тема:Робота зі списками в MS Excel. Сортування та фільтрація даних.
Мета:Закріпити навички створення та впорядкування списків. Набути навички відбору інформації за різними критеріями.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР11_Прізвище.xlsх.
3. На робочому аркуші Аркуш1 створити таблицю "Перелік товарів на складі фірми "Мінос", наведену на рис. 11.1. Змінити назву робочого аркуша Аркуш1 на Склад.
Рис. 11.1. Таблиця "Перелік товарів на складі фірми "Мінос".
4. Обчислити вартість поставлених товарів.
5. Скопіювати створений список на робочі аркуші Аркуш2 – Аркуш5.
6. На робочому аркуші Склад відобразити першу половину отриманого списку.
Вказівки до виконання:
Ÿ виділити будь-яку комірку списку;
Ÿ виконати команду Фільтр колекції Сортування й фільтр групи Редагування вкладки Основне;
Ÿ у заголовку поля № п/п натиснути кнопку зі стрілкою;
Ÿ зі списку, що відкрився вибрати команду Фільтри чисел та елемент Перші 10…;
Ÿ у діалоговому вікні Автофільтр для добору найкращої десятки встановити відповідні параметри для відображення першої половини списку (рис. 11.2.).
Рис. 11.2. Діалогове вікно Автофільтр для добору найкращої десятки
7. На аркуші Аркуш2 впорядкувати отриманий список за значеннями поля Виробник в алфавітному порядку і поля Ціна в порядку зменшення значень. Роздрукувати результат.
8. Назвати робочий аркуш Аркуш2 – Підсумки.
9. На робочому аркуші Підсумки визначити загальну суму поставлених товарів кожною з фірм-постачальників. Впорядкувати список за зменшенням загальної вартості поставлених товарів.
Вказівки до виконання:
Ÿ використати можливість обчислення проміжних підсумків у списку для визначення загальної суми поставлених товарів;
Ÿ для сортування списку в залежності від обчислених підсумків необхідно згорнути рівень структури робочого аркуша з даними, потім виконати відповідну команду сортування.
10. На робочому аркуші Аркуш3 впорядкувати список, помістивши спочатку товари фірми, яка поставила товарів на найбільшу суму, потім товари фірми наступної по величині загальної вартості поставок і т.д. Роздрукувати результат.
Вказівки до виконання:
Ÿ виконати команду Параметри вкладки Файл;
Ÿ у діалоговому вікні Параметри Excel перейти до категорії Додатково та знайти групу Загальні;
Ÿ у групі Загальні натиснути кнопку Редагувати користувацькі списки;
Ÿ у діалоговому вікні Списки створити новий список, який міститиме назви фірм-постачальників у порядку в залежності від сум поставлених товарів (див. п.9);
Ÿ закрити діалогові вікна Списки та Параметри Excel почергово натиснувши кнопку ОК;
Ÿ виконати команду Настроюване сортування… колекції Сортування й фільтр групи Редагування вкладки Основне;
Ÿ у діалоговому вікні Сортування у полі Стовпець Сортувати за вибрати елемент Постачальник, у полі Сортування за вибрати елемент Значення, у поля Порядок вибрати елемент Настроюваний список…;
Ÿ у діалоговому вікні Списки вибрати щойно створений список (рис. 11.3.).
Ÿ натиснути кнопку ОК для завершення операції сортування.
Рис. 11.3. Діалогове вікно Сортування з визначеними параметрами
11. Змінити порядок розташування полів Постачальник і Дата поставки.
Вказівки до виконання:
Ÿ виділити діапазон комірок, який відповідає полям Постачальник і Дата поставки, при цьому включити до діапазону комірки з заголовками;
Ÿ виконати команду Настроюване сортування… колекції Сортування й фільтр групи Редагування вкладки Основне;
Ÿ у діалоговому вікні Сортування натиснути кнопку Параметри…;
Ÿ у діалоговому вікні Параметри сортування (рис. 11.4.) увімкнути перемикач Сортувати стовпці діапазону.
Рис. 11.4. Діалогове вікно Параметри сортування
12. На робочому аркуші Аркуш4 відобразити Олівці поставлені у лютому місяці. Роздрукувати отриманий список.
Вказівки до виконання:
Ÿ виділити будь-яку комірку списку;
Ÿ виконати команду Фільтр колекції Сортування й фільтр групи Редагування вкладки Основне;
Ÿ у заголовку поля Група натиснути кнопку зі стрілкою;
Ÿ зі списку, що відкрився вибрати елемент Текстові фільтри та команду Містить… з меню, що відкрилося;
Ÿ у діалоговому вікні Користувацький автофільтр (рис. 11.5.) параметр лівого поля "містить" встановлюється автоматично, потрібно встановити параметр у правому полі "олівці";
Рис. 11.5. Діалогове вікно Користувацький авто фільтр
для визначення умови відбору за полем Група.
Ÿ у заголовку поля Дата поставки натиснути кнопку зі стрілкою;
Ÿ зі списку, що відкрився вибрати елемент Фільтри дат та команду Між… з меню, що відкрилося;
Ÿ у діалоговому вікні Користувацький автофільтр (рис. 11.6.) параметри лівих полів встановлюються автоматично, потрібно встановити параметри правих полів, що відповідають датам початку та кінця лютого місяця 2011 р.;
Рис. 11.6. Діалогове вікно Користувацький авто фільтр
для визначення умови відбору за полем Дата поставки
13. На робочому аркуші Аркуш5 отримати список всіх товарів фірм-виробників Самоцвет і CENTROPEN. Роздрукувати результат.
Вказівки до виконання:
Ÿ використати автофільтр.
14. На робочому аркуші Аркуш5 отримати список всіх товарів поставлених після 01.04.11р. кількістю більшою ніж 300 одиниць. Роздрукувати результат.
Вказівки до виконання:
Ÿ виділити рядок заголовку створеного списку;
Ÿ використовуючи буфер обміну скопіювати виділений діапазон і вставити його декількома рядками нижче створеного списку, строго під ним;
Ÿ у наступних рядках під скопійованим рядком заголовку вказати умови відбору записів;
Рис. 11.7. Створений діапазон умов.
Ÿ виділити будь-яку комірку списку;
Ÿ натиснути кнопку Додатково групи Сортування й фільтр вкладки Дані;
Ÿ у діалоговому вікні Розширений фільтр (рис. 11.8):
ü вказати адресу діапазону умов;
ü увімкнути перемикач скопіювати результат до іншого розташування;
ü визначити адресу діапазону з результатом фільтрації у полі Діапазон для результату.
Рис. 11.8. Діалогове вікно Розширений фільтр
15. За допомогою розширеного фільтру на робочому аркуші Аркуш5 отримати список всіх товарів виробництва ТОВ "Зошит України" поставлених фірмами, у назві яких зустрічається буква „к” або виробництва А-HIO поставлених до 01.03.05р. Роздрукувати отриманий результат.
Лабораторна робота №12
Тема:Робота зі списками в MS Excel. Фільтрація даних.
Мета:Закріпити навички створення та впорядкування списків. Набути навички відбору інформації за різними критеріями.
Хід роботи:
1. Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2. Зберегти створений документ з іменем ЛР12_Прізвище.xls.
3. На робочому аркуші Аркуш1 створити таблицю "Перелік замовлених товарів", табл. 12.1. Змінити назву робочого аркуша Аркуш1 на Замовлення.
Таблиця 12.1.
Перелік замовлених товарів