Внимание! Вы используете устаревшую версию браузера.
Для корректного отображения сайта настоятельно рекомендуем Вам установить более современную версию одного из браузеров, представленных справа. Это бесплатно и займет всего несколько минут.
Спробувати Оформити передплату
Спробувати Оформити передплату
ООО "ЛИГА ЗАКОН"
Функціональна краса: гарні й правильні моделі в Excel

Як створювати електронні таблиці Excel, які прості у використанні й тішать око.

За допомогою таблиць Excel фінансові менеджери можуть комунікувати зі слухачами й користувачами, демонструючи результати оцінки проектів, прогнози даних чи будь-який інший вид кількісного аналізу. Зверніть увагу, що ми використовуємо дієслово "комунікувати". Таблиці – це чудовий інструмент для комунікацій і виступів. Проте цей зв'язок часто затьмарюється поганими макетами, недостатньою або зайвою кількістю ярликів і невідповідностями, які спричинюють проблеми в розумінні мети й контенту таблиць у кінцевих користувачів. Ці проблеми можуть призвести до непотрібної додаткової роботи, ускладнити прийняття рішень топ-менеджерами або до неправильних висновків, що ґрунтуються на помилках у логіці та/або формулах, що не лежать на поверхні.

Тому необхідно поміркувати про розроблення дизайну й структури електронних таблиць, а не лише про формули, функції та формати, що використовуються. Про це ми й поговоримо у статті, де розглянемо основні принципи побудови якісних моделей в Excel. Розпочнемо з того, що гарна модель має чотири головні атрибути, що мають назву CRaFT (від англійського Consistency, Robustness, Flexibility, Transparency):

• послідовність;

• надійність;

• гнучкість;

• прозорість.

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

Отже, розпочнемо. І розпочнемо з порожньої робочої таблиці. Перше, що ми робимо, – це звужуємо перші кілька стовпців від стовпця A до стовпця E, так зручніше буде працювати з усією таблицею (виділити стовпці, потім клацнути правою кнопкою миші й обрати Ширину стовпця (Column Width) із випливного контекстного меню).

Обираємо ширину 3, тоді комірка буде квадратною.

Можна виділити іншу кількість стовпців або використовувати іншу ширину. Є два головних моменти:

• використовуйте стовпець А тільки для назви Заголовка таблиці. Чому? Розповімо далі;

• будьте послідовні й використовуйте однакову ширину для ідентичних стовпців на всіх робочих аркушах.

Даємо Заголовок таблиці в комірці A1. Він має бути таким самим, як в описі на вкладці аркуша. У цьому прикладі ми назвали його "Заголовок аркуша" (Sheet Title), щоб підкреслити призначення цих комірок, але без лапок:

Ось деякі рекомендації:

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

• з огляду на те, що Заголовок аркуша розміщено на аркуші, назву має бути написано формально й вона не повинна складатися із незрозумілої абревіатури;

• такий підхід відповідає узгодженості – одній із чотирьох ключових концепцій побудови найкращої моделі.

У комірці A2 пишемо назву моделі. Це важливо, оскільки у звітах і презентаціях PowerPoint ми часто даємо лише витяги із книг Excel. Тому зручно розміщувати назву моделі тут, а не у верхньому (Header) або нижньому колонтитулі (Footer) кожного робочого аркуша (як варіант, можна робити в обох місцях):

Якщо ви маєте намір додати назву у верхній або нижній колонтитул, ви можете використовувати комбінацію клавіш ALT + P + SP -> "Верхній/Нижній колонтитул" -> "Змінити колонтитул ..." (ALT + P + SP -> ‘Header / Footer’ tab -> ‘Custom Footer…’). Колонтитул зробити простіше, але в такому разі назва відображатиметься тільки під час друкування робочого аркуша. А що робити, якщо таблиця буде зображенням на слайді PowerPoint або, скажімо, додатком у документі Word? Ось чому зручно відображати назву моделі спереду і в центрі на робочих аркушах.

Наступний ключовий елемент у верхній частині таблиці – це навігаційне посилання. Робочі книги Excel можуть бути доволі складними, з багатьма робочими аркушами. У цьому випадку центральна навігаційна сторінка "Зміст" (Table of Contents) буде дуже доречною – це дасть змогу кінцевому користувачеві швидко й легко переміщатися книгою.

Здається, що в комірку A3 додали гіперпосилання, правильно? Насправді виділили комірки A3:F3, а потім об'єднали комірки за допомогою функції Злиття і Центр (Merge Across) (комбінація клавіш ALT + H + M + A):

Гіперпосилання можна легко створити за допомогою поєднання клавіш CTRL + K (або натисканням кнопки "Посилання" (Link) на вкладці "Вставка" (Insert) для посилання на місце у вашому документі. Головне – створити основний робочий аркуш зі змістом (Table of Contents), де містяться всі гіперпосилання на всі робочі аркуші:

Гіперпосилання має посилатися на комірку A1 цього робочого аркуша, і ця комірка повинна мати ім'я діапазону, наприклад HL_TOC. Важливо вказувати ім'я діапазону, аби уникнути непрацюючого посилання, якщо хтось перейменує аркуш призначення. HL означає, що комірка використовується як адресат для гіперпосилання. Комірки A3:F3 об'єднуються, тож якщо користувач натискає в будь-якому місці цього діапазону, гіперпосилання активується; інакше користувачеві доведеться натиснути саме на комірку A3 для запуску гіперпосилання.

Отже, ми підійшли до комірки A4:

Ми можемо додати тут формулу, щоб виводити будь-які перевірки помилок, що є в моделі. Зазвичай, аби помилки виводилися в одному місці на аркуші, дати й інші заголовки можна також указувати в цьому рядку:

У нашому прикладі стовпець G є стовпцем Units (одиниця виміру). У цьому стовпці ми вказуватимемо одиниці виміру, щоб користувачі могли розрізняти числові поля. Як часто вам потрапляла до рук роздруківка і ви не знали, дані зазначено в $ чи $000, чи m, чи кг? Запропонований підхід вирішує цю проблему. Слід зазначити, що цей стовпець не завжди потрібен. Як варіант, ви можете просто вказати у верхній частині аркуша: "Усі дані відображено в $K, якщо не зазначено інше".

Комірки J4:N4 містять заголовки дат. Дати мають бути періодичними (наприклад, щомісячно, щоквартально, щорічно) і завжди мають розпочинатися і закінчуватися в тих самих стовпцях (і рядках) на кожному аркуші робочої книги. Це не завжди можливо: іноді вам потрібно, щоб деякі з ваших даних прогнозувалися щорічно, а деякі – щомісячно. У таких випадках, коли необхідні періодичні звіти, ці неузгоджені робочі аркуші мають бути чітко виділені з інших областей робочої книги.

Ви, напевно, помітили, що між рядками 4 і 5 є лінія:

Це не намальована лінія. Це закріплена область (Freeze Pane). Закріплені панелі розбивають робочий аркуш на чотири сектори і дають змогу двом секторам аркуша залишатися завжди на очах ("заморожуватися"), поки користувач прокручує аркуш уздовж або поперек. На вкладці "Вид" (View) є три способи створення закріплених панелей:

"Закріпити верхній рядок" (Freeze Top Row): утримує верхній ряд видимим незалежно від того, як далеко вниз ви прокручуєте таблицю;

"Закріпити перший стовпець" (Freeze First Column): фіксує перший стовпець незалежно від того, як далеко праворуч ви прокручуєте таблицю;

"Закріпити область" (Freeze Panes): фіксує область у заданому місці перетину верхнього рядка і першого стовпця обраної комірки.

У нашому прикладі комірку A5 зроблено основою для закріпленої панелі, тож рядки з 1 по 4 завжди буде видно. Цій комірці має бути присвоєно назву діапазону, наприклад HL_Home, оскільки це комірка, до якої мають прив'язуватися гіперпосилання на цей аркуш. Додавши "заморожену" панель, ми спрощуємо навігацію серед моделі. Цю комірку може бути ідентифіковано за допомогою поєднання клавіш CTRL + HOME.

Зараз повернемося до того, чому ми звузили перші кілька стовпців. Отже, ми даємо нижче заголовки даних. Заголовки краще починати зі стовпця В, а не А. Таким чином ми створюємо видимий відступ. Спочатку вказуємо заголовок, нижче – підзаголовки.

Ми назвали їх "заголовками", "підзаголовками" тощо, щоб було зрозуміло. Але такий підхід швидко стане важким для сприйняття. Тому ми рекомендуємо називати заголовки й підзаголовки "Заголовок 1", "Заголовок 2" тощо. Це також робить їх сумісними з попередньо встановленими стилями заголовків в Excel.

Тепер, здається, зрозуміло, чому краще звужувати стовпці, A, B, C і D (і про всяк випадок зберігати стовпець E)? Звуження стовпців урізує заголовки і спрощує читання й переміщення робочими аркушами. Особливо, якщо сітку відключено.

Зверніть увагу на проміжок між назвами заголовків: один порожній ряд між заголовками; дві лінії між розділами. Можна обрати свій стиль, якщо маєте бажання, головне – бути послідовними. Це спрощує копіювання розділів і використання посилань, якщо проміжки однакові й передбачувані.

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

Додавання міток, даних і формул:

Зараз наш приклад уже більше схожий на електронну таблицю. Наступним кроком буде використання стилів (комбінація ALT + H + J), як показано нижче:

Тепер наша таблиця дуже швидко стає гарною:

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

Хочете переміщатися між основними заголовками в стовпці B? Натисніть на комірку B6, перейдіть CTRL + Стрілка вниз, і ви потрапите на комірку B18. Повторіть цю дію і наступна комірка, на яку ви потрапите, буде коміркою B1048576, тобто самою нижньою частиною таблиці, тому що в цьому стовпці після комірки B18 немає жодних даних.

Натисніть на комірку D10 (Заголовок 3) і використовуйте комбінацію клавіш CTRL + Стрілка вниз, щоб перейти до комірки D15, останньої комірки в суміжному діапазоні. Комбінації клавіш CTRL + Стрілка вгору, CTRL + Стрілка праворуч або CTRL + Стрілка ліворуч виконуватимуть аналогічні дії. Потрібно виділити діапазон? Натисніть будь-яку комірку в межах діапазону та натисніть комбінацію CTRL + A.

І останнє запитання на сьогодні: чому стовпчик А порожній? Це функціональний стовпчик для визначення "незакінченої роботи". Часто ми відриваємося від роботи, це може бути термінове питання від керівництва, дзвінок рідних, збори чи конференц-колл. Коли ми відволікаємося, необхідно запам'ятати, де ми зупинилися. Перш ніж відкласти роботу з таблицею, натисніть клавішу HOME, яка приведе вас до стовпця A того рядка, з яким ви працюєте в цей момент. Уведіть що-небудь у цій комірці, наприклад, "w" для "work-in-progress" або "с" для "check", будь-який символ чи літера підійдуть. І все, місце зупинення роботи зафіксовано.

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

За матеріалами: www.fm-magazine.com

_____________________________________________
© ТОВ "ІАЦ "ЛІГА", ТОВ "ЛІГА ЗАКОН", 2018

У разі цитування або іншого використання матеріалів, розміщених у цьому продукті ЛІГА:ЗАКОН, посилання на ЛІГА:ЗАКОН обов'язкове.
Повне або часткове відтворення чи тиражування будь-яким способом цих матеріалів без письмового дозволу ТОВ "ЛІГА ЗАКОН" заборонено.

Мати доступ до номерів і всіх статей видання Ви зможете за умови передплати на електронне видання Вісник МСФЗ
Контакти редакції:
ifrs@ligazakon.ua
 
Даний функціонал доступний передплатникам в електронному виданні.
Якщо Ви ще не передплачуєте видання, замовте безкоштовний доступ до демо-номера
або підпишіться на видання Вісник МСФЗ