Порядок виконання роботи

ТЕОРЕТИЧНІ ВІДОМОСТІ

ЛАБОРАТОРНА РОБОТА № 6

ТЕМА: ЕЛЕКТРОННІ ТАБЛИЦІ Microsoft Excel

МЕТА РОБОТИ: НАВЧИТИСЯ СТВОРЮВАТИ ТАБЛИЦЮ EXCEL ЯК БАЗУ ДАНИХ, УВОДИТИ ТА ФОРМАТУВАТИ ДАНІ УТАБЛИЦІ, ВИКОНУВАТИ РОЗРАХУНКИ ЗА ФОРМУЛАМИ ТА ФУНКЦІЯМИ

ЗАВДАННЯ:

1. Створити таблицю згідно варіанта індивідуального завдання

2. Виконати форматування даних у таблиці

3. Виконати розрахунки у таблиці, використовуючи потрібні формули та функції

4. Зробити сортування записів у базі за двома ключами (окремо): а) за убуванням поля “Залишки”; б) за зростанням поля “Прибуток від продажу”

5. Відфільтрувати дані за двома ключами: а) продукція, відпускна ціна якої знаходиться в проміжку від 45 до 100 гривень; б) продукція, яку потрібно замовити (поле “Замовити”)

Дані, організовані у список, у MS Excel часто називають базою даних. Звичайно, таблиця-список суттєво відрізняється від баз даних у фахових системах керування базами даних, але наявність спеціальних команд і функцій для керування такою «базою» істотно спрощує роботу та розширює можливості обробки даних. Зауважмо, що крім вказаних можливостей Excel підтримує обмін даними із системами керування базами даних, дозволяючи прочитати або зберегти дані в традиційних форматах «справжніх» баз даних.

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

MS Excel розпізнає списки автоматично. Перед виконанням дій зі списком достатньо активізувати будь-яку комірку всередині списку. Вміст першого рядка Excel вважає іменами полів і не опрацьовує їх, як інші дані. Якщо перед здійсненням обробки списку виділити окрему частину списку, то MS Excel вважатиме списком тільки виділений діапазон комірок.

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

· Бажано розташовувати список на окремій робочій сторінці, на якій не повинно бути більше нічого. Якщо це неможливо, то список потрібно відокремити від інших даних принаймні одним порожнім рядком та одним порожнім стовпчиком.

· Для виділення рядка з іменами полів використовують можливості форматування у вигляді шрифтів, способів їх написання та рамок. Не варто вставляти окремі рядки із дефісів, знаків рівності або інших символів, тому що MS Excel може сприйняти таке «підкреслення» за дані.

· Якщо потрібно зазначити довге ім’я поля для стовпчика з «вузькими» даними, можна записати ім’я у декількох рядках однієї комірки або змінити орієнтацію комірки.

· Для роботи з великими списками, дані яких не відображаються одночасно в робочому вікні, у Excel 2003 використовують команду Окно / Закрепить области для фіксування областей або Окно / Разделить для розбиття вікна на підвікна. Для Excel 2007 - це стрічка Вид, тематична група кнопок Окно.

· Присвоєння імені діапазону комірок, що містить список спростить роботу з списком, особливо, якщо він великий за розміром.

1. Створити електронну таблицю згідно варіанта індивідуального завдання (№ рядків узяти з Таблиці №1). Перейменувати робочий лист Лист1 на Ваше прізвище.

2. Додати перший стовпець, за допомогою стрічка Главная, група кнопок Ячейки - Вставит і увести заголовок "№ п/п" (розташування тексту – вертикальне (орієнтація +90^о), вирівнювання по вертикалі - по центру). Скористуватися командою Формат Ячеєк у вікні діалогу Формат ячеек вибрати вкладку Выравнивание. Для Excel 2007 з контекстного меню, або-стрічка Главная, група кнопок Выравнивание, або вікно діалогу Формат ячеек.

Аналогічно, після стовпця “№ п/п” добавте стовпець “Дата надходження” (дати надходження уведіть за своїм розсудом).

Додати угорі три рядки за допомогою команди Вставить стрічки Главная, групи кнопок Ячейки. У перших двох рядках уведіть назву таблиці (назву вибрати самостійно), наприклад, у першому рядку “ Рахунок - фактура ”, у другому – “ на реалізацію товарів підприємством “Єлектропостач ”. Колір шрифту для тексту установити - червоний, фон – голубий (чи за своїм розсудом) у вікні діалогу Формат ячеек або -стрічка Главная, група кнопок Шрифт - Цвет заливки, Цвет текста. Об'єднати чарунки з назвою таблиці спочатку першого, потім другого рядків окремо (вікно діалогу Формат ячеек, вкладка Выравнивание, у полі Отображение поставити позначку напроти Объединение ячеек; або за допомогою кнопки з аналогічною назвою). Вирівняти назву таблиці у рядках по центру за допомогою кнопки По центру стрічки Главная, групи кнопок Выравнивание. Також можна скористатисявікном діалогу Формат ячеек, вкладка Выравнивание – по центру.

Третій рядок залишити порожнім для відділення заголовка таблиці від самої таблиці.

Примітка: Для форматування комірок електронної таблиці можна скористуватися відповідними командами контекстного меню.

3. Помістити в комірки другого та третього стовпців над заголовком таблиці поточну дату за допомогою функції СЕГОДНЯ() або ТДАТА(). Скористатися кнопкою Вставка функции у рядку формул. У діалоговому вікні Мастер функций у полі Категория вибрати Дата и время, у полі Функция - СЕГОДНЯ() або ТДАТА().

4. Виконати форматування таблиці.

Встановити ширину стовпців таблиці відповідно кількості символів.

Установити наступний формат чарунок для стовпців:

“№ п.п ” “Числовой”

"Дата надходження" “Дата”

"Найменування товару" “Текстовый”

“Надійшло” “Числовой”

"Собівартість" “Денежный”

“Продано” “Числовой”

"Відпускна ціна" “Денежный”

Форматування даних комірок робочого листа можна виконати на вкладці Число діалогового вікна Формат ячеек або пунктом контекстного меню Формат ячеек...

Щоб застосувати потрібний формат, виберіть його у списку Числовые форматыі натисніть кнопку ОK. Якщо формат має додаткові параметри, то після його вибору на вкладці з’являються поля, в яких можна вказати ці параметри.

· Загальний формат. Якщо явно не змінювати формат комірки, то Excel застосовує до неї формат Общий,який відображає дані в такому вигляді, як вони вводяться.

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

· Грошовий формат. Формат Денежныймає усі можливості форматування формату Числовой, а також дозволяє вибирати грошову одиницю.

· Фінансовий формат.Формат Фінансовийподібний до грошового; він має додаткові можливості вертикального вирівнювання грошової одиниці і числа.

· Відсотковий формат. При застосуванні до числа формату Процентныйдесяткова кома зміщується в числі на два знаки праворуч, а в кінці числа виводиться знак відсотків «%».

· Дробовий формат. Формат Дробныйдозволяєвиводити числові значення у вигляді звичайних дробів. Вигляд дробу можна вибрати із списку Тип, який відкривається після вибору формату Дробный.

· Експоненційний формат.У форматі Експоненциальныйчисла подаються у вигляді а Е n, де а — ціле число або десяковий дріб (мантиса), n — ціле число (порядок). Зображення а Е n означає, що в комірці знаходиться число a ×10^{± n} _.

· Текстовий формат.Застосування до комірки текстового формату означає, що число або формула записані в комірці, будуть вважатися текстом.

· Формат дати. Формат відображення дати в комірці можна вибрати із списку Тип формату Дата або визначити його при вводі дати, як описано вище в п. «Типи даних».

Примітка: Для виділення декілька несуміжних діапазонів треба утримувати затисненою клавішу Ctrl.

5. Додати в кінець таблиці нове поле “Залишки”, де визначити залишки кожного товару, як різницю полів “Надійшло” та “Продано”.

6. Додати в кінець таблиці ще два поля “Витрати” та “Виручка”. Значення поля “ Витрати ” визначити як добуток між “Собівартість” та “Продано”, значення поля “ Виручка ” визначити як добуток між “Відпускна ціна” та “Продано”.

7. Додати в кінець таблиці поле “Прибуток від продажу”. Визначити отриманий прибуток від реалізації по кожному найменуванню товару, як різницю між значеннями полів “ Виручка ” та “ Витрати ”

8. Додати в кінець таблиці поле “Податок”, уміст якого визначити за формулою:

“Прибуток від продажу” * 1% * «номер варіанта», якщо

“Прибуток” <1000

“Податок” = “Прибуток від продажу” * 2% * «номер варіанта», якщо

1000<=”Прибуток” <2000

“Прибуток від продажу” * 3% * «номер варіанта», якщо

“Прибуток” >=2000

Виконати потрібні розрахунки за допомогою функції ЕСЛИ().

9. Додати в кінець таблиці поле “Замовлення”. Якщо значення залишків по кожному найменуванню товару у полі “Залишки” менше 50 шт., то напроти цього товару написати слово “Замовити”, а якщо більше - написати слово “Ні”. Це можна здійснити за допомогою функції ЕСЛИ(), скориставшися діалоговим вікном Мастер функций, де у полі Категория вибрати Логические. У вікні функції ЕСЛИ() правильно записати потрібну умову та значення, коли умова істина та неправда. Рядок формул при цьому має набути, наприклад, такий вигляд: =ЕСЛИ(H7<=50;”Замовити”;”Ні”). (Це у тому випадку, коли поле “Залишки” знаходиться у стовпці H, а перше значення цього поля у 7-му рядку).

10. Визначити товар, від реалізації якого отриманий максимальний прибуток, використовуючи умовне форматування.

Умовне форматування, на відміну від звичайного формату комірки/діапазону, спрацьовує зміною формату на новий, попередньо вказаний вами, формат лише при виконання деяких умов. Типово цими умовами є значення у самій же комірці/діапазоні. Таких спеціальних умов застосування зміни формату може бути до трьох.

Для застосування до комірки/діапазону умовного форматування:

· зробіть комірку/діапазон активним;

· стрічка Главная, група кнопок Стили, кнопка списку Условное форматирование. Відкривається форма діалогу для умовного форматування;

· у формі групою управляючих елементів пропонується задати першу умову застосування форматування. Для кожної умови (від 1 до 3-х) може бути свій варіант форматування;

· для умови можна вибрати або (1) умову для значення комірки, або (2) задати формулу (комірку з формулою). Для значення пропонуються всі основні варіанти відношення (=, <, >, <>, <=, >=, поза відрізком значень) у випадаючому списку вибору відношення. В залежності від вибраного відношення, з’являється правіше списку відношень необхідна кількість полів для значень чи посилань на комірки із значеннями;

· формула, як критерій застосування умовного форматування, використовується для звернення до будь-яких функцій та виразів, які повертають значення ИСТИНА або ЛОЖЬ;

· задавши умову форматування, натискаємо кнопку Формат. Відкривається форма діалогу Формат ячеек. Ця форма аналогічна відомій вам формі, яка викликається через меню Формат/Ячейки;

· задаємо формат: 1) шрифту, 2) границі та 3) фону комірки;

· якщо потрібна ще одна умова застосування для комірки/діапазону умовного форматування (тепер вже 2-га чи 3-я), натискаємо кнопку А также;

· якщо всі чи декілька умов (всього їх 3) потрібно анулювати, натискаємо кнопку Удалить. Відкривається діалогова форма, в якій вказується умова, що видаляється.

Для застосування умовного форматування потрібно попередньо виділити необхідний діапазон (тобто діапазон значень поля “Прибуток від продажу”) Для застосування умовного форматування використовується стрічка Главная, група кнопок Стили, кнопка списку Условное форматирование, пункт Правила выделения ячеек – Другие правила або Создать правило. У вікні Создание правила форматирования вибрати тип правила: Форматировать только ячейки которые содержат і увести потрібні дані у поле Измените описание правила.

11. Визначити сумарні витрати на виробництво всієї продукції, що надійшла, використовуючи функцію СУММПРОИЗВ. Значення помістити над назвою таблиці над полем “Собівартість” й додати відповідну примітку. Для проведення обчислень у діалоговому вікні Майстер функций у полі Категория вибрати Математические, у полі Функция - СУММПРОИЗВ і правильно заповнити діапазони комірок Массив1 (поле “Надійшло”) і Массив2 (поле “Собівартість”). Для додавання примітки для комірки використовується стрічка Рецензирование, група Примечание - Создать примечание.

Аналогічно визначити сумарні витрати на виробництво продукції, що продалася, використовуючи функцію СУММПРОИЗВ для полів “Продано” й “Собівартість”. Значення помістити над назвою таблиці над полем “Продано”, додати відповідну примітку.

Визначити суму, що надійшла від реалізації продукції, використовуючи функцію СУММПРОИЗВ для полів “Продано” й “Відпускна ціна”. Значення помістити над назвою таблиці над полем “Відпускна ціна”, додати відповідну примітку.

12. Додати в кінець таблиці новий рядок, де визначити підсумкові значення полів (“Надійшло”, “Собівартість”, “Продано”, “Відпускна ціна” і так далі). Для підрахунку суми використати кнопку списку Автосума стрічки Формули або кнопка списку Сума групи кнопок Редактирование стрічки Главная.

13. Порівняти значення для поля Витрати з підсумковими витратами на виробництво продукції, що продалася, які ви підрахували раніше, використовуючи функцію СУММПРОИЗВ для полів “Продано” й “Собівартість”. Також порівняти підсумкове значення поля Виручка з підсумковим значенням, отриманим від реалізації продукції, що ви обчислили раніше, використовуючи функцію СУММПРОИЗВ для полів “Продано” й “Відпускна ціна”. Зробити висновки. Також виконати заливання комірок з відповідними значеннями одним і тим кольором за допомогою вікна діалогу Формат ячеек, де вибрати вкладку Вид (Заливка).

14. Визначити середні значення для полів “Собівартість” і “Відпускна ціна”. Використати функцію СРЗНАЧ(). Значення помістити в таблиці під полями “Собівартість” і “Відпускна ціна”, додавши ще один рядок у кінець таблиці.

15. Додати в кінець таблиці ще одне поле “ % Прибутку” та визначити значення цього поля за наступною формулою: Значення прибутку від продажу за кожним найменуванням товару розділити на сумарне значення поля “Прибуток від продажу”. Зробити формат комірок цього поля – процентним.

16. У комірку над таблицею увести курс долара. Додати в кінець таблиці ще поле “ Прибуток в USD” та зробити перерахунок прибутку від продажу за кожним найменуванням товару за курсом USD, формат поля – Денежный ( $ Английский (США)).

17. Задати границі таблиці, попередньо виділивши таблицю. Використати вікно діалогу Формат ячеек, де вибрати вкладку Граница.

18. Скопіювати таблицю та уставити її на другий лист.

19. Відформатувати скопійовану таблицю, використовуючи один з видів автоформатів у діалоговому вікні Автоформат (меню Формат – Автоформат …) для Excel 2003. Для Excel 2007 скористайтеся відповідним стилем форматування за допомогою стрічки Главная, групи Стили.

20. Перейменувати другий лист, задавши ім'я –“ База даних”.

21. Зберегти робочу книгу на диску.

Дата добавления: 2015-05-10