На четвертому майстер-класі курсу “Легендарний EXCEL” ми розбиралися із інструментами візуального аналізу великих таблиць. Необхідність цих інструментів стає очевидною, коли ми маємо справу з таблицями настільки великими, що при нормальному перегляді вони не поміщаються на екрані і нам доводиться користуватися скролінгом для перетягування таблиці в необхідну для перегляду область. Коли ж ми зменшуємо масштаб перегляду, густина клітинок збільшується настільки, що неможливо щось розгледіти. В такому разі стануть в нагоді інструменти, що дозволяють “підсівтити” (тобто виділити контрасним кольором) шукані клітинки.

​На початку заняття ми вирішили проблему переводу комп’ютера на українські регіональні налаштування. Тепер грошовий формат даних по замовчуванню буде відображатись гривнями. Для цього в Налаштуваннях Windows потрібно послідовно обрати пункти Час і мова -> Мова і регіон. У випадаючому списку Регіональний формат обираємо Українська (Україна)

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

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

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

_____________________________________________________
​Далі ми розібрали цікаву вправу для демонстрації функцій роботи з масивами, а саме СТОЛБЕЦ (COLUMN)та СТРОКА(ROW). Вони дозволять нам визначити номер стовбчика та рядочка на перехресті яких лежить вказана клітинка.

​Для тих, хто погано пам’ятає шкільний курс математики дану вправу можна пропустити :)

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

​= СТОЛБЕЦ($О$14) - СТОЛБЕЦ (Адреса шуканої клітинки)

Зверніть увагу, що адреса червоної клітинки абсолютна. 

Подібно до цього, можна визначити відстань до вертикальних сусідів (тобто, клітинок розміщених вздовж того ж стовбця що і червона) завдяки формулі

​= СТРОКА($О$14) - СТРОКА(Адреса шуканої клітинки)

​Але для визначення відстані до клітинок, що знаходяться по діагоналі потрібно буде застосувати формулу Піфагора.

​Переведемо її в EXCEL формат.
​

= КОРЕНЬ ( СТЕПЕНЬ ( СТОЛБЕЦ($О$14) - СТОЛБЕЦ (Адреса шуканої клітинки); 2) + СТЕПЕНЬ ( СТРОКА($О$14) - СТРОКА(Адреса шуканої клітинки); 2))

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

___________________________________________________________

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

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

Спочатку давайте створимо рядок з 366 дат 2023-2024 навчального року. Для цього в якусь клітинку в лівому боці нової таблиці вносимо дату 01.09.2023 та розтягуємо її з нарощенням дати праворуч в рядок. Таким чином, доходимо до 31.08.2024

Відформатуємо отримані клітинки дат так, щоб дата відображалася лише за допомогою числа, що включає по дві цифри. Тобто 01.09.2023 перетвориться в 01 а 31.08.2024 в 31. Для цього використаємо шаблони підпункту Всі формати вікна Форматування клітинок.

Далі над кожним місяцем об’єднаємо клітинки та добавимо назви цих місяців. (При цьому можна в першу об’єднану клітинку над вересневими числами вписати слово ВЕРЕСЕНЬ та протягнути її праворуч, при цьому місяці автоматично перерахуються та повставляються у відповідні об’єднані клітинки.)

Тепер створимо під числами новий рядок пустих клітинок та повставляємо в них номер дня неділі. Для цього використаємо формулу

=ДЕНЬНЕД(D6;3)

Де D6 - адреса клітинки, в якій стоїть дата (та що вже відображається числом), а 3 - параметр, що вказує порядок підрахунку днів тижня. В нашому випадку, дні тижня будуть рахуватися від понеділка - 0 до неділі - 6. (До речі, таких порядків є багато, вони можуть бути різними в залежності від регіональних та національних вподобань. Наприклад, американці першим днем тижня вважають неділю). Як бачите, П’ятниця - 01.09.2023 року відповідає номер 04, Суботі - 05, Неділі - 06, Понеділку - 01 і т.д.

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

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

В нашому випадку, діапазон D9:D12 буде зафарбовано блакитним кольором, якщо поле зверху міститиме стрічку “СБ”. Після протягнення даного діапазону праворуч по всій таблиці, всі суботи будуть виділені.

Таким самим чином виділяємо всі клітинки під позначкою “НД”.

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

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

Тепер залишається скопіювати їх помісячно у WORD, дооформити  та сконвертувати в PDF для роздруку та збереження. Нижче зображено вигляд PDF документа (в ньому три сторінки з трьома графіками, на малюнку першbq)

_________________________________________________________________________

Домашнє завдання :

• на свому матеріалі створити подібну таблицю в EXCEL (можна обмежитись трьома місяцями вересень, жовтень, листопад 2023 року,
• скопіювати отримані таблиці помісячно в WORD,
• дооформити їх шапкою та місцем для затвердження керівникоv,
• отриманий електронний WORD-документ сконвертувати в PDF

Завдання здаємо у вигляді файлу *.xlsx (формат EXCEL 2007 і більш пізніших версій)  та PDF файлу з трьох стрінок

Файли присилаємо на пошту metiska64@gmail.com

---------------------------------------------------------------------------------------------------------------

Нижче ви зможете побачити покликання на відеозапис заняття.