ІІ етап ІОІТ-2016.
Розбір завдань

Задача потребує знання функцій ВПР, ЕСЛИ, статистичних, текстових функцій, функцій обробки помилок та роботи з датами, а також вміння роботи з умовним форматуванням та діаграмами.

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

Розпочнемо із заповнення аркуша Відвідувачі. Спочатку за допомогою функції ВПР визначимо тип номеру, в який заселений кожен відвідувач:

=ВПР(F2;Номери!$A$2:$B$34;2;FALSE)

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

=(1-ВПР($W2;Послуги!$A$6:$K$11;Послуги!B$2+1;FALSE))*Послуги!B$4

Маючи в розпорядженні дані про використання послуг відвідувачем та їх вартість для номеру, в якому цей відвідувач поселився, вартість як харчування, так і послуг рахується неважко:

=ЕСЛИОШИБКА(СУММПРОИЗВЕД(I2:K2;AE2:AG2);0)
=ЕСЛИОШИБКА(СУММПРОИЗВЕД(O2:U2;X2:AD2);0)

Кількість днів проживання обчислимо за допомогою функції РАЗНДАТ:

=РАЗНДАТ(G2;H2;"d")

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

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

=ЕСЛИ(СЧЕТЕСЛИ(Відвідувачі!F:F;Номери!A2)>0;1;0)

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

=$D2=1

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

=ВПР(B2;Типи_номерів!$B$2:$D$7;2;ЛОЖЬ)
=ВПР(B2;Типи_номерів!$B$2:$D$7;3;ЛОЖЬ)
=P2+Q2

Кількість відвідувачів визначимо як кількість заповнених рядків на аркуші Відвідувачі (без заголовку):

=СЧЕТЗ(Відвідувачі!A:A)-1

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

Для відмітки номерів готелю на схемі з аркуша План створимо нове правило умовного форматування з відповідною заливкою за умови, що цей номер відмічений зайнятим на аркуші Номери, при цьому скориставшись функцією ВПР:

=ВПР(B4;Номери!$A$2:$D$34;4;FALSE)=1

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

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

=ВПР(A2;Номери!$A$2:$B$34;2;ЛОЖЬ)
=СЧЕТЕСЛИ(Відвідувачі!F:F;A2)
=ВПР(G2;Типи_номерів!$B$2:$F$7;IF(H2>1;5;4);ЛОЖЬ)
=ВПР(A2;Відвідувачі!$F$2:$M$202;8;ЛОЖЬ)

Після цього обчислити вартість проживання легко:

=ЕСЛИОШИБКА(J2*I2;0)

Вартості проживання та послуг визначимо за допомогою функції СУММЕСЛИ, рахуватимемо суму відповідних значень для відповідного номеру кімнати:

=СУММЕСЛИ(Відвідувачі!$F$2:$F$202;'Вартість відпочинку'!A2;Відвідувачі!$V$2:$V$202)
=СУММЕСЛИ(Відвідувачі!$F$2:$F$202;'Вартість відпочинку'!A2;Відвідувачі!$N$2:$N$202)

Після цього підрахуємо загальну суму усіх обрахованих значень:

=СУММ(B2:D2)

Тепер залишається відобразити відповідно до зразка лише зайняті кімнати готелю. Для цього знов перейдемо на аркуш Номери. У окремому стовпці (наприклад, F) відобразимо номер кімнати, якщо ця кімната зайнята, а якщо ні - число 500 (завідомо більше за будь-який номер кімнати). Тоді на аркуші Вартість відпочинку достатньо вивести номери кімнат з цього стовпця за їх зростанням, а число 500 замінити на порожній рядок:

=ЕСЛИ(НАИМЕНЬШИЙ(Номери!$F$2:$F$34;СТРОКА()-1)=500;"";НАИМЕНЬШИЙ(Номери!$F$2:$F$34;СТРОКА()-1))

Тут функція НАИМЕНЬШИЙ відповідає за сортування номерів у порядку зростання: обирається СТРОКА()-1 за порядком зростання номер кімнати (тобто спочатку перший, потім - другий і так далі).

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

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

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

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

SUM(ABOVE)
SUM(C:C)
SUM(C3:C53)

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

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

Задача потребує навички побудови схеми даних, експорту даних, створення простих та складених запитів на вибірку в середовищі конструктора та мовою SQL, а також форм та макросів, вміння роботи зі вбудованою функцією IIf.

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

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

Поле Регіон таблиці Населені пункти слід перетворити в поле підстановки та обмежити в параметрах вибір лише елементами зі списку та заборонити його редагування.

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

Форми Користувачі та Населені пункти слід створювати за допомогою інструмента автоформ. На подію клацання на відповідну кнопку потрібно встановити макрокоманду ОткрытьФорму з форматом даних додавання.

Перейдемо до основної частини - створення форми Діалоги. Після додавання двох полів зі списком з відповідними джерелами рядків одержимо усі повідомлення діалогу:

			SELECT Повідомлення.Відправник, Повідомлення.Одержувач, Повідомлення.[Текст повідомлення], Повідомлення.Відправлено
			FROM Повідомлення
			WHERE (Повідомлення.Відправник=[Forms]![Діалоги]![sender] AND Повідомлення.Одержувач=[Forms]![Діалоги]![receiver]) OR (Повідомлення.Відправник=[Forms]![Діалоги]![receiver] AND Повідомлення.Одержувач=[Forms]![Діалоги]![sender]);
		

Або одержимо окремо повідомлення в різні боки та об'єднумо результати:

Діалоги прямий бік

			SELECT Повідомлення.Відправник, Повідомлення.Одержувач, Повідомлення.[Текст повідомлення], Повідомлення.Відправлено
			FROM Повідомлення
			WHERE Повідомлення.Відправник=[Forms]![Діалоги]![sender] AND Повідомлення.Одержувач=[Forms]![Діалоги]![receiver];
		

Діалоги зворотній бік

			SELECT Повідомлення.Відправник, Повідомлення.Одержувач, Повідомлення.[Текст повідомлення], Повідомлення.Відправлено
			FROM Повідомлення
			WHERE Повідомлення.Відправник=[Forms]![Діалоги]![receiver] AND Повідомлення.Одержувач=[Forms]![Діалоги]![sender];
		

Всі діалоги між обраними користувачами

			SELECT * FROM [Діалоги зворотній бік]
			UNION SELECT * FROM [Діалоги прямий бік];
		

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

Діалог по різні боки

			SELECT IIf([Відправник]=[Forms]![Діалоги]![sender],"",[Текст повідомлення]) AS ПовідомленняЗліва, 
			IIf([Відправник]=[Forms]![Діалоги]![sender],"",[Відправлено]) AS ДатаЗліва, 
			IIf([Відправник]<>[Forms]![Діалоги]![sender],"",[Текст повідомлення]) AS ПовідомленняСправа, 
			IIf([Відправник]<>[Forms]![Діалоги]![sender],"",[Відправлено]) AS ДатаСправа
			FROM [Діалоги між обраними за датою];
		

Тепер достатньо розмістити поля відповідним чином на стрічковій формі, побудованій на основі запиту Діалог по різні боки:

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

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

Відправка повідомлення

			INSERT INTO Повідомлення ( Відправник, Одержувач, [Текст повідомлення], Відправлено )
			SELECT [Forms]![Діалоги]![sender] AS Expr2, [Forms]![Діалоги]![receiver] AS Expr3, [Forms]![Діалоги]![message] AS Expr4, Now() AS Expr1;
		

Задача потребує навички роботи із зображеннями, анімаційними ефектами та їх параметрами, а також діями при наведенні на об'єкт.

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

Суть завдання полягає у створенні трьох слайдів з трьома напрямами руху снігопаду. Поверх усіх об'єктів розміщуються три прозорі прямокутники, при наведенні на які відбувається дія (Вставка-Действие) переходу на інший слайд.

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