Основні теоретичні відомості. Лабораторна робота №15. Створення та опрацювання баз даних засобами MS Excel

Лабораторна робота №15. Створення та опрацювання баз даних засобами MS Excel.

Питання для самоперевірки.

Порядок виконання завдань

1. Завантажити програму Microsoft Excel.

2. Відкрити документ Список, який розташований на диску D: в папці Соціологія та переглянути інформацію на робочому листі.

3. Зберегти документ Список під ім’ям Консолидация на гнучкий диск (А:).

4. Скопіювати інформацію з листа Лист1 на листи Лист2, Лист3, Лист4, Лист5.

5. Лист Лист 1 перейменувати в лист Итоги, Лист2 – Січень, Лист3 – Лютий, Лист4 – Березень, Лист5 – Квітень.

6. На листі Итоги підрахувати середню заробітну платню в кожній групі співробітників з однаковим складом сімї.

7. На листі Итоги визначити величину максимальної заробітної платні в кожній групі співробітників з однаковою посадою.

8. Підрахувати загальну суму З/П співробітників за чотири місяці.

9. Створити зведену таблицю аналізу даних співробітників фірми.

10. Додати у робочу книгу новий лист і надати йому ім’я Задача.

11. Створити на листі Задача наступну таблицю:

Прізвище агента	Категорія	Марка авто	Модель авто	Кількість реалізованих авто	Ціна реалізаціі, $	Ціна доставки, $
Сидорченко В.Д.	S	Lexus	IS 300		30000,00	25000,00
Петренко П.П.	L	Lexus	RX 300		54000,00	48000,00
Іванов О.М.	N	Lexus	RX 300		55000,00	48000,00
Петренко П.П.	L	Infiniti	G 35		55000,00	50000,00
Сидорченко В.Д.	S	Lexus	RX 300		57000,00	48000,00
Ільчук В.В.	N	Infiniti	FX 45		75000,00	70000,00
Петренко П.П.	L	Infiniti	FX 45		77000,00	70000,00
Сидорченко В.Д.	S	Infiniti	FX 45		78000,00	70000,00
Іванов О.М.	N	Lexus	SC 430		81000,00	70000,00
Іванов О.М.	S	Hummer	H 2		170000,00	155000,00

12. На листі Задача підрахувати проміжні підсумки, визначивши за марками авто минімальний розмір ціни реалізації.

13. Підрахувати проміжні підсумки, визначивши кількість реалізованих авто кожної марки.

14. Зберегти зміни у документі Консолидация.

1. Яке призначення має засіб Промежуточные итоги?

2. Яка технологія створення проміжних підсумків?

3. Яке призначення має засіб Консолидация?

4. Які види консолідації даних ви знаєте? Чим вони відрізняються?

5. Чим відрізняються засоби Промежуточные итоги і Консолидация?

6. Для чого призначені зведені таблиці?

7. Яка технологія створення зведених таблиць? Чи можливо редагувати створені зведені таблиці?

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

Список - це інформація в клітинах таблиці, що містить деяку множину однорідних даних по рядкам і/або стовпцям.

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

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

При використанні форми даних для пошуку окремих записів таблиці можна за один раз переглянути тільки один запис. Для пошуку записів варто виділити першу клітинку у списку, вибрати команду Данные Þ Форма, а потім у вікні форми клацнути на кнопці Критерии. Виконання цієї команди приведе до появи діалогового вікна форми даних без запису. Далі в вибраних полях слід ввести умови пошуку і натиснути кнопку Далее (або ¯). Excel виведе перший запис, що задовольняє умові пошуку, потім слід знову натиснути кнопку Далее. В умовах пошуку можна використовувати символи підстановки «*» та «?». Символ «*» використовується для вказівки будь якої кількості символів. Символ «?» використовується для вказівки одного символа. В умовах для числових полів можна використовувати оператори порівняння (=, <, >, <>, <=, >=).

При роботі із інформацією списка Excel надає можливість виконати сортування по одному, двом або трьом полям одночасно. При цьому в кожному полі може бути назначено свій порядок сортування. Щоб відсортувати список, спочатку потрібно зробити активною якусь клітину в області списку. Потім потрібно вибрати в меню Данные команду Сортировка. Якщо потрібно відсортувати тільки частину списку (підмножину блока клітин), необхідно виділити тільки ті рядки і стовпчики, що потрібно відсортувати. Потім у меню Данные потрібно вибрати команду Сортировка.

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

· задати умову із логічним оператором ИЛИ для декількох стовпчиків;

· задати три або більше умов для конкретного стовпчика з використанням, одного логічного оператора ИЛИ;

· задати умови, що розраховуються, наприклад, показати на екрані тільки тих співробітніків, у яких заробітна плата принаймні на 25 відсотків більше ніж середня;

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

Перед використанням команди Автофильтр необхідно виділити будь-яку клітину списку. Потім потрібно вибрати цю команду в підменю Фильтр із меню Данные. Excel виведе кнопки зі стрілками поруч із кожним заголовком стовпчика. За допомогою особливого автофільтру можна задавати умови добору з використанням будь-яких операторів порівняння. Для цього потрібно розкрити список автофільтру потрібного стовпчика і вибрати в ньому Условие.

При роботі з розширеним фільтром слід мати на увазі, що:

· При накладенні двох або більше умов ИЛИ для одного стовбчика слід розташувати умови вибору нижче заголовку стовбця в різних рядках.

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

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

· При накладенні умов на текст слід дотримуватися таких правил:

- одна буква означає " знайти всі значення, що починаються з цієї букви ";

- символ >(більше) або <(меньше) означає " знайти всі значення, що знаходяться в алфавіті після чи до введеного текстового значення ";

- формула ="=текст" означає" знайти всі значення, що точно співпадають з рядком симвлів " текст " ".

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

Наведемо правила, які допоможуть вірно застосовувати ці умови:

· заголовок, над умовою що обчислюється, повинен відрізнятися від заголовка будь-якого зі стовпчиків списку. Заголовок умови може бути порожнім або містити довільний текст. Навіть якщо заголовок в умові є порожнім, необхідно включити його при завданні діапазону умов у вікні діалогу Расширенный фильтр;

· посилання на клітини, що знаходяться поза списком, повинні бути абсолютними;

· посилання на клітини в списку повинні бути відносними.

Дата добавления: 2014-10-31