6.9. Інформаційні технології в Excel

6.9.1. Списки і база даних Microsoft Excel

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

• список цілком поміщається на одному робочому листі, максимальний розмір списку 65535 рядків і 256 стовпців;

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

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

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

• комірки одного стовпця списку містять однорідну інформацію.

Традиційні операції зі списками:

• введення і редагування даних списку;

• сортування - впорядкування записів (рядків/стовпців таблиці) списку;

• фільтрація (відбір) записів списку за умовами;

• агрегація інформації списку, обчислення статистичних оцінок (звідні таблиці, проміжні підсумки);

• структуризація даних (формування нових угрупувань рядків і стовпців).

• консолідація (об'єднання даних декількох списків з формуванням загальних підсумків).

Створення бази даних за допомогою форми. База даних може створюватися і редагуватися за допомогою екранної форми, яка містить імена і зна­чення полів записів списку. Для виклику форми курсор встановлюється в об­ласть списку, виконується команда Данные ► Форма. Список може містити обчислювані поля. Обчислювані поля, що містять формули, недоступні для редагування через форму. Весь список (база даних), включаючи імена полів і записи, може розглядатися як єдиний блок комірок, для якого створюється загальне ім'я. Якщо блок списку має стандартне ім'я БазаДаних, при введенні нових записів через форму введення блок автоматично розширюється, в обчислювані поля нових записів копіюються формули. Основним недоліком засто­сування екранних форм для роботи з базою даних є відсутність контролю введення даних (низький рівень достовірності даних, що вводяться). Діалогове вікно форми має ім'я, відповідне назві листа списку, за допомогою текстових кнопок форма забезпечує роботу з кожним окремим записом списку (рис.16.21).

Рисунок 6.21. Форма введення даних в список

Текстові кнопки форми:

• Добавить - введення нового запису в список.

• Назад, Далее - послідовний перехід до записів списку.

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

• Удалить- видалення запису.

• Закрыть - вихід з форми.

Форма дозволяє не тільки додавати записи, але і відбирати по критеріям.

Як приклад введемо на Лист 1 таблицю Тариф (рис. 6.22) а на Лист 2 базу даних Картотека - (рис. 6.23). У картотеку введемо на початку один запис, а потім за допомогою форми введемо решту записів і виконаємо відбір по умові за допомогою кнопки Критерії.

Рисунок 6.22. Таблиця Тарифи

Рисунок 6.23. База даних Картотека

Кожному стовпцю таблиці Тарифи привласнимо імена, відповідні назві стовпців. Для цього потрібно: виділити всі комірки таблиці включаючи всі заголовки стовпців таблиці, виконати команду меню Вставка ►Имя ► Создать ►По тексту в строке выше.

У комірку С2 введемо формулу для розрахунку ставки =В2*$D$2 і протягнемо її до комірки С7 і одержимо таблицю рис. 6.24.

Рисунок 6.24. Таблиця Тарифи з заповненими комірками

У таблицю Картотека в комірку Е2 введемо формулу =ПРОСМОТР (D2;Розряд;Ставка) і методом протягування скопіюємо її до комірки Е7 рис. 6.25.

Рисунок 6.25. База даних Картотека з заповненими комірками

За допомогою форми додамо записи в таблицю Картотека рис.6.26.

Рисунок 6.26. Доповнена таблиця Картотека

Кнопка Критерії дає можливість вибрати записи по умові. Наприклад, після натиснення кнопки Критерії необхідно ввести в поле Професія - Вантаж­ник а в полі Розряд працюючого - 3. Для переглядання записів що задовольняють умові необхідно натиснути кнопку Далее.

Сортування записів бази даних (списку). Сортування виконується командою меню Данные ►Сортировка. Після виконання команди відкривається вікно Сортировка диапазона, в якому необхідно вказати параметри сортування (за збільшенням, зменшенням і так далі).

Фільтрація списків. Розрізняють два способи фільтрації (відбору) записів списків в команді меню Данные ►Фильтр: Автофильтр і Расширенный. Автофільтр створює для кожного поля список значень (у комірці з ім'ям поля) який використовується для відбору записів.

Список значень для відбору:

• Все - немає обмежень на значення поля.

• Первые 10 - виведення перших 10 записів.

• Условие - формування умови відбору.

• Конкретное значение - відбір записів з вказаним значенням поля.

• Пустые - в полі порожньо (немає значення).

• Непустые - в полі міститься якесь значення.

Для відміни всіх умов фільтрації служить команда меню Данные ► Фильтр► Показать все, для відмови від автофільтру повторно виконується команда меню Данные ► Фильтр ►Автофильтр.

Умови можна задавати як по декільком полям так і по одному. Якщо умови задаються по декільком полям то вони пов’язані логічною функцією И. Це означає що спочатку буде виконана перша умова і будуть відфільтровані записи всього списку а потім будуть відфільтровані записи згідно другої умови. Умова для другого поля фільтрує результат попередньої фільтрації і так далі.

Якщо умови задається тільки по одному полю то вони можуть бути зв'язані як функцією И, так і ИЛИ.

Розширений фільтр. Для розширеного фільтру необхідно створити критерії. Критеріями є умови. Діапазон умов складається з імен полів списку і рядків умов. В області діапазону умов для розширеного фільтру списку можна сформулювати декілька рядків умов, зв'язаних між собою логічною функцією ИЛИ. Умови фільтрації усередині одного рядка зв'язані зв'язкою И (мал. 6.27).

Рисунок 6.27. Область критеріїв

Команда меню Данные►Фильтр►Расширенный фильтр виводить діалогове вікно Расширенный фильтр (рис.6.28.), для вказівки діапазону комірок: початкового діапазону (Исходный диапазон) - списку бази даних (доцільно привласнити ім'я бази даних БазаДаних), діапазону умов (Диапазон условий), діапазону комірок для вибору результату фільтрації (Поместить результат в диапазон).

Початковий діапазон - список бази даних, діапазон умов і копія відфільтрованих записів можуть знаходитися на одному і тому ж листі, на різних листах однієї книги і навіть в різних книгах.

Рисунок 6.28. Діалогове вікно Расширенный фильтр

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

У діалоговому вікні Расширенный фильтр можна вказати:

• скопіювати результат в інше місце;

• вихідний діапазон БазаДаних;

• діапазон умов Критерії!$A$1:$D$3;

• помістити результат у діапазон Результат!$A$1;

• клацнути по кнопці ОК.