☰

Возврат списка значений и сопоставление с типом продукта

tag-icon • tag-icon microsoft-excel search-indexing
Возврат списка значений и сопоставление с типом продукта

Я ищу формулу, которая создаст отчет об истечении срока доступа к определенным тренингам.

На данный момент моя таблица выглядит так:

Имя Обучение1 Обучение2 Обучение3
Джон 01/08/2023 01/08/2023 01/08/2023
Отметка 01/08/2023 01/06/2023 01/10/2023
Стив 01/06/2023 01/06/2023 01/06/2023

Я хотел бы создать механизм, который покажет мне срок действия обучения в течение 45 дней с текущего момента и дату его окончания, со списком названий - модулей и дат, например:

Истекает в течение 45 дней:

Имя Модуль Истекает
Джон Обучение1 01/08/2023
Джон Обучение2 01/08/2023
Джон Обучение3 01/08/2023
Отметка Обучение1 01/08/2023

Истекший:

Имя Модуль Истекает
Отметка Обучение2 01/06/2023
Стив Обучение1 01/06/2023
Стив Обучение2 01/06/2023
Стив Обучение3 01/06/2023

Мне удалось получить формулу для перечисления имен, но я не могу понять, как сопоставить обучение с заголовком или датой.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()+45), ROW(1:1))),"") / expiring within 45 days
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()), ROW(1:1))),"") / expired

Как перестроить формулу, чтобы она соответствовала заголовку обучения и дате для каждого человека?

решение1

Один из подходов к достижению этого — использование LET()оператора, который позволяет вам хранить промежуточные результаты. Таким образом, вы можете привести свои данные в правильной форме и отфильтровать их на основе ваших условий, например, дата < сегодня для истекших тренировок.

Я немного расширил ваш пример, чтобы иметь другое количество имен и модулей, чтобы сделать процедуру более удобоваримой. Данные хранятся в диапазоне A1:D5:

Имя Обучение1 Обучение2 Обучение3
Джон 01/08/2023 01/08/2023 01/08/2023
Отметка 01/08/2023 01/06/2023 01/10/2023
Стив 01/06/2023 01/06/2023 01/06/2023
Счет 01/06/2023 01/08/2023 01/10/2023

Заявление LET()выглядит следующим образом:

=LET(
data,$A$1:$D$5,

nMod,COLUMNS(INDEX(data,1,))-1,
nRow,ROWS(INDEX(data,,1))-1,

tmpM,CHOOSEROWS(TRANSPOSE(INDEX(data,1,)),SEQUENCE(nMod,,2)),
modules,SORT(INDEX(tmpM,MOD(SEQUENCE(nMod*nRow)-1,nMod)+1)),

tmpN,CHOOSEROWS(INDEX(data,,1),SEQUENCE(nRow,,2)),
names,INDEX(tmpN,MOD(SEQUENCE(nMod*nRow)-1,nRow)+1),

dates,TOCOL(CHOOSEROWS(CHOOSECOLS(data,SEQUENCE(nMod,,2)),SEQUENCE(nRow,,2)),0,TRUE),

combinedTable, HSTACK(names,modules,dates),
filteredData,SORT(FILTER(combinedTable, INDEX(combinedTable,,3)<TODAY()),1),

result, VSTACK(HSTACK("Name", "Module", "Expiring on"), filteredData),
result)

Вы начинаете с указания диапазона ваших данных, например, A1:D5в этом случае. Это может быть потенциально еще более автоматизировано, включая COUNTA()оператор или что-то подобное, но учитывая, что диапазон необходимо указать только один раз, в большинстве случаев этого достаточно. Все остальное вычисляется автоматически.

Далее мы начинаем с вычисления количества модулей и строк, как количества столбцов/строк в "data" - 1, учитывая, что "Name" и "Header" не являются соответствующим модулем или строкой. На следующем этапе мы извлекаем соответствующие имена модулей (например, Training1-Training3) как tmpM. Модули должны быть повторены x раз на основе количества соответствующих модулей и строк. Чтобы добиться этого, мы объединяем INDEX()с MOD()и SEQUENCE(). Та же процедура повторяется для создания соответствующей последовательности имен, которая повторяет имя так часто, как это необходимо на основе модулей. Наконец, нам нужно вертикально сложить все даты в один столбец, а не иметь его в виде матрицы. Для этого мы используем в основном функцию TOCOL(). Кроме того, мы также выбираем только даты, т. е. без имен и заголовков, используя дополнительно CHOOSEROWSи CHOOSECOLS. После этого мы горизонтально складываем вместе "names", "modules" и "dates" с помощью HSTACK(). Результирующая таблица выглядит следующим образом:

А Б С
Джон Обучение1 01/08/2023
Отметка Обучение1 01/08/2023
Стив Обучение1 01/06/2023
Счет Обучение1 01/06/2023
Джон Обучение2 01/08/2023
Отметка Обучение2 01/06/2023
Стив Обучение2 01/06/2023
Счет Обучение2 01/08/2023
Джон Обучение3 01/08/2023
Отметка Обучение3 01/10/2023
Стив Обучение3 01/06/2023
Счет Обучение3 01/10/2023

Следующим шагом является простой FILTER()оператор, который фильтрует данные, имеющие дату < TODAY(). Для этого мы хотим отфильтровать "combinedTable" на основе третьего столбца, т. е. даты. Чтобы использовать эту информацию в операторе фильтра, мы используем функцию INDEX().

Если вы хотите отфильтровать данные, которые относятся к следующим 45 дням, вам необходимо настроить оператор FILTER()следующим образом, чтобы отфильтровать только те случаи, которые имеют дату > TODAY()и дату <= TODAY()+45:

filteredData,SORT(FILTER(combinedTable,
(INDEX(combinedTable,,3)>TODAY())*(INDEX(combinedTable,,3)<=TODAY()+45)),1),

После фильтрации данных мы собираем результаты, т. е. включаем соответствующие заголовки и используем VSTACK()оператор для объединения заголовков с отфильтрованными данными. Выход представляет собой единый массив слива, который содержит всю необходимую информацию и нуждается только в одной формуле в одной ячейке. Окончательный вывод выглядит следующим образом:

Истекший:

Имя Модуль Истекает
Счет Обучение1 01/06/2023
Отметка Обучение2 01/06/2023
Стив Обучение1 01/06/2023
Стив Обучение2 01/06/2023
Стив Обучение3 01/06/2023

Истекает в течение 45 дней:

Имя Модуль Истекает
Счет Обучение2 01/08/2023
Джон Обучение1 01/08/2023
Джон Обучение2 01/08/2023
Джон Обучение3 01/08/2023
Отметка Обучение1 01/08/2023

Если вы хотите показать промежуточные шаги, вы можете просто заменить последний «результат» в формуле любым другим именем, которое было определено, например, «combinedTable», «dates» и т. д.

Связанный контент