Как найти все значения, находящиеся между двумя указанными датами? Давайте разберем решение этой задачи на примере.
Есть некоторая исходная таблица с данными, где каждая запись относится к определенной дате.
На основе исходной таблицы необходимо создать отчет, в котором вывести только те записи, которые соответствуют указанному интервалу дат.
Предлагаю добавить интерактивности в отчет и выбирать даты временного периода из выпадающего списка. Для большей наглядности все данные буду размещать на листе с исходной таблицей, хотя в реальной задаче было бы логичнее разделить все данные на три листа - лист с исходной таблицей, лист со вспомогательными данными и сам лист отчета.
Итак, приступим. Для решения задачи я буду использовать функции динамических массивов, так как они идеально для этого подходят.
Во-первых, подготовлю область отчета. В двух ячейках будем выбирать интересующие нас даты, а ниже будет отображаться сам отчет, содержащий только подходящие записи исходной таблицы.
Для выбора дат задействуем выпадающие списки. Данные для выпадающих списков подготовлю рядом. В первом выпадающем списке должны отображаться только те даты, которые присутствуют в соответствующем столбце исходной таблицы, однако несложно заметить, что многие даты повторяются, а значит нам нужно получить список уникальных значений, удалив дубликаты. Сделать это поможет функция УНИК, которая вернет динамический массив неповторяющихся значений.
В итоге получим диапазон числовых значений, не являющихся датами.
Это произошло потому, что для Excel любая дата является обычным числом или неким серийным номером, представляющим количество дней, прошедших с 1 января 1900 года. Поэтому для получения дат в привычном виде нужно лишь изменить числовой формат этих значений на формат даты.
Данные в исходной таблицы могут идти не в хронологической последовательности, поэтому в итоговом списке даты могут также идти не по порядку. Воспользуемся функцией СОРТ, которая позволит отсортировать полученный динамический массив.
Теперь создадим сам выпадающий список. В качестве источника данных задействуем весь динамический массив. Для этого достаточно указать первую ячейку динамического массива и затем ввести знак решетки.
Первый выпадающий список готов и теперь на его основе создадим второй. В нем должны отображаться даты, которые идут после выбранной в первом выпадающем списке. Получить их очень просто с помощью функции ФИЛЬТР.
В качестве массива данных задействуем первый динамический массив, а включать в итоговый список будет только те даты из этого массива, которые больше даты, выбранной в первом выпадающем списке. Если таких дат нет, то выведем пустоту.
Теперь создадим второй выпадающий список, указав в качестве источника данных второй динамический диапазон.
Итак, выпадающие списки с датами готовы. Сформируем сам отчет и в этом снова поможет функция ФИЛЬТР.
В качестве массива укажем весь диапазон исходной таблицы. Включать в итоговый диапазон будем лишь те данные, которые будут удовлетворять двум условиям - во-первых, дата из соответствующего столбца должна быть больше или равна дате из первого выпадающего списка, а, во-вторых, дата из этого столбца должна быть меньше или равна дате из второго выпадающего списка.
Напомню, что логический оператор И в этом случае заменяется знаком умножения (*). Логике в формулах Excel я посвятил отдельную статью и рекомендую с ней ознакомиться, чтобы понимать смысл подобных математических операций.
Если же данные удовлетворяющие этим критериям найдены не будут, то выведем текст - «Нет данных».
Теперь изменим числовое форматирование для соответствующих столбцов и получим готовый отчет. При изменении дат в выпадающих списках из исходной таблицы будут подтягиваться только соответствующие им данные.
Все готово, но стоит внести последний штрих. Для большей наглядности при создании формул я не стал изначально преобразовывать исходные данные в умную таблицу, но теперь это стоит сделать. Это позволит ссылаться в формулах не на конкретные диапазоны, а на столбцы таблицы. После преобразования исходной таблицы в умную ничего дополнительно править не придется и весь сконструированный нами отчет прекрасно будет работать при добавлении новых данных.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
