Створення і редагування списку

Тема 6. Бази даних Excel

1. Створення і редагування списку

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

3. Фільтрація даних в списку

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

5. Використання зведених таблиць

 

Створення і редагування списку

База даних – це систематизоване сховище інформації (наприклад, телефонний довідник).

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

Назви стовпців називаються іменами полів.

Самі стовпці – полями.

Рядки даних називаються записами.

У Excel базу даних частіше називають списком.

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

Для створення списку потрібно ввести назву кожного стовпця списку. Створити список можна в будь-якій частині робочого листа, яка не містить яких-небудь даних. Кожен список повинен бути відокремлений від інших порожнім рядком або стовпцем. При створенні списків доцільно дотримуватися наступних правил:

٧ На робочому листі краще розміщувати не більше одного списку;

٧ Кожен стовпець повинен мати заголовок, який повинен займати лише один рядок робочого листа;

٧ Між заголовками робочого листа і заголовком стовпців списку доцільно залишити хоч би один порожній рядок;

٧ Не допускати появи порожніх рядків або стовпців;

٧ Результати розрахунків по стовпцях, розміщених під списком, доцільно відокремити від списку порожнім рядком;

٧ Кожен список повинен бути відокремлений від інших даних порожнім рядком або стовпцем.

 

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

 

Для введення і редагування записів Excel має Форми даних.

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

Для створення форми потрібно активізувати одну з комірок, а потім виконати команду Дані>Форма. Excel автоматично генерує форму. Якщо в список дані ще не введені, тоді автоматично буде створений новий запис. В процесі введення нового запису Excel завжди додає порожній рядок внизу списку. Якщо в рядку під списком приведені певні підсумки або будь-які інші дані, то при спробі ввести новий запис Excel виводить повідомлення: «Список або базу даних розширити неможливо». Після цього потрібно закрити форму і вставити додаткові порожні рядки, щоб мати можливість вводити нові записи за допомогою форми.

 

Після введення команди Дані>Форма слідклацнути на кнопці Додати.

Ввести в кожне текстове поле форми дані, скориставшись клавішею [ Tab ] для переміщення від одного поля до іншого. [ Shift-Tab ] використовується для переміщення до попереднього текстового поля.

Після введення даних в останнє текстове поле, слід натиснути клавішу [ Enter ]. При цьому в списку з'явиться введений запис і новий бланк форми для введення нового запису.

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

За допомогою кнопок Далі і Назад можна проглянути всі записи списку. Для проглядання записів в списку можна використовувати смужку прокрутки і клавіші управління курсором:

§ проглянути відповідно попередній/наступний запис;

§ [Ctrl-PgDn] – перейти до нового запису форми;

§ [PgUp] – проглянути перший запис.

 

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

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

Для видалення запису необхідно:

٧ Встановити курсор в будь-яку комірку списку;

٧ Виконати команду Дані>Форма;

٧ За допомогою кнопки Назад/Далі знайти потрібний запис;

٧ Клацнути на кнопці Видалити;

٧ Клацнути на кнопці ОК або натиснути [ Enter ] для підтвердження видалення запису;

٧ Клацнути на кнопці Закрити для закриття вікна діалогу і повернення в робочу таблицю.

Для пошуку окремих записів таблиці також можна використовувати форму:

٧ Виділити комірку в списку;

٧ Виконати команду Дані>Форма;

٧ Клацнути по кнопці Критерії;

٧ Ввести умови тексту;

٧ Для проглядання записів по заданому критерію натиснути кнопку Далі або Назад;

٧ Клацнути по кнопці Закрити для повернення в робочу таблицю.

 

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

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

Для сортування списку потрібно:

٧ встановити покажчик на будь-яку комірку в списку (або виділити записи, які підлягають сортуванню);

٧ виконати команду Дані > Сортування;

٧ у списках полів, що розгортаються, слід послідовно вибрати імена полів списку, по яких буде проводиться сортування, потім вибрати опцію За збільшенням або За зменшенням відповідно до мети сортування. Щоб запобігти міткам стовпців від сортування разом із списком слід в розділі Ідентифікуватиполя вибрати опцію Попідписам;

٧ клацнути по кнопці ОК або натиснути [Enter];

٧ якщо при сортуванні в алфавітному порядку потрібно розрізняти великі і малі літери, то натисніть кнопку Параметри для відкриття вікна діалогу Параметри сортування, і в ньому встановити опцію Враховувати регістр. За умовчанням виділений список сортується по рядками. Для сортування списку по стовпцях потрібне у вікні Параметри сортування вибрати опцію Стовпці діапазону;

٧ для завдання власного порядку сортування у вікні діалогу Параметри сортування в полі Порядок сортування по першому ключу потрібно вибрати бажаний порядок сортування. Його можна задати за допомогою команди Сервіс > Параметри > Списки > у полі Елементи списку ввести елемент списку в потрібному порядку ввести кожний елемент списку, завершувати натисненням Enter), а потім натиснути кнопку Додати.

 

Фільтрація даних в списку

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

Критерії діляться на два типи: критерії порівняння і обчислювальні критерії.

Критерії порівняння використовують для тих записів, дані в яких точно співпадають із заданим критерієм або потрапляють в певний інтервал. Якщо один або декілька символів критерію невідомі, тоді в записі критерію використовують символи-замінники: зірочка (*) і знак, питання (?).

Для заміщення в критерії одного символу використовують знак (?), питання, а для заміщення будь-якої послідовності символів використовують зірочку (*).

Для отримання даних, які потрапляють в певний інтервал, використовують символи порівняння:

٧ = (рівно);

٧ > (більше);

٧ >= (більше або дорівнює);

٧ < (менше);

٧ <= (менше або дорівнює);

٧ < >(не рівно).

 

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

Для отримання записів, які відповідають заданим критеріям, в Excel передбачені три способи: Автофільтр, Форма і Розширений фільтр.

Автофільтр показує тільки рядки, які задовольняють заданому критерію, тимчасово не відображаючи інші рядки. Автофільтр призначений для створення простих фільтрів. Він відображає лише ті рядки (записи) які задовольняють заданому критерію.

Критерій допускає використання умов з одним логічним оператором И або ИЛИ. Автофільтр допускає використання миші.

Форма показує вибрані рядки по одній. Це спрощує редагування даних в записах. Форма не дозволяє використовувати умови, сполучені оператором ИЛИ.

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

Використання автофільтру. Метод фільтрації за допомогою автофільтру є простим. Фільтрація в цьому випадку відбувається автоматично. Щоб скористатися автофільтром потрібно:

٧ встановити курсор в комірку списку або виділити діапазон комірок, які слід відфільтрувати;

٧ виконати команду Дані >Фільтр> Автофільтр.

 

В результаті в заголовках стовпців з'являться кнопки автофільтру із стрілками, за допомогою яких задаються критерії. Якщо клацнути по кнопці автофільтру, то розкриється список з доступними опціями:

Таблиця 1

Опція автофільтру	Призначення опції
Все	Відобразити всі рядки (відміна фільтру)
Перші 10...	За умовчанням відображає перші 10 записів значеннями по вибраному полю. Цю опцію можна застосовувати до таблиць, які містять текст. з|із| найбільшими можна застосовувати до
Умова	Використовується за завдання умов фільтрації списку.
Порожні	Використовується для відображення всіх записів, які містять в даному стовпці порожні комірки.
Непорожні	Використовується для відображення всіх записів, за винятком тих, які містять порожні комірки в заданому стовпці.

 

Кнопки автофільтру будуть відображені в заголовках стовпців до тих пір, поки фільтр не буде вимкнений. Вимкнути автофільтр можна, повторно скориставшись командою Дані >Фільтр >Автофільтр.

Опції Порожні і Непорожні доступні лише тоді, коли у вибраному для фільтрації стовпці є порожні комірки.

 

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

Опція Умова в списку опцій кнопки автофільтру вибирається тоді, коли потрібно задати декілька умов, сполучених між собою логічним оператор ИЛИ або включити в умову пошуку знак підстановки. Після виконання команди Дані > Фільтр > Автофільтр клацнути мишею на кнопці автофільтру в стовпці, який містить дані для фільтрування, і з|із| розгорненого списку вибрати опцію Умова. Відкривається вікно діалогу Призначений для користувача автофільтр. У цьому вікні можна встановити наступні параметри фільтрування списку:

 

٧ Відповідність одному критерію. Для установки цього параметра фільтрування слід вибрати оператора порівняння в першому текстовому полі групи опцій Показати тільки ті терміни, значення яких (більше, менше і т.д.) Потім у верхньому правому полі необхідно ввести значення, з яким порівнюватимуться дані із списку.

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

٧ Відповідність одному з двох критеріїв. Для установки цього параметра слід вибрати оператора порівняння і значення для першого критерію, потім встановити перемикач ИЛИ. Потім вибрати оператора і значення для другого критерію.

 

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

 

Створення зведених таблиць

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

Майстер запрошує розташування даних, необхідних для створення зведеної таблиці:

Створювати таблицю на основі даних, що знаходяться:

· у списку або базі даних Microsoft Excel;

· у зовнішньому джерелі даних;

· у декількох діапазонах консолідації;

· у іншій зведеній таблиці.

 

Якщо передбачається використовувати список, розташований на робочому листі Excel, слід прийняти встановлюване за умовчанням положення перемикача " в списку або базі даних Microsoft Excel". Нажати кнопку Далі та вказати в полі Діапазон (опреділити) діапазон даних БД.

 

2). Після того, як діапазон комірок визначений, натиснути кнопку Далі. З'явиться діалогове вікно Майстра. У цьому діалоговому вікні необхідно визначити формат створюваної зведеної таблиці (кнопка Макет). У зведених таблицях використовується технологія перетягання, характерна для Windows-програм. Її можна застосовувати для визначення заголовків рядків і стовпців, а також полів даних, як при створенні таблиці, так і в майбутньому, при модифікації вже створеної таблиці.

 

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

3,4). Перетягнути потрібні кнопки полів в область Стовпець Рядок.

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

6). Натиснути кнопку Далі. Встановити в цьому вікні перемикач Помістити таблицю в положення новий лист і натиснути кнопку Готово.

Окрім таблиці на екран буде виведена панель інструментів Зведені таблиці.

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

Тема 6. Бази даних Excel

1. Створення і редагування списку

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

3. Фільтрація даних в списку

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

5. Використання зведених таблиць

 

Створення і редагування списку

База даних – це систематизоване сховище інформації (наприклад, телефонний довідник).

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

Назви стовпців називаються іменами полів.

Самі стовпці – полями.

Рядки даних називаються записами.

У Excel базу даних частіше називають списком.

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

Для створення списку потрібно ввести назву кожного стовпця списку. Створити список можна в будь-якій частині робочого листа, яка не містить яких-небудь даних. Кожен список повинен бути відокремлений від інших порожнім рядком або стовпцем. При створенні списків доцільно дотримуватися наступних правил:

٧ На робочому листі краще розміщувати не більше одного списку;

٧ Кожен стовпець повинен мати заголовок, який повинен займати лише один рядок робочого листа;

٧ Між заголовками робочого листа і заголовком стовпців списку доцільно залишити хоч би один порожній рядок;

٧ Не допускати появи порожніх рядків або стовпців;

٧ Результати розрахунків по стовпцях, розміщених під списком, доцільно відокремити від списку порожнім рядком;

٧ Кожен список повинен бути відокремлений від інших даних порожнім рядком або стовпцем.

 

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

 

Для введення і редагування записів Excel має Форми даних.

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

Для створення форми потрібно активізувати одну з комірок, а потім виконати команду Дані>Форма. Excel автоматично генерує форму. Якщо в список дані ще не введені, тоді автоматично буде створений новий запис. В процесі введення нового запису Excel завжди додає порожній рядок внизу списку. Якщо в рядку під списком приведені певні підсумки або будь-які інші дані, то при спробі ввести новий запис Excel виводить повідомлення: «Список або базу даних розширити неможливо». Після цього потрібно закрити форму і вставити додаткові порожні рядки, щоб мати можливість вводити нові записи за допомогою форми.

 

Після введення команди Дані>Форма слідклацнути на кнопці Додати.

Ввести в кожне текстове поле форми дані, скориставшись клавішею [ Tab ] для переміщення від одного поля до іншого. [ Shift-Tab ] використовується для переміщення до попереднього текстового поля.

Після введення даних в останнє текстове поле, слід натиснути клавішу [ Enter ]. При цьому в списку з'явиться введений запис і новий бланк форми для введення нового запису.

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

За допомогою кнопок Далі і Назад можна проглянути всі записи списку. Для проглядання записів в списку можна використовувати смужку прокрутки і клавіші управління курсором:

§ проглянути відповідно попередній/наступний запис;

§ [Ctrl-PgDn] – перейти до нового запису форми;

§ [PgUp] – проглянути перший запис.

 

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

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

Для видалення запису необхідно:

٧ Встановити курсор в будь-яку комірку списку;

٧ Виконати команду Дані>Форма;

٧ За допомогою кнопки Назад/Далі знайти потрібний запис;

٧ Клацнути на кнопці Видалити;

٧ Клацнути на кнопці ОК або натиснути [ Enter ] для підтвердження видалення запису;

٧ Клацнути на кнопці Закрити для закриття вікна діалогу і повернення в робочу таблицю.

Для пошуку окремих записів таблиці також можна використовувати форму:

٧ Виділити комірку в списку;

٧ Виконати команду Дані>Форма;

٧ Клацнути по кнопці Критерії;

٧ Ввести умови тексту;

٧ Для проглядання записів по заданому критерію натиснути кнопку Далі або Назад;

٧ Клацнути по кнопці Закрити для повернення в робочу таблицю.

 

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

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

Для сортування списку потрібно:

٧ встановити покажчик на будь-яку комірку в списку (або виділити записи, які підлягають сортуванню);

٧ виконати команду Дані > Сортування;

٧ у списках полів, що розгортаються, слід послідовно вибрати імена полів списку, по яких буде проводиться сортування, потім вибрати опцію За збільшенням або За зменшенням відповідно до мети сортування. Щоб запобігти міткам стовпців від сортування разом із списком слід в розділі Ідентифікуватиполя вибрати опцію Попідписам;

٧ клацнути по кнопці ОК або натиснути [Enter];

٧ якщо при сортуванні в алфавітному порядку потрібно розрізняти великі і малі літери, то натисніть кнопку Параметри для відкриття вікна діалогу Параметри сортування, і в ньому встановити опцію Враховувати регістр. За умовчанням виділений список сортується по рядками. Для сортування списку по стовпцях потрібне у вікні Параметри сортування вибрати опцію Стовпці діапазону;

٧ для завдання власного порядку сортування у вікні діалогу Параметри сортування в полі Порядок сортування по першому ключу потрібно вибрати бажаний порядок сортування. Його можна задати за допомогою команди Сервіс > Параметри > Списки > у полі Елементи списку ввести елемент списку в потрібному порядку ввести кожний елемент списку, завершувати натисненням Enter), а потім натиснути кнопку Додати.

 

Фільтрація даних в списку

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

Критерії діляться на два типи: критерії порівняння і обчислювальні критерії.

Критерії порівняння використовують для тих записів, дані в яких точно співпадають із заданим критерієм або потрапляють в певний інтервал. Якщо один або декілька символів критерію невідомі, тоді в записі критерію використовують символи-замінники: зірочка (*) і знак, питання (?).

Для заміщення в критерії одного символу використовують знак (?), питання, а для заміщення будь-якої послідовності символів використовують зірочку (*).

Для отримання даних, які потрапляють в певний інтервал, використовують символи порівняння:

٧ = (рівно);

٧ > (більше);

٧ >= (більше або дорівнює);

٧ < (менше);

٧ <= (менше або дорівнює);

٧ < >(не рівно).

 

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

Для отримання записів, які відповідають заданим критеріям, в Excel передбачені три способи: Автофільтр, Форма і Розширений фільтр.

Автофільтр показує тільки рядки, які задовольняють заданому критерію, тимчасово не відображаючи інші рядки. Автофільтр призначений для створення простих фільтрів. Він відображає лише ті рядки (записи) які задовольняють заданому критерію.

Критерій допускає використання умов з одним логічним оператором И або ИЛИ. Автофільтр допускає використання миші.

Форма показує вибрані рядки по одній. Це спрощує редагування даних в записах. Форма не дозволяє використовувати умови, сполучені оператором ИЛИ.

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

Використання автофільтру. Метод фільтрації за допомогою автофільтру є простим. Фільтрація в цьому випадку відбувається автоматично. Щоб скористатися автофільтром потрібно:

٧ встановити курсор в комірку списку або виділити діапазон комірок, які слід відфільтрувати;

٧ виконати команду Дані >Фільтр> Автофільтр.

 

В результаті в заголовках стовпців з'являться кнопки автофільтру із стрілками, за допомогою яких задаються критерії. Якщо клацнути по кнопці автофільтру, то розкриється список з доступними опціями:

Таблиця 1

Опція автофільтру	Призначення опції
Все	Відобразити всі рядки (відміна фільтру)
Перші 10...	За умовчанням відображає перші 10 записів значеннями по вибраному полю. Цю опцію можна застосовувати до таблиць, які містять текст. з|із| найбільшими можна застосовувати до
Умова	Використовується за завдання умов фільтрації списку.
Порожні	Використовується для відображення всіх записів, які містять в даному стовпці порожні комірки.
Непорожні	Використовується для відображення всіх записів, за винятком тих, які містять порожні комірки в заданому стовпці.

 

Кнопки автофільтру будуть відображені в заголовках стовпців до тих пір, поки фільтр не буде вимкнений. Вимкнути автофільтр можна, повторно скориставшись командою Дані >Фільтр >Автофільтр.

Опції Порожні і Непорожні доступні лише тоді, коли у вибраному для фільтрації стовпці є порожні комірки.

 

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

Опція Умова в списку опцій кнопки автофільтру вибирається тоді, коли потрібно задати декілька умов, сполучених між собою логічним оператор ИЛИ або включити в умову пошуку знак підстановки. Після виконання команди Дані > Фільтр > Автофільтр клацнути мишею на кнопці автофільтру в стовпці, який містить дані для фільтрування, і з|із| розгорненого списку вибрати опцію Умова. Відкривається вікно діалогу Призначений для користувача автофільтр. У цьому вікні можна встановити наступні параметри фільтрування списку:

 

٧ Відповідність одному критерію. Для установки цього параметра фільтрування слід вибрати оператора порівняння в першому текстовому полі групи опцій Показати тільки ті терміни, значення яких (більше, менше і т.д.) Потім у верхньому правому полі необхідно ввести значення, з яким порівнюватимуться дані із списку.

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

٧ Відповідність одному з двох критеріїв. Для установки цього параметра слід вибрати оператора порівняння і значення для першого критерію, потім встановити перемикач ИЛИ. Потім вибрати оператора і значення для другого критерію.

 

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