Практична робота № 2

ТЕМА: „Робота з електронною таблицею як із базою даних”

МЕТА: навчитися розробляти структуру таблиці бази даних та вводити дані;

форматувати дані; навчитися робити розрахунки в таблиці бази даних, використовуючи формули та функції.

Варіант № ____

ЗАВДАННЯ:

1. розробити структуру таблиці бази даних та увести вхідні дані згідно індивідуального завдання

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

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

Основні поняття баз даних

У Microsoft Excel як базі даних використовується список, що являє собою сукупність рядків робочого листа, що містять однотипні дані. Кожен запис розташовується в окремому рядку, а для кожного поля приділяється один стовпець. Область таблиці, починаючи з назви полів і нижче можна розглядати як базу даних. Стовпці цієї таблиці називаються полями, а рядки називаються записами.

Перш, ніж уводити інформацію в базу даних, необхідно створити її структуру, тобто увести назви (заголовки) полів.

До розташування й оформлення заголовків стовпців пред'являються наступні вимоги:

• заголовки стовпців повинні знаходиться в першому рядку списку. Вони використовуються при складанні звітів, пошуку й організації даних

• шрифт, вирівнювання, формат, шаблон, границя й формат прописних і малих літер, привласнені заголовкам стовпців списку, повинні відрізняться від формату, привласненого рядкам даних

• для відділення заголовків від розташованих нижче даних варто використовувати границі комірок, а не порожні рядки чи пунктирні лінії

• між назвою таблиці й базою даних на робочому листі варто залишати щонайменше один порожній рядок

Крім перерахованих вище вимог щодо заголовків стовпців, при створенні бази даних необхідно дотримувати наступних рекомендацій з розміру й розташування списку, а також змісту рядків і стовпців:

• на листі не слід розміщати більш одного списку

• у самому списку не повинне бути порожніх рядків і стовпців

• список повинний бути організований так, щоб у всіх рядках в однакових стовпцях знаходилися однотипні дані

• перед даними в комірках не слід уводити зайві пробіли, тому що вони впливають на сортування

Для перегляду, зміни, додавання і видалення записів або списку бази, зручно використовувати Форму.

Дія	Спосіб виконання
Додавання записів за допомогою Форми	виділити комірку списку, починаючи з якої потрібно додавати запис меню Данные / команда Форма / кнопка Добавить увести нові записи у відповідні поля для додавання запису натиснути кнопку Добавить або клавішу ENTER натиснути кнопку Закрыть для виходу з форми

Хід виконання роботи

1. Відповідно до вашого варіанта завдання розробіть структуру бази та занесіть її до протоколу. Створіть робочий лист бази даних –“БАЗА №1” та введіть заголовки полів згідно розробленої структури (на підставі завдання таблиці № 2).

Пам’ятайте, що назва стовпця може займати тільки одну КЛІТИНКУ!

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

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

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

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

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

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

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

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

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

"Дата" Дата

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

“Поступило” Числовой

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

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

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

Використайте меню Формат – Ячейки…,у вікні діалогу Формат Ячеек виберіть вкладку Число, у полі Числовые форматы виберіть потрібний формат для відповідного стовпця і параметри формату або використайте пункт контекстне меню Формат ячеек...

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

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

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

7. Додайте в кінець таблиці поле Податок. Підрахуйте податок як різницю від значень полів Ціна1 та Ціна2 поділену на шість.

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

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

10. За допомогою діалогової форми (меню Данные – Форма…) уведіть вхідну інформацію у відповідні поля введення, назви яких повинні відповідати заголовкам стовпців. Для переходу до наступного запису скористуйтеся кнопкою Добавить або клавішею ENTER. Після введення останнього запису натисніть кнопку Закрыть. Вхідні данні взяти з таблиці № 2 згідно варіанту, таблиці №1.

11. Визначити товар, від реалізації якого отриманий максимальний прибуток, використовуючи умовне форматування. Для цього попередньо виділіть потрібний діапазон (тобто діапазон значень поля Прибуток від продажу) і виберіть в меню Формат – Условное форматирование. У вікні Условное форматирование з першого списку виберіть значение, з другого - равно, а у третьому полі введіть =МАКС() і виділіть необхідний діапазон клітинок, який після виділення автоматично запишеться в дужках. Потім клацніть кнопку Формат…і у вікні Формат Ячеек виберіть вкладку Вид, де задайте колір заливання комірки з максимальним значенням прибутку.

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

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

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

13. Додайте в кінець таблиці новий рядок, де визначте підсумкові значення полів (Поступило, Собівартість, Продано, Відпускна ціна і далі). Для підрахунку суми використайте кнопку Автосумма на панелі інструментів стандартна, попередньо виділивши необхідний діапазон комірок поля.

14. Порівняйте сумарне значення поля Ціна1 з сумарними витратами на виробництво продукції, що продалася, які ви підрахували раніше, використовуючи функцію СУММПРОИЗВ для полів Продано й Собівартість. Також порівняйте сумарне значення поля Ціна2 із значенням суми, що надійшла від реалізації продукції, яке ви отримали раніше, використовуючи функцію СУММПРОИЗВ для полів Продано й Відпускна ціна. Зробіть заливання клітинок з однаковими значеннями одним і тим кольором за допомогою меню Формат – Ячейки…,у вікні діалогу Формат Ячеек виберіть вкладку Вид.

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

П ісля виконаних дій та розрахунків база даних має набути такого вигляду:

16. Задайте границі таблиці, попередньо виділивши таблицю бази даних. Використайте меню Формат – Ячейки…,у вікні діалогу Формат Ячеек виберіть вкладку Граница або скористайтеся кнопкою Границы панелі форматування.

17. Скопіюйте тільки саму таблицю бази даних (без добавлених рядків з підрахованою сумою та середніми значеннями) і вставте її на другому листі.

18. Відформатуйте скопійовану таблицю бази даних, використовуючи один з видів автоформатів у діалоговому вікні Автоформат (меню Формат – Автоформат…).

19. Перейменуйте другий лист, давши ім'я –“БАЗА №2”.

Збережіть робочу книгу на дискеті. Надрукуйте таблицю.