Практически любая задача, решаемая с помощью Excel имеет несколько вариантов решения. В этой заметке хочу вновь вернуться к графику ревизий, которому я уже посвятил две статьи.
Напомню суть задачи. Есть исходная таблица, в которую добавляется информация по планируемым проверкам в конкретных подразделениях предприятия. На основе этой таблицы нужно создать график ревизий, то есть визуально представить информацию в виде своеобразного календаря.
В первой заметке, которая была посвящена решению этой задачи, график был создан только с помощью условного форматирования.
Во второй я использовал сводную таблицу в несколько нестандартном виде, и этот вариант мне нравится больше, поскольку он универсальный и позволяет полностью автоматизировать процесс создания графика. Однако у него есть один существенный минус - сводная таблица не обновляется автоматически и каждый раз после внесения новых данных в исходную таблицу необходимо обновлять сводную вручную.
Есть еще, как минимум, один вариант решения этой задачи, который лишен данного недостатка. Я имею в виду формулу массива. Этот вариант решения сразу мне пришел в голову, но я его отложил, так как мне хотелось полностью автоматизировать процесс заполнения графика. Дело в том, что в первом столбце исходной таблицы данные дублируются, ведь за месяц подразделение может подвергаться проверкам неоднократно. Автоматизировать создание списка уникальных значений столбца с помощью формул возможно, но универсальное решение, доступное во всех версиях Эксель, будет крайне громоздким и сложным. Именно поэтому я изначально и отказался от идеи использования формулы массива для создания полностью автоматизированного графика ревизий. Но в ряде ситуаций подход, который я сейчас изложу, будет более простым и приемлемым.
Так в нашем случае количество и наименование всех подразделений известно и можно вручную создать первый столбец графика. Для этого можно либо ввести наименование подразделений с клавиатуры, либо скопировать первый столбец исходной таблицы и затем почистить его от дубликатов стандартными инструментами Эксель.
Ну а когда перечень подразделений в графике готов, то создадим календарь - первую строку графика, в которой указываем даты месяца и применяем к ним числовое форматирование, чтобы отображать только числа. Делается это также, как и в предыдущих мною рассмотренных вариантах решения этой задачи.
Осталось лишь сформировать сам график и для этого я буду использовать формулу массива.
По опыту знаю, что у большинства пользователей с формулами массива возникают затруднения, поэтому постараюсь максимально подробно разъяснить предлагаемый способ решения.
Итак, нам нужно найти наименование подразделения в исходной таблице. Для этого мы можем использовать следующее условие:
То есть мы ищем значение из первого столбца графика в соответствующем столбце исходной таблицы. Поскольку это логическое выражение, то на выходе мы получим один из двух результатов - ИСТИНА, в случае выполнения условия, или ЛОЖЬ.
Так как эту формулу мы в конечном итоге будем растягивать вниз и вправо, то нужно зафиксировать ссылки, чтобы они не смещались. С помощью клавиши F4 полностью фиксируем диапазон значений в исходной таблице, ну а поскольку формулу будем протягивать также и по горизонтали, то важно зафиксировать и столбец с наименованиями предприятий, поэтому делаем ссылку на него смешанной.
Таким образом мы сделали первую проверку, но нам нужно так же проверить и совпадение даты из первой строки графика с соответствующим столбцом исходной таблицы. Делается это точно также - выбираем дату и сравниваем ее с соответствующим диапазоном. Далее фиксируем ссылки. Дату мы будем протягивать по вертикали, поэтому фиксируем в ссылке только строку.
Теперь главный вопрос - а как связать эти два условия?
Дело в том, что логические выражения ЛОЖЬ и ИСТИНА для Excel равнозначны нулю и единице. Это означает, что с этими выражениями мы можем проводить математические операции.
Что будет, если мы перемножим два этих выражения?
В том случае, если наименование предприятия не будет совпадать мы получим ЛОЖЬ или 0, а умножение на ноль всегда даст ноль. Тоже самое будет и при несовпадении даты.
В итоге мы получим единицу только в том случае, когда и первое, и второе условие будут соблюдены, а это как раз то, что нам и нужно.
Если мы сделаем данную формулу формулой массива, то на выходе получим массив нулей и единиц, а точнее нулей и единицу, ведь ревизия в конкретном подразделении в определенную дату проводится только один раз. В ячейке, для которой такая формула рассчитывается, может выводиться только одно значение массива (первое), поэтому нам нужно просуммировать все значения массива, чтобы получить только одно значение. Обернем формулу функцией СУММ и сделаем формулу формулой массива, нажав сочетание клавиш Ctrl+Shift+Enter.
Так как изначально мы задавали формулу лишь для первой ячейки графика, то теперь растянем формулу на весь диапазон.
Сверяемся с исходными данными и видим, что на день запланированной ревизии приходится единица, остальные дни помечаются нулем.
Фактически задача решена и остались лишь косметические улучшения.
Создаем правило условного форматирования для всего диапазона формулы массива. Для нулевых значений можем сделать шрифт белым, чтобы их скрыть, а для единиц выберем и шрифт, и заливку одинаково красную.
В результате мы получим следующий график.
Данные в таком графике будут обновляться автоматически при внесении изменений в исходной таблице, но и тут есть нюансы.
Так в рассмотренном примере использовались обычные диапазоны значений, а не умная таблица. Это означает, что в случае появления новых данных они будут выходить за рамки указанного диапазона и его нужно будет расширять вручную.
Эта проблема имеет множество решений. Например, можно задать заведомо больший диапазон в формуле или даже указать весь столбец листа целиком. Есть и другие, более сложные решения этой задачи, но применение их в данном случае мне не кажется целесообразным.
Также можно использовать и умную таблицу, но, к сожалению, в этом случае универсальности не получится. Дело в том, что в разных версиях Эксель умные таблицы ведут себя немного по-разному и в таком варианте решения задачи будут скорее усложнять его, нежели упрощать.
Поэтому в данном случае я бы скорее остановился на максимально простом варианте - использовал бы заведомо больший (в разумных пределах) диапазон исходной таблицы. То есть если нам известно, что в месяц будет не более сотни проверок, то в формулу массива включаем диапазон, состоящий из сотни строк.
Думаю, что это самый простой вариант решения данной задачи.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм