Урок 49                                                                       Інформатика (ІКТ)

Таблиця як набір однотипних об’єктів.

Табличні бази даних. Основні операції над даними в базі. Надання діапазонам імен та їх використання. Вбудовані функції ТП для роботи з базою даних. Створення запитів до баз даних із використанням функцій.

Мета.

Навчальна. Ознайомити учнів з таблиця як набіром однотипних об’єктів, табличними базами даних, основними операціями над даними в базі, наданням діапазонам імен та їх використання, з вбудованими функціями ТП для роботи з базою даних, створення запитів до баз даних із використанням функцій. 

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

Виховна. Виховувати наполегливість, естетичність у оформленні, грамотно висловлювати свої думки.

Тип уроку. Засвоєння нових знань і навичок.

Матеріали для роботи з учнями:

Microsoft Exel у профільному навчанні. Завадський І.О., Забарна А.П.

Інформатика 11 клас: підручник для загальноосвітніх навчальних закладів: рівень стандарту / Й.Я. Ривкінд, T.I. Лисенко, Л.А. Чернікова, В.В. Шакотько

План

1. Організація класу.
2. Перевірка домашнього завдання.
3. Таблиця як набори однотипних об'єктів.
4. Вибір даних із таблиці за різними критеріями
5. Обчислення підсумкових показників для об'єктів, що задо­вольняють певним критеріям.
6. Практичні завдання.
7. Типові запитання до уроку.
8. Домашнє завдання.

Пам’ятка для учня!

1. Пригадайте правила техніки безпеки при роботі з ПК.
2. Через кожні 15 хв. виконуйте вправи для очей та для зняття м’язової втоми.

Хід уроку

1. Перевірка домашнього завдання.

1. Наявність.

2. Питання.

2. Актуалізація опорних знань.

1. Дайте означення поняття діаграми.
2. Які є основні типи діаграм?
3. Які є основні елементи діаграм?
4. Що означає поняття форматування?
5. Як вибирати тип діаграми?
6. Форматування діаграм.

3.  Вивчення нового матеріалу.

Таблиця як набір однотипних об'єктів

Найчастіше електронна таблиця містить інформацію про деяку множину однотипних об'єктів, тобто таких, що мають однаковий набір параметрів. Дані про кожен об'єкт записують в окремому рядку таблиці. Так, у класному журналі кожен рядок відповідає одному учню, а в розкладі руху потягів чи автобусів — рейсу. Значення параметрів записують в окремих клітинках, виділяючи для кожного з них свій стовпець. Як приклад розглянемо подану на рис. 5.1 таблицю з розкладом руху автобусів. Об'єкти, які в ній представлено, — це рейси, їхніми параметрами є номер рей­су, пункт відправлення, пункт призначення та час відправлення.

Для роботи з наборами однотипних об'єктів призначено діалого­ве вікно форми даних. У цьому вікні є кілька полів вводу, на­зви яких відповідають заголовкам стовпців таблиці. Вікно форми даних (рис. 5.2) дозволяє провести пошук потрібної інформації, доповнити перелік новими об'єктами, видалити непотрібні, від­редагувати наявні. Відкривається це вікно за допомогою команди Дані ► Форма, перед виконанням якої потрібно виділити будь-яку клітинку в таблиці. У заголовку вікна форми даних вказується ім'я поточного робочого аркуша.

Рис. 5.2. Вікно форми даних

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

Пошук даних у таблиці за простими критеріями

Якщо в таблиці необхідно знайти певне відоме значення, це зав­дання можна виконати за допомогою стандартного засобу пошуку, який запускають командою Правка ► Знайти. Проте зазвичай засто­совується більш складний алгоритм пошуку: значення одного па­раметра шукають за умовою, якій задовольняє інший параметр. Припустимо, що у класному журналі потрібно знайти оцінку, яку учень Жуков отримав за другу тематичну роботу. Тут умо­ва накладається на прізвище, а шукається значення параметра «оцінка за другу тематичну». Надання засобів для виконання подібних запитів є однією з найважливіших функцій будь-якої системи керування базами даних. Відповідні засоби є й у таблич­ному процесорі Microsoft Excel.

Пошук за допомогою форми даних

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

Пошук за значеннями в першому стовпці

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

Значення ключового параметра зазвичай записують у першому стовпці таблиці. Тому функція VLOOKUP (рос. ВПР), яка в Microsoft Excel реалізує пошук за ключовим значенням, працює так. Спо­чатку задане ключове значення відшукується в першому стовпці таблиці, а потім переглядається весь рядок, де воно міститься.

Функція повертає значення зі стовпця з заданим номером у цьому рядку. Повний формат функції такий:

ВПР(шукане_значення;таблиця;номер_стовпця;точність_пошуку) 

Стисло опишемо призначення аргументів функції:

• шукане_значення — значення, яке потрібно знайти в першому стовпці;
• таблиця — вся таблиця;
• номер_стовпця — номер стовпця, значення з якого буде по­вернуто функцією;
• точність_пошуку — логічне значення, що визначає спосіб по­шуку У першому стовпці: пошук значення, яке точно дорівнює заданому (точність_пошуку= ЛОЖЬ), або такого, що дорівнює за­даному значенню наближено (точність_пошуку-ИСТИНА).

Як приклад розглянемо таблицю з відомостями про працівників певного підприємства (рис. 5.3).

Рис. 5.3. Обчислення дати народження працівника за його прізвищем

Користувач знає прізвища працівників і бажає за введеним у клі­тинку В8 прізвищем отримати у клітинці В9 інформацію про дату народження відповідного працівника. Для цього у клітинку В9 він має ввести таку формулу:

=ВПР(B8;Al:D6;3;ЛОЖЬ)

Тут В8 — адреса клітинки, в яку користувач вводить прізвище,

     A1:D6 — діапазон таблиці,

    3 — номер стовпця, який містить відо­мості про дату народження.

 Значення ЛОЖЬ  останнього аргументу означає, що в першому стовпці шукатиметься саме те значення, яке було введено у клітинку В8.

Якщо значення останнього аргументу дорівнюватиме ИСТИНА, то дату народження можна буде визначити навіть за умови, що прізвище ви знаєте лише приблизно, але таблиця у такому разі має бути відсортована в порядку зростання значень першого стовпця.

Обчислення узагальнюючих показників за довільним критерієм

Звичайно, далеко не завжди можна знайти об'єкти за ключовими значеннями. Зокрема, це не вдасться зробити, якщо вам знадо­биться, скажімо, дізнатися, який учень отримав максимальний бал з другої тематичної роботи (продовжимо розглядати прик­лад з класним журналом). Для виконання подібних операцій у Microsoft Excel використовують окрему категорію функцій — функції для роботи з базою даних.

Функція БИЗВЛЕЧЬ: простий критерій

Найпростіша із зазначених функцій, DGET (рос. БИЗВЛЕЧЬ), подібна до функції VLOOKUP(рос. ВПР), але дозволяє здійснювати пошук об'єктів за складним критерієм, а не лише за значенням у першому стовпці таблиці. Наведемо її формат:

БИЗВЛЕЧЬ{база_даних;поле;критерій)

Призначення аргументів функції таке:

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

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

Існують певні правила заповнення діапазону критерію. У його першому рядку записують назви стовпців, на значення яких на­кладають умови, у наступних рядках — самі умови. У найпро­стішому випадку діапазон критерію складається з двох клітинок, розташованих одна під одною: у верхній записують назву стовпця, а в нижній — умову, якій мають відповідати його значення.

Наведемо приклад, звернувшись до тієї самої таблиці з відомос­тями про працівників, яку ми розглядали в попередньому під­розділі. Припустимо, потрібно визначити прізвище за введеною користувачем посадою. У цьому разі діапазоном критерію будуть клітинки А8:А9 (рис. 5.4).

Рис. 5.4. Визначення прізвища за посадою

Тут функцію для визначення прізвища працівника введено в клі­тинку D8:

= БИЗВЛЕЧЬ(A1:D6;A1;A8:A9)

Опишемо аргументи цієї функції: A1:D6 — діапазон всієї таблиці; А1 — клітинка з назвою стовпця, з якого вибираються дані; А8:А9 — діапазон критерію.

Тепер, коли ми у клітинку А9 вводитимемо назву посади, у клі­тинці D8 автоматично визначатиметься прізвище працівника, який цю посаду обіймає. Проте якщо ввести посаду продавець, то у клітинці D8 програма виведе повідомлення про помилку #NUM!. Річ у тім, що продавців двоє, і тому функція БИЗВЛЕЧЬ не може визна­чити, прізвище якого саме продавця відображати. Таким чином, для коректної роботи функції БИЗВЛЕЧЬ необхідно, щоб критерію по­шуку задовольняв лише один рядок таблиці.

Функція БИЗВЛЕЧЬ: складений критерій

У критерії пошуку за допомогою логічних сполучників «і» та «або» можуть з'єднуватися умови, накладені на значення різних параме­трів. Умови зі сполучником «і» записуються в одному рядку критерію, а з'єднані сполучником «або» — у різних. Наприклад, ми можемо шукати прізвища тих працівників, які обіймають зазна­чену користувачем посаду і оклади яких перевищують 3400 грн. У такому разі критерій запишеться так, як показано на рис. 5.5, а. Якщо ж ми шукатимемо прізвища працівників, які обіймають певну посаду або оклади яких перевищують 3400 грн, то критерій буде таким, як показано на рис. 5.5, б.

Якщо розглядати дані, наведені на рис. 5.4, то за першим кри­терієм буде знайдено прізвище Лялецький, а за другим знову відобразиться повідомлення про помилку #NUM!, оскільки цьому критерію задовольняють усі працівники.

Можна створювати критерії, що відповідають умовам з кількома сполучниками «і» та «або». Наприклад, на рис. 5.6, а зображено діапазон критерію для вибору тих працівників, які є продавцями і відповідають принаймні одній із умов: мають оклад понад 3400 грн або народилися після 1 січня 1980 року, а на рис. 5.6, б — тих, які є продавцями або задовольняють двом умовам: мають оклад понад 3400 грн і народилися після 1 січня 1980 року.

Інші функції для роботи з базою даних

Функція БИЗВЛЕЧЬ знаходить значення деякого параметра об'єкта, який задовольняє певному критерію. Проте якщо таких об'єктів кілька, ця функція поверне помилку. Але є й інші функції з категорії Робота з базою даних, які дають змогу виконувати зі зна­ченнями того самого параметра кількох об'єктів різноманітні узагальнюючі операції: підсумовувати їх, рахувати кількість, об­числювати середнє арифметичне тощо. Найуживаніші з них такі:

• ДСРЗНАЧ — обчислення середнього арифме­тичного;
• БСЧЁТ — визначення кількості значень;
• ДМАКС — визначення максимального значення;
• ДМИН — визначення мінімального значення;
• БДСУММ — обчислення суми значень.

Ці функції мають такі самі параметри, як і функція БИЗВЛЕЧЬ. Під час їх обчислення вказані операції виконуються тільки для об'єктів, що відповідають критерію. Правила формування критерію анало­гічні правилам для функції БИЗВЛЕЧЬ. Приклади застосування пере­лічених функцій наведено на рис. 5.7.

Рис. 5.7. Застосування функцій для роботи з базами даних

Згідно з критерієм, записаним у клітинках А10:В11, відбирали­ся працівники, які народилися після 1 січня 1974 року та до

1 січня 1985 року. їх троє: Мотиль, Кузнецов і Лялецький (кіль­кість працівників обчислено у клітинці В14 за допомогою функції БСЧЁТ). Оклади цих працівників становлять 5500 грн, 6200 грн та 3500 грн. Саме за вказаними сумами обчислювалися середнє, максимальне, мінімальне та сумарне значення — вони відобра­жені у клітинках В15:В18. Функції, які при цьому використову­валися, зазначені у клітинках С14:С18.

4.  Практичне завдання.

Вправа 1. Створення запитів

У цій вправі ви маєте опрацювати таблицю з інформацією про зберігання овочів та фруктів на складах. За допомогою функцій табличного процесора необхідно знайти:

• середню вартість вибраного користувачем типу товару;
• номер складу, на якому залишок груш становить 100 кг;
• найвищу та найнижчу ціну 1 кг фруктів.

1. Відкрийте файл Bnpaвa_5_1.xls, Лист 3, як на рис. 5.8.

Рис. 5.8. Таблиця з вихідними даними

2. Для визначення середньої вартості товару в клітинки F1:F2 введіть критерій відбору, у клітинку F4 — текст Середня вар­тість 1 кг овочів, а у клітинку G4 — функцію ДСРЗНАЧ(база_ даних;поле;критерій) (рис. 5.9). Аргументи функції будуть та­кими:

• база даних — вся вихідна таблиця, тобто діапазон A1:D22;
• поле — заголовок того стовпця, за значеннями якого обчис­люватиметься середнє значення, тобто D1;
• критерій — діапазон клітинок, у яких записано критерій відбору об'єктів, тобто F1:F2.

3. У водячи у клітинку F2 різні назви овочів, пересвідчіться, що в клітинці G4 обчислюватимуться значення їх середньої вар­тості.
4. Для пошуку складу, на якому є залишок груш у кількості 100 кг, у клітинки F6:G7 введіть критерій відбору, а нижче, у клітинку G9, — функцію БИЗВЛЕЧЬ(DGET) (рис. 5.10). У даному випад­ку критерій можна сформулювати зі сполучником «і»: назва товару — груші і кількість — 100 кг. Тому умови критерію запишіть в одному рядку.

Рис. 5.10. Застосування функції БИЗВЛЕЧЬ

У результаті ви маєте отримати номер складу 1.

5. Фрукти — це ананаси, виноград, груші та яблука. Отже, для пошуку їх найнижчої та найвищої ціни критерій відбору можна сформулювати так: назва товару — це ананаси, або виноград, або груші, або яблука. Уведіть цей критерій у клі­тинки F11:F15, а нижче введіть функції DMAX та DM IN для обчислення максимальної та мінімальної ціни (рис. 5.11). Збережіть електронну книгу.

Рис. 5.11. Застосування функцій ДМАКС і ДМИН

6. Самостійно модифікуйте виконаний у п. 2 пошук середньої вартості товару так, щоб визначалася середня вартість двох зазначених користувачем товарів.

5.  Підсумки уроку.

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

1. Вивчити конспект.
2. Виконати завдання:

1. За таблицею з інформацією про працівників підприємства, яка наведена на рис. 5.3, а також у файлі Вправа_5_2.хІіs Лист_2, ви­значте кількість працівників, які мають оклад понад 4000 грн або для яких виконуються такі дві умови: дата народження після 1 січня 1982 року і оклад понад 3000 грн.
2. З'ясуйте призначення функції HLOOKUP (рос. ГПР) та створіть таблицю, за допомогою якої можна продемонструвати меха­нізм використання цієї функції.
3. Які значення потрібно ввести у порожні клітинки, обведені межею, щоб функції у клітинках А4 та А7 не видавали поми­лок (рис. 5.25)?

4. Припустимо, що в електронній книзі відомості про кожного працівника зберігаються на окремому аркуші, який назва­но його прізвищем. У клітинці А2 будь-якого з цих аркушів міститься дата народження працівника. Є, крім того, аркуш Працівники. Запишіть формулу, яка дозволятиме отримати у клітинці А2 цього аркуша дату народження працівника, прізвище якого введено у клітинку А1 (рис. 5.26). У формулі використайте функцію INDIRECT (рос. ДВССЫЛ), яка належить до категорії Посилання й масиви. Довідайтесь самостійно про її призначення і спосіб використання.