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

Тема роботи: Підсумки в списках та функції для роботи з БД.

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

2.1 Вимоги до списків

Список або база даних (БД) – це один із засобів організації да­них на робочому листі. Рядки списку називають записами, а стовпці – полями. Список – це діапазон, що задовольняє таким умовам:

• дані в кожному стовпчику мусять бути однотипними;

• в першому рядку списку треба записати заголовки стовпців. Можна розмістити заголовки в декілька рядків в одній клітинці, якщо на вкладці Выравнивание вікна Формат ячеек встано­вити прапорець "Переносить по словам";

• між рядком заголовків та даними не повинно бути вільних ряд­ків. Можна використовувати для виділення формат клітинок.

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

• список рекомендовано відокремлювати від інших даних на Листі вільними рядками та стовпчиками;

• якщо виділена клітина належить списку, то це допомагає Excel правильно визначити увесь список автоматично.

На відміну від попередніх версій Excel 2003 дозволяє створюва­ти на одному Листі декілька списків і не вимагає виконання всіх пере­лічених вимог. В меню Данные існує пункт Список. Можна створити список для вказаного діапазону даних (див. рис. 2.1). Якщо в діапазоні немає заголовків, Excel 2003 може добавити стандартні заголовки Столбец1, Столбец2… Після виконання команди Данные – Список – Создать список Microsoft Excel змінює інтерфейс користувача: праворуч від кожного поля розміщується перелік варіантів сортування (по возрастанию, по убыванию) та опції Автофільтру.

Рисунок 2.1 – Вікно для створення списку

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

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

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

Рисунок 2.2 – Вікно для сортування списку

На панелі інструментів Стандартная знаходяться дві кнопки для швидкого сортування: “Сортировка по возрастанию”, та “Сорти­ровка по убыванию”. Вони дозволяють здійснити сортування списку по одному стовпчику з активною клітиною. Список в цьому випадку Excel визначає самостійно.

Рисунок 2.3 – Вікно запиту для сортування

Можна виконувати сортування будь-якого діапазону, але якщо діапазон розташований в одному стовпчику списку, то виводиться вікно запиту (див. рис. 2.3). Треба вибрати один з перемикачів для продовження сортування.

2.3 Формування підсумків в списках

Розглянемо знаходження підсумків на прикладі.

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

Перед підведенням будь-яких підсумків треба спочатку відсор­тувати список по потрібному полю, тобто по полю Бригада.

Надамо новому Листу ім’я Підсумки. Перенесемо туди значення з Листу Січень та відсортуємо їх. Для цього виконаємо такі дії:

• виділімо діапазон A1 : H18 на Листі Січень;

• виберемо в меню пункт Копировать;

• перейдемо на Лист Підсумки і зробимо активною клітинку А1;

• в меню Правка виберемо пункт Специальная вставка, встано­вимо перемикач форматы і натиснемо кнопку ОК;

• знов увійдемо в меню Правка – Специальная вставка, встано­вимо перемикач значения;

• зробимо активною яку-небудь клітинку списку;

• в меню Данные виберемо команду Сортировка та виберемо сортування за полями Бригада і П.І.Б. (див. рис. 2.2).

Тепер знайдемо підсумки. Зробимо активною клітинку списку. В меню Данные команда Итоги відкриває вікно Промежуточные итоги (див. рис. 2.4).

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

Знову виберемо пункти Данные – Итоги. У вікні Промежуточ­ные итоги в першому списку залишимо без змін елемент Бригада, а в списку Операция виберемо функцію Среднее. В третьому списку зні­мемо прапорець з поля Одержати. і встановимо його поруч з полем Відпрацьовано днів.

Щоб отримати обидва підсумки, знімемо прапорець "Заменить текущие итоги". В результаті знаходження підсумків утворюються рівні структури. Вони дозволяють приховувати або відображувати дані різних рівнів (див. рис. 2.5 – 2.6).

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

Рисунок 2.4 – Вікно для завдання підсумків

Рисунок 2.5 – Вікно підсумків

Рисунок 2.6 – Вікно підсумків з даними.

Після виконання команди Данные – Список – Создать список в Excel 2003 утворюється додатковий рядок підсумків під списком. Це дозволяє швидко знаходити загальні підсумки по кожному стовпцю. Кожна клітинка в рядку підсумків містить перелік доступних функцій. Використання інших функцій в цих клітинках неможливо.