Лабораторна робота №9

Тема: Розв’язання прикладних задач засобами MS Excel.

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

Короткі теоретичні відомості.

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

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

Сортування – це упорядкування по зростанню або зменшенню. Відсортувати дані можна максимум за трьома полями одночасно. Слід виділити список і виконати команду Данные / Сортировка. В діалоговому вікні Сортировка диапазона слід виділити 3 поля, по яким слід сортувати, і вказати як ідентифікувати поля (за надписами (перший рядок діапазону).

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

Щоб установити автофільтр, треба помістити курсор всередину таблиці і вибрати в меню Данные опції Фильтр і Автофильтр. В результаті біля заголовків стовпців з'являться кнопки списків, що розгортаються, з яких можна вибрати наступні опції:

• (Все) - знімає фільтр із даного стовпця і забезпечує вивід рядків із будь-якими значеннями в даному стовпці;
• (Первые 10...) - дозволяє відфільтрувати задану кількість або заданий відсоток найбільших або найменших елементів даного стовпця;
• (Условие...) дозволяє задати одну або дві умови фільтрації у формі рівності або нерівності

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

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

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

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

У комірки допоміжної таблиці можна включати і нерівності. Наприклад, вираз < 100 забезпечить фільтрацію чисел, менших ста.

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

Рисунок 1

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

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

Для зняття розширеного фільтра потрібно вибрати в меню Данные опції Фильтр і Отобразить все.

Прядок виконання роботи

1.Створіть у середовищі Excel наступну таблицю «База данных перечня работ и расценок строительной фирмы». Заповніть її 10-ма довільними записами.

2. Ствопці таблиці «Стоимость для физических лиц со скидкой» та «Стоимость для юридических лиц со скидкой» розрахувати за формулою згідно з вказаною скидкою.

3. Створити аркуш «Автофільтр» та скопіювати туди початкову створену таблицю. За допомогою засобу «Автофільтр» відобразити:

- Роботи, що виконуються бригадою №5.

- Роботи, що виконуються за один тиждень.

- Вартість фарбування стін.

4. Створити аркуш «Сортування» та скопіювати туди таблицю с першого аркушу. Відсортувати:

- Роботи в кожній бригаді.

- Термін роботи в кожній бригаді.

5. Створити аркуш «Розширений фільтр» та скопіювати туди таблицю с першого аркушу. За допомогою засобу «Розширений фільтр» відобразити бригади, що виконують роботи терміном 1 місяць і бригади, що виконують роботи з шифром 2963-285.

6. Створити аркуш «Підсумки» та скопіювати туди таблицю с першого аркушу. За допомогою засобу «Підсумки» обчислити:

- Кількість робіт, за які відповідає кожна бригада.

- Кількість робіт з однаковим терміном виконання роботи.

Контрольні запитання

1. Поясніть поняття бази даних.

2. Які функції можна виконувати над простішими БД в табличному процесорі Excel?

3. Як встановлюється і знімається автофільтр?

4. Як користуватися автофільтром?

5. Як встановлюється і знімається розширений фільтр?

6. Чим розширений фільтр перевищує можливості автофільтра?