Календарний план в Excel
 | За останній час до редакції надійшло декілька листів від працівників аудиторських компаній. Запитання були різні, чимало з них мали суто спеціальний характер. Але одна проблема, на мій погляд, могла б зацікавити досить широке коло наших читачів. Ідеться про розробку календарного плану. Про те, як побудувати такий план у програмі Excel і де зможе застосувати його практикуючий бухгалтер, поговоримо в цій статті. Микола КАРПЕНКО , канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Календарний план — це послідовність робіт, побудована у часі в порядку їх виконання. Для побудови плану потрібно знати перелік робіт, тривалість їх виконання, а також взаємозв’язки між початком та закінченням кожної роботи. Ці взаємозв’язки випливають із самої суті завдання. Наприклад, щоб звести стіни, потрібно попередньо закласти фундамент, перед видачею зарплати потрібно заплатити податки тощо. Календарний план можна зобразити по-різному. Зокрема, у табличній формі. Для цього в одній колонці можна скласти перелік робіт, а поруч навести строки їх виконання. Така таблиця дає вичерпну інформацію про складений план. Але користуватися нею незручно: щоб зрозуміти послідовність дій, доведеться кожного разу аналізувати дати. А це і довго, і втомливо. Тому на практиці календарні графіки прийнято зображати у вигляді так званих діаграм Ганта. Такий спосіб уперше запропонував американський інженер Генрі Лоренс Гант (Henry Laurence Gantt, 1861 — 1919). На діаграмі Ганта роботи показано у вигляді відрізків. Довжина відрізку пропорційна тривалості роботи, а самі відрізки розташовані вздовж осі часу в хронологічному порядку. Таке зображення дуже наочне, ним зручно користуватися на практиці. Саме завдяки цьому діаграми Ганта залишаються основним способом зображення календарних графіків з початку минулого століття і до наших днів.
Ідея, думаю, зрозуміла, але як застосувати її на практиці? Де можуть бути корисними діаграми Ганта? Відповідь — практично скрізь, коли йдеться про аналіз чи опис динамічних процесів. Ось тільки кілька прикладів. Робоча документація аудитора, підготовлена згідно з міжнародними стандартами (МСА 230 «Документація»), крім усього іншого, повинна містити:
1) план аудиторської перевірки;
2) характер, час та обсяг виконаних аудиторських процедур.
Уключити до зазначених документів діаграми Ганта буде зовсім не зайвим… Бухгалтер може скористатися календарними графіками для аналізу руху грошових коштів. У цьому випадку діаграма Ганта покаже точні дати майбутніх надходжень та обов’язкових платежів. До речі, такий підхід — заманлива та досить перспективна альтернатива досить неповороткому процесу бюджетування. Отже, застосувань більш ніж достатньо. Залишається запитання: як побудувати календарний графік?
Узагалі-то для створення діаграм Ганта є спеціальні програми. Це насамперед MS Project, MS Visio, Spider, Business Project та інші. Усі вони популярні та по-своєму цікаві. Але для наших цілей багаті можливості цих програмних продуктів просто не потрібні. Тим більше, що за ці можливості потрібно заплатити, і заплатити чимало. Ми застосуємо для вирішення завдання «старий добрий» MS Excel. Причому зробимо це двома способами — з використанням умовного форматування та набору стандартних діаграм.
Діаграма Ганта та умовне форматування
Насамперед чітко сформулюємо наше завдання. Є перелік робіт. Відомі дати початку та тривалість виконання кожної роботи. Наша мета — за цими даними намалювати діаграму Ганта, де всі роботи буде зображено горизонтальними відрізками вздовж осі часу. І зараз ми подивимося, як можна цього досягти за допомогою умовного форматування. У загальних рисах послідовність наших дій буде такою.
Спочатку створимо таблицю, де в окремих колонках буде записано назви робіт, а також строки їх виконання. У правій частині таблиці ми організуємо групу робочих комірок. Кожна колонка в цій частині таблиці відповідатиме певній даті, причому дати від колонки до колонки змінюватимуться з кроком в один день. Додаючи колонки в робочу область, ми зробимо так, щоб дати охоплювали весь горизонт нашого планування. Потім робочим коміркам у правій частині таблиці присвоїмо умовний формат. Як умовою для цього формату скористаємося формулою, яка визначить: потрапляє дата поточної колонки в період виконання роботи чи ні. Якщо умова буде істинна, то у відповідній комірці ми змінимо колір фону. У результаті такого форматування всієї групи робочих комірок у ній з’явиться зображення діаграми Ганта.
Почнемо з підготовки даних. Робимо так.
1. Відкриваємо документ Excel. До комірок «
A1:D1» пишемо заголовки таблиці, як наведено на рис. 1. 
2. До комірок «A2:A17» вводимо найменування етапів робіт.
3. До блоку «B2:B17» вводимо дати початку кожної роботи, до блоку «С2:С17» — тривалість їх виконання (рис. 1).
4. До комірки «D2» вводимо формулу «=B2+C2-1». Вона визначає дату закінчення роботи (до дати початку роботи додає тривалість її виконання).
5. Копіюємо формулу по колонці «D» на всю висоту таблиці.
6. До комірки «E1» вводимо формулу «=B2-1». Це дата початку найпершої роботи мінус один день.
7. У комірці «F1» розрахуємо наступну календарну дату за формулою «=E1+1». Цю формулу копіюємо вправо по рядку до колонки «AI» — на весь місяць. Форма таблиці готова.
Приступимо до умовного форматування. Спочатку створимо формулу та перевіримо її працездатність. Стаємо на комірку «E2». У колонці «A» за другим рядком записана робота «Исследование специфики предприятия». Дата початку цієї роботи «01.09.11», дата закінчення — «04.09.11». У заголовку колонки «E» зазначено дату «31.08.11». Формула повинна перевірити, належить ця дата проміжку з «01.09.11» по «04.09.11» чи ні. Якщо належить — формула поверне значення «Истина». В іншому разі значенням формули буде «Ложь». Щоб організувати таку перевірку, нам знадобиться функція «И()» зі стандартної бібліотеки MS Excel. Як параметри цієї функції потрібно вказати декілька логічних виразів. Якщо всі ці вирази істинні, результат роботи функції буде «Истина». Якщо хоча б одна умова не виконується — результатом функції буде «Ложь». У нашому конкретному випадку формула в комірці «E2» виглядатиме так: «=И(E$1>=$B2;E$1<=$D2)». Ось як вона працює. Перший параметр «E$1>=$B2» порівнює дату початку роботи «Исследование специфики предприятия» з датою в заголовку колонки «E», тобто вона порівнює «01.09.11» з «31.08.11». Результатом порівняння буде «Ложь», адже «31.08.11» не більше «01.09.11». Цього достатньо, щоб результат функції «И()» набув значення «Ложь», оскільки один із її параметрів вже повернув таке значення. Проте розберемося і з другим параметром функції. Він виглядає так: «E$1<=$D2». Ця формула порівнює дату закінчення роботи «Исследование специфики предприятия» з датою в заголовку колонки «E». Якщо дата в заголовку буде більше дати закінчення роботи, результатом перевірки буде «Ложь». Отже, вираз «=И(E$1>=$B2;E$1<=$D2)» перевіряє, чи належить дата в «E$1» інтервалу виконання роботи в поточному рядку. Оскільки ми копіюватимемо формулу вниз та вправо по таблиці, перед відповідними елементами ставимо символи абсолютної адресації «$». Записавши формулу в комірку «E1», копіюємо її на весь діапазон таблиці (блок «E2:AI17»). Результат нашої роботи наведено на рис. 2.
У цілому завдання вирішене. У тих комірках, де передбачається виконання робіт, Excel проставив значення «Истина». У решті комірок знаходиться значення «Ложь». Наприклад, роботу «Исследование специфики предприятия» ми запланували на період з «01.09.11» по «04.09.11». Під цю умову потрапляють колонки «F:I». Відповідно в комірках «F2:I2» формула повернула значення «Истина». У решті комірок за другим рядком знаходяться значення «Ложь». Тепер наше завдання — перенести формулу в параметри умовного форматування. Робимо так.
1. Стаємо на комірку «E2».
2. Натискуємо клавішу «F2» (входимо в режим редагування комірки).
3. Виділяємо текст формули та копіюємо його в буфер обміну («Ctrl+C»).
4. Натискуємо клавішу «Esc» (виходимо з режиму редагування комірки та повертаємося на робочий лист).
5. Залишаючись на комірці «E2», викликаємо меню «Формат → Условное форматирование». Відкриється вікно, як наведено на рис. 3.
6. Клацаємо по значку випадного списку «Условие 1», вибираємо варіант «формула».
7. Клацаємо мишкою по полю для параметра «Условие 1» та натискуємо «Ctrl+C» (вставляємо текст формули із буфера обміну). Вийде результат, як наведено на рис. 3.
Порада При роботі з формулами в умовному форматуванні відмініть стиль посилань «R1C1». Для цього ввійдіть до меню «Сервис → Параметры», закладка «Общие», та відключіть прапорець «стиль ссылок R1C1».
8. У вікні «
Условное форматирование» натискуємо кнопку «Формат». Відкриється вікно форматування комірок, як наведено на рис. 4. 
9. Переходимо на закладку «Вид», на ній вибираємо параметри кольору або узору для комірок з умовним форматуванням. У прикладі на рис. 4 для зображення відрізків діаграми зазначена заливка узором у вигляді скісного штрихування.
10. У вікні «Формат ячеек» натискуємо «ОК».
11. У вікні «Условное форматирование» натискуємо «ОК».
12. Залишаючись на комірці «E3», двічі клацаємо по значку «Формат по образцу» панелі інструментів «Форматирование» (рис. 5).
13. Утримуючи ліву кнопку миші, послідовно обводимо комірки «E2:AI17». Тим самим ми скопіюємо параметри умовного форматування з «E2» на всі комірки зазначеного блока.
Важливо! При копіюванні параметрів умовного форматування (наприклад, інструментом «Формат по образцу») Excel перетворить формули за тими самими правилами, що і для комірок робочого листа. При копіюванні в той чи інший бік змінюється адресація колонок. При копіюванні по вертикалі Excel коригує номери рядків. Питання абсолютної та відносної адресації справедливі і при роботі з умовним форматуванням.
14. Виділяємо блок «
E2:AI17» та натискуємо клавішу «Del», щоб видалити вміст комірок, — формули в них нам більше не потрібні.Залишається відформатувати таблицю, щоб дані та діаграма добре виглядали на робочому листі. Спочатку додамо порожні рядки, щоб створити проміжки між горизонтальними відрізками діаграми. Для цього робимо так.
1. Утримуючи клавішу «
Ctrl», послідовно (!) виділяємо рядки з «3» по «17» (рис. 1).Важливо! У цьому випадку ми повинні натиснути клавішу «Ctrl» та клацанням миші виділити кожний рядок зазначеного блока.
2. Викликаємо меню «
Вставка → Строки». Усередину блока «3:17» Excel додасть порожні рядки.3. Виділяємо всі рядки основної частини таблиці. Після додавання допоміжних рядків це буде блок «
2:32».4. Клацаємо по значку випадного списку іконки «
Внешние границы». Вона розташована на панелі інструментів «Форматирование». Відкриється список із 18 елементів (рис. 6). 
5. Із запропонованого списку вибираємо варіант «Нет границы» (рис. 6). Це потрібно зробити про всяк випадок, щоб гарантовано очистити комірки від форматування меж.
6. Не знімаючи виділення, викликаємо «Формат → Ячейки…». З’явиться вікно «Формат ячеек», як наведено на рис. 7.
7. Переходимо на закладку «Граница».
8. У групі параметрів «Линия» вибираємо тип лінії «Пунктир» (рис. 7).
9. У групі «Отдельные» послідовно клацаємо лівою кнопкою мишки по кожному вертикальному роздільнику.
10. У вікні «Формат ячеек» натискуємо «ОК». На таблиці з’явиться пунктирна лінія розмітки.
11. Утримуючи клавішу «Ctrl», послідовно виділяємо рядки таблиці, в яких є дані. Порожні рядки нас поки що не цікавлять. У нашому прикладі це будуть рядки з номерами 2, 4, 6 і так далі.
12. Клацаємо по значку випадного списку іконки «Внешние границы» (рис. 6) та вибираємо варіант «Нижняя граница».
13. Не знімаючи виділення, викликаємо меню «Формат → Строка → Высота…». У вікні «Высота строки» вводимо значення «9,5» (рис. 8). При цьому розмір шрифту в основній частині документа дорівнює «8 пт».
14. Утримуючи клавішу «Ctrl», послідовно виділяємо порожні рядки табличної частини (з номерами 3, 5, 7 і так далі).
15. Через меню «Формат → Строка → Высота…» вводимо для них висоту рядків, що дорівнює «3». Інформації в цих комірках немає. Вони нам потрібні тільки для того, щоб відокремити відрізки діаграми один від одного по вертикалі.
Усе, що залишається зробити, — це підібрати ширину колонок, щоб документ з діаграмою помістився на сторінку. Остаточний вигляд діаграми Ганта показано на рис. 9.
Спробуємо змінити дати початку та тривалість виконання робіт у колонках «B» і «C» нашого документа. У правій частині таблиці відразу ж зміниться і діаграма Ганта. До речі, прямо на діаграмі ви можете написати і відповідальних виконавців за кожним етапом робіт. На мій погляд, у такому разі документ виглядатиме солідніше (рис. 9).
Календарний план та діаграми Excel
Другий спосіб вирішення завдання — побудувати діаграму з використанням стандартних графіків MS Excel. Для прикладу скористаємося переліком робіт із таблиці на рис. 1. Потім робимо так:
1. Виділяємо діапазон комірок «
A2:B17». Це будуть вихідні дані для діаграми.2. Клацаємо по іконці «
Мастер диаграмм» (панель інструментів «Стандартная», рис. 6). Відкриється вікно роботи з Майстром, як наведено на рис. 10. 
3. У цьому вікні вибираємо варіант «Линейчатая с накоплением». Натискуємо «Далее» (рис. 10). Відкриється друге вікно Майстра діаграм для визначення діапазону даних.
4. Переходимо на вкладку «Диапазон данных». Перемикач «Ряды в:» ставимо в положення «столбцах» (рис. 11).
5. На вкладці «Ряд» натискуємо кнопку «Добавить».
6. Клацаємо лівою кнопкою в полі «Значения:».
7. Переходимо на робочий лист та виділяємо блок комірок з тривалістю виконання робіт. У нашому прикладі — це діапазон «C2:C17» (рис. 12).
8. Натискуємо кнопку «Далее». З’явиться вікно третього кроку роботи з Майстром діаграм (рис. 13).
9. У цьому вікні переходимо на вкладку «Легенда» та відключаємо прапорець «Добавить легенду».
10. Натискуємо «Готово». Результат наших зусиль показано на рис. 14. Це попередній варіант діаграми. Нам залишається довести розпочате до пуття. Робимо так.
1. Клацаємо правою кнопкою миші по вертикальній осі з назвами етапів (крапка «1» на рис. 14). Відкриється контекстне меню з двох пунктів (рис. 14).
2. Із цього меню вибираємо варіант «Формат оси…». Відкриється вікно, як наведено на рис. 15.
3. Переходимо на вкладку «Шкала» та включаємо галочки «обратный порядок категорий» та «пересечение с осью Y в максимальной категории».
4. У вікні «Формат оси» натискуємо «ОК».
Настав час переформатувати відрізки діаграми. Зараз діаграма побудована за двома змінними. Тому кожен відрізок графіка складається з двох частин. Щоб отримати діаграму Ганта, перша змінна (ліва частина відрізка) нам не потрібна. Видалити цю змінну з графіка, стерши відповідні дані не можна, — зміниться зовнішній вигляд усіх відрізків діаграми. Але вихід є — потрібно зробити одну змінну невидимою! Робимо так.
1. Ставимо покажчик миші на будь-який відрізок діаграми в області першої змінної (крапка «2» на рис. 14).
2. Двічі клацаємо лівою кнопкою мишки. Відкриється вікно настройок, як наведено на рис. 16.
3. У цьому вікні перемикач «Граница» ставимо в положення «невидимая». Для перемикача «Заливка» вибираємо варіант «прозрачная».
4. У вікні «Формат ряда данных» натискуємо «ОК». Ділянки ліворуч на відрізках діаграми стануть невидимими.
Останній штрих щодо настройок діаграми — масштабування значень на осі часу. Щоб діаграма виглядала добре, вона повинна займати весь простір області побудови. Тут ситуація така.
Значення по осі часу Excel відображає у форматі дати (день, місяць і рік). Самі ж дати він зберігає у вигляді чисел. Причому кожне число — це кількість днів від «01.01.1900» до поточної дати. Якщо ми хочемо, щоб відрізки діаграми вистроїлися в чітко визначеному інтервалі, потрібно в параметрах діаграми явно вказати можливий діапазон зміни значень по осі часу. Причому виразити цей діапазон у числах — від мінімального та до максимального значення. Зробити це просто. Спочатку визначимо граничні значення діапазону. Робимо так.
1. У вільні комірки робочого листа скопіюємо мінімальну та максимальну дати з нашої діаграми. У таблиці, наведеній на рис. 1, це будуть комірки «B2» і «D17» (відповідно «01.09.11» і «22.09.11»). Ці значення ми скопіюємо в «D19» і «D20».
2. Через меню «Формат → Ячейки…» присвоїмо коміркам «D19» і «D20» формат «Общий». У результаті побачимо значення «40787» і «40808». Саме цей діапазон ми і вкажемо як масштаб по осі часу.
3. Клацаємо правою кнопкою мишки по горизонтальній осі на діаграмі. На рис. 14 це крапка з номером «3». Відкриється меню з двох пунктів, аналогічне зображеному на рис. 14. Але в цьому випадку це меню стосується осі часу.
4. Вибираємо пункт «Формат оси…». З’явиться вікно настройок, як наведено на рис. 17.
5. Переходимо на закладку «Шкала». У полі «минимальное значение:» вводимо «40787». У полі «максимальное значение:» пишемо «40808».
6. У вікні «Формат оси» натискуємо «ОК».
Останнє, що залишається зробити, — це виділити відрізки діаграми та підібрати для них відповідний формат. Для цього клацаємо правою кнопкою на будь-якому відрізку діаграми. Із контекстного меню вибираємо «Формат рядов данных…». Потім у вікні параметрів форматування вказуємо відповідний колір та заливку для відрізків діаграми. Я зупинив свій вибір на заливці узором у вигляді діагонального штрихування. Остаточний вигляд діаграми Ганта показано на рис. 18.
З поставленим завданням ми впоралися. Причому зробили це різними способами. Кожний із них має свої переваги та недоліки. При роботі з умовним форматуванням на діаграму зручно наносити пояснювальні написи. Наприклад, у графіка на рис. 9 такими написами є прізвища виконавців окремих етапів робіт. А от масштабувати діаграму, побудовану на базі умовного форматування, не дуже-то й зручно. Навіть при банальній зміні розміру шрифту доведеться підбирати висоту рядків та ширину колонок у документі. У цьому сенсі краще користуватися вбудованими графіками Excel. За такого підходу діаграма Ганта є єдиним об’єктом. Її зручно масштабувати, переміщати по робочому листу. Але зрештою вибір, безумовно, за вами.
Успішної роботи! Чекаю ваші запитання, зауваження та пропозиції на
bk@id.factor.ua та nictomkar@rambler.ru .
