Работа с приложениями Microsoft Office

Зміст

Вступ 3

1. Теоретична частина 4

1. Загальні відомості про БД: базові визначення, операції 4

2. Основні поняття 5

3. Характеристика зв'язків і мова моделювання 6

4. Технологія вибіркового використання даних БД у Excel: фільтрація, пошук даних, реалізація запитів 9

1. Фільтрація списку за допомогою розширеного фільтра 9

2. Приклади умов добору розширеного фільтра 9

3. Пошук даних, тексту і чисел 11

4. Створення запиту 13

5. Створення запиту з умовами 14

5. Побудова зведених таблиць, звітів 14

1. Створення зведеної таблиці 15

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

3. Звіти 16

2. ПРАКТИЧНА частина 17

1. Постановка задачі та її формалізація 17

1. Загальна характеристика предметної області 17

2. Проектна частина 17

1. Опис технологічного процесу збору й опрацювання інформації 17

2. Опис вхідних даних і бази даних 19

3. Розрахункова частина. 20

1. Опис засобів MS Excel, які використовувалися при розрахунках 20

2. Розрахунок середньої ціни холодильника і розкид цін 21

3. Розрахунок середньої ціни холодильника різних виробників 22

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

Висновки 25

Список літератури 26

Додаток А -----------------------------------------------------------------------------------------------------27

Додаток Б -----------------------------------------------------------------------------------------------------28

Вступ

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

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

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

Структура курсової роботи полягає в наступному.

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

В практичної частині роботи побудована база даних, яка містить інформацію про ринок холодильників у м. Дніпропетровську. Розглядаються холодильники, які мають 1, 2, 3 камери. Як додаткова інформація приведено колір корпусу холодильнику. В роботі проведено розрахунки засобами MS Excel, та розраховано: середня ціна та розкид цін для холодильників, середня ціна холодильників для кожного виробника, кількість одиниць асортименту у різних продавців.

Курсова робота складається з теоретичної частини, практичної частини, висновків і списку літератури. Теоретична частина містить в собі 5 підрозділів. Практична частина – 3.

В курсовій роботі приведено одна таблиця і 21 рисунок. Список літератури містить 26 джерел. В Додатку А приведено повний зміст бази даних. В Додатку Б – згруповані дані за виробниками і кількістю камер холодильників.

1. Теоретична частина

1. Загальні відомості про БД: базові визначення, операції

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

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

Нерідкі випадки, коли користувачі однієї і тієї ж ЕОМ створюють і використовують у своїх програмах різні набори даних, що містять подібну інформацію. Іноді це зв'язано з тим, що користувач не знає, що в сусідній кімнаті чи за сусіднім столом сидить співробітник, що уже давно ввів в ЕОМ потрібні дані. Частіше тому, що при спільному використанні тих самих даних виникає маса проблем.

Активна діяльність по відшуканню прийнятних способів усуспільнення безупинно зростаючого обсягу інформації привела до створення на початку 60-х років спеціальних програмних комплексів, називаних "Системи керування базами даних" (СКБД).

Основна особливість СКБД – це наявність процедур для введення і збереження не тільки самих даних, але й описів їхньої структури. Файли, постачені описом збережених у них даних і знаходяться під керуванням СКБД, стали називати банки даних, а потім "Бази даних" (БД).

СКБД повинна надавати доступ до даних будь-яких користувачів, включаючи і тих, котрі практично не мають представлення про:

фізичному розміщенні в пам'яті даних і їхніх описів;

механізмах пошуку запитуваних даних;

проблемах, що виникають при одночасному запиті тих самих даних багатьма користувачами (прикладними програмами);

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

підтримці баз даних в актуальному стані і безлічі інших функцій СКБД.

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

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

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

Спочатку стали використовувати ієрархічні даталогичні моделі. Простота організації, наявність заздалегідь заданих зв'язків між сутностями, подібність з фізичними моделями даних дозволяли домагатися прийнятної продуктивності ієрархічних СКБД на повільних ЕОМ з дуже обмеженими обсягами пам'яті. Але, якщо дані не мали деревоподібної структури, то виникала маса складностей при побудові ієрархічної моделі і бажанні домогтися потрібної продуктивності.

Складність практичного використання ієрархічних СКБД змушувала шукати інші способи представлення даних. Наприкінці 60-х років з'явилися СКБД на основі інвертованих файлів, що відрізняються простотою організації і наявністю дуже зручних мов маніпулювання даними. Однак такі СКБД володіють поруч обмежень на кількість файлів для збереження даних, кількість зв'язків між ними, довжину запису і кількість її полів.

Сьогодні найбільш поширені реляционі моделі

2. Основні поняття

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

Сутність – будь-який помітний об'єкт (об'єкт, що ми можемо відрізнити від іншого), інформацію про яке необхідно зберігати в базі даних. Сутностями можуть бути люди, місця, літаки, рейси, смак, колір і т.д. Необхідно розрізняти такі поняття, як тип сутності й екземпляр сутності. Поняття тип сутності відноситься до набору однорідних особистостей, предметів, подій чи ідей, що виступають як ціле. Екземпляр сутності відноситься до конкретної речі в наборі. Наприклад, типом сутності може бути МІСТО, а екземпляром – Москва, Київ і т.д.

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

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

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

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

3. Характеристика зв'язків і мова моделювання

При побудові инфологичних моделей можна використовувати мову ER-діаграм (від англ. Entity-Relationship, тобто сутність-зв'язок). У них сутності зображуються позначеними прямокутниками, асоціації – позначеними ромбами чи шестикутниками, атрибути – позначеними овалами, а зв'язку між ними – ненаправленими ребрами, над якими може проставлятися ступінь зв'язку (1 чи буква, що заміняє слово "багато") і необхідне пояснення.

Між двома сутностям, наприклад, А і В можливі чотири види зв'язків.

Перший тип – зв'язок один-до-одного (1:1): у кожен момент часу кожному представнику (екземпляру) сутності А відповідає 1 чи 0 представників сутності В рис. 1.

Рис. 1. Зв'язок один-до-одного

Студент може не "заробити" стипендію, одержати звичайну чи одну з підвищених стипендій.

Другий тип – зв'язок один-до-баготьох (1:М): одному представнику сутності А відповідають 0, 1 чи кілько представників сутності В (рис. 2.).

Рис. 2. Зв'язок один-до-баготьох

Квартира може пустувати, у ній може жити один чи кілька мешканців.

Тому що між двома сутностями можливі зв'язки в обох напрямках, то існує ще два типи зв'язку багато-до-одного (М:1) і багато-до-баготьох (М:N).

Приклад 1. Якщо зв'язок між сутностями ЧОЛОВІКА і ЖІНКИ називається ШЛЮБ, то існує чотири можливих представлення таких зв'язків рис. 3.

Рис. 3.

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

безліч зв'язків між тими самими сутностями (рис. 4.)

Рис. 4.

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

тренарні зв'язкі (рис. 5.)

Рис. 5.

(лікар може призначити трохи пацієнтів на кілька аналізів, аналіз може бути призначений декількома лікарями декільком пацієнтам і пацієнт може бути призначений на кілька аналізів декількома лікарями);

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

У приведених прикладах для підвищення ілюстративності розглянутих зв'язків не показані атрибути сутностей і асоціацій у всіх ER-діаграмах. Так, уведення лише декількох основних атрибутів в опис шлюбних зв'язків значно ускладнить ER-діаграму (мал. 6). У зв'язку з цим мова ER-діаграм використовується для побудові невеликих моделей і ілюстрації окремих фрагментів великих. Частіше ж застосовується менш наочний, але більш змістовна мова инфологичного моделювання (МИМ), у якому сутності й асоціації представляються пропозиціями виду:

СУТНІСТЬ (атрибут 1, атрибут 2 , ..., атрибут n)

АСОЦІАЦІЯ [СУТНІСТЬ S1, СУТНІСТЬ S2, ...]

(атрибут 1, атрибут 2, ..., атрибут n)

де S – ступінь зв'язку, а атрибути, що входять у ключ, повинні бути відзначені за допомогою підкреслення.

Так, розглянутий вище приклад безлічі зв'язків між сутностями, може бути описаний на МИМ у такий спосіб:

Лікар (Номер_лікаря, Прізвище, Ім'я, По батькові, Спеціальність)

Пацієнт (Реєстраційний_номер, Номер ліжка, Прізвище,

Ім'я, По батькові, Адреса, Дата народження, Підлога)

Лікуючий_лікар [Лікар 1, Пацієнт M]

(Номер_лікаря, Реєстраційний_номер)

Консультант [Лікар M,Пацієнт N]

(Номер_лікаря, Реєстраційний_номер).

Рис. 6. Приклади ER-діаграм

Для виявлення зв'язків між сутностями необхідно, як мінімум, визначити самі сутності. Але це не проста задача, тому що в різних предметних областях той самий об'єкт може бути сутністю, чи атрибутом асоціацією. Проілюструємо таке твердження на прикладах, зв'язаних з описом шлюбних зв'язків (див. приклад 1).

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

Шлюб (Номер_свідчення, Прізвище_чоловіка, Ім'я_чоловіка,

По батькові_чоловіка, Дата_народження_чоловіка, Прізвище_дружини,

... , Дата_реєстрації, Місце_реєстрації, ...),

ER-діаграма якої приведена на Рис. 6,б.

Для кожної БД можуть існувати свої специфічні операції. Але найбільш загальні операції зв'язані з функціонуванням БД це: фільтрація, пошук даних, реалізація запитів.

4. Технологія вибіркового використання даних БД у Excel: фільтрація, пошук даних, реалізація запитів

1. Фільтрація списку за допомогою розширеного фільтра

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

1 Скопіюйте зі списку заголовки стовпців, які фільтруються .

2 Уставте скопійовані заголовки стовпців у першому порожньому рядку діапазону умов добору.

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

4 Вкажіть осередок у списку.

5 Виберіть пункт Фільтр у меню Дані, а потім — команду Розширений фільтр.

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

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

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

Якщо на листі існує діапазон з ім'ям Критерії, то в поле Діапазон умов автоматично з'явиться посилання на цей діапазон.

2. Приклади умов добору розширеного фільтра

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

На осередки одного стовпця накладаються три чи більш умови добору

Щоб задати для окремого стовпця три чи більш умови добору, введіть умови в осередки, розташовані в суміжних рядках. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «Белов», «Батурін» чи «Сушкин» у стовпці «Продавець» (Рис. 7.).

Продавець

Белов

Батурін

Сушкин

Рис. 7.

Умова добору накладається на осередки двох чи більш стовпців

Щоб накласти умови добору не кілька стовпців одночасно, введіть умови в осередки, розташовані в одному рядку діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Продукти» у стовпці «Товар», «Белов» у стовпці «Продавець», і реалізації, що мають суму, більше 1000 (рис. 8.).

Товар	Продавець	Продажу
Продукти	Белов	>1000

Рис. 8.

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

Щоб вибрати рядка, що задовольняють одному з декількох умов, накладених на різні стовпці, введіть умови в осередки, розташовані в різних рядках діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «Продукти» у стовпці «Товар», або «Белов» у стовпці «Продавець», або реалізації, що мають суму, більше 1000 (рис. 9.).

Товар	Продавець	Продажу
Продукти
	Белов
		>1000

Рис. 9.

Щоб накласти складна умова добору, уведіть його складові частини в окремі рядки діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Белов» у стовпці «Продавець» і реалізації, що мають суму, більше 3000 чи рядка, що містять «Батуріна» у стовпці «Продавець» і реалізації, що мають суму, більше 1500 (рис. 10.).

Продавець	Продажу
Белов	>3000
Батурін	>1500

Рис. 10.

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

=G5>СРЗНАЧ($E$5:$E$14)

Рис.11.

Використовувана в умові формула повинна посилатися або на заголовок стовпця (наприклад, «Продажу»), або на відповідне поле в першому записі. У приведеному прикладі G5 посилається на відповідне поле (стовпець G) першого запису (рядок 5) списку.

3. Пошук даних, тексту і чисел

Існує декілька засобів пошуку. Можна шукати дані безпосередньо. Для цього треба виконати наступні дії:

1 Виділите діапазон осередків, у якому буде проводитися пошук.

Щоб провести пошук по всьому листі, наведіть покажчик на будь-який осередок і натисніть кнопку миші.

2 В меню Виправлення виберіть команду Знайти.

3 В поле Що введіть чи текст числа, які потрібно знайти.

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

5 Виберіть команду Знайти далі.

Щоб зупинити пошук, натисніть клавішу ESC.

Можна використовувати деякі вмонтовані функції:

Функція Пошук повертає позицію першого входження чи символу текстового рядка при пошуку ліворуч праворуч. Функція ПОШУК використовується для пошуку входження чи символу рядка тексту в інший рядок тексту, для того щоб застосувати функції ПСТР чи ЗАМІНИТИ для зміни тексту.

Синтаксис:

ПОШУК(шуканий_текст;текст_для_пошуку;нач_позиція)

Шуканий_текст - це шуканий текст. Можна використовувати символи шаблона знак питання (?) і зірочка (*) в аргументі шуканий_текст. Знак питання відповідає будь-якому символу; зірочка відповідає будь-якой послідовності символів. Якщо потрібно знайти власне знак питання чи зірочку, то варто поставити перед ними знак тильда (~). Якщо шуканий_текст не знайдений, то повертається значення помилки #ЗНАЧ!.

Текст_для_пошуку - це текст, у якому потрібно знайти шуканий_текст.

Нач_позиція - це номер символу в тексті текст_для_пошуку (вважаючи ліворуч), з якого варто почати пошук.

Якщо нач_позиція опущена, то передбачається, що вона дорівнює 1.

Якщо нач_позиція не більше 0 чи більше, ніж довжина аргументу текст_для_пошуку, то повертається значення помилки #ЗНАЧ!.

Аргумент нач_позиція можна використовувати, щоб пропустити потрібну кількість символів з лівого краю тексту. Наприклад, припустимо, що Ви працюєте з текстовим рядком "МДС0093.Месячныепродажи". Щоб знайти перше входження "М" в описову частину текстового рядка, укажіть, що нач_позиція дорівнює 8, так що в тій частині тексту, що є серійним номером, пошук вироблятися не буде. Функція ПОШУК починає із символу 8, знаходить шуканий_текст у наступному символі і повертає число 9. Функція ПОШУК завжди повертає номер символу, вважаючи від лівого краю тексту, а не від значення аргументу нач_позиція.

ПОШУК не розрізняє регістра при пошуку (тобто рядкові і заголовні букви не розрізняються).

Функція ПОШУК подібний функції ЗНАЙТИ, за тим виключенням, що функція ЗНАЙТИ працює з урахуванням регістра.

Приклади:

ПОШУК("р";"Оператори";6) дорівнює 8

Якщо осередок B17 містить слово "доход", а осередок A14 містить "Сума Доходів", то:

ПОШУК($B$17;$A$14) дорівнює 7

Функція ПОШУК використовується разом з функцією ЗАМІНИТИ для того, щоб задати функції ЗАМІНИТИ коректне значення аргументу нач_позиція, з яким потрібно вставити новий текст. Використовуючи осередку попереднього приклада, одержимо:

ЗАМІНИТИ($A$14;ПОШУК($B$17;$A$14);7;"Витрат") повертає текст "Сума Витрат".

Функція Знайти знаходить входження одного текстового рядка (шуканий_текст) в інший текстовий рядок ( що, переглядається_текст) і початкову положення початку шуканого тексту щодо крайнього лівого символу тексту, що переглядається. Для пошуку входжень одного текстового рядка в інший текстовий рядок можна використовувати також функцію ПОШУК, але на відміну від функції ПОШУК, функція ЗНАЙТИ враховує регістр і не допускає символів шаблона.

Синтаксис:

ЗНАЙТИ(шуканий_текст; щопереглядається_текст;нач_позиція)

Шуканий_текст - це шуканий текст.

Якщо шуканий_текст - це "" (порожній рядок), то функція ЗНАЙТИ вважає придатним перший символ у рядку, що переглядається, (тобто поверне значення аргументу нач_ чипозиція 1).

Шуканий_текст не повинний містити ніяких символів шаблона.

Що переглядається_текст - це текст, що містить шуканий текст.

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

Якщо шуканий_текст не входить у що переглядається_текст, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.

Якщо нач_позиція чи менше дорівнює нулю, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.

Якщо нач_позиція більше довжини рядка що переглядається_текст, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.

Приклади:

ЗНАЙТИ("М";"Мадам Смирнова") дорівнює 1

ЗНАЙТИ("м";"Мадам Смирнова") дорівнює 5

ЗНАЙТИ("м";"Мадам Смирнова";6) дорівнює 8

Припустимо, що на робочому листі зберігається список деталей з їхніми серійними номерами, і потрібно виділити список найменувань деталей без серійних номерів. Можна використовувати функцію ЗНАЙТИ для пошуку символу #, а потім функцію ПСТР, щоб виключити серійний номер. Нехай осередку A2:A4 містять наступний список деталей із серійними номерами: "Керамічні ізолятори #124-6745-87", "Мідні котушки #12-671-6772", "Перемінні опори #116010".

ПСТР(A2;1;ЗНАЙТИ(" #";A2;1)-1) повертає "Керамічні ізолятори"

ПСТР(A3;1;ЗНАЙТИ(" #";A3;1)-1) повертає "Мідні котушки"

ПСТР(A4;1;ЗНАЙТИ(" #";A4;1)-1) повертає "Перемінні опори"

4. Створення запиту

Microsoft Query дозволяє одержати зовнішні дані декількома способами. Щоб скористатися цими можливостями і забезпечити необхідний результат, візьміть до уваги наступне:

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

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

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

5. Створення запиту з умовами

1 Створіть запит, що включає таблиці і поля потрібних записів. Не повертайте результати запиту в Microsoft Excel.

2 Переконаєтеся, що кнопка Автоматичний режим не натиснутий.

3 Переконаєтеся, що кнопка Відображення умов не натиснута.

4 Виберіть осередок у рядку Умова і клацніть стрільцю, щоб вибрати зі списку поле, яке варто використовувати як параметр запиту.

5 Виділите перший осередок у рядку Значення. Уведіть [ (відкриваюча квадратна дужка) і текст, що Microsoft Query буде виводити при запуску запиту. Потім уведіть ] (закриваюча квадратна дужка).

Текст повідомлення повинний відрізнятися від імені поля, однак ім'я поля може входити в нього. Більш докладні зведення про типи умов, використовуваних для вибірки даних, можна знайти в довідці по Microsoft Query.

6 Натисніть кнопку ENTER.

7 Щоб запустити запит з Microsoft Query, виберіть Виконати запит .

Щоб передати таблицю результатів у Microsoft Excel, виберіть вихід з поверненням даних.

5. Побудова зведених таблиць, звітів

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

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

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

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

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

1. Створення зведеної таблиці

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

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

Якщо зведена таблиця створюється на основі даних, що знаходяться в чи списку базі даних Microsoft Excel, виділите осередок списку чи бази даних.

2 Виберіть команду Зведена таблиця в меню Дані.

3 Випливати інструкціям майстра зведених таблиць.

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

Створення зведеної таблиці на основі даних, що знаходяться в списку чи в базі даних Microsoft Excel

Створення зведеної таблиці на основі даних, що знаходяться в зовнішніх джерелі даних

Створення зведеної таблиці на основі даних, що знаходяться в декількох діапазонах консолідації

Створення зведеної таблиці на основі даних, що знаходяться в іншій зведеній таблиці

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

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

· Підсумкова функція використовується для виконання основних обчислень (наприклад, «Сума», «Середнє», «Мінімум» і «Максимум»). Щоб одержати опис доступних підсумкових функцій, натисніть кнопку .

· Додаткові обчислення використовуються для чи порівняння індексування, а також для обчислення часткою.

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

3. Звіти

Аркуші, представлення і сценарії можуть бути об'єднані в звіти, що потім можуть бути роздруковані за допомогою надбудови «Диспетчер звітів». Після додавання звіт зберігається разом із книгою, тому його можна роздрукувати пізніше.

Наприклад, якщо існують сценарій «Кращий випадок», сценарій «Гірший випадок» і два різних користувальницьких режими «Підсумки» і «Подробиці», те існує можливість створення звіту зі сценарієм «Кращий випадок» і видом «Подробиці» і звіту зі сценарієм «Кращий випадок» і видом «Підсумки».

Створення підсумкового звіту

1 Виберіть команду Сценарії в меню Сервіс.

2 Натисніть кнопку Звіт.

3 Установите перемикач у положення чи Структура Зведена таблиця.

4 В поле Осередку результату введіть ссыл

Діалогові вікна «Додати звіт» і «Редагувати звіт»

За допомогою діалогового вікна Додати звіт створюються нові звіти. У діалоговому вікні Редагувати звіт редагуються раніше створені звіти. Мається можливість зміни назв звітів, створення нових розділів, зміни порядку проходження розділів і включення наскрізної нумерації сторінок звіту.

Ім'я звіту Щоб привласнити звіту назва, наберіть послідовність символів у поле Ім'я звітів.

Роздягнув для додавання Щоб створити розділ звіту, установите значення полів у наборі полів Роздягнув для додавання.

Лист Щоб задати лист активної книги, що повинний бути використаний у розділі звіту, виберіть його зі списку Лист.

Вид Щоб додати в розділ вид, виберіть його зі списку Вид.

Сценарій Щоб додати в розділ сценарій, виберіть його зі списку Сценарій.

Додати Щоб додати поточний розділ у список Розділи в цьому звіті, натисніть кнопку Додати.

Розділи в цьому звіті Використовується для відображення списку розділів у поточному звіті. Розділи виводяться на печатку в тім порядку, у якому вони випливають у списку.

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

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

Послідовна нумерація сторінок Установите наскрізну нумерацію сторінок звіту. Якщо даний прапорець не встановлений, то кожен розділ звіту нумерується з 1.

Видалити Щоб видалити розділ, обраний зі списку Розділи в цьому звіті, натисніть кнопку Видалити.

2. ПРАКТИЧНА частина

1. Постановка задачі та її формалізація

За завданням необхідно сформувати базу даних про ринок холодильників у м. Дніпропетровську.

1. Загальна характеристика предметної області

Була зібрана інформація про роздрібний продаж холодильників звертаючи увагу на таки ознаки:

магазин, де проводіться роздрібна торгівля,

виробник холодильника,

назва товару,

ціна холодильника,

кількість камер в холодильнику.

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

В розробленій Базі Даних проведені:

розрахунок середньої ціни товарної продукції і розкид цін,

розрахунок середньої ціни товарної одиниці холодильника різних марок і з різною кількістю камер,

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

2. Проектна частина

1. Опис технологічного процесу збору й опрацювання інформації

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

Таблиця Технологічний процес збору й опрацювання інформації

№	Найменування етапу	Зміст етапу
1.	Збір і реєстрація інформації	Джерело інформації - прайс-лісти магазинів: Электро Мир, пр. Карла Маркса, 46 АБВ техника, пр. Карла Маркса, 81 LG, пр. Карла Маркса, 109 Whirlpool, ул. Артема, 1
2.	Контроль вводу інформації	Метод контролю – візуальний. Здійснюються перевірка відповідності веденної з клавіатури інформації вихідним даним.
3.	Накопичення і збереження інформації про предметну область	Накопичення і збереження інформації здійснюється на машинному носії у файлі формату MS Excel.
4.	Пошук і опрацювання даних	Пошук і опрацювання даних зроблені засобами MS Excel.
5.	Контроль опрацювання даних	Контроль опрацювання даних проведене методом вибіркового прорахунку окремих розмірів (контрольний приклад).
6.	Вивід результатів опрацювання даних	Вивід результатів опрацювання даних зроблено на друкувальний пристрій.
7.	Передача інформації	Результати опрацювання даних у друкарському виді передаються споживачу.
8.	Ухвалення рішення	Провадиться споживачем на основі аналізу отриманих результатів опрацювання.

Рис. 1. Блок - схеми алгоритму процесу опрацювання інформації

2. Опис вхідних даних і бази даних

В якості вхідних даних використовувалася інформація з власних спостережень і з прайс-листів магазинів.

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

В базі відображено 69 записів.

В базу даних занесена інформація про кожну одиницю товару, яка містить такі ознаки:

номер один по одному,

магазин, де проводіться роздрібна торгівля,

виробник холодильника,

назва товару,

ціна холодильника,

кількість камер в холодильнику.

База сформована засобами MS Exсel у виді двомірної таблиці.

У кожному рядку таблиці розміщена інформація, що ставиться до одной одиниці товару – холодильника.

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

Стовпчик А - номер один по одному,

Стовпчик B - магазин,

Стовпчик С - виробник,

Стовпчик D - назва товару,

Стовпчик Е - ціна холодильника,

Стовпчик F - кількість камер в холодильнику,

Стовпчик G - додаткова інформація

Дані розміщені на листі книги MS Excel, який має назву "Холодильники".

Інформація про записи в базі даних частково відображені на рис. 2. Повний зміст бази даних подано в Додатку А.

	A	B	C	D	E	F	G
1	№	Магазин	Виробник	Назва товару	Ціна, грн	Кількість камер	Додаткова інформація
2	1	1	1	ARF 191/NE	2 645 грн.	1	Бар - холодильник
3	2	1	2	KSR 3895	2 471 грн.	1
4	3	1	2	KGS 3760 IE	3 712 грн.	2	корпус: червоний
5	4	1	2	KSF 3202	4 267 грн.	3

Рис. 2. Записи в базі даних

Дані про магазини і виробників холодильників розташовані на листі "Розрах". Вид таблиць з даними про магазини і виробників приведені в підрозділі де описуються розрахунки.

За параметр який описує різницю між холодильниками було взято кількість камер холодильника. Спостерігалися холодильники з 1, 2, 3 камерами. Відповідно, дані були розбити на три групи.

Опрацювання даних виконане засобами MS Excel.

На листі "Розрах" представлені розрахунки:

середня ціна товарної одиниці холодильнику,

розкид цін для холодильників,

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

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

Для тих виробників для яких не спостерігалися холодильники з якоюсь кількістю камер стоїть знак "-".

На листі "Лист3" представлена інформація яка була оброблена за допомогою автофільтру. Дані згруповані по виробниках і за кількістю камер в холодильника. Повний вигляд згрупованих даних на "Лист3" приведено в Додатку Б.

3. Розрахункова частина.

1. Опис засобів MS Excel, які використовувалися при розрахунках

По перше, був використовуване автофільтр по стовпцях С – Виробник і F – Кількість камер.

Використовувалися такі функції MS Excel:

СУММ(число1;число2; ...)

МАКС (ячейка1:ячейка2)

МІН (ячейка1:ячейка2)

СЧЁТЕСЛИ(интервал; критерий)

СРЗНАЧ (ячейка1:ячейка2)

Функція СУММ(число1;число2; ...) підсумовує всі числа в інтервалі.

Число1, число2, ... - це від 1 до 30 аргументів, для яких потрібно визначити суму.

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

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

Аргументи, що є значеннями помилок текста, не перетвореними в числа, викликають помилки.

Функція МІН повертає найменше значення з набору даних і має такий синтаксис:

=МІН(число1;число2;...)

Число1, число2,...- це від 1 до 30 чисел, серед яких шукається мінімальне значення.

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

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

Якщо аргументи не містять числа, то функція МІН повертає 0.

Функція МАКС повертає найбільше значення з набору даних і має такий синтаксис:

=МАКС(число1;число2;…)

Значення аргументу задаються таким же уявою як і у функції МИН.

Функція СЧЁТЕСЛИ(интервал; критерий) підраховує кількість осередків усередині інтервалу, що задовольняють заданому критерію.

Интервал - це інтервал осередків, що обчислюються.

Критерий - це критерій у формі числа, чи вираження у віді тексту, що визначає, яка осередок додається. Наприклад, критерій може бути виражений як 32, "32", ">32", "яблука".

Сум_интервал - це фактичні осередки для підсумовування. Осередки в сум_інтервал сумуются, тільки якщо відповідні осередки в аргументі Интервал задовольняють Критерий. Якщо сум_інтервал опущений, то сумуются осередки в аргументі інтервал.

Функція СРЗНАЧ обчисляє середнє арифметичне значення, сумуючи ряд числових значень із наступним розподілом результату на кількість значень. Ця функція має такий синтаксис:

=СРЗНАЧ(число1;число2;...)

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

2. Розрахунок середньої ціни холодильника і розкид цін

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

Середня ціна товарної одиниці холодильнику	2 612,61 грн.
Розкид цін	мінімальна	максимальна
		1 687,00 грн.	5 589,00 грн.

Рис. 3.

У режимі формул ці розрахунки мають вигляд приведений на рис. 4.

Середня ціна товарної одиниці холодильнику		=СУММ(Холодильники!E3:E71)/69
Розкид цін	мінімальна	максимальна
		=МИН(Холодильники!E:E)	=МАКС(Холодильники!E:E)

Рис. 4.

3. Розрахунок середньої ціни холодильника різних виробників

Розрахунок середньої ціни холодильника різних виробників проведені на листі "Розрах" і мають такий вигляд як на рис. 5.

№	Виробник	Середня ціна холодильнику
кількість камер
1	2	3
1	Whirlpool	1 906,17 грн.	2 593,60 грн.	-
2	Bosch	2 471,00 грн.	3 484,08 грн.	3 710,50 грн.
3	Samsung	-	3 111,00 грн.	-
4	Zanussi	1 845,00 грн.	2 404,20 грн.	-
5	LG	-	2 698,10 грн.	-
6	Indesit	-	1 916,00 грн.	-
7	Electrolux	-	5 589,00 грн.	-
8	Ardo Co	-	2 111,33 грн.	-

Рис. 5.

У режимі формул ці розрахунки мають вигляд як на рис. 6.

№	Виробник	Середня ціна холодильнику
кількість камер
1	2	3
1	Whirlpool	=СРЗНАЧ(Лист3!E3:E8)	=СРЗНАЧ(Лист3!E9:E18)	-
2	Bosch	=СРЗНАЧ(Лист3!E20)	=СРЗНАЧ(Лист3!E21:E32)	=СРЗНАЧ(Лист3!E33:E34)
3	Samsung	-	=СРЗНАЧ(Лист3!E36)	-
4	Zanussi	=СРЗНАЧ(Лист3!E38:E42)	=СРЗНАЧ(Лист3!E43:E52)	-
5	LG	-	=СРЗНАЧ(Лист3!E54:E63)	-
6	Indesit	-	=СРЗНАЧ(Лист3!E65:E69)	-
7	Electrolux	-	=СРЗНАЧ(Лист3!E71)	-
8	Ardo Co	-	=СРЗНАЧ(Лист3!E73:E78)	-

Рис. 6.

За даними таблиці приведеної на листі "Розрах" побудована діаграма (рис. 7.), яка розміщена на листі "Діаграми"

Р
ис. 7.

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

Розрахунок кількості одиниць асортименту холодильників у різних продавців проведені на листі "Розрах" і мають такий вигляд як на рис. 8.

№	Магазин	Адреса	Кількість одиниць асортименту
1	Электро Мир	пр. Карла Маркса, 46	18
2	АБВ техника	пр. Карла Маркса, 81	27
3	LG	пр. Карла Маркса, 109	11
4	Whirlpool	ул. Артема, 1	13

Рис. 8.

У режимі формул ці розрахунки мають вигляд як на рис. 9.

№	Магазин	Адреса	Кількість одиниць асортименту
1	Электро Мир	пр. Карла Маркса, 46	=СЧЁТЕСЛИ(Холодильники!$B:$B;A23)
2	АБВ техника	пр. Карла Маркса, 81	=СЧЁТЕСЛИ(Холодильники!$B:$B;A24)
3	LG	пр. Карла Маркса, 109	=СЧЁТЕСЛИ(Холодильники!$B:$B;A25)
4	Whirlpool	ул. Артема, 1	=СЧЁТЕСЛИ(Холодильники!$B:$B;A26)

Рис. 9.

За даними таблиці кількості одиниць асортименту холодильників у різних продавців, приведеної на листі "Розрах" побудована діаграма (рис. 10.), яка розміщена на листі "Діаграми".

Р
ис. 10.

Висновки

За розрахунками, що було проведено і які приведені на рис. 3, 5, 8 та за діаграмами рис. 7, 10 можна зробити такі висновки:

середня ціна холодильника складає 2 612,61 грн.;

мінімальна ціна становить 1 687,00 грн. для холодильника ART 200, виробник Whirlpool, магазин "АБВ техника";

максимальна – 5 589,00 грн. для холодильника Electrolux ER, виробник Electrolux магазин "АБВ техника";

на ринку основну частину складають холодильники, які мають дві камери;

найбільший асортимент холодильників спостерігався в магазині "АБВ техника", пр. Карла Маркса, 81.

Список літератури

Блатнер П., Ульрих Л., Кук К. Использование Excel 2000. Специальное издание. -М., К., СПб. 2000.

Карпов Б. Microsoft Office 2000 Справочник. - СПб.: Питер, 2000.

Кириллов В.В. Структуризованный язык запросов (SQL). – СПб.: ИТМО, 1994. – 80 с.

Мартин Дж. Планирование развития автоматизированных систем. – М.: Финансы и статистика, 1984. – 196 с.

Тиори Т., Фрай Дж. Проектирование структур баз данных. В 2 кн., – М.: Мир, 1985. Кн. 1. – 287 с.: Кн. 2. – 320 с.

Хаббард Дж. Автоматизированное проектирование баз данных. – М.: Мир, 1984. – 294 с.

Цикритизис Д., Лоховски Ф. Модели данных. – М.: Финансы и статистика, 1985. – 344 с.

Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичный курс інформатики. - К.: Фенікс, 1997.

Ахметов К., Борзенко А. Современный персональный компьютер. - М.: Компьютер-пресс, 1995.

Визе Макс, Word для Windows. - М.: Биком, 1998.

ДоджМ., Кината К., Стинсон К. Эффективная работа с Excel 7.0 для Windows - СПб.: Питер, 1996.

Фигурнов В.Э. IBM PC для пользователей. – Изд. 7-е. – М.: ИНФРА, 1997.

Колесников А. Windows 98: русифицированная версия – К.: BHV, 2000.

Стоцкий Ю. Office 2000: самоучитель–СПб.: Питер, 2000.

Электронный офис. В 2-х тт./ Каратыгин С. и др. – М.: Нолидж, 1999.

Йорг Шиб. Windows: сотни полезных советов. - М.: Бином, 1996.

Левин А. Самоучитель работы на компьютере. - М.: Нолидж, 1999.

Мартик Альтхауз Михаэль Орлет, Excel 7.0. - М.: Биком, 1998.

Ботт Эд. Использование Microsoft Office 97. - К.: Диалектика, 1997.

Николь Н., Альбрехт Р. Электронные таблицы Excel 7.0. -М.: ЭКОМ., 1998.

Николь Н., Альбрехт Р. Электронные таблицы Excel 7.0 для квалифицированных пользователей. -М.: ЭКОМ., 1998.

Пасько В.П. Word 7.0 для Windows – К.: BHV, 1998.

Новиков Ф.А., Яценко А. Microsoft Office в целом–СПб.: BHV-Санкт-Петербург, 2000.

Пробитюк А. Excel 7.0 для Windows в бюро. -К., BHV, 1997.

Стинсон К. Эффективная работа в Windows 98. - СПб.: Питер, 1998.

Шаффмайстер У., Пасько В. Word 7.0 для Windows в бюро. -К., BHV, 1998.