Ділова графіка. Упорядкування та пошук даних в таблиці. Фільтри

Урок 10

Тема: Ділова графіка. Упорядкування та пошук даних в таблиці. Фільтри

Навчальна мета: Познайомити учнів із середовищем табличного процесора; дати означення таких понять як, ділова графіка, фільтри, пошук за заданими умовами.

Розвивальна мета: Розвивати координацію рухів, зорову пам’ять, вміння працювати з програмами

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

Тип уроку:  Урок вивчення нового матеріалу (лекція)

Структура уроку

1. Організаційний момент
2. Етап орієнтації
3. Етап проектування
4. Етап навчальної діяльності
5. Робота за ПК
6. Контрольно-оцінювальний етап
7. Домашнє завдання                                                                                                                                                                                                                                                                                               

Хід уроку

І. Організаційний момент

Сьогодні ми з вами розглянемо тему: «Ділова графіка. Упорядкування та пошук даних в таблиці. Фільтри».

II. Етап орієнтації

Мета сьогоднішнього уроку якомога найкраще познайомитися з табличним процесором

III. Етап проектування

План уроку

1. Ділова графіка.
2. Упорядкування та пошук даних в таблиці.
3. Фільтри

ІV. Етап навчальної діяльності

Ділова графіка.

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

Бізнесмен одержує цінну для себе інформацію, аналізуючії графіки реалізації товару. Згідно цього аналізу замовлення на один товар зменшується, на інший - залишається без змін, на третій - збільшується.

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

Діаграми і графіки в електронній таблиці

В електронні таблиці включені спеціальні засоби, названі діловою графікою, які можуть табличні дані зобразити у графічному вигляді. Інформацію, що міститься в таблиці, можна у графічному вигляді зобразити по-різному. Серед стандартних діаграм і графіків є такі: гістограма, лінійчата, графік, кругова, крапкова, з областями, кільцева, пелюсткова, поверхнева, бульбашкова, біржова, циліндрична, конічна, пірамідна. Крім того, Ехсеl пропонує більше 20 нестандартних діаграм і графіків.

Кругова діаграма Стовпчикова діаграма Лінійний графік

Основою всіх цих різноманітних діаграм і графіків є три різновиди: кругова діаграма, стовпчикова діаграма і лінійний графік.

Всі види діаграм мають право на існування. Все залежить від того, що саме діаграма повинна показати найкраще.

Кругові діаграми більш наочно показують співвідношення частин у цілому. На кругову діаграму виводяться співвідношення показників, РОЗМІЩЕНИХ в одному рядку або стовпчику, співвідношення даних вказуються у відсотках від цілого.

В кругових діаграмах немає осей X і У.

Стовпчикові діаграми краще показують кількісні характеристики одночасно в кількох рядках і стовпчиках.

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

'Майстер діаграм' В Ехсеl для створення різноманітних діаграм є "Майстер діаграм", який основну роботу бере на себе, і користувачу залишається тільки робити вибір із його пропозицій. Діаграму можна створити у 4 кроки на окремому робочому листі або вставити в уже існуючий.

Створення діаграми розглянемо на такому прикладі: нехай у місті Шепетівці пройшли вибори мера. Результати виборів звели у таблицю (назви партій і кількість голосів умовні).

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

• Виділити на робочому листі дані. які необхідно відобразити на діаграмі (нехай виділено діапазон комірок FЗ:F8).

• Клацнути на кнопці "Мастер диаграмм", що на стандартній панелі інструментів, з'явиться вікно "Мастер диаграмм [шаг 1 из 4]: тип диаграммы".

• Виконати крок 1 - вибрати вкладку "Стандартные" або "Нестандартные", вибрати тип діаграми у списку "Тип:", уточнити різ¬новид типу у списку "Вид:" (Нехай для нашого прикладу вибрали "Круговая"). Подивитися результат, натиснувши і притримуючи кнопку "Просмотр результата". Клацнути на кнопці "Далее" - з'явиться діалогове вікно "Мастер диаграмм [шаг 2 из 4]: источник данных диаграммы".

• Виконати крок 2 - вибрати джерело даних діаграми. Якщо треба змінити діапазон даних (наприклад, =Лист!$Р$3:$Р$8), вибрати вкладку "Диапазон данных". У вкладці "Ряд" вибирають послідовність даних, для якої у полі "Ім'я" можна задати ім'я, звідки його взяти, а у полі "Значення" модифікувати діапазон комірок. У полі "Подписи оси X" можна вказати комірки, звідки ці підписи беруться. Клацнути на кнопці "Далее" - з'явиться нове діалогове вікно "Мастер диаграмм [шаг 3 из 4]: параметры диаграммы ".

• Виконати крок 3 - вибрати параметри діаграми у шістьох вкладках. У вкладці "Заголовки" у полі "Название диаграммы" внести назву всієї діаграми; у полях "Ось Х", "Ось У" і "Ось Z" внести назви відповідних осей. У вкладці "Оси" вибирають способи відображення написів. У вкладці Линии сетки" задають на діаграмі основні та допоміжні лінії. У вкладці "Легенда" можна розмістити на діаграмі у вибраному місці пояснюючий текст.

У вкладці "Подписи донных" послідовності даних можуть бути підписані своїми значеннями. У вкладці "Таблица донных" можна приєднати до діаграми таблицю її вихідних даних. Клацнути на кнопці "Далее" - з'явиться діалогове вікно "Мастер диаграмм [шаг 4 из 4): размещение диаграммы ".

• Виконати крок 4 - вказати, де повинна знаходитися діаграма - на окремому або поточному листі з таблицею. Клацнути на кнопці "Готово". Якщо діаграму буде розміщено на окремому листі, то краще зробити пояснюючий напис для її ярлика.

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

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

При роботі з діаграмами слід врахувати, що:

• будь-які зміни значень в комірках таблиці, які використані для побудови діаграми, одразу ж відображаються на самій діаграмі;

• перетягування мишею однієї з точок на діаграмі викликає зміну значення у відповідній комірці таблиці;

• додати в діаграму нову послідовність даних можна транспорту¬ванням мишею виділених даних в область діаграми.

Упорядкування та пошук даних в таблиці.

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

Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД).

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

Впорядкування (сортування) – це зміна відносного положення даних у списку відповідно із значенням або типом даних.

               або              Дані → Сортування

При створенні списку на робочому листі  Ехсеl необхідно виконувати такі правила:

• На одному робочому листі не слід поміщати більш одного списку, оскільки деякі операції, наприклад, фільтрація, працюють у визначений момент тільки з одним списком.
• Варто відокремлювати список від інших даних робітника листа хоча б одним порожнім стовпцем або одним порожнім рядком. Це допоможе Ехсеl автоматично виділити список при виконанні фільтрації або при сортуванні даних.
• Імена стовпців повинні розташовуватися в першому рядку списку.  Ехсеl використовує ці імена при створенні звітів, у пошуку і сортуванні даних.
• Для імен стовпців варто використовувати шрифт, тип даних, вирівнювання, формат, рамку або стиль прописних букв, відмінні від тих, котрі використовувалося для даних списку.
• Щоб відокремити імена стовпців від даних, варто розмістити рамку по нижньому краї кліток

За допомогою Microsoft Excel можна створювати і обробляти бази даних. База даних в Microsoft Excel – таблиця, що складається з однотипних записів (рядків). Стовпці таблиці є полями запису в базі даних. Під імена полів приділяється перший рядок у базі даних. Наприклад, якщо базою даних вважати телефонний довідник, то полями запису будуть: прізвища, номера телефонів і адреси абонентів.

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

Сортування дозволяє переупорядкувати рядки в таблиці по будь-якому полі. Наприклад, щоб упорядкувати дані за ціною виробу. Для сортування даних слід виділити одне вічко таблиці і викликати команду Сортировка із меню Данные .

У полі прихованого переліку Сортировать по (мал.33) вибирається поле, по якому будуть відсортовані дані, і тип сортування:

по возростанию   – цифри сортуються по зростанню, текст – за абеткою, логічні вираження – ЛОЖЬ передує ИСТИНА .

по убыванию – сортування в зворотному порядку.

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

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

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

У Microsoft Excel не слід робити нічого особливого для того, щоб використовувати перелік в якості бази даних. При виконанні характерних для баз даних операцій, таких як пошук, сортування, підведення підсумків, Microsoft Excel автоматично розглядає таблицю як базу даних.

При перегляді, зміні, додаванні і видаленні запису в базі даних, а також при пошуку конкретних записів за визначеним критерієм зручно використовувати форми даних. При звертанні до команди Форма меню Данные Microsoft Excel читає дані й створює діалогове вікно форми даних (мал.34). У формі даних на екран виводиться один запис. При уведенні або зміні даних у полях цього вікна змінюється уміст відповідних вічок у базі даних.

Для використання форм даних таблиця повинна мати імена стовпців. Імена стовпців стають іменами полів у формі даних. Поле відповідає кожному стовпцю в таблиці. Форма даних автоматично розгортається так, щоб вивести на екран відразу усі поля в даній таблиці, до 32 полів за один раз. За допомогою смуги прокручування можна прокручувати записи в базі даних. Позиція виведеного запису вказується у верхньому правом куті. Пересуватись по полях форми можна за допомогою миші та клавіш Tab (униз), Shift+Tab (угору). Праворуч розташовані такі кнопки.

Добавить – очищує поля для уведення нового запису бази даних. Якщо знову натиснути кнопку Добавить , то уведені дані будуть додані як новий запис у кінець бази даних.

Удалить – видаляє виведений запис, інші записи бази даних зсуваються. Видалені записи не можуть бути відновлені.

Мал 34                                        

Вернуть – відновлює відредаговані поля у виведеному запису, видаляючи зроблені зміни. Щоб відновити запис, необхідно зробити це перед натисканням клавіші Enter або перед переходом до іншого запису.

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

Далее – виводить наступний запис у базі даних.

Критерии – очищає поля для уведення критеріїв порівняння з операторами порівняння для пошуку необхідної підмножини записів.

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

Очистить – видаляє існуючий критерій із вікна діалогу. Доступна тільки тоді, коли натиснута кнопка Критерии .

Закрыть – закриває форму даних.

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

• ­      виділити вічко в таблиці, до котрого слід додати запис;
• ­      у меню Данные вибрати команду Форма натиснути кнопку Добавить ;
• ­      заповнити поля нового запису;
• ­      для переміщення до наступного поля натиснути клавішу Тab ;
• ­      після уведення даних натиснути клавішу Enter для додавання запису;
• ­      після додавання усіх необхідних записів, натиснути кнопку Закрыть .

Нові записи будуть додані в кінець бази даних.

Установлення інтервалу критеріїв

Критерії бувають двох типів.

1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21 ) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у вічках F7:F21 . Формула повинна повертати логічне значення ЛОЖЬ або ИСТИНА . При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА .

2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700 ).

Для пошуку за допомогою форми даних записів, що відповідають критерію, необхідно:

• ­виділити вічко в таблиці;
• ­у меню Данные вибрати команду Форма натиснути кнопку Критерии ;
• ­у полях редагування увести критерії для пошуку даних;
• ­для виводу на екран першого запису, що відповідає критерію, натиснути кнопку Далее
• ­для виводу на екран попереднього запису, що відповідає  критерію, натиснути кнопку Назад
• ­для пошуку записів у переліку по іншому критерії натиснути кнопку Критерии і увести новий критерій;
• ­по закінченні натиснути кнопку Закрыть .

Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии , а потім натиснути кнопку Правка.

Фільтри

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

Автофильтр

Команда Автофильтр установлює кнопки прихованих переліків (кнопки зі стрілкою) безпосередньо в рядок із іменами стовпців (мал.35). З їхньою допомогою можна вибрати записи бази даних, що слід вивести на екран. Після виділення елементу в переліку, що розкривається, рядки, що не містять даний елемент, будуть приховані. Наприклад, якщо у прихованому переліку поля Цена вибрати 99грн. , то будуть виведені тільки записи, в яких у полі Цена міститься значення 99грн .

  Мал. 35

 Мал. 36

Якщо у прихованому переліку вибрати пункт Условие … , то з’явиться вікно Пользовательский автофильтр (мал.36). У верхньому правому переліку слід вибрати один з операторів ( равно , больше , меньше та ін.), а у полі праворуч вибрати одне зі значень. У нижньому правому переліку можна вибрати іншій оператор, і у полі ліворуч – значення. Коли увімкнений перемикач И , то будуть виводитися тільки записи, які задовольняють обидві умови. При увімкненому перемикачу ИЛИ будуть виводитися записи, які задовольняють одну з умов. Наприклад, у вікні на мал.36 введені умови для виведення записів по виробам з ціною більше 99грн і менше 187грн .

Для виведення декількох записів з найбільшими або найменшими значенням по будь-якому полю слід у прихованому переліку поля вибрати пункт Первые 10 . У діалоговому вікні Наложение условия по списку у першому полі з лічильником необхідно вибрати кількість записів, а у полі праворуч вибрати наибольших або наименьших .

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

Расширенный фильтр

Команда Расширенный фильтр дозволяє фільтрувати дані з використанням інтервалу критеріїв для виводу тільки тих записів, що задовольняють визначеним критеріям (мал.37). При повторній фільтрації будуть проглядатися усі рядки, і приховані і відкриті. Значення перемикачів і полів вікна Расширенный фильтр наступне:

  Мал. 37

фильтровать список на месте – перемикач, що приховує рядки, які не задовольняють зазначеному критерію;

скопировать результат в другое место – копіює відфільтровані дані на інший робочий аркуш, або на інше місце на тому ж робочому аркуші;

Исходный диапазон – поле, що визначає інтервал, якій містить перелік, що підлягає фільтрації;

Диапазон условий – поле, що визначає інтервал вічок на робочому аркуші, якій містить необхідні умови;

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

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

Для установлення складних критеріїв необхідно:

1.     уставити декілька рядків у верхній частині робочого аркушу;

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

3.     при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпців, що перевіряються;

4.     у рядках, розташованих під рядком із іменами стовпців, що перевіряються, увести критерії, яким повинні відповідати вічка стовпців, що перевіряються;

5.     вибрати в меню Данные підміню Фильтр , а потім команду Расширенный фильтр , і в діалоговому вікні увести умови фільтрації.

Мал. 38

Для об'єднання критеріїв за допомогою умовного оператора И потрібно зазначити критерії в одному і тому ж рядку, а для об'єднання критеріїв за допомогою умовного оператора ИЛИ слід подати критерії в різних рядках. Наприклад, інтервал критеріїв на мал.38 виводить на екран усі записи, що мають у стовпці Цена значення більше 50 і менше 100.

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

Щоб знову вивести усі записи слід у меню Данные вибрати пункт Фильтр і потім пункт Отобразить все .

VI. Контрольно-оцінювальний етап

Запитання для узагальнення

1. Для чого використовують діаграми?
2. Що необхідно для створення діаграми?
3. Що відображається в легенді діаграми?
4. Які дії можна виконувати з діаграмою після її побудови?
5. Скільки заголовків може мати діаграма?
6. Як змінити зовнішній вигляд діаграми?
7. Що таке список?
8. За яких умов табличні дані можна розглядати як список?
9. Коли слід застосовувати впорядкування даних, які зберігаються в таблиці за 2, 3-ма полями?
10. Для даних якого типу можна використовувати умову Перші 10 при застосування Автофільтру?
11. У яких випадках доцільно додавати до таблиці проміжні підсумки?
12. Як відобразити в таблиці лише підсумкові записи?
13. Поля якого типу можуть бути розміщені в області Дані зведеної таблиці?

VII.Домашнє завдання

Вивчити терміни, конспект  § 7.6-7.11, 14,15 пит.1,3,4,6 на с.136  + пит..2,4,6 с.149