Завдання 1. Знайомство з можливостями узагальнення даних в середовищі електронних таблиць Microsoft Excel: сортування

МОДУЛЬ № 2

ЕЛЕКТРОННІ ТАБЛИЦІ MICROSOFT EXCEL 2010. ПРОГРАМУВАННЯ НА VBA У СЕРЕДОВИЩІ Excel.

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

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

Мета: набуття практичних навичок в обробці даних електронних таблиць.

План

1. Представлення електронної таблиці у вигляді списку.

2. Сортування даних.

3. Фільтрація даних у списку: фільтр (автофільтр), розширений фільтр.

4. Структурування таблиць: ручний спосіб, авто структурування, підсумки.

5. Консолідація даних.

6. Зведені таблиці.

Теоретичні відомості

Excel має розвинутий апарат швидкого та зручного узагальнення даних: сортування, фільтрація, формування підсумків, консолідація, групування даних. Ці інструменти відкриваються в меню Данные.

1. Представлення електронної таблиці у вигляді списку

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

Наприклад, журнал відомостей магазину безмитної торгівлі, що має вигляд: Назва товару, Призначення (чол./жін), Об’єм, мл, Вартість, од., Кількість, од., Загальна вартість – являє собою список даних (рис. 1). Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД). При цьому рядки таблиці – це записи бази даних, а стовпчики – поля БД.

Щоб перетворити таблицю Ехсel у список, необхідно надати стовпцям імена, які будуть використовуватися як імена полів бази даних. Імена стовпців можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис. 1.

Створюючи список на робочому листі Ехсеl необхідно дотримуватись певних правил:

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

- варто відокремлювати список від інших даних робочого листа хоча б одним незаповненим стовпцем або одним незаповненим рядком;

- список може займати весь робочий лист: 16384 рядки і 256 стовпців;

- імена стовпців повинні розташовуватися в першому рядку списку;

- для імен стовпців варто використовувати шрифт, тип даних, вирівнювання, формат, рамку або стиль букв, відмінні від тих, що використовувалися для даних списку;

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

Рис. 1. Приклад списку

Завдання 1.

1. Створіть таблицю 1 Магазин безмитної торгівлі та виконайте всі необхідні обчислення. У комірки D3:D12 встановіть грошовий формат з двома знаками після коми. В комірку F3 введіть формулу =D3*E3. Скопіюйте її на весь стовпець F (до комірки F12).

В комірку Е13 введіть формулу =СУММ(Е3:Е12) або скористайтесь командою Автосуммирование. Аналогічно в комірку F13 введіть формулу =СУММ(F3:F12).

Таблиця 1. Магазин безмитної торгівлі

2. Збережіть робочу книгу Книга1 у особистій папці Модуль2 \ Лаб4 диску S: з ім’ям Лаб4_прізвище.xls.

Для цього виберіть Файл – Сохранить, вкажіть шлях до вашої власної папки, у полі Имя файла введіть Лаб4_прізвище, у списку Тип файла виберіть Книга Ехсеl.

3. Перейменуйте лист 1, давши йому ім’я: Дані.

Для цього двічі клацніть по ярличку Лист1. Введіть нову назву листа: Дані. Натисніть кнопку Enter. Лист1 одержав нову назву.

2. Сортування даних

Список можна відсортувати за алфавітом, за значенням або в хронологічному порядку відповідно до даних конкретного поля.

Найпростіший спосіб сортування – за допомогою 2 кнопок на панелі інструментівдля швидкого сортування: Сортировка по возростанию, та Сортировка по убыванию – та . Ключем сортування в цьому випадку є стовпчик із активною клітинкою. Щоб швидко відсортувати записи потрібно:

- виділити колонку для сортування;

- натиснути одну з піктограм-кнопок.

Складне сортування здійснюється через діалогове вікно. Для цього потрібно:

- поставити курсор в межах таблиці;

- виконати команду: Данные – Сортировка;

- в пункті Сортировать по – задати поля сортування. Наприклад, у 1 полі – Назва товару, у другому полі Об’єм, мл, у 3-му – Вартість, од.

- у пункті Порядок - встановити напрямок сортування – за зростанням або за спаданням.