Табличний процесор Excel та СУБД ACCESS

Загальна характеристика та функціональні можливості MS Excel

Microsoft Excel (також іноді називається Microsoft Office Excel) – програма для роботи з електронними таблицями, створена корпорацією Microsoft для Microsoft Windows, Windows NT і Mac OS. Вона надає можливості економіко-статистичних розрахунків, графічні інструменти і, за винятком Excel 2008 під Mac OS X, мова макропрограмування VBA (Visual Basic для додатків). Microsoft Excel входить до складу Microsoft Office і на сьогоднішній день Excel є одним з найбільш популярних програм у світі.

Цінної можливістю Excel є можливість писати код на основі Visual Basic для додатків (VBA). Цей код пишеться з використанням окремого від таблиць редактора. Управління електронною таблицею здійснюється за допомогою об'єктно-орієнтованої моделі коду і даних. За допомогою цього коду дані вхідних таблиць будуть миттєво оброблятися і відображатися в таблицях і діаграмах (графіках). Таблиця стає інтерфейсом коду, дозволяючи легко працювати змінювати його і керувати розрахунками.

За допомогою Excel можна аналізувати великі масиви даних. У Excel можна використовувати більше 400 математичних, статистичних, фінансових та інших спеціалізованих функцій, пов'язувати різні таблиці між собою, вибирати довільні формати представлення даних, створювати ієрархічні структури. Воістину безмежні методи графічного представлення даних: крім кількох десятків вбудованих типів діаграм, можна створювати свої, що настроюються типи, допомагають наочно відобразити тематику діаграми. Ті, хто тільки освоює роботу з Excel, по достоїнству оцінять допомогу «майстрів» – допоміжних програм, які допомагають при створення діаграм. Вони, як добрі чарівники, задаючи навідні питання про передбачуваних подальших кроках і показуючи, в залежності від планованого відповіді, результат, проведуть користувача «за руку» за всіма етапами побудови діаграми найкоротшим шляхом.

Робота з таблицею не обмежується простим занесенням до неї даних та побудовою діаграм. Важко уявити собі галузь, де б не був потрібний аналіз цих даних. У Excel включений потужний інструмент аналізу – Зведена таблиця. З її допомогою можна аналізувати широкоформатні таблиці, містять велику кількість несистематизованих даних, і лише декількома клацаннями кнопкою миші приводити їх у зручний і читається вигляд. Освоєння цього інструменту спрощується наявністю відповідної програми-майстра.

В Microsoft Excel є два основних типи об’єктів: книга і лист.

Книга в Microsoft Excel являє собою файл, що використовується для обробки і зберігання даних. Кожна книга може складатися з декількох аркушів, тому в одному файлі можна помістити різноманітні відомості і встановити між ними необхідні зв'язку.

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

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

В Microsoft Excel дуже багато різноманітних функцій, серед яких такі:

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

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

Логічні функції – ця категорія включає 6 логічних команд, серед яких ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ.

Математичні – ця категорія – одна з найбільш густонаселених в Excel (більше 100 функцій). Ніяких особливих хитрощів в ній немає. Але є досить багато функцій на всі випадки.

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

Текстові – У цій групі десятка два команд! З їх допомогою ми зможемо порахувати кількість символів у комірці, включаючи пробіли (ДЛСТР), дізнатися код символу (КОДСИМВ), дізнатися, який символ стоїть першим (ЛЕВСИМВ) і останнім (ПРАВСИМВ) у рядку тексту, помістити в активну комірку деяку кількість символів з іншої комірці (ПСТР), помістити в активну комірку весь текст з іншого комірки великими (ПРОПИСН) або стічними літерами (СТРОЧН), перевірити, чи збігаються дві текстові комірки (СОВПАД), знайти деякий текст (ПОИСК, НАЙТИ) і замінити його іншим (ЗАМЕНИТЬ).

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

Робота з базою даних – тут можна знайти команди статистичного обліку (БДДИСП – дисперсія по вибірці з бази, БДДИСПП – дисперсія по генеральній сукупності, ДСТАНДОТКЛ – стандартне відхилення по вибірці), операції із стовпцями і рядками бази, кількість непорожніх (БСЧЕТА) або (БСЧЕТ) осередків і т.д.

Мастер диаграмм – вбудована програма EXCEL, що спрощує роботу з основними можливостями програми.

Визначення СУБД. Призначення СУБД MS ACCESS

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

Основні функції СУБД:

керування даними в зовнішньої пам'яті (на дисках);

керування даними в оперативній пам'яті з використанням дискового кешу;

журналювання змін, резервне копіювання і відновлення бази даних після збоїв;

підтримка мов БД (мова визначення даних, мова маніпулювання даними).

Звичайно сучасна СУБД містить наступні компоненти:

ядро, яке відповідає за керування даними в зовнішній і оперативної пам'яті, і журналізацію,

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

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

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

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

Основні компоненти MS Access:

побудовник таблиць;

побудовник екранних форм;

налаштування SQL-запитів (мова SQL в MS Access не відповідає стандарту ANSI);

налаштування звітів, що виводяться на друк.

Вони можуть викликати скрипти мовою VBA, тому MS ACCESS дозволяє розробляти програми і БД практично «з нуля» або написати оболонку для зовнішньої БД.

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

Істотно розширює можливості MS Access за написання додатків механізм зв'язку з різними зовнішніми СУБД: «пов'язані таблиці» (зв'язок з таблицею СУБД) і «запити до сервера» (запит на діалекті SQL, що «розуміє» СУБД). Також MS Access дозволяє будувати повноцінні клієнт-серверні додатки на СУБД MS SQL Server. При цьому є можливість поєднати з властивою MS Access простотою інструменти для управління БД і засоби розробки.

Завдання 1

Скласти таблицю, в якій зазначити рекламодавців на каналі 1+1 з кількістю рекламних роликів за 1 тиждень. Розрахувати суму затрачених ними коштів на рекламу. Якщо підсумок складає більше 100000 грн. рекламодавець отримує скидку у розмірі 8%. Побудувати кругову діаграму для кожного рекламодавця від загальних витрат.

Рекламодавець	Категорія реклами	Затрати	Підсумок з урахуванням знижки	% до загальних витрат
Час хв.	Ціна грн./хв.

Хід роботи

Створюємо новий документ EXCEL.

Заповнюємо такі поля таблиці як «Рекламодавець», «Категорія реклами», «Затрати». В комірку Е3 вводимо наступну формулу: «=ЕСЛИ (C3*D3>100000; C3*D3*(1–0,08); C3*D3)». Значення в комірках Е4-Е12 отримуємо за допомогою авто заповнення.

Далі в комірку F3 вводимо формулу: «=E3/$E$13*100». Значення в комірках F4-F12 також отримуємо за допомогою авто заповнення. Отримуємо наступні результати розрахунків, які показані на рис. 1.

Рис. 1 – Результати розрахунків витрат на рекламу

По результатам розрахунків будуємо діаграми% до загальних витрат (рис. 2).

Рис. 2 – Діаграма проценту від загальних витрат на рекламу

Рис. 3 – Формули розрахунків

Завдання 2 (лінійний обчислювальний процес)

Розрахувати наступний вираз:

Хід роботи

Створюємо новий документ EXCEL.

Заповнюємо початкові дані A, B, C та X. Для розрахунку виразу (Y) в комірку Е7 вводимо наступну формулу: «=(КОРЕНЬ (1,2*$A$7+$C$7*ABS (LN(D7)))/(2*$B$7))*EXP(D7)». Значення в комірках Е8-Е19 отримуємо за допомогою авто заповнення. Отримуємо наступні результати розрахунків виразу, які показані на рис. 3.

Рис. 4 – Результати розрахунків витрат на рекламу

По результатам розрахунків будуємо графік отриманих значень (рис. 2).

Рис. 5 – Діаграма проценту від загальних витрат на рекламу

Рис. 6 – Формули розрахунків

Завдання 3 (розгалужений обчислювальний процес)

Значення змінних x, A, B та C підібрати таким чином, щоб розрахунок виконувався за всіма трьома умовами завдання.

Хід роботи

Створюємо новий документ EXCEL.

Вводимо початкові дані x, A, B та C. Також в першу комірку F10 вводимо формулу: «=ЕСЛИ (И(E10>0; B10=0); СТЕПЕНЬ (E10; B10)*КОРЕНЬ (C10*E10)+D10*СТЕПЕНЬ (E10; C10); ЕСЛИ (И(E10=0; НЕ (И(B10>0; B10<1))); СТЕПЕНЬ (C10; 2)+СТЕПЕНЬ (D10; 2)+B10; СТЕПЕНЬ (E10; 2)+EXP (B10+E10)+СТЕПЕНЬ (C10; D10)))».

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

Рис. 7 – Результати розрахунків виразу

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

Таблиця 1 – Формули для розрахунку виразу

A	B	C	X	Y
1	1	2	2	=ЕСЛИ (И(E10>0; B10=0); СТЕПЕНЬ (E10; B10)*КОРЕНЬ (C10*E10)+D10*СТЕПЕНЬ (E10; C10); ЕСЛИ (И(E10=0; НЕ (И(B10>0; B10<1))); СТЕПЕНЬ (C10; 2)+СТЕПЕНЬ (D10; 2)+B10; СТЕПЕНЬ (E10; 2)+EXP (B10+E10)+СТЕПЕНЬ (C10; D10)))
0	2	3	-1	=ЕСЛИ (И(E11>0; B11=0); СТЕПЕНЬ (E11; B11)*КОРЕНЬ (C11*E11)+D11*СТЕПЕНЬ (E11; C11); ЕСЛИ (И(E11=0; НЕ (И(B11>0; B11<1))); СТЕПЕНЬ (C11; 2)+СТЕПЕНЬ (D11; 2)+B11; СТЕПЕНЬ (E11; 2)+EXP (B11+E11)+СТЕПЕНЬ (C11; D11)))
3	4	1	0	=ЕСЛИ (И(E12>0; B12=0); СТЕПЕНЬ (E12; B12)*КОРЕНЬ (C12*E12)+D12*СТЕПЕНЬ (E12; C12); ЕСЛИ (И(E12=0; НЕ (И(B12>0; B12<1))); СТЕПЕНЬ (C12; 2)+СТЕПЕНЬ (D12; 2)+B12; СТЕПЕНЬ (E12; 2)+EXP (B12+E12)+СТЕПЕНЬ (C12; D12)))
1	3	2	0	=ЕСЛИ (И(E13>0; B13=0); СТЕПЕНЬ (E13; B13)*КОРЕНЬ (C13*E13)+D13*СТЕПЕНЬ (E13; C13); ЕСЛИ (И(E13=0; НЕ (И(B13>0; B13<1))); СТЕПЕНЬ (C13; 2)+СТЕПЕНЬ (D13; 2)+B13; СТЕПЕНЬ (E13; 2)+EXP (B13+E13)+СТЕПЕНЬ (C13; D13)))
3	2	1	1	=ЕСЛИ (И(E14>0; B14=0); СТЕПЕНЬ (E14; B14)*КОРЕНЬ (C14*E14)+D14*СТЕПЕНЬ (E14; C14); ЕСЛИ (И(E14=0; НЕ (И(B14>0; B14<1))); СТЕПЕНЬ (C14; 2)+СТЕПЕНЬ (D14; 2)+B14; СТЕПЕНЬ (E14; 2)+EXP (B14+E14)+СТЕПЕНЬ (C14; D14)))
0	0	0	1	=ЕСЛИ (И(E15>0; B15=0); СТЕПЕНЬ (E15; B15)*КОРЕНЬ (C15*E15)+D15*СТЕПЕНЬ (E15; C15); ЕСЛИ (И(E15=0; НЕ (И(B15>0; B15<1))); СТЕПЕНЬ (C15; 2)+СТЕПЕНЬ (D15; 2)+B15; СТЕПЕНЬ (E15; 2)+EXP (B15+E15)+СТЕПЕНЬ (C15; D15)))
2	3	3	2	=ЕСЛИ (И(E16>0; B16=0); СТЕПЕНЬ (E16; B16)*КОРЕНЬ (C16*E16)+D16*СТЕПЕНЬ (E16; C16); ЕСЛИ (И(E16=0; НЕ (И(B16>0; B16<1))); СТЕПЕНЬ (C16; 2)+СТЕПЕНЬ (D16; 2)+B16; СТЕПЕНЬ (E16; 2)+EXP (B16+E16)+СТЕПЕНЬ (C16; D16)))
3	2	4	0	=ЕСЛИ (И(E17>0; B17=0); СТЕПЕНЬ (E17; B17)*КОРЕНЬ (C17*E17)+D17*СТЕПЕНЬ (E17; C17); ЕСЛИ (И(E17=0; НЕ (И(B17>0; B17<1))); СТЕПЕНЬ (C17; 2)+СТЕПЕНЬ (D17; 2)+B17; СТЕПЕНЬ (E17; 2)+EXP (B17+E17)+СТЕПЕНЬ (C17; D17)))
0	1	4	1	=ЕСЛИ (И(E18>0; B18=0); СТЕПЕНЬ (E18; B18)*КОРЕНЬ (C18*E18)+D18*СТЕПЕНЬ (E18; C18); ЕСЛИ (И(E18=0; НЕ (И(B18>0; B18<1))); СТЕПЕНЬ (C18; 2)+СТЕПЕНЬ (D18; 2)+B18; СТЕПЕНЬ (E18; 2)+EXP (B18+E18)+СТЕПЕНЬ (C18; D18)))
-1	2	2	-1,2	=ЕСЛИ (И(E19>0; B19=0); СТЕПЕНЬ (E19; B19)*КОРЕНЬ (C19*E19)+D19*СТЕПЕНЬ (E19; C19); ЕСЛИ (И(E19=0; НЕ (И(B19>0; B19<1))); СТЕПЕНЬ (C19; 2)+СТЕПЕНЬ (D19; 2)+B19; СТЕПЕНЬ (E19; 2)+EXP (B19+E19)+СТЕПЕНЬ (C19; D19)))
2	2	3	1	=ЕСЛИ (И(E20>0; B20=0); СТЕПЕНЬ (E20; B20)*КОРЕНЬ (C20*E20)+D20*СТЕПЕНЬ (E20; C20); ЕСЛИ (И(E20=0; НЕ (И(B20>0; B20<1))); СТЕПЕНЬ (C20; 2)+СТЕПЕНЬ (D20; 2)+B20; СТЕПЕНЬ (E20; 2)+EXP (B20+E20)+СТЕПЕНЬ (C20; D20)))
1	4	-3	2	=ЕСЛИ (И(E21>0; B21=0); СТЕПЕНЬ (E21; B21)*КОРЕНЬ (C21*E21)+D21*СТЕПЕНЬ (E21; C21); ЕСЛИ (И(E21=0; НЕ (И(B21>0; B21<1))); СТЕПЕНЬ (C21; 2)+СТЕПЕНЬ (D21; 2)+B21; СТЕПЕНЬ (E21; 2)+EXP (B21+E21)+СТЕПЕНЬ (C21; D21)))
0	1	-3	-2,3	=ЕСЛИ (И(E22>0; B22=0); СТЕПЕНЬ (E22; B22)*КОРЕНЬ (C22*E22)+D22*СТЕПЕНЬ (E22; C22); ЕСЛИ (И(E22=0; НЕ (И(B22>0; B22<1))); СТЕПЕНЬ (C22; 2)+СТЕПЕНЬ (D22; 2)+B22; СТЕПЕНЬ (E22; 2)+EXP (B22+E22)+СТЕПЕНЬ (C22; D22)))
1	2	1	2	=ЕСЛИ (И(E23>0; B23=0); СТЕПЕНЬ (E23; B23)*КОРЕНЬ (C23*E23)+D23*СТЕПЕНЬ (E23; C23); ЕСЛИ (И(E23=0; НЕ (И(B23>0; B23<1))); СТЕПЕНЬ (C23; 2)+СТЕПЕНЬ (D23; 2)+B23; СТЕПЕНЬ (E23; 2)+EXP (B23+E23)+СТЕПЕНЬ (C23; D23)))
2	2	1	2	=ЕСЛИ (И(E24>0; B24=0); СТЕПЕНЬ (E24; B24)*КОРЕНЬ (C24*E24)+D24*СТЕПЕНЬ (E24; C24); ЕСЛИ (И(E24=0; НЕ (И(B24>0; B24<1))); СТЕПЕНЬ (C24; 2)+СТЕПЕНЬ (D24; 2)+B24; СТЕПЕНЬ (E24; 2)+EXP (B24+E24)+СТЕПЕНЬ (C24; D24)))

Рис. 8 – Графік розрахованих значень

Завдання 4

Створити таблиці за пропонованою структурою, встановити зв’язки між таблицями, заповнити таблиці даними (не менше 10 записів):

Таблиця 2 – Довідник виробів

Код виробу	Найменування виробу	Вироблено (од.)
…	…	…

Таблиця 3 – Прейскурант цін

Код деталі	Найменування деталі	Ціна (грн.)
…	…	…

Таблиця 4 – Застосованість деталей у виробах

Код виробу	Код деталі	Кількість деталей у виробі

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

Таблиця 5 – Структура запиту для визначення вартості деталей у виробі

Код виробу	Найменування виробу	Вироблено (од.)	Код деталі	Найменування деталі	Кількість деталей у виробі	Ціна деталі (грн.)	Вартість деталей у виробі
1	2	3	4	5	6	7	8=(6*7)

Хід роботи

В СУБД Microsoft ACCESS створюємо базу даних під назвою «Робітники».

Створюємо і заповнюємо в БД 3 таблиці: «Довідник робітників», «Довідник верстатів» та «Відомість виконаних робіт». Вони показані відповідно на рис. 9, 10 та 11.

Рис. 9 – Таблиця «Довідник виробів»

Рис. 10 – Таблиця «Прейскурант цін»

Рис. 11 – Таблиця «Застосованість деталей у виробах»

Тепер зв’яжемо створені таблиці. Для цього натискаємо на верхній панелі на клавішу «Схема данных» і додаємо всі три таблиці до схеми. Далі створюємо 2 зв’язки: поле «Код виробу» таблиці «Довідник виробів» з’єднуємо з полем «Код виробу» таблиці «Застосованість деталей у виробах» та поле «Код деталі» таблиці «Прейскурант цін» – з полем «Код деталі» таблиці «Застосованість деталей у виробах». Схема даних показана на рис. 12.

Рис. 12 – Схема даних

Створимо 3 форми для перегляду кожної зі створених таблиць. Вони відповідно показані на рис. 13, 14 та 15.

Рис. 13 – Форма таблиці «Довідник виробів»

Рис. 14 – Форма таблиці «Прейскурант цін»

Рис. 15 – Форма таблиці «Застосованість деталей у виробах»

Виконати розрахунки вартості складових у кожному виробі

Тепер виконаємо розрахунки вартості складових у кожному виробі, як показано нижче. Для цього в режимі конструктора створимо запит до БД, як показано на рис. 16. Цей запит має 1 розрахункове поле (в якому і розраховується вартість деталей у виробі), яке має наступну формулу:

Вартість деталей у виробі: [Кількість деталей у виробі]*[Ціна (грн)]

Рис. 16 – Створення запиту розрахунку вартості деталей в режимі конструктора

Після створення запиту, запускаємо його на виконання, як показано на рис. 17.

Рис. 17 – Запит до БД про розрахунок вартості деталей

Одержані результати оформимо у вигляді звіту, як показано на рис. 18.

Рис. 18 – Звіт до БД про нарахування плати робітникам