Posobie_1_chast2_Excel

Практичне заняття № 5

Тема дисципліни: Microsoft Office. Табличний процесор EXCEL (рівень спеціаліста).

Тема заняття: Табличний процесор EXCEL

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

Хід виконання роботи

1.Увімкніть комп’ютер, увійдіть в систему з ім’ям USER.

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

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

Створення списку

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

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

3.Створіть нову книгу з ім’ям ПР5_Прізвище.xls.

4.На Листі1 у комірках A1:F1 створіть заголовки стовпчиків:

№	Дата	Валюта	Курс купівлі	Курс продажу

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

6.Заповните список наступними даними:

50

f№ –1;

fДата – 01.01.2005;

fВалюта – USD;

fКурс купівлі – 5,35;

fКурс продажу – 5,38

Заповнення списку за допомогою форми даних

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

Щоб скористатися формою даних для заповнення списку, треба:

•натиснути будь-яку комірку списку і вибрати команду Данные/Форма– з'явиться вікно форми даних, у поля якої внесені значення комірок першого рядка списку;

•натиснути кнопку Добавить. У результаті у верхньому правому куті вікна з'явиться напис Новая запись;

•ввести в поля, назви яких відповідають заголовкам стовпчиків, необхідні значення;

•натиснути кнопку Добавить;

•натиснути кнопку Закрыть, коли всі записи внесені.

7.Використовуючи форму даних, додайте в список:

f№ - 2;

fДата – 02.01.2005;

fВалюта – EUR;

fКурс купівлі – 7;

fКурс продажу - 7,15.

f№ - 3

fДата - 03.01.2005;

fВалюта – RUR;

fКурс купівлі –0,18;

fКурс продажу – 0,2.

8.Закрийте форму даних.

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

51

Автоматизація введення даних

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

10. Додайте новий запис до списку:

f Наберіть у комірці C5 букву U. Прийміть запропоноване значення комірки USD.

f У комірці C6 уведіть букву E. Відмовтеся від запропонованого автовводом закінчення і введіть EUR.

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

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

11. Використовуючи команду Выбрать из списка, створіть новий запис:

fвведіть у поле Валюта – UAH;

fу поле Курс – 1.

52

Пошук записів за допомогою форми даних

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

•натиснути будь-яку комірку списку; відкрити форму даних за допомогою команди Данные/Форма;

•натиснути кнопку Критерии; ввести шукані значення у відповідні поля (можна заповнювати не всі поля);

•натискати кнопку Далее, поки не пролунає звуковий сигнал, що означає, що виведено всі записи, що задовольняють обраним критеріям;

•натиснути кнопку Закрить, щоб закінчити роботу з формою даних.

12.Знайдіть за допомогою форми дані зведення про першу позицію в списку.

13.Заповните порожні поля запису, що залишилися, наступними значеннями: f Валюта – FRH;

f Курс купівлі – 3,5; f Курс продажу – 3,6;

14.Клацніть кнопку Добавить.

15.Клацніть кнопку Критерии, а потім за допомогою кнопки Очистить видаліть введені раніше критерії пошуку.

16.Видаліть запис, що містить дані про курс UAH. (Підказка: знайдіть потрібний запис за зазначеними критеріями і клацніть кнопку Удалить

17.Закрийте форму даних.

18.Закрийте створену книгу, зберігши її під ім'ям ПР5_Bank_Прізвище.xls.

53

19. Самостійно виконайте завдання:

Відкрийте книгу Equipment.xls.

Перейдіть на лист Список.

Використовуючи форму даних, додайте в список новий запис з наступними значеннями:

f № – 1141;

f Місяць – декабрь;

f Найменування цінного папера – Облигация; f Емітент – Казначейство;

f Термін звертання – 12; f Ціна за ед. (грн.) – 355; f Закупівля (шт.) – 3; f Повернення (шт.) – 0;

f Одержувач – Підприємство2.

Призначте пошук за критеріями Місяць – апрель, Найменування цінного папера – Акция.

Відредагуйте знайдений запис – у поле Одержувач уведіть Підприємство3.

Збережіть зроблені зміни у файлі Equipment_Прізвище.xls.

54

Сортування даних списку

Щоб було зручніше працювати з даними списку, необхідно ці дані упорядкувати (відсортувати). Під сортуванням мається на увазі розташування даних списку у визначеному порядку, називаному порядком сортування. Список звичайно сортується за значеннями комірок одного або декількох стовпчиків. Ці значення упорядковуються по зростанню або убуванню за абеткою, по датах або по величині чисел.

Щоб виконати сортування по зростанню або по убуванню, треба:

•натиснути будь-яку комірку того стовпця, значення комірок якого необхідно упорядкувати;

•натиснути кнопку Сортировка по возрастанию або Сортировка по убыванию на панелі інструментів Стандартная.

20. У книзі Equipment_Прізвище.xls на листі Список відсортуйте дані:

fу стовпці Валовий обсяг закупівлі – по зростанню;

fу стовпці Одержувач – по убуванню.

Переконаєтеся, що список відсортований правильно.

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

55

Сортування по декількох стовпцях

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

21. Відсортуйте список:

fспочатку по параметрі Найменування цінного папера, по зростанню,

fпотім – Емітент, по зростанню,

fв останню чергу – Ціна за одиницю, по зростанню.

Зверніть увагу, як тепер упорядковані значення.

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

Для цього треба:

•вибрати команду Данные/Сортировка і в діалоговому вікні Сортировка диапазона натиснути кнопку Параметры,

•у діалоговому вікні Параметры сортировки вибрати зі списку Порядок сортировки по первому ключу потрібний варіант;

•натиснути OK у вікні Параметры сортировки;

•натиснути OK у вікні Сортировка диапазона.

22. Задайте користувальницький список сортування:

56

f по першому полю – Місяць, по зростанню в хронологічному порядку (січень, лютий, березень і т.д.);

f по другому і третьому полю – не сортувати.

23.Самостійно виконайте завдання: у книзі Equipment_Прізвище.xls відкрийте аркуш Виробництво. У пропонованому списку зібрані дані про виробництво гвинтів трьома цехами машинобудівного заводу за період з

1997 по 1998 рік.

24.Додайте за допомогою форми даних наступні дані:

fРік –1998,

fЦех – №3,

fКвартал – 4,

fДеталь – Гвинт М12,

fЯкісні – 458000,

fБрак – 548.

Зверніть увагу, поле Загальна кількість буде недоступно для введення даних.

25. Закрийте форму даних.

Формули не можуть бути додані в список за допомогою Форми даних.

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

57

27.Відсортуйте список по зростанню спочатку по параметрі Рік, потім по параметрі Цех, і в останню чергу по параметрі Деталь.

28.Збережіть зроблені зміни у книзі Equipment_Прізвище.xls.

Автофильтр

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

•натиснути будь-яку комірку списку;

•дати команду Даные/Фильтр/Автофильтр – біля заголовків стовпчиків списку з'являться кнопки зі стрілками;

•натиснути кнопку зі стрілкою в заголовку цікавлячого стовпця і вибрати

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

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

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

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

58

Тепер Ви бачите на екрані дані, що задовольняють визначеним умовам. Номера виведених на екран рядків позначені кольором, відмінним від основного. Рядки, що задовольняють обраним критеріям, «заховані». У тих стовпцях, де була задана яка-небудь умова фільтрації, стрілки на кнопках змінили колір.

Щоб відновити вихідний вид списку, треба вибрати

Данны/Фильтр/Отобразить все.

31. Відновіть вихідний вид списку.

Створення користувальницького автофільтра

При роботі з автофільтром Ви можете користуватися не тільки критеріями, що автоматично пропонує Microsoft Excel, але і задавати власні умови відбору значень. Так, критерієм добору може служити послідовність символів (наприклад, «Акція») або умовне вираження (наприклад, «>120»).

59