У розглянутому прикладі перемикач встановлюється в положення В списку або базі даних MS Excel.

Тема. Бази даних в MS EXCEL

Мета роботи: Вивчення можливостей пакета MS Excel при роботі з базами даних. Придбання навичок створення та обробки БД.

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

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

Т.ч., можна сказати, що База даних (БД) - це сукупність різних записів, що володіють певними властивостями.

У першому рядку будь бази даних обов'язково повинні бути вказані імена полів. Максимальний розмір бази даних в MS Excel визначається можливостями версії Excel (число рядків і число стовпців у листі).

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

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

ПРИКЛАД 6.1.

Найменування товару	Дата	Постачальник	Місто	Закупівельна ціна (грн.)	Відпускна ціна	Транспортні витрати	Кількість	Прибуток
Ковбаса	02.10.03	Колбіко	Макіївка	15,80	18,00	20 грн.
Сир	05.10.03	Ромол	Харків	2,20	2,50	45 грн ..

Сформуємо БД на першому аркуші MS Excel, після чого лист перейменуємо (рис. 6.1).

Сформуємо поле Прибуток. Для цього в комірку I2 введемо формулу:

I2 = (F2-E2) * Н2-G2

За допомогою маркера заповнення скопіюємо цю формулу в комірки I2: I18.

В результаті лист прийме вид зображений на рис. 6.2.

Рис. 6.1. База даних «Постачальники товару"

Рис. 6.2. Формування поля "Прибуток"

Сортування баз даних

Для сортування бази виділимо осередку A1: I18 і виконаємо команду Дані Сортування.

Рис. 6.3. Діалогове вікно Сортування діапазону

Додавання підсумків в базу даних

Порахувати сумарну прибуток при продажу всіх продуктів можна за допомогою функції СУММ. Якщо ж скористатися пунктом меню Підсумки, то з'являється можливість розрахувати суму прибутку по кожному товару або по кожному постачальнику. Отже, для підсумовування прибутку по кожному з товарів зробимо наступне: відсортуємо БД по найменуванню товарів (рис. 6.3) і виконаємо команду Дані Підсумки (рис. 6.4). Робочий лист прийме вид зображений на рис. 6.5.

Рис. 6.4. Діалогове вікно Проміжні підсумки

Рис. 6.5. Підсумок по прибутку за всіма видами товару

Фільтрація бази даних

Процес пошуку і відбору інформації в базі даних MS Excel називається фільтрацією. У MS Excel є два види фільтра: Автофільтр і Розширений фільтр.

Автофільтр

Для включення автофільтру необхідно:

1. Клацнути в будь-якому місці бази даних, в нашому випадку діапазон A1: I18.

2. Виконати команду Дані Фільтр Автофільтр. Клацнути по кнопці списку праворуч від потрібного поля (наприклад, поле Найменування товару). Вікно БД прийме вигляд, зображений на рис. 6.6. В якості умови відбору можна вибрати або будь-яке значення зі списку, або пункт Условие.

Рис. 6.6. БД після застосування команди Автофильтр

Виберемов якості умови значення Ковбаса. В результаті вБД залишиться інформація, що стосується тільки поставок ковбаси (рис. 6.7).

Рис. 6.7. Фрагмент відфільтрованої БД

Результат буде тим же, якщо зі списку запропонованих фільтрів вибрати Умова ... і в діалоговому вікні (рис. 6.8), ввести в якості умови "дорівнює Ковбаса".

Рис. 6.8. Діалогове вікно Користувальницький Автофільтр

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

Рис. 6.9. Фрагмент діалогового вікна Користувацький Автофільтр

Розширений фільтр

Для виділення з БД більш складних умов можна скористатися командою Дані Фільтр Розширений фільтр. Розглянемо роботу з розширеним фільтром на прикладі виділення з БД записів донецьких виробників ковбаси. Для цього визначимо область для зберігання умов відбору. Кожне умова записується в два осередки: в верхню - ім'я поля, в нижню - знак відносини (>, <,> =, <=, <>) і значення. У нашому випадку в комірку K1 введемо Найменування товару, в клітинку K2-Ковбаса, в клітинку L1-Місто, в клітинку L2-Донецьк (рис. 6.10).

Рис. 6.10. Область для зберігання умов відбору

Тепер виконаємо команду Дані Фільтр Розширений фільтр (ріс.6.11).

Рис. 6.11. Діалогове вікно розширений фільтр

В даному випадку дві умови з'єднані логічним дією "І". Для об'єднання з допомогою "АБО" необхідно між ім'ям поля і умовою пропустити рядок (рис. 6.12).

Рис. 6.12. З'єднання умов за допомогою логічного "АБО"

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

Зведена таблиця

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

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

· у списку або базі даних MS Excel - якщо дані беруться з одного робочого листа;

· в зовнішньому джерелі даних - якщо дані беруться із зовнішньої бази даних;

· в декількох діапазонах консолідації - якщо дані беруться з декількох робочих листів;

· в іншій зведеній таблиці - якщо зведена таблиця створюється на підставі даних іншої зведеної таблиці.

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

Рис. 6.13. Перше діалогове вікно Майстер зведених таблиць

У розглянутому прикладі перемикач встановлюється в положення В списку або базі даних MS Excel.

У полі Діапазон друге діалогове вікно Майстер зведених таблиць (рис. 6.14) необхідно вказати діапазон, на підставі якого будується зведена таблиця. У нашому випадку: "Постачальники товару"!$A$:$I$18.

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

Рис. 6.14. Друге діалогове вікно Майстер зведених таблиць

Рис. 6.15. Третє діалогове вікно Майстер зведених таблиць

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

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

У вікні є чотири області:

· Рядок - для використання даних поля, розташованого в цій галузі, як заголовок рядка;

· Стовпець - для використання даних поля, розташованого в цій галузі, як заголовки стовпців;

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

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

Рис. 6.16. Діалогове вікно для створення макета зведеної таблиці

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

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

Рис. 6.17. Діалогове вікно Обчислення поля зведеної таблиці

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

Рис. 6.18. Діалогове вікно Параметри зведеної таблиці

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

Рис. 6. 19. Зведена таблиця підрахунку прибутку по кожному товару

ВАРІАНТИ ЗАВДАНЬ

1. Створіть табличний документ і збережіть його в особистій папці.

2. Заповніть таблицю даними і формулами відповідно до умовою завдання (не менше 10 рядків).

3. Використовуючи функцію Підсумки ..., заповнити поля відмічені зірочкою.

4. Встановіть фільтри відповідно до варіанта завдання.

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

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

7. Створіть діаграми за зведеними таблицями.

Варіант № 1. Відомість з продажу сигарет

Марка	Місяць	Фактична кількість	Ціна	Сума	План
Dunhill	Лютий		2,5
...	...	...	...	...	...
РАЗОМ		*		*

1. Сформувати стовпець суми.

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

· для Pall-Mal, Bond, L & M - 250;

· для Dunhill - 100;

· для Marlboro - 150.

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

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

5. Встановити фільтр для відображення всіх поставок сигарет Bond.

Варіант № 2. Відомість про реалізацію товарів

Дата реалізації	Найменування товарів	Постачальника	Ціна	Кількість	Сума	Знижка	Разом
1/02/02	Шкарпетки	База № 2
2/02/02	Сукня	База № 5
3/02/02	Костюм	База № 7
1/02/02	Костюм	База № 9
4/02/02	Шкарпетки	База № 2
5/02/02	Костюм	База № 9
10/02/02	Костюм	База № 7
11/02/02	Шкарпетки	База № 2
2/02/02	Сукня	База № 5
6/02/02	Шкарпетки	База № 2
5/02/02	Костюм	База № 7
4/02/02	Сукня	База № 5
3/02/02	Костюм	База № 9
РАЗОМ				*	*	*	*

1. Сформувати поле Сума.

2. Поле Знижка заповнити таким чином:

· для суми менше 100 грн. - 0%;

· для суми від 100 грн. до 1000 грн. - 2%;

· для суми понад 1000грн. - 5%.

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

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

5. Використовуючи розширений фільтр, відфільтрувати дані для відображення всіх товарів, отриманих з Баз № 2 та № 9.

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

Варіант № 3. Доставка товару

Назва морозива	Місяць	Постачальник	Закупівельна ціна	Відпускна ціна	Кількість	Вартість доставки	Загальний прибуток
Пломбір	Липень	Геркулес	0,69	0,85
Пломбір	Липень	Вінтер
Морозко	Червень	Геркулес
Ескімо	Серпень	Мушкетер
Каштан	Вересень	Вінтер
Пломбір	Вересень
...	...	...	...	...	...	...	...
Всього			*	*	*	*	*

1. Поле Вартість доставки заповнюється наступним чином:

· Пломбір - 90 грн.

· Морозко - 150 грн.

· Крем-брюле - 50 грн.

2. Сформувати поле Загальний прибуток.

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

4. Використовуючи автофільтр, відфільтрувати всі закупівлі пломбіру влітку.

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

Варіант № 4. Поставки товару

Дата поставки	Найменування товару	Постачальник товару	Ціна	Кількість	Сума	Дилерська знижка
1/04/02	Портфель	ПП "Дніпро"
1/04/02	Ремінь чоловічий	ТОВ "Донгалантерея"
1/04/02	Косметичний набір Lancom	ПП "Іванофф"
1/04/02	Сумка дорожня	ПП "Дніпро"
5/04/02	Косметичний набір Lancom	ПП "Іванофф"
5/04/02	Сумка дорожня	ПП "Дніпро"
5/04/02	Ремінь чоловічий	ТОВ "Донгалантерея"
10/04/02	Косметичний набір Lancom	ПП "Іванофф"
10/04/02	Сумка дорожня	ТОВ "Донгалантерея"
11/04/02	Сумка дорожня	ПП "Дніпро"
12/04/02	Косметичний набір Lancom	ПП "Іванофф"
15/04/02	Ремінь чоловічий	ТОВ "Донгалантерея"
Всього				*	*

1. Сформувати поле Сума.

2. Поле "Дилерська знижка" заповнюється наступним чином:

· ПП "Іванофф" - 5%.

· ТОВ "Донгалантерея" - 10%.

· ПП "Дніпро" - 6%.

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

4. Використовуючи розширений фільтр, відфільтрувати дані для відображення всіх поставок від ПП "Дніпро" після 5/04/02.

5. Використовуючи автофільтр, відобразити всі постачання косметики обсягом понад 30 одиниць після 5/02/02.

Варіант № 5. ТОВ "Світ ПК". Відомість виконання плану товарообігу за підрозділами підприємства

Найменування підрозділу	Найменування товарної групи	Постачальник	Сума замовлення	Сума фактичної реалізації	Прибуток	Форма оплати
Відділ збуту	Комп'ютери	ТОВ "Електронна техніка"
Магазин № 1	Оргтехніка	ТОВ "Імперія комп'ютерів"
Магазин № 4		Фірма "Мультимедіа"
...
Сума			*	*	*

1. Заповнити поле Прибуток.

2. Поле Форма оплати заповнюється наступним чином:

· ТОВ "Електронна техніка", - готівка;

· ТОВ "Імперія комп'ютерів", - безготівкові;

· Фірма "Мультимедіа" - кредит.

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

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

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

Варіант № 6. Відомість закупівлі чаю

Тип чаю	Форма упаковки	Виробник	Місяць	Ціна, грн	Кількість	Сума
Зелений	Пачка	Riston	Січень	1,8
Чорний	Пакетики	Ахмад
Червоний		Dilmah
Жовтий		Едвін
...
Всього					*	*

1. Сформувати поле Сума.

2. У графу Форма упаковки заносять такі значення: пачка, пакетики, банку.

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

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

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

Варіант № 7. ТОВ "продовольство"

Група товарів	Найменування товарів	Кількість	Закупівельна ціна	Відпускна ціна	Сума	Прибуток
Хлібобулочні	Хліб
Молочні	Молоко
М'ясні	Паштет
Молочні	Сир
М'ясні	Паштет
М'ясні	М'ясо
Хлібобулочні	Хліб
М'ясні	М'ясо
М'ясні	Паштет
Хлібобулочні	Батон
Молочні	Ряжанка
Всього		*		*		*

1. Сформувати поле Сума.

2. Поле Прибуток заноситься значення ТАК, якщо відпускна ціна перевищує закупівельну ціну на 10%, інакше НІ.

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

4. Використовуючи автофільтр, вивести інформацію про м'ясних продуктах ціною менше 7 грн.

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

Варіант № 8. Відомість реалізації товарів

Найменування товару	Дата реалізації	Постачальник	Ціна	Кількість	Сума
Сир	1/04/02
Масло	1/04/02
Сметана	1/04/02
Молоко	1/04/02
Сметана	4/04/02
Молоко	5/04/02
Сир	5/04/02
Масло	6/04/02
...
Всього				*	*

1. У графу Постачальник заносяться в залежності від найменування товару наступні значення:

· Сир, масло - Молокозавод № 1;

· Сметана, молоко - Молокозавод № 2;

· Йогурт - ПП "Свіжість".

2. Сформувати поле Сума.

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

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

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

Варіант № 9. Монітори

Виробник	Тип	Модель	Ціна (грн)	Ціна (у.о.)	Кількість	Вартість (грн)
Samsung	17 "	Samsung 757NF
Samsung	17 "	Samtron 76DF
LG	17 "	LG ФК 710PH
Philips	17 "	Philips 107P4Q
SONY	17 "
Samtron	19 "
SONY	19 "
Samsung	15 "
...
ВСЬОГО			*	*	*	*

1. Сформувати поле Ціна (у.о.), якщо 1 $ = 5,44 грн.

2. Поле Кількість заповнити таким чином:

· якщо ціна менша або дорівнює 350, то 10

· якщо ціна від 350 до 1000 то 7

· інакше 5

3. Сформувати поле Вартість.

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

5. Використовуючи автофільтр, необхідно відфільтрувати дані для відображення всіх моніторів, вироблених фірмою Samsung.

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

Варіант № 10. Інформація про ряд країн світу

Півкуля Землі	Частина світла	Країна	Площа, тис. кв. км.	Населення, тис. чол.	Щільність населення, чол. / Кв. км.
Східна	Африка	Гвінея
Східна	Европа	Данія	44 .5
Західна	Пд. Америка	Уругвай
Східна	Африка	Сенегал
Західна	Пд. Америка	Бразилія
Західна	Пд. Америка	Перу
Західна	Пд. Америка	Чилі
Східна	Европа	Швеція
Східна	Азія	В'єтнам	331,7
Східна	Африка	Ліберія
Східна	Азія	Монголія	1566,5
Східна	Азія	Японія
...

1. Сформувати поле Півкуля, в залежності від частини світу.

2. Сформувати поле Щільність населення.

3. Визначити загальну площу і загальна кількість населення кожного півкулі і кожній частині світу.

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

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

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

Варіант № 11. Співробітники

Ф. І. Про	Підрозділ	Посада	Дата надходження на роботу	Ставка	Оклад	Надбавка	Нараховано
Іванов П. Ф.	каф. фізики	Доцент	13.08.1991
Петров А.Р.	каф. ВМ	Доцент
Сидоров Р.Л.	каф. ТМ	Доцент
Козлов А.Д.	каф. фізики	Асистент
Іванов П. Ф	каф. ТМ	Доцент		0,25
Сидоров Р. Л	каф. ВМ	Доцент		0,25
...
РАЗОМ							*

1. Сформувати поле Оклад, враховуючи, що ставка доцента - 560 грн, старшого викладача - 470, асистента - 380.

2. Поле Надбавка заповнюється в залежності від стажу роботи на підприємстві:

· При стажі понад 3 роки - 10% від окладу;

· При стажі більше 10 років - 20% від окладу;

· При стажі більше 20 років - 30% від окладу.

3. Сформувати поле Нараховано.

4. Використовуючи функцію Підсумки ..., визначити середній оклад по кожному підрозділу.

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

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

Варіант № 12. БД "БАНК"

Прізвище клієнта	Тип вкладу	Розмір внеску (грн)	Вклад (у.о.)	Відділення банку	Примітка
Сидоров	Поточний			Північне
Іванов	Депозит			Центральне
Петров	Депозит			Західне
Сидоров	Депозит			Західне
Козлов	Поточний			Центральне
Васін	Поточний			Північне
Сидоров	Депозит			Західне
Козлов	Депозит			Північне
Васін	Депозит			Центральне
Петров	Поточний			Центральне
...
РАЗОМ		*	*

1. Заповнити поле Вклад (у.о.), якщо 1 $ = 5,44 грн.

2. Заповнити поле Примітка:

· Сидоров - Тимчасово вибув

· Козлов - Змінив адресу

· Іванов - Переклав в інше відділення

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

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

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

Варіант № 13. Принтери

Виробник	Тип	Модель	Ціна (грн)	Ціна (у.о.)	Кількість	Вартість (грн)
LEXMARK	струменевий	Z25
EPSON	струменевий	C62
CANON	струменевий	С-200
HP	струменевий	3420C
EPSON	матричний	LX-300
CANON	лазерний	LBP-810	9 52
HP	лазерний	1000W
HP	лазерний
Samsung	лазерний	ML-1210
LEXMARK	струменевий	Z45
...
ВСЬОГО					*	*

1. Сформувати поле Ціна (у.о.)

· якщо ціна в грн. менше 500, то 1 $ = 5,56 грн.

· якщо ціна в грн. від 500 до 100, то 1 $ = 5,44 грн.

· інакше 1 $ = 5,37 грн.

2. Сформувати поле Вартість.

3. Використовуючи функцію Підсумки ..., обчислити середні ціни принтерів кожного виробника в грн., І кількість принтерів кожного типу.

4. Використовуючи автофільтр, відфільтрувати дані для відображення всіх принтерів, вироблених фірмою HP.

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

Варіант № 14. ТОВ "АВТО"

Фірма	Марка	Ціна (у.о.)	Ціна (грн.)	Потужність двигуна, к.с.	Знижка	Тип кузова
Mits ubishi	Pajero Sport 2,5 TD GLX	$ 29 990				позашляховик
Mitsubishi	Pajero 3,5 GDI GLS AT	$ 49 590				позашляховик
Skoda	Fabia Combi 1,4 Класичний	$ 10 500				комбі
Mitsubishi	Galant 2,5-V6 Elegance	$ 26 900				седан
Mitsubishi	Galant 2,0 Comfort	$ 22 900				седан
Mitsubishi	Pajero Sport 3,0 V6 GLX	$ 36 590				позашляховик
Peugeot		$ 12 930				хетчбек
Skoda	Fabia Sedan 1,4 Класичний	$ 10 200				седан
Skoda	Octavia 1,8 Elegance	$ 18 800				седан
Skoda	Octavia 1,6 Класичний	$ 12 100				седан
Peugeot		$ 8775				седан
Skoda	Octavia Ambiente 1,6	$ 12 450				седан
Skoda	Fabia 1,4 Basic	$ 8600				хетчбек
Skoda	Fabia 1,4 Comfort	$ 9990				седан
...
РАЗОМ		*	*		*

1. Сформувати поле Ціна (у.о.), 1 $ = 5,37 грн

2. Сформувати поле Знижка, наступним чином:

· потужність двигуна менше 100 л.с. - 2%

· потужність двигуна від 100 до 150 л.с. - 1,5%

· інакше - 1%

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

4. Використовуючи автофільтр, відфільтрувати дані для відображення всіх автомобілів марки Skoda.

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

Варіант № 15. Відомості про ряд геометричних тіл

Номер тіла	Вид тіла	Вид матеріалу	Щільність матеріалу, г/см3	Обсяг тіла, см3	Маса тіла, г
	Куб		7,8
	Шар		2,6
	Куб		1,5
	Куб		1,2
	Шар		2,6
	Шар		1,5
	Шар		7,8
	Куб		1,2
	Куб		7,1
	Куб		8,9
1 січня	Шар		1,3
...
Разом				*	*

1. Сформувати поле Маса тіла.

2. Поле Вид матеріалу заповнити таким чином:

· щільність більше 1,5 - метал,

· інакше пластмаса.

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

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

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

Варіант № 16. Відомості про ряд геометричних фігур

Номер фігури	Вид фігури	Тип фігури	Сторона 1/1 Катет	Сторона 2/2 Катет	Площа фігури
	Трикутник
	Трикутник
	Прямокутник
	Прямокутник
	Трикутник
	Трикутник
	Прямокутник
	Трикутник
	Трикутник		5,5	2,4
...
Разом

1. Сформувати поле Тип фігури:

· Сторона 1 = Сторона 2 - квадрат

· Сторона 1 ≠ Сторона 2 - неквадрат

· Катет 1 = Катет 2 - рівносторонній

· Катет 1 ≠ Катет 2 - прямокутний

2. Сформувати поле Площа фігури, залежно від її типу.

3. Використовуючи функцію Підсумки ..., обчислити середню площу всіх трикутників і всіх прямокутників.

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

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

Варіант № 17. Акціонери фірми "купи-продай"

№ з / п	Прізвище	Випуск акцій	Вид акції	Кількість	Номінальна вартість акції	Загальна вартість акцій
	Сідіроми
	Моніторів
	Сідіроми
	Мишкін
	Сідіроми
	Моніторів
	Мишкін
	Моніторів
	Сідіроми
	Моніторів
	Діскеткін
...
Разом				*		*

1. Сформувати поле Загальна вартість.

2. Сформувати поле Вид акції, таким чином:

· номінальна вартість 500 грн. - Звичайна

· номінальна вартість 5000 грн. - Привілейована

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

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

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

Варіант № 18. Відомості про проходження автомобілями ділянок шляху

№ з / п	Фірма	Вид автомобіля	Середня швидкість на ділянці, км / год	Час проходження ділянки, ч	Довжина ділянки, км
	Nissan	Легковий		0,7
	Fiat	Вантажний		2,3
	Nissan			4,3
	Nissan			0,9
	Nissan			1,6
	Fiat			2,8
	Fiat			3,6
	Fiat			4,1
	Fiat			4,6
	Nissan			3,5
	Nissan			4,5
	Fiat			1,9
...
Разом				*	*