Практична робота 4 на тему Створення та використання списків в середовищі MS Excel

Практична робота №4.1 Тема: Створення та використання списків в середовищі MS Excel. Пошук та впорядкування даних

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

Теоретична довідка

РОБОТА ІЗ СПИСКАМИ ДАНИХ

Списком називають набір рядків таблиці, що містить пов'я­зані між собою дані.

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

Під час створення списку – бази даних у середовищі Microsoft Excel слід дотримувати певних правил:

– на одному робочому аркуші не можна розміщувати більше одного списку;

– список не може містити порожніхрядків (стовпчиків); 

– заголовки полів мають бути унікальними, тобто не може бути повторів назв заголовків полів;

– бажано, щоб формати заголовків полів відрізнялися від форматів записів;

– не може бути порожнього рядка або навіть порожньої комірки між заголовками полів і записами;

– в усіх рядках списку в однакових стовпчиках мають бути однотипні дані.

Наприклад, наведена таблиця може бути списком – базою даних:

Назви полів знаходяться у другому рядку робочого аркуша, а самі записи бази містяться в рядках з 3 по 12.

СОРТУВАННЯ ДАНИХ

Для сортування даних таблиці слід виділити таблицю або клацнути в неї курсором. Далі командисортування в порядку зростання або спадання можна знайти:

– на вкладці Дан­ные в групі Сортировка и фильтр, або

– на вкладці Главная в групі Редактирование.

ПОШУК  ДАНИХ

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

СТВОРЕННЯ ЗАПИТІВ ДЛЯ ПОШУКУ ДАНИХ ЗА ДОПОМОГОЮ ФІЛЬТРІВ

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

За допомогою фільтрів можна вибрати записи, що містять потрібні для пошуку дані. Фільтри бувають двох типів: Автофільтр і Розширений фільтр.

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

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

Використання Автофільтра

Виділяється список, вибирається вкладкаДан­ные,далікомандаФильтр. Тоді в усіх назвах полів списку розташується кнопка зі стріл­кою: .

При натисненні на цю кнопку у вибраному полі списку відкривається меню:

– пункт «(Выделить все)» виводить усі значення з позначеного поля;

– пункт «Числовые фильтры» пропонує деякі прості критерії для відбору числових даних;

– пункт «Текстовые фильтры» пропонує деякі прості критерії для відбору текстових даних;

– в пунктах «Числовые фильтры», «Текстовые фильтры» є також команда «Настраиваемый фильтр», яка дозволяє створювати складений критерій по вказаному полю, де можна зазначити одну умову – тоді заповнюється тільки перший рядок, або дві умови – заповнюється і перший, і другий рядок.

Кнопка "И" використовується, коли потрібно одночасне виконання обох введених умов, а кнопка "ИЛИ" – у випадку, коли має виконуватися одна з двох умов. При виборі одного з наведених значень список буде відфільтрований за простим кри­терієм – вказаним значенням.

При занесенні умов можна використовувати знаки ? і *. Знак ? замінює під час пошуку один будь-який текстовий сим­вол, а знак * – будь-яку кількість будь-яких текстових симво­лів.

Після встановлення фільтра стрілка на кнопці набуває вигляду фільтра – це ознака того, що дані у списку відфільтровані.

Для відновлення показу всіх записів списку можна поступо­во із меню кнопки зі стрілкою у полях, де визначався критерій, вибирати пункт "Все", або одразу вибрати на вкладціДан­ные повторнокомандуФильтр.

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

Приклад: У таблиці нижче треба відшукати дані про Фірми, в яких кількість груп більше ніж 20 або менше ніж 10.

1. Спочатку треба встановити курсор у таблицю і виконати на вкладціДан­ные командуФильтр.

2. Натиснути кнопку  у полі"Кількість груп" і вибрати пункт «Числовые фильтры», «Настраиваемый фильтр». Встановити наступні умови:

3. Після натиснення кнопки [Ok] будуть показані результати запиту:

Завдання 1. Виконати сортування даних за алфавітним покажчиком в полі «Назва фірми» в таблиці моніторингу діяльності фірм.

Завдання 2. Виконати автофільтрацію даних у відсортованій таблиці за вказаними критеріями.

Технологія виконання  роботи

1. Завантажити табличний процесор Excel.

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

3. Виконати сортування даних за назвою фірми.

4. Скопіювати таблицю на Лист2, Лист3, Лист4, Лист5.

5. На робочому аркуші Лист2 за допомогою автофільтру потрібно відібрати дані з інвестиційними витратами більше 100 тис. грн. і терміном окупності більше півроку.

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

7. На робочому аркуші Лист4 за допомогою автофільтру потрібно відібрати дані для фірми "Лідер", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

8. На робочому аркуші Лист5 за допомогою автофільтру потрібно відібрати дані для фірм "Лідер", "Термопласт", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

9. Зберегти електронну таблицю під назвою Прізвище_Практична4_1 на сервері у власній папці.

10. Відкрити файл Тури.xls і зберегти на сервері у власній папці. За допомогою команд  знайти в робочому аркуші слово «грн.» і замінити на слово «». Стовбець ЗАКАЗ ТУРА видалити. Закріпити область – заголовок таблиці (перший рядок таблиці).

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

12. Оформити звіт і здати роботу викладачу.

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

1) Що таке база даних?

2) Що таке поле бази даних? Що таке запис бази даних?

3) Чи можуть заголовки полів повторюватись?

4) Чи можуть в одному полі бази даних міститися дані різного типу?

5) Як виконати сортування записів у базі даних? На яких вкладках можна знайти команди сортування?

6) Що таке фільтрація даних?

7) Що таке складний критерій пошуку?

8) На яких вкладках можна знайти команди фільтрації?

9) Які різновиди фільтрів для виконання фільтрації даних існують у базі даних MS Excel?

10) Як створити автофільтр?

З повагою ІЦ “KURSOVIKS”!