Главная —› Страничка студента —› Учебно-методические материалы —› Темы для самостоятельного изучения —› ЕП
Тема: Робота з базами даних в електронних таблицях.
Мета:
- Розглянути можливості Excel що до роботи з інформацією як базою даних;
- Розглянути команду сортування даних у таблиці;
- Охарактеризувати команди фільтрації даних;
- Дати поняття консолідації даних;
- Розглянути команду підсумків.
Студенти повинні знати:
- Як повинна бути організована таблиця, щоб програма MS Excel сприймала її як базу даних;
- Команду сортування даних і її можливості;
- Команди фільтрації даних;
- Можливості консолідації даних в електронних таблицях;
- Для чого використовується команда Проміжні підсумки.
Студенти повинні вміти:
- Сортувати дані в електронній таблиці;
- Працювати з командами фільтрації;
- Виконувати консолідацію даних;
- Працювати з командою Проміжні підсумки.
Перелік понять:
База даних, таблиця, сортування, фільтрація, консолідація, підсумки.
План.
- Пошук і заміна даних.
- Сортування даних:
- Правила сортування.
- Порядок сортування.
- Сортування за значеннями одного стовпця.
- Сортування по декількох стовпцях.
- Сортування частини діапазону.
- Установка фільтра.
- Використання розширеного фільтру.
- Видалення дублікатів даних.
- Консолідація даних.
- Підсумки в таблицях.
Перелік понять: база даних, пошук даних, заміна даних, сортування, фільтрація, користувацький фільтр, розширений фільтр, консолідація даних, проміжні підсумки.
В MS Excel база даних являє собою таблицю, організовану у вигляді списку. Для того, щоб Excel сприймав дані певного діапазону як базу даних, необхідно виконання таких вимог:
- Дані мають бути занесені у вигляді простої структури(немає об’єднаних комірок);
- Перший рядок містить назви(імена) полів, усі інші рядки містять дані;
- Під кожною назвою поля у стовпчику таблиці розташовані однотипні дані, які являють собою значення відповідного поля;
- Кожен рядок таблиці – це запис бази даних;
- У базі даних не має бути порожніх рядків та стовпчиків;
- База даних з усіх боків повинна відокремлюватись порожніми рядками та стовпчиками.
Пошук і заміна даних
Пошук даних
Пошук даних можна робити на всьому аркуші або у виділеній області аркуша, наприклад, тільки в деяких стовпцях або рядках, а також відразу у всій книзі. Для цього:
- У групі Редагування вкладки Головна клацніть по кнопці Знайти й виділити й виберіть команду Знайти.
- У поле Знайти вкладки Знайти вікна Знайти й замінити введіть шукані дані. При пошуку можна використовувати знаки: * (зірочка) заміняє будь-яку кількість будь-яких символів; ? (знак питання) заміняє один будь-який символ.
- Для розширення можливостей пошуку у вкладці Знайти діалогового вікна Знайти й замінити натисніть кнопку Параметри. При цьому з'являться нові елементи діалогового вікна.
- У списку Шукати виберіть область пошуку: на аркуші або в книзі. У другому випадку будуть проглядатися дані всіх аркушів книги (за винятком схованих).
- У списку Область пошуку виберіть спосіб пошуку: по формулах (формули) або за значеннями комірок (значення). Наприклад, комірка може показувати значення "100", але містити формулу =А8*25. При пошуку числа "100" по формулах ця комірка знайдена не буде. При пошуку числа "100" за значеннями цей комірка буде знайдена. Пошук також можна виконувати по примітках, доданих до комірок.
- Встановіть прапорець Враховувати регістр, якщо при пошуку необхідно розрізняти прописні й малі літери.
- Встановіть прапорець Комірку цілком для забезпечення пошуку точного збігу з набором символів, заданих у полі Знайти, а не всіх слів, у які шукане слово входить як складова частина.
- Натисніть кнопку Знайти далі для знаходження найближчої комірки, що містить шукане значення. Знайдена комірка буде виділена, а її вміст відображений у рядку формул.
- Натисніть кнопку Знайти все для відображення списку всіх комірок аркуша або книги, що містять шукане значення. Для переходу до потрібної комірки клацніть мишею по її запису в списку.
З використанням вкладки Знайти діалогового вікна Знайти й замінити можна шукати не тільки конкретні дані, але й елементи оформлення комірок.
Заміна даних
Заміну даних, так само як і пошук, можна робити на всьому аркуші або у виділеній області аркуша, наприклад, тільки в деяких стовпцях або рядках, а також відразу у всій книзі.
- У групі Редагування вкладки Головна клацніть по кнопці Знайти й виділити й виберіть команду Замінити.
- У поле Знайти вкладки Замінити вікна Знайти й замінити введіть шукані дані, а в поле Замінити на – дані, якими треба замінити.
- Так само як і при пошуку даних, для розширення можливостей заміни у вкладці Замінити діалогового вікна Знайти й замінити натисніть кнопку Параметри й Встановіть особливості пошуку й заміни.
- Для заміни даних на всьому аркуші (книзі) натисніть кнопку Замінити всі, після чого вийде повідомлення про кількість зроблених замін. Якщо ж потрібно замінити тільки деякі із шуканих даних, то варто послідовно натискати кнопку Знайти далі й після знаходження необхідного значення нажати кнопку Замінити.
З використанням вкладки Замінити діалогового вікна Знайти й замінити можна заміняти не тільки конкретні дані, але й елементи оформлення комірок.
Сортування даних
Сортування - розташування даних на аркуші в певному порядку.
Найчастіше необхідно сортувати рядки з даними. Як правило, при сортуванні впорядковуються повністю рядки, але можна сортувати й окремі комірки.
Основні правила сортування
Сортування можна робити як по зростанню, так і по спаданню. При бажанні можна сортувати дані відповідно до власного порядку сортування.
Оскільки при сортуванні Microsoft Excel автоматично визначає зв'язаний діапазон даних, діапазон,що сортується не повинен мати порожніх стовпців. Наявність порожніх рядків допускається, але не рекомендується.
При сортуванні заголовки стовпців звичайно не сортуються разом з даними, але діапазон, що сортується може й не мати заголовків стовпців.
Можна виконувати сортування даних по тексту (від А к Я або від Я к А), числам (від найменших до найбільшого або від найбільших до найменшого), а також датам і часу (від старих до нових або від нових до старих). Можна також виконувати сортування по спискам, що налаштовуються, по форматам, включаючи кольори комірок, кольори шрифту, а також по значках.
Порядок сортування
За зростанням
При сортуванні по зростанню використається наступний порядок:
Числа
Числа сортуються від найменшого від’ємного до найбільшого додатного числа.
Буквено-цифрове сортування
При сортуванні алфавітно-цифрового тексту порівнюються значення по знаках зліва направо. Наприклад, якщо комірка містить текст "Будинок100", вона буде поставлена після комірки, що містить запис "Будинок1", і перед коміркою, що містить запис "Будинок12".
Логічні значення
Логічне значення НЕПРАВДА ставиться перед значенням ІСТИНА.
Значення помилки
Всі значення помилки рівні.
Порожні значення
Порожні значення завжди ставляться в кінець.
По спаданню
При сортуванні по спаданню порядок заміняється на зворотний, за винятком порожніх комірок, які завжди містяться в кінець списку.
Сортування за значеннями одного стовпця
Найпростіше сортування виконується за даними одного стовпця:
- Виділіть одну будь-яку комірку в стовпці, за даними якого сортується таблиця.
- Натисніть кнопку Сортування й фільтр групи Редагування вкладки Головна й виберіть напрямок сортування. Назва команди в меню залежить від типу даних, що сортуються (числа, текст або календарні дані).
Для сортування можна також використати кнопки групи Сортування й фільтр вкладки Дані.
Крім того, для сортування таблиці за даними одного стовпця можна використати автофильтр.
Сортування по декількох стовпцях
Сортування можна здійснювати по декількох стовпцях для угруповання даних з однаковими значеннями в одному стовпці й наступному здійсненні сортування іншого стовпця або рядка в цих групах з однаковими значеннями.
Можна одночасно здійснювати сортування по 64 стовпцям.
Сортування по декількох стовпцях:
- Виділіть одну будь-яку комірку у діапазоні даних,що сортуються.
- Натисніть кнопку Сортування й фільтр групи Редагування вкладки Головна і виберіть команду «Настраиваемая сортировка», або натисніть кнопку Сортування групи Сортування й фільтр вкладки Дані.
- У вікні Сортування в списку Стовпець, виберіть назву стовпця, за даними якого буде виконуватися сортування. У списку Сортування, виберіть ознаку сортування (значення, кольори комірки, кольори шрифту або значок комірки). Потім клацніть по стрілці списку Порядок, і виберіть напрямок сортування або кольори (значок).
- У вікні Сортування натисніть кнопку Додати рівень і після того, як з'явиться новий рядок, виберіть назву стовпця, за даними якого буде виконуватися наступне сортування. Аналогічно попередньому пункту налаштуйте порядок сортування. Повторіть цю дію для інших стовпців.
- Після вибору всіх необхідних рівнів сортування натисніть кнопку ОК.
Сортування частини діапазону
Можна сортувати частину даних таблиці, наприклад, тільки дані одного стовпця без впливу на інші стовпці:
- Виділіть комірку діапазону,що сортується.
- Натисніть кнопку Сортування й фільтр групи Редагування вкладки Головна й виберіть напрямок сортування. Або натисніть відповідну кнопку групи Сортування й фільтр вкладки Дані.
- У попередженні, що з'явилося Встановіть перемикач сортувати в межах зазначеного діапазону й натисніть кнопку Сортування.
Будьте обережні при використанні цього засобу. Сортування по одному стовпці діапазону може привести до небажаних результатів, наприклад, переміщенню комірок у цьому стовпці щодо інших комірок рядка.
Відбір даних
Найпростішим інструментом для вибору й відбору даних є фільтр. У відфільтрованому списку відображаються тільки рядки, що відповідають умовам, заданим для стовпця.
На відміну від сортування, фільтр не змінює порядок записів у списку. При фільтрації тимчасово ховаються рядки, які не потрібно відображати.
Рядка, відібрані при фільтрації, можна редагувати, форматувати, створювати на їхній основі діаграми, виводити їх на друк, не змінюючи порядок рядків і не переміщаючи їх.
Крім того, для вибору даних можна використати можливості пошуку даних, форми й деякі функції.
Установка фільтра
Фільтри можна встановити для будь-якого діапазону, розташованого в будь-якому місці аркуша. Діапазон не повинен мати повністю порожніх рядків і стовпців, окремі порожні комірки допускаються.
Установка фільтра:
- Виділіть одну будь-яку комірку у діапазоні, для якого встановлюються фільтри.
- Натисніть кнопку Сортування й фільтр групи Редагування вкладки Головна й виберіть команду Фільтр або натисніть кнопку Фільтр групи Сортування й фільтр вкладки Дані.
- Після установки фільтрів у назвах стовпців таблиці з'являться значки списків.
Для видалення фільтрів виділіть одну будь-яку комірку у діапазоні, для якого встановлені фільтри, натисніть кнопку Сортування й фільтр групи Редагування вкладки Головна й виберіть команду Фільтр або натисніть кнопку Фільтр групи Сортування й фільтр вкладки Дані.
Фільтри автоматично встановлюються при оформленні "таблиці" й автоматично віддаляються при перетворенні "таблиці" у звичайний діапазон.
Для вибірки даних з використанням фільтра варто клацнути по значку списку відповідного стовпця й вибрати значення або параметр вибірки.
Заголовки рядків аркуша, обраних з таблиці за допомогою фільтра, відображаються синіми кольорами. На значках списків у назвах стовпців, по яких була зроблена вибірка, з'являється особлива мітка. У рядку стану вікна Excel протягом деякого часу відображається текст із вказівкою кількості знайдених записів і загальної кількості записів у таблиці.
Кількість стовпців, по яких виконується вибірка, не обмежена.
Відбір за значенням
Можна вибрати рядки по одному або декількох значеннях одного або декількох стовпців.
- Клацніть по значку списку стовпця, по якому виконується відбір.
- Відзначте прапорцями значення, по яких виконується відбір. Натисніть кнопку ОК.
При роботі з календарними значеннями автоматично виконується угруповання даних і вибірку можна робити не тільки за конкретним значенням, але й по періодах: рік або місяць.
Відбір по формату
Можна вибрати рядки по кольорах комірки, кольорам тексту або значку одного або декількох стовпців.
- Клацніть по значку списку, стовпця, по якому виконується відбір.
- Виберіть команду Формат по кольорах, а потім у підлеглому меню виберіть кольори комірки, кольори шрифту або значок. Натисніть кнопку ОК.
Відбір за умовою
Можна робити вибірку не тільки за конкретним значенням, але й за умовою.
Умова можна застосовувати для числових значень.
- Клацніть по значку списку стовпця, по якому виконується відбір.
- Виберіть команду Числові фільтри, а потім у підлеглому меню виберіть застосовувану умову.
- При використанні умов: дорівнює, не дорівнює, більше, менше, більше або дорівнює, менше або дорівнює встановіть значення умови у вікні Користувацький автофильтр. Значення умови можна вибрати зі списку або ввести із клавіатури.
Умови можна використати при відборі й для текстових значень.
- Клацніть по значку списку стовпця, по якому виконується відбір.
- Виберіть команду Текстові фільтри, а потім у підлеглому меню виберіть застосовувану умову.
- При використанні умов: дорівнює, не дорівнює, містить, не містить, починається з, не починається з, закінчується на, не закінчується на встановите значення умови у вікні Користувацький автофильтр. Значення умови звичайно вводять із клавіатури.
У всіх випадках при використанні вікна Користувацький автофильтр одночасно можна застосовувати дві умови відбору, поєднуючи їхнім союзом И, якщо потрібно, щоб дані задовольняли обом умовам, або союзом АБО, якщо потрібно, щоб дані задовольняли хоча б одному з них.
Відбір найбільших і найменших значень
Для числових значень можна відібрати рядки по найбільших або найменших значеннях у якому-небудь стовпці.
- Клацніть по значку списку стовпця, по якому виконується відбір.
- Виберіть команду Числові фільтри, а потім у підлеглому меню виберіть команду Перші 10.
- У вікні Накладення умови за списком встановіть кількість (або відсоток від загального числа) елементів, що відбирають, виберіть принцип відбору (найбільші або найменші значення) і режим відбору: відображення рядків з найбільшими або найменшими значеннями (елементів списку) або відображення рядків з найбільшими або найменшими значеннями у відсотковому відношенні (% від кількості елементів).
Крім того, можна вибрати значення вище або нижче середнього.
Відбір по комірці
Дані можна швидко відфільтрувати за допомогою умови, що характеризує вміст активної комірки:
- По комірці клацніть правою кнопкою миші.
- У контекстному меню виберіть команду Фільтр, а потім у підлеглому меню виберіть принцип відбору: за значенням в комірці, кольором комірки, кольором тексту або значку.
Відмова від відбору:
- Клацніть по значку списку стовпця, по якому зроблений відбір.
- Виберіть команду Зняти фільтр.
Видалення дублікатів даних
При роботі з таблицями може виникнути необхідність пошуку й видалення повторюваних даних.
- Виділіть одну будь-яку комірку у діапазоні, у якому необхідно видалити дублікати даних.
- У групі Робота з даними вкладки Дані натисніть кнопку Видалити дублікати. У вікні Видалити дублікати відзначте стовпці, у яких варто шукати повторювані дані. Натисніть кнопку ОК.
Вилучені будуть повністю рядки, що містять у зазначеному стовпці повторювані дані. В інформаційному вікні з результатами пошуку й видалення натисніть кнопку ОК.
Використання розширеного фільтра.
Якщо вибрати розширене фільтрування кнопкою Додатково групи Сортування й фільтр вкладки Дані, можна виконати фільтрацію на місці чи витягти відфільтровані записи і помістити їх окремо, на будь-який робочий аркуш будь-якої відкритої робочої книги.
Перед використанням розширеного фільтру слід створити діапазон критерію.
Діапазон критерію – це спеціально відведена зона робочого листа, яка відповідає таким вимогам:
- Містить не менше двох рядків:
- У першому рядку містяться імена полів;
- В інших рядках під іменами полів – умови, що накладаються на ці поля;
- Не містить порожніх рядків.
Для фільтрації записів з використання розширеного фільтру на основі створеного діапазону критерію необхідно:
- Активізувати будь-яку комірку бази даних;
- Виберіть команду Додатково групи Сортування й фільтр вкладки Дані;
- У діалоговому вікні Розширений фільтр установити потрібні опції.
Консолідація — це об’єднання значень із декількох діапазонів даних.
На практиці дуже часто виникає потреба підведення підсумків за даними, розташованим на різних робочих аркушах. Нехай, наприклад, є 12 робочих аркушів, розташованих в одному або декількох файлах, кожний з який містить відомості про діяльність підприємства за місяць. На основі цих даних потрібно створити новий робочий аркуш, де б підводилися підсумки діяльності за рік. Подібні задачі можна легко вирішити, скориставшись командою Консолідація групи Робота з даними вкладки Дані. Вона дозволяє підсумувати або якось інакше підвести підсумки, базуючись на даних у відповідних комірках робочих аркушів що містять вхідні дані.
Відповідність між комірками може встановлюватися або за їхнім розташуванням на робочому аркуші (наприклад, будуть підсумовані ліві верхні комірки всіх аркушів і т.д.), або по найменуваннях рядків і стовпців, у яких знаходяться комірки.
Щоб виконати консолідацію, необхідно перейти на новий робочий аркуш, де передбачається розміщення підсумкових даних, і вибрати команду Консолідація групи Робота з даними вкладки Дані. При цьому на екрані з'явиться вікно, у якому у графі Функція треба зазначити спосіб підведення підсумків. Звичайно там встановлюється опція Сума, проте можна обрати й інший спосіб підведення підсумків: Кількість значень, Середнє, Максимум, Мінімум тощо.
У графу Посилання один за іншим уводять діапазони комірок, що підлягають консолідації. Для цього треба перейти на потрібний робочий аркуш, виділити діапазон комірок мишкою і натиснути кнопку Додати. У результаті даний діапазон буде занесений у Список діапазонів.
Якщо відповідності між комірками встановлюються за найменуваннями рядків і стовпців, то комірки діапазону треба виділяти разом з їх найменуваннями. Якщо ж відповідності встановлюються по розташуванню комірок, то виділяти слід тільки комірки з даними, а найменування рядків і стовпців у підсумкову таблицю треба скопіювати з одного з робочих аркушів. Після уведення всіх діапазонів, перед натисканням кнопки ОК, у нижній частині вікна слід зазначити чи потрібно при консолідації враховувати найменування рядків і стовпців вхідних таблиць, а також чи потрібно встановлювати зв'язок із цими робочими аркушами. Якщо ви встановите такий зв'язок, то в підсумковій таблиці буде створена структура, у якій можна буде побачити і вхідні дані, і результат їхньої консолідації.
Підсумки в таблицях
Підсумки підводять з метою визначення кращих, гірших, сумарних, середніх показників, наприклад, діяльності фірми в деяких країнах, містах, підрозділах тощо. Для цього спочатку рядки в таблиці сортують з метою групування (розташування поруч) даних, що стосуються кожної країни, міста чи підрозділу. Для отримання підсумків до впорядкованої таблиці застосовують команду Проміжні підсумки групи Робота з даними вкладки Дані, де задають:
- назву поля, що містить об’єкти, для яких створюють підсумки, наприклад;
- операцію підсумування;
- назву поля, що містить дані, що підлягають підсумуванню.
Для обчислення значень проміжних підсумків використовують підсумкову функцію, наприклад Сума , Середнє арифметичне, Мінімум, Максимум.
Проміжні підсумки можна вивести у списку з використанням одночасно декількох типів обчислення.
Література
- О.Спиридонов, Работа в Microsoft Excel 2010, Учебный курс НОУ ИНТУИТ
- Л.М.Дибкова
Інформатика та комп’ютерна техніка: Посібник для студентів вищих навчальних закладів.– К.: - Видавничий центр „Академія”,2002. -320 с. (Альма-матер) - Т.О.Кучерява, М.В.Сільченко, І.В.Шабаліна.
Інформатика та комп’ютерна техніка: активізація навчання:Практикум для індивідуальної роботи. – 2-ге вид, без змін. -
К:КНЕУ, 2008. – 448с. - І.Т.Зарецька, А.М. Гуржій, О.Ю.Соколов
Інформатика: Підручник для 10-11 кл. загальноосвіт. навч. Закладів. У 2-х част.:Форум, 2004.-288с.:іл. - Глинський Я.М.
Практикум з інформатики. Навч. Посібник. 6-те вид. – Львів: Деол, СПД Глинський, 2003. – 224 с.
Контрольні запитання по темі:
- Що являє собою база даних в MS Excel ?
- Виконання яких вимог необхідно, щоб Excel сприймав дані певного діапазону як базу даних?
- За допомогою якої команди можна включити пошук, пошук і заміну в MS Exсеl?
- Які параметри можна вказати при пошуку потрібної інформації в програмі MS Exсеl?
- Що таке сортування?
- Наведіть основні правила сортування в MS Excel.
- Як відбувається в MS Excel сортування за значенням одного стовпця?
- Як відбувається сортування по декількох стовпцях?
- Як відбувається установка фільтра в MS Excel?
- Як видалити фільтр в MS Excel?
- Які дії потрібно виконати для вибірки даних з використанням фільтра?
- По яких критеріях можна відбирати дані за допомогою фільтрації в MS Excel?
- Як вибрати розширене фільтрування?
- Що таке діапазон критерію у розширеному фільтрі?
- Яким вимогам повинен відповідати діапазон критерію?
- Що таке консолідація даних?
- Як виконати консолідацію даних в Excel?
- Для чого використовується команда Проміжний підсумок в MS Excel?
- Яку операцію треба виконати над таблицею в MS Excel перед отриманням проміжного підсумку?
- Яку команду потрібно застосувати до таблиці для отримання підсумків?
Главная —› Страничка студента —› Учебно-методические материалы —› Темы для самостоятельного изучения —› ЕП