Всі функції мають однаковий формат запису і включають ім'я функції і список розділених комою аргументів, що знаходяться у круглих дужках.
Будь-яка функція виконується або внесенням її тексту з клавіатури у активну комірку, або її викликом з активної комірки за допомогою Майстра функцій.
Майстер функцій — це спеціальна програма, за допомогою якої можна вибрати потрібну функцію і виконати її, вказавши всі потрібні параметри.
Майстер функцій можна завантажити через пункт меню
Вставка/Функція..., або за допомогою fx кнопки на панелі інструментів.
Приклади функцій:
SUM (список) – статистична функція визначення суми всіх числових значень у списку, який може складатися з адрес комірок і блоків, а також числових значень.
AVERAGE (список) – статистична функція визначення середнього арифметичного значення всіх перерахованих у списку величин.
МАХ (список) – статистична функція, результатом якої є максимальне значення у вказаному списку.
IF (умова, істина, помилка) – логічна функція, що перевіряє на істинність задану логічну умову.
31. Використання і алгоритм роботи функції ЕСЛИ(арг_лог, арг1, арг2).
Під функцією розуміють залежність однієї змінної (у) від іншої (х) або кілька змінних (х1, х2, ..., хn). Причому кожному набору значень змінних х1, х2, ..., хn буде відповідати єдине значення певного типу залежностей змінної у. Функції вводять у таблицю у склад формул або окремо.
Загальний вигляд функції:
=ім'я функції(параметр/и)
- логічні функції використовуються для побудови логічних виразів, результат яких залежить від того, чи є істиною умова, що перевіряється.
=ЕСЛИ(умова;дія 1 ;дія2)
Виконання діїї, коли умова істинна, або ді'й, коли умова хибна
=ИЛИ(умова1 ;умова2;... ;умова30)
ИСТИНА, якщо хоча б одна з умов виконується, або ЛОЖЬ, якщо жодна з умов не виконується
=И(умова1 ;умова2;... ;умова30)
ИСТИНА, якщо усі умови виконуються, або
ЛОЖЬ, якщо хоча б одна з умов не виконується
32. Методика знаходження коренів кубічного рівняння. Навести приклад. 35. Використання фінансових функцій в електронних таблицях(перерахуйте задачі, які розглядалися на лекціях та у лабораторних роботах). 42.Поняття динамічного набору даних в ACCESS 2000
33. Дії з матрицями: множення матриць, знаходження оберненої матриці. Методика розв”язання системи двох лінійних рівнянь з двома невідомими. Навести приклад.
Операції з матрицями
Для операцій з матрицями в Excel застосовують такі засоби:
3. Використання масивів.
4. Убудовані функції: ТРАНСП, МУМНОЖ, МОПРЕД, МОБР.
Масив виникає як безупинна прямокутна ділянка даних, що є посиланням у зазначених вище операціях. Формули, що обробляють масив, відрізняються від звичайних так:
4. Перед уведенням формули маркірується вся ділянка масиву.
5. Завершується введення таких формул натисканням Shift +
+ Ctrl + Enter.
6. Формула взята у фігурні дужки і повторюється в усіх клітинках ділянки.
Функції СУММ() і ЕСЛИ() можуть оперувати з масивами.
Використання масивів у функціях СУММ() і ЕСЛИ() дає можливість розрахувати суму комірок із критерієм, заданим діапазоном значень. Наприклад, маючи стовпець з датами і відповідний йому стовпець значень, можна за допомогою однієї формули знайти суму всіх чисел у заданому діапазоні дат. Ще один приклад. До речі, також можна використовувати функції типу БДСУММ(), але це не завжди зручно.
34. Ряди даних: увід послідовних рядів даних, автозаповнення. Поняття списків та їх створення. Форматування тексту в EXCEL.
Уведення данихКлітинки робочого листа можуть містити різноманітну інформацію: текст, числові значення, дати, час, формули. Щоб помістити дані у визначену клітинку робочого листа, необхідно вибрати потрібну клітинку, ввести дані та підтвердити уведення. Excel при цьому автоматично розпізнає тип даних, що вводяться. Значення, що вводиться, відображається у рядку формул. Крім того, у цьому ж рядку з'являються розглянуті раніше кнопки для опрацювання вмісту клітинки.Уведення даних завершується натисканням клавіші
уникати порожніх рядків і стовпчиків усередині списку. В ідеалі на робочому листі не повинно бути нічого, крім списку. Якщо це неможливо, то список варто відокремити від інших даних принаймні одним порожнім рядком та одним порожнім стовпчиком. Максимальний розмір списку обмежений хіба що розміром робочого листа.Excel розпізнає списки автоматично. Перед виконанням дій зі списком достатньоктивізувати будь-яку клітинку усередині списку. Вміст першого рядка Excel вважає іменами полів і не опрацьовує їх, як інші дані. Якщо перед здійсненням обробки списку виділити окрему частину списку, то Excel сприйме як список тільки виділений діапазон клітинок.Діапазону клітинок, що містить список, можна дати ім'я. Присвоєння імені дозволить простіше переходити до списку у робочій книзі. Якщо діапазону клітинок списку присвоєно ім’я «База_данных», то Excel інтерпретує вміст кожної клітинки першого рядка, як ім’я окремого поля. Щоб виділити рядок з іменами полів використовуйте шрифти та рамки. Не варто вставляти окремий рядок із дефісів або знаків рівності, тому що Excel може сприйняти таке “підкреслення” за дані.Для роботи з великими списками, дані яких не поміщаються одночасно у робочому вікні, використовуйте розглянуту раніше можливість фіксуванняобластей (команда «Окно\Закрепить области»). Перший рядок списку з іменами полів буде зафіксований на екрані та завжди представлений при прокручуванні списку.
Як бачимо, створити список можна, використовуючи традиційні засоби. Задавши імена полів і порядок їхнього розташування, визначивши ширину стовпчиків і параметри форматування (насамперед для дат, часу, числових значень), можна вводити записи списку. Якщо для стовпчика з “вузькими” даними потрібно зазначити довге ім'я поля, представте ім'я у декількох рядках клітинки. Список може також містити значення полів, які отримані у результаті обчислення формул
Фінансові функції дозволяють враховувати різну цінність грошей в залежності від часу.
Фінансові функції можна застосовувати в тих задачах, де діють час і проценти.
36. Як побудувати математичну модель задачі? На які питання потрібно дати відповідь, щоб математична модель була побудована правильно? Наведіть приклади оптимізаційних задач з життя вашої фірми або особистого життя.
Побудова матем. Моделі
Процес побудови моделі потрібно почати з пошуку відповіді на слідчі 3 питання:
1. для визначення яких величин будується модель?
2. в чому полягає ціль, для досягнення якої змножини всіх допустимих значень змінних вибираютьсяобтимальні?
3. яким обмеженням повинні задовольняти невідомі?
37. Сбалансовані та несбалансовані транспортні задачі. Дати їм визначення. Як звести несбалансовані задачі до сбалансованих?
38. Задача про призначення: її суть та формулювання. Методика розв”язування таких задач. Дати детальний опис на прикладі.
39. Поняття структуризації інформації. Від чого залежить рівень структуризації предметної області? Дати визначення бази даних. Навести приклади баз даних. Дати визначення систем керування базами даних.
База даних – це сукупність даних, яким властива структурованість і взаємопов’язаність, а також належність від прикладних програм.
40. Правила задання умов у запитах. Як створювати запити для проведення статистичних розрахунків. Узагальнюючі запити для груп записів. Вставляння в запити обчислювальних(розрахункових) полів.
Створення запиту за допомогою майстра. У меню FILE вибрати команду NEW, натиснути на селекторну кнопку QUERY, а потім — WIZARD. У вікні, що з’явилося (рис. 10.29), вибрати Query Wizard.
На першому кроці можна вибрати поля (з кількох таблиць), які будуть включені у запит (рис. 10.30).
На другому кроці встановлюється відношення між таблицями. Після вибору полів, за якими встановлюється зв’язок, необхідно натиснути на кнопку ADD, для вилучення зв’язку — REMOVE.
На третьому кроці задається фільтр для відбору записів.
На жаль, у критерії не можна використовувати функції, а тільки конкретні значення. Так у якості критерієв задано, що вибирати треба записи за 10-й місяць та код виробу рівний 22.
Четвертий крок, дозволяє задати порядок сортування за трьома полями. Треба вибрати поля та натиснути на кнопку ADD. Кнопки Ascending та Descending дають змогу вибрати порядок сортування: за зростанням або зменшенням значень обраних полів.
П’ятий крок — це закінчення створення запиту .Натиснувши на кнопку Preview, можна переглянути результат роботи запиту. Якщо у ньому щось не влаштовує, тоді натисненням на кнопку BACK можна повернутися на будь-який крок і внести зміни. Для закінчення створення запиту треба натиснути на кнопку FINISH або просто ENTER, попередньо вибравши один з трьох варіантів: зберегти запит та повернутися у вікно COMMAND (Save query); зберегти запит та виконати його (Save and run it); зберегти запит та використати конструктор запитів Query Designer для його модифікації (Save query and modify it). Потім необхідно ввести ім’я файла. На диску буде створено файл із розширенням QPR.
запиту.qpr>, або меню PROGRAM>DO та вибирається ім’я запиту. Редагування запиту відбувається тільки за допомогою конструктора QUERY DESIGNER одним з двох шляхів: командою MODIFY QUERY <ім’я запиту> (Наприклад, modify query query1); меню FILE>OPEN і вибрати тип файлів Query та ім’я запиту.
Створення запиту за допомогою конструктора запитів можна почати двома методами: увести команду CREATE QUERY <ім’я файла>; у меню FILE вибрати команду NEW, натиснути на селекторну кнопку QUERY, а потім — на кнопку NEW FILE і набрати ім’я файла. У вікні, що з’явилося ,вибрати потрібну базу даних, а далі — файли.
Для додавання вільних таблиць натиснути на кнопку OTHER. Після додавання другої таблиці на екрані з’явиться блок діалогу JOIN CONDITION ,в якому визначається спосіб зв’язку таблиць. Список ліворуч дозволяє вибрати поле із першої таблиці, список праворуч — із другої. Посередині за допомогою селекторних кнопок визначається тип відношення між полями:
• Inner join — «внутрішнє поєднання», містить тільки ті записи з обох таблиць, які відповідають умові поєднання;
• Left outer join — ліве зовнішнє поєднання, містить усі записи таблиці зліва і записи, що задовольняють умову поєднання праворуч;
• Right outer join — праве зовнішнє поєднання, містить усі записи таблиці праворуч і записи, що задовольняють умову поєднання ліворуч;
• Full outer join — повне зовнішнє поєднання, містить всі записи з обох таблиць, для яких виконується умова поєднання.
Вкладинка FIELDS призначена для вибору полів та виразів, значення яких будуть виводитись у запиті.
Для додавання у запит виразу його потрібно ввести у вікно Functions та Expressions або натиснути на кнопку, що розташована поруч. Завантажиться будівник виразів, який дозволить увести вираз у більш зручній формі. Потім потрібно натиснути на кнопку ADD для додавання виразу у список вибраних полів. Наприклад, для розрахунку потреби в деталях у запит уводиться такий вираз:
Вкладинка JOIN (рис. 10.39) призначена для задання критеріїв поєднання записів із різних таблиць. Якщо необхідно поєднати таблиці за кількома полями, у списку LOGICAL треба задати AND та у наступному рядку ввести критерій поєднання по другому полю.
Вкладинка FILTER (рис. 10.40) дозволяє, задати умову відбирання поєднаних записів. Наприклад, з таблиць ПЛАН_ВИР та СКЛАД_ВИР вибрати записи, в яких потреба в деталях більша, ніж 200 000 у місяцях з 7 по 10. Поле потреба відсутнє в таблицях, тому у списку FIELD NAME треба вибрати EXPRESSION та увести формулу ПЛАН_ВИР.КІЛЬК_ВИР*СКЛАД_ВИР.КІЛЬК_ДЕТ. Для задання місяця використовується операція BETWEEN, яка дозволяє задати місяці з 7 по 10.
Вкладинка ORDER BY дозволяє задати порядок виведення записів у результаті запиту. На рис. 10.41 в область ORDERING CRITERIA додані поля, за якими буде відбуватися впорядкування записів.
Вкладинка GROUP BY) дозволяє створювати підсумкові запити щодо заданої групи. Результат запиту містить по одному запису на кожну групу. Щоб створити такий запит, необхідно у вкладинці FIELDS у вікні EXPRESSION увести формулу з використанням таких функцій:
• COUNT(<ім’я поля>) — кількість записів, які мають не порожнє поле;
• SUM(<вираз>) — сума всіх значень виразу;
• AVG(<вираз>) — середнє значення всіх значень виразу;
• MIN(<вираз>) — найменше значення з усіх значень виразу;
• MAX(<вираз>) — найбільше значення з усіх значень виразу.
Наприклад, необхідно визначити середню кількість виробів та загальну кількість деталей по кожному місяцю. Для цього у вкладинці FIELDS: обирається поле МІСЯЦЬ; створюються поля, що обчислюються:
AVG(ПЛАН.КІЛЬК_ВИР); SUM(СКЛАД.КІЛЬК_ДЕТ), і теж додаються до списку обраних полів (рис. 10.42). У вкладинці GROUP BY треба задати групу, за якою будуть виводитися підсумки. Для даного прикладу це поле місяць..
Кнопка HAVING використовується для визначення умови
фільтрації, щоб обмежити записи, які включені у групування.
Виведення результатів запиту за замовчанням відбувається у вікно BROWSE. Вибравши у меню QUERY>QUERY DES-TINATION можна визначити напрямок виведення результа-
тів. На екрані з’явиться блок діалогу QUERY DESTINATION .
Кнопка BROWSE дозволяє переглянути результат запиту у вікні BROWSE. Інші кнопки для виведення результату запиту визначають такі імена: CURSOR — курсор (тимчасова таблиця); TABLE — файл-dbf; GRAPH — діаграма, параметри якої необхідно задати; SCREEN визначає екран (виведення відбувається аналогічно команді LIST), принтер або текстовий файл; кнопка REPORT — файл-frx (звіту) або завантажує майстер звітів; кнопка LABEL — файла-lbx (етикетки).
Виконання запиту в процесі його створення в режимі конструктора відбувається натисненням на таку кнопку панелі інстру-
ментів: або меню QUERY>RUN QUERY.
Запит можна побачити у вигляді SQL, якщо після виконання команди RUN QUERY натиснути на кнопку SQL або вибрати меню QUERY>VIEW SQL. Мовою SQL запит може мати такий вигляд:
SELECT ПЛАН_ВИР.Місяць,ПЛАН_ВИР.Код_виробу, ПЛАН_ВИР.Кільк_вир;
СКЛАД_ВИР.Код_деталі,СКЛАД_ВИР.Кільк_дет;
FROM ПОТРЕБА!ПЛАН_ВИР ПЛАН_ВИР INNER JOIN ПОТРЕБА!СКЛАД_ВИР СКЛАД_ВИР
ON ПЛАН_ВИР.КОД_ВИРОБУ = СКЛАД_ВИР.КОД_ВИРОБУ;
WHERE План_вир.кільк_вир*Склад_вир.кільк_дет > 200000; AND План_вир.місяць BETWEEN 7 AND 10;
ORDER BY План_вир.місяць, План_вир.код_виробу, Склад_вир.код_деталі
41. Алгоритм створення запитів з двух таблиць в ACCESS 2000. Алгоритм запису виразів в обчислювальні поля в запитах та звітах.
43. Проектування бази даних: запис, структура запису, поле запису та його реквізити ( ім"я, тип, розмірність)
Проектування бази даних
Перед тим як створювати таблиці, форми та інші об’єкти, потрібно задати структуру бази даних. Добра структура бази даних є основою для створення адекватної вимогам, ефективної бази даних. Сам процес проектування бази даних являє собою складний процес проектування відображення опису предметної області у схему внутрішньої моделі даних. Перебіг цього процесу є послідовністю більш простих процесів проектування менш складних відображень. Ця послідовність у процесі проектування весь час уточнюється, вдосконалюється таким чином, щоб були визначені об’єкти, їх властивості та зв’язки, які будуть потрібні майбутнім користувачам системи.
Пропонуємо майбутнім користувачам систем управління базами даних два підходи, два варіанти проектування баз даних. Перший варіант широко відомий, бо він запропонований фірмою Microsoft. Другий варіант відображає практичний досвід проектування, і за основу взято варіант, надрукований у «ComputerWorld — Moscow» за 1996 рік.
Варіант 1. Етапи проектування бази даних
Нижче наведені основні етапи проектування бази даних:
1. Визначення мети створення бази даних.
2. Визначення таблиць, що їх повинна містити база даних.
3. Визначення необхідних у таблиці полів.
4. Завдання індивідуального значення кожному полю.
5. Визначення зв’язків між таблицями.
6. Відновлення структури бази даних.
7. Додавання даних і створення запитів, форм, звітів та інших об’єктів бази даних.
8. Використання засобів аналізу в СУБД.
Розглянемо ці етапи дещо детальніше.
1. Визначення мети створення бази даних. На першому етапі проектування бази даних необхідно визначити мету створення бази даних, основні її функції та інформацію, яку вона повинна містити. Тобто потрібно визначити основні теми таблиць бази даних та інформацію, що міститимуть поля таблиць.
База даних має відповідати вимогам тих, хто безпосередньо з нею працюватиме. Для цього потрібно визначити теми, які по-
винна покривати база даних, звіти, які вона має видавати, проаналізувати форми, що у даний момент використовуються для запису даних, порівняти створювану базу даних із добре спроектованою, подібною їй базою.
2. Визначення таблиць, які повинні містити база даних. Одним із найскладніших етапів у процесі проектування бази даних є розробка таблиць, тому що результати, які повинна видавати база даних (звіти, вихідні форми тощо), не завжди дають повне уявлення про структуру таблиці. У разі проектування таблиць зовсім не обов’язково використовувати СУБД. Спочатку краще розробити структуру на папері. Отже, у разі проектування таблиць слід керуватися такими основними принципами:
— інформація в таблиці не повинна дублюватися. Не повинно бути повторень і між таблицями. Коли певна інформація зберігається лише в одній таблиці, то і змінювати її доведеться лише в одному місці. Це робить роботу ефективнішою, а також виключає можливість розбіжності інформації в різних таблицях. Наприклад, в одній таблиці мають міститися адреси й телефони клієнтів;
