Календарный план в Excel
 | За последнее время в почту редакции поступило несколько писем от работников аудиторских компаний. Вопросы были разные, многие из них носили сугубо специальный характер. Но одна проблема, на мой взгляд, могла бы заинтересовать достаточно широкий круг наших читателей. Речь идет о разработке календарного плана. О том, как построить такой план в программе Excel и где сможет применить его практикующий бухгалтер, мы поговорим в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Календарный план — это последовательность работ, выстроенная во времени в порядке их выполнения. Для построения плана нужно знать перечень работ, длительность их выполнения, а также взаимосвязи между началом и окончанием каждой работы. Эти взаимосвязи вытекают из самой сути задачи. Например, чтобы возвести стены, нужно предварительно заложить фундамент, перед выдачей зарплаты нужно заплатить налоги и т. д. Календарный план можно представить по-разному. В частности, в табличной форме. Для этого в одной колонке можно составить перечень работ, а рядом показать сроки их выполнения. Такая таблица дает исчерпывающую информацию о составленном плане. Но вот пользоваться ею неудобно: чтобы понять последовательность действий, придется каждый раз анализировать даты. А это и долго, и утомительно. Поэтому на практике календарные графики принято изображать в виде так называемых диаграмм Ганта. Такой способ впервые предложил американский инженер Генри Лоренс Гант (Henry Laurence Gantt, 1861 — 1919). На диаграмме Ганта работы показаны в виде отрезков. Длина отрезка пропорциональна продолжительности работы, а сами отрезки расположены вдоль оси времени в хронологическом порядке. Такое представление очень наглядно, им удобно пользоваться на практике. Именно благодаря этому диаграммы Ганта остаются основным способом изображения календарных графиков с начала прошлого века и до наших дней.
Идея, думаю, понятна, но как применить ее на практике? Где могут быть полезны диаграммы Ганта? Ответ — практически везде, когда речь идет об анализе или описании динамических процессов. Всего лишь несколько примеров. Рабочая документация аудитора, подготовленная согласно международным стандартам (МСА 230 «Документация»), кроме всего прочего, должна содержать:
1) план аудиторской проверки;
2) характер, время и объем выполненных аудиторских процедур.
Включить в указанные документы диаграммы Ганта будет вовсе не лишним… Бухгалтер может воспользоваться календарными графиками для анализа движения денежных средств. В этом случае диаграмма Ганта покажет точные даты будущих поступлений и обязательных платежей. Кстати, такой подход — заманчивая и довольно перспективная альтернатива достаточно неповоротливому процессу бюджетирования. Так что применений более чем достаточно. Остается вопрос: как построить календарный график?
Вообще-то для создания диаграмм Ганта есть специальные программы. Это в первую очередь MS Project, MS Visio, Spider, Business Project и мн. др. Все они популярны и по-своему интересны. Но для наших целей богатые возможности этих программных продуктов попросту не нужны. Тем более что за эти возможности нужно заплатить, и заплатить немало. Мы применим для решения задачи «старый добрый» MS Excel. Причем сделаем это двумя способами: с использованием условного форматирования и набора стандартных диаграмм.
Диаграмма Ганта и условное форматирование
Первым делом четко сформулируем нашу задачу. Есть перечень работ. Известны даты начала и длительности выполнения каждой работы. Наша цель — по этим данным нарисовать диаграмму Ганта, где все работы будут изображены горизонтальными отрезками вдоль оси времени. И сейчас мы посмотрим, как можно этого добиться при помощи условного форматирования. В общих чертах последовательность наших действий будет такой.
Вначале создадим таблицу, где в отдельных колонках будут записаны названия работ, а также сроки их выполнения. В правой части таблицы мы организуем группу рабочих ячеек. Каждая колонка в этой части таблицы будет соответствовать определенной дате. Причем даты от колонки к колонке будут изменяться с шагом 1 день. Добавляя колонки в рабочую область, мы сделаем так, чтобы даты охватывали весь горизонт нашего планирования. Затем рабочим ячейкам в правой части таблицы мы присвоим условный формат. В качестве условия для этого формата мы воспользуемся формулой, которая определит, попадает дата текущей колонки в период выполнения работы или нет. Если условие будет истинно, то в соответствующей ячейке мы изменим цвет фона. В результате такого форматирования всей группы рабочих ячеек в ней появится изображение диаграммы Ганта.
Начнем с подготовки данных. Делаем так:
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.2011», дата окончания — «04.09.2011». В заголовке колонки «E» стоит дата «31.08.11». Формула должна проверить, принадлежит эта дата промежутку с «01.09.2011» по «04.09.2011» или нет. Если принадлежит, формула вернет значение «Истина». В противном случае значением формулы будет «Ложь». Чтобы организовать такую проверку, нам понадобится функция «И()» из стандартной библиотеки MS Excel. В качестве параметров этой функции нужно указать несколько логических выражений. Если все эти выражения истинны, результат работы функции будет «Истина». Если хотя бы одно условие не выполняется, результатом функции будет «Ложь». В нашем конкретном случае формула в ячейке «E2» будет выглядеть так: «=И(E$1>=$B2;E$1<=$D2)». Вот как она работает. Первый параметр «E$1>=$B2» сравнивает дату начала работы «Исследование специфики предприятия» с датой в заголовке колонки «E», т. е. она сравнивает «01.09.2011» с «31.08.11». Результатом сравнения будет «Ложь», так как «31.08.11» не больше «01.09.2011». Этого достаточно, чтобы результат функции «И()» стал равен «Ложь», поскольку один из ее параметров уже вернул такое значение. Однако разберемся и со вторым параметром функции. Он выглядит так: «E$1<=$D2». Эта формула сравнивает дату окончания работы «Исследование специфики предприятия» с датой в заголовке колонки «E». Если дата в заголовке станет больше даты окончания работы, результатом проверки будет «Ложь». Таким образом, выражение «=И(E$1>=$B2;E$1<=$D2)» проверяет, принадлежит ли дата в «E$1» интервалу выполнения работы в текущей строке. Поскольку мы будем копировать формулу вниз и вправо по таблице, перед соответствующими элементами ставим символы абсолютной адресации «$». Записав формулу в ячейку «E1», копируем ее на весь диапазон таблицы (блок «E2:AI17»). Результат нашей работы показан на рис. 2.
В целом задача решена. В тех ячейках, где предполагается выполнение работ, Excel проставил значение «Истина». В остальных ячейках находится значение «Ложь». Например, работу «Исследование специфики предприятия» мы запланировали на период с «01.09.2011» по «04.09.2011». Под это условие попадают колонки «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 .
