І етап ІОІТ-2017.
Практичний тур

Завантажити розв'язання

В цьому завданні потрібно було відобразити дані з текстового файлу відповідно до наступного зразка:

Word не підтримує задання розділювача рядків, а тому потрібно було спочатку здійснити заміну крапки з комою та символ абзацу ^p:

Після цього можна здійснювати перетворення тексту в таблицю за допомогою інструменту Вставка – Таблиця – Перетворити на таблицю…:

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

Завантажити розв'язання

В завданні пропонувалося відтворити анімацію прототипу емблеми Рівненської АЕС відповідно до поданого відеозразка.

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

Для атома слід було налаштувати анімацію «Шляхи переміщення» зі шляхом по побудованому раніше овалу.

Аби атом розміщувався перед літерою «А» та за літерою «Р» текст назви можна розділити на дві частини: «Р» та «АЕС», відповідно, і розмістити їх поряд аби розділення не було помітним. Тоді можна окремо задати позиціонування атома відносно однієї та іншої частини і досягти ефекту, поданого на відеозразку.

Завантажити розв'язання

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

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

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

Аби додати графічні позначення достатньо було скористатися відповідним набором значків умовного форматування (Головна – Стилі – Умовне форматування – Набори значків – Напрями – 3 трикутники).

Окрім вищеназваного, на цьому аркуші також потрібно було дати відповідь на певні питання щодо викладених на аркуші даних. Розглянемо кожне із запитань окремо.

Загальна кількість виготовленої електроенергії за два роки, млн кВт*год

В комірці H4 слід було подати суму значень у стовпцях C:D: =СУММ(C3:D46)

Загальна зміна кількості виготовленої електроенергії, млн кВт*год

В комірці H6 слід було подати суму значень стовпця E: =СУММ(E3:E46)

Для створення розкривного списку у комірці H8 слід було скористатися інстументом Дані – Перевірка даних – Перевірка даних, вказати в якості типу даних Список та виділити комірки C2:D2 (саме в них розташовані відповідні роки):

Загальна кількість виготовленої електроенергії за обраний рік тепловими електростанціями, млн кВт*год

В комірці H10 слід було скористатися умовним сумуванням відповідного стовпця з накладенням умови на значення стовпця Тип:

=ЕСЛИ(H8=2015;СУММЕСЛИМН(C:C;B:B;"теплова");СУММЕСЛИМН(D:D;B:B;"теплова"))

Відповідно, ми здійснюємо сумування за стовпцем C або D (відповідно до обраного року) у випадку, якщо значення стовпця B рівне «теплова».

Загальна кількість виготовленої електроенергії за обраний рік атомними електростанціями, млн кВт*год

Аналогічно до попереднього запитання, проте значення стовпця B повинне бути рівним «атомна»:

=ЕСЛИ(H8=2015;СУММЕСЛИМН(C:C;B:B;"атомна");СУММЕСЛИМН(D:D;B:B;"атомна"))

Загальна кількість виготовленої електроенергії за обраний рік іншими електростанціями, млн кВт*год

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

=ЕСЛИ(H8=2015;СУММ(C:C);СУММ(D:D))-H10-H13

Доля атомних електростанцій у виготовленні електроенергії (у відсотках)

Доля атомних електростанції у виготовленні електроенергії – це відношення електроенергії, виготовленої атомними електростанціями, до загальної кількості виготовленої електроенергії:

=H13/(H10+H13+H16)

Також комірці H19 слід задати відсотковий формат для відображення цієї частки у відсотках.

Окрім узагальнення даних аркуша Обсяги, у завданні вимагалось використовуючи дані аркушів Реактори та Електростанції на аркуші ІС АЕС створити інформаційну систему відповідно до наступного зразка:

Для створення розкривного списку у комірці C2 потрібно було знову звернутися до інструменту Дані – Перевірка даних – Перевірка даних, вказати в якості типу даних Список та виділити комірки B1:D1 аркуша Реактори – саме там розташовані назви реакторів.

Для внесення даних до комірок C3:C5 та C7:C8 слід скористатися функцією ГПР, яка здійснює пошук в першому рядку вказаного діапазону та повертає значення, розташоване в тому ж стовпці у вказаному рядку цього діапазону.

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

=ГПР(C2;Реактори!$B$1:$D$7;2;ЛОЖЬ)

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

Формули для решти з вищевказаних комірок пропонуємо записати самостійно.

Розкривний список для комірки C9 будується дещо складніше за попередні, оскільки діапазон значень, які потрібно розташувати в списку, не повинен містити порожніх комірок, а отже буде динамічним.

Підготуємо відповідні діапазони під кожним з реакторів на аркуші Реактори.

Початкова комірка діапазону завжди буде незмінною, тому в комірках B18:D18 вказуємо відповідні комірки рядка 8. Що стосується першої порожньої комірки, то вона буде зсунута відносно початкової на кількість електростанцій, що використовують відповідний реактор, а тому обчислимо останній заповнений рядок як 8 плюс кількість заповнених комірок у рядках 8-16 для кожного реактора та мінус 1 і внесемо для зручності ці значення в рядок 19. Наприклад, для реактора ВВЕР-440 ця формула матиме вигляд:

=СЧЁТЗ(B8:B16)+7

Насправді, її достатньо протягнути для трьох інших реакторів.

Тепер побудуємо посилання у форматі A1 для останньої комірки переліку електростанцій:

=АДРЕС(B19;СТОЛБЕЦ())

Після цього адресу діапазону визначити доволі просто за допомогою оператора конкатенації:

="Реактори!"&B18&":"&B20

Тепер на нашому аркуші в комірці C13 визначимо за допомогою тієї ж функції ГПР потрібний нам діапазон:

=ГПР($C$2;Реактори!$B$1:$D$21;21;ЛОЖЬ)

Але інструмент Перевірка даних в якості діапазону даних для рядків списку не сприймає рядок в якості параметра. Що ж нам вдіяти? Вірно! Перетворити рядок на адресу за допомогою функції ДВССЫЛ:

Таким чином, отримуємо розкривний список, який відповідає вимогам умови завдання.

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

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

Наприклад, для першого зображення формула буде наступною:

="Електростанції!"&АДРЕС(СТРОКА();3)

Можна також надрукувати ці адреси вручну, оскільки зображень невелика кількість.

Аналогічним чином, в комірці C14 за допомогою функції ГПР визначимо потрібну нам адресу комірки. Після цього створимо в Excel ім’я (Формули – Визначені імена – Задати ім’я), що міститиме посилання на відповідну комірку, утворене за допомогою функції ДВССЫЛ:

Тепер скопіюємо довільне зображення з аркуша Електростанції на аркуш ІС АЕС, відповідним чином маштабуємо та присвоюємо йому у рядку формул значення:

=зображення

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

Завантажити розв'язання

В завданні розглядалась база даних предметної області «Енергетика», схему якої можна представити у наступному вигляді:

База даних містить лише тривіальні зв’язки з множинністю «один до багатьох» за відповідними полями пов’язаних таблиць. Ці зв’язки і відображені на рисунку вище.

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

Відобразіть електростанції, фактична електрична потужність яких становить принаймні 1 ГВт (гігават).

Цей запит є тривіальним запитом на просту умову:

			SELECT Електростанції.Назва
			FROM Електростанції
			WHERE Електростанції.[Фактична електрична потужність] >= 1000;
		

Відобразіть обсяги виготовленої Хмельницькою АЕС електроенергії за кожен рік окремо.

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

Код запиту, що пропонується в якості ефективного розв’язання задачі, є наступним:

			SELECT [Обсяги виготовлення].Рік, [Обсяги виготовлення].Обсяг
			FROM Електростанції INNER JOIN [Обсяги виготовлення] ON Електростанції.Ідентифікатор=[Обсяги виготовлення].Електростанція
			WHERE Електростанції.Назва=”Хмельницька АЕС”;
		

Якщо вважати код Хмельницької АЕС відомим (50 у поданій заготовці), запит можна спростити:

			SELECT [Обсяги виготовлення].Рік, [Обсяги виготовлення].Обсяг
			FROM [Обсяги виготовлення]
			WHERE [Обсяги виготовлення].Електростанція=50;
		

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

Для введеної користувачем атомної електростанції (користувач вводить назву) відобразіть діючі в ній енергоблоки.

В цьому випадку позбутися операції об’єднання вже не вийде. Нам потрібно накласти умову на назву електростанції, що міститься в таблиці Електростанції, та відобразити дані про енергоблоки, що містяться в таблиці Енергоблоки АЕС.

Код запиту, який розв’язує поставлене завдання, є наступним:

			SELECT [Енергоблоки АЕС].*
			FROM [Енергоблоки АЕС] INNER JOIN Електростанції ON [Енергоблоки АЕС].Електростанція=Електростанції.Ідентифікатор
			WHERE (Електростанції.Назва = [Введіть назву електростанції]) AND ([Енергоблоки АЕС].[Введення в експлуатацію] IS NOT NULL) AND ([Енергоблоки АЕС].[Виведення з експлуатації] IS NULL);
		

Тут поле [Введіть назву електростанції] є параметром нашого запиту та призведе до відображення діалогового вікна в ході виконання з наступним змістом:

Відобразіть дані про гідроелектростанції (ГЕС), що розташовані на перетині двох річок.

Цей запит є значно складнішим за попередні. Спочатку слід сформувати пари річок, на яких розташовані ГЕС за допомогою операції об’єднання двох екземплярів однієї і тієї ж таблиці. Потім слід накласти умову рівності поля ГЕС, що позначає ідентифікатор електростанції, для річок в кожній з пар. На виході отримаємо окрім потрібних нам пар річок ще й пари, що містять двічі одну й ту ж річку. Аби прибрати повтори та цей недолік накладемо на назви річок додаткову умову: залишимо лише ті пари, в яких спочатку знаходиться менша за алфавітним порядком річка.

Загалом код запиту матиме наступний вигляд:

			SELECT [Річки ГЕС].ГЕС
			FROM [Річки ГЕС] INNER JOIN [Річки ГЕС] AS [Річки ГЕС_1] ON [Річки ГЕС].ГЕС=[Річки ГЕС_1].ГЕС
			WHERE [Річки ГЕС].Річка<[Річки ГЕС].Річка;
		

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

Цей запит є яскравим прикладом знаходження доповнення до множини об’єктів (або різниці множин). Ми володіємо даними про електростанції та легко можемо отримати дані про електростанції, про які в нас є дані щодо виготовленої ними електроенергії. Останнє легко записується наступним запитом:

			SELECT DISTINCT Електростанції.*
			FROM Електростанції INNER JOIN [Обсяги виготовлення] ON Електростанції.Ідентифікатор=[Обсяги виготовлення].Електростанція;
		

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

Частина популярних СКБД для таких випадків пропонує спеціальний оператор MINUS, який залишає тільки ті рядки першого запиту, які відсутні в другому. Проте Access до таких СКБД не відноситься і такий оператор в цьому середовищі відсутній.

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

Спосіб 1. Використання оператора NOT IN.

			SELECT Електростанції.*
			FROM Електростанції
			WHERE Електростанції.Ідентифікатор NOT IN (
			SELECT DISTINCT Електростанції.Ідентифікатор
			FROM Електростанції INNER JOIN [Обсяги виготовлення] ON Електростанції.Ідентифікатор=[Обсяги виготовлення].Електростанція;
			);
		

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

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

Більш оптимальний запит матиме наступний вигляд:

			SELECT Електростанції.*
			FROM Електростанції
			WHERE Електростанції.Ідентифікатор NOT IN (
			SELECT [Обсяги виготовлення].Електростанція
			FROM [Обсяги виготовлення];
			);
		

Спосіб 2. Використання зовнішнього об’єднання

Окрім внутрішнього об’єднання (INNER JOIN), яке передбачає об’єднання лише тих рядків обох таблиць, що мають відповідні їх записи іншої таблиці, зовнішнє (OUTER JOIN) об’єднання дозволяє включати до результату ще й ті об’єкти, які не мають відповідників.

Наприклад, у зв’язку між класами та учнями бази даних «Школа», внутнішнє об’єднання дозволяє отримати у вигляді однієї таблиці дані про учня та клас, в якому цей учень навчається. Але до цієї таблиці не потраплять дані про класи, що не містять учнів.

Зовнішні об’єднання в теорії реляційних баз даних поділяють на:

• ліве об’єднання, LEFT JOIN (обираються усі записи таблиці з лівого боку від LEFT JOIN та лише ті записи таблиці з правого боку, що мають відповідний їм запис в лівій таблиці);
• праве об’єднання, RIGHT JOIN (обираються усі записи таблиці з правого боку від RIGHT JOIN та лише ті записи таблиці з лівого боку, що мають відповідний їм запис в правій таблиці);
• повне об’єднання, FULL JOIN (обираються усі записи таблиць з правого та лівого боку від FULL JOIN та їм співставляються поєднані записи з іншої таблиці, якщо такі записи існують).

Слід зазначити, що для записів, що містять об’єкти без відповідника, на місці полів іншої таблиці знаходяться порожні значення (NULL).

Загалом, множини, які породжуються цими об’єднаннями, задовольняють наступним властивостям:

FULL JOIN = LEFT JOIN  RIGHT JOIN

INNER JOIN  LEFT JOIN

INNER JOIN  RIGHT JOIN

З використанням зовнішнього об’єднання (а саме LEFT JOIN) запит, який дає відповідь на поставлене запитання, можна написати наступним чином:

			SELECT DISTINCT Електростанції.*
			FROM Електростанції LEFT JOIN [Обсяги виготовлення] ON Електростанції.Ідентифікатор=[Обсяги виготовлення].Електростанція
			WHERE [Обсяги виготовлення].Електростанція IS NULL;
		

Слід зауважити, що в СКБД Access цей запит виконуватиметься значно швидше, аніж поданий у способі 1, якщо кількість даних буде достатньо великою.

Як можна задати тип об’єднання в режимі конструктора пропонуємо дослідити самостійно за допомогою мережі Інтернет.

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

1. Форму Електростанції для відображення даних про усі електростанції та обсяги виготовлення ними електроенергії відповідно до зразка:



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

2. Форму Річки з ГЕС для відображення переліку річок, на яких розташовані гідроелектростанції, відповідно до наступного зразка:



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

Запит легко будується за допомогою інструкції DISTINCT:

				SELECT DISTINCT [Річки ГЕС].Річка
				FROM [Річки ГЕС];
			

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