В предыдущей заметке я рассмотрел процесс создания графика ревизий на основе исходной таблицы. При этом я применил условное форматирование.
Однако задача оказалась несколько сложнее и предложенное мною решение в данном случае не подошло. Дело в том, что в исходную таблицу вносятся данные о планируемых ревизиях в различных подразделениях предприятия и таких проверок может быть несколько в месяц. В рассмотренном примере это уже отражено, так как отдел доставки проверяется дважды, и на разработанном мною графике этот отдел также фигурирует дважды в разных строках, что крайне не информативно.
Необходимо, чтобы каждое подразделение на графике выводилось только одной строкой, в которой бы закрашивались даты ревизий.
Давайте решим и эту задачу.
Итак, нам нужны уникальные значения из первого столбца исходной таблицы, чтобы на их основе построить график. В этот раз нам поможет сводная таблица.
Во-первых, давайте преобразуем исходную таблицу с данными в умную таблицу. С такими таблицами намного проще работать, так как в случае их расширения, то есть при добавлении новых данных в таблицу, они автоматически будут подхватываться сводной таблицей, которую мы превратим в график. Для этого достаточно выделить диапазон значений и нажать сочетание клавиш Ctrl + T.
Теперь создадим на основе этой таблицы сводную. Подразделения пойдут в заголовки строк, а даты в cтолбцы. Также дата пойдет и в значения.
В итоге мы получим приблизительно то, что нужно, но вот только внешне таблица выглядит не так, как хотелось бы - в ней выводятся только даты ревизий. Необходимо, чтобы в первой строке заголовков выводились все даты месяца. Просто так занести данные в сводную и связать их с уже имеющимися датами не выйдет, но мы можем воспользоваться небольшой хитростью. Я уже рассказывал в одной из заметок о модели данных и поэтому не буду сейчас на этом подробно останавливаться. Если вкратце, то модель данных позволяет объединить информацию из нескольких таблиц и в итоге построить сводную таблицу на базе этих общих данных. Нам необходим календарь, то есть перечень дат месяца, в который будет производиться ревизия. Создам новый лист и назову его, например, «Январь». Далее с помощью автозаполнения создам список всех дат этого месяца.
Теперь преобразую диапазон с датами в умную таблицу и дам ей название, чтобы затем было проще к ней обращаться через модель данных.
Также дам название и первой таблице.
Все готово. Теперь создаем сводную на базе исходной таблицы и добавляем ее в модель данных.
Переходим на вкладку Все и видим здесь две ранее нами созданных таблицы и все поля, которые мы можем использовать в сводной.
Перетягиваем подразделения в строки, а даты из вновь нами созданной таблицы в заголовки столбцов. Даты ревизий из исходной таблицы помещаем в значения.
Появляется сообщение, что необходимо связать данные из двух разных таблиц. Этим связующим звеном у нас выступают столбцы с датами. Давайте зададим отношения вручную (кнопка СОЗДАТЬ). Сначала выбираем основную и вспомогательную таблицы, а затем столбцы в каждой из них, которые являются датами.
Все вроде бы готово, но даты месяца по прежнему не появилось.
Дело в том, что в сводной по умолчанию отображаются только столбцы с данными, а поскольку в других столбах данных нет, то они были Экселем скрыты. Через контекстное меню, вызванное щелчком правой кнопки мыши на сводной таблицы, заходим в ее параметры и переходим на вкладку Вывод. Включаем отображение пустых столбцов.
Теперь все отображается верно и осталось лишь поработать над внешним видом графика.
Во-первых, давайте скроем строку с фильтром столбцов.
Затем переименуем фильтр заголовков строк в «Подразделения» и отключим отображение общих итогов.
Теперь выделим все даты в строке заголовков и зададим для них подходящее числовое форматирование, то есть чтобы выводились лишь числа, без месяца и года.
Зададим границы для сводной и изменим ширину ее столбцов.
Ну а теперь можем воспользоваться условным форматированием и зададим простейшее правило для всего диапазона значений сводной таблицы - если значение равно единице, то и текст, и фон ячейки сделаем красным.
Теперь при введении новых данных в исходную таблицу нужно будут обновить сводную и все данные на ней отобразятся в виде закрашенных ячеек.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
★ Серия видеокурсов "Microsoft Excel Шаг за Шагом"
