Використання надбудов

Розглянемо приклад. Деяка установа надає послуги виду 1 та виду 2. Кожна послуга виду 1 дає прибуток 60 грн., а на її надання витрачається 1 одиниця ресурсу 1, 0.5 одиниць ресурсу 2 і 1 одна одиниця ресурсу 3. Кожна послуга виду 2 дає прибуток 160 грн., а на її надання витрачається 2 одиниці ресурсу 1, 0.4 одиниці ресурсу 2 і 4 одиниці ресурсу 3. Ресурси установи обмежені: щотижня вона може отримувати від своїх постачальників 130 одиниць ресурсу 1, 50 одиниць ресурсу 2 і 220 одиниць ресурсу 3. Наведені дані зафіксуємо у таблиці.

Послуги	Ресурс 1	Ресурс 2	Ресурс 3	Прибуток
Вид 1		0.5
Вид 2		0.4
Ресурси

Треба визначити, в якій кількості спланувати надання послуг виду 1 і виду 2, щоб прибуток був максимальним.

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

Сформулюємо задачу математично. Позначимо через і заплановану до надання кількість послуг 1 і послуг 2 відповідно. Обмеженість ресурсів фірми означає, що мають задовольнятись такі нерівності: . Крім того, за змістом задачі її змінні мають бути невід’ємними, тобто: і . Вони також мають бути цілочисельними. Прибуток від запланованих до виробництва стільців та крісел визначається за формулою . Отже, оптимальний план фірми, тобто числа і мають бути такими, щоб задовольнялись всі наведені нерівності, а прибуток F досягав максимального значення.

Один із допустимих планів цієї задачі такий: і . При цьому: , тобто ресурсу 3 вистачає із запасом, а ресурси 1 і 2 використовуються повністю. Прибуток при цьому складає грн. Указаний план є допустимим, але він не є оптимальним. Дійсно, для іншого плану і отримуємо: . При цьому ресурсу 2 вистачає із запасом, ресурси 1 і 3 використовуються повністю, а прибуток складає грн., що суттєво краще у порівнянні з попереднім.

Отже, розв’язок задачі розподілу ресурсів має багатоваріантний характер.

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

Порядок розв’язування задачі лінійного програмування:

¨ Установити надбудову Поиск решения. Для цього виконати команду Сервис-Надстройки.... Внаслідок цього з’являється вікно Надстройки. В цьому вікні у прокручуваному списку Список надстроек: слід установити прапорець на пункті Поиск решения і натиснути кнопку OK.

¨ В робочому листі Excel створити наступну форму:

	A	B
	Змінні:
	x1 =
	x2 =
	Максимальне значення:
	Обмеження:
	№1:
	№2:
	№3:

¨ В комірки B8, B9 і B10 внести такі формули: “=B2+2*B3”, “=0.5*B2+0.4*B3”, “=B2+4*B3”.

¨ В комірку B5 внести формулу цільової функції: «=60*B2+160*B3».

¨ Звернутися до надбудови Поиск решения з метою розв’язування задачі. Для цього виконати команду Сервис-Поиск решения.... Після цього на екрані з’являється вікно Поиск решения, в яке здійснюється внесення задачі лінійного програмування.

¨ В полі Установить целевую ячеку: надрукувати $B$5.

¨ Установити відмітку на пункті Равной: Максимальному значению.

¨ В полі Изменяя ячейки: надрукувати $B$2:$B$3.

¨ Ввести перше обмеження. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$8, в полі Ограничение: вибрати значок “<=” і надрукувати значення 130. Натиснути кнопку ОК.

¨ Аналогічно ввести друге і третє обмеження.

¨ Ввести умови невід’ємності змінних. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$2:$B$3, в полі Ограничение: вибрати значок «>=» і надрукувати значення 0. Натиснути кнопку OK.

¨ Ввести умови цілочисельності змінних. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$2:$B$3, в полі Ограничение: вибрати пункт цел. Натиснути кнопку OK.

¨ Натиснути кнопку Параметри..., установити відмітку на пункті Линейная модель і натиснути кнопку OK.

¨ Задачу ЛП повністю підготовлено. Натиснути у вікні Поиск решения кнопку Выполнить.

¨ З’являється вікно Результаты поиска решения, в якому повідомляється, що Решение найдено. Відмітити пункт Сохранить найденное решение і натиснути кнопку OK. На листі електронної таблиці бачимо оптимальний план.

МІНІСТЕРСТВО НАДЗВИЧАЙНИХ СИТУАЦІЙ УКРАЇНИ

Академія пожежної безпекиім.Героїв Чорнобиля

Факультет пожежно-профілактичної діяльності

 

Кафедра вищої математики та інформаційних технологій

 

ЗАТВЕРДЖУЮ

Начальник кафедри

ВМ та ІТ

к.ф-м.н., доц.,

полковник с.ц.з.

_______ І.П. Частоколенко

“___”______2011р.

 

Навчальна дисципліна:Інформатика та комп’ютерна техніка

1-й курс (стаціонар, курсанти).

 

ЛЕКЦІЯ

 

Тема № 4. Табличний процесор Microsoft Excel.

Час: 54, з них 10 години лекцій, 24 години практичних занять, 20 годин самостійної роботи

Тема лекції:№ 4.5 Робота з базами даних в MS Excel.

Навчальна мета: Розглянути теоретичний матеріал у відповідності з темою лекції.

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

Матеріально-методичне забезпечення: Дошка, крейда.

 

 

Розробив:

старший викладач

кафедри ВМ та ІТ О.М. Слободянюк

 

Лекція обговорена та схвалена на засіданні кафедри

Протокол №____від “__”_____________2011р.

Лекція 4.5. Робота з базами даних в MS Excel

1. Загальні положення

Табличний процесор Excel забезпечує, поряд із власне обробленням електронних таблиць-аркушів, формування ділової графіки, створення, оброблення і підтримку несклад­них, але великих баз табличних даних.

База даних — певний набір даних, призначений для зберігання ін­формації з якоїсь предметної сфери.

У цьому плані Excel можна розглядати як нескладну СУБД реляційного типу. Реляційні бази даних подають­ся у формі звичайних двовимірних електронних таблиць-відношень (relation); в останніх версіях Excel їх назива­ють просто списками. База даних (список) в Excel — той самий робочий аркуш із його стовпцями і рядками, текс­том, числами й іншими елементами, але сформований за певними правилами.

Структурними компонентами будь-якої бази даних є записи, поля і заголовний рядок.

Запис — вичерпний опис конкретного об'єкта, що міс­тить низку різнотипних, логічно пов'язаних між собою по­лів, наприклад:

10 АлмазовО. М. 1968 Інженер Харків 86

Кожний запис — це рядок бази даних. Усі записи ма­ють однакову фіксовану довжину, їх кількість, у принципі, не обмежена.

Поле— певна характеристика об'єкта або окремий еле­мент даних у запису. Кожне поле має унікальне ім'я, йому відповідають дані одного стовпця. Для ефективного пошу­ку, селекції та сортування даних бази доцільно записи по­діляти по полях, що містять найдрібніші елементи даних. Так, замість одного поля «Прізвище, ім'я, по батькові» кра­ще задати три: «Прізвище», «Ім'я» і «По батькові». Єдину адресу клієнта можна розділити на поля «Поштовий ін­декс», «Місто», «Вулиця», «Номер будинку» і т. п.

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

Загальний вигляд найпростішої бази даних Excel з ім'ям «Замовлення» зображено на рис. 2.104.

Рис. 2.104. Загальний вигляд бази даних «Замовлення»

Загальні правила формування бази даних:

§ у базі даних, як правило, розміщуються тільки поля початкових даних. Поля, що обчислюються, шапку документа і підсумкові рядки до бази не включають, їх формують на етапі створення документа-звіту;

§ заголовний рядок має відрізнятися від рядків-записів кольором, шрифтом або обрамленням. У разі моно­хромного друку його краще взяти в рамку;

§ після заголовного рядка мають іти рядки записів; розділяти їх навіть порожніми рядками не рекомен­дується;

§ в однойменних полях записів розміщують дані тіль­ки одного типу: числа, тексти або дати. Не треба розпочинати поля з пропусків;

§ текстові дані краще розпочинати з великих літер, розширюючи таким чином можливості пошуку та сортування даних;

§ фон сусідніх записів доцільно чергувати: при цьому поліпшується сприйняття даних користувачем;

§ на одному аркуші бажано розміщувати тільки од­ну базу даних. Інші дані краще розташувати на ін­ших аркушах. Поєднання різнорідних баз даних мо­жливе, якщо відокремити їх порожніми рядками і стовпцями.

Формати файлів баз даних

Базу даних, створену засобами Excel, можна зберегти не тільки у стандартному форматі книги (.xls), а й у звичному для баз даних форматі, наприклад, dbf-файлі. У цьому випадку програма коректно визначає типи полів (текст, числове, дата) та їхні довжини (для тексту) на підставі на­явних даних. Для створення dbf-файлу достатньо вибрати значення DBF 4 (dBASE IV) (*.dbf) у полі Тип файлу діалогового вікна Збереження документа, яке викликається командою Файл/Зберегти як...

Excel дає змогу також працювати з dbf-файлами та файлами баз да­них системи керування базами даних (СКБД) Microsoft Office Access. Для відкриття таких файлів у полі Тип файлу діалогового вікна Відкрит­тя документа треба, відповідно, вибрати значення Файли dBase (*.dbf) або Бази даних Access (*.mdb; *.mdb). Іншим способом відкриття таких файлів є використання команди Дані/Імпорт зовнішніх даних/Імпорт даних....

Для побудови запитів до баз даних, створених спеціалізованими СКБД (наприклад, SQL Server, MS Access, Paradox, FoxPro), в Excel є спеціаль­ний засіб - програма MS Querry, яку активізують командою Дані/Імпорт зовнішніх даних/Створити запит.... Перевага використання запитів по­лягає в тому, що в Excel переноситься лише потрібна (відфільтрована) частина записів з бази даних.