Типові задачі при роботі з готовою таблицею

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

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

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

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

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

Усі дані в таблиці можна захистити від внесення змін (наприклад, сторонніми особами) командою Сервис→ Защита→Защитить лист. Можна зняти захист з деяких клітинок, щоб у них можна було робити зміни (це роблять на закладці Защита).

Створення та редагування бази даних в Microsoft Excel

 

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

Для того, щоб вводити дані у список за допомогою форми даних необхідно виконати команду: Данные®Форма (перед цим варто задати шапку таблиці – заголовки стовпчиків). Після виклику форми даних ’зявляється вікно “ форма даних “ (мал.14).

Мал.14

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

Над списком можна виконувати такі дії:

- редагування (внесення змін);

- сортування по одному чи по кількох полях;

- підведення підсумків;

- побудова зведених таблиць;

- фільтрування даних (пошук інформації).

 

Впорядкування таблиці

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

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

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

(пробіл)! " # $ % & () *,. /:;? @ [ \ ] ^ _ " { | } ~ + < = > 0 1 2 3 4 5 6 7 8 9 А В С D Е F G Н I J К L М N О Р Q R S Т U V W X Y Z А Б В Г Д Е Е Є Ж З И І(укр.) Ї Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ь Ы Ъ Э Ю Я

Для впорядкування таблиці необхідно виділити клітинку або діапазон клітинок у списку, який необхідно відсортувати і застосувати команду Данные → Сортировка і задати необхідні параметри сортування у вікні Сортировка диапазона (Мал.15) або натиснути на одну з кнопок на панелі інструментів:

— сортування за зростанням або

— сортування за спаданням.

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

Для сортування за рядком потрібно встановити Параметры… сортування і в запропонованому діалоговому вікні Сортировать диапазон по строкам / по столбцам вибрати потрібне.

Треба звернути увагу на те, що у випадку виділення неповних рядків можна отримати зовсім неправильні результати, оскільки будуть переставлені фрагменти рядків. Якщо впорядкування виявилося невдалим, можна його відразу скасувати, клацнувши по кнопці Отменить на панелі інструментів або натиснувши клавіші Ctrl+Z.

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

Мал. 15. Діалогове вікно Сортировка диапазона

Пошук даних в таблиці

Найпростіший спосіб пошуку даних у таблиці здійснюється за допомогою команди Правка→Найти. У діалоговому вікні Найти (мал. 16) у полі Найти потрібно набрати фрагмент змісту комірки. Потім треба клацнути по кнопці Найти далее. Якщо пошук виявився вдалим, буде активізовано комірку, що містить зазначений фрагмент. Щоб закрити діалогове вікно пошуку треба клацнути по кнопці Закрыть.

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

Мал. 16. Пошук за допомогою вікна діалогу Найти

Для пошуку зручно також використовувати форму даних.

Для цього потрібно виділити діапазон пошуку, включивши у нього рядок із заголовками таблиці, обрати команду меню Данные → Форма.

Mал. 17. Форма для роботи із таблицею

У вікні форми (мал. 17) натисніть на кнопку Критерии, а в наступному діалоговому вікні (мал. 18) введіть у потрібні поля ознаки, за якими має відбуватися пошук. Наприклад, якщо пошук здійснюється за значеннями в текстовому полі, наберіть перші символи, які однозначно визначають шукані дані. Якщо ви вводите дані в декілька полів, то пошук ведеться за кількома критеріями. Часто достатньо ввести в поле дві-три перші літери. Не суттєво, якими літерами — малими або великими, — введено інформацію в поле.

Мал. 18. Задання критеріїв пошуку

У діалоговому вікні на мал. 18 введено умови пошуку потрібних ігор: жанр — Sport і ціна менше $25. Для перегляду знайдених записів натисніть кнопку Далее, і у вікні форми будуть відображені значення полів запису. Для перегляду всіх відібраних записів користуйтеся кнопками Далее і Назад.

 

Фільтрування даних

Фільтрація — це виділення рядків таблиці, які відповідають заданим критеріям.

Фільтрування можна виконувати 2-ма способами:

1) Викликати вікно форми даних і натиснути у вікні форми даних кнопку “ Критерии ”. У потрібних полях вказати значення для пошуку (ввести), і натиснути кнопку “ Далее ”. Якщо у списку є рядки, що задовільняють введеним умовам, то ними заповниться форма даних. Потім знову натискаємо кнопку “ Далее ” і у формі відобразиться наступний запис, який задовільняє введеним умовам. Натискаємо на кнопці “ Далее ” до тих пір, поки не відбудеться переходу на останній запис, який задовільняє введеним умовам.

2) Є ще один спосіб фільтрування даних –це використання простого фільтру. Для цього необхідно встановити курсор в межах бази даних і виконати команду: Данные®Фильтр®Автофильтр. Після виконання цієї команди біля заголовків полів (стовпчиків) з’являться кнопки для розгортання списків (). Якщо розгорнути список коло одного із заголовків (натиснути на кнопку списку), то побачите список всіх значень цього поля і ще такі елементи списку:
- Все;

- Первые 10;

- Условие.

Наприклад:

У таблиці в стовпчику А набрані залізничні станції відправлення (Харків-Пасажирський, Левада, Балашовка), у стовпчику В пункти призначення (Мерефа, Люботин, Зміїв тощо), у стовпчику С – час відправлення. Потрібно відібрати тільки приміські потяги, що прямують до Люботина.

Виділяємо стовпчик з пунктами призначення. В основному меню вибираємо пункт „ Данные ”. У меню, що відкривається, - команду „ Фільтр ”, підпункт „ Автофільтр ”. У верхній комірці стовпчика відкриється меню автофільтра (мал.19).

Висхідне меню автофільтра

Мал.19

 

Вибираємо в цьому списку рядок „Люботин” (усі значення що зустрічаються в цьому стовпчику будуть виведені в меню). Після вибору рядка таблиця набуде наступного вигляду – мал.20.

Відфільтрована таблиця

Мал.20

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

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

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

Мал. 21. Створення автофільтра користувача

У даному діалоговому вікні можна задати складну умову, яка формується з двох простих умов, поєднаних логічним И (якщо потрібно, щоб обидві умови виконувалися) або ИЛИ (якщо достатньо, щоб виконувалася хоча б одна умова з двох). Для текстових значень, окрім стандартних умов, можливі такі: Начинается (не начинается) із заданого тексту, Заканчивается (не заканчивается) заданим текстом і Содержит (не содержит) заданий текст.

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

 

У меню Данные → Фильтр є корисна опція Расширенный фильтр (мал. 22), що дозволяє оформити критерій для фільтрації у вигляді таблиці і вивести відфільтровані рядки в будь-який діапазон робочого аркуша.

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

Порядок роботи з розширеним фільтром такий.

1) Спочатку потрібно створити таблицю-критерій. Для цього в рядок під первинною таблицею, пропустивши декілька рядків, скопіюйте назви усіх стовпців. Рядком нижче під назвами введіть із клавіатури критерії відбору. Критеріями виступатимуть текстові записи, що збігаються зі значеннями комірок (для стовпців із текстом), або числа чи умови, що містять операції порівняння (для стовпців із числами). Таблиця-критерій може розміщуватися у будь-якому місці аркуша, але найзручніше її розташувати під первинною таблицею (див. мал. 23).

2) Активізуйте будь-яку комірку первинної таблиці і виконайте команду Данные → Фильтр → Расширенный фильтр.

3) У діалоговому вікні Расширенный фильтр (мал. 22) задайте такі параметри. У полі Исходный диапазон введіть діапазон таблиці, що фільтрується (програма звичайно сама його правильно встановлює). У полі Диапазон условий задайте діапазон таблиці-критерію (у даному випадку $А$22:$С$23). Процедура задання діапазонів проста. Клацніть по кнопці справа від поля введення і потім на робочому аркуші виділіть мишею необхідний діапазон. Після цього повторно клацніть по кнопці справа від поля введення, і ви повернетеся в діалогове вікно Расширенный фильтр.

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

5) Закрийте діалогове вікно Расширенный фильтр клацанням по кнопці ОК. Якщо ви правильно зазначили діапазон і критерії фільтрації, то на аркуші з'явиться Tаблиця-результат (рядки 26-27 на мал. 23).

Мал. 23. Застосування розширеного фільтра

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

 

5.7. Запитання, завдання, тести

Запитання для самоконтролю:

1. Що таке база даних?
2. Які є різновиди баз даних?
3. З яких елементів складається таблична база даних?
4. Які етапи планування бази даних Exel?
5. Як називаються окремі елементи інфор­мації про об'єкт бази даних (властивості)?
6. Яке співвідношення між записом і поля­ми в табличній базі даних?
7. Що таке «сортування»?
8. Які засоби існують для впорядкування робочої книги?
9. Як провести пошук потрібних даних у табличній базі даних?
10. Що є основним елементом даних у табличній базі даних?
11. Що містить запис табличної бази даних?
12. Як найшвидше впорядкувати всю таблицю за одним полем?
13. Яким чином провести впорядкування за двома полями?
14. Що таке Автофільтр?
15. Які дії треба виконати для використання автофільтру?
16. У чому полягає фільтрація даних?
17. Як виконують найпростіше сортування даних?
18. Чим відрізняється розширений фільтр від автофільтру?
19. Як застосовується розширений фільтр?

Завдання для самостійного виконання

1) У комірках стовпчика А наберіть назви страв ресторану (15 страв), у стовпчику В – їхні ціни, у стовпчику С по одній цифрі – 1 (перша страва), 2 (друга страва), 3 (десерт). Тричі скопіювати цю таблицю в поточній книзі одну під іншою. У першій таблиці установити автофільтр на перші страви, у другій – на другі, у третій – на десерт. Обвести таблиці рамками, зробити заголовки, тобто підготувати для друку.

2) Створити список (БД) “Підсумки” (поля записів: прізвище, алгебра, інформатика, укр.мова, історія). Заповнити таблицю на менше як 15-ма записами (довільні прізвища та відповідні оцінки).

- При заповненні БД користуватися засобом “Форма даних”;

- посортувати БД по полю “Прізвище”;

- засобами форми даних відібрати учнів, які мають з інформатики 10 балів;

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

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

3) Побудуйте за зразком таблицю і виконайте завдання.

ПЕРЕЛІК КНИГ

№	Автор	Назва	Кількість сторінок	Рік видання
	Іванов	Алгебра
	Тургенєв	Ася
	Мариніна	Чужа маска
	Іванов	Математика
	Мариніна	Стиліст
	Толстой	Анна Кареніна
	Донцов	Дама з кігтиками
	Донцов	Винаходи
	Тургенєв	Батьки і діти
	Толстой	Війна і мир
	Толстой	Три ведмеді
	Донцова	Басейн з крокодилами
	Донцова	Відкриття
	Іванов	Геометрія
	Тургенєв	Бежин луг

Завдання:

1. Відсортувати всі книги Толстого.
2. Відсортувати всі книги Толстого і Тургенєва.
3. Відсортувати всі книги з кількістю сторінок <100.
4. Відсортувати всі книги, автор яких не Тургенєв.
5. Відсортувати всі книги, прізвища авторів яких починаються з букв після К.
6. Відсортувати всі книги, прізвища авторів яких починаються з букви Т і роком видання >=2000
7. Відсортувати книги, представивши по одній книзі кожного автора.
8. З першої десятки книг вилучити тільки ті, в яких менше 200 сторінок, інші показувати всі.
9. Відобразити всі книги, у назві яких є слово „діти”; набрати в умові просто слово „діти” і пояснити, що вийде.
10. Відобразити всі книги, у назві яких між совами є прийменник або сполучник з однієї букви.

 

 

Тести

Перевірте себе по Розділу 5. (файл test.pps, кнопка Розділ 5.)