Б136

Формули

Формулою в Excel називається послідовність символів, що починається зі знака рівності =. У цю послідовність символів можуть входити постійні значення, посилання на комірки, імена чи функції оператора. Результатом роботи формули є нове значення, що виводиться як результат обчислення формули за вже наявними даними.

Якщо значення в комірках, на які є посилання у формулах, міняються, то результат зміниться автоматично.

Як приклад введемо формули, що обчислюють корені квадратного тричлена: ax2 + bx + c = 0. Вони введені в комірки A2 і A3 та мають такий вигляд:

= (–B1 + КОРЕНЬ(B1*B1 – 4*A1*C1))/2/A1;

= (–B1 – КОРЕНЬ(B1*B1 – 4*A1*C1))/2/A1.

У комірках A1, B1 і C1 перебувають значення коефіцієнтів a, b і с, відповідно. Якщо ви ввели значення коефіцієнтів a = 1, b = –5 і с = 6 (це означає, що в комірках A1, B1 і C1 записані числа 1, 5 і –6), то в комірках A2 і A3, де записані формули, ви одержите числа 2 і –3. Якщо ви зміните число в комірці A1 на –1, то в комірках з формулами ви одержите числа –6 і 1.

Використання посилань і імен

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

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

Ім’я — це ідентифікатор, що запам’ятовується і який можна використовувати для посилання на комірку, групу комірок чи значення у формулі. Створити ім’я для комірки можна в полі імені, чи через меню Вставка/Имя/Присвоить... Використання імен забезпечує такі переваги.

• Формули, що використовують імена, легше сприймаються і запам’ятовуються, ніж формули, що використовують посилання на комірки.

Наприклад, формула "=Активи–Пасиви" набагато зрозуміліша, ніж формула "=F6–D6".

• У разі зміни структури робочого аркуша досить оновити посилання лише в одному місці — у визначенні імен, і всі формули, що використовують ці імена, використовуватимуть коректні посилання.

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

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

• Excel автоматично створює імена на основі заголовків рядків і стовпців робочого аркуша. Докладна інформація про створення таких імен міститься в розділі «Бази даних».

Після того, як ім’я визначене, ви можете:

• замінити всі відповідні посилання цим ім’ям у всіх місцях робочого листа;

• наприклад, визначивши ім’я «Прибуток» як "=12", можна замінити всі посилання на комірку 12 ім’ям «Прибуток»;

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

Формула — це запис дій, які треба провести з вхідними даними для отримання потрібного результату. Для внесення формули треба активізувати комірку, де має розміщуватися формула, натиснути = (Дорівнює) або на клавіатурі, або мишкою ліворуч у рядку формул і заносити текст. Текст формули складається з адрес комірок із вхідними значеннями, поєднаних математичними, логічними або пунктуаційними знаками.

Якщо, наприклад необхідно скласти значення клітин А1 та В1 і вивести їх суму у клітині С1 встановіть рамку виділлення на клітину С1 і введіть формулу =В1+В2.

Зведення у ступінь ^

Арифметичні формули можуть також містити оператори порівняння: дорівнює (=), не рівно (< >), більше (>), менше (<), не більше (<=), не менше (>=). Результатом обчислення арифметичної формули є число.

Логічні формули можуть містити вказані оператори порівняння, а також спеціальні логічні оператори:

#NOT# - логічне заперечування “НІ”;

#AND# - логічне “І”;

#OR# - “АБО”.

Логічні формули визначають, вираз “істина” чи “помилка”. Виразу “істина” привласнюється числова величина 1, виразу “помилка” - 0.

Форматування даних – вибір форми представлення числових або символьних даних в комірці.

Змінювати формат можна у одній комірці або у цілому діапазоні. Виділяється комірка або діапазон комірок, вибирається пункт меню Формат/Ячейки або  права кнопка на виділеній комірці або діапазоні і пункт з динамічного меню Формат ячеек...

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

Вкладника Выравнивание використовується для вирівнювання даних по горизонталі та вертикалі (зона ліворуч угорі), для вибору кута розташування даних у комірці (зона праворуч), а покажчики, розташовані ліворуч внизу, дозволяють поєднувати комірки у суміжному діапазоні, розташовувати текст у комірках в декілька рядків та автоматично змінювати ширину стовпчиків, якщо дані не зміщуються у ньому.

Вкладника Шрифт дозволяє вибирати параметри шрифта для виділеного діапазону.

Вкладинка Граница пропонує оформлення виділеного діапазону рамками різного типу.

Вкладинка Вид опрацьовує оформлення тла діапазону різним кольором.

Вкладинка Защита використовується для захисту змісту комірок у діапазоні (спрацьовує тільки після захисту аркуша).

Робоча книга складається зпронумерованих аркушів і розміщених сіткою ліній. Імена аркушів відображаються на вкладках у нижній частині вікна книги над рядком стану.

Операції аркушами

Працюючи з кигами, ми можемо переходити з одного аркушу на інший.для цього потрібно клацнути мишкою на ярличку потрібного аркушу.той арку, що у даний момент відкритий у вікні Excel називається активним. Вкладка активного аркуша підсвічена яскравіше інших вкладок. Нова робоча книга в Excel 2000 містить за умовчанням три аркуші. Їхню кулькість можна збільшить. Клацнувши правою кнопкою миші по вкладці будь-якого аркуша й обрати команду ДОБАВИТЬ. Видаляється аркуш іншою командою – УДАЛИТЬ.

Більшість команд Excel оперують з активною коміркою або діапазоном комірок. Для виділення суміжного діапазону комірок слід за допомогою вказівника миші (утримуючи ліву кнопку миші натисненою) підсвітити потрібний прямокутник комірок. Для виділення колонки або рядка слід сумістити вказівник миші із заголовком колонки або номером рядка і клацнути лівою кнопкою миші. Для виділення несуміжного діапазону комірок слід, утримуючи натиснутим клавіш [Ctrl], пересувати вказівник миші по комірках, які потрібно виділити. Виділити всі комірки можна за допомогою команди Правка/ Виділити все.

Комірки виділеного діапазону відображаються інверсним кольором, за винятком активної комірки, яка зберігає звичайний колір. Для зняття виділення слід клацнути мишею поза виділеним діапазоном. Редагування діапазону комірок. Виділений фрагмент можна вилучити, очистити, вставити, перемістити, скопіювати. Для цього використовують буфер обміну. Робота команд Правка/Копіювати, Правка/Вирізати, Правка/ Вставити нічим не відрізняється від аналогічних команд буфера обміну середовища Windows. Перемістити або скопіювати діапазон комірок швидше і зручніше за допомогою миші. Переміщення — встановити вказівник миші на межі виділеного діапазону (вказівник набуває форми стрілки), натиснути ліву кнопку миші й відбуксувати комірки в потрібне місце. Копіювання відбувається аналогічно, але при цьому слід тримати натиснутим клавіш [Ctrl] (вказівник набуває форми стрілки з хрестиком).

Вилучення комірок відбувається за допомогою команди Правка/Вилучити, при цьому в діалоговому вікні, яке з'явиться на екрані, слід вказати напрямок зсуву комірок таблиці. Очистка діапазону комірок відбувається за допомогою команди Правка/Очистити. На екрані з'являється діалогове вікно, у якому слід вказати, що потрібно очистити: формат, значення, примітку. У формулах можуть використовуватися адреси окремих комірок, а також посилання на діапазон комірок. Вказуючи діапазон комірок, задається адреса верхньої лівої клітинки й адреса нижньої правої клітинки, між ними ставиться двокрапка.

Побудова діаграм

Для побудови діаграми виконаємо команду Вставка/ Диаграмма чи натиснемо кнопку Мастер диаграмм.

Мітками рядків і стовпців називаються заголовки рядків і стовпців. Якщо ви не включаєте мітки рядків у ділянку побудови діаграми, то на 4-му кроці побудови діаграми потрібно вказати, що під мітки рядків виділяється 0 рядків.

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

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

Лінії тренда можна проводити на гістограмах, графіках, лінійчатих і XY-крапкових діаграмах.

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

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

Розглянемо побудову діаграм на прикладі даних, наведених у табл. 8.3 і 8.4. Будь-яка діаграма може будуватися на підставі таблиць. Побудуємо кругову діаграму «Валового доходу». Кругова діаграма будується на одному ряді даних, значення елементів ряду відображаються як сектори діаграми (величина сектора залежить від величини елемента ряду). В нашому прикладі потрібно побудувати кругову діаграму по валовому доходу. Причому ряд даних має містити всі цифри доходу, за винятком загальної суми. Така діаграма буде заготовкою для відображення діаграми, що динамічно змінюється залежно від установленого на таблиці фільтра.

Процедура створення діаграми.

• Запустити Майстер діаграм.

• Указати місце та розмір майбутньої діаграми за допомогою курсора, що має вигляд.

• У першому кроці майстра вказати діапазони комірок: 5:23; 5:23, але краще виділити даний діапазон заздалегідь, тоді він автоматично вказуватиметься в запиті на введення діапазону комірок.

• На наступних кроках вибирається круговий тип діаграми та її підтип.

• Потім установлюються параметри, що визначають: яка частина діапазону буде відображена як дані, а яка — як мітки сегментів і назви.

Порядок побудови діаграм у різних версіях Excel дещо відрізняється (зміст залишається той самий, діалоги різні).

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

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

Деякі функції в Excel

Нижче подано короткий перелік функцій, що становлять певний інтерес і не зовсім зрозумілі з першого погляду. Повніший список функцій див. у додатку до даного посібника «Функції Microsoft Excel».

Математичні функції

Тригонометричні функції та функції, що працюють з кутами.

COS() і SIN() — відповідають своєму написанню.

ACOS() і ASIN() — відповідно arccos і arcsin.

TAN(), ATAN() — tg і arctg.

ATAN2() — arctg для заданих координат x і y.

ГРАДУСЫ() — перетворити радіани в градуси.

РАДИАНЫ() — перетворити градуси в радіани.

ПИ() — .

Логарифми і статечні функції.

LN(), LOG10() і LOG() — натуральний, десятковий і логарифм із довільною підставою.

EXP() і СТЕПЕНЬ() — коментарів не потребують.

COSH(), SINH() і TANH() — гіперболічні косинус, синус і тангенс.

ACOSH(), ASINH() і ATANH() — гіперболічні арккосинус, арксинус і арктангенс.

Функції округлення

ОКРВЕРХ(), ЧЕТН(), ОКРВНИЗ(), ЦЕЛОЕ(), ОКРУГЛТ(), НЕЧЕТ(), ОТБР(),ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ() — функції округлення з різними можливостями.

Операції з матрицями

Для операцій з матрицями в Excel застосовують такі засоби:

1. Використання масивів.

2. Убудовані функції: ТРАНСП, МУМНОЖ, МОПРЕД, МОБР.

Масив виникає як безупинна прямокутна ділянка даних, що є посиланням у зазначених вище операціях. Формули, що обробляють масив, відрізняються від звичайних так:

1. Перед уведенням формули маркірується вся ділянка масиву.

2. Завершується введення таких формул натисканням Shift + Ctrl + Enter.

3. Формула взята у фігурні дужки і повторюється в усіх клітинках ділянки.

Функції СУММ() і ЕСЛИ() можуть оперувати з масивами.

Використання масивів у функціях СУММ() і ЕСЛИ() дає можливість розрахувати суму комірок із критерієм, заданим діапазоном значень. Наприклад, маючи стовпець з датами і відповідний йому стовпець значень, можна за допомогою однієї формули знайти суму всіх чисел у заданому діапазоні дат. Ще один приклад. До речі, також можна використовувати функції типу БДСУММ(), але це не завжди зручно.

Як до дати додавати місяці

В Excel можна додавати до дати дні. Якщо необхідно додавати певну кількість місяців (наприклад одержати послідовність 5 січ, 5 лют, 5 бер і т. ін.) з огляду на число днів у місяці, потрібно використовувати функцію ДАТА(). Excel сам відстежуватиме число днів у місяці.

30. Поняття формули та функції в EXCEL, правила синтаксису при запису функцій. Увід і редагування формул (вставка функції у формулу, внесення змін у формулу, використання посилань).

Формула — це запис дій, які треба провести з вхідними даними для отримання потрібного результату. Для внесення формули треба активізувати комірку, де має розміщуватися формула, натиснути = (Дорівнює) або на клавіатурі, або мишкою ліворуч у рядку формул і заносити текст. Текст формули складається з адрес комірок із вхідними значеннями, поєднаних математичними, логічними або пунктуаційними знаками.

Якщо, наприклад необхідно скласти значення клітин А1 та В1 і вивести їх суму у клітині С1 встановіть рамку виділлення на клітину С1 і введіть формулу =В1+В2.

Логічні функції

Арифметичні формули можуть також містити оператори порівняння: дорівнює (=), не рівно (< >), більше (>), менше (<), не більше (<=), не менше (>=). Результатом обчислення арифметичної формули є число.

Логічні формули можуть містити вказані оператори порівняння, а також спеціальні логічні оператори:

#NOT# - логічне заперечування “НІ”;

#AND# - логічне “І”;

#OR# - “АБО”.

Логічні формули визначають, вираз “істина” чи “помилка”. Виразу “істина” привласнюється числова величина 1, виразу “помилка” - 0.

Форматування даних – вибір форми представлення числових або символьних даних в комірці.

Змінювати формат можна у одній комірці або у цілому діапазоні. Виділяється комірка або діапазон комірок, вибирається пункт меню Формат/Ячейки або права кнопка на виділеній комірці або діапазоні і пункт з динамічного меню Формат ячеек...

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

Вкладника Выравнивание використовується для вирівнювання даних по горизонталі та вертикалі (зона ліворуч угорі), для вибору кута розташування даних у комірці (зона праворуч), а покажчики, розташовані ліворуч внизу, дозволяють поєднувати комірки у суміжному діапазоні, розташовувати текст у комірках в декілька рядків та автоматично змінювати ширину стовпчиків, якщо дані не зміщуються у ньому.

Вкладника Шрифт дозволяє вибирати параметри шрифта для виділеного діапазону.

Вкладинка Граница пропонує оформлення виділеного діапазону рамками різного типу.

Вкладинка Вид опрацьовує оформлення тла діапазону різним кольором.

Вкладинка Защита використовується для захисту змісту комірок у діапазоні (спрацьовує тільки після захисту аркуша).

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

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

Загальний вигляд функції:

=ім'я функції(параметр/и).

У електронних таблицях можуть бути представлені такі види функцій:

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

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

- текстові функції виконують операції над текстовими рядками або послідовністю символів, обчислюючи довжину рядка, перетворюючи прописні літери у рядкові і т.п.;

- логічні функції використовуються для побудови логічних виразів, результат яких залежить від того, чи є істиною умова, що перевіряється;

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

- функції дати і часу та ін.

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

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

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

Майстер функцій можна завантажити через пункт меню Вставка/Функція..., або за допомогою fx кнопки на панелі інструментів.

Приклади функцій:

SUM (список) – статистична функція визначення суми всіх числових значень у списку, який може складатися з адрес комірок і блоків, а також числових значень.

AVERAGE (список) – статистична функція визначення середнього арифметичного значення всіх перерахованих у списку величин.

МАХ (список) – статистична функція, результатом якої є максимальне значення у вказаному списку.

IF (умова, істина, помилка) – логічна функція, що перевіряє на істинність задану логічну умову.

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

Загальний вигляд функції: =ім'я функції(параметр/и).

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

=ЕСЛИ(умова;дія 1 ;дія2).

Виконання діїї, коли умова істинна, або ді'й, коли умова хибна

=ИЛИ(умова1 ;умова2;... ;умова30)

ИСТИНА, якщо хоча б одна з умов виконується, або ЛОЖЬ, якщо жодна з умов не виконується

=И(умова1 ;умова2;... ;умова30)

ИСТИНА, якщо усі умови виконуються, або ЛОЖЬ, якщо хоча б одна з умов не виконується.

Операції з матрицями

Для операцій з матрицями в Excel застосовують такі засоби:

1. Використання масивів.

2. Убудовані функції: ТРАНСП, МУМНОЖ, МОПРЕД, МОБР.

Масив виникає як безупинна прямокутна ділянка даних, що є посиланням у зазначених вище операціях. Формули, що обробляють масив, відрізняються від звичайних так:

3. Перед уведенням формули маркірується вся ділянка масиву.

4. Завершується введення таких формул натисканням Shift + Ctrl + Enter.

5. Формула взята у фігурні дужки і повторюється в усіх клітинках ділянки.

Функції СУММ() і ЕСЛИ() можуть оперувати з масивами

Використання масивів у функціях СУММ() і ЕСЛИ() дає можливість розрахувати суму комірок із критерієм, заданим діапазоном значень. Наприклад, маючи стовпець з датами і відповідний йому стовпець значень, можна за допомогою однієї формули знайти суму всіх чисел у заданому діапазоні дат. Ще один приклад. До речі, також можна використовувати функції типу БДСУММ(), але це не завжди зручно.

Уведення даних

Клітинки робочого листа можуть містити різноманітну інформацію: текст, числові значення, дати, час, формули. Щоб помістити дані у визначену клітинку робочого листа, необхідно вибрати потрібну клітинку, ввести дані та підтвердити уведення. Excel при цьому автоматично розпізнає тип даних, що вводяться. Значення, що вводиться, відображається у рядку формул. Крім того, у цьому ж рядку з'являються розглянуті раніше кнопки для опрацювання вмісту клітинки.Уведення даних завершується натисканням клавіші або кнопки у рядку формул. Часто набагато зручніше для завершення уведення використовувати клавіші керування курсором. У цьому випадку після уведення поточна клітинка зміщається у відповідному напрямку.

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

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

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

Присвоєння імені дозволить простіше переходити до списку у робочій книзі. Якщо діапазону клітинок списку присвоєно ім’я «База_данных», то Excel інтерпретує вміст кожної клітинки першого рядка, як ім’я окремого поля. Щоб виділити рядок з іменами полів використовуйте шрифти та рамки. Не варто вставляти окремий рядок із дефісів або знаків рівності, тому що Excel може сприйняти таке “підкреслення” за дані.Для роботи з великими списками, дані яких не поміщаються одночасно у робочому вікні, використовуйте розглянуту раніше можливість фіксуванняобластей (команда «Окно\Закрепить области»). Перший рядок списку з іменами полів буде зафіксований на екрані та завжди представлений при прокручуванні списку.

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

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

Фінансові функції можна застосовувати в тих задачах, де діють час і проценти.

Побудова матем. Моделі

Процес побудови моделі потрібно почати з пошуку відповіді на слідчі 3 питання:

1. для визначення яких величин будується модель?

2. в чому полягає ціль, для досягнення якої змножини всіх допустимих значень змінних вибираютьсяобтимальні?

3. яким обмеженням повинні задовольняти невідомі?

Створення запиту за допомогою майстра. У меню FILE вибрати команду NEW, натиснути на селекторну кнопку QUERY, а потім — WIZARD. У вікні, що з’явилося, вибрати Query Wizard.

На першому кроці можна вибрати поля (з кількох таблиць), які будуть включені у запит.

На другому кроці встановлюється відношення між таблицями. Після вибору полів, за якими встановлюється зв’язок, необхідно натиснути на кнопку 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.

Редагування запиту відбувається тільки за допомогою конструктора 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 призначена для задання критеріїв поєднання записів із різних таблиць. Якщо необхідно поєднати таблиці за кількома полями, у списку LOGICAL треба задати AND та у наступному рядку ввести критерій поєднання по другому полю.

Вкладинка FILTER дозволяє, задати умову відбирання поєднаних записів. Наприклад, з таблиць ПЛАН_ВИР та СКЛАД_ВИР вибрати записи, в яких потреба в деталях більша, ніж 200 000 у місяцях з 7 по 10. Поле потреба відсутнє в таблицях, тому у списку FIELD NAME треба вибрати EXPRESSION та увести формулу ПЛАН_ВИР.КІЛЬК_ВИР*СКЛАД_ВИР.КІЛЬК_ДЕТ. Для задання місяця використовується операція BETWEEN, яка дозволяє задати місяці з 7 по 10.

Вкладинка ORDER BY дозволяє задати порядок виведення записів у результаті запиту. В область ORDERING CRITERIA додані поля, за якими буде відбуватися впорядкування записів.

Вкладинка GROUP BY) дозволяє створювати підсумкові запити щодо заданої групи. Результат запиту містить по одному запису на кожну групу. Щоб створити такий запит, необхідно у вкладинці FIELDS у вікні EXPRESSION увести формулу з використанням таких функцій:

• COUNT(<ім’я поля>) — кількість записів, які мають не порожнє поле;

• SUM(<вираз>) — сума всіх значень виразу;

• AVG(<вираз>) — середнє значення всіх значень виразу;

• MIN(<вираз>) — найменше значення з усіх значень виразу;

• MAX(<вираз>) — найбільше значення з усіх значень виразу.

Наприклад, необхідно визначити середню кількість виробів та загальну кількість деталей по кожному місяцю. Для цього у вкладинці FIELDS: обирається поле МІСЯЦЬ; створюються поля, що обчислюються:

AVG(ПЛАН.КІЛЬК_ВИР); SUM(СКЛАД.КІЛЬК_ДЕТ), і теж додаються до списку обраних полів. У вкладинці 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 План_вир.місяць, План_вир.код_виробу, Склад_вир.код_деталі.

Проектування бази даних

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

Пропонуємо майбутнім користувачам систем управління базами даних два підходи, два варіанти проектування баз даних. Перший варіант широко відомий, бо він запропонований фірмою Microsoft. Другий варіант відображає практичний досвід проектування, і за основу взято варіант, надрукований у «ComputerWorld — Moscow» за 1996 рік.

Варіант 1. Етапи проектування бази даних

Нижче наведені основні етапи проектування бази даних:

1. Визначення мети створення бази даних.

2. Визначення таблиць, що їх повинна містити база даних.

3. Визначення необхідних у таблиці полів.

4. Завдання індивідуального значення кожному полю.

5. Визначення зв’язків між таблицями.

6. Відновлення структури бази даних.

7. Додавання даних і створення запитів, форм, звітів та інших об’єктів бази даних.

8. Використання засобів аналізу в СУБД.

Розглянемо ці етапи дещо детальніше.

1. Визначення мети створення бази даних. На першому етапі проектування бази даних необхідно визначити мету створення бази даних, основні її функції та інформацію, яку вона повинна містити. Тобто потрібно визначити основні теми таблиць бази даних та інформацію, що міститимуть поля таблиць.

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

2. Визначення таблиць, які повинні містити база даних. Одним із найскладніших етапів у процесі проектування бази даних є розробка таблиць, тому що результати, які повинна видавати база даних (звіти, вихідні форми тощо), не завжди дають повне уявлення про структуру таблиці. У разі проектування таблиць зовсім не обов’язково використовувати СУБД. Спочатку краще розробити структуру на папері. Отже, у разі проектування таблиць слід керуватися такими основними принципами:

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

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

3. Визначення необхідних у таблиці полів. Кожна таблиця містить інформацію на окрему тему, а кожне поле в таблиці містить окремі дані по темі таблиці. Наприклад, у таблиці з даними про клієнта можуть бути поля з назвою компанії, адресою, містом, країною і номером телефону. Під час розробки полів для кожної таблиці необхідно пам’ятати:

— кожне поле має бути пов’язане з темою таблиці;

— не рекомендується включати до таблиці дані, що є результатом виразу;

— у таблиці має бути вся необхідна інформація;

— інформацію варто розбивати на найменші логічні одиниці (наприклад, поля «Ім’я» і «Прізвище», а не загальне поле «Ім’я»).

4. Задання індивідуального значення кожному полю. З тим, щоб СУБД могла зв’язати дані з різних таблиць, наприклад дані про клієнта і його замовлення, кожна таблиця повинна містити поле чи набір полів, що задаватимуть індивідуальне значення кожного запису в таблиці. Таке поле чи набір полів називають основним ключем.

5. Визначення зв’язків між таблицями. Після розподілу даних по таблицях і визначення ключових полів необхідно вибрати схему для зв’язку даних у різних таблицях. Для цього потрібно визначити зв’язки між таблицями. Бажано вивчати зв’язки між таблицями в уже існуючій базі даних. Для перегляду зв’язків у вибраній базі даних відкриваємо її і вибираємо відповідні команди.

6. Відновлення структури бази даних

Після проектування таблиць, полів і зв’язків необхідно ще раз переглянути структуру бази даних і виявити можливі недоліки. Бажано це зробити на даному етапі, поки таблиці не заповнені даними. Для перевірки необхідно створити кілька таблиць, визначити зв’язки між ними та ввести кілька записів у кожну таблицю, потім подивитися, чи відповідає база даних поставленим вимогам. Рекомендується також створити чернеткові вихідні форми та звіти й перевірити, чи видають вони необхідну інформацію. Крім того, необхідно виключити з таблиць усі можливі повторення даних.

7. Додавання даних і створення інших об’єктів бази даних. Якщо структури таблиць відповідають поставленим вимогам, то можна вводити всі дані. Потім можна створювати будь-які запити, форми, звіти, макроси та модулі.

8. Використання засобів аналізу в СУБД. Наприклад, у СУБД Microsoft Access є два інструменти для вдосконалення структури баз даних. Майстер аналізу таблиць досліджує таблицю, в разі потреби пропонує нову її структуру та зв’язки, а також переробляє її. Аналізатор швидкодії досліджує всю базу даних, дає рекомендації з її поліпшення, а також реалізує їх.

Варіант 2. Розробка проекту бази даних

1. Розробка логічної моделі даних. Логічні моделі використовуються розробниками баз даних для формального представлення інформаційних потреб виробництва, економіки, бізнесу тощо. Найрозповсюдженішою формою відображення цієї моделі слугують ER-діаграми. Основними поняттями ER-моделі є сутність, зв’язок та атрибут. Кожна з частин такої діаграми повідомляє дещо про структуру даних або про те, як ці дані співвідносяться з іншими.

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

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

2. Підготовка звіту про логічну модель. Для відстежування процесу проектування логічної моделі використовуються звіти.

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

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

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

5. Генерація схеми бази даних. Схема описує реалізацію бази даних з урахуванням специфіки конкретної СУБД. Схема може створюватися або мовою визначення даних (файли DDL), або при прямому зверненні до СУБД. Програмні продукти, які добре підтримують генерацію схеми, дають засоби контролю за генеруючими елементами схеми, що дає змогу зробити цей процес ітеративним. Варто шукати інструменти, які підключаються до нашої цільової СУБД і дають можливість переключатися між різними СУБД, мінімізуючи при цьому ручне редагування.

6. Супроводження розроблюваної моделі даних. Більшість баз даних протягом свого життєвого циклу еволюціонує. Для того, щоб спростити цей процес, рекомендується синхронно змінювати модель та базу даних. Варто звертати увагу на засоби синхронізації, утиліти керування версіями та захисту. За допомогою найзручніших у роботі інструментів можна переносити зміни в обидва боки: з моделі в схему, і навпаки. Якщо раніше замовник після здачі СУБД в експлуатацію відмовлявся від супроводження, то тепер, як правило, проектувальники супроводжують експлуатацію СУБД. Це накладає на них додаткову відповідальність за якість проектування, бо всі негаразди доводиться ліквідовувати їм самим.

7. Звернене проектування, що виходить з існуючої бази даних. Відтворення схеми існуючої бази даних служить кільком цілям. Воно дає змогу побудувати модель цієї бази даних, перенести існуючу базу даних з однієї СУБД на іншу, а також досить просто модифікувати схему бази даних, що функціонує. Ключовими параметрами для виконання такого завдання є точність та гнучкість. Ми повинні мати можливість задати елементи схеми, з якими працюватиме програма, й очікується, що внаслідок генерації схеми бази даних за відновленою моделлю має з’явитися тотожна копія початкової схеми.

Як бачимо, другий варіант окреслює загальніший підхід до проектування баз даних та враховує відносини з замовником проекту. Розглянемо етапи проектування бази даних на основі засобів СУБД MS Access 97, але перед цим дамо характеристики цій реляційній СУБД.

База даних — це інтегроване сховище взаємопов'язаних даних конкретної предметної області.

Система управління базами даних (СУБД) — це комплекс програмних засобів, призначений для інтегрованого зберігання та обробки даних.

Етапи створення бази даних у середовищі Microsoft Access:

• визначення мети створення бази даних;

• визначення таблиць, які повинна містити база даних;

• визначення структури таблиць (полів та їх типів);

• призначення ключів таблиць та створення потрібних індексів;

• визначення зв'язків між таблицями;

• завантаження даних;

• створення інших об'єктів бази даних: запитів, форм, звітів, макросів та модулів;

• «аналіз ефективності бази даних за допомогою майстра таблиць (меню СЕРВИОАНАЛИЗ > ТАБЛИЦА) та аналізатора швидкодії (меню СЕРВИОАНАЛИЗ > БЬІСТРОДЕЙСТВИЕ).

База даних – це сукупність даних, яким властива структурованість і взаємопов’язаність, а також належність від прикладних програм.

Принципи та етапи проектування бази даних

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

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

• дані перед включенням до бази даних повинні перевірятися на достовірність;

• доступ до даних повинні мати тільки особи з відповідними повноваженнями;

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

Етапи проектування бази даних:

1. Визначення мети створення бази даних.

2. Проектування концептуальної моделі бази даних.

3. Проектування зовнішніх моделей даних.

4. Проектування внутрішньої моделі даних.

5. Оцінка внутрішньої моделі даних.

6. Реалізація бази даних.

7. Аналіз ефективності бази даних.