Задача «Слідчий експеримент» (MS Excel)

Грабіжник залишає для поліції перед пограбуванням кожного банку дивні записки. Всі записки містять однаковий набір букв, але кожного разу іншу послідовность чисел. Останнім був пограбований банк "Перший". Виділивши букви з назви банку в записці, слідчі помітили, що якщо замінити виділені букви на 1, а не виділені - на 0, то
кожен рядок буде відповідати одному числу з послідовності, представленому в двійковому запису. Учасник олімпіади повинен допомогти слідчим змоделювати систему, яка після введення послідовності чисел з наступної записки повертає в комірку В14 назву банку, який збирається пограбувати грабіжник (мал.1).
Учаснику дана таблиця букв 8х8, два шифра (один у прикладі, один для рішення) і принцип роботи. Учасник повинен автоматизувати виділення потрібних літер в таблиці за допомогою умовного форматування та отримання назви банку в комірці В14 відповідної виділеним літерам.
Шифр для рішення (наступний банк) 4 32 18 77 42 18 64 24

(мал.1)
Посилання для скачування умови задачі>>

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

• Формули по всім клітинкам розмножуємо за допомогою інструменту автозаповнення.
• Під час розмноження формул не забуваємо про відносну та абсолютну адресацію.
• Для розбору матриць часто треба будувати допоміжні матриці (будемо їх називати матрицями-масками).
• Для форматування матриць найкраще використовувати умовне форматування (його легко розмножити за допомогою інструменту "Формат по образцу" )

В нашій задачі ми також маємо матрицю 8х8 клітинок з адресою B3:I10. Але до чого тут загадковий шифр зловмисника, що складається з 8 чисел?
Уважно перечитавши умову, та вивчивши наданий малюнок, я виявив, що якщо порядково зверху до низу передивитсь нашу матрицю та виписати зі всіх виділених клітинок вміст то отримаємо фразу "банк перший" (зверніть увагу що пропуск також враховано).
Тепер спробуємо виконати діїї описані у нижче процитованому фрагменті умови

"Виділивши букви з назви банку в записці, .... замінити виділені букви на 1, а не виділені - на 0...."

Для цього створимо матрицю-маску 8х8 клітинок та повиствляємо нулики та одинички на  відповідні місця (див. мал 2.)

(мал.2)

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

"Виділивши букви з назви банку в записці, слідчі помітили, що якщо замінити виділені букви на 1, а не виділені - на 0, то кожен рядок буде відповідати одному числу з послідовності, представленому в двійковому запису."

Спробуємо виписати числа з кожного рядочка  маски і отримаємо двійкові числа. (див мал.3)

(мал.3)

Переведемо двійкові числа в десяткові та отримаємо наш "таємний шифр".

00000000 (2) = 0 (10)

00000110 (2) = 6 (10)

01000010 (2) = 66 (10)

00000000 (2) = 0 (10)

00000101 (2) = 5 (10)

01000100 (2) = 68 (10)

00001001 (2) = 9 (10)

00000001 (2) = 1 (10)

Тільки тепер стає зрозумілою тактика розв'язку задачі. Робимо все навпаки!

1. Переведемо новий шифр з 8 десяткових чисел в вісьми цифрові двійкові числа
2. Згідно цифр в знайдених двійкових числах створимо матрицю-маску з 0 та 1
3. Згідно маски виділяємо кольором  у вихідній матриці відповідні клітинки
4. Згідно маски порядково  виписуємо виділені символи
5. З'єднуємо символи в слова та читаємо назву нового банку що його будуть грабувати зловмисники (який жах!)

Що ж почнемо!
Випишемо новий шифр в стовбчик та спробуємо кожне з чисел перевести в двійкове вісьми цифрове число. Для цього в EXCEL є потужний набір формул з розділу "Инженерные" для переводу чисел в інші системи числення. наведемо їх всі:

восьм.в.дв (число, [разрядность])
восьм.в.дес (число, [разрядность])
восьм.в.шестн (число, [разрядность])

дес.в.дв (число, [разрядность])
дес.в.восьм (число, [разрядность])
дес.в.шестн (число, [разрядность])

дв.в.восьм (число, [разрядность])
дв.в.дес (число, [разрядность])
дв.в.шестн (число, [разрядность])

Що вони означають, і як їх використовувати, сподіваюсь, вам не треба пояснювати. Все зрозуміло з самої назви. Лише залишилось вияснити, що означає аргумент [разрядность].
За довідкою це кількість цифр в результуючому числі. А нам на виході потрібне восьмирозрядне двійкове число (наприклад двійкове число 110 повинне виглядати як 00000110). Тому спокійно використовуємо для кожного числа функцію дес.в.дв (число, 8) і отримуємо необхідний набір двійкових чисел.

(мал.4)

Ой! Ледь не забув! А як ми отримали стовбчик "Масив транспорт" де виписані всі числа з нового шифру. Можна, звісно, це зробити і від руки, але це "не кошерно". Ми ж програмісти!
Автоматизуємо операцію транспортування (переведення діапазону стрічки в діапазон - стовбчик і навпаки). Транспортування - термін з теорії матриць.

В EXCEL є функція =ТРАНСП(діапазон). Працює вона так.  Спочатку стаємо в ту клітинку де буде починатись наш результуючий транспонований діапазон. Вписуємо функцію =ТРАНСП(діапазон).
На місце аргументу діапазон вписуємо адресу діапазону - джерела. В нашому випадку (див. мал. 2) ми повинні внести адресу B12:I12. Тиснемо Enter і Упс!!! Нічого не вийшло!!!

Тут є нюанс. Читаємо довідку

Важно.  Чтобы функция ТРАНСП работала правильно, формулу ... необходимо ввести как формулу массива. ... выделите целевой диапазон ..., начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затем — сочетание клавиш CTRL + SHIFT + ВВОД. Если формула не будет введена как формула массива, единственное значение будет равно 1.

На мій погляд, це дуже незручно. (На відміну від табличного редактора Google Sheet - де для цього існує простенька функція TRANSPOSE (див.мал 5))

(мал.5)

Повертаємось до нашого EXCEL. Робимо так як сказано  вище і все виходить. (Ну не з першого разу, звісно!)

Продовжимо нашу задачу. 

Маємо стовбчик двійкових вісьмирозрядних чисел. ЧИСЕЛ. А нам треба просто набір з восьми символів  для того щоб потім їх розкидати по клітинкам маски. Ну це просто! Є спеціальна функція  =ТЕКСТ(число, формат). Особливо не буду пояснювати, почитаєте довідку саменькі. Застосуємо її і отримуємо необхідні набори символів (див.мал.6)

(мал.6)

А от тепер розкидаємо ці набори символів посимвольно в нашу матрицю-маску. Використаємо функцію =ПСТР(стрічка;номер_першого_символа;к-сть_символів). Пояснювати цю функцію знову не буду, почитаєте саменькі. Але підскажу, що номер_першого_символа  треба звідкись взяти. Тому введемо додатковий рядок де і вкажемо цей номер. Важко? Думаю, що з малюнка 7 все стане зрозумілим.

(мал.7)

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

Далі для зручності я виготовив ще одну маску де перевів символи нуликів в логічне значення ЛОЖЬ, а одинички в ИСТИНУ. Див. мал.8

(мал.8)

Ви помітили, що я знову застосував умовне форматування до нової маски. Так що схема зафарбовування скопійована. 

Тепер вже можна виділити у вихідній матриці відповідні клітинки. Знову застосуємо умовне форматування. Але тепер використаємо правило "форматування за формулою". Опишемо подальші кроки детальніше.

• Очистимо вихідну матрицю від всякого формативання
• Виділяємо верхню-ліву клітинку вихідної матриці. 
• Через кнопку "Условное форматирование" виходимо на діалогове вікно "Диспетчер правил условного форматирования" (зрозуміло, що правило буде застосовуватись лише для виділеної клітинки назвемо її цілевою) див.мал.9

(мал.9)

• У вікні "Диспетчер правил условного форматирования" тиснемо на кнопку "Создать правило...". З'являється вікно "Создать правило форматирования"
• В ньому вибираємо пункт "Использовать формулу для определения форматируемых ячеек".
• В поле "Форматировать значения, для которых следующая формула является истинной." вводимо адресу верхньої лівої клітинки останньої маски. Назвемо її задаючою.

На цьому кроці спинимось та побачимо як все працює. Якщо введена формула дасть значення ИСТИНА то відбудеться акт форматування цілевої клітинки. Наша формула буде мати вигляд =задаюча. Зверніть увагу! В останній матриці-масці кожна клітинка містить лише ззначення ЛОЖЬ або ИСТИНА. Якщо задаюча = ЛОЖЬ форматування не буде. Якщо задаюча = ИСТИНА - форматування відбудеться. Див.мал.10.

(мал.10)

Особливо зверніть увагу на поле Адреса задаючої клітинки. Там за умовчанням виставиться =$X$3, а потрібно =X3.  Це потрібно для того, щоб при розмноженні умовного форматування від цілевої клітинки на всю вихідну матрицю кожна клітинка знайшла собі відповідника в масці.

• Розмножуємо параметри форматування цілевої клітинки на всю вихідну матрицю за допомогою інструменту "Формат по образцу". Або його ще називають "віник"
  

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

(мал.11)

Зосередимось на другій частині задачі - "Учасник повинен автоматизувати ... отримання назви банку в комірці В14 відповідної виділеним літерам."

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

ПРОСТО в верхню-ліву клітинку символьної маски вставляємо формулу  

=ЕСЛИ(X3=ИСТИНА;ЕСЛИ(B3="";" ";B3);"") 

та розмножуємо її методом автозаповнення по всій масці.

Розберемо формулу детальніше. Ми вставляємо в клітинку формулу яка згідно із значенням   відповідної клітинки в масці ИСТИНА-ЛОЖЬ або бере або не бере значення відповідної клітинки з вихідної матриці. Може достатньо записати таку формулу ?

=ЕСЛИ(X3=ИСТИНА;B3;"")

Але є ще клітинки які відображають пропуск між словами. На вихідній матриці вони зображаються пустими клітинками, а нам потрібно прочитати невидимий знак пропуску " ". Тому і потрібно добавити цю умову ЕСЛИ(B3="";" ";B3)

І як завжди в підкріплення сказаного малюнок 12

(мал.12)

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

(мал.13)

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

(мал. 14)

Завдання ІІ етапу олімпіади з ІТ 2015-2016 н.р. люб'язно надані М.М.Кузічевим, керівником центру інформаційних технологій управління освіти та науки Дніпропетровської міської ради.

За посиланням ви зможете скачати EXCEL - файл розв'язок.     Посилання>>>