В прошлой статье я рассказывал о том, как мы реализовали на основе Google Sheets собственную диаграмму Ганта для работы над игровыми проектами. Если вам зашла такая реализация или просто интересно глубже понять принципы устройства таблиц и формул, сейчас мы будем разбирать именно эти темы.
Для начала я подробнее расскажу про визуальную составляющую: какие ячейки для чего нужны. Затем познакомимся с тем, где и какие формулы применяются. Взглянем на примеры условного форматирования. Под конец узнаем, как сделать скрипт для разделения недель на шкале времени.
Для удобства дальнейшей работы повторно приведу ссылку на таблицу. Советую ее открыть и изучать формулы сразу там.
Визуал
Большая часть полей в таблице рассчитывается или заполняется автоматически. На рисунке ниже в красных прямоугольниках выделены те поля, которые требуется заполнять «вручную». Все остальное, выделенное зелеными прямоугольниками на следующей картинке, рассчитывается автоматически, через формулы. В этих областях ничего изменять руками не нужно.
На всякий случай оставлю одну картинку с описанием столбцов для ручного заполнения. Думаю, этого рисунка будет достаточно, поэтому не буду дополнительно описывать каждый столбец:
Лучше заострю внимание на столбцах и ячейках с автоматическим расчетом:
Обратим внимание на цифры в ячейках A1, I1 и K1. В них находятся формулы, которые автоматически рассчитывают нужные диапазоны для поддержки основных формул.
В ячейке A1 указан номер колонки, содержащей первую дату в шкале времени. Этот номер нужен для корректной работы скрипта по разделению недель вертикальными пунктирными сепараторами.
В ячейках I1 и K1 рассчитаны диапазоны границ всей шкалы времени. Они нужны для того, чтобы при изменении промежутка времени проекта (K3 и L3) не приходилось вручную обновлять границы в формулах для расчета дней шкалы времени. Диапазоны отличаются на одну колонку друг от друга. Для чего это сделано, мы разберем в следующих главах.
Немного про проверку данных
Прежде чем перейти к более сложной теме с формулами, отдельно стоит упомянуть про устройство работы некоторых ячеек, использующих проверку данных (data validation). Вообще, стоит сказать спасибо одному из комментариев к прошлой статье, который невольно напомнил мне, что я забыл включить описание проверки данных в эту часть.
Итак, в таблице имеются всего четыре столбца, которые используют проверку данных:
Версия задачи или вехи (колонка A);
Связи (колонки B и C);
Выбор даты (колонка H).
Валидация данных в этих колонках нужна не только для удобства пользования таблицей (как, например, в колонках выбора версии и даты), но и для реализации функционала диаграммы — например, связей.
Версия
С колонкой версии все просто. Диапазон проверки – это вся колонка версий (A), а правила (или критерий) – колонка C из листа настроек Tech Lists. В данном случае при двойном клике нам будет предложено все версии, указанных в настройках. Либо, если у нас другие цели, мы можем указать свой вариант. В таком случае проверка данных выделит уголок ячейки красным, обозначив нам ошибку (ее можно игнорировать).
Выбор дат
Валидация данных выбора дат, также как и версия, сделана больше для удобства. При двойном клике появляется дата из диапазона заданных дат проекта. Диапазон – вся колонка дат (H), а правило – вся строка с датами (строка 3), начиная от колонки O и заканчивая последней имеющейся в таблице (в данном примере — BE).
Связи
В отличие от версий и дат, тут проверка данных нужна для реализации функционала. Без этой проверки работать со связями было бы крайне затруднительно. Напомню, что поиск связей идет через название задач. По этой причине в ячейках связей нужно указать точное имя интересующей нас задачи. Таким образом, вырисовываются следующие правила для валидации данных. Диапазоны – колонки B или С в зависимости от того, какая связь нас интересует, а критерий – вся колонка E, именно в ней мы указываем названия задач и вех.
Благодаря функционалу платформы Google Sheets валидация данных работает еще и как подсказка — то есть, мы не только можем выбирать задачу через двойной клик, но также и начать вписывать название задачи, а валидация сама подскажет, какие есть варианты, основываясь на тех, что попали в критерий валидации (по сути работает как поиск).
Как работают формулы и условное форматирование
Диаграмма держится на двух слонах: формулах расчетов в самой таблице и правилах условного форматирования.
Сначала поговорим о формулах для шкалы времени и дат.
Основные формулы
На картинке красным выделены ячейки, содержащие формулы. Это все основные формулы, необходимые для работы данной таблицы (за исключением формул условного форматирования). Среди них есть только три повторяющихся формулы. Находятся они в ячейках K, L и М — это формулы расчета дат и рисков, но об этом мы уже знаем.
Всего в таблице фигурируют 10 типов формул:
Формула определения начала шкалы времени;
Формула расчета рабочих дней периода;
Формулы расчета границ шкалы времени;
Формулы расчета дат для задач (начало, конец и учет рисков);
Формула поиска границ для блоков работ и вех;
Формула отображения информации о задачах на диаграмме;
Формула определения дня недели;
Формула разбиения выбранного периода на рабочие даты;
Формула определения месяца;
Формула отображения майлстоунов в верхней части шкалы времени.
Сразу обращу внимание на то, что в некоторых форумах есть диапазоны — например, F5:F8 или подобные. Чтобы формулы работали корректно, нужно убедиться, что строка 5 — это первая строка в диапазоне ввода задач, то есть, строка с названием «Техническая строчка с формулами (добавлять ниже)», а последняя — строка «ЗАДАЧИ СОЗДАВАТЬ СТРОКОЙ ВЫШЕ». Новые строки создавать нужно только между первой и последней, но никак не после, иначе задачи выпадут из диапазона формул.
Также стоит упомянуть, что все формулы — и особенно скрипт — рассчитаны на определенный порядок строк в шапке таблицы. Это значит, что последовательность должна быть постоянной.
Получается, есть пять фиксированных строк. Первая — строка майлстоунов, после нее — месяцы, затем даты, дни и последняя – «техническая строка». Их порядок должен сохраняться всегда. Все строки ниже пятой можно перемещать как угодно (кроме самой последней).
В дополнение к этому нужно помнить, что связи между задачами (f2s и s2s) работают через функцию VLOOKUP, и зачастую в ней указан номер колонки — например, 3. Поэтому, если вы захотите удалить какие-то колонки, следует убедиться, что номер колонки не изменился, либо поменять это значение в формуле.
Формулы условного форматирования
Условное форматирование отвечает практически за всю визуальную часть таблицы, а также за одну из основных функций — отображение задач на таймлайне в виде цветных отрезков.
В работу условного форматирования входят:
Отображение задач на таймлайне, в том числе окрашивание отрезка под цвета, соответствующие отделу, и выделение рисков;
Обозначение завершенной задачи;
Разное отображение вех (блок работ и майлстоун);
Подсветка одинаковых имен в колонке задач;
Подсветка связей;
Подсветка отсутствия формул расчета дат работы;
Отображение линии дедлайна;
Выделение текущей недели и дня;
Визуальное разграничение месяцев, подсветка последней даты месяца и завершающего дня недели.
Детально о формулах
Разобьем формулы на три группы и посмотрим на логику их работы. Рекомендую держать перед глазами открытую картинку с таблицей, а лучше саму таблицу, чтобы проще понимать, на какие ячейки ссылаются формулы.
Вспомогательные формулы расчета периода
Это достаточно простые формулы, служащие для поддержки основных формул шкалы времени.
Формула определения начала шкалы времени
=COLUMN(O1)Результат: номер колонки начала таймлайна.
Эти данные необходимы для работы скрипта по отрисовке вертикальных сепараторов между неделями на таймлайне. Изначально скрипт не знает, где начинаются недели, поэтому эта функция смотрит в ячейку A1 и ожидает увидеть там номер ячейки начала таймлайна.
Формула расчета рабочих дней периода
=NETWORKDAYS(K3; L3;'Tech Lists'!$A$2:$A)Результат: количество рабочих дней между указанными датами (K1 и L1).
Формула рассчитывает количество рабочих дней, которые используются для определения длины таймлайна. В ней есть ссылка на лист настроек, а именно — на список нерабочих дней. Этот список нужен, чтобы они игнорировались в расчетах.
Формулы расчета границ шкалы времени
=ADDRESS(
3;$A$1;4)&":"&ADDRESS(3;$A$1+$I$3;4)
=ADDRESS(3;$A$1-1;4)&":"&ADDRESS(3;$A$1+$I$3;4)Результат: диапазоны таймлайна (в данном примере O3:BC3 и N3:BC3).
В этих формулах конструируются диапазоны из ссылок для линии дат со сдвигом в одну колонку. Два диапазона нужны для корректного отображения месяцев. Обратите внимание, что в формуле используется значение $I$3 — это количество дней, которое мы получили из прошлого пункта. Оно нужно, чтобы точно рассчитать необходимое количество колонок для шкалы времени.
Формулы шкалы времени
Формула расчета рабочих дат
=ARRAYFORMULA(TRANSPOSE(WORKDAY.INTL(K3;Row(Indirect("A"&Row(K3)&":A"&(Row(K3)+I3)))-Row(K3);"0000011";'Tech Lists'!$A$2:$A)))
Результат: даты расположены друг за другом в рамках заданного диапазона.
Эта формула рассчитывает дату для каждой ячейки, игнорируя нерабочие дни из таблицы настроек, и выстраивает эти даты друг за другом. Здесь используется ARRAYFORMULA для расчета дат сразу для определенного диапазона без необходимости копировать формулу в каждую ячейку.
Формула определения месяца
=arrayformula(IFERROR(IF(MONTH(INDIRECT($K$1))<>MONTH(INDIRECT($I$1)); PROPER(TEXT(INDIRECT($I$1);"Mmmm")); if(isblank(INDIRECT($K$1)); PROPER(TEXT(INDIRECT($I$1);"Mmmm"));"-"))))Результат: при смене месяца его название отображается на шкале времени.
Формула берет рассчитанные ранее диапазоны дат (ячейки I1 и K1 с результатами O3:BC3 и N3:BC3, соответственно) и смотрит, совпадает ли месяц текущей даты с месяцем даты в предшествующей ячейке. Если нет, в текущей ячейке пишется название соответствующего месяца.
Формула определения дня недели
=ARRAYFORMULA(if(O$3:3<>"";TEXT(O$3:3;"ddd");""))Результат: дата конвертируется в день недели.
Очень простая формула, превращающая дату в день недели.
Формула отображения майлстоунов в верхней части шкалы времени
=Arrayformula( IFERROR( VLOOKUP( INDIRECT($I$1); 'Tech Lists'!B$2:C; 2; FALSE);))Результат: майлстоуны из настроек отображаются в первой строке.
Данная формула смотрит на дату в строке 3 и сравнивает ее с датой майлстоуна в листе настроек. Если есть совпадение, название конкретного майлстоуна пишется в ячейке.
Формулы расчета дат и рисков
Формулы расчета дат для задач (начало, конец и учет рисков)
Все они — дата начала, конца и дата с рисками — нужны, чтобы правила условного форматирования могли опираться на рассчитанные в этих колонках даты для закрашивания нужных ячеек на таймлайне в определенные цвета. Например, ячейки от даты начала задачи до даты ее конца будут закрашены цветом отдела, а ячейки от даты конца до даты рисков — черным цветом.
Разберем каждую из формул подробнее.
Расчет начала работы
=IFERROR( IF( AND($F5="";$H5=""); MIN( INDIRECT( ADDRESS(ROW($K5)+1; COLUMN($K$1); 4) &":"& ADDRESS(ROW($K5) - 1 + MATCH(TRUE; arrayformula( ISBLANK(INDIRECT($N5))); 0); COLUMN($K$1); 4))); IFERROR( IF( ISBLANK($B5); VLOOKUP($C5; $E$5:$L$8; COLUMN($K$1) - COLUMN($E$1) + 1; FALSE); WORKDAY.INTL( VLOOKUP($B5; $E$5:$M$8; COLUMN($M$1) - COLUMN($E$1) + 1; FALSE); 1; "0000011"; 'Tech Lists'!$A$2:$A)); $H5));"--")
Результат: дата начала задачи или блока.
Данная формула достаточно объемна. По сути, она включает в себя два слоя. Первый — расчет минимальной даты скоупа работ, если строка определена как скоуп (т.е. столбцы F и H пустые). Второй – расчет начала работы над задачей, если строка определена как задача.
В первом случае формула смотрит на заранее подготовленный диапазон строк (указан в колонке N), который рассчитан отдельной формулой (чуть позже мы ее разберем). Обычно в диапазон входит следующая строка и еще 20 строк после нее (в листе настроек таблицы задается, как далеко формуле смотреть вниз). Внутри этого диапазона формула ищет минимальное значение. Это значение и будет нашей датой начала блока задач.
Во втором случае, если в строке указана отдельная задача, мы рассчитываем дату начала именно этой задачи. Так как нам нужно поддерживать связи, сначала проверяем наличие f2s связи. Если она есть, датой начала текущей задачи будет дата окончания связанной задачи +1 день. Если же связи f2s нет, смотрим на связь s2s. В этом кейсе все проще: тут датой начала текущей задачи будет дата начала связанной задачи. Если связей нет вообще, датой начала будет являться введенная нами дата в колонке H.
Расчет конца работы
=IFERROR( IF( NOT( ISBLANK($K5)); WORKDAY.INTL($K5; CEILING( IF($I5<1; 1; IF( VLOOKUP($F5; 'Tech Lists'!$D$2:$F; 3; FALSE); IFERROR($I5 * VLOOKUP($F5; 'Tech Lists'!$D$2:$G; 4; FALSE) / $G5; $I5 * VLOOKUP($F5; 'Tech Lists'!$D$2:$G; 4; FALSE)); IFERROR($I5 /$G5; $I5)))-1; 1); "0000011"; 'Tech Lists'!$A$2:$A);); "--")
Результат: рассчитанная дата завершения задачи без учета рисков.
Данная формула чуть попроще первой. К рассчитанной в прошлой формуле дате начала работы прибавляется количество дней, указанных в колонке I, но с некоторыми оговорками. Результат всегда округляется в большую сторону. Также, если указанное количество дней меньше единицы, формула все равно считает это значение как один день независимо от того, сколько человек работает над задачей.
Помимо этого, в формуле учитывается количество исполнителей. Это значит, что если мы указали оценку в 3 дня, и над задачей работают 2 человека, результатом будет 1,5 дня с округлением вверх. В информации о задаче под шкалой времени будет указано 1,5 дня, но на таймлайне будут закрашены две ячейки.
Не стоит забывать и про возможность указать специальный коэффициент для определенного отдела в листе настроек таблицы. Этот коэффициент будет влиять на расчеты данной формулы. Проще говоря, результирующее количество дней будет умножаться на коэффициент, соответствующий указанному отделу, если в настройках выставлена галочка для конкретного отдела.
Подсчет рисков
=IFERROR( IF( AND($F5=""; $H5=""; $L5<>"--"); MAX( INDIRECT( ADDRESS(ROW($M5)+1; COLUMN($M$1); 4 )&":"& ADDRESS(ROW($M5) - 1 + MATCH(TRUE; arrayformula( ISBLANK( INDIRECT($N5))); 0); COLUMN($M$1); 4))); IF($J5=""; $L5; WORKDAY.INTL($K5; CEILING( IF( VLOOKUP($F5; 'Tech Lists'!$D$2:$F; 3; FALSE); IFERROR($I5 * (1 + $J5) * VLOOKUP($F5; 'Tech Lists'!$D$2:$G; 4; FALSE)/$G5; $I5 * (1 + $J5) * VLOOKUP($F5; 'Tech Lists'!$D$2:$G; 4; FALSE)); IFERROR(($I5 * (1 + $J5)) / $G5; $I5 * (1 + $J5))); 1)-1; "0000011"; 'Tech Lists'!$A$2:$A))); "--")
Результат: дата завершения скоупа работ или перерасчет даты окончания задачи с учетом рисков, если они есть.
Так же, как и в расчете даты начала задачи, эта формула разбита на два слоя: расчет максимальной даты в случае, если строка является скоупом работ и если строка является задачей, — тогда рассчитываются риски; если рисков нет, просто транслируется дата окончания работы, рассчитанная формулой, которую мы разбирали выше.
Если в задаче указаны риски, рассчитанная прошлой формулой дата окончания игнорируется и пересчитывается заново по схожей логике, что и раньше, но оригинальная оценка (из колонки I) увеличивается с учетом заданных рисков.
Формула поиска границ для блоков работ и вех
=arrayformula(IF($F$5:$F$8=""; ADDRESS( ROW($K$5:$K$8) + 1; COLUMN($F$1); 4) &":"& ADDRESS( ROW($K$5:$K$8) + 'Tech Lists'!I2; COLUMN($F$1); 4); ""))
Результат: заранее собранные диапазоны.
Данная формула подготавливает диапазоны поиска задач внутри скоупа. Эти диапазоны нужны формулам расчета дат начала и конца скоупа работ. В отличие от формул расчета дат начала, конца и рисков, данная формула использует ARRAYFORMULA, что позволяет рассчитать диапазоны сразу для всех строк, поэтому нет необходимости ее копировать для каждой новой строчки, как это было в случае с формулами расчета дат для задач.
Может возникнуть вопрос: почему бы не применить ARRAYFORMULA для расчета дат? Ответ очень прост. Все дело в связях. ARRAYFORMULA выдает результат целиком, сразу для всех ячеек. Она не вставляет результат в ячейки по очереди, а заполняет сразу все. Так как задача может ссылаться на другую задачу, то для расчета даты, использующей связь, нужно иметь заранее рассчитанную дату связанной задачи. В случае использования ARRAYFORMULA, когда мы обратимся к дате связанной задачи, в ячейке будет пусто, так как ARRAYFORMULA еще не рассчитала даты для всех строк в списке и, следовательно, не вставила результат в соответствующие ячейки.
Формула отображения информации о задачах на таймлайне
=arrayformula(iferror( IF((O$3:$3>=$K5:$K) * (O$3:$3<=$M5:$M); if($G5:G>1; $G5:$G; ""); if(O$3:$3 = WORKDAY.INTL($M5:$M; 1; "0000011"; 'Tech Lists'!$A$2:$A); CONCAT( iferror("("& iferror( CEILING($I5:$I/$G5:$G;0,1); $I5:$I) & if(VLOOKUP($F5:$F; 'Tech Lists'!$D$2:$F; 3; false); "sp"; "d") & if($J5:$J=0; ") "; " + "& iferror( CEILING($I5:$I$J5:$J/$G5:$G; 0,1); $I5:$I$J5:$J) &"r) "); ""); $E5:$E); "")); ""))
Результат: информация о задаче выводится на таймлайне.
Формула достаточно простая, но запутанная из-за большого количества условий, спрятанных внутри. Основная цель — сконструировать текст и обозначить в нем капасити, название задачи и оценку в днях с рисками, если они есть.
Логика работы формулы следующая: проходясь по каждой ячейке под шкалой времени, она смотрит, попадает ли дата ячейки в диапазон дат между началом и концом задачи. Если да, в каждой ячейке мы указываем количество исполнителей (при условии, что их больше одного). Затем, если дата в рассматриваемой ячейке является следующим днем после завершения задачи, в этой ячейке пишем название задачи и количество дней, затраченных на работу. Причем, если используется специальный коэффициент, вместо d (days) пишется sp (special points). Кроме того, если у задачи указаны риски, они будут отражены отдельно с постфиксом r (risks).
Детально в условное форматирование
Помимо того, что с помощью условного форматирования на таймлайне отображаются отрезки с задачами, оно помогает упростить визуальное считывание информации с диаграммы.
Посмотрим на каждую из формул и разберем их.
Важно отметить, что правила условного форматирования работают по приоритетам. Это значит, что правило, расположенное первым в списке, будет важнее остальных и применится последним.
Отображение задач на таймлайне, в том числе окрашивание отрезка под цвет, соответствующий отделу, и выделение рисков
Диапазон: весь таймлайн, в данном примере — O5:BE22
=and(O$3<>"";O$3>=$K5;O$3<=$L5)Пожалуй, это основная формула, без которой диаграмму не получилось бы построить. Работает она достаточно очевидно: формула проверяет, входит ли дата ячейки в диапазон работы над задачей, указанной в рассматриваемой строке (колонки K и L).
Подсветка рисков работает по схожей формуле, только смотрит в другой диапазон – диапазон рисков (колонки L и M):
=and(O$3<>"";$M5<>"";O$3>$L5;O$3<=$M5)Если мы хотим расширить визуальную часть и присвоить цвет для каждого отдела, воспользуемся более хитрой формулой:
=and(O$3<>"";O$3>=$K5;O$3<=$L5;REGEXMATCH($F5; "(Client|Code)"))В ней добавляется функция REGEXMATCH. Она проверяет, соответствуют ли указанным правилам данные в колонке F. В рассматриваемом примере правилом является соответствие тексту «Client» или «Code». То есть, если нам нужны уникальные цвета на 5 отделов, мы должны создать 5 уникальных правил условного форматирования, каждый раз указывая интересующие нас отделы и назначая им свои уникальные цвета.
Чтобы обозначить скоуп работ, нам нужно сначала определить, что это именно скоуп, а не обычная задача. Для этого мы используем следующую формулу:
=and(O$3<>"";O$3>=$K5;O$3<=$M5;$F5="";$H5="";$G5="")В этой формуле можно увидеть дополнительные проверки на то, что колонки с отделом, датой и капасити не заполнены. В таком случае мы можем убедиться, что это скоуп, и закрасить ячейки другим цветом.
Обозначение завершенной задачи
Диапазон: весь таймлайн, в данном примере — O5:BE22
=and($D5=true;N$3=$M5)Формула проверяет, проставлена ли галочка готовности задачи на рассматриваемой строке. Если вместе с этим дата предшествующей ячейки равна дате завершения задачи, мы считаем, что в текущей ячейке содержится информация о задаче, и мы можем применить на нее форматирование. В данном примере применяется курсив и
зачеркивание.Разное отображение вех (блок работ и майлстоун)
Диапазон: все поле под шапкой A5:BE22
=and($A5<>"";$E5="")Это правило нужно, чтобы выделить релиз (майлстоун) как сепаратор. Будем считать, что колонка с релизом должна содержать какой-то текст, а колонка с названием задачи должна быть пустой. Если нам нужно отметить скоуп работ как серпаратор, как и раньше, мы убеждаемся, что в строке — именно скоуп работ, а не задача. Для этого пишем новую формулу:
=and($F5="";$E5<>"";$H5="";$A5<>"")В ней мы проверяем, что данные есть в колонках названия и версии, но отсутствует отдел и дата.
Подсветка одинаковых имен в колонке задач
Диапазон: колонка с задачами E5:E22
=countif(E$5:E;E5)>1Правило с элементарной формулой: если среди всех имен находится более одного дубликата, подкрашиваем ячейку; так как правило проходится по всем ячейкам, подкрашиваются все дубликаты.
Подсветка связей
Диапазон: колонка с задачами E5:E22
=countif(B$5:B;E5)>0Правило аналогично предыдущему, но проверяется наличие хотя бы одного совпадения имени в колонке связей. Если есть хотя бы одно вхождение, ячейка с именем подкрашивается.
В дополнение к правилу с формулой есть еще одно правило, которое просто подсвечивает ячейки со связями. В этом правиле формул нет — только условие «Содержит данные» и диапазон B5:C22 (колонки связей).
Подсветка отсутствия формулы расчета дат работы
Диапазон: колонки с формулами расчета дат и рисков K5:M22
=not(ISFORMULA(K5))Тоже довольно простое правило с формулой, которая проверяет, есть ли в ячейке формула. Если нет, к ячейке применяется форматирование.
Отображение линии дедлайна
Диапазон: весь таймлайн O5:BE22
=and(O$1<>"";$A5<>"";O$1=$A5)Здесь формула в правиле проверяет наличие данных в первой строке (т. е. наличие майлстоуна в рассматриваемой ячейке) и соответствие этого майлстоуна тому, что указан у задачи в первой колонке. Если все сходится, ячейка окрашивается.
Выделение текущей недели и дня
Диапазон: строки с датами и днями O3:BE4
=Today()=O$3Элементарное правило, которое подсвечивает сразу две ячейки, если дата ячейки соответствует сегодняшнему числу.
Визуальное разграничение месяцев, последней даты месяца и последнего дня недели
Диапазон: строка с месяцами O2:BE2
=isodd(month(O3))и
=iseven(month(O3))Тоже элементарное правило, точнее — два. Если месяц четный, закрашивается одним цветом, если нет — другим.
Выделение последней даты месяца и последнего дня недели
Диапазоны: строки дат и дней, O3:BE3 и O4:BE4 соответственно
=Month(O3)<>Month(P3)и
=weeknum(O3) <> weeknum(P3)Формулы в правилах смотрят на предыдущую ячейку, и, если месяцы или номера недель у них разные, текущая ячейка подкрашивается.
Итоги
Мы разобрали логику работы формул в таблице и в правилах условного форматирования. На деле они оказались не такими сложными, как могли бы быть. При первом просмотре они могут все еще казаться запутанными — но, немного «покурив» над ними, можно достаточно быстро разобраться в их работе.
Если интересно, рекомендую разобрать сложные формулы на части и поэкспериментировать с ними.
Я не претендую на позицию «мои формулы самые лучшие» — скорее наоборот: если у вас будут идеи, как улучшить или оптимизировать их, я буду рад почитать об этом в комментариях. Особенно если вы сможете адаптировать Arrayformula к расчету дат начала и конца задач.
И последнее, что осталось разобрать – как запустить скрипт для отрисовки сепараторов по неделям.
Простой скрипт
Как подключить
Чтобы подключить скрипт к таблице, нужно совершить три простых действия:
Перейти в Apps Script;
Скопировать код из статьи;
Вставить его в Apps Script;
Сохранить проект.
После этого у вас должна появиться новая опция в строке меню с именем Scripts. Выбрав его, появится выпадающий список с именем функции, которая создает сепараторы на границах недель.
Логика работы скрипта
Все проще, чем может показаться. Скрипт собирает данные из таблицы от колонки с номером, который указан в ячейке A1 и до самого конца своей длины, а затем смотрит в каждую ячейку на строке 3 и сравнивает ее с предыдущей. Если номер недели у этих ячеек отличается, скрипт рисует пунктирный сепаратор цвета #a7a7a7.
Заключение
За две статьи мы разобрали принцип работы всей таблицы, познакомились с формулами и правилами условного форматирования. Это должно помочь полностью осознать принцип устройства нашей диаграммы Ганта.
Подстроив под себя правила условного форматирования, убирая или добавляя колонки в таблице, ее можно настроить почти под любой проект или под любые задачи, связанные с отображением роадмапа. Вы ограничены лишь вашей фантазией. Например, для одного проекта мы чуть модернизировали таблицу для отображения план-факта по задачами.
Буду рад, если своей статьей и подробным разбором мне удалось помочь решить чью-то головную боль, связанную с построением роадмапа или заполнением диаграммы Ганта.
Приложение 1. Скрипт
var DATE_ROW = 3;
function onOpen() {
var menu = [{name: "Borders for end week cells", functionName: "DrawWeekBorder"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Scripts", menu);
}
Date.prototype.getWeek = function() {
var onejan = new Date(this.getFullYear(),0,1);
return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
}
function DrawWeekBorder() {
var sheet = SpreadsheetApp.getActiveSheet();
var dataColCount = sheet.getDataRange().getWidth()-1;
var startCol = sheet.getRange(1, 1, 1, 1).getValue();
var allDataHeight = sheet.getDataRange().getHeight();
var dateValues = sheet.getRange(DATE_ROW, startCol, 1, dataColCount).getValues();
sheet.getRange(DATE_ROW, startCol, allDataHeight, dataColCount).setBorder(null, null, null, null, false, null);
var lastWeekNum = 0;
for(var i = 0; i < dataColCount; i++)
{
var value = dateValues[0][i];
if(value == "")
continue;
var currentWeekNum = value.getWeek();
if(currentWeekNum != lastWeekNum)
{
lastWeekNum = currentWeekNum;
if(i==0)
continue;
sheet.getRange(DATE_ROW, startCol + i-1, allDataHeight, 1).setBorder(null, null, null, true, null, null,"#a7a7a7", SpreadsheetApp.BorderStyle.DASHED);
}
}
}
