Теоретичні відомості. В Excel списком називають базу даних, що розміщена на робочому аркуші

Робота зі списками

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

Як приклад розглянемо невеликий список книг відомих авторів.

Список, наведений на рисунку, складається з п'яти полів (№, Автор, Найменування, Кількість та Дата появи в магазині) і двадцяти двох записів (не враховуючи рядка заголовку).

За необхідності список можна розширити, додавши нові поля або записи. Проте треба пам’ятати, що список може містити не більше ніж 256 полів та 65 535 записів, тому що розмір робочого аркуша є обмеженим.

Поля списку повинні містити однотипну інформацію (текст, дати або числові значення). В списках не варто залишати порожніми рядки чи стовпці, а також поміщати додаткову інформацію в комірки, розміщені ліворуч чи праворуч від списку. Також слід уникати додаткових пробілів на початку та наприкінці комірок з даними

В Excel зі списками можна виконувати будь-які з перерахованих далі операцій:

• заповнювати список даними;

• фільтрувати списки відповідно до визначених критеріїв;

• сортувати дані в списку;

• обчислювати проміжні підсумки на основі необхідних

• функцій;

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

Введення інформації в список

В Excel існує декілька способів введення даних у список:

• введення інформації вручну;

• імпортування чи копіювання їх з іншого документа;

• використання для заповнення списку форму вводу даних.

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

Створення і використання форми вводу

Використання форми вводу даних дозволяє спростити і прискорити створення списку.

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

Щоб створити форму вводу для нового списку, треба виконати такі дії:

1) Ввести у відповідні комірки робочого аркуша рядок заголовку, а потім — перший рядок даних, тобто перший запис списку.

2) Відформатувати заповнені комірки так, як повинні бути відформатовані поля списку.

3) Встановити табличний курсор у будь-якій із заповнених комірок та вибрати команду Данные/Форма. На основі попередньо введеної інформації Excel створить форму для вводу даних у список і відобразить її на екрані. У цій формі будуть показані дані з першого запису.

4) Щоб додати новий запис, треба натиснути кнопку Добавить. Тепер у порожні поля форми можна буде ввести нові дані. Після введення нового запису потрібно натиснути клавішу Enter або кнопку Добавить, щоб ввести наступний запис.

5) Після завершення додавання записів, треба клацнути на кнопці Закрыть, щоб закрити форму вводу.

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

Щоб відобразити на екрані форму для готового списку, потрібно встановити табличний курсор у будь-якому місці списку та виберіть команду Данные/Форма. Для переміщення по записах списку використовується смуга прокрутки форми, кнопки Найти назад і Найти далее, а також клавіші ←, →, ↓, ↑, Enter, Shift+Enter, PgUp і PgDn. Тоді ж у правому верхньому куті форми вводу буде показано номер поточного запису та загальну кількість записів у списку.

Для видалення непотрібного запису потрібно вибрати його у формі вводу і клацнути на кнопці Удалить.

Щоб знайти записи, що відповідають певним умовам, треба клацнути на кнопці Условие та ввести у відповідні поля потрібні умови, а для перегляду знайдених записів натискати кнопки Найти далее і Найти назад. Наприклад, якщо ввести в поле Автор символи В*, то, послідовно клацаючи на кнопці Найти далее, можна переглянути у формі лише ті записи, в яких ім'я автора починається з букви В. А щоб дізнатися, кількість яких книг у магазині перевищує 10, ввести у поле Кількість вираз > 10.

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

Фільтрація списку

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

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

Найпростішим методом фільтрації списку є автоматична фільтрація. Її виконують наступним чином.

1) Встановити табличний курсор в одній з комірок списку.

2) Вибрати команду Данные/Фильтр/Автофильтр, після чого праворуч від заголовків стовпців у списку з'являться кнопки зі стрілками кнопки автофільтру.

3) Клацнути на кнопці автофільтру того поля, за значеннями якого потрібно відфільтрувати список. Отримаєте список, який міститиме всі значення цього поля, а також декілька інших елементів (таких як Сортировка по возрастанию, Сортировка по убыванию, Все, Первые 10 і Условие).

4) Вибрати зі списку те значення поля, за яким слід виконати фільтрацію. На екрані залишаться тільки ті записи, що задовольняють даній умові. На малюнку показаний результат сортування за значенням Березень.

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

Щоб скасувати фільтрацію та знову побачити список повністю, потрібно клацнути на кнопці автофільтру і вибрати у розкривному списку елемент Все або в меню Данные вибрати команду Фильтр / Отобразить все. А щоб видалити кнопки автофільтру, слід виконати ще раз команду Данные/Фильтр/Автофильтр.

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

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

• інтервал значень;

• значення, більші або менші, ніж задане значення;

• два різних значення;

• шаблон, відповідно до якого треба знайти записи.

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

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

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

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

Команди сортування доступні також у розкривному списку автофільтра, описаному вище.

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

В трьох розкривних списках даного вікна вибираються заголовки полів, які потрібно відсортувати, та встановлюються відповідні їм перемикачі за зростанням або за спаданням. Після того як клацнути на кнопці ОК, список буде відсортований заданим чином.

Якщо результат сортування записів не влаштовує, можна виконати команду Правка/Отменить сортировку, після чого список отримає початковий вигляд.

Проміжні підсумки

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

Проміжні підсумки обчислюють для стовпців, що містять числа, за допомогою різних підсумкових функцій (таких як SUM або AVERAGE). Для кожної групи рядків можна визначити суму, добуток або кількість значень в деякому стовпці; середнє, максимальне чи мінімальне значення в стовпці; зсунене чи незсунене відхилення, а також зсунену чи незсунену дисперсію.

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

1) Встановимо табличний курсор у будь-якому місці списку.

2) Виберемо команду Данные/Итоги.

3) В отриманому діалоговому вікні Промежуточные итоги встановимо параметри, вказані на малюнку.

Зауваження. Поле, яке вибирається в першому розкривному списку, обов'язково повинно бути відсортованим.

4) Клацнемо на кнопці ОК.

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

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